MySQL第六章知识点

# 第六章
# 1、where子句中的子查询
use sql_store;
select *
from products
where unit_price > (select unit_price
                    from products
                    where product_id = 3);

use sql_hr;
select first_name,
       salary
from employees
where salary > (select sum(salary) / count(employee_id) # 直接写avg(salary)就可以了
                from employees);

# 2、in运算符
# 查询没有发票的客户
use sql_invoicing;
select name
from clients
where client_id not in (select distinct client_id
                        from invoices);

# 3、子查询vs连接join
# 若添加子查询会使一个查询太过复杂，则使用join更好
use sql_store;
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 product_id = 3
order by customer_id;

# !!!!!用子查询的这个思路好好理解一下，外面没有join，查询条件放到where子句中的子查询里，在子查询里join
select customer_id, first_name, last_name # 不需要distinct，因为是在in后面匹配
from customers c
where customer_id in (select o.customer_id
                      from order_items oi
                               join orders o using (order_id)
                      where product_id = 3);


#4、all关键字——子查询返回一列值时用，可用MAX聚合函数改写
use sql_invoicing;
select *
from invoices
where invoice_total > all (select invoice_total
                           from invoices
                           where client_id = 3);
select *
from invoices
where invoice_total > (select max(invoice_total)
                       from invoices
                       where client_id = 3);

#5、any关键字（或者some）
# 查询至少有两张发票的客户
select *
from clients
where client_id in (select client_id
                    from invoices
                    group by client_id
                    having count(*) >= 2); #分组后再选用having，分组前选用where

select *
from clients
where client_id = any (select client_id
                       from invoices
                       group by client_id
                       having count(*) >= 2);


# 6、！！！！相关子查询——查询会在主查询的每一行的层面执行，常执行的很慢
use sql_hr;
select *
from employees e
where salary > (select avg(salary)
                from employees
                where e.office_id = office_id # 不能用group by(office_id)，会找出每个office的平均薪资，返回一列值，前面没写all或any等会报错
    #可以用group by(e.office_id)
);

use sql_invoicing;
select invoice_id, client_id, invoice_total
from invoices i
where invoice_total > (select avg(invoice_total)
                       from invoices
                       where client_id = i.client_id)
order by client_id;

# 7、exists运算符——当表的数据很多时，比使用in效率高，因为in后的子查询生成了很大的结果集
# 查询有发票的客户
select *
from clients
where client_id in (select distinct client_id
                    from invoices);

select distinct client_id
from clients
         join invoices using (client_id);

# 对每一个客户都检查是否存在一条符合条件的记录
select *
from clients c
where exists(select client_id
             from invoices
             where c.client_id = client_id);

# 找到从未被订购的产品
use sql_store;
select product_id
from products p
where not exists(select product_id
                 from order_items
                 where p.product_id = product_id);


# 8、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;

select client_id,
       name,
       (select sum(invoice_total)
        from invoices
        where c.client_id = invoices.client_id) as total_sales,
       (select avg(invoice_total)
        from invoices)                          as average,
#     (total_sales - average )as difference  # 这样写会报错找不到列,因为在 SQL 中，不能直接在同一 SELECT 语句中引用其他列别名
# 在执行子查询时，外部查询中定义的列别名已经被解析器处理过了，所以子查询中可以直接访问到这些已经解析过的列别名
       (select total_sales - average)           as difference
from clients c;


# from子句的子查询——必须要给子查询写一个别名，不管我们会不会用到
select *
from (select client_id,
             name,
             (select sum(invoice_total)
              from invoices
              where c.client_id = invoices.client_id) as total_sales,
             (select avg(invoice_total)
              from invoices)                          as averagge,
             (select total_sales - averagge)          as difference
      from clients c) as sales_summary
where total_sales is not null