第三單元

Oracle與SQL語言宋佳興計算機系網絡所jxsong@主要內容Oracle系統簡介Oracle系統結構Oracle系統安裝SQL語言概述SQL語言詳解Page2SQL語言詳解SQL數據定義SQL數據查詢單表查詢連接查詢嵌套查詢集合查詢SQL數據更新SQL視圖操作SQL數據控制Page3SQL數據定義如要對數據進行查詢、添加、修改等各項操作，則應先運用SQL數據定義語言定義基本表、索引和視圖。SQL數據定義用到的命令主要有三個：CREATE（創建）、DROP（刪除）、ALTER（修改），索引依賴于基本表，視圖也產生于基本表，因此SQL通常不提供修改索引和視圖的操作，用戶如果想修改這兩個部分，只能先刪再建。而對于存放數據對象的整個數據庫創建來說，使用SQL語句比使用某一數據庫系統的管理器要復雜，因而一般情況下都使用管理器進行創建。有的數據庫系統在安裝時已經創建了一個缺省的數據庫，只需對其參數進行適當調整即可。Page4SQL數據定義Oracle常用數據類型（詳細數據類型見參考資料）Page5類型含義CHAR(length)存儲固定長度的字符串。參數length指定了長度，如果存儲的字符串長度小于length，用空格填充。默認長度是1，最長不超過2000字節。VARCHAR2(length)存儲可變長度的字符串。length指定了該字符串的最大長度。默認長度是1，最長不超過4000字節。NUMBER(p,s)既可以存儲浮點數，也可以存儲整數，p表示數字的最大位數（如果是小數包括整數部分和小數部分和小數點，p默認是38位），s是指小數位數。DATE存儲日期和時間，存儲紀元、4位年、月、日、時、分、秒，存儲時間從公元前4712年1月1日到公元后4712年12月31日。TIMESTAMP不但存儲日期的年月日，時分秒，以及秒后6位，同時包含時區。CLOB存儲大的文本，比如存儲非結構化的XML文檔等。最大4GBBLOB存儲二進制對象，如圖形、視頻、聲音等。最大4GBSQL數據定義（基本表）創建基本表（CreateTable）CREATETABLE<表名>(<列名><數據類型>[<列級完整性約束條件>][,<列名><數據類型>[<列級完整性約束條件>]]…[,<表級完整性約束條件>]);<表名>：所要定義的基本表的名字<列名>：組成該表的各個屬性（列）<列級完整性約束條件>：涉及相應屬性列的完整性約束條件<表級完整性約束條件>：涉及一個或多個屬性列的完整性約束條件Page6SQL數據定義（基本表）創建基本表（CreateTable）列級完整性約束NULL|NOTNULL：允許取空值|不允許取空值。DEFAULT<值>：指定默認值。UNIQUE：唯一性約束，取值不能重復PRIMARYKEY：聲明為主鍵FOREIGNKEY：參照完整性的外鍵約束。CHECK子句：實現用戶定義完整性約束。表級完整性約束UNIQUE：唯一性約束，取值不能重復PRIMARYKEY：聲明為主鍵。FOREIGNKEY：參照完整性的外鍵約束。CHECK子句：實現用戶定義完整性約束。Page7SQL數據定義（基本表）建立學生成績管理數據庫ScoreDB中的4張表CREATETABLECourse(--定義課程表CoursecourseNochar(3)NOTNULL,--課程號

courseNamevarchar2(30)NOTNULL,--課程名

creditHournumber(1)default0NOTNULL,--學分

courseHournumber(3)default0NOTNULL,--課時數

priorCoursechar(3)NULL,--先修課程

CONSTRAINTCoursePKPRIMARYKEY(courseNo),CONSTRAINTCourseFKFOREIGNKEY(priorCourse)REFERENCESCourse(courseNo));Page8SQL數據定義（基本表）建立學生成績管理數據庫ScoreDB中的4張表CREATETABLEClass(--定義班級表ClassclassNochar(6)

NOTNULL,

--班級號

classNamevarchar2(30)

NOTNULL,

--班級名

institutevarchar2(30)

NOTNULL,

--所屬學院

gradenumber(4)default0

NOTNULL,--年級

classNumnumber(3)

NULL,

--班級人數

CONSTRAINTClassPKPRIMARYKEY(classNo));Page9SQL數據定義（基本表）建立學生成績管理數據庫ScoreDB中的4張表CREATETABLEStudent(--定義學生表Student

studentNochar(7)

NOTNULL,--學號

studentNamevarchar2(20)

NOTNULL,--姓名

sexchar(2)

NULL,--性別

birthdaydateNULL,--出生日期

nativevarchar2(20)

NULL,--籍貫

nationvarchar2(30)default'漢族'NULL,

--民族

classNochar(6)

NULL,--所屬班級

CONSTRAINTStudentPKPRIMARYKEY(studentNo),

CONSTRAINTStudentFKFOREIGNKEY(classNo)REFERENCESClass(classNo));Page10SQL數據定義（基本表）建立學生成績管理數據庫ScoreDB中的4張表CREATETABLEScore(--定義成績表ScorestudentNochar(7)

NOTNULL,--學號

courseNochar(3)

NOTNULL,--課程號

scorenumber(5,1)default0

NOTNULL,--成績

CONSTRAINTScoreCKCHECK(scoreBETWEEN0.0AND100.0),

/*主碼由兩個屬性構成，必須作為表級完整性約束進行定義*/CONSTRAINTScorePKPRIMARYKEY(studentNo,courseNo),

/*表級完整性約束條件，studentNo是外碼，被參照表是Student*/CONSTRAINTScoreFK1FOREIGNKEY(studentNo)REFERENCESstudent(studentNo),

/*表級完整性約束條件，courseNo是外碼，被參照表是Course*/CONSTRAINTScoreFK2FOREIGNKEY(courseNo)REFERENCEScourse(courseNo));Page11SQL數據定義（基本表）修改基本表（AlterTable）ALTERTABLE<表名>[ADD<新列名><數據類型>[完整性約束]][DROP<完整性約束名>][MODIFY<列名><數據類型>];<表名>：要修改的基本表ADD子句：增加新列和新的完整性約束條件DROP子句：刪除指定的完整性約束條件MODIFY子句：用于修改列名和數據類型Page12SQL數據定義（基本表）修改基本表（AlterTable）建立一個測試用的臨時表CREATETABLETempTable(xnochar(3)NOTNULL,xnamevarchar2(2)NOTNULL,CONSTRAINTTempPKPRIMARYKEY(xno));Page13SQL數據定義（基本表）修改基本表（AlterTable）【例】為TempTable表增加一列。ALTERTABLETempTableADDxsexnumber(1)default0;【例】為TempTable表的xname列修改數據類型。ALTERTABLETempTableMODIFYxnamevarchar2(10);【例】為TempTable表的xname列增加唯一約束。ALTERTABLETempTableADDCONSTRAINTUniqueXnameUNIQUE(xname);Page14SQL數據定義（基本表）刪除基本表（DropTable）當某個基本表不再需要時，應當及時刪除，以節省空間和減少出錯等問題。DROPTABLE<表名>基表一旦刪除，表中的數據、該表上建立的索引都將自動被刪除。另外，有的系統如Oracle，刪除基表后建立在該表上的視圖仍然保留在數據字典中，但用戶引用時就出錯，所以還需手動刪除相應所有視圖。【例】刪除TempTable表DROPTABLETempTable;Page15SQL數據定義（索引）建立索引是加快查詢速度的有效手段建立索引是一個數據庫所必須的建立索引DBA或表的屬主（建表人）根據需要建立有些DBMS自動建立以下屬性列上的索引PRIMARYKEYUNIQUE維護索引

DBMS自動完成

使用索引

DBMS自動選擇是否使用索引以及使用哪些索引Page16SQL數據定義（索引）創建索引（CreateIndex）CREATE[UNIQUE][CLUSTER]INDEX<索引名>ON<表名>(<列名>[<次序>][,<列名>[<次序>]]…);用<表名>指定要建索引的基本表名字索引可以建立在該表的一列或多列上，各列名之間用逗號分隔用<次序>指定索引值的排列次序，升序：ASC，降序：DESC。缺省值：ASCUNIQUE表明要建立的索引是唯一索引CLUSTER表示要建立的索引是聚簇索引Page17SQL數據定義（索引）創建索引（CreateIndex）UNIQUE表明此索引的每一個索引值只對應唯一的數據記錄。CLUSTER表明要建立的索引是聚簇索引。所謂聚簇索引是指索引項的順序與表中記錄的物理順序一致的索引組織。用戶可以在最常查詢的字段上建立聚簇索引以提高查詢效率。顯然在一個基表上最多只能建立一個聚簇索引。建立后，更新索引字段數據時，往往導致表中記錄的物理順序的變更，代價較大，因此對于經常更新的不宜建立聚簇索引。Page18SQL數據定義（索引）創建索引（CreateIndex）【例】在班級表中按所屬學院建立一個非聚集索引InstituteIdx。CREATEINDEXInstituteIdxONClass(institute);【例】在學生表中，首先按班級編號的升序，然后按出生日期的降序建立一個非聚集索引ClassBirthIdx。CREATEINDEXClassBirthIdxONStudent(classNo,birthdayDESC);Page19SQL數據定義（索引）刪除索引（DropIndex）可刪除那些不經常使用的索引刪除索引操作的語法為：DROPINDEX<索引名>

【例】刪除InstituteIdx索引。DROPINDEXInstituteIdx;Page20SQL數據定義（視圖）創建視圖（CreateView）刪除視圖（DropView）

創建視圖需要用到SQL數據查詢，本部分內容放到SQL數據查詢之后在詳細介紹。Page21SQL語言詳解SQL數據定義SQL數據查詢單表查詢連接查詢嵌套查詢集合查詢SQL數據更新SQL視圖操作SQL數據控制Page22SQL數據查詢SQL數據查詢（Select）SELECT[ALL|DISTINCT]<目標列表達式>[,<目標列表達式>]…FROM<表名或視圖名>[,<表名或視圖名>]…[WHERE<條件表達式>][GROUPBY<列名1>[HAVING<條件表達式>]][ORDERBY<列名2>[ASC|DESC]];Page23SQL數據查詢SQL數據查詢（Select）SELECT子句：指定要顯示的屬性列FROM子句：指定查詢對象（基本表、視圖或查詢表）WHERE子句：指定查詢條件GROUPBY子句：對查詢結果按指定列的值分組，屬性列值相等的元組為一個組。通常會在每組中使用集函數HAVING短語：篩選出只有滿足指定條件的組ORDERBY子句：對查詢結果表按指定列值的升序或降序排序Page24SQL數據查詢單表查詢只涉及一個基本表或者視圖的查詢連接查詢涉及兩個或者兩個以上基本表或者視圖的查詢嵌套查詢將一個子查詢嵌套在另一個查詢WHERE子句或HAVING短語條件中的一種查詢集合查詢對多個查詢的結果進行集合操作Page25SQL數據查詢（實例ScoreDB）Page26SQL數據查詢（實例ScoreDB）Page27SQL數據查詢（實例ScoreDB）Page28SQL數據查詢（實例ScoreDB）Page29SQL語言詳解SQL數據定義SQL數據查詢單表查詢連接查詢嵌套查詢集合查詢SQL數據更新SQL視圖操作SQL數據控制Page30SQL數據查詢（單表查詢）SELECT子句可以實現關系代數中的投影運算查詢指定列選取表中的指定列，通過SELECT確定要查詢的屬性【例】查詢所有班級的班級編號、班級名稱和所屬學院SELECTclassNo,className,instituteFROMClass;Page31SQL數據查詢（單表查詢）消除重復元組需要消除重復元組，使用DISTINCT關鍵字【例】查詢所有學院的名稱SELECTinstituteFROMClass;【例】查詢所有學院的名稱并消除重復元組SELECTDISTINCTinstituteFROMClass;Page32SQL數據查詢（單表查詢）查詢所有列可使用兩種方法：將所有的列在SELECT子句中列出（可以改變列的顯示順序）；使用*號，*表示所有屬性，按照表定義時的順序顯示所有屬性。【例】查詢所有班級的全部信息SELECTclassNo,className,classNum,grade,instituteFROMClass;【例】查詢所有班級的全部信息SELECT*

FROMClass;Page33SQL數據查詢（單表查詢）給屬性列取別名可為屬性列取一個便于理解的列名，如用中文來顯示列名，為屬性列取別名特別適合經過計算的列。【例】查詢所有班級的所屬學院、班級編號和班級名稱，要求用中文顯示列名SELECTinstitute所屬學院,classNo班級編號,className班級名稱FROMClass;【例】該查詢可使用AS關鍵字取別名SELECTinstituteAS所屬學院,

classNoAS班級編號,

classNameAS班級名稱FROMClass;Page34SQL數據查詢（單表查詢）查詢經過計算的列可使用屬性、常數、函數和表達式【例】查詢每個班級編號、班級名稱以及該班級現在為幾年級，并將班級編號中大寫字母改為小寫字母輸出SELECTlower(classNo)lowerclassNo,className,To_number(To_char(Sysdate,'YYYY'))-gradeclassgradeFROMClass;Oracle內置函數Sysdate返回系統當前時間函數lower()將大寫字母改為小寫字母函數To_char()將日期轉換為指定格式的字符串函數To_number()將字符串轉化為數值Page35SQL數據查詢（單表查詢）WHERE子句可以實現關系代數中的選擇運算WHERE常用的查詢條件有：比較運算：>、>=、<、<=、=、<>(或!=)范圍查詢：BETWEEN...AND集合查詢：IN空值查詢：ISnull字符匹配查詢：LIKE邏輯查詢：AND、OR、NOTPage36SQL數據查詢（單表查詢）比較運算使用比較運算符>、>=、<、<=、=、<>(或!=)

【例】查詢2007級的班級編號、班級名稱和所屬學院。SELECTclassNo,className,instituteFROMClassWHEREgrade=2007;【例】在學生Student表中查詢年齡大于或等于22歲的同學學號、姓名和出生日期。SELECTstudentNo,studentName,birthdayFROMStudentWHEREmonths_between(sysdate,birthday)/12>=22;Page37SQL數據查詢（單表查詢）范圍查詢BETWEEN...AND用于查詢屬性值在某一個范圍內的元組NOT

BETWEEN...AND用于查詢屬性值不在某一個范圍內的元組BETWEEN后是屬性的下限值，AND后是屬性的上限值【例】在選課Score表中查詢成績在80～90分之間的同學學號、課程號和相應成績SELECTstudentNo,courseNo,scoreFROMScoreWHEREscoreBETWEEN80AND90;Page38SQL數據查詢（單表查詢）范圍查詢BETWEEN...AND用于查詢屬性值在某一個范圍內的元組NOT

BETWEEN...AND用于查詢屬性值不在某一個范圍內的元組BETWEEN后是屬性的下限值，AND后是屬性的上限值【例】在選課Score表中查詢成績不在80～90分之間的同學學號、課程號和相應成績。SELECTstudentNo,courseNo,scoreFROMScoreWHEREscoreNOTBETWEEN80AND90;Page39SQL數據查詢（單表查詢）集合查詢IN用于查詢屬性值在某個集合內的元組NOTIN用于查詢屬性值不在某個集合內的元組

IN后面是集合，可以是具體的集合，也可以是查詢出來的元組集合。【例】在選課Score表中查詢選修了“001”、“005”或“003”課程的同學學號、課程號和相應成績。SELECTstudentNo,courseNo,scoreFROMScoreWHEREcourseNoIN('001','005','003');Page40SQL數據查詢（單表查詢）集合查詢IN用于查詢屬性值在某個集合內的元組NOTIN用于查詢屬性值不在某個集合內的元組

IN后面是集合，可以是具體的集合，也可以是查詢出來的元組集合。【例】在學生Student表中查詢籍貫不是“南昌”或“上海”的同學姓名、籍貫和所屬班級編號。SELECTstudentName,native,classNoFROMStudentWHEREnativeNOTIN('南昌','上海');Page41SQL數據查詢（單表查詢）空值查詢空值表示未知或不確定的值，空值表示為NULLISNULL用于查詢屬性值為空值，IS不能用“=”替代ISNOTNULL用于查詢屬性值不為空值【例】在課程表中查詢先修課程為空值的課程信息。SELECT*FROMCourseWHEREpriorCourseISNULL;【例】在課程表中查詢有先修課程的課程信息。SELECT*FROMCourseWHEREpriorCourseISNOTNULL;Page42SQL數據查詢（單表查詢）字符匹配查詢LIKE用于字符匹配查詢，語法格式為：[NOT]LIKE<匹配字符串>[ESCAPE<換碼字符>]查詢的含義是：如果LIKE前沒有NOT，則查詢指定的屬性列值與<匹配字符串>相匹配的元組；如果LIKE前有NOT，則查詢指定的屬性列值不與<匹配字符串>相匹配的元組。<匹配字符串>可以是一個具體的字符串，也可以包括通配符，符號%表示任意長度的字符串，符號_(下劃線)表示任意一個字符。Page43SQL數據查詢（單表查詢）字符匹配查詢LIKE用于字符匹配查詢，語法格式為：[NOT]LIKE<匹配字符串>[ESCAPE<換碼字符>]字符匹配例子ab%，表示所有以ab開頭的任意長度的字符串；zhang%ab，表示以zhang開頭，以ab結束，中間可以是任意個字符的字符串。ab_，表示所有以ab開頭的3個字符的字符串，其中第3個字符為任意字符；a__b表示所有以a開頭，以b結束的4個字符的字符串，且第2、3個字符為任意字符。Page44SQL數據查詢（單表查詢）字符匹配查詢【例】在班級Class表中查詢班級名稱中含有會計的班級信息。SELECT*FROMClassWHEREclassNameLIKE'%會計%';【例】在學生Student表中查詢所有姓王且全名為3個漢字的同學學號和姓名。SELECTstudentNo,studentNameFROMStudentWHEREstudentNameLIKE'王__';【例】在學生Student表中查詢名字中不含有“福”的同學學號和姓名。SELECTstudentNo,studentNameFROMStudentWHEREstudentNameNOTLIKE'%福%';Page45SQL數據查詢（單表查詢）字符匹配查詢【例】在學生Student表中查詢蒙古族的同學學號和姓名SELECTstudentNo,studentNameFROMStudentWHEREnationLIKE'蒙古族';注意：如果匹配字符串中不含有%和_，則LIKE與比較運算符“=”的查詢結果一樣，該查詢等價于下面查詢：SELECTstudentNo,studentNameFROMStudentWHEREnation='蒙古族';Page46SQL數據查詢（單表查詢）字符匹配查詢如果查詢字串中本身要包含%和_，必須使用“ESCAPE<換碼字符>”短語，對通配符進行轉義處理。【例】在班級Class表中查詢班級名稱中含有“08_”符號的班級名稱SELECTclassNameFROMClassWHEREclassNameLIKE'%08\_%'ESCAPE'\';“ESCAPE‘\’”表示\為換碼字符，緊跟在\符號后的_不是通配符，而是普通的用戶要查詢的符號，如果將#字符作為換碼字符，則該查詢可改寫為：SELECTclassNameFROMClassWHEREclassNameLIKE'%08#_%'ESCAPE'#';Page47SQL數據查詢（單表查詢）邏輯查詢SQL提供AND、OR和NOT邏輯運算符分別實現邏輯與、邏輯或和邏輯非運算【例】在選課Score表中查詢選修了“001”、“005”或“003”課程的同學學號、課程號和相應成績SELECTstudentNo,courseNo,scoreFROMScoreWHEREcourseNo='001'ORcourseNo='005'ORcourseNo='003';也可采用集合運算IN實現，本例中采用邏輯“或”運算Page48SQL數據查詢（單表查詢）邏輯查詢【例】在Student表中查詢1991年出生且籍貫為“漢族”的同學學號、姓名、出生日期。SELECTstudentNo,studentName,birthdayFROMStudentWHERETo_char(birthday,'YYYY')='1991'ANDnation='漢族';【例】在Student表中查詢籍貫不是“南昌”或“上海”的同學姓名、籍貫和所屬班級編號。SELECTstudentName,native,classNoFROMStudentWHEREnative!='南昌'ANDnative!='上海';Page49SQL數據查詢（單表查詢）邏輯查詢【例】在選課Score表中查詢成績在80～90分之間的同學學號、課程號和相應成績。SELECTstudentNo,courseNo,scoreFROMScoreWHEREscore>=80ANDscore<=90;【例】在選課Score表中查詢成績不在80～90分之間的同學學號、課程號和相應成績。SELECTstudentNo,courseNo,scoreFROMScoreWHEREscore<80OR

score>90;Page50SQL數據查詢（單表查詢）排序運算使用ORDERBY子句實現排序運算，其語法為：ORDERBY<表達式1>[ASC|DESC]

[,<表達式2>[ASC|DESC],...]基本說明：<表達式1>,<表達式2>,...可以是屬性、函數或表達式缺省按升序(ASC)排序，按降序排序，必須指明DESC選項運算含義：在查詢結果中首先按<表達式1>的值進行排序在<表達式1>值相等的情況下再按<表達式2>值排序依此類推Page51SQL數據查詢（單表查詢）排序運算【例】在學生Student表中查詢籍貫不是“南昌”或“上海”的同學姓名、籍貫和所屬班級編號，并按籍貫的降序排序輸出。SELECTstudentName,native,classNoFROMStudentWHEREnative!='南昌'ANDnative!='上海'ORDERBYnativeDESC;【例】在學生Student表中查詢“女”學生的學號、姓名、所屬班級編號和出生日期，并按班級編號的升序、出生日期的月份降序排序輸出。SELECTstudentNo,studentName,classNo,birthdayFROMStudentWHEREsex='女'ORDERBYclassNo,to_char(birthday,'MM')DESC;Page52SQL數據查詢（單表查詢）查詢表FROM子句后面可以是基本表、視圖，還可以是查詢表【例】查詢1991年出生的“女”同學基本信息。可以先將學生表中的女生記錄查詢出來，然后再對查詢表進行選擇、投影操作。SELECTstudentNo,studentName,birthdayFROM(SELECT*FROMStudentWHEREsex='女')aWHERETo_char(birthday,'YYYY')='1991';在FROM子句后是一個子查詢，表示對子查詢的查詢結果（查詢表）進行查詢，必須為查詢表取一個名稱（稱為元組變量），如取名為aPage53SQL數據查詢（單表查詢）聚合查詢SQL查詢提供了豐富的數據分類、統計功能統計功能通過聚合函數來實現分類功能通過分組子句來實現統計和分組結合在一起實現豐富的查詢功能Page54SQL數據查詢（單表查詢）聚合函數SQL提供的聚合函數包括：count([DISTINCT|ALL]{*|<列名>})：統計關系的元組個數或一列中值的個數；sum([DISTINCT|ALL]<列名>)：統計一列中值的總和；avg([DISTINCT|ALL]<列名>)：統計一列中值的平均值；max([DISTINCT|ALL]<列名>)：統計一列中值的最大值；min([DISTINCT|ALL]<列名>)：統計一列中值的最小值。指定DISTINCT謂詞，表示在計算時首先消除<列名>取重復值的元組，然后再進行統計指定ALL謂詞或沒有DISTINCT謂詞，表示不消除<列名>取重復值的元組Page55SQL數據查詢（單表查詢）聚合函數【例】查詢學生總人數。SELECTcount(*)學生人數FROMStudent;【例】查詢所有選課學生的人數。SELECTcount(studentNo)

學生人數FROMScore;查詢結果是40，由于一個學生可以選修多門課程，學號存在重復，為消除重復的元組，使用DISTINCT短語，將查詢修改為：SELECTcount(DISTINCTstudentNo)學生人數FROMScore;查詢結果為10Page56SQL數據查詢（單表查詢）聚合函數【例】查詢學號為“0800005”同學所選修課程成績的最高分SELECTmax(score)最高分FROMScoreWHEREstudentNo=

'0800005';【例】查詢學號為“0800005”同學所選修課程成績的平均分SELECTavg(score)平均分FROMScoreWHEREstudentNo='0800005';在聚合函數遇到空值時，除count(*)外所有的函數皆跳過空值，只處理非空值。Page57SQL數據查詢（單表查詢）分組運算在SQL查詢中，往往需要對數據進行分組運算，分組運算的目的是為了細化聚合函數的作用對象。如不對查詢結果分組，則聚合函數作用于整個查詢結果。如對查詢結果進行分組，則聚合函數分別作用于每個組，查詢結果按組聚合輸出。SQL通過GROUPBY和HAVING子句實現分組運算GROUPBY：對查詢結果按某一列或某幾列進行分組，值相等的分為一組；HAVING：對分組的結果進行選擇，僅輸出滿足條件的組。該子句必須與GROUPBY子句配合使用。Page58SQL數據查詢（單表查詢）分組運算【例】查詢每個同學選課門數、平均分和最高分。SELECTstudentNo,

count(*)門數,

avg(score)平均分,max(score)最高分FROMScoreGROUPBYstudentNo;結果按學號StudentNo分組，將具有相同StudentNo值的元組作為一組，然后對每組進行相應的計數、求平均值和求最大值Page59SQL數據查詢（單表查詢）分組運算【例】查詢平均分在80分以上的每個同學的選課門數、平均分和最高分。SELECTStudentNo,count(*)門數,avg(score)平均分,

max(score)最高分FROMScoreGROUPBYStudentNoHAVINGavg(score)>=80;按學號StudentNo分組，將具有相同StudentNo值的元組作為一組，然后對每組進行相應的計數、求平均值和求最大值，并判斷平均值是否大于等于80，如果是則輸出該組，否則丟棄該組，不作為輸出結果Page60SQL數據查詢（單表查詢）分組運算【例】查詢成績表Score中成績最高分的學生的學號、課程號和相應成績SELECTstudentNo,courseNo,scoreFROMScoreWHEREscore=(SELECTmax(score)FROMScore);聚合函數可直接用在HAVING子句中，也可用于子查詢中，但在WHERE子句中不可以直接使用聚合函數。如下語句是不正確的：SELECT*FROMScoreWHEREscore=max(score)Page61SQL語言詳解SQL數據定義SQL數據查詢單表查詢連接查詢嵌套查詢集合查詢SQL數據更新SQL視圖操作SQL數據控制Page62SQL數據查詢（連接查詢）連接查詢在WHERE子句中加入連接多個表的連接條件語句格式如下：WHERE[<表1>.]<列名1><比較運算符>[<表2>.]<列名2>[<邏輯運算符>

[<表3>.]<列名3><比較運算符>[<表4>.]<列名4>...]比較運算符包括：>、>=、<、<=、=、<>（或!=）當比較運算符為=時，表示等值連接，其他運算為非等值連接WHERE子句的連接謂詞中的屬性稱為連接屬性，連接屬性之間必須具有可比性Page63SQL數據查詢（連接查詢）連接查詢（等值連接）【例】查找會計學院全體同學的學號、姓名、籍貫、班級編號和所在班級名稱。SELECTstudentNo,studentName,native,Student.classNo,classNameFROMStudent,ClassWHEREStudent.classNo=Class.classNoANDinstitute='會計學院';在連接操作中，如果涉及到多個表的相同屬性名，必須在相同的屬性名前加上表名加以區分Page64SQL數據查詢（連接查詢）連接查詢（等值連接）可為參與連接的表取別名（稱為元組變量），在相同的屬性名前加上表的別名。將Student表取別名為a，Class表取別名為b，班級編號分別用a.classNo和b.classNo表示。上例可以改寫為：SELECTstudentNo,studentName,native,b.classNo,classNameFROMStudenta,ClassbWHEREa.classNo=b.classNoANDinstitute='會計學院';對于不同屬性名，可以不在屬性名前加上表名或別名。Page65SQL數據查詢（連接查詢）連接查詢（等值連接）【例】查找選修了課程名稱為“計算機原理”的同學學號、姓名。查詢語句為：SELECTa.studentNo,studentNameFROMStudenta,Courseb,ScorecWHEREb.courseNo=c.courseNo

AND

c.studentNo=a.studentNoAND

b.courseName='計算機原理';本例使用了元組變量，其連接條件為：b.courseNo=c.courseNoANDc.studentNo=a.studentNoPage66SQL數據查詢（連接查詢）連接查詢（等值連接）【例】查找同時選修了編號為“001”和“002”課程的同學學號、姓名、課程號和相應成績，并按學號排序。SELECTa.studentNo,studentName,

b.courseNo,b.score,c.courseNo,c.scoreFROMStudenta,Scoreb,(SELECT*FROMScoreWHEREcourseNo='002')cWHEREb.courseNo='001'AND

a.studentNo=b.studentNoAND

a.studentNo=c.studentNoORDERBYa.studentNo;Page67SQL數據查詢（連接查詢）連接查詢（等值連接）【例】查找同時選修了編號為“001”和“002”課程的同學學號、姓名、課程號和相應成績，并按學號排序。該查詢也可以表示為：SELECTa.studentNo,studentName,

b.courseNo,b.score,c.courseNo,c.scoreFROMStudenta,(SELECT*FROMScoreWHEREcourseNo='001')b,(SELECT*FROMScoreWHEREcourseNo='002')cWHEREa.studentNo=b.studentNoAND

a.studentNo=c.studentNoORDERBYa.studentNo;Page68SQL數據查詢（連接查詢）連接查詢（等值連接）【例】查找同時選修了編號為“001”和“002”課程的同學學號、姓名、課程號和相應成績，并按學號排序。該查詢還可以表示為：SELECTa.studentNo,studentName,

b.courseNo,b.score,c.courseNo,c.scoreFROMStudenta,Scoreb,ScorecWHEREa.studentNo=b.studentNoAND

a.studentNo=c.studentNoAND

b.courseNo='001'AND

c.courseNo='002'ORDERBYa.studentNo;Page69SQL數據查詢（連接查詢）連接查詢（自然連接）SQL不直接支持自然連接，完成自然連接的方法是在等值連接的基礎上消除重復列。【例】實現成績表Score和課程表Course的自然連接。SELECTstudentNo,a.courseNo,score,courseName,creditHour,courseHour,priorCourseFROMScorea,CoursebWHEREa.courseNo=b.courseNo;本例課程編號在兩個關系中同時出現，但在SELECT子句中僅需出現1次，因此使用a.courseNo，也可以使用b.courseNo。其他列名是唯一的，不需要加上元組變量Page70SQL數據查詢（連接查詢）連接查詢（自表連接）若某個表與自己進行連接，稱為自表連接。【例】在學生表Student中查找與“李宏冰”同學在同一個班的同學姓名、班級編號和出生日期。SELECTa.studentName,

a.classNo,

a.birthdayFROMStudenta,StudentbWHEREb.studentName='李宏冰'AND

a.classNo=b.classNo;Page71SQL數據查詢（連接查詢）連接查詢（外連接）在一般的連接中，只有滿足連接條件的元組才被檢索出來，對于沒有滿足連接條件的元組是不作為結果被檢索出來的。【例】查詢每個班級的班級名稱、所屬學院、學生學號、學生姓名，按班級名稱排序輸出。SELECTclassName,institute,studentNo,studentNameFROMClassa,StudentbWHEREa.classNo=b.classNoORDERBYclassName;Page72SQL數據查詢（連接查詢）連接查詢（外連接）從查詢結果中可以看出：班級表中的“注冊會計08_01班”、“注冊會計08_03班”以及“金融管理07_01班”3個班沒有出現在查詢結果中，原因是這3個班沒有學生。在實際應用中，往往需要將不滿足連接條件的元組也檢索出來，只是在相應的位置用空值替代，這種查詢稱為外連接查詢。外連接分為左外連接、右外連接和全外連接。在FROM子句中，寫在左邊的表稱為左關系，寫在右邊的表稱為右關系。Page73SQL數據查詢（連接查詢）連接查詢（外連接）左外連接連接結果中包含左關系中的所有元組，對于左關系中沒有連接上的元組，其右關系中的相應屬性用空值替代。【例】使用左外連接查詢每個班級的班級名稱、所屬學院、學生學號、學生姓名，按班級名稱排序輸出。SELECTclassName,institute,studentNo,studentNameFROMClassaLEFTOUTERJOINStudentbONa.classNo=b.classNoORDERBYclassName;Page74SQL數據查詢（連接查詢）連接查詢（外連接）右外連接連接結果中包含右關系中的所有元組，對于右關系中沒有連接上的元組，其左關系中的相應屬性用空值替代。【例】使用右外連接查詢每個班級的班級名稱、所屬學院、學生學號、學生姓名，按班級名稱排序輸出。SELECTclassName,institute,studentNo,studentNameFROMStudentaRIGHTOUTERJOINClassbONa.classNo=b.classNoORDERBYclassName;Page75SQL數據查詢（連接查詢）連接查詢（外連接）全外連接連接結果中包含左、右關系中的所有元組對左關系中沒有連接上的元組，其右關系中的相應屬性用空值替代對右關系中沒有連接上的元組，其左關系中的相應屬性用空值替代【例】使用全外連接查詢每個班級的班級名稱、所屬學院、學生學號、學生姓名，按班級名稱排序輸出。SELECTclassName,institute,studentNo,studentNameFROMClassaFULLOUTERJOINStudentbONa.classNo=b.classNoORDERBYclassName;Page76SQL語言詳解SQL數據定義SQL數據查詢單表查詢連接查詢嵌套查詢集合查詢SQL數據更新SQL視圖操作SQL數據控制Page77SQL數據查詢（嵌套查詢）在SQL語言中，可以用多個簡單查詢構成復雜的查詢，從而增強SQL的查詢能力。一個SELECT-FROM-WHERE查詢語句稱為一個查詢塊，將一個查詢塊嵌入到另一個查詢塊的WHERE子句或HAVING子句中，稱為嵌套查詢。在一個嵌套語句中，上層的查詢塊稱為外層查詢或父查詢，下層的查詢塊稱為內層查詢或子查詢。SQL語言允許多層嵌套查詢，但在子查詢中，不允許使用ORDERBY子句，該子句僅用于最后的輸出結果排序。Page78SQL數據查詢（嵌套查詢）嵌套查詢（使用IN/NOTIN）子查詢中使用最多的是謂詞IN和NOTIN，含義為當查詢的指定屬性值包含（或不包含）在子查詢結果屬性值表中時條件為真。【例】查詢選修過課程的學生姓名。本例查詢的含義是：在學生表Student中，將學號出現在成績表Score中（表明該學生選修過課程）的學生姓名查詢出來。SELECTstudentNameFROMStudentWHEREStudent.studentNo

IN

(SELECTScore.studentNoFROMScore);Page79SQL數據查詢（嵌套查詢）嵌套查詢（使用IN/NOTIN）【例】查詢沒有選修過任何課程的學生學號和姓名。本例查詢的含義是：在學生表Student中，將學號沒有出現在成績表Score中的學生學號和姓名查詢出來。SELECTstudentNo,

studentNameFROMStudentWHEREStudent.studentNo

NOTIN

(SELECTScore.studentNo

FROMScore);Page80SQL數據查詢（嵌套查詢）嵌套查詢（使用IN/NOTIN）【例】查找選修過課程名中包含“系統”的課程的同學學號、姓名和班級編號。SELECTstudentNo,studentName,classNoFROMStudentWHEREstudentNo

IN(SELECTstudentNoFROMScoreWHEREcourseNo

IN

(SELECTcourseNoFROMCourseWHEREcourseNameLIKE'%系統%'));WHERE子句中的IN可以實現多重嵌套，本例是一個三重嵌套的例子。Page81SQL數據查詢（嵌套查詢）嵌套查詢（使用IN/NOTIN）【例】查找選修過課程名中包含“系統”的課程的同學學號、姓名和班級編號。SELECTstudentNo,studentName,classNoFROMStudentWHEREstudentNo

IN

(SELECTDISTINCTstudentNo

FROMScorea,CoursebWHEREa.courseNo=b.courseNoANDb.courseNameLIKE'%系統%');本例是上例的另外一種實現方法，沒有使用多重嵌套Page82SQL數據查詢（嵌套查詢）嵌套查詢（使用IN/NOTIN）【例】查找同時選修過“計算機原理”和“高等數學”兩門課程的同學學號、姓名以及該同學所選修的所有課程的課程名和相應成績，按學號(升序)、成績(降序)排序輸出。SELECTa.studentNo,studentName,courseName,scoreFROMStudenta,Courseb,ScorecWHEREa.studentNo=c.studentNoANDb.courseNo=c.courseNoANDa.studentNo

IN(SELECTDISTINCTstudentNoFROMScorex,CourseyWHEREx.courseNo=y.courseNoANDcourseName='計算機原理')ANDa.studentNo

IN

(SELECTDISTINCTstudentNoFROMScorex,CourseyWHEREx.courseNo=y.courseNoANDcourseName='高等數學')ORDERBYa.studentNo,scoreDESC;Page83SQL數據查詢（嵌套查詢）嵌套查詢（使用IN/NOTIN）【例】查找同時選修過“計算機原理”和“高等數學”兩門課程的同學學號、姓名以及該同學所選修的這兩門課程的課程名和相應成績，按學號(升序)、成績(降序)排序輸出。SELECTa.studentNo,studentName,courseName,scoreFROMStudenta,Courseb,ScorecWHEREa.studentNo=c.studentNoANDb.courseNo=c.courseNoANDa.studentNo

IN(SELECTDISTINCTstudentNoFROMScorex,CourseyWHEREx.courseNo=y.courseNoANDcourseName='計算機原理')ANDa.studentNo

IN

(SELECTDISTINCTstudentNoFROMScorex,CourseyWHEREx.courseNo=y.courseNoANDcourseName='高等數學')

AND(courseName='高等數學'ORcourseName='計算機原理')ORDERBYa.studentNo,scoreDESC;Page84SQL數據查詢（嵌套查詢）嵌套查詢（使用ANY（或SOME）和ALL）ANY的含義指當所指定運算符對于子查詢返回的結果集合中的任何一個值為真時，父查詢WHERE子句的值為真。SOME與ANY是同義詞，早期用ANY，現在多用SOME。ALL的含義指當子查詢返回0行，或所指定比較運算符對于子查詢返回的所有結果均為真時，父查詢WHERE子句的值為真。ANY/SOME和ALL謂詞與集函數及IN謂詞的等價轉換關系Page85=<>或!=<<=>>=ANY/SOMEIN--<MAX<=MAX>MIN>=MINALL--NOTIN<MIN<=MIN>MAX>=MAXSQL數據查詢（嵌套查詢）嵌套查詢（使用ANY（或SOME）和ALL）【例】查詢所選修課程成績大于所有課程編號“002”號課程成績的同學學號及相應課程的課程號和成績。SELECTstudentNo,courseNo,scoreFROMScoreWHEREscore>ALL(SELECTscoreFROMScoreWHEREcourseNo='002');Page86SQL數據查詢（嵌套查詢）嵌套查詢（使用ANY（或SOME）和ALL）【例】查詢年齡小于“計算機科學與技術07_01班”某個同學年齡的所有同學的學號、姓名和生日。SELECTstudentNo,studentName,birthdayFROMStudentWHEREsysdate-birthday<ANY

(SELECTsysdate-birthdayFROMStudenta,ClassbWHEREa.classNo=b.classNoAND

b.className='計算機科學與技術07_01班'

);Page87SQL數據查詢（嵌套查詢）嵌套查詢（使用EXISTS/NOTEXISTS）謂詞EXISTS/NOTEXISTS用于測試表中記錄數據的存在性，而不管數據值是什么。EXISTS指當子查詢至少返回一個值時就取“真”，不論該值是什么（甚至可以是空值NULL）；NOTEXISTS指子查詢返回的是一個無行無列的空表時就取“真”。Page88SQL數據查詢（嵌套查詢）嵌套查詢（使用EXISTS/NOTEXISTS）【例】查詢選修了“計算機原理”課程的同學姓名、所在班級編號。該查詢可直接通過連接運算實現，也可以通過IN子查詢來實現。還可以通過EXISTS實現：SELECTstudentName,classNoFROMStudentxWHEREEXISTS

(SELECT*FROMScorea,CoursebWHEREa.courseNo=b.courseNoAND

a.studentNo=x.studentNoANDb.courseName='計算機原理');Page89SQL數據查詢（嵌套查詢）嵌套查詢（使用EXISTS/NOTEXISTS）【例】查詢沒有選修過任何課程的學生的學號和姓名。實際上也就是要查詢出在成績表中沒有記錄的學生的學號和姓名。SELECTstudentNo,studentNameFROMStudentxWHERENOTEXISTS

(SELECT*FROMScoreyWHEREy.studentNo=x.studentNo);Page90SQL數據查詢（嵌套查詢）嵌套查詢（使用EXISTS/NOTEXISTS）【例】查詢已選修課程的學生中沒有不及格課程的學生的學號和姓名。SELECTDISTINCTx.studentNo,x.studentNameFROMStudentx,scoreyWHEREx.studentNo=y.studentNoAND

NOTEXISTS(SELECT*FROMScorezWHEREz.studentNo=y.studentNoAND

z.score<60);Page91SQL數據查詢（嵌套查詢）嵌套查詢（使用EXISTS/NOTEXISTS）【例】查詢已選修課程的學生中沒有不及格課程的學生的學號和姓名。（使用NOTIN改寫上例）SELECTDISTINCTx.studentNo,x.studentNameFROMStudentx,scoreyWHEREx.studentNo=y.studentNoAND

y.studentNo

NOTIN

(SELECTz.studentNoFROMScorezWHEREz.studentNo=y.studentNoAND

z.score<60);Page92SQL語言詳解SQL數據定義SQL數據查詢單表查詢連接查詢嵌套查詢集合查詢SQL數據更新SQL視圖操作SQL數據控制Page93SQL數據查詢（集合查詢）SQL支持集合運算SELECT語句查詢的結果是集合Oracle的集合運算并：UNION（去掉重復記錄）并：UNIONALL（不去掉重復記錄）交：INTERSECT差：MINUS集合運算的要求參與運算的查詢結果的列數一樣對應列的數據類型必須一致Page94SQL數據查詢（集合查詢）集合運算【例】查詢“信息管理學院”1990年出生同學的學號、出生日期、班級名稱和所屬學院以及“會計學院”1991年出生同學的學號、出生日期、班級名稱和所屬學院。SELECTstudentNo,birthday,className,instituteFROMStudenta,ClassbWHEREa.classNo=b.classNoANDTo_char(birthday,'YYYY')='1990'ANDinstitute='信息管理學院'UNIONSELECTstudentNo,birthday,className,instituteFROMStudenta,ClassbWHEREa.classNo=b.cla