23 Natural Joins

• 让database 自动识别join 的相关列表。可能会存在一定的错误—不推荐。

24 Cross Joins

• 将A表格中的每项内容都匹配B表格中的内容。
• 运用场景：不同颜色和不同大小产生的组合方式。

SELECT 
    o.order_id,
    c.first_name
FROM customers c
CROSS JOIN orders o
## 也可以删除这行，参照18，直接用FROM customers,orders
ORDER BY order_id

25 Union

• 选择的内容column数目需一致，否则会报错。

SELECT
    customer_id,
    first_name,
    points,
    "BRONZE" AS "type" ##column名称默认为第一组的
FROM customers
WHERE points < 2000
UNION ##连接
SELECT
    customer_id,
    first_name,
    points,
    "SILVER"
FROM customers
WHERE points BETWEEN 2000 AND 3000
UNION ##联合上下row
SELECT
    customer_id,
    first_name,
    points,
    "GOLD"
FROM customers
WHERE points > 3000
ORDER BY first_name

26 Column Attributes

• 如何插入、更新、删除信息

属性：
1）PK：primary key
2）NN：NOT NULL
3）AL：auto-increment
4）default value

27 Inserting a Single Row

INSERT INTO customers
VALUES (
    DEFAULT, 
    'John', 
    'Smith', 
    NULL, 
    NULL,
    'NULL',
    'NULL',
    'VA',
    DEFAULT)

• 或者还可以在customers()中写需要添加信息的column的名称，在value中就无需写DEFAULT或NULL的内容了。

28 Inserting Multiple Rows

• 插入多行

INSERT INTO products(name,quantity_in_stock,unit_price)
VALUE('one',10,2),
    ('two',5,1),
    ('three',3,0.5)
##直接用多个括号分开来表示

29 Inserting Hierarchical Rows

• 表格之间存在一种hierarchy 关联

INSERT INTO orders (customer_id, order_date, status)
VALUES (1,'2019-01-01',1);
    
INSERT INTO order_items
VALUES
    (LAST_INSERT_ID(),1,1,5),
    (LAST_INSERT_ID(),2,1,2.5)
## LAST_INSERT_ID 可用来获取默认添加的数值（对应父表的信息）

30 Creating a Copy of a Table

• CREATE 语句可以快速复制表格，但表格的属性并不会复制到新表中

CREATE TABLE invoices_archived AS
SELECT *
FROM invoices
WHERE payment_date IS NOT NULL
##创建新表格archived 从invoices中复制，并选择有date 的rows

31 Updating a Single Row

UPDATE invoices
SET 
    payment_total = 50,
    payment_date = '2019-06-06'
WHERE invoice_id = 1
## 更新信息到 id=1 invoice 上

32 Updating Multiple Rows

• mySQL 在参数中默认启动Safe Updates 模式，使得操作中不能通过一条指令来修改非PK多行。

Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.

UPDATE customers
SET 
    points = points + 50
WHERE birth_date < '1990-01-01'
##给90年前的顾客加五十分

33 Using Subqueries in Updates

• 通过子序列来选择修改的数据范围

UPDATE orders
SET 
    comments = 'Gold Customer'
WHERE customer_id IN
    (SELECT customer_id
    FROM customers
    WHERE points >= 3000)
## 对分数大于3000的用户列表的用户，更新他们在order 列表中的评论——黄金用户。

34 Deleting Rows

DELETE FROM invoices
删除发票列表的全部信息。
也可以在底下用WHERE语句限制条件。

35 Restoring the Databases

重新打开“Open SQL script”
即可

36 Aggregate Fuctions

• mySQL 内置了大量的fuctions
  如

SUM() ##算总值
AVG() ##算平均
MAX() ##算最大值
MIN() ##算最小值
COUNT() ##计算记录数量

## 在invoice 表格中，计算19年上半年、19年下半年，以及19年全年的发票金额，与账单金额，并计算差值（获得收入）。
SELECT
    'First 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
    'Second 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'

37 The GROUP BY Clause

• GROUP BY 可以对列表信息按照一定内容分组。分组后再对数据内容按照一定函数处理。

SELECT 
    p.date,
    pm.name AS payment_method,
    sum(p.amount) AS total_payments
FROM payments p
JOIN payment_methods pm
    ON p.payment_method = pm.payment_method_id
GROUP BY p.date, p.payment_method
ORDER BY p.date
## 按照时间和付款方式分组，分组后的金额再计算总数sum()

38 The HAVING Clause

• 类似于WHERE 的filter功能
  但使用于GROUP BY操作后
  使用条件得是存在于SELECT中的内容

SELECT 
    c.first_name,
    c.last_name,
    c.state,
    SUM(oi.quantity * oi.unit_price) AS total_sales ##总的消费金额
FROM customers c
JOIN orders o
    USING (customer_id) ##c 查到o
JOIN order_items oi
    USING (order_id)## o 查到 oi 找到oi.quantity * oi.unit_price 计算total_sales
WHERE c.state = 'VA' 
GROUP BY 
    c.customer_id ##根据用户id分组
HAVING total_sales > 100
## 根据用户分组，选择住在VA，并消费金额大于100$的用户