SQL 语句学习
写在前面
上个星期一直都在准备之前的面试，也没有学习其他的新东西。所以博客很久没有更新了。
从这次开始，我们要进行数据库的相关学习了。在后端面试中，数据库也是比较重要的一个部分。
整个MySQL数据库的学习分为两个部分：
- SQL 语句
- SQL 原理：事务、锁、隔离、索引
SQL 语句
本来是应该去看《MySQL 必知必会》这本书的，但是实际上书上说的都是一些简单的操作。我们直接上实战内容会更好。
所以这里直接上 SQL面试50题
表的联结关系
表联结关系图
可以看到这个表的结构就是如此，对应关系比较简单的。
1. 查询编号01的课比编号02的课成绩高的所有学生
这里其实要用到 INNER JOIN 内部联结。
将01课程成绩和02课程成绩拿出来，做一下联结，将本来的行数据变成列数据。
-- 查询课程编号01比02课程成绩高的所有的学号（重点）
-- 直接查询是不可以的，因为成绩表中，都是按照行出现的。
-- 如果有一个 student c_01_score c_02_score 的表，就可以直接 SELECT * from WHERE 01>02 直接查出来了，所以重点变成了我们做这样的一个临时表出来
SELECT * FROM
(
SELECT s_id,c_id,s_score FROM score WHERE c_id="01"
) as a
INNER JOIN -- 内部联结
(
SELECT s_id,c_id,s_score FROM score WHERE c_id="02"
) as b
on a.s_id=b.s_id -- 联结条件
联结之后的表
所以加上条件 WHERE a.s_score>b.s_score 即可查询出来学生学号。
再加上一些其他的学生信息即可完成最后的查询
-- 查询课程编号01比02课程成绩高的所有的学号（重点）
-- 直接查询是不可以的，因为成绩表中，都是按照行出现的。
-- 如果有一个 student c_01_score c_02_score 的表，就可以直接 SELECT * from WHERE 01>02 直接查出来了，所以重点变成了我们做这样的一个临时表出来
SELECT a.s_id "student_num",c.s_name "s_name",a.s_score "01",b.s_score "02" FROM
(
SELECT s_id,c_id,s_score FROM score WHERE c_id="01"
) as a
INNER JOIN -- 内部联结
(
SELECT s_id,c_id,s_score FROM score WHERE c_id="02"
) as b
on a.s_id=b.s_id -- 联结条件
INNER JOIN student as c on a.s_id=c.s_id
WHERE a.s_score>b.s_score
最终查询结果
核心就是用 INNER JOIN -- ON 语句将行数据变成列数据。
2. 查询平均成绩大于60分的学生学号和平均成绩
这个其实就是考察 group by 分组操作
Group by 都是结合 “合计函数”，根据一个或者多个 列 对结果进行分组
这里的 “合计函数” 大概就是 count()、Avg() 之类
-- 查询平均成绩大于60分的学生的学号和平均成绩
SELECT s_id,AVG(s_score)
-- 这里最好 Select 语句的 字段是后面 GROUP BY 使用的字段
FROM score
GROUP BY s_id HAVING AVG(s_score)>60
-- HAVING 条件类似于 WHERE 条件，
-- 但是 Having 只作用于分组内数据
核心： group by .. HAVING ..
注意 group by..后面的分组条件，一般要出现在前面的 select 后面
3. 查询所有学生的学号、姓名、选课数、总成绩 （不重要）
-- 查询所有学生的学号、姓名、选课总数、总成绩
-- 同样我们想要一个 临时表 学生id 学生姓名 课程编号 课程成绩
-- 然后使用 GROUP BY 就可以做统计了
SELECT a.s_id,a.s_name,COUNT(b.c_id),
SUM(case when b.s_score is NULL then 0 ELSE b.s_score END)
FROM student as a
LEFT JOIN score as b on a.s_id=b.s_id
-- LEFT JOIN 而不是 INNER JOIN 是因为后者只取交集
-- 如果有没有选课的人就遗漏了
GROUP BY s_id,a.s_name
4. 查询性候老师的数目
-- 查询姓张老师的数目
SELECT COUNT(t_id)
FROM teacher
WHERE t_name LIKE '张%'
主要考察 like， % ，COUNT 函数的用法
5. 查询没有上过张三老师课的学生（重点）
-- 查询没有上过张三老师课的学生
-- 取一个余即可
SELECT * FROM student
WHERE s_id not in(
SELECT s_id FROM score
WHERE c_id=
(
SELECT c_id FROM course
WHERE t_id=
(
SELECT t_id
FROM teacher WHERE t_name="张三"
)
)
)
考察 not in 关键字。这里用 not in 取余即可。
从老师姓名查询到老师id，从老师id 查询到 课程id，从课程id 查询到上课的学生的 id，再 not in 即可。
这里用到很多子查询。
6. 查询上过张三老师的课的学生（重点）
这个题目乍一看不是上面已经做过了嘛。没错，不过上面5中我们用的都是一些子查询的方法，子查询有时候很影响性能。
所以在6中，我们想要使用一些表联结的方法来做。
我们想要一个 学生id，学生姓名，课程id，该课任课老师 这样的临时表
-- 查询上过张三老师课的学生
-- 为了效率我们不用子查询，改用 表联结 的方式
SELECT st.s_id,st.s_name,c.c_id,c.c_name,c.t_id,t.t_name
FROM student as st
INNER JOIN score as s on s.s_id=st.s_id
INNER JOIN course as c on s.c_id=c.c_id
INNER JOIN teacher as t on t.t_id=c.t_id
WHERE t_name='张三'
ORDER BY st.s_id
7. 查询学过01课程也学过02课程的学生（重点）
-- 查询学过01课程也学过02课程的学生
-- 分别差出来，然后做交集即可
SELECT * FROM student WHERE
s_id in
(
SELECT a.s_id FROM
(SELECT s_id FROM score WHERE c_id='01') as a
INNER JOIN
(SELECT s_id FROM score WHERE c_id='02') as b
on a.s_id=b.s_id
)
8. 查询课程编号为 02 的课程总成绩（不重点）
直接查询即可
-- 查询02 课程的总成绩
SELECT SUM(s_score) FROM score
WHERE c_id='02'
-- 使用 GROUP BY 也是可以的
SELECT c_id,SUM(s_score) FROM score
GROUP BY c_id HAVING c_id='02'
9. 查询所有成绩都小于60分的学生
-- 查询所有成绩小于60分的学生
-- 首先查同学成绩小于60分的数目
-- 然后查总共学的课程数目
-- 二者联结，如果相等就是满足条件
SELECT a.s_id FROM
(SELECT s_id,COUNT(c_id) as cnt
FROM score
WHERE s_score<60
GROUP BY s_id) as a
INNER JOIN
(SELECT s_id,COUNT(c_id) as cnt
FROM score
GROUP BY s_id) as b
on a.s_id=b.s_id
WHERE a.cnt=b.cnt
10. 查询没有学全所有课的学生学号、姓名（重点）
-- 查询没有学全所有课的学生学号、姓名（重点）
SELECT s_id FROM
score
GROUP BY s_id HAVING count(DISTINCT c_id)<
(SELECT COUNT(DISTINCT c_id) FROM course)
-- 这样实际上会漏掉一个不在 score 表中的学生
-- 所以我们用表联结的方法来做 用 student 表来 左连接 score 表
SELECT st.s_id,st.s_name,sc.c_id from
student as st
left JOIN
score as sc
on st.s_id=sc.s_id
GROUP BY st.s_id HAVING count(DISTINCT c_id)<
(SELECT COUNT(DISTINCT c_id) FROM course)
这里关于 group by 的理解，就是分组统计。根据指定列进行分组，后续可以进行统计。
这个老哥的讲解看起来还可以
