目录Mysql内连接、左连接、右连接、外连接、多表查询构建环境:一、INNERJioN内连接(A∩B)二、LEFTJOIN左外连接(A全有)三、RIGHTJOIN右外连接(B全有)四
create table t_emp(
id int primary key,
name varchar(20),
deptId int
);
create table t_dept(
id int primary key,
name varchar(20)
);
insert into t_dept(id, name) values(1, '设计部');
insert into t_dept(id, name) values(2, '开发部');
insert into t_dept(id, name) values(3, '测试部');
insert into t_emp(id, name, deptId) values(1, '张三', 1);
insert into t_emp(id, name, deptId) values(2, '李四', 2);
insert into t_emp(id, name, deptId) values(3, '王五', 0);
# ps:为了说明方便,t_emp 表 说成 A 表, t_dept 表说成 B 表
目录
SELECT * FROM t_emp e INNER JOIN t_dept d ON e.deptId = d.id;
SELECT * FROM t_emp e LEFT JOIN t_dept d ON e.deptId = d.id;
SELECT * FROM t_emp e RIGHT JOIN t_dept d ON e.deptId = d.id;
SELECT * FROM t_emp e LEFT JOIN t_dept d
ON e.deptId = d.id UNION
SELECT * FROM t_emp e RIGHT JOIN t_dept d ON e.deptId = d.id;
SELECT * FROM t_emp e LEFT JOIN t_dept d ON e.deptId= d.id WHERE d.id is null;
SELECT * FROM t_emp e RIGHT JOIN t_dept d ON e.deptId= d.id WHERE e.id is null;
SELECT * FROM t_emp e LEFT JOIN t_dept d ON e.deptId= d.id WHERE d.id is null
UNION
SELECT * FROM t_emp e RIGHT JOIN t_dept d ON e.deptId= d.id WHERE e.id is null;
本篇文章就到这里了,希望能给你带来帮助,也希望您能够多多关注编程网的更多内容!
--结束END--
本文标题: MySQL 四种连接和多表查询详解
本文链接: https://www.lsjlt.com/news/130643.html(转载时请注明来源链接)
有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
下载Word文档到电脑,方便收藏和打印~
2024-05-21
2024-05-21
2024-05-21
2024-05-21
2024-05-21
2024-05-21
2024-05-21
2024-05-21
2024-05-21
2024-05-21
回答
回答
回答
回答
回答
回答
回答
回答
回答
回答
0