当前位置：首页 > article >正文

【MySQL】零碎知识点（易忘 / 易错）总结回顾

article 2024/11/14 2:25:54

一、基础

一般的文件确实提供了数据的存储功能，但是文件并没有提供非常好的数据管理能力

1、文件保存数据的几个缺点

安全性问题

不利于数据查询和管理

不利于存储海量数据

在程序中控制不方便

数据库存储介质

磁盘
内存

2、mysql 的本质是基于 CS 模式的一种网络服务

C：mysql 是数据库服务的客户端
S：mysqld 是数据库服务的服务端

3、主流数据库

SQL Sever：微软的产品
Oracle：甲骨文产品，适合大型项目，复杂的业务逻辑，但并发一般来说不如 MySQL
MySQL：世界上最受欢迎的数据库，属于甲骨文，并发性好，但不适合做复杂的业务，主要用在电商、SNS、论坛，对简单的 SQL 处理效果好
PostgreSQL
SQLite：是一款轻型的数据库，它包含在一个相对小的 C 库中，它的设计目标是嵌入式的
H2：一个用 Java 开发的嵌入式数据库，它本身只是一个类库，可以直接嵌入到应用项目中

如果没有写 -h 127.0.0.1 默认是连接本地
如果没有写 -P 3306 默认是连接 3306 端口号

安装数据库服务器只是在机器上安装了一个数据库管理系统程序，这个管理程序可以管理多个数据库，一般会针对每一个应用创建一个数据库

4、数据库服务器、数据库和表的关系

5、mysql 在 Linux 中的表现

建立数据库本质就是在 Linux 下新建一个目录
在数据库内建立表，本质就是在 Linux 下创建对应的文件

以上工作是 mysqld 服务做的

6、MySQL 架构

MySQL 是一个可移植的数据库，几乎能在当前所有的操作系统上运行

各种系统在底层实现方面各有不同，但是 MySQL 基本上能保证在各个平台上的物理体系结构的一致性

7、SQL 语句分类

（1）DDL（Data Definition Language，数据定义语言）

用来维护存储数据的结构

代表指令：create、drop、alter

（2）DML（Data Manipulation Language，数据操纵语言）

用来对数据进行操作

代表指令：insert、delete、update
DML 中又单独分了一个 DQL，数据查询语言
- 代表指令：select
DCL（Data Control Language，数据控制语言）：主要负责权限管理和事务
- 代表指令：grant、revoke、commit

8、存储引擎

是数据库管理系统如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的一种实现方法

MySQL 的核心就是插件式存储引擎，支持多种存储引擎

查看存储引擎：show engines;

（1）存储引擎对比

9、刷新权限表

flush privileges;

二、库的操作

1、增删数据库

（1）创建数据库

create database db_name;

本质就是在 /var/lib/mysql 创建一个目录

create database db_name charset=utf8 collate utf8_general_ci;

CHARACTER SET：指定数据库采用的字符集

utf8_ general_ ci：不区分大小写
- 默认是升序排序，将大小写看成一整组来进行排序
utf8_ bin：区分大小写
- 默认是升序排序，先将大写进行排序，再对小写进行排序

COLLATE：指定数据库字符集的校验规则

另一种写法：create database db_name character set utf8 collate utf8_general_ci;

（2）删除数据库

drop database db_ name;

本质就是删除目录

2、系统编码

（1）字符集和校验规则

A. 查看系统默认字符集以及校验规则

show variables like 'character_set_database';
show variables like 'collation_database';

B. 查看数据库支持的字符集

show charset;

字符集主要是控制用什么语言，比如 utf8 就可以使用中文

C. 查看数据库支持的字符集校验规则

show collation;

（2）select 是从表里查询，通配符 * 表示将表中数据都显示出来

插入的本质是存取，采用 default-character-set=utf8，也就是存取时所用的编码
查询时是按照校验集的要求来查找的

3、改查数据库

（1）显示创建语句

show create database db_name;

MySQL 建议关键字使用大写，但不是必须的

数据库名字的反引号 `` 是为了防止使用的数据库名刚好是关键字

（2）修改数据库

alter database db_name charset=gbk collate gbk_chinese_ci;

对数据库的修改主要指的是修改数据库的字符集、校验规则

（3）查看数据库

show databases;

查询当前所在数据库：select database();

4、库的备份

库的备份：mysqldump -P3306 -uroot -p密码 -B 数据库名 > 数据库备份存储的文件路径

将 mytest 库备份到文件：mysqldump -P3306 -uroot -p123456 -B mytest > D:/mytest.sql

执行完备份后，会自动退出连接
此时 mytest.sql 文件的内容就是把整个创建数据库、建表、导入数据的语句都装在这个文件中

如果是同时备份多个数据库：# mysqldump -uroot -p -B 数据库名1 数据库名2 ... > 数据库存放路径

表的备份：mysqldump -uroot -p 数据库名表名1 表名2 > D:/mytest.sql

5、库的恢复

还原文件的路径：source D:/mytest.sql;

如果备份一个数据库时没有带上 -B 参数，那么在恢复数据库时，需要先创建空数据库，然后使用数据库，再使用 source 来还原

6、查看数据库连接情况

show processlist;

当发现自己的数据库比较慢时，可以查看数据库连接情况

可以知道当前有哪些用户连接到自己的 MySQL，如果查出某个用户不是正常登陆的，很有可能自己的数据库被入侵了

三、表的操作

1、创建表

field 表示列名，datatype 表示列的类型

如果没有指定字符集，则以所在数据库的字符集为准；如果没有指定校验规则，则以所在数据库的校验规则为准

（1）不同的存储引擎，创建表的文件不一样

A. MyISAM

users.frm：表结构
users.MYD：表数据
users.MYI：表索引

B. InnoDB

users.frm：表结构
users.ibd：表数据和索引

表默认的存储引擎是 InnoDB

2、查看表结构

desc 表名;

显示表的详细信息：show create table user1 \G;

\G：格式化显示，把不需要的符号去掉，该显示信息方法会标准化的记录下之前写的语句

3、修改表

修改表名：alter table 旧表名 rename to 新表名;

在表中添加记录：insert into table_name values (插入各项内容);

在表中添加一个字段：alter table table_name add 列名列的类型 [after ...];

插入新字段后，对原来表中的数据没有影响

修改属性：alter table 表名 modify 列名列的类型;

这里不是定向修改，所以要将所修改属性后面的内容也加上，否则将直接覆盖原有内容

删除列：alter table table_name drop 列名;

删除字段及其对应的列数据都没了

修改列名：alter table table_name change 旧列名新列名列的类型;

修改时，新字段需要完整定义，所以必须将属性带上

4、删除表

drop table 表名 ...

四、数据类型

1、数值类型

（1）tinyint 类型

整型可以指定是有符号的和无符号的，默认是有符号的
可以通过 unsigned 来说明某个字段是无符号的
- 尽量不使用 unsigned，对于 int 类型可能存放不下的数据，int unsigned 同样可能存放不下，与其如此，还不如在设计时，将 int 类型提升为 bigint 类型
在 MySQL 特定的类型中插入不合法的数据，一般会发生拦截
- 数据类型本身也是一种约束，能够保证数据库中的数据是可预期、完整的

（2）bit 类型

bit[(M)]：M 表示每个值的位数，如果 M 被忽略，默认为 1

当 bit 类型列（假设为 online）没有显示内容，是因为位类型在显示 0/1 时是按照 ASCII 码值显示的，只不过当前显示的 ASCII 码值是不可显示的
- 如果想在该列显示内容，让 online 按照十进制方案插入位结构，可以输入：select hex(online) from 表名;
- 如果只存放 0/1，可以定义 bit(1)，节省空间

（3）小数类型

A. float

float[(M, D)]：M 指定显示长度，D 指定小数位数

默认是有符号浮点数
unsigned：如果定义的是 float(4,2) unsigned，那么范围是 0~99.99，相当于有符号浮点数删减去负数取值部分
MySQL 在保存值时会进行四舍五入
- 在合法范围内允许五入，但在边界值五入会导致整体的浮点数增多，从而超过浮点数对应的范围，所以不是所有情况都可以五入
整数部分也可能有精度损失

B. decimal

decimal(M, D)：定点数 M 指定长度，D 表示小数点的位数

decimal 和 float 的区别：表示的精度不一样
- float 表示的精度大约是 7 位，会发生截断或四舍五入
- decimal 整数最大位数 M 为 65，支持小数最大位数 D 是 30
  - 如果 M 被省略，默认是 10
  - 如果 D 被省略，默认为 0
  - 完全保证精度准确

2、字符串类型

（1）char

char(L)：固定长度字符串，L 是可以存储的长度，单位为字符

语言上的字符是一个字符对应一个字节，而这里的一个字符就是代表一个符号
- char(L) 可以存放 L 个字符，可以是 L 个字母 / 汉字，但不能超过 L 个
- 在 utf-8 中一个汉字一般占 3 个字节，在 gbk 中一个汉字一般占 2 个字节

（2）varchar

varchar(L)：可变长度字符串，L 表示字符长度

varchar 长度可以指定为 0~ 65535 之间的值，但有 1~3 个字节用于记录数据大小，所以说有效字节数是 65532
- 当表的编码是 utf-8 时，varchar(n) 的参数 n 最大值是 65532/3=21844
- 当表的编码是 gbk，varchar(n) 的参数 n 最大是 65532/2=32766

如果数据确定长度都一样，就使用定长 char，比如：身份证、手机号、md5
- 定长的磁盘空间比较浪费，但效率高
如果数据长度有变化，就使用变长 varchar，比如：名字、地址，但要保证最长的能够存的进去
- 变长的磁盘空间比较节省，但效率低

3、日期和时间类型

（1）date

日期格式：'yyyy-mm-dd'，占用 3 字节

（2）datetime

时间日期格式：'yyyy-mm-dd HH:ii:ss' 表示范围为 1000~9999 ，占用 8 字节

（3）timestamp

时间戳会自动更新，从 1970 年开始
格式和 datetime 完全一致，占用 4 字节

4、enum 和 set

（1）enum

enum('选项1', '选项2', '选项3', ...);

枚举，“单选” 类型

该设定提供了若干个选项的值，最终一个单元格实际只存储了其中一个值，而且出于效率考虑，这些值实际存储的是 “数字”，因为这些选项的每个选项值依次对应如下数字：1, 2, 3, ... 最多 65535 个

当添加枚举值时，也可以添加对应的数字编号

（2）set

set('选项值1', '选项值2', '选项值3', ...);

集合，“多选” 类型

该设定只是提供了若干个选项的值，最终一个单元格可存储其中任意多个值，而且出于效率考虑，这些值实际存储的是 “数字”，因为这些选项的每个选项值依次对应如下数字：1, 2, 4, 8, 16, 32, ... 最多 64 个

1 对应 '选项值1'，2 对应 '选项值2'，3 对应 '选项值1, 选项值2'，跟二进制比特位的计数有关，比特位由低到高依次对应的是选项值从左向右

不建议在添加枚举值、集合值时采用数字的方式，因为不利于阅读

假设爱好 hobby 的属性为 set，表中有的人 hobby 是 “登山”，有的人是 “登山 + ...”，则不能查询出所有爱好为登山的人，只能查出 hobby 只为 “登山” 的人

（3）集合查询使用 find_in_set 函数

find_in_set(sub, str_list)

如果 sub 在 str_list 中，则返回下标，如果不在，则返回 0
str_list 用逗号分隔的字符串

可以查询出所有 hobby 都包含 “登山” 的人

五、表的约束

表中一定要有各种约束，通过约束可以保证插入数据库表中的数据是符合预期的

约束的本质：通过技术手段倒逼程序员插入正确的数据，反过来站在 MySQL 的角度，凡是插入进来的数据都是符合数据约束的

约束的最终目标：保证数据的完整性和可预期性

真正约束字段的是数据类型，但数据类型约束很单一，需要有一些额外的约束，更好的保证数据的合法性，从业务逻辑角度保证数据的正确性

1、空属性（非空约束）

null（默认的）和 not null（不为空）

数据库默认字段基本都是字段为空，但在实际开发时，应尽可能保证字段不为空，因为数据为空没办法参与运算

2、默认值（default 约束）

默认值：某一种数据会经常性的出现某个具体的值，可以在一开始就指定好，在需要真实数据时，用户可以选择性的使用默认值

如果设置 default 后，用户插入了具体数据，那么就用用户所提供的数据
如果数据在插入时不给该字段赋值，那么就使用默认值

null 和 not null 主要是用来约束用户想插入的内容，default 和 not null 二者并不冲突，而是相互补充的

defalut 和 not null 一般不需要同时出现，因为 default 本身有默认值，不会为空

如果用户想显示的插入，那要么插入一个具体的值，要么就插入 NULL

如果在建表时没有设置 default 默认值，前提是没有设置 not null，那么 MySQL 会对 sql 做优化，默认加了 default null

3、列描述

comment：没有实际含义，专门用来描述字段，会根据表创建语句保存

通过 desc 查看不到注释信息，show 可以看到

4、zerofill

如果宽度小于设定的宽度，自动填充 0

不够就补 0，补到括号内的数字位数为止，如果够了就直接显示

这只是最后显示的结果，在 MySQL 中实际存储的还是原先的数字

建表默认是 int(10)，是因为一个整数占 4 个字节，4 个字节有符号的取值范围：-2^31~2^31-1，无符号是：0~2^32-1

2^31-1 和 2^32-1 最后表示出来都是 10 位，用 int(10) 足以把所有对应的整数的数据位表示出来
有符号是 11 位，多了 1 是因为多了一个符号位

5、主键

primary key 用来唯一的约束该字段里面的数据，不能重复、不能为空，一张表中最多只能有一个主键，但不意味着一个表中的主键只能添加给一列

一个主键可以被添加到一列 / 多列，添加给多列则叫作复合主键

主键所在的列通常是整数类型

（1）指定主键

创建表时直接在字段上指定主键

desc 查看时，其中 Key 中的 PRI 表示该字段是主键

（2）主键约束

主键对应的字段中不能重复，否则操作失败

（3）追加主键

当表创建好后但没有主键时，可以再次追加主键，但需要删除重复内容

最好在刚开始就建立好主键

（4）删除主键

alter table 表名 drop primary key;

（5）复合主键

在创建表时，在所有字段之后使用 primary key(主键字段列表) 来创建主键，如果有多个字段作为主键，可以使用复合主键

复合主键中的字段有个别发生冲突并不影响，但如果主键中多列的列信息都与历史信息发生冲突才会触发主键约束

6、自增长

（1）auto_increment

当对应字段不给值，就会自动被系统触发，系统会从当前字段中已经有的最大值进行 +1 操作，从而得到一个新的不同值
通常和主键搭配使用，作为逻辑主键

（2）特点

任何一个字段要做自增长，前提是本身是一个索引，即 key 一栏有值
自增长字段必须是整数
一张表最多只能有一个自增长

获取上次插入的 auto_increment 的值：select last_insert_id();

（3）索引

在关系数据库中，索引是一种单独的、物理的对数据库表中一列 / 多列的值进行排序的一种存储结构，它是某个表中一列 / 若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单

索引提供指向存储在表的指定列中的数据值的指针，然后根据指定的排序顺序对这些指针进行排序

数据库使用索引以找到特定值，然后顺指针找到包含该值的行，这样可以使对应于表的 SQL 语句执行得更快，可快速访问数据库表中的特定信息

7、唯一键

虽然一张表中只能有一个主键，但唯一键就可以解决表中有多个字段需要唯一性约束的问题

唯一键的本质和主键差不多，唯一键允许为空且可以多个为空，空字段不做唯一性比较

（1）和主键的区别

二者不是对立的，是互相补充的

主键通常用来标定某一行记录在整表中的唯一性，而唯一键的侧重点在于让用户插入的列值和表中其它的列值不要发生冲突，从而保证在业务上以及在上层对应的字段的唯一性，即不能出现重复

假设 id 字段是唯一键，当 id 为 NULL 时，是可以直接插入且不冲突的

对应的主键一旦设置好，不能和其它键冲突且主键不能为空
唯一键表达了与主键类似的功能，可以为空，NULL 不参与计算，与语言上的 NULL 不一样

建议将主键设计成和当前业务无关的字段，这样当业务调整时，可以尽量不会对主键做过大的调整

8、外键

foreign key(字段名) references 主表(列名)

用于定义主表和从表之间的关系

外键约束主要定义在从表上
主表必须是有主键约束或 unique 约束

当定义外键后，要求外键列数据必须在主表的主键列存在或为 null

本质：产生关联，增加约束，整体增加表和表之间的完整性

把相关性交给 MySQL 去审核，提前告诉 MySQL 表之间的约束关系，那么当用户插入不符合业务逻辑的数据时，M ySQL 是不允许插入的

六、基本查询（表的增删查改）

1、CRUD

（1）Create（创建）

insert into 表名 [(column1, column2, ...)] values (value_list1, value_list2, ...);

若忽略 values 左侧 [] 内的内容，则称为全列插入，否则成为按列插入

单行数据 + 全列插入
- value_list 数量必须和定义表的列的数量及顺序一致
- 在插入时可以不用指定带有自增长键的属性，但此时需要明确插入数据到哪些列了，MySQL 会使用默认的值进行自增
多行数据 + 指定列插入
- value_list 数量必须和指定列数量及顺序一致

A. 插入是否更新成功

因为主键 / 唯一键对应的值已经存在而导致插入失败，此时可以选择性的进行同步更新操作

insert ... on duplicate key update column1=value1, column2=value2, ...;

B. 返回值

0 row affected：表中有冲突数据，但冲突数据的值和 update 的值相等
1 row affected：表中没有冲突数据，数据被插入
2 row affected：表中有冲突数据，并且数据已经被更新

获取受到影响的数据行数：select ROW_COUNT();

C. 替换

replace into table_name[(column1, column2, ...)] values (value_list1, value_list2, ...);

主键 / 唯一键没有冲突，则直接插入
主键 / 唯一键如果冲突，则删除后再插入

（2）Retrieve（读取）

select [distinct] {* | {column1, column2, ...} [from table_name] [where ...] [order by column [asc | desc], ...] limit ...;

SELECT 列

全列查询
- select * from 表名;
- 通常情况下不建议使用 * 进行全列查询
  - 查询的列越多，意味着需要传输的数据量越大
  - 可能会影响到索引的使用
指定列查询
- 指定列的顺序不需要按定义表的顺序来
查询字段为表达式
- 表达式不包含字段
- 表达式包含多个字段，可用于求和
为查询结果指定别名
- select column as renmae from table_name;
- 可以通过 as+xx 来重命名，xx 就是新命名的内容
- 别名不能用在 where 条件中
  - 能否使用别名完全取决于当前 sql 子句的执行顺序
结果去重
- select distinct 列名 from 表名;
WHERE 条件
- 比较运算符
  - 在 MySQL 中，用 = 来判断两个字符串或数字是否相等，而 C/C++ 是 ==
  - 用 = 判断是否等于 NULL 这样做是不安全的，因为 NULL 和 0 本身表示的含义不同
    - NULL 表示空
    - 0 表示数字 0
- 逻辑运算符
- x between a0 and a1 等价于 x>=a0 and x<=a1
- id=8 or id=9 or id=10 等价于 id in (8, 9, 10)
- NULL 和 NULL 的比较，= 和 <=> 的区别
- 结果排序
  - select column1, column2 from 表名 order by column1 [asc | desc];
    - 默认为升序 ASC，可省略不写，但不建议
    - DESC 为降序
  - 别名在 order by 中可以使用
  - 没有 order by 子句的查询，返回的顺序是未定义的，不要依赖这个顺序
  - NULL 视为比任何值都小，升序出现在最上面，降序出现在最下面
- 筛选分页结果
  - 从 0 开始，筛选 n 条结果
    - select ... from 表名 [order by ...] limit n;
  - 从 s 开始，筛选 n 条结果
    - select ... from 表名 [order by ...] limit s, n;
      - s：开始位置，下标从 0 开始
      - n：步长，从指定位置开始，连续读取 n 条记录
  - 从 s 开始，筛选 n 条结果，比第二种用法更明确，建议使用
    - select ... from 表名 [order by ...] limit n offset s;
      - n：步长，从指定位置开始，连续读取 n 条记录
      - s：开始位置，下标从 0 开始
  - 如果结果不足 n 条，不会有影响
  - limit 的本质功能是 “显示”
    - 得先有数据才能 “显示”，“显示” 时 limit 只是告诉 MySQL 只显示从哪里开始，从开始位置显示几行
    - limit 不是条件筛选，本质就是把数据准备好，排好序，然后再 limit，执行阶段更靠后

（3）Update（更新）

update 表名 set column1=expr1, column2=expr2, ...;

对查询到的结果进行列值更新

（4）Delete（删除）

A. 删除数据

delete from 表名 where ...;

B. 截断表

truncate table 表名;

只能对整表操作，不能像 delete 一样针对部分数据操作

影响行数是 0，实际上没有对数据真正操作，所以比 delete 更快，但 truncate 在删除数据时，并不经过真正的事务，所以无法回滚

会重置 auto_increment 项

C. delete 和 truncate 的区别

都可以清空表中的数据
delete from 是传统的删除，不会对计数器进行清空或重新置位，而 truncate 清空表会重置 auto_increment 项
truncate 是直接将表中数据清空，它不走事务，而 delete from 以及之前学的 sql 最终在运行时都要以事务的方式被包装，然后再让 MySQL 去运行

2、插入查询结果

insert into 表名 [(column1, column2, ...)] select ...;

3、聚合函数

使用 * 做统计，不受 NULL 影响，也可以使用表达式做统计

distinct 要写在括号内，因为是要对目标属性去重，而不是对 count() 的结果去重，NULL 不会计入结果

4、group by 子句

分组的目的：为了进行分组后，方便进行聚合统计
- 分组就是把一组按照条件拆分成了多个组，进行各自组内的统计
- 分组（“分表”）就是把一张表按照条件在逻辑上拆成了多个子表，然后分别对各自的子表进行聚合统计
在 select 中使用 group by 子句可以对指定列进行分组查询：select column1, column2, ... from 表名 group by 列名;
指定列名，实际分组是用该列不同的行数来进行分组的
having 和 group by 的语义是一样的，having 相当于是对分组聚合统计后的数据进行条件筛选
having 和 group by 配合使用，对 group by 分组的结果进行筛选，即对聚合的结果进行判断，作用有点像 where
- having 和 where 的区别与执行顺序
  - 都能够做条件筛选
  - 但它们是完全不同的条件筛选，它们的条件筛选的阶段是不同的
不要单纯的认为，只有在磁盘上将表结构导入到 MySQL，真实存在的表才叫表。在这中间筛选出来的，包括最终结果，全都是逻辑上的表，MySQL 一切皆表
SQL 查询中各个关键字的执行先后顺序：from > on > join > where > group by > with > having > select > distinct > order by > limit

七、函数

1、日期函数

日期：年月日

时间：时分秒

获得年月日：select current_date();

获得时分秒：select current_time();

获得时间戳：select current_timestamp();

在日期的基础上加日期

select date_add('yyyy-mm-dd', interval x day);
select date_add(now(), interval x minutes);

在日期的基础上减去时间

select date_sub('yyyy-mm-dd', interval x day);
select date_sub(now(), interval x minutes);

计算两个日期之间相差多少天

select datediff('y1y1y1-m1m1-d1d1', 'y2y2y2y2-m2m2-d2d2');

获取当前时间：select now();

2、字符串函数

获取字符集：select charset('abcd');

显示表中信息，按指定格式显示：select concat('a', 'b', 'c', 123, 3.14);

求表中某列占用的字节数：select length(xx) from 表名;

length 函数返回字符串的长度，以字节为单位
中文表示多个字节数，与字符集编码有关

将表中某列 colume 所有有 'X' 的替换成 'Y'：select column, replace(column, 'X', 'Y') from 表名;

截取表中 xx 字段的第 2 个到第 3 个字符：select xx, substring(xx, 2, 2) from 表名;

以首字母小写的方式显示所有 xx：select xx, concat(lcase(substring(xx, 1, 1)), substring(xx, 2)) from 表名;

去除空格

删除前导空格：select ltrim(' xx ');
删除后缀空格：select rtrim(' xx ');
从字符串中删除不需要的前导和后缀字符：select trim(' xx ');

3、数学函数

绝对值：select abs(x);

十进制转二进制：select bin(x);

对应数字转换成十六进制：select hex(x);

进制转换

select conv(num, x, y);
把 num 从 x 进制转换成 y 进制

向上取整

select ceiling(x.x);
将一个数值向上调整为最接近它的较大整数

向下取整

select floor(x.x);
将一个数值向下调整为最接近它的较小整数

保留小数位数，小数四舍五入

对 x.xxx 保留 y 位小数：select format(x.xxx, y);

产生随机数：select rand();

结果 >= 0 且 <1

模运算（取模、求余）

对 x 模 y：select mod(x, y);

4、其它函数

查询当前用户：select user();
对 'str' 字符串进行 md5 摘要，摘要后得到一个 32 位字符串：select md5('str');
显示当前正在使用的数据库：select database();
对用户加密：select password('用户名');
如果 val1 为 null 则返回 val2，否则返回 val1 的值：select ifnull(val1, val2);

八、复合查询

1、多表查询

从第一张表中选出第一条记录和第二个表的所有记录进行组合，然后从第一张表中取第二条记录，和第二张表中的所有记录组合，不加过滤条件，得到的结果称为笛卡尔积

笛卡尔积就是将数据进行穷举组合

2、自连接

在同一张表连接查询

使用子查询

使用多表查询（自查询）

给自己的表起别名，因为要先做笛卡尔积，所以别名可以先识别

3、子查询

是指嵌入在其它 sql 语句中的 select 语句，也叫嵌套查询

任何时刻查询出来的临时结构，本质在逻辑上也是表结构

单行子查询：返回一行记录的子查询

多行子查询：返回多行记录的子查询

in 关键字
- 用来判断一个对应的列值是否在某个集合当中，只要在集合当中，说明查找成功
all 关键字
- 表示与所有的值作比较
any 关键字
- 表示与任意的值作比较

多列子查询：返回多个列数据的子查询语句

4、在 from 子句中使用子查询

数据查询的技巧：把一个子查询当做一个临时表使用

解决多表问题的本质：想办法将多表转化为单表，所以在 MySQL 中，所有 select 的问题全都可以转成单表问题

5、合并查询

在实际应用中，为了合并多个 select 的执行结果，可以使用集合操作符

（1）union

该操作符用于取得两个结果集的并集

当使用该操作符时，会自动去掉结果集中的重复行

（2）union all

该操作符用于取得两个结果集的并集

当使用该操作符时，不会去掉结果集中的重复行

select 如果要使用 union / union all 来拼接两个结果，前提是必须保证两个表的列数相同

九、表的内连和外连

表的连接分为内连和外连

1、内连接

select ... from 表1 inner join 表2 on 连接条件 and 其它条件;

内连接实际上就是利用 where 子句对两种表形成的笛卡儿积进行筛选，在开发过程中使用的最多的连接查询

2、外连接

（1）左外连接

select 字段名 from 表名1 left join 表名2 on 连接条件;
如果联合查询，左侧的表完全显示，就说是左外连接
当左边表和右边表没有匹配时，也会显示左边表的数据

（2）右外连接

select 字段 from 表名1 right join 表名2 on 连接条件;
如果联合查询，右侧的表完全显示，就说是右外连接

十、索引

可以提高数据库的性能，不用加内存，不用改程序，不用调 sql，只要执行正确的 create index，查询速度就可能提高成百上千倍

查询速度的提高是以插入、更新、删除的速度为代价的，这些写操作增加了大量的 IO

它的价值在于提高一个海量数据的检索速度

MySQL 的服务器本质是在内存中的，所有数据库的 CRUD 操作全都是在内存中进行的，索引也是如此

提高算法效率的因素

1、组织数据的方式
2、算法本身

1、常见索引

（1）主键索引（primary key）

一定要创建主键，才会默认生成主键索引

向一个具有主键的表中乱序插入数据，数据会自动排序

插入数据时排序的目的就是优化查询的效率

页内部存放数据的模块实质上是一个链表结构，链表的特点就是增删快、查询修改慢，所以优化查询的效率是必须的
正是因为有序，所以在查找时，从头到尾都是有效查找，没有任何一个查找是浪费的，而且可能可以提前结束查找过程

（2）唯一索引（unique）

（3）普通索引（index）

（4）全文索引（fulltext）

2、创建索引

alter table 表名 add index(列名);

3、磁盘

（1）MySQL 与存储

MySQL 给用户提供存储服务，而存储的都是数据，数据在磁盘这个外设当中

磁盘是计算机中的一个机械设备，相比于计算机其它电子元件，磁盘效率是比较低的，再加上 IO 本身的特征

（2）磁盘中一个盘片

（3）扇区

数据库文件本质就是保存在磁盘的盘片中，也就是扇区

数据库文件很大、很多，所以一定需要占据多个扇区

在半径方向上，距离圆心越近，扇区越小，距离圆心越远，扇区越大，但它们都是 512byte

保证一个扇区多大是由比特位密度决定的

找到一个文件的全部，本质就是在磁盘找到所有保存文件的扇区，而能够定位任何一个扇区，那么就能找到所有扇区，因为查找方式相同

A. 定位扇区

柱面，等价于磁道：多盘磁盘，每盘都是双面，大小完全相等，那么同半径的磁道在整体上便构成了一个柱面
每个盘面都有一个磁头
只需要知道柱面、磁头、扇区对应编号，即可在磁盘上定位所要访问的扇区，这种磁盘数据定位方式叫做 CHS

实际系统软件使用的并不是 CHS（但硬件是），而是 LBA，一种线性地址，可以想象成虚拟地址与物理地址，系统最后将 LBA 地址转化成为 CHS，交给磁盘去进行数据读取

如果操作系统直接使用硬件提供的数据大小进行交互，那么系统的 IO 代码就和硬件强相关，也就是说如果硬件发生变化，系统就必须跟着变化

单次 IO，512 byte 还是太小了，IO 单位小意味着读取相同数据内容需要进行多次磁盘访问，会导致效率降低

文件系统读取基本单位不是扇区，而是数据块

系统读取磁盘是以块为单位的，其基本单位是 4KB

（4）磁盘随机访问（Random Access）与连续访问（Sequential Access）

A. 随机访问

本次 IO 所给出的扇区地址和上次 IO 给出的扇区地址不连续，这样磁头在两次 IO 操作之间需要做比较大的移动动作，才能重新开始读 / 写数据

磁盘是通过机械运动进行寻址的，随机访问不需要过多的定位，所以效率较高

B. 连续访问

如果当次 IO 给出的扇区地址与上次 IO 结束的扇区地址是连续的，那么磁头就能很快的开始这次 IO 操作，这样多个 IO 操作称为连续访问

即使相邻的两次 IO 操作在同一时刻发出，但如果它们的请求扇区地址相差很大的话，也只能称为随机访问，而非连续访问

（5）MySQL 与磁盘交互基本单位

MySQL 作为一款应用软件，可以想象成一种特殊的文件系统，它有着更高的 IO 场景，所以为了提高基本的 IO 效率， MySQL 进行 IO 的基本单位是 16KB

磁盘这个硬件设备的基本单位是 512 byte，而 MySQL InnoDB 引擎使用 16KB 进行 IO 交互，即 MySQL 和磁盘进行数据交互的基本单位是 16KB

这个基本数据单元在 MySQL 里叫作 page，但要注意和系统的 page 区分开
- 不要简单的将 Page 理解成是一个内存块，Page 内部也必须写入对应的管理信息，可以简单理解成一个个独立文件是由一个 / 多个 Page 构成的
- 将所有的 Page 用 “链表” 的形式管理起来，但不是数据结构中的链表，在 buffer pool 内部对 MySQL 中的 page 进行了建模
  - 不同的 Page 在 MySQL 中都是 16KB ，使用 prev 和 next 构成双向链表
  - 因为有主键，所以 MySQL 会默认按照主键给数据进行排序，从 Page 内的数据记录可以看出，数据是有序且彼此关联的
  - 每个目录项的构成：键值+指针
    - 存在一个目录页来管理页目录，目录页中的数据存放的就是指向的那一页中最小数据的键值，通过比较来找到该访问那个 Page，进而通过指针找到下一个 Page
    - 普通页中存的数据是用户数据，而目录页中存的数据是普通页的地址
    - B+树
      - 叶子节点保存数据，但路上节点没有，也就是非叶子节点不要数据，只要目录项
        就可以存储更多的目录项，目录页可以管理更多的叶子 Page
      - 这棵树一定是 “矮胖型” 的，说明途径的路上节点减少，找到目标数据只需要更少的 page，IO 次数更少，在 IO 层面提高了效率
      - 每一个节点都有目录项，可以大大提高搜索效率，叶子节点全都用链表级联起来
MySQL 中的数据文件是以 page 为单位保存在磁盘当中的
MySQL 的 CURD 操作都需要通过计算，从而找到对应的插入位置，或者找到对应要修改 / 查询的数据
- 涉及到计算就需要 CPU 参与，为了便于 CPU 参与，一定要能够先将数据移动到内存中
- 在特定的时间内，数据一定是磁盘中有，内存中也有
- 后续操作完内存数据后，以特定的刷新策略刷新到磁盘，这时就涉及到磁盘和内存的数据交互，此时 IO 的基本单位就是 Page
MySQL 服务器在内存中运行时，在服务器内部就申请了被称为 Buffer Pool 的大内存空间来进行各种缓存，其实就是很大的内存空间来和磁盘数据进行 IO 交互
- 尽可能的减少系统和磁盘 IO 的次数，以达到更高的效率
- 如果 5 条或者更多的数据都被保存在一个 Page 中，那么第一次 IO 查找 id=2 时，整个 Page 都会被加载到 MySQL 的 Buffer Pool 中，完成一次 IO，往后如果再查找 id=1, 3, 4, 5 等不再需要进行 IO 了，而是直接在内存中进行
虽然不能严格保证用户下次找的数据一定就在这个 Page 里，但有很大概率，因为有局部性原理
- IO 效率低下最主要矛盾不是单次 IO 数据量的大小，而是 IO 的次数

（6）聚簇索引和非聚簇索引

MyISAM 这种用户数据与索引数据分离的索引方案，叫做非聚簇索引

MyISAM 存储引擎 - 主键索引

MyISAM 引擎同样使用 B+ 树作为索引结果，叶节点的 data 域存放的是数据记录的地址

MyISAM 表的主索引，Col1 为主键：

最大的特点：将索引 Page 和数据 Page 分离

InnoDB 这种用户数据与索引数据在一起索引方案，叫做聚簇索引

MySQL 除了默认会建立主键索引以外，用户也有可能建立按照其他列信息建立的索引，一般这种索引可以叫做辅助索引或普通索引

对于 MyISAM，建立辅助索引和主键索引没有差别，无非就是主键不能重复，而非主键可重复
InnoDB 除了主键索引，用户也会建立辅助索引
- InnoDB 的非主键索引中，叶子节点并没有数据，而只有对应记录的 key 值，否则就太浪费空间了
- 通过辅助（普通）索引找到目标记录，需要两遍索引
  - 首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录
  - 这种过程叫作回表查询

4、操作

（1）主键索引

A. 创建方式

在创建表时，直接在字段名后指定 primary key：create table 表名(列名 datatype primary key, ...);
在创建表的最后，指定某列或某几列为主键索引：create table 表名(列名 datatype, ..., primary key(列名));
建表之后再创建主键：alter table 表名 add primary key(列名);

B. 主键索引的特点

一个表中最多有一个主键索引，可以是复合主键
主键索引的效率高，主键不可重复
创建主键索引的列，它的值不能为 null，且不能重复
主键索引的列基本上是 int

（2）唯一索引

A. 创建方式

在表定义时，在某列后直接指定 unique 唯一属性：create table 表名(列名 datatype primary key, 列名 datatype unique);
创建表时，在表的后面指定某列或某几列为 unique：create table 表名(列名 datatype primary key, ..., unique(列名));
创建表之后再创建索引：alter table 表名 add unique(列名);

B. 唯一索引的特点

在一个表中，可以有多个唯一索引
查询效率高
如果在某一列建立唯一索引，必须保证这列不能有重复数据
如果一个唯一索引上指定 not null，等价于主键索引

（3）普通索引

A. 创建方式

在表的定义最后，指定某列为索引：create table 表名(列名1 datatype1 primary key, 列名2 datatype2, ..., index(列名2));
创建完表以后指定某列为普通索引：alter table 表名 add index(列名);
创建一个索引名为 idx_name 的索引：create index idx_name on 表名(列名);

B. 特点

一个表中可以有多个普通索引
在实际开发中用的比较多
如果某列需要创建索引，但是该列有重复的值，那么就应该使用普通索引

（4）全文索引的创建

当对文章字段 / 有大量文字的字段进行检索时，会使用到全文索引

MySQL 提供全文索引机制，但要求表的存储引擎必须是 MyISAM 且默认的全文索引支持英文，不支持中文

如果对中文进行全文检索，可以使用 Sphinx 的中文版 —— CoreSeek

A. 使用全文索引

使用 MATCH 和 AGAINST 语法来执行全文搜索
- MATCH：指定要搜索的列
- AGAINST：指定要搜索的词 / 表达式
- select * from 表名 where MATCH(title, content) AGAINST('database');

（5）查询索引

show keys from 表名;
show index from 表名;
desc 表名;
- 信息比较简略

（6）删除索引

删除主键索引：alter table 表名 drop primary key;

其他索引的删除：alter table 表名 drop index 索引名;

索引名就是 show index from 表名; 中的 Key_name 字段

drop index 索引名 on 表名;

（7）索引创建原则

比较频繁作为查询条件的字段应该创建索引
唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件
更新非常频繁的字段不适合作创建索引
不会出现在 where 子句中的字段不该创建索引

（8）复合索引

（9）索引最左匹配原则

（10）索引覆盖

十一、事务

事务就是一组 DML 语句组成，这些语句在逻辑上存在相关性，这一组 DML 语句要么全部成功，要么全部失败，是一个整体

MySQL 提供一种机制，保证达到这样的效果
事务还规定不同的客户端看到的数据是不相同的

MySQL 服务可能会同时被多个客户端进程 / 线程访问，访问的方式以事务方式进行

事务主要用于处理操作量大、复杂度高的数据

一个 MySQL 数据库不止一个事务在运行，同一时刻甚至有大量的请求被包装成事务，再向 MySQL 服务器发起事务处理请求
每条事务至少一条 SQL，如果都能访问同样的表数据，那么在不加保护的情况下绝对会出问题，甚至会因为事务由多条 SQL 构成，那么也会存在执行到一半出错或者不想再执行的情况

1、一个完整的事务绝对不是简单的 sql 集合，还需要满足 4 个属性，即 ACID：

（1）原子性（Atomicity，或称不可分割性）

一个事务中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚到事务开始前的状态，就像这个事务从来没有执行过一样

（2）一致性（Consistency）

在事务开始前和事务结束后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作

事务执行的结果必须使数据库从一个一致性状态变到另一个一致性状态

当数据库只包含事务成功提交的结果时，数据库处于一致性状态，如果系统运行发生中断，某个事务尚未完成而被迫中断，而未完成的事务对数据库所做的修改已被写入数据库，此时数据库就处于一种不正确，即不一致的状态，因此一致性是通过原子性来保证的

一致性和用户的业务逻辑强相关，一般 MySQL 提供技术支持，但是一致性还是要用户业务逻辑做支撑，即一致性是由用户决定的

技术上通过 AID 来保证 C

多个事务的 update、insert 和 delete 之间在 RR 隔离级别下确实会发生加锁，这是为了确保数据的一致性和隔离性，而 select 通常不会与这些修改操作产生冲突，是因为它通过读写锁（锁有行锁 / 表锁） + MVCC 完成隔离性

（3）隔离性（Isolation，又称独立性）

数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致

所有事务都要有执行过程，那么在多个事务各自执行多个 SQL 时，就还有可能会出现互相影响的情况

比如多个事务同时访问同一张表，甚至同一行数据

隔离性：为了保证事务执行过程中尽量不受干扰
隔离级别：允许事务受不同程度的干扰

隔离基本都是通过锁实现的，不同的隔离级别，锁的使用是不同的

A. 常见锁

表锁
行锁
读锁
写锁
间隙锁（GAP）
Next-Key 锁（GAP+行锁）

B. 查看与设置隔离性

查看全局隔离级别：select @@global.tx_isolation;

查看会话（当前）全局隔离级别：select @@session.tx_isolation;

等价于 select @@tx_isolation;

设置当前会话 / 全局隔离级别：set [session | global] transaction isolation level {read uncommitted | read committed | repeatable read | serializable};

改变当前会话隔离级别，重启后不会受到影响，但改变全局隔离级别的话，重启后默认值会发生改变

C. 事务隔离的不同级别

a. 读未提交（Read Uncommitted）

在该隔离级别，所有的事务都可以看到其它事务没有提交的执行结果，实际生产中不可能使用这种隔离级别的
虽然效率高，但相当于没有任何隔离性，也会有很多并发问题，如脏读、幻读、不可重复读等
- 一个事务在执行中，读到另一个执行中事务的更新 / 其他操作，但是未 commit 的数据，这种现象叫做脏读（dirty read）

b. 读提交（Read Committed）

该隔离级别是大多数据库的默认的隔离级别，不是 MySQL 默认的
满足隔离的简单定义：一个事务只能看到其它已经提交的事务所做的改变
会引起不可重复读
- 在同一个事务内进行同样的读取，在不同的时间段（依旧还在事务操作中）读取到了不同的值，这种现象叫做不可重复读（non reapeatable read）
- 不可重复读的重点是修改和删除：同样的条件，读取过的数据再次读取出来发现值不一样了

c. 可重复读（Repeatable Read）

MySQL 默认的隔离级别，它确保同一个事务在执行中多次读取操作数据时，会看到同样的数据行，但是会出现幻读问题
一般的数据库在可重复读情况时，无法屏蔽其他事务 insert 的数据
隔离性实现是对数据加锁完成的，而 insert 待插入的数据因为并不存在，那么一般加锁无法屏蔽这类问题，会造成虽然大部分内容是可重复读的，但 insert 的数据在可重复读的情况被读取出来，导致多次查找时会查找出新的记录，就如同产生了幻觉，这种现象叫做幻读（phantom read）
- MySQL 在 RR 级别时是解决了幻读问题的，解决方式是用 Next-Key 锁
- 在可重复读中，MySQL 通过 MVCC + 事务第一次调用 SELECT 语句才生成快照，实现其在可重复读的隔离级别下不存在幻读问题
幻读的重点在于新增：同样的条件在第 1 次和第 2 次读出来的记录数不一样
当前读和快照读在 RR 级别下的区别
- select * from 表名 lock in share mode 以加共享锁方式进行读取，对应的就是当前读
- 事务中快照读的结果是非常依赖该事务首次出现快照读的地方，即某个事务中首次出现快照读决定该事务后续快照读结果的能力，delete 也同样如此
- 正是 Read View 生成时机的不同，从而造成 RC 和 RR 级别下快照读的结果的不同
  - 在 RR 级别下的某个事务的对某条记录的第一次快照读，会创建一个快照及 Read View 将当前系统活跃的其他事务记录起来
  - 此后在调用快照读时，使用的还是同一个 Read View，所以只要当前事务在其他事务提交更新之前使用过快照读，那么之后的快照读使用的都是同一个 Read View，所以对之后的修改不可见
  - 即 RR 级别下，快照读生成 Read View 时，Read View 会记录此时所有其他活动事务的快照，这些事务的修改对于当前事务都是不可见的，而早于 Read View 创建的事务所做的修改均是可见的
  - 而在 RC 级别下的，事务中每次快照读都会新生成一个快照和 Read View，就是在 RC 级别下的事务中可以看到别的事务提交的更新的原因
- 在 RC 隔离级别下，每个快照读都会生成并获取最新的 Read View，而在 RR 隔离级别下，则是同一个事务中的第一个快照读才会创建 Read View，之后的快照读获取的都是同一个 Read View
  - RC 每次快照读都会形成 Read View，所以 RC 才会有不可重复读问题

d. 串行化（Serializable）

这是事务的最高隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决了幻读的问题

对所有操作全部加锁，进行串行化不会有问题，但只要是串行化，效率就会很低，这种隔离级别太极端，实际生产基本不使用

它在每个读的数据行上面加上共享锁，但可能会导致超时和锁竞争

隔离级别越严格，安全性越高，但数据库的并发性能也就越低，往往需要在两者之间找一个平衡点

事务也有长短事务这样的概念

隔离级别决定了 select 是当前读还是快照读

D. 要有隔离级别的原因

事务都是原子的，所以无论如何，事务总有先有后
事务从 begin -> CURD -> commit 是有一个阶段的，也就是事务有执行前、中、后的阶段，但不管怎么启动多个事务，总是有先后的
多个事务在执行中的 CURD 操作是会交织在一起的，为了保证事务的 “有先后”，应该让不同的事务看到它该看到的内容，这就是所谓的隔离性与隔离级别要解决的问题

E. 数据库并发的场景有 3 种

a. 读-读

不存在任何问题，也不需要并发控制

MVCC（Multi-Version Concurrency Control，多版本并发控制）是一种用来解决读-写冲突的无锁并发控制

为事务分配单向增长的事务 ID，为每个修改保存一个版本，版本与事务 ID 关联，读操作只读该事务开始前的数据库的快照，所以 MVCC 可以为数据库解决几个问题
- 在并发读写数据库时，可以做到在读操作时不用阻塞写操作，写操作也不用阻塞读操作，提高了数据库并发读写的性能
- 同时还可以解决脏读、幻读、不可重复读等事务隔离问题，但不能解决更新丢失问题
每个事务都要有自己的事务 ID，可以根据事务 ID 的大小来决定事务到来的先后顺序
mysqld 可能会面临处理多个事务的情况，事务也有自己的生命周期，mysqld 要对多个事务进行管理
mysqld 中一定是对应一个 / 一套结构体对象 / 类对象，事务也要有自己的结构体

3 个记录隐藏字段

DB_TRX_ID：最近修改（修改 / 插入）事务 ID，记录创建这条记录 / 最后一次修改该记录的事务 ID
DB_ROLL_PTR：回滚指针，指向这条记录的上一个版本
- 可以理解成指向历史版本，这些数据一般在 undo log 中
DB_ROW_ID：隐含的自增 ID，如果数据表没有主键，InnoDB 会自动以 DB_ROW_ID 产生一个聚簇索引
实际上还有一个删除 flag 隐藏字段，既记录被更新或删除并不代表真的删除，而是删除 flag 变了

undo 日志

MySQL 将来是以服务进程的方式在内存中运行的
索引、事务、隔离性、日志等都是在内存中完成的，即在 MySQL 内部的相关缓冲区中保存相关数据，完成各种判断操作，然后在合适时将相关数据刷新到磁盘中
undo log 可以理解成 MySQL 中的一段内存缓冲区，用来保存日志数据

模拟 MVCC：

现在有一个事务 10 ，对 student 表中记录进行 update：将 name( 张三) 改成 name( 李四 )

事务 10 因为要修改，所以要先给该记录加行锁
修改前先将该行记录拷贝到 undo log 中，所以 undo log 中就有了一行副本数据，原理就是写时拷贝
现在 MySQL 中有两行同样的记录，先修改原始记录中的 name 为 '李四'，并且修改原始记录的隐藏字段 DB_TRX_ID 为当前事务 10 的 ID，默认从 10 开始，往后递增，而原始记录的回滚指针 DB_ROLL_PTR 列，里面写入 undo log 中副本数据的地址，从而指向副本记录，表示上一个版本就是它
事务 10 提交，释放锁

此时，最新的记录是 '李四' 那条记录

现在又有一个事务 11，对 student 表中记录进行 update：将 age(28) 改成 age(38)

事务 11 因为也要修改，所以要先给该记录加行锁
修改前，先将该行记录拷贝到 undo log 中，所以 undo log 中就又有了一行副本数据，此时新的副本采用头插方式，插入 undo log
修改原始记录中的 age 为 38，并且修改原始记录的隐藏字段 DB_TRX_ID 为当前事务 11 的 ID。而原始记录的回滚指针 DB_ROLL_PTR 列里面写入 undo log 中副本数据的地址，从而指向副本记录，既表示上一个版本就是它
事务 11 提交，释放锁

这样就有了一个基于链表记录的历史版本链，所谓的回滚无非就是用历史数据覆盖当前数据
一个个版本可以称之为一个个的快照
如果是 delete，则也是一样的，删数据不是清空，而是设置 flag 为删除即可，也可以形成版本
因为 insert 是插入，也就是之前没有数据，那么 insert 也就没有历史版本，但一般为了回滚操作，insert 的数据也是要被放入 undo log 中的
- 如果当前事务 commit 了，那么这个 undo log 的历史 insert 记录就可以被清空了
- 可以理解成 update 和 delete 可以形成版本链，insert 暂时不考虑
select 不会对数据做任何修改，所以为 select 维护多版本没有意义

当前读：读取最新记录就是当前读

增删改 update; insert; delete（排他锁）都叫做当前读
select 也有可能当前读，比如 select lock in share mode（共享锁），select for update
多个事务同时删改查时都是当前读，如果同时有 select 过来，也要读取最新版，就需要加锁，即串行化
读取的是记录的最新版本，读取时还要保证其它并发事务不能修改当前记录，会对读取的记录进行加锁

快照读：一般而言，读取历史版本就叫做快照读

读取历史版本是不受加锁限制的，也就是可以并行执行，提高了效率，快照读的实现是基于 MVCC，即 MVCC 的意义所在
既然是基于多版本，即快照读可能读到的并不一定是数据的最新版本，而有可能是之前的历史版本
快照读的前提是隔离级别而不是串行级别，串行级别下的快照读会退化成当前读
可以认为 MVCC 是行锁的一个变种，但它在很多情况下避免了加锁操作，降低了开销

Read View（读视图）

Read View 就是事务进行快照读操作时生产的读视图，在该事务执行快照读的那一刻会生成数据库系统当前的一个快照，记录并维护系统当前活跃事务的 ID

当每个事务开启时都会被分配一个 ID，这个 ID 是递增的，所以最新的事务 ID 值越大

Read View 在 MySQL 源码中就是一个类，本质是用来进行可见性判断的

当某个事务执行快照读时，对该记录创建一个 Read View，把它比作条件用来判断当前事务能够看到哪个版本的数据，既可能是当前最新数据，也有可能是该行记录的 undo log 里面的某个版本的数据

在实际读取数据版本链时，是能读取到每一个版本对应的事务 ID 的，即当前记录的 DB_TRX_ID

Read View 是事务可见性的一个类，不是事务创建出来就有的，而是当这个事务已经存在且首次进行快照读时，MySQL 形成的

b. 读-写

有线程安全问题，可能会造成事务隔离性问题，可能遇到脏读、幻读、不可重复读

c. 写-写

有线程安全问题，可能会存在更新丢失问题，比如第一类更新丢失，第二类更新丢失

可以理解成就是当前读

（4）持久性（Durability）

事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失

事务本质上是为了应用层服务的，而不是伴随着数据库系统天生就有的

在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务， MyISAM 不支持

查看数据库引擎：show engines[\G];

A. 事务提交方式

自动提交
手动提交

B. 改变自动提交模式

禁止自动提交：set autocommit=0;
开启自动提交：set autocommit=1;

查看事务提交方式：show variables like 'autocommit';

只要输入 begin / start transaction，事务就必须要通过 commit 提交才会持久化，与是否 set autocommit 无关

开始事务可以用 start transaction / begin

事务可以手动回滚，同时当操作异常，MySQL 会自动回滚

对于 InnoDB 每一条 SQL 语言都默认封装成事务，自动提交

select 有特殊情况，因为 MySQL 有 MVCC

如果一个事务被 commit 了，则不可以 rollback

可以选择回退到哪个保存点 savepoint

如果没有设置保存点，也可以回滚，但只能回滚到事务的开始，相当于直接使用 rollback，但前提是事务还没有提交

十二、视图

1、创建视图

create view 视图名 as select 语句;

2、删除视图

drop view 视图名;

视图是一个虚拟表，其内容由查询定义，同真实的表一样，视图包含一系列带有名称的列和行数据，视图的数据变化会影响到基表，基表的数据变化也会影响到视图

如果需要获取某个列名或是需要高频访问的数据，此时不再需要去查询原始表做多表查询了，直接查视图就可以了

3、视图规则和限制

与表一样，视图必须唯一命名，不能出现同名视图 / 表名
创建视图的数目没有限制，但要考虑复杂查询创建为视图后的性能影响
视图不能添加索引，也不能有关联的触发器或者默认值
视图可以提高安全性，必须具有足够的访问权限
order by 可以用在视图中，但如果从该视图检索数据 select 中也含有 order by，那么该视图中的 order by 将被覆盖，可以理解成 “就近原则”
视图可以和表一起使用

十三、用户管理

1、用户

（1）用户信息

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

host：表示这个用户可以从哪个主机登陆
- 如果是 localhost，表示只能从本机登陆
user：用户名
authentication_string：用户密码通过 password 函数加密后的
*_priv：用户拥有的权限

（2）创建用户

create user '用户名'@' 登陆主机/ip' identified by '密码';

创建用户时带上 % 就可以在 Windows 下远程访问 Linux 客户端：create user '用户名'@' %' identified by '密码';

（3）删除用户

drop user '用户名'@'主机名';

（4）修改用户密码

自己改自己密码：set password=password('新密码');
root 用户修改指定用户的密码：set password for '用户名'@'主机名'=password('新的密码')；

凡是有关密码的命令行，MySQL 不会记录下来，上下翻是找不到的

2、数据库的权限

（1）MySQL 数据库提供的权限列表

（2）给用户授权

刚创建的用户没有任何权限，需要给用户授权：grant 权限列表 on 库.对象名 to '用户名'@'登陆位置' [identified by '密码'];

权限列表，多个权限用逗号分开

grant select on ...
grant select, delete, create on ...
grant all [privileges] on ...：表示赋予该用户在该对象上的所有权限

（3）回收权限

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

十四、使用 C 语言连接

1、初始化

mysql_init(MYSQL *mysql)

2、链接数据库

mysql_real_connect(MYSQL *mysql, const char *host, const char *user, const char *passwd, const char *db, unsigned int port, const char *unix_socket, unsigned long client_flag)