MySQL约束和事务知识归纳。

叁歲伎倆 2023-10-02 20:59 7阅读 0赞

**目录**

一、MySQL 约束

1、主键 primary key

2、not null

3、unique

4、外键 foreign key

5、check

二、MySQL应用

1、自增长

2、MySQL索引

3、MySQL事务

4、事务隔离级别

5、表类型和存储引擎

6、视图

7、MySQL 管理

--------------------

## 一、MySQL 约束 ##

> **▶ 基本介绍**
> 
> 约束用于确保数据库的数据满足特定的商业规则。在mysql中，约束包括: not null，unique, primary key, foreign key, 和 check 五种。
> 
> ### 1、主键 primary key ###
> 
> **▶ 基本介绍**
> 
> 用于唯一的标示表行的数据,当定义主键约束后，该列不能重复
> 
> **▶ 细节**
> 
> ▷ primary key不能重复而且不能为null。
> 
> ▷ 一张表最多只能有一个主键，但可以是复合主键。
> 
> ▷ 主键的指定方式有两种：  
> 1、直接在字段名后指定 : 字段名 primakry key
> 
> 2、在表定义最后写 primary key(列名)  
> ▷ 使用desc 表名, 可以看到primary key的情况
> 
> ▷ 在实际开发中，每个表往往都会设计一个主键。
> 
> ### 2、not null ###
> 
> ![921e2c6bec914cdd8748995170c6ea2a.png][]
> 
> 如果在列上定义了not null，那么当插入数据时，必须为列提供数据。
> 
> ### 3、unique ###
> 
> ![1cef1ac20a2345bf86acdaf58e0f6195.png][]
> 
> 当定义了唯一约束后，该列值是不能重复的。
> 
>     CREATE TABLE 表名(
>          id INT UNIQUE , -- 表示 id 列是不可以重复的.
>         `name` VARCHAR(32) ,
>          email VARCHAR(32)
>        );
> 
> **▶ 细节**
> 
>  *  如果没有指定 not null , 则 unique 字段可以有多个 null 。如果一个列(字段)， 是 unique not null 使用效果类似 primary key
>  *  一张表可以有多个 unique 字段
> 
> ### 4、外键 foreign key ###
> 
> **▶ 基本介绍**
> 
> 用于定义主表和从表之间的关系:外键约束要定义在从表上，主表则必须具有主键约束或是unique约束，当定义外键约束后，要求外键列数据必须在主表的主键列存在或是为null。
> 
> ![9f5e4bae211e40f0bd993f81694fcb44.png][]
> 
> **▶ 细节**
> 
>  *  外键指向的表的字段，要求是primary key 或者是unique
>  *  表的类型是innodb,这样的表才支持外键
>  *  外键字段的类型要和主键字段的类型一致(长度可以不同)
>  *  外键字段的值，必须在主键字段中出现过, 或者为null前提是外健字段允许为null
>  *  一旦建立主外键的关系, 数据不能随意删除了
> 
> ### 5、check ###
> 
> **▶ 基本介绍**
> 
> 用于强制行数据必须满足的条件,假定在数值列上定义了check约束并要求数列值在1000~2000之间，如果不再1000~2000之间就会提示出错。  
> oracle 和 sql.server 均支持check，但是mysq15.7目前还不支持chck，只做语法校验，但不会生效。
> 
> ![836c2d9641db44e7be86bfadf1fac605.png][]
> 
> **▶ 基本使用**
> 
>     CREATE TABLE 表名 (
>         id INT PRIMARY KEY,
>         `name` VARCHAR(32) ,
>     
>         sex VARCHAR(6) CHECK (sex IN('man','woman')),
>     
>     
>         sal DOUBLE CHECK ( sal > 1000 AND sal < 2000)
>     );

--------------------

## 二、MySQL应用 ##

> ### 1、自增长 ###
> 
> ▶基本介绍
> 
> ![53677cce8cfd4b5ebc2620c1e95f06ae.png][]
> 
> ![ee3af2e1b0e444f29cfe59b28d17c5dc.png][]
> 
> **▶ 细节**
> 
>  *  一般来说自增长是和 primary key 配合使用的
>  *  自增长也可以单独使用\[但是需要配合一个unique\]
>  *  自增长修饰的字段为整数型的(虽然小数也可以但是非常非常少这样使用）
>  *  自增长默认从1开始, 你也可以通过如下命令修改alter table 表名 auto increment=新的开始值
>  *  如果你添加数据时，给自增长字段(列)指定的有值，则以指定的值为准，如果指定了自增长，一般来说.，就按照自增长的规则来添加数据
> 
> ▶ 自增长使用
> 
>     CREATE TABLE 表名 (
>         id INT PRIMARY KEY AUTO_INCREMENT,
>         email VARCHAR(32) NOT NULL DEFAULT '',
>         `name` VARCHAR(32) NOT NULL DEFAULT '');
> 
> ### 2、MySQL索引 ###
> 
> **▶ 基本介绍**
> 
> 说起提高数据库性能，索引是最物美价廉的东西了。不用加内存, 不用改程序, 不用调sql, 查询速度就可能提高百倍干倍。
> 
> **▶ 索引的原理**
> 
> 1.  没有索引为什么会慢?因为全表扫描
> 2.  使用索引为什么会快?形成一个索引的数据结构，比如二叉树
> 3.  索引的代价：
> 
> ① 磁盘占用  
> ② 对dml(update delete insert)语句的效率影响
> 
> **▶ 索引的类型**
> 
>  *  主键索引，主键自动的为主索引(类型Primary key)
>  *  唯一索引(UNIQUE)
>  *  普通索引(INDEX)
>  *  全文索引(FULLTEXT) \[适用于MyISAM\]
>  *  一般开发, 不使用mysql自带的全文索引，而是使用:全文搜索Solr和 ElasticSearch(ES)
> 
>     create table 表(
>         id int primary key, --主键，同时也是索引，称为主键索引。
>         name varchar(32));
>     
>     create table 表(
>         id int unique-- id是唯一的，同时也是索引，称为unique索引
>         );
> 
> **▶ 索引的使用**
> 
> ![d1dded5d15ed416692e0bd069f69c2b8.png][]
> 
> ![341aff79fe7a43d8a90be15136c8ae0e.png][]
> 
> ![c6d55f010f734bf0b113da52ea83062c.png][]
> 
> ![7f773157b1a440b5afc41808c95faa29.png][]
> 
> ![4215b54f6c824ae7bd7dde36d3847741.png][]
> 
> **▶ 如果使用索引**
> 
> ▷ 较频繁的作为查询条件字段应该创建索引
> 
> select \* from emp where empno=1
> 
> ▷ 唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件
> 
> select \* from emp where sex='男'
> 
> ▷ 更新非常频繁的字段不适合创建索引
> 
> select \* from emp where logincount= 1
> 
> ▷ 不会出现在WHERE子句中字段不该创建索引
> 
> ### 3、MySQL事务 ###
> 
> **▶ 基本介绍**
> 
> 事务用于保证数据的一致性,它由一组相关的dml语句组成，该组的dml活句要么全部成功, 要么全部失败。如:转账就要用事务来处理，用以保证数据的一致性。
> 
> **▶ 事务和锁**
> 
> 当执行事务操作时(dml语句) ,mysql会在表上加锁,防止其它用户改表的数据这对用户来讲是非常重要的。
> 
> ![ef8f8e88b626469e8ac1bdd173defeca.png][]
> 
>     -- 1. 创建一张测试表
>     CREATE TABLE t27 ( 
>         id INT,
>         `name` VARCHAR(32));
>     
>     -- 2. 开始事务
>     START TRANSACTION
>     
>     -- 3. 设置保存点
>     SAVEPOINT a
>     -- 执行 dml 操作
>     INSERT INTO t27 VALUES(100, 'tom');
>     
>     SAVEPOINT b
>     -- 执行 dml 操作
>     INSERT INTO t27 VALUES(200, 'jack');
>     
>     -- 回退到 b
>     ROLLBACK TO b
>     
>     -- 继续回退 a
>     ROLLBACK TO a
>     
>     -- 如果这样, 表示直接回退到事务开始的状态.
>     ROLLBACK
>     COMMIT
> 
> **▶ 回退事务**
> 
> 在介绍回退事务前，先介绍一下保存点(savepoint).保存点是事务中的点，用于取消部分事务，当结束事务时(commit)，会自动的删除该事务所定义的所有保存点当执行回退事务时，通过指定保存点可以回退到指定的点。
> 
> **▶ 提交事务**
> 
> 使用commit语包可以提交事务.当执行了commit语句子后,会确认事务的变化、结束事务、删除保存点、释放锁，数据生效。当使用commit语句结束事务子后，其它会话\[其他连接\]将可以查看到事务变化后的新数据\[所有数据就正式生效\]
> 
> ▶事务细节
> 
>  *  如果不开始事务，默认情况下，dml操作是自动提交的, 不能回滚
>  *  如果开始一个事务，你没有创建保存点.你可以执行rollback,默认就是回退到你事务开始的状态
>  *  你也可以在这个事务中(还没有提交时),创建多个保存点.比如: savepoint aaa; 执行dml, savepoint bbb;
>  *  你可以在事务没有提交前，选择回退到哪个保存点
>  *  mysql的事务机制需要innodb的存储引擎才可以使用,myisam不好使
>  *  开始一个事务 start transaction, set autocommit=off
> 
> ### 4、事务隔离级别 ###
> 
> **▶ 基本介绍**
> 
> 多个连接开启各自事务操作数据库中数据时，数据库系统要负责隔离操作，以保证各个连接在获取数据时的准确性。
> 
> 如果不考虑隔离性，可能会引发如下问题: ① 脏读；② 不可重复读； ③ 幻读
> 
> **▶ 解释**
> 
> 脏读(dirty read) : 当一个事务读取另一个事务尚未提交的改变(update,insert,delete)时，产生脏读  
> 不可重复读(nonrepeatable read) : 同一查询在同一事务中多次进行，由于其他提交事务所做的修改或删除, 每次返回不同的结果集, 此时发生不可重复读。
> 
> 幻读(phantom read) : 同一查询在同一事务中多次进行，由于其他提交事务所做的插入操作, 每次返回不同的结果集，此时发生幻读。
> 
> **▶ 隔离级别**
> 
> 概念 : MySQL隔离级别定义了事务与事务之间的隔离程度。
> 
> ![2d87f5cb999a4b13bb5df7a7eb545224.png][]
> 
> **▶ 设置隔离级别**
> 
> ▷ 查看当前会话隔离级别
> 
> set transaction.sql select @@tx isolation  
> ▷ 查看系统当前隔离级别
> 
> select @@global.tx isolation  
> ▷ 设置当前会话隔离级别  
> set session transaction isolation level repeatable read  
> ▷ 设置系统当前隔离级别  
> set global transaction isolation leve repeatable read:  
> ▷ mysql默认的事务隔离级别是 repeatable read一般情况下，没有特殊要求，没有必要修改(因为该级别可以满足绝大部分项目需求)
> 
> ![5aea8710ca2e4a4ca439a368cf598042.png][]
> 
> **▶ 事务 ACID**
> 
> ▷ 原子性(Atomicity)  
> 原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生
> 
>   
> ▷ 致性(Consistency)  
> 事务必须使数据库从一个一致性状态变换到另外一个一致性状态
> 
>   
> ▷ 隔离性 (lsolation)  
> 事务的隔离性是多个用户\#发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。
> 
>   
> ▷ 持久性(Durability)  
> 持久性是指一个事务一\#被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响
> 
> ### 5、表类型和存储引擎 ###
> 
> **▶ 基本介绍**
> 
> 1.  MySQL的表类型由存储引擎(Storage Engines) 决定，主要包括MylSAM，innoDB、 Memory等。
> 2.  MySQL 数据表主要支持六种类型，分别是:CSV、 Memory，ARCHIVE，MRG MYISAM、 MYISAM，InnoBDB。
> 3.  这六种又分为两类, 一类是”事务安全型”(transaction-safe)，比如：InnoDB。其余都属于第二类，称为”非事务安全型”(non-transaction-safe)\[mysiam 和 memory\]。
> 
> **▶ 特点**
> 
> ![b521a24144b2417588c38c583ea6bc7b.png][]
> 
> **▶ 细节**
> 
> 1.  MylSAM不支持事务、也不支持外键，但其访问速度快，对事务完整性没有要求
> 2.  InnoDB存储引擎提供了具有提交、 回滚和崩溃恢复能力的事务安全。但是比MyISAM存储引擎，InnoDB写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引。
> 3.  MEMORY存储引擎使用存在内存中的内容来创建表。 每个MEMORY表只实际对应个个磁盘文件。 MEMORY类型的表访问非常得快, 因为它的数据是放在内存中的,并且默认使用HASH索引。但是一旦MySQL服务关闭，表中的数据就会丢失掉，表的结构还在
> 
> **▶ 如何选择存储引擎**
> 
>  *  如果你的应用不需要事务, 处理的只是基本的CRUD操作，那么MyISAM是不二选择,速度快
>  *  如果需要支持事务，选择InnoDB。Memory 存储引擎就是将数据存储在内存中，由于没有磁盘I/0的等待。速度极快。 但由于是内存存储引擎，所做的任何修改在服务器重启后都将消失。(经典用法，用户的在线状态)
> 
> **▶ 修改存储引擎**
> 
> ![839ec6745c5f45dd947c46b5cdef5ec6.png][]
> 
> ### 6、视图 ###
> 
> **▶ 基本介绍**
> 
> 视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含列，其数据来自对应的真实表(基表)
> 
> ![56cdf8285dc94cafaee18dfb6db1d70c.png][]
> 
> **▶ 基本使用**
> 
>  *  create view视图名 as select语句
>  *  alter view视图名 as select语句 --更新成新的视图
>  *  SHOW CREATE VIEW 视图名
>  *  drop view 视图名1,视图名2
> 
> **▶ 细节**
> 
>  *  创建视图后, 到数据库去看, 对应视图只有一个视图结构文件(形式 : 视图名.frm)视图的数据变化会影响到基表，基表的数据变化也会影响到视图\[insert update delete\]
>  *  视图中可以再使用视图，数据仍然来自基表..
> 
> **▶ 视图最佳实践**
> 
> 1.  安全。一些数据表有着重要的信息。有些字段是保密的，不能让用户直接看到。这时就可以创建一个视图，在这张视图中只保留一部分字段。这样，用户就可以查自己需要的字段，不能查看保密的字段。
> 2.  性能。关系数据库的数据常常会分表存储，使用外键建立这些表的之间关系。这时，数据库查询通常会用到连接(UOIN)。这样做不但麻烦，效率相对也比较低。如果建立一个视图，将相关的表和字段组合在一越，就可以避免使用JOIN查询数据。
> 3.  灵活。如果系统中有一张旧的表，这张表由于设计的问题，即将被废弃。然而，很多应用都是基于这张表，不易修改。这时就可以建立一张视图，视图中的数据直接映射到新建的表。这样，就可以少做很多改动，也达到了升级数据表的目的。
> 
> ### 7、MySQL 管理 ###
> 
> **▶ MySQL 用户**
> 
>  *  host : 允许登录的“位置”localhost表示该用户只允许本机登录，也可以指定ip地址, 比如:192.168.1.100
>  *  user : 用户名
>  *  authentication string : 密码，是通过mysql的password0画数加密后的密码。
> 
> **▶ 创建用户**
> 
> **![3ae575446ff2440b890e7b28fd2e10a9.png][]**
> 
> **▶ 删除用户**
> 
> ![8d938e1f26d4428991a7f83e28c34f42.png][]
> 
> **▶ 用户修改密码**
> 
> ![1cbefc9ce2a04dbe94e887c294f9855f.png][]
> 
> **▶ MySQL 权限**
> 
> ![ab37ec2b4bda40748b9075a846808cdc.png][]
> 
> **▶ 给用户授权**
> 
> ![b4b70060b9c54797b240ef709a79f128.png][]
> 
> **▶ 回收用户权限**
> 
> ![b147ddc04ae04442b493058321441b13.png][]
> 
> **▶ 权限生效指令**
> 
> ![ed6f14ae4c6d45dfbba210a38ea019e7.png][]
> 
> **▶ 细节说明**
> 
>  *  在创建用户的时候, 如果不指定Host，则为%, %表示表示所有IP都有连接板create user Xxx；
>  *  你也可以这样指定 create user 'xxx'@'192.168.1.% 表示XXX用户在 192.168.1.\*的ip可以登录mysql
>  *  在删除用户的时候, 如果host 不是%，需要明确指定用户@'host值

[921e2c6bec914cdd8748995170c6ea2a.png]: https://img-blog.csdnimg.cn/921e2c6bec914cdd8748995170c6ea2a.png
[1cef1ac20a2345bf86acdaf58e0f6195.png]: https://img-blog.csdnimg.cn/1cef1ac20a2345bf86acdaf58e0f6195.png
[9f5e4bae211e40f0bd993f81694fcb44.png]: https://img-blog.csdnimg.cn/9f5e4bae211e40f0bd993f81694fcb44.png
[836c2d9641db44e7be86bfadf1fac605.png]: https://img-blog.csdnimg.cn/836c2d9641db44e7be86bfadf1fac605.png
[53677cce8cfd4b5ebc2620c1e95f06ae.png]: https://img-blog.csdnimg.cn/53677cce8cfd4b5ebc2620c1e95f06ae.png
[ee3af2e1b0e444f29cfe59b28d17c5dc.png]: https://img-blog.csdnimg.cn/ee3af2e1b0e444f29cfe59b28d17c5dc.png
[d1dded5d15ed416692e0bd069f69c2b8.png]: https://img-blog.csdnimg.cn/d1dded5d15ed416692e0bd069f69c2b8.png
[341aff79fe7a43d8a90be15136c8ae0e.png]: https://img-blog.csdnimg.cn/341aff79fe7a43d8a90be15136c8ae0e.png
[c6d55f010f734bf0b113da52ea83062c.png]: https://img-blog.csdnimg.cn/c6d55f010f734bf0b113da52ea83062c.png
[7f773157b1a440b5afc41808c95faa29.png]: https://img-blog.csdnimg.cn/7f773157b1a440b5afc41808c95faa29.png
[4215b54f6c824ae7bd7dde36d3847741.png]: https://img-blog.csdnimg.cn/4215b54f6c824ae7bd7dde36d3847741.png
[ef8f8e88b626469e8ac1bdd173defeca.png]: https://img-blog.csdnimg.cn/ef8f8e88b626469e8ac1bdd173defeca.png
[2d87f5cb999a4b13bb5df7a7eb545224.png]: https://img-blog.csdnimg.cn/2d87f5cb999a4b13bb5df7a7eb545224.png
[5aea8710ca2e4a4ca439a368cf598042.png]: https://img-blog.csdnimg.cn/5aea8710ca2e4a4ca439a368cf598042.png
[b521a24144b2417588c38c583ea6bc7b.png]: https://img-blog.csdnimg.cn/b521a24144b2417588c38c583ea6bc7b.png
[839ec6745c5f45dd947c46b5cdef5ec6.png]: https://img-blog.csdnimg.cn/839ec6745c5f45dd947c46b5cdef5ec6.png
[56cdf8285dc94cafaee18dfb6db1d70c.png]: https://img-blog.csdnimg.cn/56cdf8285dc94cafaee18dfb6db1d70c.png
[3ae575446ff2440b890e7b28fd2e10a9.png]: https://img-blog.csdnimg.cn/3ae575446ff2440b890e7b28fd2e10a9.png
[8d938e1f26d4428991a7f83e28c34f42.png]: https://img-blog.csdnimg.cn/8d938e1f26d4428991a7f83e28c34f42.png
[1cbefc9ce2a04dbe94e887c294f9855f.png]: https://img-blog.csdnimg.cn/1cbefc9ce2a04dbe94e887c294f9855f.png
[ab37ec2b4bda40748b9075a846808cdc.png]: https://img-blog.csdnimg.cn/ab37ec2b4bda40748b9075a846808cdc.png
[b4b70060b9c54797b240ef709a79f128.png]: https://img-blog.csdnimg.cn/b4b70060b9c54797b240ef709a79f128.png
[b147ddc04ae04442b493058321441b13.png]: https://img-blog.csdnimg.cn/b147ddc04ae04442b493058321441b13.png
[ed6f14ae4c6d45dfbba210a38ea019e7.png]: https://img-blog.csdnimg.cn/ed6f14ae4c6d45dfbba210a38ea019e7.png