SQL语言笔记 第五章 子查询

使用的是MySQL数据库

1. 子查询

• 子查询可以在WHERE子句中编写，也可以在FROM子句或者SELECT子句中编写。

SELECT *
FROM employees
WHERE salary > (
-- 括号中的就是子查询
    SELECT AVG(salary)
	 FROM employees
)

2. IN 运算符结合子查询

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

3. 子查询 vs 连接 选择可读性更好的

• 本例中子查询的更易读懂，但有时候添加子查询会使查询太复杂，这时候更适合用连接。

-- 把第二章 IN运算的查询，使用连接来改写
SELECT *
FROM clients
-- 使用左连接，clients表中的数据都会显示，不管client_id在invoices表中是否存在
LEFT JOIN invoices USING (client_id)
WHERE invoice_id IS NULL

-- 练习：筛选出购买了生菜(product_id = 3)的顾客信息
-- product_id在order_items表中可以查到，顾客信息存在于customers表中
-- orders表存在与customers表关联的customer_id，orders表还存在与order_items表关联的order_id
-- 子查询
SELECT
    customer_id,
    first_name,
    last_name
FROM customers
WHERE customer_id IN (
    SELECT o.customer_id
    FROM order_items oi
    JOIN orders o USING (order_id)
    WHERE product_id = 3
);

-- 连接
SELECT DISTINCT
    c.customer_id,
    c.first_name,
    c.last_name
FROM customers c
JOIN orders o USING (customer_id)
JOIN order_items oi USING (order_id)
WHERE oi.product_id = 3;

4. ALL 关键字

-- 使用聚合函数MAX实现，查询出所有比客户3最大发票额大的发票
SELECT *
FROM invoices
WHERE invoice_total > (
-- 这里子查询返回的是单一值，用单一值作比较
	SELECT MAX(invoice_total)
	FROM invoices
	WHERE client_id =3
)

-- 上面的代码也可以理解为查询金额大于所有客户3金额的发票
-- > ALL 与 MAX()可以互换，表示大于查到的所有数值
-- < ALL 与 MIN()可以互换，表示小于查到的所有数值
SELECT *
FROM invoices
-- 下面的子查询等于 WHERE invoice_total > ALL (140,150,160,...) 括号中是子查询中查出的所有数值
-- ALL 关键字的操作原理：发票每行都会把发票金额与括号中的这些数字做对比，满足条件的那行就会返回在最终结果集
WHERE invoice_total > ALL (
-- 这里子查询返回的是一列值，与一列值进行比较
	SELECT invoice_total
	FROM invoices
	WHERE client_id =3
)

5. ANY 关键字

-- = ANY 与 IN 运算符等效，表示等于子查询中的任意值
-- 练习：查询至少有两张发票的客户信息
SELECT *
FROM clients
-- WEHRE client_id IN () 可以替换成下面
-- ANY 关键字的操作原理：client_id等于子查询返回值中的任意一个，该客户的那行就会被返回到最终结果集
WHERE client_id = ANY (
	SELECT client_id
	FROM invoices
	GROUP BY client_id
	HAVING COUNT(*) >= 2
)

6. 相关子查询

• 因为相关子查询在主查询的每一行层面上都会执行，所以执行很慢；数据越多，查询更慢，也会占用更多的存储，但在现实中还是会有很多应用。

-- 练习：选择工资超过部门平均工资的员工
-- 解释：如部门1的员工超过部门1的平均工资就返回
-- 步骤：一、计算每个部门的平均薪资；二、返回员工薪资大于部门平均薪资的
SELECT *
FROM employees e
WHERE salary >  (
	SELECT AVG(salary)
	FROM employees
    -- 以下WHERE子句的e.office_id可以理解为主查询此时正在查询的行的office_id
    -- 比如：主查询正在查询employees表的第一行，第一行记录的office_id=1，此时e.office_id就等于1
    -- 将主查询的office_id给子查询的office_id，此时子查询的office_id=1，然后计算office_id为1的salary平均值
    -- 之后主查询拿第一行的salary与子查询算出的平均值进行比较，大于则将第一行记录返回给最终结果集
    WHERE office_id = e.office_id
    -- 相关子查询：这段子查询与主查询存在相关性，也就是子查询使用了主查询的表
    -- 主查询每查询一行，相关子查询都会执行一次；
    -- 非相关子查询只会执行一次，然后把值交给主查询的WHERE子句
)

7. EXISTS 运算符

-- 说明：查询存在发票的客户信息
-- 使用子查询
SELECT *
FROM clients
-- 子查询会把结果返回给主查询的WHERE子句，这个例子结果是返回的4个client_id的列表
-- 下面的子查询等于 WHERE client_id IN (1,2,3,5)
-- 如果有很多的client_id，会生成很大的列表，妨碍最佳性能，因此对于这种情况，使用EXISTS运算符会提高效率
WHERE client_id IN (
	SELECT client_id
	FROM invoices
);

-- 使用连接 注意要使用DISTINCT 关键字 去除重复项
SELECT DISTINCT
	c.client_id,
	c.name,
	c.address,
	c.city,
	c.state,
	c.phone
FROM clients c
JOIN invoices i USING (client_id)
WHERE c.client_id = i.client_id;

-- 使用 EXISTS 运算符
-- 子查询不会给主查询返回结果，如果子查询中有符合这个搜索条件的行，就返回TRUE 给EXISTS运算符，
-- EXISTS 运算符就会在最终结果集里添加当前记录
SELECT *
FROM clients c
-- EXISTS 运算符的操作原理：查看子查询的表里 是否存在符合WHERE子句这个条件 的行，满足就把这个client_id的客户返回给最终结果集
-- 以下子查询，客户表里的每一位客户，都会检查是否存在一条符合这个条件的记录
WHERE EXISTS (
	SELECT client_id
	FROM invoices
    -- 这里有一个相关子查询，因为子查询关联了主查询的clients表
	WHERE client_id = c.client_id
)
 
-- 练习：找到没有被订购的产品
-- 步骤：1、订单项目表中存在哪些产品id；2、返回产品表中不包含这些id的产品
-- 使用子查询
SELECT *
FROM products
WHERE product_id NOT IN (
	SELECT product_id
	FROM order_items
);

-- 使用连接
SELECT *
FROM products p
LEFT JOIN order_items oi USING (product_id)
WHERE oi.product_id IS NULL;

-- EXISTS
SELECT *
FROM products p
-- EXISTS 运算符前可以加NOT 表示不存在 返回FALSE 子查询的结果就不会放进最终结果集
WHERE NOT EXISTS (
	SELECT product_id
	FROM order_items oi
	WHERE oi.product_id = p.product_id
)

8. SELECT 子句中的子查询

-- 生成一张包含发票id 发票金额 发票平均值 发票金额与平均值的差
SELECT 
	invoice_id,
	invoice_total,
    -- 聚合函数需要写成子查询的形式，才能在最终结果集的每行都显示；否则最终结果集只有一行
    (SELECT AVG(invoice_total) FROM invoices) AS invoice_average,
    -- 差值中的平均值直接复制平均值的子查询太长了，可以用子查询来查询别名
    -- 因为表达式中不能直接使用其他列的别名，但可以使用子查询来查询其他列的别名
    invoice_total - (SELECT invoice_average) AS difference
FROM invoices

-- 练习：获取客户id name 每位客户发票的合计 发票表的发票平均值 前两列的差值
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 子句中的子查询

-- 把第8节的练习作为子查询给FROM子句
-- sales_summary表是虚拟表格，把它的查询结果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_summary
-- 可以添加筛选项
WHERE total_sales IS NOT NULL
-- 在FROM 子句中写子查询，会让整个主查询很复杂；
-- 一般可以将这段查询作为视图存储在数据库中，这可以很大程度上简化查询