第3章 關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL.ppt

1、第三章 關(guān)系數(shù)據(jù)庫的標(biāo)準(zhǔn)語言SQL,3.1 SQL概述 3.2 架構(gòu)（SCHEMA） 3.3 表 3.4 索引 3.5 數(shù)據(jù)查詢 3.6 數(shù)據(jù)更新 3.7 視圖 3.8 Transact-SQL語言,3.1 SQL概述,SQL語言及其標(biāo)準(zhǔn) 20世紀(jì)70年代中期，IBM公司在研制System-R RDBMS的過程中，開發(fā)了世界上最早的SQL語言，后來在許多數(shù)據(jù)庫系統(tǒng)中被使用，由于其廣泛的使用，出現(xiàn)標(biāo)準(zhǔn)化需求，形成SQL標(biāo)準(zhǔn)。 SQL-86 SQL-89 SQL-92(SQL2) SQL:1999(SQL3) SQL2003,SQL語言特點(diǎn),SQL的特點(diǎn) 綜合統(tǒng)一 高度非過程化 面向集合的操作方式

2、 以同一種語法結(jié)構(gòu)提供兩種使用方法 語言簡潔，易學(xué)易用,5. 語言簡捷，易學(xué)易用,3.2 架構(gòu)（SCHEMA）,CREATE SCHEMA AUTHORIZATION CREATE SCHEME ST AUTHORIZATION WANG DROP SCHEME DROP SCHEME ZHANG CSCADE,SQL Server2005中的架構(gòu),架構(gòu)（Schema）：數(shù)據(jù)庫架構(gòu)是一個獨(dú)立于數(shù)據(jù)庫用戶的非重復(fù)命名空間，您可以將架構(gòu)視為對象的容器- MSDN 命名空間名其實(shí)就是文件夾名 一個對象只能屬于一個架構(gòu)，就像一個文件只能存放于一個文件夾中一樣。與文件夾不同的是，架構(gòu)是不能嵌套的。因此，

3、我們要訪問一個數(shù)據(jù)庫對象的時(shí)候，通常應(yīng)該是引用它的全名-架構(gòu)名. 對象名。,默認(rèn)架構(gòu),每個登陸用戶都有一個default schema，當(dāng)數(shù)據(jù)庫對象無前綴時(shí)，sql server自動加上默認(rèn)架構(gòu)。 如果引用的數(shù)據(jù)庫對象不屬于default schema時(shí)，將會提示數(shù)據(jù)庫對象無效。 可以修改用戶的默認(rèn)架構(gòu)：ALTER USER dbo WITH DEFAULT_SCHEMA =emdbuser； 可以改變此表的schema,相當(dāng)于把這個表放到另一個文件夾 alter schema dbo TRANSFER emdbuser.Borrower,架構(gòu)與表,要為每一個基本表指定所屬模式，可通過以下方式

4、： 在表名中顯式地給出模式名； 在創(chuàng)建模式時(shí)創(chuàng)建表； 設(shè)置所屬的模式,3.3基本表的定義、刪除和修改,CREATE TABLE （ ， ）； ：所要定義的基本表的名字 ：組成該表的各個屬性（列） ：僅涉及單列的完整性約束條件 ：涉及一個或多個屬性列的完整性約束條件,定義基本表（續(xù)）,常用完整性約束 主碼約束： PRIMARY KEY 唯一性約束：UNIQUE 非空值約束：NOT NULL 檢查約束： CHECK 參照完整性約束:FOREIGN KEY(屬性名) REFERENCES 表名(屬性名) PRIMARY KEY與 UNIQUE的區(qū)別？,SQL Server2005數(shù)據(jù)類型,二進(jìn)制數(shù)據(jù)

5、類型 字符數(shù)據(jù)類型 Unicode 數(shù)據(jù)類型 日期和時(shí)間數(shù)據(jù)類型 數(shù)值數(shù)據(jù)類型 貨幣數(shù)據(jù)類型 特殊數(shù)據(jù)類型,二進(jìn)制數(shù)據(jù)類型,Binary(N) 是 n（ 1 到 8000 ） 位固定的二進(jìn)制數(shù)據(jù)。 Varbinary(N) 是 n （ 1 到 8000 ）位變長度的二進(jìn)制數(shù)據(jù)。 Image 數(shù)據(jù)類型中存儲的數(shù)據(jù)是以位字符串存儲的，不是由 SQL Server 解釋的，必須由應(yīng)用程序來解釋。,字符型數(shù)據(jù)類型,Char(n) 定長字符數(shù)據(jù)，其長度最多為 8KB。 VarChar(n) 是變長字符數(shù)據(jù)，其長度最多為 8KB。 Text數(shù)據(jù)類型，可用來存儲超過8KB的ASCLL數(shù)據(jù)。,Unicode

6、數(shù)據(jù)類型,它為每種語言中的每個字符設(shè)定了統(tǒng)一并且唯一的二進(jìn)制編碼，以滿足跨語言、跨平臺進(jìn)行文本轉(zhuǎn)換、處理的要求。 Unicode 數(shù)據(jù)類型包括 Nchar,Nvarchar 和Ntext。 使用Unicode 數(shù)據(jù)類型，所占用的存儲空間是使用非 Unicode 數(shù)據(jù)類型所占用空間的兩倍。,日期和時(shí)間數(shù)據(jù)類型,Date time所存儲的日期范圍是從 1753 年 1 月 1 日開始，到9999 年12月31日結(jié)束，每一個值要求 8 個存儲字節(jié)。 Smalldatetime所存儲的日期范圍是 1900年1月1日開始，到 2079 年 12 月 31 日結(jié)束，每一個值要求 4 個存儲字節(jié)。 日期格式

7、包括 MDY、DMY、YMD、YDM、MYD 和 DYM。在默認(rèn)情況下，日期格式為 MDY，可通過Set DateFormat改變。,數(shù)值數(shù)據(jù)類型,整數(shù)數(shù)據(jù)類型：int、smallint、tinyint分別用4、2、1個字節(jié)存儲整數(shù)。 精確小數(shù)Decimal（n,m)和Numberic (n,m),占用存儲空間由數(shù)據(jù)位數(shù)確定。 近似小數(shù)real和float,貨幣數(shù)據(jù)類型,Money 數(shù)據(jù)類型要求 8 個存儲字節(jié) Smallmoney 數(shù)據(jù)類型要求 4 個存儲字節(jié) 兩者都帶有四位小數(shù) 貨幣數(shù)據(jù)不需要用單引號 () 引起來。雖然可以指定前面帶有貨幣符號的貨幣值，但 SQL Server 不存儲任何

8、與符號關(guān)聯(lián)的貨幣信息，它只存儲數(shù)值。,特殊數(shù)據(jù)類型,Timestamp 用于表示SQL Server 活動的先后順序，與插入數(shù)據(jù)或者日期和時(shí)間沒有關(guān)系。 uniqueidentifier此類型數(shù)據(jù)存儲二進(jìn)制值，其作用與全局唯一標(biāo)識符(GUID)一樣。GUID 主要用于在有多個節(jié)點(diǎn)、多臺計(jì)算機(jī)的網(wǎng)絡(luò)中，分配必須具有唯一性的標(biāo)識符。占用16bytes存儲空間。 Bit由1或者0組成。當(dāng)表示真或者假、ON 或者 OFF 時(shí)，使用 Bit 數(shù)據(jù)類型。,例1 建立一個“學(xué)生”表Student，它由學(xué)號Sno、姓名Sname、性別Ssex、年齡Sage、所在系Sdept五個屬性組成。其中學(xué)號不能為空，值是

9、唯一的，并且姓名取值也唯一。 CREATE TABLE Student (Sno CHAR(5) NOT NULL UNIQUE， Sname CHAR(20) UNIQUE， Ssex CHAR(1) ， Sage INT， Sdept CHAR(15)；,例2 建立一個“學(xué)生選課”表SC，它由學(xué)號Sno、課程號Cno，修課成績Score組成，其中(Sno, Cno)為主碼,Sno為外碼參照student.sno,成績在【0100】。 CREATE TABLE SC( Sno CHAR(5) , Cno CHAR(3) , Score int, CONSTRAINT grd_chk CHEC

10、K(Score between 0 and 100) Primary key (Sno, Cno)， CONSTRAINT frk_Sno FOREIGN KEY(Sno) REFERENCE student(Sno) ON DELETE CSCADE ON UPDATE CSCADE;,二、修改基本表,ALTER TABLE ADD 完整性約束 DROP | ALTER ； ：要修改的基本表 ADD子句：增加新列和新的完整性約束條件 DROP子句：刪除指定的完整性約束條件或字段 ALTER子句：用于修改列名和數(shù)據(jù)類型,向Student表增加“入學(xué)時(shí)間”列，其數(shù)據(jù)類型為日期型。 ALTER T

11、ABLE Student ADD Scome DATE； 不論基本表中原來是否已有數(shù)據(jù)，新增加的列一律為空值。 刪除屬性列 ALTER TABLE Student DROP COLUMN Scome； 將年齡的數(shù)據(jù)類型改為半字長整數(shù)。 ALTER TABLE Student ALTER Sage SMALLINT； 刪除學(xué)生姓名必須取唯一值的約束。 ALTER TABLE Student DROP UNIQUE(Sname)；,三、刪除基本表,DROP TABLE ; 基本表定義一旦刪除，表中的數(shù)據(jù)、此表上建立的索引和視圖都將自動被刪除掉。但是有的系統(tǒng)，如Oracle則將視圖定義保留在數(shù)據(jù)字典

12、中。但是用戶引用時(shí)就報(bào)錯。 刪除Student表 DROP TABLEStudent ;,3.4 索引,建立索引是加快查詢速度的有效手段 建立索引 DBA或表的屬主（即建立表的人）根據(jù)需要建立 SQL SERVER自動建立以下列上的索引 PRIMARY KEY UNIQUE 維護(hù)索引 DBMS自動完成 使用索引 DBMS自動選擇是否使用索引以及使用哪些索引,一、建立索引,語句格式 CREATE UNIQUE CLUSTER INDEX ON (, )； 用指定要建索引的基本表名字 索引可以建立在該表的一列或多列上，各列名之間用逗號分隔 用指定索引值的排列次序，升序：ASC，降序：DESC。缺省

13、值：ASC UNIQUE表明此索引的每一個索引值只對應(yīng)唯一的數(shù)據(jù)記錄 CLUSTER表示要建立的索引是聚簇索引,唯一值索引,對于已含重復(fù)值的屬性列不能建UNIQUE索引 對某個列建立UNIQUE索引后，插入新記錄時(shí)DBMS會自動檢查新記錄在該列上是否取了重復(fù)值。這相當(dāng)于增加了一個UNIQUE約束,例題,例6 為學(xué)生-課程數(shù)據(jù)庫中的Student，Course，SC三個表建立索引。其中Student表按學(xué)號升序建唯一索引，Course表按課程號升序建唯一索引，SC表按學(xué)號升序和課程號降序建唯一索引。 CREATE UNIQUE INDEX Stusno ON Student(Sno)； CREA

14、TE UNIQUE INDEX Coucno ON Course(Cno)； CREATE UNIQUE INDEX SCno ON SC(Sno ASC，Cno DESC)；,聚簇索引,建立聚簇索引后，基表中數(shù)據(jù)也需要按指定的聚簇屬性值的升序或降序存放。也即聚簇索引的索引項(xiàng)順序與表中記錄的物理順序一致 例： CREATE CLUSTER INDEX Stusname ON Student(Sname)； 在Student表的Sname（姓名）列上建立一個聚簇索引，而 且Student表中的記錄將按照Sname值的升序存放,聚簇索引（續(xù)）,在一個基本表上最多只能建立一個聚簇索引 聚簇索引的用途

15、：對于某些類型的查詢，可以提高查詢效率 聚簇索引的適用范圍 很少對基表進(jìn)行增刪操作 很少對其中的變長列進(jìn)行修改操作,索引與聚簇索引,索引相當(dāng)于漢語字典里的索引。如果想在字典里查一個字，一頁一頁的翻非常慢，但如果使用字典的索引（拼音、偏旁、筆畫）就可以很快查到。 字典里有幾種類型的索引（拼音、偏旁、筆畫），字典本身也是有序的。一般按拼音排序，這里拼音就是聚簇索引。也就是說聚簇索引的組織順序和數(shù)據(jù)本身的組織順序是一致的。聚簇索引也只有一個，因?yàn)閿?shù)據(jù)只能按一種方式排序。,聚簇索引與非聚簇索引,聚簇索引的好處是執(zhí)行查找一批數(shù)據(jù)比較快，因?yàn)閿?shù)據(jù)已經(jīng)按聚簇索引排好序了，很少io操作就可以從數(shù)據(jù)庫中取出。例

16、如需要查找從a到c的漢字，只需查找a的開始頁和c的結(jié)束頁，不用一頁頁查找。 非聚簇索引組織順序與數(shù)據(jù)組織順序不一致。當(dāng)查找一條數(shù)據(jù)時(shí)其與非聚簇索引的效率差別不大，但查找一批數(shù)據(jù)時(shí)，非聚簇索引的IO可能是聚簇索引的n倍，因?yàn)榉蔷鄞厮饕枰粭l一條查找。,組合索引,組合索引在索引的key中保存了所有組成該組合索引的字段，但只按第一個字段進(jìn)行排序。 Key中保存的內(nèi)容多，一般需要更大空間。 好處時(shí)如果查詢所需要返回的數(shù)據(jù)字段都在組合索引的字段中，數(shù)據(jù)庫將不需訪問數(shù)據(jù)頁，而直接返回索引中的字段值，可以加快查詢速度。假如查詢不滿足這個條件，組合索引就沒有意義，反而浪費(fèi)了存儲空間。,使用索引的原則,1、在

17、需要經(jīng)常搜索的列上創(chuàng)建索引 2、經(jīng)常用于連接的列上創(chuàng)建索引 3、經(jīng)常需要根據(jù)范圍進(jìn)行搜索的列上創(chuàng)建索引 4、經(jīng)常需要排序的列上創(chuàng)建索引 5、經(jīng)常用于where子句的列上創(chuàng)建索引 不創(chuàng)建索引的原則： 1、查詢很少使用和參考的列不建索引 2、對只有少數(shù)值的列不建索引 3、定義為text、image、bit的列不建索引 4、當(dāng)需要update性能遠(yuǎn)遠(yuǎn)高于select性能時(shí)不應(yīng)建索,二、刪除索引,DROP INDEX ； 刪除索引時(shí)，系統(tǒng)會從數(shù)據(jù)字典中刪去有關(guān)該索引的描述。 例7 刪除Student表的Stusname索引。 DROP INDEX Stusname；,3.5 查 詢,語句格式 SELE

18、CT ALL|DISTINCT ， FROM ， WHERE GROUP BY HAVING ORDER BY ASC|DESC ；,語句格式,SELECT子句：指定要顯示的屬性列 FROM子句：指定查詢對象(基本表或視圖) WHERE子句：指定查詢條件 GROUP BY子句：對查詢結(jié)果按指定列的值分組，該屬性列值相等的元組為一個組。通常會在每組中作用集函數(shù)。 HAVING短語：篩選出只有滿足指定條件的組 ORDER BY子句：對查詢結(jié)果表按指定列值的升序或降序排序,3.5.2 單表查詢,查詢僅涉及一個表，是一種最簡單的查詢操作 一、選擇表中的若干列 二、選擇表中的若干元組 三、對查詢結(jié)果排序

19、 四、使用集函數(shù) 五、對查詢結(jié)果分組,查詢指定列,例1 查詢?nèi)w學(xué)生的學(xué)號與姓名。 SELECT Sno，Sname FROM Student； 例2 查詢?nèi)w學(xué)生的姓名、學(xué)號、所在系。 SELECT Sname，Sno，Sdept FROM Student； 例3 查詢?nèi)w學(xué)生的詳細(xì)記錄。 SELECT Sno，Sname，Ssex，Sage，Sdept FROM Student； 或 SELECT * FROM Student；,3. 查詢經(jīng)過計(jì)算的值,SELECT子句的為表達(dá)式 算術(shù)表達(dá)式 字符串常量 函數(shù) 列別名,3. 查詢經(jīng)過計(jì)算的值,例4 查全體學(xué)生的姓名及其出生年份。 SELEC

20、T Sname，2010-Sage FROM Student； 例5 查詢?nèi)w學(xué)生的姓名、出生年份和所有系，要求用小寫字母表示所有系名。 SELECT Sname，Year of Birth: ，2000-Sage ISLOWER(Sdept) FROM Student； 使用列別名改變查詢結(jié)果的列標(biāo)題 SELECT Sname NAME，Year of Birth: BIRTH， 2010-Sage BIRTHDAY，ISLOWER(Sdept) DEPARTMENT FROM Student；,ALL 與 DISTINCT,例6 查詢選修了課程的學(xué)生學(xué)號。 (1) SELECT Sno F

21、ROM SC; 或(默認(rèn) ALL) SELECT ALL Sno FROM SC; (2) SELECT DISTINCT Sno FROM SC;,例題（續(xù)）,注意 DISTINCT短語的作用范圍是所有目標(biāo)列 例：查詢選修課程的各種成績 錯誤的寫法 SELECT DISTINCT Cno，DISTINCT Score FROM SC; 正確的寫法 SELECT DISTINCT Cno，Score FROM SC;,2.查詢滿足條件的元組,WHERE子句常用的查詢條件,(1) 比較大小,在WHERE子句的中使用比較運(yùn)算符 =，=，!，!= 20；,(2) 確定范圍,使用謂詞 BETWEEN

22、AND NOT BETWEEN AND 例10 查詢年齡在/不在2023歲（包括20歲和23歲）之間的學(xué)生的姓名、系別和年齡。 SELECT Sname，Sdept，Sage FROM Student WHERE Sage NOT BETWEEN 20 AND 23；,(3) 確定集合,使用謂詞 IN , NOT IN ：用逗號分隔的一組取值 例12查詢信息系（IS）、數(shù)學(xué)系（MA）和計(jì)算機(jī)科學(xué)系（CS）學(xué)生的姓名和性別。 SELECT Sname，Ssex FROM Student WHERE Sdept IN ( IS，MA，CS );,(4) 字符串匹配,NOT LIKE ESCAPE

23、：指定匹配模板 匹配模板：固定字符串或含通配符的字符串 當(dāng)匹配模板為固定字符串時(shí)， 可以用 = 運(yùn)算符取代 LIKE 謂詞 用 != 或 運(yùn)算符取代 NOT LIKE 謂詞,通配符,% (百分號) 代表任意長度（長度可以為0）的字符串 例：a%b表示以a開頭，以b結(jié)尾的任意長度的字符串。如acb，addgb，ab 等都滿足該匹配串 _ (下橫線) 代表任意單個字符 例：a_b表示以a開頭，以b結(jié)尾的長度為3的任意字符串。如acb，afb等都滿足該匹配串 當(dāng)用戶要查詢的字符串本身就含有 % 或 _ 時(shí)，要使用ESCAPE 短語對通配符進(jìn)行轉(zhuǎn)義。,例題,1) 匹配模板為固定字符串 例14 查詢學(xué)號

24、為95001的學(xué)生的詳細(xì)情況。 SELECT * FROM Student WHERE Sno LIKE 95001； 等價(jià)于： SELECT * FROM Student WHERE Sno = 95001；,匹配模板為含通配符的字符串,例15 查詢所有姓劉學(xué)生的姓名、學(xué)號和性別。 SELECT Sname，Sno，Ssex FROM Student WHERE Sname LIKE 劉%； 例16 查詢姓歐陽且全名為三個漢字的學(xué)生的姓名。 SELECT Sname FROM Student WHERE Sname LIKE 歐陽_； 例17 查詢名字中第2個字為陽字的學(xué)生的姓名和學(xué)號。 S

25、ELECT Sname，Sno FROM Student WHERE Sname LIKE _陽%； 例18 查詢所有不姓劉的學(xué)生姓名。 SELECT Sname，Sno，Ssex FROM Student WHERE Sname NOT LIKE 劉%；,使用轉(zhuǎn)義字符將通配符轉(zhuǎn)義為普通字符,例19 查詢DB_Design課程的課程號和學(xué)分。 SELECT Cno，Ccredit FROM Course WHERE Cname LIKE DB_Design ESCAPE 例20 查詢以DB_開頭，且倒數(shù)第3個字符為 i的課程的詳細(xì)情況。 SELECT * FROM Course WHERE C

26、name LIKE DB_%i_ _ ESCAPE ；,(5) 涉及空值的查詢,使用謂詞 IS NULL 或 IS NOT NULL “IS NULL” 不能用 “= NULL” 代替 例21 某些學(xué)生選修課程后沒有參加考試，所以有選課記錄，但沒有考試成績。查詢?nèi)鄙俪煽兊膶W(xué)生的學(xué)號和相應(yīng)的課程號。 SELECT Sno，Cno FROM SC WHERE Score IS NULL； 例22 查所有有成績的學(xué)生學(xué)號和課程號。 SELECT Sno，Cno FROM SC WHERE Score IS NOT NULL；,(6) 多重條件查詢,用邏輯運(yùn)算符AND和 OR來聯(lián)結(jié)多個查詢條件 AND

27、的優(yōu)先級高于OR 可以用括號改變優(yōu)先級 可用來實(shí)現(xiàn)多種其他謂詞 NOT IN NOT BETWEEN AND 例23 查詢計(jì)算機(jī)系年齡在20歲以下的學(xué)生姓名。 SELECT Sname FROM Student WHERE Sdept= CS AND Sage20；,改寫例12,例12 查詢信息系（IS）、數(shù)學(xué)系（MA）和計(jì)算機(jī)科學(xué)系（CS）學(xué)生的姓名和性別。 SELECT Sname，Ssex FROM Student WHERE Sdept IN ( IS，MA，CS ) 可改寫為： SELECT Sname，Ssex FROM Student WHERE Sdept= IS OR Sde

28、pt= MA OR Sdept= CS ；,改寫例10,例10 查詢年齡在2023歲（包括20歲和23歲）之間的學(xué)生的姓名、系別和年齡。 SELECT Sname，Sdept，Sage FROM Student WHERE Sage BETWEEN 20 AND 23； 或WHERE Sage=20 AND Sage=23；,三、對查詢結(jié)果排序,使用ORDER BY子句 可以按一個或多個屬性列排序 升序：ASC；降序：DESC；缺省值為升序 當(dāng)排序列含空值時(shí) ASC：排序列為空值的元組最后顯示 DESC：排序列為空值的元組最先顯示,排序?qū)嵗?例24查詢選修了3號課程的學(xué)生的學(xué)號及其成績，查詢結(jié)

29、果按分?jǐn)?shù)降序排列。 SELECT Sno，Score FROM SC WHERE Cno= 3 ORDER BY Score DESC； 例25 查詢?nèi)w學(xué)生情況，查詢結(jié)果按所在系的系號升序排列，同一系中的學(xué)生按年齡降序排列。 SELECT * FROM Student ORDER BY Sdept，Sage DESC；,四、使用集函數(shù),計(jì)數(shù) COUNT（DISTINCT|ALL *） COUNT（DISTINCT|ALL ） 計(jì)算總和 SUM（DISTINCT|ALL ） 計(jì)算平均值 AVG（DISTINCT|ALL ） 求最大值 MAX（DISTINCT|ALL ） 求最小值 MIN（DI

30、STINCT|ALL ） DISTINCT短語：在計(jì)算時(shí)要取消指定列中的重復(fù)值 ALL短語：不取消重復(fù)值 ALL為缺省值,使用集函數(shù)實(shí)例,例26 查詢學(xué)生總?cè)藬?shù)。 SELECT COUNT(*) FROM Student； 例27 查詢選修了課程的學(xué)生人數(shù)。 SELECT COUNT(DISTINCT Sno) FROM SC； 例28 計(jì)算1號課程的學(xué)生平均成績。 SELECT AVG(Score) FROM SC WHERE Cno= 1 ； 例29 查詢選修1號課程的學(xué)生最高分?jǐn)?shù)。 SELECT MAX(Score) FROM SC WHER Cno= 1 ；,五、對查詢結(jié)果分組,使用G

31、ROUP BY子句分組 細(xì)化集函數(shù)的作用對象 未對查詢結(jié)果分組，集函數(shù)將作用于整個查詢結(jié)果 對查詢結(jié)果分組后，集函數(shù)將分別作用于每個組 GROUP BY子句的作用對象是查詢的中間結(jié)果表 分組方法：按指定的一列或多列值分組，值相等的為一組 使用GROUP BY子句后，SELECT子句的列名列表中只能出現(xiàn)分組屬性和集函數(shù),GROUP BY子句示例,例30 求各個課程號及相應(yīng)的選課人數(shù)。 SELECT Cno，COUNT(Sno) FROM SC GROUP BY Cno；,使用HAVING短語篩選最終輸出結(jié)果,只有滿足HAVING短語指定條件的組才輸出 HAVING短語與WHERE子句的區(qū)別：作用

32、對象不同 WHERE子句作用于基表或視圖，從中選擇滿足條件的元組。 HAVING短語作用于組，從中選擇滿足條件的組。,使用HAVING短語篩選最終輸出結(jié)果,例31 查詢選修了3門以上課程的學(xué)生學(xué)號。 SELECT Sno FROM SC GROUP BY Sno HAVING COUNT(*) 3； 例32 查詢有3門以上課程是90分以上的學(xué)生的學(xué)號及（90分以上的）課程數(shù) SELECT Sno, COUNT(*) FROM SC WHERE Score=90 GROUP BY Sno HAVING COUNT(*)=3;,3.3.3 連接查詢,同時(shí)涉及多個表的查詢稱為連接查詢 用來連接兩個表

33、的條件稱為連接條件或連接謂詞 一般格式： . . 比較運(yùn)算符：=、=、. BETWEEN . AND . 連接字段 連接謂詞中的列名稱為連接字段 連接條件中的各連接字段類型必須是可比的，但不必是相同的,SQL中連接查詢的主要類型,廣義笛卡爾積 等值連接(含自然連接) 非等值連接查詢 自身連接查詢 外連接查詢 復(fù)合條件連接查詢,一、廣義笛卡爾積,不帶連接謂詞的連接 很少使用 例： SELECT Student.* , SC.* FROM Student, SC,二、等值與非等值連接查詢,等值連接 連接運(yùn)算符為 = 的連接操作 . = . 任何子句中引用表1和表2中同名屬性時(shí)，都必須加表名前綴。引

34、用唯一屬性名時(shí)可以加也可以省略表名前綴。 SELECT Student.*，SC.* FROM Student，SC WHERE Student.Sno = SC.Sno；,自然連接,等值連接的一種特殊情況，把目標(biāo)列中重復(fù)的屬性列去掉。 例33 對例32用自然連接完成。 SELECT Student.Sno，Sname，Ssex，Sage， Sdept，Cno，Score FROM Student，SC WHERE Student.Sno = SC.Sno；,非等值連接查詢,連接運(yùn)算符 不是 = 的連接操作 . 比較運(yùn)算符：、=、. BETWEEN . AND .,三、自身連接,一個表與其自己

35、進(jìn)行連接，稱為表的自身連接 需要給表起別名以示區(qū)別 由于所有屬性名都是同名屬性，因此必須使用別名前綴 例34 查詢至少選修兩門課的同學(xué)學(xué)號 SELECT SC1.Sno FROM SC sc1，SC sc2 WHERE sc1.sno = sc2.sno AND o!=o；,四、外連接（Outer Join）,外連接與普通連接的區(qū)別 普通連接操作只輸出滿足連接條件的元組 外連接操作以指定表為連接主體，將主體表中不滿足連接條件的元組一并輸出 例 33 查詢每個學(xué)生及其選修課程的情況包括沒有選修課程的學(xué)生-用外連接操作 SELECT Student.Sno，Sname，Ssex， Sage，Sde

36、pt，Cno，Score FROM Student LEFT OUTER JOIN SC ON Student.Sno = SC.Sno；,外連接（續(xù)）,左外連接 包括內(nèi)連接和在左表中但內(nèi)連接不會返回的那些行。 LEFT OUTER JOIN（或LEFT JOIN ） 右外連接 包括內(nèi)連接和在右表中但內(nèi)連接不會返回的那些行。 RIGHT OUTER JOIN(或RIGHT JOIN) 全外連接 包括內(nèi)連接和在左表、右表中但內(nèi)連接不會返回的那些行。 FULL OUTER JOIN(或FULL JOIN),五、復(fù)合條件連接,WHERE子句中含多個連接條件時(shí)，稱為復(fù)合條件連接 例35查詢選修2號課程

37、且成績在90分以上的所有學(xué)生的學(xué)號、姓名 SELECT Student.Sno, student.Sname FROM Student, SC WHERE Student.Sno = SC.Sno AND SC.Cno= 2 AND SC.Score 90；,多表連接,例36 查詢每個學(xué)生的學(xué)號、姓名、選修的課程名及成績。 SELECT Student.Sno，Sname，Cname，Score FROM Student，SC，Course WHERE Student.Sno = SC.Sno and SC.Cno = Course.Cno； 或：SELECT s.Sno,Sname,Cnam

38、e,Score FROM Student s INNER JOIN (SELECT Sno ,CName,Score FROM Course c INNER JOIN SC ON c.Cno=SC.Cno) t ON t.sno=s.sno,嵌套查詢,嵌套查詢概述 一個SELECT-FROM-WHERE語句稱為一個查詢塊 將一個查詢塊嵌套在另一個查詢塊的WHERE子句或HAVING短語的條件中的查詢稱為嵌套查詢 SELECT Sname外層查詢/父查詢 FROM Student WHERE Sno IN （SELECT Sno 內(nèi)層查詢/子查詢 FROM SC WHERE Cno= 2 ）；

39、說明：子查詢中不能使用ORDER BY子句 層層嵌套方式反映了 SQL語言的結(jié)構(gòu)化 有些嵌套查詢可以用連接運(yùn)算替代,嵌套查詢的分類及求解過程,不相關(guān)子查詢：子查詢的查詢條件不依賴于父查詢 是由里向外逐層處理。即每個子查詢在上一級查詢處理之前求解，子查詢的結(jié)果用于建立其父查詢的查找條件。 相關(guān)子查詢：子查詢的查詢條件依賴于父查詢 首先取外層查詢中表的第一個元組，根據(jù)它與內(nèi)層查詢相關(guān)的屬性值處理內(nèi)層查詢，若WHERE子句返回值為真，則取此元組放入結(jié)果表； 然后再取外層表的下一個元組； 重復(fù)這一過程，直至外層表全部檢查完為止。,一、帶有IN謂詞的子查詢,例37 查詢與“劉晨”在同一個系學(xué)習(xí)的學(xué)生。

40、SELECT Sno，Sname，Sdept FROM Student WHERE Sdept IN (SELECT Sdept FROM Student WHERE Sname= 劉晨 )； 此查詢?yōu)椴幌嚓P(guān)子查詢，可以分步做，DBMS求解該查詢時(shí)也是分步去做的。,帶有IN謂詞的子查詢（續(xù)）,用自身連接完成本查詢要求 SELECT S1.Sno，S1.Sname，S1.Sdept FROM Student S1，Student S2 WHERE S1.Sdept = S2.Sdept AND S2.Sname = 劉晨；,帶有IN謂詞的子查詢（續(xù)）,父查詢和子查詢中的表均可以定義別名 SELE

41、CT Sno，Sname，Sdept FROM Student S1 WHERE S1.Sdept IN (SELECT Sdept FROM Student S2 WHERE S2.Sname= 劉晨 )；,帶有IN謂詞的子查詢（續(xù)）,例38查詢選修了課程名為“信息系統(tǒng)”的學(xué)生學(xué)號和姓名 SELECT Sno，Sname 最后在Student關(guān)系中取出 FROM Student Sno和Sname WHERE Sno IN (SELECT Sno 然后在SC關(guān)系中找出選 FROM SC 修了3號課程的學(xué)生學(xué)號 WHERE Cno IN (SELECT Cno 首先在Course關(guān)系中找出“信

42、 FROM Course 息系統(tǒng)”的課程號，結(jié)果為3號 WHERE Cname= 信息系統(tǒng));,帶有IN謂詞的子查詢（續(xù)）,用連接查詢 SELECT Sno，Sname FROM Student，SC，Course WHERE Student.Sno = SC.Sno AND SC.Cno = Course.Cno AND Course.Cname=信息系統(tǒng)；,二、帶有比較運(yùn)算符的子查詢,當(dāng)能確切知道內(nèi)層查詢返回單值時(shí)，可用比較運(yùn)算符（，=，）。 例：假設(shè)一個學(xué)生只可能在一個系學(xué)習(xí)，并且必須屬于一個系，則在例37可以用 = 代替IN ： SELECT Sno，Sname，Sdept FROM

43、Student WHERE Sdept = （SELECT Sdept FROM Student WHERE Sname= 劉晨 ）； 與ANY或ALL謂詞配合使用,帶有比較運(yùn)算符的子查詢（續(xù)）,子查詢一定要跟在比較符之后 錯誤的例子： SELECT Sno，Sname，Sdept FROM Student WHERE ( SELECT Sdept FROM Student WHERE Sname= 劉晨 ) = Sdept；,相關(guān)子查詢的執(zhí)行過程,找出每個學(xué)生超過他選修課程平均成績的課程號 SELECT Sno,Cno FROM SC x WHERE Score =(SELECT AVG(S

44、core) FROM SC y WHERE y.Sno=x.Sno),三、帶有ANY或ALL謂詞的子查詢,ANY：任意一個值 ANY大于子查詢結(jié)果中的某個值 ALL大于子查詢結(jié)果中的所有值 = ANY大于等于子查詢結(jié)果中的某個值 = ALL大于等于子查詢結(jié)果中的所有值 ）ANY不等于子查詢結(jié)果中的某個值 !=（或）ALL不等于子查詢結(jié)果中的任何一個值 ALL：所有值,帶有ANY或ALL謂詞的子查詢（續(xù)）,例39 查詢其他系中比信息系某一個學(xué)生年齡小的學(xué)生姓名和年齡 SELECT Sname，Sage FROM Student WHERE Sage IS ; /* 注意這是父查詢塊中的條件 */

45、,帶有ANY或ALL謂詞的子查詢（續(xù)）,結(jié)果 Sname Sage 王敏 18 執(zhí)行過程 1.DBMS執(zhí)行此查詢時(shí)，首先處理子查詢，找出 IS系中所有學(xué)生的年齡，構(gòu)成一個集合(19，18) 2. 處理父查詢，找所有不是IS系且年齡小于 19 或 18的學(xué)生,帶有ANY或ALL謂詞的子查詢（續(xù)）,ANY和ALL謂詞有時(shí)可以用集函數(shù)實(shí)現(xiàn) ANY與ALL與集函數(shù)的對應(yīng)關(guān)系,用集函數(shù)實(shí)現(xiàn)子查詢通常比直接用ANY或ALL查詢效率要高，因?yàn)榍罢咄ǔＤ軌驕p少比較次數(shù),帶有ANY或ALL謂詞的子查詢（續(xù)）,例39：用集函數(shù)實(shí)現(xiàn)例39 SELECT Sname，Sage FROM Student WHERE S

46、age IS ;,四、帶有EXISTS謂詞的子查詢,1. EXISTS謂詞 2. NOT EXISTS謂詞 3. 不同形式的查詢間的替換 4. 相關(guān)子查詢的效率 5. 用EXISTS/NOT EXISTS實(shí)現(xiàn)全稱量詞 6. 用EXISTS/NOT EXISTS實(shí)現(xiàn)邏輯蘊(yùn)函,帶有EXISTS謂詞的子查詢(續(xù)）,1. EXISTS謂詞 帶有EXISTS謂詞的子查詢不返回任何數(shù)據(jù)，只產(chǎn)生邏輯真值“true”或邏輯假值“false”。 若內(nèi)層查詢結(jié)果非空，則返回真值 若內(nèi)層查詢結(jié)果為空，則返回假值 由EXISTS引出的子查詢，其目標(biāo)列表達(dá)式通常都用* ，因?yàn)閹XISTS的子查詢只返回真值或假值，給出

47、列名無實(shí)際意義 2. NOT EXISTS謂詞 3. 不同形式的查詢間的替換 一些帶EXISTS或NOT EXISTS謂詞的子查詢不能被其他形式的子查詢等價(jià)替換 所有帶IN謂詞、比較運(yùn)算符、ANY和ALL謂詞的子查詢都能用帶EXISTS謂詞的子查詢等價(jià)替換。,帶有EXISTS謂詞的子查詢(續(xù)）,例41 查詢所有選修了1號課程的學(xué)生姓名。 用嵌套查詢 SELECT Sname FROM Student WHERE EXISTS (SELECT * FROM SC /*相關(guān)子查詢*/ WHERE Sno=Student.Sno AND Cno= 1 )； 用連接運(yùn)算 SELECT Sname FR

48、OM Student, SC WHERE Student.Sno=SC.Sno AND SC.Cno= 1;,帶有EXISTS謂詞的子查詢(續(xù)）,例42 查詢沒有選修1號課程的學(xué)生姓名。 SELECT Sname FROM Student WHERE NOT EXISTS (SELECT * FROM SC WHERE Sno = Student.Sno AND Cno=1)； 此例用連接運(yùn)算難于實(shí)現(xiàn),帶有EXISTS謂詞的子查詢(續(xù)）,例43 查詢選修了全部課程的學(xué)生姓名。 SELECT Sname FROM Student WHERE NOT EXISTS （SELECT * FROM C

49、ourse WHERE NOT EXISTS (SELECT * FROM SC WHERE SC.Sno= Student.Sno AND SC.Cno= Course.Cno）,帶有EXISTS謂詞的子查詢(續(xù)）,例44 查詢至少選修了學(xué)生95002選修的全部課程的學(xué)生號碼。 SELECT DISTINCT Sno FROM SC SCX WHERE NOT EXISTS (SELECT * FROM SC SCY WHERE SCY.Sno = 95002 AND NOT EXISTS (SELECT * FROM SC SCZ WHERE SCZ.Sno=SCX.Sno AND SCZ

50、.Cno=SCY.Cno)；,3.3.5 集合查詢,一般商用數(shù)據(jù)庫支持的集合操作種類 并操作(UNION) 交操作(INTERSECT) 差操作(EXCEPT) 語法 UNION（INTERSECT、EXCEPT） 說明：參加運(yùn)算的各結(jié)果表的列數(shù)必須相同；對應(yīng)項(xiàng)的數(shù)據(jù)類型也必須相同,并操作,例45 查詢計(jì)算機(jī)科學(xué)系的學(xué)生及年齡不大于19歲的學(xué)生。 方法一： SELECT * FROM Student WHERE Sdept= CS UNION SELECT * FROM Student WHERE Sage=19； / WHERE Sdept= CS OR Sage=19；,并操作,例46 查

51、詢選修了課程1或者選修了課程2的學(xué)生。 方法一： SELECT Sno FROM SC WHERE Cno= 1 UNION SELECT Sno FROM SC WHERE Cno= 2 ； / WHERE Cno= 1 OR Cno= 2 ；,并操作,例47 查詢學(xué)校中所有師生的姓名。 SELECT Sname FROM Student UNION SELECT Tname FROM Teacher;,例48 查詢計(jì)算機(jī)科學(xué)系中年齡不大于19歲的學(xué)生的姓名 SELECT SName FROM Student WHERE Sdept= CS INTERSECT SELECT SName FR

52、OM Student WHERE Sage=19,交操作,3 差操作,例49 查詢沒有選修1號課程的學(xué)生的學(xué)號。 SELECT Sno FROM Student EXCEPT SELECT Sno FROM SC WHERE Cno=1,4. 對集合操作結(jié)果的排序,ORDER BY子句只能用于對最終查詢結(jié)果排序，不能對中間結(jié)果排序 任何情況下，ORDER BY子句只能出現(xiàn)在最后 對集合操作結(jié)果排序時(shí)，ORDER BY子句中用數(shù)字指定排序?qū)傩?new,對集合操作結(jié)果的排序（續(xù)）,例53 錯誤寫法 SELECT * FROM Student WHERE Sdept= CS ORDER BY Sno

53、 UNION SELECT * FROM Student WHERE Sage=19 ORDER BY Sno；,new,對集合操作結(jié)果的排序（續(xù)）,正確寫法 SELECT * FROM Student WHERE Sdept= CS UNION SELECT * FROM Student WHERE Sage=19 ORDER BY 1；,new,3.3.6 SELECT語句的一般格式,SELECT ALL|DISTINCT 別名 FROM 別名 WHERE GROUP BY ， . HAVING ORDER BY ASC|DESC ；,3.4.1 插入數(shù)據(jù),兩種插入數(shù)據(jù)方式 插入單個元組

54、插入子查詢結(jié)果,1. 插入單個元組,語句格式 INSERT INTO (，) VALUES ( ， ) 功能 將新元組插入指定表中。,插入單個元組（續(xù)）,INTO子句 指定要插入數(shù)據(jù)的表名及屬性列 屬性列的順序可與表定義中的順序不一致 沒有指定屬性列：表示要插入的是一條完整的元組，且屬性列屬性與表定義中的順序一致 指定部分屬性列：插入的元組在其余屬性列上取空值 VALUES子句 提供的值必須與INTO子句匹配 值的個數(shù) 值的類型,插入單個元組（續(xù)）,例1 將一個新學(xué)生記錄 （學(xué)號：95020；姓名：陳冬；性別：男；所在系：IS；年齡：18歲）插入到Student表中。 INSERT INTO

55、Student VALUES (95020，陳冬，男，IS，18)； 例2 插入一條選課記錄( 95020，1 )。 INSERT INTO SC(Sno，Cno) VALUES ( 95020 ， 1 )； 新插入的記錄在Score列上取空值,2. 插入子查詢結(jié)果,語句格式 INSERT INTO ( ， ) 子查詢； 功能 將子查詢結(jié)果插入指定表中,插入子查詢結(jié)果（續(xù)）,例3 對每一個系，求學(xué)生的平均年齡，并把結(jié)果存入數(shù)據(jù)庫。 第一步：建表 CREATE TABLE Deptage (Sdept CHAR(15) ， /* 系名*/ Avgage SMALLINT)； /*學(xué)生平均年齡*/

56、 第二步：插入數(shù)據(jù) INSERT INTO Deptage(Sdept，Avgage) SELECT Sdept，AVG(Sage) FROM Student GROUP BY Sdept；,插入子查詢結(jié)果（續(xù)）,INTO子句(與插入單條元組類似) 指定要插入數(shù)據(jù)的表名及屬性列 屬性列的順序可與表定義中的順序不一致 沒有指定屬性列：表示要插入的是一條完整的元組 指定部分屬性列：插入的元組在其余屬性列上取空值 子查詢 SELECT子句目標(biāo)列必須與INTO子句匹配 值的個數(shù) 值的類型,插入子查詢結(jié)果（續(xù)）,DBMS在執(zhí)行插入語句時(shí)會檢查所插元組是 否破壞表上已定義的完整性規(guī)則 實(shí)體完整性 參照完整

57、性 用戶定義的完整性 對于有NOT NULL約束的屬性列是否提供了非空值 對于有UNIQUE約束的屬性列是否提供了非重復(fù)值 對于有值域約束的屬性列所提供的屬性值是否在值域范圍內(nèi),3.4.2 修改數(shù)據(jù),語句格式 UPDATE SET =，= WHERE ； 功能 修改指定表中滿足WHERE子句條件的元組 SET子句 指定修改方式 要修改的列 修改后取值 WHERE子句 指定要修改的元組,缺省表示要修改表中的所有元組,例4 將學(xué)生95001的年齡改為22歲。 UPDATE Student SET Sage=22 WHERE Sno= 95001 ； 例5 將所有學(xué)生的年齡增加1歲。 UPDATE

58、Student SET Sage= Sage+1； 例6 將信息系所有學(xué)生的年齡增加1歲。 UPDATE Student SET Sage= Sage+1 WHERE Sdept= IS ；,3. 帶子查詢的修改語句,UPDATE SC SET Score=0 WHERE sno in (SELETE sno FROM Student WHERE dept=cs)；,例7 將計(jì)算機(jī)科學(xué)系全體學(xué)生的成績置零。,UPDATE SC SET Score=0 WHERE CS= (SELETE sdept FROM Student WHERE sno=sc.sno)；,修改數(shù)據(jù)（續(xù)）,DBMS在執(zhí)行修改語句時(shí)會檢查修改操作 是否破壞表上已定義的完整性規(guī)則 實(shí)體完整性 主碼不允許修改 用戶定義的完整性 NOT NULL約束 UN