文章目录 连接七种JOIN介绍练习建表+存储数据笛卡尔积内连接左连接右连接A表独有B表独有全连接实现方式一:左连接+右连接+公共部分去重实现方式二:左连接+B独有 A独有+B独有
图形 | 连接方式 | 说明 | sql |
---|---|---|---|
内连接 | 共有部分 | SELECT | |
左连接 | A表独有+共有部分 | SELECT | |
右连接 | B表独有+共有部分 | SELECT | |
A的独有 | SELECT | ||
B的独有 | SELECT | ||
全连接 | A的独有+共有部分+B的独有 | SELECT | |
A独有+B独有 | SELECT |
CREATE TABLE `tbl_emp` (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(20) DEFAULT NULL,`deptId` int(11) DEFAULT NULL,PRIMARY KEY (`id`) ,KEY `fk_dept_id`(`deptId`))ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8;CREATE TABLE `tbl_dept` (`id` int(11) NOT NULL AUTO_INCREMENT,`deptName` varchar(30) DEFAULT NULL,`locAdd` varchar(40) DEFAULT NULL,PRIMARY KEY (`id`)) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8;insert into tbl_dept(deptName,locAdd) values('RD',11);insert into tbl_dept(deptName,locAdd) values('HR',12);insert into tbl_dept(deptName,locAdd) values('MK',13);insert into tbl_dept(deptName,locAdd) values('MIS',14);insert into tbl_dept(deptName,locAdd) values('FD',15);insert into tbl_emp(NAME,deptId) values('z3',1);insert into tbl_emp(NAME,deptId) values('z4',1);insert into tbl_emp(NAME,deptId) values('z5',1);insert into tbl_emp(NAME,deptId) values('w5',2);insert into tbl_emp(NAME,deptId) values('w6',2);insert into tbl_emp(NAME,deptId) values('s7',3);insert into tbl_emp(NAME,deptId) values('s8',4);insert into tbl_emp(NAME,deptId) values('s9',51);
select * from tbl_emp,tbl_dept;
5条数据
和8条数据
两两组合,一共40条
,结果如下
"id" "name" "deptId" "id" "deptName" "locAdd""1" "z3" "1" "5" "FD" "15""1" "z3" "1" "4" "MIS" "14""1" "z3" "1" "3" "MK" "13""1" "z3" "1" "2" "HR" "12""1" "z3" "1" "1" "RD" "11""2" "z4" "1" "5" "FD" "15""2" "z4" "1" "4" "MIS" "14""2" "z4" "1" "3" "MK" "13""2" "z4" "1" "2" "HR" "12""2" "z4" "1" "1" "RD" "11""3" "z5" "1" "5" "FD" "15""3" "z5" "1" "4" "MIS" "14""3" "z5" "1" "3" "MK" "13""3" "z5" "1" "2" "HR" "12""3" "z5" "1" "1" "RD" "11""4" "w5" "2" "5" "FD" "15""4" "w5" "2" "4" "MIS" "14""4" "w5" "2" "3" "MK" "13""4" "w5" "2" "2" "HR" "12""4" "w5" "2" "1" "RD" "11""5" "w6" "2" "5" "FD" "15""5" "w6" "2" "4" "MIS" "14""5" "w6" "2" "3" "MK" "13""5" "w6" "2" "2" "HR" "12""5" "w6" "2" "1" "RD" "11""6" "s7" "3" "5" "FD" "15""6" "s7" "3" "4" "MIS" "14""6" "s7" "3" "3" "MK" "13""6" "s7" "3" "2" "HR" "12""6" "s7" "3" "1" "RD" "11""7" "s8" "4" "5" "FD" "15""7" "s8" "4" "4" "MIS" "14""7" "s8" "4" "3" "MK" "13""7" "s8" "4" "2" "HR" "12""7" "s8" "4" "1" "RD" "11""8" "s9" "51" "5" "FD" "15""8" "s9" "51" "4" "MIS" "14""8" "s9" "51" "3" "MK" "13""8" "s9" "51" "2" "HR" "12""8" "s9" "51" "1" "RD" "11"
SELECT * FROM tbl_emp a INNER JOIN tbl_dept b ON a.deptId = b.id;
SELECT * FROM tbl_emp a LEFT JOIN tbl_dept b ON a.deptId = b.id;
SELECT * FROM tbl_emp a RIGHT JOIN tbl_dept b ON a.deptId = b.id;
SELECT * FROM tbl_emp a LEFT JOIN tbl_dept b ON a.deptId = b.id WHERE b.id is null;
SELECT * FROM tbl_emp a RIGHT JOIN tbl_dept b ON a.deptId = b.id WHERE a.deptId is null;
尝试换一种方式实现:A的独有+共有+B的独有
SELECT * FROM tbl_emp a LEFT JOIN tbl_dept b ON a.deptId = b.id UNION SELECT * FROM tbl_emp a RIGHT JOIN tbl_dept b ON a.deptId = b.id;
SELECT * FROM tbl_emp a LEFT JOIN tbl_dept b ON a.deptId = b.id UNioN SELECT * FROM tbl_emp a RIGHT JOIN tbl_dept b ON a.deptId = b.id WHERE a.deptId is null;
SELECT * FROM tbl_emp a LEFT JOIN tbl_dept b ON a.deptId = b.id WHERE b.id is null UNION SELECT * FROM tbl_emp a RIGHT JOIN tbl_dept b ON a.deptId = b.id WHERE a.deptId is null;
本文章为本人学习尚硅谷的学习笔记,文章中大部分内容来源于尚硅谷视频(点击学习尚硅谷相关课程),也有部分内容来自于自己的思考,发布文章是想帮助其他学习的人更方便地整理自己的笔记或者直接通过文章学习相关知识,如有侵权请联系删除,最后对尚硅谷的优质课程表示感谢。
来源地址:https://blog.csdn.net/laodanqiu/article/details/131233741
--结束END--
本文标题: 【MYSQL高级】Mysql 表的七种连接方式【附带练习sql】
本文链接: https://www.lsjlt.com/news/411395.html(转载时请注明来源链接)
有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
下载Word文档到电脑,方便收藏和打印~
2024-05-12
2024-05-12
2024-05-12
2024-05-12
2024-05-11
2024-05-11
2024-05-11
2024-05-11
2024-05-11
2024-05-11
回答
回答
回答
回答
回答
回答
回答
回答
回答
回答
0