數(shù)據(jù)庫技術(shù)及應(yīng)用第3單元2

第三單元

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

courseNamevarchar2(30)NOTNULL,--課程名

creditHournumber(1)default0NOTNULL,--學分

courseHournumber(3)default0NOTNULL,--課時數(shù)

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

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

NOTNULL,

--班級號

classNamevarchar2(30)

NOTNULL,

--班級名

institutevarchar2(30)

NOTNULL,

--所屬學院

gradenumber(4)default0

NOTNULL,--年級

classNumnumber(3)

NULL,

--班級人數(shù)

CONSTRAINTClassPKPRIMARYKEY(classNo));Page9SQL數(shù)據(jù)定義（基本表）建立學生成績管理數(shù)據(jù)庫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數(shù)據(jù)定義（基本表）建立學生成績管理數(shù)據(jù)庫ScoreDB中的4張表CREATETABLEScore(--定義成績表ScorestudentNochar(7)

NOTNULL,--學號

courseNochar(3)

NOTNULL,--課程號

scorenumber(5,1)default0

NOTNULL,--成績

CONSTRAINTScoreCKCHECK(scoreBETWEEN0.0AND100.0),

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

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

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

DBMS自動完成

使用索引

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

【例】刪除InstituteIdx索引。DROPINDEXInstituteIdx;Page20SQL數(shù)據(jù)定義（視圖）創(chuàng)建視圖（CreateView）刪除視圖（DropView）

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

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

classNoAS班級編號,

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

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

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

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

IN后面是集合，可以是具體的集合，也可以是查詢出來的元組集合?！纠吭谶x課Score表中查詢選修了“001”、“005”或“003”課程的同學學號、課程號和相應(yīng)成績。SELECTstudentNo,courseNo,scoreFROMScoreWHEREcourseNoIN('001','005','003');Page40SQL數(shù)據(jù)查詢（單表查詢）集合查詢IN用于查詢屬性值在某個集合內(nèi)的元組NOTIN用于查詢屬性值不在某個集合內(nèi)的元組

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

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

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

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

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

count(*)門數(shù),

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

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

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

AND

c.studentNo=a.studentNoAND

b.courseName='計算機原理';本例使用了元組變量，其連接條件為：b.courseNo=c.courseNoANDc.studentNo=a.studentNoPage66SQL數(shù)據(jù)查詢（連接查詢）連接查詢（等值連接）【例】查找同時選修了編號為“001”和“002”課程的同學學號、姓名、課程號和相應(yīng)成績，并按學號排序。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數(shù)據(jù)查詢（連接查詢）連接查詢（等值連接）【例】查找同時選修了編號為“001”和“002”課程的同學學號、姓名、課程號和相應(yīng)成績，并按學號排序。該查詢也可以表示為：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數(shù)據(jù)查詢（連接查詢）連接查詢（等值連接）【例】查找同時選修了編號為“001”和“002”課程的同學學號、姓名、課程號和相應(yīng)成績，并按學號排序。該查詢還可以表示為：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數(shù)據(jù)查詢（連接查詢）連接查詢（自然連接）SQL不直接支持自然連接，完成自然連接的方法是在等值連接的基礎(chǔ)上消除重復(fù)列?！纠繉崿F(xiàn)成績表Score和課程表Course的自然連接。SELECTstudentNo,a.courseNo,score,courseName,creditHour,courseHour,priorCourseFROMScorea,CoursebWHEREa.courseNo=b.courseNo;本例課程編號在兩個關(guān)系中同時出現(xiàn)，但在SELECT子句中僅需出現(xiàn)1次，因此使用a.courseNo，也可以使用b.courseNo。其他列名是唯一的，不需要加上元組變量Page70SQL數(shù)據(jù)查詢（連接查詢）連接查詢（自表連接）若某個表與自己進行連接，稱為自表連接?！纠吭趯W生表Student中查找與“李宏冰”同學在同一個班的同學姓名、班級編號和出生日期。SELECTa.studentName,

a.classNo,

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

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

IN

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

studentNameFROMStudentWHEREStudent.studentNo

NOTIN

(SELECTScore.studentNo

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

IN(SELECTstudentNoFROMScoreWHEREcourseNo

IN

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

IN

(SELECTDISTINCTstudentNo

FROMScorea,CoursebWHEREa.courseNo=b.courseNoANDb.courseNameLIKE'%系統(tǒng)%');本例是上例的另外一種實現(xiàn)方法，沒有使用多重嵌套Page82SQL數(shù)據(jù)查詢（嵌套查詢）嵌套查詢（使用IN/NOTIN）【例】查找同時選修過“計算機原理”和“高等數(shù)學”兩門課程的同學學號、姓名以及該同學所選修的所有課程的課程名和相應(yīng)成績，按學號(升序)、成績(降序)排序輸出。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='高等數(shù)學')ORDERBYa.studentNo,scoreDESC;Page83SQL數(shù)據(jù)查詢（嵌套查詢）嵌套查詢（使用IN/NOTIN）【例】查找同時選修過“計算機原理”和“高等數(shù)學”兩門課程的同學學號、姓名以及該同學所選修的這兩門課程的課程名和相應(yīng)成績，按學號(升序)、成績(降序)排序輸出。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='高等數(shù)學')

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

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

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

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

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

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

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

NOTEXISTS(SELECT*FROMScorezWHEREz.studentNo=y.studentNoAND

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

y.studentNo

NOTIN

(SELECTz.studentNoFROMScorezWHEREz.studentNo=y.studentNoAND

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