1、Page 1計教中心計教中心數據庫原理及應用數據庫原理及應用 2 2 關系數據庫關系數據庫姚普選姚普選Page 2內內 容容 關系模型關系模型 關系及關系模式關系及關系模式 關系模型的數據操作關系模型的數據操作 數據完整性約束數據完整性約束 關系代數關系代數 關系運算關系運算 關系代數的運算種類關系代數的運算種類 三種關系運算三種關系運算 SQL語言語言 2.1 關系模型關系模型關系模型由三部份組成關系模型由三部份組成 數據結構數據結構、數據操作數據操作、完整性約束完整性約束1.1.關系模型的數據結構：數據的邏輯結構關系模型的數據結構：數據的邏輯結構_ _二維表二維表學生學生選修選修課程課程m

2、n概念模型概念模型關系模式關系模式表示為表示為 關系名（屬性名關系名（屬性名1 1，屬性名，屬性名2 2，屬性名，屬性名n n）例：例：學生學生(學號，姓名，性別，年齡，班級，學院學號，姓名，性別，年齡，班級，學院)注意：注意：l關系模式關系模式是是型型(type)，描述一個關系的結構，描述一個關系的結構l關系是值，是元組的集合關系是值，是元組的集合 描述某一時刻關系模式的內容描述某一時刻關系模式的內容l故關系模式是相對穩定的，靜態的；故關系模式是相對穩定的，靜態的； 而關系是隨時間變化的，動態的而關系是隨時間變化的，動態的l關系關系可用來表示可用來表示實體實體（如學生，課程等）（如學生，課程

3、等） 也可用來表示也可用來表示實體間的聯系實體間的聯系（如選課）（如選課）關系的性質關系的性質l 每列都是不可再分的每列都是不可再分的基本字段基本字段（原子屬性）（原子屬性） 一列中各個分量具有相同的性質和意義；一列中各個分量具有相同的性質和意義；l 列名列名是唯一的（不同不二名）是唯一的（不同不二名）l 行的次序行的次序可任意交換，不會改變關系的意義可任意交換，不會改變關系的意義l 列的次序列的次序可任意交換，不會改變關系的意義可任意交換，不會改變關系的意義l 元組代表了一個實體元組代表了一個實體 因此表中因此表中不允許出現相同的行不允許出現相同的行故，判別兩個關系是否相等，與屬性次序、元組

4、次故，判別兩個關系是否相等，與屬性次序、元組次序、關系名均無關序、關系名均無關若僅這三點有別，其余完全相同，若僅這三點有別，其余完全相同， 可認為兩個關系相等可認為兩個關系相等2.2.關系模型的數據操作關系模型的數據操作l 數據操作數據操作包括對包括對表、行、列表、行、列的操作的操作l 主要操作包括主要操作包括查詢、添加（或插入）、刪除、修改查詢、添加（或插入）、刪除、修改例如：查找來自于江蘇的例如：查找來自于江蘇的02級學生的信息級學生的信息 l 關系模型的數據操作的特點關系模型的數據操作的特點：n關系模型的操作對象是集合關系模型的操作對象是集合 操作對象和操作結果都是關系（表）操作對象和操

5、作結果都是關系（表）n用戶只要指明干什么，而不必考慮怎么干用戶只要指明干什么，而不必考慮怎么干 細節由細節由DBMS處理處理nSQL語言實現關系數據庫操作語言實現關系數據庫操作 RDBMS支持支持SQL語言語言3.3.關系模型的數據完整性約束關系模型的數據完整性約束完整性約束：完整性約束：規定了特定數據模型中規定了特定數據模型中 數據必須滿足的條件數據必須滿足的條件l無論對數據進行什么操作，無論對數據進行什么操作， 其結果都必須滿足完整性約束的條件其結果都必須滿足完整性約束的條件l完整性約束的目的：完整性約束的目的： 使對數據的操作能夠正確進行使對數據的操作能夠正確進行 使數據有意義使數據有意

6、義例如：例如： 學號既不能為空也不能重復學號既不能為空也不能重復 年齡不大于年齡不大于60歲歲 學生所屬班級名稱不能寫成根本不存在的班級學生所屬班級名稱不能寫成根本不存在的班級 考試成績是考試成績是0-100之間的整數之間的整數四類完整性約束四類完整性約束實體完整性，引用完整性，值域完整性，實體完整性，引用完整性，值域完整性，用戶定義的完整性用戶定義的完整性實體完整性實體完整性（EI，Entity Integrity） 強制保證表的主碼的完整性強制保證表的主碼的完整性 通過主碼實現: 主碼的任一屬性都不能取空值主碼的任一屬性都不能取空值l “空空”（null）指無值（或不知道）指無值（或不知道

7、） 0不是不是null。null”null”主碼是區分各個實體和聯系的惟一標識主碼是區分各個實體和聯系的惟一標識 為空則意味著可存在不以主碼為標識的實體，為空則意味著可存在不以主碼為標識的實體， 與主碼定義矛盾與主碼定義矛盾引用引用( (參照參照) )完整性完整性（RI，Referential Integrity）插入、修改或刪除數據時，維護各表間數據一致性插入、修改或刪除數據時，維護各表間數據一致性 主要通過主要通過主碼與外碼主碼與外碼實現實現若二表通過主碼與外碼建立了聯系，則：若二表通過主碼與外碼建立了聯系，則：l外碼所在表（子表）不能引用主碼所在表（主表）外碼所在表（子表）不能引用主碼所

8、在表（主表）中不存在的數據中不存在的數據l主表中數據變化時，子表中相關數據相應變化。反主表中數據變化時，子表中相關數據相應變化。反之也一樣之也一樣l有時外碼可取空值。如有時外碼可取空值。如“學生學生”關系的關系的“宿舍號宿舍號”例：例：“學生學生”、“專業專業”二實體表示為關系二實體表示為關系 學生學生（學號學號，姓名，性別，專業號，出生日期），姓名，性別，專業號，出生日期） 專業專業（專業號專業號，專業名），專業名）引用完整性引用完整性值域完整性值域完整性（DI，Domain Integrity ）限制屬性值的范圍限制屬性值的范圍。例如：。例如： 性別只能是性別只能是”男男”或或”女女”，年

9、齡不大于，年齡不大于30等等 Access中的中的“有效性規則有效性規則”： “男男” or “女女” =30 SQL語句的語句的Where子句子句中寫：中寫： 性別性別=“男男” or “女女” 年齡年齡=30屬性值可為空，例如，學生管理信息系統中，屬性值可為空，例如，學生管理信息系統中， 新生報到后要記錄其住宿房號，新生報到后要記錄其住宿房號， 若該生不在學校住宿，則宿舍號可為空若該生不在學校住宿，則宿舍號可為空用戶定義的完整性用戶定義的完整性實現應用系統的業務規則實現應用系統的業務規則，由用戶自行定義，由用戶自行定義如：項目書的名稱必須以如：項目書的名稱必須以“XM”開頭開頭 折扣額不能

10、大于銷售額，等等折扣額不能大于銷售額，等等DBMS提供了用戶自定義完整性的手段：提供了用戶自定義完整性的手段：n 約束（約束（constraint）n 標識列（標識列（identity column）n 默認值（默認值（default）n 規則（規則（rule）n 觸發器（觸發器（trigger）n 數據類型驗證（數據類型驗證（data type）n 索引（索引（index）n 存儲過程（存儲過程（stored procedure）Page 13 2.2 關系代數關系代數 1. 關系代數簡介關系代數簡介關系模型關系模型：提供一組完備的關系運算，：提供一組完備的關系運算， 支持關系數據庫檢索和修

11、改支持關系數據庫檢索和修改（插入、更新、刪除）（插入、更新、刪除）操作操作關系運算方法分為兩類：關系運算方法分為兩類：關系代數關系代數：關系數據操縱的傳統表達方式，關系數據操縱的傳統表達方式， 以集合代數運算方法對關系進行數據操作以集合代數運算方法對關系進行數據操作 以若干個關系為運算對象，運算結果產生新關系以若干個關系為運算對象，運算結果產生新關系關系演算關系演算：以謂詞表達式描述關系操作的：以謂詞表達式描述關系操作的 條件和要求條件和要求Page 14關系代數運算的種類：關系代數運算的種類：二元二元二元二元按列按列輔助專門的關系運算輔助專門的關系運算按行按行按行運算按行運算說明說明不等于不

12、等于小于等于小于等于大于等于大于等于等于等于小于小于或或與與除除 連接連接投影投影 廣義笛卡爾積廣義笛卡爾積交交差差大于大于比較比較運算符運算符傳統的傳統的集合集合運算符運算符并并非非 邏輯邏輯運算符運算符選擇選擇 專門的專門的關系關系運算符運算符含義含義運算符運算符Page 152. 關系運算關系運算并運算并運算：RS 元組屬于元組屬于R或或S 差運算差運算：R-S 元組屬于元組屬于R交運算交運算：RS 元組既屬元組既屬R 又屬于又屬于S集合運算集合運算(按元組按元組)設關系設關系R、S度度(屬性個數屬性個數)為為n，相應屬性值取，相應屬性值取自同一個域自同一個域Page 16三種關系運算三

13、種關系運算：選擇、投影、連接選擇、投影、連接例例7-4：“學生學生”關系關系 Access數據庫中的數據庫中的“學生學生”表表Page 17選擇運算：選擇運算：按條件從一個或多個關系中按條件從一個或多個關系中 抽取若干個元組組成新關系，抽取若干個元組組成新關系，新關系是原關系的子集。新關系是原關系的子集。例如，條件：性別例如，條件：性別= =“女女”Page 18投影運算：投影運算：從一個或多個關系中從一個或多個關系中 選擇一些屬性組成新關系，選擇一些屬性組成新關系， 新關系是原關系子集新關系是原關系子集 例例：學生學生關系在關系在學號學號、姓名姓名、 性別性別、班級班級四個屬性上投影四個屬性

14、上投影Page 19連接運算：連接運算：連接運算：連接運算：將兩個關系中滿足條件的元組連接起來將兩個關系中滿足條件的元組連接起來 組成新關系組成新關系等連接：等連接：取兩關系笛卡爾積中取兩關系笛卡爾積中A、B屬性值相等的元組屬性值相等的元組R.C=S.TRSPage 20 2.3 SQL語言介紹語言介紹SQL（Structured Query Language，l 結構化查詢語言）結構化查詢語言） ISO命名為國際標準數據庫語言命名為國際標準數據庫語言提供了提供了數據定義數據定義、數據查詢數據查詢、數據操縱數據操縱和和數據控制數據控制語句語句，是一種綜合性的數據庫語言，可獨立完成，是一種綜合性

15、的數據庫語言，可獨立完成數據庫生命周期中的全部活動數據庫生命周期中的全部活動用戶可用戶可直接鍵入直接鍵入SQL命令命令來操縱數據庫，來操縱數據庫， 也可將其也可將其嵌入嵌入高級語言高級語言（C、Pascal、Java等）等）程序中使用程序中使用各種各種RDBMS一般都支持一般都支持SQL或提供或提供SQL接口接口。其。其影響已超出數據庫領域，擴展到了其他領域影響已超出數據庫領域，擴展到了其他領域Page 211. SQL對關系數據庫的支持對關系數據庫的支持SQL視圖視圖1視圖視圖2基表基表1基表基表2基表基表3基表基表4存儲文件存儲文件1存儲文件存儲文件2外模式外模式模模 式式內模式內模式從從

16、DBMS的角度看，數據庫系統有一個嚴謹的體系的角度看，數據庫系統有一個嚴謹的體系結構，從而保證其功能得以實現結構，從而保證其功能得以實現 SQL語言支持三級模式結構語言支持三級模式結構Page 22說明：說明：由由基表基表構成關系數據庫的構成關系數據庫的“模式模式” 基表是實際的表，一個基對應一個關系；基表是實際的表，一個基對應一個關系； 一個或多個表對應一個存儲文件；一個或多個表對應一個存儲文件；存儲文件的邏輯結構組成關系數據庫內模式；存儲文件的邏輯結構組成關系數據庫內模式；視圖是從一個或多個表中導出的表，為虛表，視圖是從一個或多個表中導出的表，為虛表， 數據庫只存放其定義而數據仍在原表中數

17、據庫只存放其定義而數據仍在原表中視圖和基表都是關系視圖和基表都是關系用戶使用用戶使用SQL語言對基表和視圖進行查詢、語言對基表和視圖進行查詢、 更新等各種操作更新等各種操作SQL語言功能概要語言功能概要四類功能：四類功能：數據定義數據定義：CREATE、DROP、ALTER 創建表、索引、查詢創建表、索引、查詢數據操縱數據操縱：INSERT、UPDATE、DELETE 插入、更新、刪除插入、更新、刪除數據查詢數據查詢：SELECT 查詢查詢數據控制數據控制：GRANT、REVOTE 授予、收回權限授予、收回權限閱讀：閱讀：SQL語言的發展語言的發展SQL：Structured Query La

18、nguagel1974年年 IBM公司公司Boyce 和和Chamberlin提出提出l1975-1979年年 IBM在在System R關系數據庫系統原型關系數據庫系統原型中實現中實現l1986年年10月月 成為成為ANSI標準：標準：SQL-86l1987年年6月月 接納為接納為ISO標準標準 l1989年年4月月 ISO較為完整的較為完整的SQL-89標準標準l1992年年8月月 ISO推出更完善的推出更完善的SQL-92標準標準l1999年年 ISO推出新的推出新的SQL-99標準標準閱讀：閱讀：SQL語言的特點語言的特點l一體化 集數據定義語言集數據定義語言DDL、數據操縱語言、數據

19、操縱語言DML和數據和數據控制語言控制語言DCL于一體，綜合管理功能于一身于一體，綜合管理功能于一身l高度非過程化 用戶只需提出用戶只需提出“做什么做什么”，而不需指明，而不需指明“怎么做怎么做”l面向集合的操作方式 操作的數據不是一條記錄，而是記錄的集合操作的數據不是一條記錄，而是記錄的集合l同樣語法提供兩種使用方式 用戶可直接鍵盤交互方式使用用戶可直接鍵盤交互方式使用SQL命令，也可將命令，也可將SQL語句嵌入其他高級語言（語句嵌入其他高級語言（VB、Delphi、VC、Java等）等）l語言簡潔，容易掌握 命令（語句）只有命令（語句）只有9條，語法接近于自然語言條，語法接近于自然語言2.

20、 SQL語言的數據類型語言的數據類型不同數據庫產品所支持的數據類型稍有差別不同數據庫產品所支持的數據類型稍有差別 （以（以SQL Server 2000為例）為例）兩大類：兩大類： 系統數據類型 系統內含的系統內含的 用戶自定義數據類型 在系統數據類型的基礎上建立在系統數據類型的基礎上建立 使用系統存儲過程使用系統存儲過程sp_addtype定義定義SQL Server 2000的系統數據類型如下：的系統數據類型如下：1）數值型）數值型 數據類型數據類型說明說明精精確確整數型整數型Bigint8字節，字節， -263263-1Int4字節，字節， -231231-1Smallint2字節，字節

21、， -215215-1Tinyint1字節，字節， 0255精確數值精確數值型型Decimal(p,q)或或Numeric(p,q)-1038+1 1038-1p為總位數，為總位數，q為小數位為小數位數數位型位型Bit存儲二進制存儲二進制0或或1近近似似浮點型浮點型Float-1.79E+308 1.79E+308Real-3.4E+38 3.4E+382） 字符型字符型 數據類型說明定長字符串定長字符串Char(n)n18000變長字符串變長字符串Varchar(n)n18000文本文本Text最多最多2311個字符個字符定長定長UnicodeNchar(n)n14000變長變長Unicod

22、eNvarchar(n)n14000Unicode文本文本Ntext最多最多2301個字符個字符定長二進制字定長二進制字符符Binary(n)n18000變長二進制字變長二進制字符符Varbinary(n)n18000任意二進制數任意二進制數據據Image最多最多2311個字節，約個字節，約2GB3） 日期時間型日期時間型數據類型數據類型說明說明Datetime8字節，字節，1753年年1月月1日日9999年年12月月31日日精度百分之三秒精度百分之三秒Smalldatetime4字節，字節，1900年年1月月1日日2079年年6月月6日，日，精度精度1分鐘分鐘日期日期：英文數字：英文數字 O

23、ct 25 2005 數字加分隔符數字加分隔符 2005-10-25 或或 2005/12/25 純數字純數字 20051025時間時間：24小時制小時制 15:28:56 12小時制小時制 3:28:56 PM4） 貨幣型貨幣型數據類型數據類型說明說明Money8字節，字節， -263 263-1，精確度為精確度為4位小數，位小數，最多最多19位數字位數字Smallmoney4字節，字節， -214748.3648 214748.3647，精確度為精確度為4位小數位小數SQL語言的數據定義語句語言的數據定義語句操作對象操作對象 創建創建 刪除刪除 修改修改 表表 CREATE TABLE D

24、ROP TABLE ALTER TABLE 視圖視圖 CREATE VIEW DROP VIEW 索引索引 CREATE INDEX DROP INDEX 觸發器觸發器 CREATE TRIGGER 存儲過程存儲過程 CREATE PROCEDURE 2.3 SQL語言數據定義功能語言數據定義功能1. 表的定義與刪除表的定義與刪除1) 建立表結構建立表結構 _基本語法：基本語法：列名列名表中列的名字表中列的名字類型類型該列該列數據類型約束約束該列該列完整性約束條件： NOT NULL 列值不能為空（不能用于表級約束）列值不能為空（不能用于表級約束） DEFAULT 指定列的默認值（不能用于表級

25、約束）指定列的默認值（不能用于表級約束） UNIQUE 列值不能重復列值不能重復 CHECK 限定列的取值范圍限定列的取值范圍 PRIMARY KEY 指定本列為主碼指定本列為主碼 FOREIGN KEY 本列為引用其他表的外碼，用法如下本列為引用其他表的外碼，用法如下 FOREIGN KEY 列名列名 REFERENCES 外表名外表名 (外表列名外表列名) CREATE TABLE 表名表名 (列名列名 類型類型 約束，約束，)，表級約束表級約束 例：創建例：創建student表表 CREATE TABLE student ( Sno char(7) PRIMARY KEY, Sname

26、char(10) NOT NULL, Ssex char(2) CHECK (Ssex = 男男 OR Ssex = 女女 ), Sage tinyint CHECK (Sage = 15 AND Sage 0), Semester tinyint CHECK (Semester 0), Period int CHECK (Period 0), PRIMARY KEY (Cno) )取值大于取值大于0整數整數學時學時Period取值大于取值大于0整數整數學期學期Cemester取值大于取值大于0整數整數學分學分Ccredit非空非空字符串，長度為字符串，長度為20課程名課程名Cname主碼主碼

27、字符串，長度為字符串，長度為10課程號課程號Cno約約 束束數據類型數據類型說說 明明列列 名名例：創建例：創建SC表表CREATE TABLE SC ( Sno char(7) NOT NULL, Cno char(10) NOT NULL, Grade tinyint CHECK (Grade=0 AND Grade=100), PRIMARY KEY(Sno, Cno), FOREIGN KEY(Sno) REFERENCES student (Sno), FOREIGN KEY(Cno) REFERENCES course (Cno) )取值取值0100整數整數成績成績Grade主碼，

28、引用主碼，引用course的外碼的外碼字符串，長度為字符串，長度為10課程號課程號Cno主碼，引用主碼，引用student的外碼的外碼字符串，長度為字符串，長度為7學號學號Sno約約 束束數據類型數據類型說說 明明列列 名名說明：說明： 刪除表時會將表結構和表中的數據一起刪除刪除表時會將表結構和表中的數據一起刪除例：例：刪除刪除test表表 DROP TABLE test 2) 刪除表（刪除表（DROP TABLE）基本語法：基本語法： DROP TABLE 表名表名3) 修改表結構修改表結構基本語法：基本語法：例例1：為：為SC表添加表添加“選課類別選課類別”列：列：XKLB char(4)

29、 ALTER TABLE SC ADD XKLB char(4)例例2：將新添加的：將新添加的XKLB列的類型改為列的類型改為char(6) ALTER TABLE SC ALTER COLUMN XKLB char(6)ALTER TABLE 表名表名 ALTER COLUMN 列名列名 新類型新類型 修改列定義修改列定義 | ADD COLUMN 列名列名 類型類型 約束約束 增加新列增加新列 | DROP COLUMN 列名列名 刪除列刪除列 | ADD PRIMARY KEY (列名列表列名列表 ) 增加主碼約束增加主碼約束 | ADD FOREIGN KEY (列名列名 ) REFE

30、RENCES 外表名外表名 (列名列名 ) 增加外碼約束增加外碼約束更新：插入、刪除、修改更新：插入、刪除、修改表中插入一行數據：表中插入一行數據： insert into student () VALUES ()修改（更新）表中數據：修改（更新）表中數據： UPDATE student SET Sage=Sage+1刪除數據：刪除數據： DELETE SC WHERE Grade 60清空整個表：清空整個表： TRUNCATE TABLE SC 2.4 SQL語言數據定義功能語言數據定義功能1. 插入數據插入數據基本語法：基本語法：列名表列名表表中的列名的列表，列名之間用逗號分隔表中的列名的

31、列表，列名之間用逗號分隔 未指定列名表時，值列表中值的順序須與表中定義列的順序未指定列名表時，值列表中值的順序須與表中定義列的順序一致，且每列都要有值（或為空）一致，且每列都要有值（或為空）值列表值列表相應列的值，值之間用逗號分隔相應列的值，值之間用逗號分隔 值列表中的值要與列名表中的列按位置順序對應，且應滿足值列表中的值要與列名表中的列按位置順序對應，且應滿足約束條件約束條件 INSERT INTO 表名表名 (列名表列名表) VALUES (值列表值列表)例：學生記錄（9512101,李勇,男,19,計算機系）插入student表insert into student (Sno, Snam

32、e, Ssex, Sage, Sdept) VALUES (9512101, 李勇李勇, 男男, 19, 計算機系計算機系)2. 更新數據更新數據基本語法：基本語法：例例1：所有學生年齡加：所有學生年齡加1 UPDATE student SET Sage=Sage+1 例例2：9512101學生年齡加學生年齡加1 UPDATE student SET Sage=Sage+1 WHERE Sno = 9512101SET子句子句指定要改的列及改后的值，可一次修改多列指定要改的列及改后的值，可一次修改多列WHERE子句子句指定要修改表中哪些記錄，如果省略，指定要修改表中哪些記錄，如果省略， 則所有

33、記錄的指定列都被修改則所有記錄的指定列都被修改 UPDATE 表名表名 SET 列名列名=表達式表達式 , WHERE 條件條件 3. 刪除數據刪除數據基本語法：基本語法：例例1：刪除所有學生的選課記錄：刪除所有學生的選課記錄 DELETE FROM SC 例例2：刪除所有不及格學生的選課記錄：刪除所有不及格學生的選課記錄 DELETE SC WHERE Grade 、=、=、(或或!=)、NOT 確定范圍確定范圍BETWEENAND、NOT BETWEENAND確定集合確定集合IN、NOT IN相似字符串匹配相似字符串匹配 LIKE、NOT LIKE空值空值IS NULL、IS NOT NU

34、LL多重條件多重條件AND、OR查詢過程中，可以使用篩選條件，使得查詢結果中查詢過程中，可以使用篩選條件，使得查詢結果中只包含滿足條件的元組（記錄行）只包含滿足條件的元組（記錄行）查詢滿足條件的元組是通過查詢滿足條件的元組是通過WHERE子句實現的。子句實現的。WHERE子句中常用的查詢條件如下：子句中常用的查詢條件如下：帶條件的查詢比較大小帶條件的查詢比較大小例例1：查詢計算機系全體學生的姓名：查詢計算機系全體學生的姓名 SELECT Sname FROM student WHERE Sdept = 計算機系計算機系 例例2：查詢所有不小于：查詢所有不小于20歲的學生的姓名及年齡歲的學生的姓

35、名及年齡 SELECT Sname, Sage FROM student WHERE Sage = 20 帶條件的查詢確定范圍帶條件的查詢確定范圍條件：條件：列名列名|表達式表達式 NOT BETWEEN 下限下限 AND 上限上限例：查詢年齡例：查詢年齡2025歲間的學生的姓名及年齡歲間的學生的姓名及年齡 SELECT Sname, Sage FROM student WHERE Sage BETWEEN 20 AND 25等價于：等價于： SELECT Sname, Sage FROM student WHERE Sage = 20 AND Sage = 25帶條件的查詢確定集合帶條件的查

36、詢確定集合l用于查找列值屬于指定集合的元組用于查找列值屬于指定集合的元組l條件格式：條件格式： 列名列名 NOT IN (常量常量1, 常量常量2, , 常量常量n)例：查詢信息系和計算機系學生的姓名和性別例：查詢信息系和計算機系學生的姓名和性別 SELECT Sname, Ssex FROM student WHERE Sdept IN (信息系信息系, 計算機系計算機系)帶條件的查詢相似字符串匹配帶條件的查詢相似字符串匹配用于查詢指定列中與匹配模板相似的元組用于查詢指定列中與匹配模板相似的元組條件：條件： 列名列名 NOT LIKE 匹配字符串模板匹配字符串模板模板中可包含如下四種通配符：

37、模板中可包含如下四種通配符：_ 代表任意單個字代表任意單個字% 代表任意長度的字符串（包括代表任意長度的字符串（包括0個）個） 匹配匹配 中的任意一個字符中的任意一個字符 如如xyz表示匹配表示匹配x、y、z中的任意一個中的任意一個 不匹配不匹配 中任意一個字符中任意一個字符相似字符串匹配（相似字符串匹配（Cont.）例例1：查詢姓：查詢姓“李李”的學生的信息的學生的信息 SELECT * FROM student WHERE Sname LIKE 李李%例例2：查詢姓：查詢姓“王王”、姓、姓“賈賈”的學生的信息的學生的信息 SELECT * FROM student WHERE Sname

38、LIKE 王賈王賈%例例3：查詢學號第：查詢學號第2、3位為位為52的學生的信息的學生的信息 SELECT * FROM student WHERE Sno LIKE _52%帶條件的查詢空值帶條件的查詢空值空值（空值（NULLNULL）：不確定的值：不確定的值比較一個值是否為空，不能用普通比較運算符比較一個值是否為空，不能用普通比較運算符 必須使用必須使用“IS NOT NULL”來判斷來判斷例：查詢無考試成績的學生的學號和相應課程號例：查詢無考試成績的學生的學號和相應課程號 SELECT Sno, Cno FROM SC WHERE Grade IS NULL 帶條件的查詢多重條件帶條件的

39、查詢多重條件多條件查詢：多條件查詢： 使用邏輯運算符使用邏輯運算符AND、OR構成條件表達式構成條件表達式例：查詢計算機系年齡在例：查詢計算機系年齡在20歲以下的歲以下的 學生的學號、姓名和性別學生的學號、姓名和性別 SELECT Sno, Sname, Ssex FROM student WHERE Sdept = 計算機系計算機系 AND Sage 32.5.2 多表連接查詢多表連接查詢多表連接查詢多表連接查詢: 多個相關聯表中獲取信息多個相關聯表中獲取信息l實際上是計算多個表的笛卡爾積（一種關系運算）實際上是計算多個表的笛卡爾積（一種關系運算）連接查詢主要包括連接查詢主要包括內連接、自連

40、接和和外連接1. 內連接內連接使用內連接使用內連接: 兩個表的相關字段滿足連接條件，則從兩個表的相關字段滿足連接條件，則從兩個表中提取數據并拼接成新記錄兩個表中提取數據并拼接成新記錄內連接格式：內連接格式：FROM 表表1 INNER JOIN 表表2 ON 連接條連接條連接條件的一般格式：連接條件的一般格式： 表名表名1.列名列名1 表名表名2.列名列名2 注意：參與比較的兩個列在語義上必須相同注意：參與比較的兩個列在語義上必須相同 多表連接查詢內連接（多表連接查詢內連接（Cont.）例如，兩表根據條件例如，兩表根據條件 number = scale 進行內連接進行內連接numbername

41、1張山張山2李斯李斯scaleAddress1北京北京1上海上海2西安西安2成都成都numbernamescaleAddress1張山張山1北京北京1張山張山1上海上海2李斯李斯2西安西安2李斯李斯2成都成都例例1：查詢每名學生的情況及其選課情況。：查詢每名學生的情況及其選課情況。 SELECT * FROM student JOIN SC ON student.Sno = SC.Sno注意注意: 結果中有重復的結果中有重復的Sno列列例例2：查詢計算機系學生選課情況，：查詢計算機系學生選課情況， 列出學生的姓名、課程號和成績列出學生的姓名、課程號和成績SELECT Sname, Cno, G

42、radeFROM student JOIN SC ON student.Sno = SC.SnoWHERE Sdept = 計算機系計算機系可為表指定別名，以簡化書寫。但一旦指定別名，用可為表指定別名，以簡化書寫。但一旦指定別名，用到該表名的地方都必須使用別名到該表名的地方都必須使用別名例例3：查詢信息系選修：查詢信息系選修VB的課程的學生的成績的課程的學生的成績 列出學號、姓名、課程名和成績列出學號、姓名、課程名和成績 SELECT a.Sno, a.Sname, c.Cname, b.Grade FROM student a JOIN SC b ON a.Sno = b.Sno JOIN

43、course c ON c.Cno = b.CnoWHERE a.Sdept = 信息系信息系 AND c.Cname = VB編程語言編程語言2. 多表連接查詢自連接多表連接查詢自連接自連接自連接: 特殊的內連接，把一個表特殊的內連接，把一個表自己與自己做內連接 即物理上一個表，邏輯上是兩個表即物理上一個表，邏輯上是兩個表使用自連接時必須為自連接的表起使用自連接時必須為自連接的表起兩個別名，使之在邏，使之在邏輯上成為兩個表輯上成為兩個表自連接的格式：自連接的格式： SELECT .列名列名1, .列名列名2 FROM 表名表名 AS 別名別名1 JOIN 表名表名 AS 別名別名2 ON 連

44、接條件連接條件 (其它子句其它子句)2. 多表連接查詢自連接（多表連接查詢自連接（Cont.）例：查詢與李勇在同一個系的學生的姓名和系名例：查詢與李勇在同一個系的學生的姓名和系名 SELECT S2.Sname, S2.Sdept FROM student S1 JOIN student S2 ON S1.Sdept = S2.Sdept WHERE S1.Sname = 李勇李勇 AND S2.Sname != 李勇李勇 3. 多表連接查詢外連接多表連接查詢外連接在內連接中，只有滿足連接條件的元組才在結果中。在內連接中，只有滿足連接條件的元組才在結果中。結果中需要不滿足連接條件的元組時：外連

45、接結果中需要不滿足連接條件的元組時：外連接外連接只限制一個表的數據必須滿足連接條件，而另外連接只限制一個表的數據必須滿足連接條件，而另一個表中的數據可以不滿足連接條件一個表中的數據可以不滿足連接條件外連接格式：外連接格式： FROM 表表1 LEFT|RIGHT OUTER JOIN 表表2 ON 連接條件連接條件LEFT OUTER JOIN稱為左外連接，它限制表稱為左外連接，它限制表2中中的數據必須滿足連接條件，表的數據必須滿足連接條件，表1不限不限RIGHT OUTER JOIN稱為右外連接，它限制表稱為右外連接，它限制表1中中的數據必須滿足連接條件，表的數據必須滿足連接條件，表2不限不

46、限多表連接查詢外連接多表連接查詢外連接例：查詢學生選課情況，包括選課及未選課的學生例：查詢學生選課情況，包括選課及未選課的學生 SELECT a.Sno, Sname, Cno, Grade FROM student a LEFT JOIN SC ON a.Sno = SC.Sno可知：可知：LEFT JOIN：左表：左表(表表1)數據全在結果中數據全在結果中RIGHT JOIN：右表：右表(表表2)數據全在結果中數據全在結果中不滿足條件的那個表的列在結果中的值均為不滿足條件的那個表的列在結果中的值均為NULL2.5.4 子查詢子查詢子查詢：嵌入某個子查詢：嵌入某個SELECT、INSERT、

47、UPDATE 或或DELETE語句中的語句中的SELECT語句語句子查詢可以嵌套多層子查詢可以嵌套多層子查詢允許出現在任何能夠使用表達式的地方，子查詢允許出現在任何能夠使用表達式的地方， 一般位于外層語句的一般位于外層語句的WHERE子句或子句或HAVING子句子句中，與比較運算符或邏輯運算符一起構成查詢條件中，與比較運算符或邏輯運算符一起構成查詢條件嵌套查詢的一般處理方法：嵌套查詢的一般處理方法： 先處理子查詢，其結果作為父查詢的條件先處理子查詢，其結果作為父查詢的條件涉及多表的查詢，可連接查詢，也可嵌套查詢涉及多表的查詢，可連接查詢，也可嵌套查詢 若查詢結果來自一個關系，適用于嵌套查詢若查

48、詢結果來自一個關系，適用于嵌套查詢1. 使用子查詢進行基于集合的測試使用子查詢進行基于集合的測試 子查詢返回的結果是一個集合子查詢返回的結果是一個集合用用IN（或（或NOT IN）操作符比較集合中的數據）操作符比較集合中的數據例例1：查詢與宋江在同一個系的學生：查詢與宋江在同一個系的學生 SELECT Sno, Sname, Sdept FROM student WHERE Sdept IN ( SELECT Sdept FROM student WHERE Sname = 宋江宋江 ) AND Sname != 宋江宋江 例例2：查詢成績小于：查詢成績小于60分的學生及其課程分的學生及其課程

49、 SELECT a.Sno, Sname, Sdept, b.Cname, Grade FROM student a JOIN SC ON a.Sno = SC.Sno JOIN course b ON b.Cno = SC.Cno WHERE a.Sno IN ( SELECT Sno FROM SC WHERE Grade 60) AND Grade ( SELECT AVG(Grade) FROM SC WHERE Cno = c02 ) 3. 使用子查詢進行存在性測試使用子查詢進行存在性測試 先執行外層查詢，再執行子查詢先執行外層查詢，再執行子查詢 子查詢返回的是一個邏輯值（真或假）子

50、查詢返回的是一個邏輯值（真或假） 用用EXISTS（或（或NOT EXISTS）確定外層查詢的數）確定外層查詢的數據是否是滿足要求的結果據是否是滿足要求的結果 子查詢不需指定列名，一般用星號（子查詢不需指定列名，一般用星號（*）代替。）代替。例：查詢選擇了例：查詢選擇了c01課程的學生姓名課程的學生姓名 SELECT Sname FROM student a WHERE EXISTS ( SELECT * FROM SC WHERE Sno = a.Sno AND Cno = c01 ) 小結小結lSQL語言的發展語言的發展l常用數據類型常用數據類型l數據定義語句：表的建立、修改、刪除數據定義

51、語句：表的建立、修改、刪除l數據的增、刪、改操作數據的增、刪、改操作l數據查詢操作數據查詢操作l單表查詢單表查詢n無條件、條件、分組、排序、聚集函數計算無條件、條件、分組、排序、聚集函數計算l多表連接查詢多表連接查詢n內連接、自連接、左外連接、右外連接內連接、自連接、左外連接、右外連接l子查詢子查詢練練 習習 題題1. 利用本章提供的三個表（利用本章提供的三個表（Student、Course和和SC）實現如下操作，寫出相應的實現如下操作，寫出相應的SQL語句，并上機驗證語句，并上機驗證結果結果a)查詢查詢SC表中的全部記錄表中的全部記錄b)查詢計算機系學生的姓名、年齡查詢計算機系學生的姓名、年

52、齡c)查詢成績小于查詢成績小于60分的學生的學號、姓名、課程名和分的學生的學號、姓名、課程名和分數分數d)統計每門課程的選課人數和考試最高分統計每門課程的選課人數和考試最高分e)查詢哪些課程沒有學生選修，列出課程號和課程名查詢哪些課程沒有學生選修，列出課程號和課程名練練 習習 題題2. 寫出創建下表的寫出創建下表的SQL語句，表名為語句，表名為test， 表結構如下：表結構如下： COL1：字符型，主碼：字符型，主碼 COL2： 整型，允許空值整型，允許空值 COL3：字符型，長度為：字符型，長度為10，不允許空值，不允許空值3. 用用ALTER語句修改上題創建的表結構：語句修改上題創建的表結

53、構：a) 增加一個新列增加一個新列COL4，貨幣類型，允許空值，貨幣類型，允許空值b) 將將COL3的長度改為的長度改為203.2 視圖視圖l 內容內容l視圖概念 l定義視圖 l刪除視圖 2.6 視圖視圖視圖視圖: :數據庫基本表中取出的數據組成的邏輯窗口，數據庫基本表中取出的數據組成的邏輯窗口，其它表中導出的其它表中導出的虛表虛表數據庫中只存放視圖的定義，不存放其包含的數據，數據庫中只存放視圖的定義，不存放其包含的數據，這些數據仍存放在原來的基本表中這些數據仍存放在原來的基本表中視圖對應于數據庫結構中的外模式視圖對應于數據庫結構中的外模式視圖可以建立在基本表上，也可以建立在其他的視圖視圖可以

54、建立在基本表上，也可以建立在其他的視圖上，即可以在一個視圖之上再定義視圖上，即可以在一個視圖之上再定義視圖一個基本表上可以建立多個視圖。一個視圖也可以建一個基本表上可以建立多個視圖。一個視圖也可以建立在多個基本表上立在多個基本表上從視圖中查詢與從表中查詢的語句基本相同從視圖中查詢與從表中查詢的語句基本相同視圖的示意圖視圖的示意圖基本表基本表1基本表基本表2視圖視圖視圖有很多優點：視圖有很多優點：簡化操作簡化操作 多表間查詢時，通過視圖實現，將復雜查詢封裝在多表間查詢時，通過視圖實現，將復雜查詢封裝在視圖內部，用戶不必學習復雜的數據庫查詢技術視圖內部，用戶不必學習復雜的數據庫查詢技術 增加了數據

55、安全性增加了數據安全性 授權用戶才能使用視圖；而且只能查詢授權的列授權用戶才能使用視圖；而且只能查詢授權的列分割數據分割數據 通過列名表縱向分割，通過通過列名表縱向分割，通過WHERE橫向分割橫向分割提供邏輯獨立性提供邏輯獨立性 對視圖進行修改時，不必修改使用視圖的應用程序對視圖進行修改時，不必修改使用視圖的應用程序定義視圖的定義視圖的CREATE VIEW語句語句的一般格式：的一般格式： CREATE VIEW 視圖名視圖名 (視圖列名表視圖列名表) AS 子查詢語句子查詢語句其中子查詢可是任意其中子查詢可是任意SELECT語句，但要注意：語句，但要注意：子查詢中通常不含子查詢中通常不含OR

56、DER BY和和DISTINCT子句子句定義視圖時要么指定全部視圖列，要么全部省略。若定義視圖時要么指定全部視圖列，要么全部省略。若省略視圖的屬性列名，則其列名同子查詢列名省略視圖的屬性列名，則其列名同子查詢列名如下三種情況須明確指定組成視圖的所有列名：如下三種情況須明確指定組成視圖的所有列名： 某個目標列是計算函數或列表達式；某個目標列是計算函數或列表達式； 多表連接時選出了幾個同名列作為視圖的字段；多表連接時選出了幾個同名列作為視圖的字段； 需要在視圖中為某個列選用新的更合適的列名。需要在視圖中為某個列選用新的更合適的列名。1. 定義單源表視圖定義單源表視圖單源表視圖：視圖來自一個基本表的

57、部分行、列單源表視圖：視圖來自一個基本表的部分行、列 可查詢和修改數據操作可查詢和修改數據操作例：建立信息系學生的視圖例：建立信息系學生的視圖 CREATE VIEW V_IS_Student AS SELECT Sno, Sname, Sage FROM Student WHERE Sdept = 信息系信息系CREATE VIEW語句的結果僅保存視圖定義語句的結果僅保存視圖定義對視圖查詢時，才按其定義從相應基本表中查詢數對視圖查詢時，才按其定義從相應基本表中查詢數如：如：SELECT * FROM IS_Student2. 定義多源表視圖定義多源表視圖多源表視圖：視圖來自于多個表的數據。多

58、源表視圖：視圖來自于多個表的數據。多源表視圖一般只用于查詢，不用于修改數據多源表視圖一般只用于查詢，不用于修改數據例：建立信息系選了例：建立信息系選了c01號課程的學生的視圖號課程的學生的視圖 CREATE VIEW V_IS_S1(Sno, Sname, Grade) ASSELECT Student.Sno, Sname, Grade FROM Student JOIN SC ON Student.Sno = SC.Sno WHERE Sdept = 信息系信息系 AND SC.Cno = c013. 在已有視圖上定義新視圖在已有視圖上定義新視圖 視圖也可以建立在已存在的視圖上。視圖也可以建立在已存在的視圖上。例：建立信息系選修了例：建立信息系選修了c01號課程且號課程且 成績在成績在90分以上的學生的視圖分以上的學生的視圖 CREATE VIEW V_IS_S2 AS SELECT Sno, Sname, Grade FROM V_IS_S1 WHERE Grade = 904. 定義帶表達式的視圖定義帶表達式的視圖定義視圖時，可根據需要設置一些從基本表中的列定義視圖時，可根據需要設置一些從基本表中的列派生出來屬性列，在這些派生屬性列中保存經過派生出來屬性列，在這些派生屬性列中保存經過計算的值計算的值例：定義一個反