MySQL基础篇——约束和事务

事务与隔离级别比较常考

1、MySQL约束

约束作用于表中字段，在创建/修改表时使用

非空约束，唯一约束，主键约束，默认约束，检查约束，外键约束

create table user( id int primary key auto_increment comment '主键', --主键且自动增长 name varchar(10) not null unique comment '姓名', --非空且唯一 age int check (age>0 and age<=120) comment '年龄', --检查约束0-120岁范围 status char(1) default '1' comment '状态', --默认值为1 gender char(1) comment '性别' )comment '用户表'; --插入数据 insert into user(name, age, status, gender) values('tom1', 19, '1', '男'),('tom2', 22, '0', '男');

外键约束（两张表之间数据一致性）

父表的主键id——关联——子表的外键字段（如dept_id）

--已有表添加外键 alter table 表名 add constraint 外键名称 foreign key (外键字段名) references 主表（主表列名）; alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id); --将emp表dept_id与dept表的id字段关联 --新增表添加外键 create table student_course( id int primary key auto_increment comment '主键', studentid int not null comment '学生ID', courseid int not null comment '课程ID', constraint fk_courseid foreign key (courseid) references course(id), constraint fk_studentid foreign key (studentid) references student(id) )comment '学生课程中间表'; --删除外键 alter table 表名 drop foreign key fk_emp_dept_id; --外键删除更新行为 no action/restrict(默认值 不允许更改)、cascade（对应修改）、set null alter table 表名 add constraint 外键名称 foreign key (外键字段名) references 主表（主表列名）on update cascade on delete cascade;

2、多表查询

多表关系：多对一（添加外键），多对多（新增中间表添加外键），一对一（添加外键且唯一约束）

--自连接 select a.name, b.name from emp a left join emp b on a.managerid=b.id; -- 必须有别名 --联合查询union、union all（上下合并查询结果） select * from a union [all] select * from b; --union 合并去重，union all只合并，字段列数必须相同 --子查询 select * from emp where (salary, managerid)= (select salary, managerid from emp where name ='tom') --一行多列数据

3、事务操作

事务是一组操作的集合，全部成功/全部失败

--MySQL默认事务自动提交 select @@autocommit; --查看事务提交方式，1自动提交，0手动提交 set @@autocommit=0; --设置事务提交方式 commit; --提交事务 rollback； --回滚事务(回到初始状态) start transaction 或 begin; --开启事务 create table account( id int primary key auto_increment comment '主键ID', name varchar(10) comment '姓名', money int comment '余额' )comment '账户表'; insert into account(name,money) values ('a',2000),('b',2000); --转账操作(手动提交) select @@autocommit; set @@autocommit=0; --1、查询a用户余额 select * from account where name ='a'; --2、将a账户余额减去1000 update account set money = money - 1000 where name ='a'; --3、将b账户余额加上1000 update account set money = money + 1000 where name ='b'; commit; rollback; --如果执行出错； --若为自动提交的事务（比较常用） start transaction； ... commit; rollback; --如果执行出错；

事务四大特性 ACID

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

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

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

持久性D：事务一旦提交，它对数据库中数据的改变是永久的，哪怕数据库发生故障；

并发事务问题

1、脏读：一个事务读到另一个事务还没有提交的数据；

2、不可重复读：一个事务先后读取同一条记录，但两次读取的数据不同；Update / Delete

3、幻读：一个事务按照条件查询数据时，没有对应的数据行，但是插入数据时又发生这行数据已经存在；Insert

事务的隔离级别

select @@transaction_isolation; --查看事务隔离级别 set [session|global] transaction isolation level read uncommitted; --设置事务隔离级别 --session当前会话，global全局

1、read uncommitted：三种问题都会出现；

2、read committed：有不可重复读和幻读问题；

3、repeatable read（MySQL默认）：有幻读问题；

4、serializable：串行化均无问题，隔离级别最高，数据最安全，性能最差；

这是理论情况，实际应用有一些变化，在锁的部分会讲解。