8/3 my sql note

資料操作語言(2)

※ GROUP BY 子句
1.select 後面有現過,才可以使用
用徒：把所有在組合了的表達式上共享同樣的值的行壓縮成一行。群組的意思!!

範例1. 計算各家供應商各供應幾種商品

方法一：
select s.供應商, count(p.產品編號) as 產品筆數
from supplier  s , product p
where s.供應商編號 = p.供應商編號
group by s.供應商                      #群組的條件
order by 產品筆數 desc            #使用產品筆數作遞減排列
 方法二：
SELECT supplier.供應商, Count(product.產品) AS 產品之筆數

FROM supplier INNER JOIN product ON supplier.供應商編號 = product.供應商編號
GROUP BY supplier.供應商;
 
範例2. 計算各個客戶下了幾筆訂單
select c.公司名稱 , year(o.訂單日期) as 年份 , count(o.訂單號碼) as 訂單筆數

from customer c , ord o
where c.客戶編號 = o.客戶編號
and year(o.訂單日期)='1997'
group by c.公司名稱
having 訂單筆數 >9       #※ HAVING 子句
order by 訂單筆數 desc

範例3. 計算各家供應商各供商品的平均單價以及庫存量總計


select s.供應商, avg(p.單價) as 平均單價 , sum(庫存量) as 庫存量總計
from supplier s , product p
where s.供應商編號 = p.供應商編號
group by s.供應商
order by 庫存量總計 desc

#avg(p.單價) 計算的平均值是算術意義 (值總數除以值數目)
#sum(庫存量)函數會加總欄位中的值
※ HAVING 子句
類似 where子句但與where子句不同，WHERE 在使用 GROUP BY 之前過濾出單獨的行，而 HAVING 過濾由 GROUP BY 分組出來的資料．
必須放在group by 後面


範例1. 計算出供應超過3種商品的供應商
select s.供應商, count(p.產品編號) as 產品筆數
from supplier s , product p
where s.供應商編號 = p.供應商編號
group by s.供應商 #群組的條件
having  產品筆數 > 3

order by 產品筆數 desc #使用產品筆數作遞減排列


※ Count 函數
Count(expr)
expr 定位符號代表辨識您要計算之資料所在欄位的字串運算式
範例1.計算每個城市的客戶各有幾家
select 城市  , count(公司名稱) as 筆數
from customer
group by 城市
order by 筆數 desc
範例2.計算每個城市的供應商各有幾家

select 城市 , count(供應商) as 供應商之筆數
from supplier
group by 城市
order by 供應商之筆數 desc

範例3.計算每產品類別各有幾種產品
方法一：
select type.類別名稱,count(product.產品) as 產品之筆數
from type inner join product on type.類別編號 = product.類別編號
group by type.類別名稱
方法二：
select t.類別名稱 ,count(p.產品) as 產品筆數
from product p , type t
where p.類別編號 = t.類別編號
group by t.類別名稱
order by 產品筆數 desc

※ Min、Max 函數 

特定欄位的每一群組(min)最小值或(max)最大值。

範例1.查詢訂單中detail最小訂購量是多少

SELECT 訂單號碼, Min(數量) AS 數量之最小值
FROM detail
GROUP BY 訂單號碼;

範例2.每種產品類別,最低單價是多少?select t.類別名稱 ,min(p.單價) as 最低單價
from product p , type t
where p.類別編號 = t.類別編號
group by t.類別名稱
order by 最低單價 desc

範例3.個產品類別，最低的庫存量是多少
select t.類別名稱 ,min(p.庫存量) as 最低庫存量
from product p , type t
where p.類別編號 = t.類別編號
group by t.類別名稱
order by 最低庫存量 desc
 
範例4.查詢訂單中detail最大訂購量是多少

SELECT 訂單號碼, Max(數量) AS 數量之最大值
FROM detail
GROUP BY 訂單號碼;


範例5.每種產品類別,最高單價是多少?
select t.類別名稱 ,max(p.單價) as 最高單價
from product p , type t
where p.類別編號 = t.類別編號
group by t.類別名稱
order by 最高單價 desc

範例6.個產品類別，最低的庫存量是多少
select t.類別名稱 ,max(p.庫存量) as 最大庫存量
from product p , type t
where p.類別編號 = t.類別編號
group by t.類別名稱
order by 最大庫存量 desc

※ Sum 函數  

傳回一組值的總和，包含於查詢的指定欄位內。

需要新增的數字資料的欄位，或是識別使用該欄位中資料執行計算的運算式。
 
範例1.查詢各類別的庫存總價值=?
select t.類別名稱, sum(p.單價*庫存量) as 庫存總價值
from product p , type t
where p.類別編號 = t.類別編號
group by t.類別名稱
order by 庫存總價值 desc

※ 綜合練習題

 查1996年,各個客戶所下的訂單總金額

select c.公司名稱 ,year(o.訂單日期) as 年份 ,sum(d.單價*d.數量) as 訂單總金
from customer c, ord o , detail d
where c.客戶編號 = o.客戶編號 and
o.訂單號碼 = d.訂單號碼 and
year(o.訂單日期) = '1996'
group by c.公司名稱
order by 訂單總金額 desc
limit 5

CREATE VIEW(檢視)

可做出一份暫存的檢視表，像是一個放大鏡的效果。view不是即時的更新檔案，因此如需常常異動的檔案不適合使用。每次伺服器從開機，更新一次檔案。

範例1.新增一個view 以便快速透視位於台中市的供應商資料。
create view s_view(supplier,city,tel)
as
select 供應商,城市,電話
where 城市='台中市';

範例2.新增一筆資料到view之中
insert into s_view1(supplier,city,tel)
values('天天','台中市',20520)