MySQL三表查询,实现全连接查询(mysql三表查询全连接)

MySQL三表查询,实现全连接查询!

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中全连接查询的方法,以及如何使用三张表联合查询学生的姓名、科目和成绩。在实际应用中,三表及以上的联合查询比较常见,开发者们应掌握该技能,提高开发效率。


数据运维技术 » MySQL三表查询,实现全连接查询(mysql三表查询全连接)