sql减法语句_SQL入门之多表查询

表的运算

1、表的加法（UNION），表的减法（EXCEPT），表的交集（INTERSECT）；

2、表运算的注意事项：作为运算对象的表的列数必须相同；类型必须相同；可以使用任何select子句，但是order by只能在最后一次使用；默认结果不包含重复行，但是如果想保留重复行需要在后面加入ALL关键字。

联结

简单来说，联结就是将其他表中的列添加过来。一般我们期望得到的数据都是分散在多张表的，使用联结就可以从多张表中获取数据了。

下面使用teacher表和course表来说明以下几种联结方式：

1、交叉联结，又叫笛卡尔积，意义不大，使用场景极为少见。

2、Left join（左联结）A left join B表示从A表中取出完整的表记录，然后再匹配B表，在B表找不到匹配项的记录，对应B表的字段会显示NULL；下面对course表和teacher表左联结：

SELECT
	CS.教师号,
	CS.课程号,
	CS.课程名称,
	TC.教师姓名
FROM
	course AS CS
LEFT JOIN teacher AS TC ON CS.教师号 = TC.教师号;

3、Right join（右联结）A right join B表示从B表中取出完整的表记录，然后再匹配A表，在A表找不到匹配项的记录，对应A表的字段会显示NULL；下面对course表和teacher表右联结：

SELECT
	CS.教师号,
	CS.课程号,
	CS.课程名称,
	TC.教师姓名
FROM
	course AS CS
RIGHT JOIN teacher AS TC ON CS.教师号 = TC.教师号;

4、Inner join（内联结）A inner join B表示显示A表和B表共有的字段对应的记录。下面对course表和teacher表内联结：

SELECT
	CS.教师号,
	CS.课程号,
	CS.课程名称,
	TC.教师姓名
FROM
	course AS CS
INNER JOIN teacher AS TC ON CS.教师号 = TC.教师号;

5、Full join：MySQL不支持全联结，需要使用左右联结UNION构造。下面对course表和teacher表做全联结：

(SELECT
	CS.教师号,
	CS.课程号,
	CS.课程名称,
	TC.教师姓名
FROM
	course AS CS
LEFT JOIN teacher AS TC ON CS.教师号 = TC.教师号)
UNION

(SELECT
	TC.教师号,
	CS.课程号,
	CS.课程名称,
	TC.教师姓名
FROM
	course AS CS
RIGHT JOIN teacher AS TC ON CS.教师号 = TC.教师号)

6、一张图读懂sql联结

练习题

第一部分：sqlzoo练习题：https://sqlzoo.net/wiki/The_JOIN_operation/zh

练习整体比较简单，仅对踩过坑的几点在此说明：

第11题For every match involving 'POL', show the matchid, date and the number of goals scored.要求既要显示matchid又要显示date，我开始只是使用了matchid做group by的条件，这样会报错：‘a.mdate' isn't in GROUP BY，所以这个时候需要将a.mdate也要写入group by才能pass。重新复习下group by存在的时候select子句中只能使用group by中的列名称以及聚合函数。

SELECT
	a.id,
	a.mdate,
	count(b.player)
FROM
	game AS a
INNER JOIN goal AS b ON a.id = b.matchid
WHERE
	(team1 = 'POL' OR team2 = 'POL')
GROUP BY
	a.id,
	a.mdate

第13题考察的是case语句，后续使用的多了，会单独写总结：

SELECT
	a.mdate,
	a.team1,
	sum(
		CASE
		WHEN b.teamid = a.team1 THEN
			1
		ELSE
			0
		END
	) AS score1,
	a.team2,
	sum(
		CASE
		WHEN b.teamid = a.team2 THEN
			1
		ELSE
			0
		END
	) AS score2
FROM
	game AS a
LEFT JOIN goal AS b ON a.id = b.matchid
GROUP BY
	a.id,
	a.mdate,
	a.team1,
	a.team2;

第二部分：针对student表，teacher表，score表和course表，做如下练习题：

• 查询所有学生的学号、姓名、选课数、总成绩
• 查询平均成绩大于85的所有学生的学号、姓名和平均成绩
• 查询学生的选课情况：学号，姓名，课程号，课程名称
• 查询出每门课程的及格人数和不及格
• /*使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩，分别统计：各分数段人数，课程号和课程名称*/

-- 查询所有学生的学号、姓名、选课数、总成绩，保存为视图，方便后续使用
CREATE VIEW score_stu_view (
	出生日期,
	姓名,
	学号,
	性别,
	成绩,
	课程号
) AS SELECT
	stu.出生日期,
	stu.姓名,
	stu.学号,
	stu.性别,
	sc.成绩,
	sc.课程号
FROM
	student AS stu
INNER JOIN score AS sc ON stu.学号 = sc.学号;
SELECT
	学号,
	姓名,
	COUNT(课程号) AS 选课数,
	SUM(成绩) AS 总成绩
FROM
	score_stu_view
GROUP BY
	学号;
-- 查询平均成绩大于85的所有学生的学号、姓名和平均成绩
SELECT
	学号,
	姓名,
	AVG(成绩) AS 平均成绩
FROM
	score_stu_view
GROUP BY
	学号
HAVING
	平均成绩 > 85;
-- 查询学生的选课情况：学号，姓名，课程号，课程名称
CREATE VIEW course_score_view(姓名,学号,成绩,课程号,课程名称) AS SELECT
	stu.姓名,
	stu.学号,
	sc.成绩,
	sc.课程号,cs.课程名称
FROM
	student AS stu
INNER JOIN score AS sc ON stu.学号 =sc.学号
INNER JOIN course AS cs ON sc.课程号 =cs.课程号;
-- 查询出每门课程的及格人数和不及格
SELECT
	课程号,
	sum(
		CASE
		WHEN 成绩 >= 60 THEN
			1
		ELSE
			0
		END
	) AS 及格人数,
	sum(CASE WHEN 成绩 < 60 THEN 1 ELSE 0 END) AS 不及格人数
FROM
	course_score_view
GROUP BY
	课程号;
/*使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩，分别统计：各分数段人数，
课程号和课程名称*/
SELECT
	课程号,
	课程名称,
	sum(
		CASE
		WHEN 成绩 > 85
		AND 成绩 <= 100 THEN
			1
		ELSE
			0
		END
	) AS 一等,
	sum(
		CASE
		WHEN 成绩 > 70
		AND 成绩 <= 85 THEN
			1
		ELSE
			0
		END
	) AS 二等,
	sum(
		CASE
		WHEN 成绩 >= 60
		AND 成绩 <= 70 THEN
			1
		ELSE
			0
		END
	) AS 三等,
	sum(CASE WHEN 成绩 < 60 THEN 1 ELSE 0 END) AS 不及格
FROM
	course_score_view
GROUP BY
	课程号;

备注：本文示例代码使用的表

course表

teacher表

score表

student表