第一部分：基础——增删查改
【第一章】做好准备 Getting Started (时长25分钟)
【第二章】在单一表格中检索数据 Retrieving Data From a Single Table (时长53分钟)
【第三章】在多张表格中检索数据 Retrieving Data From Multiple Tables (时长1小时2分)
【第四章】插入、更新和删除数据 Inserting, Updating, and Deleting Data (时长42分钟)

第二部分：基础进阶——汇总、复杂查询、内置函数
【第五章】汇总数据 Summarizing Data (时长33分钟)
【第六章】编写复杂查询 Writing Complex Query (时长45分钟)
【第七章】MySQL的基本函数 Essential MySQL Functions (时长33分钟)

第三部分：提高效率——视图、存储过程、函数
【第八章】视图 Views (时长18分钟)
【第九章】存储过程 Stored Procedures (时长48分钟)

第四部分：高阶主题——触发器、事件、事务、并发
【第十章】触发器和事件 Triggers and Events (时长22分钟)
【十一章】事务和并发 Transactions and Concurrency (时长49分钟)

第五部分：脱颖而出——数据类型、设计数据库、索引、保护
【十二章】数据类型 Data Types (时长35分钟)
【十三章】设计数据库 Designing Databases (时长1时30分)
【十四章】高效的索引 Indexing for High Performance (时长58分钟)
【十五章】保护数据库 Securing Databases (时长20分钟)

【第一章】做好准备

什么是SQL

有关系型和非关系型两类数据库，在更流行的关系型数据库中，我们把数据存储在通过某些关系相互关联的数据表中，每张表储存特定的一类数据，这正是关系型数据库名称的由来。（如：顾客表通过顾客id与订单表相联系，订单表又通过商品id与商品表相联系）
SQL（Structured Query Language，结构化查询语言）是专门用来处理（包括查询和修改）关系型数据库的标准语言
不同关系型数据库管理系统语法（MySQL、SQL Server、Oracle）略有不同，但都是基于标准SQL，本课使用最流行的开源关系型数据库管理系统，MySQL

【第二章】在单一表格中检索数据

选择子句

SELECT 是列/字段选择语句，可选择列，列间数学表达式，特定值或文本，可用AS关键字设置列别名（AS可省略），注意 DISTINCT 关键字的使用。
SQL会完全无视大小写（绝大数情况下的大小写）、多余的空格（超过一个的空格）、缩进和换行，SQL语句间完全由分号 ; 分割
单价涨价10%作为新单价：

SELECT
name,
unit_price,
unit_price * 1.1 ‘new price’
FROM products
如上面这个例子所示，取别名时，AS 可省，空格后跟别名就行，可看作是SQL会将将列变量及其数学运算之后的第一个空格识别为AS

WHERE子句

WHERE 是行筛选条件，实际是一行一行/一条条记录依次验证是否符合条件，进行筛选
实例

USE sql_store;

SELECT *
FROM customers
WHERE points > 3000
/WHERE state != ‘va’ – ‘VA’/'va’一样

比较运算符中注意等于是一个等号而不是两个等号
也可对日期或文本进行比较运算，注意SQL里日期的标准写法及其需要用引号包裹这一点
WHERE birth_date > ‘1990-01-01’

AND, OR, NOT运算符

USE sql_store;

SELECT *
FROM customers
WHERE birth_date > ‘1990-01-01’ AND points > 1000
/WHERE birth_date > ‘1990-01-01’ OR
points > 1000 AND state = ‘VA’

AND优先级高于OR，但最好加括号，更清晰
NOT的用法：

WHERE NOT (birth_date > ‘1990-01-01’ OR points > 1000)

IN运算符

用IN运算符将某一属性与多个值（一系列值）进行比较
实质是多重相等比较运算条件的简化

案例

选出’va’、‘fl’、'ga’三个州的顾客

USE sql_store;

SELECT * FROM customers
WHERE state = ‘va’ OR state = ‘fl’ OR state = ‘ga’

用 IN 操作符简化该条件
WHERE state IN (‘va’, ‘fl’, ‘ga’)
可加NOT
WHERE state NOT IN (‘va’, ‘fl’, ‘ga’)
库存量刚好为49、38或72的产品：

USE sql_store;

select * from products
where quantity_in_stock in (49, 38, 72)

BETWEEN运算符

用于表达范围型条件

用AND而非括号
闭区间，包含两端点
也可用于日期，毕竟日期本质也是数值，日期也有大小（早晚），可比较运算

选出积分在1k到3k的顾客：

USE sql_store;

select * from customers
where points >= 1000 and points <= 3000
等效简化为：

WHERE points BETWEEN 1000 AND 3000
注意两端都是包含的不能写作BETWEEN (1000, 3000)！别和IN的写法搞混

选出90后的顾客：

SELECT * FROM customers
WHERE birth_date BETWEEN ‘1990-01-01’ AND ‘2000-01-01’

LIKE运算符

模糊查找，查找具有某种模式的字符串的记录/行

过时用法（但有时还是比较好用，之后发现好像用的还是比较多的），下节课的正则表达式更灵活更强大
注意和正则表达式一样都是用引号包裹表示字符串
USE sql_store;
SELECT * FROM customers
WHERE last_name like ‘brush%’ / ‘b____y’
引号内描述想要的字符串模式，注意SQL（几乎）任何情况都是不区分大小写的

两种通配符：

% 任何个数（包括0个）的字符（用的更多）
_ 单个字符

分别选择满足如下条件的顾客：

地址包含 ‘TRAIL’ 或 ‘AVENUE’
电话号码以 9 结束

USE sql_store;

select *
from customers
where address like ‘%Trail%’ or
address like ‘%avenue%’
LIKE 执行优先级在逻辑运算符之后，毕竟 IN BETWEEN LIKE 本质可看作是比较运算符的简化，应该和比较运算同级，数学→比较→逻辑，始终记住这个顺序，上面这个如果用正则表达式会简单得多

where phone like ‘%9’
/where phone not like ‘%9’
LIKE的判断结果也是个TRUE/FASLE的问题，任何逻辑值/布林值都可前置NOT来取反

REGEXP运算符

正则表达式，在搜索字符串方面更为强大，可搜索更复杂的模板

实例

USE sql_store;

select * from customers
where last_name like ‘%field%’
等效于：

where last_name regexp ‘field’
regexp 是 regular expression（正则表达式）的缩写

正则表达式可以组合来表达更复杂的字符串模式

where last_name regexp ‘^mac|field$|rose’
where last_name regexp ‘[gi]e|e[fmq]’ – 查找含ge/ie或ef/em/eq的
where last_name regexp ‘[a-h]e|e[c-j]’

正则表达式总结：

符号意义
^ 开头
$ 结尾
[abc] 含abc
[a-c] 含a到c
| logical or
（正则表达式用法还有更多，自己去查）

练习

分别选择满足如下条件的顾客：

first names 是 ELKA 或 AMBUR
last names 以 EY 或 ON 结束
last names 以 MY 开头或包含 SE
last names 包含 BR 或 BU

select *
from customers
where first_name regexp ‘elka|ambur’
/where last_name regexp ‘ey ∣ o n |on ∣on’
/where last_name regexp ‘^my|se’
/where last_name regexp ‘b[ru]’/‘br|bu’

IS NULL运算符

找出空值，找出有某些属性缺失的记录

找出电话号码缺失的顾客，也许发个邮件提醒他们之类

USE sql_store;

select * from customers
where phone is null/is not null
注意是 IS NULL 和 IS NOT NULL 这里 NOT 不是前置于布林值，而是更符合英语语法地放在了be动词后

找出还没发货的订单（在线商城管理员的常见查询需求）

USE sql_store;

select * from orders
where shipper_id is null

回顾：

3~9 节全在讲WHERE子句中条件的具体写法 :

第3节：比较运算 > < = >= <= !=
第4节：逻辑运算 AND、OR、NOT
5~9节：特殊的比较运算（是否符合某种条件）：IN 和 BETWEEN、LIKE 和 REGEXP、IS NULL
所以总的来说WHERE条件就是

数学运算 → 比较运算（包括特殊的比较运算）→ 逻辑运算
逻辑层次和执行优先级也是按照这三个的顺序来的。

ORDER BY子句

排序语句，和 SELECT …… 很像：

可多列
可以是列间的数学表达式
可包括任何列，包括没选择的列（MySQL特性，其它DBMS可能报错），
可以是之前定义好的别名列（MySQL特性，甚至可以是用一个常数设置的列别名）
任何一个排序依据列后面都可选加 DESC
注意

最好别用 ORDER BY 1, 2（表示以 SELECT …… 选中列中的第1、2列为排序依据）这种隐性依据，因为SELECT选择的列一变就容易出错，还是显性地写出列名作为排序依据比较好

注：workbench 中扳手图标可打开表格的设计模式，查看或修改表中各列（属性），可以看到谁是主键。省略排序语句的话会默认按主键排序

实例：

USE sql_store;

select name, unit_price * 1.1 + 10 as new_price
from products
order by new_price desc, product_id
– 这两个分别是别名列和未选择列，都用到了 MySQL 特性

订单2的商品按总价降序排列：

法1. 可以以总价的数学表达式为排序依据

select * from order_items
where order_id = 2
order by quantity * unit_price desc
– 列间数学表达式
法2. 或先定义总价别名，在以别名为排序依据

select *, quantity * unit_price as total_price
from order_items
where order_id = 2
order by total_price desc
– 列别名

LIMIT子句

限制返回结果的记录数量，“前N个” 或 “跳过M个后的前N个”

实例

USE sql_store;

select * from customers
limit 3 / 300 / 6, 3
6, 3 表示跳过前6个，取第7~9个，6是偏移量，
如：网页分页每3条记录显示一页第3页应该显示的记录就是 limit 6, 3

找出积分排名前三的死忠粉

USE sql_store;

select *
from customers
order by points desc
limit 3

回顾

SELECT 语句完结了，里面的子句顺序固定要记牢，顺序乱会报错
select from where + order by limit
纵选列，确定表，横选行（各种条件写法和组合要清楚熟悉），最后再进行排序和限制

【第三章】在多张表格中检索数据

内连接

各表分开存放是为了减少重复信息和方便修改，需要时可以根据相互之间的关系连接成相应的合并详情表以满足相应的查询。FROM JOIN ON 语句就是告诉sql：将哪几张表以什么基础连接/合并起来。
这种有多表合并的查询语句可分两部分从后往前看：

后面的 from 表A join 表B on AB的关系，就是以某些相关联的列为依据（关系型数据库就是这么来的）进行多表合并得到所需的详情表
前面的 select 就是在合并详情表中找到所需的列

关于表别名 ：之前在SELECT中给选定的列加别名主要是为了得到更有意义的列名，这里在 FROM JOIN 中给表加别名主要是为了简化

USE sql_store;

select 
    order_id, 
    o.customer_id, 
    first_name, 
    last_name
from orders as o	-- 这里as也是可省略的
(inner) join customers c 
    on o.customer_id = c.customer_id	-- join默认是inner join

因为别名需要在 WHERE JOIN 语句中确定等原因，最好先SELECT * FROM 选择全部，等写好了 FROM JOIN ON 等后面的语句，即确定了选哪些表以及怎么链接它们并取好了表别名后，再回头去 SELECT 里细化明确需要的列。

跨数据库连接（合并）

有时需要选取不同库的表的列，其他都一样，就只是WHERE JOIN里对于非现在正在用的库的表要加上库名前缀而已。依然可用别名来简化
可见只有非当前使用的库才要加库前缀

use sql_store;

select * from order_items oi
join sql_inventory.products p
    on oi.product_id = p.product_id

自连接

一个表和它自己合并。如下面的例子，员工的上级也是员工，所以也在员工表里，所以想得到的有员工和他的上级信息的合并表，就要员工表自己和自己合并，用两个不同的表别名即可实现。这个例子中只有两级，但也可用类似的方法构建多层级的组织结构。
自合并必然每列都要加表前缀，因为每列都同时在两张表中出现。另外，两个 first_name 列有歧义，注意将最后一列改名为 manager 使得结果表更易于理解

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

多表连接

FROM 一个核心表A，用多个 JOIN …… ON …… 分别通过不同的链接关系链接不同的表B、C、D……，通常是让表B、C、D……为表A提供更详细的信息从而合并为一张详情合并版A表。将得到一个合并了BCD……等表详细信息的详情合并版A表。真实工作场景中有时甚至要合并十多张表

FROM  A 
    JOIN B ON AB的关系 
    JOIN C ON AC的关系 
    JOIN D ON AD的关系

订单表同时链接顾客表和订单状态表，合并为有顾客和状态信息的详细订单表

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
JOIN order_statuses os
    ON o.status = os.order_status_id

同理，支付记录表链接顾客表和支付方式表形成支付记录详情表

USE sql_invoicing;

SELECT 
    p.invoice_id,
    p.date,
    p.amount,
    c.name,
    pm.name AS payment_method
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

复合连接条件

像订单项目（order_items）这种表，订单id和产品id合在一起才能唯一表示一条记录，这叫复合主键，设计模式下也可以看到两个字段都有PK标识，订单项目备注表（order_item_notes）也是这两个复合主键，因此他们两合并时要用复合条件：FROM 表1 JOIN 表2 ON 条件1 【AND】条件2
将订单项目表和订单项目备注表合并

USE sql_store;

SELECT * 
FROM order_items oi
JOIN order_item_notes oin
    ON oi.order_Id = oin.order_Id
    AND oi.product_id = oin.product_id

隐式连接语法

就是用FROM WHERE取代FROM JOIN ON
尽量别用，因为若忘记WHERE条件筛选语句，不会报错但会得到交叉合并（cross join）结果：即10条order会分别与10个customer结合，得到100条记录。最好使用显性合并语法，因为会强制要求你写合并条件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

注意 FROM 子句里的逗号，就像 SELECT 多条列用逗号隔开一样，FROM 多个表也用逗号隔开，此时若忘记WHERE条件筛选语句则得到这几张表的交叉合并结果
这里也可以看得出来，ON/USING 和 WHERE 以及后面会学的 HAVING 的作用是类似的，本质上都是对行进行筛选的条件语句，只不过使用的位置不一样而已

外连接

(INNER) JOIN 结果只包含两表的交集，另外注意“广播（broadcast）”效应
LEFT/RIGHT (OUTER) JOIN 结果里除了交集，还包含只出现在左/右表中的记录
合并顾客表和订单表，用 INNER JOIN：

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

这样是INNER JOIN，只展示有订单的顾客（及其订单），也就是两张表的交集，但注意这里因为一个顾客可能有多个订单，所以INNER JOIN以后顾客信息其实是是广播了的，即一条顾客信息被多条订单记录共用，当然这叫广播（broadcast）效应，是另一个问题，这里关注的重点是 INNER JOIN 的结果确实是两表的交集，是那些同时有顾客信息和订单信息的记录。
若要展示全部顾客（及其订单，如果有的话），要改用LEFT (OUTER) JOIN，结果相较于 INNER JOIN 多了没有订单的那些顾客，即只有顾客信息没有订单信息的记录
当然，也可以调换左右表的顺序（即调换FROM和JOIN的对象）再 RIGHT JOIN，即：

FROM orders o
    RIGHT [OUTER] JOIN customers c
    -- 中括号 [] 表示是可选项、可省略 
    ON o.customer_id = c.customer_id

若要展示全部订单（及其顾客），就应该是 orders RIGHT JOIN customers，结果相较于 INNER JOIN 多了没有顾客的那些订单，即只有订单信息没有顾客信息的记录。（注：因为这里所有订单都有顾客，所以这里 RIGHT JOIN 结果和 INNER JOIN 一样）
展示各产品在订单项目中出现的记录和销量，也要包括没有订单的产品

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

多表外连接

与内连接类似，我们可以对多个表（3个及以上）进行外连接，最好只用 JOIN 和 LEFT JOIN
查询顾客、订单和发货商记录，要包括所有顾客（包括无订单的顾客），也要包括所有订单（包括未发出的）

USE sql_store;

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

最佳实践：虽然可以调换顺序并用 RIGHT JOIN，但作为最佳实践，最好调整顺序并统一只用 [INNER] JOIN 和 LEFT [OUTER] JOIN（总是左表全包含），这样，当要合并的表比较多时才方便书写和理解而不易混乱
查询订单 + 顾客 + 发货商 + 订单状态，包括所有的订单（包括未发货的），其实就只是前两个优先级变了一下，是要看全部订单而非全部顾客了

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，否者会筛掉没发货的订单

自我外部连接

就用前面那个员工表的例子来说，就是用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

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，中间逗号隔开就行
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)

sql_invoicing库里，将payments、clients、payment_methods三张表合并起来，以知道什么日期哪个顾客用什么方式付了多少钱

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

列名不同就必须用 ON …… 了
实际中同一个字段在不同表列名不同的情况也很常见（如上面的 payment_method 和payment_method_id），不能想当然的用USING

自然连接

NATURAL JOIN 就是让MySQL自动检索同名列作为合并条件。
最好别用，因为不确定合并条件是否找对了，有时会造成无法预料的问题，编程时保持对结果的控制是非常重要的

USE sql_store;

SELECT 
    o.order_id,
    c.first_name
FROM orders o
NATURAL JOIN customers c

交叉连接

得到名字和产品的所有组合，因此不需要合并条件。实际运用如：要得到尺寸和颜色的全部组合
得到顾客和产品的全部组合（毫无意义，纯粹为了展示交叉连接）

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

交叉合并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

联合

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';

合并不同表的例子——在同一列里显示所有顾客名以及所有商品名

USE sql_store;

    SELECT first_name AS name_of_all
    -- 新列名由排UNION前面的决定
    FROM customers

UNION

    SELECT name
    FROM products

给顾客按积分大小分类，添加新字段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

可以看出ORDER BY的优先级在UNION之后，应该是排序和限制语句的执行优先级比较靠后，不知能否用括号调整执行顺序让这个ORDER BY只作用于最后一个子查询？（估计实际中很少有这种需求，一般都是最后统一排序）。另外，这里如果没有 ORDER BY 的话就会按3个 query 的先后来排序。
总结 :
感觉本质上可以将查询语句的任何一步和任何一个层次，包括：

横纵筛选 SELECT ……WHERE ……
选表 FROM ……
横纵连接 …… JOIN ………… UNION ……
排序、限制ORDER BY ……LIMIT ……

都看作暂时生成了一张新表（虚拟表），将后续步骤都看作是在对这些新表进行进一步的操作，这样，层次步骤就能理清，就好理解了，也才真的能从本质上掌握并灵活运用

【第四章】插入、更新和删除数据

1. 列属性

插入单行

DEFAULT用在AI属性的主键列中自动递增加入；DEFAULT用在有default值的列中自动填充

INSERT INTO 目标表 （目标列，可选，逗号隔开）
VALUES (目标值，逗号隔开)

在顾客表里插入一个新顾客的信息
法1. 若不指明列名，则插入的值必须按所有字段的顺序完整插入

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
    );

法2. 指明列名，可跳过取默认值的列且可更改顺序，一般用这种，更清晰

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',  
    )

插入多行

VALUES …… 里一行内数据用括号内逗号隔开，而多行数据用括号间逗号隔开
插入多条运货商信息

USE sql_store

INSERT INTO shippers (name)
VALUES ('shipper1'),
       ('shipper2'),
       ('shipper3');

插入多条产品信息

USE sql_store;

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 ('product1', 1, 10),
       ('product2', 2, 20),
       ('product3', 3, 30)

对于AI (Auto Incremental 自动递增) 的id字段，MySQL会记住删除的/用过的id，并在此基础上递增

插入分级行

订单表（orders表）里的一条记录对应订单项目表（order_items表）里的多条记录，一对多，是相互关联的父子表。通过添加一条订单记录和对应的多条订单项目记录，学习如何向父子表插入分级（层）/耦合数据（insert hierarchical data）：
关键：在插入子表记录时，需要用内建函数 LAST_INSERT_ID() 获取相关父表记录的自增ID（这个例子中就是 order_id)
内建函数：MySQL里有很多可用的内置函数，也就是可复用的代码块，各有不同的功能，注意函数名的单词之间用下划线连接
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

INSERT INTO order_items  -- 全是必须字段，就不用指定了
VALUES 
    (last_insert_id(), 1, 2, 2.5),
    (last_insert_id(), 2, 5, 1.5)

创建表的副本

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…… 查询语句都是子查询，子查询是一个很有用的技巧。

创建已有的表或删除不存在的表的话都会报错，所以建表和删表语句都最好加上条件语句（后面会讲）
不再用全部数据，而选用原表中部分数据创建副本表，如，用今年以前的 orders 创建一个副本表 orders_archived，其实就是在子查询里增加了一个WHERE语句进行筛选。注意要先 drop 删掉或 truncate 清空掉之前建的 orders_archived 表再重建或重新插入数据。
法1. DROP TABLE 要删的表名、CREATE TABLE 新表名 AS 子查询

USE sql_store;

DROP TABLE orders_archived;  -- 也可右键该表点击 drop
CREATE TABLE orders_archived AS
    SELECT * FROM orders
    WHERE order_date < '2019-01-01'

法2. TRUCATE ‘要清空的表名’、INSERT INTO 表名子查询
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'

创建一个存档发票表，只包含有过支付记录的发票并将顾客id换成顾客名字
构建的思路顺序：

先创建子查询，确定新表内容：
A. 合并发票表和顾客表

B. 筛选支付记录不为空的行/记录

C. 筛选（并重命名）需要的列

第1步得到的查询内容，可以先运行看一下，确保准确无误后，再作为子查询内容存入新创建的副本订单存档表 CREATE TABLE 新表名 AS 子查询

USE sql_invoicing;

DROP TABLE invoices_archived;  

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

更新单行

用 UPDATE …… 语句来修改表中的一条或多条记录，具体语法结构：

UPDATE 表 
SET 要修改的字段 = 具体值/NULL/DEFAULT/列间数学表达式 （修改多个字段用逗号分隔）
WHERE 行筛选

USE sql_invoicing;

UPDATE invoices
SET 
    payment_total = 100 / 0 / DEFAULT / NULL / 0.5 * invoice_total, 
    /*注意 0.5 * invoice_total 的结果小数部分会被舍弃，
    之后讲数据类型会讲到这个问题*/
    payment_date = '2019-01-01' / DEFAULT / NULL / due_date
WHERE invoice_id = 3

更新多行

语法一样的，就是让 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) 
-- 第二章 4~9 讲的那些写 WHERE 条件的方法均可用
-- 甚至可以直接省略 WHERE 语句，会直接更改整个表的全部记录

让所有非90后顾客的积分增加50点

USE sql_store;

UPDATE customers
SET points = points + 50
WHERE birth_date < '1990-01-01'

在Updates中用子查询

非常有用，其实本质上是将子查询用在 WHERE…… 行筛选条件中
IN …… 后除了可接（……, ……）也可接由子查询得到的多个数据（一列多条数据），感觉和前面 VALUES 后可接子查询道理是相通的
更改发票记录表中名字叫 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')
)

确保WHERE行筛选条件准确准确无误后，再放到修改语句后执行修改：

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’

USE sql_store;

UPDATE orders
SET comments = 'gold customer'
WHERE customer_id IN
    (SELECT customer_id
    FROM customers
    WHERE points > 3000)

删除行

DELETE FROM 表 
WHERE 行筛选条件
（当然也可用子查询）
（若省略 WHERE 条件语句会删除表中所有记录（和 TRUNCATE 等效？））

选出顾客id为3/顾客名字叫’Myworks’的发票记录

USE sql_invoicing;

DELETE FROM invoices
WHERE client_id = 3
-- WHERE可选，省略就是会删除整个表的所有行/记录
/WHERE client_id = 
    (SELECT client_id  
    /*Mosh 错写成了 SELECT *，将报错：
    Error Code: 1241. Operand should contain 1 column(s) 
    Operand n. [计] 操作数；[计] 运算对象；运算元 */
    FROM clients
    WHERE name = 'Myworks')

恢复数据库

更多推荐

SQL进阶教程 | 史上最易懂SQL教程 5小时零基础成长SQL大师（1）

SQL进阶教程 | 史上最易懂SQL教程 5小时零基础成长SQL大师（1）

目录

【第一章】做好准备

什么是SQL

【第二章】在单一表格中检索数据

选择子句

WHERE子句

AND, OR, NOT运算符

IN运算符

BETWEEN运算符

LIKE运算符

REGEXP运算符

IS NULL运算符

ORDER BY子句

LIMIT子句

【第三章】在多张表格中检索数据

内连接

跨数据库连接（合并）

自连接

多表连接

复合连接条件

隐式连接语法

外连接

多表外连接

自我外部连接

USING子句

自然连接

交叉连接

联合

【第四章】插入、更新和删除数据

导航

1. 列属性

插入单行

插入多行

插入分级行

创建表的副本

更新单行

更新多行

在Updates中用子查询

删除行

恢复数据库

相关文章

发布评论取消回复

最近发表

热门文章

标签列表