• 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

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

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

     

    使用细节：

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

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

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

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

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

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

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

     

    代码演示：

    1. -- 第1种指定主键的方式：直接在字段后加上 PRIMARY KEY
    2. CREATE TABLE test5(
    3. 	id INT PRIMARY KEY, -- 指定为主键，其不可为NULL，不可重复
    4. 	`name` VARCHAR(23));
    5. 	
    6. INSERT INTO test5
    7. 	VALUES(1,'mike');
    8. 	
    9. INSERT INTO test5
    10. 	VALUES(2,'milan');
    11. 	
    12. -- id为主键不可重复	
    13. INSERT INTO test5
    14. 	VALUES(1,'john');
    15.  
    16. -- id为主键不可为NULL
    17. INSERT INTO test5
    18. 	VALUES(NULL,'erson');
    19. 	
    20. -- 第2种指定主键的方式：在表定义最后写PRIMARY KEY(列）	
    21. CREATE TABLE test6(
    22. 	id INT,
    23. 	`name` VARCHAR(23),
    24. 	-- 此处设置为复合主键,这两个皆不可为NULL，
    25. 	PRIMARY KEY(id,`name`));
    26.  
    27. INSERT INTO test6
    28. 	VALUES(1,'mike');
    29. 	
    30. INSERT INTO test6
    31. 	VALUES(2,'milan');
    32. -- 只有id重复不算重复，可以成功插入	
    33. INSERT INTO test6
    34. 	VALUES(1,'john');
    35. -- 只有当二者都重复时才算重复	
    36. INSERT INTO test6
    37. 	VALUES(1,'john');
    38. -- 并且设为主键的列，均不能为NULL	
    39. INSERT INTO test6
    40. 	VALUES(1,NULL);
    41.  
    42. DESC test5;

     

    ce1324c6d5ec4659be76287a23746760.png

     

    ceeb6eeafaf14986b64d77b086e20aef.png

    图解普通主键

     

    c6e07b523f6f4716a1e115d132ab9947.png

    图解复合主键

     

     

            2. NOT  NULL

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

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

            3. UNIQUE

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

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

     

    使用细节：

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

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

     

    代码演示：

    1. CREATE TABLE test7(
    2. 	id INT UNIQUE,
    3. 	`name` VARCHAR(23),
    4. 	email VARCHAR(23) UNIQUE NOT NULL); -- UNIQUE NOT NULL 类似于 PRIMARY KEY
    5. 	
    6. INSERT INTO test7
    7. 	VALUES(1,'jack','jack@qq.com');
    8. 	
    9. INSERT INTO test7
    10. 	VALUES(2,'tom','tom@qq.com');
    11. -- id重复无法插入进去
    12. INSERT INTO test7
    13. 	VALUES(2,'mumu','mumu@qq.com');
    14.  
    15. INSERT INTO test7
    16. 	VALUES(NULL,'lalala','lalala@qq.com');
    17. -- id 为空，可插入多个
    18. INSERT INTO test7
    19. 	VALUES(NULL,'lalala','lala@qq.com');

     

    cecfe723c9c74d82886ade8671694fef.png

    图解UNIQUE

     

            4. FOREIGN  KEY

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

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

     

    使用细节：

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

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

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

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

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

     

    代码演示：

    1. -- FOREIGN KEY 测试
    2. -- 主表  班级表
    3. CREATE TABLE test9(
    4. 	id INT PRIMARY KEY,
    5. 	nam VARCHAR(23),
    6. 	address VARCHAR(23));
    7. 	
    8. INSERT INTO test9
    9. 	VALUES(100,'java','北京'),(200,'web','上海');
    10. -- 从表  学生表
    11. CREATE TABLE test10(
    12. 	id INT PRIMARY KEY,
    13. 	`name` VARCHAR(23) NOT NULL DEFAULT '',
    14. 	class_id INT,
    15. 	-- 此处指向主键或UNIQUE
    16. 	FOREIGN KEY (class_id) REFERENCES test9(id)); 
    17.  
    18. INSERT INTO test10
    19. 	VALUES(1,'tom',100),(2,'jack',200);
    20. 	
    21. INSERT INTO test10
    22. 	VALUES(3,'milan',300);
    23. 	
    24. INSERT INTO test10
    25. 	VALUES(4,'erson',NULL);
    26. 	
    27. SELECT * FROM test10;
    28. SELECT * FROM test9;
    29. 	
    30. CREATE TABLE test11(
    31. 	id INT PRIMARY KEY,
    32. 	`name` VARCHAR(23) NOT NULL DEFAULT '',
    33. 	class_id INT,
    34. 	-- 此处指向的不是主键因此执行不成功
    35. 	FOREIGN KEY (`name`) REFERENCES test9(`name`));
    36.  
    37. -- 删除时需要先删除从表
    38. DROP TABLE test10;
    39.  
    40. -- 才能把删除主表
    41. DROP TABLE test9;

     

    bbc908d61a334a1e9d0a7e2544d0b03c.png

     

            5. CHECK

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

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

            在ORACLE和SQL SERVER 中均支持CHECK。

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

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

     

    代码演示： 

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

     

    5017e452894c4598974ffbe2de003afb.png

    图解CHECK

     

    二、索引

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

    1.索引的原理

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

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

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

            比如：

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

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

    2. 索引的类型

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

            b. 唯一索引（UNIQUE）

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

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

     3. 索引的使用

     

    118913f1e43a4006830aa0d9e4467c52.png

    4. 适合使用索引的列

     

    c46855965f784525b807dfab7f5ef0d6.png

    代码演示： 

    1. -- 首先我们创建了一个有八百万条数据的emp表,代码省略
    2.  
    3. -- 查询索引（三种方式）
    4. -- 方式一
    5. SHOW INDEX FROM emp;
    6.  
    7. SHOW INDEXES FROM emp;
    8.  
    9. -- 方式二
    10. SHOW KEYS FROM emp;
    11.  
    12. -- 方式三
    13. DESC emp;
    14.  
    15. -- 测试查询速度（未设置索引时）
    16. SELECT * FROM emp
    17. 	WHERE ename = 'tZOXxv';  -- 此时用时3秒
    18. 	
    19. SELECT * FROM emp
    20. 	WHERE empno = 100676;  -- 此时用时3秒
    21.  
    22. 	
    23. -- 添加索引
    24. -- 1.添加普通索引INDEX
    25. -- 方式一
    26. CREATE INDEX index_empno ON emp(empno);
    27.  
    28. -- 方式二
    29. ALTER TABLE emp ADD INDEX index_empno(empno);
    30.  
    31. -- 测试查询速度（添加索引列）
    32. SELECT * FROM emp
    33. 	WHERE empno = 100676;  -- 此时用时0.008秒
    34. 	
    35. -- 测试查询速度（未添加索引列）
    36. SELECT * FROM emp
    37. 	WHERE ename = 'tZOXxv'; -- 此时用时3秒
    38.  
    39. -- 删除索引方式一
    40. DROP INDEX index_empno ON emp;  -- 用于删除索引
    41.  
    42. -- 删除索引方式二
    43. ALTER TABLE emp DROP INDEX index_empno; -- 用于删除索引
    44.  
    45. -- 2.添加唯一索引（UNIQUE）
    46. CREATE INDEX index_empno ON emp(empno);
    47.  
    48. ALTER TABLE emp ADD UNIQUE INDEX index_empno(empno);
    49.  
    50. -- 3.添加主键索引（比较特殊）
    51. ALTER TABLE emp ADD PRIMARY KEY (empno);
    52.  
    53. -- 删除主键索引（比较特殊）
    54. 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        -- 提交事务，所有的操作生效，不能回退

    事务操作代码演示：

    1. -- 事务测试
    2. -- 开始事务
    3. START TRANSACTION;
    4.  
    5. -- 执行DML语句
    6. INSERT INTO test
    7. 	VALUES(5,'乐乐',18);
    8.  
    9. -- 设置保存点	
    10. SAVEPOINT a;
    11. 	
    12. -- 执行DML语句
    13. UPDATE test
    14. 	SET age = 23
    15. 	WHERE NAME ='浩浩';
    16. 	
    17. -- 设置保存点
    18. SAVEPOINT b;
    19.  
    20. -- 执行DML语句
    21. DELETE FROM test
    22. 	WHERE id=3;
    23.  
    24. -- 设置保存点
    25. SAVEPOINT c;
    26.  
    27. -- 回退到指定保存点
    28. ROLLBACK TO a;
    29.  
    30. -- 回退全部事务：回到事务开始时的状态
    31. ROLLBACK;
    32.  
    33. -- 提交事务，所有操作生效，保存点失效
    34. COMMIT;

    回退事务：

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

    提交事务：

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

    98df83614b0041d786bdb1dab2bd4e87.png 

     事务细节讨论：

            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）

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

     

    四、最后的话

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


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


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


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

     

  • 相关阅读:
    【C#】类和结构体的区别
    流程引擎-自定义函数的应用
    Leetcode 面试题 17.10. 主要元素
    Jquery
    Django(二)精美博客搭建(11)实现文章列表分页查询及首页简单优化
    YGG GAP 第一季 NFT 背后的设计理念
    小程序项目如何创建
    每个企业家都需要知道的 5 种个人品牌策略
    深入了解Java 8 新特性：Stream流的实践应用（一）
    docker
  • 原文地址：https://blog.csdn.net/qq_64449257/article/details/126791559