8. 多表查询（连接）

/*
在实际查询中，很多情况下用户需要的数据并不全在一个表中，而是存在于多个不同的表，此时就要使用多表查询，多表查询是通过各个表之间的共同列的相关性来查询数据的，多表查询要在这些表中建立连接，再在连接生成的结果基础上进行筛选。
在进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所以各个表结构之间也存在着各种联系，基本上分为三种：
一对多(多对一)        班级 与 学生的关系
多对多                学生 与 课程的关系
一对一                学生 与 学生详情的关系

*/
/*
笛卡尔乘积现象：表1 有m行，表2有n行，结果=m*n行

发生原因：没有有效的连接条件
如何避免：添加有效的连接条件
*/

sql

SELECT * from teacher,course; -- 错误的写法
SELECT * from teacher,course WHERE teacher.tno=course.tno; -- 正确的写法

#1、等值连接
/*

① 多表等值连接的结果为多表的交集部分
② n表连接，至少需要n-1个连接条件
③ 多表的顺序没有要求
④ 一般需要为表起别名
⑤ 可以搭配前面介绍的所有子句使用，比如排序、分组、筛选
*/
-- 列出所有老师任教的课程

sql

SELECT * from teacher,course WHERE teacher.tno=course.tno ORDER BY cno;

-- 列出各位老师任教的课程成绩

sql

SELECT tname,cname,score from teacher,course,sc where teacher.tno=course.tno and course.cno=sc.cno;

-- 统计各位老师任教的学生人数

sql

SELECT tname,count(*) 学生人数 from teacher,course,sc where teacher.tno=course.tno and course.cno=sc.cno GROUP BY tname;

-- 列出李老师任教的学生的姓名，成绩；

sql

select tname,sname,score from teacher,course,sc,student where teacher.tno=course.tno and course.cno=sc.cno and sc.sno=student.sno and teacher.tname="李老师";

-- 求李老师任课的课程平均成绩

sql

SELECT tname,avg(score) as "平均成绩" from teacher,course,sc where teacher.tno=course.tno and course.cno=sc.cno and tname="李老师";

-- 2、非等值连接

-- 查询所有学生各科的等级

sql

SELECT sc.sno,student.sname,course.cname,grades.grade_level 
from sc,grades,student,course
WHERE sc.sno=student.sno and course.cno=sc.cno and sc.score BETWEEN grades.lowest_sc and grades.highest_sc;

/*
多表查询语法格式如下:
SELECT [表名.] 目标字段表达式 [AS别名],...
FROM 左表名 [AS别名] 连接类型 右表名 [AS别名]
ON 连接条件
[WHERE 条件表达式];
其中，连接类型以及运算符共有5种。

（1)CROSS JOIN:交叉连接 笛卡尔积。
（2)INNER JOIN或JOIN:内连接。
（3)LEFT JOIN或LEFT OUTER JOIN:左外连接。
（4)RIGHT JOIN或RIGHT OUTER JOIN:右外连接。
（5)FULL JOIN或FULL OUTER JOIN:完全连接。

*/
/*
2）内连接
内连接是指用比较运算符设置连接条件，只返回满足连接条件的数据行，是将交叉连接生成的结果集按照连接条件进行筛选后形成的。内连接有以下两种语法格式：
SELECT 字段名列表
FROM 表名1 [INNER] JOIN 表名2
ON 表名1.字段名 比较运算符 表名2.字段名；
或者
SELECT 字段名列表
FROM 表名1，表名2
WHERE 表名1.字段名 比较运算符 表名2.字段名；

*/
--
-- 列出所有老师任教的课程

sql

SELECT * from teacher 
inner join course on teacher.tno=course.tno 
ORDER BY cno;

-- 统计各位老师任教的学生人数

sql

select tname,count(*) 学生人数 
from teacher 
INNER JOIN course on teacher.tno=course.tno
inner join sc on course.cno=sc.cno 
group by tname;
SELECT tname,count(*) 学生人数 from teacher,course,sc where teacher.tno=course.tno and course.cno=sc.cno GROUP BY tname;

-- 列出李老师任教的学生的姓名，成绩；

sql

select teacher.tname,student.sname,sc.score from teacher 
inner join course on teacher.tno = course.tno 
inner join sc on course.cno = sc.cno 
inner join student on sc.sno = student.sno 
where teacher.tname='李老师';
select tname,sname,score from teacher,course,sc,student where teacher.tno=course.tno and course.cno=sc.cno and sc.sno=student.sno and teacher.tname="李老师";

-- 求李老师任课的课程平均成绩(保留两位小数)

sql

select tname,round(avg(score),2) 平均成绩 from teacher 
inner join course on teacher.tno = course.tno 
inner join sc on course.cno=sc.cno 
where teacher.tname='李老师';
SELECT tname,avg(score) as "平均成绩" from teacher,course,sc where teacher.tno=course.tno and course.cno=sc.cno and tname="李老师";

/*
3）外连接
外连接与内连接不同，有主从表之分。使用外连接时，以主表中每行数据去匹配从表中的数据行，如果符合连接条件则返回到结果集中;如果没有找到匹配的数据行，则在结果集中仍然保留主表的数据行，相对应的从表中的字段则被填上NULL值。
外连接的语法格式如下:
SELECT 字段名列表
FROM 表名1 LEFT|RIGHT JOIN 表名2
ON 表名1.字段名 比较运算符 表名2.字段名；

外连接可以分为左外连接、右外连接和全外连接三种类型。
（1)左外连接(Left  Join):
返回左表中的所有记录，以及右表中与左表匹配的记录。如果右表中没有与左表匹配的记录，则右表对应部分为NULL。左外连接使用关键字“LEFT JOIN”来表示。
（2)右外连接(Right Join):
返回右表中的所有记录,以及左表中与右表匹配的记录。如果左表中没有与右表匹配的记录,则左表对应部分为NULL。右外连接使用关键字“RIGHT JOIN”来表示。
（3)全外连接(Full Join):MySQL中无此连接，只能使用union 或union all 将两个联接合并
返回两个表中的所有记录,无论是否满足连接条件。如果某一表中没有与另一表匹配的记录,则该表中的对应部分为NULL。全外连接使用关键字“FULL JOIN”来表示。

*/
-- 找出所有教师的任课情况(包含所有课程，不包含未任课的教师)

sql

SELECT tname,cname from teacher right join course on teacher.tno=course.tno;
select tname,cname from course left join teacher on teacher.tno=course.tno;

-- 不包含未安排任课教师的课程，包含未任课的教师

sql

SELECT tname,cname from teacher LEFT JOIN course on teacher.tno=course.tno;
select tname,cname from course right join teacher on teacher.tno=course.tno;

-- 既不包含未安排任课教师的课程，也不包含未任课的教师

sql

select tname,cname from teacher join course on teacher.tno=course.tno;

-- 既包含未安排任课教师的课程，也包含未任课的教师

sql

SELECT tname,cname from teacher right join course on teacher.tno=course.tno 
union 
SELECT tname,cname from teacher LEFT JOIN course on teacher.tno=course.tno;

-- 将未安排课程的教师，课程显示为未排课

sql

SELECT tname,IFNULL(cname,'未排课') 课程 
from teacher left join course
on teacher.tno=course.tno;

/*
4）自连接 与 子查询
自连接（Self-Join)是指在一个表中进行连接操作，将该表与自身进行关联。自连接常用于对表中的数据进行比较和分析，特别是当表中的某些列关联到同一个表中的其他记录时。
在自连接中，我们需要使用别名来区分被连接的两个表，因为自连接就是一个表的两个副本之间的内连接，即同一个表名在FROM子句中出现两次，必须对表指定不同的别名，字段名前也要加上表的别名进行限定。

*/
-- 查找和赵雷同班级且同籍贯的学生对，显示学生的学号，姓名，籍贯，班级

sql

SELECT a.sno,a.sname,a.sbirthplace,a.class,b.sno,b.sname,b.sbirthplace,b.class 
from student a join student b 
on a.sbirthplace=b.sbirthplace and a.class=b.class 
where a.sname="赵雷" and a.sno<>b.sno;

-- 找出年龄大于所在班级平均年龄的学生

sql

SELECT 
    s.sno, s.sname, s.sage, sub.avg_age
FROM 
    student s
JOIN 
    (
        SELECT class, AVG(sage) AS avg_age
        FROM student
        GROUP BY class
    ) sub ON s.class = sub.class
WHERE 
    s.sage > sub.avg_age;

-- 写出查询，所有学生的全部科目成绩，没有参加考试的科目也要显示出来

sql

SELECT 
    s1.Sno, s1.Sname,s1.Cno,s1.Cname,sc.score
FROM 
    (SELECT student.Sno,student.Sname,course.Cno,course.Cname
        FROM 
            student  CROSS JOIN course ) s1
LEFT JOIN 
    sc ON s1.Sno = sc.Sno AND s1.Cno = sc.Cno;

/*
5）UNION 和 UNION ALL  用于合并多个 SELECT 语句的结果集。
UNION 操作符用于合并两个或多个 SELECT 语句的结果集。它会去除合并结果集中的重复行。
要求参与 UNION 的各个 SELECT  语句的列数必须相同，并且对应列的数据类型也必须兼容（不一定完全相同，但能隐式转换）。

UNION ALL 同样用于合并多个 SELECT 语句的结果集，但它不会去除重复行。即使两个结果集中有完全相同的行，也会全部保留。
与 UNION 一样，参与 UNION ALL 的各个 SELECT 语句的列数必须相同，并且对应列的数据类型也必须兼容。
其他注意事项
1、排序：
如果要对合并后的结果集进行排序，只能在最后一个 SELECT 语句后使用 ORDER BY 子句，并且 ORDER BY 子句中的列必须是第一个 SELECT 语句中指定的列名或列的位置。
2、列别名：
可以在第一个 SELECT 语句中给列指定别名，后续 SELECT 语句中的列名会自动与第一个 SELECT 语句的列名相对应。
*/
-- 不使用分组，统计各班的人数

sql

select class 班级,count(*) 人数 from student where class="01"
UNION
select class 班级,count(*) 人数 from student where class="02"
UNION
select class 班级,count(*) 人数 from student where class="03";

# 行列转换 需要使用流程控制函数
-- 流程控制函数
-- if

sql

select if(FALSE, 'Ok', 'Error');

-- ifnull

sql

select ifnull('Ok','Default');
select ifnull('','Default');
select ifnull(null,'Default');

-- case when then else end
# 1. 等值判断

sql

CASE 表达式
    WHEN 值1 THEN 结果1
    WHEN 值2 THEN 结果2
    ...
    [ELSE 默认结果]
END

-- 逻辑：将 表达式 依次与 值1、值2 等比较，匹配成功则返回对应 结果；无匹配时返回 ELSE 结果（若省略 ELSE，返回 NULL）
# 2. 条件判断

sql

CASE
    WHEN 条件1 THEN 结果1
    WHEN 条件2 THEN 结果2
    ...
    [ELSE 默认结果]
END

-- 逻辑：依次判断 条件1、条件2 等，第一个满足的条件返回对应 结果；无匹配时返回 ELSE 结果（省略则返回 NULL）。
-- 案例1: 查询student表中的学生姓名和籍贯 (北京/上海 ----> 一线城市 , 其他 ----> 二线城市)

sql

select     sname,sbirthplace,
    ( case sbirthplace 
		when '北京' then '一线城市' 
		when '上海' then '一线城市' 
		else '二线城市' end ) as '籍贯'
from student;

-- 案例2: 统计班级各个学生的成绩，展示的规则如下：
-- >= 85，展示优秀
-- >= 60，展示及格
-- 否则，展示不及格

sql

select
    sno,score,
    (case 
		when score >= 85 then '优秀' 
		when score>=75 then '良好'
		when score >=60 then '及格' 
		else '不及格' end ) '评语'
from sc;

-- 查询各班男女生的人数和总人数

sql

SELECT class,ssex,count(*) 人数 from student GROUP BY class,ssex;

-- 这样查出来，表的数据是按行显示的，总人数也不好放入表格中

-- 每班一行显示
/*
班级  男生人数  女生人数 总人数
01          7         1       8
02          2         2       4
03          4         4       8
*/

sql

SELECT class 班级, 
sum(CASE 
	WHEN ssex="男" THEN		1
	ELSE		0
END
) 男生人数,
sum(CASE 
	WHEN ssex="女" THEN	1
	ELSE 0
END
) 女生人数,
count(*) 总人数
from student 
GROUP BY class;

-- 按班级统计各科的平均成绩
-- 有多个班级行，科目在一列上

sql

SELECT class,cname,round(avg(score),2) 平均分 
from student join sc
on student.Sno=sc.Sno
join course
on sc.Cno=course.Cno 
GROUP BY class,cname;

-- 一个班级一行，各科分列显示，平均分是具体的数据

sql

SELECT class,
round(avg(case when cname='语文' 
then score end),2) as 语文平均分,
round(avg(case when cname='数学' 
then score end),2) as 数学平均分, 
round(avg(case when cname='英语' 
then score end),2) as 英语平均分
from student left join sc
on student.Sno=sc.Sno
join course
on sc.Cno=course.Cno 
GROUP BY class;

←

上一节 7. MySQL常用函数

下一节 9. 子查询（嵌套查询）

→