MySQL多表数据记录查询（二）
（1）子查询概念
子查询也称嵌套查询，就是指一个查询之中嵌套了其他的若干查询，即在一个select语句中的where或者from字句中包含一个select查询语句。在查询语句中，外层select查询语句称为主查询，where字句中的select查询语句被称为子查询。
（2）联合查询概念
使用union关键字可以把来自多个select语句的结果组合到一个结果集中，这种查询方式称为并(union)运算或联合查询。合并时，多个select字句中对应的字段数和数据类型必须相同。
1.1、带in关键字的子查询：
带in关键字的子查询是最常用的一类子查询，1)用于判定一个给定值是否存在于子查询的结果集中。2)使用in关键字进行子查询时，内层查询语句仅仅返回一个数据列，其值将提供给外层查询进行比较操作；
1：查询选修了课程的学生姓名；(in查询)
select a.studentName -> from tb_student as a -> where a.studentNo in -> (select distinct tb_score.studentNo from tb_score); 先查内层： select distinct tb_score.studentNo from tb_score;
再外层查询；
练习：
例12:P96
查询选修了"计算机基础"和"管理学"的学生学号;
select tb_score.studentNo from tb_score,tb_course where tb_score.courseNo=tb_course.courseNo and tb_course.courseName="计算机基础" and tb_score.studentNo in (select studentNo from tb_score,tb_course where tb_score.courseNo=tb_course.courseNo and tb_course.courseName="管理学");
2:P89(not in查询)
查询没有选修过课程的学生学号和学生姓名；
select a.studentNo,a.studentName -> from tb_student as a -> where a.studentNo not in -> (select distinct tb_score.studentNo from tb_score);
结果：林海
练习;
查询选修了"计算机基础"但是没有选修"管理学"的学生学号；
select tb_score.studentNo from tb_score,tb_course where tb_score.courseNo=tb_course.courseNo and tb_course.courseName="计算机基础" and tb_score.studentNo not in (select studentNo from tb_score,tb_course where tb_score.courseNo=tb_course.courseNo and tb_course.courseName="管理学");
1.2、带比较运算符的子查询：
带比较运算符的子查询是指1)父查询与子查询之间用比较运算符进行连接。2)当用户能确切知道内层返回的是单值时，3)可以用<,<=,>,>=,=,<>,!=等比较运算符构造子查询；
3:查询班级"计算机14-1班"所有学生的学号、姓名；
select a.studentNo,a.studentName -> from tb_student as a -> where a.classNo= -> (select tb_class.ClassNo from tb_class where ClassName="计算机14-1班"); 先进行内层查询： select tb_class.ClassNo from tb_class where ClassName="计算机14-1班";
结果：CS1401
再外层查询：
select a.studentNo,a.studentName -> from tb_student as a -> where a.classNo="CS1401";
思考：
使用连接查询怎么查？
select a.studentNo,a.studentName -> from tb_student as a,tb_class as b -> where a.classNo=b.ClassNo -> and b.ClassName="计算机14-1班";
4:查询与"李明"在同一个班学习的学生学号、姓名、班号；(tb_student)
select a.studentNo,a.studentName,a.classNo -> from tb_student as a -> where a.classNo= -> (select b.classNo from tb_student as b where b.studentName="李明") and a.studentName!="李明";
思考：该查询也是自连接查询，连接语句怎么写？
select a.studentNo,a.studentName,a.classNo -> from tb_student as a,tb_student as b -> where a.classNo=b.classNo -> and b.studentName="李明" and a.studentName!="李明";
any比较：
例5:P91,//any比较
查询男生中比某个女生出生晚的学生姓名和出生年份：
select a.studentName,year(birthday) -> from tb_student as a -> where a.sex="男" and year(birthday)>any -> (select year(birthday) from tb_student where sex="女");
用聚合函数语句：min
select a.studentName,year(birthday) -> from tb_student as a -> where a.sex="男" and year(birthday)> -> (select min(year(birthday)) from tb_student where sex="女" );
6:,//all比较：
查询男生中比所有女生出生晚的学生姓名和出生年份：
select a.studentName,year(a.birthday) -> from tb_student as a -> where a.sex="男" and year(a.birthday)>all -> (select year(birthday) from tb_student where sex="女");
用聚合函数写：max
select a.studentName,year(birthday) -> from tb_student as a -> where a.sex="男" and year(birthday)> -> (select max(year(birthday)) from tb_student where sex="女" );
1.3、带exists关键字的子查询：
使用关键字exists构造子查询时，1)系统对子查询进行运算判断它是否返回结果集。2)由于带exists的子查询只返回true或false，内层查询的select子句给出的字段名没有实际意义，所以目标列表达式通常用星号*。
7:P92,关键字exists查询
查询选修了课程号为"31002"的学生姓名；
select a.studentName -> from tb_student as a -> where exists -> (select * from tb_score as b where a.studentNo=b.studentNo and b.courseNo="31002");
用in查询：
select a.studentName -> from tb_student as a -> where a.studentNo in -> (select studentNo from tb_score where courseNo="31002");
8:P93,//关键字not exists查询
查询没有选修课程号为"31002"的学生姓名；
select a.studentName -> from tb_student as a -> where not exists -> (select * from tb_score as b where a.studentNo=b.studentNo and b.courseNo="31002");
用in查询：
select a.studentName -> from tb_student as a -> where a.studentNo not in -> (select studentNo from tb_score where courseNo="31002");
9：exists成对使用，双重否定表示肯定
查询选修了全部课程的学生姓名；
思路：
查询这样一个学生，没有一门课是他没有选修的；
select a.studentName -> from tb_student as a -> where not exists -> (select * from tb_course as b -> where not exists -> (select * from tb_score as c -> where c.courseNo=b.courseNo and a.studentNo=c.studentNo));
结果：没有这样的学生；empty set；
看一共有几门课：(以课程分组)
select tb_score.courseNo,count(*) -> from tb_score -> group by courseNo; +----------+----------+ | courseNo | count(*) | +----------+----------+ | 11003 | 4 | | 21001 | 6 | | 21002 | 2 | | 21004 | 6 | | 31002 | 2 | | 44444 | 1 | +----------+----------+ 6 rows in set (0.06 sec) select tb_score.studentNo,count(courseNo) -> from tb_score -> group by studentNo; +------------+-----------------+ | studentNo | count(courseNo) | +------------+-----------------+ | 2013110101 | 2 | | 2013110103 | 2 | | 2013110201 | 2 | | 2013110202 | 2 | | 2013310101 | 2 | | 2013310103 | 2 | | 2014210101 | 2 | | 2014210102 | 2 | | 2014310101 | 2 | | 2014310102 | 2 | +------------+-----------------+ 10 rows in set (0.00 sec)
联合查询：
1)使用union关键字可以把多个来自select语句的结果组合到一个结果集中，这种查询方式称为并(union)运算或联合查询。
2)合并时，多个select字句中对应的字段数和数据类型必须相同。
3)语法：
Select-from-where Union[all] Select-from-where [...union[all]] Select-from-where Union查询：
10:union用法,unoin去重了；
使用union查询选修了"管理学"或"计算机基础"的学生学号；
select tb_score.studentNo -> from tb_score,tb_course -> where tb_score.courseNo=tb_course.courseNo and tb_course.courseName="计 算机基础" -> union -> select tb_score.studentNo -> from tb_score,tb_course -> where tb_score.courseNo=tb_course.courseNo and tb_course.courseName="管 理学";
用连接语句查询：使用distinct去重
select distinct tb_score.studentNo -> from tb_score,tb_course -> where tb_score.courseNo=tb_course.courseNo -> and (tb_course.courseName="管理学" or tb_course.courseName="计算机基础");
Union all查询：
11:union all
使用union all查询选修了"管理学"或"计算机基础"的学生学号；
select tb_score.studentNo -> from tb_score,tb_course -> where tb_score.courseNo=tb_course.courseNo and tb_course.courseName="计 算机基础" -> union all -> select tb_score.studentNo -> from tb_score,tb_course -> where tb_score.courseNo=tb_course.courseNo and tb_course.courseName="管 理学";
用连接语句查询://不用distinct去重；
select tb_score.studentNo -> from tb_score,tb_course -> where tb_score.courseNo=tb_course.courseNo -> and (tb_course.courseName="管理学" or tb_course.courseName="计算机基础");
