我使用的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的记录是自动补全的，如下图：
```
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表进行做连接后的结果集如下：
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 = '张三';
```
注意点：所有表内连接之后的结果集：可以看到是以数据量最多的表格为准，就知道有几行数据了
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()的区别：
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() 函数用于返回日期/时间的单独部分，比如年、月、日、周、小时、分钟等等。
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 ：参数可以是年、月、日、周、小时、分钟等等。
说明：只为记录个人学习过程，欢迎志同道合的朋友一起交流进步
扫一扫
2万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
