MySQL 连接查询

一.什么是连接查询

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连接查询,柠檬班公开课学习后总结! 不足之处后续补充!