MySQL 07章_子查询

子查询就是查询中还可以嵌套其他的查询，通常是内层查询的结果作为外层查询的条件来使用
    执行循序，自内向外依次执行
        一、内层查询返回“单列单行”的结果
                -- 1、查询宋江的出生日期
                SELECT TIMESTAMPDIFF(year,birthday,NOW()) FROM tb_student WHERE student_name = "宋江";
                -- 2、再使用宋江的出生日期作为条件执行查询
                SELECT * FROM tb_student WHERE TIMESTAMPDIFF(year,birthday,NOW()) <(
                SELECT TIMESTAMPDIFF(year,birthday,NOW()) FROM tb_student WHERE student_name = "宋江" 
                );

        二、内层查询返回“单列多行”的结果
               需要使用IN、NOT IN、ANY、ALL关键字在外层查询中进行比较，执行外层查询
              -- 子查询中使用IN关键字：外层查询的字段值是否包含在内层查询返回的结果中，若是返回true，反之返回false。
              -- 查询java、HTML的成绩信息
              SELECT sj.subject_name,sc.* FROM tb_score sc INNER JOIN tb_subject sj 
              WHERE sc.subject_id =sj.subject_id 
              AND sj.subject_name IN("java","html");

              -- 使用subject_name作为条件查询对应的subject_id
              SELECT subject_id FROM tb_subject WHERE subject_name IN("java","html");
              -- 再使用subject_id作为外层查询的条件查询成绩信息
              SELECT sj.subject_name,sc.* FROM tb_score sc,tb_subject sj 
              WHERE sc.subject_id = sj.subject_id AND sc.subject_id IN(
              SELECT subject_id FROM tb_subject WHERE subject_name IN("java","html")
              );

              -- NOT IN
              -- 查询java和HTML以外 的其他课程的成绩信息
              SELECT sj.subject_name,sc.* FROM tb_score sc,tb_subject sj 
              WHERE sc.subject_id = sj.subject_id AND sc.subject_id  IN(
              SELECT subject_id FROM tb_subject WHERE subject_name NOT IN("java","html")
              ); 

              -- 查询比JavaScript中所有成绩都高的其他课程的成绩信息
              -- 1、查询JavaScript课程的subject_id
              SELECT subject_id FROM tb_subject WHERE subject_name = "javascript";

              -- 2、使用subject_id作为条件查询对应的所有成绩
              SELECT student_score FROM tb_score WHERE subject_id IN(
                SELECT subject_id FROM tb_subject WHERE subject_name = "javascript"
              );

              -- 3、再使用JavaScript课程的所有成绩与外层的成绩进行比较
              SELECT * FROM tb_score WHERE student_score > ALL(
                SELECT student_score FROM tb_score WHERE subject_id IN(
                  SELECT subject_id FROM tb_subject WHERE subject_name = "javascript"
                )
              );

              SELECT * FROM tb_score WHERE student_score > ANY(
                SELECT student_score FROM tb_score WHERE subject_id IN(
                  SELECT subject_id FROM tb_subject WHERE subject_name = "javascript"
                )
              )ORDER BY student_score;
              
        三、内层查询返回“多列单行”的结果
              多列的结果要与外层查询对应的多个字段同时进行比较，才认为是满足条件
              -- 针对tb_emp表查询与史密斯的部门编号和岗位都相同的其他人员信息
              SELECT DEPTNO ,JOB FROM tb_emp WHERE ENAME = "smith";
                  以下两种方法都可以
              SELECT * FROM tb_emp WHERE( DEPTNO =(
                SELECT DEPTNO FROM tb_emp WHERE ENAME = "smith") AND JOB=(
                SELECT JOB FROM tb_emp WHERE ENAME = "smith")AND ENAME <> "smith"
              );

              SELECT * FROM tb_emp WHERE (DEPTNO,JOB) = (SELECT DEPTNO,JOB FROM tb_emp WHERE ENAME = "smith") AND ENAME <> "smith";
        四、内层查询返回“多列多行”的结果
            当内层查询返回的结果是“多列多行”或以下范围的值时，外层查询可以使用exists关键字判断内层查询是否或（没有）数据返回。
        使用EXISTS关键字：内层查询若有数据返回则为true，否则false
              SELECT ge.grade_name,st.* FROM tb_student st,tb_grade ge 
              WHERE EXISTS (
                SELECT * FROM tb_grade WHERE grade_name IN ('二年级','三年级')) AND 
                  st.grade_id = ge.grade_id AND ge.grade_name IN ('二年级','三年级'
            );

        子查询中使用NOT EXISTS关键字：与exists相反