03關(guān)系數(shù)據(jù)庫基本原理-SQL語言打印ppt課件

1、關(guān)系數(shù)據(jù)庫基本原理 （2SQL語言 王傳棟 南京郵電大學(xué)計算機學(xué)院 2 內(nèi)容與要求 知識點 （1知識點一：關(guān)系模型概述 （2知識點二：關(guān)系數(shù)據(jù)結(jié)構(gòu) （3知識點三：關(guān)系代數(shù)理論 （4知識點四：關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL （5知識點五：關(guān)系數(shù)據(jù)庫的規(guī)范化理論 實驗1 SQL語言的應(yīng)用 教學(xué)基本要求 了解關(guān)系數(shù)據(jù)結(jié)構(gòu)的基本概念，了解關(guān)系模型的各種操 作和關(guān)系代數(shù)的基本原理，掌握關(guān)系數(shù)據(jù)模型的完整性 約束機制，掌握SQL語言，了解函數(shù)依賴等基本概念， 掌握關(guān)系模式的規(guī)范化概念、方法、原理與過程。 3 引言 數(shù)據(jù)庫的應(yīng)用平臺 數(shù)據(jù)服務(wù)器數(shù)據(jù)服務(wù)器 (DBMS) 設(shè)計設(shè)計 DB 應(yīng)用服務(wù)器應(yīng)用服務(wù)器 (軟件

2、軟件) 應(yīng)用程序應(yīng)用程序 普通用戶普通用戶 DBA 嵌入式嵌入式 SQL 交互式交互式SQL 4 引言 SQL是關(guān)系數(shù)據(jù)庫的標(biāo)準(zhǔn)語言 對關(guān)系模型的發(fā)展和商用DBMS的研制起著重要 的作用 SQL語言是介乎于關(guān)系代數(shù)和元組演算之間的一 種語言 SQL語言的9個核心詞匯 Create, Alter, Drop, Select, Insert, Update, Delete Grant, Revoke 本章詳細(xì)介紹SQL的核心部分內(nèi)容 數(shù)據(jù)定義 數(shù)據(jù)查詢 數(shù)據(jù)更新 嵌入式SQL 5 SQL簡介 SQL發(fā)展史 1970年 美國IBM研究中心的E.F.Codd連續(xù)發(fā)表多篇論文， 提出關(guān)系模型 1972年

3、 IBM公司開始研制實驗型關(guān)系數(shù)據(jù)庫管理系統(tǒng) SYSTEM R，配制的查詢語言稱為SQUARE語 言 1974年 把SQUARE修改為SEQUEL語言 1978年， SEQUEL簡稱為SQL，即“結(jié)構(gòu)式查詢語言” SQL的發(fā)音仍為“sequel”，現(xiàn)在SQL已經(jīng)成為一 個標(biāo)準(zhǔn) ，其發(fā)音更傾向于發(fā)“ess-cue-ell ” 6 SQL簡介 SQL發(fā)展史 SQL86 1986年10月，ANSI SQL標(biāo)準(zhǔn) 1987年6月，ISO SQL標(biāo)準(zhǔn) SQL2 1992年8月，ISO SQL標(biāo)準(zhǔn) SQL3 2019年，ISO SQL標(biāo)準(zhǔn) 在未來很長一段時間，SQL仍將是關(guān)系數(shù)據(jù)庫領(lǐng) 域的主流語言 在軟件工

4、程、人工智能領(lǐng)域，SQL已顯示出相當(dāng) 大的潛力 7 SQL簡介 SQL數(shù)據(jù)庫的體系結(jié)構(gòu) 用戶1用戶2用戶3用戶4 視圖1 視圖2 基本表1基本表2基本表3基本表4 存儲文件1存儲文件2存儲文件3存儲文件4 SQL用戶 View Base table Stored file 8 SQL簡介 SQL數(shù)據(jù)庫的體系結(jié)構(gòu) 1一個SQL模式Schema是表和約束的集合 2一個表由行集構(gòu)成，一行是列的序列 3表類型：基本表、視圖和導(dǎo)出表 4基本表與存儲文件是M:N聯(lián)系 存儲文件與外部存儲器的物理文件是一一對應(yīng)的 5SQL語句執(zhí)行對基本表和視圖查詢等操作 在用戶看來，兩者是一樣的，都是表 6SQL用戶可以是應(yīng)

5、用程序，也可以是終端用 戶 注：雖然SQL是國際公認(rèn)的關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)，但 標(biāo)準(zhǔn)的SQL文檔中并沒有使用“關(guān)系和“數(shù)據(jù)庫 這兩個名詞 9 SQL簡介 SQL組成 1數(shù)據(jù)定義語言，即SQL DDL 用于定義SQL模式、基本表、視圖、索引等結(jié)構(gòu) 2數(shù)據(jù)操縱語言，即SQL DML 數(shù)據(jù)查詢 數(shù)據(jù)更新：包括插入、刪除和修改三種操作 3嵌入式SQL語言規(guī)則 SQL語句嵌入在宿主語言程序中的規(guī)則 4數(shù)據(jù)控制語言，即SQL DCL 授權(quán)、完整性規(guī)則的描述、事務(wù)控制等 10 SQL簡介 SQL特點 1靈活和強大的查詢功能 SELECT語句能完成相當(dāng)復(fù)雜的查詢操作 2SQL不是一個應(yīng)用開發(fā)語言 只提供對數(shù)據(jù)庫的操

6、作功能，不能完成屏幕控制、 菜單管理、報表生成等功能 3SQL是國際標(biāo)準(zhǔn)語言 有利于各種數(shù)據(jù)庫之間交換數(shù)據(jù)，有利于程序的 移植，有利于實現(xiàn)高度的數(shù)據(jù)獨立性，有利于實 現(xiàn)標(biāo)準(zhǔn)化 4SQL的詞匯不多 完成核心功能只用了9個英語動詞，它的語法結(jié) 構(gòu)接近英語，因此容易學(xué)習(xí)和使用 11 SQL的數(shù)據(jù)定義 SQL模式的創(chuàng)建和撤銷 注 方式Schema一詞，來自于“ISO SQL標(biāo)準(zhǔn) 協(xié)議 Schema在SQL數(shù)據(jù)庫中相當(dāng)于一個容器 數(shù)據(jù)庫所有的對象如表、視圖、索引、用戶、存 儲過程、觸發(fā)器等都位于容器內(nèi) 創(chuàng)建SQL模式，就是定義一個存儲空間 在商業(yè)DBMS中，大多都使用Database代替 Schema

7、12 SQL的數(shù)據(jù)定義 SQL模式的創(chuàng)建和撤銷 創(chuàng)建 CREATE SCHEMA AUTHORIZATION 撤銷 DROP SCHEMA CASCADERESTRICT 方式 CASCADE級聯(lián)式） RESTRICT約束式） 商業(yè)DBMS中 CREATE Database DROP Database 13 SQL的數(shù)據(jù)定義 常用數(shù)據(jù)類型 1數(shù)值型：Integer，Smallint，Numeric(p, d) / DEC(p,d) 2字符串型：Char(n)，Varchar(n) 3位串型：Bit(n)，Bit Varying(n) 4時間型：DATE，TIME 注 在很多DBMS中支持自定義

8、數(shù)據(jù)類型，如在 Oracle中 CREATE DOMAIN AS 列級完整性約束 ; DROP DOMAIN ; 14 SQL的數(shù)據(jù)定義 常用數(shù)據(jù)類型 例如：在Oracle中定義數(shù)據(jù)類型與應(yīng)用 定義 Create domain COLOR Char(6) Default ? Constraint VALID_COLORS Check ( value in (Red, Yellow, Green, ? ) ) ; 運用 Create Table PART ( partcolor COLOR , ) ; 15 SQL的數(shù)據(jù)定義 基本表的創(chuàng)建、修改和撤銷 表的創(chuàng)建 句法 CREATE TABLE (

9、 域完整性約束條件 , 域完整性約束條件 , ) ; 域完整性約束條件 NOT NULL / NULL ：列值是否可以為空 UNIQUE ：列值唯一，不得重復(fù) DEFAULT ：列值空缺時，由系統(tǒng)填寫默認(rèn)值 16 SQL的數(shù)據(jù)定義 基本表的創(chuàng)建、修改和撤銷 表的創(chuàng)建 表級完整性約束條件 主鍵PRIMARY KEY子句 格式：PRIMARY KEY ( ) 作用：提供實體完整性約束的說明 闡明 系統(tǒng)一般自動在主鍵上建索引 主鍵為單屬性時，可直接在屬性后的域完整性約 束條件中，使用PRIMARY KEY定義主鍵 17 SQL的數(shù)據(jù)定義 基本表的創(chuàng)建、修改和撤銷 表的創(chuàng)建 表級完整性約束條件 外鍵F

10、OREIGN KEY子句 格式：Foreign key 外鍵名 ( ) references (列名表2) on delete 作用：提供參照完整性約束的說明 18 SQL的數(shù)據(jù)定義 基本表的創(chuàng)建、修改和撤銷 表的創(chuàng)建 表級完整性約束條件 外鍵FOREIGN KEY子句 on delete 子 句的說明 RESTRICT ：主表的主鍵值行不得刪除 CASCADE ：主表主鍵值行刪除，從表的相關(guān)行 隨之刪除 SET NULL ：主表主鍵值行刪除，從表的相關(guān)行 的列值設(shè)為NULL，前提是該列的值可以為NULL 19 SQL的數(shù)據(jù)定義 基本表的創(chuàng)建、修改和撤銷 表的創(chuàng)建 表級完整性約束條件 檢查CH

11、ECK子句 格式：CHECK (約束表達(dá)式) 作用：對某元組某屬性取值的約束說明 闡明 表創(chuàng)建后是一個空表，需要使用DMLinsert， update，delete語句裝入或維護(hù)數(shù)據(jù)行 20 SQL的數(shù)據(jù)定義 基本表的創(chuàng)建、修改和撤銷 表的創(chuàng)建 例如 S（SNO，SNAME，AGE，SEX，NativePlace） 字段含義：學(xué)號，姓名，年齡，性別，籍貫 T（TNO，TNAME，TITLE，SEX） 字段含義：教師號，教師姓名，職稱，性別 Create Table S ( SNO CHAR(3) , SNAME CHAR(8) NOT NULL, AGE Integer NULL, SEX C

12、HAR(1) DEFAULT M, NativePlace VARCHAR(20) , Primary Key (SNO) , Check (SEX IN (M, F) ) ) ; Create Table T ( TNO CHAR(3) Primary Key , TNAME CHAR(8) UNIQUE , TITLE CHAR(6) NOT NULL, SEX CHAR(1) DEFAULT M , Check (TITLE IN ( 教授, 副教授, 講師, 助教) ) ) ; SNO SNAME AGE SEX NativePlace S1WANG20M北京 S2LIU18F山東 S

13、3HU17M上海 S4XIA19F四川 TNO TNAME TITLE SEX T1ZHAO講師M T2LIU教授F Create Table C ( CNO CHAR(3) Primary Key , CNAME CHAR(8) , Credit Integer , CreditHours Integer , CPNO CHAR(3) NULL , TNO CHAR(3) NOT NULL , Foreign Key (CPNO) References C(CNO) ON DELETE SET NULL , Foreign Key (TNO) References T ON DELETE S

14、ET NULL ) ; 21 SQL的數(shù)據(jù)定義 基本表的創(chuàng)建、修改和撤銷 表的創(chuàng)建 例如 C（CNO，CNAME，Credit，CreditHours，CPNO，TNO） 字段含義：課程號，課程名，學(xué)分，學(xué)時數(shù)，先修課號，授課教師號 CNO CNAME CreditCreditHours CPNO TNO C1Math348NULLT1 C2English464NULLT2 C3PM232C2T2 C4DB3.556C1T1 TNO TNAME TITLE SEX T1ZHAO講師M T2LIU教授F ? ? ? ? Create Table SC ( SNO CHAR(3) , CNO CH

15、AR(3) , GRADE DEC(5, 2) , Primary Key (SNO, CNO) , Foreign Key (SNO) References S(SNO) ON DELETE restrict , Foreign Key (CNO) References C ON DELETE cascade , Check ( GRADE Between 0 And 100) ) ; 22 SQL的數(shù)據(jù)定義 基本表的創(chuàng)建、修改和撤銷 表的創(chuàng)建 例如 SC（SNO，CNO，Grade） 字段含義：學(xué)號，課程號，成績 SNO SNAME AGE SEX NativePlace S1WANG20

16、M北京 S2LIU18F山東 S3HU17M上海 S4XIA19F四川 CNO CNAME CreditCreditHoursCPNO TNO C1Math348NULLT1 C2English464NULLT2 C3PM232C2T2 C4DB3.556C1T1 SNO CNO Grade S1C280 S1C370 S1C485 S2C160 S2C275 S2C390 S2C4NULL S3C185 S3C480 S4C285 S4C475 ? ? ? ? 23 SQL的數(shù)據(jù)定義 基本表的創(chuàng)建、修改和撤銷 表的創(chuàng)建 問關(guān)于表T、S、C、SC的創(chuàng)建順序，下列選項正 確的是？ A） C T

17、SC S B） T S C SC C） S T C SC D） S T SC C E） T C S SC F） SC C S T G） SC C T S ? ? 24 SQL的數(shù)據(jù)定義 基本表的創(chuàng)建、修改和撤銷 表的修改 句法 ALTER TABLE 增加屬性 Alter table add ； 刪除屬性 Alter table drop cascade restrict CASCADE：引用該列的視圖和約束隨之刪除 RESTRICT：只在沒有被引用時才可刪除 不是所有的DBMS都支持 25 SQL的數(shù)據(jù)定義 基本表的創(chuàng)建、修改和撤銷 表的修改 句法 ALTER TABLE 修改屬性的類型 A

18、lter table modify modify alter column 其他修改，如 補充定義主鍵，撤銷主鍵定義 補充定義外鍵，撤銷外鍵定義 定義和撤銷別名 26 SQL的數(shù)據(jù)定義 基本表的創(chuàng)建、修改和撤銷 表的撤銷 句法 DROP TABLE cascaderestrict 闡明 CASCADE：刪除該基本表時，所有引用該基本 表的視圖和約束一起自動被刪除 RESTRICT：沒有視圖和約束引用該基本表時， 才能撤銷 27 SQL的數(shù)據(jù)定義 索引的建立和撤銷 索引概念 是物理存取路徑，不屬于邏輯數(shù)據(jù)模式 RDBMS通常在主鍵上自動建立索引 查詢、更新時自動起作用適當(dāng)建立索引會提高 查詢速度

19、） 句法 創(chuàng)建索引 CREATE UNIQUE INDEX ON 基表名 ( ASCDESC , ASCDESC ) ; 撤銷索引DROP INDEX ; UNIQUE索引，只能建立在候選鍵上 28 SQL的數(shù)據(jù)定義 索引的建立和撤銷 例如 Create index Age_Indexon S(Age) ; Create unique index Age_Index on S(Age ASC) ; Create unique index SC_Index on SC(SNO, CNO DESC) ; Drop index Age_Index, SC_Index ; Create index C

20、_Index on C(TNO DESC, Credit DESC) ; SNO SNAME AGE SEX NativePlace S3HU17M上海 S2LIU18F山東 S1WANG20M北京 S4XIA20F四川 ? ? CNO CNAME CreditCreditHoursCPNO TNO C2English464NULLT2 C3PM232C2T2 C4DB3.556C1T1 C1Math348NULLT1 29 SQL的數(shù)據(jù)更新 數(shù)據(jù)插入 句法 1單元組的插入 INSERT INTO 基本表名 (列名表) VALUES(元 組值) 2多元組的插入：不是所有版本的DBMS都支持 I

21、NSERT INTO 基本表名 (列名表) VALUES (元組值), , (元組 值) 3查詢結(jié)果的插入 INSERT INTO 基本表名 (列名表) 4表的插入： INSERT INTO 基本表名1 (列名表) TABLE 基本 表名2 30 SQL的數(shù)據(jù)更新 數(shù)據(jù)插入 闡明 若插入的元組值，其屬性個數(shù)、次序和域，與表 結(jié)構(gòu)的定義一致，則列名表可以省略 否則需要列名表，且列名表的屬性個數(shù)和次序， 需與待插入值一一對應(yīng) 數(shù)據(jù)插入受關(guān)系完整性約束的制約 數(shù)據(jù)類型 實體完整性：主鍵值唯一，非空 參照完整性：不允許引用不存在的實體 用戶自定義的完整性 31 SQL的數(shù)據(jù)更新 數(shù)據(jù)插入 例如 Ins

22、ert Into S(Sno, Sname, Age, Sex, NativePlace) Values(S5, GU, 20, M, NULL) ; Insert Into S Values(S5, GU, 20, M, NULL) ; Insert Into S(Sno, Sname, Age, Sex) Values(S5, GU, 20, M) ; Insert Into S(Sname, Sno, Sex, Age) Values(GU, S5, M, 20) ; Insert Into SC(S#, C#) Values(S5, C8); Insert Into SC Values

23、(S5, C8, NULL); C（CNO，CNAME，Credit，CreditHours，CPNO，TNO） S（SNO，SNAME，AGE，SEX，NativePlace） T（TNO，TNAME，TITLE，SEX） SC（SNO，CNO，Grade） 32 數(shù)據(jù)插入 例如 Insert Into S_Grade(S#, AVG_Grade) Select Sno, AVG(Grade) From SC Where Sno in (Select Sno From S Where SexM) Group By Sno Having AVG(Grade)80 ; Insert Into S

24、C(Sno, Cno) TABLE SC4 ; SQL的數(shù)據(jù)更新 C（CNO，CNAME，Credit，CreditHours，CPNO，TNO） S（SNO，SNAME，AGE，SEX，NativePlace） T（TNO，TNAME，TITLE，SEX） SC（SNO，CNO，Grade） 33 SQL的數(shù)據(jù)更新 數(shù)據(jù)插入 例如，問： Insert Into SC Values(S4, C569, 105) ; Insert Into SC Values(S4, C5,105) ; SNO SNAME AGE SEX NativePlace S1WANG20M北京 S2LIU18F山東 S

25、3HU17M上海 S4XIA19F四川 CNO CNAME CreditCreditHoursCPNO TNO C1Math348NULLT1 C2English464NULLT2 C3PM232C2T2 C4DB3.556C1T1 SNO CNO Grade S1C280 S1C370 S1C485 S2C160 S2C275 S2C390 S2C4NULL S3C185 S3C480 S4C285 S4C475 TNO TNAMETITLE SEX T1ZHAO講師M T2LIU教授F ? ? ? ? 34 SQL的數(shù)據(jù)更新 數(shù)據(jù)插入 例如，問： Insert Into SC Values

26、(S4, C5, 95) ; Insert Into SC Values(S4, C4, 95) ; 如圖示4條相關(guān)數(shù)據(jù)的插入順序 是什么？ SNO SNAME AGE SEX NativePlace S1WANG20M北京 S2LIU18F山東 S3HU17M上海 S4XIA19F四川 SNO CNO Grade S1C280 S1C370 S1C485 S2C160 S2C275 S2C390 S2C4NULL S3C185 S3C480 S4C285 S4C475 TNO TNAME TITLE SEX T1ZHAO講師M T2LIU教授F ? ? ? ? ? ? CNO CNAME C

27、redit CreditHours CPNO TNO C1Math348NULLT1 C2English464NULLT2 C3PM232C2T2 C4DB3.556C1T1 35 SQL的數(shù)據(jù)更新 數(shù)據(jù)刪除 句法 DELETE FROM WHERE 條件表 達(dá)式 語義 是從基本表中刪除滿足條件表達(dá)式的元組 留意 DELETE語句只能從一個基本表中刪除元組 WHERE子句中條件可以嵌套，也可以是來自幾 個基本表的復(fù)合條件 如果沒有WHERE子句，則刪除表的所有元組， 表成為空表 36 SQL的數(shù)據(jù)更新 數(shù)據(jù)刪除 例如 Delete From SC Where Grade is NULL ; D

28、elete From C Where Cname Like %M% ; Delete From SC Where Cno in ( Select Cno From C Where Cname = MATHS ) ; Delete From SC, C Where SC.Cno = C.Cno and Cname = MATHS ; Delete From SC Where Cno = C4 and Grade ( Select AVG(Grade) From SC Where Cno = C4 ); ? ? CNO CNAME CreditCreditHoursCPNO TNO C1Math3

29、48NULLT1 C2English464NULLT2 C3PM232C2T2 C4DB3.556C1T1 SNO CNO Grade S1C280 S1C370 S1C485 S2C160 S2C275 S2C390 S2C4NULL S3C185 S3C480 S4C285 S4C475 Create Table SC ( SNO CHAR(3) , CNO CHAR(3) , GRADE DEC(5, 2) , Primary Key (SNO, CNO) , Foreign Key (SNO) References S(SNO) ON DELETE restrict , Foreign

30、 Key (CNO) References C ON DELETE cascade , Check ( GRADE Between 0 And 100) ) ; 37 SQL的數(shù)據(jù)更新 數(shù)據(jù)刪除 例如，問 Delete From S Where Sname Like %U% ; SNO SNAME AGE SEX NativePlace S1WANG20M北京 S2LIU18F山東 S3HU17M上海 S4XIA19F四川 CNO CNAME CreditCreditHoursCPNO TNO C1Math348NULLT1 C2English464NULLT2 C3PM232C2T2 C4D

31、B3.556C1T1 SNO CNO Grade S1C280 S1C370 S1C485 S2C160 S2C275 S2C390 S2C4NULL S3C185 S3C480 S4C285 S4C475 TNO TNAME TITLE SEX T1ZHAO講師M T2LIU教授F ? ? 38 SQL的數(shù)據(jù)更新 數(shù)據(jù)刪除 例如，問 Delete From S Where Sname Like %U% ; Delete From SC Where Sno in ( Select Sno From S Where Sname Like %U% ); Delete From S Where Sn

32、ame Like %U% ; SNO SNAME AGE SEX NativePlace S1WANG20M北京 S2LIU18F山東 S3HU17M上海 S4XIA19F四川 CNO CNAME CreditCreditHoursCPNO TNO C1Math348NULLT1 C2English464NULLT2 C3PM232C2T2 C4DB3.556C1T1 SNO CNO Grade S1C280 S1C370 S1C485 S2C160 S2C275 S2C390 S2C4NULL S3C185 S3C480 S4C285 S4C475 TNO TNAME TITLE SEX T

33、1ZHAO講師M T2LIU教授F ? ? 39 SQL的數(shù)據(jù)更新 數(shù)據(jù)刪除 例如，問 如圖示4條相關(guān)數(shù)據(jù)的刪除順序是什么？ Drop Table SC 與 Delete From SC 的區(qū)別是什么 TRUNCATE TABLE SC SNO SNAME AGE SEX NativePlace S1WANG20M北京 S2LIU18F山東 S3HU17M上海 S4XIA19F四川 CNO CNAME CreditCreditHoursCPNO TNO C1Math348NULLT1 C2English464NULLT2 C3PM232C2T2 C4DB3.556C1T1 SNO CNO Gr

34、ade S1C280 S1C370 S1C485 S2C160 S2C275 S2C390 S2C4NULL S3C185 S3C480 S4C285 S4C475 TNO TNAME TITLE SEX T1ZHAO講師M T2LIU教授F ? ? ? ? ? ? 40 SQL的數(shù)據(jù)更新 數(shù)據(jù)刪除 當(dāng)LIU老師不再教授課程時，刪除其基本信息 Delete From T Where Tname = LIU AND Tno Not in （ Select Tno From C） 當(dāng)PM課程沒人選修時，刪除這門課程信息 當(dāng)WANG同學(xué)不再選修課程時，刪除其基本信息 SNO SNAME AGE SE

35、X NativePlace S1WANG20M北京 S2LIU18F山東 S3HU17M上海 S4XIA19F四川 CNO CNAME CreditCreditHoursCPNO TNO C1Math348NULLT1 C2English464NULLT2 C3PM232C2T2 C4DB3.556C1T1 SNO CNO Grade S1C280 S1C370 S1C485 S2C160 S2C275 S2C390 S2C4NULL S3C185 S3C480 S4C285 S4C475 TNO TNAME TITLE SEX T1ZHAO講師M T2LIU教授F ? ? ? ? 41 SQ

36、L的數(shù)據(jù)更新 數(shù)據(jù)刪除 TRUNCATE TABLE 與 DELETE From 功能上相同：均刪除表中的全部行 但 TRUNCATE TABLE 比 DELETE 速度快，且 使用的系統(tǒng)和事務(wù)日志資源少 DELETE 語句每次刪除一行，并在事務(wù)日志中為 所刪除的每行記錄一項 TRUNCATE TABLE 通過釋放存儲表數(shù)據(jù)所用的 數(shù)據(jù)頁來刪除數(shù)據(jù)，并且只在事務(wù)日志中記錄頁 的釋放 42 SQL的數(shù)據(jù)更新 數(shù)據(jù)刪除 TRUNCATE TABLE 與 DELETE From TRUNCATE TABLE 刪除表中的所有行，但表結(jié) 構(gòu)及其列、約束、索引等保持不變；新行標(biāo)識所 用的計數(shù)值重置為該列的

37、種子，如果想保留標(biāo)識 計數(shù)值，應(yīng)使用 DELETE 如果要刪除表定義及其數(shù)據(jù)，應(yīng)使用 DROP TABLE 語句 對于由 FOREIGN KEY 約束引用的表，不能使用 TRUNCATE TABLE，而應(yīng)使用不帶 WHERE 子 句的 DELETE 語句 由于 TRUNCATE TABLE 不記錄在日志中，所以 它不能激活觸發(fā)器 TRUNCATE TABLE 不能用于參與了索引視圖的 表 43 SQL的數(shù)據(jù)更新 數(shù)據(jù)修改 句法 UPDATE SET 列名=值表達(dá)式，列名=值表達(dá) 式ROW = (元組) WHERE 條件表達(dá)式 語義 修改基本表中滿足條件表達(dá)式元組的指定屬性值 闡明 WHERE子

38、句表示要修改的元組需滿足的條件 SET子句表示要修改的列及其新值或元組值 UPDATE語句一次只能修改一個表中的元組 44 SQL的數(shù)據(jù)更新 數(shù)據(jù)修改 例如 Update C Set Cname=SE Where Cno=C3 ; Update SC Set Grade = Grade * 1.1 Where Sno in ( Select Sno From S Where Sex = F ) ; Update SC, S Set Grade = Grade * 1.1 Where SC.Sno = S.Sno and Sex = F ; SNO SNAME AGE SEX NativePla

39、ce S1WANG20M北京 S2LIU18F山東 S3HU17M上海 S4XIA19F四川 SNO CNO Grade S1C280 S1C370 S1C485 S2C160 S2C275 S2C390 S2C4NULL S3C185 S3C480 S4C285 S4C475 TNO TNAME TITLE SEX T1ZHAO講師M T2LIU教授F ? ? ? ? CNO CNAME CreditCreditHoursCPNO TNO C1Math348NULLT1 C2English464NULLT2 C3PM232C2T2 C4DB3.556C1T1 45 SQL的數(shù)據(jù)更新 數(shù)據(jù)修改

40、 例如 Update SC Set Grade = Grade * 1.05 Where CnoC4 and Grade ( Select AVG(Grade) From SC Where C# C4); Update C Set Row = (C3, SE, 4, 64 , C1, T2 ) Where Cno = C3 ; SNO SNAME AGE SEX NativePlace S1WANG20M北京 S2LIU18F山東 S3HU17M上海 S4XIA19F四川 SNO CNO Grade S1C280 S1C370 S1C485 S2C160 S2C275 S2C390 S2C4N

41、ULL S3C185 S3C480 S4C285 S4C475 TNO TNAME TITLE SEX T1ZHAO講師M T2LIU教授F ? ? CNO CNAME CreditCreditHoursCPNO TNO C1Math348NULLT1 C2English464NULLT2 C3PM232C2T2 C4DB3.556C1T1 46 SQL的數(shù)據(jù)更新 數(shù)據(jù)修改 例如 Update SC Set Grade = 60 Where Grade is NULL ; Update SC Set Grade = Grade + 60 Where Grade is NULL ; SNO SN

42、AME AGE SEX NativePlace S1WANG20M北京 S2LIU18F山東 S3HU17M上海 S4XIA19F四川 SNO CNO Grade S1C280 S1C370 S1C485 S2C160 S2C275 S2C390 S2C4NULL S3C185 S3C480 S4C285 S4C475 TNO TNAME TITLE SEX T1ZHAO講師M T2LIU教授F ? ? ? ? CNO CNAME CreditCreditHoursCPNO TNO C1Math348NULLT1 C2English464NULLT2 C3PM232C2T2 C4DB3.556

43、C1T1 47 SQL的數(shù)據(jù)更新 數(shù)據(jù)修改 例如 Update SC Set Grade is NULL Where Grade = 70 and Cno in ( Select Cno From C Where Cname = MATH ) ; Update SC Set Grade = NULL Where Grade = 70 and Cno in ( Select Cno From C Where Cname = MATH ) ; SNO SNAME AGE SEX NativePlace S1WANG20M北京 S2LIU18F山東 S3HU17M上海 S4XIA19F四川 SNO

44、CNO Grade S1C280 S1C370 S1C485 S2C160 S2C275 S2C390 S2C4NULL S3C185 S3C480 S4C285 S4C475 TNO TNAME TITLE SEX T1ZHAO講師M T2LIU教授F ? ? ? ? CNO CNAME CreditCreditHoursCPNO TNO C1Math348NULLT1 C2English464NULLT2 C3PM232C2T2 C4DB3.556C1T1 48 SQL的數(shù)據(jù)更新 數(shù)據(jù)修改 例如 Update SC Set Grade = Grade * 1.5 Where Sno= S2

45、 and Cno= C3 ; Update C Set Tno = T3 Where Cno= C3 ; SNO SNAME AGE SEX NativePlace S1WANG20M北京 S2LIU18F山東 S3HU17M上海 S4XIA19F四川 CNO CNAME CreditCreditHoursCPNO TNO C1Math348NULLT1 C2English464NULLT2 C3PM232C2T2 C4DB3.556C1T1 SNO CNO Grade S1C280 S1C370 S1C485 S2C160 S2C275 S2C390 S2C4NULL S3C185 S3C4

46、80 S4C285 S4C475 TNO TNAME TITLE SEX T1ZHAO講師M T2LIU教授F ? ? ? ? 49 SQL的數(shù)據(jù)更新 數(shù)據(jù)修改 例如 Update C Set Cno = C3 Where Cno= C4 ; Delete From SC Where Cno= C4 ; Update C Set Cno = C3 Where Cno= C4 ; Update C Set Cno=NULL Where Cno= C4 ; SNO SNAME AGE SEX NativePlace S1WANG20M北京 S2LIU18F山東 S3HU17M上海 S4XIA19F

47、四川 CNO CNAME CreditCreditHoursCPNO TNO C1Math348NULLT1 C2English464NULLT2 C3PM232C2T2 C4DB3.556C1T1 SNO CNO Grade S1C280 S1C370 S1C485 S2C160 S2C275 S2C390 S2C4NULL S3C185 S3C480 S4C285 S4C475 TNO TNAME TITLE SEX T1ZHAO講師M T2LIU教授F ? ? ? ? ? ? ? ? 50 SQL的數(shù)據(jù)查詢 SELECT語句的基本結(jié)構(gòu) 句型 SELECT FROM WHERE 只有SEL

48、ECT和FROM子句是每個SQL查詢語句 所必需的 語義 例如 查詢選修了DB課程的學(xué)生的姓名 SELECT A1, ,An FROMR1, , Rn WHERE F ; A1, , An(F(R1 Rn) ) 51 SQL的數(shù)據(jù)查詢 SELECT語句的基本結(jié)構(gòu) 例如 SNAME(CNAME=DB AND S.SNO=SC.SNO AND SC.CNO=C.CNO(SSCC) Select Sname From S, SC, C Where Cname=DB And S.Sno=SC.Sno And SC.Cno=C.Cno S.SNO SNAME C.CNO CNAME SC.SNO SC.

49、CNO GRADE SCSC SNO SNAME AGE SEX NativePlace S1WANG20M北京 S2LIU18F山東 S3HU17M上海 S4XIA19F四川S CNOCNAMECreditCreditHoursCPNO TNO C1Math348NULLT1 C2English464NULLT2 C3PM232C2T2 C4DB3.556C1T1C SNOCNOGrade S1C280 S1C370 S1C485 S2C160 S2C275 S2C390 S2C4NULL S3C185 S3C480 S4C285 S4C475 SC 1000行 1000行 10000行 C

50、（CNO，CNAME，Credit，CreditHours，CPNO，TNO） S（SNO，SNAME，AGE，SEX，NativePlace） T（TNO，TNAME，TITLE，SEX） SC（SNO，CNO，Grade） 52 SQL的數(shù)據(jù)查詢 SELECT語句的基本結(jié)構(gòu) 例如 SNAME(CNAME=DB(S SC C) DBMS的優(yōu)化 SNAME(SNO,SNAME(S) SNO(CNO( CNAME=DB(C) SNO,CNO(SC) Select Sname From S Where Sno in ( Select Sno From SC Where Cno in ( Selec

51、t Cno From C Where Cname=DB ) ; CNO(CNAME=DB (C) SNO,CNO(SC) SNO,SNAME (S) SNO( ) SNAME ( ) 1000110000 = 1000 1000 = 1000 = 1000 = 1000 53 SQL的數(shù)據(jù)查詢 SELECT語句的基本結(jié)構(gòu) 常見的3種SELECT語句寫法 例如：查詢選修C2課程的學(xué)生學(xué)號和姓名 1連接查詢 Select S.Sno, Sname From S, SC Where S.Sno=SC.Sno and Cno=C2 語句的執(zhí)行步驟 1笛卡爾積 2等值連接 2選擇和投影 SNO SNAM

52、E AGE SEX NativePlace S1WANG20M北京 S2LIU18F山東 S3HU17M上海 S4XIA19F四川 SNO CNO Grade S1C280 S1C370 S1C485 S2C160 S2C2NULL S2C390 S2C4NULL S3C185 S3C480 S4C2NULL S4C475 54 SQL的數(shù)據(jù)查詢 SELECT語句的基本結(jié)構(gòu) 常見的3種SELECT語句寫法 例如：查詢選修C2課程的學(xué)生學(xué)號和姓名 2IN嵌套查詢 Select Sno, Sname From S Where Sno in ( Select Sno From SC Where Cn

53、o=C2 ) ; Select Sno, Sname From S Where C2 in ( Select Cno From SC Where Sno=S.Sno ) ; 區(qū)別 1由里到外，內(nèi)層查詢執(zhí)行1次結(jié)果供給外層 使用 2依賴于外層值，內(nèi)層執(zhí)行n次結(jié)果供給外層 使用 SNO SNAME AGE SEX NativePlace S1WANG20M北京 S2LIU18F山東 S3HU17M上海 S4XIA19F四川 SNO CNO Grade S1C280 S1C370 S1C485 S2C160 S2C2NULL S2C390 S2C4NULL S3C185 S3C480 S4C2NUL

54、L S4C475 ? ? 55 SQL的數(shù)據(jù)查詢 SELECT語句的基本結(jié)構(gòu) 常見的3種SELECT語句寫法 例如：查詢選修C2課程的學(xué)生學(xué)號和姓名 3EXISTS嵌套查詢 Select Sno, Sname From S Where EXISTS ( Select * From SC Where Sno=S.Sno and Cno=C2 ) ; 闡明 13個位置特殊，需注意 2） EXISTS表示“”，判定內(nèi)層查詢結(jié)果是否為非 空， 即至少存在一個元組滿足外查詢的條件） SNO SNAME AGE SEX NativePlace S1WANG20M北京 S2LIU18F山東 S3HU17M上

55、海 S4XIA19F四川 SNO CNO Grade S1C280 S1C370 S1C485 S2C160 S2C2NULL S2C390 S2C4NULL S3C185 S3C480 S4C2NULL S4C475 56 SQL的數(shù)據(jù)查詢 SELECT語句的完整結(jié)構(gòu) 句型 執(zhí)行過程 讀取FROM子句中的數(shù)據(jù)源，執(zhí)行笛卡爾積 （） 選擇滿足WHERE子句值的元組 按GROUP BY子句指定的列值，將元組進(jìn)行 分組 在分出的所有組中，選取滿足HAVING子句 值的組 按SELECT子句的指定項，求值投影輸 出 按ORDER BY子句對式的輸出進(jìn)行排序 SELECT FROM WHERE GRO

56、UP BY HAVING ORDER BY , 57 SQL的數(shù)據(jù)查詢 SELECT語句的完整結(jié)構(gòu) 查詢中的5個聚合函數(shù) 函數(shù)釋義 COUNT COUNT( * )：統(tǒng)計元組的個數(shù) COUNT( ) ：對指定列，統(tǒng)計列值個數(shù) SUMSUM( )：對數(shù)值型列，求和 AVGAVG( )：對數(shù)值型列，求平均值 MAXMAX( )：對指定列，求最大值 MINMIN( )：對指定列，求最小值 58 SQL的數(shù)據(jù)查詢 SELECT語句的完整結(jié)構(gòu) 應(yīng)用示例 統(tǒng)計選修課程的人次數(shù) Select COUNT(Sno) as 人次數(shù) From SC ; 統(tǒng)計選修課程的人數(shù) Select COUNT(Distinc

57、t Sno) as 人數(shù) From SC ; SNO CNO Grade S1C280 S1C370 S1C485 S2C160 S2C275 S2C390 S2C4NULL S3C185 S3C480 S4C285 S4C475 C（CNO，CNAME，Credit，CreditHours，CPNO，TNO） S（SNO，SNAME，AGE，SEX，NativePlace） T（TNO，TNAME，TITLE，SEX） SC（SNO，CNO，Grade） 59 SQL的數(shù)據(jù)查詢 SELECT語句的完整結(jié)構(gòu) 應(yīng)用示例 統(tǒng)計男學(xué)生的總?cè)藬?shù)和平均年齡 Select COUNT(*) as 總?cè)藬?shù),

58、 AVG(Age) as 平均年齡 From S Where Sex = M ; 按性別統(tǒng)計學(xué)生的總?cè)藬?shù)和平均年齡 Select Sex, COUNT(*) as 總?cè)藬?shù), AVG(Age) as 平均年齡 From S Group By Sex ; SNO SNAME AGE SEX NativePlace S1WANG20M北京 S2LIU18F山東 S3HU17M上海 S4XIA19F四川 C（CNO，CNAME，Credit，CreditHours，CPNO，TNO） S（SNO，SNAME，AGE，SEX，NativePlace） T（TNO，TNAME，TITLE，SEX） SC（

59、SNO，CNO，Grade） 60 SQL的數(shù)據(jù)查詢 SELECT語句的完整結(jié)構(gòu) 應(yīng)用示例 統(tǒng)計每門課程的學(xué)生選修人數(shù)，要求顯示課程號、 課程名和學(xué)生人數(shù) Select C.Cno, Cname, COUNT(Sno) as 學(xué)生人 數(shù) From C, SC Where C.Cno = SC.Cno Group By C.Cno, Cname ; CNO CNAME CreditCreditHoursCPNO TNO C1Math348NULLT1 C2English464NULLT2 C3PM232C2T2 C4DB3.556C1T1 SNO CNO Grade S1C280 S1C370

60、S1C485 S2C160 S2C275 S2C390 S2C4NULL S3C185 S3C480 S4C285 S4C475 C（CNO，CNAME，Credit，CreditHours，CPNO，TNO） S（SNO，SNAME，AGE，SEX，NativePlace） T（TNO，TNAME，TITLE，SEX） SC（SNO，CNO，Grade） C（CNO，CNAME，Credit，CreditHours，CPNO，TNO） S（SNO，SNAME，AGE，SEX，NativePlace） T（TNO，TNAME，TITLE，SEX） SC（SNO，CNO，Grade） 61 SQ