數(shù)據(jù)庫技術(shù)及應(yīng)用：第4章 Oracle高級(jí)查詢

Oracle高級(jí)查詢第四章回顧和作業(yè)點(diǎn)評(píng)如下的T-SQL語句含義是什么？聚合函數(shù)COUNT()和SUM()各代表什么意思？SELECTTelephoneFROMstuWHERETelephoneNOTLIKE'6%'預(yù)習(xí)檢查分組查詢主要解決什么問題？多表聯(lián)接主要解決什么問題？用在什么場合？本章目標(biāo)使用GROUPBY進(jìn)行分組查詢掌握多表聯(lián)接查詢學(xué)號(hào)姓名地址所屬年級(jí)……001王明全湖南長沙1002張菲湖北宜昌1003于寄謙甘肅天水1004劉國正山東荷澤1005周接輪臺(tái)灣新竹2006鞏小妹香港龍灣2007鞏大妹香港龍灣2008張明敏北京順義3009矛十八四川棉陽3010張林光陜西臨潼3需求1每年級(jí)的學(xué)生人數(shù)各是多少？分組查詢?cè)韺W(xué)號(hào)姓名地址所屬年級(jí)001王明全湖南長沙1002張菲湖北宜昌1003于寄謙甘肅天水1004劉國正山東荷澤1005周接輪臺(tái)灣新竹2006鞏小妹香港龍灣2007鞏大妹香港龍灣2008張明敏北京順義3009矛十八四川棉陽3010張林光陜西臨潼3第一學(xué)期人數(shù)第二學(xué)期人數(shù)第三學(xué)期人數(shù)三組分組查詢用法SELECT……FROM

<表名>WHERE……GROUPBY……分組查詢語句SELECT

COUNT(*)AS人數(shù),SGrade

AS

年級(jí)FROM

Students

GROUPBYSGradeSELECT

CourseID,AVG(Score)AS課程平均成績FROMScoreGROUPBYCourseID分組查詢解析2-1對(duì)分組后的每個(gè)組內(nèi)的記錄進(jìn)行一個(gè)聚集，通常用到聚合函數(shù)查詢每門課程的平均分按照什么進(jìn)行分組？分組查詢解析2-2SELECT

CourseID,AVG(Score)AS課程平均成績FROMScoreGROUPBYCourseIDORDERBYAVG(Score)查詢每門課程的平均分，并且按照分?jǐn)?shù)由低到高的順序排列顯示SELECT

StudentID,CourseID,AVG(Score)AS課程平均成績FROMScoreGROUPBY

CourseID思考分組查詢所查詢的列1、分組列2、聚合函數(shù)計(jì)算出的列分析以下的T-SQL，結(jié)果會(huì)怎樣？需求2統(tǒng)計(jì)每學(xué)期男女同學(xué)的人數(shù)，該怎么辦？對(duì)學(xué)生進(jìn)行分組計(jì)算每組學(xué)生的總?cè)藬?shù)多列分組2-1年級(jí)分組性別分組多列分組2-2SELECTCOUNT(*)AS人數(shù),SGradeAS年級(jí),SSexAS性別

FROMStudentSGROUPBYSGrade,SSexORDERBYSGradeORDERBY子句在這里的作用？需求3如何獲得總?cè)藬?shù)超過15人的年級(jí)？條件限定分組篩選SELECT……FROM

<表名>WHERE……GROUPBY……HAVING……SELECTCOUNT(*)AS人數(shù),SGradeAS年級(jí)FROMStudentsGROUPBYSGradeHAVINGCOUNT(*)>15分組篩選語句WHEREGROUPBYHAVINGWHERE子句：用來篩選FROM子句中指定的操作所產(chǎn)生的行

GROUPBY子句：用來分組WHERE子句的輸出

HAVING子句：用來從分組的結(jié)果中篩選行

WHERE與HAVING對(duì)比SELECT 部門編號(hào),COUNT(*)FROM 員工信息表WHERE 工資>=2000GROUPBY 部門編號(hào)HAVING COUNT(*)>1思考查詢有多個(gè)員工的工資不低于2000的部門編號(hào)分析以下的T-SQL，其作用是什么？小結(jié)如果查詢語句將得到以下的查詢結(jié)果，那么以下的查詢語句將輸出哪些信息？

SELECT*FROMASELECTA1,B1,MAX(C1)FROMA需求4學(xué)號(hào)姓名年級(jí)…001張青裁1002陳剛1003蘇三東1學(xué)號(hào)科目分?jǐn)?shù)001260002270003480如何同時(shí)從這兩個(gè)表中取得數(shù)據(jù)？常用的多表聯(lián)接查詢內(nèi)聯(lián)接(INNERJOIN)外聯(lián)接左外聯(lián)接(LEFTJOIN)右外聯(lián)接(RIGHTJOIN)內(nèi)聯(lián)接3-1SNameSCode梅超風(fēng)1陳玄風(fēng)2陸乘風(fēng)3曲靈風(fēng)4StudentsIDCourseIDScore100197200189200267300276300381SNameCourseIDScore梅超風(fēng)00197陳玄風(fēng)00189陳玄風(fēng)00267陸乘風(fēng)00276陸乘風(fēng)00381StudentsScore查詢結(jié)果內(nèi)聯(lián)接使用比較運(yùn)算符根據(jù)每個(gè)表的通用列中的值匹配兩個(gè)表中的行內(nèi)聯(lián)接3-2SELECT ……

FROM

表1INNERJOIN

表2ON ……SELECT……

FROM

表1，表2WHERE……

SELECT

S.SName,C.CourseID,C.Score

FROM

ScoreASCINNERJOIN

StudentsASSON

C.StudentID=S.SCodeSELECTStudents.SName,Score.CourseID,Score.ScoreFROM Students,ScoreWHEREStudents.SCode=Score.StudentID等價(jià)內(nèi)聯(lián)接語句內(nèi)聯(lián)接3-3SELECT

S.SName,C.CourseID,C.Score

FROM

StudentsASSINNERJOIN

ScoreASCON

C.StudentID<>S.SCodeSELECT

S.SName,C.CourseID,C.Score

FROM

StudentsASSINNERJOIN

ScoreASCON

C.StudentID=S.SCode15行下面的查詢語句返回的查詢結(jié)果是一樣的嗎？根據(jù)前面表中數(shù)據(jù)，下面的查詢語句將會(huì)返回多少行記錄？SELECTStudents.SName,Score.CourseID,Score.ScoreFROM Students,ScoreWHEREStudents.SCode=Score.StudentID兩個(gè)語句的執(zhí)行結(jié)果相同SELECT

S.SNameAS姓名,CS.CourseNameAS課程,C.ScoreAS成績FROMStudentsASSINNERJOINScoreASCON(S.SCode=C.StudentID)INNERJOINCourseASCSON(CS.CourseID=C.CourseID)三表內(nèi)聯(lián)接CourseIDCourseName…………姓名課程成績………………SNameSCode…………StudentsIDCourseIDScore………………使用WHERE語句如何實(shí)現(xiàn)？左外聯(lián)接SNameSCode梅超風(fēng)1陳玄風(fēng)2陸乘風(fēng)3曲靈風(fēng)4StudentsIDCourseIDScore100197200189200267300276300381SNameCourseIDScore梅超風(fēng)00197陳玄風(fēng)00189陳玄風(fēng)00267陸乘風(fēng)00276陸乘風(fēng)00381曲靈風(fēng)NULLNULLStudentsScore查詢結(jié)果SELECT

S.SName,C.CourseID,C.Score

FROM

ScoreASCLEFTJOIN

StudentsASSON

C.StudentID=S.SCode不一樣，主表和從表位置已互換SELECT

S.SName,C.CourseID,C.Score

FROM

StudentsASSLEFTJOIN

ScoreASCON

C.StudentID=S.SCode主表（左表）Students中的數(shù)據(jù)逐條匹配從表Score中的數(shù)據(jù)1、匹配，返回到結(jié)果集2、無匹配，NULL值返回到結(jié)果集猜一猜：這樣寫，返回的查詢結(jié)果是一樣的嗎？SELECT

圖書編號(hào),圖書名稱,出版社名稱FROM

圖書表RIGHTOUTERJOIN

出版社表ON

圖書表.出版社編號(hào)=出版社表.出版社編號(hào)右外聯(lián)接圖書編號(hào)圖書名稱出版社名稱1走進(jìn)Java編程世界北大出版社2HTML和CSS網(wǎng)頁技術(shù)清華出版社………………NULLNULL新知出版社………………也許很久沒出版書籍了右外聯(lián)接的原理與左外聯(lián)接相同右表逐條去匹配記錄；否則NULL填充Oracle常用函數(shù)7-1使用函數(shù)可以大大提高SELECT語句操作數(shù)據(jù)庫的能力Oracle中函數(shù)劃分為單行函數(shù)和多行函數(shù)單行函數(shù)作用于數(shù)據(jù)庫表的某一行并返回一個(gè)值字符函數(shù)數(shù)字函數(shù)日期函數(shù)轉(zhuǎn)換函數(shù)其他函數(shù)。多行函數(shù)基于數(shù)據(jù)庫表多行進(jìn)行運(yùn)算，返回一個(gè)值例如對(duì)多行記錄的某個(gè)字段進(jìn)行求和、求最大值運(yùn)算等Oracle常用函數(shù)7-2常用的字符函數(shù)函數(shù)功能示例結(jié)果INITCAP(char)首字母大寫initcap('hello')HelloLOWER(char)轉(zhuǎn)換為小寫lower('FUN')funUPPER(char)轉(zhuǎn)換為大寫upper('sun')SUNLTRIM(char,set)左剪裁ltrim('xyzadams','xyz')adamsRTRIM(char,set)右剪裁rtrim('xyzadams','ams')xyzadTRANSLATE(char,from,to)按字符翻譯translate('jack','abcd','1234')j13kREPLACE(char,search_str,replace_str)字符串替換replace('jackandjue','j','bl')blackandblueINSTR(char,substr[,pos])查找子串位置instr('worldwide','d')5SUBSTR(char,pos,len)取子字符串substr('abcdefg',3,2)cdCONCAT(char1,char2)連接字符串concat('Hello','world')HelloworldOracle常用函數(shù)7-3常用的數(shù)字函數(shù)函數(shù)功能示例結(jié)果ABS(n)取絕對(duì)值abs(-15)15CEIL(n)向上取整ceil(44.778)45SIN(n)正弦sin(1.571).999999979COS(n)余弦cos(0)1SIGN(n)取符號(hào)sign(-32)-1FLOOR(n)向下取整floor(100.2)100POWER(m,n)m的n次冪power(4,2)16MOD(m,n)取余數(shù)mod(10,3)1ROUND(m,n)四舍五入round(100.256,2)100.26TRUNC(m,n)截?cái)鄑runk(100.256,2)100.25SQRT(n)平方根sqrt(4)2Oracle常用函數(shù)7-4常用的日期函數(shù)函數(shù)功能示例結(jié)果MONTHS_BETWEEN返回兩個(gè)日期間的月份months_between('04-11月-05','11-1月-01')57.7741935ADD_MONTHS返回把月份數(shù)加到日期上的新日期add_months('06-2月-03',1)add_months('06-2月-03',-1)06-3月-0306-1月-03NEXT_DAY返回指定日期后的星期對(duì)應(yīng)的新日期next_day('06-2月-03','星期一')10-2月-03LAST_DAY返回指定日期所在的月的最后一天last_day('06-2月-03')28-2月-03ROUND按指定格式對(duì)日期進(jìn)行四舍五入round(to_date('13-2月-03'),'YEAR')round(to_date('13-2月-03'),'MONTH')round(to_date('13-2月-03'),'DAY')01-1月-0301-2月-0316-2月-03TRUNC對(duì)日期按指定方式進(jìn)行截?cái)鄑runc(to_date('06-2月-03'),'YEAR')trunc(to_date('06-2月-03'),'MONTH')trunc(to_date('06-2月-03'),'DAY')01-1月-0301-2月-0302-2月-03Oracle常用函數(shù)7-5常用的轉(zhuǎn)換函數(shù)函數(shù)功能示例結(jié)果TO_CHAR轉(zhuǎn)換成字符串類型to_char(1234.5,'$9999.9')$1234.5TO_DATE轉(zhuǎn)換成日期類型to_date('1980-01-01','yyyy-mm-dd')01-1月-80TO_NUMBER轉(zhuǎn)換成數(shù)值類型to_number('1234.5')1234.5Oracle常用函數(shù)7-6常用的其他函數(shù)函數(shù)功能NVL(EXP1,EXP2)如果exp1的值為null，則返回exp2的值，否則返回exp1的值 NVL2(EXP1,EXP2,EXP3)如果exp1的值為null，則返回exp2