Oracle数据库技术基础全套课件

1、Oracle数据库技术基础1数据库技术基础数据库应用系统的构成硬件服务器：专用计算机，配置高端CPU、内存条、磁盘客户端：PC机，手机软件操作系统：Unix，Linux，WindowsDBMS：Oracle，DB2，SQL Server，Access客户端人员系统分析人员，数据库设计人员，应用程序开发人员数据库管理员终端用户 1-3Oracle数据库系统原理-第1章 数据库技术基础2关系模型理论的发展及主要产品数据处理的历史1940s科学与工程计算只能处理数字没有大容量存储器1950s字符发生器高速磁带机磁盘存储器文件管理系统1960s数据库技术 2-5Oracle数据库系统原理-第2章 关系

2、模型理论的发展及主要产品数据模型的三个要素数据结构对所处理的数据经过抽象后，呈现给用户的逻辑形式数据操作对数据模型中的对象可执行操作的集合约束条件完整性规则的集合 2-6Oracle数据库系统原理-第2章 关系模型理论的发展及主要产品数据模型概念的提出E. F. Codd，Data Models in Database Management，1980 2-7Oracle数据库系统原理-第2章 关系模型理论的发展及主要产品网状模型和层次模型的提出网状模型1964，通用电气公司(GE，即General Electric)Charles BachmanIDS(Integrated Data Stor

3、e)Bachman因对网状模型的贡献，获得1973年度图灵奖层次模型1961，阿波罗登月计划1968，IBM，ICS，用于管理数目庞大的零部件1969，ICS更名为IMS/360 2-8Oracle数据库系统原理-第2章 关系模型理论的发展及主要产品Charles Bachman1924.12.11，生于Manhattan, Kansas1944.31946.2，任防空炮兵(澳大利亚，菲律宾)19461948， Michigan State College(1964年，改为MSU)，BS，机械工程19491950，University of Pennsylvania，MS，机械工程，MBA19

4、60，加入GE1963，开发出IDS1973，获得Turing Award 2-9Oracle数据库系统原理-第2章 关系模型理论的发展及主要产品Charles Bachman 2-10Oracle数据库系统原理-第2章 关系模型理论的发展及主要产品层次模型和网状模型的贡献和缺陷贡献IDS是第一个具备通用功能的数据库产品，开创了数据处理的数据库时代。提出了数据库的三层模式、DML与DDL语言等概念。IMS第一个提出了程序与数据分离的思想，使得数据独立性有了很大提高。缺陷数据结构复杂数据独立性存在一定问题 2-11Oracle数据库系统原理-第2章 关系模型理论的发展及主要产品数据独立性数据的物

5、理独立性存储数据的文件位置发生改变，应用程序不需要修改。数据的逻辑独立性增加属性，扩充功能，应用程序不需要修改。 2-12Oracle数据库系统原理-第2章 关系模型理论的发展及主要产品Codd的建议用一种简单的结构存储数据。用一种更高层次的、面向集合的语言访问数据。不需要说明数据的物理存储方式。 2-13Oracle数据库系统原理-第2章 关系模型理论的发展及主要产品关系模型理论的提出1970年，Codd，A Relational Model of Data for Large Shared Data Banks因为对关系模型理论的贡献，Codd获得1981年度图灵奖 2-14Oracle数

6、据库系统原理-第2章 关系模型理论的发展及主要产品E.F. Codd1923.8.19，生于英格兰在Oxford学习数学和化学二次大战中，Royal Air Force飞行员1948，去纽约加入IBM1965, CS PhD, Univ. of Michigan, Ann Arbor1970，提出关系数据模型理论1981，获得Turing Award 2003.4.18，在佛罗里达家中因心脏病去世 2-15Oracle数据库系统原理-第2章 关系模型理论的发展及主要产品IBMS的System R项目19731979由System R演化而来的产品1979，Oracle 2.01981，SQL/

7、Data1983，DB2James Gray，项目成员，因对数据库系统和事务处理的贡献，获得1998年度图灵 2-16Oracle数据库系统原理-第2章 关系模型理论的发展及主要产品UCB的Ingres项目19731985, Michael Stonebraker与Eugene Wong由Ingres演化而来的产品1984，Ingres主要开发者Robert Epstein创立Sybase。1987.5，发布Sybase系统。1993，微软购买Sybase源代码的许可，即SQL Server。1996.8， PostgreSQL 2-17Oracle数据库系统原理-第2章 关系模型理论的发展及

8、主要产品Michael Stonebraker(1943-)1965，Princeton，BS1967， MS， University of Michigan 1971，PhD，University of Michigan19712001，UCB任教2001，MIT任教2015.3，因为对现代数据库系统的实践和基础理论贡献，获得2014年度图灵奖 2-18Oracle数据库系统原理-第2章 关系模型理论的发展及主要产品Michael Stonebraker学生：Robert Epstein (founder and former VP of Engineering ofSybase)Diane

9、 Greene (co-founder and former CEO ofVMWare )Paula Hawthorn (founder of Britton-Lee, formerly VP of Engineering ofInformix) 2-19Oracle数据库系统原理-第2章 关系模型理论的发展及主要产品关系定义关系是元组的集合元组是由若干列构成的行 2-20Oracle数据库系统原理-第2章 关系模型理论的发展及主要产品关系模型的三要素数据结构：关系，即表操作方式：关系代数选择映射并差连接约束实体完整性约束引用完整性约束 2-21Oracle数据库系统原理-第2章 关系模型理论

10、的发展及主要产品几个术语的不同叫法 2-22Oracle数据库系统原理-第2章 关系模型理论的发展及主要产品关系和表的区别关系是集合，是数学概念，行之间无顺序，列之间也无顺序。表中的行一般会为了方便查看，以某个列排序，列也会按照习惯排序。 2-23Oracle数据库系统原理-第2章 关系模型理论的发展及主要产品关系模型的特点严格的理论基础简单的逻辑结构面向集合的操作语言, 与此相对的是面向过程语言 2-24Oracle数据库系统原理-第2章 关系模型理论的发展及主要产品面向过程的数据处理方式也称为imperative language以C语言读取文件为例(写入文件代码参考本页备注)fp=fop

11、en(e:law.dat,rb);fread(s,sizeof(struct stu),1,fp); printf(输入要查询的号码：n); fflush(stdin); scanf(%d,&sn); while(!feof(fp)if(sn=s-sno) printf(%d,%s,%d,%dn, s-sno,s-sname,s-age,s-wgt);fclose(fp); 2-25Oracle数据库系统原理-第2章 关系模型理论的发展及主要产品面向集合语言也称为declarative languageselect * from emp where deptno=10 2-26Oracle数据

12、库系统原理-第2章 关系模型理论的发展及主要产品关系型数据库产品分类根据应用的规模使用方式分为PC数据库大型数据库根据是否开源分为开源数据非开源数据库 2-27Oracle数据库系统原理-第2章 关系模型理论的发展及主要产品PC数据库的特点本质上是文件管理系统不支持的高级功能C/S结构并发操作事务处理实时备份恢复用户和权限管理价低、易用、少维护一般用于小数据量、单用户系统 2-28Oracle数据库系统原理-第2章 关系模型理论的发展及主要产品非开源关系型数据库的典型产品PC数据库AccessFoxPro大型数据库Oracle：中国各电信公司计费系统SQL ServerDB2Sybase：铁路

13、售票系统，农行，广州银行Informix 2-29Oracle数据库系统原理-第2章 关系模型理论的发展及主要产品开源关系型数据库的典型产品PC数据库SQLite：嵌入式数据库，可以置于手机内中型数据库MySQL：用于论坛，微博等非关键领域PostgreSQL 2-30Oracle数据库系统原理-第2章 关系模型理论的发展及主要产品Oracle公司的早期创立者 2-31Oracle数据库系统原理-第2章 关系模型理论的发展及主要产品Ed Oates, Bruce Scott, Bob Miner, Larry Ellison公司成立一周年Larry Ellison1944.8.17，生于纽约，

14、成长于芝加哥University of Illinois at Urbana-Champaign ，University of Chicago (均中途退学)1977，founded SDL with two partners and an investment of $2000; $1200 was his.1979，开发出Oracle数据库2014， Forbes，3rd-wealthiest man in US，5th-wealthiest person in the world, $56.2 billion. 2-32Oracle数据库系统原理-第2章 关系模型理论的发展及主要产品Bo

15、b Miner1941.12.23，生于伊利诺伊州1963年，毕业于UIUC，数学一直担任Oracle公司主管技术的副总裁1994.11.11，在加州旧金山因肺癌去世2014，Forbes，美国最富有家庭，170位。 2-33Oracle数据库系统原理-第2章 关系模型理论的发展及主要产品3ER模型数据库设计在需求分析的基础上顺序进行的三个步骤概念设计逻辑设计物理设计 3-35Oracle数据库系统原理-第3章 ER模型需求分析的主要任务通过对客户的调查，得到数据库应用的下面内容要保存的数据要完成的功能 3-36Oracle数据库系统原理-第3章 ER模型数据库设计的任务概念设计由需求分析结果

16、抽象出应用的实体及联系，得到ER图。逻辑设计把ER图转化为表的结构。物理设计在具体的DBMS上把表实现，并包括创建合适的索引。 3-37Oracle数据库系统原理-第3章 ER模型ER图的概念ER表示Entity Relationship，即实体联系。是一种可以应用于关系型数据库(但不限于关系型)概念设计的建模方法。最早由陈品山(Peter Pin-Shan Chen)在1976年提出，是数据库概念设计的主要方法。 3-38Oracle数据库系统原理-第3章 ER模型陈品山1947，生于台中市1968，毕业于台湾大学，BS，EE1973，Harvard，PhD1976，发表The Entity

17、Relationship Model Toward A Unified View of Data曾在IBM，Honeywell，DEC短暂工作19741978，Assistant Professor，MIT19781983，Associate Professor，UCLA19832011，Professor，Louisiana State University当前，CMU 3-39Oracle数据库系统原理-第3章 ER模型ER模型的目的甲乙方以一种非技术的模型，交流对数据的理解，保证数据库设计反映了企业对数据的使用方式。以直观的形式开始数据库设计过程。 3-40Oracle数据库系统原理-第

18、3章 ER模型实体-联系的概念实体是现实世界中可区别于其他对象的一个“事件”或一个“物体”。现实世界是由一系列的实体以及这些实体间的联系构成的。 3-41Oracle数据库系统原理-第3章 ER模型ER图的画法陈方法Crows FeetUML 3-42Oracle数据库系统原理-第3章 ER模型UML画法用矩形表示实体把矩形分为上下两部分，上半部分是实体名称，下半部分填入实体的属性存在联系的两个实体用直线连接起来，把联系名称放在直线上面联系一般是有方向的，在其名称左侧或右侧放置一个箭头符号表示联系的方向 3-43Oracle数据库系统原理-第3章 ER模型ER图示例 3-44Oracle数据库

19、系统原理-第3章 ER模型联系的映射约束映射约束是指一个实体集中的实体通过联系可以同另一个实体集中相联系的实体数量。 3-45Oracle数据库系统原理-第3章 ER模型映射约束的种类一对一一对多多对多 3-46Oracle数据库系统原理-第3章 ER模型ER图转化为表实体名称即表名实体属性即列名根据联系的映射约束情况把联系转化为表 3-47Oracle数据库系统原理-第3章 ER模型一对一联系转化为表把两个实体及联系的属性合并为一个表，两个实体的主键都可以选作新表的主键。 3-48Oracle数据库系统原理-第3章 ER模型一对多联系转化为表把处在“一”的一方的实体的主键及联系的属性合并到“

20、多”的一方。也可以把联系转化为一个独立的表，这个新表由两个实体的主键及联系的属性构成，多的一端的主键作为这个新表的主键。 3-49Oracle数据库系统原理-第3章 ER模型多对多联系把联系转化为一个独立的表这个表由联系的属性及两个实体的主键构成这两个实体的主键作为新表的外键各自指向由两个实体转化而成的两个表的主键 3-50Oracle数据库系统原理-第3章 ER模型4规范化理论范式即Normal Form，数据规范形式目的是减少数据冗余和避免数据异常 4-52Oracle数据库系统原理-第4章 规范化理论emp和dept表合并产生的问题空间浪费产生异常 4-53Oracle数据库系统原理-第

21、4章 规范化理论Insert异常新添加员工的dept部分的细节要跟其他已有的记录完全一致，否则会造成表中的数据彼此不一致。若一个新部门还没有员工，则此部门的信息不能添加到表中去，此表中就查不到这些部门的信息。 4-54Oracle数据库系统原理-第4章 规范化理论Deletion异常如果一个部门的员工都辞职或调到其他部门，其对应员工的记录都会被删除，这个部门的信息在表中也就不存在了。 4-55Oracle数据库系统原理-第4章 规范化理论Update异常若一个部门有多个员工，而其部门名称发生改变，则必须更新这个部门的所有记录的部门名称字段，如果遗漏一个，会造成表中数据的彼此不一致。 4-56O

22、racle数据库系统原理-第4章 规范化理论第一范式如果一个表没有多值字段，则我们说这个表是满足第一范式的(1NF)下面表中的author列有两个值，不满足1NF 4-57Oracle数据库系统原理-第4章 规范化理论ISBNTITLEPUBDATERETAILCATEGORYPUBLISHERAUTHOR7-04-007494-X数据库系统概论2002-1-125.10计算机高等教育出版社萨师煊，王珊如何转化为第一范式把多值列移出，与原表主键一起构成一个新表 4-58Oracle数据库系统原理-第4章 规范化理论ISBNTITLEPUBDATERETAILCATEGORYPUBLISHER7

23、-04-007494-X数据库系统概论2002-1-125.10计算机高等教育出版社ISBNAUTHOR7-04-007494-X萨师煊7-04-007494-X王珊第二范式如果一个表为第一范式，而且非主键字段完全依赖于主键，则此表称为满足第二范式。下面表的主键由ISBN和author列构成，title等列只依赖于ISBN，不满足第二范式。 4-59Oracle数据库系统原理-第4章 规范化理论ISBNTITLEPUBDATERETAILCATEGORYPUBLISHERCONTACTAUTHOR7-04-007494-X数据库系统概论2002-1-125.10计算机高等教育出版社李军萨师煊7

24、-04-007494-X数据库系统概论2002-1-125.10计算机高等教育出版社李军王珊如何转化为第二范式把部分依赖的非主键字段移出，与原表所依赖的部分主键构成一个新表 4-60Oracle数据库系统原理-第4章 规范化理论ISBNTITLEPUBDATERETAILCATEGORYPUBLISHERCONTACT7-04-007494-X数据库系统概论2002-1-125.10计算机高等教育出版社李军ISBNAUTHOR7-04-007494-X萨师煊7-04-007494-X王珊第三范式如果一个表满足第二方式，并且没有非主键字段传递依赖于主键字段，则我们称这个表满足第三范式出版社的联系

25、人字段CONTACT显然依赖于PUBLISHER字段，而PUBLISHER字段的值又依赖于ISBN，我们称CONTACT字段传递依赖于ISBN 4-61Oracle数据库系统原理-第4章 规范化理论如何转化为第三范式把存在传递依赖的字段从原表移出，与其依赖的字段构成一个新表 4-62Oracle数据库系统原理-第4章 规范化理论ISBNTITLEPUBDATERETAILCATEGORYPUBLISHER7-04-007494-X数据库系统概论2002-1-125.10计算机高等教育出版社PUBLISHERCONTACT高等教育出版社李军7配置和使用SQL*PlusSQL*PLus是什么Ora

26、cle提供的、用于执行SQL命令的工具字符界面，只能输入命令前身是Oracle 4.0的UFI1985，Oracle 5.0，改称为SQL*Plus 7-64Oracle数据库系统原理-第7章 配置和使用SQL*PlusOracle提供的GUI客户端工具SQL Developer2006-3出品可用于多种常见数据库产品Oracle Enterprise Manager2001，Oracle9i加入使用B/S结构 7-65Oracle数据库系统原理-第7章 配置和使用SQL*Plus第三方GUI客户端工具PL/SQL Developer1998， Allround Automations公司(荷

27、兰)主要用于开发PL/SQL程序toadTool for Oracle Application Developers1995，Jim McDaniel1998-10，被Quest公司收购2012-09，Quest公司被Dell公司收购支持常见数据库产品 7-66Oracle数据库系统原理-第7章 配置和使用SQL*PlusSQL*Plus执行的命令种类SQL statementsPL/SQL blocksSQL*Plus internal commandsenvironment control commands such as SETenvironment monitoring command

28、s such as SHOWCommentsExternal commands prefixed by the ! char 7-67Oracle数据库系统原理-第7章 配置和使用SQL*Plus开始使用SQL*Plus启动/关闭数据库服务C:net start oracleservicelaw12C:net stop oracleservicelaw12连接到数据库C:sqlplus scott/tiger执行SQL命令(行尾加分号，或换行后输入/)SQL select * from dept; SQL select * from dep 2 / 7-68Oracle数据库系统原理-第7章

29、配置和使用SQL*Plus可用数据库用户系统用户sys：权限最高，具备sysdba权限，可以启动关闭数据库，口令在建库时指定system：在数据库内权限最高，具备dba角色，口令在建库时指定测试用户scott/tiger：用于学习和测试数据库功能，可以删除，可以执行？RDBMSADMIN scott.sql创建(或utlsampl.sql)，执行此脚本，会同时创建模拟人力资源管理的四张表：emp，dept，salgrade，bonus 7-69Oracle数据库系统原理-第7章 配置和使用SQL*Plus获得SQL*Plus帮助信息获得SQL*Plus帮助SQL help获得SQL*Plus帮

30、助主题SQL help index获得指定主题的帮助SQL help show 7-70Oracle数据库系统原理-第7章 配置和使用SQL*Plus常用SQL*Plus命令查看当前用户SQL show user查看当前用户下的表SQL select * from tab;修改执行过的SQL命令SQL edit 查看指定表的结构SQL describe dept执行脚本文件SQL start c:test.sql 7-71Oracle数据库系统原理-第7章 配置和使用SQL*Plus设置SQL*Plus环境设置每行的最大字符数SQL set linesize 300设置每页的最大行数SQL s

31、et pagesize 300设置指定列的宽度SQL column filename format a50环境设置永久化把环境设置命令存入glogin.sql文件 7-72Oracle数据库系统原理-第7章 配置和使用SQL*Plus给用户解锁和设置口令给scott用户解锁SQL connect system/oracle已连接。SQL alter user scott account unlock;修改scott用户的口令为tigerSQL connect system/oracle已连接。SQL alter user scott identified by tiger; 7-73Oracl

32、e数据库系统原理-第7章 配置和使用SQL*Plus8SQL查询语句SQL历史1970，Codd提出关系模型1973，IBM启动System R项目Donald Chamberlin和Ray Boyce负责操作工具1974，DML与DDL成形称为SEQUEL (Structured English Query Language)1980，重命名为SQLSEQUEL已被英国飞机公司Hawker Siddeley用作商标.SQL, Structured Query Language，结构化查询语言 8-75Oracle数据库系统原理-第8章 SQL查询语句Chamberlin(1944-)1966

33、, BS, Engineering, Harvey Mudd College1967, MS, EE, Stanford University1971, PhD, EE, Stanford University 8-76Oracle数据库系统原理-第8章 SQL查询语句SQL语言基本类型DMLselect，Update，Insert，deleteDDLcreate，drop，alter，truncate tableDCLgrant，revoke 8-77Oracle数据库系统原理-第8章 SQL查询语句Oracle对SQL语言的分类DDL语句DML语句事务控制语句会话控制语句系统控制语句嵌入式

34、SQL语句 8-78Oracle数据库系统原理-第8章 SQL查询语句select语句基本结构select 指定要查询的列名from 指定要查询的表名where 指定查询条件 group by 指定要分组列having 指定过滤分组的条件order by 指定排序列 8-79Oracle数据库系统原理-第8章 SQL查询语句查询示例 8-80Oracle数据库系统原理-第8章 SQL查询语句三种常用数据类型数值number(p,s)：p为有效数字位数，s为精度字符串日期时间date：包括年、月、日、小时、分钟、秒 8-81Oracle数据库系统原理-第8章 SQL查询语句字符集ASCII码即A

35、merican Standard Code for Information Interchange。由早期电报编码发展而来，使用7位编码。主要用于处理英文字母。包括33个不可打印的控制字符和95个包括空格在内的可打印字符。1963，ASCII码标准由ASA(American Standards Association，即后来的ANSI)发布。 8-82Oracle数据库系统原理-第8章 SQL查询语句EBCDIC编码即Extended Binary Coded Decimal Interchange Code使用8位编码设计于1963至1964年主要用于IBM大型机System/360。 8-

36、83Oracle数据库系统原理-第8章 SQL查询语句不同语言的字符集不同国家和地区编制了适合存储其语言的字符集。中文一般使用ZHS16GBK字符集。unicode字符集试图把世界上使用所有字符统一到一个字符集中。 8-84Oracle数据库系统原理-第8章 SQL查询语句unicode编码方式UTF-8字符编码单位一个字节。UTF-8编码方式中前128个字符为ASCII字符，且其编码与ASCII码相同。UTF-16字符编码单位为两个字节。 8-85Oracle数据库系统原理-第8章 SQL查询语句Oracle支持四种unicode字符集AL32UTF8用于ASCII平台。一个字符使用13个字

37、节，增补字符使用4个字节。UTF8用于ASCII平台。一个字符使用13个字节，增补字符使用6个字节。UTFE用于EBCDIC平台。一个字符使用14个字节，增补字符使用8个字节。AL16UTF16使用双字节编码，增补字符使用4个字节。 8-86Oracle数据库系统原理-第8章 SQL查询语句数据库字符集用于存储下面数据CHAR、VARCHAR2、CLOB及 LONG的列值。表名、列名以及PL/SQL程序变量的标识符。SQL语句和PL/SQL程序代码。中文简体Windows系统，默认ZHS16GBK字符集。推荐使用Unicode编码方式优先选用AL32UTF8。某些关键字只能使用单字节字符，AL

38、16UTF16不能用作数据库字符集。 8-87Oracle数据库系统原理-第8章 SQL查询语句国家字符集为什么要使用国家字符集数据库字符集未使用Unicode，数据库中存在不属于数据库字符集中的字符，可以用国家字符集以Unicode编码方式存储这些特殊字符。如何使用国家字符集存储字符把列的类型指定为nchar(n)或nvarchar(n)。如何设置字符集数据库和国家字符集在建库时指定。Oracle国家字符集支持的编码方式AL16UTF16(默认)，UTF8 8-88Oracle数据库系统原理-第8章 SQL查询语句验证数据库字符集和国家字符集SQL select property_name

39、, property_value 2 from database_properties 3 where property_name like %CHARACTERSET 4 /PROPERTY_NAME PROPERTY_VALUE NLS_CHARACTERSET ZHS16GBKNLS_NCHAR_CHARACTERSET AL16UTF16 8-89Oracle数据库系统原理-第8章 SQL查询语句字符串类型-char(n)和varchar2(n)char(n)定长，若实际长度小于n，则用空格填满n的最大值为2000单位默认为字节，也可以用char(n char)或char(n byte

40、)指定单位varchar2(n)可变长度，所占空间与实际长度相同n的最大值为4000单位默认为字节，也可以使用varchar2(n char)或varchar2(n byte)指定单位 8-90Oracle数据库系统原理-第8章 SQL查询语句字符串类型-nchar(n)与nvarchar2(n)nchar(n)n的最大值与国家字符集有关单位为字符占用空间不能超过2000字节nvarchar2(n)n的最大值与国家字符集有关单位为字符占用空间不超过4000字节 8-91Oracle数据库系统原理-第8章 SQL查询语句简单查询只指定表名select * from emp;指定列名select

41、ename,sal from emp使用列别名select ename as emp_name from emp附加条件select ename from emp where sal2000排序select ename from emp order by sal 8-92Oracle数据库系统原理-第8章 SQL查询语句查询条件使用的关系运算符运算符功能=，或!=，=，betweenand大小比较in，exists子查询中是否存在指定值like字符串模糊比较is null，is not null空值判断and，or，not或!逻辑运算符 8-93Oracle数据库系统原理-第8章 SQL查询语

42、句null作为条件正确语法where comm is nullwhere comm is not null错误语法where comm = nullwhere comm != null 8-94Oracle数据库系统原理-第8章 SQL查询语句字符串常量和字符串条件字符串常量用单引号括住-abc字符串区分大小写字符串条件where ename=SMITH字符串模糊查询where ename like %Awhere ename like _A% 8-95Oracle数据库系统原理-第8章 SQL查询语句查询条件包含特殊字符包含通配符where ename like % escape 包含单引号

43、附加单引号，如：where dname like %qtext，如：where dname=qDrs，除了外，、()、! 也可以作为定界符使用 8-96Oracle数据库系统原理-第8章 SQL查询语句字符串函数|，lower/upper，lengthsubstringsubstr(job,1,5)lpad/rpadlpad(ename,10,#), rpad(ename,10,#)replacereplace(job,SALES,MARKETING)trimtrim( abc ) 8-97Oracle数据库系统原理-第8章 SQL查询语句处理日期时间数据sysdate函数获取当前日期时间。t

44、o_date函数或date关键字把字符串常量转换为日期型常量。to_char函数把日期型列值转换为指定格式的字符串。extract函数抽取日期型字段中的指定部分(年、月、日)。 8-98Oracle数据库系统原理-第8章 SQL查询语句日期时间数据处理示例获取当前日期select sysdate from dual;字符串转化为日期值select ename from emp where hiredate=to_date(1981-12-21,yyyy-mm-dd)日期值转化为字符串select ename, to_char(hiredate,yyyy/mm/dd) from emp抽取指定部

45、分select ename from emp where extract(month from hiredate)=5 8-99Oracle数据库系统原理-第8章 SQL查询语句null值的处理nvlnvl函数有两个参数，若第一个参数不为空，则返回第一个参数的值，若第一个参数为null，则返回第二个参数的值。select ename,sal,comm,sal+nvl(comm,0) from emp;nvl2包括三个参数，第一个参数为存在空值的字段名称，第二个参数为字段值不为空时所取的常量值，第三个参数为字段值为空时所取的常量值。select ename,sal,comm,sal+nvl2(c

46、omm,comm,100) from emp; 8-100Oracle数据库系统原理-第8章 SQL查询语句分类汇总常用汇总函数：max，min，count，sum，avgwhere子句：限定参与汇总的行group by子句：指定分组列having子句：过滤分组结果order by子句：指定排序列 8-101Oracle数据库系统原理-第8章 SQL查询语句简单汇总练习查询emp表的最高工资查询emp表的最低工资查询emp表的记录个数查询emp表中部门号20中的记录个数查询emp表中不重复的部门编号查询emp表中20号部门的最高工资 8-102Oracle数据库系统原理-第8章 SQL查询语句

47、使用group by执行分类汇总练习查询emp表中每个部门的sal总和。查询emp表中，每个部门工资超过2000的那些员工的工资总和。 8-103Oracle数据库系统原理-第8章 SQL查询语句使用having子句过滤分组汇总的结果查询工资总额超过7000的部门编号及其工资总额，并以部门编号从小到大排序 8-104Oracle数据库系统原理-第8章 SQL查询语句各子句的处理顺序执行where子句，过滤符合条件的记录。执行group by子句，对以上结果以分组汇总。执行having子句，对分组后的查询结果进行过滤。执行order by子句，对查询结果以指定列排序。执行select子句，显示查

48、询结果。 8-105Oracle数据库系统原理-第8章 SQL查询语句分组汇总的注意事项where子句置于group by之前。不能在where子句中使用分组函数。having子句一般会使用分组函数，若不需使用分组函数，则推荐使用where子句。在语法顺序上，having子句可放置于group by子句之前或之后，推荐放置于group by子句之后。若未被分组函数作用，select、having和order by子句中的列只能选用group by子句中的列。 8-106Oracle数据库系统原理-第8章 SQL查询语句非相关子查询查询emp表中获得最高工资的员工名称。查询emp表中获得工资比平

49、均工资低的员工名称。 8-107Oracle数据库系统原理-第8章 SQL查询语句相关子查询查询每个员工工资与其所在部门的平均工资之差。查询每个部门的最高工资，要求在查询结果中列出部门名称。 8-108Oracle数据库系统原理-第8章 SQL查询语句不同位置的子查询where子句select子句from子句 8-109Oracle数据库系统原理-第8章 SQL查询语句in与not in查询有员工的部门名称select dname from deptwhere deptno in(select distinct deptno from emp)查询无员工的部门名称select dname fr

50、om deptwhere deptno not in(select distinct deptno from emp) 8-110Oracle数据库系统原理-第8章 SQL查询语句若deptno存在空值时，使用not inwhere deptno not in (select deptno from emp)where deptno not in(null,10,20,30)where deptno!=null and deptno!=10 and deptno!=20 and deptno!=30where false and deptno!=10 and deptno!=20 and de

51、ptno!=30where false结论：查询结果为空 8-111Oracle数据库系统原理-第8章 SQL查询语句deptno存在空值时，使用in的情形where deptno in (select deptno from emp) where deptno in(null,10,20,30)where deptno=null or deptno=10 or deptno=20 or deptno=30结论：查询结果不为空 8-112Oracle数据库系统原理-第8章 SQL查询语句exists与not exists查询有员工的部门名称select dname from deptwhere

52、 exists(select deptno from emp where deptno=dept.deptno)查询无员工的部门名称select dname from deptwhere not exists(select deptno from emp where deptno=dept.deptno) 8-113Oracle数据库系统原理-第8章 SQL查询语句多表连接查询当查询的数据存在于多个表时，要用到表的连接。实际工作中，会频繁使用表连接查询。表连接一般分为交叉连接内连接外连接 8-114Oracle数据库系统原理-第8章 SQL查询语句交叉连接-即笛卡尔积SQL select em

53、p.ename, emp.deptno, dept.* 2 from emp, dept 3 /ENAME DEPTNO DEPTNO DNAME LOC SMITH 20 10 ACCOUNTING NEW YORKALLEN 30 10 ACCOUNTING NEW YORKWARD 30 10 ACCOUNTING NEW YORKJONES 20 10 ACCOUNTING NEW YORKMARTIN 30 10 ACCOUNTING NEW YORKBLAKE 30 10 ACCOUNTING NEW YORKCLARK 10 10 ACCOUNTING NEW YORKFORD

54、20 40 OPERATIONS BOSTONMILLER 10 40 OPERATIONS BOSTON已选择56行。 8-115Oracle数据库系统原理-第8章 SQL查询语句交叉连接的意义表S和T分别有rS、rT行，cS、cT列交叉连接的结果为Q表Q表的列由S和T表的列合并得到，共cS+cT个列。Q表的行由S和T表的行两两拼接而成，共rSrT行。 8-116Oracle数据库系统原理-第8章 SQL查询语句内连接对交叉连接附加连接条件过滤出交叉连接结果中有意义的行SQL select emp.ename, emp.deptno, dept.* 2 from emp, dept 3 wh

55、ere emp.deptno=dept.deptno 4 /ENAME DEPTNO DEPTNO DNAME LOC SMITH 20 20 RESEARCH DALLASALLEN 30 30 SALES CHICAGOWARD 30 30 SALES CHICAGO已选择14行。 8-117Oracle数据库系统原理-第8章 SQL查询语句使用表别名SQL select e.ename, e.deptno, d.* 2 from emp e, dept d 3 where emp.deptno=dept.deptno 4 / 8-118Oracle数据库系统原理-第8章 SQL查询语句内

56、连接练习查询每个员工的名称及其部门名称。查询每个员工的名称及其所在地址。查询每个员工的名称及其工资级别。查询每个员工的名称及其经理名称。查询工资超过其经理的员工的名称。 8-119Oracle数据库系统原理-第8章 SQL查询语句两种连接标准：SQL-86与SQL-92SQL-86如前所述交叉连接-SQL-92语法形式select * from emp cross join dept;内连接-SQL-92语法形式select e.ename,d.dname from emp e join dept don e.deptno=d.deptno 8-120Oracle数据库系统原理-第8章 SQL

57、查询语句外连接左外连接除了把符合内连接的数据显示出来以外，也把left outer join左侧表中不满足连接条件的数据显示出来。右外连接除了把符合内连接的数据显示出来，也把right outer join右侧表中不满足连接条件的数据显示出来。全外连接除了把符合内连接的数据显示出来，也把full outer join两侧的表中不满足条件的数据显示出来。 8-121Oracle数据库系统原理-第8章 SQL查询语句外连接语法左外连接select e.ename,d.dnamefrom emp e left outer join dept d on e.deptno=d.deptno右外连接sel

58、ect e.ename,d.dnamefrom emp e right outer join dept d on e.deptno=d.deptno全外连接select e.ename,d.dnamefrom emp e full outer join dept d on e.deptno=d.deptno 8-122Oracle数据库系统原理-第8章 SQL查询语句外连接的简单应用查询有员工的部门名称查询无员工的部门名称 8-123Oracle数据库系统原理-第8章 SQL查询语句构造复杂查询得出平均工资最低的部门的名称及其平均工资所处的级别第1步：查询emp表的每个部门的平均工资。第2步：

59、得到以上三个部门的最低平均工资值。第3步：把上述查询作为子查询放入having子句，然后把此having子句添加到第1步查询命令的末尾，得到其平均工资最低的行。第4步：把以上语句作为子查询放入from子句，与salgrade表、dept表做交叉连接。第5步：分析查询结果，构造连接条件，过滤出有意义的行，第一个连接条件是前两个表的deptno相等。第二个连接条件是最低平均工资值应该在salgrade表的losal和hisal之间。第6步：在select子句中剔除不必要的列，得出查询结果。 8-124Oracle数据库系统原理-第8章 SQL查询语句9数据修改语句DML语句示例deleteSQL

60、delete from emp where deptno=10;UpdateSQL update emp set sal=2000,comm=1200 where deptno=20;InsertSQL insert into dept(deptno,dname) values(50,HR);SQL insert into t 2 select deptno,dname from dept 3 where deptno create table t1 2 ( 3 a int primary key, 4 b int 5 ) 6 /列级约束，指定名称SQL create table t2 2 (