第03章 關(guān)系數(shù)據(jù)庫標準語言

1、1/156計算機學院 陳豐 數(shù)據(jù)庫系統(tǒng)概論2/156第三章第三章 關(guān)系數(shù)據(jù)庫標準語言關(guān)系數(shù)據(jù)庫標準語言SQLSQL3.1 SQL概述3.2 數(shù)據(jù)定義3.3 查詢3.4 數(shù)據(jù)更新3.5 視圖第3章 小結(jié)3/1561 SQL的發(fā)展2 SQL的性質(zhì)3 SQL的特點4 SQL對關(guān)系數(shù)據(jù)庫的支持5 SQL的功能3.1 SQL3.1 SQL概述概述4/1561.SQL1.SQL的發(fā)展的發(fā)展 SQL: Structured Query LanguageSQL: Structured Query Language，結(jié)構(gòu)化查詢語言結(jié)構(gòu)化查詢語言 19741974年，由年，由BoyceBoyce和和Chamber

2、Chamber提出提出 1975-19791975-1979年，在年，在System RSystem R上實現(xiàn)，由上實現(xiàn)，由IBMIBM的的San JoseSan Jose研研究室研制，稱為究室研制，稱為Sequel(Sequel(色扣、色扣、C C扣等扣等) ) 有關(guān)標準有關(guān)標準SQL-86SQL-86：“數(shù)據(jù)庫語言數(shù)據(jù)庫語言SQL”SQL”SQL-89SQL-89：“具有完整性增強的數(shù)據(jù)庫語言具有完整性增強的數(shù)據(jù)庫語言SQL”SQL”，增加，增加 了對完整性約束的支持了對完整性約束的支持SQL-92SQL-92：“數(shù)據(jù)庫語言數(shù)據(jù)庫語言SQL”SQL”，是，是SQL-89SQL-89的超集，

3、增加了許的超集，增加了許 多新特性，如新的數(shù)據(jù)類型，更豐富的數(shù)據(jù)操多新特性，如新的數(shù)據(jù)類型，更豐富的數(shù)據(jù)操 作，更強的完整性、安全性支持等。作，更強的完整性、安全性支持等。SQL-1999SQL-1999：增加了對象關(guān)系特征（：增加了對象關(guān)系特征（使用比較廣的標準使用比較廣的標準）3.1 SQL3.1 SQL概述概述5/1562.SQL2.SQL的性質(zhì)的性質(zhì) SQLSQL是關(guān)系數(shù)據(jù)庫語言，提供數(shù)據(jù)的定義、查詢、更新和是關(guān)系數(shù)據(jù)庫語言，提供數(shù)據(jù)的定義、查詢、更新和 控制等功能?？刂频裙δ?。已成為數(shù)據(jù)庫領(lǐng)域中的一個主流語言已成為數(shù)據(jù)庫領(lǐng)域中的一個主流語言。 SQLSQL語言不是一個應(yīng)用程序開發(fā)語言

4、，只提供對數(shù)據(jù)庫的語言不是一個應(yīng)用程序開發(fā)語言，只提供對數(shù)據(jù)庫的 操作能力，不能完成屏幕控制，菜單管理，報表生成等功操作能力，不能完成屏幕控制，菜單管理，報表生成等功 能，但可成為應(yīng)用開發(fā)語言的一部分。能，但可成為應(yīng)用開發(fā)語言的一部分。 大多數(shù)數(shù)據(jù)庫均用大多數(shù)數(shù)據(jù)庫均用SQLSQL作為共同的數(shù)據(jù)存儲語言和標準接作為共同的數(shù)據(jù)存儲語言和標準接 口，使不同的數(shù)據(jù)庫系統(tǒng)之間的互操作有了共同的基礎(chǔ)口，使不同的數(shù)據(jù)庫系統(tǒng)之間的互操作有了共同的基礎(chǔ) 有人把確立有人把確立SQLSQL為關(guān)系數(shù)據(jù)庫語言標準及其后的發(fā)展稱為為關(guān)系數(shù)據(jù)庫語言標準及其后的發(fā)展稱為 一場革命一場革命 SQLSQL對數(shù)據(jù)庫以外的領(lǐng)域也產(chǎn)

5、生了很大影響對數(shù)據(jù)庫以外的領(lǐng)域也產(chǎn)生了很大影響3.1 SQL3.1 SQL概述概述6/1563.SQL3.SQL的特點的特點 綜合統(tǒng)一綜合統(tǒng)一集集DDLDDL，DMLDML，DCLDCL于一體，語言風格統(tǒng)一，操作符統(tǒng)一于一體，語言風格統(tǒng)一，操作符統(tǒng)一 高度非過程化高度非過程化用戶只需提出用戶只需提出“做什么做什么”，無須告訴，無須告訴“怎么做怎么做”，不必了，不必了解存取路徑解存取路徑 面向集合的操作方式面向集合的操作方式一次一集合，操作方式、操作對象、結(jié)果均為集合一次一集合，操作方式、操作對象、結(jié)果均為集合 一種語法結(jié)構(gòu)，兩種使用方式一種語法結(jié)構(gòu)，兩種使用方式SQLSQL既是自含式語言（用戶

6、使用），既是自含式語言（用戶使用），又是嵌入式語言（程序員使用）又是嵌入式語言（程序員使用） 語言簡潔，易學易用：語言簡潔，易學易用：在語言上接近英語在語言上接近英語3.1 SQL3.1 SQL概述概述7/1564.SQL4.SQL對關(guān)系數(shù)據(jù)庫的支持對關(guān)系數(shù)據(jù)庫的支持 即即SQLSQL對象與三級模式結(jié)構(gòu)的對應(yīng)關(guān)系對象與三級模式結(jié)構(gòu)的對應(yīng)關(guān)系3.1 SQL3.1 SQL概述概述存儲文件存儲文件1 1存儲文件存儲文件2 2基本表基本表1 1基本表基本表2 2基本表基本表3 3SQL視圖視圖1 1視圖視圖2 2外模式模式內(nèi)模式關(guān)系模型關(guān)系模型 SQLSQL-外模式外模式-視圖視圖(VIEW)模式模式

7、-基本表基本表(TABLE)內(nèi)模式內(nèi)模式-存儲文件存儲文件_索引索引基本表基本表是本身獨立存在的表，是本身獨立存在的表，一個表帶若干一個表帶若干索引。索引。 一個或多個基本表對應(yīng)一一個或多個基本表對應(yīng)一個個存儲文件存儲文件，它的物理結(jié)，它的物理結(jié)構(gòu)對用戶來說是透明的。構(gòu)對用戶來說是透明的。視圖視圖是從一個或多個基本表是從一個或多個基本表導出的表，不存放數(shù)據(jù)，是導出的表，不存放數(shù)據(jù)，是一個一個虛表虛表（數(shù)據(jù)庫中（數(shù)據(jù)庫中只存放只存放視圖的定義視圖的定義而不存放視圖對而不存放視圖對應(yīng)的數(shù)據(jù)，這些數(shù)據(jù)仍存放應(yīng)的數(shù)據(jù)，這些數(shù)據(jù)仍存放在導出視圖的基本表中，因在導出視圖的基本表中，因此視圖是一個虛表）。此

8、視圖是一個虛表）。視圖視圖上可以再定義視圖。上可以再定義視圖。8/1563.1 SQL3.1 SQL概述概述5.SQL5.SQL的功能的功能SQLSQL設(shè)計巧妙，設(shè)計巧妙，核心功能只需核心功能只需9 9個動詞個動詞。在語言上接近英語。在語言上接近英語SQL功能操作符操作數(shù)據(jù)查詢數(shù)據(jù)查詢 DMLDMLSELECT對基本表、視圖進行對基本表、視圖進行查詢查詢數(shù)據(jù)定義數(shù)據(jù)定義 DDLDDLCREATEALTERDROP創(chuàng)建創(chuàng)建數(shù)據(jù)庫、基本表、視圖、索引數(shù)據(jù)庫、基本表、視圖、索引修改修改數(shù)據(jù)庫、基本表、視圖數(shù)據(jù)庫、基本表、視圖刪除刪除數(shù)據(jù)庫、基本表、視圖、索引數(shù)據(jù)庫、基本表、視圖、索引數(shù)據(jù)操縱數(shù)據(jù)操縱

9、 DMLDMLINSERTUPDATEDELETE在指定表中在指定表中插入插入數(shù)據(jù)（元組）數(shù)據(jù)（元組）在指定表中在指定表中修改修改數(shù)據(jù)數(shù)據(jù)在指定表中在指定表中刪除刪除數(shù)據(jù)數(shù)據(jù)數(shù)據(jù)控制數(shù)據(jù)控制 DCLDCLGRANTREVOKE將指定操作將指定操作權(quán)限授予權(quán)限授予指定用戶指定用戶收回權(quán)限收回權(quán)限9/156第三章第三章 關(guān)系數(shù)據(jù)庫標準語言關(guān)系數(shù)據(jù)庫標準語言SQLSQL3.1 SQL概述3.2 數(shù)據(jù)定義3.3 查詢3.4 數(shù)據(jù)更新3.5 視圖第3章 小結(jié)10/1563.2 3.2 數(shù)據(jù)定義數(shù)據(jù)定義 操作對象操 作 方 式創(chuàng)建刪除修改表CREATE TABLEDROP TABLEAlter Table

10、視圖CREATE VIEWDROP VIEWAlter View索引CREATE INDEXDROP INDEXSQLSQL的數(shù)據(jù)定義功能包括定義表、定義視圖和定義索引。的數(shù)據(jù)定義功能包括定義表、定義視圖和定義索引。表表3.2 SQL3.2 SQL的數(shù)據(jù)定義語句的數(shù)據(jù)定義語句 11/1563.2 3.2 數(shù)據(jù)定義（續(xù)）數(shù)據(jù)定義（續(xù)） 3.2.1 域定義3.2.2 定義基本表3.2.3 修改基本表3.2.4 刪除基本表3.2.5 建立與刪除索引12/156SQLServer數(shù)據(jù)類型說明說明charchar（n n）固定長度的字符串類型固定長度的字符串類型，n n表示字符串的最大長度，取值范圍為表

11、示字符串的最大長度，取值范圍為1 180008000varcharvarchar（n n）可變長度的字符串類型可變長度的字符串類型，n n表示字符串的最大長度，取值范圍為表示字符串的最大長度，取值范圍為1 180008000IntInt4 4字節(jié)，存儲從字節(jié)，存儲從2 23131( (2,147,483,648 ) 2,147,483,648 ) 到到2 23131-1-1 ( 2,147,483,647 ) ( 2,147,483,647 ) 范圍的整數(shù)范圍的整數(shù) smallintsmallint2 2字節(jié)，存儲從字節(jié)，存儲從2 21515( (32,768 ) 32,768 ) 到到2 2

12、1515-1 -1 (32,767 ) (32,767 ) 范圍的整數(shù)范圍的整數(shù)numericnumeric（p,qp,q）或）或decimaldecimal（p,qp,q）定點精度和小數(shù)位數(shù)。使用最大精度時，有效值從定點精度和小數(shù)位數(shù)。使用最大精度時，有效值從 10103838 +1 +1 到到 10103838 -1 -1。其中，。其中，p p為精度為精度，指定小數(shù)點左邊和右邊可以存儲的十，指定小數(shù)點左邊和右邊可以存儲的十進制數(shù)字的最大個數(shù)。進制數(shù)字的最大個數(shù)。q q為小數(shù)位數(shù)為小數(shù)位數(shù)，指定小數(shù)點右邊可以存儲，指定小數(shù)點右邊可以存儲的十進制數(shù)字的最大個數(shù)，的十進制數(shù)字的最大個數(shù)，0 =

13、0 = q q = = p p。q q的默認值為的默認值為0 0floatfloat8 8字節(jié)，存儲從字節(jié)，存儲從1.79E + 308 1.79E + 308 到到 1.79E + 308 1.79E + 308 范圍的浮點型數(shù)范圍的浮點型數(shù)realreal4 4字節(jié)，存儲從字節(jié)，存儲從 3.40E + 38 3.40E + 38 到到 3.40E + 38 3.40E + 38 范圍的浮點范圍的浮點型數(shù)型數(shù)datetimedatetime占用占用8 8字節(jié)空間，存儲從字節(jié)空間，存儲從17531753年年1 1月月1 1日到日到99999999年年1212月月3131日的日期和時間數(shù)日的日期

14、和時間數(shù)據(jù)，精確到百分之三秒（或據(jù)，精確到百分之三秒（或 3.33 3.33 毫秒）毫秒）3.2.1 3.2.1 域定義域定義13/1561. 1.定義語句格式定義語句格式3.2.2 3.2.2 定義基本表定義基本表CREATE TABLE （ ， ， ） ：所要定義的基本表的名字：所要定義的基本表的名字（在整個數(shù)據(jù)庫中必須唯一在整個數(shù)據(jù)庫中必須唯一） ：組成該表的各個屬性（列）：組成該表的各個屬性（列）（列名在表中必須唯一列名在表中必須唯一）CREATE TABLE student(Sno char(9) PRIMARY KEY, -列級完整性約束條件，列級完整性約束條件，SnoSno是主碼

15、是主碼Sname varchar(20) UNIQUE, -Sname-Sname取唯一值取唯一值Ssex char(2),Sage int,Sdept char(20)SQL serverSQL server中的注中的注釋釋問題：問題：SnameSname中能保存多少個漢中能保存多少個漢字或英文字母字或英文字母? ? 表示內(nèi)容為可選的表示內(nèi)容為可選的14/1563.2.2 3.2.2 定義基本表（續(xù)）定義基本表（續(xù)）2. 2.定義完整性約束定義完整性約束為了維護數(shù)據(jù)完整性，為了維護數(shù)據(jù)完整性，DBMSDBMS必須提供一種機制，檢查必須提供一種機制，檢查DBDB 中的數(shù)據(jù)是否滿足語義規(guī)定的條件

16、。中的數(shù)據(jù)是否滿足語義規(guī)定的條件。這些這些加在加在DBDB的數(shù)據(jù)之上的語義約束條件的數(shù)據(jù)之上的語義約束條件就是就是數(shù)據(jù)完整性數(shù)據(jù)完整性 約束條件約束條件，這些條件作為表定義的一部分存儲在數(shù)據(jù)庫，這些條件作為表定義的一部分存儲在數(shù)據(jù)庫 的數(shù)據(jù)字典中。的數(shù)據(jù)字典中。用戶操作數(shù)據(jù)庫中的數(shù)據(jù)時用戶操作數(shù)據(jù)庫中的數(shù)據(jù)時由由DBMSDBMS自動檢查數(shù)據(jù)是否滿自動檢查數(shù)據(jù)是否滿 足完整性約束條件足完整性約束條件。15/1563.2.2 3.2.2 定義基本表（續(xù)）定義基本表（續(xù)）2. 2.定義完整性約束定義完整性約束定義表的同時，可定義與該表有關(guān)的完整性約束條件。定義表的同時，可定義與該表有關(guān)的完整性約束條

17、件。 ：涉及表中一個屬性列的完整性：涉及表中一個屬性列的完整性 約束條件，用于限制該屬性列的取值，如約束條件，用于限制該屬性列的取值，如 （NOT NULL/UNIQCE/DEFAULTNOT NULL/UNIQCE/DEFAULT等）。等）。在定義列時定義。在定義列時定義。 ：涉及表中多個屬性列的完整性：涉及表中多個屬性列的完整性 約束條件，用于限制元組中各屬性列之間的聯(lián)系，如約束條件，用于限制元組中各屬性列之間的聯(lián)系，如 （開始日期（開始日期 結(jié)束日期、發(fā)貨數(shù)量結(jié)束日期、發(fā)貨數(shù)量 0) )CREATE TABLE SC(Sno char(9),Cno char(10),grade smal

18、lint,PRIMARY KEY (Sno,Cno),FOREIGN KEY (Sno) REFERENCES student(Sno), FOREIGN KEY (Cno) REFERENCES course(Cno), CHECK (grade=0 and grade =100) )應(yīng)注意完整性約束的定義1)主碼：PRIMARY KEY關(guān)鍵字。2)外碼：FOREIGN KEY關(guān)鍵字。3)用戶定義：CHECK關(guān)鍵字等。列級完整性約束列級完整性約束表級完整性約束表級完整性約束18/1563.2.2 3.2.2 定義基本表（續(xù)）定義基本表（續(xù)）4. 4.實體完整性約束：用主碼約束（實體完整性約束

19、：用主碼約束（PRIMARY KEY）來保證）來保證每個表只有一個每個表只有一個PRIMARY KEY約束約束PRIMARY KEY約束的列值集合必須是唯一的且不允許有約束的列值集合必須是唯一的且不允許有空值空值CREATE TABLE student(Sno char(9) PRIMARY KEY, -主碼只有主碼只有1 1個屬性個屬性 )CREATE TABLE SC(Sno char(9),Cno char(10),PRIMARY KEY (Sno,Cno), -主碼由多個屬性構(gòu)成主碼由多個屬性構(gòu)成 )19/1563.2.2 3.2.2 定義基本表（續(xù)）定義基本表（續(xù)）5. 5.參照完整

20、性約束：用外碼約束（參照完整性約束：用外碼約束（FOREIGN KEY）來保證）來保證FOREIGN KEY (外碼外碼) REFERENCES (列名列名)外碼必須引用有外碼必須引用有PRIMARY KEY或或UNIQUE約束的列約束的列CREATE TABLE SC(Sno char(9),Cno char(10), FOREIGN KEY (Sno) REFERENCES student(Sno), FOREIGN KEY (Cno) REFERENCES course(Cno), )SnoCnoGrade95011929501286SCsnoSnameSsexSageSdept9501

21、李勇李勇男男20CS9502劉晨劉晨女女19ISStudentCnoCnameCpnoCcredit1數(shù)據(jù)庫數(shù)據(jù)庫542數(shù)學數(shù)學2Course20/1563.2.2 3.2.2 定義基本表（續(xù)）定義基本表（續(xù)）6. 6.用戶定義完整性約束用戶定義完整性約束唯一性約束：唯一性約束：UNIQUE非空值約束：非空值約束：NOT NULL默認值約束：默認值約束：DEFAULT CHECK約束：用于限制列的取值在指定的范圍內(nèi)，或約約束：用于限制列的取值在指定的范圍內(nèi)，或約 束同一個表某個元組中多個列之間的取值束同一個表某個元組中多個列之間的取值 *一個表中可以在一個列或多個列上定義一個表中可以在一個列或

22、多個列上定義UNIQUE *每個列只有一個每個列只有一個DEFAULT約束，允許使用系統(tǒng)提供的約束，允許使用系統(tǒng)提供的 值作為默認值值作為默認值 *執(zhí)行執(zhí)行INSERT語句或語句或UPDATE語句，系統(tǒng)自動檢查語句，系統(tǒng)自動檢查 CHECK約束，只有在向表中插入數(shù)據(jù)時才檢查約束，只有在向表中插入數(shù)據(jù)時才檢查 DEFAULT約束約束21/1566. 6.用戶定義完整性約束用戶定義完整性約束例子例子 說明說明Student表的表的Ssex只能取只能取“男男”、“女女”。 Ssex char(2) CHECK (Ssex IN(男男,女女) 說明說明SC表的表的Grade在在0到到100分之間分之間

23、 CHECK (Grade =0 and Grade oldvalue) )3.2.2 3.2.2 定義基本表（續(xù)）定義基本表（續(xù)）列級完整性約束列級完整性約束表級完整性約束表級完整性約束22/1563.2.3 3.2.3 修改基本表修改基本表ALTER TABLE ADD 完整性約束完整性約束 DROP | ALTER ：要修改的基本表：要修改的基本表 ADD子句子句：增加新列和新的完整性約束條件：增加新列和新的完整性約束條件 DROP子句子句：刪除指定的完整性約束條件或列：刪除指定的完整性約束條件或列 ALTER子句子句：用于修改列名和數(shù)據(jù)類型：用于修改列名和數(shù)據(jù)類型23/156 例例1

24、1 向向Student表增加表增加“入學時間入學時間”列，其數(shù)據(jù)類型為日期型列，其數(shù)據(jù)類型為日期型 ALTER TABLE Student ADD Sentrance DATETIME 不論基本表中原來是否已有數(shù)據(jù)，新增加的列一律為空值。不論基本表中原來是否已有數(shù)據(jù)，新增加的列一律為空值。 例例2 2 刪除列刪除列 ALTER TABLE Student Drop Column Sentrance 例例3 3 將年齡的數(shù)據(jù)類型由整數(shù)改為短整數(shù)。將年齡的數(shù)據(jù)類型由整數(shù)改為短整數(shù)。 ALTER TABLE Student ALTER Sage SmallInt 注：修改原有的列定義有可能會破壞已有

25、數(shù)據(jù)注：修改原有的列定義有可能會破壞已有數(shù)據(jù) 例例4 4 增加課程名稱必須取唯一值的約束條件。增加課程名稱必須取唯一值的約束條件。 ALTER TABLE Course ADD UNIQUE(Cname)3.2.3 3.2.3 修改基本表（續(xù)）修改基本表（續(xù)）24/1563.2.4 3.2.4 刪除基本表刪除基本表DROP TABLE 刪除基本表時，系統(tǒng)會將與該表有關(guān)的所有對象一并刪除基本表時，系統(tǒng)會將與該表有關(guān)的所有對象一并 刪除。刪除。即表中的數(shù)據(jù)、表上的索引都刪除，即表中的數(shù)據(jù)、表上的索引都刪除，表上的視表上的視 圖往往仍然保留，但無法引用。圖往往仍然保留，但無法引用。 例例1111 刪

26、除刪除Student表表 DROP TABLE Student 執(zhí)行刪除基本表的操作一定要格外小心！執(zhí)行刪除基本表的操作一定要格外小心！25/1563.2.5 3.2.5 建立與刪除索引建立與刪除索引 1 索引2 建立索引3 刪除索引26/1561. 1.索引索引建立索引的目的：建立索引的目的：加快查詢速度，提高加快查詢速度，提高數(shù)據(jù)文件訪問效率。數(shù)據(jù)文件訪問效率。 缺點：缺點：索引是有索引是有代價的（時、空）。代價的（時、空）。為了維護索引，對為了維護索引，對數(shù)據(jù)進行插入、更新、刪除操作所花費的時間會更長。數(shù)據(jù)進行插入、更新、刪除操作所花費的時間會更長。在設(shè)計和創(chuàng)建索引時，應(yīng)確保對性能的提高

27、程度大于在存儲在設(shè)計和創(chuàng)建索引時，應(yīng)確保對性能的提高程度大于在存儲空間和處理資源方面的代價?？臻g和處理資源方面的代價。3.2.5 3.2.5 建立與刪除索引（續(xù)）建立與刪除索引（續(xù)） 27/156 Microsoft SQL Server Microsoft SQL Server 支持在表中任何列（包括計算列）支持在表中任何列（包括計算列）上定義的索引。上定義的索引。如果一個表沒有創(chuàng)建索引，則數(shù)據(jù)行不按任何特定的順序如果一個表沒有創(chuàng)建索引，則數(shù)據(jù)行不按任何特定的順序存儲。這種結(jié)構(gòu)稱為存儲。這種結(jié)構(gòu)稱為堆集堆集。SQL Server SQL Server 索引的兩種類型為：索引的兩種類型為： 聚

28、集索引聚集索引 、非聚集索引、非聚集索引維護索引維護索引 DBMSDBMS自動完成自動完成使用索引使用索引 DBMSDBMS自動選擇是否使用索引以及使用哪些索引自動選擇是否使用索引以及使用哪些索引3.2.5 3.2.5 建立與刪除索引（續(xù)）建立與刪除索引（續(xù)） 28/1562. 2.建立索引建立索引 語句格式語句格式CREATE CREATE UNIQUE CLUSTERUNIQUE CLUSTER INDEX INDEX ON ON (, ) )； 用用 指定要建索引的基本表名字指定要建索引的基本表名字 索引可以建立在該表的索引可以建立在該表的一一列列或多列或多列上，各列名之間用逗上，各列名

29、之間用逗號分隔號分隔 用用 指定索引值的排列次序，升序：指定索引值的排列次序，升序：ASCASC，降序：，降序：DESCDESC。缺省值：。缺省值：ASCASC3.2.5 3.2.5 建立與刪除索引（續(xù)）建立與刪除索引（續(xù)） 例例1313 CREATE INDEX stu_sname ON Student(Sname)在在Student表的表的Sname（姓名）列上建立索引。（姓名）列上建立索引。29/156 CLUSTERED(CLUSTERED(聚集索引聚集索引) ) ：數(shù)據(jù)文件中的記錄按照索引鍵指數(shù)據(jù)文件中的記錄按照索引鍵指定的順序排序，使得具有相同索引鍵值的記錄在物理上聚集定的順序排序

30、，使得具有相同索引鍵值的記錄在物理上聚集在一起（在一起（即構(gòu)成一簇即構(gòu)成一簇cc）。）。 即即索引項的順序與表中記錄的物理順序一致索引項的順序與表中記錄的物理順序一致。 一個表只能建立一個聚集索引。一個表只能建立一個聚集索引。3.2.5 3.2.5 建立與刪除索引（續(xù)）建立與刪除索引（續(xù)） 30/156 UNIQUE(單一索引單一索引)：非聚集索引中的特例非聚集索引中的特例 唯一索引唯一索引，不允許存在索引值相同的兩行，不允許存在索引值相同的兩行（相當于增加相當于增加 了一個了一個UNIQUEUNIQUE約束）約束）3.2.5 3.2.5 建立與刪除索引（續(xù)）建立與刪除索引（續(xù)） 31/156

31、3. 3.刪除索引刪除索引DROP INDEX 刪除索引時，系統(tǒng)會從數(shù)據(jù)字典中刪去有關(guān)該索引的刪除索引時，系統(tǒng)會從數(shù)據(jù)字典中刪去有關(guān)該索引的 描述。描述。 例例7 7 刪除刪除Student表的表的stu_sname索引。索引。DROP INDEX stu_sname3.2.5 3.2.5 建立與刪除索引（續(xù)）建立與刪除索引（續(xù)） 32/156第三章第三章 關(guān)系數(shù)據(jù)庫標準語言關(guān)系數(shù)據(jù)庫標準語言SQLSQL3.1 SQL概述3.2 數(shù)據(jù)定義3.3 查詢3.4 數(shù)據(jù)更新3.5 視圖第3章 小結(jié)33/1563.3 3.3 查詢查詢 3.3.1 概述3.3.2 單表查詢3.3.3 連接查詢3.3.4

32、嵌套查詢3.3.5 集合查詢3.3.6 小結(jié) 34/156 查詢功能是查詢功能是SQLSQL語言的核心，是數(shù)據(jù)庫中用得最多的操語言的核心，是數(shù)據(jù)庫中用得最多的操作。作。SQLSQL語言的所有查詢都是利用語言的所有查詢都是利用SELECTSELECT語句完成的。語句完成的。 SELECTSELECT語句的功能：用于檢索和統(tǒng)計數(shù)據(jù)語句的功能：用于檢索和統(tǒng)計數(shù)據(jù) 作用對象：關(guān)系（表）作用對象：關(guān)系（表） 結(jié)果：產(chǎn)生一個新的關(guān)系（表）結(jié)果：產(chǎn)生一個新的關(guān)系（表） 使用方式：交互式、嵌入式使用使用方式：交互式、嵌入式使用3.3.1 3.3.1 概述概述 35/1563.3.1 3.3.1 概述（續(xù)）概述

33、（續(xù)） 1. 1.語句格式：語句格式：SELECT ALL|DISTINCT -需要哪些列需要哪些列FROM ， -來自哪些表來自哪些表 WHERE -根據(jù)什么條件根據(jù)什么條件 GROUP BY HAVING ORDER BY ASC|DESC DISTINCT表示在查詢結(jié)果中去掉重復行，表示在查詢結(jié)果中去掉重復行，ALL缺省缺省 例例1 1 查詢?nèi)w學生的學號與姓名。查詢?nèi)w學生的學號與姓名。SELECT Sno，Sname FROM Student36/1562.SELECT2.SELECT查詢語句，可看成是關(guān)系代數(shù)運算表達式查詢語句，可看成是關(guān)系代數(shù)運算表達式 SELECT子句子句：指定

34、要顯示的屬性列，作投影運算：指定要顯示的屬性列，作投影運算 FROM子句子句：指定查詢對象：指定查詢對象(基本表或視圖基本表或視圖)，多個關(guān)系，多個關(guān)系 時則表示要做笛卡爾積運算時則表示要做笛卡爾積運算 WHERE子句子句：指定查詢條件，做選擇運算：指定查詢條件，做選擇運算 GROUP BY子句子句：對查詢結(jié)果按指定列的值分組，該：對查詢結(jié)果按指定列的值分組，該 屬性列值相等的元組為一個組。通常會在每組中作用屬性列值相等的元組為一個組。通常會在每組中作用 集函數(shù)。集函數(shù)。 HAVING短語短語：篩選出只有滿足指定條件的組：篩選出只有滿足指定條件的組 ORDER BY子句子句：對查詢結(jié)果表按指定

35、列值的升序或：對查詢結(jié)果表按指定列值的升序或 降序排序降序排序 3.3.1 3.3.1 概述（續(xù)）概述（續(xù)） 37/1563.3.2 3.3.2 單表查詢單表查詢 查詢僅涉及一個表，是一種最簡單的查詢操作查詢僅涉及一個表，是一種最簡單的查詢操作1.選擇表中的若干列2.查詢滿足條件的元組3.對查詢結(jié)果排序4.使用集函數(shù)5.對查詢結(jié)果分組 38/1561. 1.選擇表中若干列選擇表中若干列 例例1 1 查詢?nèi)w學生的學號與姓名。查詢?nèi)w學生的學號與姓名。SELECT Sno，SnameFROM Student 例例2 2 查詢?nèi)w學生的姓名、學號、所在系。查詢?nèi)w學生的姓名、學號、所在系。SELE

36、CT Sname，Sno，SdeptFROM Student 例例3 3 查詢?nèi)w學生的詳細記錄。查詢?nèi)w學生的詳細記錄。 SELECT Sno, Sname, Ssex, Sage, Sdept FROM Student 或或 SELECT * * FROM Student；3.3.2 3.3.2 單表查詢（續(xù)）單表查詢（續(xù)） *表示取所有列的信息表示取所有列的信息列名要用逗號分開列名要用逗號分開39/156(1)(1)目標列表達式目標列表達式 不僅可以使用列名，還可以使用算術(shù)表達不僅可以使用列名，還可以使用算術(shù)表達 式、字符串常量和函數(shù)等。式、字符串常量和函數(shù)等。 例例 查全體學生的姓名、

37、出生年份和所有系，要求用小寫字查全體學生的姓名、出生年份和所有系，要求用小寫字 母表示所有系名。母表示所有系名。 SELECT Sname, Year of Birth:, 2005Sage, LOWER(Sdept) FROM Student3.3.2 3.3.2 單表查詢單表查詢 列表達式的顯示結(jié)果均沒有列標題列表達式的顯示結(jié)果均沒有列標題如何為空白列提供一個列標題如何為空白列提供一個列標題? ?表達式函數(shù)常量40/156使用使用列別名列別名改變查詢結(jié)果的列標題改變查詢結(jié)果的列標題 別名要用別名要用空格或空格或AS分開分開例：例： SELECT Sname NAME, Year of Bi

38、rth: AS BIRTH, 2005-Sage AS BIRTHDAY, LOWER(Sdept) AS DEPARTMENT FROM Student3.3.2 3.3.2 單表查詢單表查詢 列的別名列的別名41/156(2)(2)消除取值重復的行消除取值重復的行 在在SELECT子句中使用子句中使用DISTINCT短語短語 例：查詢選修了課程的學生學號。例：查詢選修了課程的學生學號。1) SELECT Sno FROM SC2) SELECT DISTINCT Sno FROM SC3.3.2 3.3.2 單表查詢單表查詢 SC去掉重復元組42/156注意：注意：DISTINCT短語的作

39、用范圍是所有目標列短語的作用范圍是所有目標列例：例：查詢選修課程的各種成績查詢選修課程的各種成績錯誤的寫法錯誤的寫法SELECT DISTINCT Cno，DISTINCT GradeFROM SC正確的寫法正確的寫法 SELECT DISTINCT Cno，Grade FROM SC 3.3.2 3.3.2 單表查詢單表查詢 43/1562. 2.查詢滿足條件的元組查詢滿足條件的元組WHEREWHERE子句常用的查詢條件子句常用的查詢條件查詢條件查詢條件謂謂 詞詞比較比較=，=，=，!=，!，!，NOT+上述比較運算符上述比較運算符確定范圍確定范圍BETWEEN AND，NOT BETWEE

40、N AND確定集合確定集合IN，NOT IN字符匹配字符匹配LIKE，NOT LIKE空值空值IS NULL，IS NOT NULL邏輯運算邏輯運算AND，OR，NOT3.3.2 3.3.2 單表查詢（續(xù)）單表查詢（續(xù)） 44/156(1)(1)比較大小比較大小在在WHERE子句的子句的中使用比較運算符中使用比較運算符 =，=，=，!= 或或 ，!，!， 邏輯運算符邏輯運算符NOT + 比較運算符比較運算符 例例8 8 查詢所有年齡在查詢所有年齡在20歲以下的學生姓名及其年齡。歲以下的學生姓名及其年齡。SELECT Sname, Sage FROM Student WHERE Sage = 2

41、0 * *取反操作的執(zhí)行效率比較低，盡量少用取反操作的執(zhí)行效率比較低，盡量少用45/156(2)(2)確定范圍確定范圍使用謂詞使用謂詞 BETWEEN AND NOT BETWEEN AND 例例1010 查詢年齡在查詢年齡在2023歲（包括歲（包括20歲和歲和23歲）之間的學歲）之間的學生的姓名、系別和年齡。生的姓名、系別和年齡。 SELECT Sname，Sdept，Sage FROM Student WHERE Sage BETWEEN 20 AND 23該查詢等價于下面的查詢 SELECT Sname, Sdept, Sage FROM Student WHERE Sage=20 AN

42、D Sage=2346/156(3)(3)確定集合確定集合 IN是表示某元素是否屬于某集合的謂詞是表示某元素是否屬于某集合的謂詞語法格式語法格式 WHERE NOT IN ：用逗號分隔的一組取值：用逗號分隔的一組取值 例例12 12 查詢信息系（查詢信息系（IS）、數(shù)學系（）、數(shù)學系（MA）和計）和計 算機科學系（算機科學系（CS）學生的姓名和性別。）學生的姓名和性別。 SELECT Sname，Ssex FROM Student WHERE Sdept IN ( IS，MA，CS )snoSnameSsexSageSdept9501李勇李勇男男20CS9502劉晨劉晨女女19IS9503王敏

43、王敏女女18MA9504張立張立男男19IS9505趙陽趙陽女女21GL47/156(4)(4)字符串匹配字符串匹配格式格式1 1：NOT LIKE 含義：含義：查找指定列值與查找指定列值與相匹配的元組。相匹配的元組。其中其中可含：可含：(百分號百分號) ：代表任意長度：代表任意長度(可為可為0)的字符串。的字符串。 _(下橫線下橫線) ： 代表任意單個字符。代表任意單個字符。相當于相當于WindowsWindows、dosdos等中查文件名的等中查文件名的* *和？和？例：例：查所有姓劉或姓王的學生姓名、學號和性別。查所有姓劉或姓王的學生姓名、學號和性別。SELECT Sname,Sno,S

44、sexFROM StudentWHERE Sname LIKE 劉劉 OR Sname LIKE 王王 新的問題：如何表示新的問題：如何表示% %和和_ _本身？本身？用用ESCAPE換碼轉(zhuǎn)義換碼轉(zhuǎn)義邏輯運算符邏輯運算符48/156格式格式2 2： LIKE ESCAPE 若要查的串本身含或若要查的串本身含或_ ，用，用ESCAPE 對對通配符進行轉(zhuǎn)義。通配符進行轉(zhuǎn)義。 例例2020：查查“DB_”開頭，且倒數(shù)第開頭，且倒數(shù)第2個字符為個字符為 i 的課程情況的課程情況SELECT *FROM CourseWHERE cname LIKE DB _ i _ ESCAPE _表示要找內(nèi)容為表示要

45、找內(nèi)容為_表示任意單個字符表示任意單個字符49/156書上用書上用換碼轉(zhuǎn)義換碼轉(zhuǎn)義 不幸，不幸，postgres恰好不能用恰好不能用實際上可以更靈活（常用實際上可以更靈活（常用#,&,!等，甚至用字母都可以?。?。等，甚至用字母都可以?。Ｈ缦旅娴?！轉(zhuǎn)義：如下面的！轉(zhuǎn)義： SELECT c1 FROM tb WHERE c1 LIKE %10-15!% off% ESCAPE ! !%進行換碼轉(zhuǎn)義進行換碼轉(zhuǎn)義50/156(5)(5)涉及空值的查詢涉及空值的查詢 使用謂詞使用謂詞 IS NULL 或或 IS NOT NULL “IS NULL” 不能用不能用 “= NULL” 代替代替 (注意：有

46、的系統(tǒng)允許注意：有的系統(tǒng)允許NULL比較，可以使用和比較，可以使用和，如，如MS SQL Server。有的不允許，如。有的不允許，如Oracle。SQL標準不允標準不允許許) 例例2121 某些學生選修課程后沒有參加考試，所以有選課記錄，某些學生選修課程后沒有參加考試，所以有選課記錄，但沒有考試成績。查詢?nèi)鄙俪煽兊膶W生的學號和相應(yīng)的課程但沒有考試成績。查詢?nèi)鄙俪煽兊膶W生的學號和相應(yīng)的課程號。號。 SELECT Sno，Cno FROM SC WHERE Grade IS NULL51/156(6)(6)復合條件查詢復合條件查詢用邏輯運算符用邏輯運算符AND和和 OR來聯(lián)結(jié)多個查詢條件來聯(lián)結(jié)多

47、個查詢條件 AND的優(yōu)先級高于的優(yōu)先級高于OR 可以用括號改變優(yōu)先級可以用括號改變優(yōu)先級 例例2323 查詢計算機系年齡在查詢計算機系年齡在20歲以下的學生姓名。歲以下的學生姓名。 SELECT Sname FROM Student WHERE Sdept= CS AND Sage=90 成績?yōu)槌煽優(yōu)镹ULL的學生的學號并不出現(xiàn)在結(jié)果中。的學生的學號并不出現(xiàn)在結(jié)果中。3.3.2 3.3.2 單表查詢（續(xù)）單表查詢（續(xù)） 55/1564. 4.使用集函數(shù)使用集函數(shù)為了進一步方便用戶，增強檢索功能，為了進一步方便用戶，增強檢索功能，SQL提供了許多集函提供了許多集函數(shù)，主要包括：數(shù)，主要包括： C

48、OUNT(DISTINCT|ALL *) 統(tǒng)計統(tǒng)計元組個數(shù)元組個數(shù) COUNT(DISTINCT|ALL ) 統(tǒng)計統(tǒng)計指定列指定列非空非空的的記錄個數(shù)記錄個數(shù)。 SUM(DISTINCT|ALL ) 計算一列值的計算一列值的總和總和（此（此列必須是數(shù)值型）列必須是數(shù)值型） AVG(DISTINCT|ALL ) 計算一列值的計算一列值的平均值平均值（此列必須是數(shù)值型）（此列必須是數(shù)值型） MAX(DISTINCT|ALL ) 求一列值中的求一列值中的最大值最大值 MIN(DISTINCT|ALL ) 求一列值中的求一列值中的最小值最小值 3.3.2 3.3.2 單表查詢（續(xù)）單表查詢（續(xù)） 56

49、/156使用集函數(shù)（續(xù)）使用集函數(shù)（續(xù)） DISTINCTDISTINCT短語：短語：在計算時要取消指定列中的重復值在計算時要取消指定列中的重復值A(chǔ)LLALL短語：短語：不取消重復值不取消重復值A(chǔ)LLALL為缺省值為缺省值 例例2626 查詢學生總?cè)藬?shù)。查詢學生總?cè)藬?shù)。 SELECT COUNT(*) FROM Student； 例例2727 查詢選修了課程的學生人數(shù)。查詢選修了課程的學生人數(shù)。 SELECT COUNT(DISTINCT Sno) FROM SC；注：注：學生每選修一門課，在學生每選修一門課，在SC中都有一條相應(yīng)的記錄，而一中都有一條相應(yīng)的記錄，而一個學生一般都要選修多門課程

50、，為避免重復計算學生人數(shù)，必個學生一般都要選修多門課程，為避免重復計算學生人數(shù)，必須在須在COUNT函數(shù)中用函數(shù)中用DISTINCT短語。短語。57/156使用集函數(shù)（續(xù)）使用集函數(shù)（續(xù)） 例例2828 計算計算1號課程的學生平均成績。號課程的學生平均成績。 SELECT AVG(Grade) FROM SC WHERE Cno= 1 ； 例例2929 查詢選修查詢選修1號課程的學生最高分數(shù)。號課程的學生最高分數(shù)。 SELECT MAX(Grade) FROM SC WHER Cno= 1 ； 除除count(*)外，外，NULL值均被聚集函數(shù)所忽略。值均被聚集函數(shù)所忽略。58/1565. 5

51、.對查詢結(jié)果分組對查詢結(jié)果分組 GROUP BY子句可以將查詢結(jié)果表的各行按一列或多子句可以將查詢結(jié)果表的各行按一列或多列取值相等的原則進行分組。列取值相等的原則進行分組。 對查詢結(jié)果分組的目的是為了細化集函數(shù)的作用對象。對查詢結(jié)果分組的目的是為了細化集函數(shù)的作用對象。如果未對查詢結(jié)果分組，集函數(shù)將作用于整個查詢結(jié)如果未對查詢結(jié)果分組，集函數(shù)將作用于整個查詢結(jié)果，即整個查詢結(jié)果只有一個函數(shù)值。否則，集函數(shù)果，即整個查詢結(jié)果只有一個函數(shù)值。否則，集函數(shù)將作用于每一個組，即每一組都有一個函數(shù)值。將作用于每一個組，即每一組都有一個函數(shù)值。 如例如例28中中 SELECT AVG(Grade) FRO

52、M SC WHERE Cno= 1 表示課程表示課程1的平均分，若要的平均分，若要同時查同時查 每門課程的平均分？每門課程的平均分？3.3.2 3.3.2 單表查詢（續(xù)）單表查詢（續(xù)） 59/156使用使用GROUP BYGROUP BY子句分組子句分組注：注：SQL規(guī)定，所有帶有規(guī)定，所有帶有NULL值的記錄在分組時被作為值的記錄在分組時被作為一組。一組。 例例3030 求各門課程的平均分數(shù)。求各門課程的平均分數(shù)。 SELECT Cno，AVG(Grade) as Avg_Grage FROM SC GROUP BY Cno； 一般來說，分組查詢的一般來說，分組查詢的SELECT目標列中只允

53、許出現(xiàn)目標列中只允許出現(xiàn)聚集函數(shù)和聚集函數(shù)和GROUP BY子句子句中出現(xiàn)過的列中出現(xiàn)過的列60/156注：注：使用使用HAVING短語篩選最終輸出結(jié)果短語篩選最終輸出結(jié)果 例例3131 查詢選修了查詢選修了3門以上課程的學生學號。門以上課程的學生學號。 SELECT Sno FROM SC GROUP BY Sno HAVING COUNT(*) 3； HAVING條件針對的是條件針對的是GROUP分組，分組，WHERE針對的是元組，針對的是元組，兩者并不矛盾。兩者并不矛盾。 使用使用GROUP BYGROUP BY子句分組子句分組只有滿足只有滿足HAVING短語指短語指定條件的組才輸出定條

54、件的組才輸出61/156 例例3232 查詢有查詢有3門以上課程是門以上課程是90分以上的分以上的 學生的學號及（學生的學號及（90分以上的）課程數(shù)分以上的）課程數(shù) SELECT Sno, COUNT(*) FROM SC WHERE Grade=90 -成績?yōu)槌煽優(yōu)?0分以上的課程分以上的課程 GROUP BY Sno HAVING COUNT(*)=3; -有有3門這樣的課程門這樣的課程 HAVING短語與短語與WHERE子句的區(qū)別：作用對象不同子句的區(qū)別：作用對象不同 WHERE子句作用于基表或視圖，從中選擇滿足條件的元子句作用于基表或視圖，從中選擇滿足條件的元組。組。 HAVING短語

55、作用于組，從中選擇滿足條件的組。短語作用于組，從中選擇滿足條件的組。使用使用GROUP BYGROUP BY子句分組子句分組62/1563.3.3 3.3.3 連接查詢連接查詢 同時涉及多個表的查詢稱為同時涉及多個表的查詢稱為連接查詢連接查詢學號學號課程號課程號成績成績9501192950128695021729503290學號學號學生學生性別性別年齡年齡班號班號9501李勇李勇男男241036019502劉晨劉晨女女251036019503王名王名男男231037019504張立張立女女24103702學號學號學生學生性別性別課程名課程名成績成績9501李勇李勇男男數(shù)據(jù)庫數(shù)據(jù)庫929501李

56、勇李勇男男高數(shù)高數(shù)869502劉晨劉晨女女數(shù)據(jù)庫數(shù)據(jù)庫729503王名王名男男高數(shù)高數(shù)90SCStudentCourse連接查詢連接查詢CnoCnameCpnoCcredit1數(shù)據(jù)庫數(shù)據(jù)庫542數(shù)學數(shù)學23信息系統(tǒng)信息系統(tǒng)144操作系統(tǒng)操作系統(tǒng)635數(shù)據(jù)結(jié)構(gòu)數(shù)據(jù)結(jié)構(gòu)746數(shù)據(jù)處理數(shù)據(jù)處理27C語言語言6463/156格式格式1 1（SQL-89SQL-89）：）： SELECT 列表達式列表達式1，列表達式，列表達式2，列表達式，列表達式n FROM 表表1，表，表2 WHERE AND 3.3.3 3.3.3 連接查詢（續(xù)）連接查詢（續(xù)） 格式格式2 2（SQL-92SQL-92）：）： S

57、ELECT 列表達式列表達式1，列表達式，列表達式2，列表達式，列表達式n FROM 表表1 INNER/OUTER/CROSS JOIN 表表2 ON WHERE 64/156SQLSQL中連接查詢中的主要類型：中連接查詢中的主要類型：1.內(nèi)連接 1)等值連接 2)非等值連接 3)自身連接 2.外連接3.廣義笛卡爾積4.多表連接查詢3.3.3 3.3.3 連接查詢（續(xù)）連接查詢（續(xù)） 65/1563.3.3 3.3.3 連接查詢（續(xù)）連接查詢（續(xù)） 1. 1.內(nèi)連接內(nèi)連接 是一種常用的連接類型。如果兩個表的相關(guān)字段滿足連是一種常用的連接類型。如果兩個表的相關(guān)字段滿足連 接條件，則接條件，則從

58、兩個表中提取數(shù)據(jù)并組合成新的記錄從兩個表中提取數(shù)據(jù)并組合成新的記錄。 用來連接兩個表的條件稱為用來連接兩個表的條件稱為連接條件或連接謂詞連接條件或連接謂詞，其一，其一 般格式為：般格式為：. . 比較運算符為比較運算符為“=”的連接操作稱為的連接操作稱為等值連接等值連接，反之稱為，反之稱為 非等值連接（非等值連接（比較運算符：比較運算符： 、 =、=、!=!= ）。66/156. .連接條件中的列名稱為連接條件中的列名稱為連接字段連接字段。連接條件中的各。連接條件中的各連接字段連接字段類型必須是可比類型必須是可比的，但不必是相同的。的，但不必是相同的。 例如，可以都是字符型，或都是日期型；也可

59、以一個例如，可以都是字符型，或都是日期型；也可以一個 是整型，另一個是實型，整型和實型都是數(shù)值型，因是整型，另一個是實型，整型和實型都是數(shù)值型，因 此是可比的。此是可比的。 但若一個是字符型，另一個是整數(shù)型就不允許了，因但若一個是字符型，另一個是整數(shù)型就不允許了，因 為它們是不可比的類型。為它們是不可比的類型。 若連接條件中引用表名若連接條件中引用表名1 1和表名和表名2 2的的同名屬性時同名屬性時，都，都必必 須加上表名前綴須加上表名前綴。若引用唯一屬性名時也可省略表名。若引用唯一屬性名時也可省略表名 前綴。前綴。3.3.3 3.3.3 連接查詢（續(xù)）連接查詢（續(xù)） 67/156DBMS執(zhí)行

60、連接操作的過程：執(zhí)行連接操作的過程： 首先在表首先在表1中找到第一個元組，然后從頭開始順序掃描或按索中找到第一個元組，然后從頭開始順序掃描或按索引掃描表引掃描表2，查找滿足連接條件的元組，每找到一個元組，查找滿足連接條件的元組，每找到一個元組, 就就將表將表1中的第一個元組與該元組拼接起來，形成中的第一個元組與該元組拼接起來，形成結(jié)果表結(jié)果表中一個中一個元組。元組。 表表2全部掃描完畢后，再到表全部掃描完畢后，再到表1中找第二個元組，然后再從頭中找第二個元組，然后再從頭開始順序掃描或按索引掃描表開始順序掃描或按索引掃描表2，查找滿足連接條件的元組，查找滿足連接條件的元組，每找到一個元組每找到一