MySQL 07 约束、事务

一、约束

1、列级约束

常见约束
NOT NULL：非空
该字段的值是必填的，如果不设置该约束，该字段默认是可以为空

DEFAULT:默认
该字段有默认值

CHECK:检查，mysql语法支持，但效果不支持
该字段值可以加以限制，比如年龄可以控制在0-120之间
PRIMARY KEY:主键
该字段值不可以重复
1、不可以为空
2、一个表中只能有一个主键，但是可以多个字段做组合主键
UNIQUE:唯一约束
该字段值不可以重复
1、可以为空
2、一个表中可以有多个唯一键，可以有组合唯一键
FOREIGN KEY:外键
用于限制多表的关系
1、从表的该字段值必须来自于主表的关联列的值
2、从表和主表的关联列的类型必须一样
3、主表的关联列必须是主键
4、在从表中设置外键
5、一个表中可以添加多个外键
6、插入数据时，先插入主表；删除数据时，先删除从表
分类：

项目	位置	支持
列级约束	列的后面	除了外键
表级约束	表的后面	除了非空和默认

创建表时添加约束：

CREATE TABLE 表名(
字段名 字段类型 列级约束,
字段名 字段类型 列级约束,
表级约束
)
1.添加列级约束 shift + f3
use test;
drop table if exists stuinfo;
create table stuinfo(
id int not null unique,#非空约束+唯一约束
stuname varchar(20) unique,#唯一约束
gender char default ‘男’,#默认约束
age int unsigned check (age between 0 and 120)#检查约束
);
desc stuinfo;
select * from stuinfo;
插入默认值
insert into stuinfo(id,stuname,age) values(3,‘lily’,12);
insert into stuinfo values(5,‘jack’,default,900);

唯一和主键
1.关于插入
nullinsert into stuinfo values(7,null,default,900);

2.关于插入多个唯一或主键
drop table if exists stuinfo;
create table stuinfo(
id int not null unique,#非空约束+主键约束
stuname varchar(20) unique,#唯一约束
gender char default ‘男’,#默认约束
age int unsigned check (age between 0 and 120)#检查约束
);

2、表级约束

添加表级约束
语法：
create table stuinfo(
id int ,
stuname varchar(20),
gender char,
age int unsigned,
【constraint 约束名】约束类型(字段));

案例
create table grade(
id int primary key,
gradename varchar(20)
)
drop table if exists stuinfo;
create table stuinfo(
id int not null, stuname varchar(20),
gender char default ‘男’,
age int unsigned,
gradeid int,
primary key(id,stuname),#组合主键：id+stuname
constraint uq unique(age),#唯一键
constraint fk_stuinfo foreign key(gradeid) references grade(id)#外键约束
);

DESC stuinfo;

SELECT * FROM grade;
组合主键
INSERT INTO grade VALUES(1,‘一年级’),(2,‘二年级’),(3,‘三年级’);
INSERT INTO stuinfo VALUES(2,‘lucy’,‘男’,233,1);
外键
INSERT INTO stuinfo VALUES(3,‘lucy’,‘男’,13,10);

3、修改表时添加约束

1.添加非空
ALTER TABLE stuinfo MODIFY COLUMN gender CHAR NOT NULL;

2.添加默认
ALTER TABLE stuinfo MODIFY COLUMN age INT UNSIGNED DEFAULT 18;

3.添加主键
①方式一：列级约束的做法ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;
②方式二：表级约束的做法
语法：
ALTER TABLE 表名 ADD [CONSTRAINT 约束名] 约束类型 (字段);
案例：ALTER TABLE stuinfo ADD PRIMARY KEY(id);

4.添加唯一
①方式一：列级约束的做法
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) UNIQUE;
②方式二：表级约束的做法
ALTER TABLE stuinfo ADD CONSTRAINT uq_stuinfo UNIQUE(stuname);

5.添加外键
ALTER TABLE stuinfo ADD CONSTRAINT fk_stuinfo_grade FOREIGN KEY(gradeid)
REFERENCES grade(id);

4、修改表时删除约束

1.删除非空
ALTER TABLE stuinfo MODIFY COLUMN gender CHAR ;

2.删除默认
ALTER TABLE stuinfo MODIFY COLUMN age INT UNSIGNED;

3.删除主键
ALTER TABLE stuinfo MODIFY COLUMN id INT ;
ALTER TABLE stuinfo DROP PRIMARY KEY;

4.删除唯一
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20)
;ALTER TABLE stuinfo DROP INDEX uq_stuinfo;

#查看指定表的所有索引
SHOW INDEX FROM stuinfo;

5.删除外键
ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_grade;

二、事务

1、事务的概念

**事务：**一组逻辑操作单元,使数据从一种状态变换到另一种状态。
事务处理（事务操作）： 保证所有事务都作为 一个工作单元来执行，即使出现了故障，都不能改变这种执行方式。当在一个事务中执行多个操作时，要么所有的事务 都被提交(commit)，那么这些修改就永久地保存下来；要么数据库管理系统将放弃所作的所有修改， 整个事务回滚(rollback)到最初状态。
为确保数据库中数据的一致性,数据的操纵应当是离散的成组的逻辑单元:当它全部完成时,数据的一致性可以保持,而当这个单元中的 一部分操作失败,整个事务应全部视为错误,所有从起始点以后的操作应全部回退到开始状态。

2、事务的特点

事务的ACID(acid)属性
（1） 原子性（Atomicity）
原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
（2）一致性（Consistency）
事务必须使数据库从一个一致性状态变换到另外一个一致性状态。
（3）隔离性（Isolation）
事务的隔离性是指一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
（4）持久性（Durability）
持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来的其他操作和数据库故障不应该对其有任何影响

3、事物的使用

以第一个 DML 语句的执行作为开始
以下面的其中之一作为结束:
COMMIT 或 ROLLBACK 语句
DDL 或 DCL 语句（自动提交）
用户会话正常结束
系统异常终了

set autocommit=0;#取消自动提交
start transaction;#开启事务
rollback;#回滚事务
commit;#提交事务

4、数据库的隔离级别

（1）对于同时运行的多个事务, 当这些事务访问数据库中相同的数据时, 如果没有采取必要的隔离机制, 就会导致各种并发问题:
脏读: 对于两个事务 T1, T2, T1 读取了已经被 T2 更新但还没有被提交的字段. 之后, 若 T2 回滚, T1读取的内容就是临时且无效的.
不可重复读: 对于两个事务T1, T2, T1 读取了一个字段, 然后 T2 更新了该字段. 之后, T1再次读取同一个字段, 值就不同了.
幻读: 对于两个事务T1, T2, T1 从一个表中读取了一个字段, 然后 T2 在该表中插入了一些新的行. 之后, 如果 T1 再次读取同一个表, 就会多出几行.

（2）数据库事务的隔离性: 数据库系统必须具有隔离并发运行各个事务的能力, 使它们不会相互影响, 避免各种并发问题.

（3）一个事务与其他事务隔离的程度称为隔离级别. 数据库规定了多种事务隔离级别, 不同隔离级别对应不同的干扰程度, 隔离级别越高, 数据一致性就越好, 但并发性越弱.

5、数据库提供的 4 种事务隔离级别

Oracle 支持的 2 种事务隔离级别：READ COMMITED, SERIALIZABLE。
Oracle 默认的事务隔离级别为: READ COMMITED
Mysql 支持 4 种事务隔离级别. Mysql 默认的事务隔离级别为: repeatable read

6、在 MySql 中设置隔离级别

（1）每启动一个 mysql 程序, 就会获得一个单独的数据库连接. 每个数据库连接都有一个全局变量 @@tx_isolation, 表示当前的事务隔离级别

（2）查看当前的隔离级别: SELECT @@tx_isolation;

（3）设置当前 mySQL 连接的隔离级别:
set transaction isolation level read committed;

（4）设置数据库系统的全局的隔离级别:
set global transaction isolation level read committed;
set global transaction isolation level repeatable read;