Oracle 从入门到精通

上一篇 / 下一篇 2014-05-29 10:43:31 / 个人分类：甲骨文

一、SQL
1.1、基本概念：
数据库对象：
1. 表
2. 约束条件：保证数据完整性。
3. 视图：虚表，命名的查询语句。
4. 索引：加速查询（加快查询的速度）。
5. 序列：一串连续递增或递减的数字，步长相同，（代理键）。
6. 同义词：一个对象的另外一个叫法（对象的别名）。
7. 存储过程：用于操作
8. 函数：用作复杂运算的。用于计算。
9. 触发器：由事件触发的存储过程。
10. 包
1.2、数据库安全：
1、用户
2、方案或模式(Schema)：是用户所对应的对象的集合。用户名等于方案名
3、权限
4、角色：权限组，一组权限。
5、配额(quota)：允许被使用的空间。用户可以在表空间上可以使用的空间。
端口：2030
环境变量
-ORACLE_BASE 基本目录
-ORACLE_NAME 当前的主目录
-ORACLE_NLS33 使用US7ASCLL 字符集时不用设
-PATH 路径
1.3、基本的SQL SELECT 语句
口令中的第一个字符不能为数字。
语句类型：
（一）查询：SELECT
数据操作语句：DML（数据的插入INSERT、删除DELETE、修改UPDATE、合并MERGE）
（二）合并：把一个表中的数据合并到另一个表中去，如果数据在原表中存在做UPDATE，否则INSERT
（9I 独有）。
（三）事务控制语句：COMMIT 提交、ROLLBACK 回滚、
SAVEPOINT 存储点（与 ROLLBACK 搭配使用）在回滚的时候可以回滚到某个存储点上。否则回滚到
最初起点上。
（四）数据定义语句：对对象操作。TRUNCATE 清除表中所有数据 /CREATE 创建 /DROP 删除 /ALTER
资源来自网络，仅供学习！ Oracle 从入门到精通
第 8 页，共 106 页
修改
（五）权限控制语句（DCL）：GRANT 授予权限 /REVOKE 移除权限
1.4、SELECT 语句
1、语法：
SELECT 查询列表 FROM 数据源；
*&* SQL 命令必须加分号。
ALTER USER HR IDENTIFIED BY HR ACCOUNT UNLOCK；
修改用户解锁
给HR 解锁
CONNECT(conn) HR/HR（密码）用HR 用户连接数据库。
*&*（不是SQL 命令是 SQLPLUS 命令）不用加分号
DESCRIBE(desc) DEPARTMENTS
关键字表名
描述表命令（SQLPLUS 命令）
2、SQL 语句说明：
（1）语句文本的书写不区分大小写。（但字符串在作为值的时候要注意大小写）
（2）语句可以写单行也可以写多行。
（3）关键字不能缩写或跨行。
（4）语句通常被分多行书写。
（5）缩进被用于提高语句的可读性。
3、数字和日期都可以使用数学运算符建立表达式。
+，-，*，/ <>不等于
日期可以加减数字，数字默认为天。
日期不能加日期，但日期可以减日期。
字符不能加减。
4、定义空（NULL）值
空值出现在表达式中会导致整个表达式的值为空。
NVL（字段名，将要赋予的值）函数
作用：将空值转换成其他有ASCLL 码的值。
annual_salary 年薪
5、别名
可以加中文的字段别名。
如果想强制地改变列名的大小写，可以在别名的定义时加上双引号,列名有空格时也要在列名上加双
引号。
例：select lastname as "employees name" from employees;
资源来自网络，仅供学习！ Oracle 从入门到精通
第 9 页，共 106 页
6、spool +路径
保存命令（将显示保存）
7、连接操作符： ||
select lastname || 'work in' || department_id from tablename;
select last_name || '''s salary is ' || salary 员工月薪 from employees;
~~~
注：在单引号中还要使用单引号的话，就必须使用两个单引号来实现一个单引号的功能。
8、文本字符串
*可以代表字符、数字或是日期。
*当代表字符或日期的时候用单引号括起来，数字不需要。
9、DISTINCT
在查询时默认显示所有的行，包括有重复值的行。
DISTINCT 消除重复行关键字，放在整查询列表的最前面。
作用范围：整个查询列表的组合。
消除重复行后会按字段的特性，做升序排列。（执行过程：先排序，再消除重复）
select distinct department_id,job_id from employees;
1.5、SQLPLUS 与 SQL 的关系
SQL *是一种语言
*ANSI 标准
*关键字不能缩写
*用于操作数据库中的数据和表的定义
1、SQLPLUS 命令的功能：
*描述表的结构
*编辑SQL 语句
2、查询 SQLPLUS 命令
help + 命令
3、SQLPLUSW 在 WINDOWS 下运行的分析器。
登陆ISQLPLUS
（1）先到服务中启动OracleOraHome92HTTPServer
（2）在浏览器中输入：http://wnj:7778/isqlplus
URL(网页中的地址)
资源来自网络，仅供学习！ Oracle 从入门到精通
第 10 页，共 106 页
4、SQLPLUS 命令：
4.1、与文件相关的命令：
4.1.1、 spool + 路径
.
.
spool off
4.1.2、save
把当前内存中的语句保存为文本文件。
4.1.3、run 或 /
运行当前内存中的语句
4.1.4、clear buffer（cl buff）
清空当前内存中的语句
4.1.5、start @ 读取并执行
4.1.6、get 读取不执行
4.2 编辑命令：
􀁺 List 列出一条语句
*表示当前行
􀁺 Change 修改命令
原来c/jj/kk
c/jjj/xxx
􀁺 input 在当前行之后插入一行新的数据
􀁺 append 在当前行中插入新的东西
n 写数字显示对应行
􀁺 delete
del + 回车删除当前行
del 1 3 删除第一到第三行
􀁺 edit l,c,i,a,n,d,e
􀁺 查看当前用户
SHOW USER
􀁺 默认的日期格式：DD-MON-RR 日-月-年
􀁺 日期可以进行比较；
􀁺 字符可以进行比较（以字母的 ASCLL 码比较）；
􀁺 IN（set）或NOT IN 匹配任何列表中的值；
􀁺 LIKE 模糊匹配字符串值；
􀁺 IS NULL 是否空值；
􀁺 IS NOT NULL 是否不为空；
􀁺 BETWEEN 可以做数字、日期和字符的比较。
􀁺 通配符 %、_
S_mith
WHERE first_name like 's/_%'escape'/';
解释这个符号后的下划线为正常的字符。如果不加，将被视为通配符作用的下划线。
'_'只能通配一个字符
主要用于通配固定位数的字符。例如查询月收入五位数以上的员工。五位就可以用'_'来查询。
资源来自网络，仅供学习！ Oracle 从入门到精通
第 11 页，共 106 页
􀁺 逻辑操作符（用在 WHERE 子句中）
1. AND
2. OR
3. NOT
先执行 NOT ，再执行 AND 最后执行 OR。
􀁺 ORDER BY
ORDER BY 子句在 SELECT 语句的最后。
􀁺 ASC：升序 DESC：降序
􀁺 空值作为无穷大来处理。
􀁺 rownum 显示行数量约束的关键字（在结果中可以做代理键使用）；
可以按照查询列表中序号进行排序。
系统在用户写出查询列表的同时就赋予每个列名一个序号，升序赋予。
例： SELECT name,phone,adress from.........;
1 2 3
1.6、单行函数
单行函数：对单行数据进行计算并返回一个值的函数。
*修改数据项
*接受参数返回一个值。
*对每行进行操作。
*每行返回一个结果。
*可以修改数据类型
*可以嵌套
1、character 字符类型函数：
􀁺 LOWER()强制小写
􀁺 UPPER()强制大写
􀁺 INITCAP()每个单词首字母大写,可以用在WHERE 子句中。
􀁺 CONCAT（‘’，‘’）连接函数
􀁺 SUBSTR(string,a[,b])返回string 的一部分，a 和b 以字符为单位。
􀁺 SUBSTRB(string,a[,b])返回string 的一部分，a 和b 是以字节为单位。
􀁺 SUBSTRC(string,a[,b])返回 string 的一部分，a 和b 是以UNICODE 完全字符为单位。
􀁺 SUBSTR2(string,a[,b])返回string 的一部分，a 和b 是以UCS2 代码点为单位。
􀁺 SUBSTR4(string,a[,b])返回string 的一部分，a 和b 是以UCS4 代码点为单位。
以上函数都是返回string 的一部分，从字符位置A 开始，长为B 个字符。如果A 是0，那它就
被认为是1（字符串的开始位置）。如果A 是正数，那么字符从左边开始数。如果是负数，则从STRING
的末尾开始，从右边数。如果B 不存在，那么缺省是整个字符串。如果B 小于1，将返回NULL。如
果A 或B 使用了浮点数，那么该数值首先被节取成一个整数，返回类型与STRING 相同。
􀁺 LENGTH(string)
􀁺 LENGTHB(string)
􀁺 LENGTHC(string)
􀁺 LENGTH2(string)
资源来自网络，仅供学习！ Oracle 从入门到精通
第 12 页，共 106 页
􀁺 LENGTH4(string)
以上函数返回string 的长度。因为CHAR 类型的值是填充空格的，所以如果string 是CHAR 数
据类型，那么末尾的空格算在长度之内。如果string 是NULL，函数返回NULL。
􀁺 INSTR(string1,string2[,a][,b]) 返回string1 中包含string2 的位置。a 和b 以字符为单位。
􀁺 INSTRB(string1,string2[,a][,b]) 返回string1 中包含string2 的位置。a 和b 是以字节为
单位。
􀁺 INSTRC(string1,string2[,a][,b])返回string1 中包含string2 的位置。a 和b 是以UNICODE
完全字符为单位。
􀁺 INSTR2(string1,string2[,a][,b]) 返回string1 中包含string2 的位置。a 和b 是以UCS2 代
码点为单位
􀁺 INSTR4(string1,string2[,a][,b]) 返回string1 中包含string2 的位置a 和b 是以UCS4 代码
点为单位。
以上函数返回string1 中包含string2 的位置。从左边开始扫描string1,起始位置是A。如果
A 为负数那么从右边开始扫描。第B 次出现的位置将被返回。A 和B 缺省都为1，即返回在string1
中第一次出现string2 的位置。如果string2 在A 和B 的规定下没有找到那么就返回0。位置的计
算是相对于string1 的开始位置的，而不关A 和B 的取值。
􀁺 LPAD（列名,数字,‘要补上的字符’）左补位
􀁺 RPAD（列名，数字，‘要补上的字符’）右补位
􀁺 TRIM（‘child_str’ FROM ‘parents_str’）将连续子串（只能有一个字符）从主串的两边截
取出来，区分大小写。默认为截取空格。
􀁺 LTRIM（）左截取
􀁺 RTRIM（）右截取
􀁺 ascii(x)函数，返回'X'字符的十进制数，即X 的ASCII 码值。
􀁺 chr(x)函数，返回ASCII 码为X 的字符。
􀁺 length(x)函数，求串X 的长度，与之相似的是lengthb(x)函数，用在多字节字符中。
􀁺 replace(x,y[,z])函数，返回值为将串X 中的Y 串用Z 串替换后的结果字符串。若省略Z 参数，
则将串X 中为Y 串的地方删除。
􀁺 soundex(x)函数，返回串 X 的语音描述，这个描述由4 个字符组成，说明串X 的声音表示形式
发音，有时在只知道一个名字的发音而不知道拼写情况下或许能用到。
例：select soundex('smith') from dual; 返回值为：S530.
􀁺 translate(x,y,z)函数，返回将X 串中每个字符按它在Y 串中出现的位置翻译成Z 串中相应位
置的字符后的结果，相当与替换。
例：select translate('this is an example','my is','@#$%^&') from dual;
􀁺 NLS 函数
除了NCHR，这些函数都是以字符类型为参数返回字符类型值。
􀁺 CONVERT(string,dest_charset[,source_charset])
将输入string 转换为指定字符集dest_charset。source_charset 是输入值的字符集——如果
它没有被指定，则缺省为数据库字符集。输入值可以是CHAR、VARCHAR2、NCHAR、NVARCHAR2、CLOB
和NCLOB 类型。返回值为VARCHAR2 类型。如果dest_charset 中没有输入字符串中的一个字符，将
会使用一个代替字符（由dest_charset 定义）
􀁺 NCHR(X)
返回数据库国家字符集中值为X 的字符。NCHR(X) 等价于CHR(x USING NCHAR_CS).
􀁺 NLS_CHARSET_DECL_LEN(byte_width,charset)
返回一个NCHAR 值的声明宽度(以字符为单位).byte_width 是该值以字节为单位的长度charset 是
该值的字符集ID。
资源来自网络，仅供学习！ Oracle 从入门到精通
第 13 页，共 106 页
􀁺 NLS_CHARSET_ID(charset_name)
返回指定字符集charset_name 的数字ID。为charset_name 指定“CHAR_CS”将返回数据库字符集
的ID，为charset_name 指定“NCHAR_CS”将返回数据库国家字符集的ID。如果charset_name 是一
个无效字符集名，将返回NULL。NLS_CHARSET_ID 和NLS_CHARSET_NAME 是互为反函数。
􀁺 NLS_CHARSET_NAME([charset_id])
返回指定字符集ID charset_id 的名字。如果 charset_id 是一个无效字符集ID，将返回NULL;
􀁺 NLS_INITCAP(string[,nlsparams])
以字符串中每个单词第一个字符大写而单词中其余字母小写的形式返回string.nlsparams 指定了
一个与该会话缺省的不同的排序次序。如果没有指定该参数，NLS_INITCAP 与INITCAP 相同。
nlsparams 应该采取下面的形式：
'NLS_SORT=sort',其中sort 是一个语言排序序列。
􀁺 NLS_UPPER(string[,nlsparams])
以大写形式返回string,不是字母的字符不受影响。如果没有指定nlsparams,NLS_UPPER 与UPPER
相同。
􀁺 NLS_LOWER
以小写形式返回string,不是字母的字符不受影响，如果没有指定nlsparams，NLS_LOWER 与LOWER
相同。
􀁺 NLSSORT(string[,nlsparams])
返回用于排序string 的字符串字节。所有值都被转换为字节字符串，这样在不同数据库之间就保持
了一致性。
如果没有指定nlsparams,那么就会使用会话中缺省排序序列。
􀁺 TRANSLATE(string USING {CHAR_CSINCHAR_CS})
TRANSLATE...USING 将输入string 参数转换为数据库字符集（指定CHAR_CS）或数据库国家字符集
（指定NCHAR_CS）.string 可以是CHAR、VARCHAR2、NCHAR、NVARCHAR2 类型。如果指定CHAR_CS，
返回类型为VARCHAR2，如果指定NCHAR_CS,返回类型为NVARCHAR2。TRANSLATE...USING 是CONVERT
功能的子集。
如果输入值包含UCS2 字符或反斜线符号要使用UNSTR 函数。
例：
SQL> select translate('asd' using NCHAR_CS) from dual;--数据库国家字符集
TRANSL
------
asd
SQL> select translate('asd' using CHAR_CS) from dual;--数据库字符集
TRA
---
asd
􀁺 UNSTR(s)
返回转换为数据库UNICODE 字符集的字符串。s 可包含escaped UCS2 代码点字符。它由一个反斜线
符号加上十六进制代码点数字组成。因此，要在字符串中包含一个反斜线符号就必须使用双反斜线
符号(\\).
UNISTR 与TRANSLATE...USING 相似，差别是它仅能转换为UNICODE，而且可以包含escaped 字符。
general
//number
//conversion
//date
资源来自网络，仅供学习！ Oracle 从入门到精通
第 14 页，共 106 页
多行函数：对多行数据（一组数据）进行计算并返回一个值的函数。
2、number 数字类型函数
􀁺 ABS(x) 函数，此函数用来返回一个数的绝对值。
􀁺 ACOS(x)函数，返回X 的反余弦值。X 范围从1 到-1，输入值从0 到派，以弧度为单位。
􀁺 ASIN(x)函数，返回X 的反正弦值。X 范围从1 到-1，输入值从-PI/2 到PI/2，以弧度为单位。
􀁺 ATAN(x)函数，返回X 的反正切值。输入值从-PI/2 到PI/2，以弧度为单位。
􀁺 BITAND(x,y)函数,返回X 和Y 的与结果。X 和Y 必须为非负整数。注意没有BITOR 函数，但
是在UTL_RAW 包中有用于RAW 值的位操作符。
􀁺 CEIL(x)函数，用来返回大于或等于X 的最小整数。
􀁺 COS(x)函数，返回x 的余弦值。x 是以弧度表示的角度。
􀁺 COSH(x)函数，返回X 的双曲余弦。
􀁺 EXP(x)函数，与power(x,y)函数类似，不过不用指明基数，返回E 的X 次幂。E=2.71828183...
􀁺 FLOOR(x)函数，用来返回小于或等于X 的最大整数。
􀁺 LN(x)函数，返回x 的自然对数。x 必须大于0。
􀁺 LOG(x,y)函数，返回以X 为底Y 的对数。底必须是不为0 和1 的正数，Y 是任意正数。
􀁺 MOD(被除数，除数)求余函数，如果除数为0，则返回被除数。
􀁺 POWER(x,y)函数，返回X 的Y 次幂。底X 和指数Y 都不必是正整数，但如果X 是负数的话，
Y 必须是整数。
􀁺 ROUND(x[,y])函数，返回舍入到小数点右边Y 位的X 值。Y 缺省为0，这将X 舍入为最接近的
整数。如果Y 是负数，那么舍入到小数点左边相应的位上，Y 必须为整数。
􀁺 SIGN(x)函数，此函数用来返回一个数的正负值，若为一个正数则返回1，若为一个负数则返回
-1，若为0 则仍返回0，有点像把模拟量数字化的意思。
􀁺 SIN(x)函数，返回X 的正弦。x 是以弧度表示的角度。
􀁺 SINH(x)函数，返回x 的双曲正弦。
􀁺 SQRT(x)函数，返回x 的平方根，x 不能是负数。
􀁺 TAN(x)函数，返回x 的正切。x 是以弧度表示的角度。
􀁺 TANH(x)函数，返回x 的双曲正切。
􀁺 TRUNC(x[,y])截取值函数，Y 缺省为0，这样X 被截取成一个整数。如果Y 为负数，那么截取
到小数点左边相应位置
􀁺 WIDTH_BUCKET(x,min,max,num_buckets) 只能在SQL 语句中使用。
使用 WIDTH_BUCKET 可以根据输入参数创建等长的段。范围MIN 到MAX被分为num_buckets
节，每节有相同的大小。返回X 所在的那一节。如果X 小于MIN，将返回0，如果X 大于或等于
MAX，将返回num_buckets+1.MIN 和MAX 都不能为NULL，num_buckets 必须是一个正整数。如
果X 是NULL，则返回NULL。
3、时间类型函数：（date）
内部存储格式：世纪、年、月、日、小时、分钟、秒
默认格式是：DD-MON-RR。
SYSDATE 返回当前的系统时间。
SELECT SYSDATE FROM DUAL；
资源来自网络，仅供学习！ Oracle 从入门到精通
第 15 页，共 106 页
3.1、对日期的数学运算
SELECT （SYSDATE-HIRE_DATE）/7 FROM TABLENAME WHERE ROWNUM;
SYSDATE-HIRE_DATE:数字列
ADD_MONTHS(date,x)函数，返回加上X 月后的日期DATE 的值。X 可以是任意整数。如果结果的
月份中所包含的日分量少于DATE 的月份的日分量，则返回结果月份的最后一天。如果不小
于，则结果与DATE 的日分量相同。时间分量也相同。
CURRENT_DATE 以DATE 类型返回会话时区当前的日期。这个函数同SYSDATE 相似，除了SYSDATE
不管当会话时区。
CURRENT_TIMESTAMP[(precision)] 以TIMESTAMP WITH TIMEZONE 类型返回会话时区当前的
日期。如果指定precision，它指返回秒数的精度，缺省为6。
DBTIMEZONE 返回数据库的时区。
LAST_DAY（日期）指定日期所在月份的最后一天的日期，这个函数可用来确定本月还有多少天。
LOCALTIMESTAMP[(precision)] 以TIMESTAMP 类型返回会话时区的当前日期。如果指定
precision，它指返回秒数的精度，缺省为6 。
MONTHS_BETWEEN(离当前比较近的日期date1,以前的日期) 两个日期之间相差的月数（以日作
为最小单位来计算的）。返回是相差的月数。如果date1 和date2 的日分量相同，或者这两个日
期都分别是所在月的最后一天，那么返回结果是个整数。否则，返回结果包含一个分数，以一
个月31 天计算。
NEW_TIME(d,zone1,zone2)函数，当时区zone1 中的日期和时间是D 的时候，返回时区zone2 中
的日期和时间。返回类型为DATE。zone1 和zone2 是字符字符串，另外的时区可在ORACLE9I 中
通过查询V$TIMEZONE_NAMES 得到。
NEXT_DAY （日期，星期几）指定日期后将要遇到的后七天的某一天的日期。
ROUND（日期，‘MONTH/YEAR’）四舍五入得到新的日期。保留位置是月和年
SESSIONTIMEZONE 返回当前会话的时区。返回类型是一个时区偏移或时区片名的字符字符串。
如果指定格式，则与 ALTER SESSION 语句中的格式相同。
SYS_EXTRACT_UTC(datetime) 从提供的DATETIME 中以UTC(Coordinated Universal Time)返回
时间。DATETIME 必须包含一个时区。
SYSTIMESTAMP 以TIMESTAMP WITH TIMEZONE 返回当前的日期和时间。当在分布式SQL 语句中
使用的时候，返回本地数据库的日期和时间。
TRUNC（日期，‘MONTH/YEAR’）截取
TZ_OFFSET(timezone) 以字符字符串返回提供的timezone 和UTC 之间的偏移量。timezone
可以被指定为时区名或'+/-HH:HI'格式表示的偏移量。也可使用SESSIONTIMEZONE 和
DBTIMEZONE 函数，返回格式为'+/-HH:HI'。
字符字符串时区
AST 大西洋标准时
ADT 大西洋夏令时
BST 白令标准时
BDT 白令夏令时
CST 中央标准时
CDT 中央夏令时
EST 东部标准时
EDT 东部夏令时
GMT 格林威治平均时
HST 阿拉斯加夏威夷标准时
资源来自网络，仅供学习！ Oracle 从入门到精通
第 16 页，共 106 页
HDT 阿拉斯加夏威夷夏令时
MST Mountain标准时
MDT Mountain夏令时
NST 纽芬兰标准时
PST 太平洋标准时
PDT 太平洋夏令时
YST YuKon标准时
YDT YuKon夏令时
3.2、日期和日期时间算术
运算返回类型
结果
d1-d2 NUMBER
返回D1 和D2 之间相差的天数。该值是一个数值，其小数部分代表一天的几分之几。
dt1-dt2 INTERVAL
返回DT1 和DT2 之间的时间间隔。
i1-i2 INTERVAL
返回i1 和i2 之间的差距。
d1+d2 N/A
非法——仅能进行两个日期之间的相减。
dt1+dt2 N/A
非法——仅能进行两个日期之间的相减。
i1+i2 INTERVAL
返回i1 和i2 的和。
d1+n DATE
在D1 上加上N 天作为DATE 类型返回。N 可以是实数，它包含一天的几分之几。
d1-n DATE
从D1 上减去N 天作为DATE 类型返回。N 可以是实数，它包含一天的几分之几。
dt1+i1 DATETIME
返回DT1 和I1 的和。
dt1-i1 DATETIME
返回DT1 和I1 之间的差距。
i1*n INTERVAL
返回I1 的N 次方。
i1/n INTERVAL
返回I1 除以N 的值。
表中注：
D1 和D2 指日期值；
DT1 和DT2 指日期时间值；
I1 和I2 指时间间隔值；
N 指数字值。
资源来自网络，仅供学习！ Oracle 从入门到精通
第 17 页，共 106 页
3.3、显示转换：（conversion）
TO_NUMBER(char[,'format_model']) 字符转换到数字类型
TO_DATE(char[,'format_model']) 字符转换到日期类型
格式说明符：要与前边要转换的字符串的格式要相同才能转换（匹配问题：格式和位数）。
TO_CHAR(date[,'format_model'[,nlsparams]])
第二个参数可以省略，不指定格式，按系统默认格式输出。
区分大小写。
使用FM（在格式控制符前添加）符号可以去掉空格或是首位的零。
如果指定了NLSPARAMS，则它控制返回字符串的月和日分量所使用的语言。格式为：
'NLS_DATA_LANGUAGE=language',language 指需要的语言。
例：select to_char(sysdate,'FMyyyy-mm-dd') from dual;
5.格式控制符的类型：
YYYY 四位的年
YEAR 年的拼写
MM 2 位数字的月
MONTH 月的全名
MON 月名的前三个字符
DY 星期名的前三个字符
DAY 星期名的全称
DD 2 位的天
6.时间格式控制符：
HH24:MI:SS AM
HH12:MI:SS PM
7.通过“”来实现加入特殊字符的格式控制符。
SELECT TO_CHAR(SYSDATE,'FMyyyy"年"mm"月"dd"日"') from dual;
DDSPTH
~~
DD 是格式控制符。
TH 是序数词，将日期转换成英文的序数词拼写。
SP 是基数词，将日期转换成英文的基数词拼写。
TO_CHAR(NUM[,'format_model'[,nlsparams]])转换数字
将NUMBER 类型参数NUM 转换成VARCHAR2 类型。如果指定FORMAT，它会控制整个转换。
如果没有指定FORMAT，那么结果字符串中将包含和NUM 中有效位的个数相同的字符。NLSPARAMS
用来指定小数点和千分符及货币符号。它的格式可为：'NLS_NUMERIC_CHARS=' ' dg '
'NLS_CURRENCY= ' 'string' ' ' 。 d 和g 分别代表小数点和千分符，STRING 代表货币符号。
数字格式控制符：
9 代表一位数字（替换符。有，数字显示；没有。不什么都显示。）
0 代表一位数字（有数字，显示；没有，强制显示0。）
$ 美圆符号
L 本地货币
资源来自网络，仅供学习！ Oracle 从入门到精通
第 18 页，共 106 页
. 小数点
, 千分符
B 当整数部分为0 时，将整数部分填充为空格。例：B999
MI 返回带有后继符号而不是前导负号的负数值，正数值将带有后继的空格。999MI
S 返回一个前导符号或后继符号，正数为+，负数为-。 S9999 或 9999S
PR 使用尖括号返回负数。正数将有前导或后继空格。999PR
D 在指定位置返回一个小数点。两侧的9 的个数指定了最大的位数。99D9
G 在指定位置返回千分符，G 可以在FORMAT_model 中出现多次。9G999G9
C 在指定位置返回ISO 货币符号。C 可以在FORMAT_model 中出现多次。C99
L 在指定位置上返回本地货币符号。 L99
V 返回一个被乘以10 的N 次方的数值，这里N 是V 后边9 的个数。99V99
EEEE 使用科学记数法返回该数值。9.99EEEE
RM 使用大写的罗马数字表示返回该数值。 RM
rm 使用小写的罗马数字表示返回该数值。 rm
FM 返回不含前导和后继空格的数值。 FM99.09
格式控制符位数一定要大于或等于NUMBER 的位数，不能小于。
用RR 解决跨世纪问题：小于50 的认为是1950-2050
大于50 的认为是1951-1999
数字和日期是不能相互转换的。
ASCIISTR(string)
返回只包含有效的SQL 字符和斜线的字符串。string 中的任何无效的字符将被转换为一个相当
的数字，在之前加上斜线。
BIN_TO_NUM(num[,num]...)
将一位矢量转换位相当的数字。它的参数是一系列逗号隔开的NUMS，每一个都必须是0 或1。
例如BIN_TO_NUM(1,0,1,1)将返回11，因为11 的二进制表示是1011。当使用分组集合和GROUP
BY 子句时该函数很有用。
CHARTOROWID(x)函数，
将字符串转换成一个ROWID 类型的值，注意格式必须采用ROWID 数据类型格式，即“数据块号:
行序号:数据文件号”。
COMPOSE(string)
以相同字符集中完全规格化Unicode 形式返回string.string 可以是CHAR、VARCHAR2、NCHAR、
NVARCHAR2、CLOB 或NCLOB 类型。
DECOMPOSE(string)
返回一个Unicode 字符串。它是string 的规范分解。string 可以是CHAR、VARCHAR2、 NCHAR、
NVARCHAR2、CLOB 或NCLOB 类型。
FROM_TZ(timestamp,timezone)
返回一个TIMESTAMP WITH TIMEZONE 类型值。它将TIMESTAMP(没有时区信息)和提供的TIMEZONE
组合在一起。
HEXTORAW(string)
将由STRING 表示的二进制数值转换为一个RAW 数值。STRING 应该包含十六进制值。STRING 中
的每两个字符表示结果RAW 中的一个字节。HEXTORAW 和RAWTOHEX 互为反函数。
NUMTODSINTERVAL(x,unit)
资源来自网络，仅供学习！ Oracle 从入门到精通
第 19 页，共 106 页
将X 转换为INTERVAL DAY TO SECOND 值，X 应该是一个数字。UNIT 是一个字符字符串（可以
是CHAR、VARCHAR2、NCHAR 或NVARCHAR2），且是'DAY'、'HOUR'、'MINUTE'、'SECOND'之一。unit
是不区分大小写的，返回值的缺省精度为9。
NUMTOYMINTERVAL(x,unit)
将X 转换成INTERVAL YEAR TO MONTH 值，X 应该是一个数字。UNIT 是一个字符字符串（可以
是CHAR、VARCHAR2、NCHAR 或NVARCHAR2），且是'YEAR'或'MONTH'之一。unit 是不区分大小写的，
返回值的缺省精度为9。
REFTOHEX(refvalue)
返回一REF refvalue 的十六进制表示。
RAWTOHEX(rawvalue)
将RAW 类型值rawvalue 转换为一个十六进制表示的字符串。rawvalue 中的每个字节转换为一
个双字符的字符串。
RAWTONHEX(rawvalue)
将RAW 类型值rawvalue 转换为一个十六进制表示的字符串。rawvalue 中的每个字节转换为一
个双字符的字符串。RAWTONHEX 返回值是NVARCHAR2 类型而不是VARCHAR2 类型。
ROWIDTOCHAR(rowid)函数，将ROWID 类型值转换成字符串。与CHARTOROWID 互为反函数。
ROWIDYONCHAR(rowid) 与ROWIDTOCHAR 类似，返回类型是NCHAR，而不是CHAR。
TO_CLOB(string)
将string 转换为CLOB。string 可以是文字或另一个LOB 列。如果参数包含NCHAR 数据，它被
转换为数据库字符集
TO_DSINTERVAL(string[,nlsparams])
将string(可以是CHAR、VARCHAR2、NCHAR、NVARCHAR2)转换为 INTERVAL DAY TO SECOND 类型。
如果选定nlsparams,则nlsparams 只能包含小数点和千分位字符的NLS_NUMERIC_CHARARCTERS
表示。
TO_LOB(long_column)
将long_column 转换成LOB。这个函数用于将LONG 和LONG RAW 分别转换为CLOB 和LOB。
TO_MULTI_BYTE(string)
返回将所有单字节字符替换为等价的多字节字符的STRING。该函数仅当数据库字符集同时包含
单字节和多字节字符时才使用。否则，STRING 不会进行任何处理而被返回，与TO_SINGLE_BYTE
互为反函数。
TO_NCHAR
和TO_CHAR 相似，结果是属于国家字符集而不是数据库字符集。
TO_NCLOB(string)
将STRING 转换为NCLOB。STRING 可以是文字或另一LOB 列。
TO_SINGLE_BYTE(string)
返回将所有双字节字符替换为等价的单字节字符的STRING。。该函数仅当数据库字符集同时包
含单字节和多字节字符时才使用。否则，STRING 不会进行任何处理而被返回，与TO_MULTI_BYTE
互为反函数。
TO_TIMESTAMP(string[,format[,nlsparams]])
将其参数CHAR 或VARCHAR2 类型string 转换成TIMESTAMP 类型。
TO_TIMESTAMP_TZ(string[,format[,nlsparams]])
将其参数CHAR 或VARCHAR2 类型string 转换成 TIMESTAMP WITH TIMEZONE 类型。
TO_YMINTERVAL(string)
将string(可以是CHAR、VARCHAR2、NCHAR、NVARCHAR2)转换为INTERVAL YEAR TO MONTH 类型。
TO_YMINTERVAL 与TO_DSINTERVAL 相似，除了它不能使用NLS 参数作为参数并返回YEAR TO MONTH
时间间隔而不DAY TO SECOND 时间间隔。
资源来自网络，仅供学习！ Oracle 从入门到精通
第 20 页，共 106 页
TO_label(x[,y])函数，按照格式Y 将字符串X 转换成MLSLABEL 类型的一个值，若默认格式为
Y，则按照默认格式进行转换。
dump(w,[x[,y[,z]]])函数,用来返回字符串EXPR 的数据类型，内部的存储位置和字符长度。
dump(expr,return_datatype,start_position,length).
return_datatype 是指定返回返回位置用什么方式表示，可以为8、10、16、17，分别表示用八
进制、十进制、十六进制和字符类型。
例：
select dump(last_name,8,3,2) ,dump(last_name,10,3,2) ,
dump(last_name,16,3,2) ,dump(last_name,17,3,2) from employees
where lower(last_name) = 'smith';
greatest(x,y,...)函数，返回参数列表中的最大值。其参数的类型是由第一个参数决定的，可
以为数值型、日期型、和字符型等，后面的参数被强制转换成此种数据类型。进行字符串的比
较时，其大小由字符在字符集中的数值决定，在字符集中的数值大，则此字符就大，对于字符
串，此函数返回VARCHAR2 类型。
least(x,y,......)函数，返回列表参数中的最小值。
与上两个函数类似的有：
greatest_lb(x,y,....)函数和least_lb(x,y,....)函数，分别求出列表中的标签的最大下限
和最小上限，其参数必须为
MLSLABEL 类型，返回值为 RAW MLSLABEL 类型。
user 函数，返回当前用户的数据库用户名。
uid 函数，返回唯一标识当前用户的整数。
这两个函数在完整性约束检查时会用到，可以当作引用变量一样引用它们。
userenv(x)函数，返回当前会话的一些信息，由X 指定返回何种信息。在写一个指定应用的审
计测试表或决定为当前会话指定哪种语言时会用到，但完整性约束时不能用。
参数：
Entryid 返回有效的审计条目标识
Label 返回当前会话的标签
Language 以“语言.字符集”形式返回所用的语言和字符集
Sessionid 返回正在使用的审计会话号
Terminal 返回当前会话终端所用的操作系统
1.7、嵌套函数：
单行函数可以嵌套任意层；
嵌套函数从最深层开始执行。
1. 通用函数：
BFILENAME(directory,file_name)
返回操作系统中与物理文件file_name 相关的BFILE 位置指示符。directory 必须是数据字典
资源来自网络，仅供学习！ Oracle 从入门到精通
第 21 页，共 106 页
中的一个DIRECTORY 类型对象。
COALESCE（，，，，可以多个参数）返回从左到右的第一个非空的表达式。如果所有表达式都为
NULL，则返回NULL。
EMPTY_BLOB/EMPTY_CLOB
返回一个空的LOB 位置指示符。EMPTY_CLOB 返回一个字符位置指示符，EMPTY_BLOB 返回一个二
进制位置指示符。
EXISTSNODE(XMLType_instrance,Xpath_string)
使用Xpath_string 中的路径，确定由XMLType_instrance 标识的XML 文档的TRAVELSAL 是否返
回任何节点。这个函数将返回一个NUMBER 值，如果没有节点则为0，如果有节点则为大于0。
EXTRACT(XMLType_instrance,Xpath_string)
应用Xpath_string 之后，返回由XMLType_instrance 标识的XML 文档的一部分。
GREATEST(expr1[,expr2]...)
返回其参数中最大的表达式。在进行比较之前，每个表达式都被隐式转换为EXPR1 的类型，如
果EXPR1 是字符类型，则使用非填充空格字符比较，返回结果为VARCHAR2 类型。
LEAST(expr1[,expr2]...)
返回其参数中最小的表达式，其余同上。
NVL(EXPR1,EXPR2)
类型必须匹配，如果EXPR1 是NULL，则返回EXPR2，否则返回EXPR1。返回值与EXPR1 类型相
同，除非EXPR1 是字符类型，在这种情况下将返回VARCHAR2 类型。这个函数用于确保查询记录
集中不包含NULL 值。
NVL2(EXPR1,EXPR2,EXPR3)
如果EXPR1 是NULL，则返回EXPR2，否则返回EXPR3。返回值与EXPR2 类型相同，除非EXPR2
是字符类型，在这种情况下将返回VARCHAR2 类型。
SYS_CONNECT_BY_PATH 返回列值的从根到结点的路径，它仅在层次查询中有效。
SYS_CONTEXT(namespace,parameter[,length])
返回与namespace 的内容相关联的patameter 的值。使用DBMS_SESSION.SET_CONTEXT 过程设置
参数和namespace.返回值是VARCHAR2 类型，如果没有指定length,则最大长度是255 字节。
SYS_DBURIGEN
产生一个URL 用于从数据库中提取XML 文档。
SYS_GUID
以16 位RAW 类型值形式返回一个全局唯一的标识符。
SYS_TYPEID(object_type)
返回指定类型object_type 的类型ID。
SYS_XMLAGG
将几个XML 文档或文档片段组合为一个文档。
SYS_XMLGEN
返回一个基于数据库中数据的XML 文档片段。
TREAT(expr AS [REF] [schema.]type)
TREAT 用于改变一个表达式的声明类型。仅可以将声明类型改变为给定表达式的子类型或超类
型。以类型[schema.]type 返回expr，如果指定了REF，则返回REF。
UID
返回一个唯一标识当前数据库用户的整数，UID 没有参数。
VSIZE(x)返回X 内部表示的字节数。
NULLIF(a,b)如果A 等于B 返回NULL，如果不等于返回B。
DUMP(expr[,number_format[,start_position][,length]])
返回一个包含EXPR 内部表示信息的VARCHAR 值，如果没有指定NUMBER_FORMAT，则返回结果以
资源来自网络，仅供学习！ Oracle 从入门到精通
第 22 页，共 106 页
十进制形式返回。如果指定了start_position 和length，则返回从start_position 开始，长
为length 字节的字符串，缺省是返回整个表达式。所返回的数据类型是内部数据类型编码的对
应数字。
NUMBER_FORMAT
格式返回结果
8 8 进制符号
10 10 进制符号
16 16 进制符号
17 单字符
编码数据类型有效于
1 VARCHAR2 ORACLE7
2 NUMBER ORACLE7
8 LONG ORACLE7
12 DATE ORACLE7
23 RAW ORACLE7
24 LONG RAW ORACLE7
69 ROWID ORACLE7
96 CHAR ORACLE7
112 CLOB ORACLE8
113 BLOB ORACLE8
114 BFILE ORACLE8
180 TIMESTAMP ORACLE9i
181 TIMESTAMP WITH TIMEZONE ORACLE9i
182 INTERVAL YEAR TO MONTH ORACLE9i
183 INTERVAL DAY TO SECOND ORACLE9i
208 UROWID ORACLE9i
231 TIMESTAMP WITH LOCAL TIMEZONE ORACLE9i
USERENV[option]
基于option 返回包含有关当前会话信息的VARCHAR2 值。
函数的行为
选项值 USERENV(option)的行为
'OSDBA' 如果当前会话将OSDBA 角色的设置打开了，则返回'TRUE',否则返回
'FALSE',注意返回值是VARCHAR2 类型，而不是BOOLEAN 类型。
'LABEL' 仅对TRUSTED ORACLE 中有效，返回当前会话标志。
'LANGUAGE' 返回当前会话所使用的语言和地域，以及数据库字符集，这是NLS 参数，返
回形式是LANGUAGE_TERRITORY.CHARACTERSET.
'TERMINAL' 返回当前会话所使用终端的操作系统标识符。对于分布式的SQL 语句，返回
的是本地会话的标识符。
'SESSIONID' 如果初始化参数AUDIT_TRAIL 被设置为TRUE，那么将返回审计会话标识符。
资源来自网络，仅供学习！ Oracle 从入门到精通
第 23 页，共 106 页
在分布式SQL 语句中，USERENV('SESSIONID')是无效的。
'ENTRYID' 如果初始化参数AUDIT_TRAIL 被设置为TRUE，那么将返回可用的审计项标识
符。在分布式SQL 语句中USERENV('ENTRYID')是无效的。
'LANG' 返回语言名称的ISO 缩写符号。它的格式比USERENV('LANGUAGE')要短。
例：
select USERENV('TERMINAL'),USERENV('LANGUAGE') from dual;
USERENV('TERMINA USERENV('LANGUAGE')
---------------- ----------------------------------------------------
WNJ SIMPLIFIED CHINESE_CHINA.ZHS16GBK
2. 条件表达式：
CASE 表达式（简单CASE）
语法：
CASE 表达式 WHEN 条件1 THEN 返回值1
WHEN 条件2 THEN 返回值2
.
.
.
WHEN 条件n THEN 返回值n
ELSE 返回值
END
DECODE 函数
语法：
DECODE（
条件,比较值1，返回值1
比较值2，返回值2
.
.
.
比较值n，返回值n
返回值（不满足条件时）
）
select last_name,salary,
decode( trunc(salary/2000,0),//条件
0, 0.00,//比较值1，返回值1
1, 0.09，
2, 0.20，
3, 0.30，
4, 0.40，
资源来自网络，仅供学习！ Oracle 从入门到精通
第 24 页，共 106 页
5, 0.42，
6, 0.44，
0.45
) TAX_RATE
from employees
where department_id=80;
3. 从多表中显示数据：
SQL（老版本的）
等值查询
SELECT TABLE1.COLUMN,TABLE2.COLUMN FROM TABLE1,TABLE2 WHERE
TABLE1.COLUMN1=TABLE2.COLUMN2;//自然连接使用AND 操作符增加查询条件
使用表的别名来简化查询，提高查询功能。
SELECT E.ID，D.ID FROM EMPL E，DEP D WHERE E.NAME=D.NAME；
E、D：表别名
多表等值连接查询
为了连接N 个表，至少需要N-1 个连接条件。
非等值查询
使用 BETWEEN AND 查询近似值作为连接条件的多表结果。
WHERE E.SALARY BETWEEN J.LOW AND J.HIGH
外连接查询
SELECT T1.COL,T2.COL FROM WHERE T1.COL(+)=T2.COL;左外连接所有T2 的T1 信息。
SELECT T1.COL,T2.COL FROM WHERE T1.COL=T2.COL(+);右外连接所有T1 的T2 信息。
为了看到与连接条件不匹配的数据，就必须得用外连接。
自连接
通过表的别名来创建虚拟逻辑表,进行自连接查询。
select worker.last_name || 'work for' || manager.last_name
from employees worker,employees manager
where worker.manager_id=manager.employee_id;
9I 适应性连接：
select t1.col,t2.col
from table
cross join t2 //交叉连接
natural join t2//自然连接:把两表中所有等值的字段都作为连接条件（但这些连接条件不用
写）。
从两个表中选出连接列的值相等的所有行。
如果两个列的名称相同，但数据类型不同；或是类型相同，意义不同都会出错。
资源来自网络，仅供学习！ Oracle 从入门到精通
第 25 页，共 106 页
join t2 using (column_name);基于自然连接，只有在USING 中出现的，才作为连接条件（在
USING 中列名前一定不能加前缀）。
join t2 on (t1.col=t2.col);基于ON 的自然连接。等值、非等值或自连接都可以实现。
left|right|full outer join t2 on(t1.col=t2.col);
select e.last_name,d.department_name,l.city
from employees e
left outer join departments d on e.department_id=d.department_id
right outer join locations l on d.location_id=l.location_id;
*&* 可以连续做左连接或右连接的操作。
full outer join 忽略连接条件，把要查询的列的所有行全显示出来。
笛卡尔乘积（多表查询容易产生的错误）形成原因：
*、忽略连接条件；
*、连接条件不正确；
*、笛卡尔乘积是由第一个表的所有行和第二个表的所有行联合形成的；
*、为了避免笛卡尔乘积的产生，一定要在WHERE 条件中正确写出连接条件。
set linesize 160;设置显示行的行数。
1.8、用字函数产生的总计
对多行的计算产生单行的结果。
组函数用语对每个组的行集进行运算，每个组产生一个结果。
AVG([DISTINCT/ALL]col)只能用与数字。只能对多行的数据进行运算，不能在这个函数中做单
行的数学运算。
CORR(x1,x2)
返回表达式X1 和X2 组成的集合的相关系数。在保证所有行中的X1 和X2 都不为NULL 之后结果
通过
COVAR_POP(x1,x2)/(STDDEV_POP(x1)*STDDEV_POP(x2))得到。
COUNT([DISTINCT/ALL]col)所有非空字段的行数。
COVAR_POP(x1,x2)返回表达式x1 和x2 组成的集合的人口协方差结果通过
(SUM(x1*x2)-SUM(x2)*SUM(x1)/n)/n 得到，n 是没有NULL 项的集合的数目。
COVAR_SAMP(x1,x2)返回表达式X1 和X2 组成的集合的相同协方差。
CUME_DIST 返回一组值中一个值的累积分布。
DENSE_RANK 返回有序分组的行中一行的秩，秩是从1 开始的连续的整数。
GROUP_ID()返回一个唯一数字值用于在GROUP BY 字句中辨别组。
GROUPING_ID 返回一个数字对应于一行的GROUPING 位矢量。
资源来自网络，仅供学习！ Oracle 从入门到精通
第 26 页，共 106 页
MAX([DISTINCT/ALL]col)可以用于任何类型，当用于日期类型时代表最晚。忽略空值。字符类
型时候，比较字符串首字母的ASCLL 值。
MIN([DISTINCT/ALL]col)可以用于任何类型，当用于日期类型时代表最早。忽略空值。字符类
型时候，比较字符串首字母的ASCLL 值。
PERCENTILE_CONT 这个函数是一个反分布函数，它假设了一个连续分布模式。
PERCENTILE_DISC 一个反分布函数，它假设了一个离散分布模式。
RANK 返回给定行的秩。秩不必是连续的，因为相同的行有相同的秩。
REGR 这些函数
(REGR_SLOPE,REGR_INTERCEPT,REGR_COUNT,REGR_R2,REGR_AVGX,REGR_AVGY,REGR_SXX
REGR_SYY,REGR_SXY)得到了双集合的普通最小衰减线。
SUM([DISTINCT/ALL]col)返回选择列表项目的总和，只能用于数字。
STDDEV([DISTINCT/ALL]col) 标准方差
STDDEV_POP(col)计算人口标准差并返回人口方差的平方根。
STDDEV_SAMP(col)计算累计标准差并返回例子方差的平方根。
VAR_POP(x)返回提系列数字在去除了NULL 值之后的人口不同。由
(SUM(x*x)-SUM(x)*SUM(X)/COUNT(x))/COUNT(x)得到。
VAR_SAMP(x)返回一系列数字在去NULL 值之后的范例不同。由
(SUM(x*x)-SUM(x)*SUM(X)/COUNT(x))/(COUNT(x)-1)得到。
VARIANCE([DISTINCT/ALL]col)偏移方差，返回COL 的方差。
语法：
select col,group function(col) from table where 条件 group by col;
GROUP BY
必须：出现在查询列表中的一个字段，但没有出现在函数中，那么这个字段必须要出现在GROUP
BY 中。
可以：出现在GROUP BY 子句中的字段可以不出现在查询列表中。
先排列，再运算。
WHERE 子句中不能使用 group function。
限制组必须使用 HAVING 子句。
语法：
select col,group function from table
where 条件//可以没有条件限制
group by col
having group_condition //组过滤，在过滤以后，再进行分组计算。
order by col;
组函数嵌套最多只能有两层。
select max(avg(salary))
from employees
group by employee_id;
select * from tab;查询一个用户中的所有表。
资源来自网络，仅供学习！ Oracle 从入门到精通
第 27 页，共 106 页
1.9、子查询：
语法：
select col from table
where expr operator (select col from table);
子查询在主查询执行前执行一次。
子查询的结果被用于主查询。
使用规则：
在WHERE 和 HAVING 子句中都可以使用子查询。
*、子查询必须用括号扩起。
*、子查询应该在比较条件的右边。
*、在子查询中的ORDER BY 子句不需要，除非执行TOP-N 分析。
TOP-N 分析：（在一些表里求出最怎么怎么怎么样（最好、做多...）的几个人）。
*、对单行子查询使用单行比较操作符，多行子查询使用多行比较操作符。
可以在子查询中使用组函数。
子查询的分类：
单行单列子查询：一定返回一行
单行操作符：>,<,=，>=,<=,<>
多行单列子查询
单行多列子查询：返回零行或多行
多行操作符：
in 等于列表中的任何值。（不能用NOT IN）
ANY 与子查询返回的每个值进行比较。(小于是小于最大的，大于是大于最小的)
select employee_id,last_name,job_id,salary from smployees
where
salary < any(select salary from employees where job_id='IT_PROG')
and job_id <> 'IT_PROG';
ALL（小于是小于最小的，大于是大于最大的）
select e.employee_id,e.last_name,e.salary
from employees e,
(select department_id,min(salary) m from employees
group by department_id )d
where e.department_id=d.department_id
and e.salary=d.m；
查询每个部门薪水最少的员工的资料。
4. 多行多列子查询
资源来自网络，仅供学习！ Oracle 从入门到精通
第 28 页，共 106 页
2.0、替换变量：
临时存储值：
1.&
生命周期：单次引用中，不需要声明。如果替换字符或日期类型，最好用单引号扩起。
使用范围：
where
order by
列表达式
表名
整个SELECT 语句中。
2.&&
生命周期：整个会话（session 连接）,不需要声明。
define（生命周期）：整个会话，预先声明，使用时用&引用声明的变量。
define column_name(变量名) 查看变量命令。
undefine 变量名清除变量
define variable=用户创建的CHAR 类型的值：define 变量名=值；
accept（生命周期）：整个会话，预先声明，可以客户化提示信息，使用时用&引用声明的变量。
定义：accept 变量名 number/char/date prompt '提示信息内容'
ACC[EPT] variable [NUM[BER] | CHAR | DATE] [FOR[MAT] format]
[DEF[AULT] default] [PROMPT text | NOPR[OMPT]] [HIDE]
例：accept a char prompt '请输入员工的雇佣时间(yyyy-mm-dd):'
hide
例：accept a char prompt 'input a:' hide
set verify(环境变量) off;关闭调试命令（关掉替换过程）
set verify(环境变量) on;打开调试命令(可以看到替换过程)
2.1.环境变量：
ECHO 显示回显
HEADING {OFF/ON}是否显示列标题；
ARRAYSIZE{20/n}每一次从查询得到的返回量的大小。
FEEDBACK{OFF/ON}回馈，反馈信息。
LONG{80/n}on/text} LONG 类型
LINESIZE 行的宽度。
SET LINESIZE n(最好是在200 之内)
PAGESIZE ：设置页的大小。SET PAGESIZE N
资源来自网络，仅供学习！ Oracle 从入门到精通
第 29 页，共 106 页
wrap{off/on} 折行
SET 修改
SHOW 显示
2.2 格式化命令：
COLUMN[column option]可以设置字段或字段别名的格式。
COLUMN last_name HEADING employee|name '|'代表换行。
col 字段名查看命令
CLE[AR]：清除列的格式
HEA[DING] TEXT：设置列标题
FOR[MAT] FORMAT：格式化显示列的值，对字符和数字有效，对日期无效。
column salary justify left format $999,999.00
justify left:左对齐。
col manager_id format 999999999
限制字符串的长度有A+数字限制
限制数字的长度有9，有几为9 就限制成几位。
NOPRINT/PRINT NOPRINT：把一个字段从输出上屏蔽掉（返回但不显示）。
col 字段名 noprint/print.
NULL 如果有NULL 值，显示什么。
col name null 'on employee'
TTITLE[text/off/on]设置报表的表头
BTITLE[text/off/on]设置报表的表尾
做报表的时候要先想好PAGESIZE 的大小。
BREAK ON [REPORT_ELEMENT]
压制重复值的显示。只能跟一个字段名才有效。
例：
select department_id,last_name
from employees
where rownum<30
order by 1,2;
break on department_id
otn.oracle.com/cn
www.oracle.com/cn
www.itpub.net 入门与认证版 ora-600
www.oracle.com.cn
www.cnoug.org ora-600
资源来自网络，仅供学习！ Oracle 从入门到精通
第 30 页，共 106 页
2.3 做脚本文件的过程：
变量定义accept
环境变量设置SET
格式控制命令
SPOOL
使用变量的SQL
SPOOL OFF
清除格式控制
重置环境变量
释放变量
2.3 数据操作语句：
1. 插入
INSERT INTO TABLE（字段1，字段2....）VALUES（值1，值2....）
一次插入只插入一行。字符和日期值需要单引号扩起。
插入空值：
方法一：隐示插入，插入时省略列名系统就会默认省略的列为NULL。
方法二：显示插入，在插入时指定列的值为空。
注：（1）值的个数不能少于列名的个数。
（2）注意非空属性的列，不能插入空值。
WITH CHECK OPTION 视图约束。
UID 当前数据库用户ID
在插入日期的时候最好用TO_DATE 来控制输入格式。
可以创建一个脚本用 &变量名的形式来用一个插入语句实现多行的插入（在值列表里用 &变量
名）。
插入中的子查询：将另一个表中的内容都插入被插入的表中。
insert into sales_reps(id,name,salary)
select employee_id,last_name,salary from employees where employee_id>100;
可以在子查询中做运算后插入到被插入表中。
不要使用VALUES 子句。
在子查询中的列数必须匹配 INSERT 语句中的列数。
2. 删除
DELERT FROM TABLE WHERE 条件；
删除所有符合 WHERE 条件的行。
基于子查询的删除。
注意及联删除。
3. 更新
UPDATE TABLE SET 列名1=值1，列名2=值2.....WHERE 条件；
资源来自网络，仅供学习！ Oracle 从入门到精通
第 31 页，共 106 页
如果更新错误，要用ROLLBACK 回滚。
利用子查询更新另外表中的数据，在 SET 后和 WHERE 后都可以利用子查询语句。
更新的时候要注意参照完整性约束。
子表的外键字段值必须是父表主键字段值的真子集。
DEFAULT+‘’ 默认值
在创建表的时候用，在第三个参数的位置上。
例：
CREATE TABLE A
（ C1 CHAR（10） DEFAULT
.
.
.
）
在修改的时候，如果先给DEFAULT 赋值的话，可以直接用列名=DEFAULT，使列名回复为默认值。
4. MERGE 语句
提供了对表根据条件进行插入或者更新的能力。
如果行存在则执行UPDATE，如果不存在则执行INSERT。
避免了单独的修改。
提高了性能，更便于使用。
对于数据库应用很有益。
语法：别名AL
MERGE INTO table_name（目的表） table_alias USING (table/view/sub_query)//数据来源
（可以用子查询） alias(别名)
on(连接条件)
WHEN MATCHED THEN UPDATE SET(关键字) (修改目的表)
目的表AL.col1=原表AL.col_var1
目的表AL.col2=原表AL.col2_var2
WHEN NOT MATCHEN THEN（关键字）
INSERT (目的表AL.COL_LIST)
VALUES(原表AL.COL_VARS);（插入原表）
create table newtable_name(新表) as select * from oldtable_name(原表) where 1=0;
将原表中的结构复制到新表中，但具体的数据项不进行复制。
5. 事务（transaction）：
由被逻辑组织在一起的多个DML 语句的构成。
COMMIT：提交。
ROLLBACK：回滚。
SAVEPOINT：存储点，只在事务执行过程中有效，事务结束即被释放。
事务的组成：
资源来自网络，仅供学习！ Oracle 从入门到精通
第 32 页，共 106 页
一组相同改变特性的DML 语句；
一个DDL：数据定义语句；
一个DCL：权限控制语句；
建立存储点：
例：SAVEPOINT A；
.
.
.
ROLLBACK TO A；
事务的开始：
开始于第一个DML SQL 语句执行时开始
结束的时候是在：
*、一个COMMIT 或ROLLBACK 被执行的时候。
*、一个DDL 或DCL 语句被执行（自动提交）注意*&*!（隐式）
*、用户退出SQLPLUS（隐式）
*、系统崩溃（隐式）
语句级回滚：
ORACLE 服务器执行隐式的存储点。
2.4 创建和管理表
1、表（TABLE）基本的存储单位，由行和列组成。
1. 规则：
1.表名和列名（使用规则）：
2.必须是字母开头；
3.必须是1-30 的字符长度；
4.只能包括A-Z，a-z,0-9,_,$,#；
5.在同一个用户下不能头重名的对象；
6.不能是ORACLE 的保留字；
创建需求
必须有： 1. CREATE TABLE 权限；
2.足够的存储空间；
语法：
CREATE TABLE [SCHEMA（方案）.]TABLE
（COL DATATYPE [DEFAULT 默认值][]）；
当前用户所有的表
select table_name from user_tables;
当前用户所有的对象：
资源来自网络，仅供学习！ Oracle 从入门到精通
第 33 页，共 106 页
desc user_objects
当前用户对象的别名：
select * from cat;
字段类型：
VARCHAR(size) 变长字符串类型
CHAR(size) 定长字符串类型
NUMBER(p,s)p 位整数，s 位小数
DATE
DATETIME 秒级最多可以到小数点后的9 位
TIMESTAMP 带有小数秒的日期
TIMESTAMP WITH TIME ZONE 带时区的类型
TIMESTAMP WITH LOCAL TIME ZONE 带时区的并会进行时区转换的类型（同一时间在不
同地区看到的时间）
INTERVAL YEAR TO MONTH 按年和月的间隔存储的类型
INTERVAL ‘123-2’ TEAR(3) TO MONTH
INTERVAL DAY TO SECOND 按天、小时、分和秒的间隔存储的类型
INTERVAL
LONG 变长的长字符串类型
CLOB 字符类型 4GIGABYTES
RAW 二进制类型与CHAR 对应
LONG RAW 二进制类型与LONG 对应
BLOB
BFILE 以文件的形式存储在操作系统中
ROWID 表中行的唯一地址（行地址）
2、方案：一个用户所有对象的命名集合。
如果想访问其他用户或方案的表要加上用户或方案作为前缀。
必须指明：
表名称；
列名，列类型和长度；
用户表：
被用户创建和维护的一些表；
包括了用户自己的信息；
数据字典表：
被ORACLE 数据库创建和维护的一些表；
包括了数据库的信息；
3、CTAS（子查询建表）：
CREATE TABLE table_name
资源来自网络，仅供学习！ Oracle 从入门到精通
第 34 页，共 106 页
[(col,coltype,..)]
as subquery(子查询);
创建的表的列的数目匹配子查询的列的数目。
使用子查询的列的名字和默认值定义表。
注：
*、被创建表的字段名要遵循如果没有字段别名和子查询中没设置别名的话，使用子查询中的列
名；
如果有别名，使用别名；如果有字段列表（[(col,coltype,..)]），在被创建的表中使用字
段列表；
*、有字段列表与子查询的列要匹配。
*、当没有字段列表的时候，而在子查询中有表达式的时候一定要在表达式后要加上别名。
*、只会把属性当中的非空属性复制过来，其他的比如约束条件、关联...都不会复制过来。
使用ALTER TABLE 语句可以：
*、在表中增加一个新列
语法：ALTER TABLE table add (col datatype [default],...,....);
新增加的字段一定是放在表的最后。
*、修改表字段的类型和长度
ALTER TABLE table modify (col datatype [default],...,....);
对默认值的修改只会影响到新插入的行。
如果字段下有值的话，类型的修改成功率很小（要修改数据类型，要修改的列必须为空，即没
有数据项）。
CHAR 类型不能修改长度。
*、删除表字段
ALTER TABLE table DROP COLUMN (COLUMN_NAME_LIST);
9I2 版可以修改列名
*、SET UNUSED 设置字段为不可用。
原理：清楚掉字典信息（撤消存储空间），不可恢复。
可以使用 SET UNUSED 选项标记一列或者多列不可用。
使用DROP SET UNUSED 选项删除被被标记为不可用的列。
语法：
ALTER TABLE table SET UNUSED (COLlist 多个) 或者 ALTER TABLE table SET UNUSED COLUMN
col 单个;
ALTER TABLE table DROP UNUSED COLUMNS;
删除表：
删除关联：drop table table_name cascade;
改对象名：
RENAME 对象原名 TO 要改的对象名；
注：必须是对象的所有者才能进行改名的操作。
4、截取：
不能回滚；
删除表中所有数据；
资源来自网络，仅供学习！ Oracle 从入门到精通
第 35 页，共 106 页
释放存储空间；
语法：
TRUNCATE TABLE 表名称；
DELETE 也可以删除所有行，但：
可以回滚。
不释放存储空间。
5、给表加注释：COMMENT
comment on table table_name is '注释内容';
6、约束条件：
如果经常用到约束条件的话，最好自己命名。
当定义约束的时候可以将定义的语句作为CREATE TABLE 中的参数的一部分来完成。
表级别约束定义：
CONSTRAINT 约束名约束条件（字段名）
约束在表上强制了规则。
如果有参照的花，约束防止表的删除。
ORACLE 支持的约束条件：
1.NOT NULL 非空
特点：唯一一个只能在列级定义的约束条件。
2.UNIQUE 唯一
允许有空值（空值不做比较）；
特点：当创建约束的时候，系统会自动创建对应其的索引。
3.PRIMARY KEY 主键
特点：当创建约束的时候，系统会自动创建对应其的索引。
在一个表中只允许一个主键。
4.FOREIGN KEY 外键
外键参照的一定是主表的主键或唯一键；
保证子表外键字段的值一定是主表中的被参照字段值的真子集；
当主表字段被参照的时候，其值不允许被直接删除。
5.CONSTRAINT 约束名 FOREIGN KEY （外键字段名） REFERENCES 主表名（主表字段名）;
如果在字段列表中定义外键就可以不写 FOREIGN KEY 关键字。
如下格式：CONSTRAINT 约束名 REFERENCES 主表名（主表字段名）;
6.ON DELETE CASCADE 当主表的行被删除的时候，要删除子表中参照主表的行。
ALTER TABLE TABLE_NAME DROP (PK) CASCADE CONSTRAINTS;把作为主键的字段也同时删除了。
7.ON DELETE SET NULL 当主表的行被删除的时候，转换子表中的参照值为空。
CHECK
定义一个每行都必须满足的条件。
CREATE TABLE table_name
( ....
资源来自网络，仅供学习！ Oracle 从入门到精通
第 36 页，共 106 页
salary number(10,2),
CONSTRAINT 约束名 CHECK(SALARY>0),
....
);
约束的使用：
约束的命名：给约束命名或者ORACLE 服务器将使用SYS_Cn 的格式为约束命名。
创建时期：在创建表的同时或者在建表之后。
定义级别：
可以在表级定义或列级定义。
在数据字典中可以查看约束。
使用ALTER TABLE 语句：
*、添加或者删除约束条件，但是不能修改约束条件。就算列名上已经有约束条件，还可以
继续添加约束条件的。
添加：ALTER TABLE table_name ADD [CONSTRAINT ] 约束名约束条件(column);
删除：ALTER TABLE table_name drop constraint 约束名;
ALTER TABLE table_name PRIMARY KEY CASCADE;删除主键的时候，不用约束名。
*、启动或禁用约束条件
ALTER TABLE table_name Disable constraint 约束名; 禁用
ALTER TABLE table_name ENABLE constraint 约束名; 启用
*、通过MODIFY 添加 NOT NULL 约束条件（因为NOT NULL 为列级约束，只能用MODIFY 添加）。
ALTER TABLE table_name MODIFY(col type NOT NULL);
查看约束条件：
//desc user_constraints
OWNER 拥有者；
CONSTRAINT_NAME 约束名称
CONSTRAINT_TYPE 约束类型
SEARCH_CONDITION check 的条件
select constraint_name, constraint_type,search_condition,status
from user_constraint where table_name='b';
2.5.视图（VIEW）
一个或多个表的数据集的逻辑表示（虚表，不存储数据）
视图不能提高查询的性能。
分类：
简单
数目：一个
函数：不包含
分组数据：不包含
可以做DML 操作
复杂
资源来自网络，仅供学习！ Oracle 从入门到精通
第 37 页，共 106 页
数目：一个或多个
函数：包含
分组数据：包含
不一定能做DML 操作
视图也可以用DESC 描述。
创建视图：
CREATE [or replace(修改视图)] [force/noforce] VIEW view_name(col coltype ,......)
as
subquery
[WITH CHECK OPTION [CONSTRAINT constraint]]
[WITH READ ONLY[CONSTRAINT constraint]];
USER_VIEWS 关于视图的字典
修改视图：
CREATE OR REPLACE 原视图名（字段列表）
AS 子查询；
包含：
GROUP BY 、DISTINCT、ROWNUM
不能对视图进行删除操作；
包含：
GROUP BY
DISTINCT
ROWNUM
通过表达式定义的列不能对视图进行修改操作；
包含：
GROUP BY
DISTINCT
ROWNUM
通过表达式定义的列
在视图中没有包含基表中的 NOT NULL 列，不能对视图进行插入操作；
使用视图的原因；
为了限制对数据的访问；
为了使复杂的查询变得简单；
提供了数据的独立性；
提供了对相同数据的不同显示；
使用WITH CHECK OPTION 子句创建视图
创建视图时通过 WITH CHECK OPTION 子句确保执行的DML 语句不会引起数据不出现在视图上。
在对视图做DML 操作的时候，一定要符合WHERE 子句中的条件。
CREATE OR REPLACE VIEW empvu20 as select * from employees
where check option constraint [empvu20_ck];
资源来自网络，仅供学习！ Oracle 从入门到精通
第 38 页，共 106 页
WITH READ ONLY
不可以进行DML 操作；
删除视图：
DROP VIEW view_name;
行内视图：是一个在SQL 语句中使用的带有别名的子查询，该子查询放在FROM 之后；
TOP-N:
select [col_list],rownum rank(排名)
from (select [col_list] from table_name order by top-n_col)
where rownum<=n;
序列（SEPUENCE）产生的顺序数字，单向递增或单向递减，且步长相同。
索引（INDEX）用于提高查询性能。
同义词（SYNONYM）对象的别名。
create public synonym e for hr.employees;
user_synonyms;
创建同义词要有权限，访问的时候也需要权限。
2.6、序列：
自动产生的唯一值；
一个共享的对象；
典型的用法是作为主键的值；
insert into 给主键提供值。
替代了应用的代码；
通过将序列CACHE（预先生成一部分序列号，放入到内存中）到内存中，可以加速对序列的访
问。
CREATE SEQUENCE sequence_name
[increment by n]//步长
[start with n]//起始点
[maxvalue n/nomaxvalue]//递增
[minvalue n/nominvalue]//递减
[cycle/nocycle]//循环
[cache n/nocache];//n 为预先生成序列号的个数，默认为20。
查询序列：
user_sequences
last_number 序列将要产生的下一个号是多少；
select sequence_name,min_value,max_value,increment_by last_number from
user_sequences;
伪列：NEXTVAL 引用下一个可用的序列值，不同的用户每次引用都会获得一个唯一的值。
资源来自网络，仅供学习！ Oracle 从入门到精通
第 39 页，共 106 页
CURRVAL 得到当前的值（刚被领走的号）。
在CURRVAL 执行前必须先通过NEXTVAL 得到一个初始的值。
序列名.NEXTVAL/CURRVAL
序列发生间隙是正常的，保证唯一即可。
序列的修改：
ALTER SEQUENCE sequence_name
increment by
maxvalue
cycle
cache;
start with 不能修改。
删除序列：
drop SEQUENCE sequence_name;
2.7、索引：
一个方案中的对象；
被ORACLE 服务器用来加速对表的查询；
通过使用快速路径访问方法快速定位数据；
与表独立存放；
被ORACLE 服务器使用和维护。
一定是 WHERE 条件的才有可能使用索引。
手动创建索引:
CREATE INDEX index_name on table_name (col_name);
考虑创建索引的情况：
*、包含了大量不同值的列；
*、包含了大量空值的列；
*、一个或者多个列经常被一起出现在 WHERE 条件中或者作为连接的条件出现；
*、表的数据量很大，而且对表的查询经常是得到表中数据的2%到4%（少量数据）.
不应该创建索引的情况：
*、一个很小的表；
*、列很少被用于查询的条件；
*、表上的大多数查询是得到大量数据的；
*、表中的数据经常发生变动；
*、要被索引的列被作为条件表达式的一部分。
查看：
user_indexes 得到索引的定义和唯一性。
user_ind_columns 得到索引的名称，表名和列名。
select ic.index_name,ic.column_name,ic.column_position, ic.uniquenes
from user_indexes ix,user_ind_columns ic
where ic.index_name=ix.index_name and ic.table_name='table_name';
资源来自网络，仅供学习！ Oracle 从入门到精通
第 40 页，共 106 页
删除索引：
DROP INDEX index_name;
为了删除索引，必须拥有索引或者拥有DROP ANY INDEX 权限。
2.8 控制用户的访问
1.数据库的安全性
系统安全性：
系统权限(system privilege)，获得访问数据库的能力。
超过一百个
创建新用户：
CREATE USER user_name IDENTIFIED BY password;
删除用户
删除表
授予权限：
GRANT priv_list TO user/public/role(角色);
grant create session ,create table,create sequence to user_name;
在授予建表权限的同时也应该赋予存储空间。
分配配额：ALTER USER user_name QUOTA nM ON space_name;
数据安全性：
对象权限(object privilege)，获得维护数据库的能力。
每种对象的权限都不相同。
对象的所有者拥有对象的所有权限。
对象的所有者可以将自己的对象权限赋予其他人。
GRANT object_priv_list [(col_list)]
ON owner.object TO user/role/public
[WITH GRANT OPTION];--将权限授予用户的同时，该用户也拥有了授予其他用户对象权限的
功能。（及联授予）会导致及联移除。
移除权限：
REVOKE priv_list/all ON object FROM user;
方案：数据库对象的集合，包括表、视图、序列.....。
2.角色：
CREATE ROLE role_name;
GRANT priv_list TO role_name;
GRANT role_name TO user_list/role_list;
修改口令：
方法（1）ALTER USER user_name IDENTIFIED BY password;
（2）password + 回车
资源来自网络，仅供学习！ Oracle 从入门到精通
第 41 页，共 106 页
USER_SYS_PRIVS 当前用户的系统权限。
USER_ROLE_PRIVS 当前用户的角色权限。
USER_tab_privs_made 用户对象被授予的他人的信息。
3.使用集合操作
UNION
select employee_id,job_id from employees
union
select employee_id,job_id from job_history;
两个表的并集，但不显示重复行。执行的时候要先排序再剔重，所以结果集是有序的。
union all
也是两个表的并集，而且显示重复行。
语法同上。
intersect
select employee_id,job_id from employees
intersect
select employee_id,job_id from job_history;
minus
select employee_id,job_id from employees
minus
select employee_id,job_id from job_history;
e-j=e-e 与j 的交集；
j-e=j-j 与e 的交集；
注：在select 列表中的表达式必须有同样的数目和类型。
匹配 SELECT 语句
select employee_id,job_id,salary from employees
union
select employee_id,job_id,0 from job_history;
括号可以用拉修改序列的执行顺序。
4.ORDER BY 子句：
只能在整个集合的最后出现；
可以按照第一个 SELECT 语句中的列名，别名或者位置号排序。
资源来自网络，仅供学习！ Oracle 从入门到精通
第 42 页，共 106 页
5.GROUP BY 子句的增强
CUBE 操作符的 GROUP BY
在 GROUP BY 子句中使用 ROLLUP 或者 CUBE 来产生分组小计；
ROLLUP 分组产生包括规则的分组结果和小计的结果的组合；
GROUP BY [ROLLUP]（col_name_list）
ROLLUP:
a ab abc
abc
ab ab
a a a
all all all
CUBE 分组产生包括 ROLLUP 产生的结果和交叉分组小计。
cube:
a ab abc
a ab abc
all a ab
b ac
all bc
a
b
c
all
6.GROUPING 函数
参数一定是在CUBE 或ROLLUP 里进行分组排序的字段或表达式之一。
通过1 或0 来判断结果集中的空值是由于本身列的值是空的，还是由于使用CUBE 或ROLLUP 产
生的空值。
1 代表是由于分组产生的空值，没有参与分组。
0 代表是由于列本身产生的空值，参与了分组，但分组中没有包含它。
GROUPING SETS
可以使用 GROUPING SETS 在同一个语句中定义多个组集。
只需要访问一次基表。
不需要写很复杂的UNION 语句。
GROUPING SETS 子句中组合的元素越多，语句的执行性能就越好。
group by GROUPING SETS((abc),(ab),(bc),(a),(b))
组合列：
是一个列的组合，在分组计算时被作为一个单元处理。
资源来自网络，仅供学习！ Oracle 从入门到精通
第 43 页，共 106 页
2.9 高级子查询
1. 成对子查询：
行内视图的性能比成对子查询的性能高。
相关子查询：
主查询的字段在子查询里做条件（特征）。
主查询先执行，取出第一条数据，把该数据传入子查询做比较，返回查询结果给主查询，主查
询根据这个结果再做查询
依次类推
直到主查询中没有可查询列为止。
EXISTS 操作符
EXISTS 操作符测试子查询的结果是否存在；
返回 TRUE 或 FALSE
查询机制：
如果一个子查询找到了结果：
在内部子查询中不在继续执行，条件被设为TRUE
如果一个子查询没有找到结果：条件被设为FALSE
select col_list from table_name tab_alias
where exists (select 'x' from table_name where col=tab_alias.col);
用的是相关子查询
NO EXISTS 操作符
和NOT IN 相对应，速度要快，性能好。
UPDATE 中的相关子查询
update emp e
set department_name in(select d.department_name from departments d where
e.department_id=d.department_id);
delete 中的相关子查询
2.层次查询
select [level],column,expr from table [where condition]
[start with]起点（自底向上/自顶向下）
[connect by prior + 主键/外键=外键/主键]//看你往哪个方向查
自顶向下左边放主键，右边放外键
select employee_id,last_name,salary,job_id,manager_id
from employees
start with manager_id is null
connect by prior employee_id=manager_id；
自底向上右边放主键，左边放外键
level（伪列）
层次的级别：不固定值。
资源来自网络，仅供学习！ Oracle 从入门到精通
第 44 页，共 106 页
使用 level 和 LPAD 层次化格式的显示
修剪分支
Oracle 9i 对DML 和DDL 语句的扩展
多表插入的 INSERT 语句
insert .... select 语句可以被用来在单个DML 语句中向多个表插入数据。
多表插入语句：
无条件INSERT
条件 ALL INSERT
条件 FIRST INSERT
轮巡 INSERT
二、Management：
1.Oracle 的构件和组件
instance 实例/例程
database 数据库
SGA 系统全局区
shared pool 共享池
library cache 库高速缓存区
data dictionary cache 字典高速缓存区
database buffer cache 数据库高速缓存区
redo log buffer 重做日志缓冲区
java pool java 池
large pool 大池
PMON 进程监视进程
SMON 系统监视进程
DBWR 数据库写进程
LGWR 日志写进程
CKPT 检查点进程
data file 数据文件
control file 控制文件
redo log file 重做日志文件
parameter file 初始化参数文件
password file 口令文件
archived log file 归档日志文件
user process 用户进程
server process 服务进程
PGA 程序全局区
tablespace 表空间
资源来自网络，仅供学习！ Oracle 从入门到精通
第 45 页，共 106 页
2.数据库的物理结构：
1.控制文件
2. 数据文件
3. 重做日志文件
4. data file 数据文件：
5. 作用：存放数据。
6. 数据文件大小可以扩展。
7. tablespace 表空间：一个或多个数据文件的逻辑组成。
一个数据库最少有一个system 表空间，一个表空间最少有一个数据文件。
8. redo log file 重做日志文件
重做日志：在数据库中发生的所有改变，改变的每一条信息都叫做一条重做日志信息。
存放重做日志信息的文件就叫做重做日志文件。
作用：利用日志记录可以恢复损坏的数据库，保证数据的可恢复性。
重做日志组：包含一个或多个日志文件。
特点：
一个数据库最少有两个组，一个组最少有一个组成员。
重做日志的大小是固定的。
写入方式是顺序写入，按时间。
切换（一个写满往下一个写），循环（都写满了就重新回到头组写）。
9. control file 控制文件
数据库中的核心文件。
存放内容：
数据库的名称和编号，数据库的结构信息（数据文件和重做日志文件的地址）
当前的SCN system change number 系统改变号：
给系统的每一次改变编号（最后一次同步的SCN 号）。
控制文件的SCN 号和所有存放数据文件的头部的SCN 相同。
启动的时候，一样启动，不一样恢复。
特点：一个数据库最少需要一个控制文件，但一般情况下都会有复用/副本。
10. parameter file 初始化参数文件
非必要文件
跟实例有关。
资源来自网络，仅供学习！ Oracle 从入门到精通
第 46 页，共 106 页
11. password file 口令文件
非必要文件。
作用：做特权身份认证。
sysdba/sysoper/普通身份
12. archived log file 归档日志文件
非必要文件。
内容：
重做日志文件（redo log file）内容的备份。
作用：使数据库所有的日志都会保留下来。
特点：每个重做日志内容都会对应一个归档文件。
LOG SEQUENCE 日志序列号：重做日志内容的序列号。
非归档日志：
没有归档日志文件；
性能要好一些；
可以选择归档模式或非归档模式。
3.instance 实例/例程
启动的步骤：
（1）启动实例；
实例包含：
1.SGA
2.后台进程
一个实例只有一个SGA，可以有多个PGA，建立一次连接(会话)就产生一次PGA。
ORACLE 的内存结构：
SGA 在实例启动的时候分配，是ORACLE 实例的基本组件；大小可以动态改变（内部大
小）
通过SGA_MAX_SIZE 定义大小（总大小是静态的）
PGA 程序全局区(私有区域)：在服务进程启动的时候分配。
PGA 程序全局区
是为每个用户进程连接ORA 数据库提供的内存区域；
当进程创建时分配；
当进程结束时释放；
一个PGA 只被一个进程使用；
包含：有连接的信息
SGA 系统全局区
所有数据库的后台进程和服务进程的共用内存区域。
包含：
1.shared pool 共享池
资源来自网络，仅供学习！ Oracle 从入门到精通
第 47 页，共 106 页
设置大小的参数：SHARED_POOL_SIZE
包含：
library cahce 库高速缓存区
大小由共享池决定。
包含：SQL 语句和SQLPLUS 文本，分析代码，执行计划
存储目的：与性能有关。
当两个语句相同的时候不需要重新分析，使用相同的执行计划即可。
管理方法：采用 LRU （最近使用算法）least recent used 当空间不足的时候，使用LRU。
结构： SQL 区域、PL/SQL 区域
data dictionary cache 字典高速缓存区
内容：最近最多使用的数据字典信息
作用：为了能够在分析的时候所需要的字典信息能在内存中找到，避免使用物理I/O。
管理方法：采用 LRU （最近使用算法）
2.database buffer cache 数据库高速缓存区
单位：块。
内容：最近最常数据块
作用：减少物理I/O，提高性能
参数：DB_BLOCK_SIZE 决定主数据块的大小。
会影响性能；
设置后不允许修改；
管理方法：采用 LRU （最近使用算法）
由独立的自缓冲区组成：
DB_CACHE_SIZE
CB_KEEP_CACHE_SIZE
DB_RECYCLE_CACHE_SIZE
可以通过ALTER SYSTEM 命令动态增加或收缩：
ALTER SYSTEM SET DB_CACHE_SIZE =96M;
redo log buffer 重做日志缓冲区
记录了对数据块所作的所有修改。
作用：数据库的恢复
大小由LOG_BUFFER 决定。
顺序单条写入，批量写出到文件。
java pool java 池
可选
java_pool_size 大小
large pool 大池
可选
减轻共享池的负担。
被用于： 1.在共享服务器中的会话的内存需求（UGA）；
2.I/O 辅助；
3.备份恢复操作或RMAN；
4.并行执行的信息缓冲区；
large_pool_size 大小
资源来自网络，仅供学习！ Oracle 从入门到精通
第 48 页，共 106 页
4、进程结构
1. 用户进程：开始于数据库用户请求连接数据库
2. 服务进程：与ORA 实例连接，开始于用户会话的建立。
分为：专用服务进程、共享服务进程
性能专用更好。
利用资源方面共享更好（网站方面）。
3. 后台进程：当ORA 实例启动时启动
后台进程包含：
1.PMON 进程监视进程
监视用户进程（客户端连接服务器的进程）到服务进程（在服务器端响应用户进程的进程）的
连接。
创建会话。
监视会话是否异常中断，如果中断：PMON 会回滚事务、解锁、释放资源。
2.SMON 系统监视进程
任务：会检测SCN 号，相等：启动
不相等，实例恢复（1）前滚将日志应用
（2）打开数据库
（3）恢复数据库
恢复的起点是走后一次CHECKPOINT 的位置。
每3 秒合并空闲空间
释放临时段
临时段：暂时存放在排序时中没有空间的字段值。
在排序中产生的。
数据量大的时候，排序是分成若干块执行的，当字段值排好序之后就放到临时段中。
1. DBWR 数据库写进程
/DBW0/DBWn n：0-9
将脏数据写回到数据文件中。
当发生以下情况执行：
当发生检查点事件的时候，checkpoint
资源来自网络，仅供学习！ Oracle 从入门到精通
第 49 页，共 106 页
脏块达到极限值；
没有空间的缓冲空间
超时
RAC PING 请求
表空间离线
表空间只读
在表执行DROP 或TRUNCATE
表空间上执行BEGIN BACKUP
2. LGWR 重作日志写进程
把重做日志缓冲区的内容写出到日志文件（顺序写出，按时间）。
执行条件：
事务提交
先写日志后写数据的好处：
最快地保证数据不丢失
快
重作日志缓冲区三分之一满
每3 秒
有一条超过1MB 的重作日志记录
DBWn 进程操作前
6.CKPT 检查点进程
先执行DBWR，写数据，再进行同步。
提供数据库同步性；
在执行检查点时通知DBWn 执行写操作。
7.ARCn 归档进程（可选）
保存数据库的所有修改记录
当数据库在ARCHIVELOG 模式的时候自动归档当前的重做日志记录。
8.LOGICAL STRUCTURE 逻辑结构
表明了物理空间的使用情况。
由表空间(tablespace)，段(segment)，区(extent)，和数据块(blocks)组成。
*&*cmd+回车
DBCA 命令
创建和删除数据库命令
/nolog 登陆SQLPLUS 但不连接数据库
在用户名提示框中出入
可以有选择地登陆库和用户
资源来自网络，仅供学习！ Oracle 从入门到精通
第 50 页，共 106 页
5、OEM ORACLE 企业管理器
sysman 是OEM 这个软件的用户
配置OEM 的资料档案库：（repository）就是创建一个数据库用户的过程。
建的是在数据库中的创建一个数据库�

收藏举报

TAG:

查看全部评论