数据库技术及应用：第4章 Oracle高级查询

Oracle高级查询第四章回顾和作业点评如下的T-SQL语句含义是什么？聚合函数COUNT()和SUM()各代表什么意思？SELECTTelephoneFROMstuWHERETelephoneNOTLIKE'6%'预习检查分组查询主要解决什么问题？多表联接主要解决什么问题？用在什么场合？本章目标使用GROUPBY进行分组查询掌握多表联接查询学号姓名地址所属年级……001王明全湖南长沙1002张菲湖北宜昌1003于寄谦甘肃天水1004刘国正山东荷泽1005周接轮台湾新竹2006巩小妹香港龙湾2007巩大妹香港龙湾2008张明敏北京顺义3009矛十八四川棉阳3010张林光陕西临潼3需求1每年级的学生人数各是多少？分组查询原理学号姓名地址所属年级001王明全湖南长沙1002张菲湖北宜昌1003于寄谦甘肃天水1004刘国正山东荷泽1005周接轮台湾新竹2006巩小妹香港龙湾2007巩大妹香港龙湾2008张明敏北京顺义3009矛十八四川棉阳3010张林光陕西临潼3第一学期人数第二学期人数第三学期人数三组分组查询用法SELECT……FROM

<表名>WHERE……GROUPBY……分组查询语句SELECT

COUNT(*)AS人数,SGrade

AS

年级FROM

Students

GROUPBYSGradeSELECT

CourseID,AVG(Score)AS课程平均成绩FROMScoreGROUPBYCourseID分组查询解析2-1对分组后的每个组内的记录进行一个聚集，通常用到聚合函数查询每门课程的平均分按照什么进行分组？分组查询解析2-2SELECT

CourseID,AVG(Score)AS课程平均成绩FROMScoreGROUPBYCourseIDORDERBYAVG(Score)查询每门课程的平均分，并且按照分数由低到高的顺序排列显示SELECT

StudentID,CourseID,AVG(Score)AS课程平均成绩FROMScoreGROUPBY

CourseID思考分组查询所查询的列1、分组列2、聚合函数计算出的列分析以下的T-SQL，结果会怎样？需求2统计每学期男女同学的人数，该怎么办？对学生进行分组计算每组学生的总人数多列分组2-1年级分组性别分组多列分组2-2SELECTCOUNT(*)AS人数,SGradeAS年级,SSexAS性别

FROMStudentSGROUPBYSGrade,SSexORDERBYSGradeORDERBY子句在这里的作用？需求3如何获得总人数超过15人的年级？条件限定分组筛选SELECT……FROM

<表名>WHERE……GROUPBY……HAVING……SELECTCOUNT(*)AS人数,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学号科目分数001260002270003480如何同时从这两个表中取得数据？常用的多表联接查询内联接(INNERJOIN)外联接左外联接(LEFTJOIN)右外联接(RIGHTJOIN)内联接3-1SNameSCode梅超风1陈玄风2陆乘风3曲灵风4StudentsIDCourseIDScore100197200189200267300276300381SNameCourseIDScore梅超风00197陈玄风00189陈玄风00267陆乘风00276陆乘风00381StudentsScore查询结果内联接使用比较运算符根据每个表的通用列中的值匹配两个表中的行内联接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等价内联接语句内联接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行下面的查询语句返回的查询结果是一样的吗？根据前面表中数据，下面的查询语句将会返回多少行记录？SELECTStudents.SName,Score.CourseID,Score.ScoreFROM Students,ScoreWHEREStudents.SCode=Score.StudentID两个语句的执行结果相同SELECT

S.SNameAS姓名,CS.CourseNameAS课程,C.ScoreAS成绩FROMStudentsASSINNERJOINScoreASCON(S.SCode=C.StudentID)INNERJOINCourseASCSON(CS.CourseID=C.CourseID)三表内联接CourseIDCourseName…………姓名课程成绩………………SNameSCode…………StudentsIDCourseIDScore………………使用WHERE语句如何实现？左外联接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中的数据逐条匹配从表Score中的数据1、匹配，返回到结果集2、无匹配，NULL值返回到结果集猜一猜：这样写，返回的查询结果是一样的吗？SELECT

图书编号,图书名称,出版社名称FROM

图书表RIGHTOUTERJOIN

出版社表ON

图书表.出版社编号=出版社表.出版社编号右外联接图书编号图书名称出版社名称1走进Java编程世界北大出版社2HTML和CSS网页技术清华出版社………………NULLNULL新知出版社………………也许很久没出版书籍了右外联接的原理与左外联接相同右表逐条去匹配记录；否则NULL填充Oracle常用函数7-1使用函数可以大大提高SELECT语句操作数据库的能力Oracle中函数划分为单行函数和多行函数单行函数作用于数据库表的某一行并返回一个值字符函数数字函数日期函数转换函数其他函数。多行函数基于数据库表多行进行运算，返回一个值例如对多行记录的某个字段进行求和、求最大值运算等Oracle常用函数7-2常用的字符函数函数功能示例结果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常用函数7-3常用的数字函数函数功能示例结果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)取余数mod(10,3)1ROUND(m,n)四舍五入round(100.256,2)100.26TRUNC(m,n)截断trunk(100.256,2)100.25SQRT(n)平方根sqrt(4)2Oracle常用函数7-4常用的日期函数函数功能示例结果MONTHS_BETWEEN返回两个日期间的月份months_between('04-11月-05','11-1月-01')57.7741935ADD_MONTHS返回把月份数加到日期上的新日期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常用函数7-5常用的转换函数函数功能示例结果TO_CHAR转换成字符串类型to_char(1234.5,'$9999.9')$1234.5TO_DATE转换成日期类型to_date('1980-01-01','yyyy-mm-dd')01-1月-80TO_NUMBER转换成数值类型to_number('1234.5')1234.5Oracle常用函数7-6常用的其他函数函数功能NVL(EXP1,EXP2)如果exp1的值为null，则返回exp2的值，否则返回exp1的值 NVL2(EXP1,EXP2,EXP3)如果exp1的值为null，则返回exp2