编写单行子查询

6.2.1在WHERE子句中使用子查询

1.使用“=”号比较操作符

SELECT first_name,last_name FROM customers WHERE customer_id =(SELECT customer_id FROM customers WHERE last_name='Black');

2.使用“<>”、“<”、“>”、“>=”、“<=”等比较操作符

SELECT product_id,name,price
FROM products
WHERE price >
(SELECT AVG(price)
FROM products);

6.2.2 在HAVING子句中使用子查询

SELECT product_type_id,AVG(price)
FROM products
GROUP BY product_type_id
HAVING AVG(price) <
(SELECT MAX(AVG(price))
FROM products
GROUP BY product_type_id);

6.2.3 在FROM子句中使用子查询(内联视图)

SELECT product_id
FROM
(SELECT product_id
FROM products
WHERE product_id<3);

SELECT prds.product_id,price,purchase_data.product_count
FROM products prds,
(SELECT product_id,COUNT(product_id) product_count
FROM purchases
GROUP BY product_id) purchase_data
WHERE prds.product_id=purchases_data.product.id;

6.2.4 限制

1. 在单行查询中使用的子查询不能返回多行
2.
SELECT product_id,name
FROM products
WHERE product_id=
(SELECT product_id
FROM products
WHERE name LIKE '%e%');

LIKE 操作符：
下划线：_ 匹配任意一个字符
百分号：% 匹配从指定位置开始的任意多个字符

2.子查询不能包含ORDER BY 子句,如果需排序，应将ORDER BY子句放在外部查询中

SELECT product_id,name,price
FROM products
WHERE price>
(SELECT AVG(price)
FROM products)
ORDER BY product_id DESC;

6.3 多行子查询

6.3.1 在多行子查询中使用IN操作符

SELECT product_id,name
FROM product
WHERE product_id IN
(SELECT product_id
FROM products
WHERE name LIKE '%e%');

6.3.2 在多行子查询中(与 =,<>,<,>,<=,>=搭配)使用ANY操作符

SELECT employee_id,last_name
FROM empolyees
WHERE salary < ANY
(SELECT low_salary
FROM salary_grades);

6.3.3 在多行子查询中(与 =,<>,<,>,<=,>=搭配)使用ALL操作符号

SELECT employee_id,last_name
FROM employees
WHERE salary > ALL
(SELECT high_salary
FROM salary_grades);

6.4 多列子查询
SELECT product_id,product_type_id,name,price
FROM products
WHERE (product_type_id,price) IN
(SELECT product_type_id,MIN(price)
FROM products
GROUP BY product_type_id);


6.5 关联子查询

SELECT product_id,product_type_id,name,price
FROM products outer
WHERE price>
(SELECT AVG(price)
FROM products inner
WHERE inner.product_type_id=outer.product_type_id);

6.5.2 在关联子查询中使用EXISTS 和 NOT EXISTS

1.在关联子查询中使用EXISTS

SELECT employee_id,last_name
FROM employees outer
WHERE EXISTS
(SELECT employee_id
FROM employees inner
WHERE inner.manager_id=outer.employee_id);

2. 在关联子查询中使用NOT EXISTS

SELECT product_id,name
FROM products outer
WHERE NOT EXISTS
(SELECT 1
FROM purchases inner
WHERE inner.product_id=outer.product_id);

3. EXISTS 和NOT EXISTS与IN和NOT IN 的比较

EXISTS：只检查行的存在性
IN ：则要检查值的实际存在性。

EXISTS 性能高于IN，应优先使用。

当用于比较的列值为NULL时：
NOT EXISTS 返回True
NOT IN 返回false

6.6 编写嵌套子查询---嵌套层次可多达255.

嵌套查询应尽量少用，连接查询性能高，应尽量采用。
SELECT product_type_id,AVG(price)
FROM products
GROUP BY product_type_id
HAVING AVG(price)<
(SELECT MAX(AVG(price)))
FROM products
WHERE product_type_id IN
(SELECT product_id
FROM purchases
WHERE quantity>1)
GROUP BY product_type_id);

6.7 包含子查询的UPDATE

UPDATE employees
SET salary =
(SELECT AVG(high_salary)
FROM salary_grades)
WHERE employee_id=4;

6.8 包含子查询的DELETE

DELETE FROM employees
WHERE salary >
(SELECT AVG(high_salary)
FROM salary_grades);