1、關系數據庫標準語言SQL第三章 關系數據庫標準語言SQL3.1 SQL概述概述3.2 數據定義數據定義3.3 查詢查詢3.4 數據更新數據更新3.5 視圖視圖3.6 數據控制數據控制3.7 嵌入式嵌入式SQL關系數據庫標準語言SQLSQL歷史 SQ是什么？ SQL是一個通用的、功能極強的關系數據庫的標準語言。 SQL語言是介乎于關系代數和元組演算之間的一種結構化查詢語言。 本章詳細介紹SQL的核心部分內容：數據定義、數據查詢、數據更新和嵌入式SQL。 歷史 1970年，美國IBM研究中心的E.F.Codd連續發表多篇論文，提出關系模型。 1972年，IBM公司開始研制實驗型關系數據庫管理系統S

2、YSTEM R，配制的查詢語言稱為SQUARE (Specifying Queries As Relational Expression )語言，在語言中使用了較多的數學符號。 1974年，Boyce和Chamberlin把SQUARE修改為SEQUEL (Structured English QUEry Language )語言。后來SEQUEL簡稱為SQL (Structured Query Language )，即“結構式查詢語言”，SQL的發音仍為“sequel”。現在SQL已經成為一個標準 。關系數據庫標準語言SQLSQL概述 SQL的功能SQL數據定義功能SQL模式、基本表和索引的

3、創建和撤銷SQL數據查詢功能SELECT語句的句法，SELECT語句的幾種形式及各種限定，基本表的聯接操作，SQL3中的遞歸查詢SQL數據修改功能或數據操縱插入、刪除和修改語句視圖的創建和撤銷，對視圖更新操作的限制SQL數據控制功能GRANT，REVOKE 嵌入式SQL：預處理方式，使用規定，使用技術，卷游標，動態SQL語句關系數據庫標準語言SQL數據庫的體系結構 用戶用戶1 1用戶用戶2 2用戶用戶3 3用戶用戶4 4視圖視圖1 1視圖視圖1 1基本表基本表1 1基本表基本表2 2基本表基本表3 3基本表基本表4 4存儲文件存儲文件1 1存儲文件存儲文件2 2存儲文件存儲文件3 3存儲文件存

4、儲文件4 4SQLSQL數據庫的體系結構數據庫的體系結構SQLSQL用戶用戶ViewViewB a s e B a s e tabletableStoreStored d filefile關系數據庫標準語言SQL基本概念基本表是本身獨立存在的表，在SQL中一個關系就是一個基本表。一個基本表對應一個存儲文件，一個表可以帶若干索引，索引也存放在存儲文件中。存儲文件存儲文件的邏輯結構組成了關系數據庫的內模式。存儲文件的物理結構是任意的，對用戶是透明的。視圖 是從一個或幾個基本表導出的表。它本身不獨立存儲在數據庫中，即數據庫中只存放視圖的定義而不存放視圖對應的數據，這些數據仍存放在導出視圖的基本表中，

5、因此視圖是一個虛表。視圖在概念上于基本表等同，用戶可以在視圖上再定義視圖。關系數據庫標準語言SQL基本概念 主要特點包括： 綜合統一 高度非過程化 面向集合的操作方式 以同一種語法結構提供兩種使用方式 語言簡捷，易學易用SQL語言支持關系數據庫三級模式結構。其中: 外模式 對應于 視圖和部分基本表， 模式 對應于 基本表， 內模式 對應于 存儲文件。用戶可以用SQL語言對基本表和視圖進行查詢或其他操作，基本表和視圖一樣，都是關系。關系數據庫標準語言SQLSQL的組成 核心SQL主要有四個部分: 數據定義語言即SQL DDL，用于定義SQL模式、基本表、視圖、索引等結構。 數據操縱語言即SQL

6、DML。數據操縱分成數據查詢和數據更新兩類。其中數據更新又分成插入、刪除和修改三種操作。 嵌入式SQL語言的使用規定。這一部分內容涉及到SQL語句嵌入在宿主語言程序中的規則。 數據控制語言即SQL DCL，這一部分包括對基本表和視圖的授權、完整性規則的描述、事務控制等內容。 返回關系數據庫標準語言SQLSQL數據定義功能域定義*SQL提供的主要數據類型(也稱為“域類型”)有：（1）數值型（2）字符串型（3）位串型（4）時間型基本表的定義索引的定義數據庫的建立與撤消SQL數據定義特點關系數據庫標準語言SQL定義基本表格式如下： （列級完整性約束 ，列級完整性約束，），primary key（列名

7、 ，列名 ） ，foreign key （列名 ，列名 ） references 表名 （列名 ，列名 ） ，check（條件）;說明：如果完整性約束條件涉及到該表的多個屬性列，則必須定義在表級上，否則既可以定義在列級也可以定義在表級。注意：定義表時每一個定義語句之間用逗號分隔，最后一條語句不用逗號。每個SQL語句以分號結束。關系數據庫標準語言SQL示例數據庫DEPT(Dno , DNAME , DEAN)Student(Sno , SNAME , SEX , AGE , Dno)COURSE(Cno , CName , PCno , CREDIT)SC(Sno , Cno , SCORE)P

8、ROF(Pno , PNAME, AGE, Dno , SAL)PC(Pno , Cno)關系數據庫標準語言SQL例子例1 建立一個“學生”表Student，它由學號Sno、姓名Sname、性別Ssex、年齡Sage、所在系Sdept五個屬性組成，其中學號屬性不能為空，并且其值是唯一的。CREATETABLE Student ( Sno CHAR(5) NOT NULL UNIQUE,Sname CHAR(20),Ssex CHAR(1),Sage INT,Sdept CHAR(15) );關系數據庫標準語言SQL例子*：例2CREATE TABLE Course（cno char(4) no

9、t null，cname char(10) not null，PCno char(4) , CREDIT int,PRIMARY KEY(CNO) )；例3CREATE TABLE SC (SNO char(5) NOT NULL，CNO char(4) NOT NULL，GRADE SMALLINT，PRIMARY KEY(SNO,CNO) ，FOREIGN KEY(SNO) REFERENCES S(SNO) ，FOREIGN KEY(CNO) REFERENCES C(CNO) ) ；關系數據庫標準語言SQL常見域類型*char（n）或charter（n） ：固定長度的字符串。varch

10、ar（n）：最大長度為n的可變長字符串。Int或integer：全字長二進制整數。smallint：半字長二進制整數。numeric（p，d）：定點數，小數點左邊p位，右邊q位。Decimal(p,q)或Dec (p,q)壓縮十進制數,共P位，其中小數點后有q位，0=q=p 0)關系數據庫標準語言SQL修改基本表結構格式為：ALTER TABLE ADD 完整性約束DROP MODIFY ；ADD子句用于增加新列和新的完整性約束條件，DROP子句用于刪除制定的完整性約束條件，MODIFY子句用于修改原有的列定義，包括修改列名和數據類型。示例alter table PROF add LOCATI

11、ON char30關系數據庫標準語言SQL例2 向Student表增加“入學時間”列，其數據類型為日期型ALTER TABLE Student ADD Scome DATE; 不論基本表中原來是否已有數據，新增加的列一律為空值。 例3 將年齡的數據類型改為半字長整數ALTER TABLE Student MODIFY Sage SMALLINT; 修改原有的列定義有可能會破壞已有數據。 例4 刪除關于學號必須取唯一值的約束ALTER TABLE Student DROP UNIQUE(Sno); 說明：SQL沒有提供刪除屬性列的語句，用戶只能間接實現這一功能，即先原表中要保留的列及其內容復制到

12、一個新表中，然后刪除原表，并將新表重命名為原表名。 關系數據庫標準語言SQL刪除基本表格式為：DROP TABLE 示例5 ：刪除Student表DROP TABLEStudent 撤消基本表后，基本表的定義、表中數據、索引、以及由此表導出的視圖的定義都被刪除。關系數據庫標準語言SQL索引：加快查詢速度 建立索引的一般格式為：CREATE UNIQUE CLUSTER INDEX ON （，）； 索引可以建立在該表的一列或多列上，各列名之間用逗號分隔。每個后面還可以用指定索引值的排列次序，可選ASC（升序）或DESC（降序），缺省值為ASC。 UNIQUE表明此索引的每一個索引值只對應唯一的數

13、據記錄。 CLUSTER表示要建立的索引是聚簇索引。所謂聚簇索引是指索引項的順序與表中記錄的物理順序一致的索引組織。用戶可以在最常查詢的列上建立聚簇索引以提高查詢效率。顯然在一個基本表上最多只能建立一個聚簇索引。建立聚簇索引后，更新索引列數據時，往往導致表中記錄的物理順序的變更，代價較大，因此對于經常更新的列不宜建立聚簇索引。 示例 create cluster index s-index on S(S#)關系數據庫標準語言SQL例子例6為學生-課程數據庫中的Student、Couse、SC三個表建立索引。其中Student表按學號升序建唯一索引，CREATE UNIQUE INDEX Stu

14、sno ON Student(Sno);Couse表按課程號升序建唯一索引，CREATE UNIQUE INDEX Coucno ON Couse(Cno);Sno、Cno表按學號升序和課程號降序建唯一索引。CREATE UNIQUE INDEX SCno ON SC(Sno ASC,Cno DESC); 關系數據庫標準語言SQL刪除索引一般格式：DROP INDEX ；索引的有關說明什么時候適合建立索引？可以動態地定義索引，即可以隨時建立和刪除索引。不允許用戶在數據操作中引用索引。索引如何使用完全由系統決定，這支持了數據的物理獨立性。索引是否越多越好？應該在使用頻率高的、經常用于連接的列上建

15、索引。一個表上可建多個索引。索引可以提高查詢效率，但索引過多耗費空間，且降低了插入、刪除、更新的效率。例7 刪除Student表的索引Stusname（假設已存在）DROP INDEX Stusname; 關系數據庫標準語言SQL數據庫的建立與撤消 有的數據庫系統支持多庫。建立一個新數據庫create database 數據庫名撤消一個數據庫drop database 數據庫名指定當前數據庫database 數據庫名指定當前數據庫close database 數據庫名關系數據庫標準語言SQLSQL數據定義特點* SQL中，任何時候都可以執行一個數據定義語句，隨時修改數據庫結構。而在非關系型的數

16、據庫系統中，必須在數據庫的裝入和使用前全部完成數據庫的定義。若要修改已投入運行的數據庫，則需停下一切數據庫活動，把數據庫卸出，修改數據庫定義并重新編譯，再按修改過的數據庫結構重新裝入數據。數據庫定義不斷增長（不必一開始就定義完整）。數據庫定義隨時修改（不必一開始就完全合理）。可進行增加索引、撤消索引的實驗，檢驗其對效率的影響。關系數據庫標準語言SQLSQL數據查詢功能*SQL數據查詢基本結構select子句重復元組的處理from子句where子句更名運算字符串操作元組顯示順序集合操作分組和聚集函數空值嵌套子查詢派生關系視圖關系的連接關系數據庫標準語言SQL查詢 數據庫查詢是數據庫的核心操作。S

17、QL語言提供了SELECT語句進行數據庫的查詢，該語句具有靈活的使用方式和豐富的功能。其一般格式為： SELECT ALL|DISTINCT ， FROM ， WHERE GROUP BY HAVING ORDER BY ASC|DESC； 關系數據庫標準語言SQLSELECT語句的含義 根據WHERE子句的條件表達式，從FROM子句指定的基本表或視圖中找出滿足條件的元組， 再按SELECT子句中的目標列表達式，選出元組中的屬性值形成結果表。 如果有GROUP子句，則將結果按的值進行分組，該屬性列值相等的元組為一組。通常會在每組中作用集函數。如果GROUP子句帶HAVING短語，則只有滿足指定

18、條件的組才予輸出。 如果有ORDER BY子句，則結果表還要按的值的升序或降序排序。 DISTINCT（ALL）表示選出的記錄中不包括（包括）重復記錄 注意：語法中方括號里的內容是可選項，根據功能的不同可以選也可以不選，尖括號中的內容是必選項。關系數據庫標準語言SQLSQL數據查詢基本結構基本結構select A1 , A2 , , Anfrom r1 , r2 , , rmwhere P A1 , A2 , , An(p(r1 r2 rm)示例給出所有老師的姓名。select PNAMEfrom PROF關系數據庫標準語言SQLselect子句目標列形式 可以為列名、* 、算術表達式、聚集函

19、數。“*”：表示“所有的屬性”。例：給出所有老師的信息。select *from PROF帶， ， 的算術表達式例：給出所有老師的姓名及稅后工資額。select PNAME，SAL 0.95from PROF關系數據庫標準語言SQL重復元組的處理語法約束 缺省為保留重復元組，也可用關鍵字all顯式指明。若要去掉重復元組，可用關鍵字distinct或unique指明。示例 找出所有選修課程的學生。 select distinct SNO from SC關系數據庫標準語言SQLfrom子句（）說明 from子句列出查詢的對象表。當目標列取自多個表時，在不混淆的情況下可以不用顯式指明來自哪個關系。示

20、例例：找出工資低于500的職工的姓名、工資、系別。 select PNAME , SAL , DNAME from PROF , DEPT where SAL P2.SAL關系數據庫標準語言SQL元組顯示順序命令order by 列名 asc | desc示例按系名升序列出老師姓名，所在系名，同一系中老師按姓名降序排列。 select DNAME，PNAME from PROF，DEPT where PROF.DNO = DEPT.DNO order by DNAME asc，PNAME desc關系數據庫標準語言SQL單表查詢 選擇表中的若干列 1查詢指定列 中各個列的先后順序可以與表中的順

21、序不一致。用戶可以根據應用的需要改變列的現實順序。 2查詢全部列 3查詢經過計算的值 不僅可以是算術表達式，還可以是字符串常量、函數等。注意算數表達式、字符串常量、函數做目標列表達式。用字符串常量做目標列表達式時，必須用單引號把字符串引起來，表示一個字符串常量。 用戶可以通過指定別名來改變查詢結果的列標題，這對于含算數表達式、常量、函數名的目標列表達式尤為有用。 關系數據庫標準語言SQL查詢指定列例子例1查詢全體學生的學號與姓名SELECT Sno,SnameFROM Student; 中各個列的先后順序可以與表中的順序不一致。也就是說，用戶在查詢時可以根據應用的需要改變列的顯示順序。例2查詢

22、全體學生的姓名、學號、所在系SELECT Sname, Sno, SdeptFROM Student; 這時結果表中的列的順序與基表中不同，是按查詢要求，先列出姓名屬性，然后再列學號屬性和所在系屬性。關系數據庫標準語言SQL查詢全部列例子例3查詢全體學生的詳細記錄SELECT *FROM Student;該SELECT語句實際上是無條件地把Student表的全部信息都查詢出來，所以也稱為全表查詢，這是最簡單的一種查詢。關系數據庫標準語言SQL查詢經過計算的值例子SELECT子句的不僅可以是表中的屬性列，也可以是有關表達式，即可以將查詢出來的屬性列經過一定的計算后列出結果。 例4 查全體學生的姓

23、名及其出生年份SELECT Sname, 2005-SageFROM Student; 本例中，中第二項是一個計算表達式。v例例4輸出的結果為：輸出的結果為： Sname 1996-Sage-李勇李勇1976劉晨劉晨1977王名王名1978張立張立1977-關系數據庫標準語言SQL例5查全體學生的姓名、出生年份和所有系，要求用小寫字母表示所有系名SELECT Sname, Year of Birth:, 1996-Sage, ISLOWER(Sdept)FROM Student;結果為： Sname Year of Birth:1996-SageISLOWER(Sdept)-李勇 Year o

24、f Birth:1976cs劉晨 Year of Birth:1977is王名 Year of Birth:1978ma張立 Year of Birth:1977is關系數據庫標準語言SQL 用戶可以通過指定別名來改變查詢結果的列標題，這對于含算術表達式、常量、函數名的目標列表達式尤為有用。例如對于上例，可以如下定義列別名 SELECTSname NAME, Year of Birth:BIRTH,1996-SageBIRTHDAY,ISLOWER(Sdept) DEPARTMENTFROMStudent;結果為： NAMEBIRTH BIRTHDAY DEPARTMENT -李勇Year o

25、f Birth: 1976cs劉晨Year of Birth: 1977is王名Year of Birth: 1978 ma張立Year of Birth: 1977 is關系數據庫標準語言SQL選擇表中的若干元組 1消除取值重復的行 查詢結果里可能包含了許多重復行。如果想去掉結果表中的重復行，必須指定DISTINCT短語。如果沒有指定DINTINCT短語，則缺省為ALL，即保留結果表中取值重復的行。 關系數據庫標準語言SQL例6 查所有選修過課的學生的學號SELECT SnoFROM SC;假設SC表中有下列數據 Sno Cno Grade- - - 95001 1 92 95001 2 8

26、5 95001 3 88 95002 2 90 95002 3 80 執行左面的執行左面的SELECT語句后，結語句后，結果為：果為： Sno - 95001 95001 95001 95002 95002 查詢結果里包含了查詢結果里包含了許多重復的行。許多重復的行。如果想去掉結果表中的重復行，必須指定如果想去掉結果表中的重復行，必須指定DISTINCT短語：短語： SELECT DISTINCT Sno FROM SC; 執行結果為：執行結果為： Sno - 95001 95002 -關系數據庫標準語言SQL查詢滿足條件的元組查詢滿足指定條件的元組可以通過WHERE子句實現。WHERE子句常

27、用的查詢條件如書上表3.3。 比較大小 確定范圍 確定集合字符匹配 涉及空值的查詢多重條件查詢關系數據庫標準語言SQL表3-3 常用的查詢條件查詢條件謂 詞比較 =,=,=,!=,!,! NOT+上述比較運算符確定范圍BETWEEN AND, NOT BETWEEN AND確定集合IN, NOT IN字符匹配LIKE, NOT LIKE空值IS NULL, IS NOT NULL多重條件AND, OR 關系數據庫標準語言SQL比較大小例7查計算機系全體學生的名單SELECT Sname FROM Student WHERE Sdept = CS; 例8查所有年齡在20歲以下的學生姓名及其年齡S

28、ELECT Sname, Sage FROM Student WHERE Sage = 20; 關系數據庫標準語言SQL例9查考試成績有不及格的學生的學號SELECT DISTINCT Sno FROM SCWHERE Grade 60; 這里使用了DISTINCT短語，當一個學生有多門課程不及格，他的學號也只列一次。 關系數據庫標準語言SQL確定范圍例10 查詢年齡在20至23歲之間的學生的姓名、系別、和年齡SELECT Sname, Sdept, SageFROM Student WHERE Sage BETWEEN 20 AND 23; 與BETWEEN.AND.相對的謂詞是NOT BE

29、TWEEN.AND.。 例11 查詢年齡不在20至23歲之間的學生姓名、系別和年齡。SELECT Sname, Sdept, Sage FROM StudentWHERE Sage NOT BETWEEN 20 AND 23; 關系數據庫標準語言SQL確定集合例12 查信息系（IS）、數學系（MA）和計算機科學系（CS）的學生的姓名和性別SELECT Sname, Ssex FROM Student WHERE Sdept IN (IS, MA, CS) 與IN相對的謂詞是NOT IN，用于查找屬性值不屬于指定集合的元組。 例13 查既不是信息系、數學系，也不是計算機科學系的學生的姓名和性別S

30、ELECT Sname, Ssex FROM Student WHERE Sdept NOT IN (IS, MA, CS) 關系數據庫標準語言SQL例：查詢至少選修了1號和3號課程學生的學號 SELECT SNO FROM SC WHERE CNO=1 AND SNO IN(SELECT SNO FROM SC WHERE CNO=3); 關系數據庫標準語言SQL字符匹配語法格式如下： NOT LIKE ESCAPE 其含義是查找指定的屬性列值與相匹配的元組。可以是一個完整的字符串，也可以含有通配符%和_。其中： %（百分號）代表任意長度（長度可以為0）的字符串。 _（下橫線）代表任意單個字

31、符。 如果用戶要查詢的字符串本身就包含有%或_，這時就要使用ESCAPE 短語對通配符進行轉義了。 關系數據庫標準語言SQL示例列出姓名以“張”打頭的教師的所有信息。 select * from PROF where PNAME like 張%列出名稱中含有4個字符以上，且倒數第3個字符是d，倒數第2個字符是_的系的所有信息。 select * from PROF where PNAME like % d ESCAPE 關系數據庫標準語言SQL例14 查所有姓劉的學生的姓名、學號和性別SELECT Sname, Sno, SsexFROM Student WHERE Sname LIKE 劉%

32、; 例15 查姓“歐陽”且全名為三個漢字的學生的姓名SELECT Sname FROM Student WHERE Sname LIKE 歐陽_; 注意，由于一個漢字占兩個字符的位置，所以匹配串歐陽后面需要跟個_。 關系數據庫標準語言SQL例16 查名字中第二字為“陽”字的學生的姓名和學號SELECT Sname, Sno FROM Student WHERE Sname LIKE _陽%; 例17 查所以不姓劉的學生姓名SELECT Sname, Sno, SsexFROM StudentWHERE Sname NOT LIKE 劉%;關系數據庫標準語言SQL如果用戶要查詢的匹配字符串本身就

33、含有%或_，比如要查名字為DB_Design的課程的學分，應如何實現呢？這時就要使用ESCAPE 短語對通配符進行轉義了。 例18 查DB_Design課程的課程號和學分SELECT Cno, CcreditFROM CourseWHERE Cname LIKE DB_Design ESCAPE ESCAPE 短語表示為換碼字符，這樣匹配串中緊跟在后面的字符”_”不再具有通配符的含義，而是取其本身含義，被轉義為普通的”_”字符。關系數據庫標準語言SQL例19查以”DB_”開頭，且倒數第三個字符為i的課程的詳細情況SELECT *FROM CourseWHERE Cname LIKE DB_%i

34、_ ESCAPE ;注意：這里的匹配字符串DB_%i_。第一個_前面有換碼字符，所以它被轉義為普通的_字符。而%、第二個_和第三個_前面均沒有換碼字符，所以它們仍作為通配符。其執行結果為： CnoCnameCcredit-8 DB_Design 410DB_Programing 213DB_DBMS Design4 關系數據庫標準語言SQL涉及空值的查詢例20某些學生選修某門課程后沒有參加考試，所以有選課記錄，但沒有考試成績，下面我們來查一下缺少成績的學生的學號和相應的課程號SELECT Sno, Cno FROM SCWHERE Grade IS NULL; 注意這里的IS不能用等號（=）

35、代替。 例21查所有有成績的記錄的學生學號和課程號SELECT Sno, Cno FROM SC WHERE Grade IS NOT NULL; 關系數據庫標準語言SQL多重條件查詢邏輯運算符AND和OR可用來聯結多個查詢條件。如果這兩個運算符同時出現在同一個WHERE條件子句中，則AND的優先級高于OR，但用戶可以用括號改變優先級。例22查CS系年齡在20歲以下的學生姓名SELECT Sname FROM Student WHERE Sdept=CS AND Sage20;關系數據庫標準語言SQL 例12 中的IN謂詞實際上是多個OR運算符的縮寫，因此例12中的查詢也可以用OR運算符寫成如

36、下等價形式：SELECT Sname, Ssex FROM Student WHERE Sdept=IS OR Sdept=MA OR Sdept=CS; 附：例12查信息系（IS）、數學系（MA）和計算機科學系（CS）的學生的姓名和性別SELECT Sname, Ssex FROM Student WHERE Sdept IN (IS, MA, CS) 關系數據庫標準語言SQL對查詢結果排序 如果沒有指定查詢結果的顯示順序，DBMS將按其最方便的順序（通常是元組在表中的先后順序）輸出查詢結果。用戶也可以用ORDER BY子句指定按照一個或多個屬性列的升序（ASC）或降序（DESC）重新排列查

37、詢結果，其中升序ASC為缺省值。 對于空值，若按升序排，含空值的元組將最后顯示。若按降序排，空值的元組將最先顯示。多列排序時，排序列的順序不一樣，排序優先級也不同，前面的優先級高。關系數據庫標準語言SQL例23 查詢選修了3號課程的學生的學號及其成績，查詢結果按分數的降序排列SELECT Sno, GradeFROM SCWHERE Cno=3 ORDER BY Grade DESC;例24 查詢全體學生情況，查詢結果按所在系升序排列，對同一系中的學生按年齡降序排列 SELECT * FROM Student ORDER BY Sdept, Sage DESC; 關系數據庫標準語言SQL四、使

38、用集函數 為增強檢索功能，SQL提供了許多集函數，主要包括： COUNT(DISTINCT|ALL *) 統計元組個數 COUNT(DISTINCT|ALL ) 統計一列中值的個數 SUM(DISTINCT|ALL ) 計算一列值的總和（此列必須是數值型） AVG(DISTINCT|ALL ) 計算一列值的平均值（此列必須是數值型）MAX(DISTINCT|ALL ) 求一列值中的最大值 MIN(DISTINCT|ALL ) 求一列值中的最小值 如果指定DISTINCT短語，則表示在計算時要取消指定列中的重復值。如果不指定DISTINCT短語或指定ALL短語，則表示不取消重復值。 關系數據庫標

39、準語言SQL例25 查詢學生總人數 SELECT COUNT(*) FROM Student; 例26 查詢選修了課程的學生人數SELECT COUNT(DISTINCT Sno)FROM SC;學生每選修一門課，在SC中都有一條相應的記錄，而一個學生一般都要選修多門課程，為避免重復計算學生人數，必須在COUNT函數中用DISTINCT短語。關系數據庫標準語言SQL例27 計算1號課程的學生平均成績 SELECT AVG(Grade) FROM SC WHERE Cno=1; 例28 查詢學習1號課程的學生最高分數SELECT MAX(Grade) FROM SC WHERE Cno=1; 關

40、系數據庫標準語言SQL對查詢結果分組 GROUP BY子句可以將查詢結果表的各行按一列或多列取值相等的原則進行分組。對查詢結果分組的目的是為了細化集函數的作用對象。如果未對查詢結果分組，集函數將作用于整個查詢結果，即整個查詢結果只有一個函數值。否則，集函數將作用于每一個組，即每一組都有一個函數值。 HAVING短語指定選擇組的條件，只有滿足條件的組才會被選出來。HAVING短語的條件由集函數構造。 關系數據庫標準語言SQL關系數據庫標準語言SQL例30 查詢信息系選修了3門以上課程的學生的學號SELECT SnoFROM SCWHERE Sdept=IS;GROUP BY SnoHAVING

41、COUNT(*)3; 查選修課程超過3門的信息系學生的學號，首先需要通過WHERE子句從基本表中求出信息系的學生。 然后求其中每個學生選修了幾門課，為此需要用GROUP BY子句按Sno進行分組，再用集函數COUNT對每一組計數。 如果某一組的元組數目大于3，則表示此學生選修的課超過3門，應將他的學生號選出來。HAVING短語指定選擇組的條件，只有滿足條件（即元組個數3）的組才會被選出來。 關系數據庫標準語言SQL分組和聚集函數（）分組命令group by 列名 having 條件表達式 group by將表中的元組按指定列上的值相等的原則分組，然后在每一分組上使用聚集函數，得到單一值。hav

42、ing則對分組進行選擇，只將聚集函數作用到滿足條件的分組上。聚集函數平均值：avg最小值：min最大值：max總和：sum記數：count關系數據庫標準語言SQL分組和聚集函數（）示例列出各系的老師的最高、最低、平均工資。 select DNO，max(SAL)，min(SAL)，avg(SAL) from PROF group by DNO列出及格的學生的平均成績。 select SNO，avg(SCORE) from SC group by SNO having min(SCORE) = 60關系數據庫標準語言SQL空值（）空值測試is not null測試指定列的值是否為空值。示例找出年

43、齡值為空的老師姓名。 select PNAME from PROF where AGE is null不可寫為where AGE = null關系數據庫標準語言SQL空值（）注意事項除is not null之外，空值不滿足任何查找條件。如果null參與算術運算，則該算術表達式的值為null。如果null參與比較運算，則結果可視為false。在SQL-92中可看成unknown。如果null參與聚集運算，則除count(*)之外其它聚集函數都忽略null。 例：select sum(SAL) from PROF 例：select count(*) from PROF關系數據庫標準語言SQL集合操

44、作（）命令集合并：union集合交：intersect集合差： except示例求選修了001或002號課程的學生號。（select SNO from SC where CNO = 001）union all（select SNO from SC where CNO = 002）關系數據庫標準語言SQL集合操作（）求選修了001和002號而沒有選003號課程的學生號。（select SNO from SC where CNO = 001 or CNO = 002 ）except（select SNO from SC where CNO = 003）提示 集合操作自動去除重復元組，如果要保留重復

45、元組的話，必須用all關鍵詞指明。關系數據庫標準語言SQL連接查詢 什么是連接查詢？若一個查詢同時涉及兩個以上的表，則稱之為連接查詢。連接查詢是關系數據庫中最主要的查詢，也是查詢中最難的一部分。 連接查詢主要包括等值連接非等值連接查詢、自然連接自身連接查詢外連接查詢復合條件連接查詢關系數據庫標準語言SQL等值與非等值連接查詢 連接查詢中用來連接兩個表的條件稱為連接條件或連接謂詞。其一般格式為： . . 其中比較運算符主要有：=、=、=、!=、!= 此外連接謂詞詞還可以使用下面形式：. BETWEEN . AND . 當連接運算符為=號時，稱為。其它稱為。 連接謂詞中的列名稱為。連接條件中的各連

46、接字段類型必須時可比的，但不必是相同的。 關系數據庫標準語言SQLDBMS執行連接操作的過程首先在表1中找到第一個元組，然后從頭開始掃描表2，逐一查找滿足連接條件的元組，找到后就將表1中的第一個元組與該元組拼接起來，形成結果表中一個元組。表2全部查找完后，再找表1中第2個元組，然后再從頭開始掃描表2，逐一查找滿足連接條件的元組，找到后就將表1中的第2個元組與該元組拼接起來，形成結果表中一個元組。重復上述操作，直到表1中的全部元組都處理完畢為止。 關系數據庫標準語言SQL例32 查詢每個學生及其選修課程的情況學生情況存放在Student表中，學生選課情況存放在SC表中，所以本查詢實際上同時涉及S

47、tudent與SC兩個表中的數據。這兩個表之間的聯系是通過兩個表都具有的屬性Sno實現的。要查詢學生及其選修課程的情況，就必須將這兩個表中學號相同的元組連接起來。這是一個等值連接。完成本查詢的SQL語句為： SELECT Student.*, SC.* FROM Student, SC WHERE Student.Sno=SC.Sno; 關系數據庫標準語言SQL廣義笛卡兒積是不帶連接謂詞的連接。兩個表的廣義笛卡兒積即是兩表中元組的交叉乘積，連接結果會產生一些沒有意義的元組，所以這種運算沒有什么意義。 若在等值連接中把目標列中重復的屬性列去掉則為自然連接。 關系數據庫標準語言SQL例33自然連接

48、Student和SC表SELECT Student.Sno, Sname, Ssex, Sage, Sdept, Cno, GradeFROM Student, SCWHERE Student.Sno=SC.Sno;在本查詢中，由于Sname、 Ssex、 Sage、Sdept、Cno和Grade屬性列在Student與SC表中是唯一的，因此引用時可以去掉表名前綴。而Sno在兩個表都出現了，因此引用時必須加上表名前綴。該查詢的執行結果不再出現SC.Sno列。關系數據庫標準語言SQL自身連接 連接操作不僅可以在兩個表之間進行，也可以是一個表與其自己進行連接，稱為表的自然連接。 例34：查詢每一門

49、課程的間接先行課。 SELECT FIRST.CNO,SECOND.CPNO FROM COURSE FIRST,COURSE SECOND WHERE FIRST.CPNO=SECOND.CNO; 參見page104為清楚起見，我們可以為為清楚起見，我們可以為Course表取兩個別名，表取兩個別名，一個是一個是FIRST，另一個是另一個是SECOND，也可以在考也可以在考慮問題時就把慮問題時就把Course表想成是兩個完全一樣表，表想成是兩個完全一樣表，一個是一個是FIRST表，另一個是表，另一個是SECOND表。表。 結果表如下：結果表如下： Cno Pcno - - 1 7 3 5 5

50、6 關系數據庫標準語言SQL結合連接查詢的執行過程詳細說明該語句的執行順序。 例：對于PC表（PC,HD）分別為PC的型號和硬盤，查詢在兩種或兩種以上PC機上出現的硬盤。 SELECT DISTINCT HD FROM PC PC1,PC PC2 WHERE PC1.PCPC2.PC AND PC1.HD=PC2.HD 關系數據庫標準語言SQL外連接 什么時候需要外連接？ 在通常的連接操作中，只有滿足連接條件的元組才能作為結果輸出。有時不能滿足我們的要求。例如以STUDENT表為主體列出每個學生的基本情況及其選課情況，若某個學生沒有選課，只輸出其基本情況信息，其選課信息為空值即可，這就需要外連

51、接, 外連接的表示方法為 在連接謂詞的某一邊加符號*。外連接就好像是為符號*所在邊的表增加一個“萬能”的行，這個行全部由空值組成。它可以和另一邊的表中所有不滿足連接條件的元組進行連接。 SELECT Student.Sno,sname,ssex,sage,sdept,cno,grade FROM STUDENT,SC WHERE STUDENT.SNO=SC.SNO(*); 關系數據庫標準語言SQLStudent.Sno Sname Ssex Sage Sdept Cno Grade- - - - - - - 95002 劉晨 女 19 IS 3 80 95003 王名 女 18 MA 950

52、04 張立 男 18 IS 上例中外連接符*出現在連接運算符的右邊，所以也稱其為右外連接。相應地，如果外連接符出現在連接運算符的左邊，則稱為左外連接。 關系數據庫標準語言SQL復合條件連接 連接操作除了可以是兩表連接，一個表與其自身連接外，還可以是兩個以上的表進行連接，后者通常稱為多表連接。 例35：查詢每個學生的學號、姓名、選修的課程名及成績。 SELECT Student.Sno, Sname, Course.Cname, SC.Grade FROM Student, SC, Course WHERE Student.Sno=SC.Sno and SC.Cno=Course.Cno; 關系

53、數據庫標準語言SQL例35查詢選修2號課程且成績在90分以上的所有學生 SELECT Student.Sno, Sname FROM Student, SC WHERE Student.Sno=SC.Sno AND SC.Cno=2 AND SC.Grade90; 連接操作除了可以是兩表連接，一個表與其自身連接外，還可以是兩個以上的表進行連接，后者通常稱為多表連接。關系數據庫標準語言SQL嵌套查詢 在SQL語言中，一個SELECT-FROM-WHERE語句稱為一個查詢塊。將一個查詢塊嵌套在另一個查詢塊的WHERE子句或HAVING短語的條件中的查詢稱為嵌套查詢或子查詢。例如： SELECT S

54、name FROM StudentWHERE Sno IN SELECT Sno FROM SC WHERE Cno=2;在這個例子中，下層查詢塊 SELECT Sno FROM SC WHERE Cno=2是嵌套在上層查詢塊 SELECT Sname FROM Student WHERE Sno IN 的WHERE條件中的。 關系數據庫標準語言SQL說明上層的查詢塊又稱為外層查詢或父查詢或主查詢，下層查詢塊又稱為內層查詢或子查詢。SQL語言允許多層嵌套查詢。即一個子查詢中還可以嵌套其它子查詢。需要特別指出的是，子查詢的SELECT語句中不能使用ORDER BY子句，ORDER BY子句永遠只

55、能對最終查詢結果排序。關系數據庫標準語言SQL說明嵌套查詢的求解方法是由里向外處理。即每個子查詢在其上一級查詢處理之前求解，子查詢的結果用于建立其父查詢的查找條件。 嵌套查詢使得可以用一系列簡單查詢構成復雜的查詢，從而明顯地增強了SQL的查詢能力。以層層嵌套的方式來構造程序正是 SQL(Structurred Query Language)中“結構化”的含義所在。關系數據庫標準語言SQL分步完成查詢例37 查詢與“劉晨”在同一個系學習的學生查詢與“劉晨”在同一個系學習的學生，可以首先確定“劉晨”所在系名，然后再查找所有在該系學習的學生。所以可以分步來完成此查詢： 第步，確定“劉晨”所在系名 S

56、ELECT Sdept FROM StudentWHERE Sname=劉晨; 結果為： Sdept - IS 關系數據庫標準語言SQL分步完成查詢第步，查找所有在IS系學習的學生。SELECT Sno, Sname, Sdept FROM Student WHERE Sdept=IS; 結果為： Sno Sname Sdept - - - 95001 劉晨 IS 95004 張立 IS 分步寫查詢畢竟比較麻煩，上述查詢實際上可以用子查詢來實現，即將第一步查詢嵌入到第二步查詢中，用以構造第二步查詢的條件。關系數據庫標準語言SQL嵌套查詢SQL語句如下： SELECT Sno, Sname, S

57、deptFROM Student WHERE Sdept IN （SELECT Sdept FROM Student WHERE Sname=劉晨）; 本例中的查詢也可以用我們前面學過的表的自身連接查詢來完成：SELECT Sno, Sname, Sdept FROM Student S1, Student S2 WHERE S1.Sdept = S2.Sdept AND S2.Sname=劉晨; 關系數據庫標準語言SQL嵌套與連接的方法對比可見，實現同一個查詢可以多種方法，當然不同的方法其執行效率可能會有差別，甚至會差別很大。 例38 查詢選修了課程名為信息系統的學生學號和姓名 SELECT

58、 Sno, Sname FROM Student WHERE Sno IN (SELECT Sno FROM SC WHERE Cno IN ( SELECT Cno FROM Course WHERE Cname=信息系統);結果為結果為: Sno Sname - - 95001 李勇李勇 95002 劉晨劉晨 關系數據庫標準語言SQL嵌套與連接的方法對比本查詢同樣可以用連接查詢實現：SELECT Sno, SnameFROM Student, SC, Course WHERE Student.Sno=SC.Sno AND SC.Cno=Course.Cno AND Course.Cname

59、=信息系統; 例37和例38中的各個子查詢都只執行一次，其結果用于父查詢，子查詢的查詢條件不依賴于父查詢，這類子查詢稱為不相關子查詢。不相關子查詢是最簡單的一類子查詢。 關系數據庫標準語言SQL帶有比較運算符的子查詢 帶有比較運算符的子查詢是指父查詢與子查詢之間用比較運算符進行連接。當用戶能確切知道內層查詢返回的是單值時，可以用、 =、=、!=或等比較運算符。 在例37中，由于一個學生只可能在一個系學習，也就是說內查詢劉晨所在系的結果是一個唯一值，因此該查詢也可以用比較運算符來實現，其SQL語句如下： SELECT Sno, Sname, Sdept FROM Student WHERE Sd

60、ept = (SELECT Sdept FROM Student WHERE Sname=劉晨;) 關系數據庫標準語言SQL需要注意的是，子查詢一定要跟在比較符之后，下列寫法是錯誤的：SELECT Sno, Sname, Sdept FROM Student WHERE (SELECT Sdept FROM Student WHERE Sname=劉晨) = Sdept; 關系數據庫標準語言SQL例38中信息系統的課程號是唯一的，但選修該課程的學生并不只一個，所以例38也可以用=運算符和IN謂詞共同完成： SELECT Sno, Sname FROM StudentWHERE Sno IN (