0%

MySQL笔记(5)--编写复杂查询

发表于 更新于 分类于 Valine：

1 子查询

1
2
3
4
5
6
7
8
9
10
-- Find products that are more 
-- expensive than Lettuce (id = 3)

SELECT *
FROM products
WHERE unit_price > (
    SELECT unit_price
    FROM products
    WHERE product_id = 3
)

2 IN 运算符

1
2
3
4
5
6
7
8
9
10
-- Find the products that have never been ordered

USE sql_store;

SELECT *
FROM products
WHERE product_id NOT IN (
    SELECT DISTINCT product_id
    FROM order_items
)

3 子查询 VS 连接

1
2
3
4
5
6
7
-- Find clients without invoices
SELECT *
FROM clients
WHERE client_id NOT IN (
    SELECT DISTINCT client_id
    FROM invoices
)

用连接的方法:

1
2
3
4
SELECT *
FROM clients
LEFT JOIN invoices USING (client_id)
WHERE invoice_id IS NULL

在时间一样的情况下, 选择更易读的那种方法

4 ALL 关键字

1
2
3
4
5
6
7
8
9
10
-- Select invoices larger than all invoices of client 3
USE sql_invoicing;

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

改用all 关键字:

1
2
3
4
5
6
7
SELECT *
FROM invoices
WHERE invoice_total > ALL (
    SELECT invoice_total
    FROM invoices
    WHERE client_id = 3
)

5 ANY 关键字

1
2
3
4
5
6
7
8
9
10
-- Select clients with at least two invoices

SELECT *
FROM clients
WHERE client_id IN  (
    SELECT client_id
    FROM invoices
    GROUP BY client_id
    HAVING COUNT(*) >= 2
)

改用any 关键字

1
2
3
4
5
6
7
8
9
10
-- Select clients with at least two invoices

SELECT *
FROM clients
WHERE client_id = ANY  (
    SELECT client_id
    FROM invoices
    GROUP BY client_id
    HAVING COUNT(*) >= 2
)

即 “IN” 相当于 “=ANY”

6 相关子查询

1
2
3
4
5
6
7
8
9
10
11
12
-- Select employees whose salary is 
-- above the average in their office

USE sql_hr;

SELECT *
FROM employees e
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
    WHERE office_id = e.office_id
)

注意到子查询用到了外查询的 e.office_id, 也就是说子查询和外查询是相关子查询.

对于非相关子查询, 执行一次就可以了.

但是对于相关子查询, 外查询每一条record都要执行一次子查询,所以执行时间相对较长.

7 EXISTS 运算符

1
2
3
4
5
6
7
8
-- Select clients that have an invoice

SELECT *
FROM clients
WHERE client_id IN (
    SELECT DISTINCT client_id
    FROM invoices
)

子查询返回一个列表, 然后外查询看在不在此列表中.如果子查询返回的列表非常大,就会很影响性能.

使用EXISTS运算符

1
2
3
4
5
6
7
8
9
-- Select clients that have an invoice

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

对于外查询的每一条record, EXISTS 子查询返回TRUE / FALSE, 若返回TRUE,就添加到结果集中.

Exercise

1
2
3
4
5
6
7
8
9
-- Find the products that have never been ordered

SELECT *
FROM products p
WHERE NOT EXISTS (
    SELECT product_id
    FROM order_items
    WHERE product_id = p.product_id
)

8 SELECT 子句中的子查询

1
2
3
4
5
6
7
SELECT
    invoice_id,
    invoice_total,
    (SELECT AVG(invoice_total)
        FROM invoices) AS invoice_average,
	invoice_total - (SELECT invoice_average) AS difference -- 不能直接用别名
FROM invoices

得到这样一张表:

invoice_id invoice_total invoice_average difference
1 101.79 152.388235 -50.598235
2 175.32 152.388235 22.931765
3 147.99 152.388235 -4.398235
4 152.21 152.388235 -0.178235
5 169.36 152.388235 16.971765
6 157.78 152.388235 5.391765
7 133.87 152.388235 -18.518235

Exercise

得到这样一张表:

client_id name total_sales average difference
1 Vinte 802.89 152.388235 650.501765
2 Myworks 101.79 152.388235 -50.598235
3 Yadel 705.90 152.388235 553.511765
4 Kwideo 152.388235
5 Topiclounge 980.02 152.388235 827.631765

Solution:

1
2
3
4
5
6
7
8
9
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

9 FROM 子句中的子查询

将上面的查询结果当成表来用,放在FROM 子句中:

1
2
3
4
5
6
7
8
9
10
11
12
13
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_summary
WHERE total_sales IS NOT NULL

得到:

client_id name total_sales average difference
1 Vinte 802.89 152.388235 650.501765
2 Myworks 101.79 152.388235 -50.598235
3 Yadel 705.90 152.388235 553.511765
5 Topiclounge 980.02 152.388235 827.631765