MySQL50题

MySQL50题练习

建表语句：

– 学生表

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)

);

插入数据：

– 插入学生表测试数据

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' , '女');

– 课程表测试数据

insert into Course values('01' , '语文' , '02');

insert into Course values('02' , '数学' , '01');

insert into Course values('03' , '英语' , '03');

– 教师表测试数据

insert into Teacher values('01' , '张三');

insert into Teacher values('02' , '李四');

insert into Teacher values('03' , '王五');

– 成绩表测试数据

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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128

1.查询“01”课程比“02”课程成绩高的学生的信息及课程分数

SELECT s.s_id, s.s_name,s.`s_birth`,s.`s_sex`, sc1.s_score , sc2.s_score 
FROM Student s
JOIN Score sc1 ON s.s_id = sc1.s_id AND sc1.c_id = '01'
JOIN Score sc2 ON s.s_id = sc2.s_id AND sc2.c_id = '02'
WHERE sc1.s_score > sc2.s_score;
1
2
3
4
5

这个查询语句的逻辑是:

从学生表中查询学生的id和姓名

通过内连接联合学生表和成绩表分别获取他们在01课程和02课程的成绩,连接条件是学生表的id 和课程表的学生id相等,并且 课程的id=01获取1次,等于02获取1次

再通过where条件判断来两次获取出来的成绩进行比较

2.查询“01”课程比“02”课程成绩低的学生的信息及课程分数

#同理,只需要改变符号即可
SELECT s.s_id, s.s_name,s.`s_birth`,s.`s_sex`, sc1.s_score, sc2.s_score 
FROM Student s
JOIN Score sc1 ON s.s_id = sc1.s_id AND sc1.c_id = '01'
JOIN Score sc2 ON s.s_id = sc2.s_id AND sc2.c_id = '02'
WHERE sc1.s_score < sc2.s_score;
1
2
3
4
5
6

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

SELECT s.s_id, s.s_name, AVG(sc.s_score) AS avg_score #4.分组后进行成绩计算平均
FROM Student AS s #1.学生表作为基础数据集
LEFT JOIN Score AS sc ON s.s_id = sc.s_id #2.左连接创建中间结果集,带有学生成绩
GROUP BY s.s_id, s.s_name #3.按照学生id和学生姓名进行分组
HAVING avg_score >= 60;  #5.把平均结果进行筛选

1
2
3
4
5
6

3.1总分超过 200 分 的 同 学

SELECT s.s_id, s.s_name, SUM(sc.s_score) AS total_score #4.分组之后使用sum进行统计每一组总分
FROM Student AS s #以学生表作为主表
LEFT JOIN Score AS sc ON s.s_id = sc.s_id #通过id左连接分数表
GROUP BY s.s_id, s.s_name #3.通过id和姓名进行分组
HAVING total_score > 200; # 最后使用having筛选出总分200以上
1
2
3
4
5

4.查询平均成绩小于 60 分的同学的学生编号和学生姓名和平均成绩（包括有成绩的和无成绩的）

#方法1,确定查询的内容  成绩和姓名,确定表 就分数表和学生表
SELECT  s.s_id, s.s_name, IFNULL(AVG(sc.`s_score`),0) AS avg_score #3.分组之后在查询语句进行统计平局成绩,使用IFULL函数的话返回成绩为null的话就返回后者0
FROM student AS s
LEFT JOIN score AS sc ON s.`s_id` =sc.`s_id` #1.因为包括无成绩的所有要使用左连接连接学生表
GROUP BY s.`s_id`,s.`s_name` #2.然后对连接后的表根据id和姓名进行分组
HAVING avg_score <60 ; #4.最后使用having筛选
1
2
3
4
5
6

#方法二
#先子查询成绩表的分数再联合学生表查询姓名等
SELECT s.s_id, s.s_name, IFNULL(avg_score.avg_score, 0) AS avg_score #因为是左联合返回的是左表结果及学生表所以就能查出空值然后返回0
FROM Student AS s
LEFT JOIN (
    SELECT s_id, AVG(s_score) AS avg_score#把每一个人的平均成绩查询出来
    FROM Score
    GROUP BY s_id #1.在分数表对学生学号进行分组查询
) AS avg_score ON s.s_id = avg_score.s_id #2.把平均成绩作为结果集被学生表进行左连接进行查询
HAVING avg_score < 60;#通过条件查询having函数比60小的
1
2
3
4
5
6
7
8
9
10

5.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

-- 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
# 要用到的表有学生表和成绩表,而学生表作为主表进行左连接分数表
#使用左联合 查询返回的是学生表的行 即每一个学生
#按照 学生id进行分组
# 分组之后进行 count统计课程数量和总成绩
SELECT  s.`s_id`,s.`s_name`,COUNT(sc.`c_id`) AS counts ,SUM(sc.`s_score`) AS total_sc #编号 姓名 选课总数 总成绩 #count统计课程总数,sum统计总成绩
FROM student AS s #主表
LEFT JOIN Score AS sc ON s.`s_id`=sc.`s_id` #根据id左连接成绩表
GROUP BY s.`s_id`,s.`s_name` #group by进行id 和姓名 分组
1
2
3
4
5
6
7
8
9

6.查询“李”姓老师的数量

-- 6.查询“李”姓老师的数量
# %表示一个或多个字符
# _表示一个字符
SELECT COUNT(*)  AS li_number FROM teacher #count统计个数
WHERE `t_name` LIKE '李%' #where条件查询配合like匹配查询函数 和%号通配符查询
1
2
3
4
5

7.查询学过张三老师授课的同学的信息

-- 7.查询学过张三老师授课的同学的信息
# 要查询到张三老师的id先
# 通过老师的id 查询到教授的课程id
#然后通过id进行查询分数表对应的学生id
#最后通过id 查询学生表
SELECT * FROM student
WHERE student.`s_id` IN #4.最后把学生id作为结果集被学生表进行包含查询,查询到有这些id就行
(SELECT s_id FROM  score 
WHERE score.`c_id` = #3.课程id用作分数表的查询条件查询出学生id
(SELECT c_id FROM course 
WHERE course.`t_id` = #2.然后把老师id的结果集用来查询课程id
(SELECT t_id FROM teacher
WHERE t_name LIKE '张三'))) #1.查询出张三老师的老师id


-- 联合查询更加高效
# 首先确定要查询的表有哪些哪个作为主表: 学生表 课程表 教师表
# 联合的顺序从主表开始,找出主表和有外键约束的列的表 这里是学生表连接分数表
# 联合了分数表之后的结果集作为副表连接课程表 因为有外键约束c_id
# 最后连接教师表
# 最后通过筛选出课程老师姓名为张三字段
# 从结果集中筛选出不重复的行
SELECT DISTINCT s.s_id, s.s_name #DISTINCT进行去重
FROM Student AS s
JOIN Score AS sc ON s.s_id = sc.s_id #分数表有课程id
JOIN Course AS c ON sc.c_id = c.c_id #课程表有老师id
JOIN Teacher AS t ON c.t_id = t.t_id 
WHERE t.t_name = '张三'; #筛选出带有张三的行
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

8.找出没有学过张三老师课程的学生

-- 8.找出没有学过张三老师课程的学生
# 使用子查询和NOT IN操作符
#查询出包含张三老师的学生表然后作为结果集,被整个学生表进行NOT IN判断
SELECT DISTINCT * FROM student AS s
WHERE s.`s_id` NOT IN (
SELECT DISTINCT s.s_id
FROM Student AS s
JOIN Score AS sc ON s.s_id = sc.s_id
JOIN Course AS c ON sc.c_id = c.c_id
JOIN Teacher AS t ON c.t_id = t.t_id
WHERE t.t_name = '张三');
1
2
3
4
5
6
7
8
9
10
11

9.查询学过编号为 01，并且学过编号为 02 课程的学生信息

-- 9.查询学过编号为 01，并且学过编号为 02 课程的学生信息
# 两个条件都成立 使用and连接
SELECT s.s_id, s.s_name,s.`s_sex`,s.`s_birth`
FROM student AS s 
JOIN score AS sc1 ON sc1.`s_id` =s.`s_id` #连接成绩表得到副表1,目的筛选出学过编号为 "01" 课程的学生的学号
JOIN score AS sc2 ON sc2.`s_id`=s.`s_id` #再次连接成绩表得到副表2,目的含有学过编号为 "02" 课程的学生的学号
WHERE sc1.`c_id`=01 AND sc2.`c_id`=02 #筛选两个都满足的条件
1
2
3
4
5
6
7

10.查询学过 01 课程，但是没有学过 02 课程的学生信息

SELECT * #s.s_id, s.s_name
FROM Student AS s
JOIN Score AS sc1 ON s.s_id = sc1.s_id AND sc1.c_id = '01' #帅选出副表1中包含01的的分数表
LEFT JOIN Score AS sc2 ON s.s_id = sc2.s_id AND sc2.c_id = '02' #左连接是返回右边为空则使用null填充
WHERE sc2.s_id IS NULL; #通过is null比较运算符sc2.s_id如果是null就返回结果true,然后就可以被筛选出来
1
2
3
4
5

11.查询没有学完全部课程的同学的信息

-- 11.查询没有学完全部课程的同学的信息
# 可以统计分数表中相同学生编号的小于3个
SELECT * FROM student AS s
WHERE s.`s_id` IN(
SELECT s_id
FROM score AS s
GROUP BY s_id
HAVING COUNT(s_id) < (SELECT COUNT(*) FROM Course) )

#确定要查询的表,就是学生表和分数表,因为分数表中同一个课程等于3就是选满了
SELECT s.s_id, s.s_name
FROM Student AS s
JOIN Score AS sc ON s.s_id = sc.s_id #根据id连接
GROUP BY s.s_id, s.s_name #进行学号和姓名进行分组
HAVING COUNT(DISTINCT sc.c_id) < (SELECT COUNT(*) FROM Course); #查询出课程总数3门,然后进行分组之后的每一个学生包含的id进行比较
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

12.查询至少有一门课与学号为 01 的同学所学相同的同学的信息

-- 12.查询至少有一门课与学号为 01 的同学所学相同的同学的信息
#首先查询01同学有什么课程
#查询出之后就查其他同学的课程是否包含在里面

SELECT sc1.`s_id` FROM score AS sc1
WHERE sc1.`c_id` IN (
SELECT sc.`c_id` FROM score AS sc
WHERE sc.`s_id`=01)

# 还是要使用连接查询
#先连接分数表一次得到了课程
#第二次连接的条件是课程相等并且学生号等于01
SELECT DISTINCT s.s_id, s.s_name
FROM Student AS s
JOIN score AS sc1 ON s.s_id=sc1.`s_id` #连接一次得到所有信息
JOIN score AS sc2 ON sc2.`s_id`=01 AND sc1.c_id = sc2.c_id    #它是把和01同学的有的课程作为筛选条件了们用表1的课程号去匹配表2中01的有的课程号
WHERE s.s_id <>01

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

13.查询和 01 同学学习的课程完全相同的同学的信息

-- 查询和 01 同学学习的课程完全相同的同学的信息

#把查出来的和其他同学进行匹配
# 通过join操作将学号为01的同学的课程成绩和其他同学的课程成绩关联起来
# 使用where子句筛选出学号01的同学
SELECT s2.s_id, s2.s_name
FROM Student AS s1
JOIN Score AS sc1 ON s1.s_id = sc1.s_id
JOIN Score AS sc2 ON sc1.c_id = sc2.c_id
JOIN Student AS s2 ON sc2.s_id = s2.s_id AND s2.s_id <> '01'
WHERE s1.s_id = '01'
GROUP BY s2.s_id, s2.s_name #将其他同学按照学号和姓名分组
HAVING COUNT(DISTINCT sc1.c_id) = COUNT(DISTINCT sc2.c_id) #筛选出和学号为01的同学学习的所有课程都完全相同的同学
  AND COUNT(DISTINCT sc1.c_id) = (SELECT COUNT(DISTINCT c_id) FROM Score WHERE s_id = '01');

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

14.查询没有修过张三老师讲授的任何一门课程的学生姓名

-- 14.查询没有修过张三老师讲授的任何一门课程的学生姓名
#根据老师id查询出课程id
#接着就把课程id去匹配分数表中的学生中的课程
#最后把结果判空
#再学生表进行查询


SELECT DISTINCT s.s_id, s.s_name
FROM Student AS s 
WHERE s.`s_id` NOT IN (
SELECT sc.`s_id` FROM score AS sc WHERE sc.`c_id` IN (
SELECT c.`c_id` FROM course AS c WHERE c.`t_id`= ( #查出课程号
SELECT t.`t_id`FROM teacher AS t WHERE t.`t_name` LIKE '张三')))


# 分数表连接课程表能得到含有课程id和学生id 的副表1
# 然后把副表连接老师表得到有老师id 的副表2
# 通过where like 查询出张三老师的学生id
#最后再外层根据学生id进行查询即可
SELECT DISTINCT s.s_id, s.s_name
FROM Student AS s 
WHERE s.`s_id` NOT IN (
#把内层查询进行连接 查出修了张三的学生id
SELECT sc.`s_id` FROM score AS sc
JOIN course AS c  ON sc.`c_id`=  c.`c_id`
JOIN teacher AS t ON c.`t_id`= t.`t_id`
WHERE t.`t_name` LIKE '张三')
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

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

-- 15.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
SELECT  s.s_id, s.s_name ,AVG(sc.`s_score`) AS avg_score #4.计算平均成绩
FROM student AS s
JOIN score AS sc ON s.`s_id` = sc.`s_id` #1.表连接
WHERE sc.`s_score` < 60 #2.查出少于60的成绩
GROUP BY sc.`s_id` #3.按照学号进行分组
#5.分组之后再判断数量 使用having 
HAVING COUNT(sc.`s_id`)>=2;
1
2
3
4
5
6
7
8

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

-- 16.检索 01 课程分数小于 60，按分数降序排列的学生信息
#要连接两表表
SELECT  s.s_id, s.s_name ,sc.`c_id`,sc.`s_score`
FROM student AS s
JOIN score AS sc ON s.`s_id` = sc.`s_id` #通过学号将学生和成绩表连接起来。
WHERE sc.`c_id`=01 AND sc.`s_score` < 60 #选出课程编号为 '01' 并且分数小于 60 的记录。
ORDER BY sc.`s_score` DESC; #选出课程编号为 '01' 并且分数小于 60 的记录。
1
2
3
4
5
6
7

17.按平均成绩从高到低（降序）显示所有学生的所有课程的成绩以及平均成绩

-- 17.按平均成绩从高到低（降序）显示所有学生的所有课程的成绩以及平均成绩

#需要用到学生表和成绩表
#查询出平均成绩再降序
#要进行分组计算
#还要结合课程表,因为没有选课的同学没有课程id


SELECT  s.s_id, s.s_name ,c.c_id, c.c_name, sc.`s_score`, AVG(sc.`s_score`) AS  avgs #4,查询并计算平均成绩
FROM student AS s
CROSS JOIN course AS c # 1. 产生笛卡尔集组合每一种可能,就是每一个学生和每一个课程进行组合
LEFT JOIN score AS sc ON s.`s_id` = sc.`s_id` AND c.c_id = sc.c_id #2. 然后连接分数表的条件是学生id和课程id都匹配
GROUP BY s.s_id, s.s_name, c.c_id, c.c_name, sc.s_score # 3. 按照id之后按照姓名和课程id等进行分组
ORDER BY avgs DESC; #5. 最后再降序排列

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

18.查询各科成绩最高分、最低分和平均分：以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格

率，中等率，优良率，优秀率；及格：>=60，中等为：70-80，优良为：80-90，优秀为：>=90

-- 18.查询各科成绩最高分、最低分和平均分：以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格
-- 率，中等率，优良率，优秀率；及格：>=60，中等为：70-80，优良为：80-90，优秀为：>=90
#通过统计函数统计最高分最低分平均分
#使用流程函数进行计算出比率
SELECT  
c.`c_id`,c.`c_name`,
MAX(sc.`s_score`) AS 最高分,
MIN(sc.`s_score`) AS 最低分,
AVG(sc.`s_score`) AS 平均分,
#使用流程函数,当xx就返回xx否则返回xx,统计大于60分每一门有多少人
COUNT(CASE WHEN sc.`s_score`>=60 THEN 1 ELSE  NULL END ) / COUNT(*) AS 及格率,
COUNT(CASE WHEN sc.`s_score`>=70 AND sc.`s_score`<=80 THEN 1 ELSE NULL END) / COUNT(*) AS 中等率,
COUNT(CASE WHEN sc.`s_score`>=80 AND sc.`s_score`<=90 THEN 1 ELSE NULL END) / COUNT(*) AS 优良率,
COUNT(CASE WHEN sc.`s_score`>90 THEN 1 ELSE NULL END) / COUNT(*) AS 优良率
FROM course AS c
JOIN score AS sc ON c.`c_id`=sc.`c_id`
GROUP BY c.c_id, c.c_name;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

19.按照各科成绩进行排序，并且显示排名

SELECT
    s.s_id,
    s.s_name,
    sc.c_id,
    sc.s_score,
    (
        SELECT COUNT(DISTINCT sc2.s_score) + 1 #子查询计算sc2的每门课程中比当前成绩高的学生成绩数量,并加上1得到排名
        FROM Score AS sc2
        WHERE sc2.c_id = sc.c_id AND sc2.s_score > sc.s_score #应该可以理解为遍历比较,同一个课程id进行比较,比较一遍就select的count就执行一遍
    ) AS ranking,
  
FROM
    Student AS s
JOIN
    Score AS sc ON s.s_id = sc.s_id
ORDER BY
    sc.c_id,
    sc.s_score DESC;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

20.查询学生的总成绩，并进行排名

-- 20.查询学生的总成绩，并进行排名
#确定查询的表 学生表和成绩表,把成绩表按照学号分组后查询出总数

SELECT  SUM(sc.`s_score`) AS total_score
FROM student AS s
JOIN score AS sc ON s.`s_id`= sc.`s_id`  #使用join是因为空的就不返回了
GROUP BY s.`s_id`,s.`s_name`
ORDER BY total_score DESC;



#mysql8.0支持的窗口函数排序
SELECT s.s_id, s.s_name, SUM(sc.s_score) AS total_score,
      RANK() OVER (ORDER BY SUM(sc.s_score) DESC) AS ranking
FROM student AS s
JOIN score AS sc ON s.s_id = sc.s_id
GROUP BY s.s_id, s.s_name
ORDER BY total_score DESC;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

21.查询不同老师所教不同课程平均分从高到低显示授课老师对应课程

-- 21.查询不同老师所教不同课程平均分从高到低显示
#确定的表有分数表和课程表
#不同课程使用分组,不同课程就是不同老师啦
#高低显示使用group by
SELECT t.`t_name`,c.`c_name`,AVG(sc.`s_score`)  AS avg_score#课程,课程id,平均分
FROM score AS sc
JOIN course AS c ON sc.`c_id`=c.`c_id`
JOIN teacher AS t ON c.`t_id`=t.`t_id`
GROUP BY c.`c_id`
ORDER BY avg_score DESC;
1
2
3
4
5
6
7
8
9
10

22.查询所有课程的成绩第 2 名到第 3 名的学生信息及该课程成绩

-- 查询所有课程的成绩第 2 名到第 3 名的学生信息及该课程成绩
#课程表 学生表 
#首先分组得出课程
#对课程成绩排序,筛选出2-3名
SELECT * 
FROM
(
SELECT
s.s_id,
   s.s_name,
   sc.c_id,
   sc.s_score,
   (
       SELECT COUNT(DISTINCT sc2.s_score) + 1 #子查询计算sc2的每门课程中比当前成绩高的学生成绩数量,并加上1得到排名
       FROM Score AS sc2
       WHERE sc2.c_id = sc.c_id AND sc2.s_score > sc.s_score #应该可以理解为遍历比较,同一个课程id进行比较,比较一遍就select的count就执行一遍
   ) AS ranking
 
FROM
   Student AS s
JOIN
   Score AS sc ON s.s_id = sc.s_id
ORDER BY
   sc.c_id,
   sc.s_score DESC) AS r #对查出来的表起别名
WHERE r.ranking =2 OR r.ranking =3     #之后调用该表的列名作为筛选条件
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

23.统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[0-60] 及所占百分比

-- 23.统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[0-60] 及所占百分比
#确定的分数表
#使用流程函数进行判断如果符合条件1就返回1 不符合就返回0,然后对个数进行统计
#重新使用流程函数判断出人数然后计算百分比即可
SELECT 
sc.`c_id`,
c.`c_name`,
SUM(CASE WHEN sc.`s_score`<=60 THEN 1  ELSE 0 END) AS  `[0-60]`,
SUM(CASE WHEN sc.`s_score`<=70 AND sc.`s_score`>=60 THEN 1 ELSE 0 END) AS `[70-60]`,
SUM(CASE WHEN sc.`s_score`<=85 AND sc.`s_score`>=70 THEN 1 ELSE 0 END) AS `[85-70]`,
SUM(CASE WHEN sc.`s_score`>=85 THEN 1  ELSE 0 END) AS  `[100-85]`,
SUM(CASE WHEN sc.`s_score`<=60 THEN 1  ELSE 0 END)  / COUNT(1) * 100 AS `[0-60]_percentage`,
SUM(CASE WHEN sc.`s_score`<=70 AND sc.`s_score`>=60 THEN 1 ELSE 0 END) / COUNT(1) * 100 AS `[70-60]_percentage`,
SUM(CASE WHEN sc.`s_score`<=85 AND sc.`s_score`>=70 THEN 1 ELSE 0 END) / COUNT(1) * 100 AS `[85-70]_percentage`,
SUM(CASE WHEN sc.`s_score`>=85 THEN 1  ELSE 0 END)  / COUNT(1) * 100 AS `[100-85]_percentage`
FROM score AS sc 
JOIN course AS c ON sc.`c_id` =c.`c_id`
GROUP BY sc.`c_id`;


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

24.查询学生的平均成绩及名次

-- 24.查询学生的平均成绩及名次
#学生表和成绩表
#按照学生id分组查询出平均成绩之后在计算名次

SELECT sc.`s_id`,
   AVG(sc.s_score) AS avg_score,
   (
       SELECT COUNT(*) + 1
       FROM (
           SELECT s_id, AVG(s_score) AS avg_score
           FROM Score
           GROUP BY s_id 
       ) AS avg_scores #查询出平均成绩
       WHERE avg_score > AVG(sc.s_score) #进行名次比较,当然子查询的结果和父查询的统计比较
   ) AS ranking #得出排名
FROM Score AS sc #得出排名之后进行对分数表的学生id进行分组查询成绩
GROUP BY sc.s_id;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

25.查询各科成绩前三名的记录

SELECT
   s.s_id,
   s.s_name,
   sc.c_id,
   sc.s_score
FROM
   Student AS s
JOIN
   Score AS sc ON s.s_id = sc.s_id
WHERE
   (
       SELECT COUNT(*) #计算在同一门课程中成绩大于等于当前学生的人数
       FROM Score AS sc2
       WHERE sc2.c_id = sc.c_id AND sc2.s_score >= sc.s_score 
       # sc2.s_score当做一个固定值,它与同一门课程中其他学生的成绩（即 sc.s_score）进行比较,统计在这个子查询中有多少个学生比sc2.score高分
   ) <= 3 #如果有3个那就意味着在当前学生的成绩在该课程中排名第三,如果结果是2,就一味这当前学生的排名第四
ORDER BY
   sc.c_id,
   sc.s_score DESC;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

26.查询每门课被选修的学生数

-- 26.查询每门课被选修的学生数
#左连接 分组 统计学生id即可
SELECT c.`c_id`,c.`c_name`,COUNT(sc.`s_id`) AS student_num
FROM
#课程表作为主表
course AS c
LEFT JOIN score AS sc ON sc.`c_id`=c.`c_id`
GROUP BY c.`c_id`,c.`c_name`;

1
2
3
4
5
6
7
8
9

27.查询出只有两门课程的全部学生的学号和姓名

-- 27.查询出只有两门课程的全部学生的学号和姓名
#主表是学生表  连接分数表
#连接起来查询分数表里面分组学生id中只有两个课程id的学生
SELECT
   s.s_id,
   s.s_name,
   sc.c_id,
   sc.s_score,
   COUNT(sc.`c_id`) AS num
FROM
   Student AS s
JOIN score AS sc ON s.s_id = sc.s_id
GROUP BY sc.`s_id`
HAVING num = 2
1
2
3
4
5
6
7
8
9
10
11
12
13
14

28.查询男女生人数

-- 28.查询男女生人数
#直接在学生表查询
SELECT s.`s_sex`, COUNT(*) AS 人数
FROM
Student AS s
GROUP BY s.`s_sex`
1
2
3
4
5
6

29.查询名字中含有风字的学生信息

-- 29.查询名字中含有风字的学生信息

SELECT *
FROM
Student AS s
WHERE s.`s_name` LIKE '%风%'
1
2
3
4
5
6

30.查询同名同性的学生名单，并统计同名人数

-- 30.查询同名同性的学生名单，并统计同名人数
#子查询分组每一个名字作为一组,查询每一组的个数,
SELECT s.`s_name`,COUNT(s.`s_name`)
FROM
Student AS s
WHERE s.`s_name` IN
(
SELECT s.`s_name`
FROM
Student AS s
GROUP BY s.`s_name` #分组之后统计每一组的个数
HAVING COUNT(s.`s_name`)>1)
1
2
3
4
5
6
7
8
9
10
11
12

31.查询 1990 年出生的学生信息

-- 31.查询 1990 年出生的学生信息
SELECT *
FROM
Student AS s
WHERE s.`s_birth` LIKE '1990%'

1
2
3
4
5
6

32.查询每门课程的平均成绩，结果按平均成绩降序排列；平均成绩相同时，按课程编号 c_id 升序排列

-- 32.查询每门课程的平均成绩，结果按平均成绩降序排列；平均成绩相同时，按课程编号 c_id 升序排列
SELECT sc.`c_id`,AVG(sc.`s_score`) AS avg_sc
FROM
score AS sc 
GROUP BY sc.`c_id`
ORDER BY avg_sc DESC,sc.`c_id` ASC
1
2
3
4
5
6

33.查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩

-- 33.查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩

SELECT s.`s_id`,s.`s_name`, AVG(sc.`s_score`) AS avg_sc
FROM
Student AS s
JOIN score AS sc ON sc.`s_id`=s.`s_id`
GROUP BY sc.`s_id`
HAVING avg_sc >= 85
#having与where的区别:
#having是在分组后对数据进行过滤
#where是在分组前对数据进行过滤
#having后面可以使用聚合函数
#where后面不可以使用聚合
1
2
3
4
5
6
7
8
9
10
11
12
13

34.查询课程名称为数学，且分数低于 60 的学生姓名和分数

-- 34.查询课程名称为数学，且分数低于 60 的学生姓名和分数
SELECT s.`s_id`,s.`s_name`,sc.`s_score`
FROM
Student AS s
JOIN score AS sc ON sc.`s_id`=s.`s_id`
JOIN course AS c ON sc.`c_id`=c.`c_id`
WHERE c.`c_name`='数学' AND sc.`s_score`< 60
1
2
3
4
5
6
7

35.查询所有学生的课程及分数情况

-- 35.查询所有学生的课程及分数情况
SELECT s.`s_id`,s.`s_name`,c.`c_name`, sc.`s_score`
FROM
Student AS s
LEFT JOIN score AS sc ON sc.`s_id`=s.`s_id` #使用左连接,左表为主,右表为空就返回null
LEFT JOIN course AS c ON sc.`c_id`=c.`c_id`

1
2
3
4
5
6
7

36.查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数

-- 36.查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
SELECT s.`s_id`,s.`s_name`,c.`c_name`, sc.`s_score`
FROM
Student AS s
JOIN score AS sc ON sc.`s_id`=s.`s_id` #使用左连接,左表为主,右表为空就返回null
JOIN course AS c ON sc.`c_id`=c.`c_id`
WHERE sc.`s_score`>70
1
2
3
4
5
6
7

37.查询不及格的课程

-- 37.查询不及格的课程
SELECT s.`s_id`,s.`s_name`,c.`c_name`, sc.`s_score`
FROM
Student AS s
JOIN score AS sc ON sc.`s_id`=s.`s_id` #使用左连接,左表为主,右表为空就返回null
JOIN course AS c ON sc.`c_id`=c.`c_id`
WHERE sc.`s_score`< 60

1
2
3
4
5
6
7
8

38.查询课程编号为 01 且课程成绩大于等于 80 的学生的学号和姓名

SELECT s.`s_id`,s.`s_name`,c.`c_name`, sc.`s_score`
FROM
Student AS s
JOIN score AS sc ON sc.`s_id`=s.`s_id` #使用左连接,左表为主,右表为空就返回null
JOIN course AS c ON sc.`c_id`=c.`c_id`
WHERE sc.`c_id`=01 AND sc.`s_score` >= 80
1
2
3
4
5
6

39.每门课程的学生人数

-- 39.每门课程的学生人数
# 统计一下学生id个数即可
SELECT c.`c_name`, COUNT(sc.`s_id`) AS 人数
FROM
score AS sc 
JOIN course AS c ON sc.`c_id`=c.`c_id`
GROUP BY c.`c_id`,c.`c_name`
1
2
3
4
5
6
7

40.查询选修“张三”老师所授课程的学生中，成绩最高的学生信息及其成绩

-- 40.查询选修“张三”老师所授课程的学生中，成绩最高的学生信息及其成绩

#使用max函数加匹配查找
SELECT s.`s_id`,s.`s_name`,c.`c_name`, MAX(sc.`s_score`) AS 最高分
FROM
Student AS s
JOIN score AS sc ON sc.`s_id`=s.`s_id` 
JOIN course AS c ON sc.`c_id`=c.`c_id`
JOIN teacher AS t ON c.`t_id`=t.`t_id`
WHERE t.`t_name` LIKE '张三' ;
1
2
3
4
5
6
7
8
9
10

41.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

-- 41.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

#分数表作为主表根据学生id内连接自己,并同时条件判断出不同课程id和不同分数的

SELECT s1.s_id, sc1.c_id, sc1.s_score
FROM Score AS sc1
JOIN Score AS sc2 ON sc1.s_id = sc2.s_id AND sc1.c_id <> sc2.c_id
JOIN Student AS s1 ON sc1.s_id = s1.s_id
JOIN Student AS s2 ON sc2.s_id = s2.s_id
WHERE sc1.s_score = sc2.s_score
ORDER BY s1.s_id, sc1.c_id;


SELECT
   s1.*
FROM Score AS s1 INNER JOIN Score AS s2
ON s1.s_id = s2.s_id AND s1.c_id <> s2.c_id AND s1.s_score = s2.s_score;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

42.查询每门功成绩最好的前两名

-- 42.查询每门功成绩最好的前两名
# 反正就是子查询和父查询比较

#返回的是6行
SELECT sc.s_id, sc.c_id, sc.s_score
FROM score AS sc
WHERE
(
   SELECT COUNT(*) + 1 
   FROM score AS sc2
   WHERE sc2.`c_id` = sc.`c_id` AND sc2.`s_score` > sc.`s_score`
)  <= 2
ORDER BY sc.`c_id`, sc.s_score DESC;


#这里返回10行结果
SELECT * FROM score AS sc
WHERE
(
SELECT COUNT(*)  #不加1可以统计相同分数得出相同排名,因为统计的是在该分数之前有多少个比之高
FROM score AS sc2
WHERE sc2.`c_id`=sc.`c_id` AND sc2.`s_score` >sc.`s_score`

)  <= 2
ORDER BY sc.`c_id`, sc.s_score DESC; 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

43.统计每门课程的学生选修人数（超过 5 人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

-- 43.统计每门课程的学生选修人数（超过 5 人的课程才统计）。
-- 要求输出课程号和选修人数，查询结果按人数降序排列，
-- 若人数相同，按课程号升序排列
#根据课程id进行分组
#分组之后
SELECT sc.`c_id`, COUNT(DISTINCT sc.`s_id`) AS student_count
FROM score AS sc
GROUP BY sc.`c_id`
HAVING student_count > 5
ORDER BY student_count DESC, sc.`c_id` ASC;

1
2
3
4
5
6
7
8
9
10
11

44.检索至少选修两门课程的学生学号

-- 44.检索至少选修两门课程的学生学号

SELECT sc.`s_id` ,COUNT(DISTINCT sc.`c_id`) AS course_count
FROM score AS sc
GROUP BY sc.`s_id` 
HAVING course_count >= 2
1
2
3
4
5
6

45.查询选修了全部课程的学生信息

-- 45.查询选修了全部课程的学生信息
SELECT s.*,COUNT(DISTINCT sc.`c_id`) AS course_count
FROM score AS sc
JOIN student AS s ON sc.`s_id` = s.`s_id`
GROUP BY sc.`s_id` 
HAVING course_count = (SELECT COUNT(1) FROM  course)
1
2
3
4
5
6

46.查询各学生的年龄：按照出生日期来算，当前月日 < 出生年月的月日则，年龄减 1

-- 46.查询各学生的年龄：按照出生日期来算，当前月日 < 出生年月的月日则年龄减 1

#查询当前时间
SELECT NOW();
SELECT CURRENT_TIMESTAMP;

-- 精确到年月日
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d') AS current_year_month_day;


-- 日期运算
SELECT DATEDIFF(date1, date2) AS day_difference;


SELECT s.`s_name`, DATEDIFF(DATE_FORMAT(NOW(), '%Y-%m-%d'), s.`s_birth`)  / 365.25 AS year_difference
FROM 
student AS s ;

-- 正确答案  ,通过时间函数提取年月日,配合流程函数计算
SELECT s.`s_name`,
CASE
WHEN 
MONTH(NOW())< MONTH(s.`s_birth`) 
OR MONTH(NOW())= MONTH(s.`s_birth`)  #注意 or和 and 是同级关系
AND DAY(NOW()) < DAY(s.`s_birth`) 
THEN YEAR(NOW()) - YEAR(s_birth)-1 
ELSE  YEAR(NOW()) - YEAR(s_birth) END AS age
FROM student AS s ;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

47.查询本周过生日的学生

-- 47.查询本周过生日的学生
SELECT DAYOFWEEK(NOW()) AS day_number; -- 返回今天是星期几的数字 #其中 1 表示星期日，2 表示星期一，以此类推，7 表示星期六。
SELECT DAYNAME(NOW()) AS day_name; -- 返回今天是星期几的字符串


-- WEEKOFYEAR() 函数来获取日期所在的年中的周数
SELECT s.`s_name`
FROM student AS s 
WHERE WEEKOFYEAR(s.`s_birth`) = WEEKOFYEAR(NOW());

1
2
3
4
5
6
7
8
9
10

48.查询下周过生日的学生

-- 48.查询下周过生日的学生

SELECT s_id, s_name,s_birth
FROM student 
WHERE WEEK(s_birth) = WEEK(NOW())+1;


#查询现在在一年的周数
SELECT WEEK(NOW())
1
2
3
4
5
6
7
8
9

49.查询本月过生的同学

-- 49.查询本月过生的同学
-- 同理查现在是第几个月
SELECT s_id, s_name,s_birth
FROM student 
WHERE MONTH(s_birth) = MONTH(NOW())
1
2
3
4
5

50.查询下月过生的同学

-- 50.查询下月过生的同学
SELECT s_id, s_name,s_birth
FROM student 
WHERE MONTH(s_birth) = MONTH(NOW())+1
1
2
3
4