MySQL 高级学习笔记

1. 变量相关

临时变量

用户变量，也称会话变量

系统变量

2. 账户管理

账户管理的应用场景

在实际项目开发中，不可能让每个开发人员都使用 root 账户进行登录

根据开发人员角色的不同，会分配具有不同权限的账户

MySQL 账户体系

服务实例级账户：启动了一个 mysqld，即为启动一个数据库实例。如果某用户拥有服务实例分配的权限，那么该账号就可以删除所有的数据库，连同这些库中的表

数据库级别账户：对特定数据库执行增删改查 (CRUD) 的所有操作，最常用的一种级别

数据表级别账户：对特定表执行 CRUD 的所有操作

字段级别账户：对某些表的特定字段进行操作

存储程序级别账户：对存储程序进行 CRUD 的操作

查看账户

创建账户

授权权限

查看账户权限

刷新权限设置

回收权限

删除账户

修改密码

3.函数

函数的创建

查看函数是否创建成功

调用函数

删除函数

简单示例

使用 SELECT … INTO 定义一个函数

4. 存储过程

什么是存储过程以及为什么需要存储过程

存储过程的创建

参数定义

临时变量定义 (BEGIN…END之间的变量)

示例

存储过程的调用

5. 视图

什么是视图

视图的创建

视图的查看

视图的删除

函数、存储过程、视图的比较

函数是对一个方法的封装，在 MySQL 中可分为自定义函数与内置函数

函数中的参数只是一种类型，就是输入类型，函数必须有返回值

存储过程是数据库用于处理复杂 SQL 的一段程序，它可以实现一般 SQL 实现不了的程序

存储过程可以简单认为是 SQL 与程序代码的结合体

存储过程中的参数类型有三种，OUT 类型可类比于函数中的返回值，我们一般用一个用户变量来接受它

视图是对一个复杂 SELECT 语句的封装

视图可以简单的认为是一张表，一张由 SELECT 查询结果而组成的一张数据表

6. 事务

为什么要有事务

简单的转账示例，转账操作至少涉及两方，A方扣钱，B方得钱。如果扣钱方成功，而得钱方失败。这样会导致重复扣钱的问题

事务就是为了解决这一问题而出现的，事务将整个类似 "转账" 的操作看成是一个操作集合，对整体集合进行操作规定

事务的特点 (ACID)

原子性 (Atomicity)：事务中的全部操作，要么全部完成 ，要么全部不做

一致性 (Consistency)：几个并行执行的事务，其执行结果必须与按某一顺序串行执行结果一致

隔离性 (Isolation)：一个事务不受另一事务的影响，拿转账来说，假设用户A和用户B两者的钱加起来一共是5000，那么不管A和B之间如何转账，转几次账，事务结束后两个用户的钱相加起来应该还得是5000，这就是事务的一致性。

持久性 (Durability)：对于已提交事务，系统必须保证该事务对数据库的改变不被丢失，即使数据库出现故障

名词解释

Commit：事务提交，表示整个事务下的操作集全部有效

Rollback：事务回滚，表示整个事务下的操作集全部作废，数据将还原到操作前的状态

事务不隔离，在并发访问时带来的问题

脏读：一个事务读到了另一个事务未提交的数据。比如事务一将 A 账户的钱由100改为 500，而此时一个并发的事务二读取 A 账户的钱，这时事务二读到的数据是 500 。而事务一这时又将事务回滚，导致 A 账户钱变加 100。此时若事务二再次读取，会发现数据是 100。这样，之前读取到的 500 就是脏数据。

不可重复读：一个事务读取到另一个事务已经提交的数据，并且这个数据是在UPDATE的操作下被修改的。比如上述例子，事务二先读取 A 账户的钱，发现是 100，接着事务一将钱数进行 **UPDATE ** 成 500 并提交整个事务。此时事务二再读取时，会发现得到的结果是 500。这就出现了一个事务多次查询同一个属性却得到了不同的结果值。

虚读/幻读：一个事务读取到另一个事务已经提交的数据，并且这个数据是在INSERT的操作下被修改的。比如，事务一先读取 A 账户的钱，得到 100 ，同时将 A 账户的名称改为 B。此时事务二新增一个账户 A ，并将 A 账户的钱设置为 100 。这时当事务一再次查询时，会发现 A 账户还没有修改，产生了 '幻觉'。

三者的区别：总体来说，可以分为两大类问题：读未提交和读已提交。脏读是读未提交事务数据；后两者则是读已提交数据。而后两者的区别在于已提交数据是如何操作的，不可重复读是执行 UPDATE 操作，而后者是执行 INSERT 操作。

事务的隔离级别

读未提交 (read uncommitted)：一个事务读到另一个事务没有提交的数据。上述三个问题都存在

读已提交 (read committed)：一个事务在写时禁止其他事务读写，表示必须提交后的数据才能被读取

未解决：不可重复读、虚读/幻读

解决：脏读

可重复读 (repeatable read)：一个事务在写时禁止其他事务读写，一个事务在读时，禁止其他事务写

未解决：虚读/幻读

解决：脏读，不可重复读

串行化 (serializable)：每次只能执行一个事务，上述问题全部解决。但是这种级别效率低，一般不用

常见的数据库事务隔离：

事务命令

7. 索引

问题引入：

索引的创建

查看索引

删除索引

8. 数据库的备份与恢复

备份

恢复

9. 执行外部的 sql 文件

10. Python 与 MySQL 交互

Python 中操作 MySQL 步骤图解

安装 MySQL 模块

代码示例

11. 数据库中常见的安全问题

SQL 的注入

撞库

根据在某个站点获得的用户名和密码，去登录其他站点，从而得到更多信息

因为通常我们的习惯是在不同网站注册，大多会使用相同的密码

如果某个网站设计得不好，被人窃取密码，再用相同的密码去登录其他网站，就会很危险

安全建议

对于 SQL 注入而言，容易发生在表单提交中，因此永远不要相信用户的输入，要对用户的输入先进行正则匹配，如果有特殊字符，要进行转义

对于撞库，最好不同网站使用不同的密码，特别是对重要的网站。同时要定期更换自己的密码

愿关注我们的人都能找到

属于自己的皮球