文章目錄
- 子查詢
- IN運算符
- 子查詢 VS 連接
- ALL關鍵字
- ANY關鍵字
- 相關子查詢 !
- EXISTS運算符
- select子句中的子查詢
- from子句中的子查詢
子查詢
- 獲取價格大于id為3的貨物的商品
- 用到了內查詢，獲取id為3的商品的單價，把結構傳給外查詢
- 在where子句中編寫子查詢，也可以在from或select子句中編寫。
use sql_store;
select *
from products
where unit_price > (select unit_pricefrom productswhere product_id = 3)
運行結果：
- 練習：查詢工資大于平均工資的員工
use sql_hr;
select *
from employees
where salary > (select avg(salary)from employees)
運行結果：
IN運算符
- in運算符寫子查詢
- 查詢沒有被訂購過的商品，先在子查詢中找出被訂購過的商品id（注意要去重）。將這個查詢結果作為子查詢，在外層找product_id不在子查詢結果里的數據，就是沒有被訂購過的商品。
use sql_store;
select *
from products
where product_id not in (select distinct product_idfrom order_items
)
- 練習：找到沒有支付過支票的客戶
- 從invoices中查詢去重后的clientid，將這個結果作為內查詢傳給外查詢，找不在這個內查詢結果中的id，就是沒有支付過支票的顧客
use sql_invoicing;
select *
from clients
where client_id not in (select distinct client_idfrom invoices
)
運行結果：
子查詢 VS 連接
- 在運行時間差不多的情況下，應該選擇最易讀的查詢，要注意代碼的可讀性！
- 上一個練習題，可以使用外連接進行查詢，但這樣寫可讀性不好。
select *
from clients
left join invoices using (client_id)
where invoice_id is null
- 練習
- 找到訂購了貨物id為3的顧客
- 這道題用 連接查詢 思路更清晰，可讀性更好
-- 用子查詢寫
use sql_store;
select customer_id, first_name, last_name
from customers
where customer_id in (select customer_idfrom order_itemsjoin orders using (order_id)where product_id = 3
)-- 使用連接查詢
select distinct customer_id, first_name, last_name
from customers
join orders using (customer_id)
join order_items using (order_id)
where product_id = 3
ALL關鍵字
- 查詢大于3號客戶的最大發票的所有數據
use sql_invoicing;
select *
from invoices
where invoice_total > (select max(invoice_total)from invoiceswhere client_id = 3)
- 用all關鍵字
- 查詢invoice_total比all后查詢到的所有數據都大的數據，一個一個的跟all后查詢到的結果進行比較
select *
from invoices
where invoice_total > all(select invoice_totalfrom invoiceswhere client_id = 3)
返回結果
- max寫法和all寫法可以相互改寫，兩種寫法的可讀性都較好
ANY關鍵字
- in 和 = any是等價的。
- 查詢至少有兩張發票的客戶id
- 使用count（*）查到所有的信息，根據client_id分組，分組后用having進行條件篩選
select client_id, count(*)
from invoices
group by client_id
having count(*) >= 2
- 把上述查詢當子查詢，把clients中至少有兩張發票的客戶信息查出來
- where子句中可以用in，也可以用 = any
- in 和 = any的效果是一樣的，用哪種都行
-- in
select *
from clients
where client_id in (select client_idfrom invoicesgroup by client_idhaving count(*) >= 2
)
-- = any
select *
from clients
where client_id = any (select client_idfrom invoicesgroup by client_idhaving count(*) >= 2
)
相關子查詢 !
- 查詢邏輯：先到employees表，對每個員工e執行這段子查詢，計算和e同一個部門的員工的平均工資，如果這名員工e的工資高于平均工資，就會被返回在結果中。依次一條一條的去查詢。
- 這種查詢成為相關子查詢，子查詢和外查詢存在相關性，引用了外查詢里出現的別名（即e）
- 使用相關子查詢時，這段子查詢會在主查詢每一行的層面執行，所以相關子查詢經常執行的很慢。
use sql_hr;
select *
from employees e
where salary > (select avg(salary)from employeeswhere office_id = e.office_id)
- 練習
- 查詢顧客大于自己平均值的數據
use sql_invoicing;
select *
from invoices i
where invoice_total > (select avg(invoice_total)from invoiceswhere client_id = i.client_id)
EXISTS運算符
- 獲取在發票表中有發票的客戶
- 三種寫法：子查詢，外連接，exists相關子查詢
- 用in，先將in后的子查詢運行結果返回給where。
- in后的子查詢會生成一個列表，返回給where。如果子查詢查到的過多，會導致列表特別大，這樣會妨礙最佳性能；對于這種情況，用exists能提高效率
select *
from clients
where client_id in (select distinct client_idfrom invoices)
- 用exists運算符，來查看發票表里是否存在符合這個條件的行
- 子查詢并沒有給外查詢返回一個結果，它會返回一個指令，說明這個子查詢中是否有符合這個搜索條件的行，每一行外層查詢的數據都到exists后去看是否存在；如果存在，子查詢就會給exists返回true，exists運算符就會在最終結果里添加當前的記錄。
select *
from clients
where exists(select client_idfrom invoiceswhere invoices.client_id = clients.client_id
);
運行結果
- 練習
- 找到從沒有被訂購過的商品
use sql_store;
select *
from products
where not exists(select product_idfrom order_itemswhere order_items.product_id = products.product_id
)
運行結果
select子句中的子查詢
- 在select子句中用子查詢得到平均值
- select語句中 在表達式中不能使用列的別名，這樣就只能把select子句中的子查詢再復制一遍，但是這樣很長很麻煩且重復；解決方法是：再轉換成一個子查詢（select invoice_average）
use sql_invoicing;
select invoice_id,invoice_total,(select avg(invoice_total)from invoices) as invoice_avearge,invoice_total - (select invoice_avearge) as difference
from invoices
運行結果
- 練習：得到每個客戶的總發票金額，全部發票的平均值，以及他們的差值
select client_id,name,(select sum(invoice_total)from invoiceswhere client_id = c.client_id) as total_sales,(select avg(invoice_total)from invoices) as average,(select total_sales) - (select average) as difference
from clients c
運行結果
from子句中的子查詢
- 可以把一段查詢生成的表當作另一個查詢的from
select *
from(
select client_id,name,(select sum(invoice_total)from invoiceswhere client_id = c.client_id) as total_sales,(select avg(invoice_total)from invoices) as average,(select total_sales) - (select average) as difference
from clients c
) as hahah
where total_sales is not null
運行結果
