sql视频小笔记-Mosh

yueyi_

已于 2023-09-21 16:35:13 修改

阅读量177

点赞数

文章标签： sql

于 2022-10-28 16:02:51 首次发布

本文链接：https://blog.csdn.net/yueyi_/article/details/127572817

版权

课程笔记完整版：https://zhuanlan.zhihu.com/p/222865842

一、基础查询

1、SELECT 子句

SELECT "累计" as type -- 常量字段
FROM customers
WHERE customer_id = 1 
ORDER BY first_name;

SELECT last_name, 
     first_name, 
     points, 
     (points + 10) * 100 AS discount_factor,
     points * 10 + 100 AS "discount factor"   -- 加引号可以带空格
FROM customers;

SELECT * 
FROM customers
WHERE NOT (birth_date > '1990-01-01' OR points > 1000); -- 条件的补集

2、HAVING 子句

类似where
①having在分组之后筛选数据（group by 后），where 在group by之前用。
②having子句只能筛选在select中出现的列，where不受限制

3、WITH ROLLUP

在group by 子句之后，对结果进行汇总

SELECT client_id,
	sum(invoice_total) as total_sales
FROM invoices
GROUP BY client_id WITH ROLLUP

注：使用了with rollup后，groupby后面不能用别名
在这里插入图片描述

二、复杂查询

1、子查询（where、from、select子句）

可以在where、from、select子句中编写子查询。

2、IN 运算符

select *
from clients 
where chlient_id not in (
	select distinct client -- in 运算符后的子查询返回一列值
	from invoiving
);

3、ALL 运算符

select *
from invoices
where invoice_total > ALL( -- 大于id为3的顾客所有的发票金额的发票
	select invoice_total
	from invoices
	where client_id = 3
	)

上述sql相当于

select *
from invoices 
where invoice_total > (
	select max(invoice_total)
	from invoices 
	where client_id = 3
	)

4、ANY 运算符（some）

select *
from clients
where client_id = ANY( 
	select client_id -- 至少有两张发票的用户id
	from invoices 
	group by client_id
	having count(*) >=2
	)

上述sql相当于

select *
from clients 
where client_id IN ( -- ANY与IN 运算符等效
	select client_id 
	from invoices 
	group by client_id
	having count(*) >=2
	)

5、EXISTS 运算符

与 IN 运算符可相互替代。运算的原理不同。

select *
from clients c
where EXISTS( 
	select client_id
	from invoices 
	where client_id = c.client_id -- exists运算符后的子查询中，需要用相关子查询
)

上述sql等同于：

select *
from clients 
where client_id in (
	select distinct client_id
	from invoices 
)

执行原理：
1、in运算符：先执行子查询，返回子查询的所有结果，然后执行主查询。当子查询结果数据量过大时，in运算符执行性能不佳。
2、exists运算符：执行主查询的每一行之前先执行子查询，并不返回子查询结果，返回一个指令，说明这个子查询中是否有符合这个搜索条件的行。

例子：查找从未卖出去过的商品。

select *
from products
where product_id not in (  -- in 运算符子查询商品数量巨大时运算性能不佳
	select product_id
    from order_items
)

上述sql等同于：

select *
from products p
where not exists ( -- 对于products表中的每一个产品，需要检查条件是否成立
	select product_id -- 如果订购表中存在这个产品，则返回TRUE, NOT运算符会将其变为FALSE，这条记录就不会被放在最终的结果集中
    from order_items
    where product_id = p.product_id
)

6、相关子查询

子查询中用到了外查询中的表。

-- 查找每个部门中工资大于部门平均工资的员工
select *
from employees e 
where salary > (
	select avg(salary)
	from employees 
	where office_id = e.office_id -- 重点，子查询的部门id要关联外查询的部门id
)

执行原理：
1、非关联子查询：只执行一次子查询，输出一张临时表，再进行外查询。
2、相关子查询：子查询会在主查询的每一行的层面执行，因此相关子查询执行的非常慢。

7、SELECT子句中的子查询

可以减少代码量，使代码简单易懂。

select  invoice_id -- 发票id
	, invoice_total -- 发票金额
	, (select avg(invoice_total) from invoices) as invoice_average -- 发票金额的平均值
	, invoice_total - (select invoice_average) as difference -- 发票金额与平均值的差值
from invoices

select client_id --用户id
	, name -- 用户名称
	, (select sum(invoice_total) from invoices where c.client_id = client_id) as  total_sales -- 每个用户的总消费金额
    , (select avg(invoice_total) from invoices) as average -- 所有用户单张发票的平均金额（每笔消费的平均金额）
    , (select total_sales - average) as difference -- 每个用户消费的总金额与单笔消费平均金额的差值
from clients c -- 用户信息表

8、FROM子句中的子查询

from字句中的子查询，一定需要一个别名

三、函数

1、数值函数

查看MySQL全部数值函数可谷歌 ‘mysql numeric function’，第一个就是官方文档。

SELECT ROUND(5.7365, 2)  -- 四舍五入  
SELECT TRUNCATE(5.7365, 2)  -- 截断 
SELECT CEILING(5.2)  -- 天花板函数，大于等于此数的最小整数 6 
SELECT FLOOR(5.6)  -- 地板函数，小于等于此数的最大整数 5 
SELECT ABS(-5.2)  -- 绝对值  
SELECT RAND()  -- 随机函数，0到1的随机值

2、字符串函数

查看全部搜索关键词 ‘mysql string functions’

长度、转大小写：

SELECT LENGTH('sky')  -- 字符串字符个数/长度（LENGTH）  
SELECT UPPER('sky')  -- 转大写  
SELECT LOWER('Sky')  -- 转小写

用户输入时时常多打空格，下面三个函数用于处理/修剪（trim）字符串前后的空格，L、R 表示 LEFT、RIGHT：

SELECT LTRIM('  Sky')  -- Sky
SELECT RTRIM('Sky  ') -- Sky
SELECT TRIM(' Sky ') -- Sky

切片：取左边，取右边，取中间 substring()

SELECT LEFT('Kindergarden', 4)  -- 取左边（LEFT）4个字符 -- Kind
SELECT RIGHT('Kindergarden', 6)  -- 取右边（RIGHT）6个字符 -- garden
SELECT SUBSTRING('Kindergarden', 7, 6)  -- garden
-- 取中间从第7个开始的长度为6的子串（SUBSTRING）
-- 注意是从第1个（而非第0个）开始计数的
-- 省略第3参数（子串长度）则一直截取到最后

定位：locate()

SELECT LOCATE('gar', 'Kindergarden')  -- 7
-- 定位（LOCATE）首次出现的位置
-- 没有的话返回0（其他编程语言大多返回-1，可能因为索引是从0开始的）
-- 这个定位/查找函数依然是不区分大小写的

替换：replace()

SELECT REPLACE('Kindergarten', 'garten', 'garden') -- Kindergarden

连接：concat()

USE sql_store;

SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM customers

3、MySQL中的日期函数

当前时间

SELECT NOW()  -- 2020-09-12 08:50:46
SELECT CURDATE()  -- current date, 2020-09-12
SELECT CURTIME()  -- current time, 08:50:46

以上函数将返回时间日期对象

提取时间日期对象中的元素：

SELECT YEAR(NOW())  -- 2020

还有MONTH, DAY, HOUR, MINUTE, SECOND。

以上函数均返回整数，还有另外两个返回字符串的：

SELECT DAYNAME(NOW())  -- Saturday
SELECT MONTHNAME(NOW())  -- September

标准SQL语句有一个类似的函数 EXTRACT()，若需要在不同DBMS中录入代码，最好用EXTRACT()：

SELECT EXTRACT(YEAR FROM NOW())

当然第一参数也可以是MONTH, DAY, HOUR ……
总之就是：EXTRACT(单位 FROM 日期时间对象)

练习：返回本年的订单

select *
from orders
where year(order_date) = year(now())

4、格式化日期和时间

DATE_FORMAT(date, format) 将 date 根据 format 字符串进行格式化。
TIME_FORMAT(time, format) 类似于 DATE_FORMAT 函数，但这里 format
字符串只能包含用于小时，分钟，秒和微秒的格式说明符。其他说明符产生一个 NULL 值或0。

SELECT DATE_FORMAT(NOW(), '%M %d, %Y')  -- September 12, 2020
-- 格式说明符里，大小写是不同的，这是目前SQL里第一次出现大小写不同的情况
SELECT TIME_FORMAT(NOW(), '%H:%i %p')  -- 11:07 AM

很多像这种完全不需要记也不可能记得完，重要的是知道有这么个可以实现这个功能的函数，具体的格式说明符（Specifiers）可以需要的时候去查，至少有两种方法：

直接谷歌关键词如 mysql date format functions, 其实是在官方文档的 12.7 Date and Time Functions 小结里，有两个函数的说明和 specifiers 表
用软件里的帮助功能，如 workbench 里的 HELP INDEX 打开官方文档查询或者右侧栏的 automatic comtext help (其是也是查官方文档，不过是自动的)

5、计算日期和时间

DATE_ADD 增加天数、月数、年数、小时数
DATE_SUB 减少天数、月数、年数、小时数
DATEDIFF 两个日期之间相差的天数
TIME_TO_SEC 计算从 00:00 到某时间经历的秒数

增加或减少一定的天数、月数、年数、小时数等等：

SELECT DATE_ADD(NOW(), INTERVAL -1 DAY)
SELECT DATE_SUB(NOW(), INTERVAL 1 YEAR)

但其实不用函数，直接加减更简洁：

NOW() - INTERVAL 1 DAY
NOW() - INTERVAL 1 YEAR

计算日期差异：

SELECT DATEDIFF('2019-01-01 09:00', '2019-01-05')  -- -4
-- 会忽略时间部分，只算日期差异

-- 借助 TIME_TO_SEC 函数计算时间差异：
-- TIME_TO_SEC：计算从 00:00 到某时间经历的秒数

SELECT TIME_TO_SEC('09:00')  -- 32400
SELECT TIME_TO_SEC('09:00') - TIME_TO_SEC('09:02')  -- -120

6、IFNULL和COALESCE函数

两个用来替换空值的函数：

IFNULL
COALESCE 更灵活

-- 将 orders 里 shipper.id 中的空值替换为 'Not Assigned'（未分配）：
USE sql_store;

SELECT 
    order_id,
    -- IFNULL(shipper_id, 'Not Assigned') AS shipper
    COALESCE(shipper_id, comments, 'Not Assigned') AS shipper
FROM orders

COALESCE 函数是返回一系列值中的首个非空值，更灵活。

7、IF函数

小结：

根据是否满足条件返回不同的值：
IF(条件表达式, 返回值1, 返回值2)
返回值可以是任何东西，数值文本日期时间空值null 均可

案例：将订单表中订单按是否是今年的订单分类为active（活跃）和archived（存档）。

USE sql_store;

SELECT 
    *,
    IF(YEAR(order_date) = YEAR(NOW()),
       'Active',
       'Archived') AS category
FROM orders

练习：得到包含如下字段的表：

product_id
name (产品名称)
orders (该产品出现在订单中的次数)
frequency (根据是否多于一次而分类为’Once’或’Many times’)

USE sql_store;

SELECT 
    product_id,
    name,
    COUNT(*) AS orders,
    IF(COUNT(*) = 1, 'Once', 'Many times') AS frequency
    /* 因为之后的内连接筛选掉了无订单的商品，
    所以这里不变考虑次数为0的情况 */
FROM products
JOIN order_items USING(product_id)
GROUP BY product_id

8、CASE运算符

小结：IF通常用于两种分类，当分类多余两种时，可以用IF嵌套，也可以用CASE语句，后者可读性更好

CASE语句结构：
CASE
WHEN …… THEN ……
WHEN …… THEN ……
WHEN …… THEN ……
……
[ELSE ……] （ELSE子句是可选的）
END

案例：得到包含如下字段的表：

customer
points
category（根据积分 <2k、2k~3k（包含两端）、>3k 分为青铜、白银和黄金用户）

USE sql_store;

select concat(first_name," ",last_name) as customer
	, points
    , case 
		when points > 3000 then "Gold"
        when points >= 2000 then "Silver"
        else "Bronze"
	 end as category
from customers

其实也可以用IF嵌套，甚至代码还少些，但感觉没有CASE语句结构清晰、可读性好。

SELECT
    CONCAT(first_name, ' ', last_name) AS customer,
    points,
    IF(points < 2000, 'Bronze', 
        IF(points BETWEEN 2000 AND 3000, 'Silver', 
        -- 第二层的条件表达式也可以简化为 <= 3000
            IF(points > 3000, 'Gold', null))) AS category
FROM customers

四、视图

1、创建视图

小结：
就是创建虚拟表，自动化一些重复性的查询模块。
注意视图虽然可以像一张表一样进行各种操作，但并没有真正储存数据，数据仍然储存在原始表中，视图只是储存起来的模块化的查询结果，是为了方便和简化后续进一步操作而储存起来的虚拟表。

案例：创建 sales_by_client 视图

USE sql_invoicing;

CREATE VIEW sales_by_client AS
    SELECT 
        client_id,
        name,
        SUM(invoice_total) AS total_sales
    FROM clients c
    JOIN invoices i USING(client_id)
    GROUP BY client_id, name;
    -- 虽然实际上这里加不加上name都一样

若要删掉该视图用 DROP VIEW sales_by_client 。

创建视图后可就当作 sql_invoicing 库下一张表一样进行各种操作。

USE sql_invoicing;

SELECT 
    s.name,
    s.total_sales,
    phone
FROM sales_by_client s
JOIN clients c USING(client_id)
WHERE s.total_sales > 500

2、更新或删除视图

小结：

修改视图的两种方法：
可以先DROP再CREATE
也可以用CREATE OR REPLACE（推荐）

保存视图的原始查询语句：

修改视图的两种方法：
法1. 先删除再重建

USE sql_invoicing;

DROP VIEW IF EXISTS clients_balance;
-- 若不存在这个视图，直接 DROP 会报错，所以要加上 IF EXISTS 先检测有没有这个视图

CREATE VIEW clients_balance AS 
    ……
    ORDER BY balance DESC

法2. 用REPLACE关键字，即用 CREATE OR REPLACE VIEW clients_balance AS，和上面等效。

USE sql_invoicing;

CREATE OR REPLACE VIEW clients_balance AS
    ……
    ORDER BY balance DESC

如何保存视图的原始查询语句？

法1.

（推荐方法）将原始查询语句保存为 views 文件夹下的和与视图同名的 clients_balance.sql 文件，然后将这个文件夹放在源码控制下（put these files under source control）, 通常放在 git repository（仓库）里与其它人共享，团队其他人因此能在自己的电脑上重建这个数据库。

法2.

若丢失了原始查询语句，要修改的话可点击视图的扳手按钮打开编辑模式。直接做我们需要的修改，然后点apply就行了。

法2是没有办法的办法，当然最好还是将 views 保存为 sql 文件并放入源码控制。

3、可更新视图

小结：

视图作为虚拟表/衍生表，除了可用在查询语句SELECT中，也可以用在增删改（INSERT DELETE UPDATE）语句中，但后者有一定的前提条件。

如果一个视图的原始查询语句中没有如下元素：

DISTINCT 去重
GROUP BY/HAVING/聚合函数 (后两个通常是伴随着 GROUP BY 分组出现的)
UNION 纵向连接

则该视图是可更新视图（Updatable Views），可以增删改，否则只能查。

另外，增（INSERT）还要满足附加条件：视图必须包含底层原表的所有必须字段

总之，出于安全考虑或其他原因用户通常没有某表的直接权限时，不能修改原表数据。因此可以通过修改视图来修改数据，前提是视图是可更新的。

案例：

创建视图（新虚拟表）：
invoices_with_balance（带差额的发票记录表）：

USE sql_invoicing;

CREATE OR REPLACE VIEW invoices_with_balance AS
SELECT 
    invoice_id, 
    number, 
    client_id, 
    invoice_total, 
    payment_total, 
    invoice_date,
    invoice_total - payment_total AS balance,  -- 新增列
    due_date, 
    payment_date
FROM invoices
WHERE (invoice_total - payment_total) > 0

该视图满足条件，是可更新视图，故可以增删改：

删：
删掉id为1的发票记录

DELETE FROM invoices_with_balance
WHERE invoice_id = 1

改：
将2号发票记录的期限延后两天

UPDATE invoices_with_balance
SET due_date = DATE_ADD(due_date, INTERVAL 2 DAY)
WHERE invoice_id = 2

增：
在视图中用INSERT新增记录的话还有另一个前提，即视图必须包含其底层所有原始表的所有必须字段。
例如，若这个 invoices_with_balance 视图里没有 invoice_date 字段（invoices 中的必须字段），那就无法通过该视图向 invoices 表新增记录，因为 invoices 表不会接受 invoice_date 字段为空的记录。

4、WITH CHECK OPTION 子句

小结：

在视图的原始查询语句最后加上 WITH CHECK OPTION 可以防止执行那些会让视图中某些行（记录）消失的修改语句。

案例：

接前面的 invoices_with_balance 视图的例子，该视图与原始的 orders 表相比增加了balance(invouce_total - payment_total) 列，且只显示 balance 大于0的行（记录），若将某记录（如2号订单）的 payment_total 改为和 invouce_total 相等，则 balance 为0，该记录会从视图中消失：

UPDATE invoices_with_balance
SET payment_total = invoice_total
WHERE invoice_id = 2

更新后会发现invoices_with_balance视图里2号订单消失。

但在视图原始查询语句最后加入 WITH CHECK OPTION 后，对3号订单执行类似上面的语句后会报错：

UPDATE invoices_with_balance
SET payment_total = invoice_total
WHERE invoice_id = 3

-- Error Code: 1369. CHECK OPTION failed 'sql_invoicing.invoices_with_balance'

5、视图的其他优点

小结：

三大优点：

简化查询
增加抽象层和减少变化的影响
数据安全性

具体来讲：
1、（首要优点）简化查询 simplify queries
2、增加抽象层，减少变化的影响 Reduce the impact of changes：
视图给表增加了一个抽象层（模块化），这样如果数据库设计改变了（如一个字段从一个表转移到了另一个表），只需修改视图的查询语句使其能保持原有查询结果即可，不需要修改使用这个视图的那几十个查询。相反，如果没有视图这一层的话，所有查询将直接使用指向原表的原始查询语句，这样一旦更改原表设计，就要相应地更改所有的这些查询。
3、限制对原数据的访问权限 Restrict access to the data：
在视图中可以对原表的行和列进行筛选，这样如果你禁止了对原始表的访问权限，用户只能通过视图来修改数据，他们就无法修改视图中未返回的那些字段和记录。

五、存储过程

1、什么是存储过程

小结

存储过程三大作用：

储存和管理SQL代码 Store and organize
SQL 性能优化 Faster execution
数据安全 Data security

假设你要开发一个使用数据库的应用程序，你应该将SQL语句写在哪里呢？

如果将SQL语句内嵌在应用程序的代码里，将使其混乱且难以维护，所以应该将SQL代码和应用程序代码分开，将SQL代码储存在所属的数据库中，具体来说，是放在储存过程（stored procedure）和函数中。

储存过程是一个包含SQL代码模块的数据库对象，在应用程序代码中，我们调用储存过程来获取和保存数据（get and save the data）。也就是说，我们使用储存过程来储存和管理SQL代码。

使用储存程序还有另外两个好处。首先，大部分DBMS会对储存过程中的代码进行一些优化，因此有时储存过中的SQL代码执行起来会更快。

此外，就像视图一样，储存过程能加强数据安全。比如，我们可以移除对所有原始表的访问权限，让各种增删改的操作都通过储存过程来完成，然后就可以决定谁可以执行何种储存过程，用以限制用户对我们数据的操作范围，例如，防止特定的用户删除数据。

2、创建一个存储过程

小结

DELIMITER $$ – 修改分隔符
CREATE PROCEDURE 过程名()  
    BEGIN
        ……;
        ……;
        ……;   -- 存储过程的主体（body）
    END$$
DELIMITER ; – 将分隔符改回默认的；

BEGIN 和 END 之间包裹的是此过程（PROCEDURE）的内容（body），内容里可以有多个语句，但每个语句都要以 ; 结束，包括最后一个。
为了将过程内容内部的语句分隔符与SQL本身执行层面的语句分隔符 ; 区别开，要先用 DELIMITER(分隔符) 关键字暂时将SQL语句的默认分隔符改为其他符号，一般是改成双美元符号 $$ ，创建过程结束后再改回来。

实例

-- 创建存储过程
DELIMITER $$
CREATE PROCEDURE get_clients()
BEGIN
	SELECT * FROM clients; -- 需要执行的语句
END $$

-- 在SQL中调用存储过程
CALL get_clients()

3、使用MySQL工作台创建存储过程

也可以用点击的方式创造过程，右键选择 Create Stored Procedure，填空，Apply。这种方式 Workbench 会帮你处理暂时修改分隔符的问题。
在这里插入图片描述

这种方式一样可以储存SQL文件。

事实证明，mosh很喜欢用这种方式，后面基本都是用这种方式创建过程（毕竟不用管改分隔符的问题，能偷懒又何必自找麻烦呢？谁还不是条懒狗呢？）

4、删除存储过程

小结

DROP PROCEDURE IF EXISTS 过程名() ;

实例：一个创建过程（get_clients）的标准模板

USE sql_invoicing;

DROP PROCEDURE IF EXISTS get_clients;
-- 注意加上【IF EXISTS】，以免因为此过程不存在而报错

DELIMITER $$

    CREATE PROCEDURE get_clients()
        BEGIN
            SELECT * FROM clients;
        END$$

DELIMITER ;

CALL get_clients();

最佳实践：
同视图一样，最好把删除和创建每一个过程的代码也储存在不同的SQL文件中，并把这样的文件放在 Git 这样的源码控制下，这样就能与其它团队成员共享 Git 储存库。他们就能在自己的机器上重建数据库以及该数据库下的所有的视图和储存过程。

5、参数

通常我们使用参数来给储存过程传值。
小结

CREATE PROCEDURE 过程名
(
参数1 数据类型,
参数2 数据类型,
……
)
BEGIN ……
END

案例

创建过程 get_invoices_by_client，通过 client_id 来获得某个客户的发票记录。client_id 的数据类型设置可以参考原表中该字段的数据类型。

USE sql_invoicing;

DROP PROCEDURE IF EXISTS get_invoices_by_client ;

DELIMITER $$

CREATE PROCEDURE get_invoices_by_client
(
    client_id INT  
)
BEGIN
	SELECT * 
	FROM invoices i
	WHERE i.client_id = client_id;
END$$

DELIMITER ;

CALL get_invoices_by_client(1);

注：Mosh 创建和调用都直接用的右键点击法。

6、带默认值的参数

小结
给参数设置默认值，主要是运用条件语句块和替换空值函数。

SQL中的条件类语句：

替换空值 IFNULL(值1，值2)
条件函数 IF(条件表达式, 返回值1, 返回值2)
条件语句块

方法一：用IF条件语句块实现。
案例1
把 get_clients_by_state 过程的默认参数设为’CA’，即默认查询加州的客户。

USE sql_invoicing;

DROP PROCEDURE IF EXISTS get_clients_by_state;

DELIMITER $$

CREATE PROCEDURE get_clients_by_state
(
    state CHAR(2)  
)
BEGIN
    IF state IS NULL THEN 
        SET state = 'CA';  
        /* 注意别忽略SET，
        SQL 里单个等号 '=' 是比较操作符而非赋值操作符
        '=' 与 SET 配合才是赋值 */
    END IF;
    
    SELECT * FROM clients c
    WHERE c.state = state;
END$$

DELIMITER ;

调用

CALL get_clients_by_state(NULL)

注意要调用过程并使用其默认值时时要传入参数 NULL ，MySQL不允许不传参数。

案例2
将 get_clients_by_state 过程设置为默认选取所有顾客。

……
BEGIN
    IF state IS NULL THEN 
        SELECT * FROM clients c;
    ELSE
        SELECT * FROM clients c
        WHERE c.state = state;
    END IF;    
END$$
……

方法二：用IFNULL替换空值函数实现。
案例2
将 get_clients_by_state 过程设置为默认选取所有顾客。

……
BEGIN
    SELECT * FROM clients c
    WHERE c.state = IFNULL(state, c.state)
END$$
……

练习
创建一个叫 get_payments 的过程，包含 client_id 和 payment_method_id 两个参数，数据类型分别为 INT(4) 和 TINYINT(1) 。
创建：

USE sql_invoicing;

DROP PROCEDURE IF EXISTS get_payments;

DELIMITER $$

CREATE PROCEDURE get_payments
(
    client_id INT,  
    payment_method_id TINYINT
)
BEGIN
    SELECT * FROM payments p
    WHERE 
        p.client_id = IFNULL(client_id, p.client_id) AND
        p.payment_method = IFNULL(payment_method_id, p.payment_method);
        -- 小心这种实际工作中各表相同字段名称不同的情况
END$$

DELIMITER ;

调用：

-- 所有支付记录
CALL get_payments(NULL, NULL);

-- 1号顾客的所有记录
CALL get_payments(1, NULL);

-- 3号支付方式的所有记录
CALL get_payments(NULL, 3);

-- 5号顾客用2号支付方式的所有记录
CALL get_payments(5, 2);

注意
注意一个区别：
Parameter 形参（形式参数）：创建过程中用的占位符，如 client_id、payment_method_id。
Argument 实参（实际参数）：调用时实际传入的值，如 1、3、5、NULL。

7、参数验证

小结
修改数据前最好先进行参数验证以防止不合理的修改。主要利用条件语句块和 SIGNAL SQLSTATE MESSAGE_TEXT 关键字。
具体来说是在过程的内容开头加上这样的语句：

IF 错误参数条件表达式 THEN
SIGNAL SQLSTATE ‘错误类型’
[SET MESSAGE_TEXT = ‘关于错误的补充信息’]（可选）

案例

创建一个 make_payment 过程，含invoice_id,payment_amount, payment_date 三个参数。
（Mosh还是喜欢通过右键 Create Stored Procedure 地方式创建，不必考虑暂时改分隔符的问题，更简便）

CREATE DEFINER=`root`@`localhost` PROCEDURE `make_payment`(
    invoice_id INT,
    payment_amount DECIMAL(9, 2),
    /*
    9是精度， 2是小数位数。精度表示值存储的有效位数，小数位数表示小数点后可以存储的位数见：https://dev.mysql.com/doc/refman/8.0/en/fixed-point-types.html 
    */
    payment_date DATE    
)
BEGIN   
    UPDATE invoices i
    SET 
        i.payment_total = payment_amount,
        i.payment_date = payment_date
    WHERE i.invoice_id = invoice_id;
END

为了防止传入像 -100 的 payment_total 这样不合理的参数，要在增加一段参数验证语句，利用的是条件语句块加SIGNAL关键字，和其他编程语言中的抛出异常等类似。

具体的错误类型可通过谷歌 “sqlstate error” 查阅（推荐使用IBM的那个表），这里是 ‘22 Data Exception’ 大类中的 ‘22003 A numeric value is out of range.’ 类型。注意还添加了 MESSAGE_TEXT 以提供给用户参数错误的更具体信息。现在传入负数的 payment_amount 就会报错 'Error Code: 1644. Invalid payment amount '。

CREATE DEFINER=`root`@`localhost` PROCEDURE `make_payment`(
	invoice_id int,
    payment_amount decimal(9,2),
    payment_date date
)
BEGIN
	if payment_amount <= 0 then 
		signal sqlstate '22003'
			set message_text = 'Invalid payment amount';
	end if;
    
	update invoices i
    set
		i.invoice_total = payment_amount,
        i.payment_date = payment_date
	where i.invoice_id = invoice_id;
END

8、输出参数-用户变量

小结
输入参数是用来给过程传入值的，我们也可以用输出参数来获取过程的结果值。
具体是在参数的前面加上 OUT 关键字，然后再 SELECT 后加上 INTO……
调用麻烦，如无需要，不要多此一举。

案例
创造 get_unpaid_invoices_for_client 过程，获取特定顾客所有未支付过的发票记录（即 payment_total = 0 的发票记录）。

CREATE DEFINER=`root`@`localhost` PROCEDURE `get_unpaid_invoices_for_client`(
        client_id INT
)
BEGIN
    SELECT COUNT(*), SUM(invoice_total)
    FROM invoices i
    WHERE 
        i.client_id = client_id AND
        payment_total = 0;
END

调用

call sql_invoicing.get_unpaid_invoices_for_client(3);

得到3号顾客的 COUNT(*) 和 SUM(invoice_total) （未支付过的发票数量和总金额）分别为2和286。

我们也可以通过输出参数（变量）来获取这两个结果值，修改过程，添加两个输出参数 invoice_count 和 invoice_total：

CREATE DEFINER=`root`@`localhost` PROCEDURE `get_unpaid_invoices_for_client`(
        client_id INT,
        OUT invoice_count INT,
        OUT invoice_total DECIMAL(9, 2)
        -- 默认是输入参数，输出参数要加【OUT】前缀
)
BEGIN
    SELECT COUNT(*), SUM(invoice_total)
    INTO invoice_count, invoice_total
    -- SELECT后跟上INTO语句将SELECT选出的值传入输出参数（输出变量）中
    FROM invoices i
    WHERE 
        i.client_id = client_id AND
        payment_total = 0;
END

调用：单击闪电按钮调用，只用输入client_id，得到如下语句结果：

set @invoice_count = 0;
set @invoice_total = 0;
call sql_invoicing.get_unpaid_invoices_for_client(3, @invoice_count, @invoice_total);
select @invoice_count, @invoice_total;

先定义以@前缀表示用户变量，将初始值设为0。（变量（variable）简单讲就是储存单一值的对象）再调用过程，将过程结果赋值给这两个输出参数，最后再用SELECT查看。

很明显，通过输出参数获取并读取数据有些麻烦，若无充足的原因，不要多此一举。

9、变量 Variables

小结

两种变量:

用户或会话变量 SET @变量名 = ……
本地变量 DECLARE 变量名数据类型 [DEFAULT 默认值]

1. 用户或会话变量（User or session variable）：
临时变量、用户变量。
上节课讲过，用 SET 语句并在变量名前加 @ 前缀来定义，将在整个用户会话期间存续，在会话结束断开MySQL链接时才被清空，这种变量主要在调用带输出的储存过程时，作为输出参数来获取结果值。

实例

set @invoice_count = 0;
set @invoice_total = 0;
call sql_invoicing.get_unpaid_invoices_for_client(3, @invoice_count, @invoice_total);
select @invoice_count, @invoice_total;

2. 本地变量（Local variable）：
在储存过程或函数中通过 DECLARE 声明并使用，在函数或储存过程执行结束时就被清空，常用来执行过程（或函数）中的计算。

案例
创造一个 get_risk_factor 过程，定义 risk_factor = invoices_total / invoices_count * 5。

CREATE DEFINER=`root`@`localhost` PROCEDURE `get_risk_factor`()
BEGIN
    -- 声明三个本地变量，可设默认值
    DECLARE risk_factor DECIMAL(9, 2) DEFAULT 0;
    DECLARE invoices_total DECIMAL(9, 2);
    DECLARE invoices_count INT;

    -- 用SELECT得到需要的值并用INTO传入invoices_total和invoices_count
    SELECT SUM(invoice_total), COUNT(*)
    INTO invoices_total, invoices_count
    FROM invoices;

    -- 用SET语句给risk_factor计算赋值
    SET risk_factor = invoices_total / invoices_count * 5;

    -- 展示最终结果risk_factor
    SELECT risk_factor;        
END

10、函数 Functions

小结
函数和储存过程的作用非常相似，唯一区别是函数只能返回单一值而不能返回多行多列的结果集，当你只需要返回一个值时就可以创建函数。

创建函数和创建过程的两点不同：

参数设置和body内容之间，有一段确定返回值类型以及函数属性的语句段
RETURNS INTEGER
DETERMINISTIC
READS SQL DATA
MODIFIES SQL DATA
……
最后是返回（RETURN）值而不是查询（SELECT）值
RETURN IFNULL(risk_factor, 0);

案例
在上一节的储存过程 get_risk_factor 的基础上，创建函数 get_risk_factor_for_client，计算特定顾客的 risk_factor。还是用右键 Create Function 来简化创建。

创建函数的语法和创建过程的语法极其相似，区别只在两点：

参数设置和body内容之间，有一段确定返回值类型以及函数属性的语句段
最后是返回（RETURN）值而不是查询（SELECT）值

另外，关于函数属性的说明：

DETERMINISTIC 决定性的：唯一输入决定唯一输出，和数据的改动更新无关。这里每个顾客的 risk_factor 会随着其发票记录的增加更新而改变，所以不是DETERMINISTIC的。
READS SQL DATA：需要用到 SELECT 语句进行数据读取的函数，几乎所有函数都满足。
MODIFIES SQL DATA：函数中有增删改或者说有 INSERT DELETE UPDATE 语句，这个例子不满足。

创建function：

CREATE DEFINER=`root`@`localhost` FUNCTION `get_risk_factor_for_client`
(
    client_id INT
) 
RETURNS INTEGER
-- DETERMINISTIC -- 决定性的
READS SQL DATA -- 读取数据库数据
-- MODIFIES SQL DATA -- 更改数据
BEGIN
    DECLARE risk_factor DECIMAL(9, 2) DEFAULT 0;
    DECLARE invoices_total DECIMAL(9, 2);
    DECLARE invoices_count INT;

    SELECT SUM(invoice_total), COUNT(*)
    INTO invoices_total, invoices_count
    FROM invoices i
    WHERE i.client_id = client_id;
    -- 注意不再是整体risk_factor而是特定顾客的risk_factor

    SET risk_factor = invoices_total / invoices_count * 5;
    
    RETURN IFNULL(risk_factor, 0);  -- 注意这里是return而不是select  
END

调用案例：

SELECT 
    client_id,
    name,
    get_risk_factor_for_client(client_id) AS risk_factor
    -- 函数当然是可以处理整列的，我第一时间竟只想到传入具体值
    -- 不过这里更像是一行一行的处理，所以应该每次也是传入1个client_id值
FROM clients

删除，还是用DROP：

DROP FUNCTION [IF EXISTS] get_risk_factor_for_client

删除

DROP FUNCTION [IF EXISTS] 函数名

六、触发器和事件

1、触发器Triggers

小结

触发器是在插入、更新或删除语句前后自动执行的一段SQL代码（A block of SQL code that automatically gets executed before or after an insert, update or delete statement）。通常我们使用触发器来保持数据的一致性。
创建触发器的语法要点：命名三要素，触发条件语句和触发频率语句，主体中 OLD/NEW 的使用。

1. 创建触发器
案例
在 sql_invoicing 库中，发票表中同一个发票记录可以对应付款表中的多次付款记录，发票表中的付款总额应该等于这张发票所有付款记录之和，为了保持数据一致性，可以通过触发器让每一次付款表中新增付款记录时，发票表中相应发票的付款总额（payement_total）自动增加相应数额。

语法上，和创建储存过程等类似，要暂时更改分隔符，用 CREATE 关键字，用 BEGIN 和 END 包裹的主体。

几个关键点：

命名习惯（三要素）：触发表_before/after(SQL语句执行之前或之后触发)_触发的SQL语句类型。
触发条件语句：
BEFORE/AFTER INSERT/UPDATE/DELETE ON 触发表
触发频率语句：这里 FOR EACH ROW 表明每一个受影响的行都会启动一次触发器。其它有的DBMS还支持表级别的触发器，即不管插入一行还是五行都只启动一次触发器，到Mosh录制为止MySQL还不支持这样的功能。
主体：主体里可以对各种表的数据进行修改以保持数据一致性，但注意唯一不能修改的表是触发表，否则会引发无限循环（“触发器自燃”），主体中最关键的是使用 NEW/OLD 关键字来指代受影响的新/旧行（若INSERT用NEW，若DELETE用OLD，若UPDATE似乎两个都可以用？）并可跟 ‘点+字段’ 以引用这些行的相应属性。

DELIMITER $$

CREATE TRIGGER payments_after_insert
    AFTER INSERT ON payments -- 当插入付款记录时，触发该触发器
    FOR EACH ROW
BEGIN -- begin和end之间是该触发器的主体（body）
    UPDATE invoices 
    SET payment_total = payment_total + NEW.amount
    WHERE invoice_id = NEW.invoice_id;
END$$

DELIMITER ;

测试：往 payments 里新增付款记录，发现 invoices 表对应发票的付款总额确实相应更新：

INSERT INTO payments
VALUES (DEFAULT, 5, 3, '2019-01-01', 10, 1) -- 新增一条客户5，发票号码为3的记录

执行前：
在这里插入图片描述
执行后：invoices表自动更新了payment_total的数据。

练习
创建一个和刚刚的触发器作用刚好相反的触发器，每当有付款记录被删除时，自动减少发票表中对应发票的付款总额。

DELIMITER $$

CREATE TRIGGER payments_after_delete
    AFTER delete ON payments -- 当删除付款记录时，触发该触发器
    FOR EACH ROW
BEGIN
    UPDATE invoices
    SET payment_total = payment_total - old.amount
    WHERE invoice_id = old.invoice_id;
END$$

DELIMITER ;

测试：删掉付款表里刚刚的那个给3号发票支付10美元的付款记录，则果然发票表里3号发票的付款总额相应减少10美元。
在这里插入图片描述

2. 查看触发器
用以下命令来查看已存在的触发器及其各要素

SHOW TRIGGERS

如果之前创建时遵行了三要素命名习惯，这里也可以用 LIKE 关键字来筛选特定表的触发器。

SHOW TRIGGERS LIKE 'payments%'

3. 删除触发器
和删除储存过程的语句一样。

DROP TRIGGER [IF EXISTS] payments_after_insert
-- IF EXISTS 是可选的，但一般最好加上

2、使用触发器进行审核

导航
之前已经学习了如何用触发器来保持数据一致性，触发器的另一个常见用途是为了审核的目的将修改数据的操作记录在日志里。

小结
建立一个审核表（日志表）以记录谁在什么时间做了什么修改，实现方法就是在触发器里加上创建日志记录的语句，日志记录应包含修改内容信息和操作信息两部分。

案例
首先，用 create-payments-table.sql 创建 payments_audit 表，记录所有对 payements 表的增删操作，注意该表包含 client_id, date, amount 字段来记录修改的内容信息（方便之后恢复操作，如果需要的话）和 action_type, action_date 字段来记录操作信息。

use sql_invoicing;

create table payments_audit
(
	client_id 		int 			not null,
    date			date			not null,
    amount			decimal(9,2)	not null,
    action_type		varchar(50)		not null,
    action_date		datetime		not null
)

然后，重建在 payments 表里的的增删触发器payments_after_insert 和 payments_after_delete，在触发器里加上往 payments_audit 表里添加日志记录的语句。
具体而言：
往 payments_after_insert 的主体里加上这样的语句：

INSERT INTO payments_audit
VALUES (NEW.client_id, NEW.date, NEW.amount, 'insert', NOW());

往 payments_after_delete 的主体里加上这样的语句：

INSERT INTO payments_audit
VALUES (OLD.client_id, OLD.date, OLD.amount, 'delete', NOW());

完整的语句如下：

DELIMITER $$

DROP TRIGGER IF EXISTS payments_after_insert;

CREATE TRIGGER payments_after_insert
    AFTER insert ON payments -- 当新增付款记录时，触发该触发器
    FOR EACH ROW
BEGIN
    UPDATE invoices
    SET payment_total = payment_total + new.amount
    WHERE invoice_id = new.invoice_id;
    
    INSERT INTO payments_audit -- 将操作记录进新建的审计表payments_audit表
	VALUES (new.client_id, new.date, new.amount, 'insert', NOW());
END$$

DELIMITER ;

DELIMITER $$

DROP TRIGGER IF EXISTS payments_after_delete;

CREATE TRIGGER payments_after_delete
    AFTER delete ON payments -- 当删除付款记录时，触发该触发器
    FOR EACH ROW
BEGIN
    UPDATE invoices
    SET payment_total = payment_total - old.amount
    WHERE invoice_id = old.invoice_id;
    
    INSERT INTO payments_audit  -- 将操作记录进新建的审计表payments_audit表
	VALUES (OLD.client_id, OLD.date, OLD.amount, 'delete', NOW());
END$$

DELIMITER ;

测试：
增：

INSERT INTO payments
VALUES (DEFAULT, 5, 3, '2019-01-01', 10, 1);

删：

DELETE FROM payments
WHERE payment_id = 12 -- 根据具体的payment_id

发现 payments_audit 表里果然多了两条记录以记录这两次增和删的操作。
在这里插入图片描述

注意：
实际运用中不会为数据库中的每张表建立一个审核表，相反，会有一个整体架构，通过一个总审核表来记录，这在之后设计数据库中会讲到。

3、事件 events

小结
事件是一段根据计划执行的代码，可以执行一次，或者按某种规律执行，比如每天早上10点或每月一次。
通过事件我们可以自动化数据库维护任务，比如删除过期数据、将数据从一张表复制到存档表或者汇总数据生成报告，所以事件十分有用。

1. 创建事件
首先，需要打开MySQL事件调度器（event_scheduler），这是一个时刻寻找需要执行的事件的后台程序。
查看MySQL所有系统变量：

SHOW VARIABLES;
SHOW VARIABLES LIKE 'event%';
-- 使用 LIKE 操作符查找以event开头的系统变量
-- 通常为了节约系统资源而默认关闭

用SET语句开启或关闭,不想用事件时可关闭以节省资源，这样就不会有一个不停寻找需要执行的事件的后台程序：

SET GLOBAL event_scheduler = ON/OFF

案例

创建这样一个 yearly_delete_stale_audit_row 事件，每年删除过期的（超过一年的）日志记录（stale adj. 陈腐的；不新鲜的）。

DELIMITER $$

CREATE EVENT yearly_delete_stale_audit_row

-- 设定事件的执行计划：
ON SCHEDULE  
	-- AT '2019-01-01' -- 只执行一次
    EVERY 1 YEAR STARTS '2019-01-01' ENDS '2029-01-01'  -- 每年

-- 主体部分：（注意 DO 关键字）
DO BEGIN
    DELETE FROM payments_audit
    WHERE action_date < NOW() - INTERVAL 1 YEAR; -- 可以用date_add()或date_sub()函数代替
END$$

DELIMITER ;

关键点：

命名：用时间间隔（频率）开头，可以方便之后分类检索，时间间隔（频率）包括【once】/hourly/daily/monthly/yearly 等等。
执行计划：规律性周期性执行用 EVERY 关键字，可以是 EVERY 1 HOUR / EVERY 2 DAY 等等。若只执行一次就用 AT 关键字，如：AT '2019-05-01’开始 STARTS 和结束 ENDS 时间都是可选的。
NOW() - INTERVAL 1 YEAR 等效于 DATE_ADD(NOW(), INTERVAL -1 YEAR) 或 DATE_SUB(NOW(), INTERVAL 1 YEAR)，但感觉不用DATEADD/DATESUB函数，直接相加减（但INTERVAL关键字还是要用）还简单直白点。

2. 查看、删除、更改事件

查（SHOW）和删（DROP）和之前的类似：

SHOW EVENTS; 
-- 可看到各个数据库的事件

SHOW EVENTS LIKE 'yearly%';  
-- 之前命名以时间间隔开头这里才能这样筛选

DROP EVENT IF EXISTS yearly_delete_stale_audit_row;

“改” 要特殊一些，这里首次用到 ALTER 关键字，而且有两种用法：
①如果要修改事件内容（包括执行计划和主体内容），直接把 ALTER 当 CREATE 用（或者说更像是REPLACE）直接重建语句。
在这里插入图片描述
②暂时地启用或停用事件（用 DISABLE 和 ENABLE 关键字）。

ALTER EVENT yearly_delete_stale_audit_row DISABLE/ENABLE

七、事务与并发

1、事务

事务
事务（trasaction）是完成一个完整事件的一系列SQL语句。这一组SQL语句是一条船上的蚂蚱，要不然都成功，要不然都失败，如果一部分执行成功一部分执行失败那成功的那一部分就会复原（revert）以保持数据的一致性。

例子1
银行交易：你给朋友转账包含从你账户转出和往他账户转入两个步骤，两步必须同时成功，如果转出成功但转入不成功则转出的金额会返还。

例子2
订单记录：之前学过向父子表插入分级（层）/耦合数据，一个订单 (order) 记录对应多个订单项目 (order_items) 记录，如果在记录一个新订单时，订单记录录入成功但对应的订单项目记录录一半系统就崩了，那这个订单的信息就是不完整的，我们的数据库将失去数据一致性。

ACID 特性
事务有四大特性，总结为 ACID（刚好是英文单词“酸的”）：

Atomicity 原子性，即整体性，不可拆分行（unbreakable），所有语句必须都执行成功事务才算完成，否则只要有语句执行失败，已执行的语句也会被复原。
Consistency 一致性，指的是通过事务我们的数据库将永远保持一致性状态，比如不会出现没有完整订单项目的订单。
Isolation 隔离性，指事务间是相互隔离互不影响的，尤其是需要访问相同数据时。具体而言，如果多个事务要修改相同数据，该数据会被锁定，每次只能被一个事务有权修改，其它事务必须等这个事务执行结束后才能进行。
Durability 持久性，指的是一旦事务执行完毕，这种修改就是永久的，任何停电或系统死机都不会影响这种数据修改。

2、创建事务

小结
用 START TRANSACTION 来开始创建事务，用 COMMIT 来关闭事务。

START TRANSACTION;

……;

COMMIT / ROLLBACK;

案例
创建一个事务来储存订单及其订单项目（为了简化，这个订单只有一个项目）。

USE sql_store;

START TRANSACTION;

INSERT INTO orders (customer_id, order_date, status)
VALUES (1, '2019-01-01', 1);
-- 只需明确声明并插入这三个非自增必须（不可为空）字段

INSERT INTO order_items 
-- 所有字段都是必须的，就不必申明了
VALUES (last_insert_id(), 1, 2, 3); -- last_insert_id()函数用来返回最近一次插入的id

COMMIT;

执行，会看到最新的订单和订单项目记录。

当 MySQL 看到上面这样的事务语句组，会把所有这些更改写入数据库，如果有任何一个更改失败，会自动撤销之前的修改，这种情况被称为事务被退回（回滚）（is rolled back）。

为了模拟退回的情况，可以用 Ctrl + Enter 逐条执行语句，执行一半，即录入了订单但还没录入订单项目时断开连接（模拟客户端或服务器崩溃或断网之类的情况），重连后会发现订单和订单项目都没有录入。

手动退回
多数时候是用上面的 START TRANSACTION + COMMIT 来创建事务，但当我们想先进行一下事务里语句的测试/错误检查并因此想在执行结束后手动退回时，可以将最后的 COMMIT; 换成 ROLLBACK;，这会退回事务并撤销所有的更改。

3、并发和锁定

并发
之前都只有一个用户访问数据，现实中常出现多个用户访问相同数据的情况，这被称为“并发”（concurrency），当一个用户企图修改另一个用户正在检索或修改的数据时，并发会成为一个问题。

案例
假设要通过如下事务给1号顾客的积分增加10分：

USE sql_store;
START TRANSACTION;
UPDATE customers
SET points = points + 10
WHERE customer_id = 1;
COMMIT;

现在有两个会话（注意是两个链接（connection），而不是同一个会话下的两个SQL标签，这两个链接相当于是在模拟两个用户）都要执行这段语句，用 Ctrl+Enter 逐句执行。
当第一个执行到UPDATE 而还没有 COMMIT 提交时，转到第二个会话，执行到UPDATE语句时会出现旋转指针表示在等待执行（若等的时间太久会超时而放弃执行），这时跳回第一个对话 COMMIT 提交，第二个会话的 UDDATE 才不再转圈而得以执行，最后将第二段对话的事务也COMMIT提交，此时刷新顾客表会发现1号顾客的积分多了20分。

上锁
所以，可以看到，当一个事务修改一行或多行时，会给这些行上锁，这些锁会阻止其他事务修改这些行，直到前一个事务完成（不管是提交还是退回）为止。
由于上述MySQL默认状态下的锁定行为，多数时候不需要担心并发问题，但在一些特殊情况下，默认行为不足以满足你应用里的特定场景，这时你可以修改默认行为，这正是我们接下要学习的。
在这里插入图片描述

4、并发问题

常见的并发问题：

1. Lost Updates 丢失更新
当事务A要更新john的所在州而事务B要更新john的积分时，若两个事务都读取了john的记录，在A跟新了州且尚未提交时，B更新了积分，那后执行的B的更新会覆盖先执行的A的更新，州的更新将会丢失。
解决方法就是前面说的锁定机制，锁定会防止多个事务同时更新同一个数据，必须一个完成的再执行另一个。
在这里插入图片描述

2. Dirty Reads 脏读
事务A将某顾客的积分从10分增加为20分，但在提交前就被事务B读取了，事务B按照这个尚未提交的顾客积分确定了折扣数额，可之后事务A被退回了，所以该顾客的积分其实仍然是10分，因此事务B等于是读取了一个数据库中从未提交的数据并以此做决定，这被称作为脏读。
解决办法是设定事务的隔离等级，例如让一个事务无法看见其它事务尚未提交的更新数据。标准SQL有四个隔离等级，比如，我们可以把事务B设为 READ COMMITED 等级，它将只能读取提交后的数据。
积分提交完之后，B事务依此做决定，如果之后积分再修改，这就不是我们考虑的问题了，我们只需要保证B事务读取的是提交后的数据就行了。
在这里插入图片描述

3. Non-repeating Reads 不可重复读取（或 Inconsistent Read 不一致读取）
上面的隔离能保证只读取提交过的数据，但有时会发生一个事务读取同一个数据两次但两次结果不一致的情况。
例如，事务A的语句里需要读取两次某顾客的积分数据，读取第一次时是10分，此时事务B把该积分更新为0分并提交，然后事务A第二次读取积分为0分，这就发生了不可重复读取或不一致读取。
在这里插入图片描述
一种说法是，我们应该总是依照最新的数据做决定，所以这不是个问题。在商务场景中，我们一般不用担心这个问题。
另一种说法是，我们应该保持数据一致性，以事务A在开始执行时的数据初始状态为依据来做决定，如果这是我们想要的话，就要增加事务A的隔离等级，让它在执行过程中看不见其它事务的数据更改（即便是提交过的），SQL有个标准隔离等级叫 Repeatable Read 可重复读取，可以保证读取的数据是可重复和一致的，无论过程中其它事务对数据做了何种更改，读取到的都是数据的初始状态。
在这里插入图片描述

4. Phantom Reads 幻读（n. 幽灵；幻影，幻觉）
事务A要查询所有积分超过10的顾客并向他们发送带折扣码的E-mail，查询后执行结束前，事务B更新了（可能是增删改）数据，然后多了一个满足条件的顾客，事务A执行结束后就会有这么一个满足条件的顾客没有收到折扣码，这就是幻读，Phantom是幽灵的意思，这种突然出现的数据就像幽灵一样，我们在查询中错过了它因为它是在我们查询语句后才更新的。
在这里插入图片描述
解决办法取决于想解决的商业问题具体是什么样的以及把这个顾客包括进事务中有多重要。我们总可以再次执行事务A来让这顾客包含进去。
但如果确保我们总是包含了最新的所有满足条件的顾客是至关重要的，我们就要保证查询过程中没有任何其他可能影响查询结果的事务在进行，为此，我们建立另一个隔离等级叫 Serializable 序列化，它让事务能够知晓是否有其它事务正在进行可能影响查询结果的数据更改，并会等待这些事务执行完毕后再执行，这是【最高的隔离等级】，为我们提供了最高的操作确定性。
在这里插入图片描述
但 Serializable 序列化等级是有代价的，当用户和并发增加时，等待的时间会变长，系统会变慢，所以这个隔离等级会影响性能和可扩展性，出于这个原因，我们只要在避免幻读确实必要的情形下才使用这个隔离等级。

5、事务隔离级别

总结：并发问题与隔离等级
在这里插入图片描述
四个并发问题：

Lost Updates 丢失更新：两个事务更新同一行，最后提交的事务将覆盖先前所做的更改
Dirty Reads 脏读：读取了未提交的数据
Non-repeating Reads 不可重复读取（或 Inconsistent Read 不一致读取）：在事务中读取了相同的数据两次，但得到了不同的结果
Phantom Reads 幻读：在查询中缺失了一行或多行，因为另一个事务正在修改数据而我们没有意识到事务的修改，我们就像遇见了鬼或者幽灵。

为了解决这些问题，我们有四个标准的事务隔离等级：

Read Uncommitted 读取未提交：无法解决任何一个问题，因为事务间并没有任何隔离，他们甚至可以读取彼此未提交的更改
Read Committed 读取已提交：给予事务一定的隔离，这样我们只能读取已提交的数据，这防止了Dirty Reads 脏读，但在这个级别下，事务仍可能读取同个内容两次而得到不同的结果，因为另一个事务可能在两次读取之间更新并提交了数据，也就是它不能防止Non-repeating Reads 不可重复读取（或 Inconsistent Read 不一致读取）
Repeatable Read 可重复读取：在这一级别下，我们可以确信不同的读取会返回相同的结果，即便数据在这期间被更改和提交
Serializable 序列化：可以防止以上所有问题，这一级别还能防止幻读，如果数据在我们执行过程中改变了，我们的事务会等待以获取最新的数据，但这很明显会给服务器增加负担，因为管理等待的事务需要消耗额外的储存和CPU资源。

并发问题 VS 性能和可扩展性：
更低的隔离级别更容易并发，会有更多用户能在相同时间接触到相同数据，但也因此会有更多的并发问题，另一方面因为用以隔离的锁定更少，性能会更高。
相反，更高的隔离等级限制了并发并减少了并发问题，但代价是性能和可扩展性的降低，因为我们需要更多的锁定和资源。
MySQL的默认等级是 Repeatable Read 可重复读取，它可以防止除幻读外的所有并发问题并且比序列化更快，多数情况下应该保持这个默认等级。
如果对于某个特定的事务，防止幻读至关重要，可以改为 Serializable 序列化。
对于某些对数据一致性要求不高的批量报告或者对于数据很少更新的情况，同时又想获得更好性能时，可考虑前两种等级。
总的来说，一般保持默认隔离等级，只在特别需要时才做改变。

设定隔离等级的方法
读取隔离等级：

SHOW VARIABLES LIKE 'transaction_isolation';

在这里插入图片描述
显示默认隔离等级为 ‘REPEATABLE READ’。

改变隔离等级：

SET [SESSION]/[GLOBAL] TRANSACTION ISOLATION LEVEL SERIALIZABLE;

默认设定的是下一次事务的隔离等级，加上 SESSION 就是设置本次会话（链接）之后所有事务的隔离等级，加上 GLOBAL 就是设置之后所有对话的所有事务的隔离等级。

如果你是个应用开发人员，你的应用内有一个功能或函数可以链接数据库来执行某一事务（可能是利用对象关系映射或是直接连接MySQL），你就可以连接数据库，用 SESSION 关键词设置本次链接的事务的隔离等级，然后执行事务，最后断开连接（连接到mysql——更改隔离级别——执行事务——断开连接），这样数据库的其它事务就不会受影响。

6、读取未提交隔离级别 Read Uncommitted

小结
主要通过模拟脏读来表明 Read Uncommitted（读取未提交）是最低的隔离等级并会遇到所有并发问题。

案例
建立链接1和链接2，模拟用户1和用户2，分别执行如下语句：

链接1：
查询顾客1的积分，用于之后的商业决策（如确定折扣等级）。
注意里面的 SELECT 查询语句虽然没被 START TRANSACTION + COMMIT 包裹，但由于 autucommit，MySQL会把执行的每一条没错误的语句包装在事务中并自动提交，所以这个查询语句也是一个事务，隔离等级为上一句设定的 READ UNCOMMITTED（读取未提交）

USE sql_store;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT points
FROM customers
WHERE customer_id = 1

链接2：
建立事务，将顾客1的积分（由原本的2293）改为20

USE sql_store;
START TRANSACTION;
UPDATE customers
SET points = 20
WHERE customer_id = 1;
ROLLBACK;

模拟过程:

链接1将下一次事务（感觉是针对本对话的下一次事务）的隔离等级设定为 READ UNCOMMITTED 读取未提交

→ 链接2执行了更新但尚未提交

→ 链接1执行了查询，得到结果为尚未提交的数据，即查询结果为20分而非原本的2293分

→ 链接2的更新事务被中断退回（可能是手动退回也可能是因故障中断）

这样我们的对话1就使用了一个数据库中从未存在过的值，这就是脏读问题，总之，READ UNCOMMITTED 读取未提交是最低的隔离等级，在这一级别我们会遇到所有的并发问题。

7、读取已提交隔离级别

小结
Read Committed 读取已提交等级只会读取别人已提交的数据，所以不会发生脏读，但因为能够读取到执行过程中别人已提交的更改，所以还是会发生不可重复读取（不一致读取）的问题。

案例1：不会发生脏读
就是把上一节链接1的设置隔离级别语句改为 READ COMMITTED 读取已提交等级，就会发现链接1不会读取到链接2未提交的更改，只有当改为20分的事务提交以后才能被链接1的查询语句读取到

案例2：可能会发生不可重复读取（不一致读取）
虽然不会存在脏读，但会出现其他的并发问题，如 Non-repeating Reads 不可重复读取，即在一个事务中你会两次读取相同的内容，但每次都得到不同的值。
为模拟该问题，将顾客1的分数还原为2293，将上面的连接1里的语句变为两次相同的查询（查询1号顾客的积分），连接2里的UPDATE语句不变，还是将1号顾客的积分（由原本的2293）更改为20。

USE sql_store;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
START TRANSACTION;
SELECT points FROM customers WHERE customer_id = 1;
SELECT points FROM customers WHERE customer_id = 1;
COMMIT;

注意：
虽然案例1里已经执行过一次 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; 但这里还是要再执行一次，因为该语句是设定（本对话内）【下一次（next）】事务的隔离等级，如果这里不执行，事务就会恢复为MySQL默认隔离等级，即 Repeatable Read 可重复读取。
还有因为这里事务里有两个语句，所以必须手动添加 START TRANSACTION + COMMIT 包装成一个事务，否则autocommit会把它们分别包装形成两个事务。

模拟过程：

再次设定隔离等级为 READ COMMITTED，启动事务，执行第一次查询，得到分数为2293

→ 执行链接2的 UPDATE 语句并提交

→ 再执行链接1的第二次查询，得到分数为20，同一个事务里的两次查询得到不同的结果，发生了 Non-repeating Reads 不可重复读取（或 Inconsistent Read 不一致读取）

8、重复读取隔离级别

小结
在这一默认级别上，不仅只会读取已提交的更改，而且同一个事务内读取会始终保持一致性。但可能会忽发生幻读。
之前说了MySQL默认等级正是 REPEATABLE READ（重复读取）而且MySQL默认会在执行事务内的增删改语句时锁定相关行，所以可以判断 REPEATABLE READ（重复读取）正是通过执行修改语句时锁定相关行来避免更新丢失问题的。

案例1：不会发生不可重复读取（不一致读取）
此案例和上一个案例完全一样，只是把隔离等级的设定语句改为了 REPEATABLE READ 可重复读取。然后发现两次查询中途别人把积分从2293改为20不会影响两次查询的结果，都是初始状态的20分，不会发生不可重复读取（不一致读取）。

案例2：可能发生幻读

但这一级别还是会发生幻读的问题，一个模拟情形如下：

用户1：查询在 ‘VA’ 州的顾客

USE sql_store;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT * FROM customers WHERE state = 'VA';
SELECT points FROM customers WHERE customer_id = 1;
COMMIT;

用户2：将1号顾客所在州更改为 ‘VA’

USE sql_store;
START TRANSACTION;
UPDATE customers
SET state = 'VA'
WHERE customer_id = 1;
COMMIT;

假设customer表中原本只有2号顾客在维州（‘VA’）。

→ 用户2现在正要将1号顾客也改为VA州，已执行UPDATE语句但还没有提交，所以这个更改技术上讲还在内存里
→ 此时用户1查询身处VA州的顾客，只会查到2号顾客
→ 用户2提交更改
→ 若1号用户未提交，再执行一次事务中的查询语句会还是只有2号顾客，因为在 REPEATABLE READ 可重复读取隔离级别，我们的读取会保持一致性
→ 若1号用户提交后再执行一次查询，会得到1号和2号两个顾客的结果，我们之前的查询遗漏了2号顾客，这被称作为幻读。

简单讲就是在这一等级下，1号用户在同一个事务内的读取保持一致，因此哪怕2用户已经提交了更改，1用户在当前事务内的查询（当前事务未提交未开始下一次事务前），也不能察觉用户2的更改，导致遗漏这些新的“幽灵”结果。

9、序列化隔离级别

小结
SERIALIZABLE（序列化）是最高隔离等级，它等于是把系统变成了一个单用户系统。所有并发问题得到解决，因为事务是一个接一个按照顺序执行的，前一个未执行完，后一个无法执行。
但是用户越多，同时发生的请求越多，等待时间越长，效率越低。因此只有要求避免幻读时才用这一隔离等级。

案例
和上面那个案例一摸一样，只是把用户1事务的隔离等级设置为 SERIALIZABLE 序列化，模拟场景如下：
→ 用户2现在正要将4号顾客也改为VA州，已执行UPDATE语句但还没有提交，所以这个更改技术上讲还在内存里
→ 此时用户1查询身处VA州的顾客，会察觉到用户2的事务正在进行，因而会出现旋转指针等待用户2的完成
→ 用户2提交更改
→ 用户1的查询语句执行并返回最新结果：顾客1、2、3、4

用户1：

USE sql_store;
SET TRANSACTION ISOLATION LEVEL serializable;
start transaction; --开始事务，下面先不执行，去执行用户2
SELECT * FROM customers WHERE state = 'VA';
commit;

用户2：

USE sql_store;
START TRANSACTION;
UPDATE customers
SET state = 'VA'
WHERE customer_id = 4; --执行到这一步停止，不提交
commit;

在这里插入图片描述
回到用户1，提交，会发现当用户2未提交前，用户1无法提交。

用户2提交后，用户1结束提交，新增一条客户4的结果。

10、死锁

小结
不管什么隔离等级，当一个事务增删改行，会给这些行上锁，这些锁会阻止其他事务修改这些行，直到前一个事务完成（不管是提交还是退回）为止。
如果两个同时在进行的事务分别锁定了对方下一步要使用的行，就会发生死锁，死锁不能完全避免但有一些方法能减少其发生的可能性。

案例
用户1：将1号顾客的州改为’VA’，再将1号订单的状态改为1

USE sql_store;
START TRANSACTION;
UPDATE customers SET state = 'VA' WHERE customer_id = 1;
UPDATE orders SET status = 1 WHERE order_id = 1;
COMMIT;

用户2：和用户1完全相同的两次更改，只是顺序颠倒

USE sql_store;
START TRANSACTION;
UPDATE orders SET status = 1 WHERE order_id = 1;
UPDATE customers SET state = 'VA' WHERE customer_id = 1;
COMMIT;

模拟场景：
用户1和2均执行完各自的第一个更改
→ 用户2执行第二个更改，出现旋转指针
→ 用户1执行第二个更改，出现死锁，报错：Error Code: 1213. Deadlock found ……

缓解方法
死锁如果只是偶尔发生一般不是什么问题，重新尝试或提醒用户重新尝试即可，死锁不可能完全避免，但有一些方法可以最小化其发生的概率：

注意语句顺序：如果检测到两个事务总是发生死锁，检查它们的代码，这些事务可能是储存过程的一部分，看一下事务里的语句顺序，如果这些事务以相反的顺序更新记录，就很可能出现死锁，为了减少死锁，我们在更新多条记录时可以遵循相同的顺序。
尽量让你的事务小一些：持续时间短一些，这样就不太容易和其他事务相冲突。
避开高峰期运行：如果你的事务要操作非常大的表，运行时间可能会很长，冲突的风险就会很高，看看能不能让这样的事务避开高峰期运行，以避开大量活跃用户。

八、数据类型

MySQL的数据分为以下几个大类：

String Types 字符串类型
Numeric Types 数字类型
Date and Time Types 日期和时间类型
Blog Types 存放二进制的数据类型
Spatial Types 存放地理数据的类型

1、字符串 string

小结
在这里插入图片描述

最常用的两个字符串类型：
1.CHAR() ：固定长度的字符串，如州（‘CA’, ‘NY’, ……）就是 CHAR(2)
2.VARCHAR() ：可变字符串。VARCHAR 最多能储存 64KB, 也就是最多约 65k 个字符（如果都是英文即每个字母只占一字节的话），超出部分会被截断。

注：字符串类型也可以用来储存邮编，电话号码这样的特殊的数字型数据，因为它们不会用来做数学运算而且常常包含‘-’或括号等分隔符号。
Mosh习惯用 VARCHAR(50) 来记录用户名和密码这样的短文本以及用 VARCHAR(255) 来记录像地址这样较长一些的文本，保持这样的习惯能够简化数据库设计，不必每次都想每一列该用多长的 VARCHAR。

储存较大文本的两个类型
3. MEDIUMTEXT 最大储存16MB（约16百万的英文字符），适合储存JSON对象，CS视图字符串，中短长度的书籍。
4. LONGTEXT 最大储存4GB，适合储存书籍和以年记的日志。

还有两个用的少一些的：
5. TINYTEXT 最大储存 255 Bytes。
6. TEXT 最大储存 64KB，最大储存长度和 VARCHAR 一样，但最好用 VARCHAR，因为 VARCHAR 可以使用索引（之后会讲，索引可以提高查询速度）。

国际字符：
所有这些字符串类型都支持国际字符，其中：

英文字符占1个字节
欧洲和中东语言字符占2个字节
像中日这样的亚洲语言的字符占3个字节

2、整数 integer

小结
我们用整数类型来保存没有小数的数字，MySQL里共有5种常用的整数类型：
在这里插入图片描述
属性1. 不带符号 UNSIGNED
这些整数可以选择不带符号，加上 UNSIGNED 则只储存非负数。如最常用的 UNSIGNED TINYINT，占用空间和 TINYINT 一样也是1B，但表示的数字范围不是 [-128-127] 而是 [0-255]，适合储存像年龄这样的数据，可以防止意外输入负数
属性2. 填零 ZEROFILL
整数类型的另一个属性是填零（Zerofill），主要用于当你需要给数字前面添加零让它们位数保持一致时。我们用括号表示显示位数，如 INT(4) => 0001，注意这只影响MySQL如何显示数字而不影响如何保存数字。

最佳实践
总是使用能满足你需求的最小整数类型。

3、定点数和浮点数 fixed-point & floating-point

小结

定点数：如果需要记录精确的数字，比如货币金额，就是用 DECIMAL 类型。
浮点数：如果要进行科学计算，要处理很大或很小的数据，而且精确值不重要的话，就用 FLOAT 或 DOUBLE。

在这里插入图片描述

Fixedpoint Types 定点数类型
DECIMAL(p, s) ：两个参数分别指定最大的有效数字位数和小数点后小数位数（小数位数固定）。
如：DECIMAL(9, 2) => 1234567.89 总共最多9位，小数点后两位，整数部分最多7位。
注：DECIMAL 还有几个别名，DEC / NUMERIC / FIXED，都是一样的意思，最好就使用 DECIMAL 以保持一致性。

Floatingpoint Types 浮点数类型
进行科学计算，要计算特别大或特别小的数时，就会用到浮点数类型，浮点数不是精确值而是近似值，这也正是它能表示更大范围数值的原因。具体有两个类型：

FLOAT 浮点数类型，占用4B。
DOUBLE 双精度浮点数，占用8B，显然能比前者储存更大范围的数值。

4、布尔类型 boolean

在这里插入图片描述
布林值其实本质上就是微整数 TINYINT 的另一种表现形式，TRUE / FALSE 实质上就是 1 / 0。
案例

UPDATE posts 
SET is_published = TRUE / FALSE
-- 或
SET is_published = 1 / 0

6、枚举和集合类型 enum and set

有时我们希望某个字段从固定的一系列值中取值，我们就可以用到 ENUM() 和 SET() 类型，前者是取一个值，后者是取多个值。
案例
例如，我们希望 sql_store.products（产品表）里多一个size（尺码）字段，取值为 small/medium/large 中的一个，可以打开产品表的设计模式，添加size列，数据类型设置为 ENUM(‘small’,‘medium’,‘large’)，然后apply。
则产品表会增加一个尺码列，可将其中的值设为small/medium/large(大小写无所谓)，但若设为其他值会报错。

注意
讲解 ENUM 和 SET 只是为了眼熟，最好不要用这两个数据类型，问题很多：

修改可选的值（如想增加一个’extra large’）会重建整个表，耗费资源
想查询可选值的列表或者想用可选值当作一个下拉列表都会比较麻烦
难以在其它表里复用，其它地方要用只有重建相同的列，之后想修改就要多处修改，又会很麻烦。

最佳实践
像这种某个字段是从固定的一系列值中取值的情况，不应该使用 ENUM 和 SET ，而应该用这一系列的可选值另外建一个 “查询表” (lookup table)。
例如，sql_invoicing 里为支付方式另外专门建了一个 payment_methods 可选支付方式表。这样就解决了上面的所有问题，既方便查询可选值的列表，也方便作为下拉选项，也方便复用和更改。

7、日期和时间类型 Date and Time Types

MySQL 有4种储存日期事件的类型：

DATE 有日期没时间
TIME 有时间没日期
DATETIME 包含日期和时间
TIMESTAMP 时间戳，常用来记录一行数据的的插入或最后更新时间。

最后两个的区别是：
TIMESTAMP 占4B，最晚记录到2038年，被称为“2038年问题”。
DATETIME 占8B，如果要储存超过2038年的日期时间，就要用 DATETIME。
另外，还有一个 YEAR 类型专门储存四位的年份。
在这里插入图片描述

8、二进制长对象 blob

小结
我们用 Blob 类型来储存大的二进制数据，包括PDF，图像，视频等等几乎所有的二进制的文件。
具体来说，MySQL里共有4种 Blob 类型，它们的区别在于可储存的最大文件大小：
在这里插入图片描述

注意：
通常不应该将二进制文件存放在数据库中，关系型数据库是设计来专门处理结构化关系型数据而非二进制文件的。

如果将文件储存在数据库内，会有如下问题：

数据库的大小将迅速增长
备份会很慢
性能问题，因为从数据库中读取图片永远比直接从文件系统读取慢
需要额外的读写图像的代码

所以，尽量别用数据库来存文件，除非这样做确实有必要而且上面这些问题已经被考虑到了。

9、json类型

小结

JSON标准格式为：{“key”:value,……}，可以对json对象中的键和值进行增删改查：

增：利用标准格式或利用 JSON_OBJECT, JSON_ARRAY 等内置函数
查：JSON_EXTRACT 或 ->/–>，注意表达路径时单引号、 $ 和 . 的使用
改：JSON_SET
删：JSON_REMOVE

关于JSON
MySQL还可以储存 JSON 文件，JSON 是 JavaScript Object Notation（JavaScript 对象标记法）的简称。简单讲，JSON 是一种在互联网上储存和传播数据的简便格式（Lightweight format for storing and transferring data over the Internet）
JSON 在网络和移动应用中被大量使用，多数时候你的手机应用向后端传输数据都是使用 JSON 格式。

JSON的语法结构：

{
    "key1": value1,
    "key2": value2,
    ……
}

JSON 用大括号{}表示一个对象，里面有多对键值对。
键 key 必须用引号包裹（而且似乎必须是双引号，不能用单引号）。
值 value可以是数值，布林值，数组，文本，甚至另一个对象（形成嵌套 JSON 对象）。

案例
用 sql_store 数据库，在 products 商品表里，在设计模式下新增一列 properties，设定为 JSON 类型。
在这里插入图片描述这里的 properties 记录每件产品附加的独特属性。如衣服是颜色和尺码，而电视机是的重量和尺寸，把所有可能的属性都作为不同的列添加进表是很糟糕的设计，因为每个商品都只能用到所有这些属性的一部分（一个子集）。相反，通过增加一列 JSON 类型的 properties 列，我们可以利用 JSON 里的键值对很方便的储存每个商品独特的属性。

增
给1号商品增加一系列属性，有两种方法（两个方法是等效的）：

法1：
用单引号包裹（注意不能是双引号），里面用 JSON 的标准格式：

双引号包裹键 key（注意不能是单引号）
值 value 可以是数、数组、甚至另一个用 {} 包裹的JSON对象
键值对间用逗号隔开

UPDATE products
SET properties = '
{
	"dimensions": [1, 2, 3],
    "weight": 10,
    "manufacturer":{"name":"sony"}
}
'
WHERE product_id = 1;

法2：
也可以用 MySQL 里的一些针对 JSON 的内置函数来创建商品属性：

UPDATE products
SET properties = JSON_OBJECT(
    'weight', 10,
    -- 注意用函数的话，键值对中间是逗号而非冒号
    'dimensions', JSON_ARRAY(1, 2, 3),
    'manufacturer', JSON_OBJECT('name', 'sony')
)
WHERE product_id = 1;

查
查询 JSON 对象里的特定键值对，这是将某一列设为 JSON 对象的优势所在，如果 properties 列是字符串类型如 VARCHAR 等，是很难获取特定的键值对的。
有两种方法：

法1 :
使用 JSON_EXTRACT(JSON对象, ‘路径’) 函数，其中：

第1参数指明 JSON 对象
第2参数是用单引号包裹的路径，路径中 $ 表示当前对象，点操作符 . 表示对象的属性

SELECT product_id, JSON_EXTRACT(properties, '$.weight') AS weight
FROM products
WHERE product_id = 1;
-- 结果为：10

法2：
更简便的方法，使用列路径操作符 -> 和 ->>（后者可以去掉结果外层的引号）。
用法是：JSON对象 -> ‘路径’

SELECT product_id, properties -> '$.weight' AS weight
FROM products
WHERE product_id = 1;
-- 等同于法1
-- 结果为：10

SELECT product_id, properties -> '$.dimensions'
FROM products
WHERE product_id = 1;
-- 查询值为数组的键
-- 结果为：[1, 2, 3]

SELECT product_id, properties -> '$.dimensions[0]'
FROM products
WHERE product_id = 1;
-- 访问数组中的单独项目，用中括号索引切片，且序号从0开始，与Python同
-- 结果为：1

SELECT product_id, properties -> '$.manufacturer'
FROM products
WHERE product_id = 1;
-- 访问嵌套对象
-- 结果为：{"name": "sony"}

SELECT product_id, properties -> '$.manufacturer.name'
FROM products
WHERE product_id = 1;
-- 用点运算符，访问嵌套对象中的单个属性
-- 结果为："sony"

SELECT product_id, properties ->> '$.manufacturer.name'
FROM products
WHERE product_id = 1;
-- 加一个大于号，去掉结果中的双引号
-- 结果为：sony

通过路径操作符来获取 JSON 对象的特定属性不仅可以用在 SELECT 选择语句中，也可以用在 WHERE 筛选语句中，如：筛选出制造商名称为 sony 的产品：

SELECT 
    product_id, 
    properties ->> '$.manufacturer.name' AS manufacturer_name
FROM products
WHERE properties -> '$.manufacturer.name' = 'sony'
-- WHERE properties ->> '$.manufacturer.name' = 'sony' -- 结果一样

改
如果我们是要重新设置整个 JSON 对象就用前面增里讲到的 JSON_OBJECT() 函数。但如果是想新增或者修改已有 JSON 对象里的一些属性，就要用 JSON_SET() 函数。

USE sql_store;
UPDATE products
SET properties = JSON_SET(
    properties, -- 对象
    '$.weight', 20,  -- 修改weight属性
    '$.age', 10  -- 新增age属性
)
WHERE product_id = 1;

第1参数是要修改的 JSON 对象。
后面是一个或者多个键值。

删
可以用 JSON_REMOVE() 函数实现对已有 JSON 对象特性属性的删除，原理和 JSON_SET() 一样。

USE sql_store;
UPDATE products
SET properties = JSON_REMOVE(
    properties, -- 第1参数是要删除的JSON对象
    '$.age'
)
WHERE product_id = 1;

九、设计数据库

1、介绍

1-1、数据建模过程

概念模型：概念模型主要是总览业务需求，识别业务中的实体/事物/概念以及他们彼此间的关系，通常这些实体包括人、事件、地点等。这一步暂不考虑数据类型和具体的DBMS这样的技术细节，只是从概念上总揽全局，目的是和业务人员交流，保持理解一致。
逻辑模型：对概念模型逻辑化的过程，在不依赖特定数据库系统的前提下确定数据结构。主要为：细化实体间关系，调整字段并大体确定字段的数据类型。逻辑模型会基本确立数据库中的表、列以及表间关系。
实体模型：实体模型就是逻辑模型在具体DBMS的实现，主要是一些技术上的细化，包括确定字段具体数据类型和性质（能否为空等），设置主键等。在 Workbench-file-new model 新建数据库模型。

1-2、主键、外键
外键约束：

1-3、标准化
是数据库设计的一系列原理和技术，以减少数据库中数据冗余，增进数据的一致性。99%的数据库之需要遵循前三大范式就够了。

第一范式：每个单元格都应该是单一值并且不能有重复的列。解决方法：在多对多的关键间创建链接表，细化多对多关系。
第二范式：每个表都应该是单一功能的/应该表示一个实体类型，这个表的所有字段都是用来描述这个实体的。
第三范式：一个表中的字段不应该是由表中其他字段推导（计算）而来。

1-4、模型&数据库的搭建

正向工程：通过模型正向搭建数据库：workbench 菜单的 Database 选项 → Forward Engineer 正向搭建数据库。
逆向工程：通过数据库逆向建立实体模型。如果要修改没有实体模型的数据库，第一次可以先逆向工程（Reverse Engineering）建立模型，之后每次就可以在该模型上修改了。

2、数据库同步模型

之后可能会修改数据库结构，比如更改某些表中字段的数据类型或增加字段之类，如果只是自己一个人用的一个本地数据库，可以直接打开对应表的设计模式并点击更改即可，但如果是在团队中工作通常不是这样。

在中大型团队中，我们通常有多个服务器来模拟各种环境，其中有：

生产环境（production environment）：用户真正访问应用和数据库的地方
模拟环境（staging environment）：与生产环境十分接近
测试环境（testing environment）：存粹用来做测试的
开发环境（development environment）

每次需要对数据库做修改时我们需要复制相同的修改到不同的环境以保持数据的一致性。如果是在团队中可能需要选择测试环境、模拟环境甚至开发环境的链接以对相应环境中的数据库执行更改，MySQL会自动检测到需要修改的是哪个数据库并提示要修改的表。

案例
例如我们想在 enrollments 中加上一个 coupons 折扣券字段。会提示将影响的表除了 enrollments 还有 courses 等表，因为这些表与要修改的表是相互关联的，之后的 SQL 的语句会先暂时删除相关外键以消除这些联系，对目标表做出相应更改（增加 coupons 字段）后再重建这些联系。
在这里插入图片描述

2、创建和删除数据库

可以用 workbench 的向导来创建和修改数据库能够提高效率。也可以手动写代码完成创建和修改数据库的操作，而不依赖工具。

CREATE DATABASE IF NOT EXISTS sql_store2;
DROP DATABASE IF EXISTS sql_store2

3、创建表

案例
在新建的库sql_store2下，手动创建customers表：

CREATE DATABASE IF NOT EXISTS sql_store2;
USE sql_store2;

DROP TABLE IF EXISTS customers;
CREATE TABLE IF NOT EXISTS customers -- 没有就创建，有的话就不做改变
(
    -- 只挑选几个字段来建立
    customer_id INT PRIMARY KEY AUTO_INCREMENT, -- 主键、自动递增
    first_name VARCHAR(50) NOT NULL, -- 非空
    points INT NOT NULL DEFAULT 0, -- 默认值
    email VARCHAR(255) NOT NULL UNIQUE -- UNIQUE 确保 email 值唯一，即每个用户的 email 必须不一样
);

或

USE sql_store2;

DROP TABLE IF EXISTS customers;
CREAT TABLE customers -- 没有就创建，有的话就推倒重建

注：左侧栏导航窗口选择某表中的列时，下面的 Object Info 可以查看列的数据类型。

4、更改表

USE sql_store2;
ALTER TABLE customers
	add last_name varchar(50) not null after first_name, -- 新建字段的位置
    add city	  varchar(50) not null,
    modify first_name varchar(55) default '', -- 更改字段类型，设置默认值为空
    drop points -- 删除已有字段
    ;

5、创建关系

案例
前面已经在新的 store2 数据库中创建了 customers 表，这里我们接着创建 orders 表，并在表中添加 customer_id 外键来建立表间关系。

CREATE DATABASE IF NOT EXISTS sql_store2;
USE sql_store2;

DROP TABLE IF EXISTS orders; -- customers表是关系的一部分，因此需要先删除orders表才能删除customers表
DROP TABLE IF EXISTS customers;

CREATE TABLE IF NOT EXISTS customers 
(	
    customer_id INT PRIMARY KEY AUTO_INCREMENT, -- 主键、自动递增
    first_name VARCHAR(50) NOT NULL, -- 非空
    points INT NOT NULL DEFAULT 0, -- 默认值
    email VARCHAR(255) NOT NULL UNIQUE -- UNIQUE 确保 email 值唯一，即每个用户的 email 必须不一样
);

CREATE TABLE orders
(
	order_id	INT PRIMARY KEY,
    customer_id INT NOT NULL,
    FOREIGN KEY fk_orders_customers(customer_id) -- 外键
		REFERENCES customers(customer_id) -- 引用了customers表中的customer_id列
		ON UPDATE CASCADE -- 外键约束
        ON DELETE NO ACTION
)

注意：这是因为建立主外键关系后， customers 现在和 orders 是父子表，orders 表依赖于 customers 表，所以必须先删除 orders 表才能删除 customers 表，所以应该把 orders 表的 DROP 语句放到最前面。

外键名的命名习惯：

fk（foreign key 的缩写）_子表名_父表名

设置外键的语法结构：

FOREIGN KEY 外键名 (外键字段)
        REFERENCES 父表 (主键字段)
        -- 设置外键约束：
        ON UPDATE CASCADE
        ON DELETE NO ACTION

6、更改主键和外键约束

ALTER TABLE orders
	ADD PRIMARY KEY (order_id), -- 新增主键
    DROP PRIMARY KEY, -- 删除主键
    DROP FOREIGN KEY fk_orders_customers, -- 删除外键
    ADD FOREIGN KEY fk_orders_customers(customer_id) -- 新增外键
		REFERENCES customers(customer_id)
		ON UPDATE CASCADE
        ON DELETE NO ACTION;

注：像增删主键这种既可以用菜单点击也可以用代码运行实现的操作（Workbench里这种操作相当多了），当忘记相关SQL代码写法时，可以通过菜单点击方式操作然后在 Review the SQL script 那一步看一看，就知道代码怎么写的了。

7、字符集和排序规则

字符是以数字序列的形式储存于电脑中的，字符集是数字序列与字符相互转换的字典，不同的字符集支持不同的字符范围，有些支持拉美语言字符，有些也支持亚洲语言字符，有些支持全世界所有字符，查看MySQL支持的所有字符集：

SHOW CHARSET;

其中 armscii8 支持亚美尼亚语，big5 支持繁体中文，gb2312 和 gbk 支持简体中文，而 utf-8支持全世界的语言，utf-8 也是MySQL自版本5之后的默认字符集。

还可以看到字符集描述，默认排序规则，最大长度。
【排序规则】指的是某语言内字符的排序方式，utf-8 的默认排序规则是 utf8_general_ci，其中 ci 表示 case insensitive 大小写不敏感，即MySQL在排序时不会区分大小写，这在大部分时候都是适用的，比如用户输入名字的时候大小写不固定，我们希望只按照字符顺序而不管大小写来对名字进行排序。总之，99.9% 的情况下都不需要更改默认排序规则。
【最大长度】指的是对该字符集来说，给每个字符预留的最大字节数，如 latin1 是 1 字节，utf-8 就是 3 Byte。前面说过，在utf-8里，拉丁字符使用 1 字节，欧洲和中东字符使用 2 字节，亚洲语言的字符使用 3 字节，所以 utf-8 给每个字符预留 3 字节。
对于字符集来说，大部分时候用默认的 utf-8 就行了。但有时，我们可以通过更改字符集来减少空间占用，例如，我们某个特定的应用（对应的数据库）/特定表/特定列是只能输入英文字符的，那如果将该列的字符集从 utf-8 改为 latin1，占用空间就会缩小到原来的 1/3，以字段类型为 CHAR(10)（固定预留10个字符）且有 1 百万条记录为例，占用空间就会从约 30MB 减到 10MB。

十、数据索引

1、介绍

这一章我们来看提高性能的索引，索引对大型和高并发数据库非常有用，因为它可以显著提升查询的速度。

原理和作用
以寻找所在州（state）为 ‘CA’ 的顾客为例，如果没索引，MySQL 就必须扫描筛选所有记录。索引，就好比书籍最后的那些关键词索引一样，按字母排序，这样就能按字母迅速找到需要的关键词所在的页数，类似的，对 state 字段建立索引时，其实就是把 state 列单独拿出来分类排序并建立与原表顾客记录的对应关系，然后就可以通过该索引迅速找到所在州为 ‘CA’ 的顾客。
另一方面，索引会比原表小得多，通常能够储存在内存中，而从内存读取数据总是比从硬盘读取快多了，这也会提升查询速度。

注意
建立索引也是有代价的，首先索引会占用内存，其次它会降低写入速度，因为每次修改数据时都会自动重建索引。
所以不要根据【表】建立索引，而是要针对关键的【查询】建立索引。

2、创建索引

小结

解释性查询是在查询语句前加上 EXPLAIN。
创建索引的语法：

CREATE INDEX 索引名（通常是 idx_列名） ON 表名 (列名);

案例
准备：打开 load_1000_customers.sql 并运行，该文件会向 sql_store 库的 customers 表插入上千条记录。在这里插入图片描述

练习
解释性查询积分过千的顾客id，建立索引后再来一次并对比两次结果。

EXPLAIN SELECT customer_id 
FROM customers WHERE points > 1000;

CREATE INDEX idx_points ON customers (points);

建立索引后的查询 type 为 range，表明我们查询的是一个取值范围的记录，扫描的行数 rows 从 1010 降为了 529，减了一半左右。
在这里插入图片描述

3、查看索引

实例1
查看 customers 表的索引：

SHOW INDEXES IN customers;
-- SHOW INDEXES IN 表名

可以看到有三个索引，第一个是 MySQL 为主键 customer_id 创建的索引 PRIMARY，被称作clustered index 聚合索引，每当我们为表创建主键时，MySQL 就会自动为其创建索引，这样就能快速通过主键（通常是某id）找到记录。
后两个是我们之前手动为 state 和 points 字段建立的索引 idx_state 和 idx_points，它们是 secondary index（从属索引、二级索引），MySQL 在创建从属索引时会自动为其添加主键列，如每个 idx_points 索引的记录有两个值：客户的积分points 和对应的客户编号 customer_id，这样就可以通过客户积分快速找到对应的客户记录。在这里插入图片描述
索引查询表中还列示了索引的一些性质，其中：

Non_unique 是否是非唯一的，即是否是可重复的、可相同的，一般主键索引是0，其它是1。
Column_name 表明索引建立在什么字段上Collation 是索引内数据的排序方式，其中A是升序，B是降序。
Cardinality（基数）表明索引中独特值/不同值的数量，如 PRIMARY 的基数就是 1010，毕竟每条记录都都有独特的主键，而另两个索引的基数都要少一些，从之前 Non_unique 为 1 也可以看得出来 state 和 points 有重复值，这里的基数可以更明确看到 state 和 points 具体有多少种不同的值。
Index_type 都是BTREE（二叉树），之前说过MySQL里大部分的索引都是以二叉树的形式储存的。

实例2
查看orders表的索引：

SHOW INDEXES IN orders;

总共有四个： PRIMARY、fk_orders_customers_idx、fk_orders_shippers_idx、fk_orders_order_statuses_idx，第一个是建立在主键order_id上的聚合索引，后三个是建立在三个外键 customer_id、shipper_id、status 上的从属索引。
当我们建立表间链接时，MySQL会自动为外键添加索引，这样就能快速就行表连接（join tables）了。

4、前缀索引

当索引的列是字符串时（包括 CHAR、VARCHAR、TEXT、BLOG），尤其是当字符串较长时，我们通常不会使用整个字符串而是只是用字符串的前面几个字符来建立索引，这被称作 Prefix Indexes 前缀索引，这样可以减少索引的大小使其更容易在内存中操作，毕竟在内存中操作数据比在硬盘中快很多。

案例
为 customers 表的 last_name 建立索引并且只使用其前20个字符：

CREATE INDEX idx_lastname ON customers (last_name(20));

最佳字符数
可最佳字符数如何确定呢？太多了会使得索引太大难以在内存中运行，太少又达不到筛选的效果。比如，只用第一个字符建立索引，那如果查找A开头的名字，索引可能会返回10万个结果，然后就必须对这10万个结果逐条筛选。
可以利用 COUNT、DISTINCT、LEFT 关键词和函数来测试不同数目的前缀字符得到的独特值个数，目标是用尽可能少的前缀字符得到尽可能多的独特值个数：

SELECT 
    COUNT(DISTINCT LEFT(last_name, 1)),
    COUNT(DISTINCT LEFT(last_name, 5)),
    COUNT(DISTINCT LEFT(last_name, 10))
FROM customers
-- 结果是 '25', '966', '996'

可见从前1个到前5个字符，效果提升是很显著的，但从前5个到前10个字符，所用的字符数增加了一倍但识别效果只增加了一点点，再加上5个字符已经能识别出966个独特值，与1010的记录总数相去不远了，所以可以认为用前5个字符来创建前缀索引是最优的。

5、全文索引

小结
全文索引十分强大，如果你要建一个搜索引擎可以使用它，特别是要搜索的是长文本时，如文章、博客、说明和描述，否则，如果搜索比较短的字符串，比如名字或地址，就使用前置字符串。

案例
准备：运行 create-db-blog.sql 得到 sql_blog 数据库，里面只包含一个 posts 表（文章表），每条记录就是一篇文章的编号 post_id、标题 title、内容 body 和发布日期 data_published。

假设我们创建了一个博客网站，里面有一些文章，并存放在上面这个 sql_blog 数据库里，如何让用户可以对博客文章进行搜索呢？
假设，用户想搜索标题或者正文中包含 react 及 redux这两个（两个有关前端的重要的 javascript 库）的文章，如果用 LIKE 操作符进行筛选：

USE sql_blog;
SELECT *
FROM posts
WHERE title LIKE '%react redux%'
    OR body LIKE '%react redux%';

有两个问题：
① 在没有索引的情况下，会对所有文本进行全面扫描，效率低下。如果用上节课讲的前缀索引也不行，因为前缀索引只包含标题或内容开头的若干字符，若搜索的内容不在开头，以依然需要全面扫描。
② 这种搜索方式只会返回完全符合 ‘%react redux%’ 的结果，但我们是希望得到包含这两个单词的任意一个或两个，任意顺序，中间有任意间隔的所有相关结果，即 google 式的模糊搜索。

我们通过建立 Fulltext Index 全文索引 来实现这样的搜索：
①建立全文索引：

CREATE FULLTEXT INDEX idx_title_body ON posts (title, body);

②利用全文索引，结合 MATCH 和 AGAINST 进行 google 式的模糊搜索:

SELECT *
FROM posts
WHERE MATCH(title, body) AGAINST('react redux');

注意MATCH后的括号里必须包含全文索引 idx_title_body 建立时相关的所有列，不然会报错。

③还可以把 MATCH(title, body) AGAINST(‘react redux’) 包含在选择语句里，这样还能看到各结果的 relevance score 相关性得分（一个 0 到 1 的浮点数），可以看出结果是按相关行降序排列的。

SELECT *, MATCH(title, body) AGAINST('react redux')
FROM posts
WHERE MATCH(title, body) AGAINST('react redux');

在这里插入图片描述

全文检索有两个模式：
自然语言模式和布林模式。自然语言模式是默认模式，也是上面用到的模式。布林模式可以更明确地选择包含或排除一些词汇（google也有类似功能），如：

含 react，不含 redux，必须有 form：

SELECT *, MATCH(title, body) AGAINST('react redux')
FROM posts
WHERE MATCH(title, body) AGAINST('react -redux +form' IN BOOLEAN MODE);

布林模式也可以实现精确搜索，就是将需要精确搜索的内容再用双引号包起来：

SELECT *, MATCH(title, body) AGAINST('react redux')
FROM posts
WHERE MATCH(title, body) AGAINST('"handling a form"' IN BOOLEAN MODE);

6、复合索引

案例
查询所在州为 ‘CA’ 而且积分大于 1000 的顾客id：

EXPLAIN SELECT customer_id
FROM customers
WHERE state = 'CA' AND points > 1000;

会发现 MySQL 在 idx_state、idx_points 两个候选索引最终选择了 idx_state，总共扫描了 112 行记录。
idx_state 这种单字段的索引只做了一半的工作：它能帮助快速找到在 ‘CA’ 的顾客，但要继续寻找其中积分大于1000的人时，却不得不进行原表扫描，如果加州有一百万人的话这就会变得很慢。

所以我们要建立 state 和 points 的组合索引：（两者的顺序其实很重要，下节课讲）

USE sql_store;
SHOW INDEXES IN customers; -- 当前只有idx_state、idx_points两个单字段索引
CREATE INDEX idx_state_points ON customers (state, points); -- 建立复合索引

再次运行上面的查询：
在这里插入图片描述
发现在 idx_state、idx_points、idx_state_points 三个候选索引中 MySQL 发现组合索引 idx_state_points 对我们要做的查询而言是最优的因而选择了它，最终扫描的行数由 112 降到了 58，效率提高了。

我们可以用 DROP 关键字删除掉那两个单列的索引

DROP INDEX idx_state ON customers;
DROP INDEX idx_points ON customers;

在这里插入图片描述

7、复合索引的列顺序

组合索引的原理
对于组合索引，一定要从原理上去理解，比如 idx_state_lastname，它是先对 state 建立分类排序的索引，然后再在同一州（如 ‘CA’）内建立 lastname 的分类排序索引，所以这个索引对两类查询有效：

单独针对 state 的查询（快速找到州）
同时针对 state 和 lastname 的查询（快速找到州再在该州内快速找到该姓氏）

基于对以上原理的理解，我们在确定组合索引的列顺序时有两个指导原则：

将最常使用的列放在前面
将基数（Cardinality）最大/独特性最高的列放在前面：
因为基数越大/独特性越高，起到的筛选作用越明显，能够迅速缩小查询范围。比如如果首先以性别来筛选，那只能将选择范围缩小到一半左右，但如果先以所在州来筛选，以总共 20 个州且每个州人数相当为例，那就会迅速将选择范围缩小到 1/20。因此应该将州放前面。
但最终仍然要根据实际的查询需求来决定。

案例
查找CA州，lastname首字母为A的用户。针对 state 和 last_name 两列，比较复合索引的效率。
① last_name 的独特性肯定是比 state 的独特性高的，可以用以下语句验证：

USE sql_store; 

SELECT 
    COUNT(DISTINCT state),
    COUNT(DISTINCT last_name)
FROM customers;
-- 48, 996

②同时建立两种顺序的索引 idx_state_lastname 和idx_lastname_state：

CREATE INDEX idx_state_lastname ON customers (state, last_name);
CREATE INDEX idx_lastname_state ON customers (last_name, state);

③比较查询的效率：
先放state的查询效率更高。只需要查询7行即可。

EXPLAIN SELECT customer_id
FROM customers
USE INDEX (idx_state_lastname)
WHERE state = 'CA' AND last_name LIKE 'A%';
-- 7 rows

EXPLAIN SELECT customer_id
FROM customers
USE INDEX (idx_lastname_state)
WHERE state = 'CA' AND last_name LIKE 'A%';
-- 40 rows

在这里插入图片描述

这是因为，对姓氏进行了模糊查询。因此现查州，再在结果里找姓氏会更快。

④如果是对姓氏进行准确查询：

EXPLAIN SELECT customer_id
FROM customers
WHERE state = 'CA' AND last_name = 'Combes';

在这里插入图片描述
这种目标是特定州和特定姓氏的的查询能够充分利用各列独特性，用 idx_lastname_state 先筛选姓氏能更快缩小范围提高效率。

⑤如果是对州和姓氏都进行模糊查询：

EXPLAIN SELECT customer_id
FROM customers
WHERE state LIKE 'A%' AND last_name LIKE 'A%';

在这里插入图片描述
还是先筛选lastname效率更高。符合将基数（Cardinality）最大/独特性最高的列放在前面原则。

总之，不仅要考虑各列的独特性高低，也要考虑常用的查询是否能充分利用各列的独特性，两者结合来决定组合索引里的排序，不确定就测试对比验证，所以，第二条原则也许应该改为将常用查询实际利用到的独特性程度最高的列放在前面。

还要注意一点是，如前所述， idx_state_lastname 对单独针对 last_name 的查询无效，相当于遍历全表。如果有这样的查询需要就还要另外为该列建一个可用的索引 idx_state。

8、索引无效

小结

两种索引无效的情况：

或（OR）查询
表达式中列不在单独的一侧

案例1
查找在加州或积分大于1000的顾客id。

USE sql_store;
EXPLAIN SELECT customer_id FROM customers
WHERE state = 'CA' OR points > 1000;

在这里插入图片描述
发现虽然显示 type 是 index，用的索引是 idx_state_points，但扫描的行数却是 1010 rows。
因为这里是或（OR）查询，在找到加州的顾客后，仍然需要在每个州里去找积分大于 1000 的顾客，所以要扫描所有的 1010 条索引记录，即进行了全索引扫描（full index scan）。当然全索引扫描比全表扫描要快一点，但全索引扫描依然说明索引未被有效利用，如果是百万条记录还是会很慢。
我们需要以尽可能充分利用索引地方式来编写查询。就这个例子而言，可另建一个 idx_points 并将这个 OR 查询改写为两部分，分别用各自最合适的索引，再用 UNION 融合结果。

CREATE INDEX idx_points ON customers (points);

EXPLAIN
        SELECT customer_id FROM customers
        WHERE state = 'CA'
    UNION
        SELECT customer_id FROM customers
        WHERE points > 1000;

结果显示，两部分查询中，MySQL 分别自动选用了对该查询最有效的索引 idx_state_points 和 idx_points，扫描的行数分别为 112 和 529，总共 641 行，相比于 1010 行有很大的提升。

案例2
查询目前积分增加 10 分后超过 2000 分的顾客id:

EXPLAIN SELECT customer_id FROM customers
WHERE points + 10 > 2010;
-- key: idx_points
-- rows: 1010

又变成了 1010 行全索引扫描，因为 column expression 列表达式（列运算）不能最有效地使用索引。要重写运算表达式，独立/分离此列。

EXPLAIN SELECT customer_id FROM customers
WHERE points > 2000;
-- key: idx_points
-- rows: 4

直接从1010行降为4行，效率提升显著。所以想要 MySQL 有效利用索引，就总是在表达式中将列独立出来。

9、使用索引排序

案例
1、准备：
①返回上一次查询的成本，可以用变量：

SHOW STATUS LIKE 'last_query_cost'; -- Mysql服务器使用的变量之一

②只保留customers表中的idx_lastname, idx_state_points 两个索引：
在这里插入图片描述
2、查询后按state排序：

EXPLAIN SELECT customer_id 
FROM customers
ORDER BY state;
-- type: index, rows: 1010, Extra: Using index
SHOW STATUS LIKE 'last_query_cost';  
-- cost: 102.749

使用索引排序，消耗100左右。

3、查询后按fist_name排序：

EXPLAIN SELECT customer_id 
FROM customers
ORDER BY first_name;
-- type: ALL, rows: 1010, Extra: Using filesort 
SHOW STATUS LIKE 'last_query_cost';  
-- cost: 1112.749

无法使用索引排序，只能使用外部排序（filesort），非常消耗资源。消耗1000左右。

小结
特定的索引只对特定的查询（WHERE 筛选条件）和排序（ORDER BY 排序条件）有效。
要从原理上理解索引：以 idx_state_points 为例，它等于是先对 state 分类排序，再在同一个 state 内对 points 进行分类排序，再加上 customer_id 映射到相应的原表记录。
在这里插入图片描述
因此，索引 idx_state_points 对于以下排序有效：

ORDER BY state
ORDER BY state, points
ORDER BY state desc, points desc
ORDER BY points WHERE state = 'CA'

在这里插入图片描述
对以下索引无效或只是部分有效（这些会部分或全部用到 filesort ）：

ORDER BY points
ORDER BY points, state
ORDER BY state, points desc
ORDER BY state, first_name, points

在这里插入图片描述

10、覆盖索引

小结

最高效的查询：覆盖索引（covering index），整个查询在只使用索引不碰原表的情况下完成，这是最快的查询。
覆盖索引的条件：
①先看 WHERE 子句，看看最常用的筛选字段是什么，把它们包含在索引中，这样就能迅速缩小查找范围。
②其次看 ORDER BY 子句，将这些列包含在索引中。
③最后看 SELECT 子句，如果你连这些列也包含了，就得到了覆盖索引，MySQL 就能只用索引就完成你的查询，实现最快的查询速度。

案例1

explain select customer_id from customers
where state = 'CA'
order by state, points;
-- 该查询使用了复合索引idx_state_points

复合索引 idx_state_points 中包含三种信息：state、points 以及 customer_id（注：当我们创建索引时，除了会包含相关列还会自动包含主键列来和原表中的记录建立对应关系）。上述查询使用了覆盖索引。
在这里插入图片描述

案例2
比较以下查询消耗的资源（查询都使用了复合索引idx_state_points）：

USE sql_store;

-- 1. 只选择 customer_id:
EXPLAIN SELECT customer_id FROM customers
ORDER BY state;
SHOW STATUS LIKE 'last_query_cost';

-- 2. 选择 customer_id 和 state:
EXPLAIN SELECT customer_id, state FROM customers
ORDER BY state;
SHOW STATUS LIKE 'last_query_cost';

-- 3. 选择所有字段:
EXPLAIN SELECT * FROM customers
ORDER BY state;
SHOW STATUS LIKE 'last_query_cost';