--复杂查询
    --聚合函数，例如min，max
    --1,最低工资
		select min(sal) from emp
		select * from emp
		select ename,sal from emp where sal=(select min(sal) from emp)
    --sql语句执行顺序从右向左，
    --2,平均工资和总和
	    select avg(sal) 'average',sum(sal) 'total' from emp
    --3,高于平均工资的员工选出来
      select ename , sal from emp where sal > (select avg(sal) 'average' from emp)
    --4,加入平均工资列
    --4,总人数
        select count(*) from emp

	--group by分组函数
	--每个部门的平均工资
		select avg(sal) 'average',deptno from emp group by deptno
	--每个部门每种岗位的平均工资
	    select avg(sal) 'avg' ,job,deptno from emp group by deptno ,job
	--平均工资<2000的部门及平均工资  
	    --having语句，对分组结果进行查询
	    select avg(sal),deptno from emp group by deptno having avg(sal)<2000
    --分组函数：
        --1，分组函数只能出现在选择列表
        --2，group > having > order 优先性

  select * from emp
  select * from dept
--多表查询
  --基于两个或者以上的表，视图查询，,笛卡儿集
  --1,例如显示部门位置和员工姓名
  --两张表带有相同的列需要用表名区别
  select * from emp,dept where dept.deptno=emp.deptno
  --2,指定部门号为20,30,并按照其排序
  select * from emp,dept where dept.deptno=emp.deptno and
   (dept.deptno=20 or dept.deptno=30) order by dept.deptno

update emp set mgr=7888 where mgr=7788
--自连接
  --在同一张表上的连接查询
  --1,显示lili的上级姓名
  select ename from emp where empno=(select mgr from emp where ename='lili')
  --2,显示每个人的姓名和上级的姓名
  --构建两张表，分别为w，b，
  select  worker.ename 'worker',boss.ename 'boss' from emp worker,emp boss 
  where worker.mgr=boss.empno

--子查询
  --嵌入在sql语句的select语句，把查询结果当作临时的结果
  --1,单行子查询 显示lili的同部门的员工
  select * from emp where deptno=(select deptno from emp where ename='lili')
  --2,多行子查询
  --查询部门20的工作岗位上的员工的信息
  select * from emp where job in (
  select distinct job from emp where deptno=20)
  --3,from子句中使用子查询,必须给表指定别名
  --显示高于各自部门平均工资的员工信息
  --(1)拿到各个部门的平均工资
  select avg(sal),average from emp group by deptno
  --(2） 把上面的表当作临时另一张表，进行多表查询
  select * from emp ,(select avg(sal) average,deptno from emp group by deptno) salary
  where emp.sal>salary.average and emp.deptno=salary.deptno

--分页查询，数据量比较大时，分页显示
  --按员工的id号升序取出，显示6-10排名的人
  --top指令,top后面的数表示选出6条，这6条根据后面的where选择
  select top 5 * from emp order by empno
  select top 5 * from emp where empno not in 
  (select top 5 empno from emp order by empno)
  order by empno

--删除表中的重复数据
  create table cat(
  catID int,
  catName nchar(10)
  )
  --重复执行创建大量数据
  insert into cat values(2,'kk')
  select * from cat
  --选择不重复数据输出至临时表tmp
  select distinct *  into tmp from cat
  --删除临时表tmp
  drop table cat
  --把tmp数据插入cat
  select * into cat from tmp
  --删除tmp
  drop table tmp

--左连接和有连接
  --显示员工和上级名字，要求没有上级的人也要写出来
  select * from emp
  update emp set mgr=0 where mgr=7888
  --内连接
  select w.ename,b.ename from emp w,emp b where w.mgr=b.empno
  --左外连接:指左边的表的记录全部都要出现，如果右面没有匹配的记录，
  --        就用null来填
  select w.ename,b.ename from emp w left join emp b on w.mgr=b.empno