數(shù)據(jù)庫原理第三講_第1頁
數(shù)據(jù)庫原理第三講_第2頁
數(shù)據(jù)庫原理第三講_第3頁
數(shù)據(jù)庫原理第三講_第4頁
數(shù)據(jù)庫原理第三講_第5頁
已閱讀5頁,還剩146頁未讀, 繼續(xù)免費(fèi)閱讀

下載本文檔

版權(quán)說明:本文檔由用戶提供并上傳,收益歸屬內(nèi)容提供方,若內(nèi)容存在侵權(quán),請進(jìn)行舉報(bào)或認(rèn)領(lǐng)

文檔簡介

1、數(shù)據(jù)庫原理及應(yīng)用教案數(shù)據(jù)庫原理及應(yīng)用教案計(jì)算機(jī)科學(xué)學(xué)院計(jì)算機(jī)科學(xué)學(xué)院第3章 數(shù)據(jù)庫的標(biāo)準(zhǔn)語言SQLn3.1 SQL概述n3.2 數(shù)據(jù)定義n3.3 數(shù)據(jù)查詢n3.4 數(shù)據(jù)更新n3.5 SQL的完整性控制n3.6 SQL中的觸發(fā)器n3.7 嵌入式SQL第3章 數(shù)據(jù)庫的標(biāo)準(zhǔn)語言SQLnSQL(Structrued Query Language)早已確立自己作為關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言的地位,已被眾多商用 DBMS 產(chǎn)品所采用, 使得它已成為關(guān)系數(shù)據(jù)庫領(lǐng)域中一個(gè)主流語言。 它不僅包含了數(shù)據(jù)查詢功能,還包括插入、刪除、更新和數(shù)據(jù)定義功能。作為一個(gè) SQL數(shù)據(jù)庫是表的匯集,它用一個(gè)或多個(gè) SQL模式定義。作為

2、SQL的用戶可以是應(yīng)用程序,也可以是終端用戶。31 SQL概述3.1.1 SQL語句的特征n盡管人們習(xí)慣性地稱SQL是一個(gè) “查詢語言 ”,但實(shí)際上,它的功能遠(yuǎn)非查詢信息這么簡單。主要包括:n數(shù)據(jù)查詢(Query)n數(shù)據(jù)操縱(Manipulation)n數(shù)據(jù)定義(Definition)n數(shù)據(jù)控制(Control)1SQL的特點(diǎn)n(1)綜合統(tǒng)一n(2)高度非過程化n(3)面向集合的操作方式n(4)二種使用方式n(5)語言簡潔、易學(xué)易用1SQL的特點(diǎn)nSQL 語言功能極強(qiáng),完成核心功能只用了9個(gè)動(dòng)詞,包括如下四類:n數(shù)據(jù)查詢:SELECTn數(shù)據(jù)定義:CREATE、DROP、ALTERn數(shù)據(jù)操縱:I

3、NSERT、UODATE、DELETEn數(shù)據(jù)控制:GRANT、REVORK。nSQL語言支持關(guān)系數(shù)據(jù)庫的三級模式結(jié)構(gòu),其中:n視圖對應(yīng)外模式n基本表對應(yīng)模式n存儲(chǔ)文件對應(yīng)內(nèi)模式n具體結(jié)構(gòu)如圖3-1所示。2SQL支持三級模式結(jié)構(gòu) SQLView(視圖)(視圖)1View(視圖)(視圖)2Base Table 1(基本表)(基本表)Base Table2(基本表)(基本表)Base Table 3(基本表)(基本表)Base Table4(基本表)(基本表)Sored File 1(存儲(chǔ)文件)(存儲(chǔ)文件)Sored File 2(存儲(chǔ)文件(存儲(chǔ)文件)Sored File 3(存儲(chǔ)文件)(存儲(chǔ)文件)

4、內(nèi)模式內(nèi)模式圖圖3-1 關(guān)系數(shù)據(jù)庫的三級模式結(jié)構(gòu)關(guān)系數(shù)據(jù)庫的三級模式結(jié)構(gòu)模式模式外模式外模式 3.1.2 SQL的基本組成 nSQL由以下幾個(gè)部分組成:n(1)數(shù)據(jù)定義語言(DDL):提供定義關(guān)系模式和視圖、刪除關(guān)系和視圖、修改關(guān)系模式的命令。n(2)交互式數(shù)據(jù)操縱語言(DML):提供查詢、插入、刪除和修改的命令。n(3)事務(wù)控制(transaction control):SQL提供定義事務(wù)開始和結(jié)束的命令。SQL組成部分n(4)嵌入式SQL和動(dòng)態(tài)SQL(embedded SQL and dynamic SQL)用于嵌入到某種通用的高級語言中混合編程。其中 SQL 負(fù)責(zé)操縱數(shù)據(jù)庫,高級語言負(fù)責(zé)

5、控制程序流程。n(5)完整性(integrity):SQL DDL 包括定義數(shù)據(jù)庫中的數(shù)據(jù)必須滿足的完整性約束條件的命令,對于破壞完整性約束條件的更新將被禁止。n(6)權(quán)限管理(authorization):SQL DDL中包括說明對關(guān)系和視圖的訪問權(quán)限。 32 數(shù)據(jù)定義n基本表和視圖都是表?;颈硎菍?shí)際存儲(chǔ)在數(shù)據(jù)庫中的表。視圖是虛表,它是從基本表或其它視圖中導(dǎo)出的表。數(shù)據(jù)庫中只存放視圖的定義而不存放視圖的數(shù)據(jù)。這些數(shù)據(jù)仍存放在導(dǎo)出視圖的基本表中。用戶可用SQL 語句對基本表和視圖進(jìn)行查詢等操作。一個(gè)表可以帶若干索引,索引也存儲(chǔ)在存儲(chǔ)文件中。每個(gè)存儲(chǔ)文件就是外部存儲(chǔ)器上一個(gè)物理文件,存儲(chǔ)文件的

6、邏輯結(jié)構(gòu)組成了關(guān)系數(shù)據(jù)庫的內(nèi)模式。nSQL的數(shù)據(jù)定義包括對表、視圖、索引的創(chuàng)建和刪除。 3.2.1創(chuàng)建表 (CREATE TABLE)n語句格式:nCREATE TABLE (列級完整性約束條件, 列級完整性約束條件,);3.2.1創(chuàng)建表 (CREATE TABLE)n列級完整性約束條件有:nNULL(空)nUNIQUE(取值唯一),如NOT NULL UNIQUE 表示取值唯一,不能取空值。舉例 【例3.1】建立一個(gè)供應(yīng)商、零件數(shù)據(jù)庫。其中“供應(yīng)商”表S(Sno,Sname,Status,City)分別表示:供應(yīng)商代碼、供應(yīng)商名、供應(yīng)商狀態(tài)、供應(yīng)商所在城市?!傲慵北鞵(Pno,Pname,

7、Color,Weight,City),表示零件號、零件名、顏色、重量及產(chǎn)地。其中,數(shù)據(jù)庫要滿足如下要求:n(1)供應(yīng)商代碼不能為空,且值是唯一的,供應(yīng)商的名也是唯一的。n(2)零件號不能為空,且值是唯一的。零件名不能為空n(3)一個(gè)供應(yīng)商可以供應(yīng)多個(gè)零件,而一個(gè)零件可以由多個(gè)供應(yīng)商供應(yīng)。舉例n分析:根據(jù)題意供應(yīng)商和零件分別要建立一個(gè)關(guān)系模式。供應(yīng)商和零件之間是一個(gè)多對多的聯(lián)系,在關(guān)系數(shù)據(jù)庫中,多對多聯(lián)系必須生成一個(gè)關(guān)系模式,而該模式的碼則是由該聯(lián)系兩端實(shí)體的碼加上聯(lián)系的屬性構(gòu)成的,若該聯(lián)系名為SP,那麼關(guān)系模式為SP(Sno,Pno,Qty),其中Qty表示零件的數(shù)量。n根據(jù)上述分析,用SQL

8、建立一個(gè)供應(yīng)商、零件數(shù)據(jù)庫如下:建立供應(yīng)商、零件數(shù)據(jù)庫 CREATE TABLE S(Sno CHAR(5) NOT NULL UNIQUE, Sname CHAR(30) UNIQUE, Status CHAR(8) , City CHAR(20) PRIMARY KEY(Sno); CREATE TABLE P(Pno CHAR(6), Pname CHAR(30) NOT NULL, Color CHAR(8), Weight NUMERIC (6,2), City CHAR(20) PRIMARY KEY(Pno);建立供應(yīng)商、零件數(shù)據(jù)庫 CREATE TABLE SP(Sno CHA

9、R(5), Pno CHAR(6), Status CHAR(8) , Qty NUMERIC(9), PRIMARY KEY(Sno,Pno) FOREIGN KEY(Sno) REFERENCES S(Sno), FOREIGN KEY(Pno) REFERENCES P(Pno);舉例n從上述定義可以看出,“Sno CHAR(5) NOT NULL UNIQUE”語句定義了Sno的列級完整約束條件,取值唯一,不能取空值,需要說明如下:n(1)PRIMARY KEY(Sno)已經(jīng)定義了Sno為主碼,所以,“ Sno CHAR(5) NOT NULL UNIQUE”語句中的“NOT NULL

10、 UNIQUE”可以省略。 n(2)“ FOREIGN KEY(Sno)REFERENCES S(Sno)”定義了在SP關(guān)系中Sno為外碼,其取值必須來自S關(guān)系中的Sno域。同理在SP關(guān)系中Pno也定義為外碼。 3.2.2 修改表和刪除表n1修改表(ALTER TABLE)n語句格式:nALTER TABLE ADD完整性約束條件 DROPMODIFY ;舉例n例如,向“供應(yīng)商”表S增加Zap“郵政編碼”可用如下語句:nALTER TABLE S ADD Zap CHAR(6);n注意,不論基本表中原來是否已有數(shù)據(jù),新增加的列一律為空。n又如,將Status字段改為整型可用如下信息:nALTE

11、R TABLE S MODIFY Status INT;2刪除表(DROP TABLE)n語句格式:DROP TABLE n例如,執(zhí)行DROP TABLE Student;此后關(guān)系 Student不再是數(shù)據(jù)庫模式的一部分,關(guān)系中的元組也無法訪問。 3.2.3定義和刪除索引n在數(shù)據(jù)庫中,索引使數(shù)據(jù)庫程序無需對整個(gè)表進(jìn)行掃描,就可以在其中找到所需數(shù)據(jù)。數(shù)據(jù)庫中的索引是某個(gè)表中一列或若干列值的集合和相應(yīng)的指向表中物理標(biāo)識這些值的數(shù)據(jù)頁的邏輯指針清單。 3.2.3定義和刪除索引n索引的作用如下:n通過創(chuàng)建唯一索引,可以保證數(shù)據(jù)記錄的唯一性。n可以大大加快數(shù)據(jù)檢索速度。n可以加速表與表之間的連接,這一點(diǎn)

12、在實(shí)現(xiàn)數(shù)據(jù)的參照完整性方面有特別的意義。n在使用ORDER BY和GROUP BY子句中進(jìn)行檢索數(shù)據(jù)時(shí),可以顯著減少查詢中分組和排序的時(shí)間。n使用索引可在檢索數(shù)據(jù)的過程中使用優(yōu)化隱藏器,提高系統(tǒng)性能。1聚集索引和非聚集索引n聚集索引對表的物理數(shù)據(jù)頁中的數(shù)據(jù)按列進(jìn)行排序,然后再重新存儲(chǔ)到磁盤上,即聚集索引與數(shù)據(jù)是混為一體的,它的葉節(jié)點(diǎn)中存儲(chǔ)的是實(shí)際的數(shù)據(jù)。 n非聚集索引具有完全獨(dú)立于數(shù)據(jù)行的結(jié)構(gòu),使用非聚集索引不用將物理數(shù)據(jù)頁中的數(shù)據(jù)按列排序。非聚集索引的葉節(jié)點(diǎn)存儲(chǔ)了組成非聚集索引的關(guān)鍵字值和行定位器。 2建立索引n語句格式:nCREATE UNIQUECLUSTER INDEX ON ( ,)

13、; 2建立索引n說明:n(1)次序:可選ASC(升序)或DSC(降序),默認(rèn)值為ASC。n(2)UNIQUE:表明此索引的每一個(gè)索引值只對應(yīng)唯一的數(shù)據(jù)記錄。n(3)CLUSTER:表明要建立的索引是聚簇索引,意為索引項(xiàng)的順序是與表中記錄的物理順序 一致的索引組織。舉例 【例3.2】 假設(shè)供應(yīng)銷售數(shù)據(jù)庫中有供應(yīng)商S、零件P、工程項(xiàng)目J、供銷情況SPJ關(guān)系,希望建立四個(gè)索引。其中:供應(yīng)商S中Sno按升序建立索引;零件P中 Pno按升序建立索引;工程項(xiàng)目J中 Jno 按升序建立索引;對供銷情況SPJ中的Sno按升序,Pno按降序,Jno按升序建立索引。舉例 解:根據(jù)題意建立的索引如下 CREATE

14、UNIQUE INDEX S-SNO ON S(Sno); CREATE UNIQUE INDEX P-PNO ON P(Pno); CREATE UNIQUE INDEX J-JNO ON J(Jno); CREATE UNIQUE INDEX SPJ-NO ON SPJ(Sno ASC,Pno DESC,JNO ASC);3刪除索引n語句格式:DROP INDEX n例如,執(zhí)行 DROP INDEX StudentIndex;此后索引StudentIndex不再是數(shù)據(jù)庫模式的一部分。3.2.4 定義、刪除、更新視圖n視圖是從一個(gè)或多個(gè)表或視圖中導(dǎo)出的表,其結(jié)構(gòu)和數(shù)據(jù)是建立在對表的查詢基礎(chǔ)上

15、的。n視圖包括幾個(gè)被定義的數(shù)據(jù)列和多個(gè)數(shù)據(jù)行,但從其本質(zhì)上講,這些數(shù)據(jù)列和數(shù)據(jù)行來源于其所引用的表。n視圖不是真實(shí)存在的基礎(chǔ)表而是一個(gè)虛擬表,視圖所對應(yīng)的數(shù)據(jù)并不實(shí)際地以視圖結(jié)構(gòu)存儲(chǔ)在數(shù)據(jù)庫中,而是存儲(chǔ)在視圖所引用的表中。3.2.4 定義、刪除、更新視圖 n視圖的優(yōu)點(diǎn)和作用 n可以使視圖集中數(shù)據(jù)、簡化和定制不同用戶對數(shù)據(jù)庫的不同數(shù)據(jù)要求。n使用視圖可以屏蔽數(shù)據(jù)的復(fù)雜性,用戶不必了解數(shù)據(jù)庫的結(jié)構(gòu),就可以方便地使用和管理數(shù)據(jù),簡化數(shù)據(jù)權(quán)限管理和重新組織數(shù)據(jù)以便輸出到其他應(yīng)用程序中。n視圖可以使用戶只關(guān)心他感興趣的某些特定數(shù)據(jù)和他們所負(fù)責(zé)的特定任務(wù),而那些不需要的或者無用的數(shù)據(jù)則不在視圖中顯示。視圖

16、的優(yōu)點(diǎn)和作用n視圖大大地簡化了用戶對數(shù)據(jù)的操作。n視圖可以讓不同的用戶以不同的方式看到不同或者相同的數(shù)據(jù)集。n在某些情況下,由于表中數(shù)據(jù)量太大,因此在表的設(shè)計(jì)時(shí)常將表進(jìn)行水平或者垂直分割,但表的結(jié)構(gòu)的變化對應(yīng)用程序產(chǎn)生不良的影響,視圖則避免了這樣的問題。 n視圖提供了一個(gè)簡單而有效的安全機(jī)制。 1視圖的創(chuàng)建n 語句格式:nCREATE VIEW 視圖名 (列表名) AS SELECT 查詢子句 WITH CHECK OPTION;1視圖的創(chuàng)建n視圖的創(chuàng)建中,必須遵循如下規(guī)定:n(1)子查詢可以是任意復(fù)雜的 SELECT 語句,但通常不允許含有order by 子句和DISTINCT短語。n(2

17、)WITH CHECK OPTION表示對UPDATE,INSERT,DELETE操作時(shí)保證更新、插入、或刪除的行滿足視圖定義中的謂詞條件(即子查詢中的條件表達(dá)式)。n(3)組成視圖的屬性列名或者全部省略或者全部指定。如果省略屬性列名,則隱含該視圖由SELECT子查詢目標(biāo)列的主屬性組成。舉例n【例3.3】建立“計(jì)算機(jī)系”(CS表示計(jì)算機(jī)系)學(xué)生的視圖,并要求進(jìn)行修改、插入操作時(shí)保證該視圖只有計(jì)算機(jī)系的學(xué)生。nCREATE VIEW CS-STUDENT AS SELECT Sno,Sname,Sage,Sex FROM Students WHERE SD=CS WITH CHECK OPTIO

18、N;n由于在CS_STUDENT視圖中使用了“WITH CHECK OPTION” 子句,因此,對該視圖進(jìn)行修改、插入操作時(shí)DBMS會(huì)自動(dòng)加上SD=CS的條件,保證該視圖只有計(jì)算機(jī)系的學(xué)生。舉例2視圖的撤消n語句格式: nDROP VIEW 視圖名n例如,DROP VIEW CS-STUDENT將刪除視圖CS-STUDENT。 33 數(shù)據(jù)查詢nSQL的數(shù)據(jù)操縱功能包括:nSELECT(查詢)nINSERT(插入)nDELETE(刪除)nUPDATE(修改) 3.3.1 Select基本結(jié)構(gòu)nSQL語言提供了SELECT語句進(jìn)行數(shù)據(jù)庫的查詢。n語句格式: nSELECT ALL|DISTINCT

19、 , FROM , WHERE GROUP BY HAVING ORDER BY ASC|DESC典型的SQL查詢具有如下:n所對應(yīng)關(guān)系代數(shù)表達(dá)式為: pwhererrrfromAAAselectmn,2121mpAAArrrn21,21需要說明的是:nSQL查詢中的子句順序:SELECT、FROM、WHERE、GROUP BY、HAVING和ORDER BY。但是SELECT、FROM是必須的,而且,HAVING子句只能與GROUP BY搭配起來使用。nSELECT子句對應(yīng)的是關(guān)系代數(shù)中的投影運(yùn)算,用來列出查詢結(jié)果中的屬性。其輸出可以是:列名、表達(dá)式、集函數(shù),DISTINCT選項(xiàng)可以保證查詢

20、的結(jié)果集中不存在重復(fù)元組。 nFROM 子句對應(yīng)的是關(guān)系代數(shù)中的笛卡兒積,它列出的是表達(dá)式求值過程中需掃描的關(guān)系即在 FROM子句中出現(xiàn)多個(gè)基本表或視圖時(shí),系統(tǒng)首先執(zhí)行笛卡爾積操作。nWHERE子句對應(yīng)的是關(guān)系代數(shù)中的選擇謂詞。WHERE子句的條件表達(dá)式中可以使用的運(yùn)算符如表3-1所示:需要說明的是: 3.3.1 Select基本結(jié)構(gòu)n說明1 在SELECT查詢中,沒有帶全程量詞,但可以將帶全程量詞的謂詞轉(zhuǎn)換成等價(jià)的帶有存在量詞的謂詞,其形式如下:n說明2 在SELECT查詢中,沒有邏輯蘊(yùn)含,但可以利用謂詞演算將一個(gè)邏輯蘊(yùn)含的謂詞等價(jià)地轉(zhuǎn)換,其形式如下:)()(PxPxqppq 3.3.2 簡

21、單查詢n SQL最簡單的查詢是找出關(guān)系中滿足特定條件的元組,這些查詢與關(guān)系代數(shù)中的選擇操作類似。簡單查詢只需要使用三個(gè)保留字SELECT、FROM和WHERE。舉例n【例3.4】查詢學(xué)生-課程數(shù)據(jù)庫中計(jì)算機(jī)系學(xué)生的學(xué)號、姓名及年齡。nSELECT Sno,Sname,Sage FROM Students WHERE SD=CS;舉例【例3.5】查詢數(shù)學(xué)系全體學(xué)生的詳細(xì)信息。n SELECT * FROM Students WHERE SD=MS;【例3.6】查詢學(xué)生的出生年份。n SELECT Sno,2004-Sage FROM Students; 3.3.3 連接查詢n若查詢涉及兩個(gè)以上的

22、表,則稱為連接查詢。n【例3.7】檢索選修了課程號為“C1”的學(xué)生號和學(xué)生姓名可用連接查詢和嵌套查詢實(shí)現(xiàn),實(shí)現(xiàn)方法如下:nSELECT Sno,Sname FROM Students,SC WHERE Students.Sno=SC.Sno AND SC.Cno= C1舉例 【例3.8】檢索選修課程名為“MS”的學(xué)生號和學(xué)生姓名可用連接查詢和嵌套查詢實(shí)現(xiàn),實(shí)現(xiàn)方法如下:nSELECT Sno,Sname FROM Students,SC,C WHERE Students.Sno=SC.Sno AND SC.Cno=C.Cno AND C.Cname=MS舉例 【例3.9】檢索至少選修了課程號為

23、“C1”和“C3”的學(xué)生號,實(shí)現(xiàn)方法如下:n SELECT Sno FROM SC SCX, SC SCY WHERE SCX.Sno=SCY.Sno AND SCX.Cno=C1 AND SCY.Cno=C3 3.3.4子查詢與聚集函數(shù)n 1子查詢n子查詢也稱嵌套查詢。嵌套查詢是指一個(gè) SELECT-FROM-WHERE查詢塊可以嵌入另一個(gè)查詢塊之中。在SQL中允許多重嵌套。舉例 【例3.10】例3.8可以采用嵌套查詢來實(shí)現(xiàn)。nSELECT Sno,Sname FROM Students WHERE Sno IN (SELECT Sno FROM SC WHERE Cno IN (SELEC

24、T Cno FROM C WHERE Cname=MS)2聚集函數(shù)n聚集函數(shù)是一個(gè)值的集合為輸入,返回單個(gè)值的函數(shù)。SQL 提供了5個(gè)預(yù)定義集函數(shù):平均值A(chǔ)VG、最小值MIN、最大值MAX、求和SUM及計(jì)數(shù)COUNT,如表 3-2所示。2聚集函數(shù)2聚集函數(shù)n使用ANY和ALL謂詞必須同時(shí)使用比較運(yùn)算符,其含義及等價(jià)的轉(zhuǎn)換關(guān)系如表 3-3所示。用集函數(shù)實(shí)現(xiàn)子查詢通常要比直接用ALL或ANY查詢效率高。舉例 【例3.11】查詢課程C1的最高分和最低分以及高低分之間的差距nSELECT MAX(G),MIN(G),MAX(G)-MIN(G) FROM SC WHERE Cno=C1舉例 【例3.12

25、】查詢其它系比計(jì)算機(jī)系CS所有學(xué)生年齡都要小的學(xué)生姓名及年齡。n方法1:(用ALL謂詞)nSELECT Sname,Sage FROM Students WHERE Sage ALL (SELECT Sage FROM Students WHERE SD=CS) AND SDCS舉例n方法2:(用MIN集函數(shù))從等價(jià)的轉(zhuǎn)換關(guān)系表3-3中可見,“ALL”可用“MIN”代換。nSELECT Sname,Sage FROM Students WHERE Sage (SELECT MIN (Sage) FROM Students WHERE SD=CS AND SDCS 舉例【例3.13】查詢其它系比

26、計(jì)算機(jī)系某一學(xué)生年齡小的學(xué)生姓名及年齡。n方法1:(用ANY謂詞)nSELECT Sname,Sage FROM Students WHERE Sage ANY (SELECT Sage FROM Students WHERE SD=CS) AND SDCS舉例n方法2:“ANY”可用“MAX”代換。n SELECT Sname,Sage FROM Students WHERE Sage (SELECT MAX (Sage) FROM Students WHERE SD=CS) AND SDCS 3.3.5分組查詢n1. GROUP BY子句n在WHERE子句后面加上 GROUP BY子句可以

27、對元組重新組織,并進(jìn)行分組。保留字GROUP BY后面跟一個(gè)分組屬性列表。SELECT子句中使用的聚集操作符僅用在每個(gè)分組上。舉例【例3.14】學(xué)生數(shù)據(jù)庫中的SC關(guān)系,查詢每個(gè)學(xué)生的平均成績。nSELECT Sno,AVG(Grade) FROM SC GROUP BY Sno 2. HAVING子句n假如元組在分組前按照某種方式加上限制,使得不需要的分組為空,可以在GROUP BY子句后面跟一個(gè)HAVING子句即可。2. HAVING子句n當(dāng)元組含有空值時(shí),應(yīng)注意:n第一,空值在任何聚集操作中被忽視。它對求和、求平均值和計(jì)數(shù)都沒有影響。它也不能是某列的最大值或最小值。例如,COUNT(*)是

28、某個(gè)關(guān)系中所有元組數(shù)目之和,但COUNT(A)卻是A屬性非空的元組個(gè)數(shù)之和。n第二,NULL值又可以在分組屬性中看作是一個(gè)一般的值。例如,SELECT A,AVG(B) FROM R中,當(dāng)A的屬性值為空時(shí),就會(huì)統(tǒng)計(jì)A=NULL的所有元組中B的均值。舉例【例3.15】供應(yīng)商數(shù)據(jù)庫中的S、P、J、SPJ關(guān)系,查詢某工程至少用了3家供應(yīng)商(包含3家)供應(yīng)的零件的平均數(shù)量,并按工程號的降序排列。nSELECT JNO,AVG(QTY) FROM SPJ GROUP BY JNO HAVING COUNT(DISTINCT(SNO)2 ORDER BY JNO DESC;舉例表3-4 按工程號JNO=J

29、1分組SnoPnoJnoQtyS1S2S2S2S3S4S5P1P3P3P5P1P6P3J1 J1J1 J1J1 J1 J1200400200100200300200n假如按工程號JNO=J1來分組,結(jié)果如表3-4 所示。從表中可以看出如果不加DISTINCT ,統(tǒng)計(jì)的數(shù)為 7 , 而加了DISTINCT,統(tǒng)計(jì)的數(shù)是5。 3.3.6 更名運(yùn)算nSQL提供可為關(guān)系和屬性重新命名的機(jī)制,這是通過使用具有如下形式的as子句來實(shí)現(xiàn)的:n As Old-name as new-namenAs子句即可出現(xiàn)在select子句,也可出現(xiàn)在from子句中。舉例【例3.16】查詢計(jì)算機(jī)學(xué)生的Sname和Sage,但

30、Sname的用姓名表示,Sage用年齡表示。其語句如下:nSELECT Sname as姓名,Sage as 年齡 FROM Students WHERE Sage (SELECT MAX (Sage) FROM Students WHERE SD=CS) AND SDCS舉例【例3.17】查詢計(jì)算機(jī)選修了C1課程的學(xué)生姓名Sname和成績Grade。其語句如下:nSELECT Sname,Grade FROM Students as x,SC as y WHERE x.sno=y.sno and o=C1舉例【例3.18】查詢平均成績至少比“1004”平均成績高的學(xué)生學(xué)號Sno和平均成績Gr

31、ade。其語句如下:nSELECT Sno,avg(Grade) FROM SC as x Group by sno Having avg(Grade)(SELECT avg(Grade) FROM SC as y WHERE y.sno=1004) 3.3.7字符串操作n字符串匹配使用兩個(gè)特殊的字符來描述模式:“”匹配任意字符串;“_”匹配任意一個(gè)字符。模式是大小寫敏感的。n例如:“Marry%”匹配任何以“Marry”開頭的字符串;“idge%” 匹配任何包含“idge”的字符串,如 “Marryidge”、“Rock Ridge”、“Mianus Bridge”和“Ridgeway”。n

32、“_” 匹配只含兩個(gè)字符的字符串;“_” 匹配至少包含兩個(gè)字符的字符串。舉例【例3.19】學(xué)生關(guān)系模式為(Sno,Sname,Sex,SD,SAge,SAdd),其中: Sno為學(xué)號,Sname為姓名,Sex為性別,SD為所在系,SAge為年齡,SAdd為家庭住址。請查詢:n查詢家庭住址包含“科技路”的學(xué)生姓名。n檢索名字為“曉軍”的學(xué)生姓名、年齡和所在系。舉例解:n(1)家庭住址包含“科技路”的學(xué)生姓名的SQL語句如下:nSELECT Sname n FROM Studentsn WHERE Add like 科技路n(2)名字為“曉軍”的學(xué)生姓名、年齡和所在系的SQL語句如下:nSELEC

33、T Sname,Age,SDn FROM Studentsn WHERE Sname LIKE _曉軍使用escape關(guān)鍵詞來n為了使模式中包含特殊模式字符(即%和_),在 SQL 中允許使用 escape關(guān)鍵詞來定義轉(zhuǎn)義符。轉(zhuǎn)義符緊靠著特殊字符,并放在它的前面,表示該特殊字符被當(dāng)成普通字符。例如在like比較中使用escape關(guān)鍵詞來定義轉(zhuǎn)義符,例如使用反斜杠“”作為轉(zhuǎn)義符。使用escape關(guān)鍵詞來nLike abcdescape ,匹配所有以abcd開頭的字符串。nLike abcdescape ,匹配所有以abcd開頭的字符串。 3.3.8集合操作n在關(guān)系代數(shù)中可以用集合的并、交和差來組

34、合關(guān)系。SQL也提供了對應(yīng)的操作,但是查詢的結(jié)果必須具有相同的屬性和類型列表。保留字UNION、INTERSECT和EXCEPT分別對應(yīng)。保留字用于兩個(gè)查詢時(shí),應(yīng)該分別用括號括起來。舉例n【例3.20】假定學(xué)生和教師關(guān)系模式如下所示,查詢即是女研究生,又是教師且工資大于等于1500元的名字和地址。nStudents(Name,Sno,SEX,SD,Type,Address)nTeachers(Name,Eno,SEX,Salary,Address) n解:本題第一條SELECT語句查詢和第二條SELECT語句查詢的結(jié)果集模式都為(Name,Address),故可以對它們?nèi)〗患?。n(SELECT

35、 Name,Addressn FROM Studentsn WHERE SEX女 AND Type研究生)n INTERSECTn(SELECT Name,Addressn FROM Teachersn WHERE Salary 1500)舉例舉例n同理,我們也可以對兩個(gè)相同結(jié)果集的關(guān)系取差集n【例3.21】查詢不是教師的學(xué)生。n(SELECT Name,Address FROM Students)n EXCEPTn(SELECT Name,Address FROM Teachers)3.3.9視圖的查詢和刪除n1視圖查詢n【例3.22】建立 “計(jì)算機(jī)系 ”( CS 表示計(jì)算機(jī)系)學(xué)生的視圖如

36、下所示,并要求進(jìn)行修改、插入操作時(shí)保證該視圖只有計(jì)算機(jī)系的學(xué)生。3.3.9視圖的查詢和刪除解:nCREATE VIEW CS-STUDENT n AS SELECT Sno,Sname,Sage,Sexn FROM Studentn WHERE SD=CSn WITH CHECK OPTION;舉例n例:查詢計(jì)算機(jī)年齡小于20歲的學(xué)號及年齡的:nSELECT Sno,Sage FORM CS-STUDENT WHERE SD=CS AND Sage20; n系統(tǒng)執(zhí)行該語句時(shí),通?,F(xiàn)將其轉(zhuǎn)換成等價(jià)的對基本表的查詢?nèi)缓髨?zhí)行查詢語句,即當(dāng)查詢視圖表時(shí),系統(tǒng)先從數(shù)據(jù)字典中取出該視圖的定義,然后將定義中

37、的查詢語句和對該視圖的查詢語句結(jié)合起來,形成一個(gè)修正的查詢語句。對上例修正之后的查詢語句為:nSELECT Sno,Sage FORM Student WHERE SD=CS AND Sage= n branch-total-avg.value舉例34 數(shù)據(jù)更新3.4.1 插入、刪除和修改語句n1插入語句n語句的基本格式:nINSERT INTO 基本表名(字段名,字段名) VALUES(常量,常量); 查詢語句nINSERT INTO 基本表名(列表名) SELECT 查詢語句舉例n【例3.25】將學(xué)號為“3002”、課程號為“C4”、成績?yōu)?8的元組插入SC關(guān)系中。其語句如下:nInser

38、t into SC Values(3002, C4,98)舉例【例3.26】創(chuàng)建一個(gè)新的視圖v_employees,要求該視圖基于表employees創(chuàng)建。ncreate view v_employees(number,name, n age,sex,salary)n asn select number,name,age,sex,salary n from employeesn where name=張三nInsert into v_employeesn Values(001,李力,22,m,2000)2刪除語句n語句格式:nDELETE FROM 基本表名WHERE 條件表達(dá)式【例3.27】

39、刪除表employees中姓名為張然的記錄。nDELETE from employeesn where name=張然3修改語句n語句格式:nUPDATE 基本表名 SET 列名=值表達(dá)式(,列名=值表達(dá)式) WHERE 條件表達(dá)式【例3.28】將教師的工資增加5。nupdate teachersn set Salary = Salary*1.05舉例【例3.29】將教師的工資小于1000的增加5工資。nupdate teachersn set Salary = Salary*1.05n where Salary =15 AND (SEX=M AND Sage n =25)ORn (SEX=F

40、 AND Sage =16 AND Sage=20)檢測。n基于斷言的語法格式n格式:CREATE ASSERTION n CHECK() 舉例【例3.36】教學(xué)數(shù)據(jù)庫的模式Students、SC、C中有一個(gè)約束,不允許男同學(xué)選修“張勇”老師的課。nCREATE ASSERTION ASSE-SC1 CHECKn (NOT EXISTSn (SELECT * FROM SC WHERE Cno INn (SELECT Cno FROM C WHERE n TEACHER=張勇)n AND Sno INn (SELECT Sno FROM Students WHERE n SEX=M);舉例【例

41、3.37】教學(xué)數(shù)據(jù)庫的模式Students、SC、C中有一個(gè)約束,每門課最多50名男同學(xué)選修。n解:可寫成如下的斷言形式:n CREATE ASSERTION ASSE-SC2 CHECKn (50=ALL(SELECT COUNT(SC.Sno)n FROM Students,SC n WHERE Students.Sno=SC.Sno AND n SEX=Mn GROUP BY Cno);3.6 SQL中的觸發(fā)器n觸發(fā)器(Trigger)就是一類由來事件驅(qū)動(dòng)的特殊過程,一旦由某個(gè)用戶定義,任何用戶對該觸發(fā)器指定的數(shù)據(jù)進(jìn)行增、刪或改操作時(shí),系統(tǒng)將自動(dòng)激活相應(yīng)的觸發(fā)器,在核心層進(jìn)行集中的完整性

42、控制。3.6.1 觸發(fā)器的組成和類型n 觸發(fā)器的定義包括兩個(gè)方面:n(1)指明觸發(fā)器的觸發(fā)事件;n(2)指明觸發(fā)器執(zhí)行的動(dòng)作。3.6.1 觸發(fā)器的組成和類型n觸發(fā)事件包括表中行的插入、刪除和修改,即執(zhí)行INSERT、DELETE、UPDATE語句。在修改操作(UPDATE)中,還可以指定,特定的屬性或?qū)傩越M的修改為觸發(fā)條件。事件的觸發(fā)還有兩個(gè)相關(guān)的時(shí)間:nBefore觸發(fā)器是在事件發(fā)生之前觸發(fā)nAfter觸發(fā)器是在事件發(fā)生之后觸發(fā)3.6.1 觸發(fā)器的組成和類型n觸發(fā)動(dòng)作實(shí)際上是一系列SQL語句,可以有兩種方式:n(1)對被事件影響的每一行(FOR EACH ROW)每一元組執(zhí)行觸發(fā)過程,稱為行

43、級觸發(fā)器。n(2)對整個(gè)事件只執(zhí)行一次觸發(fā)過程(FOR EACH STATEMENT),稱為語句級觸發(fā)器。該方式是觸發(fā)器的默認(rèn)方式。 3.2.2 創(chuàng)建觸發(fā)器n1創(chuàng)建觸發(fā)器語句的格式n CREATE TRIGGER BEFORE|AFTER DELETE| INSERT| UPDATEOF列名清單 ON 表名 REFERENCING臨時(shí)視圖名 WHEN觸發(fā)條件| 觸發(fā)動(dòng)作 FOR EACHROW|STATEMENT 說明n(1)BEFORE:指示DBMS在執(zhí)行觸發(fā)語句之前激發(fā)觸發(fā)器。n(2)AFTER:指示 DBMS 在執(zhí)行觸發(fā)語句之后激發(fā)觸發(fā)器。n(3)DELETE:指明是DELETE觸發(fā)器,

44、每當(dāng)一個(gè)DELETE 語句從表中刪除一行時(shí)激發(fā)觸發(fā)器。 n(4)INSERT:指明是INSERT觸發(fā)器,每當(dāng)一個(gè)INSERT語句向表中插入一行時(shí)激發(fā)觸發(fā)器。n (5) UPDATE:指明是 UPDATE 觸發(fā)器,每當(dāng)UPDATE語句修改由OF子句指定的列值時(shí),激 發(fā) 觸 發(fā) 器 。 如 果 忽 略 O F 子 句 , 每 當(dāng)UDPATE語句修改表的任何列值時(shí),DBMS都將激發(fā)觸發(fā)器。說明說明n(6) REFERENCING 臨時(shí)視圖名: 指定臨時(shí)視圖的別名。在觸發(fā)器運(yùn)行過程中,系統(tǒng)會(huì)生成兩個(gè)臨時(shí)視圖,分別存放被更新值(舊值)和更新后的值(新值)。對于行級觸發(fā)器,默認(rèn)臨時(shí)視圖名分別是OLD和NE

45、W;對于語句級觸發(fā)器,默認(rèn)臨時(shí)視圖名分別是 OLD-TABLE和 NEW-TABLE 。一旦觸發(fā)器運(yùn)行結(jié)束,臨時(shí)視圖就不在。說明n(7)WHEN 觸發(fā)條件: 指定觸發(fā)器的觸發(fā)條件。當(dāng)滿足觸發(fā)條件時(shí),DBMS才激發(fā)觸發(fā)器。觸發(fā)條件中必須包含臨時(shí)視圖名,不包含查詢。 【例3.38】為employee表創(chuàng)建一觸發(fā)器,當(dāng)修改某位職工的職稱時(shí),應(yīng)同時(shí)修改salary表中的基本工資(Basepay),具體標(biāo)準(zhǔn)為:當(dāng)職稱修改為工程師時(shí),增加150元;當(dāng)修改為高工時(shí),增加300元:舉例nCREATE TRIGGERtri-updatetitle-employeen AFTER UPDATE OF titlen

46、 ON employeen REFERENCINGn NEW AS newtuplen WHEN(newtuple.title=工程師)n UPDA TE salaryn SET basepay=basepay+150n WHERE eno=newtuple.enon WHEN(newtuple.title=高工)n UPDATE salaryn SET basepay=basepay+300n WHERE eno=newtuple.enon FOR EACH ROW舉例 37 嵌入式SQLn用某種傳統(tǒng)的編程語言編寫程序,但程序中的某些函數(shù)或某些語句是SQL語句。這種方式下使用的SQL語言稱為

47、嵌入式SQL(EmbeddedSQL),其中傳統(tǒng)的編程語言稱為宿主語言(或主語言)。 nSQL是非過程的、面向集合的數(shù)據(jù)操縱語言,它大部分語句的使用都是獨(dú)立的,與上下文無關(guān)。但在事務(wù)處理中,常需要有流程控制,如果單使用SQL 語言,很難實(shí)現(xiàn)這類應(yīng)用。另一方面,普通的編程語言在涉及數(shù)據(jù)庫操作時(shí),不能高效地進(jìn)行數(shù)據(jù)的存取。所以,嵌入式 SQL的使用,結(jié)合了編程語言的過程性和 SQL語言的數(shù)據(jù)操縱能力,可提高數(shù)據(jù)庫應(yīng)用程序的效率。 37 嵌入式SQL 3.7.1 SQL與宿主語言接口n嵌入式SQL的實(shí)現(xiàn)DBMS有兩種處理方法。n一種是擴(kuò)充主語言及修改主語言編譯程序,使之能處理SQL語句;n另一種是采

48、用預(yù)處理方式,即由DBMS的預(yù)處理程序?qū)υ闯绦蜻M(jìn)行掃描,識別出SQL語句,把它們轉(zhuǎn)換成主語言的函數(shù)調(diào)用語句,以使主語言編譯程序能識別它們,最后由主語言的編譯程序?qū)⒄麄€(gè)源程序編譯成目標(biāo)碼。 使用嵌入式SQL的原則:n1區(qū)分主語言語句與SQL語句n為了區(qū)分主語言語句與SQL語句,需要在所有的SQL語句前加前綴 EXEC SQL,而SQL的結(jié)束標(biāo)志隨主語言的不同而不同。n例如,PL/1和C語言的引用格式為:n EXEC SQL ;n又如,COBOL語言的引用格式為:n EXEC SQL END-EXEC使用嵌入式SQL的原則:n2主語言工作單元與數(shù)據(jù)庫工作單元通信n嵌入的SQL語句通過引用主語言的程

49、序變量實(shí)現(xiàn)相互間的通信。主變量是由主語言程序定義,并且必須在SQL的BEGIN DECLARE SECTION和END DECLARE SECTION說明語句之間加以說明。說明之后嵌入式SQL 語句即可引用主變量,引用時(shí)必須在主變量之前加冒號“:”作前綴標(biāo)志,以區(qū)別數(shù)據(jù)庫字段變量。 2主語言工作單元與數(shù)據(jù)庫工作單元通信n在 主 變 量 中 , 有 一 個(gè) 特 殊 的 主 變 量SQLSTATE,用于存放每條SQL語句執(zhí)行后的執(zhí)行狀況代碼,應(yīng)用程序在執(zhí)行完一條SQL語句后可以測試它,以了解SQL語句執(zhí)行狀況并作響應(yīng)處理。 2主語言工作單元與數(shù)據(jù)庫工作單元通信nSQLSTATE是由5個(gè)字符組成的字

50、符數(shù)組,是SQLCA(SQL Communication Area,SQL通信區(qū))數(shù)據(jù)結(jié)構(gòu)的一成員分量。當(dāng) SQL語句執(zhí)行成功時(shí),DBMS給SQLSTATE中賦全零 “00000”值,表示無錯(cuò);否則賦非零,分別表示SQL語句執(zhí)行時(shí)發(fā)生的各種錯(cuò)誤,如等于“02000” 表示未找到元組。SQLSTATE也必須在兩個(gè)SQL說明語句之間說明,嵌入式SQL語句才能引用。舉例n例如:在C語言中可用如下形式說明主變量:nEXEC SQL BEGIN DECLARE SECTION; n /*說明主變量/*nchar Msno4,Mcno3,givensno5;nint Mgrade;nchar SQLSTA

51、TE6;nEXEC SQL END DECLARE SECTION; n上面5行組成一個(gè)說明節(jié),說明了5個(gè)共享變量,其中, SQLSTATE是一個(gè)特殊的共享變量,起著解釋SQL語句執(zhí)行狀況的作用。因此,當(dāng)執(zhí)行一條SQL語句后,可以根據(jù)SQLSTATE的值轉(zhuǎn)向不同的分支,以控制程序的流向。舉例舉例 【例3.39】根據(jù)共享變量givensno值查詢學(xué)生關(guān)系students中學(xué)生的姓名、年齡和性別。nEXEC SQL SELECT sname,age,sexnINTO :Msno,:Mcno,:givensnonFROM studentsnWHERE sno=:Msno;舉例 【例3.40】某學(xué)生選

52、修了一門課程信息,將其插入學(xué)生選課表SC中, 假設(shè)學(xué)號、 課程號、成績分別賦給主變量 HSno、Hcno和Hgrade。 EXEC SQL INSERT INTO SC(Sno,Cno,Grade) VALUES(:Hsno,:Hcno,:Hgrade); nVALUES子句中通常使用主變量傳遞輸入數(shù)據(jù)。舉例 【例3.40】將學(xué)號等于“001”考生的考試成績增加若干分。 假設(shè)增加的分?jǐn)?shù)已賦給主變量 Hgrade。 EXEC SQL UPDATE SC SET grade=grade+:Hegrade WHERE Sno=001; 使用嵌入式SQL的原則:n3游標(biāo)nSQL語言是面向集合的,一條S

53、QL語句可產(chǎn)生或處理多條記錄。而主語言是面向記錄的,一組主變量一次只能放一條記錄,所以,引入游標(biāo),通過移動(dòng)游標(biāo)指針來決定對獲取哪一條記錄。與游標(biāo)相關(guān)的SQL語句有四條:3游標(biāo)n定義游標(biāo)。定義游標(biāo)的格式如下:nEXEC SQL DECLARE CURSOR FOR END_EXECn這是一條說明性語句,定義中的SELECT語句并不立即執(zhí)行。3游標(biāo)n打開游標(biāo)。打開游標(biāo)的格式如下:nEXEC SQL OPEN END_EXECn該語句執(zhí)行游標(biāo)定義中的SELECT語句,同時(shí)游標(biāo)處于活動(dòng)狀況。游標(biāo)是一個(gè)指針,此時(shí)指向查詢結(jié)果的第一行之前。3游標(biāo)n 推進(jìn)游標(biāo)。推進(jìn)游標(biāo)的格式如下:n EXEC SQL FETCH FROM INTO END_EXECn該語句使用時(shí),游標(biāo)推進(jìn)一行,并把游標(biāo)指向的行(稱為當(dāng)前行

溫馨提示

  • 1. 本站所有資源如無特殊說明,都需要本地電腦安裝OFFICE2007和PDF閱讀器。圖紙軟件為CAD,CAXA,PROE,UG,SolidWorks等.壓縮文件請下載最新的WinRAR軟件解壓。
  • 2. 本站的文檔不包含任何第三方提供的附件圖紙等,如果需要附件,請聯(lián)系上傳者。文件的所有權(quán)益歸上傳用戶所有。
  • 3. 本站RAR壓縮包中若帶圖紙,網(wǎng)頁內(nèi)容里面會(huì)有圖紙預(yù)覽,若沒有圖紙預(yù)覽就沒有圖紙。
  • 4. 未經(jīng)權(quán)益所有人同意不得將文件中的內(nèi)容挪作商業(yè)或盈利用途。
  • 5. 人人文庫網(wǎng)僅提供信息存儲(chǔ)空間,僅對用戶上傳內(nèi)容的表現(xiàn)方式做保護(hù)處理,對用戶上傳分享的文檔內(nèi)容本身不做任何修改或編輯,并不能對任何下載內(nèi)容負(fù)責(zé)。
  • 6. 下載文件中如有侵權(quán)或不適當(dāng)內(nèi)容,請與我們聯(lián)系,我們立即糾正。
  • 7. 本站不保證下載資源的準(zhǔn)確性、安全性和完整性, 同時(shí)也不承擔(dān)用戶因使用這些下載資源對自己和他人造成任何形式的傷害或損失。

最新文檔

評論

0/150

提交評論