文章详情

16 05月 2022

Mysql数据库基础知识

九、MYSQL约束

9.1 约束基本介绍

约束用于确保数据库数据满足特定的商业规则，在mysql中，约束包括not null（非空）、unique（唯一）、primary key（主键）、foreign key（外键）、check（检查）五种

9.1.1、主键使用方式：字段名类型 primary key

primary key主键基本作用：用于唯一的表示表行的数据，当定义主键约束后，该列不能重复

应用实例一：演示主键的使用

第一步：创建表

第二步：添加数据

primary key主键使用细节讨论：

1、primary key不能重复且值不能为空

2、一张表最多只能有一个主键，但可以是复合主键，多个字段合起来是一个主键

应用实例一：复合主键使用

错误：

创建复合主键

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

一是直接在字段名后面指定：字段名 primary key

二是在表定义的最后写primary key(列名,列名...)

4、可以使用desc 表名查看primary key的情况

5、在实际的开发中，每个表往往都会设计一个主键

9.1.2、not null（非空）使用方式：字段名类型 not null

开发中，如果在列上定义了not null，那么在插入数据时，必须为列提供数据

9.1.3、unique（唯一）使用方式：字段名字段类型 unique

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

应用实例一：演示unique的使用

unique 使用细节讨论：

1、如果没有指定主键的情况下也没有指定not null，则unique字段可以有多个null值

2、如果一个列(字段) 有unique not null 约束，使用效果类似primary key

3、一张表中可以有多个unique 字段

9.1.4、foreign key（外键）使用方式：foreign key （本表(从表、外键表)的字段名）references 主表名(主键名或unique字段名)

外键基本介绍：

外键用于定义主表和从表之间的关系，外键约束要定义在“从表”上，主表则必须具有“主键约束” 或是“unique“约束，当定义外键约束后，“要求外键列数据必须在主表的主键列存在或是为null“

应用实例一：演示外键使用

第一步：创建主表，字段有班级编号、班级名称

第二步：创建从表，字段有学生编号、学生名字、学生所在班级编号(外键)

第三步：添加测试数据

从表设置了外键，主表中没有id为300的数据，所以从表的这条添加的语句不会成功，insert into my_stu values(3,'螳螂',300)[添加失败]，但是外键字段的值如果允许为空 (没有设置not null 非空约束)，则可以添加成功

外键使用细节讨论：

1、外键指向的表的字段，要求是primary key 或者是unique

2、表的类型必须是innodb,这样的表才支持外键；mysql从5.5版本以后，默认引擎innodb；查看mysql版本使用命令status

3、外键字段的类型要和主键字段的类型一致（长度可以不一样）

4、外键字段的值，必须在主键字段中出现过，或者为null [前提是外键字段允许为null]

5、一旦建立外键关系，数据就不能随意删除了；如果要删除，除非把设置外键的从表中的对应ID的数据都删除，才能去删除主表中的该条ID记录值

9.1.5、check 使用方式：列名类型 check (check条件)

基本介绍：check用于强制行数据必须满足的条件，假定在之前的员工表的sal列上定义了check约束，并要求sal列值在1000~2000之间，如果不在1000~2000之间就会提示出错； oracle 和 sql server均支持check，但是mysql5.7目前还不支持check，只做语法校验，但不会生效

应用实例一：演示check使用

第一步：创建表

第二步：添加数据，一样是能添加到数据表中，所以，只做语法校验，但不会生效

9.2 自增长

在某张表中，存在一个id列(整数)，我们希望在添加记录的时候从1开始，自动增长。使用方式：字段名整型 primary key auto_increment

应用实例一：演示自增长的使用

第一步：创建表

第二步：添加数据

自增长使用细节讨论：

1、一般来说自增长是和peimary key配合使用的

2、自增长也可以单独使用 [需要配合一个unique]

3、自增长修饰的字段为整数类型的（虽然小数类型也可以，但是非常非常少这样用的）

4、自增长默认从1开始，也可以通过命令修改， alter table 表名 auto_increment = 10（具体整形值）

5、自如果你添加数据时，给自增长字段（列）指定有值，则以指定的值为准；以下图为例，以后再添加数据的时候，id就从667开始了

十、MYSQL索引

10.1 索引基本介绍

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

说索引之前先说一下mysql安装在硬盘文件目录下面data文件夹中的文件

1、表存储引擎是myisam，在data目录下面会看到3类文件：frm、myi、myd

*.frm-表定义，是描述表结构的文件

*.MYD-"D"数据信息文件，是表的数据文件

*.MYI-"I"索引信息文件，是表数据文件中的任何索引的数据树

2、表存储引擎是InnoDB，在data目录下面会看到2类文件：frm、ibd

*.frm-表定义，是描述表结构的文件

*.ibd-表数据和索引文件，该表的索引（B+树）的每个非叶子节点存储索引，叶子节点存储索引和索引对应的数据

3、另外db.opt文件

用来记录该库的默认字符集编码和字符集排序规则使用的。也就是说你创建数据库指定默认字符集和排序规则，那么后续创建的表如果没有指定字符集和排序规则，那么该新建的表将采用db.opt文件中指定的属性。

10.2 索引原理：

"没有使用索引" 的时候查询过程如下图：

全表扫描，从id最小查询到id最大，即使查询条件是id=1，它也会全表扫描，查询速度非常慢

二叉树简介：

1、二叉树的叶子节点就是没有子节点的节点，一棵树中没有子节点（即度为0），的节点称为叶子节点，简称“叶子”，又称为终端节点。

2、二叉树特点是每个节点最多只能有两颗子树，且有左右之分。

3、二叉树是n个有限元素的集合，该集合或者为空，或者由一个称为根的元素及两个不相交的、被分别称为左子树和右子树的二叉树组成，是有序树，当集合为空时，称该二叉树为空二叉树。在二叉树中，一个元素也称为一个节点。

建立索引的二叉树(数据结构)，在1-9中间找一个位于最大编号id和最小编号id的中间的数，假设是5，比5小的摆放位置在左边，比5大的摆放位置在右边，然后继续在1和5之间中间数，假设为2，然后比2小的继续摆放在2左边，比2大的摆放在2的右边，所有数据按照此方式分析下去

使用了索引的时候查询过程如下图：

第一步：where条件id为1，假设1--9二分，中间找到5，然后5右边的数据就不再查找了，如下图所示

第二步：where条件id为1，1比5小，再往左边再二分，假设1--5中间找到2，然后2右边的数据也不再查找，假设是一个800万的数据中查找，大概是23次左右就能找到，比顺序扫描快了不知道多少多少，如下图所示

二叉树五种基本形态：

图a：空二叉树

图b：只有一个根节点的二叉树

图c：只有左子树

图d：只有右子树

图e：完全二叉树

B树简介：

概念：

叶子节点：B树的叶子节点就是没有子节点的节点，一棵树中没有子节点（即度为0），的节点称为 "叶子节点"，简称“叶子”，又称为 "终端节点"，叶子节点的指针为空。

非叶子节点：B树的 "非叶子节点" 就是 "非终端节点"，即“树枝”。

基本定义：

1、根节点至少有两个子节点

2、叶子节点都在同一高度

3、如果非叶子节点有n个关键字，那么它有n+1个子节点，切这n个节点递增排列

B+树简介：

B+树是由B树演变而来的，所以它具有B树的所有特性，另外还具有如下两点

1、B+树的非叶子节点只有关键字，不存放数据

2、B+树的叶子节点之间用指针相连，是一个双向链表

案例一：

第一、

第二、

第三、

第四、

总结：

不得不提的时间单位换算：

1s = 1000ms（10的3次方毫秒）= 1000000us（10的6次方微秒）= 1000000000ns（10的9次方纳秒） = 10的12次方皮秒(ps)

假设3Ghz的CPU主频，再由CPU分配资源，诸多进程等待，获取CPU调度来处理MYSQL进程，假设分得的资源的一次处理周为10us，800万数据23次查询，耗时230us/0.23ms，再经过网络传输或者计算偏差等，建立索引后查询800万数据的耗时预估也就在几百微妙到几十毫秒之间

不加索引速度为什么那么慢：全表扫描查询

加索引为什么速度会很快：形成一个索引的数据结构，比如二叉树/B树/B+树

加索引代价：

1、创建索引后，文件变大，磁盘变大

2、对dml(update、delete、insert)语句的效率影响，删除/修改/添加一个数据，可能导致存索引的二叉树/B+树的结构发生变化，需要重对索引进行维护，对速度有影响在我们实际的项目中，大多数项目select的时候比update、delete、insert多得多；假设一个UP主有一千万粉丝，一天发10篇文章，“理论情况下不使用其他扩展插件”，全都浏览了文章， insert才10次，而select就是1亿次

10.3、创建索引

创建索引后，只对创建了索引的列（字段）有效

10.3.1、添加索引的两种方式

10.3.2、删除索引 drop index 索引名 on 表名

删除主键索引

10.3.3、修改索引，操作是先删除再添加

10.3.4、查询索引

1、show index from t25

2、show indexes from t25

3、show keys from t25

4、desc 表名

Non_unique：值为0代表唯一索引，值为1代表非唯一索引

10.3.5、索引的类型

1、主键索引：主键自动的为主索引（为字段添加的primary key）

2、唯一索引：唯一索引（unique）

3、普通索引：普通索引（index）

4、全文索引：全文索引（fulltext），适用于MyISAM表引擎。假如我们要在一篇文章中搜索一个人的名字，希望搜索得很快，就可以在文章的字段上建一个全文索引，在实际的开发中，一般不使用mysql自带的全文索引，而考虑使用的全文索引框架 Solr和Elasticsearch

10.3.6、索引总结

哪些列上适合创建索引呢

1、较频繁作为查询条件字段应该创建索引

2、唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件，下面的性别字段不适合创建索引

3、更新非常频繁的字段不适合创建索引，因为会频繁的去更新索引结构

4、不会出现在where字句中字段不该创建索引

mysql索引失效的情况有哪哪些？？？

① 使用了左模糊查询，“%xxx”，或者是"%x%"的查询

因为B+树是根据索引值进行排列的，前缀不确定的时候可能是，“小张”，"二张"之类的所有的情况，就只能通过全表扫描的方式来查询

② 对索引使用了函数的情况

因为索引保存的是索引字段的原始值，而不是经过函数计算后的值，所以使用函数的时候就不会走索引了

③ where子句中的or

因为 OR 的含义就是两个只要满足一个即可，因此只有一个条件列进行了索引是没有意义的，只要有条件列没有进行索引，就会进行全表扫描，因此索引的条件列也会失效

④ 对索引字段使用了表达式

select * from sys_user where user_id+10 =50;

在 where 中条件使用了条件表达式的时候，数据表中的索引就失效了，实际是因为 Mysql 需要将索引字段取出来之后再进行表达式的条件判断，因而进行了全表扫描，导致索引失效

如何避免mysql索引失效？？？

① 全部采取值匹配，对查询语句中的列都指定索引

② 如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始，并且不跳过索引中的列。

③ 避免在索引上进行运算，索引将失效

④ 避免字符串查询不加单引号，索引失效

⑤ 避免select * 的写法

⑥ 避免以“%”开头的模糊查询，“%”开头的模糊查询将失效，尽量采用尾部模糊查询

⑦ in会走索引，not in 索引将失效

⑧ 在合适的业务场景使用or查询

十一、MYSQL事务

11.1 事务基本介绍

事务用于保证数据的一致性，它由相关的dml语句组成，该组dml语句要么全部成功，要么全部失败。如转账就需要用事务来处理，用以保证数据的一致性。

11.2 事务和锁

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

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

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

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

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

4、rollback --回退全部事务

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

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

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

案例演示一、

开启事务：

设置保存点：

执行dml操作：

再设置一个保存点：

回退到保存点i：

回退到保存点a：中间的其他保存点会删除！！！

直接回退到事务开始的状态：

提交事务，所有的操作生效，不能再回退，保存点删除，commit以后没有后悔药...

11.4 mysql事务细节讨论

1、如果不开启事务，默认情况下，dml操作时自动提交的，不能回滚

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

3、在事务还没有提交的时候，你可以在事务中创建多个保存点，比如：savepoint aaa ；执行dml语句； savepoint bbb;

4、可以在事务还没提交前，选择回到哪个保存点

5、使用mysql事物机制需要innodb存储引擎才可以使用，myisam不行。

6、开始一个事物可以使用start transation，也可以使用set autocommit = off。

11.5 mysql事务隔离级别

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

基本介绍：

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

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

脏读：一个事务读取另一个事务尚未提交的改变时（update、insert、delete），产生脏读

不可重复读：同一查询在同一事务中多次进行，由于其他提交事务所做的“修改”或“删除”，每次返回不同的结果集，此时发生不可重复读

幻读：同一查询在同一事务中多次进行，由于其他提交事务所做的“插入”操作，每次返回不同的结果集，此时发生幻读

应用实例一、演示mysql事务隔离级别，read uncommitted(读未提交) 结果出现脏读、不可重复读、幻读

第一步、查询当前默认隔离级别：select @@tx_isolation，默认可重复读

第二步、把其中一个控制台的隔离级别设置为read uncommitted（读未提交）

语法：set session transaction isolation level read uncommitted

第三步、脏读

控制台1

控制台2

第四步、不可重复读

控制台1修改了id=1的数据余额为800

控制台2读取到了另一事务提交的改变（修改、删除）；事务1的提交影响了事务2对表读的操作

第五步、幻读

控制台1提交了新添加的数据

控制台2读取到了另一事务提交的改变（插入）；事务1的提交影响了事务2对表读的操作

应用实例二、演示mysql事务隔离级别，read committed(读已提交) 结果出现不可重复读、幻读，不会出现脏读

第一步、控制台1开启事务

第二步、控制台2修改事务隔离级别为read committed（读已提交），并且开启事务

语法：set session transaction isolation level read committed

第三步、脏读

控制台1添加了数据，并读取数据如下

控制台2读取数据，并没有读取到刚才控制台1事务添加的数据

第四步、不可重复读

控制台1修改了数据，并且提交

控制台2读取数据，又读取到了控制台1事务做的修改，出现不可重复读

第五步、幻读

控制台1添加了id为5的数据，并提交

控制台2查询到了控制台1事务的提交

应用实例三、演示mysql事务隔离级别，repeatable read(可重复读) 不会出现脏读、不可重复读、幻读

第一步、控制台1开启事务

第二步、控制台2修改事务隔离级别为repeatable read（可重复读），并且开启事务

语法：set session transaction isolation level repeatable read

第三步、脏读、不可重复读、幻读

控制台1添加、修改了数据，并提交数据

控制台2读取数据，依然没有变化

应用实例四、演示mysql事务隔离级别，Serializable(可串行化)不会出现脏读、不可重复读、幻读

第一步、控制台1开启事务

第二步、控制台2修改事务隔离级别为Serializable（可串行化），并且开启事务

语法：set session transaction isolation level Serializable

第三步、控制台1添加一个语句，修改一个语句

控制台1并没有提交事务，控制台2查询，卡在那里等待，超时

控制台1事务提交

控制台2查询

11.6 mysql设置隔离

1、查看当前会话隔离级别 select @@tx_isolation

2、查看系统当前隔离级别 select @@global.tx_isolation

3、设置当前会话隔离级别 set session transaction isolation level （隔离级别）

4、设置系统当前隔离级别 set session transaction isolation level （隔离级别）

5、mysql默认的事务隔离级别是repeatable read，一般情况下没有特殊要求，没有必要修改（因为该隔离级别满足绝大部分项目需求）

6、全局修改，设置mysql隔离级别，修改my.ini配置文件

11.7 mysql事务ACID

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

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

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

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

11.8 mysql表类型和存储引擎

基本介绍：

1、mysql的表类型由存储引擎决定，主要包括myisam、innodb、memory等...

2、mysql数据表主要支持6种类型，分别是：CVS、Memory、ARCHIVE、MRG MYISAM、MYISAM、InnoDB

3、这6种又分为两类，一类是“事务安全型”，比如：InnoDB；其余都属于第二类，称为“事务非安全型”

查看所有的存储引擎：show engines

主要的存储引擎及其特点

几种常用表类型的区别：

innodb存储引擎：支持事务、支持外键、支持行级锁、写入速度慢一些

myisam存储引擎：不支持事务和外键、写入速度快、支持表级锁

memory存储引擎：数据存储在内存中、执行速度很快（没有磁盘IO读写）、默认支持索引（哈希表）、重启mysql服务或者操作系统以后，数据消失，但是表结构还在

修改表存储引擎：alter table 表明 engine= innodb/myisam

11.9 mysql视图

视图基本概念：

1、视图是一个虚拟表，其内容由查询定义，同真实的表一样，视图包含列，其数据来自对应的真实表（基表）

2、视图和基表关系示意图

对视图的总结：

视图的基本使用：

1、创建视图：create view 视图名 as select语句

2、查看视图结构：desc 视图名

3、查询视图数据：select 字段 from 视图名

3、alter view 视图名 as select语句修改一个视图，其实相当于重新定义一个视图了

4、查看创建视图的指令： show create view 视图名

5、drop view 视图1 视图2

视图细节讨论：

1、创建视图后，到数据库去看，对应视图只有一个视图结构文件

2、视图的数据变化会影响基表，基表的数据变化也会影响到视图（insert、update、delete）

视图的最佳实践：

十二、MYSQL管理

12.1 mysql用户管理

当我们在项目开发时，可以根据不同的开发人员，赋给他相应的mysql操作权限！！！

mysql中的用户都存在系统数据库的mysql中的user表

其中user表重要字段说明：

1、host字段：允许登录的“位置”，localhost表示该用户只允许本机登录，也可以指定IP登录

2、user字段：用户名

3、authentication_string字段：密码，是通过mysql的password()函数加密之后的密码

创建用户

create user '用户名' @ '允许登录的位置' identified by '密码' 创建用户的同时指定密码

密码是password()函数加密的

删除用户

drop user '用户名' @ ‘允许登录的位置’

其他用户登录

普通用户登录修改自己的密码：set password = password('112235')

普通用户修改其他用户的密码：set password for '用户' @ '允许登录位置' = password('密码')

这里，访问mysql数据库权限不够，被拒绝

使用root用户来修改其他用户密码

12.2 mysql权限管理

权限列表

1、使用root账户给其他账户赋权限

如果被赋值权限的用户存在，可选里面设置了密码则表示修改密码，如果不存在就是创建用户并赋权限

2、回收用户授权

revoke 权限列表 on 库.对象名 from '用户名'@'登录位置'；

3、权限刷新指令

FLUSH PRIVILEGES;

应用实例一、用户管理练习

第一步、使用root用户创建用户

第二步、使用root用户创建testdb库和news表

第三步、使用root用户给普通用户分配权限

第四步、测试用户权限

第五步、使用root用户修改密码为abc

第六步、重新登录

第七步、回收权限

已经没有了删除权限，删除失败

mysql管理细节说明：

1、在创建用户的时候，如果不指定Host，则为%，%表示所有IP都有链接权限

2、也可以指定IP创建： create user '用户名'@'IP'

3、在删除用户的时候，如果host不是%，需要明确指定 '用户'@'host值'

host不是%的情况