MySQL学习笔记1-基础篇

公号：码农充电站pro

主页：https://codeshellme.github.io

这 5 篇文章是我在学习 MySQL 的过程中，总结的笔记：

第一篇 MySQL 学习笔记1-基础篇

1，关于 SQL

2，一条 SQL 的执行步骤

3，MySQL 存储引擎

4，数据库的基本操作

5，关于自增主键

6，SELECT 语句顺序

7，WHERE 子句

8，DISTINCT 去重

9，关于 COUNT(*) 操作

10，MYSQL 函数

11，GROUP BY 数据分组

12，子查询(嵌套查询)

13，JOIN 连接查询

14，VIEW 视图

15，存储过程

16，临时表

17，MySQL 权限管理

18，Python 操作 MySQL 的库

第二篇 MySQL 学习笔记2-进阶篇-上

19，MySQL 的基础架构

20，数据库缓冲池

21，数据库中的存储结构

22，InnoDB 中表数据的存储

第三篇 MySQL 学习笔记3-进阶篇-中

23，事务处理

24，事务的隔离级别

25，MySQL 中的锁

26，MVCC 多版本并发控制

27，MySQL 传输数据的原理

第四篇 MySQL 学习笔记4-进阶篇-下

28，Join 语句的原理

29，MySQL 如何进行排序

30，MySQL 中 kill 命令的原理

31，MySQL 中的 mysqldump 命令

32，MySQL 主从同步

33，MySQL 主备原理

第五篇 MySQL 学习笔记5-调优篇

34，关于 MySQL 索引

35，定位数据库 SQL 性能问题

1，关于 SQL

1.1，SQL 的两个标准

SQL 有两个重要的标准，分别是 SQL92 和 SQL99，它们分别代表了 92 年和 99 年颁布的 SQL 标准，今天的 SQL 语言依然遵循这些标准。

1.2，SQL 命名规范

表名、表别名、字段名、字段别名等都小写；SQL 保留字、函数名、绑定变量等都大写。

比如：

SELECT name, hp_max FROM heros WHERE role_main = '战士';

1.3，SQL 语句大小写问题

比如：

SELECT * FROM heros WHERE name = 'guanyu';
SELECT * FROM heros WHERE name = 'GUANYU';

上面两个语句，在 Oracle 中是不同的查询，而在 MySQL 中是相同的查询。

同时，可以通过修改系统参数来配置，比如在 MySQL 中可以通过参数 lower_case_table_names 来配置数据库和数据表的大小写敏感性。

2，一条 SQL 的执行步骤

一条 SQL 语句在数据库中的执行步骤如下：

在这里插入图片描述

注意在 MySQL 8.0 之后，查询缓存功能被取消。

3，MySQL 存储引擎

MySQL 的存储引擎是插件式的，在使用时可以选择不同的存储引擎。

引擎名	特点	其它
InnoDB	支持事务，表锁，行锁，外键约束等	MySQL 5.5 之后的默认引擎
MyISAM	不支持事务，不支持行锁，不支持外键等，速度快，占用资源少	MySQL 5.5 之前的默认引擎
Memory	不支持行锁，只支持表锁；数据存储在内存中，速度快，数据不能持久化	-
NDB	主要用于 MySQL Cluster 分布式集群	-
Archive	有很好的压缩机制，可用于用于文件归档	-

在 MySQL 中查看可用的存储引擎：

show engines;

结果如下：

在这里插入图片描述

InnoDB 与 Memory 的区别：

InnoDB 表的数据是有序存放的，Memory 表的数据是按照写入顺序存放的
当数据文件有空洞的时候，InnoDB 表在插入新数据的时候，为了保证数据有序性，只能在固定的位置写入新值，而 Memory 表找到空位就可以插入新值
数据位置发生变化的时候，InnoDB 表只需要修改主键索引，而 Memory 表需要修改所有索引
InnoDB 支持变长数据类型，不同记录的长度可能不同；内存表不支持 Blob 和 Text 字段，并且即使定义了 varchar(N)，实际也当作 char(N)，因此 Memory 表的每行数据长度相同

4，数据库基本操作

4.1，创建与删除数据库

CREATE DATABASE nba; -- 创建一个名为 nba 的数据库
DROP DATABASE nba;   -- 删除一个名为 nba 的数据库

4.2，创建表结构

MySQL 官方文档建议我们尽量将数据表的字段设置为 NOT NULL 约束，这样做的好处是可以更好地使用索引，节省空间，甚至加速 SQL 的运行。

创建表的语法如下：

CREATE TABLE [table_name](字段名 数据类型，......)

注意：如果创建的表没有主键，或者把一个表的主键删掉了，那么 InnoDB 会自己生成一个长度为 6 字节的 rowid 来作为主键。

示例：

CREATE TABLE player  (
  player_id int(11) NOT NULL AUTO_INCREMENT,
  player_name varchar(255) NOT NULL
);

其中：

NOT NULL PRIMARY KEY AUTO_INCREMENT 表示自增主键，NOT NULL AUTO_INCREMENT 只表示自增而非主键。
- 自增主键，在插入新记录的时候可以不指定 ID 的值，系统会获取当前 ID 最大值加 1 作为下一条记录的 ID 值。
int(11) 代表整数类型，显示长度为 11 位，括号中的参数 11 代表的是最大有效显示长度，与类型包含的数值范围大小无关。
- int 默认表示有符号整数，长度未定义的话，默认是 11；
- 无符号整数表示方法：int unsighed，长度未定义的话，默认是 10。
- 如果插入的数值大小超出了所能表示的范围，将默认插入一个临界值。
varchar(255) 代表的是最大长度为 255 个字符数的可变长字符串。
- 注意单位是字符数，一个汉字与一个英文字母都是一个字符。
- 对于 varchar(M)，M 的范围是 0 ~ 25535。
- 在定义时，M 不可省略。
- 如果超出了长度，超出的部分会被截断。
char(M)：表示固定长字符串，M 的范围是 0 ~ 255。
- 在定义时，M 可省略，默认为 1。
- 如果超出了长度，超出的部分会被截断。
- 固定长度的意思是，不管实际存储的数据是多大，都会占用固定的空间；容易造成空间浪费，但是检索性能比可变长的要好一些。
- 可变长度的意思是，会根据实际的数据的大小，去占用相应的空间，不会造成空间浪费。
float(M, N)：单精度浮点数
- N 表示小数位的位数，小数位如果超出N，则四舍五入；如果不够N 则用 0 补齐。
- M 表示整数位的位数与小数位的位数之和，如果超出 M，则存储一个边界值。
- M 和 N 都可省略，表示不限制范围。
double(M, N)：双精度浮点数
DATETIME：占 8 字节
- 表示的时间范围 1000-01-01 00:00:00/9999-12-31 23:59:59
TIMESTAMP：占 4 字节
- 表示的时间范围 1970-01-01 00:00:00/2038
DECIMAL：常用于表示金额
CHECK 约束：用来检查特定字段取值范围的有效性，CHECK 约束的结果不能为 FALSE
- 比如可以对身高 height 的数值进行 CHECK 约束，必须≥0，且＜3
- 即 CHECK(height>=0 AND height<3)

对于 int 类型的显示长度的使用，要搭配 zerofill 关键字来使用，如果不与 zerofill 一起使用，其实 int(n) 中的 n，并没有实际意义，n 是几都一样（如果数值不够 n 位，也不会用 0 补齐）。

zerofill 的使用方法：

create table t1 {
	t1 int(7) zerofill,
	t2 int(7) zerofill
};

此时如果插入的数值，不够 7 位数，则前边用 0 补齐；比如插入了 123，则显示如下：

在这里插入图片描述

从 Navicat 中导出的 SQL 语句，示例：

DROP TABLE IF EXISTS `player`;
CREATE TABLE `player`  (
  `player_id` int(11) NOT NULL AUTO_INCREMENT,
  `team_id` int(11) NOT NULL,
  `player_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `height` float(3, 2) NULL DEFAULT 0.00,
  PRIMARY KEY (`player_id`) USING BTREE,
  UNIQUE INDEX `player_name`(`player_name`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

其中：

SET = utf8 代表字符编码
COLLATE = utf8_general_ci 代表排序规则
- utf8_general_ci 代表对大小写不敏感
- 如果设置为 utf8_bin，代表对大小写敏感
- 还有许多其他排序规则
PRIMARY KEY 代表主键
- 主键起的作用是唯一标识一条记录，不能重复，不能为空，即 UNIQUE+NOT NULL
- 一个数据表的主键只能有一个
- 主键可以是一个字段，也可以由多个字段复合组成
USING BTREE 表示底层使用 BTREE 作为索引结构
- 其它的索引结构还有 HASH
UNIQUE INDEX 代表唯一索引，可以设置为其他索引方式，比如 NORMAL INDEX（普通索引）。
- 唯一索引和普通索引的区别在于，它对字段进行了唯一性的约束，索引方式使用了 BTREE

其它建表方式

-- A 的结构是 B 的精简版，A 中没有索引等信息
-- A 中的数据与 B 中的数据完全相同 
create table A as select * from B;

-- A 的结构与 B 完全相同，包括索引等其它信息
-- A 是空表，没有任何数据
create table A like B;
-- 可以使用下面语句将 B 中的数据插入 A
insert into A select * from B;

4.3，修改表结构

添加字段，在 player 表中添加 age 字段：

ALTER TABLE player ADD (age int(11));

修改字段名，将 player 表中的 age 字段名改为 player_age：

ALTER TABLE player RENAME COLUMN age to player_age

修改字段数据类型，将 player 表中的 player_age 字段的数据类型改为 float(3,1)：

ALTER TABLE player MODIFY (player_age float(3,1));

删除字段，将 player 表中的 player_age 字段删除：

ALTER TABLE player DROP COLUMN player_age;

5，关于自增主键

在 MySQL 里面，如果字段被定义为 AUTO_INCREMENT，则表示该字段自增。

5.1，查看自增值

命令 show create table table_name 可以看到下一个可用的自增值，如下：

在这里插入图片描述

5.2，自增值是如何保存的

不同的引擎对于自增值的保存策略不同：

MyISAM 引擎的自增值保存在数据文件中
InnoDB 引擎的自增值：
- 在 MySQL 5.7 及之前的版本，自增值保存在内存里，并没有持久化。每次重启后，第一次打开表的时候，都会去找自增值的最大值 max(id)，然后将 max(id)+1 作为这个表当前的自增值
- 在 MySQL 8.0 版本，将自增值的变更记录在了 redo log 中，重启的时候依靠 redo log 恢复重启之前的值

5.3，自增值的生成算法

自增值的特性如下：

如果插入数据时 id 字段指定为 0、NULL 或未指定值，那么就把这个表当前的 AUTO_INCREMENT 值填到自增字段
如果插入数据时 id 字段指定了具体的值，就直接使用语句里指定的值。假设，某次要插入的值是 X，当前的自增值是 Y：
- 如果 X<Y，那么这个表的自增值不变；
- 如果 X≥Y，就需要把当前自增值修改为新的自增值。

新的自增值生成算法是：以参数 auto_increment_offset 为初始值，以 auto_increment_increment 为步长，持续叠加，直到找到第一个大于 X 的值，作为新的自增值。

这两个参数的默认值都是 1：

当准备插入的值 >= 当前自增值，新的自增值就是“准备插入的值 +1”
否则，自增值不变

双 M 的主备架构里要求双写的时候，会设置成 auto_increment_increment=2，让一个库的自增 id 都是奇数，另一个库的自增 id 都是偶数，避免两个库生成的主键发生冲突

5.4，自增值不能保证是连续的

在这两个参数都设置为 1 的时候，自增主键 id 不能保证是连续的，有以下情况：

唯一键冲突是导致自增主键 id 不连续的第一种原因
事务回滚也会产生类似的现象，这就是第二种原因

5.5，自增 id 可能被用完

MySQL 中无符号整型 (unsigned int) 是 4 个字节，上限就是 2^32-1。

表定义的自增值达到上限后的逻辑是：再申请下一个 id 时，得到的值保持不变。这就导致，当自增 id 达到上限后，如果再插入新的值，会导致自增id 重复的错误，因此就无法再插入新的值。

因此，在建表的时候，你需要考察你的表是否有可能达到这个上限，如果有可能，就应该创建成 8 个字节的 bigint unsigned。

InnoDB 系统自增 row_id

如果创建的 InnoDB 表没有指定主键，那么 InnoDB 会创建一个不可见的，长度为 6 个字节的 row_id。

row_id 是从 0 开始到 2^48-1。达到上限后，下一个值就是 0，然后继续循环。这就会导致，当达到上限后，新的数据会覆盖旧的数据。

6，SELECT 语句顺序

关键字顺序不能颠倒：

SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ...

执行顺序：

FROM > WHERE > GROUP BY > HAVING > SELECT 的字段 > DISTINCT > ORDER BY > LIMIT

7，WHERE 子句

7.1，比较运算符

比较运算符	含义
=	等于
<> 或 !=	不等于
>	大于
<	小于
>=	大于等于
<=	小于等于
BETWEEN … AND …	在两个值之间
IS NULL	为空值

其中 BETWEEN ... AND ... 可以取到两个值的边界。

另外，在 MySQL 中空字符串 "" 与 NULL 是不同的：

空字符串代表有值，但是空字符；可以使用比较运算符进行比较。
NULL 代表无值，什么都没有，未定义；只能使用 IS NULL 来筛选。

7.2，逻辑运算符

逻辑运算符用于连接多个 where 子句：

逻辑运算符	含义
AND	并且
OR	或者
IN	在条件范围内
NOT	非

7.3，like 语句

like 语句用于模糊查询，like 根据 % 位置的不同，对索引有不同的影响：

like '%abc%' 和 like '%abc' 都无法使用索引，是全表扫描
like 'abc%' 可以使用索引

8，DISTINCT 去重

在 MySQL 中使用 distinct 去重时，distinct 的必须写在所有列的前面，也就是紧跟 select 关键字之后：

-- 对 a 列进行去重
select distinct a from table_name;

也可以对多列进行去重：

select distinct a, b, c from table_name;

此时 MySQL 会将 a,b,c 作为一个联合字段进行统一去重，而不是分别对三个字段进行了去重。

9，关于 COUNT(*) 操作

对于 count(*)，不同的 MySQL 引擎，有不同的实现方式：

MyISAM 引擎：把一个表的总行数存在了磁盘上，执行 count(*) 的时候会直接返回这个数，效率很高；
- 如果带了 where 过滤条件，就没有这么快了
- 不支持事务
InnoDB 引擎：它执行 count(*) 的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。
- 支持事务

为什么 InnoDB 不向 MyISAM 一样将行数直接记录下来呢？

这是因为，即使是在同一个时刻的多个查询，由于多版本并发控制（MVCC） 的原因，InnoDB 表“应该返回多少行”也是不确定的。

这和 InnoDB 的事务有关，可重复读是它默认的隔离级别，这是用 MVCC 来实现的。每一行记录都要判断自己是否对这个会话可见，因此对于 count(*) 请求，InnoDB 只能把数据一行一行地读出依次判断，可见的行才能够用于计算“基于这个查询”的表的总行数。

使用命令 show table status like 'tbname' 得到的结果中也有一个行数（rows），注意这个行数是不准确的，这是一个采样估算值，官方文档说误差可能达到 40% 到 50%。

几种不同的 count 用法

count(*)：总行数
count(1)：总行数
count(主键id)：总行数
count(字段名)：字段部位 NULL 的行数

性能差异：

# count(*) 是经过优化的，性能最好
count(字段名) < count(主键id) < count(1) ≈ count(*)

10，MYSQL 函数

MYSQL 函数包括内置函数和自定义函数。

内置函数可分为 5 大类：

算术函数
- ABS(n)：取绝对值
- MOD(m， n)：取余，m % n
- ROUND(字段名称，n)：四舍五入 n 个小数位
字符串函数
- CONCAT()：字符串连接
- LENGTH()：字符长度（一个汉字3 个字符长）
- CHAR_LENGTH()：字符串长度，汉字、数字、字母都算一个字符
- LOWER()：字符串小写
- UPPER()：字符串大写
- REPLACE()：字符串替换
- SUBSTRING()：字符串截取
日期函数
- CURRENT_DATE()：当前日期
- CURRENT_TIME()：当前时间
- CURRENT_TIMESTAMP()：当前日期 + 时间
- DATE()：时间的日期部分
- YEAR()：年份
- MONTH()：月份
- DAY()：天
- HOUR()：小时
- MINUTE()：分钟
- SECOND()：秒
转换函数
聚集函数
- COUNT()：总行数
- MAX()：最大值，会忽略 NULL
- MIN()：最小值，会忽略 NULL
- SUM()：求和，会忽略 NULL
- AVG()：平均值，会忽略 NULL

COUNT(*) 与 COUNT(字段名)的区别：

-- 统计所有 a 大于 100 的行
select count(*) from table_name where a > 100;

-- 统计所有 a 大于 100 且 b 不为 NULL 的行
select count(b) from table_name where a > 100;

11，GROUP BY 数据分组

数据分组使用 GROUP BY 子句，需要注意 NULL 值也会被分成一组。

示例：

-- 用字段 a 进行分组，并统计每个不同值的数量
select count(*), a from table_name group by a;

也可以对多个字段进行分组：

-- 会把 a，b 两个字段的所有取值情况都进行分组
select count(*) as num, a, b from table_name group by a, b order by num;

对分组过滤 having

WHERE 子句作用于数据行，having 子句作用域分组。

-- 筛选出 num 大于 5 的分组
select count(*) as num, a, b from table_name group by a, b having num > 5 order by num;

这里如果将 having 换成 where，则会出错。

-- 这个子句更加复杂
-- 会先筛选出所有 a > 10 的行，然后再进行分组，过滤，排序
select count(*) as num, a, b from table_name where a > 10 group by a, b having num > 5 order by num;

12，子查询(嵌套查询)

会用到下面几个关键字：

EXISTS：判断是否存在
NOT EXISTS：
IN：判断是否在集合中
NOT IN：
SOME / ANY：与子查询返回的任何值做比较，需要与比较运算符一起使用
ALL：与子查询返回的所有值做比较，需要与比较运算符一起使用

子查询分为关联子查询与非关联子查询：

关联子查询：查询子句会执行多次。
非关联子查询：查询子句只执行一次。

举例：

-- 非关联子查询
SELECT player_name, height
FROM player
WHERE height = (
	SELECT MAX(height)
	FROM player
);

-- 关联子查询
-- 查找每个球队中大于平均身高的球员有哪些，并显示他们的球员姓名、身高以及所在球队 ID。
SELECT player_name, height, team_id
FROM player a
WHERE height > (
	SELECT AVG(height)
	FROM player b
	WHERE a.team_id = b.team_id
);

EXIST 与 IN 的使用模式：

-- 下面两种含义一样
SELECT * FROM A WHERE cc IN (SELECT cc FROM B)                 -- B 表小时，用 IN 效率高
SELECT * FROM A WHERE EXIST (SELECT cc FROM B WHERE B.cc=A.cc) -- A 表小时，用 Exist 效率高

ANY 与 ALL 子句，必须与一个比较操作符一起使用：

SELECT player_id, player_name, height
FROM player
WHERE height > ANY (
	SELECT height
	FROM player
	WHERE team_id = 1002
);

SELECT player_id, player_name, height
FROM player
WHERE height > ALL (
	SELECT height
	FROM player
	WHERE team_id = 1002
);

13，JOIN 连接查询

根据不同的SQL 标准，连接查询是不同的，主要有 SQL92 和 SQL99 两种。

SQL 中的 5 中连接方式：

笛卡尔积
等值连接：连接多个表的条件是等号
非等值连接：连接多个表的条件不是等号
外连接
- 左外连接：LEFT JOIN … ON …
- 右外连接：RIGHT JOIN … ON …
- 全外连接：FULL JOIN … ON … （MYSQL 不支持）
自连接

AS 为表名重命名是 SQL92 语法，SQL99 中不需要用 AS，使用空格就行。 LEFT JOIN … ON 是 SQL99 语法，SQL92 中没有该语法。 LEFT 与 RIGHT 表示主表在哪边。

SQL 举例：

-- 笛卡尔积 SQL92 语法
SELECT * FROM player, team;
-- 笛卡尔积 SQL99 语法
SELECT * FROM player CROSS JOIN team;
SELECT * FROM t1 CROSS JOIN t2 CROSS JOIN t3; -- 三张表笛卡尔积

-- 等值连接 SQL92 语法
SELECT player_id, player.team_id, player_name, height, team_name
FROM player, team
WHERE player.team_id = team.team_id;
-- 等值连接 SQL99 语法
SELECT player_id, team_id, player_name, height, team_name
FROM player
	NATURAL JOIN team

-- 非等值连接 SQL92 语法
SELECT p.player_name, p.height, h.height_level
FROM player AS p, height_grades AS h
WHERE p.height BETWEEN h.height_lowest AND h.height_highest;
-- 非等值连接 SQL99 语法
SELECT p.player_name, p.height, h.height_level
FROM player as p JOIN height_grades as h
ON height BETWEEN h.height_lowest AND h.height_highest

-- 外连接 SQL99 语法
SELECT * 
FROM player 
LEFT JOIN team on player.team_id = team.team_id;

-- SQL99 USING 连接，USING 用于指定两张表中的同名字段
SELECT player_id, team_id, player_name, height, team_name
FROM player
	JOIN team USING (team_id)
-- 这两个 SQL 含义相同
SELECT player_id, player.team_id, player_name, height, team_name
FROM player
	JOIN team ON player.team_id = team.team_id

14，VIEW 视图

在 MySQL 里，有两个“视图”的概念：

一个是 view。它是一个用查询语句定义的虚拟表，是对 select 语句的封装，本身不具有数据；在调用的时候执行查询语句并生成结果。
- 创建视图的语法是 create view … ，而它的查询方法与表一样。
另一个是 InnoDB 在实现 MVCC 时用到的一致性读视图，即 consistent read view，用于支持读提交和可重复读隔离级别的实现。
- 它没有物理结构，作用是事务执行期间用来定义“我能看到什么数据”。

创建视图：

CREATE VIEW view_name AS
SELECT column1, column2
FROM table
WHERE condition

当视图创建之后，它就相当于一个虚拟表，可以直接使用：

SELECT * FROM view_name;

更新视图（更改一个已有的视图）：

ALTER VIEW view_name AS
SELECT column1, column2
FROM table
WHERE condition

删除视图：

DROP VIEW view_name

15，存储过程

存储过程是对 SQL 语句的封装，是程序化的SQL，也就是将 SQL 写成函数（过程）的形式。存储过程由 SQL 语句和流控制语句共同组成。

定义一个存储过程：

-- 创建存储过程
CREATE PROCEDURE 存储过程名称([参数列表])
BEGIN
    需要执行的语句
END    

-- 修改存储过程
ALTER PROCEDURE

-- 删除存储过程
DROP PROCEDURE

例如：

DELIMITER //
CREATE PROCEDURE `add_num`(IN n INT)
BEGIN
       DECLARE i INT;
       DECLARE sum INT;
       
       SET i = 1;
       SET sum = 0;
       WHILE i <= n DO
              SET sum = sum + i;
              SET i = i +1;
       END WHILE;
       SELECT sum;
END //
DELIMITER ;

-- 调用存储过程
CALL add_num(50);

其中 DELIMITER // 用于定义结束符，在存储过程结束后，要还原 DELIMITER ;。

SET 用于对变量进行赋值。

注意在使用 Navicat 时，不需要使用 DELIMITER。

参数修饰符，IN OUT INOUT 的含义：

在这里插入图片描述

示例：

CREATE PROCEDURE `get_hero_scores`(
       OUT max_max_hp FLOAT,
       OUT min_max_mp FLOAT,
       OUT avg_max_attack FLOAT,  
       s VARCHAR(255)
       )
BEGIN
       SELECT MAX(hp_max), MIN(mp_max), AVG(attack_max) FROM heros WHERE role_main = s INTO max_max_hp, min_max_mp, avg_max_attack;
END

-- 调用存储过程
CALL get_hero_scores(@max_max_hp, @min_max_mp, @avg_max_attack, '战士');
SELECT @max_max_hp, @min_max_mp, @avg_max_attack;

其中 SELECT ... INTO 是将查询结果为变量赋值。

存储过程的缺点

可移植性差
调试困难，不易维护

16，临时表

临时表不同于视图（虚拟表），临时表示真实存在的数据表，只是不会长期存在，它只为当前连接存在，连接关闭后，临时表就释放了。

临时表与内存表的区别：

内存表：特指用 Memory 引擎的表
- 建表语法是 create table … engine=memory
- 这种表的数据都保存在内存里，系统重启的时候会被清空，但是表结构还在
临时表：可以使用各种引擎类型
- 如果使用 InnoDB / MyISAM 引擎，写数据的时候是写到磁盘上的
- 临时表也可以使用 Memory 引擎
- 建表语法是 create temporary table …

一个创建临时表的事例：

在这里插入图片描述

临时表的特征：

一个临时表只能被创建它的 session 访问，对其他线程不可见。（所以，图中 session A 创建的临时表 t，对于 session B 就是不可见的）
- 由于临时表只能被创建它的 session 访问，所以在这个 session 结束的时候，会自动删除临时表
临时表可以与普通表同名
session A 内有同名的临时表和普通表的时候，show create 语句，以及增删改查语句访问的是临时表
show tables 命令不显示临时表

在实际应用中，临时表一般用于处理比较复杂的计算逻辑。

上面介绍到的临时表都是用户自己创建的，也称为用户临时表。

17，MySQL 权限管理

创建用户命令：

create user 'ua'@'%' identified by 'pa';

这条语句的逻辑是，创建一个用户 'ua'@'%'，密码是 pa。

在 MySQL 里面，用户名 (user)+ 地址 (host) 才表示一个用户，因此 ua@ip1 和 ua@ip2 代表的是两个不同的用户。

这条命令做了两个动作：

磁盘上：往 mysql.user 表里插入一行，由于没有指定权限，所以这行数据上所有表示权限的字段的值都是 N；
内存里：往数组 acl_users 里插入一个 acl_user 对象，这个对象的 access 字段值为 0。

下图是此时用户 ua 在 user 表中的状态：

在这里插入图片描述

在 MySQL 中，用户权限是有不同的范围的：

全局权限
db 权限
表权限和列权限

17.1，全局权限

全局权限作用于整个 MySQL 实例，这些权限信息保存在 mysql 库的 user 表里。

要给用户 ua 赋一个最高权限的话，语句是这么写的：

grant all privileges on *.* to 'ua'@'%' with grant option;

这个 grant 命令做了两个动作：

磁盘上：将 mysql.user 表里，用户 ’ua’@’%' 这一行的所有表示权限的字段的值都修改为‘Y’；
内存里：从数组 acl_users 中找到这个用户对应的对象，将 access 值（权限位）修改为二进制的“全 1”。

注意：一般在生产环境上要合理控制用户权限的范围。如果一个用户有所有权限，一般就不应该设置为所有 IP 地址都可以访问。

revoke 命令用于回收权限：

revoke all privileges on *.* from 'ua'@'%';

revoke 命令做了如下两个动作：

磁盘上：将 mysql.user 表里，用户’ua’@’%‘这一行的所有表示权限的字段的值都修改为“N”；
内存里：从数组 acl_users 中找到这个用户对应的对象，将 access 的值修改为 0。

17.2，库级权限

如果要让用户 ua 拥有库 db1 的所有权限，可以执行下面这条命令：

grant all privileges on db1.* to 'ua'@'%' with grant option;

基于库的权限记录保存在 mysql.db 表中，在内存里则保存在数组 acl_dbs 中。

这条 grant 命令做了如下两个动作：

磁盘上：往 mysql.db 表中插入了一行记录，所有权限位字段设置为“Y”；
内存里：增加一个对象到数组 acl_dbs 中，这个对象的权限位为“全 1”。

下图是此时用户 ua 在 db 表中的状态：

在这里插入图片描述

每次需要判断一个用户对一个数据库读写权限的时候，都需要遍历一次 acl_dbs 数组，根据 user、host 和 db 找到匹配的对象，然后根据对象的权限位来判断。

17.3，表权限和列权限

MySQL 支持更细粒度的表权限和列权限：

表权限定义存放在表 mysql.tables_priv 中
列权限定义存放在表 mysql.columns_priv 中
这两类权限，组合起来存放在内存的 hash 结构 column_priv_hash 中

示例：

# 创建一个表
create table db1.t1(id int, a int);

# 赋予表权限
grant all privileges on db1.t1 to 'ua'@'%' with grant option;

# 赋予列权限
GRANT SELECT(id), INSERT (id,a) ON mydb.mytbl TO 'ua'@'%' with grant option;

这两个权限每次 grant 的时候都会修改数据表，也会同步修改内存中的 hash 结构。

18，Python 操作 MySQL 的库

mysql-connector
MySQLdb
mysqlclient
PyMySQL
peewee：一个轻量级的 ORM 框架
SQLAIchemy：一个 ORM 框架

专注编程技术分享

MySQL学习笔记1-基础篇

目录

1，关于 SQL

1.1，SQL 的两个标准

1.2，SQL 命名规范

1.3，SQL 语句大小写问题

2，一条 SQL 的执行步骤

3，MySQL 存储引擎

4，数据库基本操作

4.1，创建与删除数据库

4.2，创建表结构

4.3，修改表结构

5，关于自增主键

5.1，查看自增值

5.2，自增值是如何保存的

5.3，自增值的生成算法

5.4，自增值不能保证是连续的

5.5，自增 id 可能被用完

6，SELECT 语句顺序

7，WHERE 子句

7.1，比较运算符

7.2，逻辑运算符

7.3，like 语句

8，DISTINCT 去重

9，关于 COUNT(*) 操作

10，MYSQL 函数

11，GROUP BY 数据分组

12，子查询(嵌套查询)

13，JOIN 连接查询

14，VIEW 视图

15，存储过程

16，临时表

17，MySQL 权限管理

17.1，全局权限

17.2，库级权限

17.3，表权限和列权限

18，Python 操作 MySQL 的库