MySQL详解（二）——基础 2.0

5. 完整性约束

完整性约束是为了表的数据的正确性！如果数据不正确，那么一开始就不能添加到表中。

5.1 主键

当某一列添加了主键约束后，那么这一列的数据就不能重复出现。这样每行记录中其主键列的值就是这一行的唯一标识。例如学生的学号可以用来做唯一标识，而学生的姓名是不能做唯一标识的，因为学习有可能同名。

主键列的值不能为NULL，也不能重复！

指定主键约束使用PRIMARY KEY关键字

创建表：定义列时指定主键：

CREATE TABLE stu(
    sid CHAR(6) PRIMARY KEY,
    sname VARCHAR(20),
    age INT,
    gender VARCHAR(10) 
);

创建表：定义列之后独立指定主键：

CREATE TABLE stu(
    sid CHAR(6),
    sname VARCHAR(20),
    age INT,
    gender VARCHAR(10),
    PRIMARY KEY(sid)
);

修改表时指定主键：

ALTER TABLE stu ADD PRIMARY KEY(sid);

删除主键（只是删除主键约束，而不会删除主键列）：

ALTER TABLE stu DROP PRIMARY KEY;

5.2 主键自增长

MySQL提供了主键自动增长的功能！这样用户就不用再为是否有主键是否重复而烦恼了。当主键设置为自动增长后，在没有给出主键值时，主键的值会自动生成，而且是最大主键值+1，也就不会出现重复主键的可能了。

创建表时设置主键自增长（主键必须是整型才可以自增长）：

CREATE TABLE stu(
    sid INT PRIMARY KEY AUTO_INCREMENT,
    sname VARCHAR(20),
    age INT,
    gender VARCHAR(10)
);

修改表时设置主键自增长：

ALTER TABLE stu CHANGE sid sid INT AUTO_INCREMENT;

修改表时删除主键自增长：

ALTER TABLE stu CHANGE sid sid INT;

5.3 非空

指定非空约束的列不能没有值，也就是说在插入记录时，对添加了非空约束的列一定要给值；在修改记录时，不能把非空列的值设置为NULL。

指定非空约束：

CREATE TABLE stu(
    sid INT PRIMARY KEY AUTO_INCREMENT,
    sname VARCHAR(10) NOT NULL,
    age INT,
    gender VARCHAR(10)
);

当为sname字段指定为非空后，在向stu表中插入记录时，必须给sname字段指定值，否则会报错：

INSERT INTO stu(sid) VALUES(1);

插入的记录中sname没有指定值，所以会报错！

5.4 唯一

还可以为字段指定唯一约束！当为字段指定唯一约束后，那么字段的值必须是唯一的。这一点与主键相似！例如给stu表的sname字段指定唯一约束：

CREATE TABLE tab_ab(
     sid INT PRIMARY KEY AUTO_INCREMENT,
     sname  VARCHAR(10) UNIQUE
);

INSERT INTO sname(sid, sname) VALUES(1001, ‘zs’);

INSERT INTO sname(sid, sname) VALUES(1002, ‘zs’);

当两次插入相同的名字时，MySQL会报错！

5.5 外键

主外键是构成表与表关联的唯一途径！

外键是另一张表的主键！例如员工表与部门表之间就存在关联关系，其中员工表中的部门编号字段就是外键，是相对部门表的外键。

创建dept表，指定deptno为主键列：

CREATE TABLE dept(

​ deptno INT PRIMARY KEY,

​ dname varchar(14),

​ loc varchar(13)

);

创建t_section表，指定sid为主键列，u_id为相对t_user表的uid列的外键：

CREATE TABLE emp(

​ empno INT,

​ ename VARCHAR(50),

​ job VARCHAR(50),

​ mgr INT,

​ hiredate DATE,

​ sal DECIMAL(7,2),

​ comm decimal(7,2),

​ deptno INT,

​ CONSTRAINT fk_e_d FOREIGN KEY(deptno) REFERENCES dept(deptno)

)

5.6 表与表之间的关系

一对一：例如t_person表和t_card表，即人和身份证。这种情况需要找出主从关系，即谁是主表，谁是从表。人可以没有身份证，但身份证必须要有人才行，所以人是主表，而身份证是从表。设计从表可以有两种方案：

在t_card表中添加外键列（相对t_user表），并且给外键添加唯一约束；

给t_card表的主键添加外键约束（相对t_user表），即t_card表的主键也是外键。

一对多（多对一）：最为常见的就是一对多！一对多和多对一，这是从哪个角度去看得出来的。t_user和t_section的关系，从t_user来看就是一对多，而从t_section的角度来看就是多对一！这种情况都是在多方创建外键！

多对多：例如t_stu和t_teacher表，即一个学生可以有多个老师，而一个老师也可以有多个学生。这种情况通常需要创建中间表来处理多对多关系。例如再创建一张表t_stu_tea表，给出两个外键，一个相对t_stu表的外键，另一个相对t_teacher表的外键。

6. 事务

事务属于TCL控制语言（Transaction Control Language）。

6.1 事务概述

什么是事务？为什么要用事务？

一个事务是由一条或者多条sql语句构成，这一条或者多条sql语句要么全部执行成功，要么全部执行失败！

默认情况下，每条单独的sql语句就是一个单独的事务！

举例：

银行转账！张三转10000块到李四的账户，这其实需要两条SQL语句：

给张三的账户减去10000元；

给李四的账户加上10000元。

如果在第一条SQL语句执行成功后，在执行第二条SQL语句之前，程序被中断了（可能是抛出了某个异常，也可能是其他什么原因），那么李四的账户没有加上10000元，而张三却减去了10000元。这肯定是不行的！

你现在可能已经知道什么是事务了吧！事务中的多个操作，要么完全成功，要么完全失败！不可能存在成功一半的情况！也就是说给张三的账户减去10000元如果成功了，那么给李四的账户加上10000元的操作也必须是成功的；否则给张三减去10000元，以及给李四加上10000元都是失败的！

6.2 事务的四大特性（ACID）

原子性（Atomicity）：事务中所有操作是不可再分割的原子单位。事务中所有操作要么全部执行成功，要么全部执行失败。

一致性（Consistency）：事务执行后，数据库状态与其它业务规则保持一致。如转账业务，无论事务执行成功与否，参与转账的两个账号余额之和应该是不变的。

隔离性（Isolation）：隔离性是指在并发操作中，不同事务之间应该隔离开来，使每个并发中的事务不会相互干扰。

持久性（Durability）：一旦事务提交成功，事务中所有的数据操作都必须被持久化到数据库中，即使提交事务后，数据库马上崩溃，在数据库重启时，也必须能保证通过某种机制恢复数据。

不考虑隔离性会产生的3个问题：

脏读：一个事务还未提交，另外一个事务访问此事务修改的数据，并使用，读取了事务中间状态数据。

幻读：一个事务读取2次，得到的记录条数不一致，由于2次读取之间另外一个事务对数据进行了增删。

不可重复读：一个事务读取同一条记录2次，得到的结果不一致，由于在2次读取之间另外一个事务对此行数据进行了修改。

Mysql的四种隔离级别
SQL标准定义了4类隔离级别，包括了一些具体规则，用来限定事务内外的哪些改变是可见的，哪些是不可见的。低级别的隔离级一般支持更高的并发处理，并拥有更低的系统开销。

Read Uncommitted（读取未提交内容）

在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。本隔离级别很少用于实际应用，因为它的性能也不比其他级别好多少。读取未提交的数据，也被称之为脏读（Dirty Read）。

Read Committed（读取提交内容）

这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）。它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。这种隔离级别 也支持所谓的不可重复读（Nonrepeatable Read），因为同一事务的其他实例在该实例处理其间可能会有新的commit，所以同一select可能返回不同结果。

Repeatable Read（可重读）

这是MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。不过理论上，这会导致另一个棘手的问题：幻读 （Phantom Read）。简单的说，幻读指当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影” 行。InnoDB和Falcon存储引擎通过多版本并发控制（MVCC，Multiversion Concurrency Control）机制解决了该问题。

Serializable（可串行化）

这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争。

在MySQL中，实现了这四种隔离级别，分别有可能产生问题如下所示：

mysql修改隔离级别，set session transaction isolation level read committed

查看mysql的隔离级别：select @@tx_transaction

6.3 MySql中的事务

在默认情况下，MySQL每执行一条SQL语句，都是一个单独的事务。如果需要在一个事务中包含多条SQL语句，那么需要开启事务和结束事务。

开启事务：start transaction；

sql语句执行；

结束事务：commit或rollback。

在执行SQL语句之前，先执行strat transaction，这就开启了一个事务（事务的起点），然后可以去执行多条SQL语句，最后要结束事务，commit表示提交，即事务中的多条SQL语句所做出的影响会持久化到数据库中。或者rollback，表示回滚，即回滚到事务的起点，之前做的所有操作都被撤消了！

分类：

隐式事务：没有明显的开启和结束标志，比如dml语句insert、update、delete。

显示事务：具有明显的开启和结束标记，比如多条sql语句组成。

演示事务

比如insert、update等自带事务，可取消事务自动开启：

set autocommit=0; 0指false

创建事务要用到的表

CREATE TABLE account(

​ id INT PRIMARY KEY AUTO_INCREMENT,

​ NAME VARCHAR(30),

​ balance NUMERIC(10.2)

);

INSERT INTO account(NAME,balance) VALUES(‘fanbingbing’, 100000);

INSERT INTO account(NAME,balance) VALUES(‘lichen’, 100000);

SELECT * FROM account;

演示事务执行失败

sql更新到缓存，成功则提交。失败回滚。

案例一：回滚 事务执行失败！

START TRANSACTION;

UPDATE account SET balance=balance-10000 WHERE id=1;

UPDATE account SET balance=balance+10000 WHERE id=2;

ROLLBACK;

案例二：退出mysql客户端 事务执行失败！

START TRANSACTION;

UPDATE account SET balance=balance-10000 WHERE id=1;

UPDATE account SET balance=balance+10000 WHERE id=2;

演示事务执行成功

START TRANSACTION;

UPDATE account SET balance=balance-10000 WHERE id=1;

UPDATE account SET balance=balance+10000 WHERE id=2;

COMMIT; 提交