mysql day5 编写复杂查询

-- select *
-- from products
-- where unit_price > (
		-- select unit_price
		-- from products
		-- where product_id = 3)

-- 习题 在平均收入以伤害
-- use sql_hr;
-- select *
-- from employees
-- where salary > (
-- 		select AVG(salary)
-- 		from employees
-- )

-- in运算符
-- use sql_store;
-- select *
-- from products
-- where product_id not in ( -- 返回没有被订购的产品
-- 		select distinct product_id -- 返回有订单产品的唯一名单
-- 		from order_items)

--  习题
--  返回没有发票的客户
-- use sql_invoicing;
-- select *
-- from clients
-- where client_id not in (
		-- select distinct client_id 
		-- from invoices
		-- )
-- 子查询和连接
-- use sql_invoicing;
-- select *
-- from clients
-- left join invoices using (client_id)
-- where invoice_id is null

-- 练习 
-- 订了生菜的顾客
-- use sql_store;
-- select 
		-- distinct c.customer_id,
		-- c.first_name,
		-- c.last_name,
		-- p.name
-- from customers c
-- join orders using (customer_id)
-- join order_items using (order_id)
-- join products p using (product_id)
-- where product_id = '3'

-- 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)

-- all关键字
-- 选择大于客户3的所有发票的发票

-- 选取客户3的所有发票 - 找打面额最大的发票
-- 法1
-- use sql_invoicing;
-- select *
-- from invoices
-- where invoice_total > (
		-- select max(invoice_total)
		-- from invoices
		-- where client_id = '3' )
-- 法2
-- select *
-- from invoices
-- where invoice_total > all(
		-- select invoice_total
		-- from invoices
		-- where client_id = '3')

-- in 与 in any相同
-- select *
-- from clients
-- where client_id in(
		-- select client_id -- count(*) -- 聚合函数 每个客户的发票总数
		-- from invoices
		-- group by client_id
		-- having count(*) >= 2)

-- 相关子查询
-- 内外查询都用相同的表，外循环对每个员工进行子查询，计算同一个部门的员工的平均工资，返回大于平均工资的员工
-- use sql_hr;
-- select *
-- from employees e
-- where salary > (
		-- select avg(salaey)
		-- 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)

-- exists运算符
-- 只返回有发票的顾客
-- select *
-- from clients
-- where client_id in (
-- select distinct client_id -- 要得出很多个结果再去主查询中匹配
-- from invoices )
-- 法二
-- select *
-- from clients c 
-- where exists ( -- exists返回一个指令，说明这个子查询中是否有符合这个搜索条件的行，有的话即返回
			-- select client_id
			-- from invoices
			-- where client_id = c.client_id) -- 发票表里是否有这个id的客户

-- 习题
-- 找到没有被订购的产品
-- use sql_store;
-- select *
-- from products
-- where product_id not in (
		-- select product_id
		-- from order_items)
-- 法二
-- use sql_store;
-- select *
-- from products p
-- where not exists (
		-- select product_id
		-- from order_items
		-- where product_id = p.product_id -- where是exists的条件
		-- )

-- select子句中的子查询
-- use sql_invoicing;
-- select 
		-- invoice_id,
		-- invoice_total,
		-- (select avg(invoice_total)
-- from invoices) as invoice_average,
-- invoice_total - (select invoice_average) as difference
-- from invoices

-- 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

-- from子句中的子查询
use sql_invoicing;
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_salary -- 产生新的表

where total_sales is not null