目录
MySql学习专栏
3. MySQL5.7开启binlog日志,及数据恢复简单示例
在使用数据库查询语句时,单表的查询有时候不能满足项目的业务需求,在项目开发过程中,有很多需求都是要涉及到多表的连接查询,总结一下mysql中的多表关联查询。
一、创建表结构SQL和数据
CREATE TABLE "employee" (
"id" int(11) NOT NULL AUTO_INCREMENT,
"user_name" varchar(32) CHARACTER SET utf8 NOT NULL DEFAULT '' COMMENT '姓名',
"age" int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
"gender" tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '性别 (0-男;1-女;2:未知)',
"dept_id" int(11) DEFAULT NULL COMMENT '部门ID',
"boss_id" int(11) DEFAULT NULL COMMENT 'bossId',
"position" varchar(32) CHARACTER SET utf8 NOT NULL DEFAULT '' COMMENT '职位',
"create_time" datetime DEFAULT NULL COMMENT '创建时间',
"status" tinyint(1) DEFAULT NULL COMMENT '状态',
PRIMARY KEY ("id")
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=REDUNDANT COMMENT='员工表';
CREATE TABLE "dept" (
"id" int(3) NOT NULL AUTO_INCREMENT,
"dept_name" varchar(20) DEFAULT NULL,
PRIMARY KEY ("id")
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COMMENT='部门表';
INSERT INTO `employee`(`id`, `user_name`, `age`, `gender`, `dept_id`, `boss_id`, `position`, `create_time`, `status`) VALUES (1, 'change', 28, 0, 1, NULL, 'java开发', '2021-02-04 13:21:37', 0);
INSERT INTO `employee`(`id`, `user_name`, `age`, `gender`, `dept_id`, `boss_id`, `position`, `create_time`, `status`) VALUES (2, 'Lilei', 27, 0, 2, 2, 'payton开发', '2021-02-03 13:22:16', 1);
INSERT INTO `employee`(`id`, `user_name`, `age`, `gender`, `dept_id`, `boss_id`, `position`, `create_time`, `status`) VALUES (3, 'Bill', 23, 0, 3, 3, 'PHP开发', '2021-01-06 13:22:49', 2);
INSERT INTO `employee`(`id`, `user_name`, `age`, `gender`, `dept_id`, `boss_id`, `position`, `create_time`, `status`) VALUES (4, 'Liping.Zou', 28, 0, 4, 4, 'java开发', '2021-02-04 20:46:02', 2);
INSERT INTO `employee`(`id`, `user_name`, `age`, `gender`, `dept_id`, `boss_id`, `position`, `create_time`, `status`) VALUES (5, 'sundy', 28, 0, 5, 5, 'java开发', '2021-02-04 20:46:02', 0);
INSERT INTO `employee`(`id`, `user_name`, `age`, `gender`, `dept_id`, `boss_id`, `position`, `create_time`, `status`) VALUES (6, 'smile', 30, 0, NULL, 6, 'java开发', '2021-02-04 20:46:02', 1);
INSERT INTO `dept`(`id`, `dept_name`) VALUES (1, '软件开发部门');
INSERT INTO `dept`(`id`, `dept_name`) VALUES (2, '市场部门');
INSERT INTO `dept`(`id`, `dept_name`) VALUES (3, '运营部门');
INSERT INTO `dept`(`id`, `dept_name`) VALUES (4, '人力资源部门');
INSERT INTO `dept`(`id`, `dept_name`) VALUES (6, '总裁部门');
二、内连接查询
关键字:inner join on
特征:组合两个表中的记录,返回关联字段相符的记录,也就是返回两个表的交集(阴影)部分。
以employee(员工表)和dept(部门表)为例:
employee表中的记录如下:dept_id 代表该员工所在的部门
select * from employee;
dept表中记录如下
select * from dept;
可以发现,其中总裁部门里没有员工(这里只是举例,可能与实际不符,但主要在于逻辑关系),而smile没有对应的部门,
现在想要查询出员工姓名以及其对应的部门名称:
关联查询sql编写的思路
- 先确定所连接的表
- 再确定所要查询的字段
- 确定连接条件以及连接方式
SELECT
e.user_name,
d.dept_name
FROM
employee e
INNER JOIN dept d ON e.dept_id = d.id;
查询的结果如下:
其中,没有部门的人员和部门没有员工的部门都没有被查询出来,这就是内连接的特点,只查询在连接的表中能够有对应的记录,其中e.dept_id = d.id是连接条件
三、左外连接查询
关键字:left join on / left outer join on
特征:是指以左边的表的数据为基准,去匹配右边的表的数据,如果匹配到就显示,匹配不到就显示为Null
查询所有员工姓名以及他所在的部门名称:在内连接中 sundy和smile 没有被查出来,因为他没有对应的部门,现在想要把 sundy和smile 也查出来,就要使用左外连接:
SELECT
e.user_name,
d.dept_name
FROM
employee e
LEFT JOIN dept d ON e.dept_id = d.id;
在这里,employee 就是左表,也就是基准表,用基准表的数据去匹配右表的数据,所以左表的记录是全部会查询出来的,如果右表没有记录对应的话就显示Null
查询结果:
关键字是left outer join,等效于 left join,在关联查询中,做外连接查询就是左连接查询,两者是一个概念
四、右外连接查询
关键字:right join on / right outer join on
特征:right join是right outer join的简写,它的全称是右外连接,是外连接中的一种。与左(外)连接相反,右(外)连接,左表(employee)只会显示符合搜索条件的记录,而右表(dept)的记录将会全部表示出来。左表记录不足的地方均为NULL。
比如:查询所有的部门和对应的员工:
SELECT
e.user_name,
d.dept_name
FROM
employee e
RIGHT JOIN dept d ON e.dept_id = d.id;
这里只是把left修改成了right,但是基准表变化了,是以右表的数据去匹配左表,所以左外连接能做到的查询,右外连接也能做到
查询结果:
五、全外连接
关键字:union / union all
特征:顾名思义,把两张表的字段都查出来,没有对应的值就显示Null,但是注意:mysql是没有全外连接的(mysql中没有full outer join关键字),想要达到全外连接的效果,可以使用union关键字连接左外连接和右外连接。
union语句注意事项
- 通过union连接的SQL它们分别单独取出的列数必须相同;
- 不要求合并的表列名称相同时,以第一个sql 表列名为准;
- 使用union 时,完全相等的行,将会被合并,由于合并比较耗时,一般不直接使用 union 进行合并,而是通常采用union all 进行合并;
- 被union 连接的sql 子句,单个子句中不用写order by ,因为不会有排序的效果。但可以对最终的结果集进行排序;
union
SELECT
e.user_name,
d.dept_name
FROM
employee e
INNER JOIN dept d ON e.dept_id = d.id
UNION
SELECT
e.user_name,
d.dept_name
FROM
employee e
RIGHT JOIN dept d ON e.dept_id = d.id;
查询结果:
union all
SELECT
e.user_name,
d.dept_name
FROM
employee e
INNER JOIN dept d ON e.dept_id = d.id
UNION ALL
SELECT
e.user_name,
d.dept_name
FROM
employee e
RIGHT JOIN dept d ON e.dept_id = d.id;
六、自连接查询
关键字:right join on / inner join on / left join on
特征:自连接查询就是当前表与自身的连接查询,关键点在于虚拟化出一张表给一个别名
例如:查询员工以及他的上司的名称,由于上司也是员工,所以这里虚拟化出一张上司表
SELECT
e.user_name,
b.user_name as user_name_b
FROM
employee e
LEFT JOIN employee b ON e.boss_id = b.id;
查询结果:
自连接查询一般用作表中的某个字段的值是引用另一个字段的值,比如权限表中,父权限也属于权限。
参考文档