数据库（五）：多表计划和多表查询

项目开发中，在进行数据库表结构计划时，会根据业务需求及业务模块之间的关系，分析并计划表结构，由于业务之间相互关联，以是各个表结构之间也存在各种联系，基本上分为三种：一对一、多对一、一对多。
一、多表计划

（一）一对多表

需求：根据页面原型及需求文档，完成部门及员工模块的表结构计划。（一个部门对应多个员工）
计划部门表和员工表（父表和子表）

1. create table tb_emp (
2.                         id int unsigned primary key auto_increment comment 'ID',
3.                         username varchar(20) not null unique comment '用户名',
4.                         password varchar(32) default '123456' comment '密码',
5.                         name varchar(10) not null comment '姓名',
6.                         gender tinyint unsigned not null comment '性别, 说明: 1 男, 2 女',
7.                         image varchar(300) comment '图像',
8.                         job tinyint unsigned comment '职位, 说明: 1 班主任,2 讲师, 3 学工主管, 4 教研主管',
9.                         entrydate date comment '入职时间',
10.                         dept_id int unsigned comment '归属部门的id',
11.                         create_time datetime not null comment '创建时间',
12.                         update_time datetime not null comment '修改时间'
13. ) comment '员工表';
15. create table tb_dept(
16.     id int unsigned primary key auto_increment comment 'ID',
17.     name varchar(10) not null unique comment '部门名称',
18.     create_date datetime not null comment '创建时间',
19.     update_time datetime not null comment  '修改时间'
20. )comment '部门表';

复制代码

插入数据：

1. insert into tb_dept (id, name, create_date, update_time) values
2.                                                              (1,'学工部',now(),now()),(2,'教研部',now(),now()),(3,'咨询部',now(),now()),
3.                                                              (4,'就业部',now(),now()),(5,'人事部',now(),now());
5. INSERT INTO tb_emp
6. (id, username, password, name, gender, image, job, entrydate,dept_id, create_time, update_time) VALUES
7.                                                                                                     (1,'jinyong','123456','金庸',1,'1.jpg',4,'2000-01-01',2,now(),now()),
8.                                                                                                     (2,'zhangwuji','123456','张无忌',1,'2.jpg',2,'2015-01-01',2,now(),now()),
9.                                                                                                     (3,'yangxiao','123456','杨逍',1,'3.jpg',2,'2008-05-01',2,now(),now()),
10.                                                                                                     (4,'weiyixiao','123456','韦一笑',1,'4.jpg',2,'2007-01-01',2,now(),now()),
11.                                                                                                     (5,'changyuchun','123456','常遇春',1,'5.jpg',2,'2012-12-05',2,now(),now()),
12.                                                                                                     (6,'xiaozhao','123456','小昭',2,'6.jpg',3,'2013-09-05',1,now(),now()),
13.                                                                                                     (7,'jixiaofu','123456','纪晓芙',2,'7.jpg',1,'2005-08-01',1,now(),now()),
14.                                                                                                     (8,'zhouzhiruo','123456','周芷若',2,'8.jpg',1,'2014-11-09',1,now(),now()),
15.                                                                                                     (9,'dingminjun','123456','丁敏君',2,'9.jpg',1,'2011-03-11',1,now(),now()),
16.                                                                                                     (10,'zhaomin','123456','赵敏',2,'10.jpg',1,'2013-09-05',1,now(),now()),
17.                                                                                                     (11,'luzhangke','123456','鹿杖客',1,'11.jpg',1,'2007-02-01',1,now(),now()),
18.                                                                                                     (12,'hebiweng','123456','鹤笔翁',1,'12.jpg',1,'2008-08-18',1,now(),now()),
19.                                                                                                     (13,'fangdongbai','123456','方东白',1,'13.jpg',2,'2012-11-01',2,now(),now()),
20.                                                                                                     (14,'zhangsanfeng','123456','张三丰',1,'14.jpg',2,'2002-08-01',2,now(),now()),
21.                                                                                                     (15,'yulianzhou','123456','俞莲舟',1,'15.jpg',2,'2011-05-01',2,now(),now()),
22.                                                                                                     (16,'songyuanqiao','123456','宋远桥',1,'16.jpg',2,'2010-01-01',2,now(),now()),
23.                                                                                                     (17,'chenyouliang','123456','陈友谅',1,'17.jpg',NULL,'2015-03-21',NULL,now(),now());

复制代码

题目：部门数据可以直接删除，然而还有员工归属于该部门下，此时就出现了数据的不完整不划一的题目。
原因：此时这两张表在数据库层面并没有建立关联，以是无法保证数据的划一性和完整性。
解决方案：使用外键束缚
（二）物理外键

外键束缚（物理外键）：使用foreign key定义外键关联另一张表
缺点：影响增删改的服从，仅用于单节点数据库，不实用于分布式、集群场景，容易引发数据库的死锁题目。

图形化界面的方式：

（三）逻辑外键

在业务逻辑层，解决外键关联，通过逻辑外键可以很方便的解决物理外键的题目。
（四）一对一

一对一关系多用于单表拆分，将一张表的基础字段放在一张表中，其他字段放在另一张表中，以提拔操作服从。
实现：在任意一方到场外键，关联另一方的主键，并且设置外键为唯一的（UNIQUE）
（五）多对多

实例：一个学生可以选择多门课，一门课也可以供多个学生选择。
实现：建立第三张中间表，中间表至少包罗两个外键，分别关联两方主键。
二、多表查询

从多张表中查询数据，并且根据业务需要消除无效信息。
建表：

1. create table tb_emp (
2.                         id int unsigned primary key auto_increment comment 'ID',
3.                         username varchar(20) not null unique comment '用户名',
4.                         password varchar(32) default '123456' comment '密码',
5.                         name varchar(10) not null comment '姓名',
6.                         gender tinyint unsigned not null comment '性别, 说明: 1 男, 2 女',
7.                         image varchar(300) comment '图像',
8.                         job tinyint unsigned comment '职位, 说明: 1 班主任,2 讲师, 3 学工主管, 4 教研主管',
9.                         entrydate date comment '入职时间',
10.                         dept_id int unsigned comment '归属部门的id',
11.                         create_time datetime not null comment '创建时间',
12.                         update_time datetime not null comment '修改时间'
13. ) comment '员工表';
15. create table tb_dept(
16.     id int unsigned primary key auto_increment comment 'ID',
17.     name varchar(10) not null unique comment '部门名称',
18.     create_date datetime not null comment '创建时间',
19.     update_time datetime not null comment  '修改时间'
20. )comment '部门表';
21. insert into tb_dept (id, name, create_date, update_time) values
22.                                                              (1,'学工部',now(),now()),(2,'教研部',now(),now()),(3,'咨询部',now(),now()),
23.                                                              (4,'就业部',now(),now()),(5,'人事部',now(),now());
25. INSERT INTO tb_emp
26. (id, username, password, name, gender, image, job, entrydate,dept_id, create_time, update_time) VALUES
27.                                                                                                     (1,'jinyong','123456','金庸',1,'1.jpg',4,'2000-01-01',2,now(),now()),
28.                                                                                                     (2,'zhangwuji','123456','张无忌',1,'2.jpg',2,'2015-01-01',2,now(),now()),
29.                                                                                                     (3,'yangxiao','123456','杨逍',1,'3.jpg',2,'2008-05-01',2,now(),now()),
30.                                                                                                     (4,'weiyixiao','123456','韦一笑',1,'4.jpg',2,'2007-01-01',2,now(),now()),
31.                                                                                                     (5,'changyuchun','123456','常遇春',1,'5.jpg',2,'2012-12-05',2,now(),now()),
32.                                                                                                     (6,'xiaozhao','123456','小昭',2,'6.jpg',3,'2013-09-05',1,now(),now()),
33.                                                                                                     (7,'jixiaofu','123456','纪晓芙',2,'7.jpg',1,'2005-08-01',1,now(),now()),
34.                                                                                                     (8,'zhouzhiruo','123456','周芷若',2,'8.jpg',1,'2014-11-09',1,now(),now()),
35.                                                                                                     (9,'dingminjun','123456','丁敏君',2,'9.jpg',1,'2011-03-11',1,now(),now()),
36.                                                                                                     (10,'zhaomin','123456','赵敏',2,'10.jpg',1,'2013-09-05',1,now(),now()),
37.                                                                                                     (11,'luzhangke','123456','鹿杖客',1,'11.jpg',1,'2007-02-01',1,now(),now()),
38.                                                                                                     (12,'hebiweng','123456','鹤笔翁',1,'12.jpg',1,'2008-08-18',1,now(),now()),
39.                                                                                                     (13,'fangdongbai','123456','方东白',1,'13.jpg',2,'2012-11-01',2,now(),now()),
40.                                                                                                     (14,'zhangsanfeng','123456','张三丰',1,'14.jpg',2,'2002-08-01',2,now(),now()),
41.                                                                                                     (15,'yulianzhou','123456','俞莲舟',1,'15.jpg',2,'2011-05-01',2,now(),now()),
42.                                                                                                     (16,'songyuanqiao','123456','宋远桥',1,'16.jpg',2,'2010-01-01',2,now(),now()),
43.                                                                                                     (17,'chenyouliang','123456','陈友谅',1,'17.jpg',NULL,'2015-03-21',NULL,now(),now());

复制代码

查询：
通过这种查询，会造成不正常的链接，也就是笛卡尔积

消除了无效的笛卡尔积：

（一）内连接

相当于查询A、B交集部门的数据。

1. # 查询员工的姓名，及所属的部门名称（隐式内连接）
2. select tb_emp.name,tb_dept.name from tb_emp,tb_dept where tb_emp.dept_id=tb_dept.id;
3. #查询员工姓名及所属部门名称（显示内连接）
4. select tb_emp.name,tb_dept.name from tb_emp inner join tb_dept on dept_id=tb_dept.id;

复制代码

（二）外连接

左外连接，查询左表全部数据（包罗两张表交集部门的数据）；右外连接，查询右表全部数据（包罗两张表交集部门的数据）。

1. #查询 所有员工的姓名，及所属的部门名称（左外连接）
2. select * from tb_emp e left join tb_dept d on e.dept_id=d.id;
3. #查询部门表 所有 部门的名称，和对应的员工名称（右外连接）
4. select e.name,d.name from tb_emp e right join tb_dept d on e.dept_id=d.id;

复制代码

（三）子查询

1、标量子查询

子查询返回的是单个值（数值、字符串、日期等），最简单的情势。常用的操作符是：= <> > >= <= <

1. #子查询
2. #标量子查询
3. #A——查询“教研部”的所有员工信息 查教研部ID+查询部门ID下的员工信息
4. select tb_dept.id from tb_dept where name='教研部';
5. select * from tb_emp where id = 2;
6. # 合并
7. select * from tb_emp where dept_id =(select tb_dept.id from tb_dept where name='教研部');
9. # 在方东白入职之后的员工信息 入职时间+该时间之后的员工信息
10. select entrydate from tb_emp where name='方东白';
12. select * from tb_emp where entrydate>='2012-11-01';
13. # 合并
14. select * from tb_emp where entrydate>=(select entrydate from tb_emp where name='方东白');

复制代码

2、列子查询

子查询返回的结果是一列（可以是多行），常用的操作符in 、not in等。

1. #列子查询
2. #查询 教研部 和 咨询部 的所有员工信息 查询ID+查询该部门下的员工信息
3. select tb_dept.id from tb_dept where name='教研部' or name = '咨询部';
5. select * from tb_emp where dept_id in (2,3);
6. # 合并
7. select * from tb_emp where dept_id in (select tb_dept.id from tb_dept where name='教研部' or name = '咨询部');

复制代码

3、行子查询

子查询返回的结果是一行（可以是多列），常用的操作符：= 、<>、 in 、not in

1. #行子查询
2. #查询与“韦一笑”这个员工的 入职日期 和 职位 都相同你的员工 =查询“韦一笑”的 入职日期 和职位 + 查询和他入职日期和职位都相同的人
3. select entrydate,job from tb_emp where name='韦一笑';
4. select * from tb_emp where entrydate = '2007-01-01' and job = 2;
5. #合并
6. select * from tb_emp where entrydate = (select entrydate from tb_emp where name='韦一笑') and job = (select job from tb_emp where name='韦一笑');
7. #优化
8. select * from tb_emp where (entrydate,job)= ('2007-01-01' ,job = 2);
9. select * from tb_emp where (entrydate,job)= (select entrydate,job from tb_emp where name='韦一笑');

复制代码

4、表子查询

子查询返回的是多行多列，常作为暂时表，常用的操作符in。

1. #表子查询
2. #查询入职日期是“2006-01-01”之后的员工信息，及其部门信息
3. #先查询“2006-01-01”之后的员工信息
4. #在查询部门信息
5. select * from tb_emp where entrydate>'2006-01-01';
6. select  e.*,d.name from (select * from tb_emp where entrydate>'2006-01-01') e,tb_dept d where e.dept_id =d.id;

复制代码

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。