20子查询：

在一条sal语句中在嵌套一条或者多条select语句。

1.单行子查询：子查询的执行结果只有一行数据；一般配合单行选择运算符使用。

        e.g:先查询出来最高的工资，然后再找出工资等于最高工资的员工

    select * from emp where sal= (select max(sal) from EMP );

注意，1.单行子查询返回的结果只能有一行数据，返回多行则报错，返回多个列也报错。

2.多行子查询：子查询的执行结果返回多行数据，并且多行子查询都是配合多行选择运算符一起使用：例如any   all   in ...

--查询部门'SALES'和'RESEARCH'部门的员工的信息

select * from emp where deptno in(select deptno from dept where dname in('SALES','RESEARCH'));

select * from emp where deptno=any(select deptno from dept where dname in('SALES','RESEARCH'));

3.关联子查询：子查询的执行结果必须要依赖外查询条件。

 --查询员工的工资大于他所在部门的平均工资的员工的信息

select *from emp e  join (select deptno,avg(sal) from emp group by deptno ) d on e.deptno=d.deptno  where e.sal>d.avg(sal);

select * from emp  e where sal>(select avg(sal) from where )

    --将张三老师教授的课程的成绩改成该课程的平均成绩
--UPDATE sc SET score=(?) WHERE cid IN(张三老师教)
    SELECT cid FROM course WHERE tid =(SELECT tid FROM teacher WHERE tname='张三');
    UPDATE sc s SET score=(SELECT AVG(score) FROM sc WHERE cid=s.cid) WHERE cid IN(SELECT cid FROM course WHERE tid =(SELECT tid FROM teacher WHERE tname='张三'))

  exists:在子查询中功能和in基本是一样的，也就是说在子查询中可以使用in实现的功能都可以使用exists来实现。 exists属于关联子查询

  in语法：select * from 表 a where 列 in(select 列 from 表)
           exists语法：select * from 表 a where exists(select * from 表 b where a.关联列=b.关联列)

-将张三老师教授的课程的成绩改成该课程的平均成绩
        --UPDATE sc SET score=(?) WHERE cid IN(张三老师教)
        --使用in
        UPDATE sc s SET score=(SELECT AVG(score) FROM sc WHERE cid=s.cid) WHERE cid IN(SELECT cid FROM course WHERE tid =(SELECT tid FROM teacher WHERE tname='张三'))       
        --使用exists
        UPDATE sc s SET score=(SELECT AVG(score) FROM sc WHERE cid=s.cid) WHERE EXISTS(SELECT cid FROM course WHERE tid =(SELECT tid FROM teacher WHERE tname='张三') AND s.cid=cid)        

 rownum:行号
       每次在查询数据的时候默认数据是没有行号，但是如果要显示行号则可以通过关键字rownum来产生一个行号
       使用场景：分页查询数据

分页查询的语法：
        select * FROM(select a.*,rownum rn from 表) where rn between 开始 and 结束;

--查询工资排名3-5的员工的姓名，职位以及工资
        SELECT * FROM (
        SELECT ename,job,sal,ROWNUM rn FROM (
        SELECT e.* FROM emp e ORDER BY sal DESC)) WHERE rn BETWEEN 3 AND 5 ;

rowid:伪列         

 伪列属于表中的额一个隐藏列，主要是保存每一行数据的物理地址，在添加数据的时候产生。

通过rowid来删除表中的重复数据：

思路：先找出重复的数据中的最大的或者是最小的rowid保留，将其他的删除
     语法：delete from 表名 where rowid not in(select max(rowid) from 表 group by 列名,列名,......)
         或者
           select from 表名 a where not exists(select max(rowid) from 表 group by 列名,列名,...... having a.rowid=max(rowid))

--删除重复的数据
    DELETE FROM rowid_t WHERE ROWID NOT IN(SELECT MAX(ROWID) FROM rowid_t GROUP BY rid,rname);

    DELETE FROM rowid_t r WHERE NOT EXISTS(SELECT MAX(ROWID) FROM rowid_t GROUP BY rid,rname HAVING r.rowid=MAX(ROWID));

2.表集合操作：

1.并集操作：将两个结果集合并到一起。

关键字：union [all]
     语法：select语句
           union [all]
           select 语句
           union [all]
           select 语句

注意：1.all关键字可以省略，但是如果省略了all则合并完成之后先进行排序然后再进行去重操作，所以效率比较低。
            如果指定了all则不会进行去重，只是简单的合并。
           所以在实际的工作中一般建议使用：union all 来代替union
           2.必须要保证合并的结果集中列数和列对应的数据类型要一致。
           

交集操作：获取两个结果集中相同的数据。
     关键字：intersect
     语法：select语句
           intersect
           select 语句
           intersect
           select 语句

--e.g:查询学过“01”并且也学过编号“02”课程的同学的学号、姓名；
    SELECT SID FROM sc WHERE cid='01'
    INTERSECT
    SELECT SID FROM sc WHERE cid='02'     

差集操作：集合A-集合B：返回结果集A中除了和B相同的数据之外的数据
    关键字：minus
    语法：select语句
           minus
           select 语句
           minus
           select 语句
           ......

--差集
    SELECT * FROM user_t
    MINUS
    SELECT * FROM user_t1;

开窗函数：在现有的表格的后面添加一些窗口列，用来显示汇总或者是处理之后的结果。
  语法：函数() over([partition by 分组列,.... order by 排序列 [ASC|DESC]])
   partition by:主要实现在开窗函数中进行分组统计数据。
   order by:在开窗中进行排序操作，如果order by和聚合函数一起使用则会触发定位框架的计算。
   定位框架语法：
     order by 排序列 [desc|asc] range|rows between 下边界  and 上边界
     默认语法：order by 排序列 [desc|asc] range between unbounded preceding  and current row

边界值：
     unbounded preceding ：一直往上到第一行
     n preceding :往上n行
     current row:当前行
     n following:往下n行
     unbounded following:一直往下到最后一行
     
     注意：range按照值的范围进行累加操作（默认）
           rows按照行进行累加操作

 开窗函数中常用的函数：
  1.聚合函数：
        ① sum() -- 分组求和

        ② count() -- 分组求总数

        ③ min() -- 分组求最小值

        ④ max() -- 分组求最大值

        ⑤ avg() --分组求均值

--查询所有的员工的信息以及员工所在部门的平均工资

SELECT e.*,SUM(sal) OVER(ORDER BY sal ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 工资总和 FROM emp e;

2.排名函数：
     注意：排名函数必须要结合开窗中的order by一起使用
     row_number():忽略重复情况，直接显示连续的不重复的名次，相当于行号。
     rank():重复的值名次一样，但是整个排名是跳跃的，不连续。
     dense_rank():重复的值名次一样，但是整个排名是连续的。

SELECT e.*,row_number()OVER(ORDER BY sal DESC),
           RANK() OVER(ORDER BY sal DESC),
           dense_rank()OVER(ORDER BY sal DESC) FROM emp e;

注意：如果要在小组内进行排名，则需要在窗体函数（over）中使用partition by进行分组

使用场景：1.明确要求显示名次。
              2.如果要获取每一个小组内的前几名，则需要使用排名函数。
              3.连续性问题解决：使用开窗函数分组排序，然后使用时间减去名次，得到的结果如果是一样的则代表是连续的,否则就不连续。

--查询连续夺冠1次以上的队伍的开始夺冠时间和结束夺冠时间。

SELECT team,MIN(y)开始,MAX(y) 结束 FROM(
            SELECT n.*,y-row_number()OVER(PARTITION BY team ORDER BY y) r FROM nba n)
            GROUP BY team,r HAVING count(*)>1 

3.移位函数：
      注意：移位函数必须要结合开窗中的order by一起使用
      1.lag():下移函数
        语法：lag(列名,n[,空值的填充值]) over(order by ....)
      2.lead():上移函数
        语法：lead(列名,n[,空值的填充值]) over(order by ....)

使用场景：计算环比和同比使用
       SELECT empno,ename,sal,LAG(sal,1,0) OVER(ORDER BY sal) 下移,
                       lead(sal,1,0) OVER(ORDER BY sal) 上移 FROM emp e;

--计算2015年每个月的环比率和同比率
        SELECT a.*,round((sell-上月)/上月*100,2) 环比率,
                   round((sell-去年)/去年*100,2) 同比率 from(
        SELECT s.*,LAG(sell,1)OVER(ORDER BY MONTHS)上月,LAG(sell,12) OVER(ORDER BY MONTHS)去年 FROM sales s) a;