【MySql约束和事务】

SQL约束

1. 约束的作用:
   对表中的数据进行进一步的限制，从而保证数据的正确性、有效性、完整性.
   违反约束的不正确数据,将无法插入到表中
2. 常见的约束

约束名	约束关键字
主键	primary key
唯一	unique
非空	not null
外键	foreign key

主键约束

1. 特点 ：不可重复 唯一 非空
2. 作用 ：用来表示数据库中的每一条记录

添加主键约束

语法格式：字段名 字段类型 primary key

# 创建一个带主键的表
# 方式一：
CREATE TABLE emp2(
	eid INT PRIMARY KEY,
	ename VARCHAR(10),
	sex CHAR(1)
);
# 方式二：
CREATE TABLE emp2(
	eid INT,
	ename VARCHAR(10),
	sex CHAR(1),
	PRIMARY KEY(eid)
);
# 方式三：
CREATE TABLE emp2(
	eid INT,
	ename VARCHAR(10),
	sex CHAR(1)
);
ALTER TABLE emp2 ADD PRIMARY KEY(eid);

删除主键约束

-- 使用DDL语句 删除表中的主键 
ALTER TABLE emp2 DROP PRIMARY KEY;

主键的自增

注: 主键如果让我们自己添加很有可能重复,我们通常希望在每次插入新记录时,数据库自动生成主键字段
的值.
关键字: AUTO_INCREMENT 表示自动增长(字段类型必须是整数类型)

# 创建主键自增的表，添加数据 观察主键的自增
CREATE TABLE emp2(
	eid INT PRIMARY KEY AUTO_INCREMENT,
	ename VARCHAR(10),
	sex CHAR(1)
);
INSERT INTO emp2 (ename, sex) VALUES ('宋江', '男');
INSERT INTO emp2 (ename, sex) VALUES ('及时雨', '男');
INSERT INTO emp2 VALUES (NULL, '李逵', '男');
INSERT INTO emp2 VALUES (NULL, '黑旋风', '男');

修改主键自增的起始值

• 默认地 AUTO_INCREMENT 的开始值是 1，如果希望修改起始值,请使用下面的方式

# 创建主键自增的表,自定义自增其实值
CREATE TABLE emp2(
	eid INT PRIMARY KEY AUTO_INCREMENT,
	ename VARCHAR(10),
	sex CHAR(1)
) AUTO_INCREMENT=66;

DELETE和TRUNCATE对自增长的影响

清空表数据的方式	特点
DELETE	只是删除表中所有数据,对自增没有影响
TRUNCATE	truncate 是将整个表删除掉,然后创建一个新的表自增的主键,重新从 1开始

# delete 删除表中所有数据
SELECT * FROM emp2;
DELETE FROM emp2;
INSERT INTO emp2 (ename, sex) VALUES ('宋江', '男');
INSERT INTO emp2 (ename, sex) VALUES ('及时雨', '男');
INSERT INTO emp2 VALUES (NULL, '李逵', '男');
INSERT INTO emp2 VALUES (NULL, '黑旋风', '男');

# truncate删除 表中数据
TRUNCATE TABLE emp2;
INSERT INTO emp2 (ename, sex) VALUES ('宋江', '男');
INSERT INTO emp2 (ename, sex) VALUES ('及时雨', '男');
INSERT INTO emp2 VALUES (NULL, '李逵', '男');
INSERT INTO emp2 VALUES (NULL, '黑旋风', '男');

非空约束

1. 非空约束的特点: 某一列不予许为空
2. 语法格式：字段名 字段类型 not null

# 非空约束 
CREATE TABLE emp2( 
	eid INT PRIMARY KEY AUTO_INCREMENT, 
	-- 添加非空约束, ename字段不能为空 
	ename VARCHAR(20) NOT NULL, 
	sex CHAR(1) 
);

唯一约束

1. 唯一约束的特点: 表中的某一列的值不能重复( 对null不做唯一的判断 )
2. 语法格式：字段名 字段值 unique

#创建emp3表 为ename 字段添加唯一约束 
CREATE TABLE emp3( 
	eid INT PRIMARY KEY AUTO_INCREMENT, 
	ename VARCHAR(20) UNIQUE, 
	sex CHAR(1) 
);

主键约束与唯一约束的区别:

• 主键约束 唯一且不能够为空
• 唯一约束,唯一 但是可以为空
• 一个表中只能有一个主键 , 但是可以有多个唯一约束

默认值

1. 默认值约束 用来指定某列的默认值
2. 语法格式：字段名 字段类型 DEFAULT 默认值

# 创建带有默认值的表,为ename 字段添加默认值
CREATE TABLE emp3 (
	eid INT PRIMARY KEY AUTO_INCREMENT,
	ename VARCHAR(10) DEFAULT '奥里给',
	sex CHAR(1)
);
# 添加数据 使用默认值
INSERT INTO emp3 (ename, sex) VALUES (DEFAULT, '男');
INSERT INTO emp3 (sex) VALUES ('男');
# 不使用默认值 
INSERT INTO emp3 (ename, sex) VALUES ('黑旋风','男');

数据库事务

1. 事务是一个整体,由一条或者多条SQL 语句组成,这些SQL语句要么都执行成功,要么都执行失败, 只要有一条SQL出现异常,整个操作就会回滚,整个业务执行失败
2. 比如: 银行的转账业务,张三给李四转账500元 , 至少要操作两次数据库, 张三 -500, 李四 + 500,这中 间任何一步出现问题,整个操作就必须全部回滚, 这样才能保证用户和银行都没有损失.
3. 回滚:即在事务运行的过程中发生了某种故障，事务不能继续执行，系统将事务中对数据库的所有已完成的操作全部撤销，滚回到事务开始时的状态。（在提交之前执行）

MySQL事务操作

• MYSQL 中可以有两种方式进行事务的操作：
  1.手动提交事务
  2.自动提交事务

手动提交事务

功能	语句
开启事务	start transaction; 或者 BEGIN;
提交事务	commit;
回滚事务	rollback;

成功案例：
1、命令行登录数据库
2、开启事务: start transaction;
3、tom账户: -500 update account set money = money - 500 where name = ‘tom’;
4、jack账户: +500 update account set money = money + 500 where name = ‘jack’;
5、提交事务: commit;
如果事务中，有某条sql语句执行时报错了，我们没有手动的commit，那整个事务会自动回滚

注：如果事务中 SQL 语句没有问题，commit 提交事务，会对数据库数据的数据进行改变。 如果事务
中 SQL 语句有问题，rollback 回滚事务，会回退到开启事务时的状态。

自动提交事务

• MySQL 默认每一条 DML(增删改)语句都是一个单独的事务，每条语句都会自动开启一个事务，语句
  执行完毕 自动提交事务，MySQL 默认开始自动提交事务
• MySQL默认是自动提交事务
  

取消自动提交

1. 登录mysql，查看autocommit状态。
   on ：自动提交
   off ：手动提交

SHOW VARIABLES LIKE 'autocommit';

2. 把 autocommit 改成 off。

SET @@autocommit=off;

3. 再次修改,需要提交之后才生效

-- 修改数据 
update account set money = money - 500 where name = 'jack'; 
-- 手动提交 
commit;

事务的四大特性 ACID

特性	含义
原子性	每个事务都是一个整体，不可再拆分，事务中所有的 SQL 语句要么都执行成功， 要么都失败。
一致性	事务在执行前数据库的状态与执行后数据库的状态保持一致。如：转账前2个人的 总金额是 2000，转账后 2 个人总金额也是 2000.
隔离性	事务与事务之间不应该相互影响，执行时保持隔离的状态.
持久性	一旦事务执行成功，对数据库的修改是持久的。就算关机，数据也是要保存下来的.

MySQL 事务隔离级别

数据并发访问

• 一个数据库可能拥有多个访问客户端,这些客户端都可以并发方式访问数据库. 数据库的相同数据可能被多个事务同时访问,如果不采取隔离措施,就会导致各种问题, 破坏数据的完整性

并发访问会产生的问题

• 事务在操作时的理想状态： 所有的事务之间保持隔离，互不影响。因为并发操作，多个用户同时访问同一个 数据。可能引发并发访问的问题

并发访问的问题	说明
脏读	一个事务读取到了另一个事务中尚未提交的数据
不可重复读	一个事务中两次读取的数据内容不一致, 要求的是在一个事务中多次读取时数据是一致的. 这是进行 update 操作时引发的问题
幻读	一个事务中,某一次的 select 操作得到的结果所表征的数据状态, 无法支撑后续的业务操作. 查询得到的数据状态不准确,导致幻读.

四种隔离级别

• 通过设置隔离级别,可以防止上面的三种并发问题.
• MySQL数据库有四种隔离级别 上面的级别最低效率最高，下面的级别最高效率最低
  

隔离级别相关命令

1. 查看隔离级别

select @@tx_isolation;

2. 设置事务隔离级别，需要退出 MySQL 再重新登录才能看到隔离级别的变化

set global transaction isolation level 级别名称;

read uncommitted 读未提交
read committed 读已提交
repeatable read 可重复读
serializable 串行化