员工培训讲稿-Oracle培训

1、打造中国金融IT服务业第一品牌1宇信易诚员工培训宇信易诚员工培训-ORACLE培训培训打造中国金融IT服务业第一品牌2目目 录录 ORACLE数据库简介 ORACLE SQL ORACLE数据库对象打造中国金融IT服务业第一品牌3Oracle Oracle 体系结构体系结构n 每一个每一个Oracle数据库都是一个数据的集合，这些数据包数据库都是一个数据的集合，这些数据包含在一个或多个文件中。数据库有物理和逻辑两种结构含在一个或多个文件中。数据库有物理和逻辑两种结构。n Oracle实例由被称为系统全局区实例由被称为系统全局区(system global area，SGA)的内存区域和相应的后

2、台进程组成。这些后台进程的内存区域和相应的后台进程组成。这些后台进程负责负责SGA和数据库磁盘文件之间的交互。和数据库磁盘文件之间的交互。Oracle服务器 Oracle 数据库 Oracle 实例打造中国金融IT服务业第一品牌4Oracle Oracle 主要组件主要组件 实 例 内存结构内存结构 后台进程后台进程 PMON SMON DBWR LGWR CKPT 其他 数据库数据库数据文件数据文件数据文件控制文件控制文件日志文件日志文件参数文件归档日志文件归档日志文件口令文件SGA用户进程服务器进程PGA共享池数据缓冲区日志缓冲区打造中国金融IT服务业第一品牌5Oracle Oracle

3、会话会话n 会话是用户与会话是用户与 Oracle 服务器的单个连接服务器的单个连接n 当用户与服务器建立连接时创建会话当用户与服务器建立连接时创建会话n 当用户与服务器断开连接时关闭会话当用户与服务器断开连接时关闭会话启动 Oracle 实例使用SQL*Plus连接至数据库创建用户进程创建服务器进程提交 SQL 查询打造中国金融IT服务业第一品牌6系统全局区系统全局区SGASGAn 数据库信息存储于数据库信息存储于SGA，由多个数据库进程共享，由多个数据库进程共享共享池共享池 数据缓冲区数据缓冲区 日志缓冲区日志缓冲区 SGA的内存结构的内存结构 打造中国金融IT服务业第一品牌7系统全局区系

4、统全局区SGASGAn 共享池共享池n 共享池是对共享池是对SQL、PL/SQL程序进行语法分析、编译、执行的内存区域程序进行语法分析、编译、执行的内存区域n 共享池由库缓存和数据字典缓存组成。共享池由库缓存和数据字典缓存组成。n 共享池的大小直接影响数据库的性能。共享池的大小直接影响数据库的性能。n 数据缓冲区数据缓冲区 n 用于存储从磁盘数据文件中读入的数据，所有用户共享。用于存储从磁盘数据文件中读入的数据，所有用户共享。n 服务器进程将读入的数据保存在数据缓冲区中，当后续的请求需要这些数据时可服务器进程将读入的数据保存在数据缓冲区中，当后续的请求需要这些数据时可以在内存中找到，不需要再从

5、磁盘读取，提高了读取速度。以在内存中找到，不需要再从磁盘读取，提高了读取速度。n 数据缓冲区的大小对数据库的读取速度有直接的影响。数据缓冲区的大小对数据库的读取速度有直接的影响。n 日志缓冲区日志缓冲区 n 日志记录数据库的所有修改信息，日志信息首先产生于日志缓冲区。日志记录数据库的所有修改信息，日志信息首先产生于日志缓冲区。 n 当日志缓冲区的日志数据达到一定数量时，由后台进程将日志数据写入日志文件当日志缓冲区的日志数据达到一定数量时，由后台进程将日志数据写入日志文件中。中。n 相对来说，日志缓冲区对数据库的性能影响较小。相对来说，日志缓冲区对数据库的性能影响较小。打造中国金融IT服务业第一

6、品牌8程序全局区程序全局区PGAPGAn 程序全局区（程序全局区（PGA）包含单个服务器进程所需的数据和）包含单个服务器进程所需的数据和控制信息控制信息n PGA是在用户进程连接到数据库并创建一个会话时自动是在用户进程连接到数据库并创建一个会话时自动分配的，保存每个与分配的，保存每个与Oracle 数据库连接的用户进程所需数据库连接的用户进程所需的信息的信息n PGA为非共享区，只能单个进程使用，当一个用户会话为非共享区，只能单个进程使用，当一个用户会话结束，结束，PGA释放释放打造中国金融IT服务业第一品牌9后台进程后台进程n PMON 进程监控进程进程监控进程n 清理出现故障的进程。清理出

7、现故障的进程。 n 释放所有当前挂起的锁定。释放所有当前挂起的锁定。n 释放故障进程使用的资源。释放故障进程使用的资源。 n SMON 系统监控进程系统监控进程n 在实例失败之后，重新打开数据库时自动恢复实例。在实例失败之后，重新打开数据库时自动恢复实例。 n 整理数据文件的自由空间，将相邻区域结合起来。整理数据文件的自由空间，将相邻区域结合起来。n 释放不再使用的临时段释放不再使用的临时段n DBWR 数据写入进程数据写入进程n 管理数据缓冲区，将最近使用过的块保留在内存中。管理数据缓冲区，将最近使用过的块保留在内存中。n 将修改后的缓冲区数据写入数据文件中。将修改后的缓冲区数据写入数据文件

8、中。n LGWR 日志写入进程日志写入进程n 负责将日志缓冲区中的日志数据写入日志文件。负责将日志缓冲区中的日志数据写入日志文件。 n 系统有多个日志文件，该进程以循环的方式将数据写入文件系统有多个日志文件，该进程以循环的方式将数据写入文件.打造中国金融IT服务业第一品牌10Oracle Oracle 物理组件物理组件n 物理组件就是物理组件就是Oracle数据库所使用的操作系统物理文件数据库所使用的操作系统物理文件。物理文件可分为三类：。物理文件可分为三类：物理组件数据文件 控制文件日志文件数据文件用于存储数据库数据，如表、索引数据等。控制文件是记录数据库物理结构的二进制文件。日志文件记录对

9、数据库的所有修改信息，用于故障恢复打造中国金融IT服务业第一品牌11Oracle Oracle 逻辑组件逻辑组件n 数据库的逻辑结构是从逻辑的角度分析数据库的组成。数据库的逻辑结构是从逻辑的角度分析数据库的组成。Oracle 的逻辑组件包括：的逻辑组件包括：数据库 表空间 段 区 数据块 打造中国金融IT服务业第一品牌12Oracle Oracle 逻辑组件逻辑组件n 表空间是数据库中最大的逻辑单位，一个表空间是数据库中最大的逻辑单位，一个 Oracle 数据库至少包含一数据库至少包含一个表空间，就是名为个表空间，就是名为SYSTEM的系统表空间。的系统表空间。n 每个表空间是由一个或多个数据

10、文件组成的，一个数据文件只能与一每个表空间是由一个或多个数据文件组成的，一个数据文件只能与一个表空间相关联。个表空间相关联。n 表空间的大小等于构成该表空间的所有数据文件大小之和。表空间的大小等于构成该表空间的所有数据文件大小之和。打造中国金融IT服务业第一品牌13Oracle Oracle 逻辑组件逻辑组件n 段段n 段是构成表空间的逻辑存储结构，段由一组区组成。段是构成表空间的逻辑存储结构，段由一组区组成。 n 按照段所存储数据的特征，将段分为四种类型，即数据段、索引段、回退段和临按照段所存储数据的特征，将段分为四种类型，即数据段、索引段、回退段和临时段。时段。n 区区n 区为段分配空间，

11、它由连续的数据块组成。区为段分配空间，它由连续的数据块组成。 n 当段中的所有空间已完全使用时，系统自动为该段分配一个新区。当段中的所有空间已完全使用时，系统自动为该段分配一个新区。n 区不能跨数据文件存在，只能存在于一个数据文件中。区不能跨数据文件存在，只能存在于一个数据文件中。n 数据块数据块n 数据块是数据块是Oracle服务器所能分配、读取或写入的最小存储单元。服务器所能分配、读取或写入的最小存储单元。n Oracle服务器以数据块为单位管理数据文件的存储空间服务器以数据块为单位管理数据文件的存储空间打造中国金融IT服务业第一品牌14Oracle Oracle 网络配置网络配置n Or

12、acle产品安装完成后，服务器和客户端都需要进行网产品安装完成后，服务器和客户端都需要进行网络配置才能实现网络连接。络配置才能实现网络连接。n 服务器端配置监听器，客户端配置网络服务名。服务器端配置监听器，客户端配置网络服务名。tnsnames.oraOracle 客户端客户端listener.oraOracle 服务器服务器打造中国金融IT服务业第一品牌15Oracle Oracle 网络配置网络配置n 服务器端监听器配置信息包括监听协议、地址及其他相关服务器端监听器配置信息包括监听协议、地址及其他相关信息。信息。 配置信息保存在名为配置信息保存在名为listener.ora的文件中。在安的

13、文件中。在安装服务器软件时自动配置一个监听器装服务器软件时自动配置一个监听器n 客户端的网络服务名配置信息包括服务器地址、监听端口客户端的网络服务名配置信息包括服务器地址、监听端口号和数据库号和数据库SID等，与服务器的监听器建立连接。配置信等，与服务器的监听器建立连接。配置信息保存在名为息保存在名为tnsnames.ora的文件中的文件中n Oracle中的中的 Net Configuration Assistant和和Net Manager工具都能用来配置监听器和网络服务名工具都能用来配置监听器和网络服务名打造中国金融IT服务业第一品牌16总结总结n Oracle 服务器由服务器由Orac

14、le 数据库和数据库和 Oracle 实例组成实例组成 n Oracle 实例由系统全局区内存结构和用于管理数据库的实例由系统全局区内存结构和用于管理数据库的后台进程组成后台进程组成n Oracle 数据库由操作系统文件组成，这些文件为数据库数据库由操作系统文件组成，这些文件为数据库信息提供实际物理存储区信息提供实际物理存储区n Oracle 数据库包括逻辑结构和物理结构数据库包括逻辑结构和物理结构打造中国金融IT服务业第一品牌17目录目录 ORACLE数据库简介 ORACLE SQL ORACLE数据库对象打造中国金融IT服务业第一品牌18基本SQL SELECT语句打造中国金融IT服务业第

15、一品牌19 基本SELECTSELECT语句nSELECT标识选择哪些列。标识选择哪些列。nFROM标识从哪个表中选择。标识从哪个表中选择。打造中国金融IT服务业第一品牌20选择全部列打造中国金融IT服务业第一品牌21选择特定的列打造中国金融IT服务业第一品牌22Writing SQL StatementsWriting SQL Statementsn SQL 语言大小写不敏感。n SQL 可以写在一行或者多行n 关键字不能被缩写也不能分行n 各子句一般要分行写。n 使用缩进提高语句的可读性。打造中国金融IT服务业第一品牌23算术运算符n 数字和日期使用的数学表达式。打造中国金融IT服务业第一

16、品牌24使用数学运算符打造中国金融IT服务业第一品牌25操作符优先级n 乘除的优先级高于加减。n 同一优先级运算符从左向右执行。n 括号内的运算先执行。打造中国金融IT服务业第一品牌26操作符优先级打造中国金融IT服务业第一品牌27使用括号打造中国金融IT服务业第一品牌28列的别名n 列的别名:n 重命名一个列。n 便于计算。n 紧跟列名，也可以在列名和别名之间加入关键字AS，以便在别名中包含空格或特殊的字符并区分大小写。打造中国金融IT服务业第一品牌29使用别名打造中国金融IT服务业第一品牌30连接符n 连接符:n 把列与列，列与字符连接在一起。n 用|表示。n 可以用来合成列。打造中国金融

17、IT服务业第一品牌31字符串n 字符串可以是SELECT列表中的一个字符,数字,日期。n 日期和字符只能在单引号中出现。n 每当返回一行时，字符串被输出一次。打造中国金融IT服务业第一品牌32重复行n 默认情况下，查询会返回全部行，包括重复行。打造中国金融IT服务业第一品牌33删除重复行n 在SELECT子句中使用关键字DISTINCT删除重复行打造中国金融IT服务业第一品牌34总结n 通过本课，您应该可以完成: n 书写SELECT语句:返回表中的全部数据。n 返回表中指定列的数据。n 使用别名。打造中国金融IT服务业第一品牌35作业作业1 1n 查询查询employees表列出所有办事员的

18、姓名（表列出所有办事员的姓名（FIRST_NAME+LAST_NAME）、编号（）、编号（EMPLOYEE_ID）和部门）和部门（DEPARTMENT_ID）其中姓名字段需要将）其中姓名字段需要将FIRST_NAME列和列和LAST_NAME进行字符串连接进行字符串连接n 查询查询employees表列出所有办事员的表列出所有办事员的EMPLOYEE_ID， SALARY，其中其中SALARY列需要显示办事员的收入为原始收入乘以列需要显示办事员的收入为原始收入乘以2。打造中国金融IT服务业第一品牌36过滤和排序数据打造中国金融IT服务业第一品牌37在查询中过滤行返回在90号部门工作的所有员工的

19、信息打造中国金融IT服务业第一品牌38过滤过滤n 使用使用WHERE子句，将不满足条件的行过滤掉。子句，将不满足条件的行过滤掉。n WHERE子句紧随子句紧随FROM子句。子句。打造中国金融IT服务业第一品牌39字符和日期字符和日期n 字符和日期要包含在单引号中。字符和日期要包含在单引号中。n 字符大小写敏感，日期格式敏感。字符大小写敏感，日期格式敏感。n 默认的日期格式是默认的日期格式是DD-MON-RR（如（如6/21/1999）。）。打造中国金融IT服务业第一品牌40比较运算比较运算打造中国金融IT服务业第一品牌41其它比较运算其它比较运算打造中国金融IT服务业第一品牌42BETWEEN

20、BETWEEN打造中国金融IT服务业第一品牌43ININ打造中国金融IT服务业第一品牌44LIKELIKEn 使用使用LIKE运算选择类似的值运算选择类似的值n 选择条件可以包含字符或数字选择条件可以包含字符或数字:n %代表一个或多个字符。代表一个或多个字符。n _代表一个字符。代表一个字符。打造中国金融IT服务业第一品牌45LIKELIKEn %和和-可以同时使用。可以同时使用。打造中国金融IT服务业第一品牌46 NULL NULLn 使用使用NULL判断空值。判断空值。打造中国金融IT服务业第一品牌47逻辑运算逻辑运算打造中国金融IT服务业第一品牌48ANDANDn AND要求与的关系为

21、真。要求与的关系为真。打造中国金融IT服务业第一品牌49ORORn OR要求或关系为真。要求或关系为真。打造中国金融IT服务业第一品牌50NOTNOT打造中国金融IT服务业第一品牌51优先级优先级打造中国金融IT服务业第一品牌52优先级优先级打造中国金融IT服务业第一品牌53优先级优先级打造中国金融IT服务业第一品牌54ORDER BYORDER BY子句子句n 使用使用ORDER BY子句排序子句排序n ASC: 升序升序n DESC: 降序降序n ORDER BY子句在子句在SELECT语句的结尾。语句的结尾。打造中国金融IT服务业第一品牌55降序排序降序排序打造中国金融IT服务业第一品牌

22、56按别名排序按别名排序打造中国金融IT服务业第一品牌57多个列排序多个列排序打造中国金融IT服务业第一品牌58总结总结n 通过本课，您应该可以完成通过本课，您应该可以完成: n 使用使用WHERE子句过滤数据子句过滤数据使用比较运算使用比较运算n 使用使用BETWEEN, IN, LIKE和和NULL运算运算n 使用逻辑运算符使用逻辑运算符AND, OR和和NOTn 使用使用ORDER BY子句进行排序。子句进行排序。打造中国金融IT服务业第一品牌59作业作业2 2n 查询查询employees表选择部门（表选择部门（department_id） 为为30中中的雇员的雇员n 显示不带有显示不

23、带有“R”的雇员姓名（的雇员姓名（first_name）.n 显示雇员的详细资料显示雇员的详细资料,按姓名（按姓名（first_name）排序）排序.n 找出不收取佣金（找出不收取佣金（null）或收取的佣金低于）或收取的佣金低于500的雇员的雇员打造中国金融IT服务业第一品牌60单行函数打造中国金融IT服务业第一品牌61SQL SQL 函数函数打造中国金融IT服务业第一品牌62单行函数单行函数n 单行函数单行函数:n 接受函数返回一个结果接受函数返回一个结果n 只对一行进行变换只对一行进行变换n 每行返回一个结果每行返回一个结果n 可以转换数据类型可以转换数据类型n 可以嵌套可以嵌套n 参数

24、可以是一列或一个值参数可以是一列或一个值打造中国金融IT服务业第一品牌63单行函数单行函数打造中国金融IT服务业第一品牌64字符函数字符函数打造中国金融IT服务业第一品牌65大小写控制函数大小写控制函数打造中国金融IT服务业第一品牌66大小写控制函数大小写控制函数打造中国金融IT服务业第一品牌67字符控制函数字符控制函数打造中国金融IT服务业第一品牌68字符控制函数字符控制函数打造中国金融IT服务业第一品牌69数字函数数字函数n ROUND: 四舍五入四舍五入n ROUND(45.926, 2) 45.93n TRUNC:截断截断n TRUNC(45.926, 2) 45.92n MOD: 求

25、余求余n MOD(1600, 300) 100打造中国金融IT服务业第一品牌70MODMOD函数函数打造中国金融IT服务业第一品牌71日期日期n Oracle 内部使用数字存储日期内部使用数字存储日期: 世纪世纪,年年,月月,日日,小时小时,分钟分钟,秒。秒。n 默认的日期格式是默认的日期格式是DD-MON-RR.可以只指定年的后两位可以只指定年的后两位在在20世纪存放世纪存放21世纪的日期。世纪的日期。n 同样可以在同样可以在21世纪存放世纪存放20世纪的日期。世纪的日期。打造中国金融IT服务业第一品牌72日期日期n 函数函数SYSDATE返回返回:n 日期日期n 时间时间n 日期的数学运算

26、：日期的数学运算：n 在日期上加上或减去一个数字结果仍为日期。在日期上加上或减去一个数字结果仍为日期。n 两个日期相减返回日期之间相差的天数。两个日期相减返回日期之间相差的天数。n 可以用数字除可以用数字除24来向日期中加上或减去小时。来向日期中加上或减去小时。打造中国金融IT服务业第一品牌73.日期函数日期函数打造中国金融IT服务业第一品牌74.日期函数日期函数nMONTHS_BETWEEN (01-SEP-95,11-JAN-94)nADD_MONTHS (11-JAN-94,6)nNEXT_DAY (01-SEP-95,FRIDAY) nLAST_DAY(01-FEB-95)19.677

27、419411-JUL-9408-SEP-9528-FEB-95打造中国金融IT服务业第一品牌75.日期函数日期函数nAssume SYSDATE = 25-JUL-95:nROUND(SYSDATE,MONTH) nROUND(SYSDATE,YEAR)nTRUNC(SYSDATE,MONTH) nTRUNC(SYSDATE,YEAR) 01-AUG-9501-JAN-96 01-JUL-95 01-JAN-95 打造中国金融IT服务业第一品牌76.转换函数转换函数打造中国金融IT服务业第一品牌77.隐式数据类型转换隐式数据类型转换nOracle 自动完成下列转换：自动完成下列转换：打造中国金

28、融IT服务业第一品牌78显式数据类型转换显式数据类型转换打造中国金融IT服务业第一品牌79TO_CHARTO_CHAR函数对日期的转换函数对日期的转换n 格式格式:n 必须包含在单引号中而且大小写敏感。必须包含在单引号中而且大小写敏感。n 可以包含任意的有效的日期格式。可以包含任意的有效的日期格式。n 可以使用可以使用fm去掉多余的空格或者前导零。去掉多余的空格或者前导零。n 与日期指用逗号隔开。与日期指用逗号隔开。打造中国金融IT服务业第一品牌80日期格式的元素日期格式的元素打造中国金融IT服务业第一品牌81日期格式的元素日期格式的元素n 时间格式时间格式n HH24:MI:SS AM 15

29、:45:32 PMn 使用双引号向日期中添加字符使用双引号向日期中添加字符n DD of MONTH 12 of OCTOBER打造中国金融IT服务业第一品牌82TO_CHARTO_CHAR函数对日期的转换函数对日期的转换打造中国金融IT服务业第一品牌83TO_CHARTO_CHAR函数对数字的转换函数对数字的转换n 下面是在下面是在TO_CHAR函数中经常使用的几种格式函数中经常使用的几种格式:打造中国金融IT服务业第一品牌84TO_NUMBERTO_NUMBER和和TO_DATETO_DATE函数函数打造中国金融IT服务业第一品牌85嵌套函数嵌套函数n 单行函数可以嵌套。单行函数可以嵌套。

30、n 嵌套函数的执行顺序是由内到外。嵌套函数的执行顺序是由内到外。打造中国金融IT服务业第一品牌86NVLNVL函数函数n 将空值转换成一个已知的值：将空值转换成一个已知的值：n 可以使用的数据类型有日期、字符、数字。可以使用的数据类型有日期、字符、数字。n 函数的一般形式函数的一般形式:n NVL(commission_pct,0)n NVL(hire_date,01-JAN-97)n NVL(job_id,No Job Yet)打造中国金融IT服务业第一品牌87使用使用NVLNVL函数函数打造中国金融IT服务业第一品牌88条件表达式条件表达式n 在在SQL语句中使用语句中使用IF-THEN-

31、ELSE 逻辑。逻辑。n 使用两种方法使用两种方法:n CASE表达式表达式n DECODE函数函数打造中国金融IT服务业第一品牌89CASECASE表达式表达式打造中国金融IT服务业第一品牌90DECODEDECODE函数函数打造中国金融IT服务业第一品牌91总结总结n 通过本章学习，您应该学会通过本章学习，您应该学会: n 使用函数对数据进行计算使用函数对数据进行计算n 使用函数修改数据使用函数修改数据n 使用函数控制一组数据的输出格式使用函数控制一组数据的输出格式n 使用函数改变日期的显示格式使用函数改变日期的显示格式n 使用函数改变数据类型使用函数改变数据类型n 使用使用NVL 函数函

32、数n 使用使用IF-THEN-ELSE 逻辑逻辑打造中国金融IT服务业第一品牌92作业作业3 3n 查询查询emp表找出各月最后一天受雇的所有雇员表找出各月最后一天受雇的所有雇员.n 以年以年-月月-日显示所有雇员的日显示所有雇员的yyyy-mm-ddemp表打造中国金融IT服务业第一品牌93多表查询与分组函数打造中国金融IT服务业第一品牌94多表查询多表查询n 从多个表中获取数据从多个表中获取数据打造中国金融IT服务业第一品牌95笛卡尔集笛卡尔集n 为了避免笛卡尔集，可以在为了避免笛卡尔集，可以在WHERE加入有效的连接条件。加入有效的连接条件。打造中国金融IT服务业第一品牌96Oracle

33、 Oracle 连接连接n 使用连接在多个表中查询数据使用连接在多个表中查询数据n 在在WHERE字句中写入连接条件。字句中写入连接条件。n 在表中有相同列时，在列名之前加上表名前缀。在表中有相同列时，在列名之前加上表名前缀。打造中国金融IT服务业第一品牌97内连接内连接n 内连接只返回满足连接条件的数据内连接只返回满足连接条件的数据打造中国金融IT服务业第一品牌98内连接内连接n 对于多个连接条件使用对于多个连接条件使用AND操作符操作符n 对于区分重复的列名对于区分重复的列名n 使用表名前缀在多个表中区分相同的列。使用表名前缀在多个表中区分相同的列。n 使用表名可以提高效率。使用表名可以提

34、高效率。n 在不同表中具有相同列名的列可以用别名加以区分。在不同表中具有相同列名的列可以用别名加以区分。n 对于多个表可以使用表的别名对于多个表可以使用表的别名n 使用别名可以简化查询。使用别名可以简化查询。n 使用表名前缀可以提高执行效率。使用表名前缀可以提高执行效率。打造中国金融IT服务业第一品牌99外连接外连接n 两个表在连接过程中除了返回满足连接条件的行以外还返两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的行，这种连接称为左（或回左（或右）表中不满足条件的行，这种连接称为左（或右）外联接。右）外联接。n 两个表在连接过程中除了返回满足连接条件的行以外还返

35、两个表在连接过程中除了返回满足连接条件的行以外还返回两个表中不满足条件的行，这种连接称为满外联接。回两个表中不满足条件的行，这种连接称为满外联接。打造中国金融IT服务业第一品牌100左外联接左外联接打造中国金融IT服务业第一品牌101右外联接右外联接打造中国金融IT服务业第一品牌102满外联接满外联接打造中国金融IT服务业第一品牌103增加连接条件增加连接条件打造中国金融IT服务业第一品牌104分组函数分组函数n 分组函数作用于一组数据，并对一组数据返回一个值。分组函数作用于一组数据，并对一组数据返回一个值。打造中国金融IT服务业第一品牌105组函数类型与语法组函数类型与语法n AVG n C

36、OUNT n MAX n MIN n STDDEVn SUM打造中国金融IT服务业第一品牌106AVGAVG（平均值）和（平均值）和SUMSUM（合计）函数（合计）函数n 可以对数值型数据使用可以对数值型数据使用AVG和和SUM函数。函数。打造中国金融IT服务业第一品牌107MINMIN（最小值）和（最小值）和MAXMAX（最大值）函数（最大值）函数n 可以对任意数据类型的数据使用可以对任意数据类型的数据使用MIN和和MAX函数。函数。打造中国金融IT服务业第一品牌108COUNTCOUNT（计数）函数（计数）函数n COUNT(*)返回表中记录总数。返回表中记录总数。打造中国金融IT服务业第

37、一品牌109DISTINCTDISTINCT关键字关键字n COUNT(DISTINCT expr)返回返回expr非空且不重复的记录非空且不重复的记录总数总数打造中国金融IT服务业第一品牌110组函数与空值组函数与空值n 组函数忽略空值。组函数忽略空值。打造中国金融IT服务业第一品牌111在组函数中使用在组函数中使用NVLNVL函数函数n NVL函数使分组函数无法忽略空值。函数使分组函数无法忽略空值。打造中国金融IT服务业第一品牌112分组数据分组数据打造中国金融IT服务业第一品牌113分组数据分组数据: GROUP BY: GROUP BY子句子句n 可以使用可以使用GROUPBY子句将表

38、中的数据分成若干组子句将表中的数据分成若干组n 在在SELECT列表中所有未包含在组函数中的列都应该列表中所有未包含在组函数中的列都应该 包含在包含在GROUP BY子句中。子句中。打造中国金融IT服务业第一品牌114分组数据分组数据: GROUP BY: GROUP BY子句子句n 包含在包含在GROUP BY 子句中的列不必包含在子句中的列不必包含在SELECT列表列表中。中。打造中国金融IT服务业第一品牌115使用多个列分组使用多个列分组打造中国金融IT服务业第一品牌116在在GROUP BYGROUP BY子句中包含多个列子句中包含多个列打造中国金融IT服务业第一品牌117过滤分组过滤

39、分组打造中国金融IT服务业第一品牌118过滤分组：过滤分组：HAVINGHAVING子句子句n 使用使用HAVING过滤分组过滤分组:n 1.行已经被分组。行已经被分组。n 2.使用了组函数。使用了组函数。n 3.满足满足HAVING子句中条件的分组将被显示。子句中条件的分组将被显示。打造中国金融IT服务业第一品牌119嵌套组函数嵌套组函数n 显示平均工资的最大值显示平均工资的最大值打造中国金融IT服务业第一品牌120作业作业n 查询查询emp表求每个部门的总工资表求每个部门的总工资n 求部门的总工资，只显示总工资小于求部门的总工资，只显示总工资小于10000的部门的部门打造中国金融IT服务业

40、第一品牌121子查询和集合打造中国金融IT服务业第一品牌122使用子查询解决问题使用子查询解决问题打造中国金融IT服务业第一品牌123子查询子查询n 子查询子查询(内查询内查询) 在主查询之前一次执行完成。在主查询之前一次执行完成。n 子查询的结果被主查询使用子查询的结果被主查询使用(外查询外查询)。打造中国金融IT服务业第一品牌124子查询注意事项子查询注意事项n 子查询要包含在括号内。子查询要包含在括号内。n 将子查询放在比较条件的右侧。将子查询放在比较条件的右侧。n 除非进行除非进行Top-N 分析，否则不要在子查询中使用分析，否则不要在子查询中使用ORDER BY子句。子句。n 单行操

41、作符对应单行子查询，多行操作符对应多行子查询。单行操作符对应单行子查询，多行操作符对应多行子查询。打造中国金融IT服务业第一品牌125子查询类型子查询类型打造中国金融IT服务业第一品牌126单行子查询单行子查询n 只返回一行。只返回一行。n 使用单行比较操作符。使用单行比较操作符。打造中国金融IT服务业第一品牌127执行单行子查询执行单行子查询打造中国金融IT服务业第一品牌128子查询中的空值问题子查询中的空值问题打造中国金融IT服务业第一品牌129多行子查询多行子查询n 返回多行。返回多行。n 使用多行比较操作符。使用多行比较操作符。打造中国金融IT服务业第一品牌130在多行子查询中使用在多

42、行子查询中使用ANYANY操作符操作符打造中国金融IT服务业第一品牌131在多行子查询中使用在多行子查询中使用ALL ALL 操作符操作符打造中国金融IT服务业第一品牌132集合集合n UNION操作符操作符n UNION操作符返回两个查询的结果集的并集操作符返回两个查询的结果集的并集打造中国金融IT服务业第一品牌133UNIONUNION操作符举例操作符举例打造中国金融IT服务业第一品牌134UNION ALL UNION ALL 操作符操作符n UNION ALL操作符返回两个查询的结果集的并集以及两操作符返回两个查询的结果集的并集以及两个结果集的重复部分（不去重）个结果集的重复部分（不去

43、重）打造中国金融IT服务业第一品牌135UNION ALL UNION ALL 操作符举例操作符举例打造中国金融IT服务业第一品牌136INTERSECT INTERSECT 操作符操作符n INTERSECT 操作符返回两个结果集的交集操作符返回两个结果集的交集打造中国金融IT服务业第一品牌137INTERSECT INTERSECT 操作符举例操作符举例打造中国金融IT服务业第一品牌138MINUS MINUS 操作符操作符n MINUS 操作符返回两个结果集的补集操作符返回两个结果集的补集打造中国金融IT服务业第一品牌139MINUS MINUS 操作符举例操作符举例打造中国金融IT服务

44、业第一品牌140总结总结n 通过本章学习，您已经学会通过本章学习，您已经学会: n 在什么时候遇到什么问题应该使用子查询。在什么时候遇到什么问题应该使用子查询。n 在查询是基于未知的值时应使用子查询。在查询是基于未知的值时应使用子查询。n 使用使用UNION操作符操作符n 使用使用UNION ALL 操作符操作符n 使用使用INTERSECT 操作符操作符n 使用使用MINUS操作符操作符打造中国金融IT服务业第一品牌141作业作业n 查找所有查找所有10部门的经理和部门的经理和 20部门的办事员部门的办事员n 查询工资要大于部门所有人的工资，大于部门的查询工资要大于部门所有人的工资，大于部门

45、的最高工资最高工资打造中国金融IT服务业第一品牌142处理数据打造中国金融IT服务业第一品牌143数据控制语言数据控制语言n DML 可以在下列条件下执行可以在下列条件下执行:n 向表中插入数据向表中插入数据n 修改现存数据修改现存数据n 删除现存数据删除现存数据n 事务是由完成若干项工作的事务是由完成若干项工作的DML语句组成的。语句组成的。打造中国金融IT服务业第一品牌144插入数据插入数据打造中国金融IT服务业第一品牌145插入数据插入数据n 为每一列添加一个新值。为每一列添加一个新值。n 按列的默认顺序列出各个列的值。按列的默认顺序列出各个列的值。n 在在INSERT子句中随意列出列名

46、和他们的值。子句中随意列出列名和他们的值。n 字符和日期型数据应包含在单引号中。字符和日期型数据应包含在单引号中。打造中国金融IT服务业第一品牌146向表中插入空值向表中插入空值n 隐式方式隐式方式: 在列名表中省略该列的值。在列名表中省略该列的值。n 显示方式显示方式:在在VALUES 子句中指定空值。子句中指定空值。打造中国金融IT服务业第一品牌147插入指定的值插入指定的值打造中国金融IT服务业第一品牌148从其它表中拷贝数据从其它表中拷贝数据n 在在INSERT 语句中加入子查询语句中加入子查询n 不必书写不必书写VALUES 子句。子句。n 子查询中的值列表应于子查询中的值列表应于I

47、NSERT子句中的列名对应。子句中的列名对应。打造中国金融IT服务业第一品牌149更新数据更新数据打造中国金融IT服务业第一品牌150更新数据更新数据n 使用使用UPDATE语句更新数据。语句更新数据。n 可以一次更新多条数据。可以一次更新多条数据。n 使用使用WHERE子句指定需要更新的数据。子句指定需要更新的数据。n 如果省略如果省略WHERE子句，则表中的所有数据都将被更新。子句，则表中的所有数据都将被更新。打造中国金融IT服务业第一品牌151在在UPDATEUPDATE语句中使用子查询语句中使用子查询n 更新更新114号员工的工作和工资使其与号员工的工作和工资使其与205号员工相同。号

48、员工相同。打造中国金融IT服务业第一品牌152删除数据删除数据打造中国金融IT服务业第一品牌153删除数据删除数据n 使用使用DELETE语句从表中删除数据。语句从表中删除数据。n 使用使用WHERE子句指定删除的记录。子句指定删除的记录。n 如果省略如果省略WHERE子句，则表中的全部数据将被删除。子句，则表中的全部数据将被删除。打造中国金融IT服务业第一品牌154在在DELETE DELETE 中使用子查询中使用子查询n 在在DELETE 中使用子查询，使删除基于另一个表中的数中使用子查询，使删除基于另一个表中的数据。据。打造中国金融IT服务业第一品牌155数据库事务数据库事务n 数据库事

49、务由以下的部分组成数据库事务由以下的部分组成:n 一个或多个一个或多个DML 语句语句n 一个一个DDL 语句语句n 一个一个DCL 语句语句n 以第一个以第一个DML语句的执行作为开始语句的执行作为开始n 以下面的其中之一作为结束以下面的其中之一作为结束:n COMMIT 或或ROLLBACK语句语句n DDL 或或DCL 语句（自动提交）语句（自动提交）n 用户会话正常结束用户会话正常结束打造中国金融IT服务业第一品牌156COMMITCOMMIT和和ROLLBACKROLLBACK语句的优点语句的优点n 使用使用COMMIT和和ROLLBACK语句语句,我们可以我们可以: n 确保数据完

50、整性。确保数据完整性。n 数据改变被提交之前预览。数据改变被提交之前预览。n 将逻辑上相关的操作分组。将逻辑上相关的操作分组。打造中国金融IT服务业第一品牌157回滚到保留点回滚到保留点n 使用使用SAVEPOINT语句在当前事务中创建保存点。语句在当前事务中创建保存点。n 使用使用ROLLBACK TOSAVEPOINT语句回滚到创建的保存语句回滚到创建的保存点点打造中国金融IT服务业第一品牌158事务进程事务进程n 自动提交在以下情况中执行自动提交在以下情况中执行:n DDL 语句。语句。n DCL 语句。语句。n 会话异常结束或系统异常会导致自动回滚。会话异常结束或系统异常会导致自动回滚

51、。打造中国金融IT服务业第一品牌159提交或回滚前的数据状态提交或回滚前的数据状态n 改变前的数据状态是可以恢复的改变前的数据状态是可以恢复的n 执行执行DML 操作的用户可以通过操作的用户可以通过SELECT语句查询之前的语句查询之前的修正修正n 其他用户不能看到当前用户所做的改变，直到当前用户结其他用户不能看到当前用户所做的改变，直到当前用户结束事务。束事务。n DML语句所涉及到的行被锁定，其他用户不能操作。语句所涉及到的行被锁定，其他用户不能操作。打造中国金融IT服务业第一品牌160提交后的数据状态提交后的数据状态n 数据的改变已经被保存到数据库中。数据的改变已经被保存到数据库中。n

52、改变前的数据已经丢失。改变前的数据已经丢失。n 所有用户可以看到结果。所有用户可以看到结果。n 锁被释放，其他用户可以操作涉及到的数据。锁被释放，其他用户可以操作涉及到的数据。n 所有保存点被释放。所有保存点被释放。打造中国金融IT服务业第一品牌161提交数据提交数据n 改变数据改变数据n 提交改变提交改变打造中国金融IT服务业第一品牌162数据回滚后的状态数据回滚后的状态n 使用使用ROLLBACK语句可使数据变化失效语句可使数据变化失效:n 数据改变被取消。数据改变被取消。n 修改前的数据状态可以被恢复。修改前的数据状态可以被恢复。n 锁被释放。锁被释放。打造中国金融IT服务业第一品牌16

53、3读一致性读一致性n 读一致性为数据提供一个一致的视图。读一致性为数据提供一个一致的视图。n 一个用户的对数据的改变不会影响其他用户的改变。一个用户的对数据的改变不会影响其他用户的改变。n 对于相同的数据读一致性保证对于相同的数据读一致性保证:n 查询不等待修改。查询不等待修改。n 修改不等待查询。修改不等待查询。打造中国金融IT服务业第一品牌164总结总结n 通过本章学习通过本章学习, 您应学会如何使用您应学会如何使用DML语句改变数据和事语句改变数据和事务控制务控制打造中国金融IT服务业第一品牌165作业作业n 为表为表DEPT80插入一行数据插入一行数据n Employee_id:158

54、7n Name :Johnn Email:n Hire_date:sysdaten Job_id:AD_PRESn更新更新DEPT80表，将表，将lindsey的工资和岗位修改为与的工资和岗位修改为与Ellen完全相同完全相同n删除雇员删除雇员lindsey打造中国金融IT服务业第一品牌166目录目录 ORACLE数据库简介 ORACLE SQL ORACLE数据库对象打造中国金融IT服务业第一品牌167常见的数据库对象常见的数据库对象打造中国金融IT服务业第一品牌168表打造中国金融IT服务业第一品牌169创建表创建表打造中国金融IT服务业第一品牌170命名规则命名规则n 表名和列名表名和列

55、名:n 必须以字母开头必须以字母开头n 必须在必须在130 个字符之间个字符之间n 必须只能包含必须只能包含AZ, az, 09, _, $, 和和#n 必须不能和用户定义的其他对象重名必须不能和用户定义的其他对象重名n 必须不能是必须不能是Oracle 的保留字的保留字打造中国金融IT服务业第一品牌171Oracle Oracle 数据库中的表数据库中的表n 用户定义的表用户定义的表:n 用户自己创建并维护的一组表用户自己创建并维护的一组表n 包含了用户所需的信息包含了用户所需的信息n 数据字典数据字典:n 由由Oracle Server自动创建的一组表自动创建的一组表n 包含数据库信息包含

56、数据库信息打造中国金融IT服务业第一品牌172查询数据字典查询数据字典打造中国金融IT服务业第一品牌173数据类型数据类型打造中国金融IT服务业第一品牌174使用子查询创建表使用子查询创建表打造中国金融IT服务业第一品牌175ALTER TABLEALTER TABLE语句语句n 使用使用ALTERTABLE语句可以语句可以:n 追加新的列追加新的列n 修改现有的列修改现有的列n 为新追加的列定义默认值为新追加的列定义默认值n 删除一个列删除一个列打造中国金融IT服务业第一品牌176追加一个新列追加一个新列打造中国金融IT服务业第一品牌177修改一个列修改一个列打造中国金融IT服务业第一品牌1

57、78删除一个列删除一个列打造中国金融IT服务业第一品牌179删除表删除表n 数据和结构都被删除数据和结构都被删除n 所有正在运行的相关事物被提交所有正在运行的相关事物被提交n 所有相关索引被删除所有相关索引被删除n DROP TABLE语句不能回滚语句不能回滚打造中国金融IT服务业第一品牌180改变对象的名称改变对象的名称n 执行执行RENAME语句改变表语句改变表, 视图视图, 序列序列, 或同义词的名称或同义词的名称n 必须是对象的拥有者必须是对象的拥有者打造中国金融IT服务业第一品牌181清空表清空表n TRUNCATE TABLE语句语句:n 删除表中所有的数据删除表中所有的数据n 释

58、放表的存储空间释放表的存储空间n TRUNCATE语句不能回滚语句不能回滚n 可以使用可以使用DELETE语句删除数据语句删除数据打造中国金融IT服务业第一品牌182约束约束n 约束是表级的强制规定约束是表级的强制规定n 约束放置在表中删除有关联关系的数据约束放置在表中删除有关联关系的数据n 有以下五种约束有以下五种约束:n NOT NULLn UNIQUE n PRIMARY KEYn FOREIGN KEYn CHECK打造中国金融IT服务业第一品牌183NOT NULLNOT NULL约束约束打造中国金融IT服务业第一品牌184UNIQUEUNIQUE约束约束打造中国金融IT服务业第一品

59、牌185UNIQUEUNIQUE约束约束打造中国金融IT服务业第一品牌186PRIMARY KEYPRIMARY KEY约束约束打造中国金融IT服务业第一品牌187PRIMARY KEYPRIMARY KEY约束约束打造中国金融IT服务业第一品牌188FOREIGN KEYFOREIGN KEY约束约束打造中国金融IT服务业第一品牌189FOREIGN KEYFOREIGN KEY约束约束打造中国金融IT服务业第一品牌190CHECKCHECK约束约束n 定义每一行必须满足的条件定义每一行必须满足的条件n 以下的表达式是不允许的以下的表达式是不允许的:n 出现出现CURRVAL, NEXTVA

60、L, LEVEL, 和和ROWNUM伪列伪列n 使用使用SYSDATE, UID, USER, 和和USERENV函数函数n 在查询中涉及到其它列的值在查询中涉及到其它列的值打造中国金融IT服务业第一品牌191添加与删除约束添加与删除约束打造中国金融IT服务业第一品牌192总结总结n 通过本章学习应该掌握通过本章学习应该掌握n 如何建立一个表如何建立一个表n 如何给表的列上增加主键，外键等约束如何给表的列上增加主键，外键等约束n 修改和删除表修改和删除表打造中国金融IT服务业第一品牌193作业作业n 建立建立author表表n 列名：列名：id number(3)n Name:varchar2