Mysql入门3——多表操作、事务、索引
一、多表设计
在项目开发中，在进行数据库表的结构设计时，会根据业务需求及业务模块之前的关系，分析并设计表的结构，由于业务之间相互关联，所以各个表之间也存在着各种关系：
- 一对多
- 一对一
- 多对多
1、外键约束foreignkey
外键约束是数据库管理系统中用于维护数据完整性的一种机制。它用于定义两个表之间的关系，确保一个表中的字段（外键）引用另一个表中的字段（主键）时，引用的值必须有效。这种约束有助于保持数据的一致性和完整性。（因此绑定外键会就不可单项删除）
基本概念：
- 主键与外键：
- 主键是表中唯一标识一条记录的字段，不能重复且不能为NULL。
- 外键是一个表中的字段，它引用另一个表的主键，建立起两者之间的关系。
- 维护数据完整性：
- 外键约束确保在一个表中引用的值在另一个表中存在，防止出现孤立的数据记录。
- 例如，如果有一个订单表引用了用户表的用户ID，外键约束将确保所有订单的用户ID在用户表中是有效的。
逻辑外键（推荐使用）：
又叫事物外键，不使用foreignkey，使用语法（代码）上产生逻辑关联而产生的外键，与传统的外键不同，逻辑外键并不直接依赖于数据库管理系统的约束机制，而是通过应用程序逻辑或业务规则来维护。
基本概念：
- 定义：
- 逻辑外键是一种没有被数据库管理系统强制施加的约束，它在应用层面上确保数据的一致性。
- 逻辑外键通常是表中的一列（或多列），其值在另一个表中存在，但没有外键约束。
- 用途：
- 逻辑外键用于表示表之间的关系，尤其在需要灵活处理数据关系时。
- 它允许开发者在不依赖数据库约束的情况下，维护和检查数据的完整性。
2、一对多
一对多（One-to-Many）关系是多表设计中最常见的关系类型之一，指的是一个表中的一条记录可以与另一个表中的多条记录相对应。这种关系通常用于表示一个实体与多个相关实体之间的关联。
基本概念：
- 表的结构：
- 在一对多关系中，通常有两个表：主表和从表。
- 主表中的每条记录可以在从表中找到多条对应的记录。
- 主键与外键：
- 主表的主键用于唯一标识每一条记录。
- 从表中会有一个外键字段，用于引用主表的主键，以建立这两个表之间的联系。
实现：在子表中添加字段（外键），来关联父表的主键
3、一对一
一对一（One-to-One）关系是多表设计中的一种关系类型，指的是一个表中的一条记录与另一个表中的唯一一条记录相对应。这种关系在数据库设计中相对较少见，但在某些特定场景下非常有用。
基本概念：
- 表的结构：
- 在一对一关系中，通常有两个表：表A和表B。
- 表A中的每条记录与表B中的一条记录相对应，反之亦然。
- 主键与外键：
- 一对一关系通常通过外键约束来实现，表A的主键也可以作为表B的主键，或者表B中的外键引用表A的主键。
- 这种设计确保了每个表中的记录都是唯一的。
实现：在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的（unique）
4、多对多
多对多（Many-to-Many）关系是数据库设计中常见的一种关系类型，指的是一个表中的一条记录可以与另一个表中的多条记录相对应，同时另一个表中的一条记录也可以与第一个表中的多条记录相对应。为了实现多对多关系，通常需要使用一个中间表（或称为关联表）来管理这两个表之间的关系。
基本概念：
- 表的结构：
- 假设有两个主要表：表A和表B。
- 由于表A中的记录可以与表B中的多条记录关联，因此需要一个中间表（表C）来存储这种关系。
- 中间表：
- 中间表通常包含两个外键，分别引用表A和表B的主键。
- 每个外键的组合在中间表中形成一条唯一的记录，表示表A和表B之间的关联。
实现：建立第三张中间表，中间表至少包括两个外键，分别关联两方主键
二、多表查询
多表查询是指在数据库中同时涉及多个表的数据查询操作。这种查询通常需要使用**联结（JOIN）**操作来结合来自不同表的数据，以便获取更全面的信息。多表查询在处理复杂的数据关系时非常有用，因此在关系数据库中广泛应用。
- 内连接：相当于查询表的交集部分
- 外连接
- 左外连接：查询左表所有数据（包括两张表交集部分数据）
- 右外连接：查询右表所有数据（包括两张表交集部分数据）
- 子查询
以下是测试的表格数据源代码：
-- 创建员工表
create table tb_emp
(
id int auto_increment comment '主键ID'
primary key,
Username varchar(20) not null comment '用户名',
password varchar(32) default '123456' null comment '密码',
name varchar(10) not null comment '姓名',
gender tinyint unsigned not null comment '性别 1-男 2-女',
image varchar(300) null comment '图像的url',
job tinyint unsigned null comment '职位： 1-班主任 2-讲师 3-学工主管 4-教研主管',
entrydata date null comment '入职日期',
dept_id int unsigned comment '入职部门',
create_time datetime not null comment '创建时间',
update_time datetime not null comment '修改时间',
constraint tb_emp_pk_2
unique (Username)
)
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 '部门表';
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() ) ;
insert into tb_emp (id, Username, password, name, gender, image, job, entrydata, 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',1, '2007-02-01 ', 1, now(), now()),
(12, 'hebiweng', '123456', '鹤笔翁',1, '12.jpg',1, '2008-08-18',1, now(), now()),
(13, ' fangdongbai', '123456','方东白',1, '13.jpg',2, '2012-11-01',2, 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, '2010-01-01' ,2, now() , now()),
(17, ' chenyouliang', '123456', '陈友谅',1, '17.jpg', NULL, '2015-03-21' , NULL, now() , now());
1、笛卡尔积
笛卡尔积（Cartesian Product）是数据库中两个表之间的一种操作，指的是将两个表中的每一条记录与另一个表中的每一条记录进行组合，形成一个新的结果集。笛卡尔积的结果集包含所有可能的记录组合，因此它的行数等于两个表行数的乘积。
2、内连接
语法：
- 隐式内连接：
select 字段列表 from 表1, 表2 where 条件……; - 显式内连接：
select 字段列表 from 表1 [inner] join 表2 on 连接条件……;
可以通过连接的条件可以来消除无效的笛卡尔积
示例：
-- 多表查询
-- 内连接
-- A.查询员工的姓名， 及所属的部门名称（隐式内连接实现）
select tb_emp.name, tb_dept.name from tb_emp, tb_dept where tb_dept.id = tb_emp.dept_id;
-- B.查询员工的姓名， 及所属的部门名称（显式内连接实现）
select tb_emp.name 姓名, tb_dept.name 所属部门 from tb_emp inner join tb_dept on tb_dept.id = tb_emp.dept_id;
3、外连接
语法：
- 左外连接：
select 字段列表 from 表1 left [outer] join 表2 on 连接条件……; - 右外连接：
select 字段列表 from 表1 right [outer] join 表2 连接条件……;
左外连接会完全包含左表数据（表1），右外连接会完全包含右表数据（表2）
示例：
-- 外连接
-- A. 查询员工表 所有 员工的姓名， 和对应的部门名称（左外连接）
select tb_emp.name 姓名, tb_dept.name 所属部门 from tb_emp left join tb_dept on tb_dept.id = tb_emp.dept_id;
-- 此时会将没有选择部门的员工也打印出来
-- B. 查询部门表 所有 部门的名称， 和对应的员工名称（右外连接）
select tb_emp.name 姓名, tb_dept.name 所属部门 from tb_emp right join tb_dept on tb_dept.id = tb_emp.dept_id;
-- 此时会打印出没有人选择的部门，即全部部门
4、子查询
SQL语句中嵌套select语句，称为嵌套查询，又称子查询。
语法：
select * from t1 where column1 = (select column1 from t2 ……);
子查询外部的语句可以是insert/ update/ select的任何一个，最常见的是select
分类：
- 标量子查询：子查询返回的结果为单个值
- 列子查询：子查询返回的结果为一列（可以是多行）
- 行子查询：子查询返回的结果为一行（可以是多列）
- 表子查询：子查询返回的结果为多行多列
4.1标量子查询
- 子查询返回的结果是单个值（数字、字符串、日期等），最简单的形式
- 常用的操作符：
=、 <>、 >、 >=、 <、 <=
示例：
-- 子查询
-- 标量子查询
-- A. 查询“教研部”所有员工信息
-- a. 查询 教研部 的部门 ID
select id from tb_dept where name = '教研部';
-- b. 查询该部门ID下的员工信息
select * from tb_emp where dept_id = (select id from tb_dept where name = '教研部');
-- B. 查询在“方东白”入职之后的员工信息
-- a. 查询 方东白 的入职日期
select entrydata from tb_emp where name = '方东白';
-- b. 查询 该日期后 入职的员工信息
select * from tb_emp where entrydata > (select entrydata from tb_emp where name = '方东白');
4.2列子查询
- 子查询返回的结果是一列（可以是多行）
- 常用的操作符：
in、 not in等
示例：
-- 列子查询（可以是多行）
-- A. 查询“教研部”和“咨询部” 的所有员工信息
-- a. 查询“教研部”和“咨询部”的部门ID
select id from tb_dept where name = '教研部' || name = '咨询部';
-- b. 根据部门ID，查询该部门下的员工信息
select * from tb_emp where dept_id in (select id from tb_dept where name = '教研部' || name = '咨询部');
4.3行子查询
- 子查询返回的结果是一行（可以是多列）
- 常用的操作符：
=、 <>、 in、 not in
示例：
-- 行子查询（可以是多列）
-- A. 查询与“韦一笑”的入职日期 及 职位都相同的员工信息
-- a. 查询“韦一笑”的入职日期 及 职位
select entrydata , job from tb_emp where name = '韦-“笑';
-- b. 查询与其入职日期相同的 及 职位都相同的员工信息
select * from tb_emp where entrydata = (select entrydata from tb_emp where name = '韦-“笑') and job = (select job from tb_emp where name = '韦-“笑');
select * from tb_emp where (entrydata, job) = (select entrydata , job from tb_emp where name = '韦-“笑');
4.4表子查询
- 子查询返回的结果是多行多列，常作为临时表
- 常用的操作符：
in
示例：
-- 表子查询
-- A. 查询入职日期是“2006-01-01”之后的员工信息， 及其部门名称
-- a. 查询入职日期是“2006-01-01”之后的员工信息
select * from tb_emp where entrydata > '2006-01-01';
-- b. 查询这部分员工信息及其所属部门的名称
select e.*, b.name from (select * from tb_emp where entrydata > '2006-01-01') e, tb_dept b where e.dept_id = b.id;
三、事务
事务（Transaction）是数据库管理系统中的一个重要概念，是一组操作的集合，用于确保一组操作的完整性和一致性。事务是一系列被视为单个逻辑单位的操作，这些操作要么全部成功执行，要么全部不执行。事务的主要目的是保证数据的完整性，避免数据不一致的情况。
事务的基本特性（ACID）
事务具有四个基本特性，统称为ACID特性：
- 原子性（
Atomicity）：- 事务中的所有操作要么全部完成，要么全部不执行。如果事务中的某个操作失败，之前的所有操作也会被撤销，数据库恢复到事务开始前的状态。
- 一致性（
Consistency）：- 事务必须使数据库从一个一致性状态转变为另一个一致性状态。在事务执行之前和之后，数据库的约束条件必须被满足。
- 隔离性（
Isolation）：- 事务的执行不应受到其他事务的影响。多个事务并发执行时，每个事务都应像是独立的，互不干扰。隔离级别的设置决定了事务之间的可见性和干扰程度。
- 持久性（
Durability）：- 一旦事务提交，其对数据库所做的更改是永久性的，即使系统崩溃或出现故障，已提交的事务也不会丢失。
事务的操作：
-- 事务 --
-- 开启事务 - start transaction 或者 begin --（类似于一个操作的备份）
start transaction;
-- 删除部门
delete from tb_dept where id = 2;
-- 删除部门下的员工
delete from tb_emp where dept_id = 2;
-- 提交事务 -- 当所有的事务都成功运行了，再提交，且操作不可逆
commit;
-- 回滚事务 -- 如果有运行失败的事务，可以通过回滚事务，恢复原来的数据
rollback;
四、索引
索引是数据库管理系统中用于提高数据查询性能的重要数据结构。它类似于书籍的目录，可以帮助快速定位到所需的数据，而无需扫描整个表。索引通过创建一个额外的数据结构来加速查找操作，从而提高查询效率。
4.1索引的结构：
Mysql数据库中默认的索引结构是B+tree。
B+tree：
B+树是一种自平衡的树数据结构，广泛用于数据库和文件系统中，以高效地管理和存储大量数据。它是B树的一种变体，具有一些特定的特性，使其在某些应用中更加高效。
B+树的基本概念
- 树的结构：
- B+树是多路平衡查找树，节点可以有多个子节点。每个节点包含多个键值和指向子节点的指针。
- 所有的叶子节点在同一层级，这使得树的高度保持较小，有利于快速查找。
- 键的存储：
- 在B+树中，所有的数据记录只存储在叶子节点中，而内部节点只存储键值和指向子节点的指针。这种设计使得内节点更加高效，有助于减少树的高度。
- 顺序访问：
- 叶子节点通过指针相互连接，允许顺序访问。这使得B+树在范围查询和排序操作中表现出色。
特点：
- 每一个节点，可以存储多个key（有n个key，就有n个指针）
- 所有的数据都存储在叶子节点，非叶子节点仅用于索引数据
- 叶子节点形成了一颗双向链表，便于数据的顺序及区间范围查询
4.2语法：
- 创建索引：
create [unique] index 索引名 on 表名(字段名, ……); - 查看索引：
show index from 表名; - 删除索引：
drop index 索引名 on 表名;
注意事项：
- 主键字段，再建表时，会自动创建主键的索引，且主键索引在所有索引中性能是最高的
- 添加唯一约束时，数据库实际上会添加唯一索引
示例：
-- 索引
-- 创建 ： 为tb_emp表的name字段建立一个索引
create index tb_emp_name on tb_emp(name);
-- 查询 ： 查询tb_emp表的索引信息
show index from tb_emp;
-- 删除 ： 删除tb_emp表中name字段的索引
drop index tb_emp_name on tb_emp;
