數(shù)據(jù)庫(kù)技術(shù)及應(yīng)用：關(guān)系數(shù)據(jù)庫(kù)標(biāo)準(zhǔn)語(yǔ)言第SQL

關(guān)系數(shù)據(jù)庫(kù)標(biāo)準(zhǔn)語(yǔ)言SQL2024/5/6第2頁(yè)3.1SQL概述SQL（StructuredQueryLanguage）,1974年被提出關(guān)系數(shù)據(jù)庫(kù)的國(guó)際標(biāo)準(zhǔn)語(yǔ)言目前仍被不斷擴(kuò)充

SQL89，SQL92，SQL99,SQL2003介于關(guān)系代數(shù)和關(guān)系演算之間的結(jié)構(gòu)化語(yǔ)言2024/5/6第3頁(yè)SQL的特點(diǎn)綜合統(tǒng)一：集DDL、DML、DCL功能于一體，可獨(dú)立完成數(shù)據(jù)庫(kù)生命周期中的全部活動(dòng)，語(yǔ)言風(fēng)格統(tǒng)一高度非過(guò)程化：用戶只需提出“做什么”，而無(wú)需指明“怎么做

”面向集合的操作方式：操作對(duì)象、查詢結(jié)果、更新數(shù)據(jù)均可以是元組的集合提供兩種使用方式：自含式、嵌入式語(yǔ)言簡(jiǎn)捷，易學(xué)易用SQL功能關(guān)鍵動(dòng)詞數(shù)據(jù)查詢SELECT數(shù)據(jù)定義CREATE,DROP,ALTER數(shù)據(jù)操縱INSERT,UPDATE,DELETE數(shù)據(jù)控制GRANT,REVOKE2024/5/6第4頁(yè)SQL語(yǔ)言的基本概念SQL支持關(guān)系數(shù)據(jù)庫(kù)的三級(jí)模式結(jié)構(gòu)存儲(chǔ)文件：組成關(guān)系數(shù)據(jù)庫(kù)的內(nèi)模式，對(duì)用戶透明基表(BaseTable)：組成關(guān)系數(shù)據(jù)庫(kù)的模式，一個(gè)關(guān)系對(duì)應(yīng)一個(gè)基表，一或多個(gè)基表對(duì)應(yīng)一個(gè)存儲(chǔ)文件視圖(View)：組成關(guān)系數(shù)據(jù)庫(kù)的外模式，從一個(gè)或多個(gè)基表中導(dǎo)出，不獨(dú)立存儲(chǔ)在數(shù)據(jù)庫(kù)中SQL視圖1視圖2基本表1基本表2基本表3基本表4存儲(chǔ)文件1存儲(chǔ)文件2外模式模式內(nèi)模式2024/5/6第5頁(yè)數(shù)據(jù)定義SQL的數(shù)據(jù)定義語(yǔ)言(DDL)操作對(duì)象操作方式創(chuàng)建刪除修改基表CREATETABLEDROPTABLEALTERTABLE視圖CREATEVIEWDROPVIEWALTERVIEW索引CREATEINDEXDROPINDEX2024/5/6第6頁(yè)

數(shù)據(jù)查詢（Select）數(shù)據(jù)庫(kù)查詢是數(shù)據(jù)庫(kù)的核心操作，SQL提供了基于集合和關(guān)系的查詢操作，具有豐富的功能和靈活的使用方式一個(gè)SQL查詢的結(jié)果是一個(gè)關(guān)系查詢可分為單表查詢：查詢只涉及一個(gè)表連接查詢：查詢同時(shí)涉及兩個(gè)以上的表嵌套查詢：一個(gè)查詢塊嵌套在另一個(gè)查詢塊中視圖查詢：在視圖基礎(chǔ)上的查詢2024/5/6第7頁(yè)SQL詢語(yǔ)句的格式查詢語(yǔ)句的典型格式這個(gè)查詢與下面的關(guān)系代數(shù)表達(dá)式等價(jià)：

A1,A2,...,An(

P(r1xr2x...xrm))SELECT[ALL|DISTINCT]<目標(biāo)列表達(dá)式>[,<目標(biāo)列表達(dá)式>]…FROM<表名或視圖名>[,<表名或視圖名>]…[WHERE<條件表達(dá)式>][GROUPBY<列名1>[HAVING<條件表達(dá)式>]][ORDERBY<列名2>[ASC|DESC]];selectA1,A2,...,An

from

r1,r2,...,rm

whereP2024/5/6第8頁(yè)SELECT*FROMStudent;3.4.1單表查詢一、選擇表中若干列查詢指定列：在<目標(biāo)列表達(dá)式>中指定預(yù)查屬性查詢?nèi)苛校涸?lt;目標(biāo)列表達(dá)式>中使用*查詢經(jīng)計(jì)算的值：在<目標(biāo)列表達(dá)式>中可使用常量、表達(dá)式、函數(shù)等SELECTSname,‘YearofBirth:’,2013-SageFROMStudent;SELECTSno,SnameFROMStudent;2024/5/6第9頁(yè)查詢實(shí)例SELECTSname,‘YearofBirth:’,2013-Sage,LOWER(Sdept)FROMStudent;大寫(xiě)：UPPER結(jié)果為：

Sname‘yearofBirth:’2013-SageLOWER(Sdept)

————————————————————————

李勇

YearofBirht:1982 cs

劉晨

YearofBirht:1983 is

王敏

YearofBirht:1984 ma

張立

YearofBirht:1983 is2024/5/6第10頁(yè)定義別名用戶可以通過(guò)指定別名來(lái)改變查詢結(jié)果的列標(biāo)題，這對(duì)于含算術(shù)表達(dá)式、常量、函數(shù)名的目標(biāo)列表達(dá)式尤為有用。例如對(duì)于上例，可以定義如下列別名：SELECTSnameNAME,’YearofBirth:’BIRTH,2013-SageYEAR,LOWER(Sdept)DEPARTMENTFROMStudent;結(jié)果為：

NAMEBIRTHYEARDEPARTMENT

——————————————————-----——————

李勇

YearofBirth:1982cs

劉晨

YearofBirth:1983if

王敏

YearofBirth:1984ma

張立

YearofBirth:1983if2024/5/6第11頁(yè)二、選擇表中若干元組取消取值重復(fù)的列：指定DISTINCT短語(yǔ)查詢滿足條件的元組：在WHERE子句中指定條件對(duì)查詢結(jié)果排序：使用ORDERBY使用集函數(shù)：COUNT,SUM,AVG,MAX,MIN對(duì)查詢結(jié)果分組：使用GROUPBY，HAVING2024/5/6第12頁(yè)1.取消取值重復(fù)的行DISTINCT應(yīng)用兩個(gè)本來(lái)不完全相同的元組，投影到指定列時(shí)，可能出現(xiàn)相同的行，SQL允許查詢結(jié)果出現(xiàn)重復(fù)的元組/行，如果需要去掉重復(fù)的元組/行，必須指定DISTINCT

短語(yǔ)，缺省為ALL例:

查詢選修了課結(jié)果為

： 結(jié)果為：

Sno Sno

———

——— 95001 95001 95001 95002 95001 95002 95002SELECTDISTINCTSnoFROMSC;SELECTSnoFROMSC;或

SELECTALLSnoFROMSC;2024/5/6第13頁(yè)2.查詢滿足條件的元組：WHERE子句WHERE子句常用的查詢條件:查詢條件謂詞比較=,>,<,>=,<=,!=,<>,!>,!<;NOT+上述比較運(yùn)算符確定范圍BETWEENAND,NOTBETWEENAND確定集合IN,NOTIN字符匹配LIKE,NOTLIKE空值ISNULL,ISNOTNULL多重條件AND,OR2024/5/6第14頁(yè)Where應(yīng)用之比較大小SELECTSnameFROMStudentWHERESdept=‘CS’;SELECTDISTINCTSnoFROMSCWHEREGrade<60;例

查詢計(jì)算機(jī)系全體學(xué)生的名單例查詢考試成績(jī)有不及格的學(xué)生的學(xué)號(hào)例

查詢所有年齡在20歲以下的學(xué)生姓名及其年齡SELECTSname,Sage SELECTSname,SageFROMStudent FROMStudentWHERESage<20; WHERENOTSage>=20;2024/5/6第15頁(yè)Where應(yīng)用之確定范圍SELECTSname,Sdept,SageFROMStudentWHERESageBETWEEN20AND40;例查詢年齡在20~40歲之間的學(xué)生姓名、系別和年齡SELECTSname,Sdept,SageFROMStudentWHERESageNOTBETWEEN20AND23;例查詢年齡不在20~23歲之間的學(xué)生姓名、系別和年齡2024/5/6第16頁(yè)Where應(yīng)用之確定集合SELECTSname,SsexFROMStudentWHERESdeptIN(‘IS’,‘MA’,‘CS’);例

查詢信息系(IS)、數(shù)學(xué)系(MA)和計(jì)算機(jī)系(CS)學(xué)生的姓名和性別SELECTSname,SsexFROMStudentWHERESdeptNOTIN(‘IS’,’MA’,’CS’)例

查詢除信息系(IS)、數(shù)學(xué)系(MA)和計(jì)算機(jī)系(CS)以外其它系學(xué)生的姓名和性別2024/5/6第17頁(yè)Where應(yīng)用之字符串匹配謂詞LIKE可以用來(lái)進(jìn)行字符串的匹配格式：[NOT]LIKE’<匹配串>’[ESCAPE’<換碼字符>’]匹配串中%代表任意長(zhǎng)度的字符串，如a%b：acb,addgb,ab匹配串中_代表任意單個(gè)字符，如a_b：acbafb例

查詢學(xué)號(hào)為95001的學(xué)生的詳細(xì)情況：相當(dāng)于SELECT*FROMStudentWHERESnoLIKE‘95001’;SELECT*FROMStudentWHERESno=‘95001’;2024/5/6第18頁(yè)通配符例子SELECT*FROMStudentWHERESnameLIKE‘劉%’;SELECT*FROMStudentWHERESnameLIKE‘__明__’;例

查詢所有姓劉的學(xué)生的詳細(xì)信息例

查詢所有全名為三個(gè)字且中間漢字為“明”的學(xué)生的詳細(xì)信息2024/5/6第19頁(yè)換碼字符例查詢DB_Design課程的課程號(hào)和學(xué)分例查詢以“DB_”開(kāi)頭，且倒數(shù)第3個(gè)字符為i的課程的詳細(xì)情況SELECTCno,CreditFROMCourseWHERECnameLIKE‘DB\_Design’ESCAPE‘\’;SELECT*FROMCourseWHERECnameLIKE‘DB\_%i__’

ESCAPE’\’;注：ESCAPE‘\’表示‘\’為轉(zhuǎn)義換碼字符，緊跟其后的_轉(zhuǎn)義為普通_字符2024/5/6第20頁(yè)Where應(yīng)用涉及空值的查詢SELECTSno,CnoFROMSCWHEREGradeISNULL;SELECTSno,CnoFROMSCWHEREGradeISNOTNULL;例：某些學(xué)生選修課程后沒(méi)有參加考試，所以有選修課記錄，但沒(méi)有考試成績(jī)。查詢?nèi)鄙俪煽?jī)的學(xué)生的學(xué)號(hào)和相應(yīng)的課程號(hào)：例查詢所有有成績(jī)的學(xué)生學(xué)號(hào)和課程號(hào)注意這里的“IS”不能用等號(hào)代替2024/5/6第21頁(yè)Where應(yīng)用多重條件查詢SELECTSnameFROMStudentWHERESdept=‘CS’ANDSage<20;SELECTSname,SsexFROMStudentWHERESdept=‘IS’ORSdept=‘MA’ORSdept=‘CS’;例查詢計(jì)算機(jī)系年齡在20歲以下的學(xué)生姓名例

查詢信息系(IS)、數(shù)學(xué)系(MA)和計(jì)算機(jī)系(CS)學(xué)生的姓名和性別其它表達(dá)方式？In（）2024/5/6第22頁(yè)3.對(duì)查詢結(jié)果排序OrderbySELECTSno,GradeFROMSCWHERECno=‘3’ORDERBYGradeDESC;SELECT*FROMStudentORDERBYSdept,SageDESC;例查詢選修了3號(hào)課程的學(xué)生的學(xué)號(hào)及其成績(jī)，查詢結(jié)果按分?jǐn)?shù)的降序排列例查詢?nèi)w學(xué)生情況，查詢結(jié)果按所在系的系號(hào)升序排列，同一系中的學(xué)生按年齡降序排列ORDERBY子句可對(duì)查詢結(jié)果按照一個(gè)或多個(gè)屬性列的升序（ASC）或降序（DESC）排列，缺省值為升序2024/5/6第23頁(yè)4.聚集函數(shù)COUNT([DISTINCT|ALL]*)統(tǒng)計(jì)元組個(gè)數(shù)COUNT([DISTINCT|ALL]<列名>)統(tǒng)計(jì)一列中值的個(gè)數(shù)SUM([DISTINCT|ALL]<列名>)計(jì)算一列值的總和AVG([DISTINCT|ALL]<列名>)計(jì)算一列值的平均值MAX([DISTINCT|ALL]<列名>)求一列值中的最大值MIN([DISTINCT|ALL]<列名>)求一列值中的最小值2024/5/6第24頁(yè)實(shí)例SELECTCOUNT(*)FROMStudent;SELECTCOUNT(DISTINCTSno)FROMSC;SELECTAVG(Grade)FROMSCWHERECno=‘1’;SELECTMAX(Grade)FROMSCWHERECno=‘1’;例查詢學(xué)生總?cè)藬?shù)例查詢選修了課程的學(xué)生人數(shù)例計(jì)算1號(hào)課程的學(xué)生平均成績(jī)例查詢選修1號(hào)課程的學(xué)生最高分?jǐn)?shù)2024/5/6第25頁(yè)5.對(duì)查詢結(jié)果分組GroupbyGROUPBY子句將查詢結(jié)果按某一列或多列分組，值相等的為一組。對(duì)查詢結(jié)果分組的目的是為了細(xì)化集函數(shù)的作用對(duì)象。如果分組后還要求按一定的條件對(duì)這些組進(jìn)行篩選，最終只輸出滿足指定條件的組，則可以是使用HAVING短語(yǔ)指定篩選條件。2024/5/6第26頁(yè)分組實(shí)例SELECTCno,COUNT(Sno)FROMSCGROUPBYCno;SELECTSnoFROMSCGROUPBYSnoHAVINGCOUNT(*)>3;例查詢選修了3門(mén)以上課程的學(xué)生學(xué)號(hào)例求各個(gè)課程號(hào)及相應(yīng)的選課人數(shù)Where與Having作用對(duì)象不同：where針對(duì)表、視圖；Having針對(duì)Groupby所分的候選組2024/5/6第27頁(yè)練習(xí)數(shù)據(jù)庫(kù)模式如下：倉(cāng)庫(kù)(倉(cāng)庫(kù)號(hào)，負(fù)責(zé)人)貨物(貨物號(hào)，貨物名，貨物描述)入庫(kù)記錄(貨物號(hào)，倉(cāng)庫(kù)號(hào)，數(shù)量，入庫(kù)日期，經(jīng)辦人號(hào))用SQL實(shí)現(xiàn)以下查詢要求：查詢倉(cāng)庫(kù)中有多少種不同的貨物 按貨物號(hào)統(tǒng)計(jì)每一種貨物在倉(cāng)庫(kù)中的總數(shù)量統(tǒng)計(jì)2008年1月1日以后每天入庫(kù)的貨物總數(shù)量2024/5/6第28頁(yè)SELECTCOUNT(DISTINCT貨物號(hào))FROM入庫(kù)記錄；SELECT入庫(kù)日期,SUM(數(shù)量)FROM入庫(kù)記錄GROUPBY入庫(kù)日期HAVING入庫(kù)日期>“2008/01/01”；SELECT貨物號(hào)，SUM(數(shù)量)FROM入庫(kù)記錄GROUPBY貨物號(hào)2024/5/6第29頁(yè)3.4.2連接查詢連接(Join)查詢查詢涉及兩個(gè)以上的表(在FROM子句中體現(xiàn))From子句對(duì)應(yīng)于關(guān)系代數(shù)中笛卡兒乘積運(yùn)算,它給出待掃描的關(guān)系/表(指定多個(gè)表)連接查詢的類(lèi)型等值連接查詢非等值連接查詢自然連接查詢自身連接查詢外連接查詢符合條件連接查詢2024/5/6第30頁(yè)一、等值與非等值連接查詢連接條件的形式[<表名1>.]<列名1><比較運(yùn)算符>[<表名2>.]<列名2>比較運(yùn)算符:=(等值連接)、>、<、>=、<=、!=連接字段必須是可比的[<表名1>.]<列名1>BETWEEN[<表名2>.]<列名2>AND[<表名2>.]<列名3>連接查詢的執(zhí)行過(guò)程首先在表1中找到第一個(gè)元組，然后從頭開(kāi)始掃描表2，逐一查找滿足連接條件的元組，找到后就將表1中的第一個(gè)元組與該元組拼接起來(lái)，形成結(jié)果表中的一個(gè)元組。表2全部查找完后，再找表1中第二個(gè)元組，然后再?gòu)念^開(kāi)始掃描表2。重復(fù)上述操作，直至表1中的全部元組都處理完畢為止。2024/5/6第31頁(yè)SELECTStudent.*,SC.*FROMStudent,SCWHEREStudent.Sno=SC.Sno;例子例33查詢每個(gè)學(xué)生及其選修課程的情況學(xué)生情況存放Student表中，學(xué)生選課情況存放在SC表中，所以本查詢實(shí)際上涉及Student與SC兩個(gè)表。通過(guò)公共屬性Sno實(shí)現(xiàn)聯(lián)系表名前綴等值連接使用表名前綴為避免混淆，若屬性名在各表中唯一，則可省略連接謂詞2024/5/6第32頁(yè)自然連接自然連接等值連接的特例在等值連接的基礎(chǔ)上將目標(biāo)列中重復(fù)的屬性去掉SELECTStudent.Sno,Sname,Ssex,Sage,Sdept,Cno,GradeFROMStudent,SCWHEREStudent.Sno=SC.Sno;例

查詢每個(gè)學(xué)生及其選修課程的情況2024/5/6第33頁(yè)二、自身連接一個(gè)表與其自己進(jìn)行連接SELECTFIRST.Cno,SECOND.CpnoFROMCourseFIRST,CourseSECONDWHEREFIRST.Cpno=SECOND.Cno;別名例查詢每一門(mén)課的間接先修課見(jiàn)P102表2024/5/6第34頁(yè)三、外連接是連接運(yùn)算的擴(kuò)充除了滿足連接條件的元組正常輸出外，所有不滿足連接條件的元組也進(jìn)行連接，并用空值NULL來(lái)填充并輸出外連接符：*（或+），表示增加一個(gè)萬(wàn)能行外連接分類(lèi)：右外連接(本例)、左外連接、SELECTStudent.Sno,Sname,Ssex,Sage,Sdept,Cno,GradeFROMStudentLEFTOUTJOINSCON(Student.Sno=SC.Sno);例查詢每個(gè)學(xué)生及其選修課程的情況2024/5/6第35頁(yè)四、復(fù)合條件連接WHERE子句中有多個(gè)連接條件SELECTStudent.Sno,SnameFROMStudent,SCWHEREStudent.Sno=SC.SnoANDSC.Cno=‘2’ANDSC.Grade>90;例查詢選修了2號(hào)課程且成績(jī)?cè)?0分以上的學(xué)生學(xué)號(hào)和姓名2024/5/6第36頁(yè)多表連接兩個(gè)以上的表的連接SELECTStudent.Sno,Sname,Cname,GradeFROMStudent,Course,SCWHEREStudent.Sno=SC.SnoANDSC.Cno=Course.Cno;例查詢每個(gè)學(xué)生的學(xué)號(hào)、姓名、選修的課程名及成績(jī)2024/5/6第37頁(yè)3.4.3嵌套查詢?cè)赟QL語(yǔ)言中，一個(gè)SELECT-FROM-WHERE語(yǔ)句稱(chēng)為一個(gè)查詢塊。一個(gè)查詢塊嵌套在另一個(gè)查詢塊的WHERE子句或HAVING短語(yǔ)的條件中的查詢。SELECTSnameFROMStudentWHERESnoIN

(SELECTSno FROMSC WHERECno=‘2’);如其等效不嵌套的查詢語(yǔ)句?（多表復(fù)合連接）2024/5/6第38頁(yè)嵌套查詢?cè)试S多層嵌套，體現(xiàn)了SQL的結(jié)構(gòu)化特色術(shù)語(yǔ)父查詢子查詢不相關(guān)子查詢（重點(diǎn)）：子查詢的查詢條件不依賴(lài)于父查詢相關(guān)子查詢：子查詢的查詢條件依賴(lài)于父查詢的某個(gè)屬性值求解方法由里向外，子查詢的結(jié)果用于建立其父查詢的查找條件 ★

ORDERBY只對(duì)最終結(jié)果排序有意義，子查詢不能使用2024/5/6第39頁(yè)一、帶有IN謂詞的子查詢嵌套查詢中，子查詢的結(jié)果往往是一個(gè)集合，所以經(jīng)常使用謂詞IN使用嵌套實(shí)現(xiàn)此查詢SELECTSno,Sname,SdeptFROMStudentWHERESdeptIN

(SELECTSdept FROMStudent WHERESname=‘劉晨’);例查詢與“劉晨”在同一個(gè)系學(xué)習(xí)的學(xué)生先分步實(shí)現(xiàn)此查詢(1)SELECTSdeptFROMstudentWHERESname=‘劉晨’結(jié)果為‘IS’SELECTSno,Sname,SdeptFROMStudentWHERESdept=‘IS’；（不相關(guān)子查詢）2024/5/6第40頁(yè)例子SELECTSno,Sname (3)FROMStudentWHERESnoIN (SELECTSno (2) FROMSC WHERECnoIN (SELECTCno (1) FROMCourse WHERECname=‘信息系統(tǒng)’));例查詢選修了課程名為“信息系統(tǒng)”的學(xué)生學(xué)號(hào)和姓名使用連接查詢實(shí)現(xiàn)使用嵌套查詢實(shí)現(xiàn)★多種實(shí)現(xiàn)方式，嵌套查詢具有結(jié)構(gòu)化程序設(shè)計(jì)特點(diǎn)SELECTSno,SnameFROMStudent,SC,CourseWHEREStudent.Sno=SC.SnoANDSC.Cno=Course.CnoANDCourse.Cname=‘信息系統(tǒng)’;（不相關(guān)子查詢）2024/5/6第41頁(yè)二、帶有比較運(yùn)算符的子查詢當(dāng)用戶能確切知道內(nèi)層查詢返回的是單值時(shí)，可以使用比較運(yùn)算符(>,<,>=,<=,!=等)注意：子查詢一定要跟在比較運(yùn)算符之后例查詢與“劉晨”在同一個(gè)系學(xué)習(xí)的學(xué)生SELECTSno,Sname,SdeptFROMStudentWHERESdept= (SELECTSdept FROMStudent WHERESname=‘劉晨’);（不相關(guān)子查詢）2024/5/6第42頁(yè)三、帶有ANY或ALL謂詞的子查詢與比較運(yùn)算符(>,<,>=,<=,!=等)配合使用>ANY(ALL)大于子查詢結(jié)果中的某個(gè)(所有)值<ANY(ALL)小于子查詢結(jié)果中的某個(gè)(所有)值>=ANY(ALL)大于等于子查詢結(jié)果中的某個(gè)(所有)值<=ANY(ALL)小于等于子查詢結(jié)果中的某個(gè)(所有)值=ANY(ALL)等于子