跟Mosh老师学SQL的第五天 - 编写复杂查询

元元的成长之路
88 阅读10分钟

第一部分：Subqueries - 子查询

--- 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
)
--- 当MySQL试图执行这条查询
--- 首先它会评估我们的内查询 或者子查询
--- 所以它会获取生菜的单价，然后把结果传给我们的外查询
--- 结果会得到两个比生菜贵的产品
--- WHERE/FROM/SELECT子句里编写子查询

EXERCISE：

Part1 - Subqueries - 子查询
-- In sql_hr database:
-- Find employees whose earn more than average
-- 这个子查询返回了一个单一值

SOLUTION:

USE sql_hr;

SELECT *
FROM employees
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
)

第二部分：The IN Operator - IN运算符

--- Find the products that have never been ordered
--- 在order items表里，我们得知每个产品被订购的次数
--- 所以为了解决这个问题 我们首先要找到表内所有产品

USE sql_store;

SELECT DISTINCT product_id
FROM order_items
--- 这个子查询返回了一个列表的值
--- 现在就有了被订购产品的唯一列表
--- 这条查询要作为一条存在另一个查询内部的子查询

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

EXERCISE：

Part2 - The IN Operator - IN运算符
-- In sql_invoicing database
-- Find clients without invoices

SOLUTION:

USE sql_invoicing;

先写subqueries
SELECT DISTINCT client_id
FROM invoices

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

第三部分：Subqueries vs Joins - 子查询vs连接

--- Find clients without invoices

USE sql_invoicing;

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

--- 我们经常用JOIN重写一段子查询 或者反过来
--- 现在来改写上面的例子，不是不用子查询，要用一个JOIN
--- 要选择所有客户 然后用invoices表连接这些客户
--- 内连接 和 外连接
--- 如果做内连接，只会获得所有 有发票的客户
--- 如果做外连接 -> 左连接，会获取所有客户，不管他们有没有发票
--- SQL语句如下：

SELECT *
FROM clients
LEFT JOIN invoices USING (client_id)
WHERE invoice_id IS NULL

--- 性能和易读性决定了采用哪种写法
--- 如果以上查询的两种写法执行时间一样
--- 应该选择第一种 -〉更加易读的查询

EXERCISE：

Part3 - Subqueries vs Joins - 子查询vs连接
-- In sql_store
-- Find customers who have ordered lettuce (id = 3)
-- Select customer_id, first_name, last_name

SOLUTION:

USE sql_store;

SELECT *
FROM customers
WHERE customer_id IN (
    SELECT o.customer_id
    FROM order_items oi
    JOIN orders o USING (order_id)
    WHERE product_id = 3
    --- 现在在这张表里我们没有customer_id
    --- 所以我们要连接这张表到orders表 来得到customer_id
)
-- 在子查询中 需要返回订购了生菜的customer_id

-- 换一种写法，SQL语句如下
SELECT DISTINCT customer_id, first_name, last_name
FROM customers c
JOIN orders o USING (customer_id)
JOIN order_items oi USING (order_id)
WHERE oi.product_id = 3

第四部分：The ALL Keyword - ALL关键字

--- Select invoices larger than all invoices of client 3
--- 比client 3的每一张发票的invoice_total都要大

USE sql_invoicing;

SELECT *
FROM invoices
WHERE client_id = 3
--- 查询结果是client 3所有的发票
--- 每张发票都有总额
--- invoice_id =3 最大的一张发票是167美元
--- 修改查询，来得到所有invoice_total大于167美元的发票

SELECT MAX(invoice_total)
FROM invoices
WHERE client_id = 3
--- 可以用这段查询，作为子查询，用以选择所有总额大于167美元的发票
--- 这段子查询返回了单一值

SELECT *
FROM invoices
WHERE invoice_total > (
    SELECT MAX(invoice_total)
    FROM invoices
    WHERE client_id = 3
)
--- 主查询会将invoice_total和$167比较


--- 另外的一张方法就是 ALL 关键词
SELECT *
FROM invoices
WHERE invoice_total > (
    SELECT invoice_total
    FROM invoices
    WHERE client_id = 3
)
--- 查询的结果是客户3所有的invoice_total
--- 现在我们想选择总额大于所有这些数值的发票

SELECT *
FROM invoices
WHERE invoice_total > ALL (
    SELECT invoice_total
    FROM invoices
    WHERE client_id = 3
)
--- 首先MySQL会执行这段子查询
--- 子查询会返回系列值
--- 主查询会将每行invoice_total和系列值中的数字做比较
--- 如果invoice_total大于 所有的 系列值中的值
--- 那么 那行invoice_total就会返回在最终结果集中
--- 获取 invoice_total高于子查询返回的所有值 的行
--- 
--- 如果不加ALL关键字，那么MySQL就无法对比invoice_total 和所有的这些值

第五部分：The ANY Keyword - ANY关键字

--- Select clients with at least two invoices
--- 返回至少有两张发票的client

--- 首先我们要知道每位client的发票数
SELECT client_id, COUNT(*)
FROM invoices
GROUP BY client_id
--- 查询结果是 每位客户边上显示了发票数
--- 但是我们只关心至少有两张发票的客户
--- 所以我们要用HAVING子句进行筛选 -> 因为我们想在数据分组之后再筛选
HAVING COUNT(*) >= 2
--- 我们并不需要查询结果中的COUNT(*)列，所以可以把它从选择子句中删除
--- 现在我们想选择有这些id的客户

SELECT *
FROM clients
WHERE client_id IN (
    SELECT client_id, COUNT(*)
    FROM invoices
    GROUP BY client_id
    HAVING COUNT(*) >= 2
)
--- 这段子查询里没有外查询的引用也没有和外查询有相关性
--- 所以MySQL只会执行一次这段子查询
--- 这里的子查询 返回了一系列的client_id
--- 它们会被赋值到外查询或者主查询的WHERE子句中

另外一种写法：
SELECT *
FROM clients
WHERE client_id = ANY (
    SELECT client_id, COUNT(*)
    FROM invoices
    GROUP BY client_id
    HAVING COUNT(*) >= 2
)
--- 意即 如果client_id等于这段查询返回的值的任何一个
--- 那位client就会被返回到最终结果
--- 所以 = ANY 和 IN operator等效

第六部分：Correlated Subqueries - 相关子查询

--- Select employees whose salary
--- above the average in their office

USE sql_hr;

--- 伪代码
--- for each employee, calculate the avg salary for employee.office
--- return the employee if salary > avg

SELECT *
FROM employees e
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
    WHERE office_id = e.office_id
    --- 外查询和内查询都用到了员工表
    --- 为了获取在同一部门的员工，给WHERE Clause 中的 office_id加了表名的前缀
)

--- 目前的逻辑是：首先MySQL会来到员工表
--- 对每位员工执行 这段子查询
--- 计算同一个部门员工的平均工资
--- 然后如果这名员工的工资高于平均值
--- 这名员工就会被返回在最终结果里
--- 然后是第二条记录
--- 第二条记录也会计算所有同一部门员工的平均工资
--- 这就被我们称为相关子查询
--- 因为这段子查询和外查询存在相关性
--- 即 在子查询的WHERE Clause中引用了外查询里出现的别名，也就是这里的e
--- 相比之下，所有我们以前写的子查询，都是非关联子查询
--- 
--- 当我们使用相关子查询的时候，这段查询会在主查询每一行的层面执行

EXERCISE：

Part6 - Correlated Subqueries - 相关子查询
-- USE sql_invoicing;
-- Get invoices that larger than the
-- client's average invoice amount
-- 每位客户可以有多张发票
-- 对每位客户都要找到平均发票额
-- 然后仅返回拥有更高invoice_total的发票
-- 并对每位顾客重复如上操作
-- 在这种时候就可以用相关子查询
-- 因为我们没有在整张表中都采用一样的平均值

SOLUTION:

USE sql_invoicing;

SELECT *
FROM invoices i
WHERE invoice_total > (
    SELECT AVG(invoice_total)
    FROM invoices
    WHERE client_id = i.client_id
)

第七部分：The EXISTS Operator - EXISTS运算符

-- Select clients that have an invoice
-- 写一段查询，得到有发票的client -> IN Operator / JOIN
-- 获取在发票表中有发票的客户

SELECT *
FROM clients
WHERE client_id IN (
    SELECT DISTINCT client_id
    FROM invoices
)
-- 在这段SQL查询中，MySQL将会首先执行子查询，然后将子查询的结果返回到WHERE子句中
-- 在这个案例中 被子查询返回到那个结果就是一个有4个client_id的列表
-- 但是，在使用IN Operator时 并且 有发票的client_id达到了成千上万或者上亿的时候
-- 子查询就会生成一张非常大的列表，就会影响SQL语句的性能
-- 在这种情况下，使用EXISTS运算符可以提高效率

另外一种方法：
SELECT *
FROM clients c
WHERE EXISTS (
    SELECT client_id
    FROM invoices
    WHERE client_id = c.client_id
)

-- 出现 相关子查询
-- 在WHERE clause中将内查询和外查询关联了
-- 在子查询中引用了clients table
---
-- 但是在外查询的WHERE子句中 我们没有引用列
-- 而是使用EXISTS运算符
-- 来查看invoices table里是否存在符合client_id = c.client_id这个条件的行
---
-- 当MySQL执行这段查询，对于client表里的每一位客户，
-- 它都会检查是否存在一条符合这个条件的记录，这里的这个条件，指子查询。
---
-- 当我们使用EXISTS运算符
-- 子查询并没有给外查询返回一个结果 (子查询并没有真的把结果集返回给外查询)
-- 它会返回一个指令，说明这个子查询中是否有符合这个搜索条件(client_id = c.client_id)的行
-- 手里拿着一个来自于invoices表的client_id，然后去invoices table里问 有没有这个id的client啊
-- 一旦它在这个表中有一条匹配这个条件的记录
-- 它就会返回TRUE，给EXISTS运算符，然后这个EXISTS运算符就会在最终结果里添加当前记录，也就是当前客户

EXERCISE：

Part7 - The EXISTS Operator - EXISTS运算符
-- Find the products that have never been ordered
-- USE sql_store;

SOLUTION:

USE sql_store;

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

-- 用EXISTS运算符改写这段查询
SELECT *
FROM products p
WHERE NOT EXISTS (
    SELECT product_id
    FROM order_items
    WHERE product_id = p.product_id
)

-- 对于每个产品 它要检查看看这个条件是否正确
-- 如果order_items存在产品对应了这个id
-- 子查询会返回TRUE值
-- 加上NOT运算符，TRUE最后会变成FALSE
-- 所以那条记录就不会被放进最终结果集中

第八部分：Subqueries in the SELECT Clause - SELECT子句中的子查询

之前都只在选择语句的WHERE子句中用子查询
但是选择子句中也是可以用子查询的
--- 试图生成包含以下四列的报告invoice_id, invoice_total, invoice_average, difference
--- invoice_average 也就是发票表里所有发票的平均值
--- 还有发票总计和发票平均的差值

SELECT
    invoice_id,
    invoice_total,
    (SELECT AVG(invoice_total)
        FROM invoices) AS invoice_average,
    --- 括号里用聚合函数求出平均值，括号外把值给到每一列
    invoice_total - invoice_average
    --- 这个地方不能写 - invoice_average
    --- 因为表达式expression中不能使用列的别名
    --- 第一种方法是复制这整段AS起了别名的子查询 替换 invoice_average
    --- 第二种方法是把这个 invoice_average 转化成一段子查询
FROM invoices

第二种方法：
SELECT
    invoice_id,
    invoice_total,
    (SELECT AVG(invoice_total)
        FROM invoices) AS invoice_average,
    invoice_total - (SELECT invoice_average) AS difference
FROM invoices
--- AS difference的原因是因为 列名被设置成了我们用在这里的表达式
--- 子查询不单单在WHERE子句中可以使用
--- 也可以用在SELECT Clause 和 FROM Clause 中

EXERCISE：

Part8 - Subqueries in the SELECT Clause - SELECT子句中的子查询
-- client_id, name, total_sales, average, difference
--

SOLUTION:

USE sql_invoicing;

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

第九部分：Subqueries in the FROM Clause - FROM子句中的子查询

USE sql_invoicing;

SELECT *
FROM (
--- 这里我们可以不引用一张表，而是可以使用这段查询的结果
--- 下方的子查询缩进，并不是必选项，只是为了增加代码的可读性
--- 这就是一个在选择语句的FROM子句中使用子查询的例子
--- 但每当我们在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子句，只返回有发票的客户
WHERE total_sales IS NOT NULL

--- 我们还可以把这张表和别的表连接
--- 但是 在select statement的FROM子句中写子查询
--- 会让我们的主查询变得更复杂
--- 一个更好的方案就是使用 视图
--- 我们可以将这段子查询作为视图存储在数据库中
--- 然后我们可以把这个视图取名为sales_summary
avatar
文章
阅读
粉丝