SQL（Structured Query Language）教程（上）

1、什么是数据库

 数据库就是一种可以快速访问的一种格式化的数据集合，为了管理数据库，使用数据库管理系统DBMS（Database Management System）来管理，连接到DBMS，然后下达指令，DBMS执行命令并把结果返回给我们。

数据库分为关系型数据库和非关系型数据库NoSQL。

关系型数据库：数据保存在彼此相互关联的表格中，使用SQL语言操作

关系型数据库的DBMS常见的有：MySQL、SQLsever、Oracle

非关系型数据库中没有关系和相互关联的表格，不支持SQL语言

2、安装MySQL

MySQL :: Begin Your Download

安装过程一直保留默认设置就可以了，设置账号密码的时候要记得自己设置的内容。

打开workbench新建连接

Table：表格，用于存储数据

Views: 视图，简表

Stored、Functions： 保存在库中用以查询的小程序

2、查询

/* 注意：sql对大小写不敏感；语句以;来结束*/
use sql_store;  -- 使用sql_store数据库,等同于USE sql_store;
SELECT *  -- 查询所有数据
FROM customers -- 选中 customer表格
WHERE customer_id = 1 -- 选中customer_id = 1 的数据
order by first_name; -- 将这些数据按照first_name排序

-- 查询某些列，可以四则运算 + - * / %（取余)
select 
	last_name,
	first_name,
    points,
	100*(points+10) as discount_factor, -- 给进行过四则运算的这一列取名 discount_factor
    points%10 as "discount rate" -- 想要在列名中使用空格就用双引号or单引号
from customers;

 

select state from customers;
select distinct state from customers; 

 

 即使列名为关键词也能查询

/* 注意：sql对大小写不敏感；语句以;来结束*/

-- 1、USE
use sql_store;  -- 使用sql_store数据库,等同于USE sql_store;

/*----------------------------- 在单一表中检索数据 -----------------------------*/
-- 2、SELECT、FROM、WHERE、ORDER BY
-- AND、OR、NOT
-- AS
SELECT *  -- 查询所有数据
FROM customers -- 选中 customer表格
WHERE not (  -- not表示取反，要使用()将整个需要not的语句括起来
(customer_id = 1 or points<3000)  -- 选中customer_id = 1 的数据
and state <> 'VA'  -- > >= < <= != <> -- and优先级大于or，可以通过()改变优先级
and birth_date <'1990-01-01' -- 日期要用''
)  
order by first_name; -- 将这些数据按照first_name排序

select name, unit_price, unit_price*1.1 as 'new price' from products;
select * from order_items where order_id=6 and unit_price*quantity >30;


-- 3、查询某些列，可以四则运算 + - * / %（取余)
select 
	last_name,
	first_name,
    points,
	100*(points+10) as discount_factor, -- 给进行过四则运算的这一列取名 discount_factor
    points%10 as "discount rate" -- 想要在列名中使用空格就用双引号or单引号
from customers;

-- 4、使用distinct来实现不重复查找
select state from customers;
select distinct state from customers; 

-- 5、IN
select * from customers where state in ('VA','FL','GA');
select * from customers where state not in ('VA','FL','GA');
-- 等价于
select * from customers where state='VA' or state='FL' or state='GA';
-- 不能写成 select * from customers where state='VA' or 'FL' or 'GA'
-- 因为or连接的是两个语句
-- exercise
select * from products where quantity_in_stock in (49,38,72);

-- 6、between x and y
-- 不能写成 where 1000<points<3000;
select * from customers where points between 1000 and 3000;
select * from customers where birth_date between '1990-01-01' and '2000-01-01';

-- 7、like 匹配字符
select* from customers where last_name like '__a%y';
-- 第三个字母是a，最后一个字母是y 
-- 匹配的a y 不区分大小写
-- _：匹配一个字符 __就是匹配两个字符
-- %：匹配任意多个字符
select * from customers 
where (address like '%trail%' or address like '%avenue%') and phone not like '%6';
-- 地址中包含这两个单词并且电话号码不以6结尾

-- 8、regexp 匹配字符
select * from customers where last_name like 'field%';
select * from customers where last_name regexp 'field$';
-- 两条命令等价
-- 正则表达式 regexp
--  '^xxx'：以xxx开头 'xxx$'：以xxx结尾 'a|b|c'：包含a或b或c
-- '[gim]e'：匹配包含ge或ie或me的字符
-- '[a-h]e'：e前面的字符为a-h中的任意一个
select * from customers where last_name regexp '[gim]e';
select * from customers where last_name regexp '[a-h]e';
-- exercise
-- regexp 中的字符匹配时不区分大小写
select * from customers where first_name regexp 'elka|ambur';
select * from customers where last_name regexp 'ey$|on$';
select * from customers where last_name regexp '^my|se'; 
select * from customers where last_name regexp 'b[ru]'; 
-- 等价于
select * from customers where last_name regexp 'br|bu'; 

-- 9、is null 查找缺失值
select * from customers where phone is null;
select * from customers where phone is not null;
-- exercise 查询发货日期缺失的列
select * from orders where shipped_date is null;

-- 10、order by 排序
-- 主键是每一行的唯一标识符，不能有重复值
-- 数据表每次展示时默认以主键升序排列
-- 以名字降序排列；无desc是升序排列
select * from customers order by first_name desc;
-- 先以state降序排列，再以名字升序排列
select * from customers order by state desc,first_name;
-- 以第1、2列排序
-- 以被选中的1、2列birth_date,first_name进行排序
select birth_date,first_name, last_name, 10 as points from customers order by 1,2;
select *, quantity*unit_price as total_price from order_items where order_id=2 order by total_price desc;

-- 11、limit 限制查询的返回结果
-- 查询前三条语句
select * from customers limit 3;
-- 跳过前6条，查询第6条之后的3条语句 7、8、9
select * from customers limit 6,3;
-- exercise 按照积分降序排列后查询前三
select * from customers order by points desc limit 3;

/*----------------------------- 在多个表中检索数据 -----------------------------*/
-- 12、inner joins ... on ... 内部连接
-- inner join 中的inner是默认的，可以省略直接写join
-- on 合并条件
select * from orders inner join customers 
on orders.customer_id = customers.customer_id; -- 在连接两个表时以这个列来连接
/* 在显示customer_id的时候必须要指明表名称
否则两个表中都出现此列，mysql不知道返回哪个表中的customer_id会报错 */
select order_id,orders.customer_id,first_name from orders inner join customers 
on orders.customer_id = customers.customer_id; 
-- 使用别名简化语句。注意：当使用别名之后，再需要引用表时只能使用别名
select order_id, o.customer_id, first_name 
from orders o inner join customers c
on o.customer_id = c.customer_id; 
-- exercise
select order_id, p.product_id, quantity from order_items oi join products p 
on oi.product_id = p.product_id;

-- 13、跨库合并
/* order_items在sql_store中
products不在sql_store中 */
use sql_store;
select * from order_items oi join sql_inventory.products p
on oi.product_id = p.product_id;

-- 14、表的自合并
use sql_hr;
-- 在选择列时要指明所属表
select e.employee_id, e.first_name, m.first_name as manager
from employees e join employees m 
on e.reports_to = m.employee_id;
-- exercise
select * 
from employees e join employees m 
on e.reports_to = m.employee_id;

-- 15、合并多个表
use sql_store;
select o.order_id, o.order_date, 
c.first_name, c.last_name, os.name as status from orders o
join customers c on o.customer_id = c.customer_id -- 合并条件 O∩C
join order_statuses os on o.status = os.order_status_id; -- O∩OS
-- exercise
use sql_invoicing;
SELECT p.date, p.invoice_id,p.amount,c.name,pm.name
FROM payments p join clients c on p.client_id = c.client_id
join payment_methods pm on p.payment_method = pm.payment_method_id;

-- 16、复合合并：多个合并条件
-- 表中有两个主键，两个主键合并在一起才能唯一表示一条记录，称为复合主键
-- 将复合主键合并成一个主键
-- 将两个列合并成唯一标识列
use sql_store;
select * from order_items oi join order_item_notes oin
on oi.order_id = oin.order_id and oi.producorder_itemst_id = oin.product_id;

-- 17、隐式连接语法
-- 就是用FROM..WHERE 取代 FROM...JOIN ON
USE sql_store;
SELECT * FROM orders o JOIN customers c
    ON o.customer_id = c.customer_id;
-- 隐式合并语法
SELECT * FROM orders o, customers c  
WHERE o.customer_id = c.customer_id;
/* 谨慎使用隐式合并语法，因为当忘记写where时会出现交叉合并
customers中的每一列都和orders中的每一列匹配一次，出现10*10条数据 */
SELECT * FROM orders o, customers c;

-- 18、外连接 outer join
/* (INNER) JOIN 结果只包含两表的交集，不会出现无法一一对应的数据，返回A∩B */
-- 只能返回同时存在客户信息和订单信息的数据
USE sql_store;
SELECT c.customer_id, c.first_name, o.order_id
FROM customers c JOIN orders o
    ON o.customer_id = c.customer_id
ORDER BY customer_id;
-- outer join: LEFT/RIGHT (OUTER) JOIN 结果里除了交集，还包含只出现在左/右表中的记录
-- 所有左表（customers）中的数据不管条件是否成立都会返回，返回结果为A∪(A∩B)
-- 还返回了没有订单信息但是有客户信息的数据
-- 左连接
SELECT c.customer_id, c.first_name, o.order_id
FROM customers c left JOIN orders o
    ON o.customer_id = c.customer_id ORDER BY customer_id;
-- 右连接
SELECT c.customer_id, c.first_name, o.order_id
FROM customers c right outer JOIN orders o
    ON o.customer_id = c.customer_id ORDER BY customer_id;
-- exercise 展示各产品在订单项目中出现的记录和销量，也要包括没有订单的产品
SELECT 
    p.product_id,
    p.name, -- 或者直接name
    oi.quantity -- 或者直接quantity
FROM products p
LEFT JOIN order_items oi
    ON p.product_id = oi.product_id;

-- 19、多表间外连接 outer join between multiple tables
USE sql_store;
/* 查询出所有的客户信息不管有没有订单，
并且查询所有的订单信息，不管有没有物流信息
查询顾客、订单和发货商记录，要包括所有顾客（包括无订单的顾客），也要包括所有订单（包括未发出的）*/
/* 虽然可以调换顺序并用 RIGHT JOIN，但是最好调整顺序并统一只用 [INNER] JOIN 和 LEFT [OUTER] JOIN（总是左表全包含）
这样，当要合并的表比较多时才方便书写和理解而不易混乱*/
SELECT c.customer_id, c.first_name, o.order_id, sh.name AS shipper
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
LEFT JOIN shippers sh ON o.shipper_id = sh.shipper_id
ORDER BY customer_id;
-- 练习：查询 订单 + 顾客 + 发货商 + 订单状态，包括所有的订单（包括未发货的），其实就只是前两个优先级变了一下，是要看全部订单而非全部顾客了
USE sql_store;
SELECT 
    o.order_id,
    o.order_date,
    c.first_name AS customer,
    sh.name AS shipper,
    os.name AS status
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id -- 决定哪两个集合相交
LEFT JOIN shippers sh ON o.shipper_id = sh.shipper_id
JOIN order_statuses os ON o.status = os.order_status_id;
/* 订单必有顾客和状态，所以这第1个和第3个 JOIN 加不加 LEFT 效果一样 
但订单不一定发货了，即不一定有发货商，所以第2个 JOIN 必须是 LEFT JOIN，否者会筛掉没发货的订单*/

-- 20、自我外部连接 Self Outer Joins 
/* 就用前面那个员工表的例子来说，就是用LEFT JOIN得到的 
员工-上级 合并表也包括老板本人
老板没有上级，即 reports_to 字段为空
如果用 JOIN 则无法保留老板信息，用 LEFT JOIN 才能保留*/
USE sql_hr;
SELECT 
    e.employee_id,
    e.first_name,
    m.first_name AS manager
FROM employees e
LEFT JOIN employees m  -- 包含所有雇员（包括没有report_to的老板本人）
    ON e.reports_to = m.employee_id;

-- 21、join using：简化join on
/* 当合并条件在两个表中有相同的列名时
可用 USING (...) 取代 ON …… 
内/外链接均可如此简化*/
/* 注意：USING 后接的是括号；列名不同时不能使用using */
SELECT
    o.order_id,
    c.first_name,
    sh.name AS shipper
FROM orders o
JOIN customers c USING (customer_id)
LEFT JOIN shippers sh USING (shipper_id)
ORDER BY order_id;

-- 22、复合主键表间复合连接条件的合并也可用 JOIN...USING(...,...)代替JOIN...ON...AND... 
SELECT *
FROM order_items oi
JOIN order_item_notes oin -- ON oi.order_id = oin.order_Id AND oi.product_id = oin.product_id
USING (order_id, product_id); -- USING对复合主键的简化效果更加明显
-- exercise 知道什么日期哪个顾客用什么方式付了多少钱
USE sql_invoicing;
SELECT 
    p.date,
    c.name AS client,
    pm.name AS payment_method,
    p.amount
FROM payments p
JOIN clients c USING (client_id)
JOIN payment_methods pm ON p.payment_method = pm.payment_method_id;
    
-- 23、自然连接 Natural Joins
-- NATURAL JOIN 就是让MySQL自动检索同名列作为合并条件
/* 注意:最好别用，因为不确定合并条件是否找对了
有时会造成无法预料的问题，编程时保持对结果的控制是非常重要的*/
USE sql_store;
SELECT o.order_id, c.first_name FROM orders o 
NATURAL JOIN customers c;

-- 24、交叉连接 Cross Joins 
-- A表中N条数据；B表中M条数据，交叉连接得到M*N条数据，因此不需要合并条件
/* 得到顾客和产品的所有组合，并无实际意义 */
USE sql_store;
SELECT 
    c.first_name AS customer,
    p.name AS product
FROM customers c
CROSS JOIN products p
ORDER BY c.first_name;
/* 上面是显性语法，还有隐式语法
即忽略WHERE子句（即合并条件）的情况，推荐显式语法，更清晰 */
USE sql_store;
SELECT c.first_name, p.name
FROM customers c, products p
ORDER BY c.first_name;
-- 练习：交叉合并shippers和products，分别用显式和隐式语法
USE sql_store;
SELECT 
    sh.name AS shippers,
    p.name AS product
FROM shippers sh
CROSS JOIN products p
ORDER BY sh.name;
-- 隐式
SELECT 
    sh.name AS shippers,
    p.name AS product
FROM shippers sh, products p
ORDER BY sh.name;

-- 25、UNION 纵向合并多个查询结果，SELECT ... UNION SELECT ...
/* 注意:
1、合并的查询结果必须列数相等，否则会报错
2、合并表里的列名由排在 UNION 前面查询的列名的决定 
3、这些查询结果可能来自相同或不同的表 */
-- 给订单表增加一个新字段 status，用以区分今年的订单和今年以前的订单
USE sql_store;
SELECT order_id,
	   order_date,
	   'Active' AS status -- 新增一列status，列内容为'Active' 有效订单
    FROM orders
    WHERE order_date >= '2019-01-01'
UNION
SELECT order_id,
	order_date,
	'Archived' AS status  -- Archived 归档
FROM orders
WHERE order_date < '2019-01-01';
-- 合并不同表，在同一列里显示所有顾客名以及所有商品名
USE sql_store; -- 新列名由排UNION前面的决定
SELECT first_name AS name_of_all FROM customers
UNION
SELECT name FROM products;
-- exercise：给顾客按积分大小分类，添加新字段type，并按顾客id排序
SELECT customer_id, first_name,points, 'Bronze' AS type
FROM customers WHERE points < 2000
UNION
SELECT customer_id, first_name,points, 'Silver' AS type
FROM customers WHERE points BETWEEN 2000 and 3000
UNION
SELECT customer_id, first_name,points, 'Gold' AS type
FROM customers WHERE points > 3000
ORDER BY customer_id;

/* 关于SQL关键词优先级的补充:
 1.FORM：对FROM的左边的表和右边的表计算笛卡尔积。产生虚表VT1
 2.ON：对虚表VT1进行ON筛选，只有那些符合<join-condition>的行才会被记录在虚表VT2中。
 3.JOIN： 如果指定了OUTER JOIN（比如left join、 right join），那么保留表中未匹配的行就会作为外部行添加到虚拟表VT2中，产生虚拟表VT3, rug from子句中包含两个以上的表的话，那么就会对上一个join连接产生的结果VT3和下一个表重复执行步骤1~3这三个步骤，一直到处理完所有的表为止。
 4.WHERE： 对虚拟表VT3进行WHERE条件过滤。  只有符合<where-condition>的记录才会被插入到虚  拟表VT4中。
 5.GROUP BY： 根据group by子句中的列，对VT4中的记录进行分组操作，产生VT5。
 6.HAVING： 对虚拟表VT5应用having过滤，只有符合<having-condition>的记录才会被 插入到虚拟表VT6中。
 7.SELECT： 执行select操作，选择指定的列，插入到虚拟表VT7中。
 8.DISTINCT： 对VT7中的记录进行去重。产生虚拟表VT8。
 9.ORDER BY: 将虚拟表VT8中的记录按照<order_by_list>进行排序操作，产生虚拟表VT9。
 10.LIMIT：取出指定行的记录，产生虚拟表VT10, 并将结果返回。
*/

3、删、改、查

• PK：primary key，主键
• AI：auto increment，自动递增属性，自动填充递增数值，与PK搭配使用
• NN：not null，不为空
• UQ：unique index，无重复值
• B：binary column，二元数值列
• UN：unsigned data type，无字符数据类型
• Default/Expression：自动填充默认值

/* 注意：sql对大小写不敏感；语句以;来结束*/

-- 1、USE
use sql_store;  -- 使用sql_store数据库,等同于USE sql_store;

/*----------------------------- 在单一表中检索数据 -----------------------------*/
-- 2、SELECT、FROM、WHERE、ORDER BY
-- AND、OR、NOT
-- AS
SELECT *  -- 查询所有数据
FROM customers -- 选中 customer表格
WHERE not (  -- not表示取反，要使用()将整个需要not的语句括起来
(customer_id = 1 or points<3000)  -- 选中customer_id = 1 的数据
and state <> 'VA'  -- > >= < <= != <> -- and优先级大于or，可以通过()改变优先级
and birth_date <'1990-01-01' -- 日期要用''
)  
order by first_name; -- 将这些数据按照first_name排序

select name, unit_price, unit_price*1.1 as 'new price' from products;
select * from order_items where order_id=6 and unit_price*quantity >30;


-- 3、查询某些列，可以四则运算 + - * / %（取余)
select 
	last_name,
	first_name,
    points,
	100*(points+10) as discount_factor, -- 给进行过四则运算的这一列取名 discount_factor
    points%10 as "discount rate" -- 想要在列名中使用空格就用双引号or单引号
from customers;

-- 4、使用distinct来实现不重复查找
select state from customers;
select distinct state from customers; 

-- 5、IN
select * from customers where state in ('VA','FL','GA');
select * from customers where state not in ('VA','FL','GA');
-- 等价于
select * from customers where state='VA' or state='FL' or state='GA';
-- 不能写成 select * from customers where state='VA' or 'FL' or 'GA'
-- 因为or连接的是两个语句
-- exercise
select * from products where quantity_in_stock in (49,38,72);

-- 6、between x and y
-- 不能写成 where 1000<points<3000;
select * from customers where points between 1000 and 3000;
select * from customers where birth_date between '1990-01-01' and '2000-01-01';

-- 7、like 匹配字符
select* from customers where last_name like '__a%y';
-- 第三个字母是a，最后一个字母是y 
-- 匹配的a y 不区分大小写
-- _：匹配一个字符 __就是匹配两个字符
-- %：匹配任意多个字符
select * from customers 
where (address like '%trail%' or address like '%avenue%') and phone not like '%6';
-- 地址中包含这两个单词并且电话号码不以6结尾

-- 8、regexp 匹配字符
select * from customers where last_name like 'field%';
select * from customers where last_name regexp 'field$';
-- 两条命令等价
-- 正则表达式 regexp
--  '^xxx'：以xxx开头 'xxx$'：以xxx结尾 'a|b|c'：包含a或b或c
-- '[gim]e'：匹配包含ge或ie或me的字符
-- '[a-h]e'：e前面的字符为a-h中的任意一个
select * from customers where last_name regexp '[gim]e';
select * from customers where last_name regexp '[a-h]e';
-- exercise
-- regexp 中的字符匹配时不区分大小写
select * from customers where first_name regexp 'elka|ambur';
select * from customers where last_name regexp 'ey$|on$';
select * from customers where last_name regexp '^my|se'; 
select * from customers where last_name regexp 'b[ru]'; 
-- 等价于
select * from customers where last_name regexp 'br|bu'; 

-- 9、is null 查找缺失值
select * from customers where phone is null;
select * from customers where phone is not null;
-- exercise 查询发货日期缺失的列
select * from orders where shipped_date is null;

-- 10、order by 排序
-- 主键是每一行的唯一标识符，不能有重复值
-- 数据表每次展示时默认以主键升序排列
-- 以名字降序排列；无desc是升序排列
select * from customers order by first_name desc;
-- 先以state降序排列，再以名字升序排列
select * from customers order by state desc,first_name;
-- 以第1、2列排序
-- 以被选中的1、2列birth_date,first_name进行排序
select birth_date,first_name, last_name, 10 as points from customers order by 1,2;
select *, quantity*unit_price as total_price from order_items where order_id=2 order by total_price desc;

-- 11、limit 限制查询的返回结果
-- 查询前三条语句
select * from customers limit 3;
-- 跳过前6条，查询第6条之后的3条语句 7、8、9
select * from customers limit 6,3;
-- exercise 按照积分降序排列后查询前三
select * from customers order by points desc limit 3;

/*----------------------------- 在多个表中检索数据 -----------------------------*/
-- 12、inner joins ... on ... 内部连接
-- inner join 中的inner是默认的，可以省略直接写join
-- on 合并条件
select * from orders inner join customers 
on orders.customer_id = customers.customer_id; -- 在连接两个表时以这个列来连接
/* 在显示customer_id的时候必须要指明表名称
否则两个表中都出现此列，mysql不知道返回哪个表中的customer_id会报错 */
select order_id,orders.customer_id,first_name from orders inner join customers 
on orders.customer_id = customers.customer_id; 
-- 使用别名简化语句。注意：当使用别名之后，再需要引用表时只能使用别名
select order_id, o.customer_id, first_name 
from orders o inner join customers c
on o.customer_id = c.customer_id; 
-- exercise
select order_id, p.product_id, quantity from order_items oi join products p 
on oi.product_id = p.product_id;

-- 13、跨库合并
/* order_items在sql_store中
products不在sql_store中 */
use sql_store;
select * from order_items oi join sql_inventory.products p
on oi.product_id = p.product_id;

-- 14、表的自合并
use sql_hr;
-- 在选择列时要指明所属表
select e.employee_id, e.first_name, m.first_name as manager
from employees e join employees m 
on e.reports_to = m.employee_id;
-- exercise
select * 
from employees e join employees m 
on e.reports_to = m.employee_id;

-- 15、合并多个表
use sql_store;
select o.order_id, o.order_date, 
c.first_name, c.last_name, os.name as status from orders o
join customers c on o.customer_id = c.customer_id -- 合并条件 O∩C
join order_statuses os on o.status = os.order_status_id; -- O∩OS
-- exercise
use sql_invoicing;
SELECT p.date, p.invoice_id,p.amount,c.name,pm.name
FROM payments p join clients c on p.client_id = c.client_id
join payment_methods pm on p.payment_method = pm.payment_method_id;

-- 16、复合合并：多个合并条件
-- 表中有两个主键，两个主键合并在一起才能唯一表示一条记录，称为复合主键
-- 将复合主键合并成一个主键
-- 将两个列合并成唯一标识列
use sql_store;
select * from order_items oi join order_item_notes oin
on oi.order_id = oin.order_id and oi.producorder_itemst_id = oin.product_id;

-- 17、隐式连接语法
-- 就是用FROM..WHERE 取代 FROM...JOIN ON
USE sql_store;
SELECT * FROM orders o JOIN customers c
    ON o.customer_id = c.customer_id;
-- 隐式合并语法
SELECT * FROM orders o, customers c  
WHERE o.customer_id = c.customer_id;
/* 谨慎使用隐式合并语法，因为当忘记写where时会出现交叉合并
customers中的每一列都和orders中的每一列匹配一次，出现10*10条数据 */
SELECT * FROM orders o, customers c;

-- 18、外连接 outer join
/* (INNER) JOIN 结果只包含两表的交集，不会出现无法一一对应的数据，返回A∩B */
-- 只能返回同时存在客户信息和订单信息的数据
USE sql_store;
SELECT c.customer_id, c.first_name, o.order_id
FROM customers c JOIN orders o
    ON o.customer_id = c.customer_id
ORDER BY customer_id;
-- outer join: LEFT/RIGHT (OUTER) JOIN 结果里除了交集，还包含只出现在左/右表中的记录
-- 所有左表（customers）中的数据不管条件是否成立都会返回，返回结果为A∪(A∩B)
-- 还返回了没有订单信息但是有客户信息的数据
-- 左连接
SELECT c.customer_id, c.first_name, o.order_id
FROM customers c left JOIN orders o
    ON o.customer_id = c.customer_id ORDER BY customer_id;
-- 右连接
SELECT c.customer_id, c.first_name, o.order_id
FROM customers c right outer JOIN orders o
    ON o.customer_id = c.customer_id ORDER BY customer_id;
-- exercise 展示各产品在订单项目中出现的记录和销量，也要包括没有订单的产品
SELECT 
    p.product_id,
    p.name, -- 或者直接name
    oi.quantity -- 或者直接quantity
FROM products p
LEFT JOIN order_items oi
    ON p.product_id = oi.product_id;

-- 19、多表间外连接 outer join between multiple tables
USE sql_store;
/* 查询出所有的客户信息不管有没有订单，
并且查询所有的订单信息，不管有没有物流信息
查询顾客、订单和发货商记录，要包括所有顾客（包括无订单的顾客），也要包括所有订单（包括未发出的）*/
/* 虽然可以调换顺序并用 RIGHT JOIN，但是最好调整顺序并统一只用 [INNER] JOIN 和 LEFT [OUTER] JOIN（总是左表全包含）
这样，当要合并的表比较多时才方便书写和理解而不易混乱*/
SELECT c.customer_id, c.first_name, o.order_id, sh.name AS shipper
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
LEFT JOIN shippers sh ON o.shipper_id = sh.shipper_id
ORDER BY customer_id;
-- 练习：查询 订单 + 顾客 + 发货商 + 订单状态，包括所有的订单（包括未发货的），其实就只是前两个优先级变了一下，是要看全部订单而非全部顾客了
USE sql_store;
SELECT 
    o.order_id,
    o.order_date,
    c.first_name AS customer,
    sh.name AS shipper,
    os.name AS status
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id -- 决定哪两个集合相交
LEFT JOIN shippers sh ON o.shipper_id = sh.shipper_id
JOIN order_statuses os ON o.status = os.order_status_id;
/* 订单必有顾客和状态，所以这第1个和第3个 JOIN 加不加 LEFT 效果一样 
但订单不一定发货了，即不一定有发货商，所以第2个 JOIN 必须是 LEFT JOIN，否者会筛掉没发货的订单*/

-- 20、自我外部连接 Self Outer Joins 
/* 就用前面那个员工表的例子来说，就是用LEFT JOIN得到的 
员工-上级 合并表也包括老板本人
老板没有上级，即 reports_to 字段为空
如果用 JOIN 则无法保留老板信息，用 LEFT JOIN 才能保留*/
USE sql_hr;
SELECT 
    e.employee_id,
    e.first_name,
    m.first_name AS manager
FROM employees e
LEFT JOIN employees m  -- 包含所有雇员（包括没有report_to的老板本人）
    ON e.reports_to = m.employee_id;

-- 21、join using：简化join on
/* 当合并条件在两个表中有相同的列名时
可用 USING (...) 取代 ON …… 
内/外链接均可如此简化*/
/* 注意：USING 后接的是括号；列名不同时不能使用using */
SELECT
    o.order_id,
    c.first_name,
    sh.name AS shipper
FROM orders o
JOIN customers c USING (customer_id)
LEFT JOIN shippers sh USING (shipper_id)
ORDER BY order_id;

-- 22、复合主键表间复合连接条件的合并也可用 JOIN...USING(...,...)代替JOIN...ON...AND... 
SELECT *
FROM order_items oi
JOIN order_item_notes oin -- ON oi.order_id = oin.order_Id AND oi.product_id = oin.product_id
USING (order_id, product_id); -- USING对复合主键的简化效果更加明显
-- exercise 知道什么日期哪个顾客用什么方式付了多少钱
USE sql_invoicing;
SELECT 
    p.date,
    c.name AS client,
    pm.name AS payment_method,
    p.amount
FROM payments p
JOIN clients c USING (client_id)
JOIN payment_methods pm ON p.payment_method = pm.payment_method_id;
    
-- 23、自然连接 Natural Joins
-- NATURAL JOIN 就是让MySQL自动检索同名列作为合并条件
/* 注意:最好别用，因为不确定合并条件是否找对了
有时会造成无法预料的问题，编程时保持对结果的控制是非常重要的*/
USE sql_store;
SELECT o.order_id, c.first_name FROM orders o 
NATURAL JOIN customers c;

-- 24、交叉连接 Cross Joins 
-- A表中N条数据；B表中M条数据，交叉连接得到M*N条数据，因此不需要合并条件
/* 得到顾客和产品的所有组合，并无实际意义 */
USE sql_store;
SELECT 
    c.first_name AS customer,
    p.name AS product
FROM customers c
CROSS JOIN products p
ORDER BY c.first_name;
/* 上面是显性语法，还有隐式语法
即忽略WHERE子句（即合并条件）的情况，推荐显式语法，更清晰 */
USE sql_store;
SELECT c.first_name, p.name
FROM customers c, products p
ORDER BY c.first_name;
-- 练习：交叉合并shippers和products，分别用显式和隐式语法
USE sql_store;
SELECT 
    sh.name AS shippers,
    p.name AS product
FROM shippers sh
CROSS JOIN products p
ORDER BY sh.name;
-- 隐式
SELECT 
    sh.name AS shippers,
    p.name AS product
FROM shippers sh, products p
ORDER BY sh.name;

-- 25、UNION 纵向合并多个查询结果，SELECT ... UNION SELECT ...
/* 注意:
1、合并的查询结果必须列数相等，否则会报错
2、合并表里的列名由排在 UNION 前面查询的列名的决定 
3、这些查询结果可能来自相同或不同的表 */
-- 给订单表增加一个新字段 status，用以区分今年的订单和今年以前的订单
USE sql_store;
SELECT order_id,
	   order_date,
	   'Active' AS status -- 新增一列status，列内容为'Active' 有效订单
    FROM orders
    WHERE order_date >= '2019-01-01'
UNION
SELECT order_id,
	order_date,
	'Archived' AS status  -- Archived 归档
FROM orders
WHERE order_date < '2019-01-01';
-- 合并不同表，在同一列里显示所有顾客名以及所有商品名
USE sql_store; -- 新列名由排UNION前面的决定
SELECT first_name AS name_of_all FROM customers
UNION
SELECT name FROM products;
-- exercise：给顾客按积分大小分类，添加新字段type，并按顾客id排序
SELECT customer_id, first_name,points, 'Bronze' AS type
FROM customers WHERE points < 2000
UNION
SELECT customer_id, first_name,points, 'Silver' AS type
FROM customers WHERE points BETWEEN 2000 and 3000
UNION
SELECT customer_id, first_name,points, 'Gold' AS type
FROM customers WHERE points > 3000
ORDER BY customer_id;

/* —————————————————————————————————————————————— 增、删、改 —————————————————————————————————————————————— */
-- 一、增
-- insert into table (col1,col3) values (val1,val3)
USE sql_store;
-- 1、插入单行
INSERT INTO customers -- 不指明列
VALUES (
    DEFAULT, -- 主键值，最好不要自己随意添加，使用默认值
    'Michael',
    'Jackson',
    '1958-08-29',  -- DEFAULT/NULL/'1958-08-29'
    DEFAULT,
    '5225 Figueroa Mountain Rd', 
    'Los Olivos',
    'CA',
    DEFAULT -- 默认值就是NULL，这里填充NULL也一样
    );
-- 指明列名，可跳过取默认值的列；可更改顺序，一般用这种，更清晰
INSERT INTO customers (
    address,
    city,
    state,
    last_name,
    first_name,
    birth_date
    )
VALUES (
    '5225 Figueroa Mountain Rd',
    'Los Olivos',
    'CA',
    'Jackson',
    'Michael',    
    '1958-08-29' 
    );
-- 2、插入多行
INSERT INTO shippers (name)
VALUES ('shipper1'),('shipper2'),('shipper3');
-- exercise
INSERT INTO products 
VALUES (DEFAULT, 'product1', 1, 10),(DEFAULT, 'product2', 2, 20),(DEFAULT, 'product3', 3, 30);
-- 或指明列名
INSERT INTO products (name, quantity_in_stock, unit_price)
VALUES (DEFAULT, 'product1', 1, 10),(DEFAULT, 'product2', 2, 20),(DEFAULT, 'product3', 3, 30);
-- 注意：对于AI (Auto Incremental 自动递增) 的字段，之前删除的/用过的id在之后无法使用，并在此基础上递增

-- 3、插入分级行 Inserting Hierarchical 
/* 订单表（orders表）里的一条记录对应订单项目表（order_items表）里的多条记录
一对多，是相互关联的父子表。 一个订单可以订多个产品
通过添加一条订单记录和对应的多条订单项目记录
学习如何向父子表插入分级（层）/耦合数据（insert hierarchical data）*/
/* 关键：在插入子表记录时，需要用内建函数 LAST_INSERT_ID() 获取相关父表记录的自增ID（这个例子中就是 order_id)
LAST_INSERT_ID()：获取最新的成功的INSERT语句中的自增id，在这个例子中就是父表里新增的order_id.

/* 新增一个订单（order），里面包含两个订单项目/两种商品（order_items），请同时更新订单表和订单项目表 */
USE sql_store;
INSERT INTO orders (customer_id, order_date, status)
VALUES (1, '2019-01-01', 1);
-- 可以先试一下用 SELECT last_insert_id() 看能否成功获取到的最新的 order_id
SELECT last_insert_id();
INSERT INTO order_items  -- 全是必须字段，就不用指定了
VALUES 
    (last_insert_id(), 1, 2, 2.5), -- last_insert_id()为最新的订单编号11
    (last_insert_id(), 2, 5, 1.5); -- 11号订单新订的两个产品记入order_items

-- 4、创建表的副本 Creating a Copy of a Table 
-- DROP TABLE table_name：彻底删除某个表 
-- TRUNCATE TABLE table_name：清空某个表的数据
-- 复制一个表 CREATE TABLE 新表名 AS 子查询
-- INSERT INTO 表名 子查询：将一个表中的某些数据插入另一个表中
/* 案例1：快速创建表 orders 的副本表 orders_archived */
USE sql_store;
CREATE TABLE orders_archived AS
    SELECT * FROM orders;  -- 子查询
/*
SELECT * FROM orders 选择了 oders 中所有数据，作为AS的内容，是一个子查询
子查询： 任何一个充当另一个SQL语句的一部分的 SELECT…… 查询语句都是子查询，子查询是一个很有用的技巧。
注意:
1、创建已有的表或删除不存在的表的话都会报错，所以建表和删表语句都最好加上条件语句（后面会讲）
2、复制的表没有继承主键和自增属性
*/
/* 案例 2：用原表中部分数据创建副本表
用今年以前的 orders 创建一个副本表 orders_archived
其实就是在子查询里增加了一个WHERE语句进行筛选*/
-- 方法1：dorp 然后 create
USE sql_store;
DROP TABLE orders_archived;  -- 这里必须先删除表，才能重新创建
CREATE TABLE orders_archived AS
    SELECT * FROM orders
    WHERE order_date < '2019-01-01';
-- 方法2. TRUCATE 然后 INSERT INTO 
TRUNCATE table orders_archived;  -- 清空表
INSERT INTO orders_archived  -- 不用指明列名，会直接用子查询表里的列名
SELECT * FROM orders  -- 子查询，替代原先插入语句中VALUES(……,……),(……,……),…… 的部分
WHERE order_date < '2019-01-01';
-- exercise：创建一个存档发票表，只包含有过支付记录的发票并将顾客id换成顾客名字
/* 构建的思路顺序：
先创建子查询，确定新表内容：
A. 合并发票表和顾客表
B. 筛选支付记录不为空的行/记录
C. 筛选（并重命名）需要的列
2. 第1步得到的查询内容，可以先运行看一下，确保准确无误后
再作为子查询内容存入新创建的副本订单存档表 CREATE TABLE 新表名 AS 子查询 */
USE sql_invoicing;
CREATE TABLE invoices_archived AS
    SELECT i.invoice_id, c.name AS client, i.payment_date  -- 为了简化，就选这三列
    FROM invoices i JOIN clients c USING (client_id)
    WHERE i.payment_date IS NOT NULL; -- 或者 i.payment_total > 0

-- 5、更新单行数据
/*
UPDATE 表 
SET clo_name1 = new_data1,col_name2 = new_data2  -- new_data=具体数值、NULL、DEFAULT、数学表达式
WHERE 行筛选 */
USE sql_invoicing;
UPDATE invoices
SET 
    payment_total = 0.5 * invoice_total, 
    payment_date = DEFAULT 
WHERE invoice_id = 2;

-- 6、更新多行 Updating Multiple Rows 
-- 语法一样的，就是让 WHERE…… 的条件包含更多记录，就会同时更改多条记录了
/* Workbench默认开启了Safe Updates功能，一次只能更新一条记录
要先关闭该功能（在 Edit-Preferences-SQL Editor-Safe Updates）*/
USE sql_invoicing;
UPDATE invoices
SET payment_total = 233, payment_date = due_date
WHERE client_id = 3; -- 该客户的发票记录不止一条，将同时更改
-- WHERE client_id IN (3, 4) 
-- 直接省略 WHERE 语句，会直接更改整个表的全部记录
-- 练习：让所有非90后顾客的积分增加50点
USE sql_store;
UPDATE customers
SET points = points+50
WHERE birth_date < '1990-01-01';

-- 7、在Updates中用子查询 Using Subqueries in Updates
-- 本质上是将子查询用在 WHERE…… 行筛选条件中
/* 案例：更改发票记录表中名字叫 Yadel 的记录
但该表只有 client_id，故先要从另一个顾客表中查询叫 Yadel 人的 client_id*/
USE sql_invoicing;
UPDATE invoices
SET payment_total = 567, payment_date = due_date
WHERE client_id = (SELECT client_id FROM clients WHERE name = 'Yadel'); -- 放入括号，确保先执行
/* 子查询语句返回多个结果则用IN
WHERE client_id IN (SELECT client_id FROM clients WHERE state IN ('CA', 'NY'))
*/
/* Update 前，最好先验证看一看子查询以及WHERE行筛选条件是不是准确的
筛选出的是不是我们的修改目标， 确保不会改错记录
再套入 UPDATE SET 语句更新*/
SELECT client_id FROM clients WHERE state IN ('CA', 'NY');
-- 以及验证WHERE行筛选条件（即先不UPDATE，先SELECT，改之前，先看一看要改的目标选对了没）
SELECT * FROM invoices
WHERE client_id IN (SELECT client_id FROM clients WHERE state IN ('CA', 'NY'));

UPDATE invoices
SET payment_total = 567, payment_date = due_date
WHERE client_id IN (SELECT client_id FROM clients WHERE state IN ('CA', 'NY'));
-- 练习:将orders表里那些 分数>3k 的用户的订单 comments 改为 'gold customer'
/*思考步骤：
WHERE 行筛选出要求的顾客
SELECT 列筛选他们的id
将前两步 作为子查询 用在修改语句中的 WHERE 条件中，执行修改 */
USE sql_store;
UPDATE orders
SET comments = 'gold customer'
WHERE customer_id IN (SELECT customer_id FROM customers WHERE points > 3000);

-- 8、删除行 Deleting Rows (1:24)
-- DELETE FROM 表 WHERE 行筛选条件（当然也可用子查询）
-- 案例：选出顾客id为3/顾客名字叫'Myworks'的发票记录
USE sql_invoicing;
DELETE FROM invoices WHERE client_id = 3; -- WHERE可选，省略就是会删除整个表的所有行/记录
DELETE FROM invoices WHERE client_id = (SELECT client_id  FROM clients WHERE name = 'Myworks');

第五章 聚合函数、汇总、分组筛选

/* —————————————————————————————————————————————— 第五章 汇总数据 —————————————————————————————————————————————— */
-- 1. 聚合函数 Aggregate Functions (9:19)
-- 小结：聚合函数：输入一系列值并聚合为一个结果的函数
-- 实例
USE sql_invoicing;
SELECT 
    MAX(invoice_date) AS latest_date,  
    -- SELECT选择的不仅可以是列，也可以是数字、列间表达式、列的聚合函数
    MIN(invoice_total) lowest,
    AVG(invoice_total) average,
    SUM(invoice_total * 1.1) total,
    COUNT(*) total_records,
    COUNT(invoice_total) number_of_invoices, 
    -- 和上一个相等
    COUNT(payment_date) number_of_payments,  
    -- 【聚合函数会忽略空值】，得到的支付数少于发票数
    COUNT(DISTINCT client_id) number_of_distinct_clients
    -- DISTINCT client_id 筛掉了该列的重复值，再COUNT计数，会得到不同顾客数
FROM invoices
WHERE invoice_date > '2019-07-01';  -- 想只统计下半年的结果
-- 练习：分为上半年、下半年
-- 思路：很明显要 分类子查询+聚合函数+UNION
USE sql_invoicing;
SELECT 
	'1st_half_of_2019' AS date_range,
	SUM(invoice_total) AS total_sales,
	SUM(payment_total) AS total_payments,
	SUM(invoice_total - payment_total) AS what_we_expect
FROM invoices
WHERE invoice_date BETWEEN '2019-01-01' AND '2019-06-30'
UNION
    SELECT 
        '2st_half_of_2019' AS date_range,
        SUM(invoice_total) AS total_sales,
        SUM(payment_total) AS total_payments,
        SUM(invoice_total - payment_total) AS what_we_expect
    FROM invoices
    WHERE invoice_date BETWEEN '2019-07-01' AND '2019-12-31'
UNION
    SELECT 
        'Total' AS date_range,
        SUM(invoice_total) AS total_sales,
        SUM(payment_total) AS total_payments,
        SUM(invoice_total - payment_total) AS what_we_expect
    FROM invoices
    WHERE invoice_date BETWEEN '2019-01-01' AND '2019-12-31';

-- 2. GROUP BY子句 The GROUP BY Clause (7:21)
-- 小结：按一列或多列分组，注意语句的位置。
-- 案例1：按一个字段分组
-- 在发票记录表中按不同顾客分组统计下半年总销售额并降序排列
USE sql_invoicing;
SELECT 
    client_id,  
    SUM(invoice_total) AS total_sales
/* 只有聚合函数是按 client_id 分组时，这里选择 client_id 列才有意义
（分组统计语句里SELECT通常都是选择分组依据列+目标统计列的聚合函数，选别的列没意义）
若未分类，结果会是一条总 total_sales 和一条 client_id（该client_id无意义）
即 client_id 会被压缩为只显示一条而非 SUM 广播为多条 */
FROM invoices
WHERE invoice_date >= '2019-07-01'  -- 筛选，过滤器
GROUP BY client_id  -- 分组
ORDER BY invoice_total DESC;
-- 若省略排序语句就会默认按分组依据排序（后面一个例子发现好像也不一定，所以最好别省略）
-- 记住语句顺序很重要 WHERE GROUP BY ORDER BY，分组语句在排序语句之前，调换顺序会报错
-- 案例2：按多个字段分组
-- 算各州各城市的总销售额
-- 如前所述，一般分组依据字段也正是 SELECT …… 里的选择字段，如下面例子里的 state 和 city
USE sql_invoicing;
SELECT 
    state,
    city,
    SUM(invoice_total) AS total_sales
FROM invoices
JOIN clients USING (client_id) 
-- 别忘了USING之后是括号，太容易忘了
GROUP BY state, city  
-- 逗号分隔就行
-- 这个例子里 GROUP BY 里去掉 state 结果一样
ORDER BY state;
/* 其实上面的例子里一个城市只能属于一个州中，所有归根结底还是算的各城市的销售额
，GROUP BY …… 里去掉 state 只写 city （但 SELECT 和 ORDER BY 里保留 state）
结果是完全一样的（包括结果里的 state 列），
下面这个例子更能说明以多个字段为分组依据进行分组统计的意义 */
-- 练习：在 payments 表中，按日期和支付方式分组统计总付款额
/* 每个分组显示一个日期和支付方式的独立组合，可以看到某特定日期特定支付方式的总付款额。
这个例子里每一种支付方式可以在不同日子里出现，每一天也可以出现多种支付方式，这种情况，
才叫真·多字段分组。不过上一个例子里那种假·多字段分组，
把 state 加在分组依据里也没坏处还能落个心安，也还是加上别省比较好 */
USE sql_invoicing;
SELECT 
    date, 
    pm.name AS payment_method,
    SUM(amount) AS total_payments
FROM payments p
JOIN payment_methods pm
    ON p.payment_method = pm.payment_method_id
GROUP BY date, payment_method
-- 用的是 SELECT 里的列别名
ORDER BY date;
-- 思想
-- 解答复杂问题时，学会先分解拆分为简单的小问题或小步骤逐个击破。合理运用分解组合和IPO（input-process-output 输入-过程-输出）思想。

-- 3. HAVING子句 The HAVING Clause (8:50)
-- 小结
-- HAVING 和 WHERE 都是是条件筛选语句，条件的写法相通，数学、比较（包括特殊比较）、逻辑运算都可以用（如 AND、REGEXP 等等）
/* 两者本质区别:
WHERE 是对 FROM JOIN 里原表中的列进行 事前筛选，所以WHERE可以对没选择的列进行筛选，但必须用原表列名而不能用SELECT中确定的列别名
相反 HAVING …… 对 SELECT …… 查询后（通常是分组并聚合查询后）的结果列进行事后筛选，
若SELECT里起了别名的字段则必须用别名进行筛选，且不能对SELECT里未选择的字段进行筛选。
唯一特殊情况是，当HAVING筛选的是聚合函数时，该聚合函数可以不在SELECT里显性出现，见最后补充 */
-- 案例 筛选出总发票金额大于500且总发票数量大于5的顾客
USE sql_invoicing;
SELECT 
    client_id,
    SUM(invoice_total) AS total_sales,
    COUNT(*) AS number_of_invoices
FROM invoices
GROUP BY client_id
HAVING total_sales > 500 AND number_of_invoices > 5;
-- 均为 SELECT 里的列别名
-- 若写：WHERE total_sales > 500 AND number_of_invoices > 5，会报错：Error Code: 1054. Unknown column 'total_sales' in 'where clause'
-- 练习
-- 在 sql_store 数据库（有顾客表、订单表、订单项目表等）中，找出在 'VA' 州且消费总额超过100美元的顾客（这是一个面试级的问题，还很常见）
/* 思路：
1. 需要的信息在顾客表、订单表、订单项目表三张表中，先将三张表合并
2. WHERE 事前筛选 'VA' 州的
3. 按顾客分组，并选取所需的列并聚合得到每位顾客的付款总额
4. HAVING 事后筛选超过 100美元的 */
USE sql_store;
SELECT 
    c.customer_id,
    c.first_name,
    c.last_name,
    SUM(oi.quantity * oi.unit_price) AS total_sales
FROM customers c
JOIN orders o USING (customer_id)  -- 别忘了括号，特容易忘
JOIN order_items oi USING (order_id)
WHERE state = 'VA'
GROUP BY 
    c.customer_id, 
    c.first_name, 
    c.last_name
HAVING total_sales > 100;
/* 补充
学第六章第6节时发现，当 HAVING 筛选的是聚合函数时，该聚合函数可以不在SELECT里显性出现。
（作为一种需要记住的特殊情况）如：下面这两种写法都能筛选出总点数大于3k的州，如果不要求显示总点数，应该用后一种*/
SELECT state, SUM(points)
FROM customers
GROUP BY state
HAVING SUM(points) > 3000;
-- 或
SELECT state
FROM customers
GROUP BY state
HAVING SUM(points) > 3000;

-- 4. ROLLUP运算符 The ROLLUP Operator (5:05)
-- GROUP BY …… WITH ROLL UP 自动汇总型分组，若是多字段分组的话汇总也会是多层次的，注意这是MySQL扩展语法，不是SQL标准语法
-- 案例 分组查询各客户的发票总额以及所有人的总发票额
USE sql_invoicing;
SELECT 
    client_id,
    SUM(invoice_total)
FROM invoices
GROUP BY client_id WITH ROLLUP;
-- 多字段分组 例1：分组查询各州、市的总销售额（发票总额）以及州层次和全国层次的两个层次的汇总额
SELECT 
    state,
    city,
    SUM(invoice_total) AS total_sales
FROM invoices
JOIN clients USING (client_id) 
GROUP BY state, city WITH ROLLUP;
-- 多字段分组 例2：分组查询特定日期特定付款方式的总支付额以及单日汇总和整体汇总
USE sql_invoicing;
SELECT 
    date, 
    pm.name AS payment_method,
    SUM(amount) AS total_payments
FROM payments p
JOIN payment_methods pm
    ON p.payment_method = pm.payment_method_id
GROUP BY date, pm.name WITH ROLLUP;
-- 练习 分组计算各个付款方式的总付款 并汇总
SELECT 
    pm.name AS payment_method,
    SUM(amount) AS total
FROM payments p
JOIN payment_methods pm
    ON p.payment_method = pm.payment_method_id
GROUP BY pm.name WITH ROLLUP;

/* 关于SQL关键词优先级的补充:
 1.FORM：对FROM的左边的表和右边的表计算笛卡尔积。产生虚表VT1
 2.JOIN： 如果指定了OUTER JOIN（比如left join、 right join），那么保留表中未匹配的行就会作为外部行添加到虚拟表VT2中。
 3.ON/USING：对虚表VT1进行ON筛选，只有那些符合<join-condition>的行才会被记录在虚表VT2中。
 4.WHERE： 对虚拟表VT3进行WHERE条件过滤。  只有符合<where-condition>的记录才会被插入到虚  拟表VT4中。
 5.GROUP BY： 根据group by子句中的列，对VT4中的记录进行分组操作，产生VT5。
 6.HAVING： 对虚拟表VT5应用having过滤，只有符合<having-condition>的记录才会被 插入到虚拟表VT6中。
 7.SELECT： 注意：若进行了分组，这一步常常要聚合;
			SELECT 是在大部分语句执行了之后才执行的，严格的说是在 FROM、WHERE 和 GROUP BY （以及 HAVING）之后执行的。
            理解这一点是非常重要的，这就是你不能在 WHERE 中使用在 SELECT 中设定别名的字段作为判断条件的原因。
			SELECT 的执行顺序是在 WHERE GROUP BY 之后，而在 HAVING 之前 
			WHERE 里必须用原列名，GROUP BY 是原列名或列别名都可用;而 HAVING 必须用 SELECT 里的列别名（聚合函数除外）
 8.DISTINCT： 对VT7中的记录进行去重。产生虚拟表VT8。
 9. UNION 纵向合并
 10.ORDER BY: 将虚拟表VT8中的记录按照<order_by_list>进行排序操作，产生虚拟表VT9。
 11.LIMIT：取出指定行的记录，产生虚拟表VT10, 并将结果返回。
*/

第六章 复杂查询

/* —————————————————————————————————————————————— 第六章 编写复杂查询 —————————————————————————————————————————————— */
-- 1. 子查询 Subqueries (2:29)
-- 子查询： 任何一个充当另一个SQL语句的一部分的 SELECT…… 查询语句都是子查询，子查询是一个很有用的技巧。子查询的层级用括号实现。
-- 案例：在 products 中，找到所有比生菜（id = 3）价格高的
-- 关键：用子查询找到生菜价格
USE sql_store;
SELECT *
FROM products
WHERE unit_price > (
    SELECT unit_price
    FROM products
    WHERE product_id = 3
);
-- MySQL执行时会先执行括号内的子查询（内查询），将获得的生菜价格作为结果返回给外查询
-- 练习：在 sql_hr 库 employees 表里，选择所有工资超过平均工资的雇员
-- 关键：由子查询得到平均工资
USE sql_hr;
SELECT *
FROM employees
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
);
-- 3. IN运算符 The IN Operator (3:39)
-- 案例：在 sql_store 库 products 表中找出那些从未被订购过的产品
/* 思路：
1. order_items 表里有所有产品被订购的记录，用 DISTINCT 去重，得到所有被订购过的产品列表
2. 不在这列表里（NOT IN 的使用）的产品即为从未被订购过的产品 */
USE sql_store;
SELECT *
FROM products
WHERE product_id NOT IN (
    SELECT DISTINCT product_id
    FROM order_items
);
-- 上一节是子查询返回一个值（平均工资），这一节是返回一列数据（被订购过的产品id列表），之后还会用子查询返回一个多列的表
-- 练习：在 sql_invoicing 库 clients 表中找到那些没有过发票记录的客户
-- 思路：和上一个例子完全一致，在invoices里用DISTINCT找到所有有过发票记录的客户列表，再用NOT IN来筛选
USE sql_invoicing;
SELECT *
FROM clients
WHERE client_id NOT IN (
    SELECT DISTINCT client_id
    FROM invoices
);
-- 4. 子查询vs连接 Subqueries vs Joins (5:07)
/* 小结
子查询（Subquery）是将一张表的查询结果作为另一张表的查询依据并层层嵌套，其实也可以先将这些表链接（Join）合并成一个包含所需全部信息的详情表再直接在详情表里筛选查询。
两种方法一般是可互换的，具体用哪一种取决于 效率/性能（Performance） 和 可读性（readability），之后会学习 执行计划，到时候就知道怎样编写并更快速地执行查询，现在主要考虑可读性*/
-- 案例：上节课的案例，找出从未订购（没有invoices）的顾客：
-- 法1. 子查询：先用子查询查出有过发票记录的顾客名单，作为筛选依据
USE sql_invoicing;
SELECT *
FROM clients
WHERE client_id NOT IN (
    SELECT DISTINCT client_id
    /*其实这里加不加DISTINCT对子查询返回的结果有影响
    但对最后的结果其实没有影响*/
    FROM invoices
);
-- 法2. 链接表：用顾客表 LEFT JOIN 发票记录表，再直接在这个合并详情表中筛选出没有发票记录的顾客
USE sql_invoicing;
SELECT DISTINCT client_id, name 
-- 不能SELECT DISTINCT *
FROM clients
LEFT JOIN invoices USING (client_id)
-- 注意不能用内链接，否则没有发票记录的顾客（我们的目标）直接就被筛掉了
WHERE invoice_id IS NULL;
-- 就上面这个案例而言，子查询可读性更好，但有时子查询会过于复杂（嵌套层数过多），用链接表更好（下面的练习就是）。总之在选择方法时，可读性是很重要的考虑因素
-- 练习：在 sql_store 中，选出买过生菜（id = 3）的顾客的id、姓和名
-- 分别用子查询法和链接表法实现并比较可读性
-- 法1. 完全子查询
USE sql_store;
SELECT customer_id, first_name, last_name
FROM customers
WHERE customer_id IN (  
    -- 子查询2：从订单表中找出哪些顾客买过生菜
    SELECT customer_id
    FROM orders
    WHERE order_id IN (  
        -- 子查询1：从订单项目表中找出哪些订单包含生菜
        SELECT DISTINCT order_id
        FROM order_items
        WHERE product_id = 3
    )
);
-- 法2. 混合：子查询 + 表连接
USE sql_store;
SELECT customer_id, first_name, last_name
FROM customers
WHERE customer_id IN (  
    -- 子查询：哪些顾客买过生菜
    SELECT customer_id
    FROM orders
    JOIN order_items USING (order_id)  
    -- 表连接：合并订单和订单项目表得到 订单详情表
    WHERE product_id = 3
);
-- 法3. 完全表连接
-- 直接链接合并3张表（顾客表、订单表和订单项目表）得到 带顾客信息的订单详情表，该合并表包含我们所需的所有信息，可直接在合并表中用WHERE筛选买过生菜的顾客（注意 DISTINCT 关键字的运用）。
USE sql_store;
SELECT DISTINCT customer_id, first_name, last_name
FROM customers
LEFT JOIN orders USING (customer_id)
LEFT JOIN order_items USING (order_id)
WHERE product_id = 3;
-- 这个案例中，先将所需信息所在的几张表全部连接合并成一张大表再来查询筛选明显比层层嵌套的多重子查询更加清晰明了

-- 5. ALL关键字 The ALL Keyword (4:52)
-- > (MAX (……)) 和 > ALL(……) 等效可互换
-- “比这里面最大的还大” = “比这里面的所有的都大”
-- 案例 sql_invoicing 库中，选出金额大于3号顾客所有发票金额（或3号顾客最大发票金额） 的发票
-- 法1. 用MAX关键字
USE sql_invoicing;
SELECT *
FROM invoices
WHERE invoice_total > (
    SELECT MAX(invoice_total)
    FROM invoices
    WHERE client_id = 3
);
-- 法2. 用ALL关键字
USE sql_invoicing;
SELECT *
FROM invoices
WHERE invoice_total > ALL (
    SELECT invoice_total
    FROM invoices
    WHERE client_id = 3
);
/* 其实就是把内层括号的MAX拿到了外层括号变成ALL：
MAX法是用MAX()返回一个顾客3的最大订单金额，再判断哪些发票的金额比这个值大；
ALL法是先返回顾客3的所有订单金额，是一列值，再用ALL()判断比所有这些金额都大的发票有哪些。
两种方法是完全等效的 */

-- 6. ANY关键字 The ANY Keyword (2:36)
-- > ANY/SOME (……) 与 > (MIN (……)) 等效
-- = ANY/SOME (……) 与 IN (……) 等效
-- 案例1 sql_invoicing 库中，选出金额大于3号顾客任何发票金额（或最小发票金额） 的发票
USE sql_invoicing;
SELECT *
FROM invoices
WHERE invoice_total > ANY (
    SELECT invoice_total
    FROM invoices
    WHERE client_id = 3
);
-- 或
WHERE invoice_total > (
    SELECT MIN(invoice_total)
    FROM invoices
    WHERE client_id = 3
);
-- 案例2
-- = ANY (……) 与 IN (……) 等效的例子:
-- 选出至少有两次发票记录的顾客
USE sql_invoicing;
SELECT *
FROM clients
WHERE client_id IN (  -- 或 = ANY ( 
    -- 子查询：有2次以上发票记录的顾客
    SELECT client_id
    FROM invoices
    GROUP BY client_id
    HAVING COUNT(*) >= 2
);

-- 7. 相关子查询 Correlated Subqueries (5:36)
/* 小结
之前都是非关联主/子（外/内）查询，比如子查询先查出整体的某平均值或满足某些条件的一列id，作为主查询的筛选依据，
这种子查询与主查询无关，会先一次性得出查询结果再返回给主查询供其使用。
而下面这种相关联子查询例子里，子查询要查询的是某员工所在办公室的平均值，子查询是依赖主查询的，
注意这种关联查询是在主查询的每一行/每一条记录层面上依次进行的，这一点可以为我们写关联子查询提供线索（注意表别名的使用），另外也正因为这一点，相关子查询会比非关联查询执行起来慢一些。*/
/* 案例
选出 sql_hr.employees 里那些工资超过他所在办公室平均工资（而不是整体平均工资）的员工
关键：如何查询目前主查询员工的所在办公室的平均工资而不是整体的平均工资？
思路：给主查询 employees表 设置别名 e，这样在子查询查询平均工资时加上 WHERE office_id = e.office_id 筛选条件即可相关联地查询到目前员工所在地办公室的平均工资 */
USE sql_hr;
SELECT *
FROM employees e  -- 关键 1
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
    WHERE office_id = e.office_id  -- 关键 2
    -- 【子查询表字段不用加前缀，主查询表的字段要加前缀，以此区分】
);
-- 相关子查询很慢，但很强大，也有很多实际运用
-- 练习：在 sql_invoicing 库 invoices 表中，找出高于每位顾客平均发票金额的发票
USE sql_invoicing;
SELECT *
FROM invoices i
WHERE  invoice_total > (
    -- 子查询：目前客户的平均发票额
    SELECT AVG(invoice_total)
    FROM invoices
    WHERE client_id = i.client_id
);

-- 8. EXISTS运算符 The EXISTS Operator (5:39)
/* 小结
IN + 子查询 等效于 EXIST + 相关子查询，如果前者子查询的结果集过大占用内存，
用后者逐条验证更有效率。另外 EXIST() 本质上是根据是否为空返回 TRUE 和 FALSE，所以也可以加 NOT 取反。*/
-- 案例：找出有过发票记录的客户，第4节学过用子查询或表连接来实现
-- 法1. 子查询
USE sql_invoicing;
SELECT *
FROM clients
WHERE client_id IN (
    SELECT DISTINCT client_id
    FROM invoices
);
-- 法2. 链接表
USE sql_invoicing;
SELECT DISTINCT client_id, name …… 
FROM clients
JOIN invoices USING (client_id);
-- 内链接，只留下有过发票记录的客户
-- 第3种方法是用EXISTS运算符实现
USE sql_invoicing;
SELECT *
FROM clients c
WHERE EXISTS (
    SELECT client_id  
    /* 就这个子查询的目的来说，SELECT的选择不影响结果，
    因为EXISTS()函数只根据是否为空返回 TRUE 和 FALSE */
    FROM invoices
    WHERE client_id = c.client_id
);
/*
这还是个相关子查询，因为在其中引用了主查询的 clients 表。这同样是按照主查询的记录一条条验证执行的。
具体说来，对于 clients 表（设置别名为 c）里的每一个顾客，子查询在 invoices 表查找这个人的发票记录（ 即 client_id = c.client_id 的发票记录），
有就返回相关记录否者返回空，然后 EXISTS() 根据是否为空得到 TRUE 和 FALSE（表示此人有无发票记录），然后主查询凭此确定是否保留此条记录。

对比一下，法1是用子查询返回一个有发票记录的顾客id列表，如（1，3，8 ……），
然后用IN运算符来判断，如果子查询表太大，可能返回一个上百万千万甚至上亿的id列表，这个id列表就会很占内存非常影响性能，
对于这种子查询会返回一个很大的结果集的情况，用这里的EXIST+相关子查询逐条筛选会更有效率

另外，因为 SELECT() 返回的是 TRUE/FALSE，所以自然也可以加上NOT取反，见下面的练习 */

-- 练习：在sql_store中，找出从来没有被订购过的产品。
USE sql_store;
SELECT *
FROM products 
WHERE product_id NOT IN (
    SELECT product_id 
    -- 加不加DISTINCT对最终结果无影响
    FROM order_items
);
-- 或
SELECT *
FROM products p
WHERE NOT EXISTS (
    SELECT *
    FROM order_items
    WHERE product_id = p.product_id
);
-- 对于亚马逊这样的大电商来说，如果用IN+子查询法，子查询可能会返回一个百万量级的产品id列表，这种情况还是用EXIST+相关子查询逐条验证法更有效率

-- 9. SELECT子句的子查询 Subqueries in the SELECT Clause (4:29)
/* 小结
不仅 WHERE 筛选条件里可以用子查询，SELECT 选择子句和 FROM 来源表子句也能用子查询，这节课讲 SELECT 子句里的子查询
简单讲就是，SELECT选择语句是用来确定查询结果选择包含哪些字段，每个字段都可以是一个表达式，而每个字段表达式里的元素除了可以是原始的列，具体的数值，也同样可以是其它各种花里胡哨的子查询的结果
任何子查询都是简单查询的嵌套，没什么新东西，只是多了一个层级而已，由内向外地一层层梳理就很清楚
要特别注意记住以子查询方式实现在SELECT中使用同级列别名的方法 */
-- 案例：得到一个有如下列的表格：invoice_id, invoice_total, avarege（总平均发票额）, difference（前两个值的差）
USE sql_invoicing;
SELECT 
    invoice_id,
    invoice_total,
    (SELECT AVG(invoice_total) FROM invoices) AS invoice_average,
    /*不能直接用聚合函数，因为“比较强势”，会压缩聚合结果为一条
    用括号+子查询(SELECT AVG(invoice_total) FROM invoices) 
    将其作为一个数值结果 152.388235 加入主查询语句*/
    invoice_total - (SELECT invoice_average) AS difference
    /*SELECT表达式里要用原列名，不能直接用别名invoice_average
    要用列别名的话用子查询（SELECT 同级的列别名）即可
    说真的，感觉这个子查询有点难以理解，但记住会用就行*/
FROM invoices;
-- 练习
-- 得到一个有如下列的表格：client_id, name, total_sales（各个客户的发票总额）, average（总平均发票额）, difference（前两个值的差）
USE sql_invoicing;
SELECT 
    client_id,
    name,
    (SELECT SUM(invoice_total) FROM invoices WHERE client_id = c.client_id) AS total_sales,
    -- 要得到【相关】客户的发票总额，要用相关子查询 WHERE client_id = c.client_id
    (SELECT AVG(invoice_total) FROM invoices) AS average,
    (SELECT total_sales - average) AS difference   
    /* 如前所述，引用同级的列别名，要加括号和 SELECT，
    和前两行子查询的区别是，引用同级的列别名不需要说明来源，
    所以没有 FROM …… */
FROM clients c;
-- 注意第四个客户的 total_sales 和 difference 都是空值 null

-- 10. FROM子句的子查询 Subqueries in the FROM Clause (2:58)
/* 小结
子查询的结果同样可以充当一个“虚拟表”作为FROM语句中的来源表，即将筛选查询结果作为来源再进行进一步的筛选查询。
但注意只有在子查询不太复杂时进行这样的嵌套，否则最好用后面讲的视图先把子查询结果储存起来再使用。*/
-- 案例 将上一节练习里的查询结果当作来源表，查询其中 total_sales 非空的记录
USE sql_invoicing;
SELECT * 
FROM (
    SELECT 
        client_id,
        name,
        (SELECT SUM(invoice_total) FROM invoices WHERE client_id = c.client_id) AS total_sales,
        (SELECT AVG(invoice_total) FROM invoices) AS average,
        (SELECT total_sales - average) AS difference   
    FROM clients c
) AS sales_summury /* 在FROM中使用子查询，即使用 “派生表” 时，必须给派生表取个别名（不管用不用），这是硬性要求，不写会报错：*/
WHERE total_sales IS NOT NULL;
-- 复杂的子查询再嵌套进 FROM 里会让整个查询看起来过于复杂，上面这个最好是将子查询结果储存为叫 sales_summury 的视图，然后再直接使用该视图作为来源表，之后会讲。

第七章 基础内置函数

/* —————————————————————————————————————————————— 第七章 MySQL的基本函数 —————————————————————————————————————————————— */
-- 内置的用来处理数值、文本、日期等的函数
-- 1. 数值函数 Numeric Functions Google：mysql numeric function
-- ROUND、TRUNCATE、CEILING、FLOOR、ABS、RAND
SELECT round(5.73568,2); -- 四舍五入
select truncate(5.73568,2); -- 只保留小数点后两位，后面的删除
select ceiling(5.73568); -- 向上取整
select floor(5.73568); -- 向下取整
select abs(-5.73568); -- 取绝对值
select rand(); -- （0，1）取随机浮点数

-- 2. 字符串
select length('apple end'); -- 字符串长度（包括空格）
select upper('apple end'); -- 变成大写
select lower('APPLE END'); -- 变成小写
select ltrim(' apple end a'); -- 删除左边空格
select rtrim('an apple end '); -- 删除右边空格
select trim(' apple end '); -- 删除所有空格
select length(' apple end '); -- 11
select length(trim(' apple end ')); -- 9
select left('string',2); -- 返回左边前两个字符
select right('string',2); -- 返回右边后两个字符
select substring('string',3,4); -- 截取字符 substring('string',起始点,长度)
select locate('r','stringred'); -- 返回第一次出现 r 的位置
select locate('tr','stringtred'); -- 返回第一次出现 tr 的位置
select replace('dancing','ing','ed'); -- 替换
select concat('first',' ','last',' ','end'); -- 连接

-- 3. 时间/日期 date function
select now(); -- 获取现在时间 '2022-02-22 14:19:17'
--
select curdate();  -- 获取现在日期 '2022-02-22'
select curtime(); -- 获取现在时刻 '14:19:17' 
--
select year(now()); -- 获取年 2022
select month(now());  -- 获取月 2
select monthname(now()); -- 'February'
select day(now());  -- 获取日 22
select dayname(now()); -- 'Tuesday'
select hour(now()); -- 获取时
select minute(now()); -- 获取分
select second(now());-- 获取秒
-- extract方法获取年月日时分秒
select extract(day from now()); -- 22 获取日
select extract(second from now()); -- 获取秒

-- 4. MySQL date format string 标准日期格式
select date_format(now(),"%y-%m-%d"); -- '22-02-22'
select date_format(now(),"%Y-%M-%D"); -- '2022-February-22nd'
select time_format(now(),"%h-%i-%p"); -- '02-50-PM' 下午 2时50分 时分秒没有大写写法

-- 5. 时间/日期计算
-- 加
select date_add(now(),interval 1 day); -- 加上一天
select date_add(now(),interval -2 hour);  -- 减去2小时
-- 减
select date_sub(now(),interval 2 hour); -- 减去2小时
-- 时间间隔
select datediff('2000-2-21','1998-11-24'); -- 计算两个日期间隔的天数
-- 将时刻化成秒
select time_to_sec('14:18:00'); 

-- 6. ifnull、coalesce
USE sql_store;
-- IFNULL 如果shipper_id中有空的内容，则填充'Not Assigned'
SELECT 
    order_id,
    IFNULL(shipper_id, 'Not Assigned') AS shipper 
FROM orders;
-- coalesce(col1, col2, str) 如果col1为空，则填充col2中的内容，如果col2为空，则填充str
SELECT 
    order_id,
    coalesce(shipper_id, order_id,'Not Assigned') AS shipper -- 如果shipper_id中有空的内容，则填充'Not Assigned'
FROM orders;

-- 7. if设置条件
-- 可以直接成为select的子句
-- if(条件, 条件为真时返回的值, 条件为假时返回的值)
select order_id,if(order_id>3,"yes","no") as clo3 from orders;

-- 8. case多个筛选条件
select 
status, 
case when status=1 then 'status 1'
when status=2 then 'status 2' 
else 'other' end as status2
from orders;
/* 
case
	when 条件1 then 'string1' -- 满足条件则填充'string'
    when 条件2 then 'string2'
    else 'string3' -- 其他情况则填充string3
    end as 别名col 
*/

第八章 视图

/* —————————————————————————————————————————————— 第八章 视图 —————————————————————————————————————————————— */
/* 就是创建虚拟表，自动化一些重复性的查询模块，简化各种复杂操作（包括复杂的子查询和连接等）
注意视图虽然可以像一张表一样进行各种操作，但并没有真正储存数据，
数据仍然储存在原始表中，视图只是储存起来的模块化的查询结果，
是为了方便和简化后续进一步操作而储存起来的虚拟表。 */
USE sql_invoicing;
/* create view view_name as select ...
视图是虚拟表不存储数据，与表的用法相同  */
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;
-- 创建视图后可就当作 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;

-- 删掉视图 DROP VIEW view_name
drop view sales_by_client;

-- 练习：创建一个客户差额表视图，可以看到客户的id，名字以及差额（发票总额-支付总额）
USE sql_invoicing;
CREATE VIEW clients_balance AS
    SELECT 
        client_id,
        c.name,
        SUM(invoice_total - payment_total) AS balance
    FROM clients c
    JOIN invoices USING(client_id)
    GROUP BY client_id;
    
-- 2. 更新或删除视图 Altering or Dropping Views (2:52)
/* 小结
修改视图可以先DROP再CREATE（也可以用CREATE OR REPLACE）
视图的查询语句可以在编辑模式下查看和修改，但最好是保存为sql文件并放在源码控制妥善管理
*/
-- 案例：想在上一节的顾客差额视图的查询语句最后加上按差额降序排列
-- 法1. 先删除再重建
USE sql_invoicing;
DROP VIEW IF EXISTS clients_balance;
CREATE VIEW clients_balance AS select * from clients
    ORDER BY client_id 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.
若丢失了原始查询语句，要修改的话可点击视图的扳手按钮打开编辑模式，可看到如下被MySQL处理了的查询语句
MySQL在前面加了些莫名其妙的东西并且在所有库名表名字段名外套上反引号防止名称冲突（当对象名和MySQL里的关键字相同时确保被当作对象名而不是关键字），但这都不影响
直接做我们需要的修改，如加上ORDER BY balance DESC 然后点apply就行了
*/
CREATE 
    ALGORITHM = UNDEFINED 
    DEFINER = `root`@`localhost` 
    SQL SECURITY DEFINER
VIEW `clients_balance` AS
    SELECT 
        `c`.`client_id` AS `client_id`,
        `c`.`name` AS `name`,
        SUM((`invoices`.`invoice_total` - `invoices`.`payment_total`)) AS `balance`
    FROM
        (`clients` `c`
        JOIN `invoices` ON ((`c`.`client_id` = `invoices`.`client_id`)))
    GROUP BY `c`.`client_id`
    ORDER BY balance DESC;
-- 法2是没有办法的办法，当然最好还是将 views 保存为 sql 文件并放入源码控制

-- 3. 可更新视图 Updatable Views (5:12)
/* 小结
如果一个视图的查询语句中没有如下元素：
1. DISTINCT 去重
2. GROUP BY/HAVING/聚合函数 (后两个通常是伴随着 GROUP BY 分组出现的)
3. UNION 纵向连接
则该视图是可更新视图（Updatable Views），可以增删改，否则只能查。
另外，只有在视图中有原始所有数据时，才能插入，因为有些列在table中是非空的，但视图不一定有这些列
当没有权限操作原表时，就操作视图
*/
-- 案例：创建视图（新虚拟表）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;
/* 这里不能用列别名balance，会报错说不存在，
必须用原列名的表达式，这还是执行顺序的问题
之前讲WHERE和HAVING作为事前筛选和事后筛选的区别时提到过 */
-- 该视图满足条件，是可更新视图，故可以增删改：
-- 删：删掉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 子句 THE WITH CHECK OPTION Clause (2:18)
/* 小结
在视图的原始查询语句最后加上 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号订单执行类似上面的语句后会报错：
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
with check option;
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. 视图的其他优点 Other Benefits of Views (2:37)
/* 三大优点：
简化查询、增加抽象层和减少变化的影响、数据安全性
具体来讲：
1、（首要优点）简化查询 simplify queries
2、增加抽象层，减少变化的影响 Reduce the impact of changes：视图给表增加了一个抽象层（模块化），
这样如果数据库设计改变了（如一个字段从一个表转移到了另一个表），
只需修改视图的查询语句使其能保持原有查询结果即可，
不需要修改使用这个视图的那几十个查询。相反，如果没有视图这一层的话，
所有查询将直接使用指向原表的原始查询语句，这样一旦更改原表设计，就要相应地更改所有的这些查询。
3、限制对原数据的访问权限 Restrict access to the data：
在视图中可以对原表的行和列进行筛选，这样如果你禁止了对原始表的访问权限，
用户只能通过视图来修改数据，他们就无法修改视图中未返回的那些字段和记录。
但注意这通常并不简单，需要良好的规划，否则最后可能搞得一团乱。
了解这些优点，但不要盲目将他们运用在所有的情形中。 */

第九章 存储过程

/* —————————————————————————————————————————————— 第九章 存储过程  —————————————————————————————————————————————— */
-- 1. 什么是存储过程 What are Stored Procedures
/* 存储过程三大作用：
储存和管理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. 创建一个存储过程 Creating a Stored Procedure 
/*
DELIMITER $$ -- 定义定界符为$$，也可以是其他sql非通用符，eg：//
CREATE PROCEDURE 过程名()  
BEGIN
	……; --BEGIN 和 END 之间包裹的是此过程（PROCEDURE）的内容（body），内容里可以有多个语句，但每个语句都要以 ; 结束
	……;
	……;
END$$
DELIMITER ; -- 还原定界符
*/
-- 实例：创造一个get_clients()过程
use sql_store;
delimiter $$
create procedure get_clients()
begin
	select * from customers;
end $$
delimiter ;
/* 
为了将过程内容内部的语句分隔符与SQL本身执行层面的语句分隔符 ; 区别开，要先用 DELIMITER(分隔符) 关键字暂时将SQL语句的默认分隔符改为其他符号，
一般是改成双美元符号 $$ ，创建过程结束后再改回来。
注意创建过程本身也是一个完整SQL语句，所以别忘了在END后要加一个暂时语句分隔符 $$ */
/* 注意
过程内容中所有语句都要以 ; 结尾,并且因此要暂时修改SQL本身的默认分隔符，这些都是MySQL地特性，在SQL Server等就不需要这样 */
/* 调用此程序：
法1. 点击闪电按钮
法2. 用CALL关键字
USE sql_store;
CALL get_clients()
或
CALL sql_invoicing.get_clients() */
/* 注意
上面讲的是如何在SQL中调用储存过程，但更多的时候其实是要在应用程序代码（可能是 C#、JAVA 或 Python 编写的）中调用。*/
-- 练习：创造一个储存过程 get_invoices_with_balance（取得有差额（差额不为0）的发票记录）
use sql_invoicing;
delimiter $$
create procedure get_invoices_with_balance()
begin
	select * from invoices_with_balance where balance>0;
end $$
delimiter ;
call sql_invoicing.get_invoices_with_balance;

-- 3. 使用MySQL工作台创建存储过程 Creating Procedures Using MySQLWorkbench (1:21)
/* 也可以用点击的方式创造过程，右键选择 Create Stored Procedure，填空，Apply。
这种方式 Workbench 会帮你处理暂时修改分隔符的问题
这种方式一样可以储存SQL文件
事实证明，mosh很喜欢用这种方式，后面基本都是用这种方式创建过程（毕竟不用管改分隔符的问题）*/

-- 4. 删除存储过程 Dropping Stored Procedures (2:09)
-- 在发现储存过程里有错误需要重建时使用
-- 实例：一个创建过程（get_clients）的标准模板
USE sql_invoicing;
DROP PROCEDURE IF EXISTS get_clients; 
-- get_clients 不存在时无法删除
-- 注意加上【IF EXISTS】，以免因为此过程不存在而报错
DELIMITER $$
    CREATE PROCEDURE get_clients()
        BEGIN
            SELECT * FROM clients;
        END$$
DELIMITER ;
CALL get_clients();
/* 最佳实践
同视图一样，最好把删除和创建每一个过程的代码也储存在不同的SQL文件中，
并把这样的文件放在 Git 这样的源码控制下，这样就能与其它团队成员共享 Git 储存库。
他们就能在自己的机器上重建数据库以及该数据库下的所有的视图和储存过程
如上面那个实例，可储存在 stored_procedures 文件夹（之前已有 views 文件夹）下的 get_clients.sql 文件。
当你把所有这些脚本放进源码控制，你能随时回来查看你对数据库对象所做的改动。*/

-- 5. 参数 Parameters 
/* 小结
CREATE PROCEDURE 过程名
(
    参数1 数据类型,
    参数2 数据类型,
    ……
)
BEGIN
……
END$$ */
-- 案例：创建过程 get_clients_by_state，可返回特定州的顾客
use sql_invoicing;
drop procedure if exists get_clients_by_state;
delimiter $$
create procedure get_clients_by_state(p_state char(2)) -- varchar(10) int 多个参数可用逗号隔开
begin
select * from clients c where c.state = p_state; -- WHERE state = state是没有意义的,所以要区分列名
end $$
delimiter ;
call get_clients_by_state('CA'); -- 不传入'CA'会报错
/* 练习
创建过程 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(p_client_id int)
begin
select * from invoices i where i.client_id = p_client_id;
end $$
delimiter ;
call get_invoices_by_client(5);

-- 6. 带默认值的参数 Parameters with Default Value (8:18)
-- 给参数设置默认值，主要是运用条件语句块、替换空值函数给null参数赋默认值
/* SQL中的条件类语句：
替换空值 IFNULL(值1，值2)
条件函数 IF(条件表达式, 返回值1, 返回值2)
条件语句块
IF 条件表达式 THEN
    语句1;
    语句2;
    ……;
[ELSE]（可选）
    语句1;
    语句2;
    ……;
END IF;
-- 别忘了【END 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(p_state char(2)) -- varchar(10) int 多个参数可用逗号隔开
begin
	if p_state is null then 
    set p_state = 'CA'; -- 注意这里的;不能忘！
    end if;
	select * from clients c where c.state = p_state; -- WHERE state = state是没有意义的,所以要区分列名
end $$
delimiter ;
-- 注意要调用过程并使用其默认值时时要传入参数 NULL ，MySQL不允许不传参数
call get_clients_by_state(null);
-- 案例2：将 get_clients_by_state 过程设置为默认选取所有顾客
-- 法1. 用IF条件语句块实现
use sql_invoicing;
drop procedure if exists get_clients_by_state;
delimiter $$
create procedure get_clients_by_state(p_state char(2)) -- varchar(10) int 多个参数可用逗号隔开
begin
	if p_state is null then 
		select * from clients c; -- 注意这里的;不能忘！
    else
		select * from clients c where c.state = p_state; -- WHERE state = state是没有意义的,所以要区分列名
	end if;
end $$
delimiter ;
call get_clients_by_state(null);
-- 法2. 用IFNULL替换空值函数实现
use sql_invoicing;
drop procedure if exists get_clients_by_state;
delimiter $$
create procedure get_clients_by_state(p_state char(2)) -- varchar(10) int 多个参数可用逗号隔开
begin
    SELECT * FROM clients c
    WHERE c.state = IFNULL(state, c.state);
-- 若参数为NULL，则返回c.state，利用 c.state = c.state 永远成立来返回所有顾客，思路很巧妙。
end $$
delimiter ;
call get_clients_by_state(null);
/* 练习
创建一个叫 get_payments 的过程，包含 client_id 和 payment_method_id 两个参数，
数据类型分别为 INT(4) 和 TINYINT(1) (1个字节，能存0~255，之后会讲数据类型，好奇可以谷歌 'mysql int size')，
默认参数设置为返回所有记录 */
use sql_invoicing;
drop procedure if exists get_payments;
delimiter $$
create procedure get_payments(p_client_id int, p_payment_method_id tinyint)
begin 
	select * from payments p
    where p.client_id = ifnull(p_client_id, p.client_id) 
    and p.payment_method = IFNULL(p_payment_method_id, p.payment_method);
end $$
delimiter ;
call get_payments(3,1);
-- 3号顾客的所有记录
call get_payments(3,null);
-- 2号支付方式的所有记录
call get_payments(null,2);
/*区别：
Parameter 形参（形式参数）：创建过程中用的占位符，如 client_id、payment_method_id
Argument 实参（实际参数）：调用时实际传入的值，如 1、3、5、NULL
*/

-- 7. 参数验证 Parameter Validation (6:40)
-- 防止输入不合理的参数值
-- 过程除了可以查，也可以增删改，但修改数据前最好先进行参数验证以防止不合理的修改
-- 主要利用 条件语句块 和 SIGNAL SQLSTATE '220033' SET MESSAGE_TEXT = 'error' 关键字
/* begin 后面加上
IF 错误参数条件表达式 THEN
    SIGNAL SQLSTATE '错误类型'
        SET MESSAGE_TEXT = '关于错误的补充信息'   -- （可选）*/
/* 案例
创建一个 make_payment 过程，含 invoice_id, payment_amount, payment_date 三个参数 */
use sql_invoicing;
drop procedure if exists make_payment;
delimiter $$
CREATE  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 $$
delimiter ;
call make_payment(10,-100,'2022-02-24');
/* 为了防止传入像 -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 ' */
use sql_invoicing;
drop procedure if exists make_payment;
delimiter $$
CREATE  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; -- 因为sql无法判断是否存在else，所以要用end if 结束条件语句
    
    UPDATE invoices i
    SET 
        i.payment_total = payment_amount,
        i.payment_date = payment_date
    WHERE i.invoice_id = invoice_id;
END $$
delimiter ;
call sql_invoicing.make_payment(9, null, '2022-02-24');
-- Error Code: 1048. Column 'payment_total' cannot be null	0.016 sec
/* 注意
加入过多的参数验证会让代码过于复杂难以维护，
像 payment_amount 非空这样的验证就不需要添加因为 payment_amount 字段本身就不允许空值因此MySQL会自动报错 */
call sql_invoicing.make_payment(9, -100, '2022-02-24');
-- Error Code: 1644. Invalid payment amount
call sql_invoicing.make_payment(10, 250, '2022-02-24');
/*
参数验证工作更多的应该在应用程序端接受用户输入数据时就检测和报告，那样更快也更有效。
储存过程里的参数验证只是在有人越过应用程序直接访问储存过程时作为最后的防线。这里只应该写那些最关键和必要的参数验证。 */


-- 8. 输出参数 Output Parameters (3:55)
/*小结
用输出参数获取过程的结果值
在参数的前面加上 OUT 关键字，然后再 SELECT 后加上 INTO……
*/
/* 案例：创造 get_unpaid_invoices_for_client 过程，获取特定顾客所有未支付过的发票记录（即 payment_total = 0 的发票记录）*/
use sql_invoicing;
delimiter $$
CREATE 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$$
delimiter ;
call get_unpaid_invoices_for_client(3);
-- 得到3号顾客的 COUNT(*) 和 SUM(invoice_total) （未支付过的发票数量和总金额）分别为2和286
-- 我们也可以通过输出参数（变量）来获取这两个结果值，修改过程，添加两个输出参数 invoice_count 和 invoice_total
use sql_invoicing;
drop procedure if exists get_unpaid_invoices_for_client;
delimiter $$
CREATE PROCEDURE get_unpaid_invoices_for_client(
        client_id INT,
        out invoice_count int,
        out invoice_total decimal(9,2)
)
BEGIN
    SELECT COUNT(*), SUM(invoice_total) into invoice_count, invoice_total
    FROM invoices i
    WHERE 
        i.client_id = client_id AND
        payment_total = 0;
END$$
delimiter ;
-- 调用
/* 先定义以@前缀表示用户变量，将初始值设为0。（变量（variable）简单讲就是储存单一值的对象）再调用过程，将过程结果赋值给这两个输出参数，最后再用SELECT查看。*/
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;
/* 很明显，通过输出参数获取并读取数据有些麻烦，若无充足的原因，不要多此一举。*/

-- 9. 变量 Variables 
/* 两种变量:
1. 用户或会话变量 SET @变量名 = ……
2. 本地变量 DECLARE 变量名 数据类型 [DEFAULT 默认值] */
/* 1. 用户或会话变量（User or session variable）：
上节课讲过，用 SET 语句并在变量名前加 @ 前缀来定义，将在整个用户会话期间存续，在会话结束断开MySQL链接时才被清空，
这种变量主要在调用带输出的储存过程时，作为输出参数来获取结果值。*/
/* 2.本地变量（Local variable）
在储存过程或函数中通过 DECLARE 声明并使用，在函数或储存过程执行结束时就被清空，常用来执行过程（或函数）中的计算 */
-- 案例：创造一个 get_risk_factor 过程，使用公式 risk_factor = invoices_total / invoices_count * 5
use sql_invoicing;
drop procedure if exists get_risk_factor;
delimiter $$
create 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 $$
delimiter ;

-- 10. 函数 Functions 
/* 
CREATE FUNCTION function_name( para1 data_type)
RETURNS [INTEGER] -- 定义返回值的数据类型
DETERMINISTIC / READS SQL DATA / MODIFIES SQL DATA -- 设置函数属性
BEGIN
    function_body
	RETURN value; -- 返回值。与存储过程不同，函数只能返回单一值，不能返回有行有列的结果集
END $$
*/
/* 函数属性的说明：
DETERMINISTIC 决定性的：唯一输入决定唯一输出，和数据的改动更新无关，
			比如税收是订单总额的10%，则以订单总额为输入税收为输出的函数就是决定性的，
            但这里每个顾客的 risk_factor 会随着其发票记录的增加更新而改变，所以不是DETERMINISTIC的
READS SQL DATA：函数中有读取数据的操作 SELECT 
MODIFIES SQL DATA：函数中有增删改的操作 INSERT DELETE UPDATE  */
-- 案例：在上一节的储存过程 get_risk_factor 的基础上，创建函数 get_risk_factor_for_client，计算特定顾客的 risk_factor
use sql_invoicing;
delimiter $$
create function get_risk_factor_for_client( client_id int)
returns integer -- 返回值为整数型
reads 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); -- 有些顾客没有发票记录，NULL乘除结果还是NULL，所以最后用 IFNULL 函数将这些人的 risk_factor 替换为 0
end $$
delimiter ;
-- 调用
select get_risk_factor_for_client(3);
SELECT 
    client_id,
    name,
	get_risk_factor_for_client(client_id) AS risk_factor -- 处理整列
FROM clients;
-- 删除：DROP FUNCTION IF EXISTS function_name
drop function if exists get_risk_factor_for_client;

第十章 触发和事件

/* —————————————————————————————————————————————— 第十章 触发和事件 —————————————————————————————————————————————— */
-- 1. 触发器 Triggers
/* 
触发器是在插入、更新或删除语句前后自动执行的一段SQL代码
通常我们使用触发器来保持数据的一致性

创建触发器的语法要点：命名三要素，触发条件语句和触发频率语句，主体中 OLD/NEW 的使用 */
/* 案例
在 sql_invoicing 库中，发票表中同一个发票记录可以对应付款表中的多次付款记录，发票表中的付款总额应该等于这张发票所有付款记录之和，
为了保持数据一致性，可以通过触发器让每一次付款表中新增付款记录时，发票表中相应发票的付款总额（payement_total）自动增加相应数额
 */
/* 几个关键点：
1. 命名习惯（三要素）：触发表_before/after(SQL语句执行之前或之后触发)_触发的SQL语句类型
2. 触发条件语句：BEFORE/AFTER INSERT/UPDATE/DELETE ON 触发表
3. 触发频率语句：这里 FOR EACH ROW 表明每一个受影响的行都会启动一次触发器。其它有的DBMS还支持表级别的触发器，即不管插入一行还是五行都只启动一次触发器
4. 主体：主体里可以对各种表的数据进行修改以保持数据一致性，但注意唯一不能修改的表是触发表，否则会引发无限循环（“触发器自燃”），
	主体中最关键的是使用 NEW/OLD 关键字来指代受影响的新/旧行（若INSERT用NEW，若DELETE用OLD）*/
use sql_invoicing;
DELIMITER $$
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;
END$$
DELIMITER ;
-- 测试：往 payments 里新增付款记录，发现 invoices 表对应发票的付款总额确实相应更新
INSERT INTO payments VALUES (DEFAULT, 5, 3, '2019-01-01', 10, 1)
-- 练习：创建一个和刚刚的触发器作用刚好相反的触发器，每当有付款记录被删除时，自动减少发票表中对应发票的付款总额
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美元.
DELETE FROM payments WHERE payment_id = 9;

-- 2. 查看触发器 Viewing Triggers 
-- 用以下命令来查看已存在的触发器及其各要素
SHOW TRIGGERS;
-- 如果之前创建时遵行了三要素命名习惯，这里也可以用 LIKE 关键字来筛选特定表的触发器
SHOW TRIGGERS LIKE 'payments%';

-- 3. 删除触发器 Dropping Triggers 
DROP TRIGGER IF EXISTS payments_after_insert;
-- IF EXISTS 是可选的，但一般最好加上
/*
最好将删除语句放在创建语句前，DROP IF EXISTS + CREATE，用于创建或更新数据库/视图/储存过程/触发器，
等效于 CREATE OR REPLACE）并将脚本录入源码库中，这样不仅团队都可以创建相同的数据库，还都能查看数据库的所有修改历史 */
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;
END$$
DELIMITER ;

-- 4. 使用触发器进行审核 Using Triggers for Auditing 
/* 
之前已经学习了如何用触发器来保持数据一致性，
触发器的另一个常见用途是用于审核，目的是将修改数据的操作记录在日志里 */
/* 建立一个审核表（日志表）以记录谁在什么时间做了什么修改，实现方法就是在触发器里加上创建日志记录的语句，日志记录应包含修信息两部分。*/
/* 案例
用 create-payments-table.sql 创建 payments_audit 表，记录所有对 payements 表的增删操作，
注意该表包含 client_id, date, amount 字段来记录修改的内容信息（方便之后恢复操作，如果需要的话）和 action_type, action_date 字段来记录操作信息。注意这是个简化了的 audit 表以方便理解。

具体实现方法是，重建在 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());
-- 测试：
-- 增：
INSERT INTO payments
VALUES (DEFAULT, 5, 3, '2019-01-01', 10, 1);
-- 删：
DELETE FROM payments
WHERE payment_id = 10;
-- 发现 payments_audit 表里果然多了两条记录以记录这两次增和删的操作
-- 注意： 实际运用中不会为数据库中的每张表建立一个审核表，相反，会有一个整体架构，通过一个总审核表来记录，这在之后设计数据库中会讲到。

-- 5. 事件 Events
/*
按计划执行的一段sql代码，可以执行一次，或者按某种规律执行，比如每天早上10点或每月一次
通过事件我们可以自动化数据库维护任务，比如删除过期数据、将数据从一张表复制到存档表 或者 汇总数据生成报告，所以事件十分有用。*/
-- 首先，需要打开MySQL事件调度器（event_scheduler），这是一个时刻寻找需要执行的事件的后台程序
-- 查看MySQL所有系统变量：
SHOW VARIABLES;
-- 使用 LIKE 操作符查找以event开头的系统变量；通常为了节约系统资源而默认关闭；用SET语句开启或关闭,不想用事件时可关闭以节省资源，这样就不会有一个不停寻找需要执行的事件的后台程序
-- 查看和开启/关闭事件调度器（event_scheduler）：
SHOW VARIABLES LIKE 'event%';
SET GLOBAL event_scheduler = ON; -- OFF
-- 案例：创建这样一个 yearly_delete_stale_audit_row 事件，每年删除过期的（超过一年的）日志记录
DELIMITER $$
CREATE EVENT yearly_delete_stale_audit_row
ON SCHEDULE  -- 设定执行计划
    EVERY 1 YEAR STARTS '2019-01-01' ENDS '2029-01-01' -- 可以不设置starts ends 时间范围
DO BEGIN -- 执行任务
    DELETE FROM payments_audit
    WHERE action_date < date_sub( NOW(),INTERVAL 1 YEAR); -- NOW() - INTERVAL 1 YEAR
END$$
DELIMITER ;
/*
1. 命名：用时间间隔（频率）开头，可以方便之后分类检索，时间间隔（频率）包括 【once】/hourly/daily/monthly/yearly 等等
2. 执行计划：
规律性周期性执行用 EVERY 关键字，可以是 EVERY 1 HOUR / EVERY 2 DAY 等等
若只执行一次就用 AT 关键字，如：AT '2019-05-01'
开始 STARTS 和结束 ENDS 时间都是可选的 */

-- 6. 删改查事件 Viewing, Dropping and Altering Events 
-- 查 show
SHOW EVENTS;
-- 可看到各个数据库的事件
SHOW EVENTS LIKE 'yearly%';  -- 之前命名以时间间隔开头这里才能这样筛选
-- 删 drop
DROP EVENT IF EXISTS yearly_delete_stale_audit_row;
-- 改 alter
/* ALTER EVENT 后面的语法和create event一样
如果要修改事件内容（包括执行计划和主体内容），直接把 ALTER 当 CREATE 用，直接重建语句 */
-- 暂时地启用或停用事件（用 DISABLE 和 ENABLE 关键字）
ALTER EVENT yearly_delete_stale_audit_row DISABLE;
ALTER EVENT yearly_delete_stale_audit_row ENABLE;

第十一章 事务和并发 Transactions and Concurrency 

/* —————————————————————————————————————————————— 第十一章 事务和并发 Transactions and Concurrency  —————————————————————————————————————————————— */
-- 1. 事务 Transactions 
/* 事务（trasaction）一组负责完成某个完整的工作的sql代码，
所有语句都必须执行成功，否则失败
如果一部分执行成功一部分执行失败，那成功的那一部分就会回滚rollback以保持数据的一致性。*/
/*
例子1
银行交易：你给朋友转账包含从你账户转出和往他账户转入两个步骤，两步必须同时成功，如果转出成功但转入不成功则转出的金额会返还
例子2
订单记录：之前学过向父子表插入分级（层）/耦合数据，一个订单 (order) 记录对应多个订单项目 (order_items) 记录，
如果在记录一个新订单时，订单记录录入成功但对应的订单项目记录录一半系统就崩了，那这个订单的信息就是不完整的，我们的数据库将失去数据一致性
*/
/* 事务有四大特性 ACID 特性
1）Atomicity 原子性，即整体性，不可拆分行（unbreakable），所有语句必须都执行成功事务才算完成，否则只要有语句执行失败，已执行的语句也会被复原
2）Consistency 一致性，指的是通过事务我们的数据库将永远保持一致性状态，比如不会出现没有完整订单项目的订单
3）Isolation 隔离性，指事务间是相互隔离互不影响的，尤其是需要访问相同数据时。具体而言，如果多个事务要修改相同数据，该数据会被锁定，每次只能被一个事务有权修改，其它事务必须等这个事务执行结束后才能进行
4）Durability 持久性，指的是一旦事务执行完毕，这种修改就是永久的，任何停电或系统死机都不会影响这种数据修改 */

-- 2. 创建事务 Creating Transactions 
-- 案例：创建一个事务来储存订单及其订单项目（为了简化，这个订单只有一个项目）
-- start transaction; commit;
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);
COMMIT; -- 执行，会看到最新的订单和订单项目记录
/* 当 MySQL 看到上面这样的事务语句组，会把所有这些更改写入数据库，
如果有任何一个更改失败，会自动撤销之前的修改，这种情况被称为事务被退回（回滚）（is rolled back）

为了模拟退回的情况，可以用 Ctrl + Enter 逐条执行语句，
执行一半，即录入了订单但还没录入订单项目时断开连接（模拟客户端或服务器崩溃或断网之类的情况），
重连后会发现订单和订单项目都没有录入*/
ROLLBACK; -- 回滚，之前的操作都不作数了
/* 手动回滚
多数时候是用上面的 START TRANSACTION + COMMIT 来创建事务，但当我们想先进行一下事务里语句的测试/错误检查并因此想在执行结束后手动退回时，可以将最后的 COMMIT; 换成 ROLLBACK; 这会退回事务并撤销所有的更改 */

/*autocommit
MySQL会自动将无错的SQL命令包裹为transaction并自动提交&执行，
这一过程可以用系统变量 autocommit 控制，默认开启

因为有 autocommit 的存在，当事务只有一个语句时，用不用 START TRANSACTION + COMMIT 都一样，
但要将多个语句作为一个事务时就必须要加 START TRANSACTION + COMMIT 来手动包装了 */
SHOW VARIABLES LIKE 'autocommit';

-- 3. 并发和锁定 Concurrency and Locking 
/* 并发
之前都只有一个用户访问数据，现实中常出现多个用户访问相同数据的情况，这被称为“并发”（concurrency），
当多个用户同时检索或修改的数据时，就会出现并发问题 */
-- MySQL是如何处理并发问题的？

-- 案例：假设要通过如下事务语句给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通过“上锁”来处理并发问题
当一个事务修改一行或多行时，会给这些行上锁，这些锁会阻止其他事务修改这些行，直到前一个事务完成（不管是提交还是退回）为止，
由于上述MySQL默认状态下的锁定行为，多数时候不需要担心并发问题，
但在一些特殊情况下，默认行为不足以满足你应用里的特定场景，这时你可以修改默认行为，这正是我们接下要学习的 */

十二章 数据类型 Data Types 

/* —————————————————————————————————————————————— 十二章 数据类型 Data Types  —————————————————————————————————————————————— */
/*
背景知识：关于储存单位
一个晶体管可开和关，表示0或1两个值，代表最小储存单位，叫一位（bit）
一字节（Byte）有8位，可表示2^8个值，即256个值
字节（B）、千字节（KB）、兆字节（GB）、太字节（TB）相邻两者之间的换算比率是2^10，即1024，约1000. 
*/

-- 1. 介绍
/*
知道MySQL支持的数据类型并且知道什么时候该用什么是很重要的

MySQL的数据分为以下几个大类：
1. String Types 字符串类型
2. Numeric Types 数字类型
3. Date and Time Types 日期和时间类型
4. Blog Types 存放二进制的数据类型
5. Spatial Types 存放地理数据的类型
*/

-- 2. 字符串类型 String Types 
/*
1. 最常用的两个字符串类型
CHAR() 固定长度的字符串，如州（'CA', 'NY', ……）就是 CHAR(2)
VARCHAR() 可变字符串
用 VARCHAR(50) 来记录用户名和密码这样的短文本 以及 用 VARCHAR(255) 来记录像地址这样较长一些的文本，保持这样的习惯能够简化数据库设计
VARCHAR 最多能储存 64KB, 也就是最多约 65k 个字符（如果都是英文即每个字母只占一字节的话），超出部分会被截断
字符串类型也可以用来储存邮编，电话号码这样的特殊的数字型数据，因为它们不会用来做数学运算而且常常包含‘-’或括号等分隔符号

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

TINYTEXT 最大储存 255 Bytes
TEXT 最大储存 64KB，最大储存长度和 VARCHAR 一样，但最好用 VARCHAR，因为 VARCHAR 可以使用索引（之后会讲，索引可以提高查询速度）

所有这些字符串类型都支持国际字符，其中：
英文字符占1个字节
欧洲和中东语言字符占2个字节
像中日这样的亚洲语言的字符占3个字节
所以，如果一列数据的类型为 CHAR(10)，MySQL会预留30字节给那一列的值 */

-- 3. 整数类型 Integer Types
/*
我们用整数类型来保存没有小数的数字，MySQL里共有5种常用的整数类型，它们的区别在于占用的空间和能记录的数字范围

整数类型	占用储存	记录的数字范围
TINYINT		1B			[-128,127]
SMALLINT	2B			[-32K,32K]
MEDIUMINT	3B			[-8M,8M]
INT			4B			[-2B,2B]
BIGINT		8B			[-9Z,9Z]

属性1. 不带符号 UNSIGNED
这些整数可以选择不带符号，加上 UNSIGNED 则只储存非负数
如最常用的 UNSIGNED TINYINT，占用空间和 TINYINT 一样也是1B，但表示的数字范围不是 [-128-127] 而是 [0-255]，适合储存像年龄这样的数据，可以防止意外输入负数

属性2. 填零 ZEROFILL
整数类型的另一个属性是填零（Zerofill），主要用于当你需要给数字前面添加零让它们位数保持一致时
我们用括号表示显示位数，如 INT(4) => 0001，注意这只影响MySQL如何显示数字而不影响如何保存数字

谷歌 mysql integer types 即可查阅

注意：如果试图存入超出范围的数字，MySQL会抛出异常 'The value is out of range'

使用能满足你需求的最小整数类型，如储存人的年龄用 UNSIGNED TINYINT 就足够了
数据需要在磁盘和内存间传输，虽然不同类型间只有几个字节的差异，但数据量大了以后对空间和查询效率的影响就很大了，
所以在数据量较大时，有意识地分配每一字节，保持数据最小化是很有必要的。
*/

-- 4. 定点数类型和浮点数类型 Fixedpoint and Floatingpoint Types 
/*
（1）Fixedpoint Types 定点数类型
DECIMAL(p, s) 两个参数分别指定最大的有效数字位数和小数点后小数位数（小数位数固定）
如：DECIMAL(9, 2) => 1234567.89 总共最多9位，小数点后两位，整数部分最多7位

DECIMAL 还有几个别名：DEC / NUMERIC / FIXED，最好就使用 DECIMAL 以保持一致性

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

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

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

-- 5. 布尔类型 Boolean Types 
/*
有时我们需要储存 是/否 型数据，如 “这个博客是否发布了？”，这里我们就要用到布尔值，来表示真或假
MySQL里有个数据类型叫 BOOL / BOOLEAN 
eg:
UPDATE posts 
SET is_published = TRUE / FALSE
或
SET is_published = 1 / 0 
*/

-- 6. 枚举和集合类型 Enum and Set Types 
/*
有时我们希望某个字段从固定的一系列值中取值，我们就可以用到 ENUM() 和 SET() 类型，前者是取一个值，后者是取多个值

ENUM()：从固定一系列值中取一个值
案例
例如，我们希望 sql_store.products（产品表）里多一个size（尺码）字段，取值为 small/medium/large 中的一个，
可以打开产品表的设计模式，添加size列，数据类型设置为 ENUM('small','medium','large')
则产品表会增加一个尺码列，可将其中的值设为small/medium/large(大小写无所谓)，但若设为其他值会报错

SET()：SET和ENUM类似，区别是，SET是从固定一系列值中取多个值而非一个值

注意
讲解 ENUM 和 SET 只是为了眼熟，最好不要用这两个数据类型，问题很多：
1. 修改可选的值（如想增加一个'extra large'）会重建整个表，耗费资源
2. 想查询可选值的列表 或者 想用可选值当作一个下拉列表都会比较麻烦
3. 难以在其它表里复用，其它地方要用只有重建相同的列，之后想修改就要多处修改，又会很麻烦

最佳实践
像这种某个字段是从固定的一系列值中取值的情况，不应该使用 ENUM 和 SET 而应该用这一系列的可选值另外建一个 “查询表” (lookup table)
例如，上面案例中，应该为尺码另外专门建一个 size表（可选尺码表）

又如，sql_invoicing 里为支付方式另外专门建了一个 payment_methods 可选支付方式表
这样就解决了上面的所有问题，既方便查询可选值的列表，也方便作为下拉选项，也方便复用和更改
*/

-- 7. 日期和时间类型 Date and Time Types 
/*
MySQL 有4种储存日期事件的类型：
1. DATE 有日期没时间
2. TIME 有时间没日期
3. DATETIME 包含日期和时间
4. TIMESTAMP 时间戳，常用来记录一行数据的的插入或最后更新时间
5. YEAR 专门储存四位的年份
最后两个的区别是：
TIMESTAMP 占4B，最晚记录2038年，被称为“2038年问题”
DATETIME 占8B，如果要储存超过2038年的日期时间，就要用 DATETIME
*/

-- 8. 二进制类型 Blob Types (1:17)
/*
我们用 Blob 类型来储存大的二进制数据，包括PDF，图像，视频等等几乎所有的二进制的文件
具体来说，MySQL里共有4种 Blob 类型，它们的区别在于可储存的最大文件大小：

占用储存		最大可储存
TINYBOLB		255B
BLOB			65KB
MEDIUM BLOB		16MB
LONG BLOB		4GB

注意
通常应该将二进制文件存放在数据库之外，关系型数据库是设计来专门处理结构化关系型数据而非二进制文件的
如果将文件储存在数据库内，会有如下问题：
1）数据库的大小将迅速增长
2）备份会很慢
3）性能问题，因为从数据库中读取图片永远比直接从文件系统读取慢
4）需要额外的读写图像的代码
所以，尽量别用数据库来存文件，除非这样做确实有必要而且上面这些问题已经被考虑到了
*/

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

{
    "key1": value1,
    "key2": value2,
    ……
}
JSON 用大括号{}表示一个对象，里面有多对键值对
键 key 必须用引号包裹（而且似乎必须是双引号，不能用单引号）
值 value 可以是数值，布林值，数组，文本， 甚至另一个对象（形成嵌套 JSON 对象）
*/
/*
案例
用 sql_store 数据库，在 products 商品表里，在设计模式下新增一列 properties，设定为 JSON 类型，
-- 注意在Workbench里，要将 Eidt-Preferences-Modeling-MySQL-Default Target MySQL Version 设定为 8.0 以上，不然设定 JSON 类型会报错 
这里的 properties 记录每件产品附加的独特属性，注意这里每件产品的独特属性是不一样的，
如衣服是颜色和尺码，而电视机是的重量和尺寸，把所有可能的属性都作为不同的列添加进表是很糟糕的设计，因为每个商品都只能用到所有这些属性的一部分（一个子集），
相反，通过增加一列 JSON 类型的 properties 列，我们可以利用 JSON 里的键值对很方便的储存每个商品独特的属性

现在我们已经有了一个 JSON 类型的列，接下来从 增 删 改 查 各角度来看看如何操作使用 JSON 类型的列，
注意这里的 增删查改 主要针对的是 properties 列里的特定键值对，即如何 增删查改 某些特定的具体属性
*/
-- 增
-- 给1号商品增加一系列属性，有两种方法
/*法1：
用单引号包裹（注意不能是双引号），里面用 JSON 的标准格式；双引号包裹键 key（注意不能是单引号）
值 value 可以是数、数组、甚至另一个用 {} 包裹的JSON对象
不同键值对用逗号隔开 */
use sql_store;
select * from products;
alter table products add properties json; -- 新增一列
update products set properties = '{"dimensions":[1,2,3], "weight":10, "manufacturer":{"name":"sony", "site":"New York"}}' where product_id =1;
-- 法2：使用MySQL内置函数
update products set properties = json_object("dimensions",json_array(1,2,3),'weight',10, 'manufacturer',json_object('name','sony', 'site','New York')) where product_id =2;

-- 查
-- 现在来讲如何查询 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;
select product_id, json_extract(properties,"$.dimensions[0]") as dimensions from products where product_id =1;
select product_id, json_extract(properties,"$.manufacturer.site") as site from products where product_id =2;
/* 法2
更简便的方法，使用列路径操作符 -> 和 ->>
用法是：JSON对象 -> '路径' */
select product_id, properties ->"$.dimensions[2]" from products where product_id = 2;
select product_id, properties ->>"$.manufacturer.site" as site from products where product_id = 2;

/*通过路径操作符来获取 JSON 对象的特定属性不仅可以用在 SELECT 选择语句中，也可以用在 WHERE 筛选语句中，如：
筛选出制造商名称为 sony 的产品： */
select product_id, properties ->>"$.manufacturer.name" as name from products where properties ->>"$.manufacturer.name" = "sony";

-- 改 JSON_SET(object, "$.key1", value1, "$.key2", value2)
-- 如果我们是要重新设置整个 JSON 对象就用前面 增 里讲到的 JSON_OBJECT() 函数，但如果是想修改已有 JSON 对象里的一些属性，就要用 JSON_SET() 函数
use sql_store;
update products set properties = json_set(properties,"$.weight",30,"$.age",20) where product_id =1;
select * from products;
-- 注意 JSON_SET() 是选择已有的 JSON 对象并修改部分属性然后返回修改后新的 JSON 对象，所以其第1参数是要修改的 JSON 对象，并且可以用

-- 删 JSON_REMOVE(object, "$.key1", "$.key2")
use sql_store;
update products set properties = json_remove(properties,"$.weight") where product_id =1;
select * from products;

/* 小结
增：object = JSON_OBJECT("key1",value1, "key2",JSON_ARRAY(1,2,3))  或 object = '{"key":value}'
查：JSON_EXTRACT(object, "$.key") 或 ->'$.key' 或 ->>"$.key1.key"
改：JSON_SET(object, "$.key1", value1, "$.key2", value2)
删：JSON_REMOVE(object, "$.key1", "$.key2")
*/