一 多表设计
概念，在进行数据库表结构设计的时候，根据业务需求和业务模块之间的关系，分析并设计表结构，由于业务的相互关联，所以各个表结构之间也存在着各种联系，基本上分为三种。
- 一对多
- 多对多
- 一对一
1 一对多
1 > 例如说部门和员工模块的表结构 设计
以下结构就是在员工表中增加一个部门id 的字段
关系实现： 只需要在数据库多的一方，添加字段，来关联一的一方的主键
-- 员工管理(带约束)
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 教研主管',entrydate 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 '部门表';2> 外键约束
多表问题的分析，部门数据可以直接删除，然而有部分员工属于部门下，此时出现了数据的不完整，不一致的问题。
缺点：
- 1 影响增删改的效率
- 2 仅用于单节点的数据库 不适用分布式 集群场景
- 3 容易引发数据库思索问题 消耗性能
因此在项目开发中 推荐使用逻辑外键
外键语法 两种方式
- 创建表的时候指定
CREATE TABLE parent_table (id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(100) NOT NULL
);CREATE TABLE child_table (id INT AUTO_INCREMENT PRIMARY KEY,parent_id INT,description VARCHAR(255),FOREIGN KEY (parent_id) REFERENCES parent_table(id)
);- 建表完成之后，添加外键
在这个例子中，fk_parent 是外键约束的名称，parent_id 是 child_table 表中的外键列，它引用了 parent_table 表中的 id 列。
-- 假设 parent_table 和 child_table 已经存在
ALTER TABLE child_table
ADD CONSTRAINT fk_parent
FOREIGN KEY (parent_id) REFERENCES parent_table(id);2 一对一
案例 用户和身份证信息的关系
关系： 一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他字段放在另一张表中，以提升操作效率
如果一张表中对与某些信息查询频率较高，对于另外的信息查询较低，那么就可以将一张大表拆分成两张小表
实现：在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的
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);二 多表查询
1 准备sql语句
-- 部门管理
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());
2 从多表中查询数据
- 连接查询
分为内连接：相当于查询A，B 的交集部分数据
外连接：
左外连接：查询左表所有的数据
右外连接：查询右表所有数据
隐式内连接 ：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_emp.dept_id = tb_dept.id;-- B 显式内连接
select tb_emp.name ,tb_dept.name from tb_emp inner join tb_dept on tb_emp.dept_id = tb_dept.id;-- 起别名
select * from tb_emp e, tb_dept d where e.dept_id = d.id;
左外连接：
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;查出来每一个部门对应的数据 即使没有数据 也查出来
- 子查询
概念：sql 语句中嵌套select 语句，称之为嵌套查询，又称之为子查询
形式：select * from t1 where column 1 = (select column 1 from t2)
子查询返回的结果：
- 标量子查询 ：返回的结果是单个值 常见的操作符 = <> > >= < <=
- 列子查询： 返回的结果是一列
- 行子查询： 返回的结果是一行
- 表子查询： 返回的结果是多行多列
# 标量子查询
-- A 查询教研部所有的员工信息
-- a1 查询部门表教研部的部门ID
-- a2 再查询员工信息表所有的员工信息
select * from tb_emp where dept_id = (select id from tb_dept where name = '教研部');-- 查询在"方东白"入职之后所有的员工信息select * from tb_emp where entrydate > (select entrydate from tb_emp where name = '方东白');列子查询
子查询返回的结果是一列 ，可以是多行，常用操作符是in not in
# 列子查询
-- A 查询教研部 和 咨询部 的所有员工信息
-- a 查询教研部和 咨询部 的部门ID 由于第一个查询条件返回的是 2 和 3 ，不是单行单列的数据，因此这个查询条件称之为列子查询
select id from tb_dept where name in ('教研部', '咨询部');select * from tb_emp where dept_id in (select id from tb_dept where name in ('教研部', '咨询部'));行子查询
子查询返回的结果是一行
常用操作符：= , <> in not in
# 行子查询
select entrydate,job from tb_emp where name = '韦一笑';
-- 出现两次子查询 不推荐
# select * from tb_emp where entrydate = (select entrydate from tb_emp where name = '韦一笑') and job = (select job from tb_emp where name = '韦一笑');
-- 出现一次子查询 推荐
select * from tb_emp where (entrydate,job) = (select entrydate,job from tb_emp where name = '韦一笑');表子查询
返回的结果是多行多列，常作为临时表
常用操作符：in
-- 查询入职日期是 '2006-01-01' 之后的员工信息 及其部门名称
-- (select *from tb_emp where entrydate > '2006-01-01') 这就是张临时表
select e.* ,d.name from (select *from tb_emp where entrydate > '2006-01-01') e,tb_dept d where e.dept_id = d.id;三 事务
场景：学工部 整个部门解散了，那么该部门下的员工都需要删除了
如果sql 是两个语句，先删除部门，再删除员工，
如果部门删除成功了，删除该部门的员工时失效了，就造成了数据的不一致的问题
1 概念：
事务是一组操作的集合，它是一个不可分割的工作单位。事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即要么同事成功，要么同事失败。
- 开始事务: start transaction / begin
- 提交事务: commit
- 回滚事务: rollback
-- 开启事务
start transaction;
-- 删除部门
delete from tb_dept where id = 3;
-- 删除员工
delete from tb_emp where dept_id = 3;-- 提交事务
commit;-- 回滚事务
rollback;2 事务的四大特性
事务的四大特性也被称为ACID特性，是数据库事务处理的核心原则，它们确保了数据的完整性和一致性，为数据库系统的稳定性和可靠性提供了有力保障。以下是事务四大特性的详细解释：
1>原子性（Atomicity）
原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚。这一特性确保了事务在执行过程中，如果任何操作失败，则其他操作也不会提交，已执行的操作必须被撤销，使得数据库返回到事务开始之前的状态。原子性通过事务日志和回滚机制来实现。
- 实现方式：当事务开始时，数据库管理系统（DBMS）会记录事务的初始状态。在事务执行过程中，如果发生错误或异常，DBMS会利用事务日志中的信息，将数据库恢复到事务开始之前的状态。
- 应用场景：例如，在银行系统中，转账操作就是一个典型的事务，它要求转出和转入两个操作要么同时成功，要么同时失败，以避免资金的不一致。
⠀2>一致性（Consistency）
一致性是指数据库在事务操作前和事务处理后，数据必须都满足业务规则约束。事务在执行完毕后，数据库的状态必须满足所有的业务规则和约束条件，确保数据的准确性和完整性。
- 实现方式：一致性通过事务的执行逻辑和数据库的约束机制来保证。在事务执行过程中，DBMS会检查所有的业务规则和约束条件，确保数据的合法性。如果事务违反了任何规则或约束，DBMS会拒绝执行该事务，并返回错误信息。
- 应用场景：例如，在库存管理系统中，当进行商品出库操作时，事务必须确保出库后的库存数量仍然满足业务规则，如不为负数等。
⠀3>、隔离性（Isolation）
隔离性是指数据库允许多个并发事务同时对其数据进行读写和修改的能力，同时防止多个事务执行导致数据的不一致。隔离性保证了事务的独立性，避免了数据的不一致和冲突。
- 实现方式：隔离性通过事务的隔离级别和锁机制来实现。DBMS提供了多种隔离级别，如读未提交、读已提交、可重复读和序列化等。不同的隔离级别提供了不同程度的隔离性，同时也影响了系统的并发性能和吞吐量。
- 应用场景：例如，在在线购物系统中，多个用户可能同时购买同一件商品。通过适当的隔离级别和锁机制，DBMS可以确保每个用户的购买操作都是独立的，不会因为其他用户的操作而受到影响。
⠀4>、持久性（Durability）
持久性是指一旦事务提交，其对数据库中数据的改变就应该是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。持久性保证了数据的可靠性和稳定性，是数据库系统可用性的重要保障。
- 实现方式：持久性通过数据库的存储机制和备份策略来实现。当事务提交时，DBMS会将事务的结果写入到持久性存储介质中，如磁盘或固态硬盘等。同时，DBMS还会定期备份数据库，以确保在发生故障时能够恢复数据。
- 应用场景：例如，在医疗系统中，当医生录入患者的诊断信息时，事务的持久性能够确保这些信息被永久地保存在数据库中，不会因为系统故障而丢失。
3 索引
1 索引（index）是帮助数据库高效获取数据的数据结构
优点：
- 提高查询效率，降低数据库的IO成本
- 通过索引对数据进行排序，降低数据排序成本，降低CPU消耗
缺点：
- 索引会占据存储空间
- 提高查询效率但是降低insert,update,delete的效率
mysql 采用的是B+Tree 多路平衡树,，它的所有实际数据都存储在叶子节点中，而非叶子节点仅用于索引。
- 节点类型：B+Tree包括根节点、分支节点（非叶子节点）和叶子节点。
- 节点内容：
- 根节点和分支节点：只存储索引值和指向子节点的指针，不存储实际数据。
- 叶子节点：存储所有的关键字（索引值）以及指向数据记录的指针（或数据本身，取决于实现）。此外，叶子节点之间通过指针链接，形成一个有序的链表，便于范围查询和遍历。
2 操作语法
- 创建索引 create [unique] index 索引名 on 表名 （字段名）
- 查看索引 show index from 表名
- 删除索引 drop index 索引名 on 表名
-- 创建索引 为tb_emp表的name字段创建索引
create index idx_emp_name on tb_emp(name);-- 查询索引 PRIMARY 是主键索引 性能是最高的
show index from tb_emp;-- 删除索引
drop index idx_emp_name on tb_emp;