Mysql（四）

一、复杂查询

1、子查询

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

2、IN运算符

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

3、子查询 VS 连接

使用哪种写法，自己思考
一、
SELECT *
FROM clients
WHERE client_id NOT IN (
    SELECT DISTINCT client_id
    FROM invoices
)
二、
SELECT *
FROM clients
LEFT JGON invoices USING (client_id)
WHERE invoice_id IS NULL

4、ALL关键字

大于客户三所有发票额的发票
(ALL 的作用是先查询ALL后面的语句得到结果，
再进行比较<invoice_total 要大于所有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
）

5、ANY、SOME 关键字

client_id 等于这段查询返回的值里的任何一个

=ANY 和 IN 是一个效果

eg: 获取至少有两张发票的用户

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

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

6、相关子查询

相关子查询相当于FOR循环(数据越多查询越费力，也更占储存)

eg:选择工资超过部门平均水平的员工

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

7、EXISTS运算符

查询有发票的客户

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


对于每一个clients  都会去查是否存在一条符合后面条件的记录
（数据多的时候使用EXISTS会更有效率）

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

8、SELECT 子句中的子查询 

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

9、FROM 子句中的子查询 

FROM 里的子查询必须要加别名

SELECT *
FROM (
    SELECT 
    invoice_id,
    invoice_total,
    ( SELECT AVG(invoice_total) FROM invoices ) AS invoice_average,
    invoice_toatl - (SELECT invoice_average) AS difference
FROM invoices
) AS sales_summary
WHERE invoice_average Is NOT NULL