练习 MySQL 经典题

超经典MySQL练习50题，做完这些你的SQL就过关了

IDE: IntellJ IDEA (可快速生成 Markdown)

---

第一题

SELECT 学生.s_id AS 编号, 学生.s_name AS 姓名, 学生.s_birth AS 生日, 学生.s_sex AS 性别, 成绩表01.s_score AS 课程01分数, 成绩表02.s_score AS 课程02分数FROM Student 学生JOIN Score 成绩表01ON 学生.s_id = 成绩表01.s_id AND 成绩表01.c_id = 01JOIN Score 成绩表02ON 学生.s_id = 成绩表02.s_id AND 成绩表02.c_id = 02WHERE 成绩表01.s_score > 成绩表02.s_score;

编号姓名生日性别课程01分数课程02分数02钱电1990-12-21男706004李云1990-08-06男5030

---

第二题

SELECT 学生.s_id AS 编号, 学生.s_name AS 姓名, 学生.s_birth AS 生日, 学生.s_sex AS 性别, 成绩表01.s_score AS 课程01分数, 成绩表02.s_score AS 课程02分数FROM Student 学生JOIN Score 成绩表01ON 学生.s_id = 成绩表01.s_id AND 成绩表01.c_id = 01JOIN Score 成绩表02ON 学生.s_id = 成绩表02.s_id AND 成绩表02.c_id = 02WHERE 成绩表01.s_score < 成绩表02.s_score;

编号姓名生日性别课程01分数课程02分数01赵雷1990-01-01男809005周梅1991-12-01女7687

---

第三题

SELECT 学生.s_id AS 编号, 学生.s_name AS 姓名, ROUND(AVG(成绩表.s_score), 2) as 平均成绩FROM Student 学生JOIN Score 成绩表ON 学生.s_id = 成绩表.s_idGROUP BY 学生.s_idHAVING 平均成绩 > 60;

编号姓名平均成绩01赵雷89.6702钱电70.0003孙风80.0005周梅81.5007郑竹93.50

附加题

SELECT 学生.s_id AS 编号, 学生.s_name AS 姓名, SUM(成绩表.s_score) as 总分FROM Student 学生JOIN Score 成绩表ON 学生.s_id = 成绩表.s_idGROUP BY 学生.s_idHAVING 总分 > 200;

编号姓名总分01赵雷26902钱电21003孙风240

---

第四题

SELECT 学生.s_id AS 编号, 学生.s_name AS 姓名, ROUND(AVG(成绩表.s_score), 2) as 平均成绩FROM Student 学生LEFT JOIN Score 成绩表 # 用 LEFT JOIN 才能取出平均成绩为 NULL 的学生ON 学生.s_id = 成绩表.s_idGROUP BY 学生.s_idHAVING 平均成绩 < 60 OR 平均成绩 IS NULL;

编号姓名平均成绩04李云33.3306吴兰32.5008王菊null

---

第五题

SELECT 学生.s_id AS 编号, 学生.s_name AS 姓名, COUNT(成绩表.c_id) AS 选课总数, SUM(成绩表.s_score) AS 总成绩FROM Student 学生LEFT JOIN Score 成绩表ON 学生.s_id = 成绩表.s_idGROUP BY 学生.s_id;

编号姓名选课总数总成绩01赵雷326902钱电321003孙风324004李云310005周梅216306吴兰26507郑竹218708王菊0null

---

第六题

SELECT COUNT(老师.t_name) AS 个数FROM Teacher 老师WHERE 老师.t_name LIKE "李%";

个数1

---

第七题
因为张三老师教数学，这题换句话说，也就是在问哪些同学有修过数学，数学的 c_id 是 02，如果张三教多堂课就不适用了!

SELECT 学生.s_id AS 编号, 学生.s_name AS 姓名, 学生.s_birth AS 生日, 学生.s_sex AS 性别FROM Student 学生JOIN Score 成绩表ON 学生.s_id = 成绩表.s_idWHERE 成绩表.c_id = 02;

编号姓名生日性别01赵雷1990-01-01男02钱电1990-12-21男03孙风1990-05-20男04李云1990-08-06男05周梅1991-12-01女07郑竹1989-07-01女

---

第八题

SELECT 学生.s_id AS 编号, 学生.s_name AS 姓名, 学生.s_birth AS 生日, 学生.s_sex AS 性别FROM Student 学生WHERE 学生.s_id NOT IN (    SELECT 成绩表.s_id    FROM Score 成绩表    JOIN Course 课程    ON 成绩表.c_id = 课程.c_id    WHERE 课程.t_id = (        SELECT 老师.t_id        FROM Teacher 老师        WHERE 老师.t_name LIKE "张%"        ));

编号姓名生日性别06吴兰1992-03-01女08王菊1990-01-20女

---

第九题

SELECT 学生.s_id AS 编号, 学生.s_name AS 姓名, 学生.s_birth AS 生日, 学生.s_sex AS 性别FROM Student 学生JOIN Score 成绩表01ON 学生.s_id = 成绩表01.s_id AND 成绩表01.c_id = 01JOIN Score 成绩表02ON 学生.s_id = 成绩表02.s_id AND 成绩表02.c_id = 02

编号姓名生日性别01赵雷1990-01-01男02钱电1990-12-21男03孙风1990-05-20男04李云1990-08-06男05周梅1991-12-01女

---

第十题
MySQL 没有差集语法 (╯•̀ὤ•́)╯

SELECT 学生.s_id AS 编号, 学生.s_name AS 姓名, 学生.s_birth AS 生日, 学生.s_sex AS 性别FROM Student 学生, Score 成绩表01WHERE 学生.s_id = 成绩表01.s_id AND 成绩表01.c_id = 01 AND 成绩表01.s_id NOT IN (    SELECT 成绩表02.s_id    FROM Student 学生, Score 成绩表02    WHERE 学生.s_id = 成绩表02.s_id AND 成绩表02.c_id = 02);

编号姓名生日性别06吴兰1992-03-01女