SQL語(yǔ)言基礎(chǔ)基本概念 SQL的數(shù)據(jù)類(lèi)型基本表的定義刪除及修改數(shù)據(jù)查詢功能數(shù)據(jù)更改功能建立與刪除索引PPT課件

《SQL語(yǔ)言基礎(chǔ)基本概念 SQL的數(shù)據(jù)類(lèi)型基本表的定義刪除及修改數(shù)據(jù)查詢功能數(shù)據(jù)更改功能建立與刪除索引PPT課件》由會(huì)員分享，可在線閱讀，更多相關(guān)《SQL語(yǔ)言基礎(chǔ)基本概念 SQL的數(shù)據(jù)類(lèi)型基本表的定義刪除及修改數(shù)據(jù)查詢功能數(shù)據(jù)更改功能建立與刪除索引PPT課件（117頁(yè)珍藏版）》請(qǐng)?jiān)谘b配圖網(wǎng)上搜索。

1、4.1 4.1 基本概念基本概念語(yǔ)言的發(fā)展 語(yǔ)言的特點(diǎn)語(yǔ)言功能概述 第1頁(yè)/共117頁(yè)語(yǔ)言的發(fā)展 1986年10月由美國(guó)ANSI 公布最早的SQL標(biāo)準(zhǔn)。 1989年4月，ISO提出了具備完整性特征的SQL，稱為SQL-89 。 1992年11月，ISO又公布了新的SQL標(biāo)準(zhǔn)，稱為SQL-92（以上均為關(guān)系形式）。 1999年頒布SQL-99，是SQL92的擴(kuò)展。第2頁(yè)/共117頁(yè)語(yǔ)言的特點(diǎn)1. 一體化。 2. 高度非過(guò)程化。 3. 簡(jiǎn)潔。 4. 使用方式多樣。第3頁(yè)/共117頁(yè)語(yǔ)言功能概述SQL功能功能命令動(dòng)詞命令動(dòng)詞數(shù)據(jù)查詢數(shù)據(jù)查詢SELECT數(shù)據(jù)定義數(shù)據(jù)定義CREATE、DROP、ALTE

2、R數(shù)據(jù)操縱數(shù)據(jù)操縱INSERT、UPDATE、DELETE數(shù)據(jù)控制數(shù)據(jù)控制GRANT、REVOKE四大功能：數(shù)據(jù)定義功能、數(shù)據(jù)控制功能、數(shù)據(jù)查詢功能和數(shù)據(jù)操縱功能。 第4頁(yè)/共117頁(yè)4.2 SQL的數(shù)據(jù)類(lèi)型SQL Server SQL Server 為例為例：數(shù)值型 字符串型日期時(shí)間型貨幣型第5頁(yè)/共117頁(yè)數(shù)值型準(zhǔn)確型準(zhǔn)確型 整數(shù)整數(shù)Bigint: 8字節(jié)，字節(jié)， Int：4字節(jié)字節(jié)Smallint：2字節(jié)，字節(jié)， Tinyint：1字節(jié)字節(jié) Bit：1位，存儲(chǔ)位，存儲(chǔ)1或或0小數(shù)小數(shù)Numeric（p,q）或）或 Decimal（p,q），）， 其中：其中：p為數(shù)字位長(zhǎng)度，為數(shù)字位長(zhǎng)度，

3、 q為小數(shù)位長(zhǎng)度。為小數(shù)位長(zhǎng)度。近似型近似型 Float：8字節(jié)字節(jié) Real：4字節(jié)字節(jié)第6頁(yè)/共117頁(yè)字符串型普通編碼字符串類(lèi)型統(tǒng)一字符編碼字符串類(lèi)型二進(jìn)制字符串類(lèi)型第7頁(yè)/共117頁(yè)普通編碼字符串類(lèi)型Char（n）:定長(zhǎng)存儲(chǔ)，n=8000 Varchar（n）：不定長(zhǎng)存儲(chǔ)（按實(shí)際長(zhǎng)度存儲(chǔ)），長(zhǎng)度最大不超過(guò)n , n=8000 注：n 為字符個(gè)數(shù)Text：存儲(chǔ)大于8000字節(jié)的文本第8頁(yè)/共117頁(yè)統(tǒng)一字符編碼（統(tǒng)一字符編碼（UnicodeUnicode）字符串類(lèi)型）字符串類(lèi)型nchar（n）:定長(zhǎng)存儲(chǔ)，n=4000 nvarchar（n）：不定長(zhǎng)存儲(chǔ)，長(zhǎng)度最大不超過(guò)n , n=4000

4、 ntext：存儲(chǔ)大于8000字節(jié)的文本特點(diǎn)：每個(gè)字符占兩個(gè)字節(jié)第9頁(yè)/共117頁(yè)二進(jìn)制字符串類(lèi)型Binary(n)：固定長(zhǎng)度，n = 8000。Varbinary(n)：可變長(zhǎng)度，n 8000 。注：n為二進(jìn)制數(shù)據(jù)的字節(jié)數(shù)image：大容量、可變長(zhǎng)二進(jìn)制字符數(shù)據(jù)，可用于存儲(chǔ)文件。第10頁(yè)/共117頁(yè)日期時(shí)間型日期時(shí)間型Datetime：8字節(jié)，年月日時(shí)分秒毫秒（例：2001/08/03 10:30:00.000 ） SmallDateTime：4字節(jié)，年月日時(shí)分 （例： 2001/08/03 10:30:00 ） 日期、時(shí)間的輸入格式第11頁(yè)/共117頁(yè)貨幣類(lèi)型Money： 8 個(gè)字節(jié)，精確

5、到貨幣單位的千分之十。Smallmoney： 4 個(gè)字節(jié)，精確到貨幣單位的千分之十。限制到小數(shù)點(diǎn)后 4 位?？梢詭в羞m當(dāng)?shù)呢泿欧?hào)。例如，100 英鎊可表示為 100。第12頁(yè)/共117頁(yè)4.3 基本表的定義、刪除及修改 基本表的定義與刪除 修改表結(jié)構(gòu) 第13頁(yè)/共117頁(yè)基本表的定義與刪除1定義基本表 使用SQL語(yǔ)言中的CREATE TABLE語(yǔ)句實(shí)現(xiàn)，其一般格式為： CREATE TABLE （ 列級(jí)完整性約束定義列級(jí)完整性約束定義 , , 列級(jí)完整性約束定義列級(jí)完整性約束定義, , 列級(jí)完整性約束定義列級(jí)完整性約束定義, , 表級(jí)完整性約束定義表級(jí)完整性約束定義 ） ; 第14頁(yè)/共11

6、7頁(yè)在列級(jí)完整性約束定義處可以定義的約束在列級(jí)完整性約束定義處可以定義的約束 NOT NULL：限制列取值非空。 DEFAULT：給定列的默認(rèn)值。 UNIQUE：限制列取值不重。 CHECK：限制列的取值范圍。 PRIMARY KEY：指定本列為主碼。 FOREIGN KEY：定義本列為引用其他表的外碼。使用形式為：FOREIGN KEY（） REFERENCES （） 第15頁(yè)/共117頁(yè)幾點(diǎn)說(shuō)明 NOT NULLNOT NULL和和DEFAULTDEFAULT只能是列級(jí)完整性約束；只能是列級(jí)完整性約束； 其他約束均可在表級(jí)完整性約束處定義。其他約束均可在表級(jí)完整性約束處定義。 注意以下幾點(diǎn)

7、：注意以下幾點(diǎn)： 第一，如果第一，如果CHECKCHECK約束是定義多列之間的取值約束，約束是定義多列之間的取值約束，則只能在表級(jí)完整性約束處定義；則只能在表級(jí)完整性約束處定義； 第二，如果表的主碼由多個(gè)列組成，則也只能在表第二，如果表的主碼由多個(gè)列組成，則也只能在表級(jí)完整性約束處定義，并將主碼列用括號(hào)括起來(lái)，級(jí)完整性約束處定義，并將主碼列用括號(hào)括起來(lái)，即：即： PRIMARY KEY PRIMARY KEY（列（列1 1 ，列，列2 2 ）；）； 第三，如果在表級(jí)完整性約束處定義外碼，第三，如果在表級(jí)完整性約束處定義外碼， 則則“FOREIGN KEY (FOREIGN KEY ()”)”部

8、分不能省。部分不能省。第16頁(yè)/共117頁(yè)約束定義 列取值非空約束 NOT NULL例：sname char(10) NOT NULL第17頁(yè)/共117頁(yè)約束定義（續(xù)） 表主碼約束表主碼約束在定義列時(shí)定義主碼（僅用于單列主碼）列定義 PRIMARY KEY例： SNO char(7) PRIMARY KEY在定義完列時(shí)定義主碼（用于單列或多列主碼）PRIMARY KEY （）例： PRIMARY KEY(SNO) PRIMARY KEY(SNO，CNO)第18頁(yè)/共117頁(yè)約束定義（續(xù)）外碼引用約束外碼引用約束 指明本表外碼列引用的表及表中的主碼列。 FOREIGN KEY （）REFEREN

9、CES （）例： FOREIGN KEY （sno） REFERENCES 學(xué)生表（sno）第19頁(yè)/共117頁(yè)約束定義（續(xù)）默認(rèn)值約束默認(rèn)值約束格式：DEFAULT 默認(rèn)值例：定義系的默認(rèn)值為“計(jì)算機(jī)系”。 DEFAULT 計(jì)算機(jī)系第20頁(yè)/共117頁(yè)約束定義（續(xù)）CHECKCHECK約束約束格式：CHECK (約束表達(dá)式)例：定義成績(jī)大于等于0。 CHECK ( grade = 0 )第21頁(yè)/共117頁(yè)約束定義（續(xù)）UNIQUEUNIQUE約束約束在列級(jí)約束定義（僅用于單列約束）列定義 UNIQUE例： SNAME char(7) UNIQUE在表級(jí)約束定義（用于單列或多列組合約束）UN

10、IQUE （）例： UNIQUE (SNO，CNO)第22頁(yè)/共117頁(yè)創(chuàng)建學(xué)生表創(chuàng)建學(xué)生表CREATE TABLE Student( Sno char(7) PRIMARY KEY, Sname char(10) NOT NULL, Ssex char(2) CHECK(Ssex=男OR Ssex=女), Sage tinyint CHECK(Sage=15 AND Sage0), Semester tinyint CHECK(Semester0), Period int CHECK (Period0), PRIMARY KEY(Cno) ) 第24頁(yè)/共117頁(yè)創(chuàng)建創(chuàng)建SC表表CREATE

11、 TABLE SC ( Sno char(7) NOT NULL, Cno char(10) NOT NULL, Grade tinyint CHECK(Grade=0 and Grade=100), PRIMARY KEY(Sno, Cno), FOREIGN KEY(Sno) REFERENCES Student(Sno), FOREIGN KEY(Cno) REFERENCES Course(Cno) )第25頁(yè)/共117頁(yè)刪除表刪除表 當(dāng)確信不再需要某個(gè)表時(shí)，可以將其刪除 刪除表時(shí)會(huì)將與表有關(guān)的所有對(duì)象一起刪掉，包括表中的數(shù)據(jù)。 刪除表的語(yǔ)句格式為： DROP TABLE , 例：刪除

12、test表的語(yǔ)句為：DROP TABLE test第26頁(yè)/共117頁(yè)修改表結(jié)構(gòu) 在定義完表之后，如果需求有變化，比如添加列、刪除列或修改列定義，可以使用ALTER TABLE語(yǔ)句實(shí)現(xiàn)。ALTER TABLE語(yǔ)句可以對(duì)表添加列、刪除列、修改列的定義、定義主碼、外碼，也可以添加和刪除約束。 第27頁(yè)/共117頁(yè)修改表結(jié)構(gòu)語(yǔ)法ALTER TABLE ALTER COLUMN | ADD COLUMN | DROP COLUMN | ADD PRIMARY KEY（列名 ， n ） | ADD FOREIGN KEY（列名） REFERNECES 表名（列名）第28頁(yè)/共117頁(yè)示例例2為SC表添加

13、“修課類(lèi)別”列，此列的定義為：XKLB char(4)ALTER TABLE SC ADD XKLB char(4) NULL 第29頁(yè)/共117頁(yè)示例例3將新添加的XKLB的類(lèi)型改為 char(6)。ALTER TABLE SC ALTER COLUMN XKLB char(6)第30頁(yè)/共117頁(yè)示例例4刪除Course表的Period列。 ALTER TABLE Course DROP COLUMN Period 第31頁(yè)/共117頁(yè)4.4 數(shù)據(jù)查詢功能 查詢語(yǔ)句的基本結(jié)構(gòu)簡(jiǎn)單查詢多表連接查詢子查詢 第32頁(yè)/共117頁(yè)查詢語(yǔ)句基本格式SELECT -需要哪些列 FROM -來(lái)自于哪些表

14、 WHERE -根據(jù)什么條件 GROUP BY HAVING ORDER BY 第33頁(yè)/共117頁(yè)簡(jiǎn)單查詢1. 選擇表中若干列 第34頁(yè)/共117頁(yè)1. 查詢指定的列查詢指定的列 查詢表中用戶感興趣的部分屬性列。 例5：查詢?nèi)w學(xué)生的學(xué)號(hào)與姓名。SELECT Sno，Sname FROM Student 例6：查詢?nèi)w學(xué)生的姓名、學(xué)號(hào)和所在系。SELECT Sname，Sno，Sdept FROM Student 第35頁(yè)/共117頁(yè)2. 查詢?nèi)苛胁樵內(nèi)苛?例7查詢?nèi)w學(xué)生的記錄SELECT Sno，Sname，Ssex, Sage, Sdept FROM Student等價(jià)于： SEL

15、ECT * FROM Student 第36頁(yè)/共117頁(yè)3. 查詢經(jīng)過(guò)計(jì)算的列查詢經(jīng)過(guò)計(jì)算的列 例8查詢?nèi)w學(xué)生的姓名及其出生年份。SELECT Sname，2006 - Sage FROM Student 例9查詢?nèi)w學(xué)生的姓名和出生年份所在系，并在出生年份列前加入一個(gè)列，此列的每行數(shù)據(jù)均為出生年份常量值。SELECT Sname，出生年份, 2006-Sage FROM Student 第37頁(yè)/共117頁(yè)改變列標(biāo)題改變列標(biāo)題 語(yǔ)法：列名 | 表達(dá)式 AS 列標(biāo)題 或：列標(biāo)題 列名 | 表達(dá)式 例： SELECT Sname 姓名，Year of Birth 出生年份, 2006 - S

16、age 年份， FROM Student 第38頁(yè)/共117頁(yè)簡(jiǎn)單查詢2. 選擇表中若干元組 第39頁(yè)/共117頁(yè)1. 消除取值相同的記錄消除取值相同的記錄 例10在修課表中查詢有哪些學(xué)生修了課程，要求列出學(xué)生的學(xué)號(hào)。 SELECT Sno FROM SC 結(jié)果中有重復(fù)的行。 用DISTINCT關(guān)鍵字可以去掉結(jié)果中的重復(fù)行。 DISTINCT關(guān)鍵字放在SELECT詞的后邊、目標(biāo)列名序列的前邊。SELECT DISTINCT Sno FROM SC第40頁(yè)/共117頁(yè)2. 查詢滿足條件的元組查詢滿足條件的元組 查詢條件查詢條件謂謂 詞詞比較運(yùn)算符=, , =, , =, （或!=）NOT+比較運(yùn)

17、算符 確定范圍BETWEENAND, NOT BETWEENAND確定集合IN, NOT IN字符匹配LIKE, NOT LIKE 空值IS NULL, IS NOT NULL邏輯謂詞）AND, OR第41頁(yè)/共117頁(yè)比較大小比較大小 例11查詢計(jì)算機(jī)系全體學(xué)生的姓名。SELECT Sname FROM Student WHERE Sdept = 計(jì)算機(jī)系 例12查詢年齡在20歲以下的學(xué)生的姓名及年齡。SELECT Sname, Sage FROM Student WHERE Sage 20 例13查詢考試成績(jī)有不及格的學(xué)生的學(xué)號(hào) SELECT DISTINCT Sno FROM SC WH

18、ERE Grade 60第42頁(yè)/共117頁(yè)確定范圍確定范圍 用BETWEENAND和NOT BETWEENAND 是邏輯運(yùn)算符，可以用來(lái)查找屬性值在或不在指定范圍內(nèi)的元組，其中BETWEEN后邊指定范圍的下限，AND后邊指定范圍的上限。 BETWEENAND的格式為： 列名 | 表達(dá)式 NOT BETWEEN 下限值 AND 上限值 如果列或表達(dá)式的值在不在下限值和上限值范圍內(nèi)，則結(jié)果為T(mén)rue，表明此記錄符合查詢條件。 第43頁(yè)/共117頁(yè)示例 例14查詢年齡在2023歲之間的學(xué)生的姓名、所在系和年齡。SELECT Sname, Sdept, Sage FROM Student WHERE

19、 Sage BETWEEN 20 AND 23 例15查詢年齡不在2023之間的學(xué)生姓名、所在系和年齡。SELECT Sname, Sdept, Sage FROM Student WHERE Sage NOT BETWEEN 20 AND 23第44頁(yè)/共117頁(yè)確定集合 使用IN運(yùn)算符。 用來(lái)查找屬性值屬于指定集合的元組。 格式為： 列名 NOT IN （常量1, 常量2, 常量n） 當(dāng)列中的值與IN中的某個(gè)常量值相等時(shí)，則結(jié)果為T(mén)rue，表明此記錄為符合查詢條件的記錄； NOT IN：當(dāng)列中的值與某個(gè)常量值相同時(shí)，則結(jié)果為False，表明此記錄為不符合查詢條件的記錄 第45頁(yè)/共117頁(yè)

20、示例 例16查詢信息系、數(shù)學(xué)系和計(jì)算機(jī)系學(xué)生的姓名和性別。SELECT Sname, Ssex FROM Student WHERE Sdept IN (信息系信息系, 數(shù)學(xué)系數(shù)學(xué)系, 計(jì)算機(jī)系計(jì)算機(jī)系) 例17查詢既不是信息系、數(shù)學(xué)系，也不是計(jì)算機(jī)系學(xué)生的姓名和性別。SELECT Sname, Ssex FROM Student WHERE Sdept NOT IN (信息系信息系, 數(shù)學(xué)系數(shù)學(xué)系, 計(jì)算機(jī)系計(jì)算機(jī)系)第46頁(yè)/共117頁(yè)字符匹配 使用LIKE運(yùn)算符 一般形式為： 列名 NOT LIKE 匹配串中可包含如下四種通配符： _：匹配任意一個(gè)字符； %：匹配0個(gè)或多個(gè)字符； ：匹配

21、 中的任意一個(gè)字符； ：不匹配 中的任意一個(gè)字符 第47頁(yè)/共117頁(yè)示例 例18查詢姓張的學(xué)生的詳細(xì)信息。SELECT * FROM Student WHERE Sname LIKE 張張% 例19查詢學(xué)生表中姓張、李和劉的學(xué)生的情況。SELECT * FROM Student WHERE Sname LIKE 張李劉張李劉% 例20查詢名字中第2個(gè)字為小或大的學(xué)生的姓名和學(xué)號(hào) SELECT Sname, Sno FROM Student WHERE Sname LIKE _小大小大%第48頁(yè)/共117頁(yè)示例（續(xù)） 例21查詢所有不姓“劉”的學(xué)生。SELECT Sname FROM Stud

22、ent WHERE Sname NOT LIKE 劉劉% 例22查詢學(xué)號(hào)的最后一位不是2、3、5的學(xué)生情況。SELECT * FROM Student WHERE Sno LIKE %235 第49頁(yè)/共117頁(yè)涉及空值的查詢涉及空值的查詢 空值（NULL）在數(shù)據(jù)庫(kù)中表示不確定的值。 例如，學(xué)生選修課程后還沒(méi)有考試時(shí)，這些學(xué)生有選課記錄，但沒(méi)有考試成績(jī)，因此考試成績(jī)?yōu)榭罩怠?判斷某個(gè)值是否為NULL值，不能使用普通的比較運(yùn)算符。 判斷取值為空的語(yǔ)句格式為：列名 IS NULL 判斷取值不為空的語(yǔ)句格式為：列名 IS NOT NULL 第50頁(yè)/共117頁(yè)示例 例23查詢無(wú)考試成績(jī)的學(xué)生的學(xué)號(hào)和

23、相應(yīng)的課程號(hào)。SELECT Sno, Cno FROM SC WHERE Grade IS NULL 例24查詢所有有考試成績(jī)的學(xué)生的學(xué)號(hào)和課程號(hào)。SELECT Sno, Cno FROM SC WHERE Grade IS NOT NULL 第51頁(yè)/共117頁(yè)多重條件查詢 在WHERE子句中可以使用邏輯運(yùn)算符AND和OR來(lái)組成多條件查詢。 用AND連接的條件表示必須全部滿足所有的條件的結(jié)果才為T(mén)rue； 用OR連接的條件表示只要滿足其中一個(gè)條件結(jié)果即為T(mén)rue。 例25查詢計(jì)算機(jī)系年齡在20歲以下的學(xué)生姓名。SELECT Sname FROM Student WHERE Sdept = C

24、S AND Sage 20 第52頁(yè)/共117頁(yè)簡(jiǎn)單查詢3. 對(duì)查詢結(jié)果進(jìn)行排序 第53頁(yè)/共117頁(yè)對(duì)查詢結(jié)果進(jìn)行排序可對(duì)查詢結(jié)果進(jìn)行排序。 排序子句為：ORDER BY ASC | DESC , 說(shuō)明：按進(jìn)行升序（ASC）或降序（DESC）排序。第54頁(yè)/共117頁(yè)示例 例26將學(xué)生按年齡的升序排序。SELECT * FROM Student ORDER BY Sage 例27查詢選修了c02號(hào)課程的學(xué)生的學(xué)號(hào)及其成績(jī)，查詢結(jié)果按成績(jī)降序排列。SELECT Sno, Grade FROM SCWHERE Cno=c02 ORDER BY Grade DESC 例28查詢?nèi)w學(xué)生的信息，查詢

25、結(jié)果按所在系的系名升序排列，同一系的學(xué)生按年齡降序排列。SELECT * FROM StudentORDER BY Sdept, Sage DESC 第55頁(yè)/共117頁(yè)簡(jiǎn)單查詢4. 使用計(jì)算函數(shù)匯總數(shù)據(jù) 第56頁(yè)/共117頁(yè)使用計(jì)算函數(shù)匯總數(shù)據(jù) SQL提供的計(jì)算函數(shù)有： COUNT（ * ）：統(tǒng)計(jì)表中元組個(gè)數(shù)； COUNT（DISTINCT ）：統(tǒng)計(jì)本列列值個(gè)數(shù)； SUM（DISTINCT ）：計(jì)算列值總和； AVG（DISTINCT ）：計(jì)算列值平均值； MAX（DISTINCT ）：求列值最大值； MIN（DISTINCT ）：求列值最小值。 上述函數(shù)中除上述函數(shù)中除COUNT（*）外，

26、其他函數(shù)在計(jì)算過(guò)程中均忽）外，其他函數(shù)在計(jì)算過(guò)程中均忽略略NULL值。值。第57頁(yè)/共117頁(yè)示例 例29統(tǒng)計(jì)學(xué)生總?cè)藬?shù)。SELECT COUNT(*) FROM Student 例30統(tǒng)計(jì)選修了課程的學(xué)生的人數(shù)。SELECT COUNT (DISTINCT Sno) FROM SC 例31 計(jì)算9512101號(hào)學(xué)生的考試總成績(jī)之和。SELECT SUM(Grade) FROM SC WHERE Sno = 9512101 第58頁(yè)/共117頁(yè)示例（續(xù)） 例32計(jì)算C01號(hào)課程學(xué)生的考試平均成績(jī)。SELECT AVG(Grade) FROM SC WHERE Cno=C01 例33查詢選修了C

27、01號(hào)課程的學(xué)生的最高分和最低分。SELECT MAX(Grade) , MIN(Grade) FROM SC WHERE Cno=C01注意：計(jì)算函數(shù)不能出現(xiàn)在WHERE子句中 第59頁(yè)/共117頁(yè)簡(jiǎn)單查詢5. 對(duì)查詢結(jié)果進(jìn)行分組計(jì)算 第60頁(yè)/共117頁(yè)對(duì)查詢結(jié)果進(jìn)行分組計(jì)算對(duì)查詢結(jié)果進(jìn)行分組計(jì)算 作用：可以控制計(jì)算的級(jí)別：對(duì)全表還是對(duì)一組。目的：細(xì)化計(jì)算函數(shù)的作用對(duì)象。分組語(yǔ)句的一般形式： GROUP BY HAVING 第61頁(yè)/共117頁(yè)1. 使用GROUP BY 例34統(tǒng)計(jì)每門(mén)課程的選課人數(shù)，列出課程號(hào)和人數(shù)。 SELECT Cno as 課程號(hào), COUNT(Sno) as 選課

28、人數(shù) FROM SC GROUP BY Cno 對(duì)查詢結(jié)果按Cno的值分組，所有具有相同Cno值的元組為一組，然后再對(duì)每一組使用COUNT計(jì)算，求得每組的學(xué)生人數(shù)。第62頁(yè)/共117頁(yè)SnoCnoGrade951201C0180951201C0278951202C0190952103C0288952103C0185952103C0391952103C0474CnoCount(Sno)C013C022C031C041SnoCnoGrade951201C0180951202C0190952103C0185951201C0278952103C0288952103C0391952103C0474第63

29、頁(yè)/共117頁(yè)例35查詢每名學(xué)生的選課門(mén)數(shù)和平均成績(jī)。 SELECT Sno as 學(xué)號(hào), COUNT(*) as 選課門(mén)數(shù), AVG(Grade) as 平均成績(jī) FROM SC GROUP BY Sno第64頁(yè)/共117頁(yè)2. 使用HAVING HAVING用于對(duì)分組自身進(jìn)行限制，它有點(diǎn)象WHERE子句，但它用于組而不是對(duì)單個(gè)記錄。 例36查詢修了3門(mén)以上課程的學(xué)生的學(xué)號(hào)SELECT Sno FROM SC GROUP BY Sno HAVING COUNT(*) 3 第65頁(yè)/共117頁(yè)示例例37查詢修課門(mén)數(shù)等于或大于4門(mén)的學(xué)生的平均成績(jī)和選課門(mén)數(shù)。SELECT Sno, AVG(Gra

30、de) 平均成績(jī), COUNT(*) 修課門(mén)數(shù) FROM SC GROUP BY Sno HAVING COUNT(*) = 4 第66頁(yè)/共117頁(yè)多表連接查詢 若一個(gè)查詢同時(shí)涉及兩個(gè)或兩個(gè)以上的表，則稱之為連接查詢。連接查詢是關(guān)系數(shù)據(jù)庫(kù)中最主要的查詢連接查詢包括內(nèi)連接、外連接和交叉連接等。第67頁(yè)/共117頁(yè)連接基礎(chǔ)知識(shí)連接基礎(chǔ)知識(shí)連接查詢中用于連接兩個(gè)表的條件稱為連接條件或連接謂詞。 一般格式為： 必須是可比的必須是可比的第68頁(yè)/共117頁(yè)內(nèi)連接SQL-92 內(nèi)連接語(yǔ)法如下： SELECT FROM 表名 INNER JOIN 被連接表 ON 連接條件第69頁(yè)/共117頁(yè)執(zhí)行連接操作的

31、過(guò)程：執(zhí)行連接操作的過(guò)程： 首先取表首先取表1 1中的第中的第1 1個(gè)元組，然后從頭開(kāi)始掃描表個(gè)元組，然后從頭開(kāi)始掃描表2 2，逐一查找，逐一查找滿足連接條件的元組，滿足連接條件的元組， 找到后就將表找到后就將表1 1中的第中的第1 1個(gè)元組與該元組拼接起來(lái)，形成結(jié)果個(gè)元組與該元組拼接起來(lái)，形成結(jié)果表中的一個(gè)元組。表中的一個(gè)元組。 表表2 2全部查找完畢后，再取表全部查找完畢后，再取表1 1中的第中的第2 2個(gè)元組，然后再?gòu)念^開(kāi)個(gè)元組，然后再?gòu)念^開(kāi)始掃描表始掃描表2 2， 重復(fù)這個(gè)過(guò)程，直到表重復(fù)這個(gè)過(guò)程，直到表1 1中的全部元組都處理完畢為止。中的全部元組都處理完畢為止。 第70頁(yè)/共117

32、頁(yè)例40.查詢計(jì)算機(jī)系學(xué)生的修課情況，要求列出學(xué)生的名字、所修課的課程號(hào)和成績(jī)。SELECT Sname, Cno, Grade FROM Student JOIN SC ON Student.Sno = SC.Sno WHERE Sdept = 計(jì)算機(jī)系 第71頁(yè)/共117頁(yè)例41. 查詢信息系修了VB課程的學(xué)生的修課成績(jī)，要求列出學(xué)生姓名、課程名和成績(jī)。SELECT Sname, Cname, Grade FROM Student s JOIN SC ON s.Sno = SC. Sno JOIN Course c ON c.Cno = SC.Cno WHERE Sdept = 信息系 A

33、ND Cname = VB 第72頁(yè)/共117頁(yè)自連接為特殊的內(nèi)連接相互連接的表物理上為同一張表。必須為兩個(gè)表取別名，使之在邏輯上成為兩個(gè)表。第73頁(yè)/共117頁(yè)例43. 查詢與劉晨在同一個(gè)系學(xué)習(xí)的學(xué)生的姓名和所在的系。SELECT S2.Sname, S2.Sdept FROM Student S1 JOIN Student S2 ON S1.Sdept = S2.Sdept WHERE S1.Sname = 劉晨 AND S2.Sname != 劉晨第74頁(yè)/共117頁(yè)外連接只限制一張表中的數(shù)據(jù)必須滿足連接條件，而另一張表中數(shù)據(jù)可以不滿足連接條件。 ANSI方式的外連接的語(yǔ)法格式為：FRO

34、M 表1 LEFT | RIGHT OUTER JOIN 表2 ON theta方式的外連接的語(yǔ)法格式為： 左外連接： FROM 表1, 表2 WHERE 表1.列名(+) 表2.列名 右外連接： FROM 表1, 表2 WHERE 表1.列名 表2.列名(+) 第75頁(yè)/共117頁(yè)例44. 查詢學(xué)生的修課情況，包括修了課程的學(xué)生和沒(méi)有修課的學(xué)生。SELECT Student.Sno, Sname, Cno, GradeFROM Student LEFT OUTER JOIN SCON Student.Sno = SC.Sno 第76頁(yè)/共117頁(yè)子查詢 在SQL語(yǔ)言中，一個(gè)SELECTFRO

35、MWHERE語(yǔ)句稱為一個(gè)查詢塊。子查詢是一個(gè) SELECT 查詢，它嵌套在 SELECT、INSERT、UPDATE、DELETE 語(yǔ)句的 WHERE 或 HAVING 子句內(nèi)，或其它子查詢中 子查詢的 SELECT 查詢總是使用圓括號(hào)括起來(lái)。第77頁(yè)/共117頁(yè)使用子查詢進(jìn)行基于集合的測(cè)試使用子查詢進(jìn)行基于集合的測(cè)試的語(yǔ)句的一般格式為：列名 NOT IN (子查詢) 第78頁(yè)/共117頁(yè)示例例45. 查詢與劉晨在同一個(gè)系的學(xué)生。SELECT Sno, Sname, Sdept FROM StudentWHERE Sdept IN ( SELECT Sdept FROM Student WHE

36、RE Sname = 劉晨 ) AND Sname != 劉晨 第79頁(yè)/共117頁(yè)示例 例46. 查詢成績(jī)?yōu)榇笥?0分的學(xué)生的學(xué)號(hào)、姓名。SELECT Sno, Sname FROM StudentWHERE Sno IN( SELECT Sno FROM SCWHERE Grade 90 ) 第80頁(yè)/共117頁(yè)例47. 查詢選修了“數(shù)據(jù)庫(kù)基礎(chǔ)”課程的學(xué)生的學(xué)號(hào)、姓名。 SELECT Sno, Sname FROM Student WHERE Sno IN( SELECT Sno FROM SC WHERE Cno IN(SELECT Cno FROM Course WHERE Cname

37、 = 數(shù)據(jù)庫(kù)基礎(chǔ)) ) 第81頁(yè)/共117頁(yè)使用子查詢進(jìn)行比較測(cè)試使用子查詢進(jìn)行比較測(cè)試 帶比較運(yùn)算符的子查詢指父查詢與子查詢之間用比較運(yùn)算符連接，當(dāng)用戶能確切知道內(nèi)層查詢返回的是單值時(shí)，可用、=、=、運(yùn)算符。第82頁(yè)/共117頁(yè)例48. 查詢修了c02課程且成績(jī)高于此課程的平均成績(jī)的學(xué)生的學(xué)號(hào)和成績(jī)。SELECT Sno , Grade FROM SC WHERE Cno = c02 and Grade ( SELECT AVG(Grade) from SC WHERE Cno = c02) 第83頁(yè)/共117頁(yè)使用子查詢進(jìn)行存在性測(cè)試使用子查詢進(jìn)行存在性測(cè)試 一般使用EXISTS謂詞。帶E

38、XISTS謂詞的子查詢不返回查詢的數(shù)據(jù)，只產(chǎn)生邏輯真值（有數(shù)據(jù)）和假值（沒(méi)有數(shù)據(jù)）。 第84頁(yè)/共117頁(yè)例49.查詢選修了c01號(hào)課程的學(xué)生姓名。SELECT Sname FROM Student WHERE EXISTS(SELECT * FROM SC WHERE Sno = Student.Sno AND Cno = c01) 第85頁(yè)/共117頁(yè)注意注1：處理過(guò)程為：先外后內(nèi)；由外層的值決定內(nèi)層的結(jié)果；內(nèi)層執(zhí)行次數(shù)由外層結(jié)果數(shù)決定。注2: 由于EXISTS的子查詢只能返回真或假值，因此在這里給出列名無(wú)意義。所以在有EXISTS的子查詢中，其目標(biāo)列表達(dá)式通常都用*。第86頁(yè)/共117頁(yè)

39、上句的處理過(guò)程1.找外層表Student表的第一行，根據(jù)其Sno值處理內(nèi)層查詢2.由外層的值與內(nèi)層的結(jié)果比較，由此決定外層條件的真、假3.順序處理外層表Student表中的第2、3、 行。第87頁(yè)/共117頁(yè)例50.查詢沒(méi)有選修c01號(hào)課程的學(xué)生姓名和所在系。SELECT Sname, Sdept FROM Student WHERE NOT EXISTS (SELECT * FROM SC WHERE Sno = Student.Sno AND Cno = c01) 第88頁(yè)/共117頁(yè)4.5 數(shù)據(jù)更改功能 插入數(shù)據(jù) 更新數(shù)據(jù) 刪除數(shù)據(jù)第89頁(yè)/共117頁(yè)插入數(shù)據(jù) 插入單行記錄的INSERT

40、語(yǔ)句的格式為：INSERT INTO () VALUES （值表）功能：新增一個(gè)符合表結(jié)構(gòu)的數(shù)據(jù)行，將值表數(shù)據(jù)按表中列定義順序或列名表順序賦給對(duì)應(yīng)列名。第90頁(yè)/共117頁(yè)注意值列表中的值與列名表中的列按位置順序?qū)?yīng)，它們的數(shù)據(jù)類(lèi)型必須一致。如果后邊沒(méi)有指明列名，則新插入記錄的值的順序必須與表中列的定義順序一致，且每一個(gè)列均有值（可以為空）。第91頁(yè)/共117頁(yè)示例例51將新生記錄（95020，陳冬，男，信息系，18歲）插入到Student表中。INSERT INTO Student VALUES (9521105, 陳冬, 男, 18, 信息系)例52在SC表中插入一新記錄，成績(jī)暫缺。INS

41、ERT INTO SC(Sno, Cno, XKLB) VALUES(9521105, c01, 必修) 實(shí)際插入的值為： (9521105, c01 ，NULL ，必修) 第92頁(yè)/共117頁(yè)更新數(shù)據(jù) 用UPDATE語(yǔ)句實(shí)現(xiàn)。格式： UPDATE SET , n WHERE 第93頁(yè)/共117頁(yè)無(wú)條件更新例53. 將所有學(xué)生的年齡加1。UPDATE Student SET Sage = Sage + 1第94頁(yè)/共117頁(yè)有條件更新1. 基于本表?xiàng)l件的更新例54. 將9512101學(xué)生的年齡改為21歲UPDATE Student SET Sage = 21WHERE Sno = 951210

42、1 第95頁(yè)/共117頁(yè)2. 基于其他表?xiàng)l件的更新例55：將計(jì)算機(jī)系全體學(xué)生的成績(jī)加5分。 （1）用子查詢實(shí)現(xiàn)）用子查詢實(shí)現(xiàn)UPDATE SC SET Grade = Grade + 5 WHERE Sno IN(SELECT Sno FROM Student WHERE Sdept = 計(jì)算機(jī)系計(jì)算機(jī)系 ) （2）用多表連接實(shí)現(xiàn)）用多表連接實(shí)現(xiàn)UPDATE SC SET Grade = Grade + 5 FROM SC JOIN Student ON SC.Sno = Student.Sno WHERE Sdept = 計(jì)算機(jī)系第96頁(yè)/共117頁(yè)刪除數(shù)據(jù) 用DELETE語(yǔ)句實(shí)現(xiàn)格式：DE

43、LETE FROM WHERE 第97頁(yè)/共117頁(yè)無(wú)條件刪除無(wú)條件刪除 例56. 刪除所有學(xué)生的選課記錄。DELETE FROM SC 第98頁(yè)/共117頁(yè)有條件刪除有條件刪除 （1）基于本表?xiàng)l件的刪除。例57刪除所有不及格學(xué)生的修課記錄。 DELETE FROM SC WHERE Grade 60 第99頁(yè)/共117頁(yè)基于其他表?xiàng)l件的刪除例58刪除計(jì)算機(jī)系不及格學(xué)生的修課記錄。 （1）用子查詢實(shí)現(xiàn)DELETE FROM SC WHERE Grade 60 AND Sno IN ( SELECT Sno FROM Student WHERE Sdept = 計(jì)算機(jī)系 ) （2）用多表連接實(shí)現(xiàn)

44、DELETE FROM SC FROM SC JOIN Student ON SC.Sno = Student.SnoWHERE Sdept = 計(jì)算機(jī)系A(chǔ)ND Grade 、=、 和 =返回一個(gè)范圍值的查詢。 被連續(xù)訪問(wèn)的列。 返回大型結(jié)果集的查詢。 經(jīng)常被用作聯(lián)接的列，一般來(lái)說(shuō)，這些是外碼列。 對(duì) ORDER BY 或 GROUP BY 子句中指定的列進(jìn)行索引。 第107頁(yè)/共117頁(yè)聚簇索引不適用于聚簇索引不適用于頻繁更改的列。因?yàn)檫@將導(dǎo)致整行移動(dòng)。字節(jié)長(zhǎng)的列。因?yàn)榫鄞厮饕逆I值將被所有非聚簇索引作為查找鍵使用，并被存儲(chǔ)在每個(gè)非聚簇索引的B樹(shù)的葉級(jí)索引項(xiàng)中。 第108頁(yè)/共117頁(yè)非聚簇

45、索引非聚簇索引 數(shù)據(jù)存儲(chǔ)在一個(gè)地方，索引存儲(chǔ)在另一個(gè)地方。 索引帶有指向數(shù)據(jù)的存儲(chǔ)位置的指針。 索引中的索引項(xiàng)按索引鍵值順序存儲(chǔ)，而表中的信息按另一種順序存儲(chǔ)。 與圖書(shū)的目錄類(lèi)似。 第109頁(yè)/共117頁(yè)非聚簇索引特點(diǎn)非聚簇索引特點(diǎn)數(shù)據(jù)行不按非聚簇索引鍵的順序排序和存儲(chǔ)。非聚簇索引的葉層不包含數(shù)據(jù)頁(yè)。非聚簇索引B樹(shù)的葉節(jié)點(diǎn)包含索引行。每個(gè)索引行包含非聚簇索引鍵值以及一個(gè)或多個(gè)行定位器，這些行定位器指向該鍵值對(duì)應(yīng)的數(shù)據(jù)行?？梢栽谟芯鄞厮饕谋砗蜔o(wú)聚簇索引的表上定義。 第110頁(yè)/共117頁(yè)SQL Server非聚簇索引中的行定位器非聚簇索引中的行定位器有兩種形式：如果表沒(méi)有聚簇索引，則行定位器就

46、是指向行的指針。該指針用文件標(biāo)識(shí)符 (ID)、頁(yè)碼和頁(yè)上的行數(shù)生成。整個(gè)指針?lè)Q為行ID。如果表有聚簇索引，則行定位器就是行的聚簇索引鍵值。通過(guò)使用聚簇索引鍵搜索聚簇索引來(lái)檢索數(shù)據(jù)行，而聚簇索引鍵存儲(chǔ)在非聚簇索引的葉節(jié)點(diǎn)行內(nèi)。 第111頁(yè)/共117頁(yè)非聚簇索的考慮非聚簇索的考慮可考慮將非聚簇索引用于： 包含大量非重復(fù)值的列。不返回大型結(jié)果集的查詢。經(jīng)常作為查詢條件使用的列。經(jīng)常作為連接和分組條件的列。 第112頁(yè)/共117頁(yè)唯一索引唯一索引可以確保索引列不包含重復(fù)的值。在多列唯一索引的情況下，該索引可以確保索引列中每個(gè)值的組合都是唯一的。聚簇索引和非聚簇索引都可以是唯一的。只要列中的數(shù)據(jù)是唯一的

47、，就可以在同一個(gè)表上創(chuàng)建一個(gè)唯一的聚簇索引和多個(gè)唯一的非聚簇索引。 第113頁(yè)/共117頁(yè)創(chuàng)建和刪除索引創(chuàng)建索引：CREATE UNIQUE CLUSTERED|NONCLUSTERED INDEX ON ( , n ） UNIQUE：創(chuàng)建唯一索引。 CLUSTERED：創(chuàng)建聚簇索引。 NONCLUSTERED：創(chuàng)建聚簇索引。 如果沒(méi)有指定索引類(lèi)型，則默認(rèn)是創(chuàng)建非聚簇索引。 第114頁(yè)/共117頁(yè)示例 例1為Student表的Sname列創(chuàng)建非聚簇索引。CREATE INDEX Sname_ind ON Stuent ( Sname ) 例2為Student表的Sid列創(chuàng)建惟一的聚簇索引。CREATE UNIQUE CLUSTERED INDEX Sid_ind ON Stuent (Sid ) 第115頁(yè)/共117頁(yè)刪除索引 刪除索引語(yǔ)句格式：DROP INDEX 例3刪除Student表中的Sname_ind索引。 DROP INDEX Sname_ind 第116頁(yè)/共117頁(yè)感謝您的觀看。第117頁(yè)/共117頁(yè)