摘要：
我们可以利用工会来实现我们的目标。上的过滤顺序优先于以下位置：；Semantic on是两个表的连接条件。其中将过滤上一个结果集。6.扩展：fulljion全连接（oracle中有fulljoin，但mysql中没有fulljoin）。
一.什么是连接查询
1.连接查询是一种非常常见的数据库操作，在两张(或者多张)表中进行匹配.
2.以mysql为例,支持Cross join(交叉连接), inner join(内连接), outer join(外连接),等其他连接.
二、数据准备(创建emp员工表和dept部门表并插入数据)
SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for emp -- ---------------------------- DROP TABLE IF EXISTS `emp`; CREATE TABLE `emp` ( `id` int(11) NOT NULL, `name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL, `dept_id` int(11) DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE, INDEX `dept_id`(`dept_id`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of emp -- ---------------------------- INSERT INTO `emp` VALUES (1, '刘备', 3); INSERT INTO `emp` VALUES (2, '关羽', 3); INSERT INTO `emp` VALUES (3, '张飞', 3); INSERT INTO `emp` VALUES (4, '张角', 2); INSERT INTO `emp` VALUES (5, '张宝', 2); INSERT INTO `emp` VALUES (6, '张梁', 2); INSERT INTO `emp` VALUES (7, '曹操', 1); INSERT INTO `emp` VALUES (8, '曹丕', 1); INSERT INTO `emp` VALUES (9, '曹植', 1); INSERT INTO `emp` VALUES (10, '曹冲', 1); INSERT INTO `emp` VALUES (11, '孙权', 0); SET FOREIGN_KEY_CHECKS = 1;
SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for dept -- ---------------------------- DROP TABLE IF EXISTS `dept`; CREATE TABLE `dept` ( `id` int(11) NOT NULL, `name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of dept -- ---------------------------- INSERT INTO `dept` VALUES (1, '人力资源部'); INSERT INTO `dept` VALUES (2, '财务部'); INSERT INTO `dept` VALUES (3, '后勤部'); INSERT INTO `dept` VALUES (4, '法务部'); SET FOREIGN_KEY_CHECKS = 1;
三、连接实战
select * from dept; select * from emp; -- 交叉连接 select * from dept,emp; -- 44条数据 笛卡尔积 a表的长度 * b表的长度 select * from dept cross join emp; -- 笛卡尔积在查询的时候没有太大的意义，在数学上表示所有可能性 -- 内连接是查询出满足条件的数据 -- inner join 隐式内连接 select * from emp as a,dept as b where a.dept_id=b.id; -- 显式内连接 select * from emp as a join dept as b on a.dept_id = b.id; -- outer join 外连接(包含左外连接和右外连接) -- 左外连接(以左表为主表，右表为从表) -- 主表全部显示，从表只有匹配到才显示，如果没有匹配到就显示null select * from emp a left join dept b on a.dept_id=b.id; -- 右外连接(以右表为主表，左表为从表) select * from emp a right join dept b on a.dept_id=b.id;
四、例题(A表示emp表,B表示dept表)
select * from emp as a join dept as b on a.dept_id = b.id; -- 显式内连接
select * from emp as a left join dept as b on a.dept_id = b.id;
select * from emp as a right join dept as b on a.dept_id = b.id;
A表的非交集数据
select * from emp as a left join dept as b on a.dept_id = b.id where b.id is null;
B 表的非交集数据
select * from emp as a right join dept as b on a.dept_id = b.id where a.id is null;
-- union 全连接 select * from emp as a left join dept as b on a.dept_id = b.id union -- union 自动去重 select * from emp as a right join dept as b on a.dept_id = b.id; select * from emp as a left join dept as b on a.dept_id = b.id union all -- union all 不会自动去重 select * from emp as a right join dept as b on a.dept_id = b.id;
扩展:oracle里面有full join,但是在mysql中没有full join。我们可以使用union来达到目的。
select * from emp as a left join dept as b on a.dept_id = b.id where b.id is null union select * from emp as a right join dept as b on a.dept_id = b.id where a.dept_id is null;
五、扩展：where 和 on 的区别？
(1) 筛选的顺序 on 优先于 where ;
(2) 语意 on 是两张表的连接条件(外键关联), where会把前面的结果集进行筛选(可以筛选所有字段条件)
六 、扩展： full jion 全连接（oracle里面有full join,但是在mysql中没有full join。我们可以使用union来达到目的。）
-- full join 的用法 select a.id,a.name,a.dept_id,b.id,b.name from emp as a full join dept as b on a.dept_id = b.id;
七、总结 : SQL连接查询,柠檬班公开课学习后总结! 不足之处后续补充!