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

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% </br>2 数学 3 50.00% 1 16.67% 1 16.67% 2 33.33%</br>3 英语 2 33.33% 1 16.67% 1 16.67% 2 33.33%</br>

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

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</br>赵大 数学 90</br>赵大 英语 99</br>李二 语文 70</br>张三 语文 80</br>张三 数学 80</br>张三 英语 80</br>周梅 语文 76</br>周梅 数学 87</br>夏竹 数学 89</br>夏竹 英语 98</br>

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</br>刘四 数学 30</br>刘四 英语 40</br>吴兰 语文 31</br>吴兰 英语 34</br>

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 女