MySQL基础（下）

一、约束

1、概述

• 概念：约束是作用于表中字段上的规则，用于限制存储在表中的数据。
• 目的：保证数据库中数据的正确、有效性和完整性。
• 分类：

约束	描述	关键字
非空约束	限制该字段的数据不能为null	not null
唯一约束	保证该字段的所有数据都是唯一、不重复的	unique
主键约束	主键是一行数据的唯一标识，要求非空且唯一	primary key
默认约束	保存数据时，如果未指定该字段的值，则采用默认值	default
检查约束	（8.0.1版本后） 保证字段值满足某一个条件	check
外键约束	用来让两张图的数据之间建立连接，保证数据的一致性和完整性	foreign key

注意：约束是作用于表中字段上的，可以再创建表/修改表的时候添加约束。

常用约束

约束条件	关键字
主键	primary key
自动增长	auto_increment
不为空	not null
唯一	unique
逻辑条件	check
默认值	default

例子：

create table user(
    id int primary key auto_increment,
    name varchar(10) not null unique,
    age int check(age > 0 and age < 120),
    status char(1) default '1',
    gender char(1)
);

2、外键约束

外键用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性。
添加外键：

create table 表名(
    字段名 字段类型,
    ...
    [constraint] [外键名称] foreign key(外键字段名) references 主表(主表列名)
);
alter table 表名 add constraint 外键名称 foreign key (外键字段名) references 主表(主表列名);
-- 例子
alter table emp add constraint fk_emp_dept_id foreign key(dept_id) references dept(id);

删除外键：

alter table 表名 drop foreign key 外键名;

删除/更新行为

行为	说明
no action	当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新（与restrict一致）
restrict	当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新（与no action一致）
cascade	当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则也删除/更新外键在子表中的记录
set null	当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键值为null（要求该外键允许为null）
set default	父表有变更时，子表将外键设为一个默认值（innodb不支持）

更改删除/更新行为：

alter table 表名 add constraint 外键名称 foreign key (外键字段) references 主表名(主表字段名) on update 行为 on delete 行为;

二、多表查询

1、多表关系

1.1 概述

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

• 一对多（多对一）
• 多对多
• 一对一

1.2 一对多

案例：部门与员工
关系：一个部门对应多个员工，一个员工对应一个部门
实现：在多的一方建立外键，指向一的一方的主键

1.3 多对多

案例：学生与课程
关系：一个学生可以选多门课程，一门课程也可以供多个学生选修
实现：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

1.4 一对一

案例：用户与用户详情
关系：一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率
实现：在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的（unique）

2、多表查询概述

2.1 概述

指从多张表中查询数据

2.2 笛卡尔积

两个集合a集合和b集合的所有组合情况（在多表查询时，需要消除无效的笛卡尔积）
合并查询（笛卡尔积，会展示所有组合结果）：

select * from employee, dept;

消除无效笛卡尔积：

select * from employee, dept where employee.dept = dept.id;

2.3 多表查询分类

（1）连接查询

• 内连接：相当于查询两张表交集部分数据
• 外连接
  左外连接：查询左表所有数据以及两张表交集部分数据
  右外连接：查询右表所有数据以及两张表交集部分数据
• 自连接：当前表与自身的连接查询，自连接必须使用表别名

（2）子查询

3、连接查询—内连接

内连接查询的是两张表交集的部分
隐式内连接：

select 字段列表 from 表1, 表2 where 条件 ...;

显式内连接：

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

显式性能比隐式高
例子：

-- 查询员工姓名，及关联的部门的名称
-- 隐式
select e.name, d.name from employee as e, dept as d where e.dept = d.id;
-- 显式
select e.name, d.name from employee as e inner join dept as d on e.dept = d.id;

4、连接查询—外连接

左外连接：
查询左表所有数据，以及两张表交集部分数据

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

相当于查询表1的所有数据，包含表1和表2交集部分数据
右外连接：
查询右表所有数据，以及两张表交集部分数据

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

相当于查询表2的所有数据，包含表1和表2交集部分数据
例子：

-- 左
select e.*, d.name from employee as e left outer join dept as d on e.dept = d.id;
select d.name, e.* from dept d left outer join emp e on e.dept = d.id;  -- 这条语句与下面的语句效果一样
-- 右
select d.name, e.* from employee as e right outer join dept as d on e.dept = d.id;

左连接可以查询到没有dept的employee，右连接可以查询到没有employee的dept

5、连接查询—自连接

当前表与自身的连接查询，自连接必须使用表别名
语法：

select 字段列表 from 表a 别名a join 表a 别名b on 条件 ...;

自连接查询，可以是内连接查询，也可以是外连接查询
例子：

-- 查询员工及其所属领导的名字
select a.name, b.name from employee a, employee b where a.manager = b.id;
-- 没有领导的也查询出来
select a.name, b.name from employee a left join employee b on a.manager = b.id;

6、联合查询-union, union all

对于union查询，就是把多次查询的结果合并，形成一个新的查询集
语法：

select 字段列表 from 表a ...
union [all]
select 字段列表 from 表b ...

注意：
1、对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致
2、union all会将全部的数据直接合并在一起，union会对合并之后的数据去重
3、联合查询比使用or效率高，不会使索引失效

7、子查询

7.1 概念

sql语句中嵌套select语句，称谓嵌套查询，又称子查询。

select * from t1 where column1 = ( select column1 from t2);

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

7.2 分类

根据子查询结果可以分为：

• 标量子查询（子查询结果为单个值）
• 列子查询（子查询结果为一列）
• 行子查询（子查询结果为一行）
• 表子查询（子查询结果为多行多列）

根据子查询位置可分为：

• where 之后
• from 之后
• select 之后

7.3 标量子查询

子查询返回的结果是单个值（数字、字符串、日期等）。
常用操作符：- < > > >= < <=
例子：

-- 查询销售部所有员工
select id from dept where name = '销售部';
-- 根据销售部部门id，查询员工信息
select * from employee where dept = 4;
-- 合并（子查询）
select * from employee where dept = (select id from dept where name = '销售部');
-- 查询xxx入职之后的员工信息
select * from employee where entrydate > (select entrydate from employee where name = 'xxx');

7.4 列子查询

返回的结果是一列（可以是多行）。
常用操作符：

操作符	描述
in	在指定的集合范围内，多选一
not in	不在指定的集合范围内
any	子查询返回列表中，有任意一个满足即可
some	与any等同，使用some的地方都可以使用any
all	子查询返回列表的所有值都必须满足

例子：

-- 查询销售部和市场部的所有员工信息
select * from employee where dept in (select id from dept where name = '销售部' or name = '市场部');
-- 查询比财务部所有人工资都高的员工信息
select * from employee where salary > all(select salary from employee where dept = (select id from dept where name = '财务部'));
-- 查询比研发部任意一人工资高的员工信息
select * from employee where salary > any (select salary from employee where dept = (select id from dept where name = '研发部'));

7.5 行子查询

子查询返回的结果是一行（可以是多列）。
常用操作符：=, <, >, in, not in
例子：

-- 查询与xxx的薪资及直属领导相同的员工信息
select * from employee where (salary, manager) = (12500, 1);
select * from employee where (salary, manager) = (select salary, manager from employee where name = 'xxx');

7.6 表子查询

子查询返回的结果是多行多列。
常用操作符：in
例子：

-- 查询与xxx1，xxx2的职位和薪资相同的员工
select * from employee where (job, salary) in (select job, salary from employee where name = 'xxx1' or name = 'xxx2');
-- 查询入职日期是2006-01-01之后的员工，及其部门信息
select e.*, d.* from (select * from employee where entrydate > '2006-01-01') as e left join dept as d on e.dept = d.id;

三、事务

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

1、事务操作

基本操作：

-- 1. 查询张三账户余额
select * from account where name = '张三';
-- 2. 将张三账户余额-1000
update account set money = money - 1000 where name = '张三';
-- 此语句出错后张三钱减少但是李四钱没有增加
模拟sql语句错误
-- 3. 将李四账户余额+1000
update account set money = money + 1000 where name = '李四';

查看事务提交方式

select @@autocommit;

设置事务提交方式，1为自动提交，0为手动提交，该设置只对当前会话有效

set @@autocommit = 0;

提交事务

commit;

回滚事务

rollback;

-- 设置手动提交后上面代码改为：
select * from account where name = '张三';
update account set money = money - 1000 where name = '张三';
update account set money = money + 1000 where name = '李四';
commit;

操作方式二：
开启事务：

start transaction 或 begin transaction;

提交事务：

commit;

回滚事务：

rollback;

操作实例：

start transaction;
select * from account where name = '张三';
update account set money = money - 1000 where name = '张三';
update account set money = money + 1000 where name = '李四';
commit;

2、事务四大特性（acid）

• 原子性(atomicity)：事务是不可分割的最小操作但愿，要么全部成功，要么全部失败
• 一致性(consistency)：事务完成时，必须使所有数据都保持一致状态
• 隔离性(isolation)：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行
• 持久性(durability)：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的

3、并发事务问题

问题	描述
脏读	一个事务读到另一个事务还没提交的数据
不可重复读	一个事务先后读取同一条记录，但两次读取的数据不同
幻读	一个事务按照条件查询数据时，没有对应的数据行，但是再插入数据时，又发现这行数据已经存在

4、事务隔离级别

隔离级别	脏读	不可重复读	幻读
read uncommitted	√	√	√
read committed	×	√	√
repeatable read(默认)	×	×	√
serializable	×	×	×

1、√表示在当前隔离级别下该问题会出现
2、serializable 性能最低；read uncommitted 性能最高，数据安全性最差

查看事务隔离级别：

select @@transaction_isolation;

设置事务隔离级别：

set [ session | global ] transaction isolation level {read uncommitted | read committed | repeatable read | serializable };

session 是会话级别，表示只针对当前会话有效，global 表示对所有会话有效