SQL 查询语句练习

数据表设计

1. 学生表（student）

   字段名称	数据类型	描述	主键	是否为空	是否是外键
   student_id	INT	学生编号	是	否	否
   student_name	VARCHAR(100)	学生姓名	否	否	否
   birth_date	DATE	出生年月	否	否	否
   gender	CHAR(1)	性别	否	否	否

2. 教师表（teacher）

   字段名称	数据类型	描述	主键	是否为空	是否是外键
   teacher_id	INT	教师编号	是	否	否
   teacher_name	VARCHAR(100)	教师姓名	否	否	否

3. 课程表（course）

   字段名称	数据类型	描述	主键	是否为空	是否是外键
   course_id	INT	课程编号	是	否	否
   course_name	VARCHAR(100)	课程名称	否	否	否
   teacher_id	INT	教师编号	否	否	是 (teacher)

4. 成绩表（score）

   字段名称	数据类型	描述	主键	是否为空	是否是外键
   student_id	INT	学生编号	是	否	是 (student)
   course_id	INT	课程编号	是	否	是 (course)
   score	DECIMAL(5, 2)	成绩	否	否	否

数据插入

1. 学生表（student）

   sql

   12345678910111213

    CREATE TABLE student (     student_id INT PRIMARY KEY,     student_name VARCHAR(100) NOT NULL,     birth_date DATE NOT NULL,     gender CHAR(1) NOT NULL CHECK (gender IN ('男', '女')) );  INSERT INTO student (student_id, student_name, birth_date, gender) VALUES (1, '陈晓', '2000-01-01', '男'), (2, '陈小明', '2001-02-02', '男'), (3, '李晓', '2000-03-03', '女'), (4, '王晓晓', '2002-04-04', '女'), (5, '赵小明', '2001-05-05', '男');

2. 教师表（teacher）

   sql

   123456789

   CREATE TABLE teacher (    teacher_id INT PRIMARY KEY,    teacher_name VARCHAR(100) NOT NULL);​INSERT INTO teacher (teacher_id, teacher_name) VALUES(1, '钱红'),(2, '孙丽'),(3, '周强');

3. 课程表（course）

   sql

   1234567891011121314

   CREATE TABLE course (    course_id INT PRIMARY KEY,    course_name VARCHAR(100) NOT NULL,    teacher_id INT NOT NULL,    FOREIGN KEY (teacher_id) REFERENCES teacher(teacher_id));​INSERT INTO course (course_id, course_name, teacher_id) VALUES(1, '语文', 1),(2, '数学', 2),(3, '英语', 3),(4, '物理', 2),(5, '化学', 3),(6, '生物', 1);

4. 成绩表（score）

   sql

   12345678910111213141516171819202122232425

   CREATE TABLE score (    student_id INT NOT NULL,    course_id INT NOT NULL,    score DECIMAL(5, 2) NOT NULL,    PRIMARY KEY (student_id, course_id),    FOREIGN KEY (student_id) REFERENCES student(student_id),    FOREIGN KEY (course_id) REFERENCES course(course_id));​INSERT INTO score (student_id, course_id, score) VALUES(1, 1, 85.50),(1, 2, 90.00),(1, 3, 88.75),(1, 4, 92.50),(2, 1, 78.00),(2, 3, 88.50),(2, 5, 79.25),(3, 1, 92.00),(3, 2, 81.75),(3, 6, 89.00),(4, 3, 95.00),(4, 4, 87.00),(5, 2, 87.00),(5, 3, 89.25),(5, 6, 90.50);

5. 可视化展示

   

简单查询

入门热身

1. 查询所有男同学的信息

   sql

   123

   SELECT *FROM studentWHERE gender = '男';

2. 查询名字中包含“晓”字的学生

   sql

   123

   SELECT *FROM studentWHERE student_name LIKE '%晓%';

3. 查询姓“陈”的学生的个数

   sql

   123

   SELECT COUNT(student_id)FROM studentWHERE student_name LIKE '陈%';

4. 查询课程名称和对应的教师名字

   sql

   123

   SELECT course_name, teacher_nameFROM courseJOIN teacher ON course.teacher_id = teacher.teacher_id;

汇总和分组

查询每门课程的最高分、最低分、平均分

思路：

1. 确认数据表：需要查询每门课程的相关汇总分数，因此需要连接课程表和分数表

2. 确定要查询的信息：需要查询每门课程的最高分、最低分和平均分。因此，最终的结果应包含如下字段：course_name、max_score、min_score、avg_score。

3. SQL 构建：

   为了得到上述结果，需要进行以下步骤：

   • 表连接：将 score 表和 course 表连接起来，以便能够根据 course_id 获取到 course_name。
   • 聚合函数：利用聚合函数 MAX()、MIN() 和 AVG() 分别计算最高分、最低分和平均分。
   • 分组：使用 GROUP BY 子句对于 course_name 进行分组，以便对每门课程进行统计。

根据上述步骤，构建的 SQL 查询语句如下：

SELECT course_name, MAX(score) AS max_score, MIN(score) AS min_score, AVG(score) AS avg_scoreFROM scoreJOIN course ON score.course_id = course.course_idGROUP BY course_name;

查询每位学生的平均成绩（学生的每门课程分数相加取平均值）

SELECT student_name, AVG(score) AS avg_scoreFROM scoreJOIN student ON score.student_id = student.student_idGROUP BY student_name;

统计每门课程的选课人数

SELECT course_name, count(student_id) AS num_studentsFROM scoreJOIN course ON score.course_id = course.course_idGROUP BY course_name;

查询每位教师所教各门课程的平均成绩

SELECT teacher.teacher_name, course.course_name, AVG(score) AS avg_scoreFROM scoreJOIN course ON score.course_id = course.course_idJOIN teacher ON course.teacher_id = teacher.teacher_idGROUP BY teacher.teacher_name, course.course_name;

排序

查询所有学生的信息，按出生日期降序排列（降序是 DESC，升序是 ASC）。

SELECT *FROM studentORDER BY birth_date DESC;

查询所有课程的名称和对应的教师，按教师姓名升序排列。

SELECT course_name, teacher_nameFROM courseJOIN teacher ON course.teacher_id = teacher.teacher_idORDER BY teacher_name ASC;

查询每位学生的总成绩，按总成绩降序排列。

SELECT student_name, SUM(score) AS sum_scoreFROM scoreJOIN student ON student.student_id = score.student_idGROUP BY student_nameORDER BY sum_score DESC;

分组结果的条件

查询平均成绩大于 85 分的学生姓名和他们的平均成绩。

SELECT student_name, AVG(score) AS avg_scoreFROM scoreJOIN student ON student.student_id = score.student_idGROUP BY student_nameHAVING avg_score > 85;

查询每门课程的平均成绩低于 85 分的课程名称。

SELECT course_nameFROM scoreJOIN course ON course.course_id = score.course_idGROUP BY course_nameHAVING AVG(score) < 85;

查询选修了至少三门课程的学生姓名。

SELECT student_nameFROM scoreJOIN course ON course.course_id = score.course_idJOIN student ON student.student_id = score.student_idGROUP BY student_nameHAVING COUNT(student_name) >= 3;

左连接和右连接

内连接、左连接和右连接的区别

1. 像前面的所有连接使用的都是内连接，内连接只返回两个表中都存在的匹配行。
2. 左连接返回包括左表中所有记录和右表中匹配的记录。如果左表的某些行在右表中没有匹配，则结果中这些行的右表列显示为 NULL。
3. 右连接返回包括右表中所有记录和左表中匹配的记录。如果右表的某些行在左表中没有匹配，则结果中这些行的左表列显示为 NULL。

至于如何区分那个是左表那个是右表，只需要记住JOIN 关键字之前的就是左表，JOIN 关键字之后的就是右表。

查询所有学生及其对应的课程名称及其教师姓名（即使某些学生没有选课或某些课程还没有分配教师）。

SELECT s.student_name, c.course_name, t.teacher_nameFROM student sLEFT JOIN score sc ON s.student_id = sc.student_idLEFT JOIN course c ON sc.course_id = c.course_idLEFT JOIN teacher t ON c.teacher_id = t.teacher_id;

进阶查询

查询每位学生的每门课程的成绩，以及该课程中其他学生的平均成绩（注意这里的平均成绩不能把该学生自己的成绩算进去）。

分析：

如果这道问题的平均成绩是所有学生的平均成绩（也包括自己），那么很简单，SQL 如下：

SELECT student_name, course_name, score, AVG(score) AS avg_scoreFROM scoreJOIN student ON student.student_id = score.student_idJOIN course ON course.course_id = score.course_idGROUP BY student_name, course_name, score;

但是这道题的平均成绩偏偏不能把自己的成绩计算进去，那么就需要对于平均成绩这个列进行单独的计算，也就是需要单独写一个查询去计算平均成绩，然后再把平均成绩嵌入到原来的 SQL 当中。

所以完整 SQL 如下：

SELECT student_name, course_name, sc1.score,	(      SELECT AVG(sc2.score)      FROM score sc2      WHERE sc2.course_id = sc1.course_id AND sc2.student_id != sc1.student_id    ) AS avg_other_studentsFROM score sc1JOIN student ON student.student_id = sc1.student_idJOIN course ON course.course_id = sc1.course_id

查询每门课程的最高分及其学生姓名，如果有多个学生得分相同，则都显示

分析：

• 确定要查询的信息：需要查询到学生姓名、课程名和该课程的最高分。这三个信息来自 student 表、course 表和 score 表，所以注定是要连接这三张表的。

• 如何得到课程的最高得分？使用 max 函数可以吗？答案是可以，但是不推荐。因为题目有说明：如果有多个学生得分相同，则都显示。但是 max 函数对于多个最大值只会返回其中一个。要想解决这个问题需要使用连接子查询，这导致整个 SQL 逻辑复杂，所以不推荐。

• mysql 当中有一个窗口函数 RANK()，该函数可以对数据进行分组排名，如果存在名次相同的数据，后续的排名将会产生跳跃。比如有两个第一名，那么后面一个就是第三名。RANK()函数的语法如下，PARTITION BY partition_expression是可选部分，用于将结果集按指定的列分成不同的分区。每个分区内独立计算排名。

  sql

  1234

  RANK() OVER (    [PARTITION BY partition_expression]    ORDER BY sort_expression [ASC | DESC])

思路：

• 需要使用两个查询，这两个查询的关系为嵌套关系，父查询的任务是从子查询中选择出 course_name、student_name 和 score，并且只保留排名为 1 的记录。
• 子查询的作用是对每门课程中的每个学生的得分进行排名，并将排名结果与课程名称和学生姓名一起返回。

-- 父查询部分SELECT course_name, student_name, scoreFROM (    -- 子查询部分) ranked_scoresWHERE rnk = 1;​-- 子查询的结果被当作一个临时表，起名为 ranked_scores。-- 在 SQL 中，如果子查询作为一个临时表使用，必须给这个子查询一个别名。如果没有别名，SQL 语句会报错。

-- 子查询部分SELECT c.course_name, s.student_name, sc.score,       RANK() OVER (PARTITION BY sc.course_id ORDER BY sc.score DESC) as rnkFROM score scJOIN student s ON sc.student_id = s.student_idJOIN course c ON sc.course_id = c.course_id​-- 子查询首先连接三张表-- RANK() OVER (PARTITION BY sc.course_id ORDER BY sc.score DESC) as rnk:-- 使用 RANK() 函数对每门课程的学生按分数从高到低排名。-- 对于每个 course_id 分区内的行，根据 score 列降序排序，并为其分配排名。排名列别名为 rnk。

将父子查询合并后：

SELECT course_name, student_name, scoreFROM (    SELECT c.course_name, s.student_name, sc.score,           RANK() OVER (PARTITION BY sc.course_id ORDER BY sc.score DESC) as rnk    FROM score sc    JOIN student s ON sc.student_id = s.student_id    JOIN course c ON sc.course_id = c.course_id) ranked_scoresWHERE rnk = 1;

最后贴上 ChatGPT 使用 max 函数生成的 SQL 语句（难以理解这段 SQL），同样能解决上面的需求。

SELECT c.course_name, s.student_name, sc.scoreFROM score scJOIN student s ON sc.student_id = s.student_idJOIN course c ON sc.course_id = c.course_idJOIN (    SELECT course_id, MAX(score) as max_score    FROM score    GROUP BY course_id) max_scores ON sc.course_id = max_scores.course_id AND sc.score = max_scores.max_score;

查询每位学生的成绩排名（按总成绩降序排序），相同总成绩的学生排名相同。（前面 RANK()函数的一道衍生题）。

SELECT RANK() OVER (ORDER BY total_score DESC) as ranking, student_name, total_scoreFROM (    SELECT s.student_name, SUM(sc.score) as total_score    FROM student s    JOIN score sc ON s.student_id = sc.student_id    GROUP BY s.student_name) total_scores;

分析如下：

• 子查询的作用：首先连接学生表和分数表，返回学生姓名和学生的总成绩的一张临时表。
• 父查询的作用：根据 total_score 的值对这张临时表进行排序。