第3章 數(shù)據(jù)庫語言及訪問接口

1、3.1 SQL簡介簡介3.2 SQL的數(shù)據(jù)定義的數(shù)據(jù)定義3.3 SQL的數(shù)據(jù)查詢的數(shù)據(jù)查詢3.4 SQL的數(shù)據(jù)更新的數(shù)據(jù)更新3.5 SQL中的視圖中的視圖3.6 數(shù)據(jù)庫訪問技術(shù)數(shù)據(jù)庫訪問技術(shù)n SQLSQLStructured Query LanguageStructured Query Language（結(jié)構(gòu)化查詢語言），是通（結(jié)構(gòu)化查詢語言），是通用的，功能極強的關(guān)系用的，功能極強的關(guān)系DBDB語言。語言。1. SQL的主要標(biāo)準(zhǔn)的主要標(biāo)準(zhǔn)n SQL-86SQL-86。SQLSQL的第一個標(biāo)準(zhǔn)是的第一個標(biāo)準(zhǔn)是19861986年年1010月由美國國家標(biāo)準(zhǔn)化組織月由美國國家標(biāo)準(zhǔn)化組織(ANSI)

2、(ANSI)公布的。公布的。n SQL-89SQL-89。ANSIANSI以后通過對以后通過對SQL-86SQL-86的不斷修改和完善，于的不斷修改和完善，于19891989年年第二次公布了第二次公布了SQLSQL標(biāo)準(zhǔn)，即標(biāo)準(zhǔn)，即SQL-89SQL-89，該標(biāo)準(zhǔn)增強了完整性的語言特，該標(biāo)準(zhǔn)增強了完整性的語言特征。征。n SQL-92SQL-92(SQL2)(SQL2)。19921992年又公布了年又公布了SQL-92SQL-92標(biāo)準(zhǔn)，該標(biāo)準(zhǔn)增加了支標(biāo)準(zhǔn)，該標(biāo)準(zhǔn)增加了支持對遠(yuǎn)程數(shù)據(jù)庫的訪問，擴充了數(shù)據(jù)類型、操作類型、動態(tài)持對遠(yuǎn)程數(shù)據(jù)庫的訪問，擴充了數(shù)據(jù)類型、操作類型、動態(tài)SQLSQL等等許多新的

3、特征。許多新的特征。n SQL-99SQL-99(SQL3)(SQL3)。完成于。完成于19991999年的年的SQL-99SQL-99修訂本具有更高級的特修訂本具有更高級的特征。引入了支持對象征。引入了支持對象- -關(guān)系關(guān)系DBMSDBMS模型的模型的SQLSQL，擴展了對象、遞歸、，擴展了對象、遞歸、觸發(fā)等許多新的特征，支持用戶自定義函數(shù)、自定義數(shù)據(jù)類型。觸發(fā)等許多新的特征，支持用戶自定義函數(shù)、自定義數(shù)據(jù)類型。2. SQL2. SQL的功能、特點的功能、特點n 功能：功能：數(shù)據(jù)定義數(shù)據(jù)定義數(shù)據(jù)查詢數(shù)據(jù)查詢 數(shù)據(jù)操縱數(shù)據(jù)操縱數(shù)據(jù)控制數(shù)據(jù)控制 特點：特點： 綜合統(tǒng)一綜合統(tǒng)一 SQLSQL語言集

4、數(shù)據(jù)定義、操縱和控制功語言集數(shù)據(jù)定義、操縱和控制功能于一體，語言風(fēng)格統(tǒng)一，可以獨立能于一體，語言風(fēng)格統(tǒng)一，可以獨立完成數(shù)據(jù)庫生命周期中的全部活動，完成數(shù)據(jù)庫生命周期中的全部活動，包括定義關(guān)系模式、錄入數(shù)據(jù)以建立包括定義關(guān)系模式、錄入數(shù)據(jù)以建立數(shù)據(jù)庫、查詢、更新、維護(hù)、數(shù)據(jù)庫數(shù)據(jù)庫、查詢、更新、維護(hù)、數(shù)據(jù)庫重構(gòu)、數(shù)據(jù)庫安全性控制等一系列操重構(gòu)、數(shù)據(jù)庫安全性控制等一系列操作要求。作要求。 高度非過程化高度非過程化 非關(guān)系數(shù)據(jù)模型的數(shù)據(jù)操縱語言是面向過程的語言，用其完成某非關(guān)系數(shù)據(jù)模型的數(shù)據(jù)操縱語言是面向過程的語言，用其完成某項請求，必須指定存取路徑。而用項請求，必須指定存取路徑。而用SQLSQL語

5、言進(jìn)行數(shù)據(jù)操作，用戶只語言進(jìn)行數(shù)據(jù)操作，用戶只需提出需提出“做什么做什么”，而不必指明，而不必指明“怎么做怎么做”，因此用戶無需了解存，因此用戶無需了解存取路徑，存取路徑的選擇以及取路徑，存取路徑的選擇以及SQLSQL語句的操作過程由系統(tǒng)自動完成。語句的操作過程由系統(tǒng)自動完成。這不但大大減輕了用戶負(fù)擔(dān)，而且有利于提高數(shù)據(jù)獨立性這不但大大減輕了用戶負(fù)擔(dān)，而且有利于提高數(shù)據(jù)獨立性。 靈活的使用方式靈活的使用方式nSQLSQL語言既是自含式語言，又是嵌入式語言。語言既是自含式語言，又是嵌入式語言。 n作為自含式語言，它能夠獨立地用于聯(lián)機交互的使用方式，作為自含式語言，它能夠獨立地用于聯(lián)機交互的使用方

6、式，用戶可以在終端鍵盤上直接鍵入用戶可以在終端鍵盤上直接鍵入SQLSQL命令對數(shù)據(jù)庫進(jìn)行操作。命令對數(shù)據(jù)庫進(jìn)行操作。作為嵌入式語言，作為嵌入式語言，SQLSQL語句能夠嵌入到高級語言（例如語句能夠嵌入到高級語言（例如C C、COBOLCOBOL、FORTRANFORTRAN、PL/1PL/1）程序中，供程序員設(shè)計程序時使用。）程序中，供程序員設(shè)計程序時使用。而在兩種不同的使用方式下，而在兩種不同的使用方式下，SQLSQL語言的語法結(jié)構(gòu)基本上是一語言的語法結(jié)構(gòu)基本上是一致的。致的。 簡潔、通用、功能強簡潔、通用、功能強 SQLSQL語言功能極強，設(shè)計巧妙，語言簡潔，完成數(shù)據(jù)定義、數(shù)語言功能極強，

7、設(shè)計巧妙，語言簡潔，完成數(shù)據(jù)定義、數(shù)據(jù)操縱、數(shù)據(jù)控制的核心功能只用了據(jù)操縱、數(shù)據(jù)控制的核心功能只用了9 9個動詞，如表個動詞，如表3-13-1所示。而所示。而且且SQLSQL語言語法簡單，接近英語口語，因此容易學(xué)習(xí)，容易使用。語言語法簡單，接近英語口語，因此容易學(xué)習(xí)，容易使用。 3. SQL3. SQL的基本組成的基本組成 （1 1）SQLSQL數(shù)據(jù)庫層次結(jié)構(gòu)數(shù)據(jù)庫層次結(jié)構(gòu) 存儲文件存儲文件1存儲文件存儲文件2SQL視圖視圖1視圖視圖2基本表基本表1 基本表基本表2基本表基本表3術(shù)語對照：術(shù)語對照：一般關(guān)系模型一般關(guān)系模型 SQLn 外模式外模式-視圖（視圖（VIEWVIEW) )n 模式模式

8、-基本表基本表( (TABLETABLE) )n 內(nèi)模式內(nèi)模式-存儲文件存儲文件- -索引索引n 元組元組-行行( (ROWROW) )n 屬性屬性-列列( (COLUMNCOLUMN) )n基本表是本身獨立存在的表，在基本表是本身獨立存在的表，在SQLSQL中一個關(guān)系就對應(yīng)一個表。中一個關(guān)系就對應(yīng)一個表。一些基本表對應(yīng)一個存儲文件，一個表可以帶若干索引，索引一些基本表對應(yīng)一個存儲文件，一個表可以帶若干索引，索引也存放在存儲文件中。也存放在存儲文件中。 n存儲文件的邏輯結(jié)構(gòu)組成了關(guān)系數(shù)據(jù)庫的內(nèi)模式。存儲文件的存儲文件的邏輯結(jié)構(gòu)組成了關(guān)系數(shù)據(jù)庫的內(nèi)模式。存儲文件的物理文件結(jié)構(gòu)是任意的。物理文件結(jié)

9、構(gòu)是任意的。 n視圖是從基本表或其他視圖中導(dǎo)出的表，它本身不獨立存儲在視圖是從基本表或其他視圖中導(dǎo)出的表，它本身不獨立存儲在數(shù)據(jù)庫中，也就是說數(shù)據(jù)庫中只存放視圖的定義而不存放視圖數(shù)據(jù)庫中，也就是說數(shù)據(jù)庫中只存放視圖的定義而不存放視圖對應(yīng)的數(shù)據(jù)，這些數(shù)據(jù)仍存放在導(dǎo)出視圖的基本表中，因此視對應(yīng)的數(shù)據(jù)，這些數(shù)據(jù)仍存放在導(dǎo)出視圖的基本表中，因此視圖是一個虛表。圖是一個虛表。 用戶可以用用戶可以用SQLSQL語言對視圖和基本表進(jìn)行查詢。語言對視圖和基本表進(jìn)行查詢。在用戶眼中，視圖和基本表都是關(guān)系，而存儲文件對用戶是透在用戶眼中，視圖和基本表都是關(guān)系，而存儲文件對用戶是透明的。明的。 （2 2）SQLSQ

10、L語言的組成語言的組成 n數(shù)據(jù)定義語言（數(shù)據(jù)定義語言（DDLDDL）n數(shù)據(jù)操縱語言（數(shù)據(jù)操縱語言（DMLDML） n數(shù)據(jù)控制語言（數(shù)據(jù)控制語言（DCLDCL） n嵌入與會話規(guī)則嵌入與會話規(guī)則 （3 3）SQLSQL的語句類型的語句類型 nSQLSQL模式語句模式語句 nSQLSQL數(shù)據(jù)語句數(shù)據(jù)語句 nSQLSQL事務(wù)與控制語句事務(wù)與控制語句 nSQLSQL連接、會話及診斷語句連接、會話及診斷語句 4 4、SQLSQL的數(shù)據(jù)類型的數(shù)據(jù)類型 預(yù)定義數(shù)據(jù)類型預(yù)定義數(shù)據(jù)類型 int int、realreal、doubledouble、charchar、varcharvarchar、datedate、b

11、ooleanboolean等等 構(gòu)造數(shù)據(jù)類型構(gòu)造數(shù)據(jù)類型 array array、refref、rowrow等。等。 用戶定義數(shù)據(jù)類型（用戶定義數(shù)據(jù)類型（UDT，User Defined Type）5 5、SQLSQL環(huán)境環(huán)境（1 1）SQLSQL模式與目錄模式與目錄 SQL模式：模式：基本表、視圖、角色等的集合?；颈怼⒁晥D、角色等的集合。 好處：好處：允許在不同的允許在不同的SQLSQL模式中出現(xiàn)同名的基表名或模式中出現(xiàn)同名的基表名或 視圖名。視圖名。 目錄：目錄：SQLSQL環(huán)境中所有模式的集合。環(huán)境中所有模式的集合。 定位基表的方式：定位基表的方式： （2 2）SQLSQL環(huán)境環(huán)境 n

12、 設(shè)置默認(rèn)的目錄和模式設(shè)置默認(rèn)的目錄和模式 n 設(shè)置用戶身份設(shè)置用戶身份 1. SQLSQL模式的定義與撤銷模式的定義與撤銷（1 1）SQL模式的定義模式的定義 （P.68P.68） CREATE SCHEMA CREATE SCHEMA 模式名模式名 AUTHORIZATION AUTHORIZATION 用戶名用戶名 CREATE DOMAINCREATE DOMAIN子句子句| |CREATE TABLECREATE TABLE子句子句| |CREATE VIEW CREATE VIEW | | n其中：其中：表示其中的成分為任選項。：表示其中的成分為任選項。 ：表示其中的成分由用戶具體

13、給定。：表示其中的成分由用戶具體給定。 | |： 表示其中并列的成分只能擇一。表示其中并列的成分只能擇一。例：例：CREATE SCHEMA Teaching_db AUTHORIZATION HangCREATE SCHEMA Teaching_db AUTHORIZATION Hang；（2 2）數(shù)據(jù)庫模式的刪除）數(shù)據(jù)庫模式的刪除 DROP SCHEMA DROP SCHEMA 模式名模式名 CASCADE | RESTRICTCASCADE | RESTRICT CASCADECASCADE：級聯(lián)式級聯(lián)式 RESTRICTRESTRICT：約束式（受限式）約束式（受限式） 2. 2. 表

14、的建立和刪除表的建立和刪除 （1）表的建立表的建立 命令格式：命令格式： CREATE TABLE 模式名模式名. ( ，)；n例例：CREATE TABLE Student ( sno CHAR(5) NOT NULL UNIQUE， sname CHAR(8) NOT NULL ， sex CHAR(2)， age INT ， dept CHAR(20) )；主主鍵鍵n完整性約束條件涉及到該表的多個屬性列，則完整性約束條件涉及到該表的多個屬性列，則必須定義在表級上，否則既可定義在列級，也必須定義在表級上，否則既可定義在列級，也可以定義在表級?？梢远x在表級。注意：注意： 例：例：定義學(xué)生定

15、義學(xué)生_選課數(shù)據(jù)庫中的三個表結(jié)構(gòu)，并指定相應(yīng)的數(shù)據(jù)完選課數(shù)據(jù)庫中的三個表結(jié)構(gòu)，并指定相應(yīng)的數(shù)據(jù)完整性約束條件。整性約束條件。分析分析外外鍵鍵：（sno,cno） 主主鍵鍵：sno姓名：非空姓名：非空性別：男、女兩值性別：男、女兩值Student表：表：Course表：表：主主鍵鍵：cno課程名：非空課程名：非空外外鍵鍵：pcnoSC表：表：主主鍵鍵：(sno,cno)成績：成績：0100CREATE TABLE StudentCREATE TABLE Student ( ( sno CHAR (5),sno CHAR (5), sname CHAR (8) NOT NULL, sname CH

16、AR (8) NOT NULL, sex CHAR (2), sex CHAR (2), age SMALLINT, age SMALLINT, dept CHAR (20), dept CHAR (20), PRIMARY KEY(sno), PRIMARY KEY(sno), CHECK sex IN ( CHECK sex IN (男男,女女) ) ) ); ;列級完整性約束條件列級完整性約束條件實體完整性約束條件實體完整性約束條件用戶自定義完整性約束條件用戶自定義完整性約束條件CREATE TABLE CourseCREATE TABLE Course ( ( cno CHAR (4)

17、,cno CHAR (4), cname CHAR (10) NOT NULL, cname CHAR (10) NOT NULL, pcno CHAR (4), pcno CHAR (4), credit SMALLINT, credit SMALLINT, PRIMARY KEY (cno), PRIMARY KEY (cno), FOREIGN KEY (pcno) REFERENCES Course(cno) FOREIGN KEY (pcno) REFERENCES Course(cno) ) ); ;參照完整性約束條件參照完整性約束條件CREATE TABLE SCCREATE T

18、ABLE SC ( ( sno CHAR (5),sno CHAR (5), cno CHAR (4) , cno CHAR (4) , grade SMALLINT, grade SMALLINT, PRIMARY KEY (sno,cno), PRIMARY KEY (sno,cno), FOREIGN KEY (sno)REFERENCES Student(sno), FOREIGN KEY (sno)REFERENCES Student(sno), FOREIGN KEY (cno)REFERENCES Course(cno), FOREIGN KEY (cno)REFERENCES

19、Course(cno), CHECK CHECK ( (grade IS NULL) OR (grade IS NULL) OR (grade BETWEEN 0 AND 100) (grade BETWEEN 0 AND 100) ) ) ); ;（2 2）表的刪除）表的刪除 格式：格式：DROP TABLE DROP TABLE CASCADE | RESTRICT CASCADE | RESTRICTn基本表定義一旦刪除，表中的數(shù)據(jù)、在此表上建立的視基本表定義一旦刪除，表中的數(shù)據(jù)、在此表上建立的視圖 、 索 引 、 觸 發(fā) 器 、 斷 言 都 將 自 動 被 刪 除 掉 。圖 、 索 引

20、 、 觸 發(fā) 器 、 斷 言 都 將 自 動 被 刪 除 掉 。RESTRICT確保只有不具有相關(guān)對象的表才能被撤銷。確保只有不具有相關(guān)對象的表才能被撤銷。例：例：DROP TABLE St-quit CASCADEDROP TABLE St-quit CASCADE； 3.3.表的擴充和修改表的擴充和修改n一般格式為：一般格式為： ALTER TABLE ALTER TABLE ADD ADD 完整性約完整性約束束DROPDROP MODIFY MODIFY ;n其中其中 指定需要修改的基本表，指定需要修改的基本表，ADDADD子句用于增加新列和子句用于增加新列和新的完整性約束條件，新的完整

21、性約束條件，DROPDROP子句用于刪除指定的完整性約束子句用于刪除指定的完整性約束條件，條件，MODIFYMODIFY子句用于修改原有的列定義。子句用于修改原有的列定義。（1 1）在現(xiàn)存表中增加新列）在現(xiàn)存表中增加新列 格式：格式：ALTER TABLE ALTER TABLE ADD ( ADD ( ， ) ) 例：例：ALTER TABLE StudentALTER TABLE Student ADD (place CHAR(20) ADD (place CHAR(20)，addr CHAR(20)addr CHAR(20)； （2 2）刪除已存在的某個列）刪除已存在的某個列 格式：格式

22、：ALTER TABLE ALTER TABLE DROP DROP CASCADE | RESTRICT CASCADE | RESTRICT 例：例：ALTER TABLE StudentALTER TABLE Student DROP addr DROP addr；（3 3）修改原有列的類型）修改原有列的類型 格式：格式：ALTER TABLE ALTER TABLE MODIFY MODIFY ；例：例：ALTER TABLE StudentALTER TABLE Student MODIFY place CHAR(8) MODIFY place CHAR(8)； （4 4）補充定義主

23、）補充定義主鍵鍵 格式：格式：ALTER TABLE ALTER TABLE ADD PRIMARY KEY (ADD PRIMARY KEY ( ) )（5 5）刪除主）刪除主鍵鍵 格式：格式：ALTER TABLE ALTER TABLE DROP PRIMARY KEYDROP PRIMARY KEY4.4.域定義域定義 域定義用于建立用戶自定義的數(shù)據(jù)類型。域定義用于建立用戶自定義的數(shù)據(jù)類型。n命令格式：命令格式： CREATE DOMAIN CREATE DOMAIN AS AS DEFAULT DEFAULT 例例3.3.3 3 建立一個建筑公司的數(shù)據(jù)庫模式，由建立一個建筑公司的數(shù)據(jù)

24、庫模式，由3 3個表組成。個表組成。其中：其中： CRETE DOMAIN ITEM_ID NUMBERIC(4) DEFAULT 0CRETE DOMAIN ITEM_ID NUMBERIC(4) DEFAULT 0 CHECK (VALUE IS NOT NULL) CHECK (VALUE IS NOT NULL)5.5.索引的建立與刪除索引的建立與刪除 n 建立索引的目的：建立索引的目的：基本表上建立一個或多個索引，以提供多種存基本表上建立一個或多個索引，以提供多種存取路徑，加快查找速度。取路徑，加快查找速度。n 命令格式：命令格式： CREATE UNIQUE CLUSTER IND

25、EX ON ( ，)； 次序：次序： 升序（升序（ASC，缺?。?，缺?。?降序降序 （DESC） UNIQUE: 每一個索引值只對應(yīng)惟一的數(shù)據(jù)記錄。每一個索引值只對應(yīng)惟一的數(shù)據(jù)記錄。 CLUSTER: 建立聚簇索引，即索引項的順序與表中記錄的物理順建立聚簇索引，即索引項的順序與表中記錄的物理順序一致。序一致。 n注意：注意：在一個基本表上最多只能建立一個聚簇索引。在一個基本表上最多只能建立一個聚簇索引。n經(jīng)常更新的列不宜建立聚簇索引。經(jīng)常更新的列不宜建立聚簇索引。n所建索引放何處？所建索引放何處？n例例: 為為Student表按學(xué)號升序建惟一聚簇索引。表按學(xué)號升序建惟一聚簇索引。 為為SC表按

26、學(xué)號表按學(xué)號升序升序和課程號和課程號降序降序建惟一索引。建惟一索引。 CREATE UNIQUE CLUSTER INDEX Stno ON Student(Sno)； CREATE UNIQUE INDEX Scno ON SC(Sno ，Cno DESC)；n刪除索引一般格式為：刪除索引一般格式為： DROP INDEX；n例：例： DROP INDEX Stno ；n刪除索引時，系統(tǒng)會同時從刪除索引時，系統(tǒng)會同時從數(shù)據(jù)字典數(shù)據(jù)字典中刪去有關(guān)該索引的描述。中刪去有關(guān)該索引的描述。n基本形式：基本形式：SELECT FROM WHERE ;n查詢語句塊查詢語句塊含義含義: : 從表從表 (視

27、圖視圖) )R中找出滿足條件中找出滿足條件F的行，的行， 再從中選出目標(biāo)屬性再從中選出目標(biāo)屬性A的的值形成結(jié)果表。值形成結(jié)果表。查詢目標(biāo)查詢目標(biāo):為為屬性名表屬性名表或或表達(dá)式表達(dá)式或或* *數(shù)據(jù)來源數(shù)據(jù)來源:表表或或視圖視圖選擇行選擇行( (元組元組) )的條件的條件關(guān)系代數(shù)關(guān)系代數(shù): : A (F (R)n 例例:已知已知R(no,name,sum)求總分大于求總分大于600的學(xué)生號和姓名。的學(xué)生號和姓名。目標(biāo)目標(biāo) A條件條件 F 來源來源 RSELECT no, nameFROM RWHERE sum600;n 解解: no，name(sum600sum600(R)n學(xué)生學(xué)生-課程數(shù)據(jù)庫

28、課程數(shù)據(jù)庫: Student(sno，sname，sex，age，dept) Course(cno，cname，credit ，pcno) SC(sno，cno，grade)3.3.1 單表查詢單表查詢 一、選擇表中的列一、選擇表中的列n例例: 查詢所有學(xué)生的姓名、學(xué)號、所在院系。查詢所有學(xué)生的姓名、學(xué)號、所在院系。 SELECT sname，sno，dept FROM Student WHERE .T. ;sname sno dept王蕭虎王蕭虎 200101 信息院信息院來源來源R條件條件F 目標(biāo)目標(biāo)An例例：查全體學(xué)生的姓名及其出生年份。：查全體學(xué)生的姓名及其出生年份。 SELECT s

29、name，2015-age FROM Student ；來源來源R條件條件F目標(biāo)目標(biāo)ASname 2015-age王蕭虎王蕭虎 1988AS Birth別名別名 Birth二、選擇表中的行二、選擇表中的行（1 1）比較比較 例例: :查考試成績不及格的學(xué)號。查考試成績不及格的學(xué)號。SELECT SnoFROM SCWHERE grade60 ；DISTINCT去掉重復(fù)元組去掉重復(fù)元組表達(dá)式表達(dá)式 (2)(2)確定范圍確定范圍 (BETWEEN AND ) 例例3.53.5 查詢選查詢選004號課程且成績在號課程且成績在85-95的學(xué)生號。的學(xué)生號。 SELECT sno FROM SC WHE

30、RE cno=004 AND grade BETWEEN 8 85 5 AND 95； 其他表示？其他表示？ (3)(3)字符匹配字符匹配-近似查詢，模糊查詢近似查詢，模糊查詢 格式格式1：NOT LIKE 含義：含義：查找指定的屬性列值與查找指定的屬性列值與相匹配的元組。相匹配的元組。n其中匹配串可含：其中匹配串可含： ：代表任意長度：代表任意長度(可為可為0)的字符串。的字符串。 _：代表任意單個字符。：代表任意單個字符。 例例 查所有姓劉或姓王的學(xué)生姓名、學(xué)號和性別。查所有姓劉或姓王的學(xué)生姓名、學(xué)號和性別。 SELECT sname，sno，sex FROM Student WHERE

31、sname LIKE 劉劉 OR sname LIKE 王王 ；n? 查詢所有不姓劉或不姓王的學(xué)生姓名、學(xué)號和性別。查詢所有不姓劉或不姓王的學(xué)生姓名、學(xué)號和性別。NOTNOT 例例 查姓查姓“歐陽歐陽”且全名為三個漢字的學(xué)生的姓名且全名為三個漢字的學(xué)生的姓名。 SELECT Sname SELECT Sname FROM Student FROM Student WHERE Sname LIKE WHERE Sname LIKE 歐陽歐陽_ _ _; ; n注意，由于一個漢字占兩個字符的位置，所以匹配串歐陽后面需注意，由于一個漢字占兩個字符的位置，所以匹配串歐陽后面需要跟個要跟個_。 例例 查

32、名字中第二字為查名字中第二字為“陽陽”字的學(xué)生的姓名和學(xué)號字的學(xué)生的姓名和學(xué)號。 SELECT Sname, Sno SELECT Sname, Sno FROM Student FROM Student WHERE Sname LIKE WHERE Sname LIKE _ _ _陽陽%; %; 格式格式2： LIKE ESCAPE n若要查的串本身含或若要查的串本身含或_ _ ，用，用ESCAPE ESCAPE 對通配符對通配符進(jìn)行轉(zhuǎn)義。進(jìn)行轉(zhuǎn)義。ESCAPE ESCAPE 短語表示短語表示 為換碼字符，這樣匹配串中為換碼字符，這樣匹配串中緊跟在緊跟在 后面的字符后面的字符”_”_”不再具

33、有通配符的含義，而是取其本身不再具有通配符的含義，而是取其本身含義，被轉(zhuǎn)義為普通的含義，被轉(zhuǎn)義為普通的”_”_”字符。字符。 例例 查以查以“數(shù)據(jù)數(shù)據(jù)_”_”開頭，且倒數(shù)第開頭，且倒數(shù)第2 2個漢字為個漢字為 ” ”原原”的課程情況。的課程情況。 SELECT SELECT * * FROM Course FROM Course WHERE cname LIKE WHERE cname LIKE 數(shù)據(jù)數(shù)據(jù) _ _ 原原 _ _ _ ESCAPE ESCAPE ； 若要查以若要查以 “ “ 數(shù)據(jù)數(shù)據(jù) _ _ ” ”開頭的呢？開頭的呢？ LIKE LIKE 數(shù)據(jù)數(shù)據(jù) * * _ _ ESCAPE

34、ESCAPE * *； (4)(4)確定集合確定集合 (IN) 例：例：查詢信息院、數(shù)學(xué)系和計算機學(xué)院學(xué)生的姓名和性別。查詢信息院、數(shù)學(xué)系和計算機學(xué)院學(xué)生的姓名和性別。 SELECT sname，sex FROM Student WHERE dept IN (信息院信息院，數(shù)學(xué)系數(shù)學(xué)系，計算機計算機學(xué)院學(xué)院); ? 查詢不是這三個系的學(xué)生的姓名和性別。查詢不是這三個系的學(xué)生的姓名和性別。 NOT n一般形式：一般形式：SELECT FROM WHERE 查詢目標(biāo)查詢目標(biāo): 為為屬性名表屬性名表或或表達(dá)式表達(dá)式或或* *數(shù)據(jù)來源數(shù)據(jù)來源: 表表或或視圖視圖選擇行選擇行( (元組元組) )的條件的

35、條件將選擇結(jié)果按將選擇結(jié)果按 的值進(jìn)行分組的值進(jìn)行分組選擇滿足條件的小組選擇滿足條件的小組按按 排序查詢結(jié)果排序查詢結(jié)果目標(biāo)目標(biāo)A來源來源R條件條件F分組分組 選組選組 排序排序GROUP BY HAVING ORDER BY ASC|DESC； 三、對查詢結(jié)果排序三、對查詢結(jié)果排序 例例3 3.5.5 查詢?nèi)w男學(xué)生的學(xué)號、姓名，查詢?nèi)w男學(xué)生的學(xué)號、姓名， 結(jié)果按所在的系升序排列，結(jié)果按所在的系升序排列， 同一系中的學(xué)生按年齡降序排列。同一系中的學(xué)生按年齡降序排列。 來源來源 R 條件條件 F目標(biāo)目標(biāo) AStudent表排序二排序二排序一排序一 SELECT sno，sname FROM

36、Student WHERE sex=男男 ORDER BY dept，age DESC； 四、四、表達(dá)式與函數(shù)的使用表達(dá)式與函數(shù)的使用 1、使用集函數(shù)使用集函數(shù) COUNT(COUNT(DISTINCT | ALLDISTINCT | ALL * * ) ) 統(tǒng)計元組個數(shù)統(tǒng)計元組個數(shù) COUNT( COUNT(DISTINCT | ALLDISTINCT | ALL ) ) 統(tǒng)計一列中值的個數(shù)統(tǒng)計一列中值的個數(shù) SUM( SUM(DISTINCT | ALLDISTINCT | ALL ) ) 計算一數(shù)值型列值的總和計算一數(shù)值型列值的總和 AVG( AVG(DISTINCT | ALLDIST

37、INCT | ALL ) ) 計算一數(shù)值型列值的平均值計算一數(shù)值型列值的平均值 MAX( MAX(DISTINCT | ALLDISTINCT | ALL ) ) 求一列值的最大值求一列值的最大值 MIN( MIN(DISTINCT | ALLDISTINCT | ALL ) ) 求一列值的最小值求一列值的最小值缺省值缺省值無重復(fù)值無重復(fù)值例例: 查詢女學(xué)生的總?cè)藬?shù)和平均年齡。查詢女學(xué)生的總?cè)藬?shù)和平均年齡。 SELECT COUNT(SELECT COUNT(* *) AS ) AS 女學(xué)生人數(shù)女學(xué)生人數(shù),AVG(age) AS ,AVG(age) AS 平均年齡平均年齡 FROM Stude

38、nt FROM Student WHERE sex= WHERE sex=女女; 例例3.3.6 6 查詢選修了課程的學(xué)生人數(shù)。查詢選修了課程的學(xué)生人數(shù)。 SELECT COUNT(SELECT COUNT(DISTINCTDISTINCT sno) AS sno) AS 選課學(xué)生人數(shù)選課學(xué)生人數(shù) FROM SC;FROM SC; 例例3.3.6 6 查詢選修查詢選修001001號課程并及格的學(xué)生的最高分?jǐn)?shù)、最低分號課程并及格的學(xué)生的最高分?jǐn)?shù)、最低分及總分及總分。 SELECT MAX(grade) AS SELECT MAX(grade) AS 最高分最高分,MIN(grade) AS ,M

39、IN(grade) AS 最低分最低分,SUM(grade) AS ,SUM(grade) AS 總分總分 FROM SC FROM SC WHERE cno=001 and grade=60; WHERE cno=001 and grade=60;2 2、使用表達(dá)式使用表達(dá)式 例例 查詢各課程的學(xué)時數(shù)。查詢各課程的學(xué)時數(shù)。 SELECT cname AS course_name,SELECT cname AS course_name,creditcredit* *1818 AS course_time AS course_time FROM course; FROM course; 3、對查

40、詢結(jié)果分組對查詢結(jié)果分組（GROUP BY GROUP BY 子句子句）n將查詢結(jié)果表按某一將查詢結(jié)果表按某一( (多多) )列值分組，值相等的為一組。列值分組，值相等的為一組。n目的：細(xì)化集函數(shù)的作用對象。目的：細(xì)化集函數(shù)的作用對象。如果未對查詢結(jié)果分組，集函數(shù)如果未對查詢結(jié)果分組，集函數(shù)將作用于整個查詢結(jié)果，即整個查詢結(jié)果只有一個函數(shù)值。否則，將作用于整個查詢結(jié)果，即整個查詢結(jié)果只有一個函數(shù)值。否則，集函數(shù)將作用于每一個組，即每一組都有一個函數(shù)值。集函數(shù)將作用于每一個組，即每一組都有一個函數(shù)值。 例例3.3.6 6 求每個學(xué)生求每個學(xué)生所選課程所選課程的平均成績。的平均成績。 SELECT

41、 sno SELECT sno，AVG( grade) AS AVG( grade) AS 平均成績平均成績 FROM SC FROM SC GROUP BY sno GROUP BY sno HAVINGHAVING短語：在短語：在各組各組中選擇滿足條件的中選擇滿足條件的小組小組。 WHEREWHERE子句子句 ：在：在表表中選擇滿足條件的中選擇滿足條件的元組元組。WHERE ?并將其超過并將其超過8888分的輸出。分的輸出。HAVING AVG( grade) 88HAVING AVG( grade) 88；分組情況分組情況snosnocnocnogradegrade000101c1900

42、00101c285000101c380010101c185010101c275020101c380分組情況及查詢結(jié)果示意圖分組情況及查詢結(jié)果示意圖3 3組組1 1組組2 2組組 sno AVG(AVG(grade) 000101 85 010101 80 例例: :求每個學(xué)生的平均求每個學(xué)生的平均 成績。成績。 SELECT sno,AVG(grade)SELECT sno,AVG(grade) FROM SC FROM SC GROUP BY sno GROUP BY sno ；求每門課程的求每門課程的平均成績？平均成績？集函數(shù)在成組之前不計算，因此集函數(shù)在成組之前不計算，因此不能用于不能用

43、于WHERE子句子句, 一般將簡一般將簡單條件寫入單條件寫入WHERE。HAVING子句的條件運算數(shù)之一子句的條件運算數(shù)之一是一個集函數(shù)。是一個集函數(shù)。若若HAVING子句無前導(dǎo)子句無前導(dǎo)GROUPBY，選擇清單中不能含有非集函數(shù)列。選擇清單中不能含有非集函數(shù)列。【練習(xí)】【練習(xí)】查詢至少選修查詢至少選修4門課程的學(xué)生學(xué)號。門課程的學(xué)生學(xué)號。 SELECT sno FROM SC GROUP BY sno HAVING COUNT(*)=4 例例3.3.6 6 求學(xué)生關(guān)系中求學(xué)生關(guān)系中湖北籍男湖北籍男生的每一年齡組（生的每一年齡組（不不少于少于3 30 0人）人）共共有多少有多少人人，要求查詢結(jié)

44、果按人數(shù)升序排列，要求查詢結(jié)果按人數(shù)升序排列，人數(shù)相同時按年齡降序排列。人數(shù)相同時按年齡降序排列。 SELECT age,COUNT(sno) numberSELECT age,COUNT(sno) number FROM Student FROM Student WHERE sex= WHERE sex=男男 AND place= AND place=湖北湖北 GROUP BY age GROUP BY age HAVING number HAVING number3 30 0 ORDER BY number,age DESC; ORDER BY number,age DESC;3.3.2

45、多表查詢多表查詢 1. 子查詢（嵌套查詢）子查詢（嵌套查詢）n查詢塊：查詢塊：nSELECT nFROM nWHERE n子查詢（嵌套查詢）：子查詢（嵌套查詢）： 一個查詢塊嵌套在另一查詢塊中作為條件的查詢。一個查詢塊嵌套在另一查詢塊中作為條件的查詢。n上層的查詢塊又稱為外層查詢或父查詢或主查詢，下層上層的查詢塊又稱為外層查詢或父查詢或主查詢，下層查詢塊又稱為內(nèi)層查詢或子查詢。查詢塊又稱為內(nèi)層查詢或子查詢。 n子查詢一般跟在子查詢一般跟在ININ、SOMESOME（ANYANY）、）、ALLALL和和EXISTEXIST等謂詞等謂詞后面。后面。 nSQLSQL語言允許多層嵌套查詢。語言允許多層

46、嵌套查詢。n嵌套查詢的求解方法是嵌套查詢的求解方法是由里向外由里向外處理。即每個子查詢在處理。即每個子查詢在其上一級查詢處理之前求解，子查詢的結(jié)果用于建立其其上一級查詢處理之前求解，子查詢的結(jié)果用于建立其父查詢的查找條件。父查詢的查找條件。 n嵌套查詢使得可以用一系列簡單查詢構(gòu)成復(fù)雜的查詢，嵌套查詢使得可以用一系列簡單查詢構(gòu)成復(fù)雜的查詢，從而明顯地增強了從而明顯地增強了SQLSQL的查詢能力。以層層嵌套的方式的查詢能力。以層層嵌套的方式來構(gòu)造程序正是來構(gòu)造程序正是 SQL(Structurred Query Language)SQL(Structurred Query Language)中中“

47、結(jié)構(gòu)化結(jié)構(gòu)化”的含義所在。的含義所在。(1) 帶有帶有IN謂詞的子查詢謂詞的子查詢 帶有帶有IN謂詞的子查詢是指父查詢與子查詢之間用謂詞的子查詢是指父查詢與子查詢之間用IN進(jìn)行連接，進(jìn)行連接，判斷某個屬性列值是否在子查詢的結(jié)果中。判斷某個屬性列值是否在子查詢的結(jié)果中。例：查詢與例：查詢與“劉晨劉晨”在同一個系學(xué)習(xí)的學(xué)生的學(xué)號、姓名、系在同一個系學(xué)習(xí)的學(xué)生的學(xué)號、姓名、系 確定確定“劉晨劉晨”所在系名所在系名 SELECT Sdept FROM StudentWHERE Sname=劉晨劉晨; 查找所有在查找所有在IS系學(xué)習(xí)的學(xué)生。系學(xué)習(xí)的學(xué)生。SELECT Sno, Sname, Sdept

48、FROM Student WHERE Sdept=IS; 子查詢實現(xiàn)：將第一步查子查詢實現(xiàn)：將第一步查詢嵌入到第二步查詢中，詢嵌入到第二步查詢中，用以構(gòu)造第二步查詢的條用以構(gòu)造第二步查詢的條件。件。 WHERE Sdept IN ( S E L E C T ) 例例 查詢選修了數(shù)據(jù)庫課程的學(xué)生號、查詢選修了數(shù)據(jù)庫課程的學(xué)生號、成績。成績。 SELECT sno， grade FROM SC WHERE cno IN (SELECT cno FROM Course WHERE cname=數(shù)據(jù)庫數(shù)據(jù)庫)； （2 2）帶有比較運算符的子查詢）帶有比較運算符的子查詢 例例3 3.7.7 找出年齡超過

49、平均找出年齡超過平均年齡的學(xué)生姓名。年齡的學(xué)生姓名。 SELECT sname FROM Student WHERE age (SELECT AVG(age) FROM Student)；(3)(3)帶帶SOME(ANY)SOME(ANY)、ALLALL謂詞的子查詢謂詞的子查詢nSOME 大于子查詢結(jié)果中大于子查詢結(jié)果中的某個值的某個值nALL 小于子查詢結(jié)果中小于子查詢結(jié)果中的所有值的所有值注意：注意：SOME、ALL必須與關(guān)系比較符必須與關(guān)系比較符同時使用。同時使用。n例：例：查詢其他系比信息院某學(xué)生年查詢其他系比信息院某學(xué)生年齡小的學(xué)生名、年齡。齡小的學(xué)生名、年齡。 SELECT sna

50、meSELECT sname，ageage FROM Student FROM Student WHERE age SOME WHERE age SOME ( SELECT age( SELECT age FROM Student FROM Student WHERE dept= WHERE dept=信息院信息院) ) AND deptAND dept信息信息院院 ；( SELECT MAX(age) = ALL ( SELECT AVG(grade) FROM SC GROUP BY sno)；姓名姓名 ？集合集合一個一個 例例 找出有一門選課成績在找出有一門選課成績在9090分以上的學(xué)生

51、姓名。分以上的學(xué)生姓名。 SELECT sname FROM Student WHERE sno IN ( SELECT DISTINCT sno FROM SC WHERE grade=90)；=SOME SELECT Student. sno，sname，sex，age， dept，cno，grade FROM Student，SC WHERE Student. sno=SC. sno ； 2. 條件連接查詢條件連接查詢 連接條件連接條件的一般格式為：的一般格式為： . . 當(dāng)連接運算符為當(dāng)連接運算符為=時，稱為時，稱為等值連接等值連接。 例例: 查詢每個學(xué)生的情況及其選課成績。查詢每個學(xué)

52、生的情況及其選課成績。 例例 找出籍貫為湖北或河北，選課成績?yōu)檎页黾灋楹被蚝颖?，選課成績?yōu)?090分以上的學(xué)生分以上的學(xué)生的姓名、課號和成績。的姓名、課號和成績。 SELECT sname，cno，grade FROM Student，SC WHERE Student.sno=SC.sno AND place IN (湖北湖北，河北河北) AND grade90； 例例3.3.8 8 查詢選修了數(shù)據(jù)庫的學(xué)生號、成績查詢選修了數(shù)據(jù)庫的學(xué)生號、成績學(xué)分學(xué)分 ？SELECT sno， gradeFROM SC，CourseWHERE SC. cno = Course . cno AND cnam

53、e=DB ；, credit注意：注意：子查詢不能用子查詢不能用ORDER BYORDER BY子句子句為什么？為什么？不相關(guān)子查詢不相關(guān)子查詢 ( SELECT cno FROM Course WHERE cname=DB)； SELECT sno， grade FROM SC WHERE cno IN =方法方法1 1：方法方法2 2： 例例3.83.8 按平均成績的降序給出所有課程都及格的學(xué)生按平均成績的降序給出所有課程都及格的學(xué)生（號、名）及其平均成績，其中成績統(tǒng)計時不包括（號、名）及其平均成績，其中成績統(tǒng)計時不包括008008號考查課。號考查課。 SELECT Student.sno

54、 SELECT Student.sno，snamesname，AVG(grade) AS avg_gAVG(grade) AS avg_g FROM Student FROM Student，SCSC WHERE Student.sno=SC.sno AND cno008 WHERE Student.sno=SC.sno AND cno008 GROUP BY GROUP BY Student.Student.snosno,sname,sname HAVING MIN(grade)=60 HAVING MIN(grade)=60 ORDER BY avg_g DESC ORDER BY avg

55、_g DESC ； n自身連接查詢自身連接查詢 例例3.3.8 8 找出年齡比找出年齡比“王迎王迎”同學(xué)大的同學(xué)的姓名及年同學(xué)大的同學(xué)的姓名及年齡。齡。SELECT s1.sname，s1.ageFROM Student AS s1，Student AS s2WHERE s1.age s2.age AND s2.sname =王迎王迎； 其他方法？其他方法？ n練習(xí)練習(xí)：查每門課的查每門課的間接間接先修課先修課(即先修課的先修課即先修課的先修課)。d2d1003003004001 SELECT o, d2.pcno FROM Course AS d1, Course AS d2 WHERE d

56、1.pcno=o;3、 相關(guān)子查詢相關(guān)子查詢n當(dāng)一個子查詢的判斷條件涉及到一個來自外部查詢的當(dāng)一個子查詢的判斷條件涉及到一個來自外部查詢的列時，稱為列時，稱為相關(guān)子查詢相關(guān)子查詢。n帶存在謂詞的子查詢：只產(chǎn)生邏輯值帶存在謂詞的子查詢：只產(chǎn)生邏輯值n存在謂詞存在謂詞EXISTS作用作用: 若內(nèi)層查詢結(jié)果非空，則外若內(nèi)層查詢結(jié)果非空，則外層的層的WHERE子句返回真值，否則返回假值。子句返回真值，否則返回假值。n求解相關(guān)子查詢不能象求解不相關(guān)子查詢那樣，一次求解相關(guān)子查詢不能象求解不相關(guān)子查詢那樣，一次將子查詢求解出來，然后求解父查詢。相關(guān)子查詢的將子查詢求解出來，然后求解父查詢。相關(guān)子查詢的內(nèi)層

57、查詢由于與外層查詢有關(guān)，因此必須反復(fù)求值。內(nèi)層查詢由于與外層查詢有關(guān)，因此必須反復(fù)求值。 例例3.93.9 查詢所有選修了查詢所有選修了005005號號課程的學(xué)生姓名課程的學(xué)生姓名和學(xué)號和學(xué)號。 SELECT sname SELECT sname,sno,sno FROM Student FROM Student WHERE WHERE EXISTS EXISTS (SELECT (SELECT * * FROM SC FROM SC WHERE WHERE SC.sSC.sno=no=Student.Student.s snono AND cno= AND cno=005005)；n 分析：

58、分析：查詢所有選修了查詢所有選修了005005號課程的學(xué)生姓名涉及號課程的學(xué)生姓名涉及StudentStudent關(guān)系和關(guān)系和SCSC關(guān)系，在關(guān)系，在StudentStudent關(guān)系中依次取每個元關(guān)系中依次取每個元組的組的SnoSno值，用此值，用此Student.SnoStudent.Sno值去檢查值去檢查SCSC關(guān)系，若關(guān)系，若SCSC中存在中存在這樣的元組：其這樣的元組：其SC.SnoSC.Sno值等于值等于用來檢查的用來檢查的Student.SnoStudent.Sno值，值，并且其并且其SC.Cno=SC.Cno=005005，則取，則取此此Student.SnameStudent.

59、Sname送入結(jié)果關(guān)送入結(jié)果關(guān)系。系。n 相關(guān)子查詢的一般處理過程：相關(guān)子查詢的一般處理過程： 首先取外層查詢中首先取外層查詢中StudentStudent表的第一個元組，根據(jù)它與表的第一個元組，根據(jù)它與內(nèi)層查詢相關(guān)的屬性值（即內(nèi)層查詢相關(guān)的屬性值（即SnoSno值）處理內(nèi)層查詢，若值）處理內(nèi)層查詢，若WHEREWHERE子句返回值為真（即內(nèi)層查詢結(jié)果非空），則取此子句返回值為真（即內(nèi)層查詢結(jié)果非空），則取此元組放入結(jié)果表；然后再檢查元組放入結(jié)果表；然后再檢查StudentStudent表的下一個元組；表的下一個元組；重復(fù)這一過程，直至重復(fù)這一過程，直至StudentStudent表全部檢查完

60、畢為止。表全部檢查完畢為止。 例例3.93.9 查詢查詢沒沒選修選修001001號課程號課程的學(xué)生的學(xué)生學(xué)號和姓名學(xué)號和姓名。方法方法1 1： SELECT sn SELECT sno,snameo,sname FROM Student FROM Student WHERE WHERE NOT NOT EXISTSEXISTS (SELECT (SELECT * * FROM SC FROM SC WHERE WHERE SC.sSC.sno=no=Student.Student.s snono AND cno= AND cno=001001)；方法方法2 2： SELECT sn SELEC