(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步骤:
- 得到一个待解决的问题后,先将问题翻译成能看明白的大白话
- 写出解决问题的分析思路(每一步要做些什么)
- 写出分析思路对应的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 );