SQL编程及高级查询

第七章SQL编程及高级查询第二单元本章相关学习资源《SQL语言和常用函数》专题《高级查询》专题《Web前端基础及数据库开发》第7章《SQL编程及高级查询》学习平台“Oracle数据库”课程学生用书第2页/共47页预习检查例举数据操纵语言中常用的SQL命令例举Oracle中常用的函数简述查询的分类第3页/共47页本章任务任务1：使用SQL操作学生表任务2：使用SQL函数对表进行操作任务3：查询雇员相关信息第4页/共47页会使用DML语言对数据库进行操作能够熟练运用Oracle常用函数能够熟练运用子查询能够熟练运用连接查询能够熟练运用查询实现分页显示功能本章目标点重点难点难点重点难第5页/共47页串讲：SQL简介SQL是StructuredQueryLanguage（结构化查询语言）的首字母缩写词SQL是数据库语言，Oracle使用该语言存储和检索信息表是主要的数据库对象，用于存储数据SELECTename

FROMEmp;发送SQL查询Oracle服务器enameBLAKESMITH

ALLEN

DAVID

MARTIN发送命令输出到用户端用户通过SQL可以实现与Oracle服务器的通信第6页/共47页串讲：SQL简介SQL支持下列类别的命令：数据定义语言（DDL）数据操纵语言（DML）事务控制语言（TCL）数据控制语言（DCL）数据定义语言CREATEALTERDROP数据操纵语言INSERTSELECTDELETEUPDATE事务控制语言COMMITSAVEPOINTROLLBACK数据控制语言GRANTREVOKE第7页/共47页串讲：数据操纵语言数据操纵语言用于检索、插入和修改数据数据操纵语言是最常见的SQL命令数据操纵语言命令包括：INSERTUPDATEDELETESELECT第8页/共47页串讲：INSERT

命令CREATETABLEstuInfo(stuNoCHAR(6)NOTNULL,--学号，非空stuNameVARCHAR2(20)NOTNULL,--学员姓名，非空stuAgeNUMBER(3,0)NOTNULL,--年龄，非空beginDateDATE--开始学习时间，日期类型);INSERTINTOstuInfo(stuNo,stuName,stuAge)VALUES('034','Tom',23);INSERTINTOstuInfoVALUES('032','Ford',22,SYSDATE);INSERTINTOstuInfoSELECTstuNo,stuName,stuAge,beginDateFROMstuInfoBAK;插入数据插入来自其他表的记录,其中stuInfoBAK为stuInfo备份表已知学生信息表stuInfo,如何向表中存入学生信息？第9页/共47页串讲：UPDATE命令--UPDATE单列UPDATEstuInfoSETstuAge=26WHEREstuNo='035';--UPDATE多列UPDATEstuInfoSETstuage=26,beginDate='2012-09-18'WHEREstuNo='035';修改数据如何修改年龄和开始学习时间？第10页/共47页串讲：DELETE命令--删除学号为035的学生记录DELETEFROMstuInfoWHEREstuNo='035';删除数据如何删除学号为035的学生记录？第11页/共47页如何查询所有学生记录？如何查询学号为035的记录？如何选择指定的学号、姓名列？如何筛除重复的行？串讲：SELECT命令SELECT*FROMstuInfo;SELECT*FROMstuInfoWHEREstuNo='035';

SELECTstuNo,stuNameFROMstuInfo;SELECTDISTINCTstuNo,stuNameFROMstuInfo;

第12页/共47页如何对结果集排序,先按照年龄降序，如果年龄相同的按照学号升序排列?如何利用现有的表(stuInfo)创建新表(stuInfoBAK)？如何使用列别名？别名中有空格如何解决？串讲：SELECT命令CREATETABLEstuInfoBAKasSELECT*FROMstuInfo;SELECT‘S’||stuNo学号,stuName“姓名”FROMstuInfo;SELECT*FROMstuInfoORDERBYstuAgeDESC,stuNoASC;

第13页/共47页演示示例：DML语句操作其中两个字符串拼接用‘||’符号上机练习需求说明针对供应商表S，产品表P，供应情况表SP插入数据。参照提供的素材《供应商练习相关表》。用SQL语句完成以下查询求供应产品编号为P1的供应商编号求供应绿茶等级为1的供应商编号求没有使用北京公司供应商的茶叶，但产品级别是1级茶叶的产品编号完成时间：10分钟共性问题集中讲解练习第14页/共47页串讲：SQL

函数Oracle提供一系列用于执行特定操作的函数SQL函数带有一个或多个参数并返回一个值以下是SQL函数的分类：SQL函数单行函数分析函数聚合函数第15页/共47页串讲：单行函数分类单行函数对于从表中查询的每一行只返回一个值可以出现在SELECT子句中和WHERE子句中单行函数可以大致划分为：日期函数数字函数字符函数转换函数其他函数第16页/共47页串讲：转换函数转换函数将值从一种数据类型转换为另一种数据类型常用的转换函数有：TO_CHARTO_DATETO_NUMBERSELECTTO_CHAR(sysdate,'YYYY"年"MM"月"DD"日"HH24:MI:SS')FROMdual;SELECTTO_CHAR(123456.03,'099,999.99')FROMdual;

SELECTTO_DATE('2005-12-06','yyyy-mm-dd')FROMdual;SELECTTO_NUMBER('100')FROMdual;第17页/共47页演示示例：

函数-转换函数其它函数转换空值的函数NVLNVLDECODESELECTename,sal+NVL(comm,0)sal1,

NVL2(comm,sal+comm,sal)sal2,

DECODE(to_char(hiredate,‘MM’),‘

01’,‘一月’,‘02’,‘二月’,

‘03’,‘三月‘,'04','四月',

‘05','五月','06','六月',

'下半年')

mon

FROMemployee;示例18/45演示示例8：其他函数串讲：聚合函数聚合函数基于一组行来返回结果为每一组行返回一个值AVGMINMAXSUMCOUNTSELECTSUM(sal)FROMemp;SELECTAVG(sal)FROMempWHEREjob='CLERK';SELECTMAX(sal)FROMemp;SELECTCOUNT(*)FROMemp;SELECTCOUNT(comm)FROMemp;SELECTCOUNT(DISTINCTjob)FROMemp;聚合函数第19页/共47页讲解：GROUPBY和HAVING子句GROUPBY子句用于将信息划分为更小的组每一组行返回针对该组的单个结果HAVING子句用于指定GROUPBY子句检索行的条件SELECTdeptno,MAX(sal)maxSal,AVG(sal)avgSal,MIN(sal)minSal

FROMempGROUPBYdeptno;SELECTdeptno,MAX(sal)maxSal,AVG(sal)avgSal,MIN(sal)minSalFROMempGROUPBYdeptnoHAVINGAVG(sal)>2000;第20页/共47页演示示例：

函数-聚合函数讲解：分析函数分析函数根据一组行来计算聚合值用于计算完成聚集的累计排名等分析函数为每组记录返回多个行分析函数DENSE_RANKRANKROW_NUMBER第21页/共47页讲解：分析函数以下三个分析函数用于计算一个行在一组有序行中的排位，序号从1开始ROW_NUMBER返回连续的排位，不论值是否相等RANK具有相等值的行排位相同，序数随后跳跃DENSE_RANK具有相等值的行排位相同，序号是连续的第22页/共47页SELECTename,job,deptno,sal,ROW_NUMBER()OVER(ORDERBYsalDESC)ASSAL_RANKFROMSCOTT.EMP;SELECTdeptno,ename,sal,comm,RANK()OVER(PARTITIONBYdeptnoORDERBYsalDESC,comm)RANKFROMemp;SELECTd.dname,e.ename,e.sal,DENSE_RANK()OVER(PARTITIONBYe.deptnoORDERBYe.salDESC)ASDENRANKFROMempe,deptdWHEREe.deptno=d.deptno;演示示例：

函数-分析函数上机练习需求说明针对供应情况表进行如下操作

查询供货商总人数查询一次供应茶叶的最大斤数查询各个供应商编号及供应茶叶的总斤数查询提供了2种以上茶叶的供货商编号完成时间：15分钟共性问题集中讲解练习第23页/共47页SQL函数分为（)、()和(

)。请列举聚合函数，有（）、（

）、（

）、（）等。小结单行函数聚合函数分析函数SUM

COUNT

AVGMAX第24页/共47页讲解：查询SELECT语句的作用是什么？多表查询的类型有哪些？子查询-将一个查询包含到另一个查询中连接-合并多个数据表中的列联合-合并多个数据表中的行第25页/共47页讲解：子查询为什么使用子查询？是表达查询最自然的方式使编写SELECT语句变得更加简单有些查询如果不使用子查询就无法用SQL表达出来第26页/共47页讲解：子查询实例一EMP表中，查询与“SCOTT”在同一个部门的雇员信息SELECTempno,ename,deptnoFROMempWHEREdeptnoIN(SELECTdeptnoFROMempWHEREename='SCOTT');执行父查询返回子查询值给父查询执行子查询获得结果值实例如果将IN换成NOTIN又如何？第27页/共47页讲解：子查询实例二EMP表中，找出每个雇员超过他所在部门平均工资的雇员编号、雇员名称、薪水、部门编号返回第一步SELECTempno,ename,sal,deptnoFROMempe1WHEREsal>(SELECTAVG(sal)FROMempe2WHEREe1.deptno=e2.deptno);父查询传送列值给子查询子查询获取父查询传送的列值子查询返回查询值给父查询父查询传送下一行的列值给子查询(重复1-3步)实例第28页/共47页讲解：子查询实例三查询其他部门中比30号部门某一雇员薪水少的雇员信息SELECTempno,ename,sal,deptnoFROMempe1WHEREsal<ANY(SELECTsalFROMempWHEREdeptno=30

)

ANDdeptno<>30;实例返回多值时可用ANY或ALL修饰符其中，=ANY等价于IN，<ANY等价于<MAX,<>ALL等价于NOTIN，<ALL等价于<MIN第29页/共47页讲解：子查询实例四EMP表中，查询雇员薪水大于3000的部门名称SELECTdnameFROMdeptdWHEREexists(SELECT*FROMempeWHEREd.deptno=e.deptnoANDsal>3000);实例（1）首先取外层查询中表的第一个记录，根据它与内层查询相关的属性值（deptno）处理内层查询，若where子句返回值为真，则取外层查询中该记录放入结果表（2）再取外层查询的下一个记录，重复这一过程，直到外层查询全部检查完为止由于带EXISTS的相关子查询只关心内层查询是否有返回值，并不需要查具体值，有时是高效的方法第30页/共47页讲解：子查询的分类不相关子查询子查询的查询条件不依赖于父查询属于较简单的一类查询，通常说的嵌套查询属于此类相关子查询子查询的查询条件依赖于父查询第31页/共47页演示示例：查询-子查询串讲：常用的多表连接查询连接分类第32页/共47页内连接外连接自连接交叉连接连接左外连接右外连接等值连接自然连接不等值连接串讲：内连接内连接使用比较运算符根据每个表的通用列中的值匹配两个表中的行SNameSCode梅超风1陈玄风2陆乘风3曲灵风4StudentIDCourseIDScore100197200189200267300276300381SNameCourseIDScore梅超风00197陈玄风00189陈玄风00267陆乘风00276陆乘风00381StudentsScore查询结果第33页/共47页串讲：内连接SELECT

……

FROM

表1

INNERJOIN

表2ON ……SELECT

……

FROM

表1，表2WHERE……

SELECT

S.SName,C.CourseID,C.Score

FROM

ScoreCINNERJOIN

StudentsSON

C.StudentID=S.SCode;SELECTStudents.SName,Score.CourseID,Score.ScoreFROM Students,ScoreWHEREStudents.SCode=Score.StudentID;等价第34页/共47页查询学员姓名、课程和分数演示示例：

查询-内连接下面的查询语句返回的查询结果是一样的吗？根据前面表中数据，下面的查询语句将会返回多少行记录？串讲：内连接SELECT

S.SName,C.CourseID,C.Score

FROM

StudentsSINNERJOIN

ScoreCON

C.StudentID<>S.SCode;SELECT

S.SName,C.CourseID,C.Score

FROM

StudentsSINNERJOIN

ScoreCON

C.StudentID=S.SCode;15行SELECTStudents.SName,Score.CourseID,Score.ScoreFROM Students,ScoreWHEREStudents.SCode=Score.StudentID;两个语句的执行结果相同第35页/共47页上机练习需求说明查询所有雇员姓名和部门名称从emp表中找到在工资等级表中对应的工资等级工资等级表表结构参照书中资料完成时间：8分钟共性问题集中讲解练习第36页/共47页串讲：左外连接SNameSCode梅超风1陈玄风2陆乘风3曲灵风4StudentIDCourseIDScore100197200189200267300276300381SNameCourseIDScore梅超风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

StudentsSLEFTJOIN

ScoreCON

C.StudentID=S.SCode主表（左表）Students中的数据逐条匹配从表Score中的数据1、匹配，返回到结果集2、无匹配，NULL值返回到结果集猜一猜：这样写，返回的查询结果是一样的吗？第37页/共47页演示示例：

查询-左外连接SELECT图书编号,图书名称,出版社名称FROM图书表RIGHTOUTERJOIN出版社表ON图书表.出版社编号=出版社表.出版社编号串讲：右外连接图书编号图书名称出版社名称1走进Java编程世界北大出版社2HTML和CSS网页技术清华出版社………………NULLNULL新知出版社………………也许很久没出版书籍了右外连接的原理与左外连接相同右表逐条去匹配记录；否则NULL填充第38页/共47页讲解：分页查询什么是Oracle伪列？Oracle中伪列就像一个表列，但是它并没有存储在表中伪列可以从表中查询，但不能插入、更新和删除它们的值常用的伪列有哪些？ROWIDROWNUMROWID是表中行的存储地址，该地址可以唯一地标识数据库中的一行，可以使用ROWID伪列快速地定位表中的一行ROWNUM是查询返回的结果集中行的序号，可以使用它来限制查询返回的行数第39页/共47页讲解：分页查询方法一SELECT*FROMempWHERErownum<6ORDERBYsalDESC;方法二SELECT*FROM(SELECT*FROMempORDERBYsalDESC)WHERErownum<6;如何获取雇员表中薪水最高的前5人？方法一为什么错误？第40页/共47页演示示例：

查询-分页查询讲解：分页查询方法一SELECT*FROM(SELECT*FROMempORDE