更详细的说明：https://zhuanlan.zhihu.com/p/222865842
1. 查询中匹配特殊字符串
SELECT * FROM customer WHERE last_name LIKE 'b____y'
% 代表任何数量的字符
_ 代表单个字符
SELECT * FROM customer WHERE last_name REGEXP '^field|mac|rose'
REGEXP 可以用任何正则表达式
^ 代表开头
$ 代表结尾
| 代表或
[abcd] 代表abcd其中之一
[a-f] 代表a到f的字母中的一个
2. 按照某个字段排序
SELECT first_name, last_name FROM customer ORDER BY state DESC, first_name DESC
这个排序是有顺序的，先按第一个字段排，如果相同的话，再按照第二个字段排。
3. 内连接
SELECT o.customer_id, first_name FROM orders o JOIN customers c ON o.customer_id = c.customer_id
隐式连接语法
就是用FROM WHERE取代FROM JOIN ON, 尽量别用，因为若忘记WHERE条件筛选语句(这种情况就成了交叉连接 CROSS JOIN)，不会报错但会得到交叉合并（cross join）结果
SELECT o.customer_id, first_name FROM orders o，customers c WHERE o.customer_id = c.customer_id
4. 外连接
(INNER) JOIN结果只包含两表的交集，另外注意“广播（broadcast）”效应LEFT/RIGHT (OUTER) JOIN结果里除了交集，还包含只出现在左/右表中的记录- 虽然可以调换顺序并用 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
5. USING子句
当作为合并条件（join condition）的列在两个表中有相同的列名时，可用 USING (……, ……) 取代 ON …… AND …… 予以简化，内/外链接均可如此简化。一定注意 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
复合主键表间复合连接条件的合并也可用 USING，中间逗号隔开就行：
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对复合主键的简化效果更加明显
6. 自然连接
NATURAL JOIN 就是让MySQL自动检索同名列作为合并条件。
注意
最好别用，因为不确定合并条件是否找对了，有时会造成无法预料的问题，编程时保持对结果的控制是非常重要的
USE sql_store; SELECT o.order_id, c.first_name FROM orders o NATURAL JOIN customers c
7. 交叉连接
得到名字和产品的所有组合，因此不需要合并条件。 实际运用如：要得到尺寸和颜色的全部组合
得到顾客和产品的全部组合（毫无意义，纯粹为了展示交叉连接）
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子句（即合并条件）的情况，也就是把 CROSS JOIN 改为逗号，即 FROM A CROSS JOIN B 等效于 FROM A, B，Mosh更推荐显式语法，因为更清晰
USE sql_store; SELECT c.first_name, p.name FROM customers c, products p ORDER BY c.first_name
8. 联合
FROM …… JOIN …… 可对多张表进行横向列合并，而 …… UNION …… 可用来按行纵向合并多个查询结果，这些查询结果可能来自相同或不同的表
- 同一张表可通过UNION添加新的分类字段，即先通过分类查询并添加新的分类字段再UNION合并为带分类字段的新表。
- 不同表通过UNION合并的情况如：将一张18年的订单表和19年的订单表纵向合并起来在一张表里展示
注意
- 合并的查询结果必须列数（字段要一样多）相等，否则会报错
- 合并表里的列名由排在 UNION 前面的决定
给订单表增加一个新字段——status，用以区分今年的订单和今年以前的订单
USE sql_store; SELECT order_id, order_date, 'Active' AS status 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';
9. 插入单行
INSERT INTO 目标表 （目标列，可选，逗号隔开） VALUES (目标值，逗号隔开)
若不指明列名，则插入的值必须按所有字段的顺序完整插入
USE sql_store; INSERT INTO customers -- 目标表 VALUES ( DEFAULT, 'Michael', 'Jackson', '1958-08-29', -- DEFAULT/NULL/'1958-08-29' DEFAULT, '5225 Figueroa Mountain Rd', 'Los Olivos', 'CA', DEFAULT );
指明列名，可跳过取默认值的列且可更改顺序，一般用这种，更清晰
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', )
10. 插入多行
VALUES …… 里一行内数据用括号内逗号隔开，而多行数据用括号间逗号隔开
案例 ： 插入多条运货商信息
USE sql_store INSERT INTO shippers (name) VALUES ('shipper1'), ('shipper2'), ('shipper3');
注意 ： 对于AI (Auto Incremental 自动递增) 的id字段，MySQL会记住删除的/用过的id，并在此基础上递增
11. 在多个表里插入多行
案例
新增一个订单（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 INSERT INTO order_items -- 全是必须字段，就不用指定了 VALUES (last_insert_id(), 1, 2, 2.5), (last_insert_id(), 2, 5, 1.5)
- 关键：在插入子表记录时，需要用内建函数
LAST_INSERT_ID()获取相关父表记录的自增ID（这个例子中就是 order_id) - 内建函数：MySQL里有很多可用的内置函数，也就是可复用的代码块，各有不同的功能，注意函数名的单词之间用下划线连接
LAST_INSERT_ID()：获取最新的成功的INSERT 语句中的自增id，在这个例子中就是父表里新增的 order_id.
12. 创建表的副本
小结
DROP TABLE 要删的表名、CREATE TABLE 新表名 AS 子查询
TRUCATE '要清空的表名'、INSERT INTO 表名 子查询
子查询里当然也可以用WHERE语句进行筛选
示例：运用 CREAT TABLE 新表名 AS 子查询 快速创建表 orders 的副本表 orders_archived
USE sql_store; CREATE TABLE orders_archived AS SELECT * FROM orders -- 子查询
SELECT * FROM orders 选择了 oders 中所有数据，作为AS的内容，是一个子查询
- 子查询： 任何一个充当另一个SQL语句的一部分的
SELECT……查询语句都是子查询，子查询是一个很有用的技巧。
注意
创建已有的表或删除不存在的表的话都会报错，所以建表和删表语句都最好加上条件语句（后面会讲）
INSERT INTO 表名 子查询 很常用，子查询替代原先插入语句中 VALUES(……,……),(……,……),…… 的部分
TRUNCATE 'orders_archived'; -- 也可右键该表点击 truncate /*新的 8.0版 MySQL 的语法好像变为了 TRUNCATE TABLE orders_archived？ 那样就与 DROP TABLE orders_archived 一致了*/ INSERT INTO orders_archived -- 不用指明列名，会直接用子查询表里的列名 SELECT * FROM orders -- 子查询，替代原先插入语句中VALUES(……,……),(……,……),…… 的部分 WHERE order_date < '2019-01-01'
13. 更新单行
用 UPDATE …… 语句 来修改表中的一条或多条记录，具体语法结构：
UPDATE 表 SET 要修改的字段 = 具体值/NULL/DEFAULT/列间数学表达式 （修改多个字段用逗号分隔） WHERE 行筛选
14. 删除行
语法结构：
DELETE FROM 表 WHERE 行筛选条件 （当然也可用子查询） （若省略 WHERE 条件语句会删除表中所有记录（和 TRUNCATE 等效？））
15. 聚合函数 Aggregate Functions
聚合函数：输入一系列值并聚合为一个结果的函数
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' -- 想只统计下半年的结果
16. GROUP BY子句
按一列或多列分组，注意语句的位置。
在发票记录表中按不同顾客分组统计下半年总销售额并降序排列
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，分组语句在排序语句之前，调换顺序会报错
按多个字段分组
在 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
17. HAVING 子句
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(*/invoice_total/invoice_date) 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
所有出现在SELECT子句里面的字段，除了聚合函数的字段，其他都要出现在GROUP BY 子句，否则会随意填一个值到对应的字段上
学第六章第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
18.
