JavaWeb笔记——mysql多表操作、事务、索引

---

一、外键约束

1. 背景：在外表中添加主表主键的字段，来关联主表。如部门表中含主键id和部门名称字段，员工表通过添加部门id字段关联部门表。

2. 外键意义：保证数据的一致性和完整性，当外键关联时，无法轻易删除主表中被关联的数据段。如员工表中有部门Id=1，则部门表上id=1的数据不能删除

3. 语法：
   

4. 物理外键foreign key的缺点：
   1）影响增、删、改的效率（需要检查外键关系）。
   2）仅用于单节点数据库，不适用与分布式、集群场景。
   3）容易引发数据库的死锁问题，消耗性能
   4）解决办法：通过逻辑外键：使用事务+逻辑判断，在业务层逻辑中，解决外键关联

5. 表格关系：
   1）一对多：在多表上增加外键，关联一表的主键。举例：员工表（外表）和部门表（主表），员工表设置部门id为外键关联部门表的主键id。
   2）一对一：在任意一方中增加唯一的外键（unique foreign key），关联另一方的主键。举例：用户基础信息表和用户偏好表，通过设置唯一的id外键保证一对一。分成两张表是因为基础信息和偏好表的查询频率不一样，分开提高效率。
   3）多对多：增加一个中间的关系表，至少有两个外键，分别关联两方的主键。举例：课程表和学生表，增加选修表，设置两个外键字段：学生id和课程id。

---

二、多表查询

2.1 概述

1. 在多表查询时，需要消除无效的笛卡尔积。笛卡尔乘积是指在数学中，两个集合(A集合 和 B集合)的所有组合情况。
   
2. 多表查询分为连接查询和子查询。
   1）连接查询分为内外连接；
   2）子查询指查询之间内外嵌套；
3. 内连接：相当于查询A、B交集部分数据
4. 外连接：
   1）左外连接：查询左表所有数据(包括两张表交集部分数据)
   2）右外连接：查询右表所有数据(包括两张表交集部分数据)
   

2.2 内连接

1. 隐式内连接和显式内连接：如果交集中出现null数据，则不可查询
   
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 '部门表';

-- 员工管理
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 '员工表';

2）隐式：

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

3）显式：

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

2.3 外连接

1. 语法：left指包含左表（表1）全部数据；right指包含右表（表2）全部数据。
   
2. 实例：查询部门表所有部门名称，和对应的员工姓名，按照部门名称降序排列
   1）数据：同2.2的数据
   2）sql语句：

select tb_dept.name,tb_emp.name from tb_dept left outer join tb_emp on tb_dept.id = tb_emp.dept_id order by tb_dept.name desc;

2.4 子查询/嵌套查询

1. SQL语句中嵌套select语句，称为嵌套查询，又称子查询。子查询外部的语句可以是insert / update / delete / select 的任何一个，最常见的是 select。

2. 语法：
   

3. 实例：
   1）数据同2.2
   2）查询教研部们所有员工的信息

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

3）查询与’韦一笑’入职日期和职位一致的员工信息，使用(字段1，字段2)=(子查询)的方式简化

select * from tb_emp where entrydate = (select entrydate from tb_emp where name='韦一笑') and job =(select job from tb_emp where name='韦一笑'); 
# 上句采用了两次子查询，效率低，可以使用 where (字段1，字段2)=（子查询）的方式
select * from tb_emp where (entrydate,job) = (select entrydate,job from tb_emp where name='韦一笑') ;

---

三、事务

1. 事务理论：事务是一组操作的集合，它是一个不可分割的工作单位。事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。例如删除某一部门的同时需要删除员工表中对应部门的员工信息，否则数据不一致。
2. 语法：
   1）默认一个sql语句是一个单独的事务，执行后默认提交。
   2）控制语法：
   提交业务：在提交业务之前，在其他界面上并没有执行操作的结果，当业务操作没有问题，则运行commit提交；
   滚回业务：当业务操作出错，则回复导业务开启前；
   
3. 实例：删除部门3并且删除部门内所有员工数据
   1）数据同2.2
   2）sql语句

-- 开启业务
start transaction ;
-- 进行系列操作
delete from tb_emp where dept_id=3;
delete from tb_dept where id=3;
-- 提交业务：在提交业务之前，在其他界面上并没有执行操作的结果，当业务操作没有问题，则运行commit提交
commit ;
-- 滚回业务：当业务操作出错，则回复导业务开启前
rollback ;

4. 事务的四大特性：
   1）原子性：事务是不可分割的最小单元，要么全部成功，要么全部失败；
   2）一致性：事务完成时，必须使所有的数据都保持一致状态；
   3）隔离性：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行；隔离性越高，数据越安全，效率越低；
   4）持久性：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的；

---

四、查询效率优化——索引

1. 索引：帮助数据库高效获取数据的数据结构。适合数据量大且查询需求频率高，增删改频率低的数据。

2. 优点：
   1）提高数据查询的效率，降低数据库的IO成本。
   2）通过索引列对数据进行排序，降低数据排序的成本，降低CPU消耗。

3. 缺点：
   1）索引会占用存储空间。
   2）索引大大提高了查询效率，同时却也降低了insert、update、delete的效率。

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

5. B+Tree：
   1）每一个节点，可以存储多个key（有n个key，就有n个指针）。
   2）所有的数据都存储在叶子节点，非叶子节点仅用于索引数据。
   3）叶子节点形成了一颗双向链表，便于数据的排序及区间范围查询。
   

6. 索引语法：
   1）在建表时，如果有主键，会自动创建主键索引。
   2）添加唯一约束时，数据库实际上会添加唯一索引。