MySQL三表查询实现全连接查询mysql三表查询全连接
最新回答
狙击甜心
2024-11-24 11:44:28
MySQL是一种常用的关系型数据库管理系统，它能够快速、高效地存储和获取数据。在实际应用中，常常需要对多张数据表进行联合查询。本文将介绍如何使用MySQL实现三表的全连接查询。
一、什么是全连接查询？
在MySQL中，联合查询共分为三种方式：内连接查询、左连接查询和右连接查询。而全连接查询，是内连接查询和左连接查询的并集。也就是说，全连接查询会返回左表和右表中所有的行，如果在另一张表中也存在匹配的行，则一并返回。
二、实现三表全连接查询的流程
本次使用以下三张表：
students表
| id | name | gender |
|—-|———-|——–|
| 1 | Tom | Male |
| 2 | Jerry | Male |
| 3 | Angela | Female |
| 4 | Ginger | Female |
| 5 | Hatchet | Male |
scores表
| id | student_id | language | score |
|—-|————|———-|——-|
| 1 | 1 | Chinese | 85 |
| 2 | 1 | English | 90 |
| 3 | 2 | Chinese | 70 |
| 4 | 2 | English | 80 |
| 5 | 3 | Chinese | 92 |
| 6 | 3 | English | 96 |
| 7 | 4 | Chinese | 78 |
| 8 | 5 | Chinese | 60 |
subjects表
| id | subject |
|—-|———|
| 1 | Chinese |
| 2 | English |
| 3 | Math |
我们的任务是，在三张表中联合查询学生的姓名、科目和成绩。
1. 创建三张表
CREATE TABLE students(
id INT(11) PRIMARY KEY,
name VARCHAR(20) NOT NULL,
gender VARCHAR(10)
);
CREATE TABLE scores(
id INT(11) PRIMARY KEY,
student_id INT(11) NOT NULL,
language VARCHAR(20),
score INT(11)
);
CREATE TABLE subjects(
id INT(11) PRIMARY KEY,
subject VARCHAR(20) NOT NULL
);
2. 插入数据
INSERT INTO students(id,name,gender) VALUES(1,’Tom’,’Male’);
INSERT INTO students(id,name,gender) VALUES(2,’Jerry’,’Male’);
INSERT INTO students(id,name,gender) VALUES(3,’Angela’,’Female’);
INSERT INTO students(id,name,gender) VALUES(4,’Ginger’,’Female’);
INSERT INTO students(id,name,gender) VALUES(5,’Hatchet’,’Male’);
INSERT INTO scores(id,student_id,language,score) VALUES(1,1,’Chinese’,85);
INSERT INTO scores(id,student_id,language,score) VALUES(2,1,’English’,90);
INSERT INTO scores(id,student_id,language,score) VALUES(3,2,’Chinese’,70);
INSERT INTO scores(id,student_id,language,score) VALUES(4,2,’English’,80);
INSERT INTO scores(id,student_id,language,score) VALUES(5,3,’Chinese’,92);
INSERT INTO scores(id,student_id,language,score) VALUES(6,3,’English’,96);
INSERT INTO scores(id,student_id,language,score) VALUES(7,4,’Chinese’,78);
INSERT INTO scores(id,student_id,language,score) VALUES(8,5,’Chinese’,60);
INSERT INTO subjects(id,subject) VALUES(1,’Chinese’);
INSERT INTO subjects(id,subject) VALUES(2,’English’);
INSERT INTO subjects(id,subject) VALUES(3,’Math’);
3. 三表联合查询
SELECT
students.name AS student_name,
subjects.subject AS subject_name,
scores.score AS score
FROM
students
FULL OUTER JOIN scores ON students.id = scores.student_id
FULL OUTER JOIN subjects ON scores.language = subjects.subject
ORDER BY
students.name,
subjects.subject;
结果如下：
| student_name | subject_name | score |
|————–|————-|——-|
| Angela | Chinese | 92 |
| Angela | English | 96 |
| Ginger | Chinese | 78 |
| Hatchet | Chinese | 60 |
| Jerry | Chinese | 70 |
| Jerry | English | 80 |
| Tom | Chinese | 85 |
| Tom | English | 90 |
| NULL | Math | NULL |
本次查询使用了FULL OUTER JOIN方法。该方法会返回两张表的所有行，如果某一方没有匹配的数据，则取NULL。当然，由于MySQL不支持FULL OUTER JOIN，我们可以使用UNION ALL连接笛卡尔积的左右联接并使用SELECT DISTINCT去除重复。
SELECT
students.name AS student_name,
subjects.subject AS subject_name,
scores.score AS score
FROM
students
LEFT JOIN scores ON students.id = scores.student_id
LEFT JOIN subjects ON scores.language = subjects.subject
UNION ALL
SELECT
students.name AS student_name,
subjects.subject AS subject_name,
scores.score AS score
FROM
students
RIGHT JOIN scores ON students.id = scores.student_id
RIGHT JOIN subjects ON scores.language = subjects.subject
WHERE
students.id IS NULL
ORDER BY
student_name,
subject_name;
结果和上述FULL OUTER JOIN的结果相同。
三、总结
本文介绍了MySQL中全连接查询的方法，以及如何使用三张表联合查询学生的姓名、科目和成绩。在实际应用中，三表及以上的联合查询比较常见，开发者们应掌握该技能，提高开发效率。
热门标签
