《SQL进阶》PPT课件.ppt
1,Prentice Hall,2002,第8章：SQL進階,資料庫管理與應用第六版Jeffrey A.Hoffer,Mary B.Prescott,Fred R.McFadden,2,Prentice Hall,2002,處理多個表格 合併,合併 將兩個以上含有相同值域的表格結合成單一表格或視界的關聯式運算 等值合併 以相等的共同欄位值為合併條件的一種合併，共同的欄位會重複出現在結果表格中 自然合併 會消除結果表格中重複欄位的等值合併 外部合併 這種合併會將共同欄位值不相符的列也納入結果表格中（在內部合併中，則只有欄位值相符的資料列會出現在結果表格中）聯集合併 包含合併中所有表格的欄位，每個表格的每一列為一實例,所合併表格中的共同欄位，通常是在1:M關係中之主要表格的主鍵，以及相依表格的外來鍵,3,Prentice Hall,2002,重訪圖 7-3：松谷家具的樣本資料,顧客,訂單,訂單明細,產品,4,Prentice Hall,2002,對於每位有下訂單的顧客，他的姓名與訂單號碼為何？SELECT CUSTOMER_T.CUSTOMER_ID,CUSTOMER_NAME,ORDER_IDFROM CUSTOMER_T,ORDER_TWHERE CUSTOMER_T.CUSTOMER_ID=ORDER_T.CUSTOMER_ID,自然合併範例,5,Prentice Hall,2002,為CUSTOMER表格中的所有顧客，列出他們的顧客姓名、識別碼、及訂單號碼。即使沒有訂單的顧客，也請列出他們的顧客識別碼與姓名。SELECT CUSTOMER_T.CUSTOMER_ID,CUSTOMER_NAME,ORDER_IDFROM CUSTOMER_T,LEFT OUTER JOIN ORDER_TWHERE CUSTOMER_T.CUSTOMER_ID=ORDER_T.CUSTOMER_ID,外部合併範例,6,Prentice Hall,2002,準備為1006號訂單產生發票所需的資訊 SELECT CUSTOMER_T.CUSTOMER_ID,CUSTOMER_NAME,CUSTOMER_ADDRESS,CITY,SATE,POSTAL_CODE,ORDER_T.ORDER_ID,ORDER_DATE,QUANTITY,PRODUCT_NAME,UNIT_PRICE,(QUANTITY*UNIT_PRICE)FROM CUSTOMER_T,ORDER_T,ORDER_LINE_T,PRODUCT_TWHERE CUSTOMER_T.CUSTOMER_ID=ORDER_LINE.CUSTOMER_ID AND ORDER_T.ORDER_ID=ORDER_LINE_T.ORDER_ID AND ORDER_LINE_T.PROEUCT_ID=PRODUCT_PRODUCT_IDAND ORDER_T.ORDER_ID=1006;,多個表格的合併範例,7,Prentice Hall,2002,圖8-1 4個表格的合併結果,8,Prentice Hall,2002,處理多個表格-子查詢,子查詢=將內層查詢（SELECT敘述）置於外層查詢中選項：放入WHERE子句的條件中做為FROM子句中的表格放在HAVING子句中子查詢可以是：非關聯的 針對整個外層查詢執行一次相關聯的 針對外層查詢傳回的每一列執行一次,9,Prentice Hall,2002,顯示所有下過訂單的顧客？SELECT CUSTOMER_NAME FROM CUSTOMER_TWHERE CUSTOMER_ID IN(SELECT DISTINCT CUSTOMER_ID FROM ORDER_T);,子查詢範例,10,Prentice Hall,2002,相關聯的 vs.非關聯的子查詢,非關聯的子查詢：不必依賴外層查詢的資料 針對整個外層查詢執行一次相關聯子查詢：必須利用外層查詢的資料針對外層查詢的每一列執行一次可以利用EXISTS運算子,11,Prentice Hall,2002,圖 8-2(a)處理非關聯的子查詢,沒有參考外層查詢的資料，所以子查詢只執行一次,1.子查詢（顯示在方框中）會先處理，並建立中間的結果表格：,2.外層查詢會傳回包含在中間結果表格中每名顧客的顧客資訊：,12,Prentice Hall,2002,顯示所有包含天然梣木傢具的訂單SELECT DISTINCT ORDER_ID FROM ORDER_LINE_TWHERE EXISTS(SELECT*FROM PRODUCT_T WHERE PRODUCT_ID=ORDER_LINE_T.PRODUCT_ID AND PRODUCT_FINISH=Natural ash);,相關聯子查詢範例,13,Prentice Hall,2002,圖8-2(b)：處理相關聯子查詢,子查詢會參考外層查詢，所以會針對外層查詢的每一列執行一次,1.從ORDER_LINE_T:ORDER_ID=1001選取第一個訂單ID。2.評估子查詢，以判斷該訂單中是否有任何產品是要梣木外裝。Product 2是梣木外裝，並且是該筆訂單的一部份，所以EXISTS的值為true，而訂單ID會新增到結果表格中。3.從ORDER_LINE_T:ORDER_ID=1002選取下一個訂單ID。4.評估子查詢，以判斷該訂單中是否有任何產品是要梣木外裝。結果的確如此，所以EXISTS的值為true，而訂單ID會新增到結果表格中。5.繼續處理每筆訂單ID。訂單1004、1005、與1010並沒有放入結果表格中，因為它們之中並沒有具有梣木外觀的傢具。最後的結果表格顯示於子查詢一節中的最後一個範例。,14,Prentice Hall,2002,顯示所有包含天然梣木傢具的訂單 SELECT PRODUCT_DESCRIPTION,STANDARD_PRICE,AVGPRICEFROM(SELECT AVG(STANDARD_PRICE)AVGPRICE FROM PRODUCT_T),PRODUCT_TWHERE STANDARD_PRICE AVG_PRICE;,子查詢範例 使用衍生性表格,15,Prentice Hall,2002,確保異動完整性,異動=必須一起做完、或完全都不要做的獨立工作單位可能涉及多個更新如果有任何更新失敗，其他所有的更新都必須取消異動的SQL命令BEGIN TRANSACTION/END TRANSACTION標示異動的疆界COMMIT產生永久的變動ROLLBACK取消從上次COMMIT到目前為止的更新,16,Prentice Hall,2002,圖8-4：SQL的異動序列（虛擬程式碼）,新增有效的資訊 COMMIT工作,資料的所有變動成為永久性資料,輸入無效的Product_ID,異動會被ABORTED ROLLBACK對Order_t所作的所有變動,對Order_t與Order_line_t所作的所有變動都被移除。資料庫狀態如同異動開始之前一樣。,17,Prentice Hall,2002,資料字典工具,儲存metadata的系統表格通常允許使用者可以看到其中的一些表格限制使用者不能更新Oracle8i的範例DBA_TABLES 描述表格 DBA_CONSTRAINTS 描述限制DBA_USERS 關於系統使用者的資訊DBA_TAB_PRIVS 描述資料庫物件的權限,18,Prentice Hall,2002,SQL-99的改良/延伸,使用者自定資料型態(UDT)標準型態的子類別或是物件的資料型態分析函式(供OLAP使用)駐留性儲存模組(SQL/PSM)建立與移除程式模組的能力新的敘述：CASE,IF,LOOP,FOR,WHILE等讓 SQL 成為程序性語言SQL-99 標準尚未被廣泛採用Oracle 的專屬版本稱為 PL/SQL,19,Prentice Hall,2002,觸發程序與常式,常式被呼叫才會執行的程式模組函式 會傳回值並且有輸入參數的常式程序 不傳回值並且可以有輸入或輸出參數的常式觸發程序 為了回應資料庫事件而被執行的常式(INSERT、UPDATE、或DELETE),20,Prentice Hall,2002,圖8-5：觸發程序與內儲程序的比較,必須被另行呼叫的程序,由事件驅動的觸發程序,Source:adapted from Mullins,1995.,21,Prentice Hall,2002,圖8-6：Oracle PL/SQL的觸發程序語法,圖8-7：SQL-99建立常式的語法,22,Prentice Hall,2002,內嵌式與動態SQL,內嵌式SQL將固定的SQL敘述寫入以其他語言（如C或Java）所撰寫的程式中動態SQL 讓應用在執行時，能夠產生特定SQL程式碼的能力,
