【读书笔记】mysql必知必会

参考书是《MySQL必知必会》作者：Ben Forta

配套网站：

https://www.sjkjc.com/mysql/install-on-ubuntu/

https://dev.mysql.com/downloads/mysql/

mysql有两个部分，分别是客户端和服务器端，首先要在系统上安装mysql_server，再用mysql_client访问。

首先安装mysql_server，之后执行下面的步骤

ubuntu中，@前是用户，@后是主机：

1	root@bruce:/#

切换至 root 用户 :

sudo su

切回普通用户：

su 用户名

查询 mysql 运行状态：

natstat是一个网络工具，netstat 命令用来显示网络状态，-t 选项表示显示TCP连接，-a 选项表示显示所有连接，-p 选项表示显示进程名称。管道符 | 将 netstat 的输出传递给 grep 命令，后者用来搜索包含 mysql 的行

1	netstat -tap \| grep mysql

验证mysql安装是否成功：

1	mysqladmin --version

如果成功会打印：

1	mysqladmin Ver 8.0.32-0ubuntu0.20.04.2 for Linux on aarch64 ((Ubuntu))

在 MySQL Client(Mysql客户端) 使用 mysql 命令连接到 MySQL 服务器上，默认情况下 MySQL 服务器的登录密码为空：

mysql

使用以下命令来创建root用户的密码(在sql命令行退出情况下)：

1	mysqladmin -u root password "new_password"

这样会有警告：

1 2	mysqladmin: [Warning] Using a password on the command line interface can be insecure. Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.

可以登录到mysql命令行，然后运行以下命令：

1	ALTER USER 'root'@'localhost' IDENTIFIED BY '密码';

请注意，这种方法仅适用于MySQL 5.7.6及更高版本。如果您使用的是早期版本，您可以使用以下命令：

1	SET PASSWORD FOR 'root'@'localhost' = PASSWORD('密码');

但是我发现这时还是不需要密码就能登录，问题出在插件上。登录到mysql命令行，然后运行以下命令来查看用户列表，这将显示所有MySQL用户及其对应的主机名：

1	SELECT User, Host, plugin FROM mysql.user;

这时可以发现root的plugin和其他几个用户不一样：

+------------------+-----------+-----------------------+
| user             | Host      | plugin                |
+------------------+-----------+-----------------------+
| debian-sys-maint | localhost | caching_sha2_password |
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session    | localhost | caching_sha2_password |
| mysql.sys        | localhost | caching_sha2_password |
| root             | localhost | auth_socket           |
+------------------+-----------+-----------------------+
5 rows in set (0.01 sec)

经过资料查找可以发现：caching_sha2_password和auth_socket是两种不同的MySQL身份验证插件。caching_sha2_password插件使用SHA-2算法，提供256位密码加密。在应用SHA-256转换之前，密码会与随机数据混合。生成的哈希值存储在mysql.user表中。使用盐有助于防御针对存储的密码哈希值的字典攻击。另一方面，auth_socket插件通过Unix套接字文件对从本地主机连接到MySQL服务器的客户端进行身份验证。

使用auth_socket插件进行身份验证时，用户无需指定密码即可连接到服务器。但是，使用auth_socket插件进行身份验证的用户受到限制，不能远程连接；他们只能通过Unix套接字文件从本地主机连接。

auth_socket插件检查套接字用户名（操作系统用户名）是否与客户端程序向服务器指定的MySQL用户名匹配。如果名称不匹配，则插件还会检查套接字用户名是否与mysql.user表行中authentication_string列中指定的名称匹配。

可以输入以下命令：

1	update mysql.user set plugin = 'mysql_native_password' where user = 'root' and host = 'localhost';

刷新权限缓存：

1	FLUSH PRIVILEGES;

退出重启，但是这时还是不行，发现是密码强度比较高，所以要降低强度，显示现在的密码策略：

1	SHOW VARIABLES LIKE 'validate_password%';

修改密码强度：

1	SET GLOBAL validate_password.policy = LOW;

重新设置密码，就成功了！

1	mysql -u root -p

查看 MySQL 服务器状态： systemctl status mysql
启动 MySQL 服务器： systemctl start mysql
停止 MySQL 服务器： systemctl stop mysql
重启 MySQL 服务器： systemctl restart mysql
配置 MySQL 服务器自启动： systemctl enable mysql

导入本书配套的样例表：

先下载两个文件：create.sql, populate.sql

登录mysql，然后创建一个database：

1	create database crashcourse;

选取该database：

1	use crashcourse;

执行create.sql脚本，创建数据库表:

1	source ~/Desktop/mysql_scripts/create.sql;

执行populate.sql脚本，填充这些表的insert语句:

1	source ~/Desktop/mysql_scripts/populate.sql;

查看是否成功：

1	show tables;

了解SQL

数据库：保存有组织的数据的容器（通常是一个文件或者一组文件）

数据库软件（DBMS，数据库管理系统）：通过DBMS创建和操作数据库容器

表（table）：某种特定类型数据的结构化清单

模式（schema）：关于数据库和表的局部及特性的信息

主键：一列，其值可以唯一区分表中的一行

sql：结构化查询语言，用来与数据库通信的语言

MySQL简介

MySQL是一种客户机-服务器DBMS

有三种客户机程序，后两者是图形化程序：

mysql命令行
mysql administrator
mysql query browser

基本的SQL语句

SELECT
INSERT
UPDATE：更新特定行，所有行（删除特定列）
DELETE：删除行而不删除表
CREATE TABLE
ALTER TABLE
DROP TABLE

用通配符进行过滤

WHERE, IN, BETWEENT 这些操作符都是针对已知值进行过滤，但是对于过滤未知值不太好用。例如：搜索产品名中包含文本anvil的所有产品。这种不能使用比较操作符，而是应该使用通配符。利用通配符可以创建比较特定数据的搜索模式。

通配符（wildcard）：用来匹配值的一部分的特殊字符。也就是where子句中有特殊含义的字符，必须使用LIKE操作符，再用通配符。

搜索模式（search pattern）：由字面值、通配符或两者组合构成的搜索条件。

上面的例子可以用以下的句子解决：

mysql> SELECT prod_name, prod_id FROM products WHERE prod_name LIKE '%anvil%';
+--------------+---------+
| prod_name    | prod_id |
+--------------+---------+
| .5 ton anvil | ANV01   |
| 1 ton anvil  | ANV02   |
| 2 ton anvil  | ANV03   |
+--------------+---------+
3 rows in set (0.00 sec)

用正则表达式进行搜索

正则表达式是用来匹配文本的特殊的串（字符集合）。用处广泛，例如：从文本中提取电话号码；查找所有名字中带有数字的文件等。

参考书：正则表达式必知必会，作者：Ben Forta

LIKE和REGEXP的区别：LIKE必须整个数据都匹配，REGEXP只需要数据里的任意一个片段匹配。

创建计算字段

计算字段用于从数据库中检索出转换、计算或者格式化过的数据。计算字段是运行时在SELECT语句内创建的。

虽然很多在SQL语句中完成的转换和格式化工作都可以先把数据查询到客户机，再由客户机上的应用程序完成，但是在DBMS上完成好再传回的速度更快。

使用数据处理函数

每个DBMS支持的函数都不太相同，所以函数的可移植性没有SQL语句强。

一般来说，大多数SQL支持以下类型的函数：

用于处理文本串的文本函数：删除或者填充值，转换大小写
用于数值数据上进行算术操作的数值函数：返回绝对值，进行代数运算
用于处理日期和时间值并从这些值中提取特定成分的时间和日期函数：返回两个时间之差，检查日期有效性
返回DBMS正在使用的特殊信息的系统函数：返回用户登录信息，检查版本细节

汇总数据

聚集函数（aggregate function）：运行在行组上，计算和返回单个值的函数。

一共有五个，分别是：AVG(), COUNT(), MAX(), MIN(), SUM()

分组数据

分组数据用于汇总表内容的子集。涉及到两个select子句，分别是group by子句和having子句。

GROUP BY 对数据进行分组聚集
HAVING 对这些分组后的数据进行过滤，而WHERE只能对分组前的数据进行过滤
一般在使用GROUP BY子句时，也应该给出ORDER BY子句，保证数据正确排序

select子句顺序汇总

子句	说明	是否必须
select		是
from		仅在从表选择数据时使用
where	行级过滤	否
group by	分组说明	仅在按组计算聚集时使用
having	组级过滤	否
order by	输出排序顺序	否
limit	检索有限行数	否

使用子查询

查询（query）：所有的sql语句都是查询，但是查询一般指select语句

子查询（subquery）：嵌套在其他查询中的查询，select从最内部的子查询开始往外查询，并且对于嵌套的子查询数目没有限制。

相关子查询（correlated subquery）：涉及外部查询的子查询。这种子查询需要使用以下语法，即完全限定列名：

表名.列名

例如：

1	orders.cust_id = customers.cust_id

这样做是为了防止歧义

联结表

关系表：关系表的设计就是要保证把信息分解为多个表，一类数据存一个表。各表通过某些常用的值（relation）相关联。

外键（foreign key）：某个表中的一列，包含另一个表的主键值，定义了两个表之间的关系。

可伸缩性（scale）：能够适应不断增加的工作量而不失败。设计良好的数据库称为可伸缩性好。

笛卡尔积：由没有联结条件的表关系返回的结果为笛卡尔积。

内部联结(inner join)：**等值联结(equijoin)**也叫做内部连接，两种语法结果相同

SELECT vend_name, prod_name, prod_price
FROM vendors, products
WHERE vendors.vend_id = products.vend_id;

SELECT vend_name, prod_name, prod_price
FROM vendors INNER JOIN products
ON vendors.vend_id = products.vend_id;

创建高级联结

1.使用表别名：

SELECT cust_name, cust_contact
FROM customers AS c, orders AS o, orderitems AS oi
WHERE c.cust_id = o.cust_id
	AND oi.order_num = o.order_num
	AND prod_id = 'TNT2';

表别名只在查询执行中使用，不同于列别名，表别名不返回到客户机。

2.不同类型联结：

自联结：在单条select语句中不止一次引用相同的表
自然联结：排除多次出现，使每个列只返回一次
外部联结(outer join)：用于包含在相关表中没有关联行的行。分为左外部联结（left outer join）和右外部联结（right outer join）两种，两种联结的关联表顺序不同。

3.使用带聚集函数的联结

组合查询

并（union）/复合查询（compound query）：执行多个查询，并将结果作为单个查询结果集返回

在单个查询中从不同的表返回类似结构的数据
对单个表执行多个查询，按单个查询返回数据

union会从查询结果中自动去除重复的行，如果使用union all则不取消重复的行

全文本搜索

不是所有数据库引擎都支持全文本搜索，例如在MySQL中，innodb不支持，而myisam支持。

全文本搜索是比通配符和正则表达式更强的搜索机制，在性能，明确控制，智能化结果等方面做的更好

全文本搜索
查询扩展：放宽所返回的全文本搜索结果的范围
布尔文本搜索（Boolean mode）：不用定义fulltext索引，但是性能降低

DBMS内置引擎

innodb：支持事务处理，不支持全文本搜索。
memory：在功能上等同于myisam，但是数据存储在内存而不是硬盘，所以速度快，适合用于临时表。
myisam：高性能引擎，支持全文本搜索，但是不支持事务处理。

外键不能跨引擎使用

视图

视图（view）：不包含表中的任何列或数据，而是包含一个SQL查询的结果。把这个查询结果包装成一个虚拟表，再从该虚拟表中检索数据。

存储过程

存储过程：为以后的使用而保存的一条或者多条mysql语句的集合。可以将其视为批文件，用于批处理。可以提高安全性，封装重复操作

mysql> CREATE PROCEDURE ordertotal(
    -> 		IN onumber INT,
    -> 		OUT ototal DECIMAL(8,2)
    -> )
    -> BEGIN
    -> 		SELECT Sum(item_price*quantity)
    -> 		FROM orderitems
    -> 		WHERE order_num = onumber
    -> 		INTO ototal;
    -> END;
Query OK, 0 rows affected (0.03 sec)

mysql> CALL ordertotal(20005, @total);
Query OK, 1 row affected (0.00 sec)

mysql> select @total;
+--------+
| @total |
+--------+
| 149.87 |
+--------+
1 row in set (0.00 sec)

关键词OUT用来从存储过程中传出一个值返回给调用者
关键词IN用于传入一个值给存储过程
关键词INTO用于将查询结果保存到相应的变量

在创建存储过程的前后分别使用以下语句，可以在命令行中正确处理存储过程中出现的；符号，避免报错：

-- 创建之前，规定//符号为新的语句结束分隔符
DELIMITER //

CREATE PROCEDURE func()
BEGIN
END //

-- 创建之后再改回;符号
DELIMITER ;

智能存储过程，也就是比较复杂的存储过程，类似于函数：

-- 声明存储过程和这个存储过程用到的变量
CREATE PROCEDURE ordertotal(
	IN onumber INT,
	IN taxable BOOLEAN,
	OUT ototal DECIMAL(8,2)  -- 储存过程对外输入的变量
) COMMENT '获取加税之后的订单的总额'  -- 起到注释说明的作用
BEGIN
	-- 声明局部变量
	DECLARE total DECIMAL(8,2); 
	-- 指定局部变量的默认值,假定税率为6%
	DECLARE taxrate INT DEFAULT 6;
	
	SELECT SUM(item_price*quantity) 
	FROM orderitems
	WHERE order_num = onumber
	-- 把查询的结果存入声明的局部变量中
	INTO total;
	
	-- 如果含税的话,就需要计算税
	IF taxable
		THEN
			SELECT total + (total/100*taxrate) INTO total;
	END IF;
	
	-- 把计算的总的结果放入对外的输出的变量中
	SELECT total INTO ototal;
END;


mysql> CALL ordertotal(20005,0,@total);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT @total;
+--------+
| @total |
+--------+
| 149.87 |
+--------+
1 row in set (0.00 sec)


mysql> CALL ordertotal(20005,1,@total);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT @total;
+--------+
| @total |
+--------+
| 158.86 |
+--------+
1 row in set (0.00 sec)

游标

游标（cursor）：一个存储在MySQL服务器上的查询的结果集。存储了游标之后，应用程序可以根据需要滚动或者浏览其中的数据。

MySQL游标只能用于存储过程和函数。打开游标后需要用close关闭，以释放资源，如果不关闭，则会在到达end语句时自动关闭。

CREATE PROCEDURE processorders()
BEGIN
	-- 游标的使用
	-- 声明本地的变量
	DECLARE done BOOLEAN DEFAULT 0;
	DECLARE o INt;
	DECLARE t DECIMAL(8,2);

	-- 声明游标
	DECLARE ordernumbers CURSOR
	FOR
	SELECT order_num FROM orders;

	-- 指定游标条件
	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

	-- 创建一张新表
	CREATE TABLE IF NOT EXISTS ordertotals (order_num INT ,total DECIMAL(8,2));

	-- 打开游标
	OPEN ordernumbers;

	-- 准备循环所有的行
	REPEAT
		-- 游标一行的数据放到变量o当中
		FETCH ordernumbers INTO o;
		-- 调用提前准备好的存储函数
		-- o是存放游标数据的变量,1表示要计算税,t表示储存函数的输出值
		CALL ordertotal(o,1,t);
		-- 向创建的表中插入数据
		INSERT INTO ordertotals(order_num,total) VALUES(o,t);
	UNTIL done END REPEAT;

	-- 关闭游标
	CLOSE ordernumbers;
END;

触发器

触发器：是指MySQL响应某些语句（delete, insert, update ）而自动执行的一条语句或者是一组语句（位于begin和end之间）。也就是说在执行delete, insert, update 这些活动后，会触发一些自行设置的活动。

MySQL中需要保持每个表的触发器命名唯一，不同表之间可以相同。其他DBMS则要求全数据库命名唯一。
可以指定触发器在delete, insert, update 这些活动之前或者之后进行。
只有表支持触发器，视图和临时表不支持。
每个表最多支持六个触发器（delete, insert, update 三个事件的发生前后）。
单一触发器不能关联多个事件，必须定义多个触发器。

管理事务处理

事务处理（transaction processing）：维护数据库的完整性，保证成批的MySQL操作要么完全执行，要么完全不执行（回退）。

事务（transaction）：一组SQL语句
回退（rollback）：撤销指定SQL语句的过程
提交（commit）：将未存储的SQL语句结果写入数据库表。默认的SQL行为自动提交所有更改，然而在事务处理块中，提交不会自动进行，需要手动给出。
保留点（savepoint）：事务处理中设置的临时占位符（place holder），可以对其发布回退（不同于回退整个事务处理）

全球化与本地化——处理不同字符集和语言

字符集：字母和符号的集合
编码：某个字符集成员的内部表示
校对(collation)：规定字符之间如何比较的指令。可以用于order by，group by，having子句，聚集函数，别名等。

安全管理

赋予用户特殊的权限进行访问控制和保护MySQL服务器。

数据库维护

备份数据
- flush tables：刷新未写数据，用于备份之前
数据库维护
- analyze table：检查表键是否正确
- check table：针对特定问题对表进行检查
- repair table：修复表
- optimize table：回收空间
诊断启动问题

手动启动，查看日志
日志文件
- 错误日志：包含启动和关闭的错误
- 查询日志：记录所有MySQL活动
- 二进制日志：记录更新过数据的所有语句
- 缓慢查询日志：记录执行缓慢的查询
使用flush logs 可以刷新和清空所有日志文件