Mysql 经典练习题
我使用的Mysql版本是5.7.27-log，答案可能会因版本会有少许出入。
一、数据源准备及说明：
1.1、数据字段说明:
1.学生表 Student(SId,Sname,Sage,Ssex)
SId ：学生编号
Sname：学生姓名
Sage ：出生年月
Ssex：学生性别
2.课程表 Course(CId,Cname,TId)
CId ：课程编号
Cname ：课程名称
TId ：教师编号
3.教师表 Teacher(TId,Tname)
TId ：教师编号
Tname ：教师姓名
4.成绩表 SC(SId,CId,score)
SId ：学生编号
CId ：课程编号
score： 分数
1.2、将数据导入数据库：
导入数据方法：
1、将以下 mysql 语句，完整复制到 workbench 语句窗口（或者是mysql 的黑窗口或者Navicat窗口也可以）
2、然后运行即可导入，不需要另外创建表，下面表的操作一样。
注释：这些语句第一条是创建表（create table），后面都是插入数据到表中（insert into table ）：
学生表 Student：
create table Student(SId varchar(10),Sname varchar(10),Sage datetime,Ssex 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('09' , '张三' , '2017-12-20' , '女');
insert into Student values('10' , '李四' , '2017-12-25' , '女');
insert into Student values('11' , '李四' , '2017-12-30' , '女');
insert into Student values('12' , '赵六' , '2017-01-01' , '女');
insert into Student values('13' , '孙七' , '2018-01-01' , '女');
科目表 Course:
create table Course(CId varchar(10),Cname nvarchar(10),TId varchar(10));
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');
教师表 Teacher:
create table Teacher(TId varchar(10),Tname varchar(10));
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');
成绩表 SC:
create table SC(SId varchar(10),CId varchar(10),score decimal(18,1));
insert into SC values('01' , '01' , 80);
insert into SC values('01' , '02' , 90);
insert into SC values('01' , '03' , 99);
insert into SC values('02' , '01' , 70);
insert into SC values('02' , '02' , 60);
insert into SC values('02' , '03' , 80);
insert into SC values('03' , '01' , 80);
insert into SC values('03' , '02' , 80);
insert into SC values('03' , '03' , 80);
insert into SC values('04' , '01' , 50);
insert into SC values('04' , '02' , 30);
insert into SC values('04' , '03' , 20);
insert into SC values('05' , '01' , 76);
insert into SC values('05' , '02' , 87);
insert into SC values('06' , '01' , 31);
insert into SC values('06' , '03' , 34);
insert into SC values('07' , '02' , 89);
insert into SC values('07' , '03' , 98);
二、练习题目：
1、查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数
分析：题意就是想查询所有学生中01课程分数比02课程分数高的学生有哪些？
思路：分别查出01和02的sid和分数，然后与student表内连接查询学生姓名，输出样式：Sid,Sname,score01,score02，然后用where条件a.score > b.score过滤一下即可实现查询结果。
SELECT a.sid
,st.Sname
,a.score
,b.score
FROM(SELECT SId
,score
FROM sc
WHERE CId = 01) AS a # 查询01课程的分数
INNER JOIN (SELECT SId
,score
FROM sc
WHERE CId = 02) AS b ON a.sid = b.sid # 查询02课程的分数
INNER JOIN student AS st ON st.SId = a.SId # 2个内连接查交集
WHERE a.score > b.score;
1.1、查询同时学过" 01 "课程和" 02 "课程的学生信息
思路：使用子查询，先分别查询出01课程和02课程的sid，然后将这两个表内连接，即是同时学01和02课程的sid，此时再与studen表中的sid匹配查到学生详细信息。
解法1：标量子查询
SELECT student.*
FROM student
WHERE student.SId IN (SELECT a.SId
FROM (SELECT sid
FROM sc
WHERE CId = "01") AS a
INNER JOIN (SELECT *
FROM sc
WHERE CId = "02") AS b ON a.SId = b.SId);
但这个查询结果仅仅只能看到学生信息，看不到学生各个课程的分数，输出样式不是最优，所以我们希望输出的样式是这样的：sid,sname,score01,score02,那么就可以如题1一样，使用多表连接对三个表做内连接，然后直接取需要的字段即可：
SELECT a.sid
,st.Sname
,a.score
,b.score
FROM(SELECT SId
,score
FROM sc
WHERE CId = 01) AS a
INNER JOIN (SELECT SId
,score
FROM sc
WHERE CId = 02) AS b ON a.sid = b.sid
INNER JOIN student AS st ON st.SId = a.SId;
1.2、查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )
分析：所谓"情况"，无非就是查这样一些人：必须学过01课课程，不一定学过02课程的学生是哪些，那既然知道了学生id，那很有可能想知道这些学生的信息，所以我们希望查询结果以这样的格式进行输出：sid,sname,score01,score02.
思路：多表连接的时候使用左连接即可，分别查询出学过01和02课程的sid和分数，然后将两个查询结果进行左连接，确保01课程的所有人都在，然后与student表左连接，这样就得到了想要查询结果了。
SELECT a.sid
,st.Sname
,a.score
,b.score
FROM(SELECT SId
,score
FROM sc
WHERE CId = 01) AS a
LEFT JOIN (SELECT SId
,score
FROM sc
WHERE CId = 02) AS b ON a.sid = b.sid
LEFT JOIN student AS st ON st.SId = a.SId;
1.3、查询不存在" 01 "课程但存在" 02 "课程的情况
思路：过滤掉cid = 01 课程的所有sid，再选取cid = 02的学生
解法1：使用子查询过滤掉学过01课程的学生，然后再选出那些学了02课程的学生，用AND将两个约束条件进行连接就可以得到最终的结果集了。
SELECT a.sid
,b.sname
,a.cid
,a.score
FROM(SELECT *
FROM sc
WHERE SId NOT IN (SELECT SId
FROM sc
WHERE CId='01') AND CId='02') AS a #查询满足要求的sid，cid，score
INNER JOIN student AS b ON a.sid = b.sid; # 查询对应的学生信息
解法2：虽然结果一样，但写法感觉别扭，再回头看时自己都忘了咋写出来的了
SELECT b.*
FROM(SELECT *
FROM sc
GROUP BY sid
HAVING cid != "01") AS a
INNER JOIN(SELECT *
FROM sc
WHERE cid = "02") AS b ON a.sid = b.sid
GROUP BY b.sid;
2、查询平均成绩大于等于 60 分的同学的学生编号、学生姓名、平均成绩
思路：内连接student表和sc表，然后按照sid分组计算平均成绩且过滤掉那些平均分小于60分的人
解法一：使用子查询，先查询出平均成绩大于等于60的学生的sid和平均分，然后与student表内连接获取学生姓名字段。
SELECT a.sid
,b.sname
,a.avg_score
FROM(SELECT sid
,AVG(score) AS avg_score
FROM sc
GROUP BY sid
HAVING AVG(score) >= 60) AS a
INNER JOIN student AS b ON a.sid = b.sid；
解法二：先对表sc和student内连接，然后按照sid分组计算后取值，这里需要注意的点是student和sc表是一对多的关系，进行内连接之后，新表里student的记录是自动补全的，如下图：
image.png
SELECT s.SId
,st.sname
,AVG(score)
FROM student AS st
INNER JOIN sc AS s ON st.SId = s.SId
GROUP BY s.SId,st.sname
HAVING AVG(score) >= 60;
3、查询在 SC 表存在成绩的学生信息
思路：使用子查询，先在sc表按sid去重看看sc表都有哪些sid，然后在student表中查询对应sid的学生信息即可
SELECT student.*
FROM student
WHERE sid IN (SELECT DISTINCT sid
FROM sc)
GROUP BY sid; # 以防万一再在student表中按sid group by去重一下。
4、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 0 )
思路：student和sc表左连接之后，按照sid、sname分组统计取需要字段即可
SELECT st.SId
,st.Sname
,COUNT(DISTINCT s.CId)
,SUM(CASE WHEN s.score IS NULL THEN 0 ELSE s.score END) # 这里用到的的case when是需要特别注意的技巧
FROM student AS st
LEFT JOIN sc AS s ON st.SId = s.SId
GROUP BY st.SId,st.Sname
ORDER BY st.SId
注意：student表和sc表进行做连接后的结果集如下：
image.png
4.1 、查有成绩的学生信息
思路：先看sc表中有成绩的sid是哪些，然后就可以根据sid查看学生信息了
解法一：使用子查询 IN
SELECT *
FROM student AS a
WHERE a.sid IN (SELECT DISTINCT sid
FROM sc
GROUP BY sid);
解法二：如上题，对student表和sc表左内连接之后，按照sid进行分组去重，得到学生信息
SELECT st.*
FROM student AS st
INNER JOIN sc AS s ON st.sid = s.sid
GROUP BY s.sid
ORDER BY s.sid
5、查询「李」姓老师的数量
思路：通配符和聚合函数的使用，这题就很简单，没什么说的
SELECT COUNT(*)
FROM teacher
WHERE Tname LIKE '李%'
6、查询学过「张三」老师授课的同学的信息
思路：多表连接或者子查询
解法一：使用子查询书写
SELECT *
FROM student
WHERE sid IN (SELECT sid
FROM sc
WHERE cid = (SELECT cid
FROM course
WHERE tid = (SELECT tid
FROM teacher
WHERE tname = "张三")));
解法二：直接内连接所有需要用到的表
SELECT st.SId
,st.Sname
,st.sage
,st.ssex
,s.cid
,s.score
,c.cname
,t.tid
,t.tname # 字段选取可根据需要去留
FROM student AS st
INNER JOIN sc AS s ON st.SId = s.SId
INNER JOIN course AS c ON c.CId = s.CId
INNER JOIN teacher AS t ON t.TId = c.TId
WHERE t.Tname = '张三';
查询结果
注意点：所有表内连接之后的结果集：可以看到是以数据量最多的表格为准，就知道有几行数据了
image.png
6.1、查询没学过张三老师课程的学生的学号和姓名
思路：所有学过张三老师课程的学生进行取反
SELECT *
FROM student
WHERE SId NOT IN(SELECT st.SId
FROM student AS st
INNER JOIN sc AS s ON st.SId = s.SId
INNER JOIN course AS c ON c.CId = s.CId
INNER JOIN teacher AS t ON t.TId = c.TId
WHERE t.Tname = '张三');
7、查询没有学全所有课程的同学的信息
思路：将student表和sc表进行左连接，这样不会漏掉一门课也没有学的同学，然后按照sid分组，统计分组后的同学的课程数量，如果小于总的课程数量，那么就是没学全
SELECT st.SId
,st.Sname
,COUNT(DISTINCT s.CId)
FROM student AS st
LEFT JOIN sc AS s ON st.SId = s.SId
GROUP BY st.SId
HAVING COUNT(DISTINCT s.CId) < (SELECT COUNT(DISTINCT CId)
FROM course);
8、查询至少有一门课与学号为" 01 "的同学所学课程相同的同学的信息
思路：子查询嵌套，先找01号学生的课程，再找包含在这里面的其他sid，然后查信息，需要注意的是最后还要排除01号这个sid。
解法一：使用子查询
SELECT SId
,Sname
FROM student
WHERE SId IN(SELECT SId
FROM sc
WHERE cid IN(SELECT cid
FROM sc
WHERE SId = 01)) AND SId <> 01;
解法二：外层student表和子查询结果表内连接，提升查询效率，具体如下：
注释：数据较多时，inner join查询效率比子查询高
select a.sid
,a.sname
FROM student AS a
INNER JOIN(SELECT distinct sid
FROM sc
WHERE cid in (SELECT cid
FROM sc
WHERE sid = "01") AND sid <> "01") AS b ON a.sid =b.sid;
解法三：使用多表连接查询的思路也是不错的，直接右连接student表和sc表（此题目中其实用什么类型的连接结果都是一样的），因为表关系是一对多，所以直接使用过滤条件去重查询，然后再去掉01号同学就可以了
SELECT distinct b.*
FROM sc a
LEFT JOIN student b
ON a.sid=b.sid
WHERE cid IN (SELECT cid
FROM sc
WHERE sid='01') AND a.sid != "01";
9、查询和" 01 "号同学学习的课程 完全相同的其他同学的信息。
思路：先查询出与01号同学所学课程完全不同的学生的sid，然后对其取反，那么得到的就是与01号同学所学课程至少有一门课程相同的学生，如果此时他们所学的课程数量又相等，那么该学生必然与01号同学所学课程是完全相同的
SELECT student.*
FROM student
WHERE SId IN(SELECT SId
FROM sc
WHERE SId <> 01 # 先过滤掉01号同学本身
GROUP BY SId
HAVING COUNT(DISTINCT CId) = (SELECT COUNT(DISTINCT CId) #计算01号同学所学课程数量
FROM sc
WHERE SId = 01) AND SId NOT IN(SELECT SId# 再取反排除这些与01号同学所学课程完全不同的学生
FROM sc # 对下级查询结果取反，得到哪些跟01号同学所学课程完全不同的学生编号
WHERE CId NOT IN(SELECT CId
FROM sc
WHERE SId = '01');#查01号同学学了哪几门课程的编号
-- 这题还是蛮有意思的，思路不错
10、查询没学过"张三"老师讲授的任一门课程的学生姓名
思路：查询出“张三”老师所教的课程id有哪些，然后看谁选了他教的课程，然后对其取反即可，就是那些一门都没有学习张三老师课程的学生信息。
SELECT *
FROM student
WHERE SId NOT IN(SELECT st.SId
FROM student AS st
INNER JOIN sc AS s ON st.SId = s.SId
INNER JOIN course AS c ON c.CId = s.CId
INNER JOIN teacher AS t ON t.TId = c.TId
WHERE t.Tname = '张三');
思考：假设使用子查询，先查询出张三老师所教课程的全部编号，然后在sc表中对这个子查询结果进行取反，然后根据sc表中的sid是否就可以查到学生的信息了呢？
答：不可以，因为在sc表中进行排除的时候，比如01号同学同时学些了01/02/03课程，使用where条件过滤掉02，实际上01号同学还是被选出来了。那用先分组再having过滤呢？（我的答案是不可以）
10.1、查询学过张三老师所教的所有课程的同学的学号和姓名
思路：只要明白多表连接之后的“表”是什么样子的就很容易写出答案来了
SELECT st.SId
,st.Sname
FROM student AS st
INNER JOIN sc AS s ON st.SId = s.SId
INNER JOIN course AS c ON c.CId = s.CId
INNER JOIN teacher AS t ON t.TId = c.TId
WHERE t.Tname = '张三';
11、查询两门及以上不及格课程的同学的学号，姓名及其平均成绩
思路：先查出成绩小于60的所有记录，然后按sid分组，统计课程数大于等于2的sid是哪些，然后内连接student和sc ，然后按sid，sname分组，计算分组后某个sid的平均分
SELECT st.sid
,st.sname
,AVG(s.score)
FROM student AS st
INNER JOIN sc AS s ON st.sid = s.sid
WHERE st.SId IN(SELECT SId
FROM sc
WHERE score < 60
GROUP BY SId
HAVING COUNT(DISTINCT CId) >= 2)
GROUP BY st.sid,st.sname;
12、检索" 01 "课程分数小于 60，按分数降序排列的学生信息
思路：直接内连接student和sc表，然后where过滤条件，按分数降序排列（子查询也可以实现，这里就不写具体代码了）
SELECT st.*
,s.CId
,s.score
FROM sc AS s
INNER JOIN student AS st ON s.SId = st.SId
WHERE s.CId = 01 AND s.score <60
ORDER BY s.score DESC;
13、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
思路：可以使用子查询，也可以使用case when
解法一：使用子查询，先查询出学生平均分，然后与sc表内连接，这样连接之后的表每一条数据后都有了平均分，然后排序就好了。
SELECT s.SId
,s.score
,a.avg_score
FROM sc AS s
INNER JOIN (SELECT SId
,AVG(score) AS avg_score
FROM sc
GROUP BY SId) AS a ON s.SId = a.SId
ORDER BY a.avg_score DESC;
值得注意的是：这样写结果是出来了，但显示方式不是很好(这里其最重要的作用是验证多表联结时，出现多对一的情况的时候，“一”会在后面自动补全)
推荐使用下面这种写法：展示样式为：sid-语文-数学-英语-平均分，更符合实际业务场景
SELECT SId
,MAX(CASE WHEN CId = 01 THEN score ELSE NULL END) AS 语文
,MAX(CASE WHEN CId = 02 THEN score ELSE NULL END) AS 数学
,MAX(CASE WHEN CId = 03 THEN score ELSE NULL END) AS 英语
,AVG(score) AS 平均分
FROM sc
GROUP BY SId
ORDER BY 平均分 DESC;
这里的case when用法不错，因为只有一个值，用max、avg等其实是一样的结果。
14、查询各科成绩最高分、最低分和平均分： 以如下形式显示：
- 课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率。
- 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
- 要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列。
思路：实际就是按照cid进行分组统计，查看每门课程的最大、最小、平均值，及相应的XX率。从题意得知需要用到的表为sc表和course表，所以直接将这两个表做一个内连接，然后进行取字段求值。
这里特别要注意的是case when的用法，当用sum进行人数统计的时候，需要分门别类的进行数量统计，所以就想到使用case when来进行分类汇总。
SELECT s.cid
,c.cname
,MAX(s.score)
,MIN(s.score)
,AVG(s.score)
,CONCAT(TRUNCATE(SUM(CASE WHEN s.score >= 60 THEN 1 ELSE 0 END)/COUNT(DISTINCT s.sid) * 100,2),"%") AS 及格率
,CONCAT(TRUNCATE(SUM(CASE WHEN s.score >= 70 AND s.score < 80 THEN 1 ELSE 0 END)/COUNT(DISTINCT s.sid),2) * 100,"%") AS 中等率
,CONCAT(TRUNCATE(SUM(CASE WHEN s.score >= 80 AND s.score < 90 THEN 1 ELSE 0 END)/COUNT(DISTINCT s.sid),2) * 100,"%") AS 优良率
,CONCAT(TRUNCATE(SUM(CASE WHEN s.score >= 90 THEN 1 ELSE 0 END)/COUNT(DISTINCT s.sid) * 100,2),"%") AS 优秀率
FROM sc AS s
INNER JOIN course AS c ON s.cid = c.cid
GROUP BY s.cid;
注意：还需要注意的是concat函数的用法和truncate的用法，与truncate（不进行四舍五入）用法类似的函数round（四舍五入）的区别。
区别实例
15、按各科成绩进行排序，并显示排名， Score 重复时名次空缺 （1224格式）
思路：用窗口函数，需Mysql8.0以上版本
SELECT CId
,RANK() OVER (PARTITION BY CId ORDER BY score DESC) AS rank_num
FROM sc;
15.1 按各科成绩进行排序，并显示排名， Score 重复时合并名次（假设是1223类型的排序）
分析：窗口函数，需Mysql8.0以上版本
SELECT CId
,score
,DENSE_RANK() over(PARTITION BY CId ORDER BY score DESC) AS rank
FROM sc；
16、查询学生的总成绩，并进行排名，总分重复时名次空缺 (假设按照1224)
解法一：窗口函数,需Mysql8.0以上版本
SELECT SId
,total_score
,RANK() OVER (ORDER BY total_score DESC) AS rank_num
FROM(SELECT SId
,SUM(score) AS total_score
FROM sc
GROUP BY SId) AS t;
解法二：定义变量：
SELECT SId
,sum_score
,@rank := IF(@score1 = sum_score,@rank,@rank + 1) AS rank_num
,@score1 := sum_score AS 总成绩 # 保存上一次的分数
FROM (SELECT SId
,SUM(score) AS sum_score
FROM sc
GROUP BY SId
ORDER BY SUM(score) DESC) AS a
JOIN (SELECT @rank := '',@score1 := '') AS b;
也可以用case when的语法：
将：
@rank := IF(@score1 = sum_score,@rank,@rank + 1) AS rank_num
替换成：
CASE WHEN @score1 = sum_score THEN @rank := @rank ELSE @rank := @rank + 1 END AS 名次。
跟用IF逻辑是一样的，都是做判断,具体如下：
SELECT SId
,sum_score
,CASE WHEN @score1 = sum_score THEN @rank := @rank ELSE @rank := @rank + 1 END AS 名次
,@score1 := sum_score AS 总成绩
FROM (SELECT SId
,SUM(score) AS sum_score
FROM sc
GROUP BY SId
ORDER BY SUM(score) DESC) AS a
JOIN (SELECT @rank := '',@score1 := '') AS b;
总结：
需要定义两个空变量，@rank变量用于排名，@score1变量用于与总成绩进行比较，从而得到排名，需要特别注意的是“”@score1 := sum_score AS 总成绩“”必须要写，因为这一步是将比较后的值赋给@score1，以便后续的比较排名。
16.1、 查询学生的总成绩，并进行排名，总分重复时名次不空缺（1223）
解法一：窗口函数：需Mysql8.0以上版本
SELECT SId
,total_score
,DENSE_RANK() OVER (ORDER BY total_score DESC) AS rank_num
FROM(SELECT SId
,SUM(score) AS total_score
FROM sc
GROUP BY SId) AS t;
解法二：定义变量：所谓空缺不空缺到底啥意思？这里我分两种情况写：
# 1.假设直接1234排序：结果同ROW_NUMBER()
SET @rank = 0;
SELECT SId
,total_score
,@rank := @rank + 1 AS rank_num
FROM (SELECT SId
,SUM(score) AS total_score
FROM sc
GROUP BY SId
ORDER BY SUM(score) DESC) AS t；
# 2.假设是1223的顺序：
SELECT SId
,sum_score
,CASE WHEN @score1 = sum_score THEN @rank := @rank ELSE @rank := @rank + 1 END AS 名次
,@score1 := sum_score AS 总成绩
FROM (SELECT SId
,SUM(score) AS sum_score
FROM sc
GROUP BY SId
ORDER BY SUM(score) DESC) AS a
JOIN (SELECT @rank := '',@score1 := '') AS b;
17、统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比
SELECT s.cid
,c.cname
,SUM(CASE WHEN s.score <= 100 AND s.score > 85 THEN 1 ELSE 0 END) AS "[100,85)"
,SUM(CASE WHEN s.score <= 85 AND s.score > 70 THEN 1 ELSE 0 END) AS "[85,70)"
,SUM(CASE WHEN s.score <= 70 AND s.score > 60 THEN 1 ELSE 0 END) AS "[70,60)"
,SUM(CASE WHEN s.score <= 60 THEN 1 ELSE 0 END) AS "(0,60]"
FROM sc AS s
INNER JOIN course AS c ON s.cid = c.cid
GROUP BY s.cid,c.cname;
18、查询各科成绩前三名的记录
解法一：窗口函数，需Mysql8.0以上版本
SELECT *
FROM(SELECT CId
,score
,ROW_NUMBER() OVER (PARTITION BY CId ORDER BY score DESC) AS rank_num
FROM sc) AS t
WHERE rank_num <= 3;
19、查询每门课程被选修的学生数
SELECT s.CId
,c.Cname
,COUNT(DISTINCT s.SId)
FROM sc AS s
INNER JOIN course AS c ON s.CId = c.CId
GROUP BY s.CId,c.Cname;
20、查询出只选修两门课程的学生学号和姓名
SELECT s.SId
,st.Sname
,COUNT(DISTINCT s.CId) AS cnt
FROM sc AS s
INNER JOIN student AS st ON s.SId = st.SId
GROUP BY s.SId,st.Sname
HAVING cnt = 2
也可以使用子查询，但性能可能较低
SELECT SId
,Sname
FROM student
WHERE SId IN (SELECT SId
FROM sc
GROUP BY SId
HAVING COUNT(DISTINCT CId) = 2);
21、查询男生、女生人数
SELECT Ssex
,COUNT(DISTINCT sid)
FROM student
GROUP BY Ssex;
用case when来实现的注意事项：
SELECT SUM(CASE WHEN Ssex = "男" THEN 1 else 0 END) AS '男生人数'
,SUM(CASE WHEN Ssex = "女" THEN 1 else 0 END) AS '女生人数'
FROM student;
需要注意的是如果用count，那么上面的0要改成null才行，因为null是不参与计算的，而0参与。
SELECT COUNT(CASE WHEN Ssex = "男" THEN 1 else NULL END) AS '男生人数'
,COUNT(CASE WHEN Ssex = "女" THEN 1 else NULL END) AS '女生人数'
FROM student;
# 如果将NULL修改为0的话，那么查询出来的结果无论男女数量都是12，因为0也被计入count的计算中了
22、查询名字中含有「风」字的学生信息
SELECT *
FROM student
WHERE Sname LIKE "%风%";
23、查询同名同性学生名单，并统计同名人数
解法一：按照姓名分组，姓名形同的情况下按照性别分组统计人数，如果统计人数大于等于2，那说明这个人就是同名同性的
SELECT Sname
,Ssex
,COUNT(Sname)
FROM student
GROUP BY Sname,Ssex
HAVING COUNT(Sname) >= 2;
解法二：自连接，过滤条件为性别相同，且sid不相同
SELECT st1.*
,COUNT(1) AS cons
FROM student AS st1
INNER JOIN student AS st2 ON st1.sname=st2.sname AND st1.ssex=st2.ssex AND st1.sid != st2.sid;
解法三：
select *
from student LEFT JOIN (select Sname
,Ssex,COUNT(*)同名人数
from Student
group by Sname,Ssex) as t1 on student.Sname =t1.Sname and student.Ssex=t1.Ssex
where t1.同名人数>1
24、查询 1990 年出生的学生名单
SELECT *
FROM student
WHERE YEAR(Sage) = 1990;
25、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
SELECT s.CId
,c.Cname
,AVG(score) AS avg_score
FROM sc AS s
INNER JOIN course AS c ON c.CId = s.CId
GROUP BY s.CId
,c.Cname
ORDER BY avg_score DESC,s.CId ASC;
26、查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
SELECT st.SId
,st.Sname
,AVG(s.score)
FROM sc AS s
INNER JOIN student AS st ON s.SId = st.SId
GROUP BY st.SId,st.Sname
HAVING AVG(s.score) >= 85;
27、查询课程名称为「数学」，且分数低于 60 的学生姓名和分数
SELECT st.SId
,st.Sname
,s.score
,c.Cname
FROM student AS st
INNER JOIN sc AS s ON st.SId = s.SId
INNER JOIN course AS c ON s.CId = c.CId
WHERE c.Cname = "数学" AND s.score < 60;
28、查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）
# 按实际需求希望表头如：sid sname 语文 数学 英语 进行展示
SELECT st.SId
,st.Sname
,MAX(CASE WHEN c.Cname = "语文" THEN s.score ELSE NULL END) AS "语文"
,MAX(CASE WHEN c.Cname = "数学" THEN s.score ELSE NULL END) AS "数学"
,MAX(CASE WHEN c.Cname = "英语" THEN s.score ELSE NULL END) AS "英语"
FROM sc AS s
INNER JOIN course AS c ON s.CId = c.CId
INNER JOIN student AS st ON s.SId = st.SId
GROUP BY st.SId
,st.Sname;
需要注意的是：本题如果只按sid，sname分组查询，成绩只能返回第一个，所以必须用case when进行分类后逐一进行判断然后返回最大值（当然这里用MIN其实也无所谓）
29、查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
思路：题意应该是想查询那些任何一门成绩都在70分以上的人的情况
SELECT s.SId
,st.Sname
,c.Cname
,s.score
FROM sc AS s
INNER JOIN course AS c ON s.CId = c.CId
INNER JOIN student AS st ON s.SId = st.SId
WHERE s.score > 70;
30、查询不及格的课程并按课程编号从大到小排列
思路：就是查询有哪些课程存在不及格的情况，是谁。
SELECT s.sid
,st.Sname
,s.cid
,c.cname
,s.score
FROM sc AS s
INNER JOIN student AS st ON s.SId = st.SId
INNER JOIN course AS c ON s.CId = c.CId
WHERE score < 60
ORDER BY s.CId DESC;
31、查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名
SELECT s.SId
,st.Sname
,s.CId
,c.Cname
,s.score
FROM sc as s
INNER JOIN student AS st ON s.SId = st.SId
INNER JOIN course AS c ON s.CId = c.CId
WHERE s.CId = "01" AND s.score > 80;
32、求每门课程的学生人数
SELECT s.CId
,c.Cname
,COUNT(DISTINCT SId)
FROM sc AS s
INNER JOIN course AS c ON s.CId = c.CId
GROUP BY s.CId;
33、成绩不重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
分析：成绩不重复，排序后取第一个或者直接取MAX（score）就可以了
SELECT s.SId
,st.Sname
,s.score
,s.CId
,t.Tname
FROM sc AS s
INNER JOIN student as st ON s.SId = st.SId
INNER JOIN course AS c ON c.CId = s.CId
INNER JOIN teacher AS t ON t.TId = c.TId
WHERE t.Tname = "张三"
ORDER BY s.score DESC
LIMIT 1;
或者
SELECT s.SId
,st.Sname
,MAX(s.score)
,s.CId
,t.Tname
FROM sc AS s
INNER JOIN student as st ON s.SId = st.SId
INNER JOIN course AS c ON c.CId = s.CId
INNER JOIN teacher AS t ON t.TId = c.TId
WHERE t.Tname = "张三";
34、成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
分析：成绩重复的情况下，那么第一名有可能是多个人，这时候用limit限制就不行了，所以使用窗口函数中的RANK()是完美的解决办法。
SELECT * #3.1.查看第一名的信息
FROM(SELECT *
,RANK() OVER (ORDER BY score desc) AS rank_num #2.对子查询结果进行排名
FROM(SELECT st.SId #1.先把学张三老师课的人找出来
,st.Sname
,s.score
FROM student AS st
INNER JOIN sc AS s ON st.SId = s.SId
INNER JOIN course AS c ON c.CId = s.CId
INNER JOIN teacher AS t ON t.TId = c.TId
WHERE t.Tname = '张三')AS x)AS y
WHERE rank_num = 1; #3.2. 筛选排名第一的
35、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
思路：意思是查询哪些同学每门课程成绩都一样（这里是我的理解），所以先查询所学课程总数大于1的学生id，然后与sc表做内连接，再按sid和score的组合进行分组统计，如果几率条数只有一条，那么就意味着这位同学的不同科目的成绩都是一样的。
SELECT *
FROM(SELECT a.sid
,a.score
,b.cnt
FROM sc AS a
INNER JOIN(SELECT SId #查询课程数大于1的学生id
,COUNT(DISTINCT CId) AS cnt
FROM sc
GROUP BY SId
HAVING cnt > 1) AS b ON a.sid = b.sid
GROUP BY a.sid
,a.score) AS c
GROUP BY sid
HAVING COUNT(sid) = 1;
这里需要特别注意的是：group by 之后的字段的分组明细，必须所有字段值都一样才会输出一条记录，否则就是多条记录。
36、查询每门课程成绩最好的前两名
分析：前两名，如果碰到成绩一样的情况，按照实际需求，应该前两名包含不止2人才对，是名次排在第一和第二的都要包含进去才符合实际，所以这里我用rank（）
# 使用窗口函数
SELECT *
FROM(SELECT CId
,score
,RANK() OVER (PARTITION BY CId ORDER BY score DESC) AS rank_num
FROM sc) AS t
WHERE rank_num <= 2;
37、统计每门课程的学生选修人数（超过 5 人的课程才统计）,要求输出课程号和选修人数，查询结果按照人数降序，若人数相同，按照课程号升序
SELECT CId
,COUNT(DISTINCT SId) AS cnt
FROM sc
GROUP BY CId
HAVING cnt >= 5
ORDER BY cnt DESC
,CId ASC;
38、检索至少选修两门课程的学生学号
SELECT SId
,COUNT(DISTINCT CId) AS cnt
FROM sc
GROUP BY SId
HAVING cnt >= 2;
39、查询选修了全部课程的学生信息
SELECT SId
,COUNT(DISTINCT CId) AS cnt
FROM sc
GROUP BY SId # 按照sid分组之后count课程数量
HAVING cnt = (SELECT COUNT(DISTINCT Cid)
FROM course);
40、查询各学生的年龄，只按年份来算
解法一：用DATEDIFF函数，计算出生到现在的时间天数，然后除以365得到年份，然后用FLOOR函数向下取整得到最终的年龄
SELECT student.*
,FLOOR(DATEDIFF(NOW(),Sage)/365) AS "年龄"
FROM student;
总结：
DATEDIFF() 函数返回两个日期之间的天数。
FLOOR(X)根据官方文档的提示,floor函数返回小于等于该值的最大整数.
解法二：用YEAR()函数和NOW()函数进行相减
SELECT student.*
,YEAR(now())-YEAR(sage) AS age
FROM student;
41、按照出生日期来算，当前月日 < 出生年月的月日则年龄减一
分析：比如01号同学，当前日期减去出生日期为30.9年，这取30.
# 这里 timestampdiff 会用年月日去计算 年 的相隔时间，
如果相差1.9年则为1年，所以实际上是已经相减了的，正好用来计算生日
SELECT SId AS 学生编号
,Sname AS 学生姓名
,TIMESTAMPDIFF(YEAR,Sage,CURDATE()) AS 学生年龄
FROM student;
总结：SELECT NOW(),CURDATE(),CURTIME()的区别：
image.png
42、查询本周过生日的学生
SELECT sid
,sname
,YEARWEEK(sage)
,YEARWEEK(NOW())
FROM student
WHERE YEARWEEK(sage) = YEARWEEK(NOW());
小结：
YEARWEEK(date, mode)
返回年份及第几周（0到53），mode为可选参数，其中 中 0 （默认参数）表示从周天开始，1表示周一开始，以此类推：
SELECT YEARWEEK("2017-06-15"); -> 201724
select WEEK('2019-07-11',1);
返回值是28
select YEARWEEK('2019-07-11',1);
返回值是201928
43、查询下周过生日的学生
解法一：用YEARWEEK()函数
SELECT sid
,sname
,YEARWEEK(sage)
,YEARWEEK(NOW())
FROM student
WHERE YEARWEEK(sage) = YEARWEEK(NOW()) + 1；
解法二：
SELECT sid
,sname
,EXTRACT(week FROM sage) as sweek
,EXTRACT(week FROM curdate()) as nweek
FROM student
WHERE EXTRACT(week FROM sage) = EXTRACT(week FROM curdate()) + 1;
小结：extract()函数的用法
EXTRACT() 函数用于返回日期/时间的单独部分，比如年、月、日、周、小时、分钟等等。
image.png
44、查询本月过生日的学生
解法一：判断月份是否相等
SELECT sid
,sname
,MONTH(sage) AS "生日"
,MONTH(NOW())
FROM student
WHERE MONTH(sage) = MONTH(NOW());
解法二：EXTRACT()函数获取月份时间，判断是否相等
SELECT *
FROM student
WHERE EXTRACT(MONTH FROM Sage) = EXTRACT(MONTH FROM CURDATE())；
45、查询下月过生日的学生
解法一：
SELECT sid
,sname
,MONTH(sage) AS "生日"
,MONTH(NOW())
FROM student
WHERE MONTH(sage) = MONTH(NOW()) + 1;
解法二：EXTRACT()函数获取月份时间，DATE_ADD()函数计算下月时间
SELECT *
FROM student
WHERE EXTRACT(MONTH FROM Sage) = EXTRACT(MONTH FROM DATE_ADD(CURDATE(),INTERVAL 1 MONTH))；
小结：DATE_ADD()函数
定义和用法
DATE_ADD() 函数向日期添加指定的时间间隔。
语法
DATE_ADD(date,INTERVAL expr type)
date： 参数是合法的日期表达式
expr ：参数是您希望添加的时间间隔
type ：参数可以是年、月、日、周、小时、分钟等等。
实例
说明：只为记录个人学习过程，欢迎志同道合的朋友一起交流进步
网友评论