MySQL多张表格检索数据及编写复杂查询

内连接

USE store;

SELECT c.customer_id,first_name,last_name 
FROM customers AS c
JOIN orders AS o
    ON c.customer_id = o.customer_id
-- USING (customer_id)也可以

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

注：用了别名后其他地方只能用别名，用全名会报错。

跨数据库连接（合并）

SELECT *
FROM order_items AS oi
JOIN sql_inventory.products AS p
	ON oi.product_id = p.product_id

注：JOIN里对于非现在正在用的库的表要加上库名前缀。

自连接

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

多表连接

USE store;

SELECT c.customer_id,first_name,last_name 
FROM customers AS c
JOIN orders AS o
    ON c.customer_id = o.customer_id
JOIN order_statuses os
    ON o.status = os.order_status_id

复合连接条件

USE 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
-- USING (order_id, product_id)

隐式连接

SELECT * 
FROM orders o, customers c  
WHERE o.customer_id = c.customer_id

若忘记WHERE条件筛选语句则得到这几张表的交叉合并结果，所有不建议使用，

外连接

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

LIFT / RIGHT JOIN结果包含只出现在左/右表中的记录

尽量不要使用右连接

自然连接

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

 自然连接会让MySQL自动检索同名列作为合并条件，一般不用。

交叉连接

SELECT *
FROM teachers
CROSS JOIN students

UNION子句

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

ALL关键字

SELECT *
FROM invoices
WHERE invoice_total > ALL (
    SELECT invoice_total
    FROM invoices
    WHERE client_id = 3
)

SELECT *
FROM invoices
WHERE invoice_total > (
    SELECT MAX(invoice_total)
    FROM invoices
    WHERE client_id = 3
)

MAX是用MAX()返回一个顾客3的最大订单金额，再判断哪些发票的金额比这个值大；
ALL法是先返回顾客3的所有订单金额，再用ALL()判断比所有这些金额都大的发票有哪些。
两种方法是完全等效的 。

ANY/SOME关键字

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次以上发票记录的顾客
SELECT *
FROM clients
-- WHERE client_id IN (  
WHERE client_id = ANY (  
    SELECT client_id
    FROM invoices
    GROUP BY client_id
    HAVING COUNT(*) >= 2
)

EXISTS关键字

SELECT *
FROM clients c
WHERE EXISTS (
    SELECT */client_id  
    FROM invoices
    WHERE client_id = c.client_id
)

相关子查询

SELECT *
FROM employees e  
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
    WHERE office_id = e.office_id  
    -- 子查询不加前缀，主查询加
)

SELECT子查询

SELECT 
    invoice_id,
    invoice_total,
    (SELECT AVG(invoice_total) FROM invoices) AS invoice_average,
    invoice_total - (SELECT invoice_average) AS difference
FROM invoices

FROM子查询

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
WHERE total_sales IS NOT NULL