SQL基礎(chǔ)學(xué)習(xí)教材

關(guān)系模型與SQL1數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL提綱關(guān)系基本概念SQL概述SQL數(shù)據(jù)定義功能SQL數(shù)據(jù)查詢功能SQL數(shù)據(jù)修改功能視圖2數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL關(guān)系模型回顧E.F.Codd于70年代初提出關(guān)系數(shù)據(jù)理論，他因此獲得1981年的ACM圖靈獎(jiǎng)關(guān)系理論是建立在集合代數(shù)理論基礎(chǔ)上的，有著堅(jiān)實(shí)的數(shù)學(xué)基礎(chǔ)早期代表系統(tǒng)SystemＲ：由IBM研制INGRES：由加州Berkeley分校研制目前主流的商業(yè)數(shù)據(jù)庫(kù)系統(tǒng)Oracle，SQLServer，DB2，Informix，SybaseAccess，F(xiàn)oxpro，F(xiàn)oxbase3數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL關(guān)系模型概述關(guān)系數(shù)據(jù)庫(kù)系統(tǒng)是支持關(guān)系模型的數(shù)據(jù)庫(kù)系統(tǒng)關(guān)系模型的組成關(guān)系數(shù)據(jù)結(jié)構(gòu)關(guān)系操作集合關(guān)系完整性約束4數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL關(guān)系數(shù)據(jù)結(jié)構(gòu)單一的數(shù)據(jù)結(jié)構(gòu)----關(guān)系現(xiàn)實(shí)世界的實(shí)體以及實(shí)體間的各種聯(lián)系均用關(guān)系來(lái)表示數(shù)據(jù)的邏輯結(jié)構(gòu)----二維表從用戶角度，關(guān)系模型中數(shù)據(jù)的邏輯結(jié)構(gòu)是一張二維表。5數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL關(guān)系基本概念域（Domain）一組值的集合，這組值具有相同的數(shù)據(jù)類型如整數(shù)的集合、字符串的集合、全體學(xué)生的集合笛卡爾積（CartesianProduct）一組域D1,D2,…,Dn的笛卡爾積為:D1×D2×…×Dn

={(d1,d2,…,dn)|di∈Di

,i=1,…,n}笛卡爾積的每個(gè)元素(d1,d2,…,dn)稱作一個(gè)n-元組（n-tuple）元組的每一個(gè)值di叫做一個(gè)分量（component）若Di的基數(shù)為mi，則笛卡爾積的基數(shù)為6數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL關(guān)系基本概念例：設(shè)

D1為教師集合（T）={t1，t2} D2為學(xué)生集合（S）={s1，s2，s3} D3為課程集合（C）={c1，c2}

則D1×D2×D3是個(gè)三元組集合，元組個(gè)數(shù)為2×3×2，是所有可能的（教師，學(xué)生，課程）元組集合笛卡爾積可表示為二維表的形式TSCt1s1c1t1s1c2t1s2c1………t2s3c27數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL關(guān)系基本概念關(guān)系笛卡爾積D1×D2×…×Dn的子集叫做在域D1,D2,…,Dn上的關(guān)系，用R(D1,D2,…,Dn

)表示R是關(guān)系的名字，n是關(guān)系的度或目關(guān)系是笛卡爾積中有意義的子集關(guān)系也可以表示為二維表關(guān)系TEACH(T,S,C)TSCt1s1c1t1s1c2t1s2c1t2s3c2元組屬性8數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL關(guān)系基本概念例給出三個(gè)域：

D1=TUTOR={張清玫，劉逸}

D2=SPECIALITY={計(jì)算機(jī)專業(yè)，信息專業(yè)}D3=POSTGRADUATE={李勇，劉晨，王敏}則D1，D2，D3的笛卡爾積為：D1×D2×D3＝｛(張清玫，計(jì)算機(jī)專業(yè)，李勇)，(張清玫，計(jì)算機(jī)專業(yè)，劉晨)，

(張清玫，計(jì)算機(jī)專業(yè)，王敏)，(張清玫，信息專業(yè)，李勇)，

(張清玫，信息專業(yè)，劉晨)，(張清玫，信息專業(yè)，王敏)，

(劉逸，計(jì)算機(jī)專業(yè)，李勇)，(劉逸，計(jì)算機(jī)專業(yè)，劉晨)，

(劉逸，計(jì)算機(jī)專業(yè)，王敏)，(劉逸，信息專業(yè)，李勇)，

(劉逸，信息專業(yè)，劉晨)，(劉逸，信息專業(yè)，王敏)｝9數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL關(guān)系基本概念關(guān)系：SAP(TUTOR，SPECIALITY，POSTGRADUATE)關(guān)系名，屬性名假設(shè)：專業(yè)與導(dǎo)師：1:n，導(dǎo)師與研究生：1:m于是：SAP關(guān)系可以包含三個(gè)元組｛(張清玫，信息專業(yè)，李勇)，

(張清玫，信息專業(yè)，劉晨)，

(劉逸，計(jì)算機(jī)專業(yè)，王敏)}10數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL關(guān)系基本概念-關(guān)系的性質(zhì)關(guān)系的性質(zhì)列是同質(zhì)的即每一列中的分量來(lái)自同一域，是同一類型的數(shù)據(jù)。如TEACH(T,S,C)={(t1,s1

,c1),(t1,t2,c1)}是錯(cuò)誤的不同的列可來(lái)自同一域，每列必須有不同的屬性名。如P={t1，t2，s1，s2，s3}，C={c1，c2}，則TEACH不能寫成TEACH(P,P,C)，還應(yīng)寫成TEACH(T,S,C)11數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL關(guān)系基本概念-關(guān)系的性質(zhì)-列的次序可以任意交換遵循這一性質(zhì)的數(shù)據(jù)庫(kù)產(chǎn)品(如ORACLE)，增加新屬性時(shí)，永遠(yuǎn)是插至最后一列但也有許多數(shù)據(jù)庫(kù)產(chǎn)品沒有遵循這一性質(zhì)，例如FoxPro仍然區(qū)分了屬性順序12數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL關(guān)系基本概念-關(guān)系的性質(zhì)-任意兩個(gè)元組不能完全相同由笛卡爾積的性質(zhì)決定但許多關(guān)系數(shù)據(jù)庫(kù)產(chǎn)品沒有遵循這一性質(zhì)。例如:Oracle，F(xiàn)oxPro等都允許關(guān)系表中存在兩個(gè)完全相同的元組，除非用戶特別定義了相應(yīng)的約束條件13數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL關(guān)系基本概念-關(guān)系的性質(zhì)每一分量必須是不可再分的數(shù)據(jù)。滿足這一條件的關(guān)系稱作滿足第一范式（1NF）的14數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL關(guān)系模式關(guān)系模式關(guān)系的描述稱作關(guān)系模式，包括關(guān)系名、關(guān)系中的屬性名、屬性向域的映象、屬性間的數(shù)據(jù)依賴關(guān)系等關(guān)系模式可以形式化地表示為：

R（U，D，dom，F(xiàn)）

R關(guān)系名

U

組成該關(guān)系的屬性名集合

D

屬性組U中屬性所來(lái)自的域

dom

屬性向域的映象集合

F

屬性間的數(shù)據(jù)依賴關(guān)系集合關(guān)系是某一時(shí)刻的值，是隨時(shí)間不斷變化的15數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL關(guān)系模式例:導(dǎo)師和研究生出自同一個(gè)域——人，取不同的屬性名，并在模式中定義屬性向域的映象，即說(shuō)明它們分別出自哪個(gè)域：

dom（SUPERVISOR-PERSON）=dom（POSTGRADUATE-PERSON）=PERSON16數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL關(guān)系模式記作R(A1,A2,…,An)屬性向域的映象一般直接說(shuō)明為屬性的類型、長(zhǎng)度等某一時(shí)刻對(duì)應(yīng)某個(gè)關(guān)系模式的內(nèi)容(元組的集合)稱作關(guān)系關(guān)系模式是型，是穩(wěn)定的17數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL關(guān)系模型關(guān)系數(shù)據(jù)庫(kù)其型是關(guān)系模式的集合，即數(shù)據(jù)庫(kù)描述，稱作數(shù)據(jù)庫(kù)的內(nèi)涵(Intension)其值是某一時(shí)刻關(guān)系的集合，稱作數(shù)據(jù)庫(kù)的外延(Extension)18數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL關(guān)系模型關(guān)系操作關(guān)系操作是集合操作，操作的對(duì)象及結(jié)果都是集合，是一次一集合（Set-at-a-time）的方式而非關(guān)系型的數(shù)據(jù)操作方式是一次一記錄（Record-at-a-time）關(guān)系操作可以用關(guān)系代數(shù)和關(guān)系演算兩種方式來(lái)表示，它們是相互等價(jià)的如用關(guān)系代數(shù)來(lái)表示關(guān)系的操作，可以有選擇、投影、連接、除、交、差、并等19數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL關(guān)系模型數(shù)據(jù)結(jié)構(gòu)單一的數(shù)據(jù)結(jié)構(gòu)——關(guān)系實(shí)體集、聯(lián)系都表示成關(guān)系D(DNO,DNAME,DEAN)S(SNO,SNAME,SEX,AGE,DNO)C(CNO,CN,PCNO,CREDIT)SC(SNO,CNO,SCORE)PROF(PNO,PN,DNO,SAL)PC(PNO,CNO)學(xué)生課程選修屬于系教師教授工作管理20數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL關(guān)系模型超碼(superkey)使一個(gè)或多個(gè)屬性的集合，這些屬性的集合可以使我們?cè)谝粋€(gè)關(guān)系中唯一地標(biāo)識(shí)一個(gè)元組候選碼（CandidateKey）關(guān)系中的一個(gè)屬性組，其值能唯一標(biāo)識(shí)一個(gè)元組。若從屬性組中去掉任何一個(gè)屬性，它就不具有這一性質(zhì)了，這樣的屬性組稱作候選碼，如DEPT中的DNO，DN都可作為候選碼任何一個(gè)候選碼中的屬性稱作主屬性，如SC中的SNO，CNO21數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL關(guān)系模型主碼（PrimaryKey）進(jìn)行數(shù)據(jù)庫(kù)設(shè)計(jì)時(shí)，從一個(gè)關(guān)系的多個(gè)候選碼中選定一個(gè)作為主碼，如可選定DNO作為DEPT的主碼外部碼（ForeignKey）關(guān)系R中的一個(gè)屬性組，它不是R的主碼，但它與關(guān)系S的主碼相對(duì)應(yīng)，則稱這個(gè)屬性組為R的外部碼（R和S可以是同一關(guān)系）。如S關(guān)系中的DNO屬性22數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL關(guān)系模型關(guān)系模式的完整性實(shí)體完整性（EntityIntegrity）關(guān)系的主碼中的屬性值不能為空值空值：不知道或無(wú)意義意義：關(guān)系對(duì)應(yīng)到現(xiàn)實(shí)世界中的實(shí)體集，元組對(duì)應(yīng)到實(shí)體，實(shí)體是相互可區(qū)分的，通過(guò)主碼來(lái)唯一標(biāo)識(shí)，若主碼為空，則出現(xiàn)不可標(biāo)識(shí)的實(shí)體，這是不允許的23數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL關(guān)系模型關(guān)系模型必須遵守實(shí)體完整性規(guī)則的原因(1)實(shí)體完整性規(guī)則是針對(duì)基本關(guān)系而言的。一個(gè)基本表通常對(duì)應(yīng)現(xiàn)實(shí)世界的一個(gè)實(shí)體集或多對(duì)多聯(lián)系集。(2)現(xiàn)實(shí)世界中的實(shí)體和實(shí)體間的聯(lián)系都是可區(qū)分的，即它們具有某種唯一性標(biāo)識(shí)。(3)相應(yīng)地，關(guān)系模型中以主碼作為唯一性標(biāo)識(shí)24數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL關(guān)系模型關(guān)系模型必須遵守實(shí)體完整性規(guī)則的原因(續(xù))(4)主碼中的屬性即主屬性不能取空值。空值就是“不知道”或“無(wú)意義”的值。主屬性取空值，就說(shuō)明存在某個(gè)不可標(biāo)識(shí)的實(shí)體，即存在不可區(qū)分的實(shí)體，這與第（2）點(diǎn)相矛盾，因此這個(gè)規(guī)則稱為實(shí)體完整性。25數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL關(guān)系模型注意實(shí)體完整性規(guī)則規(guī)定基本關(guān)系的主碼中的所有屬性都不能取空值例選課（學(xué)號(hào)，課程號(hào)，成績(jī)）“學(xué)號(hào)、課程號(hào)”為主碼，則兩個(gè)屬性都不能取空值。26數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL關(guān)系模型-參照完整性（ReferentialIntegrity）在關(guān)系模型中實(shí)體及實(shí)體間的聯(lián)系都是用關(guān)系來(lái)描述的，因此可能存在著關(guān)系與關(guān)系間的引用。例1

學(xué)生實(shí)體、專業(yè)實(shí)體以及專業(yè)與學(xué)生間的一對(duì)多聯(lián)系學(xué)生（學(xué)號(hào)，姓名，性別，院系編號(hào)，年齡）專業(yè)（院系編號(hào)，專業(yè)名）27數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL關(guān)系模型參照完整性如果關(guān)系R的外部碼Fk與關(guān)系S的主碼Pk相對(duì)應(yīng)，則R中的每一個(gè)元組的Fk值或者等于S

中某個(gè)元組的Pk值，或者為空值意義：如果關(guān)系R的某個(gè)元組t2參照了關(guān)系S的某個(gè)元組t1，則t1必須存在例如關(guān)系S在DNO上的取值有兩種可能空值，表示該學(xué)生尚未分到任何系中若非空值，則必須是DEPT關(guān)系中某個(gè)元組的DNO值，表示該學(xué)生不可能分到一個(gè)不存在的系中28數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL關(guān)系模型29數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL關(guān)系模型學(xué)生、課程、學(xué)生與課程之間的多對(duì)多聯(lián)系學(xué)生（學(xué)號(hào)，姓名，性別，專業(yè)號(hào)，年齡）課程（課程號(hào)，課程名，學(xué)分）選修（學(xué)號(hào)，課程號(hào)，成績(jī)）30數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL關(guān)系模型學(xué)生實(shí)體及其內(nèi)部的領(lǐng)導(dǎo)聯(lián)系(一對(duì)多)31數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL關(guān)系模型用戶定義的完整性（User-definedIntegrity）用戶針對(duì)具體的應(yīng)用環(huán)境定義的完整性約束條件如SNO要求是8位整數(shù)，SEX要求取值為“1”或“2”系統(tǒng)支持實(shí)體完整性和參照完整性由系統(tǒng)自動(dòng)支持系統(tǒng)應(yīng)提供定義和檢驗(yàn)用戶定義的完整性的機(jī)制32數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL關(guān)系模型課程(課程號(hào)，課程名，學(xué)分)“課程名”屬性必須取唯一值非主屬性“課程名”也不能取空值“學(xué)分”屬性只能取值{1，2，3，4}33數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL關(guān)系模型供應(yīng)商號(hào)供應(yīng)商名所在城市B01紅星北京S10宇宙上海T20黎明天津Z01立新重慶零件號(hào)顏色供應(yīng)商號(hào)010紅B01312白S10201藍(lán)T20

今要向關(guān)系P中插入新行，新行的值分別列出如下。哪些行能夠插入？A.(‘037’，‘綠’，null)B.(null，‘黃’，‘T20’)C.(‘201’，‘紅’，‘T20’)D.(‘105’，‘藍(lán)’，‘B01’)E.(‘101’，‘黃’，‘T11’)零件關(guān)系P（主碼是“零件號(hào)”，外碼是“供應(yīng)商號(hào)”）供應(yīng)商關(guān)系S（主碼是“供應(yīng)商號(hào)”）34數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL關(guān)系數(shù)據(jù)語(yǔ)言概述關(guān)系數(shù)據(jù)語(yǔ)言的特點(diǎn)一體化一般關(guān)系系統(tǒng)的數(shù)據(jù)語(yǔ)言都同時(shí)具有數(shù)據(jù)定義、數(shù)據(jù)操縱和數(shù)據(jù)控制語(yǔ)言，而不是分為幾個(gè)語(yǔ)言。對(duì)象單一，都是關(guān)系，因此操作符也單一。而非關(guān)系型系統(tǒng)，如DBTG，有對(duì)記錄的操作，有對(duì)系的操作非過(guò)程化用戶只需提出“做什么”，無(wú)須說(shuō)明“怎么做”，存取路徑的選擇和操作過(guò)程由系統(tǒng)自動(dòng)完成面向集合的存取方式操作對(duì)象是一個(gè)或多個(gè)關(guān)系，結(jié)果是一個(gè)新的關(guān)系（一次一關(guān)系）。非關(guān)系系統(tǒng)是一次一記錄的方式35數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL數(shù)據(jù)庫(kù)聯(lián)接Oracle客戶端配置連接數(shù)據(jù)庫(kù)關(guān)于TNSNAMES.ora文件36數(shù)據(jù)庫(kù)系統(tǒng)概念----SQLOracle7客戶端Si_test.world=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(COMMUNITY=tcp.world)(PROTOCOL=TCP)(Host=)(Port=1523)))(CONNECT_DATA=(SID=ora3)))37數(shù)據(jù)庫(kù)系統(tǒng)概念----SQLOracle8客戶端SI_TEST=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP) (HOST=)(PORT=1523) ))(CONNECT_DATA=(SID=ora3)))38數(shù)據(jù)庫(kù)系統(tǒng)概念----SQLSQL概述歷史SQL:StruceuredQueryLanguang1974年，由Boyce和Chamber提出1975-1979年，在SystemR上實(shí)現(xiàn)，由IBM的SanJose研究室研制，稱為Sequel39數(shù)據(jù)庫(kù)系統(tǒng)概念----SQLSQL概述SQL數(shù)據(jù)庫(kù)體系結(jié)構(gòu)用戶1用戶2用戶4視圖V1視圖V1用戶3基本表B1基本表B2基本表B4基本表B3存儲(chǔ)文件S1存儲(chǔ)文件S2存儲(chǔ)文件S4存儲(chǔ)文件S3SQL用戶VIEWBaseTableStoredfile40數(shù)據(jù)庫(kù)系統(tǒng)概念----SQLSQL概述標(biāo)準(zhǔn)化有關(guān)組織ANSI(AmericanNaturalStandardInstitute)ISO(InternationalOrganizationforStandardization)有關(guān)標(biāo)準(zhǔn)SQL-86“數(shù)據(jù)庫(kù)語(yǔ)言SQL”SQL-89“具有完整性增強(qiáng)的數(shù)據(jù)庫(kù)語(yǔ)言SQL”，增加了對(duì)完整性約束的支持SQL-92“數(shù)據(jù)庫(kù)語(yǔ)言SQL”，是SQL-89的超集，增加了許多新特性，如新的數(shù)據(jù)類型，更豐富的數(shù)據(jù)操作，更強(qiáng)的完整性、安全性支持等。SQL-2003正在討論中的新的標(biāo)準(zhǔn)，將增加對(duì)面向?qū)ο竽Ｐ偷闹С?1數(shù)據(jù)庫(kù)系統(tǒng)概念----SQLSQL概述特點(diǎn)一體化集DDL，DML，DCL于一體單一的結(jié)構(gòu)----關(guān)系，帶來(lái)了數(shù)據(jù)操作符的統(tǒng)一面向集合的操作方式一次一集合高度非過(guò)程化用戶只需提出“做什么”，無(wú)須告訴“怎么做”，不必了解存取路徑兩種使用方式，統(tǒng)一的語(yǔ)法結(jié)構(gòu)SQL既是自含式語(yǔ)言（用戶使用），又是嵌入式語(yǔ)言（程序員使用）語(yǔ)言簡(jiǎn)潔，易學(xué)易用42數(shù)據(jù)庫(kù)系統(tǒng)概念----SQLSQL概述SQL組成：數(shù)據(jù)定義語(yǔ)言（DDL）數(shù)據(jù)操縱語(yǔ)言（DML）數(shù)據(jù)控制語(yǔ)言（DCL）完整性（Integrity）視圖定義（ViewDefinition）事務(wù)控制（TransactionControl）嵌入式SQL和動(dòng)態(tài)SQL（EmbeddedSQLandDynamicSQL）授權(quán)（Authorization）43數(shù)據(jù)庫(kù)系統(tǒng)概念----SQLSQL概述GRANT，REVOKE數(shù)據(jù)控制INSERT，UPDATE，DELETE數(shù)據(jù)操縱CREATE，ALTER，DROP數(shù)據(jù)定義SELECT數(shù)據(jù)查詢操作符SQL功能44數(shù)據(jù)庫(kù)系統(tǒng)概念----SQLSQL數(shù)據(jù)定義功能基本表的定義索引的定義數(shù)據(jù)庫(kù)的建立與撤消SQL數(shù)據(jù)定義特點(diǎn)45數(shù)據(jù)庫(kù)系統(tǒng)概念----SQLSQL數(shù)據(jù)定義功能46數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL域定義域類型char（n）：固定長(zhǎng)度的字符串varchar2（n）：可變長(zhǎng)字符串numeric（p，d）：定點(diǎn)數(shù)，小數(shù)點(diǎn)左邊p位，右邊q位date：日期（年、月、日、時(shí)、分、秒）Bolb：二進(jìn)制數(shù)據(jù)的大對(duì)象數(shù)據(jù)類型47數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL基本表的定義CREATETABLE<表名>

（<列名><數(shù)據(jù)類型>[<列級(jí)完整性約束條件>][，<列名><數(shù)據(jù)類型>[<列級(jí)完整性約束條件>]]…[，<表級(jí)完整性約束條件>]）；<表名>：所要定義的基本表的名字<列名>：組成該表的各個(gè)屬性（列）<列級(jí)完整性約束條件>：涉及相應(yīng)屬性列的完整性約束條件<表級(jí)完整性約束條件>：涉及一個(gè)或多個(gè)屬性列的完整性約束條件48數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL基本表的定義基本表的定義（CREATE）格式

createtable

表名（列名數(shù)據(jù)類型[default

缺省值][notnull][，列名數(shù)據(jù)類型[default

缺省值][notnull]]…… [，primarykey（列名[，列名]…）] [，foreignkey

（列名[，列名]…）

references

表名（列名[，列名]…）] [，check（條件）]）49數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL基本表的定義示例

CREATETABLE

S (SNOCHAR（4）,

SNAMECHAR（8）

NOTNULL,

SEX

CHAR（1）, constraintpk_sPRIMARYKEY(SNO),

CHECK(SEX=0ORSEX=1) )50數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL基本表的定義CREATETABLE

C (CNOCHAR（4）,

CNAMECHAR（10）

UNIQUE,

TEACHER

CHAR（8）, constraintpk_cPRIMARYKEY(CNO), )51數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL基本表的定義CREATETABLESC (SNOCHAR（4）, CNOCHAR（4）, SCORE

SAMLLINT,

constraintpk_scPRIMARYKEY(SNO，CNO),

constraintfk_scsFOREIGNKEY(SNO) REFERENCESS(SNO),

constraintfk_sccFOREIGNKEY(CNO)

REFERENCESC(CNO),

CHECK((score

ISNULL)OR

scoreBETWEEN0AND100))52數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL基本表的定義常用完整性約束主碼約束：PRIMARYKEY唯一性約束：UNIQUE非空值約束：NOTNULL參照完整性約束：FOREIGNKEYPRIMARYKEY與

UNIQUE的區(qū)別？53數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL表空間一個(gè)數(shù)據(jù)庫(kù)被劃分為一個(gè)或多個(gè)稱為表空間的邏輯存儲(chǔ)單元表空間有一個(gè)或多個(gè)物理的數(shù)據(jù)文件組成。一個(gè)數(shù)據(jù)文件只可以與一個(gè)表空間和數(shù)據(jù)庫(kù)關(guān)聯(lián)一個(gè)Oracle數(shù)據(jù)庫(kù)中的數(shù)據(jù)邏輯上被存放在表空間中，物理上被存放在與相應(yīng)表空間關(guān)聯(lián)的數(shù)據(jù)文件中一個(gè)數(shù)據(jù)庫(kù)可以包括兩類表空間：SYSTEM（系統(tǒng)）表空間和非SYSTEM表空間54數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL基本表的定義修改基本表定義（ALTER）格式：

altertable

表名

[add

子句] 增加新列

[drop子句] 刪除列或者完整性約束

[modify

子句] 修改列定義

55數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL基本表的定義示例altertablePROF

addLOCATIONchar[30]；altertable

SMODIFYname

varchar2（30）；AltertablePROFaddLOCATIONchar[30]notnull；？？？56數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL基本表的定義ALTERTABLE<基本表名>DROP<列名>［CASCADE│RESTRICT］

此處CASCADE方式表示：在基本表中刪除某列時(shí)，所有引用到該列的視圖和約束也要一起自動(dòng)地被刪除。而RESTRICT方式表示在沒有視圖或約束引用該屬性時(shí)，才能在基本表中刪除該列，否則拒絕刪除操作。

57數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL基本表的定義刪除屬性列

直接/間接刪除把表中要保留的列及其內(nèi)容復(fù)制到一個(gè)新表中刪除原表再將新表重命名為原表名直接刪除屬性列:(新)例：ALTERTABLESDropScome

CASCADE

；58數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL基本表的定義刪除表中的約束Altertable表名

dropconstraint約束名AltertableSdropconstraintpk_s59數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL基本表的定義撤消基本表定義（drop）格式

droptable

表名示例

droptable

DEPTDANGER

撤消基本表后，基本表的定義、表中數(shù)據(jù)、索引、以及由此表導(dǎo)出的視圖的定義都被刪除60數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL索引的定義建立索引是加快查詢速度的有效手段建立索引DBA或表的屬主（即建立表的人）根據(jù)需要建立有些DBMS自動(dòng)建立以下列上的索引

PRIMARYKEYUNIQUE維護(hù)索引

DBMS自動(dòng)完成

使用索引

DBMS自動(dòng)選擇是否使用索引以及使用哪些索引61數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL索引的定義索引的定義格式create[unique/distinct][cluster]index

索引名on

表名(列名[asc/desc][,列名asc/desc]]…)

unique（distinct）：唯一性索引，不允許表中不同的行在索引列上取相同值。若已有相同值存在，則系統(tǒng)給出相關(guān)信息，不建此索引。系統(tǒng)并拒絕違背唯一性的插入、更新

cluster：聚集索引，表中元組按索引項(xiàng)的值排序并物理地聚集在一起。一個(gè)基本表上只能建一個(gè)聚集索引

asc/desc：索引表中索引值的排序次序，缺省為asc62數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL索引的定義示例：

createclusterindexs-indexonS（SNO）索引的刪除格式

dropindex

索引名63數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL索引的定義索引的有關(guān)說(shuō)明可以動(dòng)態(tài)地定義索引，即可以隨時(shí)建立和刪除索引不允許用戶在數(shù)據(jù)操作中引用索引，索引如何使用完全由系統(tǒng)決定應(yīng)該在使用頻率高的、經(jīng)常用于連接的列上建索引一個(gè)表上可建多個(gè)索引。索引可以提高查詢效率，但索引過(guò)多耗費(fèi)空間，且降低了插入、刪除、更新的效率64數(shù)據(jù)庫(kù)系統(tǒng)概念----SQLSQL數(shù)據(jù)定義語(yǔ)言特點(diǎn)SQL中，任何時(shí)候都可以執(zhí)行一個(gè)數(shù)據(jù)定義語(yǔ)句，隨時(shí)修改數(shù)據(jù)庫(kù)結(jié)構(gòu)。而在非關(guān)系型的數(shù)據(jù)庫(kù)系統(tǒng)中，必須在數(shù)據(jù)庫(kù)的裝入和使用前全部完成數(shù)據(jù)庫(kù)的定義。若要修改已投入運(yùn)行的數(shù)據(jù)庫(kù)，則需停下一切數(shù)據(jù)庫(kù)活動(dòng)，把數(shù)據(jù)庫(kù)卸出，修改數(shù)據(jù)庫(kù)定義并重新編譯，再按修改過(guò)的數(shù)據(jù)庫(kù)結(jié)構(gòu)重新裝入數(shù)據(jù)數(shù)據(jù)庫(kù)定義不斷增長(zhǎng)（不必一開始就定義完整）數(shù)據(jù)庫(kù)定義隨時(shí)修改（不必一開始就完全合理）可進(jìn)行增加索引、撤消索引的實(shí)驗(yàn)，檢驗(yàn)其對(duì)效率的影響65數(shù)據(jù)庫(kù)系統(tǒng)概念----SQLSQL數(shù)據(jù)查詢功能SQL數(shù)據(jù)查詢基本結(jié)構(gòu)select子句重復(fù)元組的處理from子句where子句更名運(yùn)算字符串操作元組顯示順序集合操作66數(shù)據(jù)庫(kù)系統(tǒng)概念----SQLSQL數(shù)據(jù)查詢功能分組和聚集函數(shù)空值嵌套子查詢派生關(guān)系視圖關(guān)系的連接67數(shù)據(jù)庫(kù)系統(tǒng)概念----SQLSQL數(shù)據(jù)查詢基本結(jié)構(gòu)基本結(jié)構(gòu)

select A1,A2,…,An

from T1,T2,…

,Tm

whereP

∏A1,A2,…,An(

p(T1T2

…Tm))68數(shù)據(jù)庫(kù)系統(tǒng)概念----SQLSQL數(shù)據(jù)查詢基本結(jié)構(gòu)語(yǔ)句格式SELECT[ALL|DISTINCT]<目標(biāo)列表達(dá)式>[，<目標(biāo)列表達(dá)式>]…FROM<表名或視圖名>[，<表名或視圖名>]…[WHERE<條件表達(dá)式>][GROUPBY<列名1>[HAVING<條件表達(dá)式>]][ORDERBY<列名2>[ASC|DESC]]；69數(shù)據(jù)庫(kù)系統(tǒng)概念----SQLSQL數(shù)據(jù)查詢基本結(jié)構(gòu)SELECT子句：指定要顯示的屬性列FROM子句：指定查詢對(duì)象(基本表或視圖)WHERE子句：指定查詢條件GROUPBY子句：對(duì)查詢結(jié)果按指定列的值分組，該屬性列值相等的元組為一個(gè)組。通常會(huì)在每組中作用集函數(shù)。HAVING短語(yǔ)：篩選出只有滿足指定條件的組ORDERBY子句：對(duì)查詢結(jié)果表按指定列值的升序或降序排序70數(shù)據(jù)庫(kù)系統(tǒng)概念----SQLSQL數(shù)據(jù)查詢基本結(jié)構(gòu)示例給出所有老師的姓名select PNAMEfrom PROF；71數(shù)據(jù)庫(kù)系統(tǒng)概念----SQLselect子句目標(biāo)列形式可以為列名，*，算術(shù)表達(dá)式，聚集函數(shù)“*”：表示“所有的屬性” 查詢老師的所有信息

select*

from PROF；72數(shù)據(jù)庫(kù)系統(tǒng)概念----SQLselect子句帶，，，的算術(shù)表達(dá)式 給出所有老師的姓名及稅后工資額

selectPNAME，SAL

0.95

from PROF；73數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL重復(fù)元組的處理語(yǔ)法約束缺省為保留重復(fù)元組，也可用關(guān)鍵字all顯式指明。若要去掉重復(fù)元組，可用關(guān)鍵字distinct或unique指明示例

查詢所有選修課程的學(xué)生

select

distinctSNO

fromSC；74數(shù)據(jù)庫(kù)系統(tǒng)概念----SQLfrom子句說(shuō)明from子句列出查詢的對(duì)象表當(dāng)目標(biāo)列取自多個(gè)表時(shí)，在不混淆的情況下可以不用顯式指明來(lái)自哪個(gè)關(guān)系示例例：找出工資低于500的職工的姓名、工資、系別

select PNAME,SAL,DNAME

from PROF,DEPT

where SAL<500

andPROF.DNO=DEPT.DNO；75數(shù)據(jù)庫(kù)系統(tǒng)概念----SQLfrom子句例：列出教授“哲學(xué)”課程的老師的教工號(hào)及姓名

selectPROF.PNO,PNAME

fromPROF,PC,C wherePROF.PNO=PC.PNO

andPC.CNO=C.CNO andC.CNAME=“哲學(xué)”；76數(shù)據(jù)庫(kù)系統(tǒng)概念----SQLwhere子句語(yǔ)法成分比較運(yùn)算符

、

、

、

、=、

邏輯運(yùn)算符and，or，notbetween條件判斷表達(dá)式的值是否在某范圍內(nèi)77數(shù)據(jù)庫(kù)系統(tǒng)概念----SQLwhere子句示例列出工資在500~1000之間的教師姓名

select

pname

fromPROFwhere

sal

between500and1000；

78數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL連接查詢同時(shí)涉及多個(gè)表的查詢稱為連接查詢用來(lái)連接兩個(gè)表的條件稱為連接條件或連接謂詞一般格式：[<表名1>.]<列名1><比較運(yùn)算符>[<表名2>.]<列名2>

比較運(yùn)算符：=、>、<、>=、<=、!=[<表名1>.]<列名1>BETWEEN[<表名2>.]<列名2>AND[<表名2>.]<列名3>79數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL更名運(yùn)算格式old_nameasnew_name

為關(guān)系和屬性重新命名，可出現(xiàn)在select和from子句中 注：as可選80數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL更名運(yùn)算示例例：給出所有老師的姓名、所納稅額及稅后工資額

selectPNAME，SAL

0.05astax，

SAL*0.95asincoming

fromPROF；81數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL元組變量SQL中的元組變量必須和特定的關(guān)系相聯(lián)系，元組變量是通過(guò)在from子句中使用as子句來(lái)定義的例：查詢選修了001號(hào)課程的學(xué)生學(xué)號(hào)和姓名Selecta.sno,snamefromsa,scbwherea.sno=b.snoandcno=‘001’82數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL元組變量Selects.sno,snamefroms,scwheres.sno=sc.snoandcno=‘001’此種SQL的表達(dá)方法，用關(guān)系名作為隱含的元組變量83數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL元組變量同一關(guān)系中的兩個(gè)元組查詢一門課的間接先行課

selecto,second.cpno

fromcfirst,csecondwherefirst.cpno=o;84數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL元組變量查詢數(shù)學(xué)成績(jī)比王紅同學(xué)數(shù)學(xué)成績(jī)高的學(xué)生89數(shù)學(xué)張軍86數(shù)學(xué)王紅93物理張軍成績(jī)課程姓名RSelectb.snamefromRa,Rbwherea.sname=‘王紅’andame=‘?dāng)?shù)學(xué)’and

ame=‘?dāng)?shù)學(xué)’andb.score>a.score

85數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL元組變量SQL允許用記號(hào)（v1,v2,…,vn）表示一個(gè)有n個(gè)分量的元組，各分量的值分別為v1,v2,…,vn。元組上可以使用比較運(yùn)算符，按字典順序進(jìn)行比較運(yùn)算86數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL字符串操作命令格式格式列名[not]like“字符串” 找出滿足給定匹配條件的字符串87數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL字符串操作匹配規(guī)則“%”匹配零個(gè)或多個(gè)字符“＿”匹配任意單個(gè)字符Escape定義轉(zhuǎn)義字符，以去掉特殊字符的特定含義，使其被作為普通字符看待如escape“\”，定義\作為轉(zhuǎn)義字符，則可用\%去匹配%，用\＿去匹配＿思考：用什么去匹配\？88數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL字符串操作示例列出姓名以“張”打頭的教師的所有信息

select*

fromPROF

wherePNAMElike“張%”89數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL字符串操作列出名稱中含有4個(gè)字符以上，且倒數(shù)第3個(gè)字符是d，倒數(shù)第2個(gè)字符是_的系的所有信息

select*

fromPROF

wherePNAMElike‘%＿d\＿＿’’escape\’90數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL元組顯示順序命令orderby

列名[asc|desc]示例按系名升序列出老師姓名，所在系名，同一系中老師按姓名降序排列

selectDNAME，PNAME

fromPROF，DEPT

wherePROF.DNO=DEPT.DNO orderbyDNAMEasc，PNAMEdesc91數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL元組顯示順序當(dāng)排序列含空值時(shí)ASC：排序列為空值的元組最后顯示DESC：排序列為空值的元組最先顯示92數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL集合操作命令集合并：union集合交：intersect集合差：

except（minus）提示集合操作自動(dòng)去除重復(fù)元組，如果要保留重復(fù)元組的話，必須用all關(guān)鍵詞指明93數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL集合操作示例求選修了001或（且）002號(hào)課程的學(xué)生號(hào)

(selectSNO

fromSC

whereCNO=001) union(intersect)

(selectSNO

fromSC

whereCNO=002);94數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL集合操作求選修了001和002號(hào)而沒有選003號(hào)課程的學(xué)生號(hào)

(selectSNO

fromSC

whereCNO=001orCNO=002) except

(selectSNO

fromSC

whereCNO=003)95數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL聚集函數(shù)聚集函數(shù)平均值：avg最小值：min最大值：max總和：sum記數(shù)：countcount（*）與count（列名）的差別96數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL聚集函數(shù)查詢學(xué)生總?cè)藬?shù)SELECTCOUNT（*）

FROMS；查詢選修了課程的學(xué)生人數(shù)SELECTcount(distinct

sno)FROMSC;97數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL聚集函數(shù)分組命令groupby

列名[having

條件表達(dá)式]groupby將表中的元組按指定列上值相等的原則分組，然后在每一分組上使用聚集函數(shù)，得到單一值

having則對(duì)分組進(jìn)行選擇，只將聚集函數(shù)作用到滿足條件的分組上98數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL聚集函數(shù)GROUPBY子句的作用對(duì)象是查詢的中間結(jié)果表分組方法：按指定的一列或多列值分組，值相等的為一組使用GROUPBY子句后，SELECT子句的列名列表中只能出現(xiàn)分組屬性和聚集函數(shù)99數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL聚集函數(shù)SNOCNOSCOREs1c184s1c290s1c396s2c180s2c290s3c296s3c388{{{SNOCNOSCOREs1c184s1c290s1c396s2c180s2c290s3c296s3c388列出每個(gè)學(xué)生的平均成績(jī)列出每門課程的平均成績(jī)groupbySNOgroupbyCNO928590929282100數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL聚集函數(shù)示例列出各系的老師的最高、最低、平均工資

selectDNO，max(SAL)，min(SAL)，avg(SAL)

fromPROFgroupbyDNO101數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL聚集函數(shù)示例

①求選修了課程的學(xué)生人數(shù)

select

count(SNO)

fromSC②selectPNAME，max(SAL)

fromPROF③selectDNO，avg(SAL)

fromPROFwhereAGE>60groupbyDNO102數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL聚集函數(shù)列出及格的學(xué)生的平均成績(jī)

哪個(gè)正確？selectSNO，avg(SCORE)

fromSC groupbySNO

having

min(SCORE)>=60selectSNO，avg(SCORE)

fromSC

whereSCORE>=60 groupbySNO103數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL聚集函數(shù)列出每一年齡組中男學(xué)生（超過(guò)50人）的人數(shù)

selectage，count(SNO)

fromS

whereSEX=‘M’groupbyage

havingcount(*)>50104數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL聚集函數(shù)查詢有3門以上課程是90分以上的學(xué)生的學(xué)號(hào)及（90分以上的）課程數(shù)

SELECTSno,COUNT(*)FROMSCWHEREscore>=90GROUPBYSnoHAVINGCOUNT(*)>=3;105數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL空值空值測(cè)試is

[not]null測(cè)試指定列的值是否為空值示例找出年齡值為空的老師姓名

selectPNAMEfromPROFwhereAGEisnull;不可寫為whereAGE=null106數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL空值注意事項(xiàng)除is[not]null之外，空值不滿足任何查找條件如果null參與算術(shù)運(yùn)算，則該算術(shù)表達(dá)式的值為null如果null參與比較運(yùn)算，則結(jié)果可視為false。如果null參與聚集運(yùn)算，則除count(*)之外其它聚集函數(shù)都忽略null

107數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL空值例：select

sum(score)

fromSC例：select

count(*)

fromSC

例：selectcount(score)fromSCSNOCNOSCOREs1c180s1c290s1c395s2c185s2c2nulls3c2null36064108數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL嵌套子查詢(NestedSubqueries)集合成員資格集合之間的比較集合基數(shù)的測(cè)試測(cè)試集合是否為空測(cè)試集合是否存在重復(fù)元組109數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL集合成員資格in子查詢表達(dá)式[not]in

（子查詢）判斷表達(dá)式的值是否在子查詢的結(jié)果中示例列出張軍和王紅同學(xué)的所有信息

select*

fromS

whereSNAMEin（“張軍”，“王紅”）110數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL集合成員資格選修了001號(hào)課程的學(xué)生的學(xué)號(hào)及姓名

selectSNO，SNAME

fromSwhereSNOin (selectSNO

fromSC

whereCNO=001）外層查詢/父查詢內(nèi)層查詢/子查詢111數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL集合成員資格子查詢的限制不能使用ORDERBY子句層層嵌套方式反映了SQL語(yǔ)言的結(jié)構(gòu)化有些嵌套查詢可以用連接運(yùn)算替代112數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL集合成員資格不相關(guān)子查詢子查詢的查詢條件不依賴于父查詢相關(guān)子查詢子查詢的查詢條件依賴于父查詢113數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL集合成員資格列出選修了001號(hào)且選修了002號(hào)課程的學(xué)生的學(xué)號(hào)

selectSNO

fromSC

whereSC.CNO=001

andSNOin

（selectSNO

fromSC

whereCNO=002）114數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL集合成員資格查詢與“劉晨”在同一個(gè)系學(xué)習(xí)的學(xué)生SELECTsno，sname，dnoFROMSWHEREdnoIN(SELECTdnoFROMSWHEREname=‘劉晨’)；115數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL集合之間的比較當(dāng)能確切知道內(nèi)層查詢返回單值時(shí)，可用比較運(yùn)算符（>，<，=，>=，<=，!=或<>）116數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL集合之間的比較查詢與“劉晨”在同一個(gè)系學(xué)習(xí)的學(xué)生SELECTsno，name，dnoFROMSWHEREdno=(SELECTdnoFROMSWHEREname=‘劉晨’)；117數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL集合之間的比較子查詢一定要跟在比較符之后

錯(cuò)誤的例子：

SELECTsno，name，dnoFROMSWHERE(SELECTdnoFROMSWHEREname=‘劉晨’)

=dno；118數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL集合之間的比較some/all子查詢表達(dá)式比較運(yùn)算符

some

（子查詢）表達(dá)式的值至少與子查詢結(jié)果中的一個(gè)值相比滿足比較運(yùn)算符

表達(dá)式比較運(yùn)算符

all

（子查詢）表達(dá)式的值與子查詢結(jié)果中的所有的值相比都滿足比較運(yùn)算符

119數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL集合之間的比較-查詢其他系中比d1系某個(gè)學(xué)生年齡小的學(xué)生姓名和年齡

SELECTsname，ageFROMSWHEREage<some(SELECTageFROMSWHEREdno='d1’)

ANDdno<>'d1';/*注意這是父查詢塊中的條件*/120數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL集合之間的比較SOME和ALL謂詞有時(shí)可以用集函數(shù)實(shí)現(xiàn)some與ALL與集函數(shù)的對(duì)應(yīng)關(guān)系

=<>或!=

<

<=

>

>=SOME

IN

--<MAX<=MAX>MIN>=MINALL

--NOTIN<MIN<=MIN>MAX>=MAX121數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL集合之間的比較-查詢其他系中比d1系任意一個(gè)學(xué)生年齡小的學(xué)生姓名和年齡SELECTname，ageFROMSWHEREage<(SELECTMAX(age)FROMSWHEREdno='d1')ANDdno<>'d1';122數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL集合之間的比較示例找出平均成績(jī)最高的學(xué)生號(hào)

selectSNO

fromSC

groupbySNO

having

avg(SCORE)>=all

（select

avg(SCORE)

fromSC

groupbySNO）123數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL測(cè)試是否為空關(guān)系測(cè)試集合是否為空[not]exists

（子查詢）判斷子查詢的結(jié)果集合中是否有任何元組存在

in后的子查詢與外層查詢無(wú)關(guān)，每個(gè)子查詢執(zhí)行一次，而exists后的子查詢與外層查詢有關(guān)，需要執(zhí)行多次，稱之為相關(guān)子查詢124數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL測(cè)試是否為空關(guān)系1.EXISTS謂詞存在量詞

帶有EXISTS謂詞的子查詢不返回任何數(shù)據(jù)，只產(chǎn)生邏輯真值“true”或邏輯假值“false”。若內(nèi)層查詢結(jié)果非空，則返回真值若內(nèi)層查詢結(jié)果為空，則返回假值由EXISTS引出的子查詢，其目標(biāo)列表達(dá)式通常都用*，因?yàn)閹XISTS的子查詢只返回真值或假值，給出列名無(wú)實(shí)際意義2.NOTEXISTS謂詞125數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL測(cè)試是否為空關(guān)系列出選修了C1號(hào)課程的學(xué)生的姓名

selectNAME

fromS

whereexists

（select*

fromSC

whereCNO='C1‘

andSNO=S.SNO）126數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL測(cè)試是否為空關(guān)系相關(guān)子查詢首先取外層查詢中表的第一個(gè)元組，根據(jù)它與內(nèi)層查詢相關(guān)的屬性值處理內(nèi)層查詢，若WHERE子句返回值為真，則取此元組放入結(jié)果表；然后再取外層表的下一個(gè)元組；重復(fù)這一過(guò)程，直至外層表全部檢查完為止。127數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL測(cè)試是否為空關(guān)系思路分析：本查詢涉及S和SC關(guān)系。在S中依次取每個(gè)元組的Sno值，用此值去檢查SC關(guān)系。若SC中存在這樣的元組，其Sno值等于此S.Sno值，并且其Cno='1'，則取此S.name送入結(jié)果關(guān)系。128數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL測(cè)試是否為空關(guān)系-查詢沒有選修C1號(hào)課程的學(xué)生姓名。SELECTnameFROMSWHERENOTEXISTS(SELECT*FROMSCWHERESno=S.SnoANDCno=C1);129數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL測(cè)試是否為空關(guān)系查詢同時(shí)選修了001號(hào)和002號(hào)課程的學(xué)生的學(xué)號(hào)

selectSNO

fromSCSC1

whereSC1.CNO=001

and

exists

（selectSNO

fromSCSC2

whereSC2.CNO=002

andSC2.SNO=SC1.SNO）130數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL測(cè)試是否為空關(guān)系用EXISTS/NOTEXISTS實(shí)現(xiàn)全稱量詞SQL語(yǔ)言中沒有全稱量詞

（Forall）可以把帶有全稱量詞的謂詞轉(zhuǎn)換為等價(jià)的帶有存在量詞的謂詞：

(

x)P≡

(

x(

P))131數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL測(cè)試是否為空關(guān)系列出選修了全部課程的學(xué)生姓名

selectSNAMEfromSwherenotexists

（selectcno fromC wherenotexists

（select* fromSC whereSC.cno=C.cno andSC.sno=S.sno

））任意課程，所求學(xué)生選之

不存在任何一門課程，所求學(xué)生沒有選之132數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL測(cè)試是否為空關(guān)系用EXISTS/NOTEXISTS實(shí)現(xiàn)邏輯蘊(yùn)函SQL語(yǔ)言中沒有蘊(yùn)函(Implication)邏輯運(yùn)算可以利用謂詞演算將邏輯蘊(yùn)函謂詞等價(jià)轉(zhuǎn)換為：

p

q≡

p∨q133數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL測(cè)試是否為空關(guān)系查詢至少選修了學(xué)生002選修的全部課程的學(xué)生號(hào)碼。解題思路：用邏輯蘊(yùn)函表達(dá)：查詢學(xué)號(hào)為x的學(xué)生，對(duì)所有的課程y，只要002學(xué)生選修了課程y，則x也選修了y。形式化表示： 用P表示謂詞“學(xué)生002選修了課程y”

用q表示謂詞“學(xué)生x選修了課程y”

則上述查詢?yōu)?(

y)p

q134數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL測(cè)試是否為空關(guān)系等價(jià)變換：

(

y)p

q≡

(

y(

(p

q))≡

(

y(

(

p∨q)≡

y(p∧

q)變換后語(yǔ)義：不存在這樣的課程y，學(xué)生002選修了y，而學(xué)生x沒有選。135數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL測(cè)試是否為空關(guān)系用NOTEXISTS謂詞表示：

SELECTDISTINCTSnoFROMSCSCXWHERENOTEXISTS(SELECT*FROMSCSCYWHERESCY.Sno='002'ANDNOTEXISTS(SELECT*FROMSCSCZWHERESCZ.Sno=SCX.SnoAND

SCZ.Cno=SCY.Cno))136數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL測(cè)試是否為空關(guān)系-關(guān)系A(chǔ)包含B:notexists(BexceptA)-選修了全部課程的學(xué)生姓名Selectname

fromSWherenotexists(selectcno

fromCexcept(selectcnofromSCwheres.sno=sc.sno));137數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL測(cè)試是否為空關(guān)系-未選課的學(xué)生姓名Selectname

fromSWherenotexists(selectcno

fromCintersect(selectcnofromSCwheres.sno=sc.sno));138數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL復(fù)雜查詢--派生關(guān)系命令（子查詢）as

關(guān)系名（列名，列名，…）

SQL-92中，允許在from子句中使用子查詢表達(dá)式，這時(shí)可將該子查詢的結(jié)果命名為一個(gè)臨時(shí)關(guān)系加以引用139數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL復(fù)雜查詢--派生關(guān)系示例找出平均成績(jī)及格的學(xué)生先求出每個(gè)學(xué)生的平均成績(jī)，再?gòu)闹姓页黾案竦膶W(xué)生

selectSNAME，

avg（SCORE）

fromS，SC

whereSC.SNO=S.SNO

groupbyS.SNAME140數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL復(fù)雜查詢--派生關(guān)系selectSNAME，AVG_SCOREfrom

(selectSNAME，

avg（SCORE）

fromS，SC

whereSC.SNO=S.SNO

groupbyS.SNAME)

asresult(SNAME，AVG_SCORE)whereAVG_SCORE>=60141數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL視圖視圖的特點(diǎn)虛表，是從一個(gè)或幾個(gè)基本表（或視圖）導(dǎo)出的關(guān)系只存放視圖的定義，不會(huì)出現(xiàn)數(shù)據(jù)冗余基表中的數(shù)據(jù)發(fā)生變化，從視圖中查詢出的數(shù)據(jù)也隨之改變142數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL視圖定義視圖

createviewview_name[(列名，列名

…)]

as

（查詢表達(dá)式）-視圖的屬性名缺省為子查詢結(jié)果中的屬性名，也可以顯式指明，在下列情況下，必須指明視圖的所有列名：

-某個(gè)目標(biāo)列是聚集函數(shù)或者目標(biāo)列表達(dá)式

-多表連接時(shí)，選出了幾個(gè)同名列作為視圖的字段

-需要在視圖中為某個(gè)列啟用新的更合適的名字

-目標(biāo)列是*

143數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL視圖撤消視圖

dropviewview_name144數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL視圖示例createviewCOMPUTER_PROF

as(selectPNO,PNAME,SAL

fromPROF，DEPT

wherePROF.PNO=DEPT.PNO

andDEPT.DNAME=“計(jì)算機(jī)系”）；145數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL視圖CREATEVIEWIS_StudentASSELECTSno，Sname，ageFROMSWHERESdept='IS'；從單個(gè)基本表導(dǎo)出只是去掉了基本表的某些行和某些列保留了碼，行列子集視圖146數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL視圖定義一個(gè)反映學(xué)生出生年份的視圖。

CREATEVIEWBT_S(Sno，name，Sbirth)ASSELECTSno，name，2007-SageFROMS設(shè)置一些派生屬性列,也稱為虛擬列--Sbirth

帶表達(dá)式的視圖必須明確定義組成視圖的各個(gè)屬性列名147數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL視圖createviewDEPTSAL(DNO,LOW,HIGH,AVERAGE,TOTAL)

as(selectDNO,min(SAL),max(SAL),avg(SAL),sum(SAL)

fromPROF

groupbyDNO)；148數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL視圖給出計(jì)算機(jī)系工資超過(guò)800的老師姓名

selectPNAME fromCOMPUTER_PROF whereSAL>800給出計(jì)算機(jī)系老師的最低、最高、平均工資以及工資總額

selectLOW,HIGH,AVERAGE,TOTAL fromDEPTSAL,DEPT whereDEPTSAL.DNO=DEPT.DNO andDEPT.DNAME=“計(jì)算機(jī)系”149數(shù)據(jù)庫(kù)系統(tǒng)概念----SQL視圖從用戶角度：查詢視圖與查詢基本表相同視圖展開Repeat找出e1中的任何視圖關(guān)系vi用定義視圖vi的表達(dá)式替換視圖關(guān)系viUntile1

中不再存在任何視圖關(guān)系