MYSQL多表设计，多表查询，事务，索引

#员工表
create table tb_emp(
    id int unsigned primary key auto_increment comment 'ID',
    username varchar(20) not null unique comment '用户名',
    password varchar(20) default '123456' comment '密码',
    name varchar(10) not null comment '姓名',
    gender tinyint unsigned not null comment '性别,说明 1：男，2：女',
    image varchar(300) comment '图像',
    job tinyint unsigned comment '职位,说明:1班主任，2：讲师，3：学工主管,4:教研主管',
    entrydata date comment '入职时间',


    dept_id int unsigned comment '部门ID',#一对多员工的归属部门

    create_time datetime not null comment '创建时间',
    update_time datetime not null comment '更新时间'

)comment '员工表';


create table tb_dept(
    id int unsigned primary key auto_increment comment '主键ID',
    name varchar(10) not null unique comment '部门名称',
    create_time datetime not null comment '创建时间',
    update_time datetime not null comment '更新时间'
)comment '部门表'

一对多关系实现：在数据库表中多的一方，添加字段，来关联属于一这方的主键。

1.1.1 外键约束

表结构创建完毕后，我们看到两张表的数据分别为：

现在员工表中有五个员工都归属于1号部门(学工部)，当删除了1号部门后，数据变为

1 号部门被删除了，但是依然还有 5 个员工是属于 1 号部门的。此时：就出现数据的不完整、不一致了。

问题分析

目前上述的两张表 ( 员工表、部门表 ) ，在数据库层面，并未建立关联，所以是无法保证数据的一致性和

完整性的

问题解决

想解决上述的问题呢，我们就可以通过数据库中的 外键约束 来解决。
外键约束：让两张表的数据建立连接，保证数据的一致性和完整性。

对应的关键字： foreign key

外键约束的语法：

-- 创建表时指定

create table 表名 (

字段名数据类型 ,

...

[ constraint ] [ 外键名称 ] foreign key ( 外键字段名 ) references

主表 ( 主表列名 )

) ;

-- 建完表后，添加外键

alter table 表名 add constraint 外键名称 foreign key ( 外键字段名 )

references 主表 ( 主表列名 ) ;

图形界面添加外键

等于白雪

在现在的企业开发中，很少会使用物理外键，都是使用逻辑外键。甚至在一些数据库开发规范

中，会明确指出禁止使用物理外键 foreign key

1.2一对一

一对一关系表在实际开发中应用起来比较简单，通常是用来做单表的拆分，也就是将一张大表拆分成两张小表，将大表中的一些基础字段放在一张表当中，将其他的字段放在另外一张表当中，以此来提高数据的操作效率。

基本信息：用户的 ID 、姓名、性别、手机号、学历

身份信息：民族、生日、身份证号、身份证签发机关，身份证的有效期 ( 开始时间、结束时

间 )

如果在业务系统当中，对用户的基本信息查询频率特别的高，但是对于用户的身份信息查询频率很

低，此时出于提高查询效率的考虑，我就可以将这张大表拆分成两张小表，第一张表存放的是用户

的基本信息，而第二张表存放的就是用户的身份信息。他们两者之间一对一的关系，一个用户只能

对应一个身份证，而一个身份证也只能关联一个用户。

那么在数据库层面怎么去体现上述两者之间是一对一的关系呢？

其实一对一我们可以看成一种特殊的一对多。一对多我们是怎么设计表关系的？是不是在多的一方添加外键。同样我们也可以通过外键来体现一对一之间的关系，我们只需要在任意一方来添加一个外键就可以了。

一对一：在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的(UNIQUE)

-- 用户基本信息表
create table tb_user(
                        id int unsigned primary key auto_increment comment 'ID',
                        name varchar(10) not null comment '姓名',
                        gender tinyint unsigned not null comment '性别, 1 男 2 女',
                        phone char(11) comment '手机号',
                        degree varchar(10) comment '学历'
) comment '用户基本信息表';
-- 测试数据
insert into tb_user values (1,'白眉鹰王',1,'18812340001','初中'),
                           (2,'青翼蝠王',1,'18812340002','大专'),
                           (3,'金毛狮王',1,'18812340003','初中'),
                           (4,'紫衫龙王',2,'18812340004','硕士');
-- 用户身份信息表
create table tb_user_card(
                             id int unsigned primary key auto_increment comment 'ID',
                             nationality varchar(10) not null comment '民族',
                             birthday date not null comment '生日',
                             idcard char(18) not null comment '身份证号',
                             issued varchar(20) not null comment '签发机关',
                             expire_begin date not null comment '有效期限-开始',
                             expire_end date comment '有效期限-结束',
                             user_id int unsigned not null unique comment '用户ID',
                             constraint fk_user_id foreign key (user_id) references
                                 tb_user(id)
) comment '用户身份信息表';
-- 测试数据
insert into tb_user_card
values (1, '汉', '1960-11-06', '100000100000100001', '朝阳区公安局', '2000-06-10', null, 1),
       (2, '汉', '1971-11-06', '100000100000100002', '静安区公安局', '2005-06-10', '2025-06-10', 2),
       (3, '汉', '1963-11-06', '100000100000100003', '昌平区公安局', '2006-06-10', null, 3),
       (4, '回', '1980-11-06', '100000100000100004', '海淀区公安局', '2008-06-10', '2028-06-10', 4);

1.3多对多

多对多的关系在开发中属于也比较常见的。比如：学生和老师的关系，一个学生可以有多个授课老师，

一个授课老师也可以有多个学生。在比如：学生和课程的关系，一个学生可以选修多门课程，一个课程

也可以供多个学生选修。

案例：学生与课程的关系

关系：一个学生可以选修多门课程，一门课程也可以供多个学生选择

实现关系：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

-- 学生表
create table tb_student(
                           id int auto_increment primary key comment '主键ID',
                           name varchar(10) comment '姓名',
                           no varchar(10) comment '学号'
) comment '学生表';
-- 学生表测试数据
insert into tb_student(name, no) values ('黛绮丝', '2000100101'),('谢
逊', '2000100102'),('殷天正', '2000100103'),('韦一笑', '2000100104');
-- 课程表
create table tb_course(
                          id int auto_increment primary key comment '主键ID',
                          name varchar(10) comment '课程名称'
) comment '课程表';
-- 课程表测试数据
insert into tb_course (name) values ('Java'), ('PHP'), ('MySQL') ,
                                    ('Hadoop');
-- 学生课程表（中间表）
create table tb_student_course(
                                  id int auto_increment comment '主键' primary key,
                                  student_id int not null comment '学生ID',
                                  course_id int not null comment '课程ID',
                                  constraint fk_courseid foreign key (course_id) references
                                      tb_course (id),
                                  constraint fk_studentid foreign key (student_id) references
                                      tb_student (id)
)comment '学生课程中间表';
-- 学生课程表测试数据
insert into tb_student_course(student_id, course_id) values (1,1),
                                                            (1,2),(1,3),(2,2),(2,3),(3,4);

二多表查询

2.1数据准备：

-- 部门管理
create table tb_dept(
                        id int unsigned primary key auto_increment comment '主键ID',
                        name varchar(10) not null unique comment '部门名称',
                        create_time datetime not null comment '创建时间',
                        update_time datetime not null comment '修改时间'
) comment '部门表';

insert into tb_dept (id, name, create_time, update_time) values(1,'学工部',now(),now()),(2,'教研部',now(),now()),(3,'咨询部',now(),now()), (4,'就业部',now(),now()),(5,'人事部',now(),now());



-- 员工管理
create table tb_emp (
                        id int unsigned primary key auto_increment comment 'ID',
                        username varchar(20) not null unique comment '用户名',
                        password varchar(32) default '123456' comment '密码',
                        name varchar(10) not null comment '姓名',
                        gender tinyint unsigned not null comment '性别, 说明: 1 男, 2 女',
                        image varchar(300) comment '图像',
                        job tinyint unsigned comment '职位, 说明: 1 班主任,2 讲师, 3 学工主管, 4 教研主管, 5 咨询师',
                        entrydate date comment '入职时间',
                        dept_id int unsigned comment '部门ID',
                        create_time datetime not null comment '创建时间',
                        update_time datetime not null comment '修改时间'
) comment '员工表';

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

多表查询：查询时从多张表中获取所需数据

单表查询的 SQL 语句： select 字段列表 from 表名 ;

那么要执行多表查询，只需要使用逗号分隔多张表即可，如： select 字段列表 from

表 1, 表 2;

select * from tb_emp , tb_dept;

2.2笛卡儿积：

在 SQL 语句中，如何去除无效的笛卡尔积呢？只需要给多表查询加上连接查询的条件即可。

select * from tb_emp , tb_dept where tb_emp .dept_id = tb_dept .id ;

2.3 分类

多表查询可以分为：

. 1 连接查询

内连接：相当于查询 A 、 B 交集部分数据

2. 外连接

左外连接：查询左表所有数据 ( 包括两张表交集部分数据 )

右外连接：查询右表所有数据 ( 包括两张表交集部分数据 )

3. 子查询

2.3.1内连接

内连接查询：查询两表或多表中交集部分数据。

内连接从语法上可以分为：

隐式内连接 select 字段列表 from 表 1 , 表 2 where 条件 ... ;

显式内连接 select 字段列表 from 表 1 [ inner ] join 表 2 on 连接条件 ... ;

案例：查询员工的姓名及所属的部门名称

#隐式内连接
select tb_emp.name,tb_dept.name from tb_emp,tb_dept where tb_emp.dept_id=tb_dept.id;

#显示内连接

select d.name,e.name from tb_dept d  inner join tb_emp e on d.id=e.dept_id;

补充

多表查询时给表起别名：

tableA as 别名 1 , tableB as 别名 2 ;

tableA 别名 1 , tableB 别名 2 ;

注意事项 :

一旦为表起了别名，就不能再使用表名来指定对应的字段了，此时只能够使用别名来指定字段。

2.3.2外连接

外连接分为两种：左外连接和右外连接。

左外连接语法结构：

select 字段列表 from 表 1 left [ outer ] join 表 2 on 连接条件 ... ;

左外连接相当于查询表 1( 左表 ) 的所有数据，当然也包含表 1 和表 2 交集部分的数据。

右外连接语法结构：

select 字段列表 from 表 1 right [ outer ] join 表 2 on 连接条件 ... ;

右外连接相当于查询表 2( 右表 ) 的所有数据，当然也包含表 1 和表 2 交集部分的数据

案例：查询部门表中所有部门的名称, 和对应的员工名称

-- 左外连接：以left join关键字左边的表为主表，查询主表中所有数据，以及和主表
匹配的右边表中的数据
select emp.name , dept.name
from tb_emp AS emp left join tb_dept AS dept
on emp.dept_id = dept.id;

-- 右外连接
select dept.name , emp.name
from tb_emp AS emp right join tb_dept AS dept
on emp.dept_id = dept.id;

注意事项：

左外连接和右外连接是可以相互替换的，只需要调整连接查询时 SQL 语句中表的先后顺序就可以

了。而我们在日常开发使用时，更偏向于左外连接。

三子查询

SQL 语句中嵌套 select 语句，称为嵌套查询，又称子查询。

SELECT * FROM t1 WHERE column1 = ( SELECT column1 FROM t2 ... ) ;

子查询外部的语句可以是 insert / update / delete / select 的任何一个，最常见的是

select 。

根据子查询结果的不同分为：

1. 标量子查询（子查询结果为单个值 [ 一行一列 ] ）

2. 列子查询（子查询结果为一列，但可以是多行）

3. 行子查询（子查询结果为一行，但可以是多列）

4. 表子查询（子查询结果为多行多列 [ 相当于子查询结果是一张表 ] ）

子查询可以书写的位置：

1. where 之后

2. from 之后

3. select 之后

3.1标量子查询

子查询返回的结果是单个值 ( 数字、字符串、日期等 ) ，最简单的形式，这种子查询称为标量子查询。

常用的操作符： = <> > >= < <=

#案例1：查询"教研部"的所有员工信息

select id from tb_dept where tb_dept.name='教研部';

select * from tb_emp where tb_emp.dept_id=(select id from tb_dept where tb_dept.name='教研部');

3.2列子查询

子查询返回的结果是一列 ( 可以是多行 ) ，这种子查询称为列子查询。

常用的操作符

案例：查询 " 教研部 " 和 " 咨询部 " 的所有员工信息

-- 1.查询"销售部"和"市场部"的部门ID
select id from tb_dept where name = '教研部' or name = '咨询部'; #查
询结果：3,2
-- 2.根据部门ID, 查询员工信息
select * from tb_emp where dept_id in (3,2);
-- 合并以上两条SQL语句
select * from tb_emp where dept_id in (select id from tb_dept where
name = '教研部' or name = '咨询部');

3.3行子查询

子查询返回的结果是一行 ( 可以是多列 ) ，这种子查询称为行子查询。

-- 查询"韦一笑"的入职日期 及 职位
select entrydate , job from tb_emp where name = '韦一笑'; #查询结果：
2007-01-01 , 2
-- 查询与"韦一笑"的入职日期及职位相同的员工信息
select * from tb_emp where (entrydate,job) = ('2007-01-01',2);
-- 合并以上两条SQL语句
select * from tb_emp where (entrydate,job) = (select entrydate , job
from tb_emp where name = '韦一笑');

3.4表子查询

子查询返回的结果是多行多列，常作为临时表，这种子查询称为表子查询。

select * from emp where entrydate > '2006-01-01';
select e.*, d.* from (select * from emp where entrydate > '2006-01-
01') e left join dept d on e.dept_id = d.id ;

四事务

4.1场景

在实际的业务开发中，有些业务操作要多次访问数据库。一个业务要发送多条 SQL 语句给数据库执行。

需要将多次访问数据库的操作视为一个整体来执行，要么所有的 SQL 语句全部执行成功。如果其中有一条SQL 语句失败，就进行事务的回滚，所有的 SQL 语句全部执行失败。

简而言之：事务是一组操作的集合，它是一个不可分割的工作单位。事务会把所有的操作作为一个整体

一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。

事务作用：保证在一个事务中多次操作数据库表中数据时，要么全都成功 , 要么全都失败

4.2语法

4.3事务特性

面试题：事务有哪些特性？

原子性（ Atomicity ）：事务是不可分割的最小单元，要么全部成功，要么全部失败。

一致性（ Consistency ）：事务完成时，必须使所有的数据都保持一致状态。

隔离性（ Isolation ）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立

环境下运行。

持久性（ Durability ）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。

事务的四大特性简称为：ACID

五索引

5.1索引概述

索引 (index) ：是帮助数据库高效获取数据的数据结构。

简单来讲，就是使用索引可以提高查询的效率。

优点：

1. 提高数据查询的效率，降低数据库的 IO 成本。

2. 通过索引列对数据进行排序，降低数据排序的成本，降低 CPU 消耗。

缺点：

1. 索引会占用存储空间。

2. 索引大大提高了查询效率，同时却也降低了 insert 、 update 、 delete 的效率

5.2B+tree树结构

B+Tree 结构：

每一个节点，可以存储多个 key （有 n 个 key ，就有 n 个指针）

节点分为：叶子节点、非叶子节点

叶子节点，就是最后一层子节点，所有的数据都存储在叶子节点上

非叶子节点，不是树结构最下面的节点，用于索引数据，存储的的是： key+ 指针

为了提高范围查询效率，叶子节点形成了一个双向链表，便于数据的排序及区间范围查询