Oracle数据库基础知识指导

oracle 数据库基础

Oracle 数据库

基 础 知 识 指 导 书

oracle 数据库基础

目 录

第一部分 Oracle数据库概念与原理 .............................................................................................. 1

一、Oracle数据库 ................................................................................................................... 1 1. 数据库 ........................................................................................................................ 1 2. Oracle数据库 ........................................................................................................... 1

二、Oracle数据库原理 ........................................................................................................... 1 1. Oracle数据库的基本任务 ....................................................................................... 1 2. Oracle数据库的组成结构 ....................................................................................... 1 3. Oracle数据库的数据处理流程 ............................................................................... 3 第二部分：Oracle数据库的安装与建立 ....................................................................................... 3

一、Oracle数据库的安装 ....................................................................................................... 3 二、Oracle数据库启动和关闭 ............................................................................................... 5 1. 启动数据库： ............................................................................................................ 5 2. 关闭数据库 ............................................................................................................... 6

三、 建立数据库 ...................................................................................................................... 6 1. 创建数据库 ............................................................................................................... 6 3. 建表、建视图、建索引 ......................................................................................... 10 第三部分．ORACLE数据库的应用 ............................................................................................ 11

一、 查询 ................................................................................................................................ 12 1. 主要命令和语法： .................................................................................................. 12 2. 举例： ...................................................................................................................... 12 第四部分 Oracle数据库备份与恢复 ........................................................................................... 16

一、数据库备份 ..................................................................................................................... 16 二、数据库恢复 ..................................................................................................................... 19 第五部分 Oracle数据库SQL*Net ............................................................................................... 22 第六部分 DBA常用查询指令 ..................................................................................................... 24 第七部分 关系数据库SQL语言 ................................................................................................. 27

一．SQL数据库的体系结构： ............................................................................................ 27 二．SQL数据定义功能 ........................................................................................................ 27 三．SQL数据操纵功能 ........................................................................................................ 28 1. SELECT 创建记录和表 ........................................................................................... 28 2.插入数据 ................................................................................................................... 34

I

oracle 数据库基础

3.删除记录 ................................................................................................................... 35 4.更新记录 ................................................................................................................. 35 5.集合函数介绍 ......................................................................................................... 36

II

oracle 数据库基础

第一部分 Oracle数据库概念与原理

一、Oracle数据库 1. 数据库

数据库是一套解决信息管理问题的工具，是数据文件及处理这些数据文件的程序的集合。一个数据库系统必须实现在多用户环境下可靠地管理大量的数据，使得很多用户在并发处理时获得相同的结果，而且必须具有处理数据的高效性、可靠性、安全性和容错性，同时提供简便易用的客户端用户操作过程和应用接入。

2. Oracle数据库

Oracle数据库是采用数据库进程和应用程序分进程处理的Client/Server结构的关系型数据库，采用大型数据库的磁盘空间管理形式，支持大量用户同时操作相同的数据，实现高度可靠性、高度的安全性、高效率和在线备份机制。Oracle数据库适合于各种不同的硬件环境和不同的操作系统，且具有接口方便和控制容易的特性，并支持多点实时复制。

二、Oracle数据库原理 1. Oracle数据库的基本任务

（1）录入数据； （2） 存储数据； （3） 查询、处理数据。

2. Oracle数据库的组成结构

Oracle按照应用情况可以分为核心数据（DATA）、操作系统（Operation System）、数据库服务器部分（Oracle Server）、Oracle SQL Server程序、Oracle工具（包括SQL*PLUS和很多像Developer 2000、designer 2000等工具）以及Oracle的各种应用。 按照Oracle的对象组成结构来看，Oracle数据库由以下几部分组成：

1

oracle 数据库基础

（1）实例（Instance）：Oracle实例是一组有自己的系统全局区和与其相关数据库文件的 Oracle服务器进程，每个实例都有自己的SID（系统识别符）。

（2）对象（Object）：由应用程序中的SQL语句引用的表空间、用户、表、视图、索引、权限、DBA（数据库管理员）等。

（3）程序：也是Oracle数据库的过程，主要包括客户过程（client processes），根据用户的要求向服务器过程提出需求信息的请求；服务器过程（server processes），接收用户过程发出的请求，并根据请求与数据库通信，完成对数据库中数据的处理。 （4）文件：包括数据文件（Datafile），存放所有数据库有关的数据。

? 日志文件（Log File），也称重演日志文件（Redo Logs File）或事务记录文件

（Trasaction Log File），记录针对数据库进行的修改操作或各种事务，节省数据库直接操作数据文件的时间和次数，提高处理效率。

? 控制文件（Control File），存放与Oracle数据库所有文件均相关的关键信息，

Oracle数据库通过控制文件保持数据的一致性，以及恢复数据时使用哪些重演日志。

每个数据库至少应该有两个以上的控制文件和日志文件备份。 （5） 内存结构

Oracle的内存结构SGA主要是一个通信器，它是一块存放系统信息的内存区域，使得通信过程尽量能够在内存中处理，达到快速度、高效率。所有用户进程和服务器进程都能够访问这个内存结构。SGA区主要分为四块：

? Data Buffer Cache（数据高速缓冲区）中存放着Oracle最近使用过的数据库数据

块，当用户一次访问数据时，用户进程从物理磁盘中读取数据后将其放在这个高速缓冲区，下一次或其他用户访问时从缓冲区读取，Oracle数据库根据数据被访问的频率和缓冲区的物理大小通过相应的HASH算法将经常访问的数据保留，而去除不经常访问的数据块。

? Dictionary Cache（字典高速缓冲区）用于保存数据字典中的行，数据字典中存放

Oracle系统管理时需要的所有信息，这些信息包括：访问Oracle数据库的用户以及他们访问哪些数据库对象和这些对象所放的位置等。

? Redo Log Cache（即重演日志缓冲区），任何需要事务在写入到重演日志之前首

先放到缓冲区中，然后定期将其写入到重演日志。

? Share SQL Pool（共享SQL池）相当于程序高速缓冲区，所有用户的经过语法分

析的、准备执行的SQL语句都放在此共享池中。

2

oracle 数据库基础

3. Oracle数据库的数据处理流程

Oracle数据处理流程，下面以简单的update操作为例加以说明。

先经过系统监视器（SMON）和过程监视器（PMON）检测数据库运行正常，Server进程和Clients进程运行在不同的机器上，彼此通过网络连接。

? 一个Oracle实例运行在数据库Server主机上，用户进程通过SQL*Net连接到

Server主机，Server根据User Process的请求启动一个Server Process。 ? 用户通过SQL语句向Server发出查询或者修改数据信息的请求，并且提交。 ? 数据库Server进程接到请求后检测SGA区中Share Pool中的共享SQL池是否有

相同的SQL语句。如果有，Server进程检验用户的合法性，然后执行共享池中的SQL语句；如果没有，Server进程在共享池中产生一个新的SQL语句，可以供以后任何进程使用，然后执行此SQL语句。

? Server进程检测所有操作的表是否在共享池，如果在，则直接执行下一步；如果

不在，从数据库文件中读取放到高速缓冲区中；此时如果需要擦除缓冲区中的脏数据块，则需要通过校验点CKPT进行校验，再执行下一步。

? 将原来共享池中的数据写入到回滚段 （Redo Log Buffer），通过LGWR在重演日

志中生成该事务的一个拷贝。

? 通过DBWR修改数据文件（Data Files），将执行成功的信号返回到SGA区。 ? 将完成的事务记录到重演日志中。 ? 清除回滚段的信息。 ? 完成本次操作。

说明：以上LCKn为lock（锁）情况，RECO 为RECOVER（恢复）过程，SNPn为snapshot Refresh Process（快照刷新进程），Qnnn为Parallel Query Processes（并行查询进程），ARCH为Archiver（物理热备份归档处理进程）

第二部分：Oracle数据库的安装与建立

一、Oracle数据库的安装

以ORACLE816在AIX操作系统上安装为例，下面介绍安装过程中的一些关键步骤和有关注意事项，详细安装请参考随机手册。

（1）需要运行oracle数据库的机器在安装系统时必须使swap区为为系统内存的2-3倍，建议为3倍。（如果物理内存超过1GB，则两倍就足够）

3

oracle 数据库基础

（2） 建立Oracle软件的安装点：建议将安装点统一放在oracle用户缺省主目录：/home/oracle下，不需要手工创建安装点目录

（3） 用SMIT建立：GROUP（oracle）和USER（oracle），并将oracle用户加入到这个组中，如果上面步骤已经建立了安装点，将ORACLE用户主目录设置成安装点目录，如果没有建立了安装点，则可以用缺省主目录：/home/oracle ，然后在用户主目录下建立orahome属主为orcle:oracle，将该目录作为ORACLE系统主目录，注意，ORACLE系统主目录应该与环境变量:ORACLE_HOME保持一致。

（4）在AIX操作系统上安装ORACLE数据库不需要调整内核参数。

（5）以oracle用户在X-WINDOWS界面登录（如果在PC上远程安装，需要用Reflection软件，记住，不管是在本机还是在客户端安装，均要用采用英文环境安装，否则，安装可能出问题！）然后修改两个文件:.profile和.dtprofile ，将profile文件修改成：

umask 022

export TERM=vt100

export DISPLAY=localhost:0.0 export ORACLE_BASE=/home/oracle

export ORACLE_HOME=$ORACLE_BASE/orahome export ORACLE_SID=zxin

export PATH=$PATH:$ORACLE_HOME/bin:.

export CLASPATH=JRE_Location:$ORACLE_HOME/jlib:$ORACLE_HOME/product/jlib export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/network/lib export LIBPATH=$LIBPATH:$ORACLE_HOME/lib export LANG=Zh_CN

export LOCPATH=/usr/lib/nls/loc

export NLSPATH=/usr/lib/nls/msg/%L/%N:/usr/lib/nls/msg/%L/%N.cat set -o vi

PATH=$PATH:/usr/vac/bin export PATH

PS1='$PWD$ '; export PS1

然后将.dtprofile文件中最后一行：# DTSOURCEPROFILE=true前面的#号去掉 （6）在图形界面中开启终端窗口：然后用su命令以超级用户登录，运行以下命令：

mount /cdrom （由于已经制作/cdrom文件系统，所以可以直接mount，不需要 CDROM设备号）

（7）退出图形界面，右键选择【log out】，以oracle用户重新登录使环境变量生效 （8）cd /cdrom 然后在当前目录下运行/runInstaller开始oracle的安装 （9）安装选择注意事项：

? 选择需要安装的ORACLE产品：Oracle8i Enterprice Edition 8.1.6.0.0。

4

oracle 数据库基础

? 选择安装类型：custom。

? 产品语言中（product language）增加:Simplified Chinese 。 ? 安装过程提示是否要建缺省数据库，选择NO 。

? 安装最后阶段根据提示运行root.sh完成用超级用户权限的有关配置。

? 系统安装完毕后，生成缺省系统管理员用户:system和sys，默认密码分别为

manager和change_on_install。

（10）安装过程结束后，安装程序会利用Net8进行网络配置：在图形界面下按照要求添入本机IP地址，端口号和服务名，系统自动生成 $ORACLE_HOME/network/admin目录下的两个文件：listener.ora 和 tnsnames.ora。

（11） 以超级用户，修改侦听端口文件：#vi /etc/services，在文件的最后添加：

listener1 1521/tcp #oracle listener port1 listener2 1521/tcp #oracle listener port2

二、Oracle数据库启动和关闭 1. 启动数据库：

（1）可以利用写好的脚本文件自动启动?start或者依照以下步骤启动：

首先以oracle用户身份启动Server Manager，以数据库系统管理员用户连接到数据库，连接到internal.

? $ svrmgrl（命令格式进入数据库管理状态） ? SVRMGR>connect internal（以sysdba身份登录）

（2）启动Oracle实例（Instance）、Mount数据库（database）、打开数据库

SVRMGR>startup（默认方式为启动实例， mount数据库和打开数据库步骤打开数据库，即:Startup nomount ，alter database mount ，alter database open） 如果不以默认方式启动数据库，可以有以下几种方式：

? 启动实例，mount数据库，但不打开数据库。 ? 启动实例，mount数据库，打开数据库。 ? 带参数文件的方式启动数据库 SVRMGR>startup pfile=/path/pfilename。

? 打开数据库后，可以执行下述指令来限制只有拥有“Restricted Sesstion”系统特

权的用户才可以登录到数据库

SVRMGR>alter system enable restricted session。 用下述语句解除该限制：

5

oracle 数据库基础

SVRMGR>alter system dusable restricted session。

（3）授权SQLNET可以远程访问该数据库，启动Listener进程： $1snrctl start

启动ORCLE侦听后台进程，如果已经启动，可以先用: lsnrctl stop 关闭后再启 动，然后用：tnsping [servicename] 检测Listener后台进程是否启动

2. 关闭数据库

（1） 先关闭Oracle的Listener Server，以Oracle 用户执行$1snrctl stop。 （2）再关闭Oracle 数据库，进入数据库管理状态：SVRMGR> connect internal: ? SVRMGR> shutdown：（缺省为normal方式，停止接受新的数据库联机请求，等

待当前所有联机请求处理完正执行的联机程序，等待数据所有的Commit和rollback 执行完成以后关闭数据库。）

? SVRWGR> shtudown immediate：（停止接受新的数据库联机请求，立即中断目前

所有联机用户正执行的SQL指令，回滚所有用户尚未承诺的交易后立即关闭数据 库）

? SVRMGR> shutdown abort：（立即终止一切 对数据库的所有会话，关闭数据库

实例。此种方式立即关闭数据库以后，当下一次启动数据库时，Oracle需要做很多修复数据库 工作，所以启动会花较长时间。）

三、 建立数据库 1、创建数据库

安装完数据库以后，需要根据应用程序的需求建立一个相应能满足要求的数据库。 创建数据库的主要步骤如下：

（1） 通过应用情况设计和决定数据库的内容

（2） 设计数据库的结构使得数据库内容紧凑和减少碎片（初步创建时可以使用默认设置）

（3）安装时事先定好ORACLE用户的环境变量

主要有：ORACLE_SID 、ORACLE_HOME和相关的路径设置。 （4） 从标准例子的init.ora文件中拷贝和编辑参数文件

必须配置的参数有db_name（数据库名称）、 control_files （控制文件名称和位置，可以有多个）和重做日志文件个数等等，其它各项可以在安装数据库时设置成默认参数。具

6

oracle 数据库基础

体有以下参数值得重视：

? Db_block_size :该参数设置ORACLE数据库Database Block的大小，它代表

ORACLE数据库数据存取的最小单位，最好是操作系统块大小的整数倍为宜，较大的设置值可以减少大量存取数据的时间，但会浪费磁盘空间，该参数在创库时一旦设置，便不能更改，除非重新创建数据库，一般设置大小：4096或8192。 ? Db_block_buffers :该参数设置ORACLE数据库内存缓冲区块的个数，该参数和

Db_block_size的乘积即为整个数据高速缓冲区Database Buffer Cache的大小，该值要根据系统物理内存情况设置成一个比较大的数。

? Log_checkpoint_timeout :该参数设置距上次触发checkpoint事件过多少秒后触发

下一次checkpoint事件，该值设大可以减少磁盘I/O的频繁程度，推荐设置（缺省设置）：1800[秒]。

? Log_buffer :该参数设置Redo Log Buffer的大小，设大该值，可以减少Redo日志

文件磁盘I/O。

? nls_language :该参数设置国家语言有关的各种NLS数据显示格式参数，推荐设

置：“SIMPLIFIED CHINESE”。

? nls_territory :该参数设置国家惯例有关的各种NLS数据显示格式参数，推荐设

置：“CHINA”。

? shared_pool_size :该参数主要包含共享SQL池、字典高速缓冲区，设大该值可以

增加运行SQL语句和数据字典在内存中的占有率，以此增加数据库SQL语句的执行效率，推荐设置较大的值。

（5） 连接到数据库的Server端启动svrmgrl，连接成internal状态。以nomount方式启动数据库：

SVRMGR>startup nomount

（6）使用create database的sql命令创建数据库

? 主要格式：

sql> create database databasename[controlfile1 [ controlfile2 ]] 2>[logfile [group n] filespec] [datafile filespec] [maxdatafiles n] 3>[archivelog （noarchivelog）]

注意：filespec包括文件的位置、名称、大小。 ? 举例：

SVRMGR>create database test

2>datafile '/data1/oracle/dbfile/test.dbf' size 100M reuse autoextend on 3> next 5M maxsize 200M

4>logfile group 1 '/data1/oracle/dbfile/redo01.log' size 30M， 5>group 2 '/data1/oracle/dbfile/redo02.log' size 30M

7

oracle 数据库基础

sql>select LOGIN_DATE，LOGIN_NAME from BCOL_REG_ACCT 2>where LOGIN_DATE='JUN-20-1998'; 4）查询出所有用户名LOGIN_NAME为abcde 登录日期大于'JUN-20-1998'的所有三列

LOGIN_DATE，LOGIN_NAME，START_TIME，并且按照登录日期的顺序排列： sql>select LOGIN_DATE，LOGIN_NAME，START_TIME from BCOL_REG_ACCT 2>where LOGIN_NAME='abcde' andLOGIN_DATE>'JUN- 20-1998' 3>order by LOGIN_DATE; 5）非等值条件查询

查询出所有用户名以abc开头，登录时间界于JUN-10-1998和JUN-20-1998之间，连接时长不等于

5000的所有列，并以LOGIN_NAME反向排序： sql>select * from BCOL_REG_ACCT 2>where LOGIN_NAME like 'abc%'

3>and LOGIN_DATE between 'JUN-10- 1998' and 'JUN-20-1998' 4>and CONNECT_DUR!=5000 5>order by LOGIN_NAME desc; 6） 使用空值（NULL）查询

查询出LOGIN_NAME，LOGIN_DATE，如果为空值则以JUA-01-1998代替，START_TIME符合LOGIN_NAME不为空，且 START_TIME为空的列。

sql>select LOGIN_NAME，nvl（LOGIN_ DATE，JUA-01-1998），START_TIME 2>from BCOL_REG_ACCT

3>where LOGIN_NAME is not NULL 4>and START_TIME is NULL; 7） 基于值集的查询

查询出用户名LOGIN_ NAME在abcde，abcdf，abcdg，abcdh中，且日期LOGIN_DATE大于 'JUN-20-1998' 和小于'MAY-20-1998'的所有列并且按照LOGIN_DATE的顺序排列，并按照LOGIN_DATE归类，即每天为一 类数据。

Sql>select * from BCOL_REG_ACCT

2>where LOGIN_NAME in （'abcde'，'abcdf'， 'abcdg'，'abcdh'） 3>and （acctdate>='06/20/1998' or acctdate <'05/20/1998'） 4>order by acctdate，group by acctdate; （2） 使用表达式

查询出符合留入字节树大于1000的所有连接时长（CONNECT_DUR）和留入字节数与加上1000再除以100的数值：

13

oracle 数据库基础

sql>select CONNECT_DUR，（BYTES_IN+1000）/100 from BCOL_REG_ACCT 2>where BYTES_IN>1000;

查询出BYTES_IN大于100*10即1000且节点号等于10的LOGIN_NAME空格加上NODE_ID加上两个空格再加上（BYTES_IN+1000）/100的值：

sql>select LOGIN_NAME||' '||NODE_ID||' '||（BYTES_IN+1000）/100 2>from BCOL_REG_ACCT where BYTES_IN>100*10 and node_id=10; （3） 使用特殊的伪列

查询1周以后的日期从一个特殊的系统表dual。伪列主要有如下几种：

sequence.CURRVAL:上一次有序列产生器产生的序列名值。只有当前实例会话中从该序列中选择过一次值，这个伪列才会有效。

LEVEL：查询的深度，适用于特殊的树查询。 Sequence.NEXTVAL：使得每次的检索都返回下一个值

ROWID：表示数据行确切的存储位置。其格式是一个三个16进制的结构AAAAAAAA.BBBB .CCCC，AAAAAAA为该行的块 号，BBBB是数据块内部的行号，而CCCC则是数据库中的文件ID

ROWNUM：被检索数据行的序列号 SYSDATE：当前日期和时间 UID：当前用户的标识ID USER：用户登录数据库的用户名 sql>select sysdate + 7 from dual;

sql>select USER.sequence.currval from dual;

（4） 使用列和表的别名、查询中的多表连接以及使用子查询和distinct去掉重复记录查询

查询出BCOL_REG_ACCT表中的LOGIN_NAME，LOGIN_DATE，CONNECT_ DUR列和ANODE_NODEINFO中的NODE_ID列，符合 两个表中的NODE_ID列相等并且LOGIN_NAME在表AUSR_ACCOUNTINFO的列LOGIN_NAME中且名字以abc开头且没有重名的条 件，按照ANODE_NODEINFO表中的NODE_ID排序：

sql>select b.LOGIN_NAME，b.LOGIN_DATE ，c.NODE_ID，b.CONNECT_DUR

2>from BCOL_REG_ACCT b，ANODE_ NODEINFO c 3>where b.NODE_ID=c.NODE_ID 4>and b.LOGIN_NAME in

5>（select distinct LOGIN_NAME from AUSR_ACCOUNTINFO 6>where LOGIN_NAME like 'abc%'） 7>order by c.NODE_ID;

（5） Union、Intersect和Minus等操作符连接的多步查询

14

oracle 数据库基础

查询出两各表中满足各自条件的用户名，UNION即两个查询的并集： sql>select LOGIN_NAME from BCOL_ REG_ACCT 2>where CONNECTION_DUR>5000 3>and LOGIN_NAME like 'abc%' 4>UNION

5>select LOGIN_NAME from AUSR_ACCOUNTINFO 6>where LOGIN_NAME like '?c%' 7>and NODE_ID=8;

类似地，INTERSECT返回出现在所有单个查询结果中都存在的数据行（即两个查询结果的交集）；MINUS返回只出现在

第一个查询结果中且不出现在第二个查询结果中的数据行（即两个查询的差集）。 （6） 复杂查询

1） 递归查询 构造树的递归查询

树查询是在标准的select语句中增加两个新子句完成。

CONNECT BY子句定义表中的各个行是如何互相联系的，connect by子句中也可以加入更多的条件语句来删除没有必 要的树的分枝。

START WITH子句定义数据行查询的初始起点。可以定义不同的起点来实现向上或者向下浏览。

以下例子connect by 指定半价时间必须等于前面已经选择过的连接时长。Start with子句指定从用户名为abc的用户开始。

Sql>select LOGIN_NAME，CONNECT_DUR， DISCOUNT_DUR 2>from BCOL_REG_ACCT

3>connect by DISCOUNT_DUR=prior CONNECT_DUR 4>start with LOGIN_NAME='abc'; 2） 外连接查询

外连接即是在被连接的表中数据中加入一个空行来匹配没有与其匹配的数据，以下例子中第三行的（+）即是外连接查 询，将没有匹配的行加入一行空行。

sql>select b.LOGIN_NAME，a.NODE_ID

2>from BCOL_REG_ACCT b， ANODE_NODEINFO a 3>where b.NODE_ID（+）=a.NODE_ID 4>order by b.LOGIN_NAME，a.NODE_ID; 3）相关子查询

相关子查询就是在其where条件中引用了主查询中表的子查询： a. 单行条件的相关子查询，以下例子来实现单行子查询的功能：

15

oracle 数据库基础

sql>select a.NODE_ID，b.LOGIN_NAME，

2>nvl（to_char（b.LOGIN_DATE，'DD-MON-YY'），'UNKNOWN'） 3>from AUSR_ACCOUNTINFO a，BCOL_REG_ACCT b 4>where b.NODE_ID（+）=a.NODE_ID 5>and （b.LOGIN_DATE is NULL or

6>b.LOGIN_DATE = （select max （c.LOGIN_DATE） from BCOL_REG_ACCT c 7>where c.NODE_ID=a.NODE_ID）） 8>and a.NODE_ID=

9>（select NODE_ID from ANODE_ NODEINFO where ANODE_NAME=aaa'）;

b. EXISTS和NOT EXISTS条件判断的相关子查询: 以下例子为以EXISTS判断的相关子查询： sql>select a.NODE_ID from ANODE_NODEINFO a 2>where exists （select 'x' from BCOL_ REG_ACCT b 3> where a.NODE_ID=b.NODE_ID 4> and b.CONNECT_ DUR=500） 5>order by a.NODE_ID;

以下例子是以NOT EXISTS判断的相关子查询，在涉及到子查询的条件时，最好用NOT EXISTS判断代替not in判断：

sql>select a.NODE_ID from ANODE_ NODEINFO a 2>where not exists （select 'x' from BCOL_REG_ACCT b 3> where a.NODE_ID=b.NODE_ID 4> and b.CONNECT_DUR!=500） 5>order by a.NODE_ID;

（insert、update、delete语法参考关系数据库SQL语言）

第四部分 Oracle数据库备份与恢复

一、数据库备份

（1） 数据备份形式分为以下两种

1） 实体数据备份：直接备份操作系统数据库文件，通过将数据库文件备份到其它设备如磁带机或磁盘，获得安全保障，需要备份的文件一般包括： 数据文件、联机重做日志文件、数据库控制文件、脱机交易日志文件

16

oracle 数据库基础

2） 逻辑数据备份：指备份数据库系统的逻辑对象，包括对象的定义与其所存储的数据内容，ORACLE提供两个命令：exp和imp实现数据逻辑备份 可以用 exp help=y imp help=y来查看其用法 （2）数据库分为归档日志执行模式和非归档日志执行模式

? 在归档日志执行模式下：数据库后台进程ARCH在每次【Log Switch】事件发生

时，将刚填满的联机重做日志文件备份到disk或tape中成为脱机交易记录文件，当需要恢复数据时，只需要将最近一次的数据库文件的全备份再加上这些脱机重做日志文件即可恢复到问题发生的时间点

? 在非归档日志执行模式下：数据库系统循环使用一组联机重做日志文件（online

redo log files），所以只能保存近期的交易记录，这样的话，要进行数据恢复时，只能恢复近期所做的交易，这样就要求数据经常关闭做数据库全备份，否则数据库恢复就无法进行。

通过以下命令查看数据库当前模式: SVRMGR>archive log list

在Database log mode一栏显示:[No] Archive Mode 从非归档日志执行模式到归档日志模式的切换方式:

1) 修改启动参数文件，设置如下项 log_archive_start=true （启动ARCH后台进程）

log_archive_dest=/vol4/arch/arch （设置存放脱机交易记录文件的位置及文件名开头） log_archive_format=-%s.log （设置脱机交易记录文件的后段文件名，%s代表记录文件的?log sequence number?）

2) 按以下步骤重启数据库并修改数据库设置 SVRMGR>connect internal; SVRMGR>shutdown; SVRMGR>startup mount;

SVRMGR>alter database archivelog; SVRMGR>alter database open;

用：SVRMGR>archive log list; 再次查看当前归档日志的设置模式 （3）数据库备份方法

数据库备份方法可以分为脱机执行数据库备份和联机执行数据库备份 1) 脱机执行数据库整体备份

当数据库shutdown normal或shutdown immediate指令正常关闭数据库系统时，系统触发一次检查点:checkpoint事件，并实现数据文件与联机重做日志文件的写入，并将该时间点同时记录在所有数据文件、联机重做日志文件和控制文件中，所以如果等数据库停止后

17

oracle 数据库基础

保留所有文件的一个拷贝，就可以利用这份拷贝将整个数据库恢复到该时间点。 备份方法：

先用以下语句查看所有需要备份的文件：

SVRMGR> select * from v$datafile; （所有数据文件） SVRMGR> select * from v$controlfile（所有控制文件） SVRMGR> select * from v$logfile; （所有联机重做日志文件）

记录下所有的这些文件的路径和文件名，同时连同参数文件和密码文件一起备份到disk或tape

2) 联机执行数据库备份

在数据库不能经常shutdown的情况下，数据库处于Archivelog Mode 执行模式下才能够联机数据备份，对数据文件进行联机备份时，数据库系统必须暂停对数据文件的写入，但为保证交易可以正常进行，内存使用必然加重，所以为减少数据库系统的负担，在执行联机数据库备份时，Oracle只允许以Tablespace为单位一个个逐步进行。

? 在线备份数据文件

首先先了解要备份的表空间包含的数据文件有那些，通过以下命令确定： SVRMGR>select tablespace_name，file_name from dba_data_files;

备份一个表空间时，需要将该表空间的所有数据文件全部备份出来，否则毫无意义。 步骤:

SVRMGR>alter tablespace tablespace_name begin backup; （先将要备份的表空间设置成Backup Mode）

在操作系统环境下，将此tablespace 所属的所有数据文件通过拷贝命令复制到备份介质上。

SVRMGR>alter tablespace tablespace_name end backup; （备份完数据文件后，结束表空间的Backup Mode状态）

重复上述过程完成所有的表空间备份。千万注意备份的时间要尽量短，实际应用中可以写shell脚本将备份执行过程一气呵成。

? 在线备份控制文件

控制文件中包含了存储整个数据库所需的各个文件信息以及整个数据库结构等重要信息，数据库依据控制文件信息来操作整个数据库，所以需要进行控制文件的备份，除了同时设置多个控制文件以满足安全保证外，在每次修改完数据库结构以后，数据库管理员最好立刻执行一次控制文件的在线备份:

? 自行指定目录文件名，备份一个完全相同的控制文件:

SVRMGR>alter database backup controlfile >to 【/vol4/dow0/control.bak;

? 产生一个可重新生成控制文件的SQL程序文件，并置于存放Trace-file

18

oracle 数据库基础

的相同目录下，使用系统的内定文件名:

SVRMGR>alter database backup controlfile to trace;

所产生的SQL程序文件其文件名称如 “ zxin_ora_916.trc”，其中zxin为数据库名称，916为执行此Backup操作的服务进程ID号。

二、数据库恢复

启动数据库系统时，如果控制文件的Current Log Sequence Number与任一数据库的数据文件的Log Sequence Number不同的话，比那时该数据文件处于不同步状态，此时，数据库管理员必须使用以前备份的数据文件和重做日志文件，对数据库进行恢复，使数据库恢复到同步状态。 （1） 数据恢复过程：

整个数据库的数据恢复过程可以分为3个阶段进行： 1） 使用备份数据文件恢复损坏的数据文件 2） 自备份文件的时间点开始重做交易 3） 回滚未被提交的交易

数据库恢复可以分为完全恢复和不完全恢复，完全恢复可以利用数据备份文件、脱机重做日志文件和联机重做日志文件将数据恢复到数据库出问题的时间点，不完全恢复是由于脱机重做日志文件或联机重做日志文件中的某个关键文件被毁坏从而只能恢复到以前的某个时间点。 （2） 数据恢复方式：

1） 无归档日志的数据库恢复方式

无归档日志的数据库恢复方式是用于从数据库关闭做完全备份到数据库重新打开而在某个时间点数据库出现问题的这个时间段的的数据损失是可以接受的，或可以通过其他方式重做，而采用的数据恢复方式，其恢复方法也相对简单，恢复的时间也比较快，只要将最近一次执行全备份的所有文件拷贝回来即可。

执行步骤：

SVRMGR>shutdown abort（将数据库实例先关闭）

将上一次全备份的所有数据库文件（控制文件、重做日志文件、数据文件）全部恢复到原有目录，如果参数文件和密码文件未被破坏，则可不必恢复

SVRMGR>connect internal SVRMGR>startup

数据库重起以后即恢复正常使用。

如果无法将数据文件恢复到原来目录位置，可将其放置于其他目录下，只要在开放数

19

oracle 数据库基础

据库系统使用前，通过以下方式修改控制文件中的设置即可:

SVRMGR>startup mount

SVGMGR>alter database rename file

2>?/vol2/ncm.dat? to ?/vol4/ncm.dat?; SVRMGR>alter database open;

2） 有归档日志的数据库完全恢复方式

完全恢复可将数据库完全恢复到目前的问题发生时间点，完全恢复可以针对整个数据库或单独的表空间、数据文件，一般来说，应该优先考虑使用最简单而且不容易出问题的Full Database Recovery。只有在不能Shutdown数据库的情况下，才需要考虑执行Tablespace Recovery 与 Datafile Recovery

Full Database Recovery可以解决所有表空间和数据文件的问题，操作简单且不容易出错，但只能在mount启动模式下进行，因此得先Shutdown整个数据库。执行步骤如下：

SVRMGR>shutdown SVRMGR>connect internal SVRMGR>startup mount

SVRMGR>set autorecovery on #设置以默认选项回答 SVRMGR>recover database; #开始执行恢复 SVRMGR>alter database open; #启动数据库到打开状态

由于Full Database Recovery需要关闭数据库，为减少关闭数据库所带来的冲击，数据库管理员可以同时启动多个进程来进行恢复，可以在参数文件中设置：

parallel_max_servers=20 #Server最多启动20个process recovery_parallelism=5 #Recovery Process默认5个 或者通过执行：

SVRMGR>recover database parallel （degree 5）;

当数据库必须维持联机状态无法shutdown的话，无法选择执行过程较为简单的Full Database Recovery，于是可以对单独的表空间或数据文件进行恢复，恢复步骤如下： SVRMGR>alter tablespace tablespace_name offline immediate; #将需要恢复的表空间脱机，并在指令中加上immediate避免checkpoint操作

用备份文件替代损坏的数据文件

将恢复过程所需的Archive Log File保存回原来设置的目录位置 执行整个表空间恢复：

SVRMGR>recover tablespace tablespace_name; 也可以恢复表空间的一个或多个数据文件： SVRMGR>recover datafile ?/vol2/datafile.dbf?; 使恢复完成的表空间联机：

20

oracle 数据库基础

SVRMGR>alter tablespace tablespace_name online; 3） 有归档日志的数据库不完全恢复方式

如果在完全恢复过程中发生问题的话，数据库管理员只能将数据库恢复到过去的某一个时间点，成为不完全恢复。 执行不完全恢复的注意事项

? 由于不完全恢复操作过程较为复杂，为保险起见，数据管理员应先在关闭数

据库的状态下做一次完全脱机备份后再开始

? 如果恢复到的时间点时刻的数据库结构和当前的不一样，则需要使用当时的

控制文件来做恢复

? 执行完不完全备份以后，为使现有的控制文件中的Log Sequence Number与

所有的Datafiles一致，必须执行alter database open resetlogs指令来打开数据库，将所有相关文件的Log Sequence Number重设为１

? Log Sequence Number 重设为１后，以前所有的Archive Log File都将失效，

所以应立即shutdown整个数据库再做一次完全数据库脱机备份。 执行不完全恢复的原因：

? 用户错误删除数据或Drop掉某个表，为救回数据，只要将数据恢复到错误发

生以前

? 执行完全恢复的过程中因某个重做日志文件坏掉而无法完成，数据库管理员

只要讲数据库恢复到前一个正常的重做日志文件的起点 ? 联机使用的所有控制文件都不可用时 ? 一个含有活动回滚段的数据文件毁坏时 ? 必须使用备份的控制文件来进行恢复时 执行不完全恢复的方法：

? Cancel-Based Recovery：恢复过程中当进行到过去的某个重做日志文件的交

易完成之后，在交互问答中敲入【CANCEL】命令结束恢复： SVRMGR>connect internal SVRMGR>startup mount

SVRMGR>recover database until cancle; 或者指定一个控制文件： SVRMGR>recover database until cancel

2>uning backup file ?/vol1/control01?;

在接下去的交互式问答过程中，依次执行重做日志文件的交易，直到用户打入 【CALCEL】时结束不完全恢复

恢复完以后用以下指令同步控制文件后数据文件的：Log Sequence Number SVRMGR>alter database open resetlogs;\\

? Time-Based Recovery：使恢复进行到过去的某个指定的时间点：

21

oracle 数据库基础

SVRMGR>connect internal SVRMGR>startup mount SVRMGR>set autorecovery on;

SVRMGR>recover database until time ?2001-08-27:18:27:08? 2>uning backup file ?/vol1/control01?;

恢复完以后用以下指令同步控制文件后数据文件的：Log Sequence Number

SVRMGR>alter database open resetlogs;\\

? Change-Based Recovery：使恢复进行到过去某个特别指定System Change

Number时间点便结束恢复，由于极少用到，所以不再详细介绍。

第五部分 Oracle数据库SQL*Net

SQL*Net是基于Oracle特有的Transparent Network Substrate（简称TNS）技术发展出来的网络存取中介软件，使用不同的网络通讯协议（如TCP/IP，SPX/IPX与DECNet等），SQL*Net也可以处理不同的数据格式和国家语言字码间的转换。 客户/服务器结构系统配置

在客户/服务器结构下，通信的两端不论是客户端或服务器端都需配置SQL*Net中介软件，服务器端在数据库主机上还需启动一个侦听程序（listener），以便能够不断侦听所有来自网络各地的客户端联机请求，侦听程序处理客户端联机请求的做法可以有以下类型：

? 专用服务器

当侦听程序受到一个客户端联机请求时，侦听程序新启动一个专用服务器程序负责服务用户程序的各项要求，并将执行结果传回给用户程序。整个通信过程，专用服务器程序与用户程序通过SQL*Net来对话。 ? 多线程服务器

当侦听程序受到一个客户端联机请求时，侦听程序将用户程序连接到一个已预先启动的共享调度程序（Shared Dispatcher）上，此共享调度程序负责接受用户程序的各项要求，并将之放入要求队列，共享服务器从队列中取出数据加以执行，然后将执行结果放至响应队列中，同享调度程序再自响应队列中取得数据后传回给客户端，整个通信过程，专用服务器程序与用户程序通过SQL*Net来对话。

侦听程序配置： 侦听程序相关文件包括：

$ORACLE_HOME/bin/lsnrctl 侦听程序文件 $ORACLE_HOME/network/admin/listener.ora

22

侦听程序组态设置文件

oracle 数据库基础

$ORACLE_HOME/network/log/listener.log ? 启动侦听程序： $lsnrctl start ? 侦听程序状态： $lsnrctl status ? 终止侦听程序： $lsnrctl stop

组态文件设置： 组态文件名称 Listener.ora Tnsnames.ora 设置内容说明 侦听程序执行记录文件 客户端 服务器端 ● 描述数据库主机所有侦听程序的名称与网址以及数据库SID 描述客户端会连上的每一个目标数据库位置，并各配以一专用之服务名称，以便在联机时指明 ● ● Sqlnet.ora 设置SQL*Net执行时诊断错误用的选项参数，若只用到默认值的话可以不需要该组态文件 ● ● 设置listener.ora组态文件 同一台主机所启动的各个listener共享同一个listener.ora组态文件，该组态文件的设置包括以下几个部分： 1） listener名称

2） listener的侦听对象地址:这里需要列出与listener进行通讯的所有对象，包括使用进程间调用（IPC）的本地客户端应用程序与ORACLE数据库的共享调度程序（Shared Dispatcher）、使用TCP/IP、SPX/IPX或DECNet的远程客户端应用程序等

3） 使用这个listener的数据库:必须列出这台主机上所有共享此listener的数据库SID

listener.ora组态文件设置范例：

LISTENER1=（ADDRESS_LIST=（ADDRESS=（PROTOCOL=IPC）

（KEY=ZXIN_IPC））#ZXIN_IPC为本地用户所用的Service Name（ADDRESS=（PROTOCOL=IPC）（KEY=ZXIN））#ZXIN为数据库SID，给Dispatcher用（ADDRESS=（PROTOCOL=TCP）（HOST=H80_1）（PORT=1521）#供远程的TCP/IP网络用户联机使用）

SID_LIST_LISTENER1=#定义这台主机中使用的（SID_LIST= LISTENER1数据库

有哪些（SID_DESC=（SID_NAME=ZXIN）（ORACLE_HOME=/home/oracle/orahome）

设置tnsnames.ora组态文件

tnsnames.ora组态文件只需要用于客户端，设置包括两个部分：

23

oracle 数据库基础

1） Service名称：设置一个联机模式的代表名称，当远程用户或服务器程序与数据库联机时，就可以简单指出Service名称即可

2）联机模式数据：包括远程数据库的listener地址与数据库SID，分别写在ADDRESS与CONNECT_DATA参数中

tnsnames.ora组态文件设置范例： ZXIN_IPC=

（

DESCRIPTION=

（

ADDRESS=

（

PROTOCOL=IPC

）

（KEY=ZXIN_IPC）） #配合listener.ora中的定义（CONNECT_DATA=（SID=ZXIN）） #定义联机数据库的SID）

ZXIN=（DESCRIPTION=（ADDRESS=（PROTOCOL=TCP）（HOST=H80_2）

（PORT=1521）） #定义联机主机地址和端口号（CONNECT_DATA=（SID=ZXIN））#定义联机数据库的SID）

ZX192_200_1_133=（DESCRIPTION=（ADDRESS=（PROTOCOL=TCP）

（HOST=H80_2）（PORT=1521）） #定义联机主机地址和端口号（CONNECT_DATA=（SID=ZXIN））#定义联机数据库的SID）

客户端联机范例：

$sqlplus zxinconfig/zxinconfig@zxin_ipc $sqlplus zxinconfig/zxinconfig@zxin

$sqlplus zxinconfig/zxinconfig@zx192_200_1_133

第六部分 DBA常用查询指令

dba常用指令列表：

显示所有参数极其设置值 Show parameter 或 Select * from v$parameter; 显示所有NLS参数可设置的值 显示所有参数中有】dest】字符串的参数极其设置值 显示SGA各部分设置大小 Select * from v$nls_valid_values; Show parameter dest Show sga 或 Select * from v$sga; Select file_name，tablespace_name， bytes from dba_data_files; Select status，enabled，name from v$datafile; Instance Parameter 显示所有数据文件极其大小 Data File 显示所有数据文件的状态 24

oracle 数据库基础

显示所有Redo Log File的状态 Select group#，status，first_time from v$log; Select group#，member from Redo Log File 显示所有Redo Log File的名称 v$logfile; Select username，default_tablespace， temporary_tablespace from dba_users; User 显示所有User的相关数据 或 select tablespace_name，username，bytes， max_bytes from dba_ts_quotas; Select username，process from v$session; 或 select username，default_tablespace， 显示所有联机User的相关数据 temporary_tablespace from user_users; 或 select tablespace_name，username，bytes， max_bytes from user_ts_quotas; Profile 显示所有User分配给的Profile 显示用户目前使用系统资源的各项限制 显示目前数据库所有Profile与其设置 显示授权给Users及Roles的系统特权 显示授权给用户的角色 Select username，profile from dba_users; Select resource_name，limit from User_resource_limits; Select * from dba_profile; Select * from dba_sys_privs; Select * from user_role_privs; Select * from session_roles; Select * from dba_roles; Role 显示用户当前会话开启的角色 显示数据库中以建立的角色信息 显示数据库中所有角色被授予的系统特权 Select * from role_sys_privs; 25

oracle 数据库基础

显示数据库中所有角色被授予的表对象特权 显示数据库中将ROLE 特权授予另一角色的信息 显示所有表空间状态 Select * from role_tab_privs; Select * from role_role_privs; Select tablespace_name， Status，contents from dba_tablespaces; Select tabkespace_name，file_id Block_id，bytes，blocks from Dba_free_space; Select segment_name，Tablespace 显示所有表空间Fragments与剩余空间的大小 Segment 显示所有Rollback Segment的相关数据 tablespace_name， Status from dba_rollback_segs; Select initial_extent，next_extent， Segment 显示某个Segment （EMP）的Storage Parameters的相关数据 min_extentspct_increase from ，max_extents，dba_segments where segment_name=?EMP? 显示某个Segment（EMP）所使Select extent_id，bytes from Extent 用的Extent的相关数据 dba_extents Where segment_name=?EMP?; Select * from dba_free_space where Tablespace_name=?zxin?; Select constraint_name，Free Space 显示某个表空间对象（zxin）内使用的Extent 的相关空间数据 显示某个表对象（tb_zxinaplist） constraint_type， Status from dba_constraints where Table_name=?tb_zxinaplist?; Select column_name，constraint_name From dba_cons_columns where Table_name=?tb_zxinaplist?; Select distinct privilege from Constraint 内相关的Constraints设置数据 显示某个表对象（tb_zxinaplist） 内所设置Constraints与Column Privilege 显示ORACLE数据库提供的系统特权有哪些 查看自己被授予的系统特权有哪些 dba_sys_privs; Select * from user_sys_privs; 26

oracle 数据库基础

第七部分 关系数据库SQL语言

一．SQL数据库的体系结构：

? 用户可以用SQL语言的语句对视图（View）和基本表（Base table）进行查询等

操作。在用户看来，视图和基本表一样，都是关系（表格）。

? 视图是从一个或几个基本表导出的表，它本身不独立存储在数据库中，因此，视

图也称为虚表。

? 基本表是独立存在的表，每个基本表对应一个存储文件。 ? 每个存储文件对应外部存储器上一个物理文件。

二．SQL数据定义功能

（1） 基本表的建立、扩充和撤消

? 基本表的建立

CREATE TABLE S（ SNO CHAR（4） NOT NULL，SNAME CHAR（10）， AGE SMALLINT，SEX CHAR（1） ）；

? 基本表的扩充

ALTER TABLE S ADD （ADDR CHAR（10） ）；

? 基本表的撤消 DROP TABLE S； （2） 视图的建立、撤消

视图是从一个或几个基本表（或视图）导出的表。

? 视图的建立

CREATE VIEW STUDENT-GRADE AS SELE CT S.SNO， SNAME ，CNAME，GRADE FROM S， SC ， C WHERE S.SNO=SC.SNO;

? 视图的撤消

DROP VIEW STUDENT-GRADE （3）索引的建立、撤消

? 索引的建立

CREATE INDEX mycolumn_index ON mytable （myclumn）

这个语句建立了一个名为mycolumn_index的索引。可以给一个索引起任何名字，但应该在索引名中包含所索引的字段名，这对将来弄清楚建立该索引的意图是有帮助的。

? 索引的撤消

27

oracle 数据库基础

DROP INDEX mycolumn_index

三．SQL数据操纵功能

在基本表和视图用CREATE语句后，用户就可以使用SQL数据操纵语句使用数据库。对数据库的使用包括四种操作：查询、插入、删除、修改。查询操作称为“检索”，后三种操作也称为“更新”操作。

1. SELECT 创建记录和表

SQL查询的句法非常简单。假设有一个名为email_table 的表，包含名字和地址两个字段，要得到Bill Gates 的e_mail地址，可以使用下面的查询： SELECT email from email_table WHERE name=\

当这个查询执行时，就从名为email_table的表中读取Bill Gates的e_mail 地址。这个简单的语句包括三部分：

? SELECT语句的第一部分指名要选取的列。在此例中，只有email列被选取。当

执行 时，只显示email列的值 billg@microsoft.com。

? SELECTT语句的第二部份指明要从哪个（些）表中查询数据。在此例中，要查

询的表名为email_table 。

? 最后，SELECT语句的WHERE子句指明要选择满足什么条件的记录。在此例

中，查询条件为只有name列的值为Bill Gates 的记录才被选取。

Bill Gates很有可能拥有不止一个email地址。如果表中包含Bill Gates的多个email地址。用上述的SELECT语句可以读取他所有的email地址。SELECT语句从表中取出所有name字段值为Bill Gates 的记录的email 字段的值。

前面说过，查询可以在查询条件中包含逻辑运算符。假如想读取Bill Gates 或Clinton总统的所有email地址，可以使用下面的查询语句：

SELECT email FROM email_table WHERE name=\Clinton\

此例中的查询条件比前一个复杂了一点。这个语句从表email_table中选出所有name列为Bill Gates或president Clinton的记录。如果表中含有Bill Gates或president Clinton的多个地址，所有的地址都被读取。

SELECT语句的结构看起来很直观。如果我们一个朋友从一个表中为你选择一组记录，我们也许以非常相似的方式提出你的要求。在SQL SELECT语句中，“SELECT特定的列FROM一个表WHERE某些列满足一个特定的条件”。

28

oracle 数据库基础

你所执行的SELECT语句从表authors中取出所有名字为Ringer的作者的电话号码。你通过在WHERE子句中使用特殊的选择条件来限制查询的结果。你也可以忽略选择条件，从表中取出所有作者的电话号码。要做到这一点，单击Query标签，返回到查询窗口，输入以下的SELECT语句： ELECT Phone FROM authors

这个查询执行后，会取出表authors中的所有电话号码（没有特定的顺序）。如果表authors中包含一百个电话号码，会有一百个记录被取出，如果表中有十亿个电话号码，这十亿条记录都会被取出（这也许需要一些时间）。

表authrs的字段包括姓，名字，电话号码，地址，城市，州和邮政编码。通过在SELECT语句的第一部份指定它们，你可以从表中取出任何一个字段。你可以在一个SELECT语句中一次取出多个字段，比如：

SELECT au_fname ，au_lname， phone FROM authors

这个SELECT语句执行后，将取出这三个列的所有值。下面是这个查询的结果的一个示例（为了节省纸张，只显示查询结果的一部分，其余记录用省略号代替）： au_fname au_lname phone

…………………………………………………………………………. Johnson White 408 496_7223 Marjorie Green 415 986_7020 Cheryl Carson 415 548_7723 Michael O】Leary 408 286_2428 …

（23 row（s） affected）

在SELECT语句中，你需要列出多少个字段，你就可以列出多少。不要忘了把字段名用逗号隔开。你也可以用星号（*）从一个表中取出所有的字段。这里有一个使用星号的例子：

SELECT * FROM authors

这个SELECT语句执行后，表中的所有字段的值都被取出。你会发现你将在SQL查询中频繁使用星号。 （1） 操作多个表

到现在为止，你只尝试了用一句SQL查询从一个表中取出数据。你也可以用一个SELECT语句同时从多个表中取出数据，只需在SELECT语句的FROM从句中列出要从中取出数据的表名称即可：

SELECT au_lname ，title FROM authors， titles

这个SELECT语句执行时，同时从表authors和表titles中取出数据。从表authors中取出所有的作者名字，从表titles中取出所有的书名。在ISQL/w程序中执行这个查询，看一

29

oracle 数据库基础

下查询结果。你会发现一些奇怪的出乎意料的情况：作者的名字并没有和它们所著的书相匹配，而是出现了作者名字和书名的所有可能的组合，这也许不是你所希望见到的。 出了什么差错？问题在于你没有指明这两个表之间的关系。你没有通过任何方式告诉SQL如何把表和表关联在一起。由于不知道如何关联两个表，服务器只能简单地返回取自两个表中的记录的所有可能组合。

要从两个表中选出有意义的记录组合，你需要通过建立两表中字段的关系来关联两个表。要做到这一点的途径之一是创建第三个表，专门用来描述另外两个表的字段之间的关系。

表authors有一个名为au_id的字段，包含有每个作者的唯一标识。表titles有一个名为title_id的字段，包含每个书名的唯一标识。如果你能在字段au_id和字段title_id 之间建立一个关系，你就可以关联这两个表。数据库pubs中有一个名为titleauthor的表，正是用来完成这个工作。表中的每个记录包括两个字段，用来把表titles和表authors关联在一起。下面的SELECT语句使用了这三个表以得到正确的结果： SELECT au_name，title FROM authors，titles，titleauthor WHERE authors.au_id=titleauthor.au_id AND titles.title_id=titleauthor.title_id

当这个SELECT语句执行时，每个作者都将与正确的书名相匹配。表titleauthor指明了表authors和表titles的关系，它通过包含分别来自两个表的各一个字段实现这一点。第三个表的唯一目的是在另外两个表的字段之间建立关系。它本身不包含任何附加数据。 注意在这个例子中字段名是如何书写的。为了区别表authors和表titles中相同的字段名au_id，每个字段名前面都加上了表名前缀和一个句号。名为author.au_id 的字段属于表authors，名为titleauthor.au_id的字段属于表titleauthor，两者不会混淆。

通过使用第三个表，你可以在两个表的字段之间建立各种类型的关系。例如，一个作者也许写了许多不同的书，或者一本书也许由许多不同的作者共同完成。当两个表的字段之间有这种“多对多”的关系时，你需要使用第三个表来指明这种关系。

但是，在许多情况下，两个表之间的关系并不复杂。比如你需要指明表titles和表publishers之间的关系。因为一个书名不可能与多个出版商相匹配，你不需要通过第三个表来指明这两个表之间的关系。要指明表titles和表publishers之间的关系，你只要让这两个表有一个公共的字段就可以了。在数据库pubs中，表titles和表publishers都有一个名为pub_id的字段。如果你想得到书名及其出版商的一个列表，你可以使用如下的语句： SELECT title，pub_name FROM titles，publishers WHERE titles.pub_id=publishers.pub_id

30

oracle 数据库基础

当然，如果一本书是由两个出版商联合出版的，那么你需要第三个表来代表这种关系。

通常，当你予先知道两个表的字段间存在“多对多”关系时，就使用第三个表来关联这两个表。反之，如果两个表的字段间只有“一对一”或“一对多”关系，你可以使用公共字段来关联它们。 （2） 操作字段

通常，当你从一个表中取出字段值时，该值与创建该表时所定义的字段名联系在一起。如果你从表authors中选择所有的作者名字，所有的值将会与字段名au_lname相联系。但是在某些情况下，你需要对字段名进行操作。在SELECT语句中，你可以在缺省字段名后面仅跟一个新名字来取代它。例如，可以用一个更直观易读的名字Author Last Name来代替字段名au_lname：

SELECT au_lname \

当这个SELECT语句执行时，来自字段au_lname的值会与“Author Last Name”相联系。查询结果可能是这样： Author Last Name

…………………………………………………………………….. White Green Carson O?Leary Straight …

（23 row（s） affected）

注意字段标题不再是au_lname，而是被Author Last Name所取代。

你也可以通过执行运算，来操作从一个表返回的字段值。例如，如果你想把表titles中的所有书的价格加倍，你可以使用下面的SELECT语句： SELECT price*2 FROM titles

当这个查询执行时，每本书的价格从表中取出时都会加倍。但是，通过这种途径操作字段不会改变存储在表中的书价。对字段的运算只会影响SELECT语句的输出，而不会影响表中的数据。为了同时显示书的原始价格和涨价后的新价格，你可以使用下面的查询： SELECT price \， price*2 \

当数据从表titles中取出时，原始价格显示在标题Original price下面，加倍后的价格显示在标题New price下面。结果可能是这样： original price new price

……………………………………………………………….

31

oracle 数据库基础

39.98

11.95 23.90 5.98 39.98 …

（18 row（s） affected）

你可以使用大多数标准的数学运算符来操作字段值，如加（+），减（-），乘（*）和除（/）。你也可以一次对多个字段进行运算，例如： SELECT price*ytd_sales \

在这个例子中，通过把价格与销售量相乘，计算出了每种书的总销售额。这个SELECT语句的结果将是这样的： total revenue

…………………………………………….. 81，859，05 46，318，20 55，978，78 81，859，05 40，619，68 …

（18 row（s） affected）

最后，你还可以使用连接运算符（它看起来像个加号）来连接两个字符型字段： SELECT au_fname+\

在这个例子中，你把字段au_fname和字段au_lname粘贴在一起，中间用一个逗号 隔开，并把查询结果的标题指定为author name。这个语句的执行结果将是这样的： author names

………………………………………………………… Johnson White Marjorie Green Cheryl Carson Michael O?Leary Dean Straight …

（23 row（s） affected）

可以看到，SQL为你提供了对查询结果的许多控制。你应该在ASP编程过程中充分利用这些优点。使用SQL来操作查询结果几乎总是比使用有同样作用的脚本效率更高。

32

oracle 数据库基础

（3）排序查询结果

SQL表没有内在的顺序， 例如，从一个表中取第二个记录是没有意义的。从SQL的角度看来，没有一个记录在任何其他记录之前。然而，你可以操纵一个SQL查询结果的顺序。在缺省情况下，当记录从表中取出时，记录不以特定的顺序出现。例如，当从表authors中取出字段au_lname时，查询结果显示成这样： au_lname

……………………………………. White Green Carson O?Leary Straight …

（23 row（s） affected）

看一列没有特定顺序的名字是很不方便的。如果把这些名字按字母顺序排列，读起来就会容易很多。通过使用ORDER BY子句，你可以强制一个查询结果按升序排列，就像这样：

SELECT au_lname FROM authors ORDER BY au_lname

当这个SELECT语句执行时，作者名字的显示将按字母顺序排列。ORDER BY子句将作者名字按升序排列。

你也可以同时对多个列使用ORDER BY子句。例如，如果你想同时按升序显示字段au_lname和字段au_fname，你需要对两个字段都进行排序：

SELECT au_lname，au_fname FROM authors ORDER BY au_lname ，au_fname 这个查询首先把结果按au_lname字段进行排序，然后按字段au_fname排序。记录将按如下的顺序取出：

au_lname au_fname

……………………………………………………………………. Bennet Abraham Ringer Albert Ringer Anne Smith Meander …

（23 row（s） affected）

你也许已经注意到，INSERT 语句与DELETE语句和UPDATE语句有一点不同，它一次只操作一个记录。然而，有一个方法可以使INSERT 语句一次添加多个记录。要做到这一点，你需要把INSERT 语句与SELECT 语句结合起来，象这样：

33

oracle 数据库基础

INSERT mytable （first_column，second_column） SELECT another_first，another_second FROM anothertable

WHERE another_first=?Copy Me!?

这个语句从anothertable拷贝记录到mytable只有表anothertable中字段another_first的值为?Copy Me！?的记录才被拷贝。

当为一个表中的记录建立备份时，这种形式的INSERT 语句是非常有用的。在删除一个表中的记录之前，你可以先用这种方法把它们拷贝到另一个表中。

如果你需要拷贝整个表，你可以使用SELECT INTO 语句。例如，下面的语句创建了一个名为newtable的新表，该表包含表mytable的所有数据：

SELECT * INTO newtable FROM mytable

你也可以指定只有特定的字段被用来创建这个新表。要做到这一点，只需在字段列表中指定你想要拷贝的字段。另外，你可以使用WHERE 子句来限制拷贝到新表中的记录。下面的例子只拷贝字段second_columnd的值等于?Copy Me!?的记录的first_column字段。 SELECT first_column INTO newtable FROM mytable WHERE second_column=?Copy Me!?

使用SQL修改已经建立的表是很困难的。例如，如果你向一个表中添加了一个字段，没有容易的办法来去除它。另外，如果你不小心把一个字段的数据类型给错了，你将没有办法改变它。但是，使用本节中讲述的SQL语句，你可以绕过这两个问题。

例如，假设你想从一个表中删除一个字段。使用SELECT INTO 语句，你可以创建该表的一个拷贝，但不包含要删除的字段。这使你既删除了该字段，又保留了不想删除的数据。

如果你想改变一个字段的数据类型，你可以创建一个包含正确数据类型字段的新表。创建好该表后，你就可以结合使用UPDATE语句和SELECT 语句，把原来表中的所有数据拷贝到新表中。通过这种方法，你既可以修改表的结构，又能保存原有的数据。

2.插入数据

向表中添加一个新记录，你要使用SQL INSERT 语句。这里有一个如何使用这种语句的例子：

INSERT mytable （mycolumn） VALUES （?some data?）

这个语句把字符串?some data?插入表mytable的mycolumn字段中。将要被插入数据的字段的名字在第一个括号中指定，实际的数据在第二个括号中给出。

INSERT 语句的完整句法如下：

INSERT [INTO] {table_name|view_name} [（column_list）] {DEFAULT VALUES |

34

oracle 数据库基础

Values_list | select_statement}

如果一个表有多个字段，通过把字段名和字段值用逗号隔开，你可以向所有的字段中插入数据。假设表mytable有三个字段irst_column，second_column，和third_column。下面的INSERT语句添加了一条三个字段都有值的完整记录： INSERT mytable （first_column，second_column，third_column） VALUES （?some data?，?some more data?，?yet more data?）

3.删除记录

要从表中删除一个或多个记录，需要使用SQL DELETE语句。可以给DELETE 语句提供WHERE 子句。WHERE子句用来选择要删除的记录。例如，下面的这个DELETE语句只删除字段first_column的值等于?Delete Me?的记录：

DELETE mytable WHERE first_column=?Deletet Me? DELETE 语句的完整句法如下：

DELETE [FROM] {table_name|view_name} [WHERE clause]

在SQL SELECT 语句中可以使用的任何条件都可以在DELECT 语句的WHERE子句 中使用。例如，下面的这个DELETE语句只删除那些first_column字段的值为?goodbye?或second_column字段的值为?so long?的记录：

DELETE mytable WHERE first_column=?goodby? OR second_column=?so long? 如果不给DELETE 语句提供WHERE 子句，表中的所有记录都将被删除。

4.更新记录

要修改表中已经存在的一条或多条记录，应使用SQL UPDATE语句。同DELETE语句一样，UPDATE语句可以使用WHERE子句来选择更新特定的记录。请看这个例子： UPDATE mytable SET first_column=?Updated!? WHERE second_column=?Update Me!? 这个UPDATE 语句更新所有second_column字段的值为?Update Me!?的记录。对所有被选中的记录，字段first_column的值被置为?Updated!?。 下面是UPDATE语句的完整句法：

UPDATE {table_name|view_name} SET [{table_name|view_name}] {column_list|variable_list|variable_and_column_list} [，{column_list2|variable_list2|variable_and_column_list2}… [，{column_listN|variable_listN|variable_and_column_listN}]] [WHERE clause]

35

oracle 数据库基础

5.集合函数介绍

（1） 统计字段值的数目

函数COUNT（）也许是最有用的集合函数。可以用这个函数来统计一个表中有多少条记录。这里有一个例子：

SELECT COUNT（au_lname） FROM authors

这个例子计算表authors中名字（last name）的数目。如果相同的名字出现了不止一次，该名字将会被计算多次。如果你想知道名字为某个特定值的作者有多少个，你可以使用WHERE子句，如下例所示：

SELECT COUNT（au_lname） FROM authors WHERE au_lname=?Ringer?

这个例子返回名字为?Ringer?的作者的数目。如果这个名字在表authors中出现了两次，则次函数的返回值是2。

假如想知道有不同名字的作者的数目。可以通过使用关键字DISTINCT来得到该数目。如下例所示：

SELECT COUNT（DISTINCT au_lname） FROM authors

如果名字?Ringer?出现了不止一次，它将只被计算一次。关键字DISTINCT 决定了只有互不相同的值才被计算。

通常，当使用COUNT（）时，字段中的空值将被忽略。一般来说，这正是所希望的。但是，如果仅仅想知道表中记录的数目，那么需要计算表中所有的记录，不管它是否包含空值。下面是一个如何做到这一点的例子：

SELECT COUNT（*） FROM authors

注意函数COUNT（）没有指定任何字段。这个语句计算表中所有记录所数目，包括有空值的记录。因此，你不需要指定要被计算的特定字段。 （2） 计算字段的平均值

使用函数COUNT（），你可以统计一个字段中有多少个值。但有时你需要计算这些值的平均值。使用函数AVG（），你可以返回一个字段中所有值的平均值。

假如你对你的站点进行一次较为复杂的民意调查。访问者可以在1到10之间投票，表示他们喜欢你站点的程度。你把投票结果保存在名为vote的INT型字段中。要计算你的用户投票的平均值，你需要使用函数AVG（）：

SELECT AVG（vote） FROM opinion

这个SELECT语句的返回值代表用户对你站点的平均喜欢程度。函数AVG（）只能对数值型字段使用。这个函数在计算平均值时也忽略空值。 （3）计算字段值的和

36

oracle 数据库基础

假设你的站点被用来出售卡片，已经运行了两个月，是该计算赚了多少钱的时候了。假设有一个名为orders的表用来记录所有访问者的定购信息。要计算所有定购量的总和，你可以使用函数SUM（）：

SELECT SUM（purchase_amount） FROM orders

函数SUM（）的返回值代表字段purchase_amount中所有值的平均值。字段purchase_amount的数据类型也许是MONEY型，但你也可以对其它数值型字段使用函数SUM（）。

（4）返回最大值或最小值

再一次假设你有一个表用来保存对你的站点进行民意调查的结果。访问者可以选择从1到10 的值来表示他们对你站点的评价。如果你想知道访问者对你站点的最高评价，你可以使用如下的语句：

SELECT MAX（vote） FROM opinion

你也许希望有人对你的站点给予了很高的评价。通过函数MAX（），你可以知道一个数值型字段的所有值中的最大值。如果有人对你的站点投了数字10，函数MAX（）将返回该值。

另一方面，假如你想知道访问者对你站点的的最低评价，你可以使用函数MIN（），如下例所示：

SELECT MIN（vote） FROM opinion

函数MIN（）返回一个字段的所有值中的最小值。如果字段是空的，函数MIN（）返回空值。

37

本文来源：https://www.bwwdw.com/article/z063.html