mysql刷新权限-Mysql数据库系统学习笔记

第 1 章

目录大纲

DDL数据库定义语言

DML数据库操作语言

DQL数据库查询语言

单表查询案例

DCI数据库控制语言

多表设计

多表查询

多表查询案例

索引

索引优化

索引创建的原则

事务

MVCC多版本并发控制

Mysql主从同步原理

第 2 章

DDL数据库定义语言

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

2.1、数据库的定义

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

2.2、数据类型

=>1、数值类型

signed ：有符号，取值可以是负数；

unsigned：无符号，取值只能是正数。

=>2、字符类型

1、char : 定长字符串

存储性能高但浪费空间。

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

2、varchar : 变长字符串。

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

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

=>3、日期时间类型

2.3、约束

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

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

2.4、创建表的案例

根据页面原型图设计表

1、页面原型图

2、字段限制说明

设计一张表，基本的流程如下：

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

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

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

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

2.5、修改表结构

第 3 章

DML数据库操作语言

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

3.1、增加(insert)

3.2、修改(update)

3.2、删除(delete)

补充：

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

第 4 章

DQL数据库查询语言

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

4.1、select基本查询

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

4.2、where条件查询

=>1、比较运算符

=>3、逻辑运算符

例题：

4.3、聚合函数

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

4.4、分组查询

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

注意：如果是进行多表联查时，得根据需求的分组字段来确定填写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、排序查询

4.6、分页查询

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

第 5 章

单表查询案例

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

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

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

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

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

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

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

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

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

第 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、一对多关系表设计

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

部门表原型图：

员工表原型图：

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

=>部门表

=>员工表

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

7.2、外键约束

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

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

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

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

7.3、物理外键和逻辑外键

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

7.4、一对一表设计

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

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

7.3、多对多表设计

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

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

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

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

第 8 章

多表查询

8.1、多表查询分类

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

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

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

2、外连接

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

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

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

3、子查询

8.2、内连接

8.3、外连接

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、事务操作

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

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默认的隔离级别=====>可重复读

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

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，把它写入到从库的中继日志，然后从库再从中继日志中读取数据，写入到自己的数据库中，那这样数据就保持同步了。

· 往期回顾·