1、數據庫原理及應用教案數據庫原理及應用教案計算機科學學院計算機科學學院第3章 數據庫的標準語言SQLn3.1 SQL概述n3.2 數據定義n3.3 數據查詢n3.4 數據更新n3.5 SQL的完整性控制n3.6 SQL中的觸發器n3.7 嵌入式SQL第3章 數據庫的標準語言SQLnSQL（Structrued Query Language）早已確立自己作為關系數據庫標準語言的地位，已被眾多商用 DBMS 產品所采用， 使得它已成為關系數據庫領域中一個主流語言。 它不僅包含了數據查詢功能，還包括插入、刪除、更新和數據定義功能。作為一個 SQL數據庫是表的匯集，它用一個或多個 SQL模式定義。作為

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

3、NSERT、UODATE、DELETEn數據控制:GRANT、REVORK。nSQL語言支持關系數據庫的三級模式結構，其中：n視圖對應外模式n基本表對應模式n存儲文件對應內模式n具體結構如圖3-1所示。2SQL支持三級模式結構 SQLView（視圖）（視圖）1View（視圖）（視圖）2Base Table 1（基本表）（基本表）Base Table2（基本表）（基本表）Base Table 3（基本表）（基本表）Base Table4（基本表）（基本表）Sored File 1（存儲文件）（存儲文件）Sored File 2（存儲文件（存儲文件）Sored File 3（存儲文件）（存儲文件）

4、內模式內模式圖圖3-1 關系數據庫的三級模式結構關系數據庫的三級模式結構模式模式外模式外模式 3.1.2 SQL的基本組成 nSQL由以下幾個部分組成:n(1)數據定義語言(DDL)：提供定義關系模式和視圖、刪除關系和視圖、修改關系模式的命令。n(2)交互式數據操縱語言(DML)：提供查詢、插入、刪除和修改的命令。n(3)事務控制(transaction control)：SQL提供定義事務開始和結束的命令。SQL組成部分n(4)嵌入式SQL和動態SQL（embedded SQL and dynamic SQL）用于嵌入到某種通用的高級語言中混合編程。其中 SQL 負責操縱數據庫，高級語言負責

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

6、邏輯結構組成了關系數據庫的內模式。nSQL的數據定義包括對表、視圖、索引的創建和刪除。 3.2.1創建表 (CREATE TABLE)n語句格式:nCREATE TABLE (列級完整性約束條件， 列級完整性約束條件，)；3.2.1創建表 (CREATE TABLE)n列級完整性約束條件有:nNULL（空）nUNIQUE（取值唯一），如NOT NULL UNIQUE 表示取值唯一，不能取空值。舉例 【例3.1】建立一個供應商、零件數據庫。其中“供應商”表S(Sno，Sname，Status，City)分別表示:供應商代碼、供應商名、供應商狀態、供應商所在城市。“零件”表P(Pno，Pname，

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

8、建立一個供應商、零件數據庫如下:建立供應商、零件數據庫 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);建立供應商、零件數據庫 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）已經定義了Sno為主碼，所以，“ Sno CHAR(5) NOT NULL UNIQUE”語句中的“NOT NULL

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

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

12、在實現數據的參照完整性方面有特別的意義。n在使用ORDER BY和GROUP BY子句中進行檢索數據時，可以顯著減少查詢中分組和排序的時間。n使用索引可在檢索數據的過程中使用優化隱藏器，提高系統性能。1聚集索引和非聚集索引n聚集索引對表的物理數據頁中的數據按列進行排序，然后再重新存儲到磁盤上，即聚集索引與數據是混為一體的，它的葉節點中存儲的是實際的數據。 n非聚集索引具有完全獨立于數據行的結構，使用非聚集索引不用將物理數據頁中的數據按列排序。非聚集索引的葉節點存儲了組成非聚集索引的關鍵字值和行定位器。 2建立索引n語句格式:nCREATE UNIQUECLUSTER INDEX ON （ ，）

13、； 2建立索引n說明:n(1)次序:可選ASC（升序）或DSC（降序），默認值為ASC。n(2)UNIQUE:表明此索引的每一個索引值只對應唯一的數據記錄。n(3)CLUSTER:表明要建立的索引是聚簇索引，意為索引項的順序是與表中記錄的物理順序 一致的索引組織。舉例 【例3.2】 假設供應銷售數據庫中有供應商S、零件P、工程項目J、供銷情況SPJ關系，希望建立四個索引。其中:供應商S中Sno按升序建立索引；零件P中 Pno按升序建立索引；工程項目J中 Jno 按升序建立索引；對供銷情況SPJ中的Sno按升序，Pno按降序，Jno按升序建立索引。舉例 解:根據題意建立的索引如下 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例如，執行 DROP INDEX StudentIndex；此后索引StudentIndex不再是數據庫模式的一部分。3.2.4 定義、刪除、更新視圖n視圖是從一個或多個表或視圖中導出的表，其結構和數據是建立在對表的查詢基礎上

15、的。n視圖包括幾個被定義的數據列和多個數據行，但從其本質上講，這些數據列和數據行來源于其所引用的表。n視圖不是真實存在的基礎表而是一個虛擬表，視圖所對應的數據并不實際地以視圖結構存儲在數據庫中，而是存儲在視圖所引用的表中。3.2.4 定義、刪除、更新視圖 n視圖的優點和作用 n可以使視圖集中數據、簡化和定制不同用戶對數據庫的不同數據要求。n使用視圖可以屏蔽數據的復雜性，用戶不必了解數據庫的結構，就可以方便地使用和管理數據，簡化數據權限管理和重新組織數據以便輸出到其他應用程序中。n視圖可以使用戶只關心他感興趣的某些特定數據和他們所負責的特定任務，而那些不需要的或者無用的數據則不在視圖中顯示。視圖

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

17、)WITH CHECK OPTION表示對UPDATE，INSERT，DELETE操作時保證更新、插入、或刪除的行滿足視圖定義中的謂詞條件（即子查詢中的條件表達式）。n(3)組成視圖的屬性列名或者全部省略或者全部指定。如果省略屬性列名，則隱含該視圖由SELECT子查詢目標列的主屬性組成。舉例n【例3.3】建立“計算機系”（CS表示計算機系）學生的視圖，并要求進行修改、插入操作時保證該視圖只有計算機系的學生。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” 子句，因此，對該視圖進行修改、插入操作時DBMS會自動加上SD=CS的條件，保證該視圖只有計算機系的學生。舉例2視圖的撤消n語句格式: nDROP VIEW 視圖名n例如，DROP VIEW CS-STUDENT將刪除視圖CS-STUDENT。 33 數據查詢nSQL的數據操縱功能包括：nSELECT(查詢)nINSERT(插入)nDELETE(刪除)nUPDATE(修改) 3.3.1 Select基本結構nSQL語言提供了SELECT語句進行數據庫的查詢。n語句格式: nSELECT ALL|DISTINCT

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

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

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

22、表，則稱為連接查詢。n【例3.7】檢索選修了課程號為“C1”的學生號和學生姓名可用連接查詢和嵌套查詢實現，實現方法如下:nSELECT Sno，Sname FROM Students，SC WHERE Students.Sno=SC.Sno AND SC.Cno= C1舉例 【例3.8】檢索選修課程名為“MS”的學生號和學生姓名可用連接查詢和嵌套查詢實現，實現方法如下: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”的學生號，實現方法如下:n SELECT Sno FROM SC SCX， SC SCY WHERE SCX.Sno=SCY.Sno AND SCX.Cno=C1 AND SCY.Cno=C3 3.3.4子查詢與聚集函數n 1子查詢n子查詢也稱嵌套查詢。嵌套查詢是指一個 SELECT-FROM-WHERE查詢塊可以嵌入另一個查詢塊之中。在SQL中允許多重嵌套。舉例 【例3.10】例3.8可以采用嵌套查詢來實現。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聚集函數n聚集函數是一個值的集合為輸入，返回單個值的函數。SQL 提供了5個預定義集函數：平均值AVG、最小值MIN、最大值MAX、求和SUM及計數COUNT，如表 3-2所示。2聚集函數2聚集函數n使用ANY和ALL謂詞必須同時使用比較運算符，其含義及等價的轉換關系如表 3-3所示。用集函數實現子查詢通常要比直接用ALL或ANY查詢效率高。舉例 【例3.11】查詢課程C1的最高分和最低分以及高低分之間的差距nSELECT MAX(G),MIN(G),MAX(G)-MIN(G) FROM SC WHERE Cno=C1舉例 【例3.12

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

26、計算機系某一學生年齡小的學生姓名及年齡。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、對元組重新組織，并進行分組。保留字GROUP BY后面跟一個分組屬性列表。SELECT子句中使用的聚集操作符僅用在每個分組上。舉例【例3.14】學生數據庫中的SC關系，查詢每個學生的平均成績。nSELECT Sno，AVG（Grade） FROM SC GROUP BY Sno 2. HAVING子句n假如元組在分組前按照某種方式加上限制，使得不需要的分組為空，可以在GROUP BY子句后面跟一個HAVING子句即可。2. HAVING子句n當元組含有空值時，應注意:n第一，空值在任何聚集操作中被忽視。它對求和、求平均值和計數都沒有影響。它也不能是某列的最大值或最小值。例如，COUNT(*)是

28、某個關系中所有元組數目之和，但COUNT(A)卻是A屬性非空的元組個數之和。n第二，NULL值又可以在分組屬性中看作是一個一般的值。例如，SELECT A，AVG(B) FROM R中，當A的屬性值為空時，就會統計A=NULL的所有元組中B的均值。舉例【例3.15】供應商數據庫中的S、P、J、SPJ關系，查詢某工程至少用了3家供應商（包含3家）供應的零件的平均數量，并按工程號的降序排列。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來分組，結果如表3-4 所示。從表中可以看出如果不加DISTINCT ,統計的數為 7 ， 而加了DISTINCT，統計的數是5。 3.3.6 更名運算nSQL提供可為關系和屬性重新命名的機制，這是通過使用具有如下形式的as子句來實現的:n As Old-name as new-namenAs子句即可出現在select子句，也可出現在from子句中。舉例【例3.16】查詢計算機學生的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】查詢計算機選修了C1課程的學生姓名Sname和成績Grade。其語句如下:nSELECT Sname，Grade FROM Students as x，SC as y WHERE x.sno=y.sno and o=C1舉例【例3.18】查詢平均成績至少比“1004”平均成績高的學生學號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字符串匹配使用兩個特殊的字符來描述模式：“”匹配任意字符串；“_”匹配任意一個字符。模式是大小寫敏感的。n例如:“Marry%”匹配任何以“Marry”開頭的字符串；“idge%” 匹配任何包含“idge”的字符串，如 “Marryidge”、“Rock Ridge”、“Mianus Bridge”和“Ridgeway”。n

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

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

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

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

36、下所示，并要求進行修改、插入操作時保證該視圖只有計算機系的學生。3.3.9視圖的查詢和刪除解：nCREATE VIEW CS-STUDENT n AS SELECT Sno，Sname，Sage，Sexn FROM Studentn WHERE SD=CSn WITH CHECK OPTION;舉例n例:查詢計算機年齡小于20歲的學號及年齡的:nSELECT Sno，Sage FORM CS-STUDENT WHERE SD=CS AND Sage20; n系統執行該語句時，通常現將其轉換成等價的對基本表的查詢然后執行查詢語句，即當查詢視圖表時，系統先從數據字典中取出該視圖的定義，然后將定義中

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

38、t into SC Values(3002， C4，98)舉例【例3.26】創建一個新的視圖v_employees，要求該視圖基于表employees創建。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 條件表達式【例3.27】

39、刪除表employees中姓名為張然的記錄。nDELETE from employeesn where name=張然3修改語句n語句格式：nUPDATE 基本表名 SET 列名=值表達式(，列名=值表達式) WHERE 條件表達式【例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】教學數據庫的模式Students、SC、C中有一個約束，不允許男同學選修“張勇”老師的課。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】教學數據庫的模式Students、SC、C中有一個約束，每門課最多50名男同學選修。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中的觸發器n觸發器（Trigger）就是一類由來事件驅動的特殊過程，一旦由某個用戶定義，任何用戶對該觸發器指定的數據進行增、刪或改操作時，系統將自動激活相應的觸發器，在核心層進行集中的完整性

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

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

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

45、W；對于語句級觸發器，默認臨時視圖名分別是 OLD-TABLE和 NEW-TABLE 。一旦觸發器運行結束，臨時視圖就不在。說明n(7)WHEN 觸發條件: 指定觸發器的觸發條件。當滿足觸發條件時，DBMS才激發觸發器。觸發條件中必須包含臨時視圖名，不包含查詢。 【例3.38】為employee表創建一觸發器，當修改某位職工的職稱時，應同時修改salary表中的基本工資（Basepay），具體標準為:當職稱修改為工程師時，增加150元；當修改為高工時，增加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用某種傳統的編程語言編寫程序，但程序中的某些函數或某些語句是SQL語句。這種方式下使用的SQL語言稱為

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

48、用預處理方式，即由DBMS的預處理程序對源程序進行掃描，識別出SQL語句，把它們轉換成主語言的函數調用語句，以使主語言編譯程序能識別它們，最后由主語言的編譯程序將整個源程序編譯成目標碼。 使用嵌入式SQL的原則:n1區分主語言語句與SQL語句n為了區分主語言語句與SQL語句，需要在所有的SQL語句前加前綴 EXEC SQL，而SQL的結束標志隨主語言的不同而不同。n例如，PL/1和C語言的引用格式為:n EXEC SQL ；n又如，COBOL語言的引用格式為:n EXEC SQL END-EXEC使用嵌入式SQL的原則:n2主語言工作單元與數據庫工作單元通信n嵌入的SQL語句通過引用主語言的程

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

50、符數組，是SQLCA(SQL Communication Area，SQL通信區)數據結構的一成員分量。當 SQL語句執行成功時，DBMS給SQLSTATE中賦全零 “00000”值，表示無錯;否則賦非零，分別表示SQL語句執行時發生的各種錯誤，如等于“02000” 表示未找到元組。SQLSTATE也必須在兩個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行組成一個說明節，說明了5個共享變量，其中， SQLSTATE是一個特殊的共享變量，起著解釋SQL語句執行狀況的作用。因此，當執行一條SQL語句后，可以根據SQLSTATE的值轉向不同的分支，以控制程序的流向。舉例舉例 【例3.39】根據共享變量givensno值查詢學生關系students中學生的姓名、年齡和性別。nEXEC SQL SELECT sname，age，sexnINTO :Msno，:Mcno，:givensnonFROM studentsnWHERE sno=:Msno;舉例 【例3.40】某學生選

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

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