mysql基础进阶——编写复杂查询

史呆芬
125 阅读4分钟

1.子查询

  • 说明：子查询也称“内部查询”或者“嵌套查询”，是指将一个 SELECT 查询（子查询）的结果作为另一个 SQL 语句（主查询）的数据来源或者判断条件。

  • 子查询可以嵌入 SELECT、INSERT、UPDATE 和 DELETE 语句中，也可以和 =、<、>、IN、BETWEEN、EXISTS 等运算符一起使用。

  • 实例：employees 表里，选择所有工资超过平均工资的雇员

  • 思路：先由子查询得到平均工资，在选择工资超过平均工资的雇员

  • 代码如下：

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

2.IN运算符

  • 说明：IN运算符来确定指定列的值是否匹配列表中的值或子查询中的任何值。
  • 实例：在 sql_store 库 products 表中找出那些从未被订购过的产品
  • 思路：用DISTINCT去重，得到所有被订购的产品列表，在使用NOT IN 选出不在此列表的产品即为从未被订购过的产品。
  • 代码如下：
SELECT *
FROM products
WHERE product_id NOT IN (
	SELECT DISTINCT  product_id
	FROM order_items
)

3.ALL运算符

  • 说明：ALL是一个逻辑运算符，它将单个值与子查询返回的单列值集进行比较。
  • 注意：ALL必须以比较运算符开头，例如：>,>=,<,<=,<>,=,后跟子查询。
  • 描述说明：

image.png

  • 实例：选择大于客户3所有发票额的发票
SELECT *
FROM invoices
WHERE invoice_total > ALL(
	SELECT invoice_total
	FROM invoices
	WHERE client_id = 3	
)

4.ANY运算符

  • 说明：ANY是一个逻辑运算符，它将值与子查询返回的一组值进行比较。
  • 注意：ANY必须以比较运算符开头，例如：>,>=,<,<=,<>,=,后跟子查询。
  • 描述说明：

image.png

  • 实例：选择至少有两张发票的客户
 SELECT *
  FROM  clients
  WHERE client_id = ANY(
	  SELECT client_id
          FROM invoices
          GROUP BY client_id
          HAVING COUNT(*) >=2
  )

5.相关子查询

  • 说明：执行依赖于外部查询，多数情况下是子查询的WHERE子句中引用了外部查询的表
  • 执行过程：
    • （1）从外层查询中取出一个元组，将元组相关列的值传给内层查询。
    • （2）执行内层查询，得到子查询操作的值。
    • （3）外查询根据子查询返回的结果或结果集得到满足条件的行。
    • （4）然后外层查询取出下一个元组重复做步骤1-3，直到外层的元组全部处理完毕。
    • 因此，相关子查询时一个类似于循环的过程
  • 实例：选出 sql_hr.employees 里那些工资超过他所在办公室平均工资（而不是整体平均工资）的员工。
  • 思路：给主查询 employees表 设置别名 e，这样在子查询查询平均工资时加上 WHERE office_id = e.office_id 筛选条件即可相关联地查询到目前员工所在地办公室的平均工资
  • 代码如下：
SELECT *
FROM employees e -- 关键1
WHERE salary > (
	SELECT AVG(salary)
	FROM employees
	WHERE office_id = e.office_id  --关键2

)

6.EXISTS运算符

  • 说明：EXISTS用于判断查询子句是否有记录，如果有一条或多条记录存在返回True，否则返回False。
  • 实例：找出有过发票记录的客户
  • 代码如下：
SELECT *
FROM clients c
WHERE EXISTS(
	SELECT client_id
	FROM invoices
	WHERE client_id = c.client_id
	)

7.SELECT子句中的子查询

  • 简单讲就是，SELECT选择语句是用来确定查询结果选择包含哪些字段，每个字段都可以是一个表达式，而每个字段表达式里的元素除了可以是原始的列，具体的数值，也同样可以是其它各种花里胡哨的子查询的结果
  • 任何子查询都是简单查询的嵌套，没什么新东西，只是多了一个层级而已，由内向外地一层层梳理就很清楚
  • 要特别注意记住以子查询方式实现在SELECT中使用同级列别名的方法
  • 实例：得到一个有如下列的表格：invoice_id, invoice_total, avarege（总平均发票额）, difference（前两个值的差）image.png
  • 代码如下：
SELECT 
    invoice_id,
    invoice_total,
    (SELECT AVG(invoice_total) FROM invoices) AS invoice_average,
    invoice_total - (SELECT invoice_average) AS difference
FROM invoices

8.FROM子句中的子查询

  • 子查询的结果同样可以充当一个“虚拟表”作为FROM语句中的来源表，即将筛选查询结果作为来源再进行进一步的筛选查询。但注意只有在子查询不太复杂时进行这样的嵌套，否则最好用后面讲的视图先把子查询结果储存起来再使用。
  • 实例：将上一节练习里的查询结果当作来源表，查询其中 total_sales 非空的记录
  • 代码如下：
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_summury
WHERE total_sales IS NOT NULL
avatar
文章
阅读
粉丝