SQL-Server-2005數據庫原理及應用教程第4章-關系數據庫標準語言--SQL

第4章關系數據庫標準語言

——SQL

4.1SQL概述

4.1.1SQL語言的特點 SQL語言之所以能夠得到廣泛的應用，并成為國際上的數據庫主流語言，是因為它是一個綜合的、功能強大的、易學易用的語言。主要特點包括以下幾個方面。1．綜合統(tǒng)一2．高度非過程化3．面向集合的操作方式4．兩種使用方式，統(tǒng)一的語法結構5．語言簡潔、易學易用4.1.2SQL數據庫的三級模式結構SQL語言支持關系數據庫的三級模式結構，其中視圖對應外模式，基本表對應模式，存儲文件對應內模式。①用戶可以對視圖(View)和基本表(BaseTable)進行查詢等操作。②視圖是從一個或幾個基本表導出的表，它本身不獨立存儲在數據庫中，即數據庫中只存儲視圖的定義，不存儲對應的數據。因此視圖是一張?zhí)摂M表。③基本表是本身獨立存在的表，每個基本表都有與之對應的存儲文件。一個基本表可以跨越若干個存儲文件，一個存儲文件也可以存放若干個基本表。④SQL用戶可以是應用程序，也可以是最終用戶。SQL用戶也可以作為獨立的用戶接口，供交互環(huán)境下的終端用戶使用。4.2SQL的數據定義

SQL語言的數據定義(DDL)功能包括：定義基本表、定義視圖和定義索引。4.2.1基本表的定義在SQL中基本表的定義功能包括基本表的創(chuàng)建、修改和刪除三種語句。1．基本表的創(chuàng)建SQL語言使用CREATETABLE語句創(chuàng)建基本表，格式為：CREATETABLE<表名>(<列名><數據類型>[列級完整性約束條件][,<列名><數據類型>[列級完整性約束條件]]…[,<表級完整性約束條件>])；其中，<表名>表示所要創(chuàng)建的基本表的名稱，<列名>為表中屬性的名稱，可用英文也可用漢字。每個屬性都有數據類型，不同的關系數據庫系統(tǒng)所支持的數據類型存在部分差異，但一般都支持表4-3所列的一些主要數據類型。建表的時候還可以同時定義與該表有關的完整性約束條件，包括表級完整性約束條件與列級完整性約束條件。列級完整性約束條件是針對屬性值設置的限制條件，SQL的列級完整性約束條件有以下幾種。1)NULL或NOTNULL約束2)UNIQUE約束3)DEFAULT約束4)CHECK約束表級完整性約束條件是指涉及到關系中兩列或兩列以上的限制條件。SQL的表級完整性約束條件有以下幾種。1)UNIQUE約束2)PRIMARYKEY約束3)FOREIGNKEY約束2．基本表的修改在數據庫的實際應用中，隨著應用環(huán)境和需求的變化，經常需要修改基本表的結構，包括修改屬性列的寬度和數據類型，增加新的屬性，增加新的約束條件，刪除原有的約束條件等。SQL語言使用ALTERTABLE語句修改基本表，一般格式為：ALTERTABLE<表名>[ADD<新列名><數據類型>[完整性約束條件]][DROP<完整性約束名>][MODIFY<列名><數據類型>];其中，<表名>是已經存在且要修改的表。ADD子句用于增加新列，但新屬性不能定義為NOTNULL，因為表在新增加一列后，原來的原組在新增的列上的值都為空值。DROP子句用于刪除指定的完整性約束條件。MODIFY子句用于修改原有的列名和數據類型，修改表中原有的列的定義可能會破壞原有的數據。3．基本表的刪除當不再需要某個基本表時，可以使用DROPTABLE語句刪除它。刪除基本表語句的一般格式為：DROPTABLE<表名>;該語句將一個基表的定義，連同表中的所有數據記錄、索引以及由此表導出的所有視圖全部刪除，并釋放相應的存儲空間。4.3SQL的數據查詢SQL數據查詢是SQL語言中最重要、最豐富，也是最靈活的內容。SQL語言中提供了SELECT語句進行數據庫的查詢，它具有數據查詢、統(tǒng)計、分組和排序的功能。一般格式為：SELECT[ALL|DISTINCT]<目標列表達式>[，<目標列表達式>]…FROM<表名或視圖名>[,<表名或視圖名>]…[WHERE<條件表達式>][GROUPBY<列名1>[HAVING<條件表達式>]][ORDERBY<列名2>[ASC|DESC]]；①SQL查詢中的子句順序：SELECT，FROM，WHERE，GROUPBY，HAVING和ORDERBY。其中SELECT和FROM是必須的，而HAVING子句只能和GROUPBY子句搭配起來使用。②SELECT子句對應的是關系代數中的投影運算，用來列出查詢結果中的屬性。其輸出可以是列名、表達式或集函數(AVG，COUNT，MAX，MIN和SUM)。DISTINCT選項可以保證查詢結果集中不存在重復元組。③FROM子句對應的是關系代數中的笛卡兒積，它列出的是表達式求值過程中必須掃描的關系。在FROM子句出現多個基本表或視圖時，系統(tǒng)首先執(zhí)行笛卡兒積操作。④WHERE子句對應的是關系代數中的選擇謂詞。WHERE子句的條件表達式中常用的查詢條件如下4.3.1簡單查詢簡單查詢是指在查詢過程中只涉及一個基本表或視圖的查詢，它是一種最簡單的查詢。1．最簡單的查詢最簡單的查詢是指查詢一張表，并且只有SELECT子句和FROM子句，沒有WHERE子句，表示被查詢的對象是關系中的所有元組。例4-12

SELECT(Pno,Pname,Pcity,Ptelephone)FROMProvider;在這里，當查詢列的顯示順序與其在基本表中順序相同時，也可以簡單地將<目標列表達式>指定為*。因此上面的語句等價于：SELECT*FROMProvider;2．DISTINCT短語按照關系的一般理論，重復的元組是不會出現在關系中的。但是，重復元組的刪除是相當費時的，所以SQL允許在關系和查詢中出現重復。在SELECT語句中缺省或ALL是保留重復元組的，因此一般情況下不寫ALL。如果想要刪除重復，則必須使用DISTINCT短語。重復元組一般是在投影的時候產生的。例4-14查詢供應商所在城市。SELECTPcityFROMProvider;3．表達式的查詢SELECT子句可以包含算術表達式、字符串常量和函數等。例4-15查詢所有工程的工程名和首付款(預算的50%)。SELECTProjectname,首付款,Budget*0.5首付FROMProject;4．WHERE子句WHERE子句的作用是選擇滿足條件的元組。1）比較運算SQL提供的比較運算符主要有：=、<>、!=、<、<=、>、>=，以及BETWEEN…AND…。SQL允許使用比較運算符、比較算術表達式和字符串等特殊類型數據(如日期)等。例4-16查詢天津市供應商的名稱和聯系電話。SELECTPname,PtelephoneFORMProviderWHEREPcity='天津';2）邏輯運算SQL在WHERE子句中可以使用邏輯運算符AND，OR和NOT，邏輯運算的對象是包含比較運算符的表達式。例4-19查詢供應數量在5000-10000之間的供應商代碼。SELECTPnoFROMSupplyWHEREAccount>=5000ANDAccount<=10000;3）謂詞ININ表示查找的條件包含在集合中。NOTIN表示查找的條件不包含在集合中。例4-21查詢天津和北京的供應商信息。SELECT*FROMProviderWHEREPcityIN('天津','北京');4）字符串操作字符串也可以進行比較運算。若兩個字符串具有相同的字符序列，則這兩個字符串是相等的。當對兩個字符串用“小于或大于”比較時，實際上是對這兩個字符串按字母順序依次進行字母大小比較，例如'FHGE'>'FHFF'SQL還提供了使用操作符LIKE的模式匹配的字符串比較功能，勝于查找指定的屬性列值與<匹配串>相匹配的原組。：[NOT]LIKE'<匹配串>'[ESCAPE'<換碼字符>']例4-22查詢所有綠色的零件的信息。SELECT*FROMAccessoryWHEREAcolorLIKE'綠';5）ORDERBY子句在查詢中，經常要求將查詢結果按照某種規(guī)則進行排序。SQL通過在查詢基本結構中加入ORDERBY子句實現對查詢結果按一個或多個列排序，格式為：ORDERBY列名1[ASC|DESC],列名2[ASC|DESC],…列名n[ASC|DESC]；ASC代表的是升序，DESC代表的是降序，默認是升序，ASC可以省略。例4-28查詢使用了“零件編號”為0043的“項目編號”及“供應數量”，并將查詢結果按“項目編號”升序排列，同一個項目按“供應數量”降序排列。SELECTProjectno,AccountFROMSupplyWHEREAno='0043'ORDERBYProjectno,AccountDESC;6）使用集函數為了方便用戶，增強檢索功能，SQL提供了許多集函數例4-29查詢供銷商的個數。SELECTCOUNT(*)FROMProvider;例4-30查詢?yōu)轫椖抗肆慵墓痰膫€數。SELECTCOUNT(DISTINCTPno)FROMSupply;供應商為一個項目供應了一種零件，在Supply表中就有一條記錄。一個供應商要供應多個項目多種零件，為避免重復計算供應商個數，必須在COUNT函數中使用DISTINCT短語。4.3.2連接查詢

連接查詢是指涉及兩個或兩個以上的基本表或視圖的連接操作而實現的查詢。連接查詢包括等值連接、自然連接、求笛卡爾積、一般連接、外連接、內連接、左連接、右連接和自然連接等多種。1．等值連接和非等值連接查詢連接查詢中，用來連接兩個表的條件稱為連接條件或連接謂詞。連接條件的一般格式為：[<表名1>.]<列名1><比較運算符>[<表名2>.]<列名2>其中，比較運算符主要有：=、>、<、>=、<=和!=。連接謂詞中的列名稱為連接字段。連接條件中，連接字段類型必須是可比的，但連接字段不一定是同名的。當連接運算符為“=”時，該連接操作稱為等值連接；否則，使用其他運算符的連接運算稱為非等值連接。當等值連接中的連接字段相同，并且在SELECT語句中去除了重復字段時，該連接操作為自然連接。例4-35查詢供應商的情況及其所供應的項目和零件。SELECTProvider.*,Supply.*FROMSupply,ProviderWHERESupply.Pno=Provider.Pno;說明：①該題的目標列中含供應商表的全部屬性和供應零件表的全部屬性；②由于目標列中有供應商表中的供應商編號和供應零件表中的供應商編號兩個相同屬性名的屬性，故它的連接操作是等值連接，如果在SELECT語句中將重復屬性去掉，該操作即為自然連接操作；③連接操作的連接條件必須在WHERE子句中寫出，如果使用了兩個表查詢，但WHERE子句中無連接條件，則結果為廣義笛卡爾積操作結果。2．自身連接查詢連接操作不只是在兩個表之間進行，一個表也可以進行自身連接操作。表自身的連接操作稱為自身連接。例4-38查詢所有與“0065”零件顏色相同的零件名稱。SELECTB.AnameFROMAccessoryA,AccessoryBWHEREA.Ano='0065'ANDAcolor=B.Acolor;說明：①自身連接實際上是將一個表作為兩個表來處理，為了對它們進行區(qū)分，在查詢語句中需要使用表的別名，在本例中的A和B分別是Accessory表的別名，A和B分別作為獨立表使用；②該題的連接條件是A表中的顏色與B表中的顏色等值連接，由于A和B都是零件的別名，所以該例是自然連接操作；③數據庫系統(tǒng)在執(zhí)行本例的自然連接操作時，首先按別名形成兩個獨立的表A和B，然后根據要求連接成結果表；3．外部連接查詢在前面的連接示例中，結果集中只保留了符合連接條件的元組，而去除了兩個表中沒有對應的或匹配的元組情況，這種連接稱為內連接。如果要求查詢結果集中保留非匹配的元組，這就是外部連接操作。SQL的外部連接分為左連接和右連接兩種：左連接是在結果集中保留連接表達式左集中的非匹配記錄；右連接是在結果集中保留連接表達式右集中的非匹配記錄。左連接符號為“*=”，右連接符號為“=*”。外部連接中不匹配的分量在結果集中使用NULL表示?？梢詫⒗?-35改寫為：SELECTProvider.*,Supply.*FROMSupply,ProviderWHERESupply.Pno=*Provider.Pno;或SELECTProvider.*,Supply.*FROMSupply,ProviderWHEREProvider.Pno*=Supply.Pno;4.3.3嵌套查詢在SQL語言中，一個SELECT…FROM…WHERE語句稱為一個查詢塊。將一個查詢塊嵌套在另一個查詢塊的WHERE子句或HAVING短語的條件中的查詢稱為嵌套查詢。在嵌套語句中，上層的查詢塊稱為外層查詢或父查詢，下層查詢塊稱為內層查詢或子查詢。SQL語言允許多層嵌套查詢，即一個子查詢中還可以嵌套其他子查詢。需要特別注意的是，子查詢的SELECT語句中不能使用ORDERBY子句，ORDERBY子句只能對最終查詢結果排序。嵌套查詢一般的處理方法是由里向外處理，即每個子查詢在上一級查詢處理之前處理，子查詢的結果用于建立起父查詢的查找條件。1．使用IN操作符的嵌套查詢在嵌套查詢中，子查詢的結果往往是一個集合，所以謂詞IN是嵌套查詢中最經常使用的謂詞。例4-39查詢使用了零件“螺釘”的項目編號和名稱。SELECTProjectno,ProjectnameFROMProjectWHEREProjectnoIN(SELECTProjectnoFROMSupply WHEREAnoIN(SELECTAno FROMAccessory WHEREAname='螺釘'));該題的執(zhí)行步驟是，首先在零件表中查詢出螺釘的零件編號(可能為多個)，然后根據這個(些)零件編號在項目表中找出項目編號，最后根據項目編號在項目表中找出其名稱；2．使用比較運算符的嵌套查詢IN操作符用于一個值與多個值的比較，而比較符用于一個值與另一個值之間的比較。當比較運算符后面的值需要通過查詢才能得到的，就需要使用比較符嵌套查詢。例4-40查詢在“001”工程中比“2948”零件供應量大的零件編號。SELECTAnoFROMSupplyWHEREProjectno='001'ANDAccount>(SELECTAccountFROMSupply WHEREAno='2948'ANDProjectno='001');3．使用ANY或ALL操作符的嵌套查詢ANY或ALL操作符時必須與比較運算符配合使用，格式為：<字段><比較符>[ANY|ALL]<子查詢>ANY和ALL與比較運算符結合及語義如表4.16所示4.3.4組合查詢SELECT語句的查詢結果是元組的集合，因此，可以將多個SELECT語句的查詢結果進行組合操作。組合操作主要包括并操作UNION、交操作INTERSECT和差MINUS操作。例4-47查詢藍色或綠色的零件編號。SELECTAnoFROMAccessoryWHEREAcolor='藍'UNIONSELECTAnoFROMAccessoryWHEREAcolor='綠';①由于組合查詢的整體是一個查詢，故只能在最后一條語句的后面加結束符號“；”，而不能在每個分查詢子句后加結束符號；②組合查詢中的每個分查詢結果集的結構應一致；4.4SQL的數據更新數據更新是指數據的插入、刪除和修改操作。SQL的數據更新語句主要包括INSERT(插入)、UPDATE(修改)和DELETE(刪除)三種。4.4.1插入語句

SQL的數據插入語句INSERT有兩種形式：一種是一次插入一個元組，另一種是一次插入一個子查詢的結果。1．插入單個元組插入單個元組的INSERT語句的格式為：INSERTINTO<表名>[(<屬性列1>[，<屬性列2>…])VALUES(<常量1>[，<常量2>]…);其功能是將新元組插入到指定表中，其中新插入的元組的<屬性列1>的值為<常量1>，<屬性列2>的值為<常量2>，…。INTO子句中沒有出現的屬性列，新記錄在這些列上將為空值。注意在表定義時說明了NOTNULL的屬性列不能取空值，否則會出錯。如果INTO子句中沒有指明任何列名，則新插入的記錄必須在每個屬性列上均有值。例4-50零件表中插入一條新記錄(零件編號：3000；零件名稱：渦輪；產地：南京；規(guī)格：W_h21；顏色：紅)INSERTINTOAccessoryVALUES('3000','渦輪','南京','W_h21','紅');2．插入子查詢的結果集SQL

允許將子查詢的結果嵌到數據插入語句中，以便將查詢得到的結果集作為數據插入到數據表中。插入子查詢結果集的INSERT語句格式為：INSERTINTO<表名>[(<屬性列1>[，<屬性列2>…])<子查詢>;例4-52求每項工程所用零件的總數量。CREATETABLEProductsum(ProjectnoCHAR(10),SumINT);INSERTINTOProductsumSELECTProjectno,SUM(Account)FROMSupplyGROUPBYProjectno;說明：本例首先用CREATETABLE語句建立了Productsum基本表，然后使用INSERT語句將查詢得到的結果集插入到Productsum基本表中。4.4.2刪除語句數據刪除語句的一般格式為：

DELETE FROM<表名> [WHERE<條件>];DELETE語句的功能是從指定表中刪除滿足WHERE子句條件的所有元組。如果省略WHERE子句，表示刪除表中全部的元組。注意DELETE語句與DROPTABLE語句的區(qū)別，DELETE語句刪除的是表中的數據，而DROPTABLE語句刪除的是表的結構。例4-53刪除編號為“007”的工程記錄。DELETEFROMProjectWHEREProjectno='007';例4-54刪除所有的供應零件記錄。DELETEFROMSupply;4.4.3修改語句數據修改語句的一般格式為：

UPDATE<表名>SET<列名>=<表達式>[，<列名2>=<表達式2>]…[WHERE<條件>];SQL數據修改語句的功能是將<表名>中那些符合WHERE子句條件的元組的某些列，用SET子句中給出的表達式的值替代。如果UPDATE語句中無WHERE子句，則表示要修改指定表中的全部元組。在UPDATE語句的WHERE子句中可以嵌入查詢語句。例4-56將工程表中全部工程的預算增加10%。UPDATEProjectSETBudget=Budget*1.1;4.5視圖視圖是由基本表或其他視圖導出的虛表。數據庫中只存放視圖的定義，視圖并不存放數據，用戶在視圖中看到的數據存放在相關的基本表中。視圖定義之后，和基本表一樣可以被用戶查詢，但通過視圖來更新基本表中的數據有一定的限制。視圖的作用主要有以下三個方面。（1）可以減輕普通用戶的工作。普通用戶關心的數據往往是對基本表進行計算的，或統(tǒng)計的，或多表連接后的信息，數據庫管理員可把這些數據做成視圖，使這些視圖直接滿足普通用戶的要求。（2）視圖是數據庫系統(tǒng)的一種安全機制。對不同的用戶，提供不同的視圖，不讓用戶直接面對基本表，使得某些重要或機密的數據不出現在無權看這些數據的用戶的視圖上。(3)視圖可以提高數據的邏輯獨立性。在數據庫需要重構時，可以減少對用戶和應用程序的影響。4.5.1視圖的定義和刪除1．定義視圖SQL語言定義視圖的一般格式為：CREATEVIEW<視圖名>[(<列名1>[,<列名2>]…)]AS<子查詢>[WITHCHECKOPTION];其中<子查詢>可以是任意復雜的SELECT查詢語句，但不同的是在視圖中的<子查詢>通常不允許含有ORDERBY子句和DISTINCT短語。<列名>是<子查詢>的結果在該視圖中的屬性名稱，它必須與<子查詢>中的目標列相對應。它要么全部省略要么全部指定，但若<子查詢>中的目標列是集函數、表達式和多表連接的同名列時，必須全部指定<列名>，另外若視圖要用與<子查詢>中的目標列名不同的新名時，也必須全部指定<列名>。WITHCHECKOPTION選項表示在對視圖更新操作時要保證操作的數據滿足視圖定義的謂詞條件。例4-59建立紅色零件的視圖。CREATEVIEW紅色零件ASSELECTAno,Aname,Aplace,AspecFROMAccessoryWHEREAcolor='紅';例4-60建立一個“002”工程所需零件的視圖，其屬性包括工程代碼，工程名稱，所需零件名稱，用量，并要求進行修改時須保證該視圖只有“002”工程所需的零件。CREATEVIEW工程零件(工程代碼,工程名稱,所需零件名稱,用量)ASSELECTSupply.Projectno,Projectname,Aname,AccountFROMSupply,Project,AccessoryWHERESupply.Projectno=Project.ProjectnoANDSupply.Ano=Accessory.AnoWITHCHECKOPTION;2．刪除視圖SQL中視圖刪除的一般格式為：DROPVIEW<視圖名>;當基本表刪除后，由該基本表導出的所有視圖（定義）沒有被刪除，但均已無法使用。刪除這些視圖（定義）需要顯式地使用DROPVIEW語句。視圖刪除后，視圖的定義將從數據字典中刪