本资源 主要是根据两个表(student)和科目分数表(grade)的sql语句查询，适合目前学习mysql数据库进行练习多表查询的操作。主要是针对三个问题展开的①列出各门课程成绩最好的2位学生，要求显示字段:学号，姓名，科目，成绩；②计算每个人的成绩，总分数，平均分，要求显示学号，姓名，语文，数学，英语，总分，平均分;③列出数学成绩的排名（要求显示字段：学号，姓名，成绩，排名）。资源里展现了从创建数据库到查询的全部过程。
### 查询学生不同科目的成绩
#### 背景与目标
在本篇文章中，我们将通过一个具体的案例来探讨如何利用SQL语句实现多表查询，特别是针对学生表(student)和科目分数表(grade)的数据查询。这个案例非常适合正在学习MySQL数据库的同学作为练习，通过实际操作加深对多表联查的理解。本文将详细介绍以下三个问题：
1. 列出各门课程成绩最好的2位学生，并显示学号、姓名、科目和成绩。
2. 计算每位学生的总分数和平均分，并显示学号、姓名、语文、数学、英语的成绩及总分和平均分。
3. 列出数学成绩的排名，并显示学号、姓名、成绩和排名。
#### 数据库与数据表准备
为了能够进行多表查询练习，首先我们需要创建一个数据库以及相应的数据表，并填充必要的数据。具体步骤如下：
1. **创建数据库**：
```sql
CREATE DATABASE test9;
USE test9;
```
2. **创建学生表**：
```sql
CREATE TABLE student (
id INT(4) PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(18),
age TINYINT(3)
) ENGINE = InnoDB CHARSET = 'utf8' AUTO_INCREMENT = 1001;
```
向学生表中添加数据：
```sql
INSERT INTO student () VALUES (1001, '张三', 18);
INSERT INTO student () VALUES (1002, '张思', 19);
INSERT INTO student () VALUES (1003, '张良', 23);
INSERT INTO student () VALUES (1004, '张武', 21);
```
3. **创建科目和分数表**：
```sql
CREATE TABLE grade (
No INT(4) PRIMARY KEY AUTO_INCREMENT,
Id INT NOT NULL,
Kemu VARCHAR(10),
Score FLOAT(5, 2),
FOREIGN KEY (Id) REFERENCES student(id)
) ENGINE = InnoDB CHARSET = 'utf8' AUTO_INCREMENT = 1;
```
向科目分数表中添加数据：
```sql
INSERT INTO grade () VALUES (1, 1001, '语文', 85.00);
INSERT INTO grade () VALUES (2, 1001, '数学', 86.00);
INSERT INTO grade () VALUES (3, 1002, '英语', 98.00);
INSERT INTO grade () VALUES (4, 1002, '语文', 94.00);
INSERT INTO grade () VALUES (5, 1002, '数学', 56.00);
INSERT INTO grade () VALUES (6, 1003, '数学', 87.00);
INSERT INTO grade () VALUES (7, 1003, '语文', 91.00);
INSERT INTO grade () VALUES (8, 1003, '英语', 78.00);
INSERT INTO grade () VALUES (9, 1004, '英语', 99.00);
INSERT INTO grade () VALUES (10, 1004, '数学', 92.00);
```
#### 解决方案详解
**问题一：列出各门课程成绩最好的2位学生**
为了找出每门课程成绩最好的两位学生，我们可以先通过`SELECT`语句将学生表与科目分数表联接起来，然后使用子查询来筛选出每个科目成绩最好的前两名学生。具体查询语句如下：
```sql
SELECT a.id, a.name, a.kemu, a.score
FROM
(SELECT s.id, s.name, g.kemu, g.score FROM student s JOIN grade g ON s.id = g.Id) AS a
WHERE
(SELECT COUNT(*) FROM (SELECT s.id, s.name, g.kemu, g.score FROM student s JOIN grade g ON s.id = g.Id) AS b WHERE b.kemu = a.kemu AND b.score >= a.score) <= 2
ORDER BY a.kemu ASC, a.score DESC;
```
**问题二：计算每个人的成绩、总分数、平均分**
为了计算每位学生的总分和平均分，我们需要使用`JOIN`操作将学生表与科目分数表联接起来，并使用聚合函数如`SUM()`和`AVG()`来完成计算。具体查询语句如下：
```sql
SELECT s.id, s.name,
MAX(CASE WHEN g.kemu = '语文' THEN g.score ELSE 0 END) AS 语文,
MAX(CASE WHEN g.kemu = '数学' THEN g.score ELSE 0 END) AS 数学,
MAX(CASE WHEN g.kemu = '英语' THEN g.score ELSE 0 END) AS 英语,
SUM(g.score) AS 总分,
AVG(g.score) AS 平均分
FROM student s JOIN grade g ON s.id = g.Id
GROUP BY s.id;
```
**问题三：列出数学成绩的排名**
为了获取数学成绩的排名，我们可以再次使用子查询来计算每位学生的数学成绩排名。具体查询语句如下：
```sql
SELECT a.id, a.name, a.score,
(SELECT COUNT(*) FROM grade WHERE kemu = '数学' AND score > a.score) + 1 AS 名次
FROM
(SELECT s.id, s.name, g.kemu, g.score FROM student s JOIN grade g ON s.id = g.Id) AS a
WHERE a.kemu = '数学'
ORDER BY a.score DESC;
```
以上查询语句可以有效地解决所提出的问题，并帮助读者更好地理解如何在MySQL中执行复杂的多表查询操作。通过这些示例，读者不仅可以掌握基本的SQL语法，还可以学习如何处理实际场景中的复杂查询需求。
- 1
- 2
前往页