![44df7ba4e552fc51607eb5102f39f650.png](https://img-blog.csdnimg.cn/img_convert/44df7ba4e552fc51607eb5102f39f650.png)
(1)表的加法
![b4e784c40427abd7706463f6719892c8.png](https://img-blog.csdnimg.cn/img_convert/b4e784c40427abd7706463f6719892c8.png)
运用表的加法(union)的出的结果不包含重复的数据
![eef3b3357548cd2cfa8687e543317735.png](https://img-blog.csdnimg.cn/img_convert/eef3b3357548cd2cfa8687e543317735.png)
运用表的加法(union all)的出的结果包含重复的数据
![87a0914dce3677d8119289533993ef35.png](https://img-blog.csdnimg.cn/img_convert/87a0914dce3677d8119289533993ef35.png)
练习
SELECT * FROM course UNION SELECT * FROM course1;
![823050ef2cc74a577b7fad7b52ba2501.png](https://img-blog.csdnimg.cn/img_convert/823050ef2cc74a577b7fad7b52ba2501.png)
(2)表的联结
![2c6ca48df9026467f0d41e4cd9b038ad.png](https://img-blog.csdnimg.cn/img_convert/2c6ca48df9026467f0d41e4cd9b038ad.png)
![0f7c68856f28f8334a44d09d4c116086.png](https://img-blog.csdnimg.cn/img_convert/0f7c68856f28f8334a44d09d4c116086.png)
![039efc9f1bfaed48c1ea43b692498f4c.png](https://img-blog.csdnimg.cn/img_convert/039efc9f1bfaed48c1ea43b692498f4c.png)
常用的联结方式有:
交叉联结(cross join)、内联结(inner join)、左联结(left join)、右联结(right join)、全联结(full join)
1)交叉联结(cross join)
交叉联结又称为笛卡尔联结、无条件(内)联结
区分有没有条件,以是否在SQL中有关键字 `on` 出现作为判定
![b44bf0e51d04a5dd4e3fd13fb0a06f02.png](https://img-blog.csdnimg.cn/img_convert/b44bf0e51d04a5dd4e3fd13fb0a06f02.png)
练习
SELECT * FROM student CROSS JOIN score;
注:下图截图不完整
![2d0f8f02ca5caac43c252e1651f24308.png](https://img-blog.csdnimg.cn/img_convert/2d0f8f02ca5caac43c252e1651f24308.png)
2)内联结(inner join)
![cdb4fef70e25981a104338b56af3ea02.png](https://img-blog.csdnimg.cn/img_convert/cdb4fef70e25981a104338b56af3ea02.png)
![22208af6a6b43756e09f10d175247e78.png](https://img-blog.csdnimg.cn/img_convert/22208af6a6b43756e09f10d175247e78.png)
![b2bddf9275ba1b73cbb5563c443fa731.png](https://img-blog.csdnimg.cn/img_convert/b2bddf9275ba1b73cbb5563c443fa731.png)
练习
SELECT * FROM student INNER JOIN score;
注:下图截图不完整
![2d0f8f02ca5caac43c252e1651f24308.png](https://img-blog.csdnimg.cn/img_convert/2d0f8f02ca5caac43c252e1651f24308.png)
3)左联结(left join)
左联结将左侧表的数据全部取出来
![d37660def91e2d37d7827064970acab4.png](https://img-blog.csdnimg.cn/img_convert/d37660def91e2d37d7827064970acab4.png)
两张表进行左联结的时候,会将左侧的表作为主表,主表中的数据全部被读取
![d1c12051ec05771cfd5d9b0d5466da92.png](https://img-blog.csdnimg.cn/img_convert/d1c12051ec05771cfd5d9b0d5466da92.png)
![25fc75e4929224feb447b469f5fc981f.png](https://img-blog.csdnimg.cn/img_convert/25fc75e4929224feb447b469f5fc981f.png)
![da28b74c1161059d39ac760d3f4bb298.png](https://img-blog.csdnimg.cn/img_convert/da28b74c1161059d39ac760d3f4bb298.png)
![79f3481ac9119ffc2200c0450bcca343.png](https://img-blog.csdnimg.cn/img_convert/79f3481ac9119ffc2200c0450bcca343.png)
![ca9798652172a2efdd9fcef23fe3bf5d.png](https://img-blog.csdnimg.cn/img_convert/ca9798652172a2efdd9fcef23fe3bf5d.png)
练习
SELECT * FROM student LEFT JOIN score ON student.`学号` = score.`学号`;
![75f3cc78abb5726d4ae7e08d037929c2.png](https://img-blog.csdnimg.cn/img_convert/75f3cc78abb5726d4ae7e08d037929c2.png)
4)右联结(right join)
右联结将右侧表的数据全部取出来
![98941b92e519aac81af88ee73f576eb7.png](https://img-blog.csdnimg.cn/img_convert/98941b92e519aac81af88ee73f576eb7.png)
两张表进行右联结的时候,会将右侧的表作为主表,主表中的数据全部被读取
![5d1b78bf6a8b84454a6ce0e19437a0dc.png](https://img-blog.csdnimg.cn/img_convert/5d1b78bf6a8b84454a6ce0e19437a0dc.png)
![251f4ab057121d88bcba1c8832b4f90b.png](https://img-blog.csdnimg.cn/img_convert/251f4ab057121d88bcba1c8832b4f90b.png)
![f921804b6291b719fc1865f9b0425b08.png](https://img-blog.csdnimg.cn/img_convert/f921804b6291b719fc1865f9b0425b08.png)
![31a11bcb582dbd80e826b7ec4c402d4b.png](https://img-blog.csdnimg.cn/img_convert/31a11bcb582dbd80e826b7ec4c402d4b.png)
练习
SELECT * FROM student LEFT JOIN score ON student.`学号` = score.`学号`;
![6d2a35040449b9c18ce7d648031f26ea.png](https://img-blog.csdnimg.cn/img_convert/6d2a35040449b9c18ce7d648031f26ea.png)
5)全联结(full join)
注意:MySQL不支持全联结
![aadf8a8f0ae48aea1bb11a1a91368cca.png](https://img-blog.csdnimg.cn/img_convert/aadf8a8f0ae48aea1bb11a1a91368cca.png)
![be0c11762898e0f62dcb3f63faac04ad.png](https://img-blog.csdnimg.cn/img_convert/be0c11762898e0f62dcb3f63faac04ad.png)
表的联结总结:
![43f6cafdedee54ddb562ce0c13fbe40d.png](https://img-blog.csdnimg.cn/img_convert/43f6cafdedee54ddb562ce0c13fbe40d.png)
注意:找空值用is null,而不是用= null
SELECT * FROM student LEFT JOIN score ON student.`学号` = score.`学号` WHERE score.`学号` is NULL;
![69a82ee805d9d05f147baf05b40ced12.png](https://img-blog.csdnimg.cn/img_convert/69a82ee805d9d05f147baf05b40ced12.png)
SELECT * FROM student LEFT JOIN score ON student.`学号` = score.`学号` WHERE score.`学号` = NULL;
![e36923b9ee5085232742f4a253332616.png](https://img-blog.csdnimg.cn/img_convert/e36923b9ee5085232742f4a253332616.png)
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步骤:
- 得到一个待解决的问题后,先将问题翻译成能看明白的大白话
- 写出解决问题的分析思路(每一步要做些什么)
- 写出分析思路对应的SQL子句
案例一:
![d06d313091eb1e1911dc506d3e5b8939.png](https://img-blog.csdnimg.cn/img_convert/d06d313091eb1e1911dc506d3e5b8939.png)
![72828c174ead9802dd88bf0cd2403ae3.png](https://img-blog.csdnimg.cn/img_convert/72828c174ead9802dd88bf0cd2403ae3.png)
![1c26c7c097b35c42957312d03619dc1b.png](https://img-blog.csdnimg.cn/img_convert/1c26c7c097b35c42957312d03619dc1b.png)
SELECT
a.`学号`,
a.`姓名`,
COUNT( b.`课程号` ) AS `选课数`,
SUM( b.`成绩` ) AS `总成绩`
FROM
student AS a
LEFT JOIN score AS b ON a.`学号` = b.`学号`
GROUP BY
a.`学号`;
![2dad36c86667e9d2aeb0d48e28f8a398.png](https://img-blog.csdnimg.cn/img_convert/2dad36c86667e9d2aeb0d48e28f8a398.png)
案例二:
![9c81a2ed4d87034b0bd78b52645a0540.png](https://img-blog.csdnimg.cn/img_convert/9c81a2ed4d87034b0bd78b52645a0540.png)
![17a73ba4cf8a43a1619b2ddba552adbc.png](https://img-blog.csdnimg.cn/img_convert/17a73ba4cf8a43a1619b2ddba552adbc.png)
![cab81569e878b9d180ab4df7bdfca6e0.png](https://img-blog.csdnimg.cn/img_convert/cab81569e878b9d180ab4df7bdfca6e0.png)
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;
![2e81c429302ae14441059be5bdc4c71c.png](https://img-blog.csdnimg.cn/img_convert/2e81c429302ae14441059be5bdc4c71c.png)
案例三:
![1a30d9a4c29932cb7e8908707220a063.png](https://img-blog.csdnimg.cn/img_convert/1a30d9a4c29932cb7e8908707220a063.png)
![a848637fc9ed7eecffb9768e38ee10a4.png](https://img-blog.csdnimg.cn/img_convert/a848637fc9ed7eecffb9768e38ee10a4.png)
左联结:
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.`课程号`;
![fc72a7b6ee0ddc137743afe739451eb0.png](https://img-blog.csdnimg.cn/img_convert/fc72a7b6ee0ddc137743afe739451eb0.png)
内联结:
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.`课程号`;
![f6667183e5766a930d996f84eb3508f7.png](https://img-blog.csdnimg.cn/img_convert/f6667183e5766a930d996f84eb3508f7.png)
(4)case表达式
case表达式,通常用来做多分支判断,语法如下:
![5f9b7c635e2ff81a1ed1f7f8c8d6d62e.png](https://img-blog.csdnimg.cn/img_convert/5f9b7c635e2ff81a1ed1f7f8c8d6d62e.png)
应用举例:
用case表达式中的when与else对表格中的每一行成绩做判断,每判断完一行成绩之后,再进行下一行判断
![bc508b4fa83844c809c9bf09fbb519c2.png](https://img-blog.csdnimg.cn/img_convert/bc508b4fa83844c809c9bf09fbb519c2.png)
![40c9618f0c2324d1d29396952daa85ab.png](https://img-blog.csdnimg.cn/img_convert/40c9618f0c2324d1d29396952daa85ab.png)
举例:
![8221607f83e048b27e71777fc4750d9a.png](https://img-blog.csdnimg.cn/img_convert/8221607f83e048b27e71777fc4750d9a.png)
![032296a6558f01cc814e86ee8992c110.png](https://img-blog.csdnimg.cn/img_convert/032296a6558f01cc814e86ee8992c110.png)
![bab896ad9c36e36dd2101bff0090bdec.png](https://img-blog.csdnimg.cn/img_convert/bab896ad9c36e36dd2101bff0090bdec.png)
计算 `及格人数` 的执行流程:
![04bddc1fb3698df8c750e2e168cefeb2.png](https://img-blog.csdnimg.cn/img_convert/04bddc1fb3698df8c750e2e168cefeb2.png)
注意事项:
![113a67cd9dc925383ac8d1e90558afb6.png](https://img-blog.csdnimg.cn/img_convert/113a67cd9dc925383ac8d1e90558afb6.png)
else子句可以不写,不写默认为空值
end不能省略
案例:
![8c2027cd32ec205a9ab7593c555fb4bc.png](https://img-blog.csdnimg.cn/img_convert/8c2027cd32ec205a9ab7593c555fb4bc.png)
![1e194acfc184c0e499bcdfb616b94f54.png](https://img-blog.csdnimg.cn/img_convert/1e194acfc184c0e499bcdfb616b94f54.png)
因为要查找课程表里的全部数据,上图用的是右联结, `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.课程名称的结果都一样
![5a88d0dda56e85668aeff092013d8cd8.png](https://img-blog.csdnimg.cn/img_convert/5a88d0dda56e85668aeff092013d8cd8.png)
多列分组反例:
SELECT continent,name ,max( area )
FROM world
where area > 0
group by continent;
![f052991e5a0de65393611878e27c8eca.png](https://img-blog.csdnimg.cn/img_convert/f052991e5a0de65393611878e27c8eca.png)
# 只有当某些字段单独分组时得到的结果一样时,才能联合分组
# 联合分组的好处是使select子句的字段不用聚合函数,以此回避不能对字段使用聚合函数的情况
SELECT continent, name ,max( area )
FROM world
where area > 0
group by continent, name;
![d27b4123fc086c74c8a4f08c71467c21.png](https://img-blog.csdnimg.cn/img_convert/d27b4123fc086c74c8a4f08c71467c21.png)
# 分组之后,非分组列的列名全部要用聚合函数,否则报错
SELECT continent,count( name ) ,max( area )
FROM world
where area > 0
group by continent;
![8b1338a97b3c18284341084103b3a6ea.png](https://img-blog.csdnimg.cn/img_convert/8b1338a97b3c18284341084103b3a6ea.png)
正确做法:
/*
【知识点】关联子查询
一般来说 先执行子查询,但关联子查询例外。有关联子查询时,先执行主查询再执行关联子查询。
*/
SELECT
continent,
NAME,
area
FROM
world AS x
WHERE
area = ( SELECT max( area ) FROM world AS y WHERE y.continent = x.continent );
![31807172796540b2dce19087194d0e05.png](https://img-blog.csdnimg.cn/img_convert/31807172796540b2dce19087194d0e05.png)