SQL经典50题（MySQL版）

近段时间一直都在学习MySQL，为了检验自己的学习效果，特意拿了网上经典的SQL50题练了一下。

我用的是MySQL Server 8.0版本（新增了窗口函数rank(）等，接下来我会把自己的SQL语句以及运行结果放到这里来。答案不唯一，仅供参考。若有更好的查询方式，欢迎交流学习。

已知有以下4张表：

• 学生表：student(s_id,s_name,s_age,s_sex) – –学号,学生姓名,出生年月,性别

• 成绩表：score(s_id,c_id,score) – –学号,课程号,成绩

• 课程表：course(c_id,c_name,t_id) – –课程号,课程名称,教师号

• 教师表：teacher(t_id,t_name) – –教师号,教师姓名

以上4个表是通过加粗的字段建立连接的。

---

一、创建数据库和表

-- 创建数据库，并且使用
CREATE DATABASE IF NOT EXISTS df_school;
USE df_school;

​​
1、创建学生表并插入数据：

-- 创建学生表
CREATE TABLE IF NOT EXISTS student(
    s_id VARCHAR(10),
    s_name VARCHAR(20),
    s_age DATE,
    s_sex VARCHAR(10)
);

-- 往学生表中插入数据
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' , '女');

2、创建课程表并插入数据

-- 创建课程表
CREATE TABLE IF NOT EXISTS course(
    c_id VARCHAR(10),
    c_name VARCHAR(20),
    t_id VARCHAR(10)
);

-- 往课程表插入数据
INSERT INTO Course VALUES('01' , '语文' , '02');
INSERT INTO Course VALUES('02' , '数学' , '01');
INSERT INTO Course VALUES('03' , '英语' , '03');

3、创建教师表并插入数据

-- 创建教师表
CREATE TABLE IF NOT EXISTS teacher (
    t_id VARCHAR(10),
    t_name VARCHAR(20)
);

-- 往教师表插入数据
INSERT INTO Teacher VALUES('01' , '张三');
INSERT INTO Teacher VALUES('02' , '李四');
INSERT INTO Teacher VALUES('03' , '王五');

4、创建成绩表并插入数据

-- 创建成绩表
CREATE TABLE IF NOT EXISTS score (
    s_id VARCHAR(10),
    c_id VARCHAR(10),
    score VARCHAR(10)
);

-- 往成绩表插入数据
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);

创建好的4张表如下：

---

1、查询"01"课程比"02"课程成绩高的学生的学号及课程分数

-- 1、查询"01"课程比"02"课程成绩高的学生的学号及课程分数
SELECT a.s_id AS s_id, score1, score2
FROM 
(SELECT s_id, score AS score1 FROM score WHERE c_id = '01') a
INNER JOIN
(SELECT s_id, score AS score2 FROM score WHERE c_id = '02') b
ON a.s_id = b.s_id WHERE score1 > score2;

---

2、查询"01"课程比"02"课程成绩高的学生的信息及课程分数

-- 2、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
SELECT s.*, a.score AS score1, b.score AS score2
FROM student s,
     (SELECT s_id,score FROM score WHERE c_id = '01') a,
     (SELECT s_id,score FROM score WHERE c_id = '02') b
WHERE a.s_id = b.s_id AND a.score > b.score AND s.`s_id` = a.s_id;

3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

-- 3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
SELECT s.`s_id`,s.`s_name`,AVG(score) AS avg_score 
FROM student AS s,score AS sc
WHERE s.`s_id` = sc.`s_id` 
GROUP BY s.`s_id`
HAVING avg_score >= 60;

-- 3、(法二)查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
SELECT s.`s_id`,s.`s_name`,b.avg_score AS avg_score 
FROM student s
RIGHT JOIN
(SELECT s_id, AVG(score) AS avg_score FROM score
GROUP BY s_id HAVING avg_score >= 60) b
ON s.`s_id` = b.s_id;

---

4、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩显示null)

这道题得用到left join，不能用where连接，因为题目说了要求有显示为null的，where是inner join，不会出现null，如果用where在这道题里会查不出第08号学生

-- 4、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩显示null)
SELECT s.`s_id`,s_name,COUNT(c_id)AS 选课总数,SUM(score) AS 总成绩
FROM student s
LEFT JOIN score sc
ON s.`s_id` = sc.`s_id`
GROUP BY s_id;

---

5、查询姓“李”的老师的个数

-- 5、查询姓“李”的老师的个数
SELECT COUNT(t_name) AS 人数
FROM teacher 
WHERE t_name LIKE '李%';

---

6、查询没学过“张三”老师课的学生的学号、姓名

-- 6、查询没学过“张三”老师课的学生的学号、姓名
SELECT s_id, s_name FROM student WHERE s_id NOT IN
(SELECT s_id FROM score WHERE c_id IN
(SELECT c_id FROM course WHERE t_id IN 
(SELECT t_id 
FROM teacher 
WHERE t_name = '张三')));

-- 法二
SELECT s_id, s_name 
FROM student
WHERE s_id NOT IN(SELECT sc.s_id FROM score sc
INNER JOIN course co ON sc.`c_id` = co.`c_id`
INNER JOIN teacher te ON co.`t_id`= te.`t_id`
WHERE te.`t_name`='张三');

-- 法三
SELECT s_id, s_name
FROM student
WHERE s_name NOT IN (
    SELECT s.s_name
    FROM student AS s, course AS c, teacher AS t, score AS sc
    WHERE s.s_id = sc.s_id
        AND sc.c_id = c.c_id
        AND c.t_id = t.t_id
        AND t.t_name = '张三');

---

7、查询学过编号为“01”的课程并且也学过编号为“02”的课程的学生的学号、姓名

-- 7、查询学过编号为“01”的课程并且也学过编号为“02”的课程的学生的学号、姓名
SELECT s_id,s_name 
FROM student 
WHERE s_id IN (
SELECT s_id
FROM score
WHERE c_id = '01' OR c_id = '02'
GROUP BY s_id
HAVING COUNT(c_id) >= 2);

---

8、查询课程编号为“02”的总成绩

--  8、查询课程编号为“02”的总成绩
SELECT SUM(score) AS 总成绩
FROM score
WHERE c_id = '02';

---

9、查询没有学全所有课的学生的学号、姓名

-- 9、查询没有学全所有课的学生的学号、姓名
SELECT st.`s_id`,st.`s_name` 
FROM student st
INNER JOIN score sc
ON st.`s_id` = sc.`s_id`
GROUP BY sc.`s_id`
HAVING COUNT(sc.`c_id`) < (SELECT
COUNT(DISTINCT c_id) 
FROM course);

---

10、查询至少有一门课与学号为“01”的学生所学课程相同的学生的学号和姓名

-- 10、查询至少有一门课与学号为“01”的学生所学课程相同的学生的学号和姓名
SELECT st.`s_id`,st.`s_name`
FROM student st 
WHERE st.`s_id` IN(
SELECT DISTINCT sc.`s_id` 
FROM score sc
WHERE sc.`c_id` IN(
SELECT sc.`c_id`
FROM score sc
WHERE sc.`s_id` = 01)) AND
st.`s_id` <> '01';

-- 法二
SELECT DISTINCT st.`s_id`,st.`s_name`
FROM student st 
INNER JOIN score sc
ON st.`s_id`= sc.`s_id`
WHERE sc.`c_id` IN(
SELECT sc.`c_id`
FROM score sc
WHERE sc.`s_id` = '01') AND
st.`s_id` <> '01';

---

11、查询和“01”号同学所学课程完全相同的其他同学的信息

-- 11、查询和“01”号同学所学课程完全相同的其他同学的信息
SELECT DISTINCT st.* 
FROM student st
INNER JOIN score sc
ON st.`s_id` = sc.`s_id`
WHERE sc.`c_id` IN
(SELECT sc.`c_id`
FROM score sc
WHERE sc.`s_id`= '01') AND  sc.`s_id` <> '01'
GROUP BY sc.`s_id`
HAVING COUNT(sc.`c_id`) = (SELECT
COUNT(c_id) FROM score WHERE s_id = '01');

---

12、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

-- 12、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
SELECT st.`s_id`,st.`s_name`,AVG(sc.score) AS avg_score
FROM student st, score sc 
WHERE st.`s_id` = sc.`s_id`
AND sc.`score` < 60
GROUP BY sc.`s_id`
HAVING COUNT(sc.`c_id`) >= 2;

---

13、检索"01"课程分数小于60，按分数降序排列的学生信息

-- 13、检索"01"课程分数小于60，按分数降序排列的学生信息
SELECT st.*,sc.`score`
FROM student st
INNER JOIN score sc ON 
st.`s_id`= sc.`s_id` 
WHERE sc.`c_id` = '01' AND sc.`score` < 60
ORDER BY sc.`score` DESC;

---

14、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

-- 14、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
SELECT s_id, 
SUM(CASE WHEN c_id = '01' THEN score ELSE NULL END) AS score1,