《Oracle数据库经典学习教程》pdf电子书免费下载
下载方式一：
百度网盘下载地址：https://pan.baidu.com/s/13Rfnsxx-RHYNsuJ1n115eQ
百度网盘密码：1111
下载方式二：
http://ziliaoshare.cn/Download/ae_123597_do_OracleSJKJDXXJC.zip
|
|
作者：empty 出版社：empty |
《Oracle数据库经典学习教程》介绍
目录
走进Oracle2
1.Oracle简介3
2.Oracle安装4
3.Oracle客户端工具9
4.Oracle服务16
5.Oracle启动和关闭17
6.Oracle用户和权限18
7.本章总结21
8.本章练习22
SQL数据操作和查询25
1.SQL简介26
2.Oracle数据类型26
3.创建表和约束27
4.数据操纵语言（DML）30
5.操作符35
6.高级查询36
7.本章总结44
8.本章练习45
子查询和常用函数48
1.子查询49
2.Oracle中的伪列51
3.Oracle函数54
4.本章总结62
5.本章练习63
表空间、数据库对象66
1.Oracle数据库对象67
2.同义词67
3.序列70
4.视图72
5.索引74
6.表空间75
7.本章总结79
8.本章练习80
PL/SQL程序设计83
1.PL/SQL简介84
2.PL/SQL块85
3.PL/SQL数据类型89
4.PL/SQL条件控制和循环控制91
5.PL/SQL中动态执行SQL语句101
6.PL/SQL的异常处理102
7.本章总结108
8.本章练习109
Oracle应用于.Net平台111
1.回顾ADO.NET112
2.使用ADO.NET连接Oracle113
3.抽象工厂中加入Oracle117
4.本章总结121
5.本章练习122
数据库导入导出124
1.Oracle导入导出125
2.EXP导出数据125
3.IMP导入128
4.常见问题128
第1章
走进Oracle
主要内容
Oracle安装
Oracle创建用户和角色
客户端链接Oracle服务器
1.Oracle简介
在第一学期我们已经接触过关系型数据库SQL Server，对数据库、表、记录、表的增删改查操作等这些基本的概念已经了解。Oracle是基于对象的关系型数据库，Oracle也是用表的形式对数据存储和管理，并且在Oracle的操作中添加了一些面向对象的思想。
Oracle数据库是Oracle（中文名称叫甲骨文）公司的核心产品，Oracle数据库是一个适合于大中型企业的数据库管理系统。在所有的数据库管理系统中（比如：微软的SQL Server，IBM的DB2等），Oracle的主要用户涉及面非常广，包括：银行、电信、移动通信、航空、保险、金融、电子商务和跨国公司等。Oracle产品是免费的，可以在Oracle官方网站上下载到安装包，另一方面Oracle服务是收费的。
Oracle公司成立以来，从最初的数据库版本到Oracle7、Oracle8i、Oracle9i，Oracle10g到Oracle11g，虽然每一个版本之间的操作都存在一定的差别，但是Oracle对数据的操作基本上都遵循SQL标准。因此对Oracle开发来说版本之间的差别不大。
很多人没有学习Oracle就开始发怵，因为人们在误解Oracle，认为Oracle太难学了，认为Oracle不是一般人用的数据库，其实任何数据库对应用程序研发人员来说，都是大同小异，因为目前多数数据库都支持标准的SQL。在Oracle这本书中，我们能学习到：
Oracle的安装
Oracle数据管理
常用子查询及常用函数
PL/SQL编程
Oracle基本管理
由于在第一学期已经接触了SQL Server，Oracle数据库的概念不是很难，主要是实践，因此在本书的学习中，认真的完成上机练习是学习好本书的关键。
接下来我们先从Oracle安装开始，接触一些Oracle中基本的概念。
2.Oracle安装
Oracle数据库产品是免费的，我们可以从Oracle的官方网站(http://www.oracle.com)下载到程序安装包，Oracle在Windows下的安装非常方便，安装开始后，一直点击安装程序的“下一步”即可。
1.下载Oracle10g后，解压到一个文件夹下，单击“setup.exe”文件即可启动安装界面。如下图：
图1 Oracle安装启动界面
Oracle主目录位置就是Oracle准备安装的位置，称为“Oracle_Home”，一般Oracle根据当前计算机的硬盘大小默认给出一个合适的位置。Oracle安装时可以只安装Oracle软件，然后单独创建数据库，也可以在上图中选中“创建启动数据库”复选框，在安装Oracle产品时，同时创建一个数据库，对初学者来说，推荐这样安装。填写全局数据库名，以及管理员的密码。全局数据库名是数据库在服务器网络中的唯一标识。
2.点击“下一步”，就会出现如下图内容，开始对Oracle服务器进行环境检查，主要查看服务器是否符合Oracle安装的条件，比如操作系统是否支持、系统内存是否符合Oracle安装的最低要求等。
图2 Oracle安装前环境检查
3.Oracle检查通过后，单击“下一步”，就会列出所有安装Oracle过程中的默认选项。
图3 Oracle默认安装设置
4.单击“安装”按钮，进入安装界面，这一过程经历时间比较长，根据计算机的性能不同有很大差别。
图4 Oracle安装
5.上图完成后，进入了各种Oracle工具的安装阶段，包括网络配置向导，iSQL*plus等（后面课程中讲解）。如下图所示：
图5 Oracle各种工具的安装
6.接下来自动启动DBCA（Database Configuration Assistant）进入创建默认数据库阶段。
图6 DBCA下安装数据库
Oracle中的数据库主要是指存放数据的文件，这些文件在Oracle安装完成后，在计算机硬盘上都能找到，包括数据文件、控制文件和数据库日志文件。
数据库创建后会有一系列为该数据库提供服务的内存空间和后台进程，称为该数据库的实例。每一个数据库至少会有一个实例为其服务。实例中的内存结构称为系统全局区（SGA），系统会根据当前计算机系统的性能给SGA分配非常可观的内存空间。
Oracle创建数据库不能像SQL Server那样用一个简单的CREATE DATABASE命令就能完成，在创建数据库的过程中还需要配置各种参数。虽然有DBCA工具向导，但是仍然需要进行比较麻烦的配置。
虽然一个Oracle数据库服务器中可以安装多个数据库，但是一个数据库需要占用非常大的内存空间，因此一般一个服务器只安装一个数据库。每一个数据库可以有很多用户，不同的用户拥有自己的数据库对象（比如：数据库表），一个用户如果访问其他用户的数据库对象，必须由对方用户授予一定的权限。不同的用户创建的表，只能被当前用户访问。因此在Oracle开发中，不同的应用程序只需使用不同的用户访问即可。
7.数据库创建完毕后，需要设置数据库的默认用户。Oracle中为管理员预置了两个用户分别是SYS和SYSTEM。同时Oracle为程序测试提供了一个普通用户scott，口令管理中，可以对数据库用户设置密码，设置是否锁定。 Oracle客户端使用用户名和密码登录Oracle系统后才能对数据库操作。
图7 DBCA下的口令管理
图8 为system,sys,scott用户设置密码
默认的用户中，SYS和SYSTEM用户是没有锁定的，安装成功后可以直接使用，SCOTT用户默认为锁定状态，因此不能直接使用，需要把SCOTT用户设定为非锁定状态才能正常使用。
这一步完成后，Oracle系统安装成功。
Oracle数据库中，默认情况下，所有系统的数据，SQL关键字等都是大写的，在操作过程中，Oracle会自动把这些内容转换为大写，因此用户操作时不需考虑大小写问题，一般情况下，为了良好的程序风格，程序中建议关键字用大写，非关键字可以使用小写。
3.Oracle客户端工具
Oracle服务器安装成功后，就可以通过客户端工具连接Oracle服务器了，可以到Oracle官方下载Oracle专用的客户端软件，大多客户端工具都是基于Oracle客户端软件的。接下来介绍几种常用的Oracle客户端工具。
SQL*Plus工具
该工具是Oracle系统默认安装下，自带的一个客户端工具。在Windows命令行中输入“sqlplusw”命令，就能够启动该工具了。
图9 SQL*Plus工具
输入用户名和密码后，如果SQL*Plus与数据库服务器在同一台计算机上，并且当前服务器下只有一个数据库实例，那么“主机字符串”可以不用填写。
SQL*Plus工具虽然是Oracle自带的工具，但是在现实开发中，基于该环境对开发不方便，因此很少使用。
SQL*Plus连接成功后就如图所示：
图10 SQL*Plus工具登录后
SQL*Plus命令行工具
该命令行工具，提供了与数据库交互的能力和维护数据库的能力，包括了Oracle自带的SQL*Plus工具的全部功能，在Oracle管理中经常使用。在命令行中输入：“sqlplus /nolog”即可启动该工具。如下图：
图11 启动SQL*Plus命令行工具
输入“sqlplus /nolog”命令后，只是启动了一个客户端进程，并没有与服务器连接，连接到Oracle服务器的命令是：
conn 用户名/密码 as 连接身份@服务器连接字符串
说明：
1.连接身份：表示该用户连接后拥有的权限。
sysdba： 即数据库管理员，权限包括：打开数据库服务器、关闭数据库服务器、备份数据库、恢复数据库、日志归档、会话限制、管理功能、创建数据库。sys用户必须用sysdba身份才能登录，system用户可以用普通身份登录。
sysyoper：即数据库操作员，权限包括：打开数据库服务器、关闭数据库服务器、备份数据库、恢复数据库、日志归档、会话限制。
normal：即普通用户，权限只有查询某些数据表的数据。默认的身份是normal用户。
2.客户端工具可以根据“服务器连接字符串”对服务器进行连接，有了连接字符串后客户端就可以像操作本机一样操作远程数据库，因此“服务器连接字符串”的配置也叫本地网络服务配置，如果SQL*Plus工具启动在服务器上，并且服务器上只有一个数据库实例的情况下，连接字符串可以缺省，在连接字符串中包括连接服务器的协议，服务器的地址，服务器的端口等设置，Oracle服务名等，该配置文件在Oracle安装目录下的： network/ADMIN/ tnsnames.ora。该文件是一个文本文件，用记事本打开后如下所示：
图12 服务器连接字符串配置
配置本地网络服务名
本地网络服务名，即客户端与服务器的连接字符串，本地网络服务名是客户端的配置，Oracle客户端安装后，可以使用客户端自带的网络配置向导（Net Configuration Assistant）进行配置：
1.启动Net Configuration Assistant。选择“本地Net服务名配置”选项。如下图所示：
图13 启动Net Configuration Assistant
2.选择“下一步”，本步骤可以对本地网络服务名进行添加，删除，测试是否正常连接等操作，选择“添加”选项。
图14 Net Configuration Assistant
3.点击“下一步”，填写服务名，该服务名就是Oracle安装时（图1），为数据库取的全局数据库名。
图15 服务名配置
4.点击“下一步”，选择服务需要的协议，默认是TCP协议。推荐使用默认的TCP协议。
图16 选择协议
5.点击“下一步”，输入主机名，主机名可以是计算机名称，也可以是一个IP地址，主机如果是本机，可以使用本机计算机名称、“localhost”、“127.0.0.1”、或者本机的IP地址。
图17 输入主机名和端口
6.单击“下一步”，选择“是，进行测试”选项。进入下图界面。
图18 测试成功
在测试时，默认采用的用户名和密码是system/manager进行测试，如果用户system的密码不是“manager”，有可能测试通不过，更改登录后，输入正确的用户名和密码后再进行测试即可。
7.测试成功后，单击“下一步”，出现如下界面，这一步是为本地网络服务命名，即图12中的服务器连接字符串名。
图19 为网络服务名命名
点击“下一步”，配置就完成了，进入tnsnames.ora文件中查看，就出现了如图12中的内容。
PL/SQL Developer工具
在实际Oracle开发中，经常使用一个功能强大的第三方工具：“PL/SQL Developer”工具。PL/SQL Developer基本上可以实现Oracle开发中的任何操作。它运行在客户端时必须先安装Oracle客户端，并且通过网络配置向导配置网络服务名后才能正常与服务器连接。
图20 PL/SQL Developer
4.Oracle服务
Oracle在windows中安装完成后，会安装很多服务，下面介绍几个主要的服务。
图21 Oracle服务
OracleService+服务名，该服务是数据库启动的基础，只有该服务启动了，Oracle数据库才能正常启动。这是必须启动的服务。
OracleOraDb10g_home1TNSListener，该服务是服务器端为客户端提供的监听服务，只有该服务在服务器上正常启动，客户端才能连接到服务器。该监听服务接收客户端发出的请求，然后将请求传递给数据库服务器。一旦建立了连接，客户端和数据库服务器就能直接通信了。
OracleOraDb10g_home1iSQL*Plus，该服务提供了用浏览器对数据库中数据操作的方式。该服务启动后，就可以使用浏览器进行远程登录并进行数据库操作了。如下图所示：
图22 iSQL*Plus
OracleDBConsole+服务名，Oracle10g中的一个新服务。在Oracle9i之前，Oracle官方提供了一个基于图形界面的企业管理器（EM），从Oracle10g开始，Oracle提供了一个基于B/S的企业管理器，在操作系统的命令行中输入命令：emctl start dbconsole，就可以启动OracleDbConsole服务，如下图所示：
图23 EM服务的启动
服务启动之后，就可以在浏览器中输入上图中进入EM的地址，使用B/S方式管理Oracle服务器。
5.Oracle启动和关闭
OracleService启动动后，就可以对数据库进行管理了，Oracle的启动和关闭是最基本的命令，在SQL*Plus中，启动Oracle必须是sys用户，命令格式是：
startup open
图24 Oracle服务启动
Oracle服务关闭用命令：shutdown immediate
图25 Oracle服务关闭
6.Oracle用户和权限
Oracle中，一般不会轻易在一个服务器上创建多个数据库，在一个数据库中，不同的项目由不同的用户访问，每一个用户拥有自身创建的数据库对象，因此用户的概念在Oracle中非常重要。Oracle的用户可以用CREATE USER命令来创建。其语法是：
语法结构：创建用户
CREATE USER 用户名 IDENTIFIED BY 口令 [ACCOUNT LOCK|UNLOCK]
语法解析：
LOCK|UNLOCK创建用户时是否锁定，默认为锁定状态。锁定的用户无法正常的登录进行数据库操作。
代码演示：创建用户
SQL> CREATE USER jerry
2 IDENTIFIED BY tom
3 ACCOUNT UNLOCK;
Oracle在SQL*Plus中的命令以分号（；）结尾，代表命令完毕并执行，系统同时会把该命令保存在缓存中，缓存中只保存最近执行过的命令，如果重新执行缓存中的命令，直接使用左斜杠符号（/）。如果命令不以分号结尾，该命令只是写入缓存保存起来，但并不执行。
尽管用户成功创建，但是还不能正常的登录Oracle数据库系统，因为该用户还没有任何权限。如果用户能够正常登录，至少需要CREATE SESSION系统权限。
Oracle用户对数据库管理或对象操作的权利，分为系统权限和数据库对象权限。系统权限比如：CREATE SESSION，CREATE TABLE等，拥有系统权限的用户，允许拥有相应的系统操作。数据库对象权限，比如对表中的数据进行增删改操作等，拥有数据库对象权限的用户可以对所拥有的对象进行对应的操作。
还有一个概念就是数据库角色（role），数据库角色就是若干个系统权限的集合。下面介绍几个常用角色：
CONNECT角色，主要应用在临时用户，特别是那些不需要建表的用户，通常只赋予他们CONNECT role。CONNECT是使用Oracle的简单权限，拥有CONNECT角色的用户，可以与服务器建立连接会话（session，客户端对服务器连接，称为会话）。
RESOURCE角色，更可靠和正式的数据库用户可以授予RESOURCE role。RESOURCE提供给用户另外的权限以创建他们自己的表、序列、过程（procedure）、触发器（trigger）、索引（index）等。
DBA角色，DBA role拥有所有的系统权限----包括无限制的空间限额和给其他用户授予各种权限的能力。用户SYSTEM拥有DBA角色。
一般情况下，一个普通的用户（如SCOTT），拥有CONNECT和RESOURCE两个角色即可进行常规的数据库开发工作。
可以把某个权限授予某个角色，可以把权限、角色授予某个用户。系统权限只能由DBA用户授权，对象权限由拥有该对象的用户授权，授权语法是：
语法结构：授权
GRANT角色|权限 TO 用户（角色）
代码演示：授权
SQL> GRANT CONNECT TO jerry;
授权成功。
SQL> GRANT RESOURCE TO jerry;
授权成功。
SQL>
语法结构：其他操作
//回收权限
REVOKE 角色|权限 FROM 用户（角色）
//修改用户的密码
ALTER USER 用户名 IDENTIFIED BY 新密码
//修改用户处于锁定（非锁定）状态
ALTER USER 用户名 ACCOUNT LOCK|UNLOCK
7.本章总结
Oracle是基于对象的关系型数据库，Oracle产品免费，服务收费。
Oracle安装后默认会有两个管理员用户（system，sys）和一个普通用户Scott。
Sql*plus是Oracle管理和数据操作的客户端工具。
客户端链接服务器前，服务器要启动监听服务，并且客户端工具要安装Oracle客户端，并且在客户端要建立本地网络服务名。
Oracle服务和监听启动后才能对数据库进行操作。
用startup命令启动数据库，用shutdown命令关闭数据库。
Oracle的角色包括了一系列系统权限和普通对象权限，可以把权限授权给角色，把权限或者角色授权给用户。
8.本章练习
1.描述Oracle安装过程中的关键点。
2.描述创建本地网络服务名的步骤。
3.描述Oracle主要服务的作用。
4.Oracle使用什么命令才能启动和关闭。
5.什么是Oracle权限和角色？他们的关系是什么？
6.创建一个用户，并授权CONNECT和RESOURCE。
章节知识结构图
第2章
SQL数据操作和查询
主要内容
Oracle数据类型
SQL建表和约束
SQL对数据增删改
SQL查询
Oracle伪列
1.SQL简介
在第一学期的SQL Server学习中，已经知道，SQL是结构化查询语言（Structured Query Language），专门用于数据存取、数据更新及数据库管理等操作。并且已经学习了用SQL语句对数据库的表进行增删改查的操作。
在Oracle开发中，客户端把SQL语句发送给服务器，服务器对SQL语句进行编译、执行，把执行的结果返回给客户端。Oracle SQL语句由如下命令组成：
数据定义语言（DDL），包括CREATE（创建）命令、ALTER（修改）命令、DROP（删除）命令等。
数据操纵语言（DML），包括INSERT（插入）命令、UPDATE（更新）命令、DELETE（删除）命令、SELECT … FOR UPDATE（查询）等。
数据查询语言（DQL），包括基本查询语句、Order By子句、Group By子句等。
事务控制语言（TCL），包括COMMIT（提交）命令、SAVEPOINT（保存点）命令、ROLLBACK（回滚）命令。
数据控制语言（DCL），GRANT（授权）命令、REVOKE（撤销）命令。
目前主流的数据库产品（比如：SQL Server、Oracle）都支持标准的SQL语句。数据定义语言，表的增删改操作，数据的简单查询，事务的提交和回滚，权限的授权和撤销等，Oracle与SQL Server在操作上基本一致。
2.Oracle数据类型
Oracle数据库的核心是表，表中的列使用到的常见数据类型如下：
类型含义
CHAR(length)存储固定长度的字符串。参数length指定了长度，如果存储的字符串长度小于length，用空格填充。默认长度是1，最长不超过2000字节。
VARCHAR2(length)存储可变长度的字符串。length指定了该字符串的最大长度。默认长度是1，最长不超过4000字符。
NUMBER(p，s)既可以存储浮点数，也可以存储整数，p表示数字的最大位数（如果是小数包括整数部分和小数部分和小数点，p默认是38为），s是指小数位数。
DATE存储日期和时间，存储纪元、4位年、月、日、时、分、秒，存储时间从公元前4712年1月1日到公元后4712年12月31日。
TIMESTAMP不但存储日期的年月日，时分秒，以及秒后6位，同时包含时区。
CLOB存储大的文本，比如存储非结构化的XML文档
BLOB存储二进制对象，如图形、视频、声音等。
表1 Oracle的部分数据类型
对应NUMBER类型的示例：
格式输入的数字实际的存储
NUMBER1234.5671234.567
NUMBER（6，2）123.4567123.46
NUMBER（4，2）12345.67输入的数字超过了所指定的精度，数据库不能存储
表2 Number示例
对于日期类型，可以使用sysdate内置函数可以获取当前的系统日期和时间，返回DATE类型，用systimestamp函数可以返回当前日期、时间和时区。
图1 sysdate和sysTimestamp
Oracle的查询中，必须使用“select 列… from 表”的完整语法，当查询单行函数的时候，from后面使用DUAL表，dual表在系统中只有一行一列，该表在输出单行函数时为了select…from的语法完整性而使用。
3.创建表和约束
Oracle创建表同SQL Server一样，使用CREATE TABLE命令来完成。创建约束则使用如下命令：
语法格式：ALTER TABLE命令
ALTER TABLE 表名 ADD CONSTRAINT 约束名 约束内容。
不论创建表还是约束，与SQL Server基本相同，注意：在Oracle中default是一个值，而SQL Server中default是一个约束，因此Oracle的default设置可以在建表的时候创建。
案例1：创建一个学生信息（INFOS）表和约束
代码演示：Oracle创建表和约束
CREATE TABLE INFOS
(
STUID VARCHAR2(7) NOT NULL, --学号 学号=‘S’+班号+2位序号
STUNAME VARCHAR2(10) NOT NULL, --姓名
GENDER VARCHAR2(2) NOT NULL, --性别
AGE NUMBER(2) NOT NULL, --年龄
SEAT NUMBER(2) NOT NULL, --座号
ENROLLDATE DATE, --入学时间
STUADDRESS VARCHAR2(50) DEFAULT '地址不详', --住址
CLASSNO VARCHAR2(4) NOT NULL --班号 班号=学期序号+班级序号
)
/ ①
ALTER TABLE INFOS ADD CONSTRAINT PK_INFOS PRIMARY KEY(STUID) ②
/
ALTER TABLE INFOS ADD CONSTRAINT CK_INFOS_GENDER
CHECK(GENDER = '男' OR GENDER = '女') ③
/
ALTER TABLE INFOS ADD CONSTRAINT CK_INFOS_SEAT
CHECK(SEAT >=0 AND SEAT =50) ④
/
ALTER TABLE INFOS ADD CONSTRAINT CK_INFOS_AGE
CHECK(AGE >=0 AND AGE =100) ⑤
/
ALTER TABLE INFOS ADD CONSTRAINT CK_INFOS_CLASSNO
CHECK((CLASSNO >='1001' AND CLASSNO ='1999') OR
(CLASSNO >='2001' AND CLASSNO ='2999')) ⑥
/
ALTER TABLE INFOS ADD CONSTRAINTS UN_STUNAME UNIQUE(STUNAME) ⑦
/
代码解析：
①在Oracle代码中，“/”执行缓存区中的语句，由于缓冲区中只存储一条刚刚保存过语句，由于每条语句没有用分号结尾，只是保存在缓冲区，因此每条语句后面都有单独一行“/”。
②创建一个主键约束。
③与 ④ ⑤ ⑥ ⑦一起创建各种check约束。其中⑦是唯一约束，表示该列值是唯一的，列中的值不能重复。
Oracle中创建外键约束与SQL Server相同。比如：现有成绩表定义如下：
案例2：创建一个成绩表（SCORES）表和约束
代码演示：Oracle创建表和约束
CREATE TABLE SCORES
(
ID NUMBER , --ID ①
TERM VARCHAR2(2),--学期 S1或S2
STUID VARCHAR2(7) NOT NULL,--学号
EXAMNO VARCHAR2(7) NOT NULL,--考号 E+班号+序号
WRITTENSCORE NUMBER(4,1) NOT NULL,--笔试成绩
LABSCORE NUMBER(4,1) NOT NULL--机试成绩
)
ALTER TABLE SCORES
ADD CONSTRAINT CK_SCORES_TERM CHECK(TERM = 'S1' OR TERM ='S2')
/
ALTER TABLE SCORES
ADD CONSTRAINT FK_SCORES_INFOS_STUID FOREIGN KEY(STUID) REFERENCES INFOS(STUID) ②
/
代码解析：
①SQL Server中可以使用identify创建自动增长列，但是Oracle中的自动增长需要借助序列（Sequence）完成，在后面章节中讲解。
②Oracle中的外键约束定义。
4.数据操纵语言（DML）
数据操纵语言（DML）用于对数据库的表中数据进行添加、修改、删除和SELECT…For UPDATE(后面专门学习该查询)操作。对比一期学习过的SQL Server操作，接下来一一介绍在Oracle中的操作。
简单查询
数据查询是用SELECT命令从数据库的表中提取信息。SELECT语句的语法是：
语法结构：简单查询
SELECT *|列名|表达式 FROM 表名 WHERE 条件 ORDER BY 列名
语法解析：
1.*表示表中的所有列。
2.列名可以选择若干个表中的列名，各个列表中间用逗号分隔。
3.表达式可以是列名、函数、常数等组成的表达式。
4.WHERE子句是查询的条件。
5.ORDER BY 要求在查询的结果中排序，默认是升序。
图2 数据查询
Oracle中可以把查询的结果根据结果集中的表结构和数据形成一张新表。
语法结构：根据结果集创建表
CREATE TABLE 表名 AS SELECT语句
代码演示：根据结果集创建表
SQL> CREATE TABLE INFOS1 AS SELECT * FROM INFOS;
TABLE CREATED
使用上面命令创建的新表中，不存在任何约束，并且把查询的数据一起插入到新表中。如果只复制表结构，只需使查询的条件不成立（比如where 1=2），就不会查询从出任何数据，从而复制一个表结构。
代码演示：复制表结构
SQL> CREATE TABLE INFOS2 AS SELECT * FROM INFOS WHERE 1=2;
TABLE CREATED
数据插入
用INSERT命令完成对数据的插入。
语法结构：根据结果集创建表
INSERT INTO 表名(列名1，列名2……) VALUES (值1，值2……)
语法解析：
1.列名可以省略。当省略列名时，默认是表中的所有列名，列名顺序为表定义中列的先后顺序。
2.值的数量和顺序要与列名的数量和顺序一致。值的类型与列名的类型一致。
代码演示：向INFOS表和SCORES表中插入数据
SQL> INSERT INTO INFOS VALUES ( ①
2 's100102', '林冲', '男', 22, 2,
3 TO_DATE('2009-8-9 06:30:10',' YYYY-MM-DD HH24:MI:SS '), ②
4 '西安', '1001'
5 )
6 /
1 row inserted
SQL> INSERT INTO INFOS VALUES (
's100104','阮小二','男',26,3,SYSDATE,default,'1001'); ③
1 row inserted
SQL>COMMIT; ④
代码解析：
①表名后面缺省了列名，默认是表Infos中的所有列名，values中的值要与表中列一一对应，包括顺序和数据类型的对应。在SQL*Plus中一条语句可以写在多行，那么从第二行开始，sqlplus会为每一行前面给出行号。
②在Oracle中，日期是国际化的，不同的区域安装的数据库，默认的日期格式不同，因此为了程序便于移植，日期的输入要使用TO_DATE函数对日期格式化后输入，采用格式化字符串对日期进行格式化时，格式化字符串中字符不区分大小写，常见的格式化字符如下：
1.yyyy表示四位年份
2.mm表示两位月份，比如3月表示为03
3.dd表示两位日期
4.hh24表示小时从0-23，hh12也表示小时从0-11。
5.mi 表示分钟
6.ss表示秒
③在遇到存在默认值的列时，可以使用default值代替。
④commit是把用户操作（添加、删除、修改操作）提交，只有提交操作后，数据才能真正更新到表中，否则其他用户无法查询到当前用户操作的结果。
在Oracle中，一个INSERT命令可以把一个结果集一次性插入到一张表中。使用的语句是：INSERT INTO 表 SELECT子句，如下示例：
代码演示：INSERT向表中插入一个结果集
SQL> INSERT INTO INFOS2 SELECT * FROM INFOS;
5 rows inserted
在这种语法下，要求结果集中每一列的数据类型必须与表中的每一列的数据类型一致，结果集中的列的数量与表中的列的数量一致。比如表INFOS2，该表的结构与INFO表一样，那么可以把INFO表中的所有记录一次性插入到INFOS2表中。
Oracle的简单查询和SQL Server一样都可以在查询列中使用常量，如图：
图3 Select中的常量
可以使用刚才的做法，把该结果集中的数据插入到表INFOS中。
代码演示：INSERT向表中插入一个常量结果集
SQL> INSERT INTO INFOS
SELECT 's100106','卢俊义','男',23,5,
TO_DATE('2009-8-9 08:00:10','YYYY-MM-DD HH24:MI:SS'),
'青龙寺','1001'
FROM DUAL;
1 rows inserted
SQL>COMMIT;
更新数据
Oracle在表中更新数据的语法是：
语法结构：UPDATE操作
UPDATE 表名 SET 列名1=值，列名2=值…… WHERE 条件
代码演示：UPDATE操作
SQL> UPDATE INFOS SET CLASSNO='1002',STUADDRESS='山东莱芜'
WHERE STUNAME='阮小二';
1 rows updated
SQL> commit;
删除数据
Oracle在表中删除数据的语法是：
语法结构：DELETE操作
DELETE FROM表名 WHERE 条件
代码演示：DELETE操作
SQL> DELETE FROM INFOS WHERE STUID='s100103';
1 ROW DELETED
SQL> COMMIT;
TRUNCATE
在数据库操作中， TRUNCATE命令（是一个DDL命令）可以把表中的所有数据一次性全部删除，语法是：
语法结构：TRUNCATE
TRUNCATE TABLE 表名
TRUNCATE和DELETE都能把表中的数据全部删除，他们的区别是：
1.TRUNCATE是DDL命令，删除的数据不能恢复；DELETE命令是DML命令，删除后的数据可以通过日志文件恢复。
2.如果一个表中数据记录很多，TRUNCATE相对DELETE速度快。
由于TRUNCATE命令比较危险，因此在实际开发中，TRUNCATE命令慎用。
Oracle默认安装中，已经创建了一个SCOTT用户，默认密码是：tiger，该用户下有四张表分别是：雇员表（EMP），部门表（DEPT），工资登记表和奖金表，请参考本章后面的附表。接下来很多操作都是在该用户下完成的。
5.操作符
Oracle开发中，依然存在算术运算，关系运算，和逻辑运算。
算术运算
Oracle中的算术运算符，没有C#中的算术运算符丰富，只有+、-、*、/四个，其中除号(/)的结果是浮点数。求余运算只能借助函数：MOD(x,y)：返回x除以y的余数。
案例3：每名员工年终奖是2000元，请显示基本工资在2000元以上的员工的月工资，年总工资。
该案例的表请参见本章练习的附表1、附表2、附表3，这三张表是ORACLE 10g自带的。
代码演示：查询中的算术运算
SQL> SELECT ENAME,SAL,(SAL*12+2000) FROM EMP WHERE SAL>2000;
ENAME SAL (SAL*12+2000)
JONES 2975 37700
BLAKE 2850 36200
CLARK 2450 31400
SCOTT 3000 38000
KING 5000 62000
FORD 3000 38000
6 rows selected
关系运算和逻辑运算
Oracle中Where子句经中经常见到关系运算和逻辑运算，常见的关系运算有：
运算符说明运算符说明
=等于 >大于
>或者!=不等于 =小于或者等于
小于>=大于或者等于
表3 Oracle的关系运算符
逻辑运算符有三个：AND、OR、NOT
关系运算和逻辑运算与前面SQL Server学习过的一致。
字符串连接操作符（||）
在Oracle中，字符串的连接用双竖线（||）表示。比如，在EMP表中，查询工资在2000元以上的姓名以及工作。
代码演示：字符串连接
SQL> SELECT (ENAME || 'is a ' || JOB) AS Employee Details ①
2 FROM EMP
3 WHERE SAL>2000;
Employee Details
------------------------
JONESis a MANAGER
BLAKEis a MANAGER
CLARKis a MANAGER
SCOTTis a ANALYST
KINGis a PRESIDENT
FORDis a ANALYST
6 rows selected
代码解析：
①Oracle中字符串可以用单引号，也可以用双引号，在别名中存在空格时，必须用双引号。在表名、列名时用双引号。
6.高级查询
在第一期学习过SQL的简单查询和连接查询。现在学习一些新的SQL操作符。
消除重复行
在Oracle查询中结果中，可能出现若干行相同的情况，那么可以使用DISTINCT消除重复行。具体的用法如示例：
代码演示：DISTINCT消除重复行
SQL> SELECT DISTINCT DEPTNO FROM EMP;
DEPTNO
------
30
20
10
NULL操作
如果某条记录中有缺少的数据值，就是空值（NULL值）。空值不等于0或者空格，空值是指未赋值、未知或不可用的值。任何数据类型的列都可以包括NULL值，除非该列被定义为非空或者主键。
代码演示：EMP中的NULL值
SQL> SELECT ENAME,JOB,SAL,COMM FROM EMP WHERE SAL 2000;
ENAME JOB SAL COMM
SMITH CLERK 800
ALLEN SALESMAN 1600 300
WARD SALESMAN 1250 500
MARTIN SALESMAN 1250 1400
TURNER SALESMAN 1500 0
ADAMS CLERK 1100
JAMES CLERK 950
7 rows selected
在查询条件中NULL值用IS NULL作条件，非NULL值用NOT IS NULL做条件。
案例4：查询EMP表中没有发奖金的员工。
代码演示：NULL值查询
SQL> SELECT ENAME,JOB,SAL,COMM FROM EMP
2 WHERE SAL 2000 AND COMM IS NULL;
ENAME JOB SAL COMM
SMITH CLERK 800
ADAMS CLERK 1100
JAMES CLERK 950
MILLER CLERK 1300
IN 操作
在Where子句中可以使用IN操作符来查询其列值在指定的列表中的行。比如：查询出工作职责是SALESMAN、PRESIDENT或者ANALYST的员工。条件有两种表示方法：
1.WHERE job = 'SALESMAN ' OR job = 'PRESIDENT ' OR job = 'ANALYST '
2.WHERE job IN ('SALESMAN', 'PRESIDENT', 'ANALYST')
代码演示：IN操作
SQL> SELECT ENAME,JOB,SAL FROM EMP
2 WHERE job IN ('SALESMAN', 'PRESIDENT', 'ANALYST');
ENAME JOB SAL
ALLEN SALESMAN 1600
WARD SALESMAN 1250
MARTIN SALESMAN 1250
SCOTT ANALYST 3000
KING PRESIDENT 5000
TURNER SALESMAN 1500
FORD ANALYST 3000
7 rows selected
对应IN操作的还有NOT IN，用法一样，结果相反。
BETWEEN…AND…
在WHERE子句中，可以使用BETWEEN操作符来查询列值包含在指定区间内的行。比如，查询工资从1000到2000之间的员工。可以使用传统方法：
WHERE SAL>=1000 AND SAL =2000
也可以使用：
WHERE SAL BETWEEN 1000 AND 2000
BWTWEEN操作所指定的范围也包括边界。
代码演示：BETWEEN操作
SQL> SELECT ename,job,sal FROM EMP WHERE sal BETWEEN 1000 AND 2000;
ENAME JOB SAL
ALLEN SALESMAN 1600
WARD SALESMAN 1250
MARTIN SALESMAN 1250
TURNER SALESMAN 1500
ADAMS CLERK 1100
MILLER CLERK 1300
6 rows selected
LIKE模糊查询
在一些查询时，可能把握不准需要查询的确切值，比如百度搜索时输入关键字即可查询出相关的结果，这种查询称为模糊查询。模糊查询使用LIKE关键字通过字符匹配检索出所需要的数据行。字符匹配操作可以使用通配符“%”和“_”:
%：表示零个或者多个任意字符。
_：代表一个任意字符。
语法是：LIKE '字符串'[ESCAPE '字符']。匹配的字符串中，ESCAPE后面的“字符”作为转义字符。与一期SQLServer中ESCAPE用法相同。
通配符表达式说明
'S%'以S开头的字符串。
'_S%'第二个字符时S的字符串。
'%30 %%' escape ' '包含“30%”的字符串，“ ”指转义字符，“ %”在字符串中表示一个字符“%”。
表4 通配符示例
案例5：显示员工名称以J开头以S结尾的员工的姓名、工资和工资。
代码演示：LIKE操作
SQL> SELECT ENAME,JOB,SAL FROM EMP WHERE ENAME LIKE 'J%S';
ENAME JOB SAL
---------- --------- ---------
JONES MANAGER 2975.00
JAMES CLERK 950.00
集合运算
集合运算就是将两个或者多个结果集组合成为一个结果集。集合运算包括：
INTERSECT(交集)，返回两个查询共有的记录。
UNION ALL(并集)，返回各个查询的所有记录，包括重复记录。
UNION(并集)，返回各个查询的所有记录，不包括重复记录。
MINUS(补集)，返回第一个查询检索出的记录减去第二个查询检索出的记录之后剩余的记录。
当使用集合操作的时候，要注意：查询所返回的列数以及列的类型必须匹配，列名可以不同。
案例6：查询出dept表中哪个部门下没有员工。只需求出dept表中的部门号和emp表中的部门号的补集即可。
代码演示：求补运算
SQL> SELECT DEPTNO FROM DEPT
2 MINUS
3 SELECT DEPTNO FROM EMP;
DEPTNO
------
40
前面学习过可以通过insert into …select把一个结果集插入到另一张结构相同的表中，因此可以使用union把若干条记录一次性插入到一张表中。
代码演示：用union插入多条数据
SQL> INSERT INTO DEPT
2 SELECT 50,'公关部','台湾' FROM DUAL
3 UNION
4 SELECT 60,'研发部','西安' FROM DUAL
5 UNION
6 SELECT 70,'培训部','西安' FROM DUAL
7 /
3 rows inserted
连接查询
在SQL Server中已经学习过内联接(inner join)、外联接(outer join)，外联接又分为左外联接(left outer join)和右外联接(right outer join)。Oracle中对两个表或者若干表之间的外联接用（+）表示。
案例7：请查询出工资大于2000元的，员工姓名，部门，工作，工资。
由于部门名称在dept中，其他的信息在emp表中，需要内联接才能完成。
代码演示：内联接
SQL> SELECT e.ENAME,e.JOB,e.SAL,d.DNAME
2 FROM emp e,dept d
3 WHERE e.deptno=d.deptno
4 AND e.SAL>2000;
ENAME JOB SAL DNAME
JONES MANAGER 2975 RESEARCH
BLAKE MANAGER 2850 SALES
CLARK MANAGER 2450 ACCOUNTING
SCOTT ANALYST 3000 RESEARCH
KING PRESIDENT 5000 ACCOUNTING
FORD ANALYST 3000 RESEARCH
6 rows selected
也可以使用SQL/92标准中的内联接：
代码演示：内联接
SELECT e.ENAME,e.JOB,e.SAL,d.DNAME
FROM EMP e INNER JOIN DEPT d ON e.DEPTNO=d.DEPTNO
WHERE e.SAL>2000
这里INNER JOIN中，关键字INNER可以省略。
案例8：请查询出每个部门下的员工姓名，工资。
案例分析：
Emp表用外键deptno引用Dept表中的deptno，在Dept表中如果有某些部门没有员工，那么用内联接，没有员工的部门将无法显示，因此必须以Dept表为基准的外联接。
代码演示：外联接
SQL> SELECT e.ENAME,e.JOB,e.SAL,d.DNAME
2 FROM EMP e ,DEPT d
3 WHERE e.DEPTNO(+)=d.DEPTNO ①
4 /
ENAME JOB SAL DNAME
SMITH CLERK 800 RESEARCH
ALLEN SALESMAN 1600 SALES
WARD SALESMAN 1250 SALES
JONES MANAGER 2975 RESEARCH
MARTIN SALESMAN 1250 SALES
BLAKE MANAGER 2850 SALES
CLARK MANAGER 2450 ACCOUNTING
SCOTT ANALYST 3000 RESEARCH
KING PRESIDENT 5000 ACCOUNTING
TURNER SALESMAN 1500 SALES
ADAMS CLERK 1100 RESEARCH
JAMES CLERK 950 SALES
FORD ANALYST 3000 RESEARCH
MILLER CLERK 1300 ACCOUNTING
公关部
研发部
培训部
OPERATIONS
18 rows selected
代码解析：
① (+)：Oracle专用的联接符，在条件中出现在左边指右外联接，出现在右边指左外联接。
也可以使用SQL/92标准的写法：
代码演示：外联接
SELECT e.ENAME,e.JOB,e.SAL,d.DNAME
FROM EMP e RIGHT OUTER JOIN DEPT d ON e.DEPTNO=d.DEPTNO
这里RIGHT OUTER JOIN中，关键字OUTER可以省略。
虽然Oracle自身的联接查询语法比较好写，同时容易理解，但是为了程序便于移植，推荐使用SQL/92表中的联接查询。同时也可以与SQL Server获得一致。
7.本章总结
Oracle SQL语句中有数据操纵语言（DML）、数据定义语言（DDL）、数据控制语言（DCL）、事务控制语言（TCL）等等。
DML语句包括增删改查语句，DDL语句包括数据库对象创建、修改和删除语句，数据控制命令包括GRANT、REVOKE等，事务控制命令有COMMIT、ROLLBACK等。
数据库中建表常用的类型有：数字类型number（p，s），可变字符串varchar2（length），日期date。
Oracle中default是一个值，在Oracle中不存在default约束。
Oracle的增删改语句与SQL Server基本一致，都是使用INSERT、UPDATE、DELETE完成。
Oracle高级查询中要注意：DISTINCT、NULL、IN、BETWEEN…AND…。
集合操作有：UNION、UNION ALL、INTESECT、MINUS。
联接查询有内联接和外联接。
8.本章练习
1.创建一查询，显示与Blake在同一部门工作的雇员的项目和受雇日期，但是Blake不包含在内。
2.显示位置在Dallas的部门内的雇员姓名、变化以及工作。
3.显示被King直接管理的雇员的姓名以及工资。
4.创建一查询，显示能获得与Scott一样工资和奖金的其他雇员的姓名、受雇日期以及工资。
附表1：Scott表中的EMP表：员工表
序号列名类型说明
1EMPNONUMBER(4)员工编号，EMP表主键
2ENAMEVARCHAR2(10)员工姓名
3JOBVARCHAR2(9)员工工作
4MGRNUMBER(4)员工的领导编号，引用EMPNO
5HIREDATEDATE入职日期
6SALNUMBER(7,2)员工工资
7COMMNUMBER(7,2)员工奖金
8DEPTNONUMBER(2)员工部门编号，是表DEPT的外键。
附表2：Scott表中的DEPT表：部门表
序号列名类型说明
1DEPTNONUMBER(2)部门编号，主键
2DNAMEVARCHAR2(14)部门名称
3LOCVARCHAR2(13)部门位置
附表3：Scott表中的SALGRADE表：工资等级表
序号列名类型说明
1GRADENUMBER等级
2LOSALNUMBER此等级下最低工资
3HISALNUMBER此等级下最高工资
章节知识结构图
第3章
子查询和常用函数
主要内容
子查询
伪列
锁的概念
1.子查询
子查询在SELECT、UPDATE、DELETE语句内部可以出现SELECT语句。内部的SELECT语句结果可以作为外部语句中条件子句的一部分，也可以作为外部查询的临时表。子查询的类型有：
1.单行子查询：不向外部返回结果，或者只返回一行结果。
2.多行子查询：向外部返回零行、一行或者多行结果。
案例1：查询出销售部（SALES）下面的员工姓名，工作，工资。
案例分析
该问题可以用联接查询实现，由于所需的结果信息都在Emp表中，可以先从Dept表中查询出销售部对应的部门号，然后根据当前部门号再到Emp表中查询出符合该部门的员工记录即可。从销售表中查询出的结果可以作为Emp表中查询的条件，SQL语句实现如下：
代码演示：单行子查询
SQL> SELECT ENAME,JOB,SAL FROM EMP
2 WHERE DEPTNO=(SELECT DEPTNO FROM DEPT WHERE DNAME='SALES') ①
3 /
ENAME JOB SAL
ALLEN SALESMAN 1600
WARD SALESMAN 1250
MARTIN SALESMAN 1250
BLAKE MANAGER 2850
TURNER SALESMAN 1500
JAMES CLERK 950
6 rows selected
代码解析：
①内部查询的结果作为外部查询的条件。
需要注意：
如果内部查询不返回任何记录，则外部条件中字段DEPTNO与NULL比较永远为假，也就是说外部查询不返还任何结果。
在单行子查询中外部查询可以使用=、>、 、>=、 =、 >等比较运算符。
内部查询返回的结果必须与外部查询条件中的字段（DEPTNO）匹配。
如果内部查询返回多行结果则出现错误。
案例2：查询出Emp表中比任意一个销售员(“SALESMAN”)工资低的员工姓名、工作、工资。
案例分析
销售员在Emp表中有很多条记录，每个人工资不相等，如果返回“比任意员工的工资还低”的条件，返回比“最高工资还低”即可。如果用子查询做，子查询中就会返回多条记录。用普通的关系符（>、 等）运行就会出错。这时候需要用关键字ANY。ANY放在比较运算符后面，表示“任意”的意思。
代码演示：ANY子查询
SQL> SELECT ENAME,JOB,SAL FROM EMP
2 WHERE SAL ANY (SELECT SAL FROM EMP WHERE JOB='SALESMAN') ①
3 /
ENAME JOB SAL
SMITH CLERK 800
JAMES CLERK 950
ADAMS CLERK 1100
WARD SALESMAN 1250
MARTIN SALESMAN 1250
MILLER CLERK 1300
TURNER SALESMAN 1500
7 rows selected
代码解析：
① any:比子查询结果中任意的值都小，也就是说，比子查询结果中最大值还小，那么同理>any表示比子查询结果中最小的还大。
案例3：查询出比所有销售员的工资都高的员工姓名，工作，工资。
案例分析
ANY可以表示任意的，但本案例中要求比所有销售员工资都高，那么就要使用另外一个关键字ALL。ALL与关系操作符一起使用，表示与子查询中所有元素比较。
代码演示：ALL子查询
SQL> SELECT ENAME,JOB,SAL FROM EMP
2 WHERE SAL>ALL (SELECT SAL FROM EMP WHERE JOB='SALESMAN') ①
3 /
ENAME JOB SAL
JONES MANAGER 2975
BLAKE MANAGER 2850
CLARK MANAGER 2450
SCOTT ANALYST 3000
KING PRESIDENT 5000
FORD ANALYST 3000
6 rows selected
代码解析：
①>ALL：比子查询结果中所有值还要大，也就是说，比子查询结果中最大值还要大。 ALL表示比最小值还要小。
对于子查询还可以使用IN和NOT IN操作符进行操作。
2.Oracle中的伪列
在Oracle的表的使用过程中，实际表中还有一些附加的列，称为伪列。伪列就像表中的列一样，但是在表中并不存储。伪列只能查询，不能进行增删改操作。接下来学习两个伪列：ROWID和ROWNUM。
ROWID
表中的每一行在数据文件中都有一个物理地址，ROWID伪列返回的就是该行的物理地址。使用ROWID可以快速的定位表中的某一行。ROWID值可以唯一的标识表中的一行。由于ROWID返回的是该行的物理地址，因此使用ROWID可以显示行是如何存储的。
代码演示：ROWID
SQL> SELECT ROWID,ENAME FROM EMP WHERE SAL>2000;
ROWID ENAME
AAAMgzAAEAAAAAgAAD JONES
AAAMgzAAEAAAAAgAAF BLAKE
AAAMgzAAEAAAAAgAAG CLARK
AAAMgzAAEAAAAAgAAH SCOTT
AAAMgzAAEAAAAAgAAI KING
AAAMgzAAEAAAAAgAAM FORD
6 rows selected
ROWNUM
在查询的结果集中，ROWNUM为结果集中每一行标识一个行号，第一行返回1，第二行返回2，以此类推。通过ROWNUM伪列可以限制查询结果集中返回的行数。
ROWNUM与ROWID不同，ROWID是插入记录时生成，ROWNUM是查询数据时生成。ROWID标识的是行的物理地址。ROWNUM标识的是查询结果中的行的次序。
案例4：查询出员工表中前5名员工的姓名，工作，工资。
代码演示：ROWNUM
SQL> SELECT ROWNUM,ENAME,JOB,SAL FROM EMP WHERE ROWNUM =5;
ROWNUM ENAME JOB SAL
1 SMITH CLERK 800
2 ALLEN SALESMAN 1600
3 WARD SALESMAN 1250
4 JONES MANAGER 2975
5 MARTIN SALESMAN 1250
案例5：查询出工资最高的前5名员工的姓名、工资和工资。
案例分析
“工资最高的前5名”需要先降序排序，再取前5名，但是生成ROWNUM操作比排序要早，排序时已经连同ROWNUM一起排序了，因此不能直接在案例1的语句中直接加上Order by就行，而是需要对排序的结果重新做二次查询，产生新的ROWNUM才能作为查询的条件依据。
代码演示：ROWNUM应用
SQL> SELECT ROWNUM,T.* FROM ①
2 (SELECT ENAME,JOB,SAL
3 FROM EMP ORDER BY SAL DESC) T ②
4 WHERE ROWNUM =5
5 /
ROWNUM ENAME JOB SAL
1 KING PRESIDENT 5000
2 SCOTT ANALYST 3000
3 FORD ANALYST 3000
4 JONES MANAGER 2975
5 BLAKE MANAGER 2850
代码解析：
①T是子查询②的别名，这里的ROWNUM是第二次查询后的ROWNUM。
案例6：查询出表EMP中第5条到第10条之间的记录。
案例分析
这是分页的应用。在查询条件中，如果查询条件中ROWNUM大于某一正整数，则不返还任何结果。
代码演示：ROWNUM分页
SQL> SELECT * FROM
2 (SELECT ROWNUM R,ENAME,JOB,SAL ①
3 FROM EMP WHERE ROWNUM =10) ②
4 WHERE R>5 ③
5 /
R ENAME JOB SAL
6 BLAKE MANAGER 2850
7 CLARK MANAGER 2450
8 SCOTT ANALYST 3000
9 KING PRESIDENT 5000
10 TURNER SALESMAN 1500
代码解析：
①内部查询中得到ROWNUM 并且用别名R记录，供外层条件③使用。
②内部查询的ROWNUM，与外出的ROWNUM列是平等的两列。
③使用的R是内层产生的ROWNUM，在外层看来，内层查询的ROWNUM是正常的一列。
3.Oracle函数
Oracle SQL提供了用于执行特定操作的专用函数。这些函数大大增强了SQL语言的功能。函数可以接受零个或者多个输入参数，并返回一个输出结果。Oracle数据库中主要使用两种类型的函数：
1.单行函数：对每一个函数应用在表的记录中时，只能输入一行结果，返回一个结果，比如：MOD(x,y)返回x除以y的余数（x和y可以是两个整数，也可以是表中的整数列）。常用的单行函数有：
字符函数：对字符串操作。
数字函数：对数字进行计算，返回一个数字。
转换函数：可以将一种数据类型转换为另外一种数据类型。
日期函数：对日期和时间进行处理。
2.聚合函数：聚合函数同时可以对多行数据进行操作，并返回一个结果。比如SUM(x)返回结果集中x列的总合。
字符函数
字符函数接受字符参数，这些参数可以是表中的列，也可以是一个字符串表达式。下表列出了常用的字符函数。
函数说明
ASCII(x)返回字符x的ASCII码。
CONCAT(x,y)连接字符串x和y。
INSTR(x, str [,start] [,n)在x中查找str，可以指定从start开始，也可以指定从第n次开始。
LENGTH(x)返回x的长度。
LOWER(x)x转换为小写。
UPPER(x)x转换为大写。
LTRIM(x[,trim_str])把x的左边截去trim_str字符串，缺省截去空格。
RTRIM(x[,trim_str])把x的右边截去trim_str字符串，缺省截去空格。
TRIM([trim_str FROM] x)把x的两边截去trim_str字符串，缺省截去空格。
REPLACE(x,old,new)在x中查找old，并替换为new。
SUBSTR(x,start[,length])返回x的字串，从staart处开始，截取length个字符，缺省length，默认到结尾。
表1 字符函数
示例示例结果
SELECT ASCII('a') FROM DUAL97
SELECT CONCAT('Hello', ' world') FROM DUALHello world
SELECT INSTR('Hello world'，'or') FROM DUAL8
SELECT LENGTH('Hello') FROM DUAL5
SELECT LOWER('hElLO') FROM DUAL;hello
SELECT UPPER('hello') FROM DUALHELLO
SELECT LTRIM('===HELLO===', '=') FROM DUALHELLO===
SELECT '=='||LTRIM(' HELLO===') FROM DUAL==HELLO===
SELECT RTRIM('===HELLO===', '=') FROM DUAL===HELLO
SELECT '='||TRIM(' HELLO ')||'=' FROM DUAL=HELLO=
SELECT TRIM('=' FROM '===HELLO===') FROM DUALHELLO
SELECT REPLACE('ABCDE','CD','AAA') FROM DUALABAAAE
SELECT SUBSTR('ABCDE',2) FROM DUALBCDE
SELECT SUBSTR('ABCDE',2,3) FROM DUALBCD
表2 字符函数示例
数字函数
数字函数接受数字参数，参数可以来自表中的一列，也可以是一个数字表达式。
函数说明示例
ABS(x)x绝对值ABS(-3)=3
ACOS(x)x的反余弦ACOS(1)=0
COS(x)余弦COS(1)=1.57079633
CEIL(x)大于或等于x的最小值CEIL(5.4)=6
FLOOR(x)小于或等于x的最大值FLOOR(5.8)=5
LOG(x,y)x为底y的对数LOG(2,4)=2
MOD(x,y)x除以y的余数MOD(8,3)=2
POWER(x,y)x的y次幂POWER(2,3)=8
ROUND(x[,y])x在第y位四舍五入ROUND(3.456,2)=3.46
SQRT(x)x的平方根SQRT(4)=2
TRUNC(x[,y])x在第y位截断TRUNC(3.456,2)=3.45
表3 数字函数
说明：
1.ROUND(X[,Y])，四舍五入。
在缺省y时，默认y=0；比如：ROUND(3.56)=4。
y是正整数，就是四舍五入到小数点后y位。ROUND(5.654,2)=5.65。
y是负整数，四舍五入到小数点左边|y|位。ROUND(351.654,-2)=400。
2. TRUNC(x[,y])，直接截取，不四舍五入。
在缺省y时，默认y=0；比如：TRUNC (3.56)=3。
y是正整数，就是四舍五入到小数点后y位。TRUNC (5.654,2)=5.65。
y是负整数，四舍五入到小数点左边|y|位。TRUNC (351.654,-2)=300。
日期函数
日期函数对日期进行运算。常用的日期函数有：
1.ADD_MONTHS(d,n)，在某一个日期d上，加上指定的月数n，返回计算后的新日期。d表示日期，n表示要加的月数。
图1 ADD_MONTHS函数示例
2.LAST_DAY(d)，返回指定日期当月的最后一天。
图2 LAST_DAY函数示例
3.ROUND(d[,fmt])，返回一个以fmt为格式的四舍五入日期值，d是日期，fmt是格式模型。默认fmt为DDD，即月中的某一天。
如果fmt为“YEAR”则舍入到某年的1月1日，即前半年舍去，后半年作为下一年。
如果fmt为“MONTH”则舍入到某月的1日，即前月舍去，后半月作为下一月。
默认为“DDD”，即月中的某一天，最靠近的天，前半天舍去，后半天作为第二天。
如果fmt为“DAY”则舍入到最近的周的周日，即上半周舍去，下半周作为下一周周日。
图3 ROUND函数示例
与ROUND对应的函数时TRUNC(d[,fmt])对日期的操作，TRUNC与ROUND非常相似，只是不对日期进行舍入，直接截取到对应格式的第一天。
4.EXTRACT(fmt FROM d)，提取日期中的特定部分。
fmt为：YEAR、MONTH、DAY、HOUR、MINUTE、SECOND。其中YEAR、MONTH、DAY可以为DATE类型匹配，也可以与TIMESTAMP类型匹配；但是HOUR、MINUTE、SECOND必须与TIMESTAMP类型匹配。
HOUR匹配的结果中没有加上时区，因此在中国运行的结果小8小时。
图4 EXTRACT函数示例
转换函数
转换函数将值从一种数据类型转换为另外一种数据类型。常用的转换函数有：
1.TO_CHAR(d|n[,fmt])
把日期和数字转换为制定格式的字符串。fmt是格式化字符串，日期的格式化字符串前面已经学习过。
代码演示：TO_CHAR对日期的处理
SQL> SELECT TO_CHAR(SYSDATE,'YYYY 年 MM 月 DD 日 HH24:MI:SS') date ①
2 FROM DUAL;
date
-----------------------
2009年08月11日 12:06:00
代码解析：
①在格式化字符串中，使用双引号对非格式化字符进行引用。
针对数字的格式化，格式化字符有：
参数示例说明
9999指定位置处显示数字。
.9.9指定位置返回小数点
,99,99指定位置返回一个逗号
$$999数字开头返回一个美元符号
EEEE9.99EEEE科学计数法表示
LL999数字前加一个本地货币符号
PR999PR如果数字式负数则用尖括号进行表示
表4 数字格式化字符
代码演示：TO_CHAR对数字的处理
SQL> SELECT TO_CHAR(-123123.45,'L9.9EEEEPR') date
2 FROM DUAL
3 /
date
--------------------
￥1.2E+05>
2.TO_DATE(x [,fmt])
把一个字符串以fmt格式转换为一个日期类型，前面已经学习过。
3.TO_NUMBER(x[,fmt])
把一个字符串以fmt格式转换为一个数字。fmt格式字符参考表3。
代码演示：TO_NUM函数
SQL> SELECT TO_NUMBER('-$12,345.67','$99,999.99') NUM
2 FROM DUAL
3 /
NUM
---------------
-12345.67
其他单行函数
1.NVL(x,value)
如果x为空，返回value，否则返回x。
案例7：对工资是2000元以下的员工，如果没有发奖金，每人奖金100元。
代码演示：NVL函数
SQL> SELECT ENAME,JOB,SAL,NVL(COMM,100) FROM EMP WHERE SAL 2000;
ENAME JOB SAL NVL(COMM,100)
SMITH CLERK 800 100
ALLEN SALESMAN 1600 300
WARD SALESMAN 1250 500
MARTIN SALESMAN 1250 1400
TURNER SALESMAN 1500 50
ADAMS CLERK 1100 100
JAMES CLERK 950 100
7 rows selected
2.NVL2(x,value1,value2)
如果x非空，返回value1，否则返回value2。
案例8：对EMP表中工资为2000元以下的员工，如果没有奖金，则奖金为200元，如果有奖金，则在原来的奖金基础上加100元。
代码演示：NVL2函数
SQL> SELECT ENAME,JOB,SAL,NVL2(COMM,comm+100,200) comm
2 FROM EMP WHERE SAL 2000;
ENAME JOB SAL comm
SMITH CLERK 800 200
ALLEN SALESMAN 1600 400
WARD SALESMAN 1250 600
MARTIN SALESMAN 1250 1500
TURNER SALESMAN 1500 150
ADAMS CLERK 1100 200
JAMES CLERK 950 200
MILLER CLERK 1300 200
8 rows selected
聚合函数
聚合函数同时对一组数据进行操作，返回一行结果，比如计算一组数据的总和，平均值等。
名称作用语法
AVG平均值AVG(表达式)
SUM求和SUM(表达式)
MIN、MAX最小值、最大值MIN(表达式)、MAX(表达式)
COUNT数据统计COUNT(表达式)
表 5 聚合函数
案例9：求本月所有员工的基本工资总和。
代码演示：SUM函数
SQL> select sum(sal) from emp;
SUM(SAL)
----------------
29025
案例10：求不同部门的平均工资。
代码演示：AVG函数下的分组查询
SQL> SELECT DEPTNO,AVG(SAL) FROM EMP GROUP BY DEPTNO;
DEPTNO AVG(SAL)
--------- ----------
30 1566.66666
20 2175
10 2916.66666
4.本章总结
Oracle常用函数有字符相关的函数、数字相关的函数、日期相关的函数、转换函数等。
EXTRACT函数能够获取日期中的某个字段的值。
TO_CHAR函数能够把数字和日期转换成固定的字符串格式。TO_DATE函数能够把固定格式的字符串转换为日期类型。
子查询中有返回单行的子查询和返回多行的子查询。
Oracle中存在ROWID、ROWNUM等伪列。
5.本章练习
1.描述TO_CHAR和TO_DATE函数的用法。
2.描述EXTRACT函数的用法。
3.你知道有哪些关于日期函数的用法？
章节知识结构图
第4章
表空间、数据库对象
主要内容
同义词概念
序列的应用
视图的概念
索引的概念
表空间的概念
1.Oracle数据库对象
数据库对象是数据库的组成部分，常常用CREATE命令进行创建，可以使用ALTER命令修改，用DROP执行删除操作。前面已经接触过的数据库对象有表、用户等。
今天将学习更多的Oracle数据库对象：
同义词：就是给数据库对象一个别名。
序列：Oracle中实现增长的对象。
视图：预定义的查询，作为表一样的查询使用，是一张虚拟表。
索引：对数据库表中的某些列进行排序，便于提高查询效率。
2.同义词
同义词（Synonym）是数据库对象