第3章 數據庫語言及訪問接口

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

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

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

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

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

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

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

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

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

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

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

12、 設置默認的目錄和模式設置默認的目錄和模式 n 設置用戶身份設置用戶身份 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）數據庫模式的刪除）數據庫模式的刪除 DROP SCHEMA DROP SCHEMA 模式名模式名 CASCADE | RESTRICTCASCADE | RESTRICT CASCADECASCADE：級聯式級聯式 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在表級。注意：注意： 例：例：定義學生定

15、義學生_選課數據庫中的三個表結構，并指定相應的數據完選課數據庫中的三個表結構，并指定相應的數據完整性約束條件。整性約束條件。分析分析外外鍵鍵：（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基本表定義一旦刪除，表中的數據、在此表上建立的視基本表定義一旦刪除，表中的數據、在此表上建立的視圖 、 索 引 、 觸 發(fā) 器 、 斷 言 都 將 自 動 被 刪 除 掉 。圖 、 索 引

20、 、 觸 發(fā) 器 、 斷 言 都 將 自 動 被 刪 除 掉 。RESTRICT確保只有不具有相關對象的表才能被撤銷。確保只有不具有相關對象的表才能被撤銷。例：例：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）在現存表中增加新列）在現存表中增加新列 格式：格式：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.域定義域定義 域定義用于建立用戶自定義的數據類型。域定義用于建立用戶自定義的數據類型。n命令格式：命令格式： CREATE DOMAIN CREATE DOMAIN AS AS DEFAULT DEFAULT 例例3.3.3 3 建立一個建筑公司的數據庫模式，由建立一個建筑公司的數據

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: 每一個索引值只對應惟一的數據記錄。每一個索引值只對應惟一的數據記錄。 CLUSTER: 建立聚簇索引，即索引項的順序與表中記錄的物理順建立聚簇索引，即索引項的順序與表中記錄的物理順序一致。序一致。 n注意：注意：在一個基本表上最多只能建立一個聚簇索引。在一個基本表上最多只能建立一個聚簇索引。n經常更新的列不宜建立聚簇索引。經常更新的列不宜建立聚簇索引。n所建索引放何處？所建索引放何處？n例例: 為為Student表按學號升序建惟一聚簇索引。表按學號升序建惟一聚簇索引。 為為SC表按

26、學號表按學號升序升序和課程號和課程號降序降序建惟一索引。建惟一索引。 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)會同時從數據字典數據字典中刪去有關該索引的描述。中刪去有關該索引的描述。n基本形式：基本形式：SELECT FROM WHERE ;n查詢語句塊查詢語句塊含義含義: : 從表從表 (視

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

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

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

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

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

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

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

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

35、條件將選擇結果按將選擇結果按 的值進行分組的值進行分組選擇滿足條件的小組選擇滿足條件的小組按按 排序查詢結果排序查詢結果目標目標A來源來源R條件條件F分組分組 選組選組 排序排序GROUP BY HAVING ORDER BY ASC|DESC； 三、對查詢結果排序三、對查詢結果排序 例例3 3.5.5 查詢全體男學生的學號、姓名，查詢全體男學生的學號、姓名， 結果按所在的系升序排列，結果按所在的系升序排列， 同一系中的學生按年齡降序排列。同一系中的學生按年齡降序排列。 來源來源 R 條件條件 F目標目標 AStudent表排序二排序二排序一排序一 SELECT sno，sname FROM

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

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

38、nt FROM Student WHERE sex= WHERE sex=女女; 例例3.3.6 6 查詢選修了課程的學生人數。查詢選修了課程的學生人數。 SELECT COUNT(SELECT COUNT(DISTINCTDISTINCT sno) AS sno) AS 選課學生人數選課學生人數 FROM SC;FROM SC; 例例3.3.6 6 查詢選修查詢選修001001號課程并及格的學生的最高分數、最低分號課程并及格的學生的最高分數、最低分及總分及總分。 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、使用表達式使用表達式 例例 查詢各課程的學時數。查詢各課程的學時數。 SELECT cname AS course_name,SELECT cname AS course_name,creditcredit* *1818 AS course_time AS course_time FROM course; FROM course; 3、對查

40、詢結果分組對查詢結果分組（GROUP BY GROUP BY 子句子句）n將查詢結果表按某一將查詢結果表按某一( (多多) )列值分組，值相等的為一組。列值分組，值相等的為一組。n目的：細化集函數的作用對象。目的：細化集函數的作用對象。如果未對查詢結果分組，集函數如果未對查詢結果分組，集函數將作用于整個查詢結果，即整個查詢結果只有一個函數值。否則，將作用于整個查詢結果，即整個查詢結果只有一個函數值。否則，集函數將作用于每一個組，即每一組都有一個函數值。集函數將作用于每一個組，即每一組都有一個函數值。 例例3.3.6 6 求每個學生求每個學生所選課程所選課程的平均成績。的平均成績。 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分組情況及查詢結果示意圖分組情況及查詢結果示意圖3 3組組1 1組組2 2組組 sno AVG(AVG(grade) 000101 85 010101 80 例例: :求每個學生的平均求每個學生的平均 成績。成績。 SELECT sno,AVG(grade)SELECT sno,AVG(grade) FROM SC FROM SC GROUP BY sno GROUP BY sno ；求每門課程的求每門課程的平均成績？平均成績？集函數在成組之前不計算，因此集函數在成組之前不計算，因此不能用于不能用

43、于WHERE子句子句, 一般將簡一般將簡單條件寫入單條件寫入WHERE。HAVING子句的條件運算數之一子句的條件運算數之一是一個集函數。是一個集函數。若若HAVING子句無前導子句無前導GROUPBY，選擇清單中不能含有非集函數列。選擇清單中不能含有非集函數列?！揪毩暋俊揪毩暋坎樵冎辽龠x修查詢至少選修4門課程的學生學號。門課程的學生學號。 SELECT sno FROM SC GROUP BY sno HAVING COUNT(*)=4 例例3.3.6 6 求學生關系中求學生關系中湖北籍男湖北籍男生的每一年齡組（生的每一年齡組（不不少于少于3 30 0人）人）共共有多少有多少人人，要求查詢結

44、果按人數升序排列，要求查詢結果按人數升序排列，人數相同時按年齡降序排列。人數相同時按年齡降序排列。 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子查詢一般跟在子查詢一般跟在ININ、SOMESOME（ANYANY）、）、ALLALL和和EXISTEXIST等謂詞等謂詞后面。后面。 nSQLSQL語言允許多層嵌套查詢。語言允許多層

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

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

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

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

51、姓名。分以上的學生姓名。 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. 條件連接查詢條件連接查詢 連接條件連接條件的一般格式為：的一般格式為： . . 當連接運算符為當連接運算符為=時，稱為時，稱為等值連接等值連接。 例例: 查詢每個學生的情況及其選課成績。查詢每個學

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

53、e=DB ；, credit注意：注意：子查詢不能用子查詢不能用ORDER BYORDER BY子句子句為什么？為什么？不相關子查詢不相關子查詢 ( SELECT cno FROM Course WHERE cname=DB)； SELECT sno， grade FROM SC WHERE cno IN =方法方法1 1：方法方法2 2： 例例3.83.8 按平均成績的降序給出所有課程都及格的學生按平均成績的降序給出所有課程都及格的學生（號、名）及其平均成績，其中成績統(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 找出年齡比找出年齡比“王迎王迎”同學大的同學的姓名及年同學大的同學的姓名及年齡。齡。SELECT s1.sname，s1.ageFROM Student AS s1，Student AS s2WHERE s1.age s2.age AND s2.sname =王迎王迎； 其他方法？其他方法？ n練習練習：查每門課的查每門課的間接間接先修課先修課(即先修課的先修課即先修課的先修課)。d2d1003003004001 SELECT o, d2.pcno FROM Course AS d1, Course AS d2 WHERE d

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

57、查詢由于與外層查詢有關，因此必須反復求值。內層查詢由于與外層查詢有關，因此必須反復求值。 例例3.93.9 查詢所有選修了查詢所有選修了005005號號課程的學生姓名課程的學生姓名和學號和學號。 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號課程的學生姓名涉及號課程的學生姓名涉及StudentStudent關系和關系和SCSC關系，在關系，在StudentStudent關系中依次取每個元關系中依次取每個元組的組的SnoSno值，用此值，用此Student.SnoStudent.Sno值去檢查值去檢查SCSC關系，若關系，若SCSC中存在中存在這樣的元組：其這樣的元組：其SC.SnoSC.Sno值等于值等于用來檢查的用來檢查的Student.SnoStudent.Sno值，值，并且其并且其SC.Cno=SC.Cno=005005，則取，則取此此Student.SnameStudent.

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

60、畢為止。表全部檢查完畢為止。 例例3.93.9 查詢查詢沒沒選修選修001001號課程號課程的學生的學生學號和姓名學號和姓名。方法方法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