數(shù)據(jù)庫(kù)原理與應(yīng)用第三章課件

第三章關(guān)系數(shù)據(jù)庫(kù)標(biāo)準(zhǔn)語(yǔ)言 SQL3.1SQL概述SQL的特點(diǎn)1.綜合統(tǒng)一2.高度非過(guò)程化3.面向集合的操作方式4.以同一種語(yǔ)法結(jié)構(gòu)提供兩種使用方法5.語(yǔ)言簡(jiǎn)潔，易學(xué)易用5.語(yǔ)言簡(jiǎn)捷，易學(xué)易用3.2數(shù)據(jù)定義3.2.1定義語(yǔ)句格式CREATETABLE<表名>(<列名><數(shù)據(jù)類型>[<列級(jí)完整性約束>],<列名><數(shù)據(jù)類型>[<列級(jí)完整性約束>],……,[<表級(jí)完整性約束>])[<其它參數(shù)>]?

<表名>給出要?jiǎng)?chuàng)立的根本表的名稱；?

<列名>給出列名或字段名；?

<數(shù)據(jù)類型>?

<列級(jí)完整性約束>?

<表級(jí)完整性約束>?

<其它參數(shù)>為列指定數(shù)據(jù)類型及其數(shù)據(jù)寬度；關(guān)系數(shù)據(jù)庫(kù)支持非常豐富的數(shù)據(jù)類型，不同的數(shù)據(jù)庫(kù)管理系統(tǒng)支持的數(shù)據(jù)類型根本是一樣的，右表列出了常用的數(shù)據(jù)類型。數(shù)據(jù)類型用于定義列或字段一級(jí)的完整性約束，一般包括：NOTNULL和NULL約束

PRIMARYKEY約束

UNIQUE約束

FOREIGNKEY約束

DEFAULT定義

CHECK約束

列級(jí)完整性約束用于定義表一級(jí)的完整性約束，一般包括：PRIMARYKEY約束〔復(fù)合屬性構(gòu)成的主關(guān)鍵字說(shuō)明〕FOREIGNKEY約束〔外部關(guān)鍵字及參照關(guān)系說(shuō)明〕CHECK約束〔同時(shí)涉及到多個(gè)屬性的域完整性約束〕表級(jí)完整性約束不是SQL的標(biāo)準(zhǔn)選項(xiàng)，一般用于與物理存儲(chǔ)有關(guān)的說(shuō)明，不同的數(shù)據(jù)庫(kù)管理系統(tǒng)定義的方式肯定不同，另外該項(xiàng)參數(shù)一般也不是必需的。

其他參數(shù)例題[例1]建立一個(gè)“學(xué)生〞表Student，它由學(xué)號(hào)Sno、姓名Sname、性別Ssex、年齡Sage、所在系Sdept五個(gè)屬性組成。其中學(xué)號(hào)不能為空，值是唯一的，并且姓名取值也唯一。CREATETABLEStudent(SnoCHAR(5)NOTNULLUNIQUE，SnameCHAR(20)UNIQUE，SsexCHAR(1)，SageINT，SdeptCHAR(15))；二、修改根本表ALTERTABLE<表名>[ADD<新列名><數(shù)據(jù)類型>[<列級(jí)完整性約束>]]|[DROP<完整性約束名>][MODIFY<列名><數(shù)據(jù)類型>][DROPCOLUMN<列名>][ALTERCOLUMN<列名><數(shù)據(jù)類型>[<列級(jí)完整性約束>]]例題[例2]向Student表增加“入學(xué)時(shí)間〞列，其數(shù)據(jù)類型為日期型。ALTERTABLEStudentADDScomeDATE；不管根本表中原來(lái)是否已有數(shù)據(jù)，新增加的列一律為空值。

例題[例3]將年齡的數(shù)據(jù)類型改為半字長(zhǎng)整數(shù)。

ALTERTABLEStudentMODIFYSageSMALLINT；修改原有的列定義可能會(huì)破壞已有數(shù)據(jù)。

例題[例4]刪除關(guān)于學(xué)號(hào)必須取唯一值的約束。ALTERTABLEStudentDROPUNIQUE〔Sno〕；SQL沒(méi)有提供刪除屬性列的語(yǔ)句，只能間接實(shí)現(xiàn)，先將原表中要保存的列及其內(nèi)容復(fù)制到一個(gè)新表中，然后刪除原表，并將新表命名為原表名。

三、刪除根本表 DROPTABLE<表名>;

根本表刪除 數(shù)據(jù)、表上的索引都刪除 表上的視圖往往仍然保存，但 無(wú)法引用刪除根本表時(shí)，系統(tǒng)會(huì)自動(dòng)從數(shù)據(jù)字典中刪去有關(guān)該根本表及其索引的描述，因此建立在此表上的視圖雖然已保存，但已無(wú)法引用例題例5刪除Student表DROPTABLEStudent;3.2.2建立與刪除索引建立索引是加快查詢速度的有效手段建立索引DBA或表的屬主〔即建立表的人〕根據(jù)需要建立和刪除有些DBMS自動(dòng)建立以以下上的索引PRIMARYKEYUNIQUE維護(hù)索引DBMS自動(dòng)完成

使用索引DBMS自動(dòng)選擇是否使用索引以及使用哪些索引一、建立索引語(yǔ)句格式CREATE[UNIQUE][CLUSTER]INDEX<索引名>ON<表名>(<列名>[<次序>][,<列名>[<次序>]]…)； 用<表名>指定要建索引的根本表名字索引可以建立在該表的一列或多列上，各列名之間用逗號(hào)分隔用<次序>指定索引值的排列次序，升序：ASC，降序：DESC。缺省值：ASCUNIQUE說(shuō)明此索引的每一個(gè)索引值只對(duì)應(yīng)唯一的數(shù)據(jù)記錄CLUSTER表示要建立的索引是聚簇索引建立索引〔續(xù)〕唯一值索引對(duì)于已含重復(fù)值的屬性列不能建UNIQUE索引對(duì)某個(gè)列建立UNIQUE索引后，插入新記錄時(shí)DBMS會(huì)自動(dòng)檢查新記錄在該列上是否取了重復(fù)值。這相當(dāng)于增加了一個(gè)UNIQUE約束建立索引〔續(xù)〕聚簇索引建立聚簇索引后，基表中數(shù)據(jù)也需要按指定的聚簇屬性值的升序或降序存放。也即聚簇索引的索引項(xiàng)順序與表中記錄的物理順序一致例：CREATECLUSTERINDEXStusnameONStudent(Sname)；在Student表的Sname〔姓名〕列上建立一個(gè)聚簇索引，而且Student表中的記錄將按照Sname值的升序存放建立索引〔續(xù)〕在一個(gè)根本表上最多只能建立一個(gè)聚簇索引聚簇索引的用途：對(duì)于某些類型的查詢，可以提高查詢效率聚簇索引的適用范圍很少對(duì)基表進(jìn)行增刪操作很少對(duì)其中的變長(zhǎng)列進(jìn)行修改操作聚簇索引是指索引項(xiàng)的順序與表中記錄的物理順序一致的索引組織。例CREATECLUSTERINDEXStusnameONStudent(Sname);將會(huì)在Student表的Sname列上建一個(gè)聚簇索引，而且記錄會(huì)按照Sname值的升序存放。用戶可以在最常查詢的列上建立聚簇索引以提高查詢效率。但在一個(gè)根本表上只能建一個(gè)，而且更新索引列數(shù)據(jù)會(huì)導(dǎo)致表中記錄的物理順序的變更，因此經(jīng)常更新的數(shù)據(jù)列不宜建立。例題[例6]為學(xué)生-課程數(shù)據(jù)庫(kù)中的Student，Course，SC三個(gè)表建立索引。其中Student表按學(xué)號(hào)升序建唯一索引，Course表按課程號(hào)升序建唯一索引，SC表按學(xué)號(hào)升序和課程號(hào)降序建唯一索引。CREATEUNIQUEINDEXStusnoONStudent(Sno)CREATEUNIQUEINDEXCoucnoONCourse(Cno)；CREATEUNIQUEINDEXSCnoONSC(SnoASC，CnoDESC)；二、刪除索引DROPINDEX<索引名>；刪除索引時(shí)，系統(tǒng)會(huì)從數(shù)據(jù)字典中刪去有關(guān)該索引的描述。[例7]刪除Student表的Stusname索引。 DROPINDEXStusname；3.3.1概述語(yǔ)句格式SELECT[ALL|DISTINCT]<目標(biāo)表達(dá)式>[,<目標(biāo)表達(dá)式>]…FROM<表名或視圖名>[,<表名或視圖名>…][WHERE<條件表達(dá)式>][GROUPBY<列名1>[HAVING<條件表達(dá)式>]][ORDERBY<列名2>[ASC|DESC]]；例如數(shù)據(jù)庫(kù)學(xué)生-課程數(shù)據(jù)庫(kù)學(xué)生表：Student(Sno，Sname，Ssex，Sage，Sdept)課程表：Course(Cno，Cname，Cpno，Ccredit)學(xué)生選課表：SC(Sno，Cno，Grade)3.3查詢3.3.1概述3.3.2單表查詢3.3.3連接查詢3.3.4嵌套查詢3.3.5集合查詢3.3.6小結(jié)3.3.2單表查詢查詢僅涉及一個(gè)表，是一種最簡(jiǎn)單的查詢操作一、選擇表中的假設(shè)干列二、選擇表中的假設(shè)干元組三、對(duì)查詢結(jié)果排序四、使用集函數(shù)五、對(duì)查詢結(jié)果分組從職工關(guān)系中檢索所有工資值SELECT工資FROM職工結(jié)果是：12201210125012301250SELECTDISTINCT工資FROM職工結(jié)果是：1220121012501230單表查詢用戶在查詢時(shí)可根據(jù)應(yīng)用的需要改變列的顯示順序例2查詢?nèi)w學(xué)生的姓名、學(xué)號(hào)、所在系。查詢?nèi)苛?將所有的列名在SELECT后面列出或者用*表示列名例3SELECT*FROMStudent;查詢經(jīng)過(guò)計(jì)算的值例4查詢?nèi)w學(xué)生姓名及其出生年份SELECTSname,1996-SageFROMStudent;單表查詢<目標(biāo)列表達(dá)式>不僅可以是算術(shù)表達(dá)式，還可以是字符串常量、函數(shù)等。例5查詢?nèi)w學(xué)生的姓名、出生年份和所有系，要求用小寫字母表示所有系名SELECTSname,’YearofBirth:’,1996-Sage,ISLOWER(Sdept)FROMStudent;輸出結(jié)果：Sname’YearofBirth:’1996-SageISLOWER(Sdept)------------------------------------------------------李勇YearofBirth:1976cs劉晨YearofBirth:1977is王名YearofBirth:1978ma張立YearofBirth:1977is[例5.*]使用列別名改變查詢結(jié)果的列標(biāo)題SELECTSnameNAME，'YearofBirth:’

BIRTH，

2000-SageBIRTHDAY，ISLOWER(Sdept)DEPARTMENTFROM

Student；輸出結(jié)果：

NAMEBIRTHBIRTHDAYDEPARTMENT------------------------------------------------------李勇YearofBirth:1976cs劉晨YearofBirth:1977is王名YearofBirth:1978ma張立YearofBirth:1977is二、選擇表中的假設(shè)干元組消除取值重復(fù)的行查詢滿足條件的元組1.消除取值重復(fù)的行在SELECT子句中使用DISTINCT短語(yǔ)假設(shè)SC表中有以下數(shù)據(jù) SnoCnoGrade---------------------9500119295001285950013889500229095002380ALL與

DISTINCT

[例6]查詢選修了課程的學(xué)生學(xué)號(hào)。(1)SELECTSnoFROMSC;(因?yàn)槟J(rèn)的是ALL)SELECTALLSnoFROMSC;

所以結(jié)果為：Sno-------9500195001950019500295002例題〔續(xù)〕(2)SELECTDISTINCTSnoFROMSC;

結(jié)果：

Sno-------9500195002總結(jié)：兩個(gè)本來(lái)并不完全相同的元組，投影到指定的某些列上后，可能變成完全相同的行，于是必須用DISTINCT短語(yǔ)或象下面那樣用WHERE語(yǔ)句。2.查詢滿足條件的元組WHERE子句常用的查詢條件(1)比較大小在WHERE子句的<比較條件>中使用比較運(yùn)算符=，>，<，>=，<=，!=或<>，!>，!<，邏輯運(yùn)算符NOT+比較運(yùn)算符[例8]查詢所有年齡在20歲以下的學(xué)生姓名及其年齡。SELECTSname，SageFROMStudentWHERESage<20；或SELECTSname，SageFROMStudentWHERENOTSage>=20；例9查詢考試成績(jī)有不及格的學(xué)生的學(xué)號(hào)SELECTDISTINCTSnoFROMSCWHEREGrade<60;(2)確定范圍使用謂詞BETWEEN…(下限)AND…〔上限〕NOTBETWEEN…AND…[例10]查詢年齡在20~23歲〔包括20歲和23歲〕之間的學(xué)生的姓名、系別和年齡。SELECTSname，Sdept，SageFROMStudentWHERESageBETWEEN20AND23；例題〔續(xù)〕[例11]查詢年齡不在20~23歲之間的學(xué)生姓名、系別和年齡。SELECTSname，Sdept，SageFROMStudentWHERESageNOTBETWEEN20AND23；

(3)確定集合使用謂詞IN<值表>,NOTIN<值表><值表>：用逗號(hào)分隔的一組取值[例12]查詢信息系〔IS〕、數(shù)學(xué)系〔MA〕和計(jì)算機(jī)科學(xué)系〔CS〕學(xué)生的姓名和性別。SELECTSname，SsexFROMStudentWHERESdeptIN('IS'，'MA'，'CS');(3)確定集合[例13]查詢既不是信息系、數(shù)學(xué)系，也不是計(jì)算機(jī)科學(xué)系的學(xué)生的姓名和性別。SELECTSname，SsexFROMStudent WHERESdeptNOTIN('IS'，'MA'，'CS');(4)字符串匹配[NOT]LIKE‘<匹配串>’[ESCAPE‘<換碼字符>’]<匹配串>：指定匹配模板匹配模板：固定字符串或含通配符的字符串當(dāng)匹配模板為固定字符串時(shí)，即不含通配符時(shí)可以用=運(yùn)算符取代LIKE謂詞用!=或<>運(yùn)算符取代NOTLIKE謂詞通配符%(百分號(hào))代表任意長(zhǎng)度〔長(zhǎng)度可以為0〕的字符串例：a%b表示以a開頭，以b結(jié)尾的任意長(zhǎng)度的字符串。如acb，addgb，ab等都滿足該匹配串_(下橫線)代表任意單個(gè)字符例：a_b表示以a開頭，以b結(jié)尾的長(zhǎng)度為3的任意字符串。如acb，afb等都滿足該匹配串ESCAPE短語(yǔ)：當(dāng)用戶要查詢的字符串本身就含有%或_時(shí)，要使用ESCAPE'<換碼字符>'短語(yǔ)對(duì)通配符進(jìn)行轉(zhuǎn)義。例題1)匹配模板為固定字符串[例14]查詢學(xué)號(hào)為95001的學(xué)生的詳細(xì)情況。

SELECT*FROMStudentWHERESnoLIKE'95001'；等價(jià)于：

SELECT*FROMStudentWHERESno='95001'；例題〔續(xù)〕2)匹配模板為含通配符的字符串[例15]查詢所有姓劉學(xué)生的姓名、學(xué)號(hào)和性別。

SELECTSname，Sno，SsexFROMStudentWHERESnameLIKE‘劉%’；例題〔續(xù)〕匹配模板為含通配符的字符串〔續(xù)〕[例16]查詢姓"歐陽(yáng)"且全名為三個(gè)漢字的學(xué)生的姓名。SELECTSnameFROMStudentWHERESnameLIKE'歐陽(yáng)__'；例題〔續(xù)〕匹配模板為含通配符的字符串〔續(xù)〕[例17]查詢名字中第2個(gè)字為"陽(yáng)"字的學(xué)生的姓名和學(xué)號(hào)。SELECTSname，SnoFROMStudentWHERESnameLIKE'__陽(yáng)%'；例題〔續(xù)〕匹配模板為含通配符的字符串〔續(xù)〕[例18]查詢所有不姓劉的學(xué)生姓名。SELECTSname，Sno，SsexFROMStudentWHERESnameNOTLIKE'劉%'；例題〔續(xù)〕3)使用換碼字符將通配符轉(zhuǎn)義為普通字符

[例19]查詢DB_Design課程的課程號(hào)和學(xué)分。SELECTCno，CcreditFROMCourseWHERECnameLIKE'DB\_Design'

ESCAPE'\'例題〔續(xù)〕使用換碼字符將通配符轉(zhuǎn)義為普通字符(續(xù))[例20]查詢以"DB_"開頭，且倒數(shù)第3個(gè)字符為i的課程的詳細(xì)情況。SELECT*FROMCourseWHERECnameLIKE'DB\_%i__'ESCAPE'\'；(5)涉及空值的查詢使用謂詞ISNULL或ISNOTNULL“ISNULL〞不能用“=NULL〞代替[例21]某些學(xué)生選修課程后沒(méi)有參加考試，所以有選課記錄，但沒(méi)有考試成績(jī)。查詢?nèi)鄙俪煽?jī)的學(xué)生的學(xué)號(hào)和相應(yīng)的課程號(hào)。SELECTSno，CnoFROMSCWHEREGradeISNULL；例題(續(xù))[例22]查所有有成績(jī)的學(xué)生學(xué)號(hào)和課程號(hào)。SELECTSno，CnoFROMSCWHEREGradeISNOTNULL；(6)多重條件查詢用邏輯運(yùn)算符AND和OR來(lái)聯(lián)結(jié)多個(gè)查詢條件

AND的優(yōu)先級(jí)高于OR

可以用括號(hào)改變優(yōu)先級(jí)可用來(lái)實(shí)現(xiàn)多種其他謂詞

[NOT]IN[NOT]BETWEEN…AND…例題[例23]查詢計(jì)算機(jī)系年齡在20歲以下的學(xué)生姓名。

SELECTSnameFROMStudentWHERESdept='CS'ANDSage<20；改寫[例12][例12]查詢信息系〔IS〕、數(shù)學(xué)系〔MA〕和計(jì)算機(jī)科學(xué)系〔CS〕學(xué)生的姓名和性別。SELECTSname，SsexFROMStudentWHERESdeptIN('IS'，'MA'，'CS')可改寫為：SELECTSname，SsexFROMStudentWHERESdept='IS'ORSdept='MA'ORSdept='CS'；改寫[例10][例10]查詢年齡在20~23歲〔包括20歲和23歲〕之間的學(xué)生的姓名、系別和年齡。

SELECTSname，Sdept，SageFROMStudentWHERESageBETWEEN20AND23；可改寫為：SELECTSname，Sdept，SageFROMStudentWHERESage>=20ANDSage<=23；三、對(duì)查詢結(jié)果排序 假設(shè)沒(méi)有指定查詢結(jié)果的顯示順序，DBMS按最方便的〔元組在表中的先后〕方式輸出查詢結(jié)果使用ORDERBY子句可以按一個(gè)或多個(gè)屬性列排序升序：ASC；降序：DESC；缺省值為升序當(dāng)排序列含空值時(shí)ASC：排序列為空值的元組最后顯示DESC：排序列為空值的元組最先顯示對(duì)查詢結(jié)果排序〔續(xù)〕[例24]查詢選修了3號(hào)課程的學(xué)生的學(xué)號(hào)及其成績(jī)，查詢結(jié)果按分?jǐn)?shù)降序排列。

SELECTSno，GradeFROMSCWHERECno='3'ORDERBYGradeDESC；查詢結(jié)果

SnoGrade--------------9501095024950079295003829501082950097595014619500255對(duì)查詢結(jié)果排序〔續(xù)〕[例25]查詢?nèi)w學(xué)生情況，查詢結(jié)果按所在系的系號(hào)升序排列，同一系中的學(xué)生按年齡降序排列。

SELECT*FROMStudentORDERBYSdept，SageDESC；四、使用集函數(shù)5類主要集函數(shù)計(jì)數(shù)COUNT〔[DISTINCT|ALL]*〕COUNT〔[DISTINCT|ALL]<列名>〕計(jì)算總和，〔此列必須是數(shù)值型的〕SUM〔[DISTINCT|ALL]<列名>〕 計(jì)算平均值，〔此列必須是數(shù)值型的〕AVG〔[DISTINCT|ALL]<列名>〕使用集函數(shù)〔續(xù)〕求最大值MAX〔[DISTINCT|ALL]<列名>〕 求最小值MIN〔[DISTINCT|ALL]<列名>〕 DISTINCT短語(yǔ)：在計(jì)算時(shí)要取消指定列中的重復(fù)值A(chǔ)LL短語(yǔ)：不取消重復(fù)值A(chǔ)LL為缺省值使用集函數(shù)〔續(xù)〕[例26]查詢學(xué)生總?cè)藬?shù)。SELECTCOUNT(*)FROMStudent；

[例27]查詢選修了課程的學(xué)生人數(shù)。SELECTCOUNT(DISTINCTSno)FROMSC；注：用DISTINCT以