3,編寫高階select語句（筆記）

1,使用group by 子句
group by 子句將表分為幾組，此子句通常與為每個這樣的組生產總結值的聚集函式組合。
使用不帶聚集的group by 子句與在select 子句中使用的distinct（或unique)關鍵字很相似。
select distinct customer_num from orders;
selecct customer_num from orders group by customer_num;

group by 子句將行收集到組中，因此每一組中的每一行具有相同的客戶號。在沒有選擇任何其它列的情況下，結果是唯一的customer_num值的列表。

select order_num,count(*) number,sum(total_price) price from items group by 1 order by 3;

select o.order_num,sum(i.total_price) from order o,items i where o.order_date > '01/01/98'
and ocustomer_num = 110 and o.order_num = i.order_num group by o.order_num;

使用having子句
要完成group by 子句，使用having 子句來在構成組之後將一個或多個限制條件應用於這些組。having子句對組的影響類似於
where 子句限制個別行的方式，使用having子句的一個優點是可以在搜尋條件中包括聚集，而在where子句的搜尋條件中卻不能包括聚集。

每個having條件將組的一列或一個聚集表示式與組的另一個聚集表示式或與常量作比較。可以使用having來對列值或組列表中的聚集值設定條件。

下面的語句返回具有兩個商品以上的訂單上每個商品的平均總價格。having子句在每個構成的測試組，並選擇由兩行以上構成的那些組。
select order_num,count(*) number,avg(total_price) average from items group by order_num having count(*) > 2;

如果不帶group by 子句使用having子句，則該having條件適應雨滿足搜尋條件的所有行。換言之，滿足搜尋條件的所有行構成一個組。
select avg(total_price) average from items having count(*) > 2;

select o.order_num,sum(i.total_price) price, paid_date - order_date span from orders o,items i
where o.order_date > '01/01/98'
and o.customer_num > 110
and o.order_num = i.order_num
group by 1,3
having count(*) < 5
order by 3
into temp temptab1;

建立高階連線

自連線
連線不一定都是涉及兩個不同的表。可以將表連線至它本身，纏結自連線。當想要將列中的值與同一列中的其它值進行比較時，將表連線
至它自身非常有用。

要建立自連線，在from 子句中列示表兩次，並且每次為它指定不同的別名。與表之間的連線一樣，可以在自連線中使用算術表示式。
可以測試空值，可以使用order by 子句來以升序或將序對指定列中的值進行排序。

select x.order_num,x.ship_weight,x,ship_date,y.order_num,y.ship_weight,y.ship_date from order x,order y
where x.ship_weight >= 5*y.ship_date
and x.ship_date is not null
and y.ship_date is not null
order by x.ship_date;

如果想要將自連線的結果儲存到臨時表中，則將Into temp子句追加到select語句中，並至少對一組列指定顯示標號，以重新命名這些列。否則，
重複列名將導致錯誤，並且不會建立臨時表。

select x.order_num orders1,x.po_num purch1,
x.ship_date ship1,y.order_num orders2,
y.po_num purch2,y.ship_date ship2
from order x,orders y
where x.ship_weight >= 5*y.ship_weight
and x.ship_date is not null
and y.ship_date is not null
order by orders1,orders2
into temp shipping;

自連線子句三次
select s1.manu_code,s2.manu_code,s3.manu_code,
s1.stock_num,s1.descripton
from stock s1,stock s2,stock s3
where s1.stock_num = s2.stock_num
and s2.stock_num = s3.stock_num
and s1.manu_code < s2.manu_code
and s2.manu_code < s3.manu_code
order by stock_num;

如果想要從payroll表選擇行來確定那些職員的薪水高於他們的經理，按照下面select 語句所示來構造自連線：
select emp.employee_num,emp.gross_pay,emp.level,
emp.detp_num,mgr.employee_num,mgr.gross_pay,
mgr.dept_num,mgr.level
from payroll emp,payroll mgr
where emp.gross_pay > mgr.gross_pay
and emp.level < mgr.level
and emp.dept_num = mgr.dept_num
order by 4;

使用相關子查詢來檢索並列示預定的10種價格最高的商品
select order_num,total_price
from items a
where 10 >
(select count(*)
from items b
where b.total_price < a.total_price )
order by total_price;

外連線
外連線使其中一個表成為控制表（也成為外部表），控制其它從屬表（也成為內部表）。
在內連線或簡單連線中，結果只保護滿足連線條件的行組合，廢棄部滿足連線條件的行。
在外連線中，結果包含滿足連線條件的行與控制表中的行（如果在從屬表中找不到匹配的行將廢棄這些行）的組合。
在從屬表中沒有相匹配的行的控制表在從屬表選擇的列中包含null值。

外連線允許在應用連線條件之前將連線過慮器應用於內部表。

ANSI外連線語法用left join,left outer join,right join和right outer join關鍵字開始外連線。outer關鍵字是可選的。
查詢可在on子句中指定連線條件和可選連線過慮器。where 子句指定後連線過慮器。

左外連線
在左外連線的語法中，外連線的控制表顯示在開始外連線的關鍵字的左邊。左外連線返回連線條件為true的所有行，除此之外，
還返回控制表中的所有其它行並將從屬表中的相應值顯示為Null。

select c.customer_num,c.lname,c.company,c.phone,u.call_dtime,u.call_descr
from customer c left outer join cust_calls u on c.customer_num = u.customer_num;

select c.customer_num,c.lname,c.company,c.phone,u.call_dtime,u.call_descr
from customer c left outer join cust_calls u
on c.customer_num = u.customer_num
where u.customer_num is null;

右外連線
在右外連線的語法中，外連線的控制表顯示在開始外連線的關鍵字右邊，右外連線返回連線條件為true的所有行，除此之外，
還返回控制表中的所有其它行並將從屬表中的相應值顯示為null

select c.customer_num,c.fname,c.lname,o.order_num,o.order_date,o.customer_num
from customer c right outer join orders o on (c.customer_num = o.customer_num);

簡單連線
select c.customer_num,c.lname,c.company,c.phone,u.call_dtime,u.call_descr from customer c,cust_calls u
where c.customer_num = u.customer_num;

對兩個表的簡單外連線
select c.customer_num,c.lname,c.company,c.phone,u.call_dtime,u.call_descr
from customer c,outer cust_calls u where c.customer_num = u.customer_num;

cust_calls表前面的附加關鍵字outer使該表成為從屬表。外連線導致查詢返回有關所有客戶的資訊，而不管他們是否已經致電客戶
服務中心，檢索控制表customer的所有行，並且將Null值指定給從屬表cust_calls的列。

與第三個表進行簡單連線的外連線
這種外連線也稱為巢狀簡單連線

select c.customer_num,c.lname,o.order_num,i.stock_num,i.manu_code,i.quantity
from customer c, left outer join (orders o,items i)
where c.customer_num = o.customer_num
and o.order_num = i.order_num
and manu_code in ('KAR','SHM')
ORDER BY lanme;

將兩個表與第三個表進行外連線
作為兩個表中的每一個與第三個表的外連線的結果的外連線。在此第三中型別的外連線中，連線關係可能僅僅使與
從屬表之間的關係。

select c.customer_num,c.lname,o.order_num,order_date,call_dtime
from customer c,outer orders o,outer cust_calls x
where c.customer_num = o.customer_num
and c.customer_num = x.customer_num
order by lname
into temp service;

組合外連線的連線
要實現多級巢狀，可以建立使用三種外連線型別的任何組合的連線。使用ansi語法，建立作為對兩個表與另一個外連線的簡單外連線
組合結果的連線。

select c.customer_num,c.lname,o.order_num,stock_num,manu_code,quantity
from customer c,outer (orders o,outer items i)
where c.customer_num = o.customer_num
and o.order_num = i.order_num
and manu_code in ('KAR','SHM')
ORDER BY lname;

select 語句中的子查詢

下列情況定義資料庫伺服器支援子查詢型別：
1，巢狀在另一個select 語句的投影列表中的select語句
2，巢狀在另一個select語句（或insert,delete或update語句中）的where子句中的select語句。

相關子查詢
相關子查詢使引用不在其from子句中的列或表的子查詢。該列可以在projection子句或在where子句中。要查詢查詢引用的表，搜尋不相關的列
直到找到相關為止。

projection子句中的子查詢
子查詢可以在另一個select語句的投影列表中發生。
select customer.customer_num,
(select sum(ship_charge)
from orders
where customer.customer_num = orders.customer_num)
as total_ship_chg
from customer;

where 子句中的子查詢
下來關鍵子在select語句的where子句中引入子查詢
all
any
in
exists

使用all
在子查詢前面使用關鍵字all來確定對返回的每個值的比較是否為true.如果字查詢不返回任何值，則搜尋條件為true。
（如果字查詢不返回任何值，則對於所有零值條件為true)

select order_num,stock_num,manu_code,total_price
from items
where total_price < all
(select total_price from items
where order_num = 1023);

使用any
在子查詢前使用關鍵字any(或其同義詞some)來確定是否對至少一個返回值的比較為true.如果子查詢不返回任何值，則搜尋
條件為false.(因為沒有值存在，所以對於其中一個值條件不能為true)

select distinct order_num from items where total_price > any
(select total_price from items where order_num = 1005);

單值子查詢
如果你指定子查詢可能對外部級別查詢返回剛好一個值，則不需要包括關鍵字all或any.可如同對待函式一樣對待函式一樣對待
只返回一個值的子查詢。這種子查詢通常使用聚集函式，原因是聚集函式總是返回單個值。

select order_num from items where stock_num = 9 and quantity =
(select max(quantity) from items where stock_num = 9);

相關子查詢
select po_num,ship_date from orders main where 10>
( select count (idstinct ship_date) from orders sub where sub.ship_date < main.ship_date) and
ship_date is not null
order by ship_date,po_num;

使用exists
關鍵字exists也稱為存在限定符，原因是僅當外部select找到至少一行時，子查詢才為true.
select unique manu_name,lead_time from manufact where exists
(select * from stock where description mantches '*shoe*'
and manufact.manu_code = stock.manu_code);

集合運算
標準集合運算聯合，相交和差異允許你出來資料庫資訊。這三種運算允許你使用select語句在執行更新，
插入或刪除之後檢查資料庫的完整性。

聯合
聯合運算使用union關鍵字或運算子來將兩個查詢組合成單個符合查詢。可以在兩個或多個select語句之間使用
union運算子來聯合它們，併產生一個臨時表，它包含存在於任何一個原始表或所有原始表中的行。還可以在檢視
的定義中使用union運算子。

例子：
select distinct stock_num,manu_code from stock where unit_price < 25.00 union
select stock_num,manu_code from items where quantity > 3;

select distinct stock_num,manu_code from stock where unit_price < 25.00 union
select stock_num,manu_code from items where quantity > 3 order by 2;

將order by 與 union 配合使用
select distinct stock_num,manu_code from stock where unit_price < 25.
union select stock_num,manu_code from items where quantity > 3 order by 2;

使用union all
預設情況下，union關鍵字排除重複的行。要保留重複的行，新增可選的關鍵字all

select stock_num,manu_code from stock where unit_price < 25.00
union all select stock_num,manu_code from items where quantity >3 order by 2 into temp stock item;

使用不同的列名
select distinct state from customer where customer_num between 120 and 15
union select distinct code from state where sname matches '*a';

將union與多個表配合使用
select stock_num,manu_code from stock where unit_price > 600.00 union all
select stock_num,manu_code from ctalog where catalog_num = 10025
union all select stock_num,manu_code from items where quantity = 10 order by 2;

相交
兩個行集的相交產生一個表，它包含同時存在於兩個原始表的行。使用關鍵字exists或in來引入顯示兩個集合相交的子查詢
select stock_num,manu_code,unit_price from stock where stock_num in ( select stock_num from items)
order by stock_num;

差異
兩個行集之間的差異產生一個表，它波包含在第一個行集中但不在第二個行集中的行，使用關鍵字not exists或not in 來
引入兩個集合之間的差異的子查詢。

select stock_num,manu_code,unit_price from stock where stock_num not in ( select stock_num from items)
order by stock_num;