数据库基础学习四 一、子查询 1、带比较运算符的子查询（与子查询单一结果值进行比较） 2、带in关键字的子查询 3、带exists关键字的子查询 二、连接查询 1、内连接（inner join） 2

目标

• 数据的高级查询操作（select）
  - 子查询
  - 连接查询
  - 分组查询

• 子查询是指一个查询语句嵌套在另一个查询语句内部的查询
• 子查询（内层）结果作为外层SQL操作的过滤条件
• 子查询可以添加到select、update、delete语句中
• 子查询可进行多次嵌套
• 子查询类型
  -带比较运算符的子查询
  - 带in关键字的子查询
  - 带exists关键字的子查询
  - 带any/some关键字的子查询
  -带all关键字的子查询

一、子查询

1、带比较运算符的子查询（与子查询单一结果值进行比较）

？问题：在选修记录信息表中，查询考试成绩比所有考试成绩平均分高的记录信息

 select  *  from  eLogs  where  elScore>AVG(elScore);

#上边语句是错误的！！！（聚合函数不能直接出现在where子句中)

！分析：
第一步：统计所有成绩的平均成绩

select  avg(elScore)  from  eLogs;

第二步：再查询考试成绩分数大于平均分（78.57142857142857）的记录信息

故，将上边两条语句整合到一条SQL语句，如下

select  *  from  eLogs  where  elScore>(select  avg(elScore)  from  eLogs);

提醒：子查询字段与外层where对应字段须保持一致！！！

另附所有选修记录信息，以便验证确认！

2、带in关键字的子查询

如果子查询将返回多个数据行，可以用in和not in操作符来构造外层查询的检索条件。

?问题：查询登记过选修课程学习的学生姓名
！分析：

• 题干涉及内容包含两个表：选修记录信息表（eLogs）、学生信息表（Students）
• 从表结构设计知：选修记录信息表与学生信息表存在外键约束（两表都存储着学生学号）
• 查询的内容为学生姓名（学生信息表中的字段sName）
• 查询的条件为登记过选修记录的学生（即学生学号在选修记录信息表中的字段sID中出现过，则代表该学生有选修记录的登记）

则初步预判此题可使用连接技术的内连接完成，如下图，得到登记过选修课程学习的学生有两位。

另附所有选修记录、学生记录信息，以便验证确认！

选修记录信息

从上边两个结果集对照发现：有三位学生登记过选修记录，那么该结论则与上边内连接技术实现结果出现差异！发现原因是内连接技术实现该题为了避免有多次选修登记的学生姓名重复出现，而使用了distinct消除重复行；但是在一个学校出现学生姓名重复是很正常的事情。所以本题建议使用子查询完成，在选修记录信息表中查询学生学号的时候就进行去重（学号是学生信息表的主键，能唯一区分学生信息，不用担心重复问题），并将查询的结果集作为外层查询的检索条件；因子查询的结果集可能为多个数据记录，则此题使用带in关键字的子查询。

select sName from Students where sNo in(select distinct sID from eLogs);

提醒：子查询字段与外层where对应字段须保持一致！！！

3、带exists关键字的子查询

• exists可以检测数据是否存在。如果子查询的结果非空，则exists(子查询)将返回真(true/1),否则返回假(false/0)。
• 查看课程信息表中数据信息，发现有书籍的描述信息（cDescription）是空值
  
  执行：select exists(select * from Courses where cDescription is null);后效果如下图（exists返回值为true/1）

故，执行select * from Teachers where exists(select * from Courses where cDescription is null);则可看到所有教师的信息

另补充：

#创建指定名称的数据库时先判断其是否存在，如果不存在则创建（存在则不创建）
create  database  if  not exists  electivesdb;

#创建指定名称的数据表时先判断其是否存在，如果不存在则创建（存在则不创建）
create  table  if  not  exists  Test(
    a  int,
    b  carchar(20)
);

提醒：带any/some关键字的子查询、带all关键字的子查询自行搜集资料学习。

二、连接查询

连接查询是关系数据库中重要的查询技术，也是项目实际中常常使用的；本次主要讲解内连接；演示外连接（左外连接/左连接、右外连接/右连接）、交叉连接。
_{连接技术：针对多表操作，将多个表的结构看成是一个表的结构}

1、内连接（inner join）

内连接：多表同时符合条件的组合
内连接的语法：select 字段列表 from 主表 inner join 从表 on 主键字段=外键字段 [where 条件表达式];

？问题：查询老张老师所授课程的名称
！分析：

• 题干涉及内容包含两个表：教师信息表（Teachers）、课程信息表（Courses）
• 从表结构设计知：教师信息表与课程信息表存在外键约束（两表都存储着教师编号）
• 查询的内容为课程名称（课程信息表中的字段cName）
• 查询的条件为老张老师所授课程（即老张老师的教师编号在课程信息表中字段cID中出现过）

select cName from Teachers inner join Courses on tNo=tID where tName=‘老张’;

说明老张老师上了两门课程。另附课程信息表、教师信息表记录数据，以便验证！

<sub>课程信息表信息

教师信息表信息</sub>
演示：三表连接且对表进行重命名操作（以便表中字段重名时区分，设置字段时采用“表名.字段名”方式）

select * from Students as s inner join eLogs as e on s.sNo=e.sID inner join Courses as c on c.cNo=e.cID;

2、外连接（outer join）

• 左外连接/左连接（left outer join/left join）：获取左表所有记录，即使右表没有对应匹配的记录。
• 右外连接/右连接（right outer join/right join）: 与左连接相反，用于获取右表所有记录，即使左表没有对应匹配的记录。
• 左连接、右连接的区别实质就是以谁作为基表查看数据，这个左与右是指外连接关键字的左与右

执行：select * from Teachers left join courses on tNo=tID;

另通过上边的语句执行结果，我们可以很清楚知悉：哪些老师还未安排授课，也可执行：
select tName from Teachers left join courses on tNo=tID where cNo is null;
演示：右外连接（比较与上边SQL执行的区别）

另通过上边的语句执行结果，我们可以很清楚知悉：哪些课程还未安排老师，也可执行：
select cNo,cName from Teachers right join courses on tNo=tID where tNo is null;

三、分组查询

~聚合函数只能返回一个单一的汇总数据，而使用GROUP BY子句，则可以生成分组的汇总数据。~

语法：

SELECT [ALL | DISTINCT]  select_list
FROM table_list
[WHERE conditions]
[GROUP BY group_list]
[HAVING conditions]
[ORDER BY order_list]

？问题：统计在选修记录信息表中，已登记同学每人选修课程门数
！分析：

• 根据题意，则将每位同学的学号作为分组标识，这样就可以每位学生为个体统计其登记选修课程的门数
• 参照分组查询的语法得：
  select COUNT(cID) from eLogs group by sID;

因前面的截图信息我们知道，目前表中共有三位同学进行了选修登记，则本次查询的结果应有三个统计值；但是如此我们不知道是哪位同学已选择了五门，哪两位同学选择了两门，则更改下之前的语句

再这个结果集基础上，我们还可借助group_concat()函数呈现具体选择课程的编号，如下SQL语句。

select sID,COUNT(cID),GROUP_CONCAT(cID) from eLogs group by sID;

再上边查询基础上，我们再引入连接技术，语句如下：

select sName,COUNT(cID),GROUP_CONCAT(cName) from eLogs inner join Students on sNo=sID inner join Courses on cNo=cID group by sID;

可在结果集内看到具体的学生姓名、已选修课程门数及具体已选修课程名称列表。

另编写一条SQL语句：

select  sID,COUNT(cID) from elogs where elDate between '2018-01-01' and '2019-04-04'  group by sID having COUNT(cID)=1 order by sID desc;