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

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

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

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

AS

年級FROM

Students

GROUPBYSGradeSELECT

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

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

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

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

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

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

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

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

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

SELECT*FROMASELECTA1,B1,MAX(C1)FROMA需求4學號姓名年級…001張青裁1002陳剛1003蘇三東1學號科目分數(shù)001260002270003480如何同時從這兩個表中取得數(shù)據(jù)？常用的多表聯(lián)接查詢內聯(lián)接(INNERJOIN)外聯(lián)接左外聯(lián)接(LEFTJOIN)右外聯(lián)接(RIGHTJOIN)內聯(lián)接3-1SNameSCode梅超風1陳玄風2陸乘風3曲靈風4StudentsIDCourseIDScore100197200189200267300276300381SNameCourseIDScore梅超風00197陳玄風00189陳玄風00267陸乘風00276陸乘風00381StudentsScore查詢結果內聯(lián)接使用比較運算符根據(jù)每個表的通用列中的值匹配兩個表中的行內聯(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等價內聯(lián)接語句內聯(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行下面的查詢語句返回的查詢結果是一樣的嗎？根據(jù)前面表中數(shù)據(jù)，下面的查詢語句將會返回多少行記錄？SELECTStudents.SName,Score.CourseID,Score.ScoreFROM Students,ScoreWHEREStudents.SCode=Score.StudentID兩個語句的執(zhí)行結果相同SELECT

S.SNameAS姓名,CS.CourseNameAS課程,C.ScoreAS成績FROMStudentsASSINNERJOINScoreASCON(S.SCode=C.StudentID)INNERJOINCourseASCSON(CS.CourseID=C.CourseID)三表內聯(lián)接CourseIDCourseName…………姓名課程成績………………SNameSCode…………StudentsIDCourseIDScore………………使用WHERE語句如何實現(xiàn)？左外聯(lián)接SNameSCode梅超風1陳玄風2陸乘風3曲靈風4StudentsIDCourseIDScore100197200189200267300276300381SNameCourseIDScore梅超風00197陳玄風00189陳玄風00267陸乘風00276陸乘風00381曲靈風NULLNULLStudentsScore查詢結果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、匹配，返回到結果集2、無匹配，NULL值返回到結果集猜一猜：這樣寫，返回的查詢結果是一樣的嗎？SELECT

圖書編號,圖書名稱,出版社名稱FROM

圖書表RIGHTOUTERJOIN

出版社表ON

圖書表.出版社編號=出版社表.出版社編號右外聯(lián)接圖書編號圖書名稱出版社名稱1走進Java編程世界北大出版社2HTML和CSS網(wǎng)頁技術清華出版社………………NULLNULL新知出版社………………也許很久沒出版書籍了右外聯(lián)接的原理與左外聯(lián)接相同右表逐條去匹配記錄；否則NULL填充Oracle常用函數(shù)7-1使用函數(shù)可以大大提高SELECT語句操作數(shù)據(jù)庫的能力Oracle中函數(shù)劃分為單行函數(shù)和多行函數(shù)單行函數(shù)作用于數(shù)據(jù)庫表的某一行并返回一個值字符函數(shù)數(shù)字函數(shù)日期函數(shù)轉換函數(shù)其他函數(shù)。多行函數(shù)基于數(shù)據(jù)庫表多行進行運算，返回一個值例如對多行記錄的某個字段進行求和、求最大值運算等Oracle常用函數(shù)7-2常用的字符函數(shù)函數(shù)功能示例結果INITCAP(char)首字母大寫initcap('hello')HelloLOWER(char)轉換為小寫lower('FUN')funUPPER(char)轉換為大寫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ù)功能示例結果ABS(n)取絕對值abs(-15)15CEIL(n)向上取整ceil(44.778)45SIN(n)正弦sin(1.571).999999979COS(n)余弦cos(0)1SIGN(n)取符號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)截斷trunk(100.256,2)100.25SQRT(n)平方根sqrt(4)2Oracle常用函數(shù)7-4常用的日期函數(shù)函數(shù)功能示例結果MONTHS_BETWEEN返回兩個日期間的月份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返回指定日期后的星期對應的新日期next_day('06-2月-03','星期一')10-2月-03LAST_DAY返回指定日期所在的月的最后一天last_day('06-2月-03')28-2月-03ROUND按指定格式對日期進行四舍五入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對日期按指定方式進行截斷trunc(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常用的轉換函數(shù)函數(shù)功能示例結果TO_CHAR轉換成字符串類型to_char(1234.5,'$9999.9')$1234.5TO_DATE轉換成日期類型to_date('1980-01-01','yyyy-mm-dd')01-1月-80TO_NUMBER轉換成數(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