第六章：子查询

• 第六章：子查询
  • 2.2 子查询
  • 2.3 IN 预算符
  • 2.4 in + 子查询 v.s. join（内连接） 和 left join（外连接）
  • 2.5 all 关键字
  • 2.6 any 关键字
  • 2.7 相关子查询!!
  • 2.8 exists 运算符alter
  • 2.9 select 子句中的子查询
  • 2.10 from 子句中的子查询

2.2 子查询

use sql_store;
select *
from products
where unit_price >= (select unit_price
					from products
					where name regexp 'lettuce');
                    
use sql_hr;
select *
from employees
where salary > (select avg(salary)
				from employees);

2.3 IN 预算符

use sql_store;
select * from products
where product_id not in (
	select distinct product_id
-- 	select product_id
	from order_items
);

use sql_invoicing;
select * 
from clients
where client_id not in (
	select distinct client_id
	from invoices
);

2.4 in + 子查询 v.s. join（内连接） 和 left join（外连接）

select *
from clients
left join invoices using(client_id)
where invoice_id is NULL;   # 和前面in+子查询效果一样

use sql_store;  # 多个表格的连接
select distinct customer_id, first_name, last_name
from customers
left join orders using(customer_id)
left join order_items using (order_id)
where product_id in (select product_id
					from products
					where name regexp 'lettuce');

2.5 all 关键字

use sql_invoicing;
select *
from invoices
where invoice_total >  all (  # 大于一列数，其实就是大于一列数的最大值
	select invoice_total
-- where invoice_total > (  #  和上面是等价的
-- 	select max(invoice_total)
	from invoices
	where client_id = 3
);

2.6 any 关键字

select *
from clients
-- where client_id in (
where client_id = any (   # = any 等价于 in
	select client_id
	from invoices
	group by client_id
	having count(*) > 2
);

2.7 相关子查询!!

use sql_hr;
select *
from employees e 
where salary > (
	select avg(salary)
	from employees
	where office_id = e.office_id   # 关联子查询
);

use sql_invoicing;
select *
from invoices i
where invoice_total > (
	select avg(invoice_total)
    from invoices
    where client_id = i.client_id
);

2.8 exists 运算符alter

/*
第一种方法：in + distinct
第二种方法：exists
1.第一种方法，先运行括号里子查询，结果返回给where子句【空间复杂度增加】
2.第二种方法，返回一个指令，对于每一位客户，子查询检查是否符合条件，找到一个就会返回True
*/

select *
from clients
where client_id in (
	select distinct client_id
    from clients
);

select *    
from clients c        # 连接+存在 
where exists (
	select client_id
    from invoices
    where client_id = c.client_id
);

use sql_store;
select *
from products p
where not exists(
	select product_id
    from order_items
    where product_id = p.product_id
);

2.9 select 子句中的子查询

###  之前所有都是在where后面加子查询
use invoicing;
select invoice_id,
	invoice_total,
    (select avg(invoice_total) from invoices)
		as mean_invoice,  # 把均值变成一列
	(select mean_invoice),
	invoice_total - (select mean_invoice) as diff 
from invoices;

select
	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)
from clients c; 

2.10 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)
	from clients c
) as sales_summary;