这50题是网上经典的SQL题目，先尝试自己写，然后与老师的写法做对比。在这个过程中我会把我犯的错，以及把老师的写法与我的写法都贴上来，当然很有可能存在考虑不严谨的地方，希望大家多多指正。另外本人是做产品的，非技术出身，且本系列文章是初次学习手稿，如果犯了比较低级的错误，轻喷~ 哈哈
该系列文章我会分五篇发出，每篇10题。
下面是建表语句：
CREATE TABLE `Student`(
`s_id` VARCHAR(20),
`s_name` VARCHAR(20) NOT NULL DEFAULT ”,
`s_birth` VARCHAR(20) NOT NULL DEFAULT ”,
`s_sex` VARCHAR(10) NOT NULL DEFAULT ”,
PRIMARY KEY(`s_id`)
);
CREATE TABLE `Course`(
`c_id` VARCHAR(20),
`c_name` VARCHAR(20) NOT NULL DEFAULT ”,
`t_id` VARCHAR(20) NOT NULL,
PRIMARY KEY(`c_id`)
);
CREATE TABLE `Teacher`(
`t_id` VARCHAR(20),
`t_name` VARCHAR(20) NOT NULL DEFAULT ”,
PRIMARY KEY(`t_id`)
);
CREATE TABLE `Score`(
`s_id` VARCHAR(20),
`c_id` VARCHAR(20),
`s_score` INT(3),
PRIMARY KEY(`s_id`,`c_id`)
);
下面是插入数据的语句：
# 插入Student表数据
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表数据
INSERT INTO Course VALUES(’01’ , ‘语文’ , ’02’);
INSERT INTO Course VALUES(’02’ , ‘数学’ , ’01’);
INSERT INTO Course VALUES(’03’ , ‘英语’ , ’03’);
#插入Teacher表数据
INSERT INTO Teacher VALUES(’01’ , ‘张三’);
INSERT INTO Teacher VALUES(’02’ , ‘李四’);
INSERT INTO Teacher VALUES(’03’ , ‘王五’);
#插入Score表数据
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);
下面是1-10题的学习分享：
第1题： 查询课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号
第一种写法
SELECT a.s_id 学号,c.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
JOIN
(SELECT s_id,c_id,s_score FROM score WHERE c_id = ’02’) AS b
JOIN Student c
ON a.s_id = b.s_id AND b.s_id=c.s_id
WHERE a.s_score > b.s_score;
第二种写法
SELECT a.s_id 学号,c.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
JOIN
(SELECT s_id,c_id,s_score FROM score WHERE c_id = ’02’) AS b
ON a.s_id = b.s_id
JOIN Student c ON a.s_id=c.s_id
WHERE a.s_score > b.s_score;
第2题： 查询平均成绩大于60分的学生的学号和平均成绩
SELECT s_id 学号,AVG(s_score) 平均成绩
FROM score
GROUP BY s_id
HAVING AVG(s_score)>60
ORDER BY AVG(s_score);
第3题： 查询所有学生的学号、姓名、选课数、总成绩
SELECT a.s_id 学号,b.s_name 姓名,a.选课数,a.总成绩 FROM
(SELECT s_id,COUNT(*) 选课数,SUM(s_score) 总成绩FROM score
GROUP BY s_id ) AS a JOIN student b ON a.s_id = b.s_id;
/*以上写法（自己第一次写的）有几个问题
1.使用的是内连接，若有的学生没有选课，那么就会少统计学生，所以需要使用外连接
2.若有学生未选课则总成绩可能会显示null值
3.写的有些复杂，也许可以先把score与student表先外连接后再做相关查询，而不是先查询后再做连接
*/
老师的写法
SELECT b.s_id 学号,b.s_name 姓名,COUNT(a.c_id) 选课数, IFNULL(SUM(a.s_score),0) 总成绩 FROM
score a RIGHT JOIN student b ON a.`s_id` = b.`s_id`
GROUP BY b.s_id
/*
在写上面sql的过程中 出现了如下几个问题
SELECT b.s_id 学号,b.s_name 姓名,COUNT(*) 选课数, IFNULL(SUM(a.s_score),0) 总成绩 FROM
score a RIGHT JOIN student b ON a.s_id = b.s_id
GROUP BY b.s_id
COUNT(a.c_id)写成 count(*),此时王菊的选课数会是1，因为把null那一行给统计到了
（在进行右连接的时候，王菊的s_score、c_id均为null 也就意味着有这么一行数据，所以count（*）会统计进来）
因此在写此类sql时，最好还是按照需要查询的字段进行聚合。
2.按照从表的s_id分组聚合了，且又查询了主表的s_name字段，所以此处应该用b.s_id聚合
SELECT b.s_id 学号,b.s_name 姓名,COUNT(a.c_id) 选课数, IFNULL(SUM(a.s_score),0) 总成绩 FROM
score a RIGHT JOIN student b ON a.s_id = b.s_id
GROUP BY a.s_id
*/
第4题：查询姓猴的老师个数
SELECT COUNT(*) FROM teacher
WHERE t_name LIKE ‘猴%’;
补充：有时候统计需要排除重复值，则需要用到关键字 distinct
SELECT COUNT(DISTINCT t_name)
FROM teacher
WHERE t_name LIKE ‘张%’;
/*
此题错误点是like的用法 把% 写成 * 了，正则表达式中的任意字符是用“*”，
而mysql中like 是用%表示通配符，用_表示单一字符
SELECT COUNT(*) FROM teacher
WHERE t_name LIKE ‘猴*’;
*/
第5题：查询没有学过张三老师课的学生学号和姓名（搞了很久没搞对）
以下是自己写的sql，有些混乱复杂 且写了很久 并且看了视频后才写出来
SELECT s_id 学号,s_name 姓名 FROM student
WHERE s_name NOT IN
(SELECT s_name FROM
(SELECT a.`t_id`,a.`c_id` FROM
course a LEFT JOIN teacher b ON a.`t_id`=b.`t_id`
WHERE b.`t_name`=”张三”
)c
LEFT JOIN (SELECT score.`c_id`, student.`s_name` FROM score LEFT JOIN student ON score.`s_id`=student.`s_id`) d
ON c.c_id = d.`c_id` )
以下是老师写的sql
SELECT s_id 学号, s_name 姓名 FROM student
WHERE s_id NOT IN
(
SELECT s_id FROM score AS s
INNER JOIN course AS c ON s.c_id = c.c_id
INNER JOIN teacher AS t ON c.t_id = t.t_id
WHERE t.t_name = “张三” )
以上代码使用not in 并不是使用的 where c_id != “02” 因为这样只是排除了02 这一行，再仔细思考
第6题：查询学过张三老师所教的所有课的同学的学号和姓名
SELECT stu.s_id 学号, stu.s_name 姓名 FROM teacher t
LEFT JOIN course c ON t.t_id = c.t_id
LEFT JOIN score s ON c.c_id = s.c_id
LEFT JOIN student stu ON s.`s_id` = stu.`s_id`
WHERE t.t_name = “张三”
第7题：查询学过编号为“01”的课程并且也学过编号为“02”的课程的学生的学号、姓名 stu.s_id,stu.s_name
SELECT stu.s_id,stu.s_name FROM student stu
LEFT JOIN score s ON stu.s_id = s.s_id
WHERE s.c_id = ’01’ OR s.c_id = ’02’
GROUP BY stu.s_name,stu.s_id # group by 后放多个字段的含义就是把多个字段当成一个整体进行分组
HAVING COUNT(*)= 2;
/*以上是我自己写的，但是用时很久，其中出现的错误有：
- 只按照 一个字段进行group by，但是却在select中查询2个字段，这种写法语法错误。
所有后面把两个字段都加上了 。
SELECT stu.s_id,stu.s_name FROM student stu
LEFT JOIN score s ON stu.s_id = s.s_id
WHERE s.c_id = ’01’ OR s.c_id = ’02’
GROUP BY stu.s_name
HAVING COUNT(*)= 2
2.where条件写成了WHERE s.c_id = ’01’ and s.c_id = ’02’ 错误的原因是：
同一行的数据同一个字段 不可能有2个值（即2个单元格）。这种写法不报错，但是查询出来的结果是空值
SELECT stu.s_id,stu.s_name FROM student stu
LEFT JOIN score s ON stu.s_id = s.s_id
WHERE s.c_id = ’01’ AND s.c_id = ’02’
GROUP BY stu.s_id,stu.s_name
HAVING COUNT(*)= 2
第7题 老师的写法
SELECT s_id,s_name 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”的总成绩
SELECT SUM(s_score) FROM score
WHERE c_id = “02”;
第9题：查询所有课程成绩小于60分的学生的学号、姓名
SELECT stu.s_id,stu.s_name
LEFT FROM student stu JOIN score s
ON stu.`s_id` = s.`s_id`
WHERE s.`s_score` < 60
GROUP BY stu.s_id,stu.s_name
HAVING COUNT(*)= 3;
/*自己的做法如上,是存在问题的：并不是所有的学生都学习了3门课程，
所以COUNT(*)= 3是错误的，这就默认了每个学生都学习了3门课程。
*/
根据弹幕提示，修改如下
SELECT stu.s_id,stu.s_name
FROM student stu LEFT JOIN score s
ON stu.`s_id` = s.`s_id`
GROUP BY stu.s_id
HAVING MAX(s.`s_score`)< 60;
/*注意后期发现 王菊 是没参加课程的 所以存在null值，
但是在max运算的时候忽略了她，也许此处可以用上ifnull,优化如下
SELECT stu.s_id,stu.s_name
FROM student stu LEFT JOIN score s
ON stu.`s_id` = s.`s_id`
GROUP BY stu.s_id
HAVING ifnull(MAX(s.`s_score`),0)< 60;
其实我还有个问题,我上面的sql语句，如果按照stu.s_name来groupby就会报错呢，
但是按照stu.s_id 就不会报错，
当然按照stu.s_id ，stu.s_name也不会报错。
出现这种情况的原因是因为 s_id这个是主键？
*/
第10题：查询没有学全所有课的学生的学号、姓名
SELECT stu.s_id,stu.s_name
FROM student stu LEFT JOIN score s # 注意需要使用左连接，如果直接使用join会漏掉王菊
ON stu.`s_id` = s.`s_id`
GROUP BY stu.s_id
HAVING COUNT(DISTINCT c_id)< 3;#注意使用distinct 是为了排除一门课有多个结果的情况
严格的来说 没有学完所有课不应该直接写个3 而是由course来汇总得到的
所以sql可以改为：
SELECT stu.s_id,stu.s_name
FROM student stu LEFT JOIN score s
ON stu.`s_id` = s.`s_id`
GROUP BY stu.s_id
HAVING COUNT(DISTINCT c_id)< (select count(*) from course);
