数据库多表连接查询每个学生的学号、姓名、选修的课程名及成绩,按成绩递增排列
时间: 2024-05-29 18:09:02 浏览: 15
可以使用 SQL 语句进行多表连接查询,具体如下:
SELECT student.id, student.name, course.course_name, score.score
FROM student
INNER JOIN score ON student.id = score.student_id
INNER JOIN course ON score.course_id = course.id
ORDER BY score ASC;
这条语句可以查询每个学生选修的课程及成绩,并按成绩递增排列。
相关问题
查询每个学生的学号、姓名、选修的课程名及成绩,按成绩递增排列
Sure, this is a query that can be performed on a relational database. Can you provide more details on the structure of the tables and attributes in the database so that I can help you write the SQL query?
数据库MySQL查询学生的学号、姓名、选修课程的课程名及成绩。(3表连接)
假设我们有三张表:`students`、`courses`和`scores`,分别存储学生信息、课程信息和成绩信息。它们的结构如下:
```
students表:
+----+--------+
| id | name |
+----+--------+
| 1 | Alice |
| 2 | Bob |
| 3 | Charlie|
+----+--------+
courses表:
+----+-----------+
| id | name |
+----+-----------+
| 1 | Math |
| 2 | Physics |
| 3 | Chemistry |
+----+-----------+
scores表:
+----+------------+--------+-------+
| id | student_id | course_id | score |
+----+------------+----------+-------+
| 1 | 1 | 1 | 90 |
| 2 | 1 | 2 | 85 |
| 3 | 2 | 1 | 95 |
| 4 | 2 | 3 | 80 |
| 5 | 3 | 2 | 92 |
| 6 | 3 | 3 | 88 |
+----+------------+----------+-------+
```
我们可以通过以下 SQL 语句查询学生的学号、姓名、选修课程的课程名及成绩:
```
SELECT students.id, students.name, courses.name, scores.score
FROM students
INNER JOIN scores ON students.id = scores.student_id
INNER JOIN courses ON scores.course_id = courses.id;
```
运行结果如下:
```
+----+--------+-----------+-------+
| id | name | name | score |
+----+--------+-----------+-------+
| 1 | Alice | Math | 90 |
| 1 | Alice | Physics | 85 |
| 2 | Bob | Math | 95 |
| 2 | Bob | Chemistry | 80 |
| 3 | Charlie| Physics | 92 |
| 3 | Charlie| Chemistry | 88 |
+----+--------+-----------+-------+
```
相关推荐
![zip](https://img-home.csdnimg.cn/images/20210720083736.png)
![rar](https://img-home.csdnimg.cn/images/20210720083606.png)
![](https://img-home.csdnimg.cn/images/20210720083646.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)