基本资料查询

1、第八章基本資料查詢 1Chapter 8 Outline8-1:示範資料庫結構介紹 8-2:切換資料庫(USE) 8-3:選取資料(SELECT)的基本操作 8-4:從兩個以上的資料表選取資料(JOIN) 8-5:子查詢 8-6:使用UNION運算元 8-7:本章總結 28-1示範資料庫結構介紹 示範資料庫(TradeDB)中具有6張資料表，分別為訂單資料主/細檔(orders/items)客戶基本資料(customers)產品基本資料(products)供應商基本資料(suppliers) 縣市代碼(hsienshi) 3訂單資料表資料表名稱：orders欄位名稱欄位簡述欄位資料型態orde

2、r_num訂單號碼int/PKorder_date訂購日期datetimecust_num客戶代碼int/FKpaid_date付款日期datetime客戶資料表4訂單項目資料表資料表名稱：items欄位名稱欄位簡述欄位資料型態item_num訂單項目號碼int/PKorder_num訂單號碼int/PK /FKprod_num產品號碼int/FKsupp_code供應商代碼char(3)/FKunit_price單價moneyquantity單項訂購數量inttotal_price單項總價money產品資料表訂單資料表5客戶資料表資料表名稱：customers欄位名稱欄位簡述欄位資料型態cu

3、st_num客戶編號int/PKboss_name負責人姓名char(16)cust_name公司名稱char(20)hsienshi_code所屬縣市碼char(3)/FKaddress公司地址char(30)phone電話號碼char(12)縣市資料表6產品資料表資料表名稱：products欄位名稱欄位簡述欄位資料型態prod_num產品號碼int/PKsupp_code供應商代碼char(3)/PK/FKprod_num產品名稱char(24)unit_price產品單價money供應商資料表7供應商資料表資料表名稱：suppliers欄位名稱欄位簡述欄位資料型態supp_code供應商

4、代碼char(3)/PKsupp_name供應商名稱char(15)縣市資料表資料表名稱：hsienshi欄位名稱欄位簡述欄位資料型態hsienshi_code縣市代碼char(3)/PKhsienshi_name縣市名稱char(10)88-2 切換資料庫(USE)資料是放在個別的資料庫中，所以需要使用正確的資料庫方能查詢正確的資料，例如要切換到TradeDB資料庫則可使用use TradeDB。如果只是暫時選取某個資料庫的資料則可以使用db_name.owner.obj_name方式，例如：TradeDB.dbo.orders98-3 選取資料的基本操作 SQL的select敘述主要功能是

5、作查詢處理，它是由一些子句所組成的，其結構如下：Select 子句Into 子句From 子句Where 子句Group By子句Having 子句Order by 子句108-3 選取資料的基本操作(Select) 選取所有的欄位(*代表所有欄位)範例8-3.1：請查詢供應商資料表內所有的資料 select * from suppliers選取特定的欄位(需指出正確的欄位名稱)範例8-3.2：請查詢所有經銷商的公司名稱與負責人姓名 select company, boss_name from distributors118-3選取資料的基本操作 (Select) 選取唯一的數值(使用DIST

6、INCT)範例8-3.3：請查詢已經下訂單的經銷商號碼，但是為了畫面精簡起見，請去除重複的經銷商號碼。select distinct distri_num from orders128-3選取資料的基本操作 (WHERE)通常稱之為“選取”資料就是要經過篩選，最基本的篩選方式就是使用WHERE子句。WHERE子句中可以使用的條件限制有很多種，包括(等於=)、不等於(!=)、大於()、小於(=)、小於等於(=)、介於與之間(between and)、屬於(in) 、類似(Like %)等。上述所列可以運用在數值資料也可用在字串資料，只是字串資料需要加上單引號。除了比較之外，還可以使用 and 、

7、or來連接多個條件138-3選取資料的基本操作 (WHERE)範例8-3.4：請查詢供應商代碼為HHT供應商所有的相關資料 select * from suppliers where supp_code = HHT範例8-3.5：請查詢供應商代碼不為HHT的供應商所有的相關資料 select * from suppliers where supp_code != HHT148-3選取資料的基本操作 (WHERE)範例8-3.6：請查詢單價介於2000元與3000元之間所有產品的產品名稱與單價資料 select prod_name,unit_price from products where u

8、nit_price between 2000 and 3000 範例8-3.7：請查詢客戶代碼為1018、1014、1006、1001或1027的客戶資料，我們所關心的資訊為客戶代碼、負責人姓名以及公司名稱 select cust_num, boss_name, cust_name from customers where cust_num in (1018, 1014, 1006, 1001, 1027) 158-3選取資料的基本操作 (WHERE)範例8-3.8：請查詢尚未付款客戶的所有資料 select * from orders where paid_date is null範例8-3

9、.9：請查詢已付款客戶的所有資料 select * from orders where paid_date is not null 範例8-3.11：想查詢地址位於南京一帶的客戶資料。 select * from customers where address like 南京%在SQL敘述中 %表示任意長度的字串，並非使用*如果要查詢地址中有三段二字的客戶要如何查呢？168-3 選取資料的基本操作 (WHERE)範例8-3.12：請查詢供應商代碼第一、第三位分別為H與T所有供應商的資料select * from suppliers where supp_code like H_T在SQL中，%

10、表示任意長度的字串，而以_(底線)表示單一長度的任意字串。H_T表示長度為3，而且第一、第三位分別為H與T 的所有字串，但要注意的是關鍵字還是必須使用LIKE而不是=，因為如果使用=會讓系統以為您是要找與H_T一樣的字串。 178-3選取資料的基本操作(ORDER BY)想要依某鍵值來排序則可使用order by範例8-3.13：請查詢所有產品的產品號碼、供應商代碼、產品名稱以及單價資訊，並請依單價由小至大加以排序。 select prod_num, supp_code, prod_name, unit_price from products order by unit_price asc其中

11、asc表示由小到大排序，為預設方法，所以也可以不用指定；如果想要由大到小排序，則請用 desc188-3 選取資料的基本操作在選取資料時可以為欄位命名範例8-3.15：請顯示產品價格與加上稅金5%後的價格，由於在輸出結果中，虛擬欄位並沒有欄位名稱 。為了易於閱讀起見，請為該虛擬欄位加上欄位名稱TaxAmount。 select prod_num, prod_name, unit_price,unit_price * 1.05 TaxAmount from products order by unit_price使用標籤除了能為虛擬欄位命名，也可以為一般欄位命名以改變輸出結果的標題，而且新的命名

12、也可以用於WHERE與ORDER BY子句中。 198-3 選取資料的基本操作(Into)使用SELECT INTO的方式可以用來建立新資料表，而且資料表欄位名稱就是選取的資料欄位名稱；而資料型態也會與選取表格的資料型態一樣。範例4-6.1：請將產品號碼、供應商代碼、產品名稱以及稅後價格(加5%)建立一名為productstemp的暫存表格select prod_num, supp_code, prod_name,unit_price * 1.05 Tax_Amount into productstemp from products 208-4從兩個以上的資料表選取資料(合併/JOIN) 將資

13、料表無損分解成多個較小的資料表之後就要靠合併的技巧查詢資料將之還原。合併的形式有很多種 ，包括：Inner JoinOuter Join左方外部合併（Left Outer Join） 右方外部合併（Right Outer Join） 完全外部合併（Full Outer Join） Cross JoinSelf Join 218-4 合併 Inner JOIN內部合併作常用的方法為等價合併(Equal Join)等價合併的做法就是在兩資料表間使用某一個鍵值最為合併的等價條件，然後將相同值的資料錄合併起來。JOIN是很浪費系統資源的查詢，但是這又是正規化的必然結果，所以最好解決方式就是使用索引輔助

14、 Equal JOIN運作示意圖 22TABLE A 欄位A1 A2abcSQLMySQLAccess8-4 合併 Inner JOINTABLE B 欄位B1 B2aacJohnDavidWangcPeterSelect A.A1,A.A2,B.B2 from A,B where A.A1=B.B1 A1 A2 B2aacJohnDavidWangcPeterAccessSQLSQLAccess238-4 合併 Inner JOIN範例8-4.1：請查詢每一客戶所有的訂單資料，列出客戶代號、公司名稱、電話號碼、訂單號碼以及訂貨日期，並以客戶代號由小至大排序。 select customers

15、.cust_num, cust_name, phone,order_num, order_date from customers, orderswhere customers.cust_num = orders.cust_num order by 1248-4 合併 Inner JOIN範例8-4.1a：使用INNER JOIN方式改寫範例8-4.1。 select customers.cust_num, cust_name, phone,order_num, order_date from customers INNER JOIN orders ON customers.cust_num =

16、 orders.cust_num order by 1 該段SQL敘述意義為將customers 與 orders 在cust_num相同的條件下作INNER JOIN，上述的INNER可以省略258-4 合併 Inner JOINInner Join可以合併多個資料表，但是合併太多會有效率與資源上的問題。範例8-4.3：請查詢代號為1004的客戶之所有訂單的資料，並列出客戶代碼、客戶名稱、訂單號碼、項目號碼以及單項總價。select customers.cust_num, cust_name,orders.order_num, item_num,total_price from custom

17、ers, orders, items where customers.cust_num = orders.cust_num and orders.order_num = items.order_num and customers.cust_num = 1004 268-4 合併 Inner JOIN選取多個資料表時由於必須說明資料表的名稱，過長的資料表名稱造成撰寫SQL敘述的困擾，此時就可以使用別名的方式。範例8-4.4：以定義資料表別名的方式改寫請參考範例8-4.3 。select c.cust_num, o.order_num, item_num, total_pricefrom cust

18、omers c, orders o, items i where c.cust_num = o.cust_num and o.order_num = i.order_num and c.cust_num = 1004 278-4 合併 Outer JOIN內部合併只會列出存在於兩個資料表內的資料 。外部合併會列出只存在於一資料表的資料 Outer Join又可分為Left Outer JoinRight Outer JoinFull Outer Join。 288-4 合併 Outer JOIN範例8-4.6：請查詢所有客戶的下單明細(包括尚未下單的客戶)，列出客戶代碼碼、負責人姓名以及訂單號

19、碼。 select customers.cust_num, boss_name, order_num from customers left outer join orders on customers.cust_num = orders.cust_num 範例8-4.7：使用right outer join改寫範例8-4.6select customers.cust_num, boss_name, order_num from orders right outer join customers on customers.cust_num = orders.cust_num 298-4 合併F

20、ULL Outer Join ：就是Left Outer Join與Right Outer Join的聯集 Cross Join ：就是兩個資料表作乘積列出來而已，例如資料表A有n筆資料，資料表B有m筆資料，則 A Cross Join B就有n * m筆資料SELF JOIN ：資料表Join的對象就是自身308-5 子查詢(Subquery) 如果一查詢的結果是一個數值，則我們可以將查詢的結果當作WHERE子句的比對條件，而成為一個附屬的SELECT敘述，我們稱該種查詢為子查詢 範例8-5.1：請查詢與負責人為張江悅的經銷商在同一縣市的所有經銷商的資料。select * from distributors where hsienshi_code = （select hsienshi_codefrom distributorswhere boss_name = 張江悅） 318-6 使用Union運算元 當需要從不同的資料表或是使用不同的方法擷取相同格式的資料，而且希望將所選取的資料整合在一起時可以使用UNION運算元。範例8-6.1：請