sql 加法_SQL——多表查询

（1）表的加法

运用表的加法（union）的出的结果不包含重复的数据

运用表的加法（union all）的出的结果包含重复的数据

练习

SELECT * FROM course UNION SELECT * FROM course1;

（2）表的联结

常用的联结方式有：

交叉联结（cross join）、内联结（inner join）、左联结（left join）、右联结（right join）、全联结（full join）

1）交叉联结（cross join）

交叉联结又称为笛卡尔联结、无条件（内）联结

区分有没有条件，以是否在SQL中有关键字 `on` 出现作为判定

练习

SELECT * FROM student CROSS JOIN score;

注：下图截图不完整

2）内联结（inner join）

练习

SELECT * FROM student INNER JOIN score;

注：下图截图不完整

3）左联结（left join）

左联结将左侧表的数据全部取出来

两张表进行左联结的时候，会将左侧的表作为主表，主表中的数据全部被读取

练习

SELECT * FROM student LEFT JOIN score ON student.`学号` = score.`学号`;

4）右联结（right join）

右联结将右侧表的数据全部取出来

两张表进行右联结的时候，会将右侧的表作为主表，主表中的数据全部被读取

练习

SELECT * FROM student LEFT JOIN score ON student.`学号` = score.`学号`;

5）全联结（full join）

注意：MySQL不支持全联结

表的联结总结：

注意：找空值用is null，而不是用= null

SELECT * FROM student LEFT JOIN score ON student.`学号` = score.`学号` WHERE score.`学号` is NULL;

SELECT * FROM student LEFT JOIN score ON student.`学号` = score.`学号` WHERE score.`学号` = NULL;

is 仅用于is null或is not null

= 用在2种情况下：一是where 查询条件，如：where id=1；二是用于赋值，如：set id=1

举例：update user set name = null where id = 1;

（3）联结应用案例

用SQL解决业务问题的3步骤：

1. 得到一个待解决的问题后，先将问题翻译成能看明白的大白话
2. 写出解决问题的分析思路（每一步要做些什么）
3. 写出分析思路对应的SQL子句

案例一：

SELECT
	a.`学号`,
	a.`姓名`,
	COUNT( b.`课程号` ) AS `选课数`,
	SUM( b.`成绩` ) AS `总成绩` 
FROM
	student AS a
	LEFT JOIN score AS b ON a.`学号` = b.`学号` 
GROUP BY
	a.`学号`;

案例二：

SELECT
	a.`学号`,
	a.`姓名`,
	AVG( b.`成绩` ) AS `平均成绩` 
FROM
	student AS a
	LEFT JOIN score AS b ON a.`学号` = b.`学号` 
GROUP BY
	a.`学号` 
HAVING
	AVG( b.`成绩` ) > 85;

案例三：

左联结：

SELECT
	a.`学号`,
	a.`姓名`,
	b.`课程号`,
	c.`课程名称` 
FROM
	student AS a
	LEFT JOIN score AS b ON a.`学号` = b.`学号`
	LEFT JOIN course AS c ON b.`课程号` = c.`课程号`;

内联结：

SELECT
	a.`学号`,
	a.`姓名`,
	b.`课程号`,
	c.`课程名称` 
FROM
	student AS a
	INNER JOIN score AS b ON a.`学号` = b.`学号`
	INNER JOIN course AS c ON b.`课程号` = c.`课程号`;

（4）case表达式

case表达式，通常用来做多分支判断，语法如下：

应用举例：

用case表达式中的when与else对表格中的每一行成绩做判断，每判断完一行成绩之后，再进行下一行判断

举例：

计算 `及格人数` 的执行流程：

注意事项：

else子句可以不写，不写默认为空值

end不能省略

案例：

因为要查找课程表里的全部数据，上图用的是右联结， `group by` 子句是对联结结果按课程号、课程名称来分组，由于select子句里的列名只能是group by子句里的列名，为了让查询结果同时显示出课程名称，所以在 group by 子句里加入了课程名称，这里加入的前提是不影响分组结果，因为课程号与分组结果是一对一的关系，,所以在这里加不加与课程名称进行分组，对分组结果都没有影响

只有当某些字段单独分组时得到的结果一样时，才能联合分组

联合分组的好处是使select子句的字段不用聚合函数，以此回避不能对字段使用聚合函数的情况

SELECT
	b.课程号,
	b.课程名称,
	SUM( CASE WHEN a.成绩 <= 100 AND a.成绩 >= 85 THEN 1 ELSE 0 END ) AS '[100-85]',
	SUM( CASE WHEN a.成绩 < 85 AND a.成绩 >= 70 THEN 1 ELSE 0 END ) AS '[85-70]',
	SUM( CASE WHEN a.成绩 < 70 AND a.成绩 >= 60 THEN 1 ELSE 0 END ) AS '[70-60]',
	SUM( CASE WHEN a.成绩 < 60 THEN 1 ELSE 0 END ) AS '[<60]' 
FROM
	score AS a
	RIGHT JOIN course AS b ON a.课程号 = b.课程号 
GROUP BY
	b.课程名称;

# 上面的SQL语句中使用 GROUP BY a.课程号 或 GROUP BY b.课程名称 或 GROUP BY a.课程号, b.课程名称的结果都一样

多列分组反例：

SELECT  continent,name ,max( area ) 
FROM world
where area > 0
group by continent;

#  只有当某些字段单独分组时得到的结果一样时，才能联合分组
#  联合分组的好处是使select子句的字段不用聚合函数，以此回避不能对字段使用聚合函数的情况

SELECT  continent, name ,max( area ) 
FROM world
where area > 0
group by continent， name;

# 分组之后，非分组列的列名全部要用聚合函数，否则报错

SELECT  continent,count( name ) ,max( area ) 
FROM world
where area > 0
group by continent;

正确做法：

/*
【知识点】关联子查询
一般来说 先执行子查询，但关联子查询例外。有关联子查询时，先执行主查询再执行关联子查询。
*/

SELECT
	continent,
	NAME,
	area 
FROM
	world AS x 
WHERE
	area = ( SELECT max( area ) FROM world AS y WHERE y.continent = x.continent );