mysql练习50题：建表篇

创建数据库和表

数据库

1
2
3
4
5
6
7
8
-- 删除数据库（无需 CASCADE）
DROP DATABASE IF EXISTS mysql_ex;

-- 创建数据库
CREATE DATABASE mysql_ex;

-- 使用数据库
USE mysql_ex;

学生表 student

创建学生表 student
1
2
3
4
5
6
CREATE TABLE student (
	s_id INT KEY,
	s_name VARCHAR ( 8 ),
	s_birth DATE,
s_sex VARCHAR ( 4 ) 
)
插入学生数据
1
2
3
4
5
6
7
8
9
10
INSERT INTO student
VALUES
	( 1, '赵大', '1990-01-01', '男' ),
	( 2, '李二', '1990-12-21', '男' ),
	( 3, '张三', '1990-05-20', '男' ),
	( 4, '刘四', '1990-08-06', '男' ),
	( 5, '周梅', '1991-12-01', '女' ),
	( 6, '吴兰', '1992-03-01', '女' ),
	( 7, '夏竹', '1989-07-01', '女' ),
	( 8, '赵菊', '1990-01-20', '女' );

课程表 course

创建课程表 course、
1
2
3
4
5
CREATE TABLE course (
	c_id INT KEY,
	c_name VARCHAR ( 8 ),
t_id INT 
)
插入课程数据
1
2
3
4
5
INSERT INTO course
VALUES
	( 1, '语文', 2 ),
	( 2, '数学', 1 ),
	( 3, '英语', 3 );

教师表 teacher

创建教师表 teacher
1
CREATE TABLE teacher ( t_id INT KEY, t_name VARCHAR ( 8 ) );
插入教师数据
1
2
3
4
5
INSERT INTO teacher
VALUES
	( 1, '迪迦' ),
	( 2, '戴拿' ),
	( 3, '盖亚' );

成绩表 score

创建成绩表 score
1
CREATE TABLE score ( s_id INT, c_id INT, s_score INT );

插入成绩数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
INSERT INTO score
VALUES
	( 1, 1, 80 ),
	( 1, 2, 90 ),
	( 1, 3, 99 ),
	( 2, 1, 70 ),
	( 2, 2, 60 ),
	( 2, 3, 65 ),
	( 3, 1, 80 ),
	( 3, 2, 80 ),
	( 3, 3, 80 ),
	( 4, 1, 50 ),
	( 4, 2, 30 ),
	( 4, 3, 40 ),
	( 5, 1, 76 ),
	( 5, 2, 87 ),
	( 6, 1, 31 ),
	( 6, 3, 34 ),
	( 7, 2, 89 ),
	( 7, 3, 98 );

mysql练习50题

1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT
    student.s_id,
    student.s_name,
    student.s_birth,
    student.s_sex,
    s1.score1,
    s2.score2 
FROM
    student
    INNER JOIN ( SELECT s_id, s_score AS "score1" FROM score WHERE c_id = 1 ) s1 ON student.s_id = s1.s_id
    INNER JOIN ( SELECT s_id, s_score AS "score2" FROM score WHERE c_id = 2 ) s2 ON student.s_id = s2.s_id 
WHERE
    s1.score1 > s2.score2

所得结果：

s_id s_name s_birth s_sex score1 score2
2 李二 1990-12-21 男 70 60
4 刘四 1990-08-06 男 50 30

2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT
    student.s_id,
    student.s_name,
    student.s_birth,
    student.s_sex,
    s1.score1,
    s2.score2 
FROM
    student
    INNER JOIN ( SELECT s_id, s_score AS "score1" FROM score WHERE c_id = 1 ) s1 ON student.s_id = s1.s_id
    INNER JOIN ( SELECT s_id, s_score AS "score2" FROM score WHERE c_id = 2 ) s2 ON student.s_id = s2.s_id 
WHERE
    s1.score1 < s2.score2

结果如下：

s_id s_name s_birth s_sex score1 score2
1 赵大 1990-01-01 男 80 90
5 周梅 1991-12-01 女 76 87

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

1
2
3
4
5
6
7
8
9
SELECT
    student.s_id,
    student.s_name,
    avg_score 
FROM
    student
    LEFT JOIN ( SELECT s_id, ROUND( AVG( s_score ), 2 ) AS avg_score FROM score GROUP BY s_id ) s1 ON student.s_id = s1.s_id 
WHERE
    avg_score >= 60

结果如下：

s_id s_name avg_score
1 赵大 89.67
2 李二 65.00
3 张三 80.00
5 周梅 81.50
7 夏竹 93.50

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

1
2
3
4
5
6
7
8
9
SELECT
    student.s_id,
    student.s_name,
    ROUND( IFNULL( avg_score, 0 ), 2 ) 
FROM
    student
    LEFT JOIN ( SELECT s_id, AVG( s_score ) AS avg_score FROM score GROUP BY s_id ) s1 ON student.s_id = s1.s_id 
WHERE
    IFNULL( avg_score, 0 ) < 60

结果如下：

s_id s_name avg_score
4 刘四 40.00
6 吴兰 32.50
8 赵菊 0.00

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

1
2
3
4
5
6
7
8
9
SELECT
    student.s_id,
    student.s_name,
    IFNULL( sum_course.course, 0 ) as 'course',
    IFNULL( sum_score.score, 0 ) as 'score'
FROM
    student
    LEFT JOIN ( SELECT s_id, SUM( c_id ) AS 'course' FROM score GROUP BY score.s_id ) sum_course ON sum_course.s_id = student.s_id
    LEFT JOIN ( SELECT s_id, SUM( s_score ) AS 'score' FROM score GROUP BY score.s_id ) sum_score ON student.s_id = sum_score.s_id

结果如下：

s_id s_name course score
1 赵大 6 269
2 李二 6 195
3 张三 6 240
4 刘四 6 120
5 周梅 3 163
6 吴兰 4 65
7 夏竹 5 187
8 赵菊 0 0

6、查询"戴"姓老师的数量

1
2
3
4
5
6
SELECT
    COUNT( t_id ) as 'teacher_num'
FROM
    teacher 
WHERE
    t_name LIKE '戴%'

结果如下：

teacher_num
1

7、询学过"迪迦"老师授课的同学的信息

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT
    s_id,
    s_name,
    s_sex,
    s_birth 
FROM
    student 
WHERE
    s_id IN (
    SELECT
        s_id 
    FROM
        score 
WHERE
    c_id IN ( SELECT c_id FROM course WHERE t_id IN ( SELECT t_id FROM teacher WHERE t_name = "迪迦" ) ))

结果如下：

s_id s_name s_sex s_birth
1 赵大 男 1990-01-01
2 李二 男 1990-12-21
3 张三 男 1990-05-20
4 刘四 男 1990-08-06
5 周梅 女 1991-12-01
7 夏竹 女 1989-07-01

8、查询没学过"迪迦"老师授课的同学的信息

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT
    s_id,
    s_name,
    s_sex,
    s_birth 
FROM
    student 
WHERE
    s_id NOT IN (
    SELECT
        s_id 
    FROM
        score 
WHERE
    c_id IN ( SELECT c_id FROM course WHERE t_id IN ( SELECT t_id FROM teacher WHERE t_name = "迪迦" ) ))

结果如下：

s_id s_name s_sex s_birth
6 吴兰 女 1992-03-01
8 赵菊 女 1990-01-20

9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息

1
2
3
4
5
6
7
8
9
SELECT
    s_id,
    s_name,
    s_sex,
    s_birth 
FROM
    student 
WHERE
    s_id IN ( SELECT s_id FROM score WHERE c_id IN ( 1, 2 ) GROUP BY s_id HAVING COUNT( c_id ) = 2 )

解题思路：

  • SELECT s_id FROM score WHERE c_id IN (1, 2)：首先筛选出所有选修过01或02课程的记录。
  • GROUP BY s_id：按学生分组，统计每个学生的相关记录。
  • HAVING COUNT(c_id) = 2：要求每组（每个学生）必须有恰好两条课程记录

结果如下：

s_id s_name s_sex s_birth
1 赵大 男 1990-01-01
2 李二 男 1990-12-21
3 张三 男 1990-05-20
4 刘四 男 1990-08-06
5 周梅 女 1991-12-01

10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息

1
2
3
4
5
6
7
8
9
SELECT
    s_id,
    s_name,
    s_sex,
    s_birth 
FROM
    student 
WHERE
    s_id IN ( SELECT s_id FROM score WHERE c_id = 1 AND s_id NOT IN ( SELECT s_id FROM score WHERE c_id = 2 ) )

解题思路：

这题主要是操作score表，所以先找出score表中所有的标号为1的课，再使用not in排除所有编号为2的课即可

结果如下：

s_id s_name s_sex s_birth
6 吴兰 女 1992-03-01
11、查询没有学全所有课程的同学的信息
1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT
    * 
FROM
    student 
WHERE
    s_id NOT IN (
    SELECT
        s_id 
    FROM
        score 
    GROUP BY
        s_id 
HAVING
    COUNT( c_id ) = ( SELECT COUNT( c_id ) FROM course ))
思路：

因为存在有人完全没选课，所以采用查所有的学完三门的学生id，然后再反向排除即可得到没学所有课程的同学

结果如下：
s_id s_name s_birth s_sex
5 周梅 1991-12-01 女
6 吴兰 1992-03-01 女
7 夏竹 1989-07-01 女
8 赵菊 1990-01-20 女
12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT
    * 
FROM
    student 
WHERE
    s_id IN (
    SELECT
        s_id 
    FROM
        score 
    WHERE
        c_id IN ( SELECT c_id FROM score WHERE s_id = 1 ) 
        AND s_id != 1 
GROUP BY
    s_id)
结果如下：

这里我排出来s_id为1的学生

s_id s_name s_birth s_sex
2 李二 1990-12-21 男
3 张三 1990-05-20 男
4 刘四 1990-08-06 男
5 周梅 1991-12-01 女
6 吴兰 1992-03-01 女
7 夏竹 1989-07-01 女
13、查询和"01"号的同学学习的课程完全相同的其他同学的信息
1
2
3
4
5
6
7
8
9
10
11
12
SELECT * FROM student WHERE s_id IN (
SELECT
    s_id 
FROM
    score 
WHERE
    score.c_id IN ( SELECT c_id FROM score WHERE s_id = 1 ) 
GROUP BY
    score.s_id 
HAVING
    COUNT( score.c_id ) = ( SELECT COUNT( c_id ) FROM score WHERE s_id = 1 )
) AND s_id !=1
结果如下：
s_id s_name s_birth s_sex
2 李二 1990-12-21 男
3 张三 1990-05-20 男
4 刘四 1990-08-06 男
14、查询没学过"迪迦"老师讲授的任一门课程的学生姓名
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SELECT
    student.s_name 
FROM
    student 
WHERE
    s_id NOT IN (
    SELECT
        s_id 
    FROM
        score 
    WHERE
        c_id IN (
        SELECT
            c_id 
        FROM
            course 
    WHERE
    t_id IN ( SELECT t_id FROM teacher WHERE t_name = "迪迦" )))
结果如下：
s_name
吴兰
赵菊
15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩(此题还需改进)
1
2
3
4
5
6
7
8
9
SELECT
    student.s_id,
    student.s_name,
    s1.avg_score
FROM
    student 
    INNER JOIN ( SELECT s_id, ROUND( AVG( s_score ), 2 ) as 'avg_score' FROM score GROUP BY s_id ) s1 ON student.s_id = s1.s_id
    WHERE
    student.s_id IN ( SELECT s_id FROM score WHERE IFNULL( s_score, 0 ) < 60 GROUP BY s_id HAVING COUNT( c_id ) >= 2 )

目前这题得出的答案并不包括成绩为空的学生，如果成绩为空的学生是做为0，则目前答案并不算对，现在太晚了，以后再改

s_id s_name avg_score
4 刘四 40.00
6 吴兰 32.50

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

1
2
3
4
5
6
7
8
SELECT
    student.*,
    s1.s_score 
FROM
    student
    INNER JOIN ( SELECT s_id, s_score FROM score WHERE s_score < 60 AND c_id = 1 ) s1 ON s1.s_id = student.s_id 
ORDER BY
    s1.s_score DESC

结果如下

s_id s_name s_brith s_sex s_score
4 刘四 1990-08-06 男 50
6 吴兰 1992-03-01 女 31

17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT
    st.s_id AS '学号',
    st.s_name AS '姓名',
    SUM( CASE c_id WHEN 1 THEN s_score ELSE 0 END ) AS '语文',
    SUM( CASE c_id WHEN 2 THEN s_score ELSE 0 END ) AS '数学',
    SUM( CASE c_id WHEN 3 THEN s_score ELSE 0 END ) AS '英语',
    IFNULL( ROUND( AVG( s_score ), 2 ), 0 ) AS '平均成绩' 
FROM
    student st
    LEFT JOIN score sc ON st.s_id = sc.s_id 
GROUP BY
    st.s_id 
ORDER BY
    '平均成绩' DESC

*注*

该 SQL 语句中的 SUM( CASE c_id WHEN 1 THEN s_score ELSE 0 END ) 是一个条件聚合的表达式，其作用是计算特定科目的总成绩。

-**SUM**: 聚合函数，用于计算一组值的和。

-**CASE**: 条件语句，允许在查询中进行条件判断。

其运算逻辑是：如果 c_id（课程 ID）等于 1，那么返回该行的 s_score（成绩）；如果不等于 1，则返回 0。

结果如下：

学号 姓名 语文 数学 英语 平均成绩
1 赵大 80 90 99 89.67
2 李二 70 60 65 65.00
3 张三 80 80 80 80.00
4 刘四 50 30 40 40.00
5 周梅 76 87 0 81.50
6 吴兰 31 0 34 32.50
7 夏竹 0 89 98 93.50
8 赵菊 0 0 0 0.00

18、查询各科成绩最高分、最低分和平均分，以如下形式显示：

课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率– 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT
    co.c_id,
    co.c_name,
    ROUND( AVG( s_score ), 2 ) AS '平均分',
    MAX( s_score ) AS '最高分',
    MIN( s_score ) AS '最低分',
    concat( round( sum( CASE WHEN s_score >= 60 THEN 1 ELSE 0 END ) / count(*) * 100, 2 ), '%' ) AS 及格率,
    concat( round( sum( CASE WHEN s_score BETWEEN 70 AND 80 THEN 1 ELSE 0 END ) / count(*) * 100, 2 ), '%' ) AS 中等率,
    concat( round( sum( CASE WHEN s_score BETWEEN 80 AND 90 THEN 1 ELSE 0 END ) / count(*) * 100, 2 ), '%' ) AS 优良率,
    concat( round( sum( CASE WHEN s_score >= 90 THEN 1 ELSE 0 END ) / count(*) * 100, 2 ), '%' ) AS 优秀率 
FROM
    course co
    INNER JOIN score sc ON sc.c_id = co.c_id 
GROUP BY
    co.c_id

*注*

优秀率、及格率看起来很复杂，其实不然，只要分段来看就行。

其中concat(..., '%')是用来拼接百分号的

结果如下：

c_id c_name 平均分 最高分 最低分 及格率 中等率 优良率 优秀率
1 语文 64.50 80 31 66.67% 66.67% 33.33% 0.00%
2 数学 72.67 90 30 83.33% 16.67% 66.67% 16.67%
3 英语 69.33 99 34 66.67% 16.67% 16.67% 33.33%

19、按各科成绩进行排序，并显示排名

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT
    st.s_id,
    st.s_name,
    co.c_name,
    sc.s_score,
    RANK() over ( PARTITION BY co.c_id ORDER BY sc.s_score DESC ) AS '排名' 
FROM
    score sc
    JOIN course co ON sc.c_id = co.c_id
    JOIN student st ON st.s_id = sc.s_id 
ORDER BY
    co.c_id,
    '排名'

*注*

  • RANK() OVER (PARTITION BY c.c_id ORDER BY sc.s_score DESC) AS '排名': 计算每门课程的成绩排名，使用 RANK() 函数按成绩降序排列。
  • PARTITION BY c.c_id: 将数据按课程 ID 划分成不同的组，以便对每门课程内的学生成绩进行排名。
  • RANK() 属于 SQL 的 窗口函数，它会为数据生成一个排名值，不会合并行，并为每一行返回一个计算结果
  • OVER 是窗口函数的核心，它定义了 如何划分数据窗口（即如何分组和排序）

整个表达式的执行步骤如下：

  1. 按课程分组：将数据按 c.c_id 分成多个小组（每个小组是一门课的数据）。
  2. 在组内排序：每个小组内按 s_score 降序排列。
  3. 计算排名

：在每组内为每一行生成排名值（第1名、第2名等）。

  • 如果有多个相同的分数，它们的排名相同，且下一个排名会跳过空缺的位置。

最后结果如下（太长了，不想做表格🤪）

1 赵大 语文 80 1
3 张三 语文 80 1
5 周梅 语文 76 3
2 李二 语文 70 4
4 刘四 语文 50 5
6 吴兰 语文 31 6
1 赵大 数学 90 1
7 夏竹 数学 89 2
5 周梅 数学 87 3
3 张三 数学 80 4
2 李二 数学 60 5
4 刘四 数学 30 6
1 赵大 英语 99 1
7 夏竹 英语 98 2
3 张三 英语 80 3
2 李二 英语 65 4
4 刘四 英语 40 5
6 吴兰 英语 34 6

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

1
2
3
4
5
6
7
8
SELECT
    st.s_id,
    st.s_name,
    SUM(s_score) as 'sum_score',
    RANK() over( ORDER BY SUM(s_score) DESC) as '排名'
FROM
    score sc
    JOIN student st ON sc.s_id = st.s_id GROUP BY sc.s_id ORDER BY '排名'

*注*

因为这里只要比较总成绩，不需要用课程区分，所以可以省略PARTITION BY分组

结果如下

s_id s_name sum_score 排名
1 赵大 269 1
3 张三 240 2
2 李二 195 3
7 夏竹 187 4
5 周梅 163 5
4 刘四 120 6
6 吴兰 65 7

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

1
2
3
4
5
6
7
8
9
10
SELECT
    t.t_name,
    co.c_name,
    ROUND( AVG( s_score ), 2 ) AS '平均分' 
FROM
    course co
    INNER JOIN score sc ON co.c_id = sc.c_id
    INNER JOIN teacher t ON t.t_id = co.t_id 
GROUP BY
    co.c_id

最后结果

t_name c_name 平均分
戴拿 语文 64.50
迪迦 数学 72.67
盖亚 英语 69.33

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT
sc.rank,
    st.s_id,
    st.s_name,
    st.s_birth,
    st.s_sex,
    co.c_name,
    sc.s_score
FROM
    student st
    JOIN ( SELECT c_id,s_id, s_score, RANK() over ( PARTITION BY c_id ORDER BY s_score DESC) AS 'rank' FROM score ) sc ON st.s_id = sc.s_id 
    INNER JOIN course co ON co.c_id = sc.c_id 
WHERE
    sc.rank IN (
    2,
    3)
    ORDER BY co.c_id,sc.rank

*注*

原帖这里的结果我不太认同，因为语文有两个80分并列第一，但是原帖将其中一个设置为第二名，我觉得不是很合适，所以我这语文没有第二名

结果如下

rank s_id s_name s_birth s_sex c_name s_score
3 5 周梅 1991-12-01 女 语文 76
2 7 夏竹 1989-07-01 女 数学 89
3 5 周梅 1991-12-01 女 数学 87
2 7 夏竹 1989-07-01 女 英语 98
3 3 张三 1990-05-20 男 英语 80

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

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
-- 23、统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比
SELECT
    co.c_id,
    co.c_name,
    SUM( CASE WHEN sc.s_score BETWEEN 85 AND 100 THEN 1 ELSE 0 END ) AS '[100-85]',
    CONCAT(
        ROUND( SUM( CASE WHEN sc.s_score BETWEEN 85 AND 100 THEN 1 ELSE 0 END ) / COUNT(*) * 100, 2 ),
        '%' 
    ) AS '[100-85]百分比',
    SUM( CASE WHEN sc.s_score BETWEEN 70 AND 85 THEN 1 ELSE 0 END ) AS '[85-70]',
    CONCAT(
        ROUND( SUM( CASE WHEN sc.s_score BETWEEN 70 AND 85 THEN 1 ELSE 0 END ) / COUNT(*) * 100, 2 ),
        '%' 
    ) AS '[85-70]百分比',
    SUM( CASE WHEN sc.s_score BETWEEN 60 AND 70 THEN 1 ELSE 0 END ) AS '[70-60]',
    CONCAT(
        ROUND( SUM( CASE WHEN sc.s_score BETWEEN 60 AND 70 THEN 1 ELSE 0 END ) / COUNT(*) * 100, 2 ),
        '%' 
    ) AS '[70-60]百分比',
    SUM( CASE WHEN sc.s_score BETWEEN 0 AND 60 THEN 1 ELSE 0 END ) AS '[0-60]',
    CONCAT(
        ROUND( SUM( CASE WHEN sc.s_score BETWEEN 0 AND 60 THEN 1 ELSE 0 END ) / COUNT(*) * 100, 2 ),
        '%' 
    ) AS '[0-60]百分比' 
FROM
    course co
    INNER JOIN score sc ON sc.c_id = co.c_id 
GROUP BY
    co.c_id

*注*

这个sql虽然看起来很复杂，其实挺简单，后面几个不过是更改了参数范围，核心语句都是

SUM( CASE WHEN sc.s_score BETWEEN 85 AND 100 THEN 1 ELSE 0 END )，这个和之前的练习差不多，不过是case后面由原来的c_id WHEN 1 改成了WHEN sc.s_score BETWEEN 85 AND 100，你可以理解成更改了判定条件

结果如下

太长了，懒得做表格，字段和sql顺序一致

1 语文 0 0.00% 4 66.67% 1 16.67% 2 33.33%
2 数学 3 50.00% 1 16.67% 1 16.67% 2 33.33%
3 英语 2 33.33% 1 16.67% 1 16.67% 2 33.33%

24、查询学生平均成绩及其名次

1
2
3
4
5
6
7
8
9
10
SELECT
    sc.s_id,
    st.s_name,
    ROUND(AVG(s_score),2) AS '平均成绩',
    RANK() over(ORDER BY AVG(s_score) DESC) AS 'rank'
FROM
    score sc
    INNER JOIN student st ON sc.s_id = st.s_id
    GROUP BY sc.s_id
    ORDER BY 'rank'

结果如下

s_id s_name 平均成绩 rank
7 夏竹 93.50 1
1 赵大 89.67 2
5 周梅 81.50 3
3 张三 80.00 4
2 李二 65.00 5
4 刘四 40.00 6
6 吴兰 32.50 7

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

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT
    st.s_id,
    st.s_name,
    st.s_birth,
    st.s_sex,
    co.c_name,
    sc.s_score,
    sc.rank 
FROM
    ( SELECT s_id,c_id,s_score, RANK() over ( PARTITION BY c_id ORDER BY s_score DESC ) AS 'rank' FROM score ) sc
    JOIN course co ON sc.c_id = co.c_id
    JOIN student st ON st.s_id = sc.s_id
    WHERE sc.rank in (1,2,3)

结果如下

太长了，懒得做表格，字段和sql顺序一致

1 赵大 1990-01-01 男 语文 80 1
3 张三 1990-05-20 男 语文 80 1
5 周梅 1991-12-01 女 语文 76 3
1 赵大 1990-01-01 男 数学 90 1
7 夏竹 1989-07-01 女 数学 89 2
5 周梅 1991-12-01 女 数学 87 3
1 赵大 1990-01-01 男 英语 99 1
7 夏竹 1989-07-01 女 英语 98 2
3 张三 1990-05-20 男 英语 80 3

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

1
2
3
4
5
6
7
SELECT
    co.c_id,
    co.c_name,
    sc.total 
FROM
    course co
    JOIN ( SELECT c_id, COUNT(*) AS 'total' FROM score GROUP BY c_id ) sc ON sc.c_id = co.c_id
结果如下
c_id c_name total
1 语文 6
2 数学 6
3 英语 6

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

1
2
3
4
5
6
7
8
SELECT
    st.s_id,
    st.s_name 
FROM
    student st
    JOIN ( SELECT s_id, COUNT( c_id ) AS 'total' FROM score GROUP BY s_id ) sc ON st.s_id = sc.s_id 
WHERE
    sc.total = 2
结果如下
s_id s_name
5 周梅
6 吴兰
7 夏竹

28、查询男生、女生人数

SELECT s_sex,COUNT(*) AS ‘人数’ FROM student GROUP BY s_sex

结果如下
s_sex 人数
男 4
女 4

29、查询名字中含有"赵"字的学生信息

SELECT * FROM student WHERE s_name LIKE ‘%赵%’

结果如下
s_id s_name s_birth s_sex
1 赵大 1990-01-01 男
8 赵菊 1990-01-20 女

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

SELECT COUNT() AS ‘count’ FROM student GROUP BY s_name,s_sex HAVING COUNT() > 1

结果如下
count

没有结果，没人同名同性

31、查询1990年出生的学生名单

SELECT s_id,s_name,s_birth,s_sex FROM student WHERE YEAR(s_birth) = ‘1990’

最终结果
s_id s_name s_birth s_sex
1 赵大 1990-01-01 男
2 李二 1990-12-21 男
3 张三 1990-05-20 男
4 刘四 1990-08-06 男
8 赵菊 1990-01-20 女

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

1
2
3
4
5
6
7
8
SELECT
    co.c_id,
    co.c_name,
    sc.avg_score 
FROM
    course co
    JOIN ( SELECT c_id, ROUND( AVG( s_score ), 2 ) AS 'avg_score' FROM score GROUP BY c_id ) sc ON sc.c_id = co.c_id
    ORDER BY sc.avg_score DESC,co.c_id
最终结果
c_id c_name avg_score
2 数学 72.67
3 英语 69.33
1 语文 64.50

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

1
2
3
4
5
6
7
8
9
SELECT
    st.s_id,
    st.s_name,
    sc.avg_score 
FROM
    student st
    JOIN ( SELECT s_id, ROUND( AVG( s_score ), 2 ) AS 'avg_score' FROM score GROUP BY s_id ) sc ON sc.s_id = st.s_id 
WHERE
    sc.avg_score >= 85
最终结果
s_id s_name avg_score
1 赵大 89.67
7 夏竹 93.50

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT
    s_name,
    sc.s_score 
FROM
    student st
    JOIN (
    SELECT
        s_id,
        s_score 
    FROM
        score 
    WHERE
    c_id IN ( SELECT c_id FROM course WHERE c_name = '数学' )) sc ON sc.s_id = st.s_id 
WHERE
    sc.s_score < 60
最终结果
s_name s_score
刘四 30

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

1
2
3
4
5
6
7
8
9
10
11
SELECT
    st.s_id,
    st.s_name,
    SUM( CASE c_id WHEN 1 THEN sc.s_score ELSE 0 END ) AS '语文' ,
    SUM( CASE c_id WHEN 2 THEN sc.s_score ELSE 0 END ) AS '数学' ,
    SUM( CASE c_id WHEN 3 THEN sc.s_score ELSE 0 END ) AS '英语' 
FROM
    student st
    left JOIN score sc ON sc.s_id = st.s_id
GROUP BY
    st.s_id

*注*

这里采用的左连接

最终结果
s_id s_name 语文 数学 英语
1 赵大 80 90 99
2 李二 70 60 65
3 张三 80 80 80
4 刘四 50 30 40
5 周梅 76 87 0
6 吴兰 31 0 34
7 夏竹 0 89 98
8 赵菊 0 0 0

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

1
2
3
4
5
6
7
8
9
10
SELECT
    st.s_name,
    co.c_name,
    sc.s_score 
FROM
    score sc
    INNER JOIN student st ON sc.s_id = st.s_id
    INNER JOIN course co ON co.c_id = sc.c_id 
WHERE
    sc.s_score >= 70
结果如下

赵大 语文 80
赵大 数学 90
赵大 英语 99
李二 语文 70
张三 语文 80
张三 数学 80
张三 英语 80
周梅 语文 76
周梅 数学 87
夏竹 数学 89
夏竹 英语 98

37、查询课程不及格的学生

1
2
3
4
5
6
7
8
9
10
SELECT
    st.s_name,
    co.c_name,
    sc.s_score 
FROM
    score sc
    INNER JOIN student st ON sc.s_id = st.s_id
    INNER JOIN course co ON co.c_id = sc.c_id 
WHERE
    sc.s_score < 60
结果如下

刘四 语文 50
刘四 数学 30
刘四 英语 40
吴兰 语文 31
吴兰 英语 34

38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名

1
2
3
4
5
6
SELECT
    st.s_id,
    st.s_name 
FROM
    student st
    JOIN ( SELECT s_id FROM score WHERE c_id = 1 AND s_score >= 80 ) sc ON sc.s_id = st.s_id
结果如下
s_id s_name
1 赵大
3 张三

39、求每门课程的学生人数

1
2
3
4
5
6
7
8
9
SELECT
    co.c_id,
    co.c_name,
    COUNT(*) AS '人数'
FROM
    score sc
    JOIN course co ON sc.c_id = co.c_id 
GROUP BY
    c_id
结果如下
c_id c_name 人数
1 语文 6
2 数学 6
3 英语 6

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT
    st.s_id,
    st.s_name,
    st.s_birth,
    st.s_sex,
    co.c_name,
    sc.s_score 
FROM
    student st
    INNER JOIN score sc ON st.s_id = sc.s_id
    INNER JOIN course co ON co.c_id = sc.c_id
    INNER JOIN teacher t ON t.t_id = co.t_id 
WHERE
    t_name = '迪迦' 
ORDER BY
    sc.s_score DESC
    LIMIT 1
结果如下
s_id s_name s_birth s_sex c_name s_score
1 赵大 1990-01-01 男 数学 90

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

1
2
3
4
5
6
7
8
9
10
11
12
SELECT
    * 
FROM
    score WHERE s_score IN (
    SELECT
        s_score 
    FROM
        score 
    GROUP BY
        s_score 
HAVING
    COUNT(*) > 2)

结果如下

s_id c_id s_score
1 1 80
3 1 80
3 2 80
3 3 80

42、查询每门课程成绩最好的前三名

1
2
3
4
5
6
7
8
9
10
11
12
SELECT
    st.s_id,
    st.s_name,
    sc.c_id,
    sc.s_score,
    co.c_name,
    sc.rank 
FROM
    ( SELECT *, RANK() over ( PARTITION BY c_id ORDER BY s_score DESC ) AS 'rank' FROM score ) sc
    JOIN course co ON sc.c_id = co.c_id
    JOIN student st ON sc.s_id = st.s_id
    WHERE sc.rank in (1,2,3)

结果如下

s_id s_name c_id s_score c_name rank
1 赵大 1 80 语文 1
3 张三 1 80 语文 1
5 周梅 1 76 语文 3
1 赵大 2 90 数学 1
7 夏竹 2 89 数学 2
5 周梅 2 87 数学 3
1 赵大 3 99 英语 1
7 夏竹 3 98 英语 2
3 张三 3 80 英语 3

43、统计每门课程的学生选修人数（超过5人的课程才统计）

1
2
3
4
5
6
7
SELECT
    co.c_id,
    co.c_name,
    sc.total 
FROM
    course co
    JOIN ( SELECT c_id, COUNT(*) AS 'total' FROM score GROUP BY c_id HAVING COUNT(*) > 5 ) sc ON co.c_id = sc.c_id

结果如下

c_id c_name total
1 语文 6
2 数学 6
3 英语 6

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

SELECT s_id,COUNT(*) as ‘课程数’ FROM score GROUP BY s_id HAVING COUNT(1) >= 2

结果如下

s_id 课程数
1 3
2 3
3 3
4 3
5 2
6 2
7 2

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT
    * 
FROM
    student 
WHERE
    s_id IN (
    SELECT
        s_id 
    FROM
        score 
    GROUP BY
        s_id 
HAVING
    COUNT(*) IN ( SELECT COUNT( * ) FROM course ))

结果如下

s_id s_name s_birth s_sex
1 赵大 1990-01-01 男
2 李二 1990-12-21 男
3 张三 1990-05-20 男
4 刘四 1990-08-06 男

46、查询各学生的年龄(周岁,此时2025.3.26)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
SELECT
    s_id,
    s_name,
    s_sex,
    s_birth,
IF
    (
        MONTH (
        CURRENT_DATE ()) < MONTH ( s_birth ) 
        OR (
            MONTH (
            CURRENT_DATE ()) = MONTH ( s_birth ) 
            AND DAY (
            CURRENT_DATE ()) < DAY ( s_birth )),
        YEAR (
        CURRENT_DATE ()) - YEAR ( s_birth ) - 1,
        YEAR (
        CURRENT_DATE ()) - YEAR ( s_birth ) 
    ) AS '周岁' 
FROM
    student

结果如下

s_id s_name s_sex s_birth 周岁
1 赵大 男 1990-01-01 35
2 李二 男 1990-12-21 34
3 张三 男 1990-05-20 34
4 刘四 男 1990-08-06 34
5 周梅 女 1991-12-01 33
6 吴兰 女 1992-03-01 33
7 夏竹 女 1989-07-01 35
8 赵菊 女 1990-01-20 35

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

1
2
3
4
5
6
7
8
select *
from student
where
    datediff(concat(year(current_date()), date_format(s_birth, '-%m-%d')), current_date())
    between 0 and 7
or
    datediff(concat(year(current_date()) + 1, date_format(s_birth, '-%m-%d')), current_date())
    between 0 and 7;

结果如下

很遗憾，在2025.3.16这天他们没人过生日

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

1
2
3
4
5
6
7
8
select *
from student
where
    datediff(concat(year(current_date()), date_format(s_birth, '-%m-%d')), current_date())
    between 7 and 14
or
    datediff(concat(year(current_date()) + 1, date_format(s_birth, '-%m-%d')), current_date())
    between 7 and 14;

结果如下

很遗憾，下周他们也没人过生日

49、查询本月过生日的学生

1
2
3
4
5
6
7
8
9
SELECT
    * 
FROM
    student 
WHERE
    MONTH ( s_birth ) = MONTH ( CURRENT_DATE ) 
    AND DAY ( s_birth ) BETWEEN 1 
    AND DAY (
    LAST_DAY( CURRENT_DATE ));

结果如下

s_id s_name s_birth s_sex
6 吴兰 1992-03-01 女

50、查询12月份过生日的学生

SELECT * FROM student WHERE MONTH(s_birth) = 12

结果如下

s_id s_name s_birth s_sex
2 李二 1990-12-21 男
5 周梅 1991-12-01 女