第一部分：Column Attributes - 列属性

first_name Datatype VARCHAR(50) - 可变字符的缩写，括号里标注了50，就是说这列最多可以有50个字符
如果顾客的名字只有5个字符，那么就只储存那5个字符
所以即使这列字符最长可以有50个字符的长度，你也不会因为first_name没到50字符浪费空间

相比之下

另一种数据类型叫CHAR(character)，如果这里写了CHAR(50)
而first_name只有5个字符，MySQL会再插入45个space字符来填满这列，这样就会导致空间的浪费

所以我们建议一般最好使用VARCHAR来储存字符串或者文本值

1. 在MySQL中，PK意为Primary Key，Column左侧有黄色钥匙标识，PK的值可以识别每一位顾客
2. NN - Not Null意为非空值，这个属性决定了该列是否可以写空值
3. AI - Automatic Increase意为自动递增，通常被用于主键列，所以每次我们在表中插入一条新记录时，我们让MySQL或者数据库引擎在列中插入一个值，其实就是在最后一行加入一个customer_id,在加入新纪录的同时，将customer_id增加1
4. Default/Expression - 每列的默认值
4.1 如果本列中标明了NULL，说明本列的默认值即为空值
4.2 如果没有标明，MySQL将会自动给它们写入No Value - 也是空值
4.3 默认值也可以为‘0’

第二部分：Inserting a Row - 插入单行

INSERT INTO customers
VALUES (DEFAULT, 'John', 'Smith', '1990-01-01', NULL,
        'address', 'city', 'CA', DEFAULT
        )
--- 在VALUES子句后的括号中 写入customer这个表中 每一列的值

--- 对于由AI控制的PK而言，我们可以选择写入指定的确切值 也可以选择使用默认值让MySQL自己生成这个值，但是考虑到在以后可能会有同样id的customer，PK列又必须是唯一的，不能有重复值，所以在这里我们在这里建议使用DEFAULT
--- 在SQL里，字符串和日期值要带上引号
VALUES (DEFAULT, 'John', 'Smith', NULL, DEFAULT, ...)
--- 如果在表的属性中，NN这一列没有打勾，那么意即这一列是可选的，所以这一列可以选择输入空值或者一个确切值，在这里的空值的意思是值缺失；由于在表的属性中这一列的默认值也为NULL，所以在这里输入DEFAULT关键字和NULL关键字的效果是等价的。
--- 对于points这一列我们可以使用确切值或者默认值

我们也可以指定给哪些列添加值，这样可以根据默认值填充/或者AI自动填充的值就不需要写出
INSERT INTO customers (
        first_name,
        last_name,
        birth_date,
        address,
        city,
        state)
VALUES ('John', 
        'Smith', 
        '1990-01-01', 
        'adress', 
        'city', 
        'CA')

第三部分：Inserting Multiple Rows - 插入多行

Part3以Shippers表为例
shipper_id是主键(PK)，不能为空值(NN)，并且它是自动递增列(AI)
name的Datatypes是VARCHAR(50)，不能为空值(NN)

INSERT INTO shippers (name)
VALUES ('Shippers1'),
       ('Shippers2'),
       ('Shippers3')

EXERCISE：

Part3 - Inserting Multiple Rows - 插入多行
-- Insert three rows in the products table

-- products表有四列
-- product_id有以下数据类型和列属性：INT(11), PK, NN, AI
-- name有以下数据类型和列属性：VARCHAR(50), NN
-- quantity_in_stock有以下数据类型和列属性：INT(11), NN
-- unit_price有以下数据类型和列属性：DECIMAL(4,2)
第一列可以忽略，因为它是一个自动递增列

SOLUTION:

USE sql_store;

INSERT INTO products (name, quantity_in_stock, unit_price)
VALUES ('Product1', 10, 1.95),
       ('Product2', 11, 1.95),
       ('Product3', 12, 1.95)

第四部分：Inserting Hierarchical Rows - 插入分层行

orders table
order_id INT(11)，列属性为PK,NN,AI,无默认值
customer_id INT(11)，列属性为NN,无默认值
order_date DATE，列属性为NN,无默认值
status TINYINT(4)，列属性为NN,默认值为'1'
comments VARCHAR(200)，无列属性，默认值为NULL
shipped_date DATE，无列属，默认值为NULL
shipper_id SMALLINT(6)，无列属性，默认值为NULL

实际的order items在order_items表中
order_id INT(11)，列属性为PK,NN,AI,无默认值
product_id INT(11)，列属性为PK,NN,无默认值
quantity INT(11)，列属性为NN,无默认值
unit_price DECIMAL(4,2)，列属性为NN,无默认值

这两个表的关系就是我们常说的 - 母子关系
在这段关系中订单表是母方，订单项目表是子方
订单表的一行可以在订单项目表中有一子或者多子

插入分层行示例:

INSERT INTO orders (customer_id, order_date, status)
VALUES (1, '2019-01-02', 1);

--- MySQL自带的last insertinsert id 意指最近插入id
INSERT INTO order_items
VALUES (LAST_INSERT_ID(), 1, 1, 2.95),
       (LAST_INSERT_ID(), 2, 1, 3.95)
      
代码执行的结果 应该是order_items中多了两行记录

第五部分：Creating a Copy of a Table - 创建表复制

CREATE TABLE orders_archived AS
SELECT * FROM orders

--- 可以看到订单表被完全复制了
--- 但是如果以设计模式打开这张表，你会发现，与原orders表相比
--- 复制表没有主键，即order_id这一列没有被标记为主键，另外它也没有被标记为AI(自动递增)列
--- 所以如果用以上的SQL语句去创建表的复制时，MySQL会忽略这些属性

--- 这也就是说 如果我们想直接把记录插入这张新的复制表，我们就不能再省略order_id的值了，因为这一列不再是自动递增列
--- 综上所述，使用CREATE TABLE AS语句可以快速创建一张表的复制
--- SELECT * FROM orders 这个选择语句称为子查询，子查询是部分属于另外一段SQL语句的选择语句

--- Truncate Table 指删除表里的所有数据，但是会保存表头

假设你只想复制orders table里的一部分记录到这张表里
比如 下列SQL语句查询到结果：所有在2019年之前下的订单
SELECT *
FROM orders
WHERE order_date < '2019-01-01'

INSERT INTO orders_archived
-- 这里不需要提供列名称，因为我们会给这段查询的每一列都赋值，所以在表名orders_archived 
SELECT *
FROM orders
WHERE order_date < '2019-01-01'

EXERCISE：

Part5 - Creating a Copy of a Table - 创建表复制
-- sql_invoicing database 中的 invoices table
-- 它含有invoice_id, number, client_id, invoice_total, payment_total, invoice_date, due_date, payment_date
-- 假设我们想要创建一份这张表部分记录（支付过的发票记录）的复制，并且置于新表invoices archive
-- 在新表中，我们不想要客户id列，我们想要客户名列
-- 所以需要连接这张表和客户表
-- 然后使用那段查询，作为CREATE TABLE语句中的一段子查询

-- 付款日期列是否为空决定了该发票是否有付款，所以我们只需要选择有付款日期/付款总计大于0的发票记录

SOLUTION:

USE sql_invoicing;

CREATE TABLE invoices_archived AS
SELECT
    i.invoice_id,
    i.number,
    c.name AS client,
    i.invoice_total,
    i.payment_total,
    i.invoice_date,
    i.payment_date,
    i.due_date
FROM invoices i
JOIN clients c
    USING (client_id)
WHERE payment_date IS NOT NULL

从SELECT -> WHERE部分的SQL语句 可以用作CREATE TABLE AS语句的子查询

第六部分：Updating a Single Row - 更新单行

使用UPDATE语句可以更新表中的一条或者多条记录
SET子句用来指定一列或多列的新值
WHERE条件来识别记录或者需要被更新的记录

UPDATE invoices
SET payment_total = 10, payment_date = '2019-03-01'
WHERE invoice_id = 1

如果我们改错了

UPDATE invoices
SET payment_total = 0, payment_date = NULL
可以使用NULL关键字在允许空值的列中输入空值
WHERE invoice_id = 1

--- 在设计模式中打开这个表，payment_date的默认值是NULL
--- payment_tatol的默认值是'0.00'，所以payment_total也可以从0改为写DEFAULT
例如:
UPDATE invoices
SET payment_total = DEFAULT, payment_date = NULL
可以使用NULL关键字在允许空值的列中输入空值
WHERE invoice_id = 1

UPDATE invoices
SET 
    payment_total = invoice_total * 0.5,
    payment_date = due_date
WHERE invoice_id = 3

第七部分：Updating Multiple Rows - 更新多行

UPDATE invoices
SET 
    payment_total = invoice_total * 0.5,
    payment_date = due_date
WHERE client_id = 3

--- 在MySQL中运行这段SQL代码会有错误提示
--- 这是因为在默认情况下，MySQL Workbench会在安全更新模式下运行
--- MySQL只会允许你更新一条记录

更改设置即可：MySQL Workbench -> preferences -> SQL editor -> 最下方✅取消

UPDATE invoices
SET 
    payment_total = invoice_total * 0.5,
    payment_date = due_date
WHERE client_id = (3, 4)

--- 严格来说，这个WHERE子句是可选的
--- 如果你想要在一张表里更新所有记录，你可以直接忽略它

EXERCISE：

Part7 - Updating Multiple Rows - 更新多行
-- USE sql_store
-- Write a SQL statement to

-- give any customers born before 1990
-- 50 extra points

SOLUTION:

USE sql_store;

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

第八部分：Using Subqueries in Updates - 在Updates中使用子查询

依然用前例中的SQL语句

UPDATE invoices
SET 
    payment_total = invoice_total * 0.5,
    payment_date = due_date
WHERE client_id = 3

--- 如果我们没有client的id，只有client name的话怎么办
--- 就需要先找到那位client的id，然后用id来更新所有的发票

UPDATE invoices
SET 
    payment_total = invoice_total * 0.5,
    payment_date = due_date
WHERE client_id = 
        (SELECT client_id
        FROM clients
        WHERE name = 'Myworks');
--- 然后可以用选择语句作为UPDATE语句里的子查询
--- 子查询sub query就是在另一段SQL语句里的select statement

UPDATE invoices
SET 
    payment_total = invoice_total * 0.5,
    payment_date = due_date
WHERE client_id IN 
        (SELECT client_id
        FROM clients
        WHERE state IN ('CA', 'NY'));

--- 现在这个子查询会返回多条记录（client id = 1，3）
--- 所以要把 client_id后面的 = 改为 IN

UPDATE invoices
SET 
    payment_total = invoice_total * 0.5,
    payment_date = due_date
WHERE payment_date IS NULL

EXERCISE：

Part8 - Using Subqueries in Updates - 在Updates中使用子查询
-- USE sql_store
-- orders table中有些订单没有注释

-- 写一段SQL语句，为超过3000积分的顾客更新order comments
-- 如果这个顾客下过订单，那就改注释为 金牌顾客

SOLUTION:

USE sql_store;

UPDATE orders
SET comments = 'Gold customer'
WHERE customer_id IN

(SELECT customer_id
FROM customers
WHERE points > 3000)

第九部分：Deleting Rows - 删除行

DELETE FROM invoices
WHERE invoice_id = (
SELECT *
FROM clients
WHERE name = 'Myworks'
)

第十部分：Restoring the Databases - 恢复数据库

MySQL Workbench -> File菜单 -> Open SQL scripts
-> 去到储存SQL脚本的目录位置 -> create-databases.sql -> 执行脚本，重建所有数据库