技术阅读-《MySQL 必知必会》

2019 年 10 月 5 日
筆記

前一阵子参与一个技术社群的打卡读书活动，花了将近十天读完，每天阅读三章，才有了这篇阅读笔记，主要以书中知识点为主。这是本 MySQL 经典的入门书籍，对 MySQL 方面的知识查漏补缺还是不错的。后续有机会选择更加深入讲解MySQL的书读一下，好加强对 MySQL 的理解和使用。

第一章了解SQL第二章 MySQL 介绍第三章使用 MySQL第四章检索数据第五章排序检索数据第六章过滤数据第七章数据过滤第八章通配符过滤第九章正则搜索第十章创建计算字段第十一章数据处理函数第十二章汇总数据第十三章数据分组第十四章使用子查询第十五章联结表第十六章高级联结第十七章组合查询第十八章全文本搜索第十九章插入数据第二十章更新和删除数据第二十一章表的增删改第二十二章视图第二十三章存储过程第二十四章游标第二十五章使用触发器第二十六章事务处理第二十七章全球化和本地化第二十八章安全管理第二十九数据库维护第三十章改善性能

第一章了解SQL

数据库基础

什么是数据库？

保存有组织的数据的容器
数据库软件 DBMS 常常被错误认为这就是数据库。

一个能存放数据，并且检索的地方，类比现实中存放货物的仓库，这些货物就是数据。

什么是表？

某种特定类型数据的结构化文件。
名称唯一

存储同一类数据的地方，类比存钱罐，存放相同结构，不同面值的硬币。

Schema/模式: 数据库的同义词。

列：组成表的字段。

将把一个表想成一个网格，网格里每一列存储数据都是属于该字段下的,每列相互独立。

每列都有对应的数据类型，就是说每个字段都有数据类型限制，这个类型能帮助我们排序数据和检索数据。行：表的数据按行存储，每行存放特定的一条记录。

将表想象成网格，水平的一行就是表的行。

行数表示着记录的总数。行 == 记录

主键：具有唯一标识的列，它的值能唯一区分表里的每一行，起到唯一行的作用。主键特点:

行之间主键的值不能重复
每个行都必须有一个主键值，不能为 NULL

好比身份证，唯一且必须有。

主键不仅限于一列，可以让多个列形成一个主键，也叫做复合主键。

SQL 基础

全称：结构化查询语言 Structured Query Language , 用来与数据库通信的语言。 SQL 设计优势：

简单易学
通用灵活

第二章 MySQL 介绍

MySQL 数据库软件的一种，负责数据的存储，检索，管理和处理。

开源免费成本低
性能高
简单易用缺点在于缺少其他 DBMS 提供的功能和特性。 MySQL 采用 C/S 架构，服务端程序负责数据处理的请求，而一般请求都由客户端通过网络发起。

MySQL C/S 架构下可以支持丰富的客户端工具和开发语言来与数据库加护，目前 JavaScript 也可以做到了：Node.js 提供了 mysql 模块。

如何获取 mysql 命令帮助 mysql –help

第三章使用 MySQL

连接数据库四要素

主机名，如 127.0.0.1
端口, 默认 3306
用户名
密码，可选

选择数据库使用 USE 关键字 USE databaseName 只有选择了指定数据库，才能读取其中的数据。

查看数据库,表，字段信息使用 SHOW 关键字查看所有数据库 SHOW DATABASES 查看一个数据库下的所有表 SHOW TABLES 查看一个表下所有列字段 SHOW COLUMNS FROM aTable 更多 SHOW 用法可以使用 HELP 命令查看。 HELP ‘SHOW'

必须带单引号或者双引号，否则提示语法错误。

第四章检索数据

查询 SELECT

使用 SELECT 查数据时要指定查什么（字段），从哪查(库.表)

注意点：

SQL 不区分大小写，但是 SQL 关键字尽量大写，这样好阅读和区分
SQL 忽略空格

使用通配符* 可以表示返回所有列，但是为了检索效率考虑，尽量不要使用 *

去重 DISTINCT

需要检索不同的行数据时需要使用到 DISTINCT 关键字，使得执行 SQL 查询时只返回不同的值。

SELECT DISTINCT ....

注意：

如果多个列修饰了 DISTINCT，必须要每列不同，否则都会被查出来。

限制结果 LIMIT

使用 LIMIT 可以限制查询返回的行数。

SELECT x FROM a_table LIMIT 1

也可以指定需要查询的开始行数和行数。

SELECT * FROM a_table LIMIT 2,3

LIMIT 第一个参数为开始位置，第二参数为要查询的行数。

索引位置从 0 开始计算，LIMIT 1,1 查询的是第一条。

表的完全限定名

SQL 可以使用表名.列名的形式引用列，表示唯一的列。

SELECT a_table.x FROM a_table

第五章排序检索数据

**排序数据 ORDER BY **

关系数据库设计理论：如果不明确规定排序顺序，不应该假定检索的数据的顺序有意义。

默认为正序排列，从小到大

SELECT x FROM a_table ORDER BY x;

若要按照多个列排序，只要在 ORDER BY后指定多列，逗号隔开即可；下面语句将结果先按照列 x 排序，再按列 y 排序。

SELECT * FROM a_table ORDER BY x,y

需要让查询结果降序排列时，从大到小，可以在 ORDER BY 最后使用 DESC 关键字

SELECT x FROM a_table ORDER BY x DESC;

在 MySQL的字典排序规则中 A 被视为与 a 相同。

ORDER BY 必须在 FROM 后面，而使用 LIMIT 时必须在 ORDER BY 后面。

第六章过滤数据

条件查询 WHERE

在 SELECT 查询中可以使用 WHERE 指定搜索条件进行过滤，WHERE 紧跟 FROM 后面。

SELECT x,y FROM a_table WHERE x = z;

搜索的常见操作符

=
<> 等价于 !=
<
<=
>
>=
BETWEEN 用于范围查询，数字或者日期

空值检查

NULL 表示无值，当要查询值是 NULL 的列时，需要使用 IS NULL 语句判断，而并不是 = NULL

SELECT x,y FROM a_table WHERE y IS NULL

第七章数据过滤

逻辑操作符 + WHERE

且逻辑 – AND 操作符，查询满足所有条件的行

或逻辑 – OR 操作符，查询满足任一条件的行

顺序问题：

SQL 优先处理 AND 操作符，再处理 OR 操作符，当存在不同操作符时最好使用圆括号对操作符相关的语句明确分组。

SELECT x FROM a_table WHERE (y = ? OR z =  ?) AND r = ? ;

范围操作符 IN

用于指定范围的检索，满足括号里范围值的行都可以被查到。

SELECT x FROM a_table WHERE y IN (y1,y2,y3);

一定场景下，使用 IN 可以代替 OR 操作符：

语句更加直观清楚
执行速度更快
可以内嵌其他 SELECT 语句，更灵活

非逻辑 NOT

检索否定条件的数据，NOT 否定跟在它之后的条件。

支持与 IN，BETWEEN，EXISTS子语句配合使用，表示范围条件的取反。

SELECT x FROM a_table WHERE y NOT     IN (y1,y2,y3);

第八章通配符过滤

百分比通配符 %

% 表示任何字符出现任意次数，并且搜索内容区分大小写；但是不能匹配 NULL 的数据。

SELECT x FROM a_table WHERE y LIKE "z%";

下划线通配符 _

_ 只匹配单个字符

SELECT x FROM a_table WHERE y LIKE "z_";

使用通配符的建议

尽量减少使用，搜索时间更长
尽量不要以通配符开头进行搜索，这样最慢。

第九章正则搜索

正则匹配的效率不高，但是它能精准地控制匹配元素规则。

基本字符匹配

使用 REGEXP 关键字 ，后面的东西作为正则表达式

SELECT * FROM a_table WHERE x REGEXP 'expression';

使用正则匹配时默认不区分大小写，如果需要区分，可使用 BINARY 关键字： REGREXP BINARY 'expression'。

或匹配 |

在正则表达式中使用 | ，匹配多个条件

SELECT * FROM a_table WHERE x REGEXP 'expression1|expression2';

多字符匹配 [x]

匹配任何单一字符,允许多个，用 [] 包含起来。

SELECT * FROM a_table WHERE x REGEXP '[yz]';

范围匹配

匹配多个字符时，当具有顺序时可以使用 – 定义范围,如 [0-9] 表示匹配从 0 到 9 的数，同样地，[a-z]匹配任意字母字符。

SELECT * FROM a_table WHERE x REGEXP '[0-9]';

特殊字符匹配

匹配特殊字符时，需要使用\作为转义再进行匹配。

SELECT * FROM a_table WHERE x REGEXP '\.';

若要匹配反斜杠，则需要写成 \

匹配多实例

需要对正则匹配的数目控制时，应该使用重复元字符。

常见有 6 种

* 0或多个
+ 一个或多个
? 0个或多个
{n} n 个
{n,} 至少 n 个
{n,m} 数目在 n 到 m 之间

SELECT * FROM a_table WHERE x REGEXP 'y{1,2}';

定位符

正则允许匹配特定位置的内容，使用定位符

^ 开始位置
$ 结尾位置
[[:<:]] 词的开始
[[:>:]] 词的结尾

SELECT * FROM a_table WHERE x REGEXP '^y';

第十章创建计算字段

什么是计算字段

需要对查询的列数据进行处理，如求和，平均，格式化等

拼接字段 CONCAT 函数

使用 CONCAT 函数可以将多个值拼接成一个值。

SELECT CONCAT(x,y) FROM a_table;

去除空格的三个函数

Trtim()
RTrim()
LTrim()

别名 AS

别名就是表示一个字段所定义的另一种名称。

使用 AS 关键字定义别名。

SELECT x AS x1 FROM a_table

算术计算

对查询的数据进行加减乘除匀速，使用通常的算术运算符即可：+，-，*，/

第十一章数据处理函数

文本函数

Trim 系列函数，去空格
Upper 函数/ Lower 函数，对字符串大小写转换
SubString 函数，返回子字符串

时间日期函数

AddDate/AddTime 函数，增加日期，时间
CurDate/CurTime 函数，返回当前日期，时间
Date_Format 函数，返回格式化的日期时间
DateDiff 返回两个日期之差
Date 函数，获取日期
Time 函数，获取时间
Now 函数，当前日期时间
…

数值处理函数

Abs 函数，返回绝对值
Mod 函数，返回除后的余数
Rand 函数，返回一个随机数

第十二章汇总数据

通常使用 MySQL 提供的汇聚函数比自己获取到在客户端里计算的效率更高，但仅对简单的查询汇总操作来说。

聚集函数

对查询出来的数据进行汇总统计

AVG 函数，返回一列的平均值
COUNT 函数，返回一列的行数，使用 COUNT(*) 时包含该列值为 NULL 的行，否则不在统计范围内
MIN/MAX 函数，返回一列的最小值/最大值
SUM 函数，返回一列的最大值

聚集不同值

ALL 返回所有行数，默认行为
DISTINCT 只返回包含不同的值

组合聚集函数

在一个查询语句允许采用多个函数。

SELECT COUNT(*), MIN(x),MAX(y),AVG(z) FROM a_table;

第十三章数据分组

分组就是对每个组的数据进行聚集计算，使用关键字 **GROUP BY **实现

SELECT x,COUNT(*) FROM a_table GROUP BY x;

GROUP BY 语句必须出现在WHERE语句之后，ORDER BY语句之前，并且不能使用别名。

除此之外，处理聚集计算语句之外，SELECT语句中的每个列都必须在GROUP BY语

句中给出，否则会报错。

过滤分组 HAVING

MySQL 支持基于分组之上的条件过滤，WHERE 过滤的只是行，而 HAVING 过滤分组。

SELECT x,COUNT(*) FROM a_table GROUP BY x HAVEING COUNT(*) >= y;

并且HAVING支持所有WHERE操作符, WHERE在数据

分组前进行过滤，HAVING在数据分组后进行过滤。

分组和排序

一般使用 GROUP BY 语句时，也会使用 ORDER BY 语句，保证数据正确排序。

SELECT x,COUNT(*) FROM a_table GROUP BY x ORDER BY x;

SELECT 语句的执行顺序

SELECT
FROM
WHERE
GROUP BY
ORDER BY
LIMIT

第十四章使用子查询

什么是子查询？

嵌套在其他查询中的查询语句。

SELECT x FROM a_table WHERE x IN (SELECT y FROM b_table);

子查询总是从内向外处理，优先处理内部的查询，再执行最后的查询。

虽然子查询的嵌套数目没有限制，不过在实际使用时由于性能的限制，不应该嵌套太多的子查询。

相关子查询

涉及外部查询的子查询,利用子查询获得外部表的指定字段。

SELECT x,(SELECT y FROM b_table WHERE z = x) AS v  FROM a_table;

当列名可能有多义性时，就必须使用表名和列名由一个句点分隔的语法，防止冲突。

第十五章联结表

联结就是在正对多个表，进行SQL查询时的组合。能更有效地查询多表数据。

SELECT a,b FORM a_table,b_table WHERE a_table.x = b_table.x;

联结的创建通过 = 在 WHERE 语句中将两个表相同含义的字段关联起来，达到关联表的作用。

笛卡尔积

两个没有联结条件的表返回的结果, 检索出的行的数目将是第一个表中的行数乘以第二个表中的行数。

SELECT a,b FORM a_table,b_table;

内部联结

基于两个表之间的相等联结，使用 INNER JOIN 指定需要联结的表，而用 ON 指定条件条件。

SELECT a,b FORM a_table,b_table INNER JOIN a_table.x = b_table.x;

MySQL在运行时的联结操作处理很耗费资源，减少过多表的联结，以免查询效率下降。

第十六章高级联结

SQL 允许给字段，列名起别名之外，还允许给表名起别名，这样可以进一步简化SQL 语句，当 SELECT 语句不止一次引用相同的表时尤为有用。

SELECT * FROM a_table a WHERE a.x = X;

自联结

当单句 SELECT 语句中 FROM 引用了一个表两次时使用，为了避免歧义可以采用给表取别名，然后使用

SELECT a.a1,a2.a2 FROM a_table a1, a_table a2 WHERE a1.x = a2.x AND a1.y = ?

自然联结

对表进行联结时，至少有一列出现在不止一个表中，并且返回时不会重复展示，每列只返回一次。

外部联结

左外联结 LEFT OUTER JOIN

右外联结 RIGHT OUTER JOIN

OUTER 关键字可以省略。

关联顺序决定了是左联结，还是右联结，其实是可以相互转换的，使用哪个联结由哪个更方便而定。

使用联结的要点：

进行联结时要使用正确的联结条件，避免笛卡尔积
一个联结可以包含多个表，一个语句中也允许存在多个联结

第十七章组合查询

组合查询

将多个查询语句的结果作为单个查询结果局进行返回

使用场景：

在单个查询中从不同的表返回一样结构的数据
在单个表执行多个查询，按照单个查询返回数据

如何使用组合查询

用 UNION 操作符组件多个 SQL 查询语句。

SELECT a FROM a_table  UNION  SELECT b FROM b_table;

UNION 使用规则

UNION 必须由两条以上的 SELECT 语句组成，语句间用 UNION 分隔
UNION 每个查询都要返回相同列数的数据
使用 UNION 组合查询时默认去除了重复行数，若需要返回所有行数，则使用 UNION ALL

组合查询的排序

使用组合查询时，只要在语句最后加上排序规则，即可排序所有语句返回的结果。

第十八章全文本搜索

需要全文本搜索时，列的数据类型应为 FULLTEXT，MySQL 会针对 FULLTEXT 列创建索引。

然后使用Match() 和 Against() 执行全文本搜索。

SELECT text FROM a_table WHERE Match(text) Against('keywords');

使用全文本搜索的注意点

只有在 MyISAM 数据库引擎下才支持全文本搜索

第十九章插入数据

INSERT 新增行数据

// 用法一  INSERT INTO a_table VALUES(x1,y1,z1)    // 用法二  INSERT INTO a_table(x,y,z) VALUES(x1,y1,z1)    // 用法三  INSERT INTO a_table(x,y,z) VALUES(x1,y1,z1),(x2,y2,z2),(x3,y3,z3)

INSERT 执行后不会输出任何信息。

INSERT 时尽量指明列名进行数据插入，这样不依赖顺序，新增字段也没影响。

INSERT LOW_PRIORITY INTO 可以降低INSERT语句的优先级。

除了一般的插入操作，还可以将查询出的数据进行插入。

INSERT INTO a_table(a_x,a_y,a_z) SELECT b_x,b_y,b_z FROM b_table;

第二十章更新和删除数据

UPDATE 更改行数据

安全起见，必须带条件。

UPDATE语句还可以使用子查询。

IGNORE 关键字可以指定某个更新操作不成功时继续执行其他更新操作

DELETE 删除行数据

也必须带条件操作，防止误删数据。

MySQL 提供更快的清楚表数据的方式：TRUNCATE TABLE, 实际操作为删除原来的表然后新建。

操作更新/删除的重要步骤：

不要无查询条件的更新、删除
使用 UPDATE、DELETE 之前先用 SELECT 语句查询测试 WHERE 条件的正确性

第二十一章表的增删改

CREATE TABLE 创建表

即使使用了MySQL GUI 工具新增表，底层还是使用了 CREATE TABLE

image-20190610213850359

新增表时，MySQL 不会对应有的表进行覆盖操作，会提示错误。

一般新建时在表名前添加 IF NOT EXISTS 。

创建主键：PRIMARY KEY(id)

创建自增序号：AUTO_INCREMENT (每个表只能有一列自增，并且必须有索引)

使用默认值：DEFAULT value

设置数据库引擎类型：ENGINE=InnoDB

InnoDB是支持事务处理引擎，其他都不支持
MEMORY 将数据存储在内存中
MyISAM 性能极高，支持全文本搜索
不同的表可以用不同的数据库引擎，但是使用外键时两个表的数据库引擎必须一致

ALTER TABLE 更新表

只能操作存在的表，否则报错。

新增字段

ALERT TABLE table  ADD  x int;

删除字段

ALERT TABLE table  DROP y;

表结构的更改要小心，应该进行备份操作，避免数据丢失。

DROP TABLE 删除表

只会删除存在的表，没有确认和撤销的操作。

RENAME TABLE 重命名表

对表进行重命名，允许多个表同时操作

RENAME TABLE a_table TO x_table,b_table TO y_table;

第二十二章视图

抛出问题：视图具体是什么，为什么在日常开发见不到呢？

书中指出 视图是虚拟的表，包含的数据是动态的。

使用视图的几个场景

重用 SQL
维护数据安全
更改数据格式和展示方式
对应复杂场景的业务处理

使用规则

命名唯一，不能跟已有表名冲突
隐藏复杂的 SQL，查询语句简化
创建视图需要授权
视图不能索引

缺陷就是大量使用视图会降低查询性能，所以不推荐使用。

具体用法

CREATE VIEW a_view AS  SELECT x,y,z  FROM a_table    SELECT x FROM a_view WHERE y = ?

视图的更新

视图主要用来数据查询，更新频繁的情况不推荐使用。

第二十三章存储过程

抛出问题：使用存储过程有哪些好处

什么是存储过程：

通过保存一条或多条 SQL 语句，进行批处理执行。

好处

封装复杂的操作
安全
使用存储过程比使用单独的 SQL 快

缺点

更复杂，对编写者要求高

用法： CALL 执行存储过程

CALL a_process(@a,@b,@c)

@为使用存储过程参数的方式，MySQL 变量都以 @ 开始，包括存储过程中的变量。

存储过程的创建

CREATE PROCEDURE a_process()  BEGIN      SELECT Avg(x) AS a      FROM a_table;  END;

如果需要接受参数，则在 ()中指明。

存储过程删除

DROP PROCEDURE IF EXISTS a_process;

存储过程的参数

OUT 标记返回值，IN 标记输入值，使用 INTO 给变量赋值。

CREATE PROCEDURE a_process(OUT a)  BEGIN      SELECT Avg(x)      INTO a      FROM a_table;  END;

局部变量声明 DECLAR

在编写存储过程中，需要定义局部变量时，可以使用 DECLAR 关键字。

DECLAR a INT DEFAULT 0

局部变量只能在 BEGIN 和 END 之间定义和使用。

检查存储过程

具体展示创建的时间，创建人等信息。

SHOW CREATE PRODURE a_process;

第二十四章游标

MySQL里的游标指什么，跟迭代器里的一样的概念吗？

针对查询出的行数据的位置进行存储，用于浏览指定的行数据，类似下一页效果。

MySQL 游标只能用于存储过程和函数中

游标使用步骤：

事前定义 CREATE PROCEDURE a_processor BEGIN DECLARE a_cursor CURSOR FOR SELECT a FROM a_table END;
打开游标 OPEN a_cursor;
使用游标检索需要的数据 FETCH a_cursor INTO c 将游标指向的数据赋值给局部变量 c
关闭游标 CLOSE a_cursor; 不手动关闭，MySQL 还在 END 时自动关闭。

一般游标配合数据的重复循环处理，MySQL 循环使用 REPEAT-UNTIL。

第二十五章使用触发器

什么是触发器？

在某个表数据发生变更时自动执行一条 MySQL 语句的东西。

只有 DELETE，INSERT，UPDATE 语句支持触发器。

创建触发器 CREATE TRIGGER

提供唯一的名称，关联的表和操作行为(DELETE，INSERT，UPDATE)，设置执行时机。

只有表才支持触发器，视图，临时表都不支持。

删除触发器 DROP TRIGGER

触发器不能更新，覆盖，只能先删除再创建。

使用触发器

CREATE TRIGGER a_trigger AFTER/BEFORE INSERT / DELETE / UPDATE ON a_table FOR ...

触发器的好处

固定执行，透明处理
创建跟踪记录

第二十六章事务处理

提问：MySQL 是怎么管理事务的？

事务处理保证了多个 MySQL 操作要么完全执行，要么完全不执行，保证原子性操作。

常见名词

事务 Transaction
回退 rollback
提交 commit
保留点 savepoint 事务处理中设置的占位符，用于部分回退

可以回滚的语句

INSERT
UPDATE
DELETE

当COMMIT或ROLLBACK语句执行后，事务会自

动关闭

使用保留点

目的支持回退部分事务，在事务处理块中合适的未知设置 SavePoint

SAVEPOINT a_point  ROLLBACK TO a_point  RELEASE SAVEPOINT a_point

保留点在事务处理完成（执行一条ROLLBACK或COMMIT）后自动释放

默认的MySQL行为是自动提交所有更改，如果需要设置手动提交: SET autocommit=0;

第二十七章全球化和本地化

MySQL 怎么实现国际化呢

为了能对不同语言字符集能够排序，MySQL 提供了多种字符集。

查看字符集校对顺序

SHOW COLLATION;

手动指定字符集和校对的表或者列

CREATE TABLE a_table  (col1 INT,       col2 VARCHAR,       col3 VARCHAR CHARACTER SET latin1 COLLATE latin1_general_ci  ) DEFAULT CHARACTER SET hebrew COLLATE latin1_general_ci

CHARACTER 指定一个字符集
COLLATE 指定一个校对顺序

第二十八章安全管理

为什么需要安全管理

访问控制，就是给用户提供他们所需的访问权。

日常 MySQL操作中不应该使用 root 账户。

管理用户

USE mysql;  SELECT user FROM users;

新建用户

CREATE USER a_user IDENTIFIED BY 'password'

重命名用户

RENAME USER a_user TO b_user;

删除用户

DROP USER a_user;

查看访问权限

SHOW GRANTS FOR a_user;

USAGE ON *.* 表示没有任何权限

赋予权限

GRANT SELECT ON a_db.* TO a_user;

撤销权限

REVOKE SELECT ON a_db.* FROM a_user;

如何设置密码

SET PASSWORD FOR a_user = Password('password');

第二十九数据库维护

如何有效维护数据库的数据

备份数据方案

mysqldump 备份转储文件
mysqlhotcopy 从数据库赋值所有数据，部分数据库引擎不支持
BACKUP TABLE/SELECT INTO OUTFILE 转储外部文件

分析表状态

ANALYZE TABLE a_table;

查看表信息

CHECK TABLE a_table;

常用 mysqld 命令

–help
–safe-mode
–verbose 显示全本文信息
–version

日志文件维护

错误文件:hostname.err
查询日志：hostname.log，
二进制日志：hostname-bin，
慢查询日志：hostname-slow.log

都位于 data 目录。

第三十章改善性能

MySQL 性能优化主要从哪几方面考虑

硬件的好坏
参数设置，如内存分配，缓冲区分配
SHOW PROCESSLIST 查询，KILL 命令终止特定语句进程
查询语句优化
SQL 优化