SQL 子查询

一、子查询的概念
    将一个查询块嵌套在另一个查询块的 WHERE 子句或 HAVING 短语的条件中的查询称为子查询。一个 SELECT-FROM-WHERE 语句称为一个查询块。
    例如：select sname from student where sno in (select studentid from sc  where courseid=2);
    子查询返回的数据通常在主查询里作为一个条件，从而进一步限制数据库返回的数据。它可以用于 SELECT、INSERT、UPDATE 和 DELETE 语句。
    在某些情况下，子查询能够间接地基于一个或多个条件把多个表里的数据关联起来，从而代替结合操作。当查询里使用子查询时，子查询首先被执行，然后主查询再根据子查询返回的结果执行。子查询可以用于主查询的 WHERE 或 HAVING 子句。逻辑和关系操作符，比如=、>、<、<>、!=、IN、NOT IN、AND、OR，可以用于子查询里，也可以在 WHERE 或 HAVING 子句里对子查询进行操作。
    注意：子查询规则——标准查询的规则同样适用于子查询，结合操作、函数、转换和其他选项都可以在子查询里使用。
二、子查询必须遵守的规则
    1、子查询必须位于圆括号里。
    2、除非主查询里有多个字段让子查询进行比较，否则子查询的 SELECT 子句里只能有一个字段。
    3、子查询里不能使用 ORDER BY 子句。在子查询里，我们可以用 GROUP BY 子句实现 ORDER BY 功能。
    4、返回多条记录的子查询只能与多值操作符（比如 IN）配合使用。
    5、子查询不能直接被包围在函数里。
    6、操作符 BETWEEN 不能用于子查询，但子查询内部可以使用它。
二、子查询的分类
    按照内外层是否进行连接划分，子查询可分为嵌套子查询和相关子查询。              
    1、嵌套子查询
       嵌套子查询——子查询独立，不受主查询影响，运算完成后返回结果给主查询。
       例1：查询雇员 ID 是1 的上级领导所管理的所有员工数据：
       SELECT * FROM demo_employee WHERE 上级=(SELECT 上级 FROM demo_empoyee WHERE 雇员ID=1);
       子查询可以嵌入到另一个子查询里，就象子查询嵌套在普通查询里一样。在有子查询时，子查询先于主查询执行。类似地，在嵌套的子查询里，最内层的子查询先被执行，然后再依次执行外层的子查询，直到主查询。
    2、相关子查询
        相关子查询——主查询将必要信息传入子查询对比，符合的子查询数据才会被外层查询返回结果。
        例2：查询雇员ID是1 的上级领导所管理的所有员工数据： SELECT * FROM demo_employee E WHERE EXISTS (SELECT 1 FROM demo_employee D WHERE E.上级=D.上级 AND D.雇员ID=1);
        子查询可以与数据操作语言（DML）配合使用。下面我们以数据库 learnsql 中的表做例子加以说明。
三、在 INSERT 语句中使用子查询
    INSERT 语句将子查询返回的结果插入到另一个表。
        例3：把小时工资高于雇员220984332的所有雇员的 EMP_ID、LAST_NAME、FIRST_NAME 和 PAY_RATE 插入到一个名为 RICH_EMPLOYEES 的表里：
        INSERT INTO RICH_EMPLOYEES
        SELECT E.EMP_ID, E.LAST_NAME, E.FIRST_NAME, EP.PAY_RATE FROM EMPLOYEE_TBL E, EMPLOYEE_PAY_TBL EP WHERE E.EMP_ID=EP.EMP_ID AND EP.PAY_RATE>(SELECT PAY_RATE  FROM EMPLOYEE_PAY_TBL WHERE EMP_ID='220984332');
四、在 UPDATE 语句中使用子查询
       子查询可以与 UPDATE 语句配合使用来更新一个表里的一个或多个字段。
       例4：把居住在 Indianapolis 的全部雇员的小时工资提高 10%：
       UPDATE EMPLOYEE_PAY_TBL
       SET PAY_RATE=PAY_RATE*1.1
       WHERE EMP_ID IN (SELECT EMP_ID FROM EMPLOYEE_TBL WHERE CITY='INDIANAPOLIS');
       这个子查询返回多条记录，因此要使用操作符 IN 而不是等号。
五、在 DELETE 语句中使用子查询
        子查询也可以与 DELETE 语句配合使用。
        例5：从 EMPLOYEE_PAY_TBL 表里删除 BRANDON GLASS 的记录，由于不知道他的标识号码，我们可以利用一个子查询，根据名字从 EMPLOYEE_TBL 表里获取他的标识号码：
         DELETE FROM EMPLOYEE_PAY_TBL
         WHERE EMP_ID=(SELECT EMP_IDFROM EMPLOYEE_TBL
                       WHERE LAST_NAME='GLASS'
                          AND FIRST_NAME='BRANDON');
六、子查询举例
        有学生表 Student(Sno, Sname, Sage, Ssex)，包括学号、姓名、年龄、性别四个字段；
        课程表 Course(Cno, Cname, Tno)，包括课程编号、课程名、教师编号三个字段；
        成绩表 SC(Sno, Cno, Score)，包括学号、课程编号、成绩三个字段；
        教师表 Teacher(Tno, Tname)，包括教师编号、姓名两个字段。
        完成如下查询：
    1、查询全部学生都选修的课程的课程号和课程名
    Select Cno, Cname
    From Course
    Where Cno in (select Cno from SC
          group by Cno);
    2、查询没学过"叶平"老师讲授的任一门课程的学生姓名
    Select Sname
    From Student
    Where Sno not in (select Sno from Course,Teacher,SC
    where Course.Tno=Teacher.Tno and SC.Cno=course.Cno
    and Tname='叶平');
    3、查询所有课程成绩小于60分的同学的学号、姓名；
    Select Sno,Sname
    From Student
    Where Sno not in (select S.Sno
    from Student S, SC
    where S.Sno=SC.Sno and SC.Score>=60);
    4、查询有两门以上课程不及格的学生的学号及其平均成绩
    Select Sno, avg(Score)
    From SC
    Where Sno in (select Sno from SC
    where Score<60
    group by Sno
    having count(*)>2)
   Group by Sno;
    5、查询没有学全所有课的同学的学号、姓名；
    Select Student.Sno, Student.Sname
    From Student,SC
    Where Student.Sno=SC.Sno
    Group by Student.Sno, Student.Sname
    Having count(SC.Cno)<(select count(Cno) from Course);
    6、查询学过编号"001"并且也学过编号"002"课程的同学的学号、姓名
    Select Student.Sno, Student.Sname
    From Student,SC
    Where Studet.Sno=SC.Sno and SC.Cno='001'
    and exists (Select * from SC as SC_2
    where SC_2.Sno=SC.Sno and SC_2.Cno='002');
    7、查询至少有一门课与学号为"1001"的同学所学相同的同学的学号和姓名；
    Select S.Sno, S.Sname
    From Student S, SC
    Where S.Sno=SC.Sno 
    and SC.Cno in (select Cno from SC 
    where Sno='1001');
    8、查询学过学号为"001"同学所学一门课的其他同学的学号和姓名；
    Select distinct S.Sno, S.Sname
    From Student S, SC
    Where S.Sno=SC.Sno 
    and Cno in (select Cno from SC 
    where Sno='001');
    9、查询和"1002"号同学学习的课程完全相同的其他同学的学号和姓名；
    Select Sno from SC
    Where Cno in (select Cno from SC where Sno='1002')
    group by Sno
    having count(*)=(select count(*) from SC where Sno='1002');
    10、向 SC 表中插入一些记录，这些记录要求符合以下条件：没有上过编号"003"课程的同学学号、"002"号课的平均成绩；
    Insert SC
    Select Sno, '002', (select avg(Score) from SC where Cno='002')
    From Student
    Where Sno not in (select Sno from SC where Cno='003');
    11、查询没学过"叶平"老师课的同学的学号、姓名
    Select Student.Sno, Student.Sname
    From Student
    Where Sno not in (Select distinct SC.Sno
    From SC,Course,Teacher
    Where SC.Cno=Course.Cno
    and Teacher.Tno=Course.Tno
    and Teacher.Tname='叶平');
    12、查询学过"叶平"老师所教的所有课的同学的学号、姓名；
    Select Sno,Sname
    From Student
    Where Sno in (select Sno from SC,Course,Teacher
    where SC.Cno=Course.Cno
    And Teacher.Tno=Course.Tno
    and Teacher.Tname='叶平' 
    group by Sno
    having Count(SC.Cno)=(select count(Cno) From Course,Teacher
    Where Teacher.Tno=Course.Tno
    and Tname='叶平') );
    13、查询选修"叶平"老师所授课程的学生中，成绩最高的学生姓名及其成绩
    Select Student.Sname,SC.Score
    From Student,SC,Course C,Teacher
    Where Student.Sno=SC.Sno and SC.Cno=C.Cno 
    and C.Tno=Teacher.Tno 
    and Teacher.Tname='叶平' 
    and SC.score=(select max(score) from SC 
    where Cno=C.Cno);