多表联合查询 -- 一对多

前言 -- 创建表

创建老师表，并添加数据

CREATE TABLE teacher(
    id INT(3) PRIMARY KEY auto_increment,
    name VARCHAR(32)
)
INSERT INTO teacher(name) VALUES('何老师');
INSERT INTO teacher(name) VALUES('苍老师');
INSERT INTO teacher(name) VALUES('言老师');

创建学生表，并添加数据

CREATE TABLE student(
    id INT(3) PRIMARY KEY auto_increment,
    name VARCHAR(32),
    t_id INT(3)
)
INSERT INTO student(name,t_id) VALUES('刘彦含',1);
INSERT INTO student(name,t_id) VALUES('江星谊',1);
INSERT INTO student(name,t_id) VALUES('马智威',1);
INSERT INTO student(name,t_id) VALUES('张海杰',2);
INSERT INTO student(name,t_id) VALUES('张子琦',2);
INSERT INTO student(name) VALUES('陈宽朗');

内连接

理解：查询出多表之间有联系的数据

SELECT s.name,t.name FROM student s, teacher t WHERE s.t_id=t.id;
​
SELECT s.name,t.name FROM student s INNER JOIN teacher t ON s.t_id=t.id;

外连接

左连接（针对于左边表查询数据）

SELECT s.name,t.name FROM student s LEFT JOIN teacher t ON s.t_id=t.id;

右链接（针对于右边表查询数据）

SELECT s.name,t.name FROM student s RIGHT JOIN teacher t ON s.t_id=t.id; 

全连接

合并查询结果

SELECT s.name,t.name FROM student s LEFT JOIN teacher t ON s.t_id=t.id
UNION ALL
SELECT s.name,t.name FROM student s RIGHT JOIN teacher t ON s.t_id=t.id;

全连接

注意

MySQL中没有全连接的sql命令，使用合并查询+去重实现全连接的思想

SELECT s.name,t.name FROM student s LEFT JOIN teacher t ON s.t_id = t.id
UNION
SELECT s.name,t.name FROM student s RIGHT JOIN teacher t ON s.t_id = t.id;