實作練習 (續)

在實作練習中，我們建立了四個表單，各是~ 客戶資料表 (customer)、產品資料表(product)、訂單表頭資料表(order_head)、訂單表身資料表(order_body)。

現在我們來練習SQL command的SELECT用法。

SELECT [fields list] FROM [tables list]
JOIN [tables] ON [conditions]
WHERE [conditions]
GROUP BY [columns]
HAVING [conditions]
ORDER BY [columns]

(1) 列出客戶資料表 (customer)中的所有客戶代號、客戶名稱。

SELECT cus_no, cus_name FROM customer;

(2) 找到客戶代號為c01的客戶地址。

SELECT cus_address FROM customer WHERE cus_no='c01';

(3) 找到客戶名稱中存在"科技"字串的所有客戶地址。

SELECT cus_address FROM customer WHERE cus_name like '%科技%';

(4) 找到客戶名稱中以"科技"字串結尾的所有客戶地址。

SELECT cus_address FROM customer WHERE cus_name like '%科技';

(5) 透過GROUP BY找某類型的總額~ SELECT "欄位1", SUM("欄位2") FROM "表格名" GROUP BY "欄位1"

SELECT ordb_prod_id, SUM(ordb_amount) FROM order_body
GROUP BY ordb_prod_id;

(6) 透過HAVING限定函數條件
SELECT "欄位1", SUM("欄位2") FROM "表格名" GROUP BY "欄位1" HAVING (函數條件)
如果被 SELECT 的只有函數欄， 那就不需要 GROUP BY 子句。

SELECT ordb_prod_id, SUM(ordb_amount) FROM order_body
GROUP BY ordb_prod_id
HAVING SUM(ordb_amount) > 500;

(7) 透過ORDER BY做排序

SELECT ordb_prod_id, SUM(ordb_amount) FROM order_body
GROUP BY ordb_prod_id
ORDER BY SUM(ordb_amount) DESC; //大到小排序

SELECT ordb_prod_id, SUM(ordb_amount) FROM order_body
GROUP BY ordb_prod_id
ORDER BY SUM(ordb_amount) ASC; //小到大排序, 這是預設的排序方式

(8) 使用JOIN ~ 找到訂購產品編號是1的客戶編號

SELECT ord_cus_id
FROM order_head
JOIN order_body
ON order_head.ord_id=order_body.ordb_ord_id
WHERE order_body.ordb_prod_id=1;

但是我們能夠印出上述條件的客戶名稱嗎? 如下的指令會如何呢?
SELECT cus_name FROM customer
WHERE cus_id = ( SELECT ord_cus_id FROM order_head JOIN order_body ON order_head.ord_id = order_body.ordb_ord_id WHERE order_body.ordb_prod_id =1 )

跟這個有何不同呢?
SELECT cus_name FROM customer
WHERE cus_id in ( SELECT ord_cus_id FROM order_head JOIN order_body ON order_head.ord_id = order_body.ordb_ord_id WHERE order_body.ordb_prod_id =1 )

JOIN比較簡單的例子:
album (album_id, album_title, album_artist)
track (track_id, album_id, song)

要找到某首歌的演唱者是誰? 因為song跟album_artist分屬不同的表單，所以必須查詢兩個table。

SELECT album_artist
FROM album
JOIN track ON album.album_id=track.album_id
WHERE track.song='歌名';

或是

SELECT album_artist
FROM album, track
WHERE album.album_id=track.album_id AND track.song='歌名';

(9) 如何讓同一張訂單，可以分批出貨，或是分批付款呢?

更多參考:
http://www.1keydata.com/tw/sql/sql.html
http://www.dbabeta.com/2011/visual-sql-joins.html
http://www.enet.com.cn/article/2006/1123/A20061123303667.shtml
http://www.study-area.org/coobila/tutorial_381.html
http://www.plus2net.com/sql_tutorial/sql_like.php