文章目录
- 1. 表格
- 2. 题目
- 3. 题目 + 解答
- 1、查询课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号
- 2、查询平均成绩大于60分的学生的学号和平均成绩
- 2.1、所有成绩小于60分的学生信息
- 2.2、查询平均成绩小于60分的学生的学号和平均成绩，考虑没参加考试的情况
- 3、查询所有学生的学号、姓名、选课数、总成绩
- 4、查询姓“猴”的老师的个数
- 5、查询没学过“张三”老师课的学生的学号、姓名
- 6、查询学过“张三”老师所教的所有课的同学的学号、姓名
- 7、查询学过编号为“01”的课程并且也学过编号为“02”的课程的学生的学号、姓名（！）
- 7.1、查询学过编号为“01”的课程但没有学过编号为“02”的课程的学生的学号、姓名（！）
- 8、查询课程编号为“02”的总成绩
- 9、查询所有课程成绩小于60分的学生的学号、姓名
- 10、查询没有学全所有课的学生的学号、姓名 （！）
- 11、查询至少有一门课与学号为“01”的学生所学课程相同的学生的学号和姓名
-
题目来源：超经典SQL练习题，做完这些你的ＳＱＬ就过关了（时间比较久，可能是原出处）
-
工具：
Navicat For MySQL
1. 表格
题目涉及到四张表格：
- 学生表（Student）
- 课程表（Course）
- 教师表（Teacher）
- 成绩表（Score）
附表格创建代码：
# Student 学生表
CREATE TABLE Student
(
s_id VARCHAR(20),
s_name VARCHAR(20) NOT NULL,
s_birth VARCHAR(20) NOT NULL,
s_sex VARCHAR(10) NOT NULL,
PRIMARY KEY(s_id)
);
INSERT INTO Student VALUES('01', '赵雷', '1990-01-01', '男');
INSERT INTO Student VALUES('02', '钱电', '1990-12-21', '男');
INSERT INTO Student VALUES('03', '孙风', '1990-05-20', '男');
INSERT INTO Student VALUES('04', '李云', '1990-08-06', '男');
INSERT INTO Student VALUES('05', '周梅', '1991-12-01', '女');
INSERT INTO Student VALUES('06', '吴兰', '1992-03-01', '女');
INSERT INTO Student VALUES('07', '郑竹', '1989-07-01', '女');
INSERT INTO Student VALUES('08', '王菊', '1990-01-20', '女');
# Course 课程表
CREATE TABLE Course
(
c_id VARCHAR(20),
c_name VARCHAR(20) NOT NULL,
t_id VARCHAR(20) NOT NULL,
PRIMARY KEY(c_id)
);
INSERT INTO Course VALUES('01', '语文', '02');
INSERT INTO Course VALUES('02', '数学', '01');
INSERT INTO Course VALUES('03', '英语', '03');
# Teacher 教师表
CREATE TABLE Teacher
(
t_id VARCHAR(20),
t_name VARCHAR(20) NOT NULL DEFAULT '',
PRIMARY KEY(t_id)
);
INSERT INTO Teacher VALUES('01', '张三');
INSERT INTO Teacher VALUES('02', '李四');
INSERT INTO Teacher VALUES('03', '王五');
# Score 分数表
CREATE TABLE Score
(
s_id VARCHAR(20),
c_id VARCHAR(20),
s_score INT(3),
PRIMARY KEY(s_id, c_id) # 注意这里是联合主键
);
INSERT INTO Score VALUES('01', '01', 80);
INSERT INTO Score VALUES('01', '02', 90);
INSERT INTO Score VALUES('01', '03', 99);
INSERT INTO Score VALUES('02', '01', 70);
INSERT INTO Score VALUES('02', '02', 60);
INSERT INTO Score VALUES('02', '03', 80);
INSERT INTO Score VALUES('03', '01', 80);
INSERT INTO Score VALUES('03', '02', 80);
INSERT INTO Score VALUES('03', '03', 80);
INSERT INTO Score VALUES('04', '01', 50);
INSERT INTO Score VALUES('04', '02', 30);
INSERT INTO Score VALUES('04', '03', 20);
INSERT INTO Score VALUES('05', '01', 76);
INSERT INTO Score VALUES('05', '02', 87);
INSERT INTO Score VALUES('06', '01', 31);
INSERT INTO Score VALUES('06', '03', 34);
INSERT INTO Score VALUES('07', '02', 89);
INSERT INTO Score VALUES('07', '03', 98);
# 四张表
SELECT * FROM Student;
SELECT * FROM Course;
SELECT * FROM Teacher;
SELECT * FROM Score;
2. 题目
1、查询课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号
2、查询同时存在" 01 “课程和” 02 "课程的情况
3. 题目 + 解答
1、查询课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号
多次将Score表自连接实现将同一个字段两次使用：
## 自连接
SELECT st.*, m.s_score1, m.s_score2
FROM
(
SELECT sc1.s_id, sc1.s_score s_score1, sc2.s_score s_score2 # 注意有两个成绩
FROM Score sc1
JOIN Score sc2
ON sc1.s_id = sc2.s_id
AND sc1.c_id = '01' # 因为是INNER JOIN 下面的条件可以不写在WHERE中
AND sc2.c_id = '02'
AND sc1.s_score > sc2.s_score
) m
JOIN Student st
ON m.s_id = st.s_id;
2、查询平均成绩大于60分的学生的学号和平均成绩
SELECT s_id, AVG(s_score) avg_score
FROM Score
GROUP BY s_id
HAVING avg_score > 60;
2.1、所有成绩小于60分的学生信息
SELECT st.s_id, st.s_name, st.s_birth, st.s_sex
FROM Student st
JOIN (
SELECT s_id, MIN(s_score) min_score # 可以对下边界来进行限制，来满足【所有】这个条件
FROM Score
GROUP BY s_id
HAVING min_score < 60) t
ON st.s_id = t.s_id
## 也可以使用 WHERE > 60 + NOT IN 的思路
2.2、查询平均成绩小于60分的学生的学号和平均成绩，考虑没参加考试的情况
-- ## 错误：当前使用计数的方式来设置条件，无法匹配到没参加考试的情况 （考点其实应该是 LEFT JOIN）
-- SELECT s_id,
-- CASE
-- WHEN COUNT(s_id) = 1 THEN SUM(s_score) / 3 #注意s_score必须在聚合函数内
-- WHEN COUNT(s_id) = 2 THEN SUM(s_score) / 3
-- WHEN COUNT(s_id) = 3 THEN AVG(s_score)
-- ELSE 0
-- END avg_score
-- FROM Score
-- GROUP BY s_id
-- HAVING avg_score < 60
# 正解 （还有更简单的方法：IFNULL(col, value)）
SELECT m.s_id,
AVG(m.score) avg_score
FROM
(
SELECT st.s_id,
IF(sc.s_score IS NULL, 0, sc.s_score) score
FROM Student st
LEFT JOIN Score sc
ON st.s_id = sc.s_id
) m # 将未参加考试的部分记零分
GROUP BY m.s_id
HAVING avg_score < 60;
另解：
# 使用IFNULL() 一步到位
SELECT m.s_id, AVG(IFNULL(m.s_score, 0)) avg_score
FROM
(
SELECT st.s_id, sc.s_score
FROM Student st
LEFT JOIN Score sc
ON st.s_id = sc.s_id
) m
GROUP BY m.s_id
HAVING AVG(IFNULL(m.s_score, 0)) < 60
-- HAVING avg_score < 60 # why this also OK ！！（记住HAVING 可以使用SELECT 字段的别名（突破执行顺序的羁绊！））
3、查询所有学生的学号、姓名、选课数、总成绩
-- ## 错误：没有考虑到可能有学生完全没有选课，应该使用LEFT JOIN
-- SELECT st.s_id, st.s_name, COUNT(sc.c_id), SUM(sc.s_score)
-- FROM Student st
-- JOIN Score sc
-- ON st.s_id = sc.s_id
-- GROUP BY st.s_id, st.s_name
## 正解
SELECT st.s_id, st.s_name, COUNT(sc.c_id), SUM(sc.s_score)
FROM Student st
LEFT JOIN Score sc
ON st.s_id = sc.s_id
GROUP BY st.s_id, st.s_name
4、查询姓“猴”的老师的个数
SELECT COUNT(t_name)
FROM Teacher
WHERE t_name LIKE "猴%"
5、查询没学过“张三”老师课的学生的学号、姓名
-- ## 错误（没选课程和选了课程的同学都没找出来）
-- SELECT DISTINCT st.s_id, st.s_name # 注意 DISTINCT 去重
-- FROM Student st
-- JOIN Score sc
-- ON st.s_id = sc.s_id
-- JOIN Course c
-- ON sc.c_id = c.c_id
-- JOIN Teacher t
-- ON c.t_id = t.t_id
-- WHERE t.t_name != "张三"
## 正解：【没有】这个条件可以使用 NOT IN
SELECT st.s_id, st.s_name
FROM Student st
WHERE s_id NOT IN
(
SELECT sc.s_id
FROM Score sc
JOIN Course c
ON sc.c_id = c.c_id
JOIN Teacher t
ON c.t_id = t.t_id
WHERE t.t_name = "张三"
)
6、查询学过“张三”老师所教的所有课的同学的学号、姓名
## 有点难度，想不过来就很难【自连接的情况】
SELECT st.s_id, st.s_name
FROM Student st
WHERE st.s_id IN
(
SELECT DISTINCT sc.s_id
FROM
(SELECT c.c_id
FROM Course c
JOIN Teacher t
ON c.t_id = t.t_id
WHERE t.t_name = "张三") s # “张三”老师所教的所有课
LEFT JOIN Score sc
ON s.c_id = sc.c_id
WHERE sc.s_id IS NOT NULL
);
7、查询学过编号为“01”的课程并且也学过编号为“02”的课程的学生的学号、姓名（！）
-- ## 不严谨的奇葩解法
-- SELECT st.s_id, st.s_name
-- FROM (
-- SELECT s_id, Group_CONCAT(c_id) c_str
-- FROM Score
-- GROUP BY s_id) t # 分组进行进行行合并
-- JOIN Student st
-- ON t.s_id = st.s_id
-- WHERE c_str LIKE '%01%' AND c_str LIKE '%02%'
--
# 正解：自连接
SELECT st.s_id, st.s_name
FROM Student st
JOIN
(
SELECT sc1.*
FROM Score sc1
JOIN Score sc2
ON sc1.s_id = sc2.s_id
WHERE sc1.c_id = '01' # 这里不需要使用IN，也不需要纠结顺序问题，因为两张表都是Score
AND sc2.c_id = '02'
) m
ON st.s_id = m.s_id;
7.1、查询学过编号为“01”的课程但没有学过编号为“02”的课程的学生的学号、姓名（！）
## 同样的奇葩解法
-- SELECT DISTINCT st.s_id, st.s_name ## 注意使用DISTINCT
-- FROM (
-- SELECT s_id, Group_CONCAT(c_id) c_str
-- FROM Score
-- GROUP BY s_id) t # 分组进行进行行合并
-- JOIN Student st
-- ON t.s_id = st.s_id
-- WHERE c_str LIKE '%01%' AND c_str NOT LIKE '%02%'
## 正解：自连接
SELECT DISTINCT st.s_id, st.s_name ## 注意使用DISTINCT
FROM Student st
JOIN
(
SELECT sc1.*
FROM Score sc1
JOIN Score sc2
ON sc1.c_id = '01' ## 无关次序
AND sc2.c_id != '02'
) m
ON st.s_id = m.s_id
8、查询课程编号为“02”的总成绩
SELECT SUM(s_score)
FROM Score
GROUP BY c_id
-- WHERE c_id = '02' # 考察 HAVING，聚合条件限制不能使用WHERE
HAVING c_id = '02'
9、查询所有课程成绩小于60分的学生的学号、姓名
## 【所有】这个条件使用边界值进行限定
SELECT DISTINCT st.s_id, st.s_name
FROM Student st
JOIN
(
SELECT s_id,
MIN(s_score) min_score
FROM Score s
GROUP BY s.s_id
HAVING min_score < 60
) s # 满足条件的学生
ON st.s_id = s.s_id
10、查询没有学全所有课的学生的学号、姓名 （！）
## 这题用LEFT JOIN也不好使
SELECT DISTINCT st.s_id, st.s_name
FROM Student st
JOIN
(
SELECT m.s_id
FROM (
SELECT s_id, COUNT(c_id) cnt
FROM Score
GROUP BY s_id
) m
WHERE m.cnt != (SELECT COUNT(c_id) FROM Course)
) n # 子查询注意都要使用别名
ON st.s_id = n.s_id
11、查询至少有一门课与学号为“01”的学生所学课程相同的学生的学号和姓名
-- ## 错误：误解题意+没有排除自己
-- SELECT st.s_id, st.s_name
-- FROM
-- (
-- SELECT DISTINCT s_id
-- FROM Score
-- WHERE c_id = '01'
-- ) m # 至少有一门课与学号为“01”的学生id
-- JOIN Student st
-- WHERE m.s_id = st.s_id