MYSQL学习笔记

窗口函数

select invoice_id from(
select invoice_id, invoice_total,
avg(invoice_total) over(partition by client_id) as avg_pay
from invoices) a  -- 此处需命名，否则报错
where invoice_total > avg_pay
order by invoice_id

where和窗口函数同时出现时，先执行where

EXISTS

-- 找出从未被订购过的产品
-- 方法1：IN，优点，简单；缺点，如果数据量大，速度慢
use sql_store;
select *
from products
where product_id not in(
select distinct product_id
from order_items
);
-- 方法2：exists，优点：数据量大时效率更高，因为不会生成表
select *
from products a
where exists(
select product_id
from order_items
where product_id != a.product_id) -- 此处写法错误！
-- 正确写法
select *
from products a
where not exists(  -- 不存在加个not就可以
select product_id
from order_items
where product_id = a.product_id)

– P53 select中的子查询：类似窗口函数，但是应该比窗口函数慢

use sql_invoicing;
select
invoice_id,
invoice_total,
(select avg(invoice_total) from invoices) as invoice_avg,
invoice_total - invoice_avg -- 报错，因为不能直接引用别名
from invoices

可以用select invoice_avg

use sql_invoicing;
select
invoice_id,
invoice_total,
(select avg(invoice_total) from invoices) as invoice_avg,
invoice_total - (select invoice_avg) 
from invoices