MYSQL基础-多表操作-事务-索引

1. 多表设计

概述

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

1. 一对多(多对一)

2. 多对多

3. 一对一

1.1 一对多

需求：根据页面原型 及 需求文档，完成部门及员工模块的表结构设计。

 

 多表问题分析

现象

部门数据可以直接删除，然而还有部分员工归属于该部门下，此时就出现了数据的不完整、不一致问题。

问题分析

目前上述的两张表，在数据库层面，并未建立关联，所以是无法保证数据的一致性和完整性的。

 外键约束

语法

-- 创建表时指定
create table 表名(
	字段名 数据类型，
	...
	[constraint][外键名称] foreign key(外键字段名) references 主表(字段名)
);
-- 建完表后，添加外键
alter table 表名 add constraint 外键名称  foreign key (外键字段名) references 主表(字段名);

物理外键

概念:使用foreign key 定义外键关联另外一张表。

缺点：

影响增、删、改的效率(需要检查外键关系)。

仅用于单节点数据库，不适应于分布式、集群场景。

容易引发数据库的死锁问题，消耗性能。

逻辑外键

概念：在业务层逻辑中，解决i外键关联。

通过逻辑外键，就可以很方便的解决上述问题。

1.2 一对一

案例：用户与身份证信息 的关系

关系：一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他字段放在另一张表中，以提升操作效率。

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

 多对多

案例：学生与课程的关系

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

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

 2. 多表查询

概述：指从多张表中查询数据

笛卡尔积：笛卡尔乘积是指在数学中，两个集合(A集合 和 B集合)的所有组合情况。(在多表查询时需要删除无效的笛卡尔积)

形式分类

连接查询

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

外连接

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

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

子查询

2.1 内连接

语法

隐式内连接：select 字段列表 from 表1，表2 where 条件...；

显示内连接：slelect 字段列表 from 表1 [inner] join 表2 on 连接条件...；

-- 内连接
-- A. 查询员工的姓名，及所属的部门名称(隐式内连接实现)
select tb_emp.name,tb_dept.name
from tb_emp,tb_dept where tb_emp.dept_id = tb_dept.id;

-- 起别名
select e.name,d.name
from tb_emp e,tb_dept d where e.dept_id = d.id;

-- B. 查询员工的姓名，及所属的部门名称(显示内连接实现)
select tb_emp.name,tb_dept.name
from tb_emp inner join tb_dept on tb_emp.dept_id = tb_dept.id;

2.2 外连接

语法

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

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

-- 外连接
-- A. 查询员工表所有员工的姓名，和对应的部门名称(左外连接)
select e.name,d.name
from tb_emp e left join tb_dept d on e.dept_id = d.id;
-- B. 查询部门表 所有部门的名称，和对应的员工名称(右外连接)
select e.name,d.name
from tb_emp e right join tb_dept d on e.dept_id = d.id;

2.3 子查询

概述

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

形式：select * from te wher  coloumn1 = (select column1 from t2 ... )

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

分类

标量子查询：子查询返回的结果为单个值

列子查询：子查询返回的结果为一列

行子查询：子查询返回的结果为一行

表子查询：子查询返回的结果为多行多列

2.3.1 标量子查询

子查询返回的结果是单个值(数字、字符串、日期等)，最简单的形式

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

-- 标量子查询
-- A. 查询"教研部"的所有员工信息
select *
from tb_emp,tb_dept where (select tb_dept.id where tb_dept.name = '教研部');

-- B.查询在 方东白 入职之后的员工信息
select *
from tb_emp where entrydate > (select tb_emp.entrydate from tb_emp where name = '方东白');

2.3.2 列子查询

子查询返回的结果是一列(可以是多行)

常用的操作符：in、not in等

2.3.3 行子查询

子查询返回的结果是一行(可以是多列)。

常用的操作符：=、<>、in、not in

-- 行子查询
-- 查询与韦一笑入职日期及职位都相同的员工信息；
select *
from tb_emp where entrydate = (select entrydate from tb_emp where name = '韦一笑') and dept_id = (select dept_id from tb_emp where name = '韦一笑');

select *
from tb_emp where (entrydate,dept_id) = (select entrydate,dept_id from tb_emp where name = '韦一笑');

2.3.4 表子查询

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

常用的操作符：in

3. 事务

3.1 场景

学工部 整个部门解散了，该部门及该部门下的员工都需要删除了。

操作

-- 删除学工部
delete from tb_dept where id = 1;

-- 删除学工部的员工
delete from tb_emp where dept_id = 1;

问题

如果删除部门成功了，而删除该部门的员工时失败了，就造成了数据的不一致。

3.2 介绍

事物是一组操作的集合，它是一个不可分割的工作单位。事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。

注意事项

默认MYSQL的事务是自动提交的，也就是说，当执行一条DML语句，MYSQL会立即隐式的提交事务。

3.3 操作

开启事务：start transation; / begin ;

提交事务：commit;

回滚事务：rollback;

-- 事务
-- 开启事务
start transaction ;
-- 删除部门
delete from tb_dept where id = 3;
-- 删除部门下的员工
delete from tb_emp where dept_id = 3;
-- 提交事务
commit ;
-- 回滚事务
rollback ;

3.4 事务的四大特性

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

2. 一致性：事务完成时，必须使所有的数据都保持一致状态

3. 隔离性：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行

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

4. 索引

4.1 介绍

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

4.2 优缺点

优点：

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

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

缺点：

索引会占用存储空间

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

4.3 结构

MYSQL数据库支持的索引结构有很多，如：Hash索引、B+Tree索引、Full-Text索引等。我们平常所说的索引，如果没有特别指明，都是指默认的 B+Tree 结构组织的索引。

 B+Tree(多路平衡搜索树)

 注意事项：

每一个节点，可以存储多个key(有n个key，就有n个指针)。

所有的数据都存储在叶子节点，非叶子节点仅用于索引数据。

叶子节点形成了一颗双向链表，便于数据的排序及区间范围查询。

4.4 语法

创建索引

create [unique] index 索引名 on 表名 (字段名,...);

查看索引

show index from 表名;

删除索引

drop index 索引名 on 表名;

-- 索引
-- 创建：为tb_emp 表的name字段建立一个索引
create index idx_emp_name on tb_emp(name);
-- 查询tb_emp 表的索引信息
show index in tb_emp;
-- 删除tb_emp表中name字段的索引
drop index idx_emp_name on tb_emp;

注意事项

主键字段，在建表时，会自动创建主键索引。

添加唯一约束时，数据库实际上会添加唯一索引。