引言： 在今天的数据库应用中，多表查询是一项非常重要的技能。它允许我们从不同的数据表中获取有关联的数据，并且在实际的应用中非常常见。本篇博客将带你一步步了解如何使用多表查询来创建一个简单的学生成绩管理系统，以学生和成绩两个表为例进行演示。
第一步：创建数据库和数据表
首先，我们需要创建一个数据库，我们将其命名为"学生成绩管理系统"。然后，在该数据库中创建两个数据表，分别为"student"和"score"表。"student"表用于存储学生的基本信息，"score"表用于存储学生的成绩信息。
CREATE DATABASE 学生成绩管理系统; USE 学生成绩管理系统; CREATE TABLE student ( id INT(10) NOT NULL UNIQUE PRIMARY KEY, name VARCHAR(20) NOT NULL, sex VARCHAR(4), birth YEAR, department VARCHAR(20), address VARCHAR(50) ); CREATE TABLE score ( id INT(10) NOT NULL UNIQUE PRIMARY KEY AUTO_INCREMENT, stu_id INT(10) NOT NULL, c_name VARCHAR(20), grade INT(10) );
第二步：插入数据
接下来，我们向"student"表和"score"表中插入一些示例数据，以便后续查询和演示。
INSERT INTO student VALUES(901, '张老大', '男', 1985, '计算机系', '北京市海淀区'); INSERT INTO student VALUES(902, '张老二', '男', 1986, '中文系', '北京市昌平区'); INSERT INTO student VALUES(903, '张三', '女', 1990, '中文系', '湖南省永州市'); INSERT INTO student VALUES(904, '李四', '男', 1990, '英语系', '辽宁省阜新市'); INSERT INTO student VALUES(905, '王五', '女', 1991, '英语系', '福建省厦门市'); INSERT INTO student VALUES(906, '王六', '男', 1988, '计算机系', '湖南省衡阳市'); INSERT INTO score VALUES(NULL, 901, '计算机', 98); INSERT INTO score VALUES(NULL, 901, '英语', 80); INSERT INTO score VALUES(NULL, 902, '计算机', 65); INSERT INTO score VALUES(NULL, 902, '中文', 88); INSERT INTO score VALUES(NULL, 903, '中文', 95); INSERT INTO score VALUES(NULL, 904, '计算机', 70); INSERT INTO score VALUES(NULL, 904, '英语', 92); INSERT INTO score VALUES(NULL, 905, '英语', 94); INSERT INTO score VALUES(NULL, 906, '计算机', 90); INSERT INTO score VALUES(NULL, 906, '英语', 85);
第三步：多表查询实例
1.查询student表的所有记录
SELECT * FROM student;
2.查询student表的第2条到4条记录
SELECT * FROM student LIMIT 1 OFFSET 1; -- This will fetch the second record SELECT * FROM student LIMIT 1 OFFSET 2; -- This will fetch the third record SELECT * FROM student LIMIT 1 OFFSET 3; -- This will fetch the fourth record
3. 从student表查询所有学生的学号（id）、姓名（name）和院系（department）的信息
SELECT id, name, department FROM student;
4.从student表中查询计算机系和英语系的学生的信息
SELECT * FROM student WHERE department IN ('计算机系', '英语系');
5.从student表中查询年龄18~22岁的学生信息
SELECT * FROM student WHERE YEAR(CURRENT_DATE) - birth BETWEEN 18 AND 22;
6.从student表中查询每个院系有多少人
SELECT department, COUNT(*) AS total_students FROM student GROUP BY department;
7.从score表中查询每个科目的最高分
SELECT c_name, MAX(grade) AS max_grade FROM score GROUP BY c_name;
8.查询李四的考试科目（c_name）和考试成绩（grade
SELECT c_name, grade FROM score WHERE stu_id = (SELECT id FROM student WHERE name = '李四');
9.用连接的方式查询所有学生的信息和考试信息
SELECT s.id, s.name, s.sex, s.birth, s.department, s.address, sc.c_name, sc.grade FROM student s JOIN score sc ON s.id = sc.stu_id;
10. 计算每个学生的总成绩
SELECT s.id, s.name, SUM(sc.grade) AS total_score FROM student s JOIN score sc ON s.id = sc.stu_id GROUP BY s.id, s.name;
11. 计算每个考试科目的平均成绩
SELECT c_name, AVG(grade) AS average_grade FROM score GROUP BY c_name;
12.查询计算机成绩低于95的学生信息
SELECT s.id, s.name, s.department, sc.c_name, sc.grade FROM student s JOIN score sc ON s.id = sc.stu_id WHERE sc.c_name = '计算机' AND sc.grade < 95;
13. 查询同时参加计算机和英语考试的学生的信息
SELECT s.id, s.name, s.department, sc.c_name, sc.grade FROM student s JOIN score sc ON s.id = sc.stu_id WHERE sc.c_name IN ('计算机', '英语') GROUP BY s.id, s.name, s.department HAVING COUNT(DISTINCT sc.c_name) = 2;
14. 将计算机考试成绩按从高到低进行排序
SELECT s.id, s.name, s.department, sc.c_name, sc.grade FROM student s JOIN score sc ON s.id = sc.stu_id WHERE sc.c_name = '计算机' ORDER BY sc.grade DESC;
15. 从student表和score表中查询出学生的学号，然后合并查询结果
SELECT s.id AS stu_id, s.name AS stu_name, sc.c_name, sc.grade FROM student s LEFT JOIN score sc ON s.id = sc.stu_id;
16. 查询姓张或者姓王的同学的姓名、院系和考试科目及成绩
SELECT s.name, s.department, sc.c_name, sc.grade FROM student s JOIN score sc ON s.id = sc.stu_id WHERE s.name LIKE '张%' OR s.name LIKE '王%';
17.查询都是湖南的学生的姓名、年龄、院系和考试科目及成绩
SELECT s.name, YEAR(CURRENT_DATE) - s.birth AS age, s.department, sc.c_name, sc.grade FROM student s JOIN score sc ON s.id = sc.stu_id WHERE s.address LIKE '%湖南%';
总结
本文以学生成绩管理系统为例，通过多表查询实例的展示，详细介绍了使用SQL语句从不同数据表中获取相关联数据的方法。掌握多表查询的技巧可以帮助我们在实际应用中更高效地操作数据库，提高数据检索和分析的效率。希望这篇博文能够对你在学习和应用多表查询时有所帮助。希望能够点个关注，有问题也可以私信询问。
