第 1 章

目录大纲

DDL数据库定义语言

DML数据库操作语言

DQL数据库查询语言

单表查询案例

DCI数据库控制语言

多表设计

多表查询

多表查询案例

索引

索引优化

索引创建的原则

事务

MVCC多版本并发控制

Mysql主从同步原理

第 2 章

DDL数据库定义语言

DDL英文全称是Data Definition Language(数据定义语言),用来定义数据库对象(数据库、表)。

2.1、数据库的定义

DDL中数据库的常见操作有:创建、查询、使用、删除。

刷新权限表的命令_mysql刷新权限_刷新权限的命令

2.2、数据类型

=>1、数值类型

signed :有符号,取值可以是负数;

unsigned:无符号,取值只能是正数。

=>2、字符类型

mysql刷新权限_刷新权限表的命令_刷新权限的命令

1、char : 定长字符串

存储性能高但浪费空间。

eg :phone char(11) 如果存储的数据字符个数不足11个,也会占11个空间。

2、varchar : 变长字符串。

存储性低,但能节省空间。

eg : name varchar(20) 如果存储的数据字符串个数不足20个,那就是数据占多少个就是多少个。

=>3、日期时间类型

刷新权限表的命令_刷新权限的命令_mysql刷新权限

2.3、约束

约束是作用于表中字段上的,可以在创建表/修改表的时候添加约束,用于限制存储在表中的数据,保证数据的正确性、有限性和完整性。

Mysql的约束有:主键约束、外键约束、唯一约束、非空约束、默认约束。

mysql刷新权限_刷新权限表的命令_刷新权限的命令

2.4、创建表的案例

根据页面原型图设计表

1、页面原型图

刷新权限表的命令_刷新权限的命令_mysql刷新权限

2、字段限制说明

刷新权限表的命令_刷新权限的命令_mysql刷新权限

设计一张表,基本的流程如下:

阅读页面原型及需求文档。

基于页面原型和需求文档,确定原型字段(类型、长度限制、约束)。

增加表设计所需要的业务基础字段(id主键、插入时间、修改时间)。

区间长度限制一般是前端或者或者后端程序进行校验,数据库存储一般存储区间最大值。

刷新权限表的命令_mysql刷新权限_刷新权限的命令

刷新权限表的命令_mysql刷新权限_刷新权限的命令

2.5、修改表结构

刷新权限表的命令_mysql刷新权限_刷新权限的命令

第 3 章

DML数据库操作语言

DML英文全称是Data Manipulation Language(数据操作语言),用来对数据库中表的数据记录进行增、删、改操作。

3.1、增加(insert)

刷新权限表的命令_mysql刷新权限_刷新权限的命令

3.2、修改(update)

mysql刷新权限_刷新权限的命令_刷新权限表的命令

3.2、删除(delete)

刷新权限的命令_mysql刷新权限_刷新权限表的命令

补充:

drop是删除整个表;truncate是删除整个表中的数据,保留表结构,删数据除后自增长字段恢复从1开始;delete清空表中的数据,自增长字段不会恢复从1开始。

第 4 章

DQL数据库查询语言

DQL英文全称是Data Query Language(数据查询语言),用来查询数据库表中的记录。

刷新权限的命令_刷新权限表的命令_mysql刷新权限

4.1、select基本查询

刷新权限的命令_刷新权限表的命令_mysql刷新权限

公司开发规范,在业务层代码中是不允许使用*字段通配符,因为不方便阅读,会导致回表查询,而且后面可能数据库表字段会扩展,会导致代码出现bug

4.2、where条件查询

=>1、比较运算符

刷新权限表的命令_刷新权限的命令_mysql刷新权限

=>3、逻辑运算符

mysql刷新权限_刷新权限表的命令_刷新权限的命令

例题:

刷新权限的命令_mysql刷新权限_刷新权限表的命令

4.3、聚合函数

基本查询和条件查询都是横向查询,就是根据条件一行一行的进行判断,而使用聚合函数查询就是纵向查询,它是对一列的值进行计算,然后返回一个结果值。

刷新权限的命令_mysql刷新权限_刷新权限表的命令

mysql刷新权限_刷新权限表的命令_刷新权限的命令

4.4、分组查询

分组查询通常会使用聚合函数进行计算。分组其实就是按列进行分类(指定列下相同的数据归为一类),然后可以对分类完的数据进行合并计算,把相同的数据进行合并输出。

mysql刷新权限_刷新权限的命令_刷新权限表的命令

注意:如果是进行多表联查时,得根据需求的分组字段来确定填写count函数里面的值,不能用*代表全部。

where与having区别:

1、执行时机不同:where是分组之前进行过滤,不满足where条件,不参与分组;而having是分组之后对结果进行过滤。

2、判断条件不同:where不能对聚合函数进行判断,而having可以。

3、查询的性能效率不同:

(1)、where

where是在数据库引擎读取数据前进行筛选的mysql刷新权限,可以根据各种条件进行筛选,例如:等于、大于、小于、不等于、范围、匹配等,筛选后的数据才会进入到Mysql的查询缓存中,从而提高查询效率。

(2)、having

having是在MySQL中对查询结果进行筛选的,一般是对分组后的结果进行筛选,例如:按年龄分组,筛选年龄大于30岁的人数,这个操作是在Mysql服务器中进行的,而不是在数据库引擎中进行的。因此,having的效率比where要低。

综上所述,如果只是简单的筛选数据,应该优先使用where,而如果需要对分组后的结果进行筛选,才使用having。在存储性能方面,where的效率更高,因为它是在数据库引擎读取数据前进行筛选的,而having的效率则较低。

4.5、排序查询

mysql刷新权限_刷新权限表的命令_刷新权限的命令

4.6、分页查询

分页操作在业务系统开发时,也是非常常见的一个功能,日常我们在网站中看到的各种各样的分页条,后台也都需要借助于数据库的分页操作。

刷新权限表的命令_刷新权限的命令_mysql刷新权限

刷新权限表的命令_刷新权限的命令_mysql刷新权限

第 5 章

单表查询案例

5.1、案例1:根据需求完成员工管理的条件分页查询

刷新权限的命令_mysql刷新权限_刷新权限表的命令

需求分析:根据输入的条件,查询第1页数据。

1. 从原型图可以分析出了内容:在员工管理的列表上方有一些查询条件:员工姓名(需要支持进行模糊匹配)、员工性别,员工入职时间(开始时间~结束时间)。

2. 除了上方查询条件外,在列表的下面还有一个分页条,这就涉及到了分页查询,查询第1页数据(每页显示10条数据)

3. 通过观察页面查询的结果可知,是先按照入职时间进行排序,入职时间相同则按照修改时间进行降序排序。

结论:条件查询+ 排序查询+分页查询

5.2、案例2:根据需求完成员工信息的统计

刷新权限的命令_刷新权限表的命令_mysql刷新权限

分析:以上信息统计在开发中也叫图形报表(将统计好的数据以可视化的形式展示出来)。

看到统计这两个字就要知道这个需求要用到聚合函数。

刷新权限表的命令_mysql刷新权限_刷新权限的命令

刷新权限的命令_刷新权限表的命令_mysql刷新权限

第 6 章

DCL数据库控制语言

DCL英文全称是Data Control Language(数据控制语言)指的是用于控制数据库访问权限和安全性的SQL命令集合。

1. grant:用于为用户或用户组授予数据库对象(如表、视图、存储过程等)的访问权限。

2. revoke:用于撤销用户或用户组对数据库对象的访问权限。

3. create user:用于创建一个新的数据库用户。

4. drop user:用于删除一个数据库用户。

5. alter user:用于更改一个数据库用户的属性,如密码、访问权限等。

6. create role:用于创建一个数据库角色。

7. drop role:用于删除一个数据库角色。

8. grant role:用于向用户或用户组授予一个或多个角色。

9. revoke role:用于从用户或用户组中撤销一个或多个角色。

10. set role:用于在当前会话中激活一个或多个角色。

这些命令可以帮助管理员更好地控制数据库的安全性和访问权限,以保护敏感数据不被未授权的用户访问。

6.1、创建数据库用户

1. 首先需要使用root用户登录到mysql数据库。

2. 创建一个新用户,命令为:create user ‘username’@’localhost’ identified by ‘password’; 其中,username为你要创建的用户名,password为你要设置的密码。如果你希望这个用户可以从任何地方登录,可以将localhost改为%。

3. 授予新用户权限,命令为:grant all privileges on *.* to ‘username’@’localhost’; 其中,username为你创建的用户名,*.*表示授权所有数据库和表的权限。如果你只想授权特定的数据库和表,可以将*.*改为你要授权的数据库和表名。

4. 刷新权限,命令为:flush privileges; 这样新用户就可以登录并访问指定的数据库和表了。

第 7 章

多表设计

项目开发中,在进行数据库表结构设计时,有的业务模块之间的是有联系的,所以我们设计表的时候也要建立联系。常见的联系三种:

1、一对多(多对一)。

2、多对多。

3、一对一。

7.1、一对多关系表设计

需求:根据页面原型及需求文档 ,完成部门及员工的表结构设计。

部门表原型图:

mysql刷新权限_刷新权限的命令_刷新权限表的命令

员工表原型图:

刷新权限的命令_刷新权限表的命令_mysql刷新权限

逻辑分析:一个部门可以拥有多个员工,一个员工只能归属一个部门。因此部门和员工的关系是一对多的关系。

=>部门表

刷新权限的命令_刷新权限表的命令_mysql刷新权限

=>员工表

mysql刷新权限_刷新权限表的命令_刷新权限的命令

刷新权限的命令_刷新权限表的命令_mysql刷新权限

一对多关系实现:在数据库表中多的一方【子表】,添加字段,来关联属于一这一方【父表】的主键。

7.2、外键约束

上面建立的部门表和员工表没有使用外键进行约束,修改和删除数据之后就会导致数据不完整。

因此可以通过使用foreign key在员工表定义外键关联到部门表。

方式1:建表时指定外键:

刷新权限的命令_刷新权限表的命令_mysql刷新权限

方式2:通过图形化界面设置外键。

mysql刷新权限_刷新权限的命令_刷新权限表的命令

7.3、物理外键和逻辑外键

在现在的企业开发中,很少会使用物理外键,都是使用逻辑外键。比如阿里的Java开发手册中明确指出“不得使用外键与级联,一切外键概念必须在应用层解决”。

mysql刷新权限_刷新权限表的命令_刷新权限的命令

刷新权限表的命令_刷新权限的命令_mysql刷新权限

7.4、一对一表设计

一对一关系表在实际开发中应用起来比较简单,通常是用来做单表的拆分,也就是将一张大表拆分成两张小表,将大表中的一些基础字段放在一张表当中,将其他的字段放在另外一张表当中,以此来提高数据的操作效率。

例如在业务系统当中,对用户的基本信息查询频率特别的高,但是对于用户的身份信息查询频率很低,此时出于提高查询效率的考虑,就可以将这张大表拆分成两张小表,第一张表存放的是用户的基本信息,而第二张表存放的就是用户的身份信息。他们两者之间一对一的关系,一个用户只能对应一个身份证,而一个身份证也只能关联一个用户。

刷新权限的命令_刷新权限表的命令_mysql刷新权限

7.3、多对多表设计

多对多的关系在开发中属于也比较常见的。比如:学生和老师的关系,一个学生可以有多个授课老师,一个授课老师也可以有多个学生。在比如:学生和课程的关系,一个学生可以选修多门课程,一个课程也可以供多个学生选修。

案例:学生与课程的关系:

关系:一个学生可以选修多门课程,一门课程也可以供多个学生选择

实现关系:建立第三张中间表,中间表至少包含两个外键,分别关联两方主键。

刷新权限的命令_mysql刷新权限_刷新权限表的命令

第 8 章

多表查询

8.1、多表查询分类

多表查询就是查询时从多张表中获取所需数据。

要执行多表查询,只需要在单表查询基础上使用逗号分隔多张表即可。

mysql刷新权限_刷新权限表的命令_刷新权限的命令

1、内连接:相当于查询A、B交集部分数据。

2、外连接

(1)、左外连接:查询左表所有数据(另外加上两张表交集部分数据,左边中的数据,在右表没有以NULL值进行填充)。

(2)、右外连接:查询右表所有数据(另外加上两张表交集部分数据)。

(3)、注意事项:左外连接和右外连接是可以相互替换的,只需要调整连接查询时SQL语句中表的先后顺序就可以了。而我们在日常开发使用时,更偏向于左外连接。

3、子查询

8.2、内连接

刷新权限表的命令_刷新权限的命令_mysql刷新权限

8.3、外连接

刷新权限的命令_mysql刷新权限_刷新权限表的命令

8.4、子查询

1、根据子查询结果的不同分为:

[1]标量子查询(子查询结果为单个值【一行一列】)。

[2]列子查询(子查询结果为一列,但可以是多行)。

[3]行子查询(子查询结果为一行,但可以是多列)。

[4]表子查询(子查询结果为多行多列【相当于子查询结果是一张表】)。

8.4.1、标量子查询

子查询返回的结果是单个值(数字、字符串、日期等),最简单的形式,这种子查询称为标量子查询。

常用的操作符:= > >= 范围查询,右边的列索引会失效。

第3种情况是===>不要要在索引列上进行运算操作,不然会导致索引失效。

第4种情况是===>字符串查询,没有使用单引号包裹,也会导致索引失效。主要原因是字符串不加单引号,在查询的时候,Mysql的查询优化器会自动进行类型转换,只要是在索引上发生了任何的类型转换,都会造成索引的失效。

第5种情况是===>使用like模糊查询,会导致索引失效。

通常要判断出某条SQL 是否失效了,可以通过explain执行计划来分析。

12.3、谈谈你对sql优化的理解

写sql的前我会先通过可视化工具查看表结构,

1.如果表中使用了索引,要避免索引失效的写法。比如说使用了联合索引;要遵守最左前缀法则、进行范围查询时,有索引的等值判断要放在范围判断的左边,字符串查询一定要加上引号,使用like模糊查询,避免%号写在左边。

2.select * 查询要指明字段名称,尽可能避免回表查询,也为了防止后续数据库扩展字段时,这里出现bug。

3.使用合并查询时,尽量使用union all代替union,减少过滤次数

4.在进行join连接查询时,能使用inner join就不要使用left join和right join,必须用使用left join和right join的话,应该以小表驱动大表,减少数据库的IO操作次数。

比如注册的时候判断某个用户名是否存在,使用

select name from stu where name = ‘张三’ limit 0,1;

指明返回值字段,限制只查询一个,查询到一个值就不往下查询了。

4、使用主从读写分离的架构,master主库负责写操作,slave从库负责读操作,避免写操作影响读操作。

我们的生产环境都搭建主库和从库来去分开读操作和写操作,但是只运维他们去负责搭建的。

5、分库分表

第五个是分库分表,一般是在数据量特别大的时候,比如说一张表的数据超过了 500 万,那这时候我们就可以考虑分库分表了。

第 13 章

事务

13.1、事务简介

事务 是一组操作的集合,它是一个不可分割的工作单位,事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求,即这些操作要么同时成功,要么同时失败。

比如: 张三给李四转账1000块钱,张三银行账户的钱减少1000块钱,而李四银行账户的钱要增加1000块钱。这一组操作就必须在一个事务的范围内,要么都成功,要么都失败。

13.2、事务操作

刷新权限的命令_mysql刷新权限_刷新权限表的命令

使用事务控制转账案例的操作:

mysql刷新权限_刷新权限表的命令_刷新权限的命令

13.2、事务的四大特性

事务是一组操作的组合,它是一个不可分割的工作单位,事务会把所有的操作作为一个整体,一起向系统提交或撤销操作请求。那简单来说就是这些操作要么同时成功,要么同时失败。

比如: 张三给李四转账1000块钱,张三银行账户的钱减少1000块钱,而李四银行账户的钱要增加1000块钱。这一组操作就必须在一个事务的范围内,要么都成功,要么都失败。

1、原子性(Atomicity):事务是不可分割的最小操作单元,要么全部成功,要么全部失败。

2、一致性(Consistency):一个事务完成之后数据都必须处于一致性状态。如果事务成功的完成,那么数据库的所有变化将生效。如果事务执行出现错误,那么数据库的所有变化将会被回滚(撤销),返回到原始状态。

比如说上面转账的业务,A+B=2000,不管最终转账是否成功,A+B的总额也要等于2000。

3、隔离性(Isolation):数据库系统提供的隔离机制,保证事务在不受外部并发操作影响的独立环境下运行。

4、持久性(Durability):事务一旦提交或回滚,它对数据库中的数据的改变就是永久的。哪怕数据库发生异常,重启之后数据亦然存在。

上述就是事务的四大特性,简称ACID。

下面我用一个转账的案例介绍数据库事务的4大特性 :

第1个原子性:比如 a 向 b 去转了 1000 块钱,那转账成功之后,那 a 扣除1000, b 要增加1000,原子性就体现在要么都成功,要么都失败。

第二个在转账的过程中数据要一致,那 a 扣了1000, b 必须增加1000,原始a+b=1000,事务完成之后a+b=1000,中途不能打折扣。

第三个在转账的过程中,隔离性就体现在 a 向 b 转账不能受其他事务的干扰。

第四个持久性体现在事务提交之后,要把数据持久化保存在磁盘之中。

13.3、并发事务问题

1、赃读:一个事务读到另外一个事务还没有提交的数据。

2、不可重复读:一个事务先后读取同一条记录,但两次读取的数据不同,称之为不可重复读。

3、幻读:一个事务按照条件查询数据时,没有对应的数据行,但是在插入数据时,又发现这行数据 已经存在,好像出现了 “幻影”。

13.4、事务隔离级别

解决并发事务问题就是使用Mysql的隔离级别,一共有4个隔离级别,分别是读未提交、读已提交、可重复读、串行化。

读未提交,不能解决任何问题,

读已提交,可以解决脏读,但是不能解决可不重复读、幻读

可重复读可以解决脏读、不可重复读,但是不能解决幻读

串行化可以解决任何问题,但是性能会比较差,因为串行化的意思就是一个事务提交之后mysql刷新权限,其他的事务才能接着运行。

Mysql默认的隔离级别=====>可重复读

mysql刷新权限_刷新权限表的命令_刷新权限的命令

注意:事务隔离级别越高,数据越安全,但是性能越低。

13.5、redo.log和undo.log的区别

redo.log,它记录的是数据页的物理变化,当增删改操作时会先将修改的数据存储到redo.log磁盘文件中,当Mysql宕机时,通过读取redo.log文件中的数据同步到磁盘中,解决内存中数据丢失的问题。

undo.log,记录的是逻辑日志(记录增删改相反的语句),当事务回滚时,可以通过逆操作恢复到原来的数据,

其中 redo.log 可以保证事务的持久性,undo.log 是保障事务的原子性和一致性。

mysql服务器有两个组成部分=====>内存和磁盘

那当我们去操作数据的时候,比如说update、delete、insert,那这些它并不会直接去操作磁盘,它首先会去操作内存。如果内存中没有的话,它就会从磁盘中把数据加载到内存中。操作完成之后,它会按照一定的频率再把数据同步到磁盘中,那这时候就能够去减少磁盘的IO,加快这个处理的速度。

=====>但是在同步时,如果Mysql服务器宕机了,会导致内存中数据丢失的问题。所以 MySQL 中引入redo.log和undo.log来解决数据同步和保障事务的原子性和一致性。

第 14章

MVCC多版本并发控制

14.1、MVCC介绍

MVCC的实现要依赖于三个内容,第一个是隐藏字段,第二个是 undo log 日志,第三个是 readView。

1.其中隐藏字段里边有两个内容,第一个是叫 transaction id, 就是事务id,当前每一个表中都有这么一个隐藏字段,就是记住每一次操作的事务ID,它是一个自增的值。第二个是叫 root pointer,回滚指针,它指向的是上一个事务版本的地址。

2.第二个叫 undo.log,它第一个作用就是回滚日志。第二个是存储老版本的数据,里边有一个版本链,当多个事务并行操作某一行记录的时候,它记录的是不同事务修改数据的版本,通过这个 root pointer 就是回滚指针形成一个链表,那链表的头部就是最新修改的旧记录,那链表的尾部是最早修改的旧记录。

3.第三个叫 readView,它解决的是一个事务查询选择版本的问题,首先这里边儿它会定义了一些匹配规则,它会判断当前的查询到底应该访问历史版本的哪一条数据?

当然不同的隔离级别,它们最终生成的结果是不一样的。

RC(Read Committed===>读已提交),它每一次执行快照读时生成一个 readView,就是每一次查询都会去生成一个 readview。

RR(Repeatable Read===>可重复读),它是仅在第一次执行查询的时候,它就会生成一个 review,后期都去复用这个 readview。

14.2、事务的隔离级别是如何保证的?

隔离性是由两个功能完成的,一个是锁,一个是MVCC。

第一个是排他锁,它的含义就是如果一个事务获取了一个数据行的排他锁,那么其他事务就不能再获取该行的其他锁了。我们平时使用的insert、update、delete 都会去自动添加这个排他锁,就是为了防止其他事务去修改这行数据。

第二个是MVCC,它是 MySQL 中多版本的并发控制,它维护了数据的多个版本,并且使得数据读写操作没有冲突。

14.3、当前读和快照读

当前读

当前读是读取最新版本,阻塞其他操作修改记录。比如select… update,update,delete,insert这类操作都是当前读。

快照读

只有select单纯的读取操作,会在读取的时候生成ReadView快照数据。

并且RC(都已提交)隔离级别下,会在每次select操作的时候都生成一个ReadView快照。

而在RR(可重复度)隔离级别下,同一个事务中只会在第一次select操作的时候生成ReadView快照,避免同一个事务多次读取的数据不一样。

14.4、MVCC过程会加锁吗?

MVCC叫做多版本控制,他是确保在高并发的情况下,多个事务读取数据的时候不加锁,也可以多次读取相同的值,MVCC在可重复读的事务隔离级别下,可以解决脏读、脏写,不可重复读等问题。MVCC是基于乐观锁来实现的。

在MVCC中通常不需要加锁来控制并发的访问,每个事务都可以读取到已提交的快照,而不需要获取共享锁或者排他锁。

在写操作的时候MVCC会使用一种“写时复制”,copywrite副本的技术,就是写修改数据之前先将数据复制一份,从而创建一个新的快照。当一个事务需要修改数据的时候,MVCC会先检查修改数据的快照版本号,是否与该事务的快照版本号一致,如果一致就表示可以修改这条数据,否则这条数据需要等待其他事务完成对该数据的修改。另外这个数据在快照之上修改结果,不会影响原始数据,其他事务可以继续读取原始数据的快照,从而解决脏读和不可重复读的问题。所以正是有了MVCC机制,让多个事务对同一个数据进行读写的时候,不需要加锁也不会出现读写冲突的问题。

第 15章

Mysql主从同步原理

主从同步是一个是主库,一个是从库,那主库负责写数据,从库负责读数据,当主库写数据的时候,这时候就要把数据同步到从库中。

Mysql的主从同步核心就是一个二进制文件bin.log,他的作用就是去记录DDL和DML的一些操作,这里不包含查询,有了这个 bin.log 之后,在做数据同步的时候,那就方便多了。

首先主库把变化的数据写入到 bin.log 中,从库读取主库的bin.log,把它写入到从库的中继日志,然后从库再从中继日志中读取数据,写入到自己的数据库中,那这样数据就保持同步了。

· 往期回顾·

———END———
限 时 特 惠: 本站每日持续更新海量各大内部创业教程,一年会员只需98元,全站资源免费下载 点击查看详情
站 长 微 信: xdbaoku