结构化查询语言SQL(一).ppt

第六章 結構化查詢語言SQL(一)檔案系統(File System)關聯式資料模型(Relational Data Model)關聯式資料庫管理系統(RDBMS)物件導向之類別圖(UML-Class Diagram)檔案(File)關聯(Relation)資料表(Table)類別(Class)欄位(Fields)屬性(Attribute)行(Column)屬性(Attribute)紀錄(Record)值組(Tuple)列(Row)物件(Object)/實例(Instance)相關名詞比較表一：相關名詞比較6-26-2資料定義語言(DDL)(DDL)定義綱要(Schema)定義資料表(Tables)(Tables)6-36-3資料操作語言(DML)(DML)新增操作(Inert Operation)(Inert Operation)刪除操作(Delete Operation)(Delete Operation)更新操作(Update Operation)(Update Operation)查詢操作(Select Operation)(Select Operation)3/107定義資料表之基本語法5/107圖6-4 資料庫ERD之主從關係建建立立順順序序(由由上上而而下下)【題目】依圖6-4定義出所有之資料表1)員工2)客戶3)供應商4)產品類別5)產品資料6)訂單7)訂單明細【說明】定義資料表的基本語法在第一欄為資料表之屬性，再定義每個屬性的資料型態，資料型態之後即是該屬性的限制。範例 6-3【員工】資料表之限制說明在員工資料表中，員工編號為此資料表之主要鍵(Primary Key)，姓名之屬性值不得為空值(Null Value)。1)建立建立【員工】資料表【語法】(語法一)將員工編號為主要鍵之限制寫在下方的table level區，並將姓名不得為空值之限制，直接寫在姓名屬性後方。(語法二)將員工編號為主要鍵之限制直接寫在員工編號屬性後方。【客戶】資料表之限制說明在客戶資料表中，客戶編號為此資料表之主要鍵(Primary Key)，公司名稱之屬性值不得為空值(Null Value)。2)建立建立【客戶】資料表【語法】(語法一)將客戶編號為主要鍵之限制寫在下方的table level區，並將公司名稱不得為空值之限制，直接寫在公司名稱屬性後方。(語法二)將客戶編號為主要鍵之限制直接寫在客戶編號屬性後方。【供應商】資料表之限制說明在供應商資料表中，除了供應商編號為此資料表之主要鍵(Primary Key)之外，此資料表中的地址和電話屬性，不得同時為空值(Null Value)，也就是至少要有一個屬性有值。【語法】(語法一)將供應商編號為主要鍵之限制寫在下方的table level區，並將地址和電話屬性，不得同時為空值(Null Value)之限制，寫於table level區。(語法二)將供應商編號為主要鍵之限制直接寫在供應商編號屬性後面，但將地址和電話屬性，不得同時為空值(Null Value)之限制，仍要寫於table level區，不得寫於個別的屬性後方。倘若將此限制寫於個別屬性後方，其語意將成為地址和電話兩個屬性皆不可為空值(Null Value)。3)建立建立【供應商】資料表【語法】(語法一)將供應商編號為主要鍵之限制寫在下方的table level區，並將地址和電話屬性，不得同時不得同時為空值(Null Value)之限制，寫於table level區。(語法二)將供應商編號為主要鍵之限制直接寫在供應商編號屬性後面，但將地址和電話屬性，不得同時不得同時為空值(Null Value)之限制，仍要寫於table level區，不得寫於個別的屬性後方。倘若將此限制寫於個別屬性後方，其語意將成為地址和電話兩個屬性皆不可皆不可為空值(Null Value)。【產品類別】資料表之限制說明在產品類別資料表中，類別編號為此資料表之主要鍵(Primary Key)，類別名稱之屬性值不得為空值(Null Value)。【語法】(語法一)將類別編號為主要鍵之限制寫在下方的table level區，並將類別名稱不得為空值之限制，直接寫在類別名稱屬性後方。(語法二)將類別編號為主要鍵之限制直接寫在類別編號屬性後方。4)建立建立【產品類別】資料表【語法】(語法一)將類別編號為主要鍵之限制寫在下方的table level區，並將類別名稱不得為空值之限制，直接寫在類別名稱屬性後方。(語法二)將類別編號為主要鍵之限制直接寫在類別編號屬性後方。語法一?語法二?【產品資料】資料表之限制說明在產品資料的資料表中，產品編號為此資料表之主要鍵(Primary Key)，類別編號為此資料表中一個外來鍵，參考產品類別資料表中的主要鍵類別編號；供應商編號為此資料表中另一個外來鍵，參考供應商資料表中的主要鍵供應商編號；並且庫存量和安全存量必須設預設值 0。【語法】(語法一)將產品編號為主要鍵及類別編號和供應商編號為兩個外來鍵之限制寫在下方的table level區，並將庫存量和安全存量設預設值為0之限制，直接寫在兩個屬性後方。(語法二)將產品編號為主要鍵及類別編號和供應商編號為兩個外來鍵之限制直接寫在個別屬性後方。5)建立建立【產品資料】資料表(語法一)將產品編號為主要鍵及類別編號和供應商編號為兩個外來鍵之限制寫在下方的table level區，並將庫存量和安全存量設預設值為0之限制，直接寫在兩個屬性後方。(語法二)將產品編號為主要鍵及類別編號和供應商編號為兩個外來鍵之限制直接寫在個別屬性後方。【訂單】資料表之限制說明在訂單資料表中，訂單編號為此資料表之主要鍵(Primary Key)，員工編號為此資料表中一個外來鍵，參考員工資料表中的主要鍵員工編號；客戶編號為此資料表中另一個外來鍵，是參考客戶資料表中的主要鍵客戶編號。【語法】(語法一)將訂單編號為主要鍵及員工編號和客戶編號為兩個外來鍵之限制寫在下方的table level區。(語法二)將訂單編號為主要鍵及員工編號和客戶編號為兩個外來鍵之限制直接寫在個別屬性後方。6)建立建立【訂單】資料表(語法一)將訂單編號為主要鍵及員工編號和客戶編號為兩個外來鍵之限制寫在下方的table level區。語法二?(語法二)將訂單編號為主要鍵及員工編號和客戶編號為兩個外來鍵之限制直接寫在個別屬性後方。【訂單明細】資料表之限制說明訂單編號+產品編號兩個屬性結合成為此資料表之主要鍵(Primary Key)。單一個產品編號屬性又剛好為此資料表中一個外來鍵，參考訂單資料表中的主要鍵訂單編號，除了要達到參考完整性限制外，若有違反此限制時，採用連鎖刪除(DELETE CASCADE)和連鎖更新(UPDATE CASCADE)；產品編號為此資料表中另一個外來鍵，參考產品資料資料表中的主要鍵產品編號。除此之外，對於數量屬性也將限制必須大於0。ON DELETE CASCADE 指定若有人嘗試刪除包含了索引鍵的資料列，而該索引鍵又被其他資料表的現有資料列中的外部索引鍵所參考時，包含這些外部索引鍵的所有資料列也會被刪除。ON UPDATE CASCADE 指定若有人嘗試更新資料列中的索引鍵值，而該索引鍵又被其他資料表的現有資料列中的外部索引鍵所參考時，所有組成該外部索引鍵的值也會更新成指定給該索引鍵的新值。7)建立建立【訂單明細】資料表(語法一)將所有的限制全部寫在下方的table level區。但是在外來鍵的限制方面，本資料表共有兩個外來鍵，一為訂單編號參考訂單資料表的訂單編號，且要求在違反限制時，將使用連鎖刪除和連鎖更新，所以在其參考語法後方要再加上選項ON DELETE CASCADE ON UPDATE CASCADE。另一個外來鍵為產品編號參考產品資料資料表的產品編號，由於沒有特別限制在違反時的處理方式，預設沒有任何選項時，則為拒絕刪除和拒絕更新。最後為一個數量的限制必須大於0。ON DELETE NO ACTION|CASCADE|SET NULL|SET DEFAULT ON UPDATE NO ACTION|CASCADE|SET NULL|SET DEFAULT 若沒有指定 ON DELETE ON DELETE 或 ON UPDATEON UPDATE，NO ACTION NO ACTION 將會是預設值。ON DELETE NO ACTION 指定若有人嘗試刪除包含了索引鍵的資料列，而該索引鍵又被其他資料表的現有資料列中的外部索引鍵所參考，此動作就會產生錯誤，而 DELETE DELETE 陳述式則會復原。ON UPDATE NO ACTION 指定若有人嘗試更新資料列中的索引鍵值，而該索引鍵又被其他資料表的現有資料列中的外部索引鍵所參考，此動作就會產生錯誤，而 UPDATE UPDATE 陳述式則會復原。CASCADECASCADE、SET NULL SET NULL 和 SET DEFAULT SET DEFAULT 可讓索引鍵值的刪除或更新動作，影響已定義成擁有外部索引鍵關聯性的資料表，以便追蹤回執行修改的資料表。若串聯式參考動作也已定義於目標資料表中，指定的串聯式動作也會作用於這些被刪除或更新的資料列。CASCADE CASCADE 不能針對具有 timestamp timestamp 資料行的任何外部索引鍵或主索引鍵來指定。ON DELETE CASCADE 指定若有人嘗試刪除包含了索引鍵的資料列，而該索引鍵又被其他資料表的現有資料列中的外部索引鍵所參考時，包含這些外部索引鍵的所有資料列也會被刪除。ON UPDATE CASCADE 指定若有人嘗試更新資料列中的索引鍵值，而該索引鍵又被其他資料表的現有資料列中的外部索引鍵所參考時，所有組成該外部索引鍵的值也會更新成指定給該索引鍵的新值。語法二?(語法二)由於此資料表的主要鍵是由兩個屬性訂單編號和產品編號組合而成，所以並不可分別寫在屬性後方，否則在語意上將解釋為訂單編號為主要鍵，產品編號也是主要鍵，也就是此資料表有兩個主要鍵，這是不被允許的，所以只要多於一個屬性所組成的鍵，就必須要置於table level區，不可分別寫於屬性後方。其他限制都屬於單一個屬性的限制，所以皆可置於該屬性後面做限制。刪除資料表之基本語法刪除順序與建立順序相反6-1簡介6-2資料定義語言(DDL)定義綱要(Schema)定義資料表(Tables)6-36-36-36-3資料操作語言資料操作語言(DML)(DML)(DML)(DML)新增操作新增操作(Inert Operation)(Inert Operation)(Inert Operation)(Inert Operation)刪除操作(Delete Operation)(Delete Operation)更新操作(Update Operation)(Update Operation)查詢操作(Select Operation)(Select Operation)本章內容新增INSERT之基本語法【範例6-4】新增一筆新的產品資料。【說明】(語法一)將屬性列全部省略不寫，但必須將新增的資料依資料表內的屬性順序填寫，否則將會造成錯誤。【語法】(語法一)INSERT INTO 產品資料 VALUES(13,8,S0005,拿鐵,35,0,30)範例 6-4(1/2)【範例6-4】新增一筆新的產品資料。【說明】(語法二)將屬性列名稱全部列出，其順序只要和所要新增的屬性值位置相對應即可。【語法】(語法二)INSERT INTO 產品資料(產品編號,類別編號,供應商編號,產品名稱,建議單價,庫存量,安全存量)VALUES(13,8,S0005,拿鐵,35,0,30)範例 6-4(2/2)【範例6-5】新增一筆新的產品資料，其中的庫存量與安全存量使用資料表內的預設值。【說明】此範例只要是說明新增一筆資料時，可以使用保留字DEFAULT，來達到該屬性是使用資料表內的預設值。【語法】INSERT INTO 產品資料(產品編號,類別編號,產品名稱,建議單價,庫存量,安全存量)VALUES(15,2,S0002,紅茶拿鐵,20,DEFAULTDEFAULT,DEFAULTDEFAULT)31/107範例 6-5刪除delete之基本語法【範例6-6】從供應商資料表中，刪除掉供應商編號為S0006S0006的供應商資料。【說明】此範例是利用DML中的DELETE語法，藉由WHERE的條件限制，將供應商編號為S0006的資料刪除。【語法】DELETE FROM 供應商WHERE 供應商編號=S0006範例 6-6【範例6-7】從供應商資料表中，刪除所有資料。【說明】此範例可以使用TRUNCATE TABLE的語法將供應商資料表內的資料全部刪除。但也等同於利用DML中的DELETE語法，而不使用WHERE的任何限制，亦可將全部資料刪除。但此兩者的差異在於，TRUNCATE並不會將所刪除的執行過程記錄於交易日誌檔(log file)中，而DELETE的語法，則會將所有被刪除的資料全部記錄於交易日誌檔中，所以在刪除大量資料時，TRUNCATE將會顯得較有效率。【語法】(語法一)TRUNCATE TABLE 供應商(語法二)DELETE FROM 供應商 範例 6-7更新UPDATE之基本語法【範例6-8】將產品資料資料表中的產品編號為13的庫存量更新為300，安全存量更新為50。【說明】此範例可以利用DML中的UPDATE語法，並使用WHERE來限制要更新的資料。【語法】UPDATE 產品資料 SET 庫存量=300,安全存量=50WHERE 產品編號=13範例 6-8【範例6-9】將產品資料資料表中類別編號為1的所有產品之建議單價更新為原價之80%。【說明】此範例可以利用DML中的UPDATE語法，使用建議單價=建議單價*0.8計算式，並使用WHERE來限制被更新的資料條件限制。【語法】UPDATE 產品資料 SET 建議單價=建議單價*0.8WHERE 類別編號=1範例 6-9【範例6-10】將產品資料資料表中類別編號為1的所有產品之安全存量全部改成資料表內的預設值。【說明】此範例只要是說明更新一筆資料時，可以使用保留字DEFAULT，來達到該屬性是使用資料表內的預設值。【語法】UPDATE 產品資料 SET 安全存量=DEFAULTWHERE 類別編號=1範例 6-10查詢SELECTSELECT之基本語法【範例6-11】基本使用與萬用字元查詢使用SELECT查詢出所有的產品資料。【說明】在此範例中可使用萬用字元(wild card)*來代表所有的屬性，或將所有屬性一一寫出也等同意義。【語法】(語法一)SELECT *FROM 產品資料(語法二)SELECT產品編號,類別編號,供應商編號,產品名稱,建議單價,庫存量,安全存量 FROM 產品資料【範例6-11】基本使用與萬用字元查詢【結果】【範例6-12】基本屬性輸出之查詢利用SELECT查詢出產品資料資料表中的產品編號,類別編號,產品名稱,建議單價,庫存量等屬性之屬性值。【說明】此範例中已一一表示出屬性，故必須要將每一屬性清楚寫出，不可使用萬用字元。並且此範例亦等同於關聯代數中的投影操作(PROJECT)-。【語法】SELECT 產品編號,類別編號,產品名稱,建議單價,庫存量FROM 產品資料【範例6-12】基本屬性輸出之查詢(a)原產品資料之資料表(b)查詢後之結果【範例6-13】條件限制之查詢利用SELECT查詢出產品資料之資料表中，類別編號為1的所有產品資訊。【說明】此範例可使用WHERE來限制輸出的資料中，僅包含類別為1的紀錄，而輸出之屬性可直接使用萬用字元*。並且此範例亦等同於關聯代數中的選取操作(SELECT)-。【語法】SELECT*FROM 產品資料WHERE 類別編號=1【範例6-13】條件限制之查詢(a)原產品資料之資料表(b)查詢後之結果【範例6-14】基本屬性輸出與條件限制之綜合查詢利用SELECT查詢出 產品資料資料表中類別編號小於5的所有相關產品資料，輸出包括產品編號,類別編號,產品名稱,建議單價,庫存量等屬性。【說明】此範例相當於綜合了【範例6-12】和【範例6-13】兩個範例，也就是利用關聯代數中的縱向選取(投影操作)和橫向選取(選取操作)兩個操作原理。【語法】SELECT產品編號,類別編號,產品名稱,建議單價,庫存量FROM 產品資料WHERE 類別編號 5【範例6-14】基本屬性輸出與條件限制之綜合查詢(a)原產品資料之資料表(b)查詢後之結果【範例6-15】屬性別名之查詢請查詢出男性業務之員工相關資料，包括員工編號、姓名、職稱和性別，但輸出時之屬性名稱要改為員工編號、員工姓名、職務名稱和性別。【說明】此範例主要是說明輸出查詢時，將屬性以別名方式來表示，只要在每個欲給別名的屬性後方給予 AS AS 別名【語法】SELECT 員工編號,姓名 AS AS 員工姓名,職稱 AS AS 職務名稱,性別FROM 員工WHERE 性別=男 AND 職稱=業務 48/107【範例6-156-15】屬性別名之查詢續續下頁【結果】49/107【範例6-16】排序問題之查詢請查詢產品資料，並依類別編號遞增排序，倘若相同類別編號之產品，再依據產品編號遞減排序，輸出屬性依序包括類別編號、產品編號、產品名稱和建議單價。【說明】此範例主要是說明輸出查詢時的排序問題，可使用ORDER BY方式，並於每個欲排序的屬性後方指名遞增(ASC)或遞減(DESC)排序，倘若未明確指出是遞增或遞減，預設值將以遞增(ASC)做為排序。【語法】SELECT 類別編號,產品編號,產品名稱,建議單價FROM 產品資料ORDER BY ORDER BY 類別編號 ASC,ASC,產品編號 DESCDESC50/107【範例6-166-16】排序問題之查詢續續下頁【結果】51/107【範例6-17】重複值只輸出一筆之查詢請從員工資料表中查詢出有那幾種職稱，重複職稱僅一筆顯示，並依職稱屬性遞增排序。【說明】此範例主要在屬性前加上DISTINCTDISTINCT即可，如圖6-13(a)當不加DISTINCT時會有很多重複的職稱出現，加上DISTINCT之後如(b)重複之資料只會出現一次。【語法】SELECT DISTINCTDISTINCT 職稱FROM 員工ORDER BY 職稱52/107【範例6-176-17】重複值只輸出一筆之查詢續續下頁【結果】53/107(a)不加DISTINCT(b)加DISTINCT重複重複重複【範例6-18】經過計算的輸出之查詢請依訂單明細資料表，查詢出訂單編號小於94010300之資料，並於每一筆後面計算出該筆資料之小計(小計=實際單價數量)，並依訂單編號、產品編號遞增排序。輸出之屬性包括訂單編號、產品編號、實際單價、數量和小計。【說明】此範例主要是說明輸出查詢時，可利用SELECT的運算方式，將訂單明細資料表內的實際單價與數量計算出該筆的小計金額，並給于一個別名為小計，計算方式如下 實際單價*數量 AS AS 小計【語法】SELECT 訂單編號,產品編號,實際單價,數量,實際單價*數量 AS AS 小計FROM 訂單明細WHERE 訂單編號 94010300ORDER BY 訂單編號,產品編號 54/107【範例6-186-18】經過計算的輸出之查詢續續下頁【結果】55/107【範例6-19】兩個資料表的基本內部合併(INNER JOIN)查詢請查詢出每一位員工所承接的訂單資料，輸出屬性包括員工編號、姓名、訂單編號、訂貨日期，並依員工編號和訂單編號遞增排序。【說明】由於此範例所要查詢的屬性，可從圖6-15(a)的ER圖中看出，分佈在員工資料表和訂單資料表，所以本查詢必須使用兩個資料表的合併查詢，可參考圖(b)的Join示意圖，是取得兩個資料表關聯之後的共同紀錄，也就是藉由員工資料表中的員工編號和訂單資料表中的員工編號的相等關係。而在此範例中使用WHERE對兩資料表限制其中的關聯性，其效果等同於內部合併(INNER JOIN)。也由於在此兩個資料表中，皆有員工編號之屬性，為避免產生資料庫管理系統的混淆，所以必須在屬性前加上資料表名稱，並加上一個點，稱之為點表示法(Dot Notation)，如語法中的員工.員工編號和訂單.員工編號。56/107【範例6-196-19】兩個資料表的基本內部合併(INNER JOIN)(INNER JOIN)查詢續續下頁57/107員工訂單(a)ER圖(b)Join之示意圖續續下頁【語法】SELECT 員工.員工編號,姓名,訂單編號,訂貨日期FROM 員工,訂單WHERE 員工.員工編號=訂單.員工編號ORDER BY 員工.員工編號,訂單編號【結果】58/107【範例6-20】三個資料表的基本內部合併(INNER JOIN)查詢請查詢出每一位員工所承接的訂單資料，輸出屬性包括員工編號、姓名、訂單編號、訂貨日期和產品編號，並依員工編號、訂單編號和產品編號三個屬性遞增排序。【說明】由於此範例所要查詢的屬性，可從圖6-17(a)的ER圖中看出，分佈在員工資料表、訂單資料表和訂單明細資料表三個資料表，所以本查詢必須使用此三個資料表的合併查詢，可參考圖(b)的Join示意圖，是取得三個資料表關聯之後的共同紀錄，也就是藉由員工資料表中的員工編號和訂單資料表中的員工編號的相等關係以及訂單資料表中的訂單編號和訂單明細資料表中的訂單編號的相等關係。而在此範例中使用WHERE對兩資料表限制其中的關聯性，其效果等同於內部合併(INNER JOIN)。59/107【範例6-206-20】三個資料表的基本內部合併(INNER JOIN)(INNER JOIN)查詢續續下頁60/107(a)ER圖(b)Join之示意圖員工訂單訂單明細續續下頁【語法】SELECT 員工.員工編號,姓名,訂單.訂單編號,訂貨日期,產品編號FROM 員工,訂單,訂單明細WHERE 員工.員工編號=訂單.員工編號 AND 訂單.訂單編號=訂單明細.訂單編號ORDER BY 員工.員工編號,訂單.訂單編號,產品編號61/107【範例6-21】間接關聯性的基本內部合併(INNER JOIN)查詢請查詢出每一位員工所承接的訂單資料，輸出屬性包括員工編號、姓名、訂單編號、訂貨日期和產品名稱，並依員工編號、訂單編號二個屬性遞增排序。【說明】由於此範例所要查詢的屬性，可從圖6-19(a)的ER圖中看出，分佈在員工資料表、訂單資料表和產品資料資料表三個資料表，但是從圖中可看出，訂單資料表與產品資料資料表之間並無直接的關聯性存在，而是透過訂單明細資料表所產生的間接關聯性，所以本查詢必須使用四個資料表的合併查詢，可參考圖(b)的Join示意圖，取得四個資料表關聯之後的共同紀錄，也就是藉由員工資料表中的員工編號和訂單資料表中的員工編號的相等關係、訂單資料表中的訂單編號和訂單明細資料表中的訂單編號的相等關係以及訂單明細資料表中的產品編號和產品資料資料表中的產品編號的相等關係。而在此範例中使用WHERE對兩資料表限制其中的關聯性，其效果等同於內部合併(INNER JOIN)。62/107【範例6-216-21】間接關聯性的基本內部合併(INNER JOIN)(INNER JOIN)查詢續續下頁63/107(a)ER圖(b)Join之示意圖員工訂單訂單明細產品資料透過訂單明細產生間接關聯性續續下頁【語法】SELECT 員工.員工編號,姓名,訂單.訂單編號,訂貨日期,產品名稱FROM 員工,訂單,訂單明細,產品資料WHERE 員工.員工編號=訂單.員工編號 AND 訂單.訂單編號=訂單明細.訂單編號 AND 訂單明細.產品編號=產品資料.產品編號ORDER BY 員工.員工編號,訂單.訂單編號64/107續續下頁65/107【範例6-22】使用資料表的別名如同【範例6-21】的查詢條件，並使用資料表的別名。【說明】此範例主要是說明當很多資料表的屬性名稱相同時，皆必須要於屬性前加上資料表名稱，無形中會產生很多冗長的文字，此問題可透過給於資料表別名方式來達成精簡的效果。【語法】SELECT E E.員工編號,姓名,O O.訂單編號,訂貨日期,產品名稱FROM 員工 AS EAS E,訂單 AS OAS O,訂單明細 AS ODAS OD,產品資料 AS PAS PWHERE E E.員工編號=O O.員工編號 AND O O.訂單編號=ODOD.訂單編號 AND ODOD.產品編號=P P.產品編號ORDER BY E E.員工編號,O O.訂單編號66/107【範例6-226-22】使用資料表的別名續續下頁