MySQL入门指南5（约束，索引，事务）

前期回顾：

        MySQL入门指南1（进入命令行，数据库，数据表操作）

        MySQL入门指南2（SQLyog使用，增删改查）

        MySQL入门指南3（常用函数）

        MySQL入门指南4（查询进阶，外连接）

目录

一、约束

        1. PRINARY  KEY

        2. NOT  NULL

        3. UNIQUE

        4. FOREIGN  KEY

        5. CHECK

二、索引

三、事务

        1. 事务介绍

        2. 事务隔离级别

        3. 事务ACID

四、最后的话

---

 

一、约束

        约束用于确保数据库的数据满足特定的商业规则。在MySQL中，约束包括：NOT NULL、UNIQUE、PRIMARY  KEY、FOREIGN  KEY、CHECK。

        1. PRINARY  KEY

        用于唯一的标示表行的数据，当定义主键约束后，该列不能重复。    

-- 基本用法：
字段 数据类型 PRIMARY KEY;

 

使用细节：

        1.primary key(主键)修饰的列中字段不能重复且不能为null。

        2. 一张表最多只能有一个主键，可以是复合主键。

        3. 主键的指定方式有两种

                        a. 创建表时直接在字段后加上 primary  key

                        b. 在表定义最后写primary  key(列）

        4.使用desc可以看到 primary key 的情况

        5. 实际开发中每个表往往都会设计一个主键。

 

代码演示：

-- 第1种指定主键的方式：直接在字段后加上 PRIMARY KEY
CREATE TABLE test5(
	id INT PRIMARY KEY, -- 指定为主键，其不可为NULL，不可重复
	`name` VARCHAR(23));
	
INSERT INTO test5
	VALUES(1,'mike');
	
INSERT INTO test5
	VALUES(2,'milan');
	
-- id为主键不可重复	
INSERT INTO test5
	VALUES(1,'john');

-- id为主键不可为NULL
INSERT INTO test5
	VALUES(NULL,'erson');
	
-- 第2种指定主键的方式：在表定义最后写PRIMARY KEY(列）	
CREATE TABLE test6(
	id INT,
	`name` VARCHAR(23),
	-- 此处设置为复合主键,这两个皆不可为NULL，
	PRIMARY KEY(id,`name`));

INSERT INTO test6
	VALUES(1,'mike');
	
INSERT INTO test6
	VALUES(2,'milan');
-- 只有id重复不算重复，可以成功插入	
INSERT INTO test6
	VALUES(1,'john');
-- 只有当二者都重复时才算重复	
INSERT INTO test6
	VALUES(1,'john');
-- 并且设为主键的列，均不能为NULL	
INSERT INTO test6
	VALUES(1,NULL);

DESC test5;

 

 

图解普通主键

 

图解复合主键

 

 

---

        2. NOT  NULL

        如果在列上定义了NOT NULL 那么当插入数据时，必须为列提供数据。

 -- 基本用法：
字段 字段类型 NOT NULL;

---

        3. UNIQUE

        当定义了唯一约束后，该列值是不能重复的。

基本用法：
字段名 字段类型 UNIQUE;

 

使用细节：

        1. 如果没有指定 NOT  NULL，则UNIQUE字段可以有多个NULL

        2.一张表中可以有多个UNIQUE

 

代码演示：

CREATE TABLE test7(
	id INT UNIQUE,
	`name` VARCHAR(23),
	email VARCHAR(23) UNIQUE NOT NULL); -- UNIQUE NOT NULL 类似于 PRIMARY KEY
	
INSERT INTO test7
	VALUES(1,'jack','jack@qq.com');
	
INSERT INTO test7
	VALUES(2,'tom','tom@qq.com');
-- id重复无法插入进去
INSERT INTO test7
	VALUES(2,'mumu','mumu@qq.com');

INSERT INTO test7
	VALUES(NULL,'lalala','lalala@qq.com');
-- id 为空，可插入多个
INSERT INTO test7
	VALUES(NULL,'lalala','lala@qq.com');

 

图解UNIQUE

 

---

        4. FOREIGN  KEY

        用于定义主表与从表之间的关系：外键约束要定义在从表上，主表则必须具有主键约束或者UNIQUE约束。当定义外键约束后，要求外键列数据必须在主表的主键列存在或者是为NULL。       

-- 基本用法：
FOREIGN KEY (本表字段名) 
    REFERENCES 主表名（主表的主键名或UNIQUE字段名）

 

使用细节：

        1. 外键指向的表的字段，要求是PRIMARY KEY或者是UNIQUE。

        2. 表的存储引擎必须是INDODB ，这样的表才支持外键。

        3. 外键字段的类型要与主键字段的类型保持一致（长度可不同）。

        4. 外键字段的值，必须在主键字段中出现过，或者为NULL（未指定NOT  NULL时）。

        5. 一旦建立主外键关系，数据就不能随意删除。必须先删除从表才能再删除主表。

 

代码演示：

-- FOREIGN KEY 测试
-- 主表  班级表
CREATE TABLE test9(
	id INT PRIMARY KEY,
	nam VARCHAR(23),
	address VARCHAR(23));
	
INSERT INTO test9
	VALUES(100,'java','北京'),(200,'web','上海');
-- 从表  学生表
CREATE TABLE test10(
	id INT PRIMARY KEY,
	`name` VARCHAR(23) NOT NULL DEFAULT '',
	class_id INT,
	-- 此处指向主键或UNIQUE
	FOREIGN KEY (class_id) REFERENCES test9(id)); 

INSERT INTO test10
	VALUES(1,'tom',100),(2,'jack',200);
	
INSERT INTO test10
	VALUES(3,'milan',300);
	
INSERT INTO test10
	VALUES(4,'erson',NULL);
	
SELECT * FROM test10;
SELECT * FROM test9;
	
CREATE TABLE test11(
	id INT PRIMARY KEY,
	`name` VARCHAR(23) NOT NULL DEFAULT '',
	class_id INT,
	-- 此处指向的不是主键因此执行不成功
	FOREIGN KEY (`name`) REFERENCES test9(`name`));

-- 删除时需要先删除从表
DROP TABLE test10;

-- 才能把删除主表
DROP TABLE test9;

 

 

---

        5. CHECK

        用于强制行数据必须满足的条件，若未满足条件则会提示出错。

        MySQL5.7目前还不支持CHECK，只做语法校验。

        在ORACLE和SQL SERVER 中均支持CHECK。

        在MySQL实现CHECK的功能，一般时在程序中控制，或者通过触发器完成。

-- 基本用法:
字段 字段类型 CHECK (检查条件)

 

代码演示： 

-- CHECK 测试
CREATE TABLE test8(
	id INT UNIQUE NOT NULL DEFAULT 0,
	`name` VARCHAR(23),
	sex CHAR(1) CHECK(sex IN('男','女')),
	age INT CHECK(age>18&&age<60));
	
INSERT INTO test8
	VALUES(1,'Lihua','中',78);

 

图解CHECK

 

---

二、索引

        说起提高数据库性能，索引是最物美价廉的东西了。不用加内存，不用改程序，不用调sql，查询速度就可能提高千倍百倍。

1.索引的原理

        MySQL表进行查询时在没有索引的情况下，是进行全表扫描来查找。

        如果使用了索引，则会形成一个索引的数据结构，比如二叉树。这样在查询时，效率就会大大提升。

        但在查询效率提升的同时，也需要付出一些代价：

        比如：

                1. 会占用更多的磁盘空间

                2. 会影响 dml ( update , delete , insert ) 语句的效率（因为每次在执行这些操作时都要重建索引）

2. 索引的类型

        a. 主键索引，主键自动的为主索引（类型 PRIMARY KEY）

        b. 唯一索引（UNIQUE）

        c. 全文索引（FULLTEXT）[适用于MyISAM存储引擎]

        一般开发，不适应MySQL自带的全文索引，而是使用：全文搜索Solr和ElasticSearch(ES)

 3. 索引的使用

 

4. 适合使用索引的列

 

代码演示： 

-- 首先我们创建了一个有八百万条数据的emp表,代码省略

-- 查询索引（三种方式）
-- 方式一
SHOW INDEX FROM emp;

SHOW INDEXES FROM emp;

-- 方式二
SHOW KEYS FROM emp;

-- 方式三
DESC emp;

-- 测试查询速度（未设置索引时）
SELECT * FROM emp
	WHERE ename = 'tZOXxv';  -- 此时用时3秒
	
SELECT * FROM emp
	WHERE empno = 100676;  -- 此时用时3秒

	
-- 添加索引
-- 1.添加普通索引INDEX
-- 方式一
CREATE INDEX index_empno ON emp(empno);

-- 方式二
ALTER TABLE emp ADD INDEX index_empno(empno);

-- 测试查询速度（添加索引列）
SELECT * FROM emp
	WHERE empno = 100676;  -- 此时用时0.008秒
	
-- 测试查询速度（未添加索引列）
SELECT * FROM emp
	WHERE ename = 'tZOXxv'; -- 此时用时3秒

-- 删除索引方式一
DROP INDEX index_empno ON emp;  -- 用于删除索引

-- 删除索引方式二
ALTER TABLE emp DROP INDEX index_empno; -- 用于删除索引

-- 2.添加唯一索引（UNIQUE）
CREATE INDEX index_empno ON emp(empno);

ALTER TABLE emp ADD UNIQUE INDEX index_empno(empno);

-- 3.添加主键索引（比较特殊）
ALTER TABLE emp ADD PRIMARY KEY (empno);

-- 删除主键索引（比较特殊）
ALTER TABLE emp DROP PRIMARY KEY;

---

三、事务

        1. 事务介绍

        事务用于保证数据的一致性，它由一组相关的DML语句(INSERT ,DELETE ,UPDATE )组成，该组的DML语句要么全部成功，要么全部失败。如：转账就要用事务来处理，以保证数据的一致性。

        当执行事务操作时（DML语句），MySQL会在表上加锁，防止其他用户更改表的数据。这对用户来讲非常重要。

         MySQL数据库控制台事务的几个重要操作

                1. start  transaction      -- 开始一个事务

                2. savepoint  保存点名       -- 设置保存点

                3. rollback  to   保存点名       -- 回退事务

                4. rollback       -- 回退全部事务

                5. commit        -- 提交事务，所有的操作生效，不能回退

事务操作代码演示：

-- 事务测试
-- 开始事务
START TRANSACTION;

-- 执行DML语句
INSERT INTO test
	VALUES(5,'乐乐',18);

-- 设置保存点	
SAVEPOINT a;
	
-- 执行DML语句
UPDATE test
	SET age = 23
	WHERE NAME ='浩浩';
	
-- 设置保存点
SAVEPOINT b;

-- 执行DML语句
DELETE FROM test
	WHERE id=3;

-- 设置保存点
SAVEPOINT c;

-- 回退到指定保存点
ROLLBACK TO a;

-- 回退全部事务：回到事务开始时的状态
ROLLBACK;

-- 提交事务，所有操作生效，保存点失效
COMMIT;

回退事务：

        在介绍回退事务前，先介绍一下保存点（savepoint）。保存点是事务中的点，用于取消部分事务，当结束事务时（commit），会自动的删除该事务所定义的所有保存点。当执行回退事务时，通过指定保存点可以回退到指定的状态。 

提交事务：

        使用 commit 语句可以提交事务。当执行了 commit 语句后，会确认事务的变化、结束事务、删除保存点、释放锁、数据生效。当使用 commit 语句结束事务后，其它会话（其他连接）将可以查看到事务变化的新数据（所有数据就此生效）。 

 

 事务细节讨论：

        1. 如果不开始事务，默认情况下，DML操作是自动提交的，不能回滚。

        2. 如果开始一个事务，但没有创建保存点，可以执行 rollback，默认就是回退到你事务你开始的状态。

        3. 可以在事务中创建多个保存点（提交事务前）。

        4. 可以选择回退到你设置的保存点（提交事务前且未回到比该保存点更早的保存点前）

        5. MySQL的事务机制需要INNODB存储引擎才可以使用。 

---

        2. 事务隔离级别

        多个连接开启各自事务 操作数据库中数据时，数据库系统要负责隔离操作，以保证各个连接在获取数据时的准确性。

        如果不考虑隔离性，可能会引发如下问题：

        1. 脏读：一个事务读取到另一个事务尚未提交的改变（DML操作），此时发生脏读。

        2. 不可重复读：一个事务多次查询操作，由于其他事务提交所做的修改或删除，每次查询到不同的结果，此时发生不可重复读。

        3. 幻读：一个事务多次查询操作，由于其他事务提交所作的插入操作，每次查询到不同的结果，此时发生幻读。

        在多连接事务操作中，一般我们期望的是：当前事务只能读取到当前事务开始时的事务状态，而不能读取到其他操作产生的影响。

        四种隔离级别

        概念：MySQL隔离级别定义了事务与事务之间的隔离程度。

MySQL隔离级别	脏读	不可重复读	幻读	加锁读
读未提交（Read uncommitted）	是	是	是	不加锁
读已提交（Read committed）	否	是	是	不加锁
可重复读（Repeatable read）	否	否	否	不加锁
可串行化（Serializable）	否	否	否	加锁

 设置事务隔离级别

        1. 查看当前会话隔离级别

SELECT @@tx_isolation;

        2. 查看系统当前隔离级别

SELECT @@global.tx_isolation;

        3. 设置当前对话隔离级别

SET SESSION TRANSACTION ISOLATION LEVEL 指定隔离级别;

        4. 设置系统当前隔离级别

SET GLOBAL TRANSACTION ISOLATION LEVEL 指定隔离级别;

        5. 全局修改，修改 my.ini 配置文件，在最后加上

transaction-isolation = 指定隔离级别

        6. MySQL默认的事务隔离级别是Repeatable read，一般情况下，没有特殊要求，没有必要修改，因为该级别可以满足绝大部分项目需求。

   

温馨提示：可以试着多开几个mysql对话然后设置成不同的隔离级别，去做一些DML操作，看看四种隔离级别的差别，体会体会脏读，不可重复读，幻读的发生造成的困扰，理解理解 可串行化（Serializable）加锁操作的利与弊（设置为可串行化时，一个事务未提交，另一个事务将无法开始）。

---

        3. 事务ACID

1. 原子性（Atomicity）

        原子性是指事务时一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。

2. 一致性（Consistency）

        事务必须使数据库从一个一致性状态变换到另外一个一致性状态。

3. 隔离性（Isolation）

        事务的隔离性是多个用户并发访问数据库时，数据库为每个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。

4. 持久性（Durability）

        持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响。

 

---

四、最后的话

✨  原创不易，还希望各位大佬支持一下

👍  点赞，你的认可是我创作的动力！

⭐️  收藏，你的青睐是我努力的方向！

✏️  评论，你的意见是我进步的财富！