关闭

关闭

关闭

封号提示

内容

首页 51CTO-Oracle数据库实用教程.pdf

51CTO-Oracle数据库实用教程.pdf

51CTO-Oracle数据库实用教程.pdf

上传者: huoying0403 2010-12-18 评分 0 0 0 0 0 0 暂无简介 简介 举报

简介:本文档为《51CTO-Oracle数据库实用教程pdf》,可适用于IT/计算机领域,主题内容包含Oracle数据库实用教程第一章数据库基础张张利利国国VV工作室工作室荣誉出品荣誉出品本章学习目标数据建模数据库基本概念关系型数据库ER模型数据库基符等。

Oracle数据库实用教程第一章数据库基础张张利利国国VV工作室工作室荣誉出品荣誉出品本章学习目标数据建模数据库基本概念关系型数据库ER模型数据库基本概念ƒ数据库(Database,DB)ƒ数据库管理系统(DatabaseManagementSystem,DBMS)ƒ数据库管理员(DatabaseAdministrator,DBA)ƒ数据库系统(DatabaseSystem,DBS)应用程序数据库管理系统数据库数据库系统最终用户数据库管理员数据库基本概念ƒ关系型数据库(RelationshipDatabase,RDB)ƒ关系型数据库管理系统(RDBMS)ƒSQL语言(StructuredQueryLanguage)使用关系模型的数据库语言用于和各类数据库的交互提供通用的数据管理和查询功能:•改变数据库结构•更改系统安全设置•设置用户对数据库或表的许可权限•在数据库中检索需要的信息•对数据库的信息进行更新常用SQL指令:SELECT、INSERT、DELETE、UPDATE、CREATE、DROP数据管理历程ƒ手工管理阶段数据不保存数据由应用程序管理编写程序要考虑数据结构数据面向程序不共享数据不具有独立性随其逻辑结构和物理结构而变化。ƒ文件管理阶段数据以文件形式长期存在数据共享性差ƒ数据库管理阶段有利于实现数据共享数据面向应用而非面向程序数据库发展历程ƒ第一代非关系型数据库系统上世纪年代末问世包括层次型和网状型。ƒ第二代关系型数据库系统(RDBS)上世纪年代中期问世。ƒ第三代对象关系数据库系统(ORDBS、OODBS)上世纪年代中期至今。数据库分类ƒ网状数据库采用以记录类型为结点的网状数据模型ƒ层次型数据库采用层次模型模拟现实世界中按层次组织起来的事物ƒ关系型数据库国家省直辖市自治区市县盟商品员工定单厂家银行支票…关系型数据库ƒ采用二维表结构储存与管理数据并规定了表内和表间数据的依存关系。ƒ当前流行的大型关系型数据库:Oracle、IBMDB、SQLServer、SyBase、Informix等。ƒ关系型数据库采用结构化查询语言(SQL)作为客户端程序与数据库服务器间沟通的桥梁客户端发送SQL指令到服务器端服务器端执行相关的指令并返回其查询的结果结果。赵六王五李四张三姓名女男女男性别ssss学号电话英语古典文学数据结构英语科目成绩ssss学号数据建模ƒ数据建模要将现实世界中客观存在的事物以数据的形式存储到计算机中并进行处理就需要对其进行分析、抽象进而确定数据的结构以及数据间的内在联系这一过程称为数据建模。ƒ数据模型应满足三个方面要求:能够比较真实地模拟现实世界容易为人所理解便于计算机实现ƒ数据模型三要素:数据结构描述事物的静态特性数据操作描述事物的动态特性完整性约束描述事物内部和事物间的约束性关系数据建模(续)概念世界机器世界现实世界ƒ现实世界事物以及事物间的联系是客观存在的ƒ概念世界是人们对现实世界中客观事物及其联系的认识和抽象。概念世界中按用户的观点对现实世界建模所得到的“概念数据模型”不依赖具体的计算机系统和DBMS。ƒ机器世界按计算机的观点对概念世界中的事物(实体及实体间的联系)进一步建模将之抽象转换为与计算机DBMS相关的“物理数据模型”。认识抽象转换数据建模(续)ƒ概念数据模型(ConceptualDatabaseModel,CDM)CDM从用户的观点出发对信息进行建模并不依赖于具体的计算机系统或某个DBMS系统主要用于数据库的概念设计。CDM以的实体关系(ER)模型为基础将现实世界中的客观对象抽象为实体和关系。到机器世界中CDM将被转换为特定DBMS所支持的物理数据模型(PhysicalDatabaseModel,PDM)。ƒCDM相关术语实体(Entity):客观存在并且可以相互区分开来的事物实体集(EntitySet):同一类实体的集合属性(Attribute):描述实体的特性关系(Relationship):实体集之间的对应关系(现实世界事物之间的相互关联)。ER模型ƒ实体关系模型(EntityRelationshipModel,ERModel)ER模型也称为ER方法由PPSChen于年提出该方法使用ER图来描述现实世界的概念模型。在ER模型中现实世界是由一且称为实体的对象和这些对象之间的关系组成的。ER模型三要素:实体、关系、属性ƒER图(EntityRelationshipDiagram)是描述概念世界建立概念模型的实用工具。ER图三要素:•实体用矩形框表示•属性用椭圆形表示并用连线与实体连在一起。•实体间联系用菱形框表示并用连线分别与相关实体相连且需在连线上注明联系类型。ER模型(续)ƒER图举例ƒ实体间联系类型一对一关系(:)一对多关系(:n)多对多关系(m:n)学号学生姓名性别出生日期课程课程编号课时数课程名称选课mn实体间联系ƒ一对一关系(:)两个实体集A和B若A中的每个实体至多和B中的一个实体有联系反之B中的每个实体至多和A中的一个实体有联系称A对B或B对A是一对一关系。ƒ一对多关系(:n)两个实体集A和B如果A中的每个实体可以和B中的~多个实体有联系而B中的每个实体至多和A中的一个实体有联系则称A对B为一对多关系。ƒ多对多关系(m:n)两个实体集A和B若A中的每个实体可与和B中的多个实体有联系反之亦然称A对B或B对A是多对多关系。关系型数据库基本术语ƒ关系:整个二维表ƒ关系名:表格名称ƒ元组:行数据(记录)ƒ属性:列数据(字段分量)ƒ属性名:列名称(字段名)ƒ主键:唯一确定元组的属性组(关键字)ƒ域:属性的取值范围ƒ关系模式:关系名(属性列表)如:学生(学号姓名性别系别年龄籍贯)ƒ约束域完整性约束实体完整性约束参照完整性约束赵六王五李四张三姓名女男女男性别ssss学号电话学员信息表学员信息表OracleƒOracle(甲骨文)公司年月创办(SoftwareDevelopmentLaboratoriesSDL)年SDL更名为RelationalSoftwareIncRSI年RSI再次更名为ORACLE现为全球最大数据库软件及服务供应商、全球第二大软件供应商ƒOracle数据库全球化、跨平台的数据库Oracle遵守数据存取语言、操作系统、用户接口和网络通信协议的工业标准支持多用户、高性能的事务处理强大的安全性控制和完整性控制支持分布式数据库和分布处理Oracle数据库基础教程第二章Oracle入门张张利利国国VV工作室工作室荣誉出品荣誉出品本章学习目标Oracle数据库安装和卸载Oracle基本开发和管理工具Oracle数据库基本概念安装Oracle数据库安装Oracle安装Oracle安装Oracle安装Oracle安装Oracle安装Oracle安装Oracle安装Oracle安装Oracle安装Oracle安装Oracle安装Oracle安装Oracle卸载Oracle数据库ƒ卸载步骤:停止Oracle所有服务运行OracleUniversalInstaller卸载Oracle修改注册表删除Oracle相关信息•Oracle软件有关键值HKEYLOCALMACHINESOFTWAREOracle•Oracle服务HKEYLOCALMACHINESYSTEMCurrentControlSetServices•Oracle事件日志HKEYLOCALMACHINESYSTEMCurrentControlSetServicesEventlogApplication删除Oracle系统目录C:programfilesoralce删除Oracle环境变量删除程序菜单项中的Oracle菜单(重启系统后)删除Oracle工作主目录D:oralceOracle基本概念术语ƒ数据库(Database)是磁盘上存储的数据的集合(包括配置文件、数据文件、日志文件和控制文件等)ƒ数据库实例(DatabaseInstance)运行在数据库文件上的一组Oracle后台进程线程以及一个共享内存区。数据库可以由实例装载和打开。ƒOracle实时应用集群(RAC,RealApplicationClusters)Oracle并行服务器架构RAC架构ƒ数据库服务名(DatabaseServiceName)从oraclei开始引入数据库客户端与服务器端连接时建议指定其数据库服务名而不是数据库实例名。ƒ网络服务名(NetServiceName)数据库在客户端的逻辑表示包含数据库服务名和网络地址两方面信息ƒ监听器(Monitor)Oracle基本概念术语ƒ数据库对象表视图约束条件索引序列同义词存储过程函数触发器包ƒ数据库安全用户方案权限角色配额Oracle数据库存储结构ƒ物理存储结构数据文件(DataFile)重做日志文件(RedoLogFile)控制文件(ControlFile)ƒ逻辑存储结构表空间(TableSpace)段(Segment)区(Extent)块(Block)表空间数据段数据段区块Oracle基本工具ƒ服务器管理工具OEM集成式的系统管理控制台提供管理、诊断以及调优数据库功能远程管理多个网络数据库节点和服务管理并行服务器ƒ客户端工具:SqlPlusSqlPlusWorkSheetiSqlplusOracle数据库基础教程第三章数据库查询基础张张利利国国VV工作室工作室荣誉出品荣誉出品本章学习目标条件查询创建表、插入记录、删除表、查看表结构Select基本语法Select语句中使用表达式、算术运算符、连接运算符、空值、字符串、字段别名Select语句基本用法对数据表的简单操作where子句、比较和逻辑运算符、去除重复行、结果排序简单的表操作创建一个简单的表createtablestudent(namevarchar(),agenumber())插入新记录insertintostudentvalues('Tom',)查看表结构descstudent删除表droptablestudent查询记录select*fromstudentSQL语句分类ƒSelect查询语句ƒDML语句(数据操作语言)Insert、Update、Delete、MergeƒDDL语句(数据定义语言)Create、Alter、Drop、TruncateƒDCL语句(数据控制语言)Grant、Revokeƒ事务控制语句Commit、Rollback、SavepointSelect查询语句记录记录字段字段TableTableTableTableTableTableTableTable联合查询联合查询简单的Select语句ƒ语法格式ƒ查询结果的默认显示效果Sql*plus的默认显示•Date和character型数据左对齐•Numeric型数据右对齐•列标题默认显示为大写iSql*plus的默认显示•列标题默认显示为大写•列标题缺省居中对齐SELECT*|{<字段名>,}FROM<表名>使用算术表达式ƒ在Select语句中对NUMBER型数据可以使用算术运算符创建表达式。ƒ算术运算符*ƒ运算优先级乘法和除法的优先级高于加法和减法同优先级运算的顺序是从左到右表达式中可使用小括号强行改变运算顺序selectempno,ename,sal,sal*fromempselectempno,ename,sal,sal*fromempselectempno,ename,sal,sal*()fromemp连接运算符ƒ连接运算符‘||’可以把列与字符、或其它表达式连接在一起得到一个新的字符串实现‘合成’列的功能。ƒ用法举例:selectename||'isa'||jobfromempselectempno,ename||'''sannualsalaryis'||sal*fromemp使用字段别名ƒ字段别名重命名查询结果中的字段以增强可读性如果别名中使用特殊字符或者是强制大小写敏感需使用双引号ƒ语法格式:ƒ用法举例:selectempnoas员工编号,ename员工姓名,sal*"年薪"fromempselectempno,ename"Ename",sal*"AnualSalary"fromempSELECT<字段名>|<表达式>AS<字段别名>,FROM<表名>空值ƒ什么是空值?空值是无效的未指定的未知的或不可预知的值。空值不等同于空格或者。ƒ空值举例:selectempno,ename,job,sal,commfromemp在表达式中使用空值ƒ算术表达式中如果出现空值则整个表达式结果为空ƒ连接表达式中出现的空值被当作一个空的(长度为零的)字符串处理selectempno,ename,sal,comm,ename||''||comm,salcommfromemp去除重复行ƒ在缺省情况下查询结果中包含所有符合条件的记录行包括重复行。ƒ使用DISTINCT关键字可从查询结果中清除重复行ƒDISTINCT的作用范围是后面所有字段的组合selectdeptnofromempselectdistinctdeptnofromempselectdistinctdeptno,jobfromemp查询结果排序ƒ查询结果缺省按照记录的插入顺序进行排列ƒ也可使用ORDERBY子句对查询结果进行排序排序方式包括升序(ASC缺省)和降序(DESC)两种:ƒ按多字段排序ƒ使用字段别名排序selectempno,ename,salfromemporderbysalselectempno,ename,salfromemporderbysaldescselectdeptno,empno,ename,salfromemporderbydeptno,salselectempno,ename,sal*annsalfromemporderbyannsal条件查询select*fromempwheredeptno=条件查询ƒ语法格式ƒ查询语句中使用字符串和日期字符串和日期值要用单引号扩起来字符串大小写敏感日期值格式敏感缺省的日期格式是'DDMONRR'获取当前缺省日期格式SELECT*|{DISTINCT<字段名>|<表达式><别名>,}FROM<表名>WHERE<查询条件>select*fromempwhereename='SMITH'select*fromempwherehiredate='月'selectsysdatefromdual比较运算符不等于<>小于等于<=小于<大于等于>=大于>等于=含义运算符select*fromempwheresal>select*fromempwheredeptno<>比较运算符()为空值IS模糊查询LIKE出现在集合中IN(set)界于两值之间(包括边界)BETWEENAND含义运算符select*fromempwheresalbetweenandselect*fromempwhereenamein('SMITH','CLARK','KING','Tom')模糊查询ƒ使用LIKE运算符执行模糊查询(通配查询)表示零或多个字符表示一个字符对于特殊符号可使用ESCAPE标识符来查找select*fromempwhereenamelike'S'select*fromempwhereenamelike'A'select*fromempwhereenamelike''escape''判断空值ƒ使用IS运算符进行空值判断select*fromempwherecommis逻辑运算符逻辑"非"NOT逻辑"或"OR逻辑"与"AND含义运算符select*fromempwheredeptno=andsal>select*fromempwheredeptno=orsal>select*fromempwheredeptnonotin(,,)运算符优先级ƒ可使用小括号强行改变运算顺序ORANDNOTNOTBETWEENANDISNOTLIKENOTIN=>>=<<=<>||*运算符优先级select*fromempwherejob='SALESMAN'orjob='CLERK'andsal>=select*fromempwhere(job='SALESMAN'orjob='CLERK')andsal>=Oracle数据库基础教程第四章函数张张利利国国VV工作室工作室荣誉出品荣誉出品字符函数、数值函数、日期函数、转换函数、通用函数本章学习目标函数函数OracleOracle数据类型数据类型函数的分类函数的分类单行函数单行函数常用分组函数、GROUPBY子句、基于多字段分组、HAVING子句分组函数(多行函数)分组函数(多行函数)Oracle主要数据类型在数据库外部保存的大型二进制文件大对象类型最大长度GBbfile基于NLS国家字符集的字符字符大对象类型最大长度GBnclob字符大对象类型最大长度GBclob二进制大对象类型最大长度GBblob变长二进制数据类型最大长度GBlongraw变长二进制数据类型最大长度Braw变长字符型最大长度GB不支持对字符串内容进行搜索long日期型。有效表数范围:公元前年月日到公元后年月日date数值型。m为总位数n为小数位数。总长度最大为位。number(m,n)同varcharvarchar基于NLS国家字符集的字符型其余同varcharnvarchar变长字符型最大长度Bvarchar基于NLS国家字符集的字符型最大长度B缺省为字符nchar字符型最大长度B缺省长度为Bchar说明数据类型函数ƒOracle函数分为单行函数和多行函数两大类ƒ单行函数操作数据项接受参数并返回处理结果对每一返回行起作用可修改数据类型可嵌套使用ƒ单行函数分类字符函数数值函数日期函数转换函数通用函数单行函数单行函数多行函数多行函数字符函数ƒ字符大小写转换函数ƒ字符处理函数单词首字母大写转换为大写转换为小写功能initcap('JOHNsmith')upper('JohnSmith')lower('JohnSmith')用法JohnSmithinitcap()JOHNSIMTHupper()johnsmithlower()返回结果函数MrsSmithandMrsWhitereplace('MrSmithandMrWhite','Mr','Mrs')替换replace()MrSmithtrim('MrSmith')过滤首尾空格trim()Smith*****rpad('Smith',,'*')右侧填充rpad()*****Smithlpad('Smith',,'*')左侧填充lpad()instr('HelloWorld','or')定位子串instr()返回字符串长度截取子串字符串连接功能length('HelloWorld')substr('HelloWorld',,)concat('Hello','World')用法length()loWsubstr()HelloWorldconcat()返回结果函数数值函数sqrt()开平方根sqrt()trunc(,)截断trunc()floor()向下取整floor()sign()判断数值正负sign()sin()三角函数……sin()…power(,)power(,)幂运算power()mod(,)取模mod()exp()基数为e的幂运算exp()log(,)对数运算log()ln()自然对数运算ln()向上取整四舍五入取绝对值功能ceil()round()round(,)round(,)abs()用法ceil()round()abs()返回结果函数日期类型ƒ关于日期类型Oracle内部以数字格式存储日期和时间信息:世纪,年,月,日,小时,分钟,秒缺省的日期格式是DDMONYY可使用sysdate函数获取当前系统日期和时间ƒ日期型数据的算术运算日期型数据可以直接加或减一个数值结果仍为日期两个日期型数据可以相减结果为二者相差多少天日期函数monthsbetween(sysdate,hiredate)返回日期x和y之间相差的月数monthsbetween(x,y)trunc(sysdate,'month')trunc(sysdate,'year')将日期x截断到y所指定的日期单位(月或年)的第一天trunc(x,y)nextday(sysdate,'星期二')计算指定日期x后的第一个星期几(由参数y指定)对应的日期nextday(x,y)将日期x四舍五入到y所指定的日期单位(月或年)的第一天返回日期x当月最后一天的日期计算在日期x基础上增加y个月后的日期功能round(sysdate,'month')round(sysdate,'year')lastday(sysdate)addmonths(sysdate,)用法round(x,y)lastday(x)addmonths(x,y)返回结果函数转换函数ƒ数据类型转换包括隐含转换和显式转换两种方式建议使用显式的数据类型转换确保SQL语句的可靠性。数值类型数值类型字符类型字符类型tochar()tochar()tonumber()tonumber()日期类型日期类型tochar()tochar()todate()todate()日期>字符串ƒtochar()函数可以将日期型数值转换为字符串形式ƒ格式:tochar(date)缺省转换为'ddmmyy'格式tochar(date,'formatmodel')转换为模式串指定的格式selectempno,ename,sal,tochar(hiredate,'yyyymmdd')fromemp常用日期格式符星期五星期几day上午显示上午下午ampm::小时hhhhhh分钟mi秒钟ss在当月中是第几天用数字显示月份年份说明举例ddmmyyyy格式符说明:除上述格式符外日期模式串中还可直接出现如下字符:如要显示其它文本字符串则需使用双引号括起来也可在模式串的开头使用"fm"标记以去掉数字前面的零。字符串>日期ƒtodate()函数可以将字符串转换为日期类型ƒ格式:todate(char)按缺省格式('ddmmyy')进行解析todate(char,'formatmodel')按模式串指定的格式进行解析insertintotestvalues('Tom',todate('','yyyymmdd'))数字>字符串ƒtochar()函数可以将数字值转换为字符串形式ƒ格式:ƒ数字模式符:tochar(number)tochar(number,'formatmodel')selecttochar()fromdualselecttochar(,'$,')fromdual千位指示符,小数点放置一个本地货币符放置一个美圆符$代表一位数字强制显示代表一位数字说明$L格式符字符串>数字ƒtonumber()函数可以将字符串转换为数字值ƒ格式:ƒ举例:tonumber(char)tonumber(char,'formatmodel')selecttonumber('')fromdualselecttonumber('$,','$,')fromdual通用函数ƒ通用函数适用于任何类型数据(包括空值):nvl()nvl()if()coalesce()case表达式decode()NVL()函数ƒNVL()函数用于将空值替换为指定的缺省值适用于字符、数字、日期等类型数据。ƒ语法格式:ƒ说明:如果表达式exp的值为则返回exp的值否则返回exp的值。ƒ用法举例:NVL(exp,exp)selectempno,ename,sal,comm,salnvl(comm,)fromempselectempno,ename,hiredate,nvl(hiredate,sysdate)fromempselectempno,ename,job,nvl(job,'Nojobyet')fromempNVL()函数ƒNVL()函数用于实现条件表达式功能。ƒ语法格式:ƒ说明:如果表达式exp的值不为则返回exp的值否则返回exp的值。ƒ用法举例:NVL(exp,exp,exp)selectempno,ename,sal,comm,nvl(comm,salcomm,sal)totalfromempIF()函数ƒif()函数用于数据等价性比较并根据比较结果返回或其中一个被比较的数值。ƒ语法格式:ƒ说明:如果表达式exp与exp的值相等则返回否则返回exp的值。ƒ用法举例:if(exp,exp)selectname原名,if(penname,name)化名fromauthorCOALESCE()函数ƒcoalesce()函数用于实现数据“接合”功能。ƒ语法格式:ƒ说明:依次考察各参数表达式遇到非值即停止并返回该值。ƒ用法举例:coalesce(exp,exp,…)selectempno,ename,sal,comm,coalesce(salcomm,sal,)总收入fromempCASE表达式ƒcase表达式用于实现多路分支结构ƒ语法格式:ƒ用法举例:caseexpwhencomparisonexpthenreturnexpwhencomparisonexpthenreturnexpwhencomparisonexpnthenreturnexpnelseelseexpendselectempno,ename,sal,casedeptnowhenthen'财务部'whenthen'研发部'whenthen'销售部'else'未知部门'end部门fromempDECODE()函数ƒ和case表达式类似decode()函数也用于实现多路分支结构ƒ语法格式:ƒ用法举例:decode(col|expression,search,result,search,result,,,default)selectempno,ename,sal,decode(deptno,,'财务部',,'研发部',,'销售部','未知部门')部门fromemp函数嵌套ƒ单行函数可以嵌套使用嵌套层次无限制ƒ嵌套函数的执行顺序是由内到外selectempno,lpad(initcap(trim(ename)),,'')name,job,salfromemp单行函数小结ƒ使用系统提供的单行函数可实现如下功能:对数据进行计算控制数据的输出格式设置改变日期的显示格式进行数据类型转换使用NVL函数处理空值实现IFTHENELSE多路分支逻辑分组函数ƒ分组函数对一组数据进行运算针对一组数据(多行记录)只返回一个结果也称多行函数。ƒ常用分组函数求和计算最小值计算最大值返回查询所得到的记录行数计算平均值功能说明数值型sum()任何类型数据任何类型数据任何类型数据数值型适用类型count()max()min()avg()函数selectavg(sal),max(sal),min(sal),sum(sal)fromempselectmax(hiredate),min(hiredate)fromempCOUNT()函数ƒcount(*)返回组中总记录数目ƒcount(exp)返回表达式exp值非空的记录数目ƒcount(distinct(exp))返回表达式exp值不重复的、非空的记录数目。selectcount(*)fromempselectcount(comm)fromempselectcount(distinct(deptno))fromemp分组函数与空值ƒ分组函数省略列中的空值ƒ可使用NVL()函数强制分组函数处理空值selectavg(comm)fromempselectsum(comm)fromempselectavg(nvl(comm,))fromemp建立数据分组EMP表计算EMP表各部门平均工资GROUPBY子句ƒGROUPBY子句将表中数据分成若干小组ƒ语法格式ƒ说明出现在SELECT列表中的字段如果不是包含在组函数中那么该字段必须同时在GROUPBY子句中出现。包含在GROUPBY子句中的字段则不必须出现在SELECT列表中。可使用where字句限定查询条件可使用Orderby子句指定排序方式selectcolumn,groupfunction(column)fromtablewhereconditiongroupbygroupbyexpressionorderbycolumnselectdeptno,avg(sal)fromempgroupbydeptno基于多个字段分组selectdeptno,job,avg(sal)fromempgroupbydeptno,job组函数的错误用法ƒ如果没有GROUPBY子句SELECT列表中不允许出现字段(单行函数)与分组函数混用的情况。ƒ不允许在WHERE子句中使用分组函数。selectempno,salfromemp合法selectavg(sal)fromemp合法selectempno,initcap(ename),avg(sal)fromemp非法selectdeptno,avg(sal)fromempwhereavg(sal)>groupbydeptnowhereavg(sal)>*ERROR位于第行:ORA:此处不允许使用分组函数SP:未知的命令开头"groupbyd"忽略了剩余的行。HAVING子句ƒHAVING子句用于过滤分组ƒ语法格式selectcolumn,groupfunction(column)fromtablewhereconditiongroupbygroupbyexpressionhavinggroupconditionorderbycolumnselectdeptno,job,avg(sal)fromempwherehiredate>=todate('','yyyymmdd')groupbydeptno,jobhavingavg(sal)>orderbydeptno,job分组函数嵌套ƒ分组函数最多可嵌套两层selectmax(avg(sal))fromempgroupbydeptnoOracle数据库基础教程第五章多表连接与子查询张张利利国国VV工作室工作室荣誉出品荣誉出品单行子查询、多行子查询、子查询中的空值单行子查询、多行子查询、子查询中的空值问题、问题、TopNTopN查询分析查询分析本章学习目标多表连接查询多表连接查询子查询子查询等值连接和非等值连接等值连接和非等值连接外连接外连接自连接自连接SQLSQL表连接语法表连接语法什么是多表连接查询EMP表DEP表笛卡尔集EMP表(行)DEP表(行)笛卡儿集(x=行)Oracle表连接ƒSQLOracle使用表连接从多个表中查询数据ƒ语法格式:ƒ说明:在Where子句中指定连接条件当被连接的多个表中存在同名字段时必须在该字段前加上"表名"作为前缀。select字段列表fromtable,tablewheretablecolumn=tablecolumn连接的类型ƒOraclei之前的表连接:等值连接(Equijoin)非等值连接(NonEquijoin)外连接(Outerjoin)•左外连接•右外连接自连接(Selfjoin)ƒOraclei新引入的的连接形式(支持SQL规范):交叉连接(Crossjoin)自然连接(Naturaljoin)使用Using子句建立连接使用On子句建立连接外连接(Outerjoin)•左外连接•右外连接•全外连接等值连接(Equijoin)ƒ什么是等值连接?ƒ多表连接中:可使用AND操作符增加查询条件使用表别名可以简化查询使用表名(表别名)前缀可提高查询效率为了连接n个表至少需要n个连接条件selectempno,ename,sal,empdeptno,dnamefromemp,deptwhereempdeptno=deptdeptno非等值连接(NonEquijoin)EMP表(行)SALGRADE表(行)问题:如何查得每个员工的工资等级?外连接(Outerjoin)ƒ使用外连接可以看到参与连接的某一方不满足连接条件的记录。ƒ外连接运算符为()ƒ传统的外连接分为左外连接和右外连接两种ƒ语法格式:select字段列表fromtable,tablewheretablecolumn()=tablecolumnselect字段列表fromtable,tablewheretablecolumn=tablecolumn()自连接(Selfjoin)员工表(EMP)管理人员表(EMP)问题:如何查得每个员工及其上司的工号和姓名?SQL连接语法ƒSQL规范中规定的连接查询语法select字段列表fromtablecrossjointable|naturaljointable|jointableusing(字段名)|jointableon(tablecolumnname=tablecolumnname)|(left|right|fullouter)jointableon(tablecolumnname=tablecolumnname)交叉连接(Crossjoin)ƒCrossjoin产生了一个笛卡尔集其效果等同于在两个表进行连接时未使用WHERE子句限定连接条件ƒ举例:selectempno,ename,sal,empdeptno,dnamefromempcrossjoindept自然连接(Naturaljoin)ƒNaturaljoin基于两个表中的全部同名列建立连接从两个表中选出同名列的值均对应相等的所有行如果两个表中同名列的数据类型不同则出错不允许在参照列上使用表名或者别名作为前缀ƒ举例:selectempno,ename,sal,deptno,dnamefromempnaturaljoindeptUsing子句ƒ如果不希望参照被连接表的所有同名列进行等值连接自然连接将无法满足要求可以在连接时使用USING子句来设置用于等值连接的列(参照列)名。ƒ举例:ƒ不允许在参照列上使用表名或者别名作为前缀selectempno,ename,sal,deptno,dnamefromempjoindeptusing(deptno)On子句ƒ如果要参照非同名的列进行等值连接或想设置任意的连接条件可以使用ON子句。ƒ举例:selectempno,ename,sal,empdeptno,dnamefromempjoindepton(empdeptno=deptdeptno)多表连接ƒ使用SQL连接语法两个以上的表进行连接时应依次分别指定相临的两个表之间的连接条件。select字段列表fromtablecrossjointable|naturaljointable|jointableusing(字段名)|jointableon(tablecolumnname=tablecolumnname)|(left|right|fullouter)jointableon(tablecolumnname=tablecolumnname)crossjointable|naturaljointable|jointableusing(字段名)|jointableon(tablecolumnname=tablecolumnname)|(left|right|fullouter)jointableon(tablecolumnname=tablecolumnname)……内连接和外连接ƒ内连接(InnerJoin)在SQL规范中内连接只返回满足连接条件的数据。ƒ外连接(OuterJoin)左外联接(LeftOuterJoin)两个表在连接过程中除返回满足连接条件的行以外还返回左表中不满足条件的行这种连接称为左外联接。右外联接(RightOuterJoin)两个表在连接过程中除返回满足连接条件的行以外还返回右表中不满足条件的行这种连接称为右外联接。满外联接(FullOuterJoin)Oraclei开始新增功能两个表在连接过程中除返回满足连接条件的行以外还返回两个表中不满足条件的所有行这种连接称为满外联接。子查询(SubQuery)ƒ问题引入如何查得所有比"张三"工资高的员工的信息ƒ子查询子查询在主查询前执行一次主查询使用子查询的结果语法格式:select字段列表fromtablewhere表达式operator(select字段列表fromtable)子查询的种类ƒ单行子查询ƒ多行子查询主查询子查询返回KINGALLENMARTINTURNER主查询子查询返回使用子查询注意事项ƒ在查询是基于未知值时应考虑使用子查询ƒ

用户评论(0)

0/200

精彩专题

上传我的资料

每篇奖励 +2积分

资料评价:

/19
仅支持在线阅读

意见
反馈

立即扫码关注

爱问共享资料微信公众号

返回
顶部