sql必知必會2

将之前學習的資料庫知識在整理下，主要是看的**《SQL必知必會》**。這本書不愧是經典，入門資料庫真的完全足夠啦！

分組資料

group by

分組資料主要是靠

group by

和

having

子句來實作的。

select vend_id, count(*) as num_prods   -- * 表示統計總數，起别名
from products
group by vend_id           

複制

group by

子句的相關規定：

1. 子句中可以包含任意數目的列
2. 子句中列出的每列都必須是檢索列或者有效的表達式，不能是聚集函數
3. 子句中不可帶有長度可變的資料類型
4. 如果分組列中含有NULL行，不管是一行還是多行，都當做一行進行傳回
5. group by子句必須在where語句之後，在order by子句之前

過濾分組having

where指定的過濾行而不是分組；having支援所有的where操作符。

• where過濾行
• having過濾分組

select cust_id, count(*) as orders
from orders
group by cust_id   -- 先分組
having count(*) >= 2;  -- 再過濾分組           

複制

筆記：where在分組前進行過濾；having在資料分組後進行過濾

select vend_id, count(*) as num_prods
from products
where prod_price >= 4   -- 過濾價格
group by vend_id
having count(*) >= 2;  -- 過濾分組之後的計數           

複制

筆記：having和group by應該結合使用；where子句值标準的行級過濾。

Order by 和group by 差別

Order by	group by
對産生的輸出排序	對行分組，但是輸出可能不是分組的順序
任意的列都可以使用	隻能使用選擇列或者表達式列
不一定需要	如果是和聚合函數一起使用列（表達式），必須使用

大多數情況下，group by分組的資料确實是以分組順序輸出的。

group by和order by子句最好結合使用

select order_num, count(*) as items
from orderitems
group by order_num    --分組資料
having count(*) >= 3  -- 篩選
order by items, order_num  -- 将分組之後的結果進行排序，預設是升序           

複制

select子句順序

1. select
2. from
3. where
4. group by
5. having
6. order by

子查詢

利用子查詢進行過濾

select cust_id  -- 2. 從已經查詢出的order_num查詢出cust_id
from orders
where order_num in (
	select order_num   -- 1. 先查詢出order_num
  from orderitems
  where prod_id = 'RGANO1'
)           

複制

筆記：

1. 子查詢總是從裡向外執行；
2. 将子查詢分解成多行，同時進行适當的縮進
3. 不能嵌套太多的子查詢

建立計算字段使用子查詢

select cust_name,
			 cust_state,
			 (select count(*)   -- 統計總數
       from orders
       where orders.cust_id=customers.cust_id) as orders  --子查詢建立計算字段orders
from customers
order by cust_name;           

複制

筆記：在select語句中操作多個表，應該使用完全限制列名

orders.cust_id=customers.cust_id

來避免歧義。

聯結表

SQL

最強大的功能之一就是在查詢的過程中使用聯結表。聯結是一種機制，用來在一條

select

語句中關聯表。

建立聯結

select vend_name, prod_name, prod_price
from vendors, products   -- 需要聯結的兩個表
where vendor.cust_id = products.cust_id  -- 通過兩個表中的相同字段進行聯結           

複制

筆記：必須有where子句。如果沒有，則傳回的是笛卡爾積（沒有聯結條件的表傳回的結果，有時候也稱之為叉聯結cross join）。

内連接配接inner join

内連接配接也叫等值連接配接，基于兩個表之間的等值測試。

select vend_name, prod_name, prod_price
from Vendors inner join Products   -- 内連接配接：inner join ... on ...
on Vendors.vend_id = Products.vend_id           

複制

進階連接配接

使用表别名

select cust_name, cust_contact
from customers as C, orders as O, orderitems as OI   -- 内連接配接的表起别名
where C.cust_id = O.cust_id
and OI.order_num = O.order_num
and prod_id = 'RGAN01';           

複制

自連接配接

要求：給和Jim Jones同一公司的所有顧客發郵件

子查詢實作

select cust_id, cust_name, cust_contact   -- 2. 再從customers表中找出該公司的相關人員資訊
from customers
where cust_name = (select cust_name   -- 1. 利用子查詢先鎖定JJ工作的公司名字
  								 from Customers
 									 where cust_contact = 'Jim Jones');           

複制

自連接配接實作

select C1.cust_id, C1.cust_name, C1.cust_contact
from customers as C1, customers as C2
where C1.cust_name = C2.cust_name
and C2.cust_contact = 'Jim Jones';           

複制

外連接配接

外連接配接中包含了那些在相關表中沒有關聯行的行，包含兩種情況：

• 左連接配接
• 右連接配接

select customers.cust_id, orders.order_num
from customers left outer join orders   -- 左連接配接
on customers.cust_id = orders.cust_id;  -- customers表在前面

select customers.cust_id, orders.order_num
from customers right outer join orders   -- 右連接配接
on orders.cust_id = customers.cust_id;           

複制

在使用outer join時候，必須指定left或者right關鍵。

帶有聚集函數的連接配接

select customers.cust_id,
			 count(order.order_num) as num_ord  -- 篩選兩個資訊，一個帶上别名
from customers inner join orders  -- 通過内連接配接方式
on customers.cust_id = orders.cust_id   -- 連接配接的條件指定
group by customers.cust_id;  -- 分組條件指定           

複制

總結

1. 一般使用内連接配接，外連接配接也是有效的
2. 提供連接配接條件，否則得到的是笛卡爾積
3. 一個聯結中可以包含多個表；每個聯結可以采用不同的聯結類型