第二周第五天分组&嵌套查询&多表关联_

---

分组

思路

group by 可以认为是把一张表中的数据，按照分组字段的值，划分成了多个不同的子表

例如

对于下表 我们可以对cno字段分组统计
select cno,count(*) from 表名 group by cno;

原表

image.png

根据cno字段的值拆分成了4张子表

image.png

使用count（*）对每张子表中的数据进行统计

image.png

关键字

group by

格式

select * from 表名 group by 分组字段 having 过滤条件;

练习

-- 统计出每个学生的面试次数
-- 统计出每个学生的面试次数并按面试次数从大到小的顺序排序
-- 找出最大的面试次数

嵌套查询

• 嵌套字句只有一个值

格式

select 展示字段名1,展示字段名2 ... from 表名 where 字段名=(select 展示字段名1from 表名)
或者
select 展示字段名1,展示字段名2 ... from 表名 where 字段名 in(select 展示字段名1from 表名)

• 嵌套字句有多个值

格式

select 展示字段名1,展示字段名2 ... from 表名 where 字段名 in(select 展示字段名1from 表名)

练习题

-- 找出面试最多的学生

多表关联

关联方法

• where
• join

区别

where:

第一步：拿两表数据做笛卡尔乘积生成一张中间表;
第二步：根据where条件进行筛选；
缺点：若量表数据比较庞大的时候，生成的中间表数据量太大，会影响数据库的性能

image.png

练习

-- 查询选修计算机导论的学生人数

-- 查询选修计算机导论和高等数学的所有学生信息

-- 查询成绩不及格的学生姓名

画完ER图后再做一遍

image.png

join

第一步：先用on条件进行数据筛选
第二步：满足条件的显示，不满足条件的丢弃

表数据

image.png

内连接

先使用on条件过滤，只显示满足条件的数据，不满足条件的跳过
关键字： inner join .. on ..或者 join.. on ..
格式：
select * from 表1 inner join 表2 on 连接条件;
或者
select * from 表1 join 表2 on 连接条件;
例子
select * from teacher a inner join student b where a.tid = b.tid;
或者
select * from teacher a join student b where a.tid = b.tid;

image.png

左连接

以left join左边的表为主表，左表数据全显示，右表的数据只显示满足条件的，不满足条件的则以null代替
关键字：left join .. on ..
格式：
select * from 表1 left join 表2 on 连接条件;

例子：
select * from teacher a left join student b where a.tid = b.tid;

image.png

右连接

以right join右边的表为主表，右表的数据全部显示，左表的数据只显示满足条件的，不满足条件的则以NULL代替

关键字：right join .. on ..
格式：
select * from 表1 right join 表2 on 连接条件;

例子：
select * from teacher a right join student b where a.tid = b.tid;

image.png

全连接

两张表都为主表，不满足条件的以null代替

关键字：full join .. on ..
格式：
select * from 表1 full join 表2 on 连接条件;

例子：
select * from teacher a fulljoin student b where a.tid = b.tid;

image.png

课后练习

表结构：

image.png

1、 查询Student表中的所有记录的Sname、Ssex和Class列
2、 查询教师所有的单位即不重复的Depart列。
3、 查询Student表的所有记录。
4、 查询Score表中成绩在60到80之间的所有记录。
5、 查询Score表中成绩为85，86或88的记录。
6、 查询Student表中“95031”班或性别为“女”的同学记录。
7、 以Class降序查询Student表的所有记录。
8、 以Cno升序、Degree降序查询Score表的所有记录。
9、 查询“95031”班的学生人数。
10、查询Score表中的最高分的学生学号和课程号。（有难度）
11、查询每门课的平均成绩。
12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。（有难度）
13、查询分数大于70，小于90的Sno列。
14、查询所有学生的Sname、Cno和Degree列。（多表关联）
15、查询所有学生的Sno、Cname和Degree列。（多表关联）
16、查询所有学生的Sname、Cname和Degree列。（多表关联）
17、查询“95033”班学生的平均分。
18、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。（有难度）
19、查询选修某课程的同学人数多于5人的教师姓名。（有难度）（多表关联）
20、查询95033班和95031班全体学生的记录。
21、查询存在有85分以上成绩的课程Cno.
22、查询出“计算机系“教师所教课程的成绩表。（有难度）（多表关联）
23、查询至少有2名男生的班号。
24、查询Student表中不姓“王”的同学记录。
25、以班号和年龄从大到小的顺序查询Student表中的全部记录。
26、查询“男”教师及其所上的课程。（多表关联）