ORACLE__SQL语句教学

1-1 Copyright © Oracle Corporation,  2001. All rights reserved.
基本SQL SELECT 语句
1-2 Copyright © Oracle Corporation,  2001. All rights reserved.
目标
通过本章学习，您将可以:
• 列举SQL SELECT语句的功能。
• 执行简单的选择语句。
• SQL 语言和SQL*Plus  命令的不同。
1-3 Copyright © Oracle Corporation,  2001. All rights reserved.
SQL SELECT 语句的功能
行 列
表1 表2
表
Join
表
1-4 Copyright © Oracle Corporation,  2001. All rights reserved.
基本SELECT 语句
SELECT *|{[DISTINCT] column|expression[alias],...}
FROM table;
• SELECT 标识选择哪些列。
• FROM 标识从哪个表中选择。
1-5 Copyright © Oracle Corporation,  2001. All rights reserved.
SELECT *
FROM   departments;
选择全部列
1-6 Copyright © Oracle Corporation,  2001. All rights reserved.
选择特定的列
SELECT department_id, location_id
FROM   departments;
1-7 Copyright © Oracle Corporation,  2001. All rights reserved.
Writing SQL Statements
• SQL 语言大小写不敏感。
• SQL 可以写在一行或者多行
• 关键字不能被缩写也不能分行
• 各子句一般要分行写。
• 使用缩进提高语句的可读性。
1-8 Copyright © Oracle Corporation,  2001. All rights reserved.
列头设置
• SQL*Plus:
– 字符和日期类型的列左对齐
– 字符类型的列右对齐
– 默认头显示方式:大写
1-9 Copyright © Oracle Corporation,  2001. All rights reserved.
算术运算符
数字和日期使用的数学表达式。
操作符
+
-*
/      
描述
加
减
乘
除
1-10 Copyright © Oracle Corporation,  2001. All rights reserved.
使用数学运算符
SELECT last_name, salary, salary + 300
FROM   employees;
…
1-11 Copyright © Oracle Corporation,  2001. All rights reserved.
操作符优先级
• 乘除的优先级高于加减。
• 同一优先级运算符从左向右执行。
• 括号内的运算先执行。
*
/ +
_
1-12 Copyright © Oracle Corporation,  2001. All rights reserved.
操作符优先级
SELECT last_name, salary, 12*salary+100
FROM   employees;
…
1-13 Copyright © Oracle Corporation,  2001. All rights reserved.
使用括号
SELECT last_name, salary, 12*(salary+100)
FROM   employees;
…
1-14 Copyright © Oracle Corporation,  2001. All rights reserved.
定义空值
• 空值是无效的，未指定的，未知的或不可预知的值。
• 空值不是空格或者0 。
SELECT last_name, job_id, salary, commission_pct
FROM   employees;
…
…
1-15 Copyright © Oracle Corporation,  2001. All rights reserved.
SELECT last_name, 12*salary*commission_pct
FROM   employees;
空值在数学运算中的使用
包含空值的数学表达式的值都为空值
…
…
1-16 Copyright © Oracle Corporation,  2001. All rights reserved.
列的别名
列的别名:
• 重命名一个列。
• 便于计算。
• 紧跟列名，也可以在列名和别名之间加入关键字‘AS’
，以便在别名中包含空格或特殊的字符并区分大小写。
1-17 Copyright © Oracle Corporation,  2001. All rights reserved.
使用别名
SELECT last_name "Name", salary*12 "Annual Salary"
FROM   employees;
SELECT last_name AS name, commission_pct comm
FROM   employees;
…
…
1-18 Copyright © Oracle Corporation,  2001. All rights reserved.
连接符
连接符:
• 把列与列，列与字符连接在一起。
• 用‘|| ’表示。
• 可以用来‘合成’列。
1-19 Copyright © Oracle Corporation,  2001. All rights reserved.
连接符应用举例
SELECT last_name||job_id AS "Employees"
FROM  employees;
…
1-20 Copyright © Oracle Corporation,  2001. All rights reserved.
字符串
• 字符串可以是SELECT 列表中的一个字符, 数字, 日期。
• 日期和字符只能在单引号中出现。
• 每当返回一行时，字符串被输出一次。
1-21 Copyright © Oracle Corporation,  2001. All rights reserved.
字符串
SELECT last_name ||' is a '||job_id
AS "Employee Details"
FROM   employees;
…
1-22 Copyright © Oracle Corporation,  2001. All rights reserved.
重复行
默认情况下，查询会返回全部行，包括重复行。
SELECT department_id
FROM   employees;
…
1-23 Copyright © Oracle Corporation,  2001. All rights reserved.
删除重复行
在SELECT 子句中使用关键字‘DISTINCT ’删除重复行
。
SELECT DISTINCT department_id
FROM   employees;
1-24 Copyright © Oracle Corporation,  2001. All rights reserved.
SQL 和SQL*Plus
SQL 语句
Oracle
服务器
查询结果 SQL*Plus
命令
客户端
格式化报表
浏览器
SQL*Plus
1-25 Copyright © Oracle Corporation,  2001. All rights reserved.
SQL 语句与
SQL*Plus  命令
SQL
statements
SQL
• 一种语言
• ANSI 标准
• 关键字不能缩写
• 使用语句控制数据库中的表
的定义信息和表中的数据
SQL*Plus
• 一种环境
• Oracle 的特性之一
• 关键字可以缩写
• 命令不能改变数据库中的数
据的值
• 集中运行
SQL*Plus
commands
1-26 Copyright © Oracle Corporation,  2001. All rights reserved.
SQL*Plus
使用SQL*Plus可以:
• 描述表结构。
• 编辑SQL 语句。
• 执行SQL语句。
• 将SQL 保存在文件中并将SQL语句执行结果保存在文
件中。
• 在保存的文件中执行语句。
• 将文本文件装入SQL*Plus编辑窗口。
1-27 Copyright © Oracle Corporation,  2001. All rights reserved.
显示表结构
使用DESCRIBE 命令，表示表结构
DESC[RIBE] tablename
1-28 Copyright © Oracle Corporation,  2001. All rights reserved.
显示表结构
DESCRIBE employees
1-29 Copyright © Oracle Corporation,  2001. All rights reserved.
总结
SELECT *|{[DISTINCT] column|expression [alias],...}
FROM table;
通过本课，您应该可以完成:
• 书写SELECT 语句:
– 返回表中的全部数据。
– 返回表中指定列的数据。
– 使用别名。
• 使用SQL*Plus  环境，书写，保存和执行SQL 语句和
SQL*Plus  命令。
1-30 Copyright © Oracle Corporation,  2001. All rights reserved.
过滤和排序数据
1-31 Copyright © Oracle Corporation,  2001. All rights reserved.
目标
通过本章学习，您将可以:
• 在查询中过滤行。
• 在查询中对行进行排序。
1-32 Copyright © Oracle Corporation,  2001. All rights reserved.
在查询中过滤行
返回在90好部门工
作的所有员工的信
息
EMPLOYEES
…
1-33 Copyright © Oracle Corporation,  2001. All rights reserved.
过滤
• 使用WHERE子句，将不满足条件的行过滤掉。
• WHERE子句紧随FROM 子句。
SELECT *|{[DISTINCT] column|expression [alias],...}
FROM table
[WHERE condition(s)];
1-34 Copyright © Oracle Corporation,  2001. All rights reserved.
WHERE子句
SELECT employee_id, last_name, job_id, department_id
FROM   employees
WHERE  department_id = 90 ;
1-35 Copyright © Oracle Corporation,  2001. All rights reserved.
字符和日期
• 字符和日期要包含在单引号中。
• 字符大小写敏感，日期格式敏感。
• 默认的日期格式是DD-MON-RR。
SELECT last_name, job_id, department_id
FROM   employees
WHERE  last_name = 'Whalen';
1-36 Copyright © Oracle Corporation,  2001. All rights reserved.
比较运算
操作符
=
>
>=
<
<=
<>
含义
等于
大于
大于、等于
小于
小于、等于
不等于
1-37 Copyright © Oracle Corporation,  2001. All rights reserved.
SELECT last_name, salary
FROM   employees
WHERE  salary <= 3000;
比较运算
1-38 Copyright © Oracle Corporation,  2001. All rights reserved.
其它比较运算
操作符
BETWEEN
...AND...
IN(set)
LIKE
IS NULL
含义
在两个值之间( 包含边界)
等于值列表中的一个
模糊查询
空值
1-39 Copyright © Oracle Corporation,  2001. All rights reserved.
BETWEEN
使用BETWEEN  运算来显示在一个区间内的值。
SELECT last_name, salary
FROM   employees
WHERE  salary BETWEEN 2500 AND 3500;
Lower limit Upper limit
1-40 Copyright © Oracle Corporation,  2001. All rights reserved.
SELECT employee_id, last_name, salary, manager_id
FROM   employees
WHERE  manager_id IN (100, 101, 201);
IN
使用IN运算显示列表中的值。
1-41 Copyright © Oracle Corporation,  2001. All rights reserved.
LIKE
• 使用LIKE 运算选择类似的值
• 选择条件可以包含字符或数字:
– % 代表一个或多个字符。
– _ 代表一个字符。
SELECT first_name
FROM  employees
WHERE first_name LIKE 'S%';
1-42 Copyright © Oracle Corporation,  2001. All rights reserved.
• ‘%’ 和‘-’可以同时使用。
• 可以使用ESCAPE 标识符选择‘% ’和‘ _ ’ 符号。
LIKE
SELECT last_name
FROM   employees
WHERE  last_name LIKE '_o%';
1-43 Copyright © Oracle Corporation,  2001. All rights reserved.
NULL
使用NULL 判断空值。
SELECT last_name, manager_id
FROM   employees
WHERE  manager_id IS NULL;
1-44 Copyright © Oracle Corporation,  2001. All rights reserved.
逻辑运算
操作符
AND
OR
NOT
含义
逻辑并
逻辑或
逻辑否
1-45 Copyright © Oracle Corporation,  2001. All rights reserved.
AND
AND 要求和的关系为真。
SELECT employee_id, last_name, job_id, salary
FROM   employees
WHERE  salary >=10000
AND    job_id LIKE '%MAN%';
1-46 Copyright © Oracle Corporation,  2001. All rights reserved.
OR
OR要求或关系为真。
SELECT employee_id, last_name, job_id, salary
FROM   employees
WHERE  salary >= 10000
OR     job_id LIKE '%MAN%';
1-47 Copyright © Oracle Corporation,  2001. All rights reserved.
SELECT last_name, job_id
FROM   employees
WHERE  job_id
NOT IN ('IT_PROG', 'ST_CLERK', 'SA_REP');
NOT
1-48 Copyright © Oracle Corporation,  2001. All rights reserved.
优先级
可以使用括号改变优先级顺序
优先级
1 算术运算符
2 连接符
3 比较符
4 IS[NOT]NULL,  LIKE,  [NOT]IN
5 [NOT] BETWEEN
6 NOT
7 AND
8 OR
1-49 Copyright © Oracle Corporation,  2001. All rights reserved.
SELECT last_name, job_id, salary
FROM   employees
WHERE  job_id = 'SA_REP'
OR     job_id = 'AD_PRES'
AND    salary > 15000;
优先级
1-50 Copyright © Oracle Corporation,  2001. All rights reserved.
SELECT last_name, job_id, salary
FROM   employees
WHERE  (job_id = 'SA_REP'
OR     job_id = 'AD_PRES')
AND    salary > 15000;
优先级
使用括号控制执行顺序。
1-51 Copyright © Oracle Corporation,  2001. All rights reserved.
SELECT   last_name, job_id, department_id, hire_date
FROM     employees
ORDER BY hire_date ;
ORDER BY 子句
• 使用ORDER BY 子句排序
– ASC: 升序
– DESC: 降序
• ORDER BY 子句在SELECT 语句的结尾。
…
1-52 Copyright © Oracle Corporation,  2001. All rights reserved.
降序排序
SELECT   last_name, job_id, department_id, hire_date
FROM     employees
ORDER BY hire_date DESC ;
…
1-53 Copyright © Oracle Corporation,  2001. All rights reserved.
按别名排序
SELECT employee_id, last_name, salary*12 annsal
FROM   employees
ORDER BY annsal;
…
1-54 Copyright © Oracle Corporation,  2001. All rights reserved.
• 按照ORDER BY 列表的顺序排序。
• 可以使用不在SELECT 列表中的列排序。
SELECT last_name, department_id, salary
FROM   employees
ORDER BY department_id, salary DESC;
多个列排序
…
1-55 Copyright © Oracle Corporation,  2001. All rights reserved.
总结
SELECT     *|{[DISTINCT]  column|expression [alias],...}
FROM       table
[WHERE     condition(s)]
[ORDER BY  {column, expr, alias} [ASC|DESC]];
通过本课，您应该可以完成:
• 使用WHERE子句过滤数据
– 使用比较运算
– 使用BETWEEN,  IN,  LIKE和NULL运算
– 使用逻辑运算符AND ,  OR和NOT
• 使用ORDER BY 子句进行排序。
1-56 Copyright © Oracle Corporation,  2001. All rights reserved.
单行函数
1-57 Copyright © Oracle Corporation,  2001. All rights reserved.
目标
通过本章学习，您将可以:
• SQL中不同类型的函数。
• 在SELECT 语句中使用字符，数字和日期函数。
• 描述转换型函数的用途。
1-58 Copyright © Oracle Corporation,  2001. All rights reserved.
SQL 函数
函数
输入
参数1
参数2
参数n
函数执行
输出
结果
1-59 Copyright © Oracle Corporation,  2001. All rights reserved.
两种SQL 函数
函数
单行函数 多行函数
1-60 Copyright © Oracle Corporation,  2001. All rights reserved.
单行函数
单行函数:
• 操作数句对象
• 接受函数返回一个结果
• 只对一行进行变换
• 每行返回一个结果
• 可以转换数据类型
• 可以嵌套
• 参数可以是一列或一个值
function_name [(arg1, arg2,...)]
1-61 Copyright © Oracle Corporation,  2001. All rights reserved.
单行函数
转换
字符
数值
日期
通用
单行函数
1-62 Copyright © Oracle Corporation,  2001. All rights reserved.
字符函数
字符函数
LOWER
UPPER
INITCAP
CONCAT
SUBSTR
LENGTH
INSTR
LPAD | RPAD
TRIM
REPLACE
大小写控制函数 字符控制函数
1-63 Copyright © Oracle Corporation,  2001. All rights reserved.
函数 结果
大小写控制函数
这类函数改变字符的大小写。
LOWER('SQL Course')
UPPER('SQL Course')
INITCAP('SQL Course')
sql course
SQL COURSE
Sql Course
1-64 Copyright © Oracle Corporation,  2001. All rights reserved.
大小写控制函数
显示员工Higgins的信息:
SELECT employee_id, last_name, department_id
FROM   employees
WHERE  last_name = 'higgins';
no rows selected
SELECT employee_id, last_name, department_id
FROM   employees
WHERE  LOWER(last_name) = 'higgins';
1-65 Copyright © Oracle Corporation,  2001. All rights reserved.
CONCAT('Hello', 'World')
SUBSTR('HelloWorld',1,5)
LENGTH('HelloWorld')
INSTR('HelloWorld', 'W')
LPAD(salary,10,'*')
RPAD(salary, 10, '*')
TRIM('H' FROM 'HelloWorld')
HelloWorld
Hello
10
6
*****24000
24000*****
elloWorld
函数 结果
字符控制函数
这类函数控制字符:
1-66 Copyright © Oracle Corporation,  2001. All rights reserved.
SELECT employee_id, CONCAT(first_name, last_name) NAME,
job_id, LENGTH (last_name),
INSTR(last_name, 'a') "Contains 'a'?"
FROM   employees
WHERE  SUBSTR(job_id, 4) = 'REP';
字符控制函数
1
2
3 1 2
3
1-67 Copyright © Oracle Corporation,  2001. All rights reserved.
数字函数
• ROUND:  四舍五入
ROUND(45.926, 2) 45.93
• TRUNC: 截断
TRUNC(45.926, 2) 45.92
• MOD :  求余
MOD(1600, 300) 100
1-68 Copyright © Oracle Corporation,  2001. All rights reserved.
SELECT ROUND(45.923,2), ROUND(45.923,0),
ROUND(45.923, -1)
FROM   DUAL;
ROUND函数
DUAL 是一个‘伪表’，可以用来测试函数和表达式。
1 2
3
3 1 2
1-69 Copyright © Oracle Corporation,  2001. All rights reserved.
SELECT  TRUNC(45.923,2), TRUNC(45.923),
TRUNC(45.923, -2)
FROM   DUAL;
TRUNC函数
3 1 2
1 2
3
1-70 Copyright © Oracle Corporation,  2001. All rights reserved.
SELECT last_name, salary, MOD(salary, 5000)
FROM   employees
WHERE  job_id = 'SA_REP';
MOD 函数
1-71 Copyright © Oracle Corporation,  2001. All rights reserved.
日期
• Oracle 内部使用数字存储日期:  世纪, 年, 月, 日, 小时, 分
钟, 秒。
• 默认的日期格式是DD-MON-RR.
– 可以只指定年的后两位在20世纪存放21世纪的日期。
– 同样可以在21世纪存放20世纪的日期。
SELECT last_name, hire_date
FROM   employees
WHERE  last_name like 'G%';
1-72 Copyright © Oracle Corporation,  2001. All rights reserved.
日期
函数SYSDATE返回:
• 日期
• 时间
1-73 Copyright © Oracle Corporation,  2001. All rights reserved.
日期的数学运算
• 在日期上加上或减去一个数字结果仍为日期。
• 两个日期相减返回日期之间相差的天数。
• 可以用数字除24来向日期中加上或减去小时。
1-74 Copyright © Oracle Corporation,  2001. All rights reserved.
日期的数学运算
SELECT last_name, (SYSDATE-hire_date)/7 AS WEEKS
FROM   employees
WHERE  department_id = 90;
1-75 Copyright © Oracle Corporation,  2001. All rights reserved.
日期函数
两个日期相差的月数 MONTHS_BETWEEN
ADD_MONTHS
NEXT_DAY
LAST_DAY
ROUND
TRUNC
向指定日期中加上若干月数
指定日期的下一个日期
本月的最后一天
日期四舍五入
日期截断
函数 描述
1-76 Copyright © Oracle Corporation,  2001. All rights reserved.
• MONTHS_BETWEEN ('01- SEP - 95','11- JAN - 94')
日期函数
• ADD_MONTHS ('11- JAN - 94',6)
• NEXT_DAY ('01 - SEP - 95','FRIDAY')
• LAST_DAY('01- FEB - 95')
19.6774194
'11 - JUL - 94'
'08 - SEP - 95'
'28 - FEB - 95'
1-77 Copyright © Oracle Corporation,  2001. All rights reserved.
• ROUND(SYSDATE,'MONTH')         01 - AUG - 95
• ROUND(SYSDATE ,'YEAR')         01 - JAN - 96
• TRUNC(SYSDATE ,'MONTH')        01 - JUL - 95
• TRUNC(SYSDATE ,'YEAR')         01 - JAN - 95
日期函数
Assume SYSDATE = '25 - JUL - 95' :
1-78 Copyright © Oracle Corporation,  2001. All rights reserved.
转换函数
隐性 显性
数据类型转换
1-79 Copyright © Oracle Corporation,  2001. All rights reserved.
隐式数据类型转换
Oracle 自动完成下列转换：
VARCHAR2 or CHAR
源数据类型 目标数据类型
VARCHAR2 or CHAR
NUMBER
DATE
NUMBER
DATE
VARCHAR2
VARCHAR2
1-80 Copyright © Oracle Corporation,  2001. All rights reserved.
隐式数据类型转换
表达式计算中, Oracle 自动完成下列转换:
VARCHAR2 or CHAR
源数据类型 目标数据类型
VARCHAR2 or CHAR
NUMBER
DATE
1-81 Copyright © Oracle Corporation,  2001. All rights reserved.
显式数据类型转换
NUMBER CHARACTER
TO_CHAR
TO_NUMBER
DATE
TO_CHAR
TO_DATE
1-82 Copyright © Oracle Corporation,  2001. All rights reserved.
TO_CHAR函数对日期的转换
格式:
• 必须包含在单引号中而且大小写敏感。
• 可以包含任意的有效的日期格式。
• 可以使用fm去掉多余的空格或者前导零。
• 与日期指用逗号隔开。
TO_CHAR( date, 'format_model')
1-83 Copyright © Oracle Corporation,  2001. All rights reserved.
YYYY
日期格式的元素
YEAR
MM
MONTH
DY
D AY
2004
TWO THOUSAND AND FOUR
02
MON
MONDAY
J U LY
MON
JUL
DD 02
1-84 Copyright © Oracle Corporation,  2001. All rights reserved.
日期格式的元素
• 时间格式
• 使用双引号向日期中添加字符
• 日期在月份中的位置
HH24:MI:SS AM 15:45:32 PM
DD "of" MONTH 12  of OCTOBER
ddspth fourteenth
1-85 Copyright © Oracle Corporation,  2001. All rights reserved.
TO_CHAR函数对日期的转换
SELECT last_name,
TO_CHAR(hire_date, 'fmDD Month YYYY')
AS HIREDATE
FROM   employees;
…
1-86 Copyright © Oracle Corporation,  2001. All rights reserved.
TO_CHAR函数对数字的转换
下面是在TO_CHAR函数中经常使用的几种格式:
TO_CHAR( number,  'format_model')
9
0
$
L
.
,
数字
零
美元符
本地货币符号
小数点
千位符
1-87 Copyright © Oracle Corporation,  2001. All rights reserved.
SELECT TO_CHAR(salary, '$99,999.00') SALARY
FROM   employees
WHERE  last_name = 'Ernst';
TO_CHAR函数对数字的转换
1-88 Copyright © Oracle Corporation,  2001. All rights reserved.
TO_NUMBER 和TO_DATE函数
• 使用TO_NUMBER 函数将字符转换成数字:
• 使用TO_DATE函数将字符转换成日期:
• 这些函数可以使用fx修饰符。n
TO_NUMBER(char[, 'format_model'])
TO_DATE( char[, 'format_model'])
1-89 Copyright © Oracle Corporation,  2001. All rights reserved.
TO_NUMBER 和TO_DATE函数
• 使用TO_NUMBER 函数将字符转换成数字:
• 使用TO_DATE函数将字符转换成日期:
• 这些函数可以使用fx修饰符。
TO_NUMBER(char[, 'format_model'])
TO_DATE( char[, 'format_model'])
1-90 Copyright © Oracle Corporation,  2001. All rights reserved.
RR 日期格式
当前年
1995
2001
日期
27- OCT- 95
27- OCT- 17
27- OCT- 17
27- OCT- 95
RR 格式
1995
2017
1995
YY 格式
1995
1917
2017
2095
当前的年份:
0 – 49
0 – 49 50– 99
50– 99
The return date is in
the current century
The return date is in
the century after
the current one
The return date is in
the century before
the current one
The return date is in
the current century
指定的年份:
1-91 Copyright © Oracle Corporation,  2001. All rights reserved.
RR日期格式
SELECT last_name, TO_CHAR(hire_date, 'DD -Mon -YYYY')
FROM  employees
WHERE hire_date < TO_DATE('01-Jan -90', 'DD -Mon -RR');
使用RR日期格式查找雇佣日期在1990年之前的员工，
在1999或现在使用下面的命令会产生相同的结果:
1-92 Copyright © Oracle Corporation,  2001. All rights reserved.
嵌套函数
• 单行函数可以嵌套。
• 嵌套函数的执行顺序是由内到外。
F3(F2 (F1(col,arg1) ,arg2),arg3)
步骤1 =  结果1
步骤2 = 结果2
步骤3 =结果3
1-93 Copyright © Oracle Corporation,  2001. All rights reserved.
SELECT last_name,
NVL(TO_CHAR(manager_id), 'No Manager')
FROM   employees
WHERE  manager_id IS NULL;
嵌套函数
1-94 Copyright © Oracle Corporation,  2001. All rights reserved.
通用函数
这些函数适用于任何数据类型，同时也适用于空值：
• NVL (expr1, expr2)
• NVL2 (expr1, expr2, expr3)
• NULLIF (expr1, expr2)
• COALESCE (expr1, expr2, ..., expr n )
1-95 Copyright © Oracle Corporation,  2001. All rights reserved.
NVL 函数
将空值转换成一个已知的值：
• 可以使用的数据类型有日期、字符、数字。
• 函数的一般形式:
– NVL(commission_pct,0)
– NVL(hire_date,'01-JAN -97')
– NVL(job_id,'No Job Yet')
1-96 Copyright © Oracle Corporation,  2001. All rights reserved.
SELECT last_name, salary, NVL(commission_pct, 0),
(salary*12) + (salary*12*NVL(commission_pct, 0)) AN_SAL
FROM employees;
使用NVL 函数
…
1 2
1
2
1-97 Copyright © Oracle Corporation,  2001. All rights reserved.
SELECT last_name,  salary, commission_pct,
NVL2(commission_pct,
'SAL+COMM', 'SAL') income
FROM   employees WHERE department_id IN (50, 80);
使用NVL2 函数
1 2
1
2
1-98 Copyright © Oracle Corporation,  2001. All rights reserved.
SELECT first_name, LENGTH(first_name) "expr1",
last_name,  LENGTH(last_name)  "expr2",
NULLIF(LENGTH(first_name), LENGTH(last_name)) result
FROM   employees;
使用NULLIF 函数
…
1
2
3
1 2 3
1-99 Copyright © Oracle Corporation,  2001. All rights reserved.
使用COALESCE 函数
• COALESCE 与NVL 相比的优点在于COALESCE 可以同
时处理交替的多个值。
• 如果第一个表达式费空, 则返回这个表达式，对其他的
参数进行COALESCE 。
1-100 Copyright © Oracle Corporation,  2001. All rights reserved.
SELECT   last_name,
COALESCE(commission_pct, salary, 10) comm
FROM     employees
ORDER BY commission_pct;
使用COALESCE 函数
…
1-101 Copyright © Oracle Corporation,  2001. All rights reserved.
条件表达式
• 在SQL语句中使用IF -THEN -ELSE  逻辑。
• 使用两种方法:
– CASE 表达式
– DECODE 函数
1-102 Copyright © Oracle Corporation,  2001. All rights reserved.
CASE 表达式
在需要使用IF -THEN -ELSE  逻辑时:
CASE expr WHEN comparison_expr1 THEN return_expr1
[WHENcomparison_expr2 THEN return_expr2
WHEN comparison_exprn THEN return_exprn
ELSE else_expr]
END
1-103 Copyright © Oracle Corporation,  2001. All rights reserved.
SELECT last_name, job_id, salary,
CASE job_id WHEN 'IT_PROG'  THEN  1.10*salary
WHEN 'ST_CLERK' THEN  1.15*salary
WHEN 'SA_REP'   THEN  1.20*salary
ELSE      salary END     "REVISED_SALARY"
FROM   employees;
CASE 表达式
下面是使用case表达式的一个例子：
…
…
1-104 Copyright © Oracle Corporation,  2001. All rights reserved.
DECODE 函数
在需要使用IF -THEN -ELSE  逻辑时:
DECODE(col|expression, search1, result1
[, search2, result2,..., ]
[, default])
1-105 Copyright © Oracle Corporation,  2001. All rights reserved.
DECODE 函数
SELECT last_name, job_id, salary,
DECODE(job_id, 'IT_PROG',  1.10*salary,
'ST_CLERK', 1.15*salary,
'SA_REP',   1.20*salary,
salary)
REVISED_SALARY
FROM   employees;
…
…
1-106 Copyright © Oracle Corporation,  2001. All rights reserved.
DECODE 函数
SELECT last_name, salary,
DECODE (TRUNC(salary/2000, 0),
0, 0.00,
1, 0.09,
2, 0.20,
3, 0.30,
4, 0.40,
5, 0.42,
6, 0.44,
0.45) TAX_RATE
FROM   employees
WHERE  department_id = 80;
使用decode函数的一个例子：
1-107 Copyright © Oracle Corporation,  2001. All rights reserved.
总结
通过本章学习，您应该学会:
• 使用函数对数据进行计算
• 使用函数修改数据
• 使用函数控制一组数据的输出格式
• 使用函数改变日期的显示格式
• 使用函数改变数据类型
• 使用NVL 函数
• 使用IF -THEN -ELSE  逻辑
1-108 Copyright © Oracle Corporation,  2001. All rights reserved.
多表查询
1-109 Copyright © Oracle Corporation,  2001. All rights reserved.
目标
通过本章学习，您将可以:
• 使用等值和不等值连接在SELECT 语句中查询多个表中
的数据。
• 使用外连接查询不满足连接条件的数据。
• 使用自连接。
1-110 Copyright © Oracle Corporation,  2001. All rights reserved.
从多个表中获取数据
EMPLOYEES DEPARTMENTS
…
…
1-111 Copyright © Oracle Corporation,  2001. All rights reserved.
笛卡尔集
• 笛卡尔集会在下面条件下产生:
– 省略连接条件
– 连接条件无效
– 所有表中的所有行互相连接
• 为了避免笛卡尔集，可以在WHERE加入有效的连接条
件。
1-112 Copyright © Oracle Corporation,  2001. All rights reserved.
笛卡尔集
笛卡尔集:
20x8=160行
EMPLOYEES (20 行) DEPARTMENTS (8 行)
…
…
1-113 Copyright © Oracle Corporation,  2001. All rights reserved.
• Equijoin
• Non - equijoin
• Outer join
• Self join
连接的类型
• Cross joins
• Natural joins
• Using clause
• Full or two sided outer
joins
• Arbitrary join conditions
for outer joins
适用于SQL:
1999的连接:
Oracle 提供的连接(8 i 或更早):
1-114 Copyright © Oracle Corporation,  2001. All rights reserved.
Oracle 连接
使用连接在多个表中查询数据。
• 在WHERE字句中写入连接条件。
• 在表中有相同列时，在列名之前加上表名前缀。
SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column1 = table2.column2;
1-115 Copyright © Oracle Corporation,  2001. All rights reserved.
等值连接
EMPLOYEES DEPARTMENTS
外键 主键
… …
1-116 Copyright © Oracle Corporation,  2001. All rights reserved.
SELECT employees.employee_id, employees.last_name,
employees.department_id, departments.department_id,
departments.location_id
FROM   employees, departments
WHERE  employees.department_id = departments.department_id;
等值连接
…
1-117 Copyright © Oracle Corporation,  2001. All rights reserved.
多个连接条件与AND 操作符
EMPLOYEES DEPARTMENTS
… …
1-118 Copyright © Oracle Corporation,  2001. All rights reserved.
区分重复的列名
• 使用表名前缀在多个表中区分相同的列。
• 使用表名可以提高效率。
• 在不同表中具有相同列名的列可以用别名加以区分。
1-119 Copyright © Oracle Corporation,  2001. All rights reserved.
SELECT e.employee_id, e.last_name, e.department_id,
d.department_id, d.location_id
FROM   employees e , departments d
WHERE  e.department_id = d.department_id;
表的别名
• 使用别名可以简化查询。
• 使用表名前缀可以提高执行效率。
1-120 Copyright © Oracle Corporation,  2001. All rights reserved.
连接多个表
EMPLOYEES LOCATIONS DEPARTMENTS
• 连接n个表, 至少需要n-1 个连接条件。例如：连接三
个表，至少需要两个连接条件。
…
1-121 Copyright © Oracle Corporation,  2001. All rights reserved.
非等值连接
EMPLOYEES JOB_GRADES
EMPLOYEES表中的列工资
应在JOB_GRADES 表中的最高
工资与最低工资之间 …
1-122 Copyright © Oracle Corporation,  2001. All rights reserved.
非等值连接
SELECT e.last_name, e.salary, j.grade_level
FROM   employees e, job_grades j
WHERE  e.salary
BETWEEN j.lowest_sal AND j.highest_sal;
…
1-123 Copyright © Oracle Corporation,  2001. All rights reserved.
外连接
EMPLOYEES DEPARTMENTS
190 号部门没有员工
…
1-124 Copyright © Oracle Corporation,  2001. All rights reserved.
外连接语法
• 使用外连接可以查询不满足连接条件的数据。
• 外连接的符号是(+)。
SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column (+) = table2.column;
SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column = table2.column (+) ;
1-125 Copyright © Oracle Corporation,  2001. All rights reserved.
SELECT e.last_name, e.department_id, d.department_name
FROM   employees e, departments d
WHERE  e.department_id(+) = d.department_id ;
外连接
…
1-126 Copyright © Oracle Corporation,  2001. All rights reserved.
自连接
EMPLOYEES (WORKER) EMPLOYEES (MANAGER)
WORKER 表中的MANAGER_ID 和MANAGER表中的
MANAGER_ID相等
… …
1-127 Copyright © Oracle Corporation,  2001. All rights reserved.
自连接
SELECT worker.last_name || ' works for '
|| manager.last_name
FROM   employees worker, employees manager
WHERE  worker.manager_id = manager.employee_id ;
…
1-128 Copyright © Oracle Corporation,  2001. All rights reserved.
使用SQL: 1999 语法连接
使用连接从多个表中查询数据：
SELECT table1.column, table2.column
FROM table1
[CROSS JOIN table2] |
[NATURAL JOIN table2] |
[JOIN table2 USING (column_name)] |
[JOIN table2
ON( table1.column_name = table2.column_name )] |
[LEFT|RIGHT|FULL OUTER JOIN  table2
ON (table1.column_name = table2.column_name )];
1-129 Copyright © Oracle Corporation,  2001. All rights reserved.
叉集
• 使用CROSS JOIN子句使连接的表产生叉集。
• 叉集和笛卡尔集是相同的。
SELECT last_name, department_name
FROM   employees
CROSS JOIN departments ;
…
1-130 Copyright © Oracle Corporation,  2001. All rights reserved.
自然连接
• NATURAL JOIN子句，会以两个表中具有相同名字的
列为条件创建等值连接。
• 在表中查询满足等值条件的数据。
• 如果只是列名相同而数据类型不同，则会产生错误。
1-131 Copyright © Oracle Corporation,  2001. All rights reserved.
SELECT department_id, department_name,
location_id, city
FROM   departments
NATURAL JOIN locations ;
自然连接
1-132 Copyright © Oracle Corporation,  2001. All rights reserved.
使用USING子句创建连接
• 在NATURAL JOIN子句创建等值连接时，可以使用
USING子句指定等值连接中需要用到的列。
• 使用USING可以在有多个列满足条件时进行选择。
• 不要给选中的列中加上表名前缀或别名。
• NATURAL JOIN和USING子句经常同时使用。
1-133 Copyright © Oracle Corporation,  2001. All rights reserved.
SELECT e.employee_id, e.last_name, d.location_id
FROM   employees e JOIN departments d
USING (department_id) ;
USING子句
…
1-134 Copyright © Oracle Corporation,  2001. All rights reserved.
使用ON子句创建连接
• 自然连接中是以具有相同名字的列为连接条件的。
• 可以使用ON子句指定额外的连接条件。
• 这个连接条件是与其它条件分开的。
• ON子句使语句具有更高的易读性。
1-135 Copyright © Oracle Corporation,  2001. All rights reserved.
SELECT e.employee_id, e.last_name, e.department_id,
d.department_id, d.location_id
FROM   employees e JOIN departments d
ON     (e.department_id = d.department_id);
ON子句
…
1-136 Copyright © Oracle Corporation,  2001. All rights reserved.
使用ON子句创建多表连接
SELECT employee_id, city, department_name
FROM   employees e
JOIN   departments d
ON     d.department_id = e.department_id
JOIN   locations l
ON     d.location_id = l.location_id;
…
1-137 Copyright © Oracle Corporation,  2001. All rights reserved.
内连接与外连接
• 在SQL: 1999 中，内连接只返回满足连接条件的数据。
• 两个表在连接过程中除了返回满足连接条件的行以外还
返回左（或右）表中不满足条件的行，这种连接称为
左（或右）外联接。
• 两个表在连接过程中除了返回满足连接条件的行以外还
返回两个表中不满足条件的行，这种连接称为满外联
接。
1-138 Copyright © Oracle Corporation,  2001. All rights reserved.
SELECT e.last_name, e.department_id, d.department_name
FROM   employees e
LEFT OUTER JOIN departments d
ON   (e.department_id = d.department_id) ;
左外联接
…
1-139 Copyright © Oracle Corporation,  2001. All rights reserved.
SELECT e.last_name, e.department_id, d.department_name
FROM   employees e
RIGHT OUTER JOIN departments d
ON    (e.department_id = d.department_id) ;
右外联接
…
1-140 Copyright © Oracle Corporation,  2001. All rights reserved.
SELECT e.last_name, e.department_id, d.department_name
FROM   employees e
FULL OUTER JOIN departments d
ON   (e.department_id = d.department_id) ;
满外联接
…
1-141 Copyright © Oracle Corporation,  2001. All rights reserved.
SELECT e.employee_id, e.last_name, e.department_id,
d.department_id, d.location_id
FROM   employees e JOIN departments d
ON     (e.department_id = d.department_id)
AND    e.manager_id = 149 ;
增加连接条件
1-142 Copyright © Oracle Corporation,  2001. All rights reserved.
分组函数
1-143 Copyright © Oracle Corporation,  2001. All rights reserved.
目标
通过本章学习，您将可以:
• 了解组函数。
• 描述组函数的用途。
• 使用GROUP BY 字句数据分组。
• 使用HAVING 字句过滤分组结果集。
1-144 Copyright © Oracle Corporation,  2001. All rights reserved.
什么是分组函数
分组函数作用于一组数据，并对一组数据返回一个值。
EMPLOYEES
表EMPLOYEES
中的工资最大值
…
1-145 Copyright © Oracle Corporation,  2001. All rights reserved.
组函数类型
• AVG
• COUNT
• MAX
• MIN
• STDDEV
• SUM
1-146 Copyright © Oracle Corporation,  2001. All rights reserved.
SELECT [column,]  group_function(column), ...
FROM table
[WHERE condition]
[GROUP BY column]
[ORDER BY column];
组函数语法
1-147 Copyright © Oracle Corporation,  2001. All rights reserved.
SELECT AVG(salary), MAX(salary),
MIN(salary), SUM(salary)
FROM   employees
WHERE  job_id LIKE '%REP%';
AVG （平均值）和SUM （合计）函数
可以对数值型数据使用AVG 和SUM 函数。
1-148 Copyright © Oracle Corporation,  2001. All rights reserved.
MIN （最小值）和MAX （最大值）函数
可以对任意数据类型的数据使用MIN 和MAX 函数。
SELECT MIN(hire_date), MAX(hire_date)
FROM employees;
1-149 Copyright © Oracle Corporation,  2001. All rights reserved.
SELECT COUNT(*)
FROM employees
WHERE  department_id = 50;
COUNT（计数）函数
COUNT(*) 返回表中记录总数。
1-150 Copyright © Oracle Corporation,  2001. All rights reserved.
COUNT（计数）函数
• COUNT( expr) 返回expr不为空的记录总数。
SELECT COUNT(commission_pct)
FROM   employees
WHERE  department_id = 80;
1-151 Copyright © Oracle Corporation,  2001. All rights reserved.
SELECT COUNT(DISTINCT department_id)
FROM   employees;
DISTINCT 关键字
• COUNT(DISTINCT expr)返回expr非空且不重复的
记录总数
1-152 Copyright © Oracle Corporation,  2001. All rights reserved.
SELECT AVG(commission_pct)
FROM   employees;
组函数与空值
组函数忽略空值。
1-153 Copyright © Oracle Corporation,  2001. All rights reserved.
SELECT AVG(NVL(commission_pct, 0))
FROM   employees;
在组函数中使用NVL 函数
NVL 函数使分组函数无法忽略空值。
1-154 Copyright © Oracle Corporation,  2001. All rights reserved.
分组数据
EMPLOYEES
求出
EMPLOYEES
表中各
部门的
平均工资
4400
…
9500
3500
6400
10033
1-155 Copyright © Oracle Corporation,  2001. All rights reserved.
SELECT column, group_function(column)
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[ORDER BY column];
分组数据:
GROUP BY 子句语法
可以使用GROUPBY子句将表中的数据分成若干组
1-156 Copyright © Oracle Corporation,  2001. All rights reserved.
SELECT   department_id, AVG(salary)
FROM     employees
GROUP BY department_id ;
GROUP BY 子句
在SELECT 列表中所有未包含在组函数中的列都应该包含
在GROUP BY 子句中。
1-157 Copyright © Oracle Corporation,  2001. All rights reserved.
GROUP BY 子句
包含在GROUP BY 子句中的列不必包含在SELECT 列表中。
SELECT   AVG(salary)
FROM     employees
GROUP BY department_id ;
1-158 Copyright © Oracle Corporation,  2001. All rights reserved.
使用多个列分组
EMPLOYEES
使用多个列
进行分组
…
1-159 Copyright © Oracle Corporation,  2001. All rights reserved.
SELECT   department_id dept_id, job_id, SUM(salary)
FROM     employees
GROUP BY department_id, job_id ;
在GROUP BY 子句中包含多个列
1-160 Copyright © Oracle Corporation,  2001. All rights reserved.
非法使用组函数
所用包含于SELECT 列表中，而未包含于组函数中的列都
必须包含于GROUPBY子句中。
SELECT department_id, COUNT(last_name)
FROM   employees;
SELECT department_id, COUNT(last_name)
*
ERROR at line 1:
ORA -00937: not a single-group group function
1-161 Copyright © Oracle Corporation,  2001. All rights reserved.
非法使用组函数
• 不能在WHERE子句中使用组函数。
• 可以在HAVING 子句中使用组函数。
SELECT   department_id, AVG(salary)
FROM     employees
WHERE    AVG(salary) > 8000
GROUP BY department_id;
WHERE  AVG(salary) > 8000
*
ERROR at line 3:
ORA -00934: group function is not allowed here
1-162 Copyright © Oracle Corporation,  2001. All rights reserved.
过滤分组
The maximum
salary
per department
when it is
greater than
$10,000
EMPLOYEES
…
1-163 Copyright © Oracle Corporation,  2001. All rights reserved.
SELECT column, group_function
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[HAVING group_condition]
[ORDER BY column];
过滤分组：HAVING 子句
使用HAVING 过滤分组:
1. 行已经被分组。
2. 使用了组函数。
3. 满足HAVING 子句中条件的分组将被显示。
1-164 Copyright © Oracle Corporation,  2001. All rights reserved.
HAVING 子句
SELECT   department_id, MAX(salary)
FROM     employees
GROUP BY department_id
HAVING   MAX(salary)>10000 ;
1-165 Copyright © Oracle Corporation,  2001. All rights reserved.
SELECT   job_id, SUM(salary) PAYROLL
FROM     employees
WHERE    job_id NOT LIKE '%REP%'
GROUP BY job_id
HAVING   SUM(salary) > 13000
ORDER BY SUM(salary);
HAVING 子句
1-166 Copyright © Oracle Corporation,  2001. All rights reserved.
嵌套组函数
显示平均工资的最大值
SELECT   MAX(AVG(salary))
FROM     employees
GROUP BY department_id;
1-167 Copyright © Oracle Corporation,  2001. All rights reserved.
SELECT column, group_function(column)
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[HAVING group_condition]
[ORDER BY column];
总结
通过本章学习，您已经学会:
• 使用组函数。
• 在查询中使用GROUP BY 子句。
• 在查询中使用HAVING 子句。
1-168 Copyright © Oracle Corporation,  2001. All rights reserved.
子查询
1-169 Copyright © Oracle Corporation,  2001. All rights reserved.
目标
通过本章学习，您将可以:
• 描述子查询可以解决的问题
• 定义子查询。
• 列句子查询的类型。
• 书写单行子查询和多行字查询。
1-170 Copyright © Oracle Corporation,  2001. All rights reserved.
使用子查询解决问题
谁的工资比Abel 高?
谁的工资比Abel 高?
Main Query:
?
Abel的工资是多少?
?
Subquery
1-171 Copyright © Oracle Corporation,  2001. All rights reserved.
子查询语法
• 子查询(内查询)  在主查询之前一次执行完成。
• 子查询的结果被主查询使用(外查询)。
SELECT select_list
FROM table
WHERE expr operator
(SELECT select_list
FROM table);
1-172 Copyright © Oracle Corporation,  2001. All rights reserved.
SELECT last_name
FROM   employees
WHERE  salary >
(SELECT salary
FROM   employees
WHERE  last_name = 'Abel');
子查询
11000
1-173 Copyright © Oracle Corporation,  2001. All rights reserved.
注意事项
• 子查询要包含在括号内。
• 将子查询放在比较条件的右侧。
• 除非进行Top -N  分析，否则不要在子查询中使用ORDER
BY子句。
• 单行操作符对应单行子查询，多行操作符对应多行子查
询。
1-174 Copyright © Oracle Corporation,  2001. All rights reserved.
子查询类型
主查询
子查询
返回
ST_CLERK
• 多行子查询
ST_CLERK
SA_MAN
主查询
子查询
返回
• 单行子查询
1-175 Copyright © Oracle Corporation,  2001. All rights reserved.
单行子查询
• 只返回一行。
• 使用单行比较操作符。
操作符
=
>
>=
<
<=
<>
含义
Equal to
Greater than
Greater than or equal to
Less than
Less than or equal to
Not equal to
1-176 Copyright © Oracle Corporation,  2001. All rights reserved.
SELECT last_name, job_id, salary
FROM   employees
WHERE  job_id = 
(SELECT job_id
FROM   employees
WHERE  employee_id = 141)
AND    salary >
(SELECT salary
FROM   employees
WHERE  employee_id = 143);
执行单行子查询
ST_CLERK
2600
1-177 Copyright © Oracle Corporation,  2001. All rights reserved.
SELECT last_name, job_id, salary
FROM   employees
WHERE  salary =
(SELECT MIN(salary)
FROM   employees);
在子查询中使用组函数
2500
1-178 Copyright © Oracle Corporation,  2001. All rights reserved.
子查询中的HAVING 子句
• 首先执行子查询。
• 向主查询中的HAVING 子句返回结果。
SELECT   department_id, MIN(salary)
FROM     employees
GROUP BY department_id
HAVING   MIN(salary) >
(SELECT MIN(salary)
FROM   employees
WHERE  department_id = 50);
2500
1-179 Copyright © Oracle Corporation,  2001. All rights reserved.
SELECT employee_id, last_name
FROM   employees
WHERE  salary =
(SELECT   MIN(salary)
FROM     employees
GROUP BY department_id);
非法使用子查询
ERROR at line 4:
ORA -01427: single -row subquery returns more than
one row
1-180 Copyright © Oracle Corporation,  2001. All rights reserved.
子查询中的空值问题
no rows selected
SELECT last_name, job_id
FROM   employees
WHERE  job_id =
(SELECT job_id
FROM   employees
WHERE  last_name = 'Haas');
1-181 Copyright © Oracle Corporation,  2001. All rights reserved.
多行子查询
• 返回多行。
• 使用多行比较操作符。
操作符
IN
ANY
ALL
含义
等于列表中的任何一个
和子查询返回的任意一个值比较
和子查询返回的所有值比较
1-182 Copyright © Oracle Corporation,  2001. All rights reserved.
在多行子查询中使用ANY 操作符
9000, 6000, 4200
SELECT employee_id, last_name, job_id, salary
FROM   employees
WHERE  salary < ANY
(SELECT salary
FROM   employees
WHERE  job_id = 'IT_PROG')
AND    job_id <> 'IT_PROG';
…
1-183 Copyright © Oracle Corporation,  2001. All rights reserved.
SELECT employee_id, last_name, job_id, salary
FROM   employees
WHERE  salary < ALL
(SELECT salary
FROM   employees
WHERE  job_id = 'IT_PROG')
AND    job_id <> 'IT_PROG';
在多行子查询中使用ALL 操作符
9000, 6000, 4200
1-184 Copyright © Oracle Corporation,  2001. All rights reserved.
子查询中的空值问题
SELECT emp.last_name
FROM   employees emp
WHERE  emp.employee_id NOT IN
(SELECT mgr.manager_id
FROM   employees mgr);
no rows selected
1-185 Copyright © Oracle Corporation,  2001. All rights reserved.
总结
通过本章学习，您已经学会:
• 在什么时候遇到什么问题应该使用子查询。
• 在查询是基于未知的值时应使用子查询。
SELECT select_list
FROM table
WHERE expr operator
(SELECT  select_list
FROM table);
1-186 Copyright © Oracle Corporation,  2001. All rights reserved.
i SQL*Plus
1-187 Copyright © Oracle Corporation,  2001. All rights reserved.
目标
通过本章学习，您将可以:
• 在查询中使用变量。
• 熟悉i SQL*Plus  环境。
• 使输出更便于理解。
• 创建和执行脚本。
1-188 Copyright © Oracle Corporation,  2001. All rights reserved.
变量
I want to query
different values.
... salary = ? …
… department_id = ? …
... last_name = ? ...
User
1-189 Copyright © Oracle Corporation,  2001. All rights reserved.
变量
使用i SQL*Plus  变量:
• 临时存储值
– 单个( & )
– 两个( &&)
– 定义命令
• 在SQL语句中改变变量的值。
• 动态修改开头和结尾。
1-190 Copyright © Oracle Corporation,  2001. All rights reserved.
& 变量
在变量名前加前缀(& )  使用户输入值。
SELECT employee_id, last_name, salary, department_id
FROM employees
WHERE employee_id = &employee_num ;
1-191 Copyright © Oracle Corporation,  2001. All rights reserved.
& 变量
1
2
101
1-192 Copyright © Oracle Corporation,  2001. All rights reserved.
SELECT last_name, department_id, salary*12
FROM   employees
WHERE  job_id = '&job_title' ;
字符和日期型变量
在子符和日期两端加单引号。
1-193 Copyright © Oracle Corporation,  2001. All rights reserved.
指定列名、表达式和文本
使用变量可以提供下面的内容:
• WHERE条件
• ORDER BY 子句
• 列表达式
• 表名
• 整个SELECT 语句
1-194 Copyright © Oracle Corporation,  2001. All rights reserved.
SELECT employee_id, last_name, job_id,
&column_name
FROM employees
WHERE &condition
ORDER BY &order_column ;
指定列名、表达式和文本
1-195 Copyright © Oracle Corporation,  2001. All rights reserved.
定义变量
• 可以使用DEFINE 命令提前定义i SQL*Plus  变量。
DEFINE variable = value创建一个字符型用户变
量
• 使用DEFINE 定义的变量名字中包含空格时，变量名应
包含在单引号中。
• 定义的边令在会话级有效。
1-196 Copyright © Oracle Corporation,  2001. All rights reserved.
DEFINE 和UNDEFINE 命令
• 定义命令在下列条件下失效:
– UNDEFINE 命令
– 退出i SQL*Plus
• 可以重复使用DEFINE 命令改变变量。
DEFINE job_title = IT_PROG
DEFINE job_title
DEFINE JOB_TITLE       = "IT_PROG" (CHAR)
UNDEFINE job_title
DEFINE job_title
SP2 -0135: symbol job_title is UNDEFINED
1-197 Copyright © Oracle Corporation,  2001. All rights reserved.
SELECT employee_id, last_name, salary, department_id
FROM   employees
WHERE  employee_id = &employee_num ;
DEFINE 命令与& 变量
• 使用DEFINE 创建变量。
• 使用变量前缀(& )  在SQL 语句中引用变量。
DEFINE employee_num = 200
1-198 Copyright © Oracle Corporation,  2001. All rights reserved.
SELECT   employee_id, last_name, job_id, &&column_name
FROM     employees
ORDER BY &column_name;
使用(&&)  避免为同一变量重复赋值。
…
&&变量
1-199 Copyright © Oracle Corporation,  2001. All rights reserved.
old   3: WHERE  employee_id = &employee_num
new   3: WHERE  employee_id = 200
VERIFY 命令
使用VERIFY 在i SQL*Plus  中显示变量被替代前和变量被替代后
的SQL语句。
SET VERIFY ON
SELECT employee_id, last_name, salary, department_id
FROM   employees
WHERE  employee_id = &employee_num;
1-200 Copyright © Oracle Corporation,  2001. All rights reserved.
i SQL*Plus  环境
• 使用SET 命令控制当前会话。
• 使用SHOW 命令显示当前的设置。
SET ECHO ON
SHOW ECHO
echo ON
SET system_variable value
1-201 Copyright © Oracle Corporation,  2001. All rights reserved.
SET 命令
• ARRAYSIZE  {20 | n }
• FEEDBACK { 6 | n |OFF | ON}
• HEADING  {OFF |  ON}
• LONG  {80 | n }| ON |  text}
SET HEADING OFF
SHOW HEADING
HEADING OFF
1-202 Copyright © Oracle Corporation,  2001. All rights reserved.
i SQL*Plus  格式命令
• COLUMN [ column option ]
• TTITLE [ text | OFF | ON]
• BTITLE [ text | OFF | ON]
• BREAK  [ON  report_element]
1-203 Copyright © Oracle Corporation,  2001. All rights reserved.
COLUMN 命令
控制列的输出：
• CLE[AR]:  清除列格式
• HEA[DING] text:  设置列头
• FOR[MAT] format :  改变列的输出格式
• NOPRINT | PRINT
• NULL
COL[UMN] [{column|alias} [option]]
1-204 Copyright © Oracle Corporation,  2001. All rights reserved.
COLUMN 命令
• 创建列头：
COLUMN last_name HEADING 'Employee|Name'
COLUMN salary JUSTIFY LEFT FORMAT $99,990.00
COLUMN manager FORMAT 999999999 NULL 'No manager'
COLUMN last_name
COLUMN last_name CLEAR
• 显示LAST_NAME 列的当前格式。
• 清除LAST_NAME 列的当前格式设置
1-205 Copyright © Oracle Corporation,  2001. All rights reserved.
COLUMN 格式
Result
1234
001234
$1234
L1234
1234.00
1,234
Example
999999
099999
$9999
L9999
9999.99
9,999
Element
9
0
$
L
.
,
Description
Single zero- suppression
digit
Enforces leading zero
Floating dollar sign
Local currency
Position of decimal point
Thousand separator
1-206 Copyright © Oracle Corporation,  2001. All rights reserved.
BREAK命令
使用BREAK命令去重。
BREAK ON job_id
1-207 Copyright © Oracle Corporation,  2001. All rights reserved.
TTITLE 和BTITLE 命令
• 显示报告头和报告尾
• 设置报告头。
• 摄制报告尾。
TTI[TLE] [ text|OFF|ON]
TTITLE 'Salary|Report'
BTITLE 'Confidential'
1-208 Copyright © Oracle Corporation,  2001. All rights reserved.
TTITLE 和BTITLE 命令
• 显示报告头和报告尾。
• 设置报告头。
• 设置报告尾。
TTI[TLE] [ text|OFF|ON]
TTITLE 'Salary|Report'
BTITLE 'Confidential'
1-209 Copyright © Oracle Corporation,  2001. All rights reserved.
使用脚本创建报告
1. 书写并测试SQL SELECT 语句。
2. 保存SELECT 语句到脚本文件。
3. 在编辑器中执行脚本。
4. 在SELECT 语句前添加格式命令。
5. 在SELECT 语句后添加终止符。
1-210 Copyright © Oracle Corporation,  2001. All rights reserved.
使用脚本创建报告
6. 在SELECT 后清除格式设置。
7. 保存脚本。
8. 在i SQL*Plus  的文本框中加载脚本,  点击执行按钮
运行脚本。
1-211 Copyright © Oracle Corporation,  2001. All rights reserved.
报告
…
1-212 Copyright © Oracle Corporation,  2001. All rights reserved.
报告
…
1-213 Copyright © Oracle Corporation,  2001. All rights reserved.
总结
通过本章学习，您已经学会:
• 使用i SQL*Plus  变量临时存储值。
• 使用SET 命令控制当前i SQL*Plus  环境。
• 使用COLUMN 命令控制列的输出。
• 使用BREAK命令去重并将结果积分组。
• 使用TTITLE 和BTITLE 显示报告头和报告尾。
1-214 Copyright © Oracle Corporation,  2001. All rights reserved.
Practice 7 Overview
This practice covers the following topics:
• Creating a query to display values using
substitution variables
• Starting a command file containing variables
1-215 Copyright © Oracle Corporation,  2001. All rights reserved.
处理数据
1-216 Copyright © Oracle Corporation,  2001. All rights reserved.
目标
通过本章学习，您将可以:
• 使用DML 语句
• 向表中插入数据
• 更新表中数据
• 从表中删除数据
• 将表中数据和并
• 控制事务
1-217 Copyright © Oracle Corporation,  2001. All rights reserved.
数据控制语言
• DML 可以在下列条件下执行:
– 向表中插入数据
– 修改现存数据
– 删除现存数据
• 事务是由完成若干项工作的DML语句组成的。
1-218 Copyright © Oracle Corporation,  2001. All rights reserved.
插入数据
DEPARTMENTS
新行
向DEPARMENTS
表中插入
新的记录
1-219 Copyright © Oracle Corporation,  2001. All rights reserved.
INSERT 语句语法
• 使用INSERT 语句向表中插入数据。
• 使用这种语法一次只能向表中插入一条数据。
INSERT INTO table [(column [, column...])]
VALUES (value [, value...]);
1-220 Copyright © Oracle Corporation,  2001. All rights reserved.
插入数据
• 为每一列添加一个新值。
• 按列的默认顺序列出各个列的值。
• 在INSERT 子句中随意列出列名和他们的值。
• 字符和日期型数据应包含在单引号中。
INSERT INTO departments(department_id, department_name,
manager_id, location_id)
VALUES      (70, 'Public Relations', 100, 1700);
1 row created.
1-221 Copyright © Oracle Corporation,  2001. All rights reserved.
INSERT INTO departments
VALUES (100, 'Finance', NULL, NULL);
1 row created.
INSERT INTO departments (department_id,
department_name    )
VALUES (30, 'Purchasing');
1 row created.
向表中插入空值
• 隐式方式:  在列名表中省略该列的值。
• 显示方式:在VALUES 子句中指定空值。
1-222 Copyright © Oracle Corporation,  2001. All rights reserved.
INSERT INTO employees (employee_id,
first_name, last_name,
email, phone_number,
hire_date, job_id, salary,
commission_pct, manager_id,
department_id)
VALUES (113,
'Louis', 'Popp',
'LPOPP', '515.124.4567',
SYSDATE, 'AC_ACCOUNT', 6900,
NULL, 205, 100);
1 row created.
插入指定的值
SYSDATE记录当前系统的日期和时间。
1-223 Copyright © Oracle Corporation,  2001. All rights reserved.
INSERT INTO employees
VALUES      (114,
'Den', 'Raphealy',
'DRAPHEAL', '515.127.4561',
TO_DATE('FEB 3, 1999', 'MON DD, YYYY'),
'AC_ACCOUNT', 11000, NULL, 100, 30);
1 row created.
插入指定的值
• 加入新员工
• 检查插入的数据
1-224 Copyright © Oracle Corporation,  2001. All rights reserved.
INSERT INTO departments
(department_id, department_name, location_id)
VALUES      (&department_id, '&department_name',&location);
创建脚本
• 在SQL 语句中使用&  变量指定列值。
• & 变量放在VALUES子句中。
1 row created.
1-225 Copyright © Oracle Corporation,  2001. All rights reserved.
• 在INSERT 语句中加入子查询。
• 不必书写VALUES 子句。
• 子查询中的值列表应于INSERT 子句中的列名对应。
INSERT INTO sales_reps(id, name, salary, commission_pct)
SELECT employee_id, last_name, salary, commission_pct
FROM   employees
WHERE  job_id LIKE '%REP%';
4 rows created.
从其它表中拷贝数据
1-226 Copyright © Oracle Corporation,  2001. All rights reserved.
更新数据
EMPLOYEES
更新EMPLOYEES 表
1-227 Copyright © Oracle Corporation,  2001. All rights reserved.
UPDATE 语句语法
• 使用UPDATE 语句更新数据。
• 可以一次更新多条数据。
UPDATE table
SET column = value[,  column = value, ... ]
[WHERE  condition];
1-228 Copyright © Oracle Corporation,  2001. All rights reserved.
UPDATE employees
SET    department_id = 70
WHERE  employee_id = 113;
1 row updated.
• 使用WHERE子句指定需要更新的数据。
• 如果省略WHERE子句，则表中的所有数据都将被更新。
更新数据
UPDATE  copy_emp
SET     department_id = 110;
22 rows updated.
1-229 Copyright © Oracle Corporation,  2001. All rights reserved.
UPDATE   employees
SET      job_id  = (SELECT  job_id
FROM    employees
WHERE   employee_id = 205),
salary  = (SELECT  salary
FROM    employees
WHERE   employee_id = 205)
WHERE    employee_id    =  114;
1 row updated.
在UPDATE语句中使用子查询
更新114 号员工的工作和工资使其与205 号员工相同。
1-230 Copyright © Oracle Corporation,  2001. All rights reserved.
UPDATE  copy_emp
SET     department_id  =  (SELECT department_id
FROM employees
WHERE employee_id = 100)
WHERE   job_id         =  (SELECT job_id
FROM employees
WHERE employee_id = 200);
1 row updated.
在UPDATE语句中使用子查询
在UPDATE 中使用子查询，使更新基于另一个表中的数据。
1-231 Copyright © Oracle Corporation,  2001. All rights reserved.
UPDATE employees
*
ERROR at line 1:
ORA -02291: integrity constraint (HR.EMP_DEPT_FK)
violated - parent key not found
UPDATE employees
SET    department_id = 55
WHERE  department_id = 110;
更新中的数据完整性错误
1-232 Copyright © Oracle Corporation,  2001. All rights reserved.
从表DEPARTMENTS 中删除一条记录。
删除数据
DEPARTMENTS
1-233 Copyright © Oracle Corporation,  2001. All rights reserved.
DELETE 语句
使用DELETE 语句从表中删除数据。
DELETE [FROM] table
[WHERE condition];
1-234 Copyright © Oracle Corporation,  2001. All rights reserved.
• 使用WHERE子句指定删除的记录。
• 如果省略WHERE子句，则表中的全部数据将被删除。
删除数据
DELETE FROM departments
WHERE  department_name = 'Finance';
1 row deleted.
DELETE FROM  copy_emp;
22 rows deleted.
1-235 Copyright © Oracle Corporation,  2001. All rights reserved.
DELETE FROM employees
WHERE  department_id =
(SELECT department_id
FROM   departments
WHERE  department_name LIKE '%Public%');
1 row deleted.
在DELETE 中使用子查询
在DELETE 中使用子查询，使删除基于另一个表中的数据。
1-236 Copyright © Oracle Corporation,  2001. All rights reserved.
删除中的数据完整性错误
DELETE FROM departments
WHERE       department_id = 60;
DELETE FROM departments
*
ERROR at line 1:
ORA -02292: integrity constraint (HR.EMP_DEPT_FK)
violated - child record found
1-237 Copyright © Oracle Corporation,  2001. All rights reserved.
在INSERT 语句中使用子查询
INSERT INTO
(SELECT employee_id, last_name,
email, hire_date, job_id, salary,
department_id
FROM   employees
WHERE  department_id = 50)
VALUES (99999, 'Taylor', 'DTAYLOR',
TO_DATE('07-JUN -99', 'DD -MON -RR'),
'ST_CLERK', 5000, 50);
1 row created.
1-238 Copyright © Oracle Corporation,  2001. All rights reserved.
在INSERT 语句中使用子查询
• Verify the results SELECT employee_id, last_name, email, hire_date,
job_id, salary, department_id
FROM   employees
WHERE  department_id = 50;
1-239 Copyright © Oracle Corporation,  2001. All rights reserved.
在DML语句中使用WITH CHECK OPTION关
键字
• 使用子查询表示DML 语句中使用的表
• WITH CHECK OPTION关键字避免修改子查询范围外
的数据
INSERT INTO  (SELECT employee_id, last_name, email,
hire_date, job_id, salary
FROM   employees
WHERE  department_id = 50  WITH CHECK OPTION)
VALUES (99998, 'Smith', 'JSMITH',
TO_DATE('07 -JUN -99', 'DD -MON - RR'),
'ST_CLERK', 5000);
INSERT INTO
*
ERROR at line 1:
ORA -01402: view WITH CHECK OPTION where- clause violation
1-240 Copyright © Oracle Corporation,  2001. All rights reserved.
显式默认值
• 使用DEFAULT关键字表示默认值
• 可以使用显示默认值控制默认值的使用
• 显示默认值可以在INSERT 和UPDATE 语句中使用
1-241 Copyright © Oracle Corporation,  2001. All rights reserved.
显示使用默认值
INSERT INTO departments
(department_id, department_name, manager_id)
VALUES (300, 'Engineering',  DEFAULT);
UPDATE departments
SET manager_id = DEFAULT WHERE department_id = 10;
• 在插入操作中使用默认值:
• 在更新操作中使用默认值:
1-242 Copyright © Oracle Corporation,  2001. All rights reserved.
合并语句
• 按照指定的条件执行插入或更新操作
• 如果满足条件的行存在，执行更新操作；否则执行插入
操作:
– 避免多次重复执行插入和删除操作
– 提高效率而且使用方便
– 在数据仓库应用中经常使用
1-243 Copyright © Oracle Corporation,  2001. All rights reserved.
合并语句的语法
可以使用merge 语句，根据指定的条件进行插入或更新操作
MERGE INTO table_nametable_alias
USING (table|view|sub_query) alias
ON (join condition)
WHEN MATCHED THEN
UPDATE SET
col1 = col_val1,
col2 = col2_val
WHEN NOT MATCHED THEN
INSERT ( column_list)
VALUES ( column_values );
1-244 Copyright © Oracle Corporation,  2001. All rights reserved.
MERGE INTO copy_emp  c
USING employees e
ON (c.employee_id = e.employee_id)
WHEN MATCHED THEN
UPDATE SET
c.first_name     = e.first_name,
c.last_name      = e.last_name,
...
c.department_id  = e.department_id
WHEN NOT MATCHED THEN
INSERT VALUES(e.employee_id, e.first_name, e.last_name,
e.email, e.phone_number, e.hire_date, e.job_id,
e.salary, e.commission_pct, e.manager_id,
e.department_id);
合并语句举例
在对表COPY_EMP 使用merge语句，根据指定的条件从表
EMPLOYEES中插入或更新数据。
1-245 Copyright © Oracle Corporation,  2001. All rights reserved.
合并语句举例
MERGE INTO copy_emp c
USING employees e
ON (c.employee_id = e.employee_id)
WHEN MATCHED THEN
UPDATE SET
...
WHEN NOT MATCHED THEN
INSERT VALUES...;
SELECT *
FROM COPY_EMP;
no rows selected
SELECT *
FROM COPY_EMP;
20 rows selected.
1-246 Copyright © Oracle Corporation,  2001. All rights reserved.
数据库事务
数据库事务由以下的部分组成:
• 一个或多个DML 语句
• 一个DDL 语句
• 一个DCL 语句
1-247 Copyright © Oracle Corporation,  2001. All rights reserved.
数据库事务
• 以第一个DML 语句的执行作为开始
• 以下面的其中之一作为结束:
– COMMIT 或ROLLBACK 语句
– DDL 或DCL 语句（自动提交）
– 用户会话正常结束
– 系统异常终了
1-248 Copyright © Oracle Corporation,  2001. All rights reserved.
COMMIT 和ROLLBACK 语句的优点
使用COMMIT 和ROLLBACK 语句, 我们可以:
• 确保数据完整性。
• 数据改变被提交之前预览。
• 将逻辑上相关的操作分组。
1-249 Copyright © Oracle Corporation,  2001. All rights reserved.
控制事务
保存点B
保存点A
DELETE
INSERT
UPDATE
INSERT
COMMIT
Time
事务
ROLLBACK
to SAVEPOINT B
ROLLBACK
to SAVEPOINT A
ROLLBACK
1-250 Copyright © Oracle Corporation,  2001. All rights reserved.
UPDATE...
SAVEPOINT update_done;
Savepoint created.
INSERT...
ROLLBACK TO update_done;
Rollback complete.
回滚到保留点
• 使用SAVEPOINT 语句在当前事务中创建保存点。
• 使用ROLLBACK TO SAVEPOINT 语句回滚到创建的保
存点。
1-251 Copyright © Oracle Corporation,  2001. All rights reserved.
• 自动提交在以下情况中执行:
– DDL 语句。
– DCL 语句。
– 不使用COMMIT 或ROLLBACK 语句提交或回滚，正常结
束会话。
• 会话异常结束或系统异常会导致自动回滚。
事务进程
1-252 Copyright © Oracle Corporation,  2001. All rights reserved.
提交或回滚前的数据状态
• 改变前的数据状态是可以恢复的
• 执行DML 操作的用户可以通过SELECT 语句查询之前的修正
• 其他用户不能看到当前用户所做的改变，直到当前用户结束事
务。
• DML语句所涉及到的行被锁定，其他用户不能操作。
1-253 Copyright © Oracle Corporation,  2001. All rights reserved.
提交后的数据状态
• 数据的改变已经被保存到数据库中。
• 改变前的数据已经丢失。
• 所有用户可以看到结果。
• 锁被释放，其他用户可以操作涉及到的数据。
• 所有保存点被释放。
1-254 Copyright © Oracle Corporation,  2001. All rights reserved.
COMMIT;
Commit complete.
• 改变数据
• 提交改变
DELETE FROM employees
WHERE  employee_id = 99999;
1 row deleted.
INSERT INTO departments
VALUES (290, 'Corporate Tax', NULL, 1700);
1 row inserted.
提交数据
1-255 Copyright © Oracle Corporation,  2001. All rights reserved.
数据回滚后的状态
使用ROLLBACK 语句可使数据变化失效:
• 数据改变被取消。
• 修改前的数据状态可以被恢复。
• 锁被释放。
DELETE FROM copy_emp;
22 rows deleted.
ROLLBACK;
Rollback complete.
1-256 Copyright © Oracle Corporation,  2001. All rights reserved.
语句级回滚
• 单独DML 语句执行失败时，只有该语句被回滚。
• Oracle 服务器自动创建一个隐式的保留点。
• 其他数据改变仍被保留。
• 用户应执行COMMIT 或ROLLBACK 语句结束事务。
1-257 Copyright © Oracle Corporation,  2001. All rights reserved.
读一致性
• 读一致性为数据提供一个一致的视图。
• 一个用户的对数据的改变不会影响其他用户的改变。
• 对于相同的数据读一致性保证:
– 查询不等待修改。
– 修改不等待查询。
1-258 Copyright © Oracle Corporation,  2001. All rights reserved.
SELECT  *
FROM userA.employees;
读一致性
UPDATE employees
SET    salary = 7000
WHERE  last_name = 'Goyal';
数据块
回滚段
改变和未改变
的数据
改变之前的
数据
User A
User B
快照
1-259 Copyright © Oracle Corporation,  2001. All rights reserved.
锁
Oracle 数据库中，锁是:
• 并行事务中避免资源竞争。
• 避免用户动作。
• 自动使用最低级别的限制。
• 在事务结束结束前存在。
• 两种类型:  显示和隐式。
1-260 Copyright © Oracle Corporation,  2001. All rights reserved.
锁
• 两种模式:
– 独占锁:  屏蔽其他用户。
– 共享锁:  允许其他用户操作。
• 高级别的数据并发性:
– DML:  表共享，行独占
– Queries: 不需要加锁
– DDL: 保护对象定义
• 提交或回滚后锁被释放。
1-261 Copyright © Oracle Corporation,  2001. All rights reserved.
总结
功能
插入
修正
删除
合并
提交
保存点
回滚
语句
INSERT
UPDATE
DELETE
MERGE
COMMIT
SAVEPOINT
ROLLBACK
通过本章学习,  您应学会如何使用DML
语句改变数据和事务控制
1-262 Copyright © Oracle Corporation,  2001. All rights reserved.
输出 时间 会话1                             会话2
t1
t2
t3
t4
t5
SELECT salary FROM employees
WHERE  last_name='King';
24000
UPDATE employees
SET    salary=salary+10000
WHERE  last_name='King';
24000
COMMIT;
34000
SELECT salary FROM employees
WHERE  last_name='King';
SELECT salary FROM employees
WHERE  last_name='King';
读一致性举例
1-263 Copyright © Oracle Corporation,  2001. All rights reserved.
创建和管理表
1-264 Copyright © Oracle Corporation,  2001. All rights reserved.
目标
通过本章学习，您将可以:
• 描述主要的数据库对象。
• 创建表。
• 描述各种数据类型。
• 修改表的定义。
• 删除，重命名和清空表。
1-265 Copyright © Oracle Corporation,  2001. All rights reserved.
常见的数据库对象
对象 描述
表 基本的数据存储集合，由行和列组成。
视图 从表中抽出的逻辑上相关的数据集合。
序列 提供有规律的数值。
索引 提高查询的效率
同义词 给对象起别名
1-266 Copyright © Oracle Corporation,  2001. All rights reserved.
命名规则
表名和列名:
• 必须以字母开头
• 必须在1 – 30 个字符之间
• 必须只能包含A– Z, a – z, 0 – 9, _, $, 和#
• 必须不能和用户定义的其他对象重名
• 必须不能是Oracle 的保留字
1-267 Copyright © Oracle Corporation,  2001. All rights reserved.
CREATE TABLE语句
• 必须具备:
– CREATE TABLE权限
– 存储空间
• 必须指定:
– 表名
– 列名,  数据类型,  尺寸
CREATE TABLE [schema.]table
(column datatype [DEFAULT expr][, ...]);
1-268 Copyright © Oracle Corporation,  2001. All rights reserved.
引用其他用户的表
• 其他用户定义的表不在当前用户的方案中
• 应该使用用户名座位前缀，引用其他用户定义的对象
1-269 Copyright © Oracle Corporation,  2001. All rights reserved.
DEFAULT选项
• 插入时为一个列指定默认值
• 字符串,  表达式,  或SQL 函数都是合法的
• 其它列的列名和伪列是非法的
• 默认值必须满足列的数据类型定义
... hire_date DATE DEFAULT SYSDATE, ...
1-270 Copyright © Oracle Corporation,  2001. All rights reserved.
• 语法
• 确认
创建表
CREATE TABLE dept
(deptno  NUMBER(2),
dname  VARCHAR2(14),
loc  VARCHAR2(13));
Table created.
DESCRIBE dept
1-271 Copyright © Oracle Corporation,  2001. All rights reserved.
Oracle 数据库中的表
• 用户定义的表:
– 用户自己创建并维护的一组表
– 包含了用户所需的信息
• 数据字典:
– 由Oracle Server自动创建的一组表
– 包含数据库信息
1-272 Copyright © Oracle Corporation,  2001. All rights reserved.
SELECT table_name
FROM user_tables ;
SELECT *
FROM user_catalog ;
查询数据字典
• 查看用户定义的各种数据库对象
• 查看用户定义的表,  视图,  同义词和序列
SELECT DISTINCT object_type
FROM  user_objects ;
• 查看用户定义的表.
1-273 Copyright © Oracle Corporation,  2001. All rights reserved.
数据类型
数据类型 描述
VARCHAR2(size) 可变长字符数据
CHAR( size)   定长字符数据
NUMBER(p ,s) 可变长数值数据
DATE  日期型数据
LONG  可变长字符数据，最大可达到2G
CLOB 字符数据，最大可达到4G
RAW and LONG RAW 裸二进制数据
BLOB 二进制数据，最大可达到4G
BFILE 存储外部文件的二进制数据，最大可达到4G
ROWID 行地址
1-274 Copyright © Oracle Corporation,  2001. All rights reserved.
创建和管理表
1-275 Copyright © Oracle Corporation,  2001. All rights reserved.
日期数据类型
数据类型 描述
TIMESTAMP 时间撮
INTERVAL YEAR TO MONTH 若干年月
INTERVAL DAY TO SECOND 若干天到秒
Oracle9i 对日期的改进:
• 加入了新的日期型数据类型.
• 有效的存储新数据类型.
• 提高对时区和本地时区的支持.
1-276 Copyright © Oracle Corporation,  2001. All rights reserved.
日期数据类型
• TIMESTAMP 数据类型是对DATE 数据类型的扩展
• 按DATE数据类型存放年,  月,  日,  小时,  分钟,  秒以及微
秒甚至纳秒
• TIMESTAMP 数据类型的一般形式:
TIMESTAMP[(fractional_seconds_precision)]
1-277 Copyright © Oracle Corporation,  2001. All rights reserved.
TIMESTAMP WITH TIME ZONE
• TIMESTAMP WITH TIME ZONE 是一个带有时区的
TIMESTAMP
• 时区部分按照小时和分钟显示本地时区与UTC的时差
TIMESTAMP[(fractional_seconds_precision)]
WITH TIME ZONE
1-278 Copyright © Oracle Corporation,  2001. All rights reserved.
TIMESTAMP WITH LOCALTIME
• TIMESTAMP WITH LOCALTIME ZONE 是一种带有本
地时区的TIMESTAMP
• 数据库按照数据库的本地时区存放数据
• 时区不显示在数据后面，Oracle 自动将数据转换为用
户所在的时区
• TIMESTAMP WITH LOCAL TIME ZONE的一般形式
TIMESTAMP[(fractional_seconds_precision)]
WITH LOCAL TIME ZONE
1-279 Copyright © Oracle Corporation,  2001. All rights reserved.
INTERVAL YEAR TOMONTH数据
• INTERVAL YEAR TOMONTH存放若干年和若干月的一
个时间段。
INTERVAL YEAR [(year_precision)] TO MONTH
INTERVAL '123 -2' YEAR(3) TO MONTH
Indicates an interval of 123 years, 2 months.
INTERVAL '123' YEAR(3)
Indicates an interval of 123 years 0 months.
INTERVAL '300' MONTH(3)
Indicates an interval of 300 months.
INTERVAL '123' YEAR
Returns an error, because the default precision is 2,
and '123' has 3 digits.
1-280 Copyright © Oracle Corporation,  2001. All rights reserved.
INTERVAL DAY TOSECOND 数据
• INTERVAL DAY TO SECOND存放若干天到若干秒的
一个时间段
INTERVAL DAY [(day_precision)]
TO SECOND [(fractional_seconds_precision)]
INTERVAL '4 5:12:10.222' DAY TO SECOND(3)
Indicates 4 days, 5 hours, 12 minutes, 10 seconds,
and 222 thousandths of a second.INTERVAL '123' YEAR(3).
INTERVAL '7' DAY
Indicates 7 days.
INTERVAL '180' DAY(3)
Indicates 180 days.
1-281 Copyright © Oracle Corporation,  2001. All rights reserved.
INTERVAL DAY TOSECOND 数据
• INTERVAL DAY TO SECOND存放若干天到若干秒的一
个时间段
INTERVAL '4 5:12:10.222' DAY TO SECOND(3)
Indicates 4 days, 5 hours, 12 minutes, 10 seconds,
and 222 thousandths of a second.
INTERVAL '4 5:12' DAY TO MINUTE
Indicates 4 days, 5 hours and 12 minutes.
INTERVAL '400 5' DAY(3) TO HOUR
Indicates 400 days 5 hours.
INTERVAL '11:12:10.2222222' HOUR TO SECOND(7)
indicates 11 hours, 12 minutes, and 10.2222222 seconds.
1-282 Copyright © Oracle Corporation,  2001. All rights reserved.
使用子查询创建表
• 时候用AS  subquery 选项，将创建表和插入数据结合
起来
• 指定的列和子查询中的列要一一对应
• 通过列名和默认值定义列
CREATE TABLE  table
[(column, column...)]
AS  subquery;
1-283 Copyright © Oracle Corporation,  2001. All rights reserved.
使用子查询创建表举例
DESCRIBE dept80
CREATE TABLE  dept80
AS
SELECT  employee_id, last_name,
salary*12 ANNSAL,
hire_date
FROM    employees
WHERE   department_id = 80;
Table created.
1-284 Copyright © Oracle Corporation,  2001. All rights reserved.
ALTERTABLE语句
使用ALTERTABLE语句可以:
• 追加新的列
• 修改现有的列
• 为新追加的列定义默认值
• 删除一个列
1-285 Copyright © Oracle Corporation,  2001. All rights reserved.
ALTERTABLE语句
使用ALTER TABLE 语句追加,  修改,  或
删除列的语法.
ALTER TABLE table
ADD (column datatype [DEFAULT expr]
[,  column datatype ]...);
ALTER TABLE table
MODIFY (column datatype [DEFAULT expr]
[,  column datatype ]...);
ALTER TABLE table
DROP (column);
1-286 Copyright © Oracle Corporation,  2001. All rights reserved.
追加一个新列
DEPT80
追加一个新列
DEPT80
新列
1-287 Copyright © Oracle Corporation,  2001. All rights reserved.
追加一个新列
• 使用ADD 子句追加一个新列
• 新列是表中的最后一列
ALTER TABLE dept80
ADD (job_id VARCHAR2(9));
Table altered.
1-288 Copyright © Oracle Corporation,  2001. All rights reserved.
修改一个列
• 可以修改列的数据类型,  尺寸,  和默认值
• 对默认值的修改只影响今后对表的修改
ALTER TABLE dept80
MODIFY (last_name VARCHAR2(30));
Table altered.
1-289 Copyright © Oracle Corporation,  2001. All rights reserved.
删除一个列
使用DROP COLUMN 子句删除不再需要的列.
ALTER TABLE  dept80
DROP COLUMN  job_id;
Table altered.
1-290 Copyright © Oracle Corporation,  2001. All rights reserved.
ALTER TABLE table
SET   UNUSED (column);
ALTER TABLE  table
SET   UNUSED COLUMN  column;
SET UNUSED 选项
• 使用SET UNUSED使一个或多个列被标记为不可用
• 使用DROP UNUSED COLUMNS 选项删除不可用的列
OR
ALTER TABLE table
DROP  UNUSED COLUMNS;
1-291 Copyright © Oracle Corporation,  2001. All rights reserved.
删除表
• 数据和结构都被删除
• 所有正在运行的相关事物被提交
• 所有相关索引被删除
• DROP TABLE语句不能回滚
DROP TABLE dept80;
Table dropped.
1-292 Copyright © Oracle Corporation,  2001. All rights reserved.
改变对象的名称
• 执行RENAME 语句改变表,  视图,  序列,  或同义词的名称
• 必须是对象的拥有者
RENAME dept TO detail_dept;
Table renamed.
1-293 Copyright © Oracle Corporation,  2001. All rights reserved.
清空表
• TRUNCATE TABLE 语句:
– 删除表中所有的数据
– 释放表的存储空间
• TRUNCATE 语句不能回滚
• 可以使用DELETE 语句删除数据
TRUNCATE TABLE detail_dept;
Table truncated.
1-294 Copyright © Oracle Corporation,  2001. All rights reserved.
表的注释
• 使用COMMENT语句给表或列添加注释
• 可以通过下列数据字典视图查看所添加的注释:
– ALL_COL_COMMENTS
– USER_COL_COMMENTS
– ALL_TAB_COMMENTS
– USER_TAB_COMMENTS
COMMENT ON TABLE employees
IS 'Employee Information';
Comment created.
1-295 Copyright © Oracle Corporation,  2001. All rights reserved.
总结
语句 描述
CREATE TABLE 创建表
ALTER TABLE 修改表结构
DROP TABLE 删除表
RENAME 重命名表
TRUNCATE  删除表中的所有数据，并释放存储空间
COMMENT  给对象加注释
通过本章学习，您已经学会如何使用DDL语句
创建,  修改,  删除,  和重命名表.
1-296 Copyright © Oracle Corporation,  2001. All rights reserved.
约束
1-297 Copyright © Oracle Corporation,  2001. All rights reserved.
目标
通过本章学习，您将可以:
• 描述约束
• 创建和维护约束
1-298 Copyright © Oracle Corporation,  2001. All rights reserved.
什么是约束
• 约束是表级的强制规定
• 约束放置在表中删除有关联关系的数据
• 有以下五种约束:
– NOT NULL
– UNIQUE
– PRIMARY KEY
– FOREIGN KEY
– CHECK
1-299 Copyright © Oracle Corporation,  2001. All rights reserved.
注意事项
• 如果不指定约束名Oracle server 自动按照SYS_Cn 的格
式指定约束名
• 在什么时候创建约束:
– 建表的同时
– 建表之后
• 可以在表级或列级定义约束
• 可以通过数据字典视图查看约束
1-300 Copyright © Oracle Corporation,  2001. All rights reserved.
定义约束
CREATE TABLE [schema.]table
(column datatype [DEFAULT expr]
[column_constraint],
...
[table_constraint][,...]);
CREATE TABLE employees(
employee_id  NUMBER(6),
first_name   VARCHAR2(20),
...
job_id       VARCHAR2(10) NOT NULL,
CONSTRAINT emp_emp_id_pk
PRIMARY KEY (EMPLOYEE_ID));
1-301 Copyright © Oracle Corporation,  2001. All rights reserved.
定义约束
• 列级
• 表级
column [CONSTRAINT constraint_name] constraint_type,
column,...
[CONSTRAINT constraint_name] constraint_type
(column, ...),
1-302 Copyright © Oracle Corporation,  2001. All rights reserved.
NOT NULL 约束
保证列值不能为空:
NOT NULL 约束 无NOT NULL 约束 NOT NULL
约束
…
1-303 Copyright © Oracle Corporation,  2001. All rights reserved.
CREATE TABLE employees(
employee_id    NUMBER(6),
last_name      VARCHAR2(25) NOT NULL,
salary         NUMBER(8,2),
commission_pct NUMBER(2,2),
hire_date      DATE
CONSTRAINT emp_hire_date_nn
NOT NULL,
... 
NOT NULL 约束
只能定义在列级:
系统命名
用户命名
1-304 Copyright © Oracle Corporation,  2001. All rights reserved.
UNIQUE 约束
EMPLOYEES
UNIQUE 约束
INSERT INTO
不允许:  已经存
在
允许
…
1-305 Copyright © Oracle Corporation,  2001. All rights reserved.
UNIQUE 约束
可以定义在表级或列级:
CREATE TABLE employees(
employee_id      NUMBER(6),
last_name        VARCHAR2(25) NOT NULL,
email            VARCHAR2(25) ,
salary           NUMBER(8,2),
commission_pct   NUMBER(2,2),
hire_date        DATE NOT NULL,
... 
CONSTRAINT emp_email_uk UNIQUE(email));
1-306 Copyright © Oracle Corporation,  2001. All rights reserved.
PRIMARYKEY 约束
DEPARTMENTS
PRIMARY KEY
INSERT INTO 不允许
( 空值)
不允许
(50  已经存在)
…
1-307 Copyright © Oracle Corporation,  2001. All rights reserved.
CREATE TABLE   departments(
department_id        NUMBER(4),
department_name      VARCHAR2(30)
CONSTRAINT dept_name_nn NOT NULL,
manager_id           NUMBER(6),
location_id          NUMBER(4),
CONSTRAINT dept_id_pk PRIMARY KEY(department_id));
PRIMARYKEY 约束
可以定义在表级或列级:
1-308 Copyright © Oracle Corporation,  2001. All rights reserved.
FOREIGNKEY 约束
DEPARTMENTS
EMPLOYEES
FOREIGN
KEY
INSERT INTO
不允许( 9 不存
在)
允许
PRIMARY
KEY
…
…
1-309 Copyright © Oracle Corporation,  2001. All rights reserved.
FOREIGNKEY 约束
可以定义在表级或列级:
CREATE TABLE employees(
employee_id      NUMBER(6),
last_name        VARCHAR2(25) NOT NULL,
email            VARCHAR2(25),
salary           NUMBER(8,2),
commission_pct   NUMBER(2,2),
hire_date        DATE NOT NULL,
...
department_id    NUMBER(4),
CONSTRAINT emp_dept_fk FOREIGN KEY (department_id)
REFERENCES departments(department_id),
CONSTRAINT emp_email_uk UNIQUE(email));
1-310 Copyright © Oracle Corporation,  2001. All rights reserved.
FOREIGNKEY 约束的关键字
• FOREIGN KEY :  在表级指定子表中的列
• REFERENCES:  标示在父表中的列
• ON DELETE CASCADE:  当父表中的列被删除是，子表
中相对应的列也被删除
• ON DELETE SET NULL :  子表中相应的列置空
1-311 Copyright © Oracle Corporation,  2001. All rights reserved.
CHECK约束
• 定义每一行必须满足的条件
• 以下的表达式是不允许的:
– 出现CURRVAL,  NEXTVAL,  LEVEL,  和ROWNUM 伪列
– 使用SYSDATE,  UID ,  USER,  和USERENV函数
– 在查询中涉及到其它列的值
..., salary NUMBER(2)
CONSTRAINT emp_salary_min 
CHECK (salary > 0),...
1-312 Copyright © Oracle Corporation,  2001. All rights reserved.
添加约束的语法
使用ALTER TABLE 语句:
• 添加或删除约束,  但是不能修改约束
• 有效化或无效化约束
• 添加NOT NULL 约束要使用MODIFY 语句
ALTER TABLE table
ADD [CONSTRAINT constraint] type (column);
1-313 Copyright © Oracle Corporation,  2001. All rights reserved.
添加约束
添加约束举例
ALTER TABLE     employees
ADD CONSTRAINT  emp_manager_fk
FOREIGN KEY(manager_id)
REFERENCES employees(employee_id);
Table altered.
1-314 Copyright © Oracle Corporation,  2001. All rights reserved.
删除约束
• 从表EMPLOYEES 中删除约束
• 使用CASCADE 选项删除约束
ALTER TABLE      employees
DROP CONSTRAINT  emp_manager_fk;
Table altered.
ALTER TABLE departments
DROP PRIMARY KEY CASCADE;
Table altered.
1-315 Copyright © Oracle Corporation,  2001. All rights reserved.
无效化约束
• 在ALTERTABLE语句中使用DISABLE子句将约束无
效化。
• 使用CASCADE选项将相关的约束也无效化
ALTER TABLE employees
DISABLE CONSTRAINT emp_emp_id_pk CASCADE;
Table altered.
1-316 Copyright © Oracle Corporation,  2001. All rights reserved.
激活约束
• ENABLE 子句可将当前无效的约束激活
• 当定义或激活UNIQUE 或PRIMARY KEY 约束时系统会
自动创建UNIQUE 或PRIMARY KEY 索引
ALTER TABLE employees
ENABLE CONSTRAINT emp_emp_id_pk;
Table altered.
1-317 Copyright © Oracle Corporation,  2001. All rights reserved.
及连约束
• CASCADE CONSTRAINTS 子句在DROP COLUMN 子句
中使用
• 在删除表的列时CASCADE CONSTRAINTS 子句指定
将相关的约束一起删除
• 在删除表的列时CASCADE CONSTRAINTS 子句同时
也删除多列约束
1-318 Copyright © Oracle Corporation,  2001. All rights reserved.
及连约束
及连约束举例:
ALTER TABLE test1
DROP (pk) CASCADE CONSTRAINTS;
Table altered.
ALTER TABLE test1
DROP (pk, fk, col1) CASCADE CONSTRAINTS;
Table altered.
1-319 Copyright © Oracle Corporation,  2001. All rights reserved.
SELECT constraint_name, constraint_type,
search_condition
FROM user_constraints
WHERE table_name = 'EMPLOYEES';
查询约束
查询数据字典视图USER_CONSTRAINTS
…
1-320 Copyright © Oracle Corporation,  2001. All rights reserved.
SELECT constraint_name, column_name
FROM user_cons_columns
WHERE table_name = 'EMPLOYEES';
查询定义约束的列
查询数据字典视图USER_CONS_COLUMNS
…
1-321 Copyright © Oracle Corporation,  2001. All rights reserved.
总结
通过本章学习，您已经学会如何创建约束
描述约束的类型:
– NOT NULL
– UNIQUE
– PRIMARY KEY
– FOREIGN KEY
– CHECK
• 查询数据字典视图以获得约束的信息
1-322 Copyright © Oracle Corporation,  2001. All rights reserved.
视图
1-323 Copyright © Oracle Corporation,  2001. All rights reserved.
目标
通过本章学习，您将可以:
• 描述视图
• 创建和修改视图的定义，删除视图
• 从视图中查询数据
• 通过视图插入,  修改和删除数据
• 创建和使用临时视图
• 使用“Top -N” 分析
1-324 Copyright © Oracle Corporation,  2001. All rights reserved.
常见的数据库对象
对象 描述
表 基本的数据存储集合，由行和列组成。
视图 从表中抽出的逻辑上相关的数据集合。
序列 提供有规律的数值。
索引 提高查询的效率
同义词 给对象起别名
1-325 Copyright © Oracle Corporation,  2001. All rights reserved.
视图
表EMPLOYEES :
1-326 Copyright © Oracle Corporation,  2001. All rights reserved.
为什么使用视图
• 控制数据访问
• 简化查询
• 数据独立性
• 避免重复访问相同的数据
1-327 Copyright © Oracle Corporation,  2001. All rights reserved.
简单视图和复杂视图
特性 简单视图 复杂视图
表的数量 一个 一个或多个
函数 没有 有
分组 没有 有
DML 操作 可以 有时可以
1-328 Copyright © Oracle Corporation,  2001. All rights reserved.
创建视图
• 在CREATE VIEW 语句中嵌入子查询
• 子查询可以是复杂的SELECT 语句
CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view
[(alias[,  alias]...)]
AS  subquery
[WITH CHECK OPTION [CONSTRAINT constraint]]
[WITH READ ONLY [CONSTRAINT  constraint]];
1-329 Copyright © Oracle Corporation,  2001. All rights reserved.
创建视图
• 创建视图举例
• 描述视图结构
DESCRIBE empvu80
CREATE VIEW empvu80
AS SELECT  employee_id, last_name, salary
FROM    employees
WHERE   department_id = 80;
View created.
1-330 Copyright © Oracle Corporation,  2001. All rights reserved.
创建视图
• 创建视图时在子查询中给列定义别名
• 在选择视图中的列时应使用别名
CREATE VIEW salvu50
AS SELECT  employee_id ID_NUMBER, last_name NAME,
salary*12 ANN_SALARY
FROM    employees
WHERE   department_id = 50;
View created.
1-331 Copyright © Oracle Corporation,  2001. All rights reserved.
查询视图
SELECT *
FROM salvu50;
1-332 Copyright © Oracle Corporation,  2001. All rights reserved.
查询视图
USER_VIEWS
EMPVU80
SELECT employee_id,
last_name, salary
FROM   employees
WHERE  department_id=80;
SQL*Plus
SELECT   *
FROM       empvu80 ;
EMPLOYEES
Oracle Server
1-333 Copyright © Oracle Corporation,  2001. All rights reserved.
修改视图
• 使用CREATE OR REPLACE VIEW子句修改视图
• CREATE VIEW 子句中各列的别名应和子查询中各列相
对应
CREATE OR REPLACE VIEW empvu80
(id_number, name, sal, department_id)
AS SELECT  employee_id, first_name || ' ' || last_name,
salary, department_id
FROM    employees
WHERE   department_id = 80;
View created.
1-334 Copyright © Oracle Corporation,  2001. All rights reserved.
创建复杂视图
复杂视图举例
CREATE VIEW dept_sum_vu
(name, minsal, maxsal, avgsal)
AS SELECT d.department_name, MIN(e.salary),
MAX(e.salary),AVG(e.salary)
FROM      employees e, departments d
WHERE     e.department_id = d.department_id
GROUP BY  d.department_name;
View created.
1-335 Copyright © Oracle Corporation,  2001. All rights reserved.
视图中使用DML的规定
• 可以在简单视图中执行DML 操作
• 当视图定义中包含以下元素之一时不能使用delete:
– 组函数
– GROUP BY 子句
– DISTINCT 关键字
– ROWNUM 伪列
1-336 Copyright © Oracle Corporation,  2001. All rights reserved.
视图中使用DML的规定
当视图定义中包含以下元素之一时不能使用update :
• 组函数
• GROUP BY 子句
• DISTINCT 关键字
• ROWNUM 伪列
• 列的定义为表达式
1-337 Copyright © Oracle Corporation,  2001. All rights reserved.
视图中使用DML的规定
当视图定义中包含以下元素之一时不能使用insert :
• 组函数
• GROUP BY 子句
• DISTINCT 关键字
• ROWNUM 伪列
• 列的定义为表达式
• 表中非空的列在视图定义中未包括
1-338 Copyright © Oracle Corporation,  2001. All rights reserved.
• 使用WITH CHECK OPTION子句确保DML只能在特定
的范围内执行
• 任何违反WITH CHECK OPTION约束的请求都会失败
CREATE OR REPLACE VIEW empvu20
AS SELECT *
FROM     employees
WHERE    department_id = 20
WITH CHECK OPTION CONSTRAINT empvu20_ck ;
View created.
WITH CHECKOPTION 子句
1-339 Copyright © Oracle Corporation,  2001. All rights reserved.
屏蔽DML 操作
• 可以使用WITH READ ONLY 选项屏蔽对视图的DML
操作
• 任何DML 操作都会返回一个Oracle server 错误
1-340 Copyright © Oracle Corporation,  2001. All rights reserved.
屏蔽DML 操作
CREATE OR REPLACE VIEW empvu10
(employee_number, employee_name, job_title)
AS SELECT employee_id, last_name, job_id
FROM     employees
WHERE    department_id = 10
WITH READ ONLY;
View created.
1-341 Copyright © Oracle Corporation,  2001. All rights reserved.
删除视图
删除视图只是删除视图的定义，并不会删除基表的数据
DROP VIEW empvu80;
View dropped.
DROP VIEW  view;
1-342 Copyright © Oracle Corporation,  2001. All rights reserved.
临时视图
• 临时视图可以是嵌套在SQL语句中的子查询
• 在FROM 子句中的的子查询是临时视图
• 临时视图不是数据库对象
1-343 Copyright © Oracle Corporation,  2001. All rights reserved.
Top -N  分析
• Top -N  分析查询一个列中最大或最小的n 个值:
– 销售量最高的十种产品是什么?
– 销售量最差的十种产品是什么?
• 最大和最小的值的集合是Top -N  分析所关心的
1-344 Copyright © Oracle Corporation,  2001. All rights reserved.
Top -N  分析
查询最大的几个值的Top -N  分析:
SELECT [ column_list], ROWNUM 
FROM   (SELECT [column_list]
FROM table
ORDER  BY Top -N_column)
WHERE  ROWNUM <=  N;
1-345 Copyright © Oracle Corporation,  2001. All rights reserved.
Top -N  分析
查询工资最高的三名员工:
SELECT ROWNUM as RANK, last_name, salary
FROM  (SELECT last_name,salary FROM employees
ORDER BY salary DESC)
WHERE ROWNUM <= 3;
3 1 2
1 2 3
1-346 Copyright © Oracle Corporation,  2001. All rights reserved.
总结
通过本章学习，您已经了解视图的优点和基本应用:
• 控制数据访问
• 简化查询
• 数据独立性
• 删除时不删除数据
• 子查询是临时视图的一种
• Top -N  分析
1-347 Copyright © Oracle Corporation,  2001. All rights reserved.
其它数据库对象
1-348 Copyright © Oracle Corporation,  2001. All rights reserved.
目标
通过本章学习，您将可以:
• 创建,  维护,  和使用序列
• 创建和维护索引
• 创建私有和公有同义词
1-349 Copyright © Oracle Corporation,  2001. All rights reserved.
常见的数据库对象
对象 描述
表 基本的数据存储集合，由行和列组成。
视图 从表中抽出的逻辑上相关的数据集合。
序列 提供有规律的数值。
索引 提高查询的效率
同义词 给对象起别名
1-350 Copyright © Oracle Corporation,  2001. All rights reserved.
什么是序列?
序列:
• 自动提供唯一的数值
• 共享对象
• 主要用于提供主键值
• 代替应用代码
• 将序列值装入内存可以提高访问效率
1-351 Copyright © Oracle Corporation,  2001. All rights reserved.
CREATE SEQUENCE 语句
定义序列:
CREATE SEQUENCE sequence
[INCREMENT BY n]
[START WITH n]
[{MAXVALUE n | NOMAXVALUE}]
[{MINVALUE n | NOMINVALUE}]
[{CYCLE |  NOCYCLE}]
[{CACHE  n | NOCACHE}];
1-352 Copyright © Oracle Corporation,  2001. All rights reserved.
创建序列
• 创建序列DEPT_DEPTID_SEQ为表DEPARTMENTS 提
供主键
• 不使用CYCLE选项
CREATE SEQUENCE dept_deptid_seq
INCREMENT BY 10
START WITH 120
MAXVALUE 9999
NOCACHE
NOCYCLE;
Sequence created.
1-353 Copyright © Oracle Corporation,  2001. All rights reserved.
查询序列
• 查询数据字典视图USER_SEQUENCES获取序列定义信
息
• 如果指定NOCACHE  选项，则列LAST_NUMBER 显示序
列中下一个有效的值
SELECT sequence_name, min_value, max_value,
increment_by, last_number
FROM user_sequences;
1-354 Copyright © Oracle Corporation,  2001. All rights reserved.
NEXTVAL和CURRVAL伪列
• NEXTVAL返回序列中下一个有效的值，任何用户都可
以引用
• CURRVAL中存放序列的当前值
• NEXTVAL应在CURRVAL之前指定，二者应同时有效
1-355 Copyright © Oracle Corporation,  2001. All rights reserved.
序列应用举例
• 序列DEPT_DEPTID_SEQ的当前值
INSERT INTO departments(department_id,
department_name, location_id)
VALUES      (dept_deptid_seq.NEXTVAL,
'Support', 2500);
1 row created.
SELECT dept_deptid_seq.CURRVAL
FROM dual;
1-356 Copyright © Oracle Corporation,  2001. All rights reserved.
使用序列
• 将序列值装入内存可提高访问效率
• 序列在下列情况下出现裂缝:
– 回滚
– 系统异常
– 多个表同时使用同一序列
• 如果不讲序列的值装入内存(NOCACHE) ,  可使用表
USER_SEQUENCES 查看序列当前的有效值
1-357 Copyright © Oracle Corporation,  2001. All rights reserved.
修改序列
修改序列的增量,  最大值,  最小值,  循环选项,  或是否装入内
存
ALTER SEQUENCE dept_deptid_seq
INCREMENT BY 20
MAXVALUE 999999
NOCACHE
NOCYCLE;
Sequence altered.
1-358 Copyright © Oracle Corporation,  2001. All rights reserved.
修改序列的注意事项
• 必须是序列的拥有者或对序列有ALTER权限
• 只有将来的序列值会被改变
• 改变序列的初始值只能通过删除序列之后重建序列的方
法实现
• 其它的一些限制
1-359 Copyright © Oracle Corporation,  2001. All rights reserved.
删除序列
• 使用DROP SEQUENCE 语句删除序列
• 删除之后，序列不能再次被引用
DROP SEQUENCE dept_deptid_seq;
Sequence dropped.
1-360 Copyright © Oracle Corporation,  2001. All rights reserved.
索引
索引:
• 一种数据库对象
• 通过指针加速Oracle 服务器的查询速度
• 通过快速定位数据的方法，减少磁盘I/O
• 索引与表相互独立
• Oracle 服务器自动使用和维护索引
1-361 Copyright © Oracle Corporation,  2001. All rights reserved.
创建索引
• 自动创建:  在定义PRIMARY KEY 或UNIQUE 约束后系
统自动在相应的列上创建唯一性索引
• 手动创建:  用户可以在其它列上创建非唯一的索引，以
加速查询
1-362 Copyright © Oracle Corporation,  2001. All rights reserved.
创建索引
• 在一个或多个列上创建索引
• 在表EMPLOYEES的列LAST_NAME 上创建索引
CREATE INDEX  emp_last_name_idx
ON  employees(last_name);
Index created.
CREATE INDEX  index
ON  table(column[,  column]...);
1-363 Copyright © Oracle Corporation,  2001. All rights reserved.
什么时候创建索引
以下情况可以创建索引:
• 列中数据值分布范围很广
• 列中包含大量空值
• 列经常在WHERE子句或连接条件中出现
• 表经常被访问而且数据量很大，访问的数据大概占数
据总量的2%到4%
1-364 Copyright © Oracle Corporation,  2001. All rights reserved.
什么时候不要创建索引
下列情况不要创建索引:
• 表很小
• 列不经常作为连接条件或出现在WHERE子句中
• 查询的数据大于2%到4%
• 表经常更新
• 加索引的列包含在表达式中
1-365 Copyright © Oracle Corporation,  2001. All rights reserved.
SELECT ic.index_name, ic.column_name,
ic.column_position col_pos,ix.uniqueness
FROM user_indexes ix, user_ind_columns ic
WHERE ic.index_name = ix.index_name
AND ic.table_name = 'EMPLOYEES';
查询索引
• 可以使用数据字典视图USER_INDEXES和
USER_IND_COLUMNS 查看索引的信息
1-366 Copyright © Oracle Corporation,  2001. All rights reserved.
基于函数的索引
• 基于函数的索引是一个基于表达式的索引
• 索引表达式由列,  常量, SQL  函数和用户自定义的函数
CREATE INDEX upper_dept_name_idx
ON departments(UPPER(department_name));
Index created.
SELECT *
FROM   departments
WHERE  UPPER(department_name) = 'SALES';
1-367 Copyright © Oracle Corporation,  2001. All rights reserved.
删除索引
• 使用DROP INDEX命令删除索引
• 删除索引UPPER_LAST_NAME_IDX
• 只有索引的拥有者或拥有DROP ANY INDEX权限的用
户才可以删除索引
DROP INDEX upper_last_name_idx;
Index dropped.
DROP INDEX index;
1-368 Copyright © Oracle Corporation,  2001. All rights reserved.
同义词
使用同义词访问相同的对象:
• 方便访问其它用户的对象
• 缩短对象名字的长度
CREATE [PUBLIC] SYNONYM synonym
FOR    object;
1-369 Copyright © Oracle Corporation,  2001. All rights reserved.
创建和删除同义词
• 为视图DEPT_SUM_VU 创建同义词
• 删除同义词
CREATE SYNONYM  d_sum
FOR  dept_sum_vu;
Synonym Created.
DROP SYNONYM d_sum;
Synonym dropped.
1-370 Copyright © Oracle Corporation,  2001. All rights reserved.
总结
通过本章学习, 您已经可以:
• 使用序列
• 通过数据字典视图USER_SEQUENCES 查看序列信息
• 使用索引提高查询效率
• 通过数据字典视图USER_INDEXES查看索引信息
• 为数据对象定义同义词
1-371 Copyright © Oracle Corporation,  2001. All rights reserved.
控制用户权限
1-372 Copyright © Oracle Corporation,  2001. All rights reserved.
目标
通过本章学习，您将可以:
• 创建用户
• 创建角色
• 使用GRANT和REVOKE 语句赋予和回收权限
• 创建数据库联接
1-373 Copyright © Oracle Corporation,  2001. All rights reserved.
控制用户权限
数据库管理员
用户
用户名和密码
权限
1-374 Copyright © Oracle Corporation,  2001. All rights reserved.
权限
• 数据库安全性:
– 系统安全性
– 数据安全性
• 系统权限:  对于数据库的权限
• 对象权限:  操作数据库对象的权限
• 方案:  一组数据库对象集合,  例如表,  视图, 和序列
1-375 Copyright © Oracle Corporation,  2001. All rights reserved.
系统权限
• 超过一百多种100  有效的权限
• 数据库管理员具有高级权限以完成管理任务，例如:
– 创建新用户
– 删除用户
– 删除表
– 备份表
1-376 Copyright © Oracle Corporation,  2001. All rights reserved.
创建用户
DBA 使用CREATE USER 语句创建用户
CREATE USER  scott
IDENTIFIED BY   tiger;
User created.
CREATE USER user
IDENTIFIED BY   password ;
1-377 Copyright © Oracle Corporation,  2001. All rights reserved.
用户的系统权限
• 用户创建之后,  DBA 会赋予用户一些系统权限
• 以应用程序开发者为例,  一般具有下列系统权限:
– CREATE SESSION（创建会话）
– CREATE TABLE（创建表）
– CREATE SEQUENCE （创建序列）
– CREATE VIEW（创建视图）
– CREATE PROCEDURE（创建过程）
GRANT privilege [,  privilege...]
TO  user [,  user| role, PUBLIC ...];
1-378 Copyright © Oracle Corporation,  2001. All rights reserved.
赋予系统权限
DBA 可以赋予用户特定的权限
GRANT  create session, create table,
create sequence, create view
TO     scott;
Grant succeeded.
1-379 Copyright © Oracle Corporation,  2001. All rights reserved.
角色
不使用角色分配权限 使用角色分配权限
权限
用户
Manager
1-380 Copyright © Oracle Corporation,  2001. All rights reserved.
创建角色并赋予权限
CREATE ROLE manager;
Role created.
GRANT create table, create view
TO manager;
Grant succeeded.
GRANT manager TO DEHAAN, KOCHHAR;    
Grant succeeded.
• 创建角色
• 为角色赋予权限
• 将角色赋予用户
1-381 Copyright © Oracle Corporation,  2001. All rights reserved.
修改密码
• DBA 可以创建用户和修改密码
• 用户本人可以使用ALTER USER 语句修改密码
ALTER USER scott            
IDENTIFIED BY lion;
User altered.
1-382 Copyright © Oracle Corporation,  2001. All rights reserved.
对象权限 表 视图 序列 过程
修改 √ √
删除 √ √
执行 √
索引 √
插入 √ √
关联 √ √
选择 √ √ √
更新 √ √
对象权限
1-383 Copyright © Oracle Corporation,  2001. All rights reserved.
对象权限
• 不同的对象具有不同的对象权限
• 对象的拥有者拥有所有权限
• 对象的拥有者可以向外分配权限
GRANT object_priv [(columns)]
ON object
TO {user|role|PUBLIC}
[WITH GRANT OPTION];
1-384 Copyright © Oracle Corporation,  2001. All rights reserved.
分配对象权限
• 分配表EMPLOYEES 的查询权限
• 分配表中各个列的更新权限
GRANT  select
ON     employees
TO     sue, rich;
Grant succeeded.
GRANT  update (department_name, location_id)
ON     departments
TO     scott, manager
Grant succeeded.
1-385 Copyright © Oracle Corporation,  2001. All rights reserved.
WITH GRANTOPTION 和PUBLIC 关键字
• WITH GRANTOPTION 使用户同样具有分配权限的权利
• 向数据库中所有用户分配权限
GRANT  select, insert
ON     departments
TO     scott
WITH   GRANT OPTION;
Grant succeeded.
GRANT  select
ON alice.departments
TO PUBLIC;
Grant succeeded.
1-386 Copyright © Oracle Corporation,  2001. All rights reserved.
查询权限分配情况
数据字典视图 描述
ROLE_SYS_PRIVS 角色拥有的系统权限
ROLE_TAB_PRIVS 角色拥有的对象权限
USER_ROLE_PRIVS 用户拥有的角色
USER_TAB_PRIVS_MADE 用户分配的关于表对象权限
USER_TAB_PRIVS_RECD 用户拥有的关于表对象权限
USER_COL_PRIVS_MADE 用户分配的关于列的对象权限
USER_COL_PRIVS_RECD 用户拥有的关于列的对象权限
USER_SYS_PRIVS 用户拥有的系统权限
1-387 Copyright © Oracle Corporation,  2001. All rights reserved.
收回对象权限
• 使用REVOKE 语句收回权限
• 使用WITH GRANT OPTION子句所分配的权限同样被
收回
REVOKE {privilege [, privilege...]|ALL}
ON object
FROM   {user[, user...]|role|PUBLIC}
[CASCADE CONSTRAINTS];
1-388 Copyright © Oracle Corporation,  2001. All rights reserved.
收回对象权限举例
REVOKE  select, insert
ON      departments
FROM    scott;
Revoke succeeded.
1-389 Copyright © Oracle Corporation,  2001. All rights reserved.
数据库联接
数据库联接使用户可以在本地访问远程数据库
本地数据库 远程数据库
SELECT * FROM
emp@HQ_ACME.COM;
HQ_ACME.COM
database
EMP Ta b l e
1-390 Copyright © Oracle Corporation,  2001. All rights reserved.
数据库联接
• 创建数据库联接
• 使用SQL 语句访问远程数据库
CREATE PUBLIC DATABASE LINK hq.acme.com
USING 'sales';
Database link created.
SELECT *
FROM [e-mail];
1-391 Copyright © Oracle Corporation,  2001. All rights reserved.
总结
语句 功能
CREATE USER 创建用户( 通常由DBA 完成)
GRANT 分配权限
CREATE ROLE 创建角色( 通常由DBA 完成)
ALTER USER 修改用户密码
REVOKE 收回权限
通过本章学习, 您已经可以使用DCL 控制数据库权限，
创建数据库联接:
1-392 Copyright © Oracle Corporation,  2001. All rights reserved.
SET 运算符
1-393 Copyright © Oracle Corporation,  2001. All rights reserved.
目标
通过本章学习，您将可以:
• 描述SET 操作符
• 将多个查询用SET 操作符联接组成一个新的查询
• 排序
1-394 Copyright © Oracle Corporation,  2001. All rights reserved.
SET 操作符
A B
UNION/UNION ALL
A B
A B
INTERSECT
A B
MINUS
1-395 Copyright © Oracle Corporation,  2001. All rights reserved.
UNION操作符
UNION操作符返回两个查询的结果集的并集
A
B
1-396 Copyright © Oracle Corporation,  2001. All rights reserved.
UNION操作符举例
SELECT employee_id, job_id
FROM   employees
UNION
SELECT employee_id, job_id
FROM   job_history;
…
1-397 Copyright © Oracle Corporation,  2001. All rights reserved.
UNION ALL  操作符
A B
UNION ALL 操作符返回两个查询的结果集的并集以及两个结果
集的重复部分（不去重）
1-398 Copyright © Oracle Corporation,  2001. All rights reserved.
UNION ALL  操作符举例
SELECT employee_id, job_id, department_id
FROM   employees
UNION ALL
SELECT employee_id, job_id, department_id
FROM   job_history
ORDER BY  employee_id;
…
…
1-399 Copyright © Oracle Corporation,  2001. All rights reserved.
INTERSECT 操作符
A B
INTERSECT 操作符返回两个结果
集的交集
1-400 Copyright © Oracle Corporation,  2001. All rights reserved.
INTERSECT 操作符举例
SELECT employee_id, job_id
FROM   employees
INTERSECT
SELECT employee_id, job_id
FROM   job_history;
1-401 Copyright © Oracle Corporation,  2001. All rights reserved.
MINUS 操作符
A B
MINUS 操作符返回两个结果集的补集
1-402 Copyright © Oracle Corporation,  2001. All rights reserved.
MINUS 操作符举例
SELECT employee_id,job_id
FROM   employees
MINUS
SELECT employee_id,job_id
FROM   job_history;
…
1-403 Copyright © Oracle Corporation,  2001. All rights reserved.
使用SET 操作符注意事项
• 在SELECT 列表中的列名和表达式在数量和数据类型上
要相对应
• 括号可以改变执行的顺序
• ORDER BY 子句:
– 只能在语句的最后出现
– 可以使用第一个查询中的列名,  别名或相对位置
1-404 Copyright © Oracle Corporation,  2001. All rights reserved.
SET 操作符
• 除UNION ALL之外，系统会自动将重复的记录删除
• 系统将第一个查询的列名显示在输出中
• 除UNION ALL之外，系统自动按照第一个查询中的第
一个列的升序排列
1-405 Copyright © Oracle Corporation,  2001. All rights reserved.
匹配各SELECT 语句举例
SELECT department_id, TO_NUMBER(null)
location, hire_date
FROM   employees
UNION
SELECT department_id, location_id,  TO_DATE(null)
FROM   departments;
…
1-406 Copyright © Oracle Corporation,  2001. All rights reserved.
匹配各SELECT 语句举例
SELECT employee_id, job_id,salary
FROM   employees
UNION
SELECT employee_id, job_id,0
FROM   job_history;
…
1-407 Copyright © Oracle Corporation,  2001. All rights reserved.
使用相对位置排序举例
COLUMN a_dummy NOPRINT
SELECT 'sing' AS "My dream", 3 a_dummy
FROM dual
UNION
SELECT 'I''d like to teach', 1
FROM dual
UNION
SELECT 'the world to', 2
FROM dual
ORDER BY 2;
1-408 Copyright © Oracle Corporation,  2001. All rights reserved.
总结
通过本章学习, 您已经可以:
• 使用UNION操作符
• 使用UNION ALL 操作符
• 使用INTERSECT 操作符
• 使用MINUS操作符
• 使用ORDER BY 对结果集排序
1-409 Copyright © Oracle Corporation,  2001. All rights reserved.
Oracle9i 日期函数
1-410 Copyright © Oracle Corporation,  2001. All rights reserved.
目标
通过本章学习，您将可以使用下列日期函数:
• TZ_OFFSET
• CURRENT_DATE
• CURRENT_TIMESTAMP
• LOCALTIMESTAMP
• DBTIMEZONE
• SESSIONTIMEZONE
• EXTRACT
• FROM_TZ
• TO_TIMESTAMP
• TO_TIMESTAMP_TZ
• TO_YMINTERVAL
1-411 Copyright © Oracle Corporation,  2001. All rights reserved.
时区
-08:00
上图显示了全球24个时区以及当格林威
治时间是12:00时各时区的时差
-05:00
+02:00 +10:00
+07:00
1-412 Copyright © Oracle Corporation,  2001. All rights reserved.
Oracle9i  日期支持
• Oracle9i 中,  可以将时区加入到日期和时间中而且可以
将秒进行进一步的精确
• 日期中加入了三种新的数据类型:
– TIMESTAMP（时间撮）
– TIMESTAMP WITH TIME ZONE (TSTZ) （带时区的
时间撮）
– TIMESTAMP WITH LOCAL TIME ZONE (TSLTZ) （
带有本地时区的时间撮）
• Oracle9i 支持夏令时
1-413 Copyright © Oracle Corporation,  2001. All rights reserved.
TZ_OFFSET
SELECT TZ_OFFSET('US/Eastern') FROM DUAL;
SELECT TZ_OFFSET('Canada/Yukon') FROM DUAL;
SELECT TZ_OFFSET('Europe/London') FROM DUAL;
• 显示时区‘US/Eastern’ 的时差
• 显示时区'Canada/Yukon'的时差
• 显示时区'Europe/London'的时差
1-414 Copyright © Oracle Corporation,  2001. All rights reserved.
CURRENT_DATE
• CURRENT_DATE对会话所在的时区是敏感的
ALTER SESSION SET TIME_ZONE = '-8:0';
SELECT SESSIONTIMEZONE, CURRENT_DATE FROM DUAL;
ALTER SESSION SET TIME_ZONE = '-5:0';
SELECT SESSIONTIMEZONE, CURRENT_DATE FROM DUAL;
• 按照当前会话的时区显示当前会话的时间
ALTER SESSION
SET NLS_DATE_FORMAT = 'DD -MON -YYYY HH24:MI:SS';
1-415 Copyright © Oracle Corporation,  2001. All rights reserved.
CURRENT_TIMESTAMP
ALTER SESSION SET TIME_ZONE = '-8:0';
SELECT SESSIONTIMEZONE, CURRENT_TIMESTAMP
FROM DUAL;
ALTER SESSION SET TIME_ZONE = '-5:0';
SELECT SESSIONTIMEZONE, CURRENT_TIMESTAMP
FROM DUAL;
• 按照当前会话的时区显示当前会话的时间
• CURRENT_TIMESTAMP  对会话所在的时区是敏感的
• 返回值是TIMESTAMP WITH TIME ZONE 数据类型
1-416 Copyright © Oracle Corporation,  2001. All rights reserved.
LOCALTIMESTAMP
ALTER SESSION SET TIME_ZONE = '-5:0';
SELECT CURRENT_TIMESTAMP, LOCALTIMESTAMP
FROM DUAL;
ALTER SESSION SET TIME_ZONE = '-8:0';
SELECT CURRENT_TIMESTAMP, LOCALTIMESTAMP
FROM DUAL;
• 按照当前会话的时区显示当前会话的时间
• LOCALTIMESTAMP对会话所在的时区是敏感的
• 返回值是TIMESTAMP 数据类型
1-417 Copyright © Oracle Corporation,  2001. All rights reserved.
DBTIMEZONE和SESSIONTIMEZONE
SELECT DBTIMEZONE FROM DUAL;
SELECT SESSIONTIMEZONE FROM DUAL;
• 显示数据库所在的时区
• 显示会话所在的时区
1-418 Copyright © Oracle Corporation,  2001. All rights reserved.
EXTRACT
SELECT EXTRACT (YEAR FROM SYSDATE) FROM DUAL;
SELECT last_name, hire_date,
EXTRACT (MONTH FROM HIRE_DATE)
FROM employees
WHERE manager_id = 100;
• 从SYSDATE  中抽出年
• 从HIRE_DATE 中抽出月
1-419 Copyright © Oracle Corporation,  2001. All rights reserved.
FROM_TZ  应用举例
SELECT FROM_TZ(TIMESTAMP
'2000-03-28 08:00:00','3:00')
FROM DUAL;
SELECT FROM_TZ(TIMESTAMP
'2000-03-28 08:00:00', 'Australia/North')
FROM DUAL;
1-420 Copyright © Oracle Corporation,  2001. All rights reserved.
SELECT
TO_TIMESTAMP_TZ('1999-12-01 11:00:00 -8:00',
'YYYY-MM-DD HH:MI:SS TZH:TZM')
FROM DUAL;
TO_TIMESTAMP和TO_TIMESTAMP_TZ 应用
举例
SELECT TO_TIMESTAMP ('2000-12-01 11:00:00',
'YYYY-MM-DD HH:MI:SS')   
FROM DUAL;
1-421 Copyright © Oracle Corporation,  2001. All rights reserved.
TO_YMINTERVAL 应用举例
SELECT hire_date,
hire_date + TO_YMINTERVAL('01-02') AS
HIRE_DATE_YMININTERVAL
FROM EMPLOYEES
WHERE department_id = 20;
1-422 Copyright © Oracle Corporation,  2001. All rights reserved.
总结
• TZ_OFFSET
• FROM_TZ
• TO_TIMESTAMP
• TO_TIMESTAMP_TZ
• TO_YMINTERVAL
• CURRENT_DATE
• CURRENT_TIMESTAMP
• LOCALTIMESTAMP
• DBTIMEZONE
• SESSIONTIMEZONE
• EXTRACT
通过本章学习, 您已经可以使用:
1-423 Copyright © Oracle Corporation,  2001. All rights reserved.
对GROUP BY 子句的扩展
1-424 Copyright © Oracle Corporation,  2001. All rights reserved.
目标
通过本章学习，您将可以:
• 使用ROLLUP 操作分组
• 使用CUBE 操作分组
• 使用GROUPING 函数处理ROLLUP 或CUBE操作所产
生的空值
• 使用GROUPING SETS 操作进行单独分组
1-425 Copyright © Oracle Corporation,  2001. All rights reserved.
组函数
组函数处理多行返回一个行
SELECT [column,]  group_function(column). . .
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[ORDER BY column];
SELECT AVG(salary), STDDEV(salary),
COUNT(commission_pct),MAX(hire_date)
FROM   employees
WHERE  job_id LIKE 'SA%';
例子:
1-426 Copyright © Oracle Corporation,  2001. All rights reserved.
GROUP BY 子句
SELECT   department_id, job_id, SUM(salary),
COUNT(employee_id)
FROM     employees
GROUP BY department_id, job_id ;
SELECT [column,]
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[ORDER BY column];
例子:
语法:
group_function(column). . .
1-427 Copyright © Oracle Corporation,  2001. All rights reserved.
HAVING 子句
• 使用HAVING 对组函数进行限制
• 对查询进行第二次限制
SELECT [column,]  group_function(column)...
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[HAVING  having_expression]
[ORDER BY column];
1-428 Copyright © Oracle Corporation,  2001. All rights reserved.
带有ROLLUP 和
CUBE 操作的GROUP BY 子句
• 使用带有ROLLUP 和CUBE 操作的GROUP BY 子句产生
多种分组结果
• ROLLUP 产生n + 1种分组结果
• CUBE 产生2 的n次方种分组结果
1-429 Copyright © Oracle Corporation,  2001. All rights reserved.
ROLLUP 操作符
• ROLLUP 是对GROUP BY 子句的扩展
• ROLLUP 产生n + 1种分组结果，顺序是从右向左
SELECT [column,]  group_function(column). . .
FROM table
[WHERE condition]
[GROUP BY [ROLLUP] group_by_expression]
[HAVING  having_expression];
[ORDER BY column];
1-430 Copyright © Oracle Corporation,  2001. All rights reserved.
ROLLUP 应用举例
SELECT   department_id, job_id, SUM(salary)
FROM     employees 
WHERE    department_id < 60
GROUP BY ROLLUP(department_id, job_id);
1
2
3
1-431 Copyright © Oracle Corporation,  2001. All rights reserved.
CUBE 操作符
• CUBE是对GROUP BY 子句的扩展
• CUBE 会产生类似于笛卡尔集的分组结果
SELECT [column,]  group_function(column)...
FROM table
[WHERE condition]
[GROUP BY [CUBE] group_by_expression]
[HAVING  having_expression]
[ORDER BY column];
1-432 Copyright © Oracle Corporation,  2001. All rights reserved.
CUBE 应用举例
SELECT   department_id, job_id, SUM(salary)
FROM     employees 
WHERE    department_id < 60
GROUP BY CUBE (department_id, job_id) ;
1
2
3
4
1-433 Copyright © Oracle Corporation,  2001. All rights reserved.
GROUPING 函数
• GROUPING 函数可以和CUBE 或ROLLUP 结合使用
• 使用GROUPING 函数，可以找到哪些列在该行中参加
了分组
• 使用GROUPING 函数,  可以区分空值产生的原因
• GROUPING 函数返回0  或1
SELECT    [column,]  group_function(column) . ,
GROUPING(expr)
FROM table
[WHERE     condition]
[GROUP BY [ROLLUP][CUBE]  group_by_expression]
[HAVING    having_expression]
[ORDER BY  column];
1-434 Copyright © Oracle Corporation,  2001. All rights reserved.
GROUPING 函数举例
SELECT   department_id DEPTID, job_id JOB,
SUM(salary),
GROUPING(department_id) GRP_DEPT,
GROUPING(job_id) GRP_JOB
FROM     employees
WHERE    department_id < 50
GROUP BY ROLLUP(department_id, job_id);
2
1
3
1-435 Copyright © Oracle Corporation,  2001. All rights reserved.
GROUPING SETS
• GROUPING SETS 是对GROUP BY 子句的进一步扩充
• 使用GROUPING SETS 在同一个查询中定义多个分组集
• Oracle 对GROUPING SETS 子句指定的分组集进行分组后
用UNION ALL 操作将各分组结果结合起来
• Grouping set 的优点:
– 只进行一次分组即可
– 不必书写复杂的UNION语句
– GROUPING SETS 中包含的分组项越多,性能越好。
1-436 Copyright © Oracle Corporation,  2001. All rights reserved.
GROUPING SETS 应用举例
SELECT   department_id, job_id,
manager_id,avg(salary)
FROM     employees
GROUP BY GROUPING SETS
((department_id,job_id), (job_id,manager_id));
1
2
…
1-437 Copyright © Oracle Corporation,  2001. All rights reserved.
复合列
• 复合列是被作为整体处理的一组列的集合
ROLLUP (a,       , d)
• 使用括号将若干列组成复合列在ROLLUP 或CUBE 中作
为整体进行操作
• 在ROLLUP 或CUBE中,  复合列可以避免产生不必要的分
组结果
(b,c)
1-438 Copyright © Oracle Corporation,  2001. All rights reserved.
复合列应用举例
SELECT   department_id, job_id, manager_id,
SUM(salary)
FROM     employees 
GROUP BY ROLLUP( department_id,(job_id, manager_id));
2
1
3 …
1-439 Copyright © Oracle Corporation,  2001. All rights reserved.
连接分组集
• 连接分组集可以产生有用的对分组项的结合
• 将各分组集,  ROLLUP 和CUBE 用逗号连接Oracle 自
动在GROUP BY 子句中将各分组集进行连接
• 连接的结果是对各分组生成笛卡尔集
GROUP BY GROUPING SETS(a, b), GROUPING SETS(c, d)
1-440 Copyright © Oracle Corporation,  2001. All rights reserved.
…
…
连接分组集应用举例
SELECT   department_id, job_id, manager_id,
SUM(salary)
FROM     employees
GROUP BY department_id,
ROLLUP(job_id),
CUBE(manager_id);
…
…
1
2
3
4
1-441 Copyright © Oracle Corporation,  2001. All rights reserved.
总结
通过本章学习, 您已经可以:
• 使用ROLLUP 操作符
• 使用CUBE 操作符
• 使用GROUPING 函数处理在ROLLUP 或CUBE中产生的空值
• 使用GROUPING SETS 创建分组集
• 在GROUP BY 子句中组合分组:
– 复合列
– 连接分组集
1-442 Copyright © Oracle Corporation,  2001. All rights reserved.
高级子查询
1-443 Copyright © Oracle Corporation,  2001. All rights reserved.
目标
通过本章学习，您将可以:
• 书写多列子查询
• 子查询对空值的处理
• 在FROM 子句中使用子查询
• 在SQL中使用单列子查询
• 相关子查询
• 书写相关子查询
• 使用子查询更新和删除数据
• 使用EXISTS 和NOT EXISTS操作符
• 使用WITH 子句
1-444 Copyright © Oracle Corporation,  2001. All rights reserved.
子查询
子查询是嵌套在SQL语句中的另一个SELECT 语句
SELECT ...
FROM   ...
WHERE  ...
(SELECT ...
FROM   ...
WHERE  ...)
主查询
子查询
1-445 Copyright © Oracle Corporation,  2001. All rights reserved.
子查询
• 子查询(内查询)  在主查询执行之前执行
• 主查询使用子查询的结果(外查询)
SELECT select_list
FROM table
WHERE expr operator (SELECT  select_list
FROM table);
1-446 Copyright © Oracle Corporation,  2001. All rights reserved.
SELECT last_name
FROM   employees
WHERE  salary >
(SELECT salary
FROM   employees
WHERE  employee_id = 149) ;
子查询应用举例
10500
1-447 Copyright © Oracle Corporation,  2001. All rights reserved.
多列子查询
Main query
WHERE (MANAGER_ID, DEPARTMENT_ID)  IN
Subquery
100             90
102             60
124             50
主查询与子查询返回的多个列进行
比较
1-448 Copyright © Oracle Corporation,  2001. All rights reserved.
列比较
多列子查询中的比较分为两种:
• 成对比较
• 不成对比较
1-449 Copyright © Oracle Corporation,  2001. All rights reserved.
成对比较举例
SELECT employee_id, manager_id, department_id
FROM employees
WHERE  (manager_id, department_id) IN
(SELECT manager_id, department_id
FROM   employees
WHERE  employee_id IN (178,174))
AND employee_id NOT IN (178,174);
1-450 Copyright © Oracle Corporation,  2001. All rights reserved.
不成对比较举例
SELECT  employee_id, manager_id, department_id
FROM    employees
WHERE   manager_id IN
(SELECT  manager_id
FROM    employees
WHERE   employee_id IN (174,141))
AND     department_id IN
(SELECT  department_id
FROM    employees
WHERE   employee_id IN (174,141))
AND employee_id NOT IN(174,141);
1-451 Copyright © Oracle Corporation,  2001. All rights reserved.
SELECT  a.last_name, a.salary,
a.department_id, b.salavg
FROM    employees a, (SELECT   department_id,
AVG(salary) salavg
FROM     employees
GROUP BY department_id) b
WHERE   a.department_id = b.department_id
AND     a.salary > b.salavg;
在FROM 子句中使用子查询
1-452 Copyright © Oracle Corporation,  2001. All rights reserved.
单列子查询表达式
• 单列子查询表达式是在一行中只返回一列的子查询
• Oracle8i 只在下列情况下可以使用,  例如:
– SELECT 语句( FROM 和WHERE子句)
– INSERT 语句中的VALUES列表中
• Oracle9i 中单列子查询表达式可在下列情况下使用:
– DECODE 和CASE
– SELECT 中除GROUP BY 子句以外的所有子句中
1-453 Copyright © Oracle Corporation,  2001. All rights reserved.
单列子查询应用举例
在CASE 表达式中使用单列子查询
SELECT employee_id, last_name,
(CASE
WHEN department_id =
THEN 'Canada' ELSE 'USA' END) location
FROM   employees;
(SELECT department_id FROM departments
WHERE location_id = 1800)
在ORDER BY 子句中使用单列子查询
SELECT   employee_id, last_name
FROM     employees e
ORDER BY
20
(SELECT department_name
FROM departments d
WHERE e.department_id = d.department_id);
1-454 Copyright © Oracle Corporation,  2001. All rights reserved.
相关子查询
相关子查询按照一行接一行的顺序执行，主查询的每一
行都执行一次子查询
GET
从主查询中获取候选列
EXECUTE
子查询使用主查询的数据
USE
如果满足内查询的条件则返回该行
1-455 Copyright © Oracle Corporation,  2001. All rights reserved.
相关子查询
SELECT column1, column2, ...
FROM   table1
WHERE  column1 operator
(SELECT  colum1, column2
FROM     table2
WHERE    expr1=
.expr2);
子查询中使用主查询中的列
outer
outer
1-456 Copyright © Oracle Corporation,  2001. All rights reserved.
SELECT last_name, salary, department_id
FROM   employees outer
WHERE  salary >
相关子查询举例
(SELECT AVG(salary)
FROM   employees
WHERE  department_id = 
outer.department_id) ;
1-457 Copyright © Oracle Corporation,  2001. All rights reserved.
相关子查询举例
SELECT e.employee_id, last_name,e.job_id
FROM   employees e
WHERE  2 <= (SELECT COUNT(*)
FROM   job_history
WHERE  employee_id = e.employee_id);
1-458 Copyright © Oracle Corporation,  2001. All rights reserved.
EXISTS 操作符
• EXISTS 操作符检查在子查询中是否存在满足条件的行
• 如果在子查询中存在满足条件的行:
– 不在子查询中继续查找
– 条件返回TRUE
• 如果在子查询中不存在满足条件的行:
– 条件返回FALSE
– 继续在子查询中查找
1-459 Copyright © Oracle Corporation,  2001. All rights reserved.
SELECT employee_id, last_name, job_id, department_id
FROM   employees outer
WHERE  EXISTS ( SELECT 'X'
FROM   employees
WHERE  manager_id =
outer.employee_id);
EXISTS 操作符应用举例
1-460 Copyright © Oracle Corporation,  2001. All rights reserved.
SELECT department_id, department_name
FROM departments d
WHERE NOT EXISTS (SELECT 'X'
FROM   employees
WHERE  department_id
= d.department_id);
NOT EXISTS 操作符应用举例
1-461 Copyright © Oracle Corporation,  2001. All rights reserved.
相关更新
使用相关子查询依据一个表中的数据更新另一个表的数据
UPDATE table1 alias1
SET    column = (SELECT expression
FROM   table2 alias2
WHERE  alias1.column =   
alias2.column );
1-462 Copyright © Oracle Corporation,  2001. All rights reserved.
相关更新应用举例
ALTER TABLE employees
ADD(department_name VARCHAR2(14));
UPDATE employees e
SET    department_name =
(SELECT department_name
FROM   departments d
WHERE  e.department_id = d.department_id);
1-463 Copyright © Oracle Corporation,  2001. All rights reserved.
DELETE FROM table1 alias1
WHERE  column operator
(SELECT  expression
FROM   table2 alias2
WHERE  alias1.column = alias2.column);
相关删除
使用相关子查询依据一个表中的数据删除另一个表的数据
1-464 Copyright © Oracle Corporation,  2001. All rights reserved.
DELETE FROM employees E
WHERE employee_id = 
(SELECT employee_id
FROM   emp_history
WHERE  employee_id = E.employee_id);
相关删除应用举例
1-465 Copyright © Oracle Corporation,  2001. All rights reserved.
WITH 子句
• 使用WITH 子句,  可以避免在SELECT 语句中重复书写
相同的语句块
• WITH 子句将该子句中的语句块执行一次并存储到用户
的临时表空间中
• 使用WITH 子句可以提高查询效率
1-466 Copyright © Oracle Corporation,  2001. All rights reserved.
WITH 子句应用举例
WITH
dept_costs AS (
SELECT  d.department_name, SUM(e.salary) AS dept_total
FROM    employees e, departments d
WHERE   e.department_id = d.department_id
GROUP BY d.department_name),
avg_cost AS (
SELECT SUM(dept_total)/COUNT(*) AS dept_avg
FROM   dept_costs)
SELECT *
FROM   dept_costs
WHERE  dept_total >
(SELECT dept_avg
FROM avg_cost )
ORDER BY department_name;
1-467 Copyright © Oracle Corporation,  2001. All rights reserved.
总结
通过本章学习, 您已经可以:
• 使用多列子查询
• 多列子查询的成对和非成对比较
• 单列子查询
• 相关子查询
• EXISTS 和NOT EXISTS操作符
• 相关更新和相关删除
• WITH子句
1-468 Copyright © Oracle Corporation,  2001. All rights reserved.
分级查询
1-469 Copyright © Oracle Corporation,  2001. All rights reserved.
目标
通过本章学习，您将可以:
• 分级查询的概念
• 创建树形的报表
• 格式划分级数据
• 在树形结构中删除分支和节点
1-470 Copyright © Oracle Corporation,  2001. All rights reserved.
EMPLOYEES 表中的数据
1-471 Copyright © Oracle Corporation,  2001. All rights reserved.
树形结构
De Hann
King
Hunold
EMPLOYEE_ID = 100( 双亲)
MANAGER_ID = 100 ( 孩子)
Whalen
Kochhar
Higgins
Mourgos
Zlotkey
Rajs Davies Matos
Gietz
Ernst Lorentz
Hartstein
Fay
Abel Taylor Grant
Vargas
1-472 Copyright © Oracle Corporation,  2001. All rights reserved.
分级查询
WHERE条件:
expr comparison_operator expr
SELECT [LEVEL], column, expr...
FROM   table
[WHERE condition(s)]
[START WITH condition(s)]
[CONNECT BY PRIOR  condition(s)] ;
1-473 Copyright © Oracle Corporation,  2001. All rights reserved.
遍历树
始点
• 指定需要满足的条件
• 接受有效的条件
遍历EMPLOYEES 表,  以姓名为Kochhar的员工作为始点
... START WITH last_name = 'Kochhar'
START WITH column1 = value
1-474 Copyright © Oracle Corporation,  2001. All rights reserved.
遍历树
方向
从顶到底 Column1 = Parent Key
Column2 = Child Key
从底到顶 Column1 = Child Key
Column2 = Parent Key
从顶到底遍历EMPLOYEES 表
CONNECT BY PRIOR column1 = column2
... CONNECT BY PRIOR employee_id = manager_id
1-475 Copyright © Oracle Corporation,  2001. All rights reserved.
遍历树:  从底到顶
SELECT employee_id, last_name, job_id, manager_id
FROM   employees
START  WITH  employee_id = 101
CONNECT BY PRIOR manager_id = employee_id ;
1-476 Copyright © Oracle Corporation,  2001. All rights reserved.
遍历树:  从顶到底
SELECT  last_name||' reports to '||
PRIOR   last_name "Walk Top Down"
FROM    employees
START   WITH last_name = 'King'
CONNECT BY PRIOR employee_id = manager_id ;
…
1-477 Copyright © Oracle Corporation,  2001. All rights reserved.
使用LEVEL 伪列标记层次
De Hann
King
Hunold Whalen
Kochhar
Higgins
Mourgos
Zlotkey
RajsDavies Matos
Gietz
Ernst Lorentz
Hartstein
Fay
Abel Taylor Grant
Vargas
层次1
根/双亲
层次2
双亲/孩子
层次3
双亲/孩子
/叶子
层次4
叶子
1-478 Copyright © Oracle Corporation,  2001. All rights reserved.
使用LEVEL和LPAD格式化分层查询
COLUMN org_chart FORMAT A12
SELECT LPAD(last_name, LENGTH(last_name)+(LEVEL*2) -2,'_')
AS org_chart
FROM   employees
START WITH last_name='King'
CONNECT BY PRIOR employee_id=manager_id
1-479 Copyright © Oracle Corporation,  2001. All rights reserved.
修剪树枝
使用WHERE子句删除节点 使用CONNECT BY 子句删除树枝
WHERE last_name != 'Higgins' CONNECT BY PRIOR
employee_id = manager_id
AND last_name != 'Higgins'
Kochhar
Higgins
Gietz
Whalen
Kochhar
Higgins Whalen
Gietz
1-480 Copyright © Oracle Corporation,  2001. All rights reserved.
总结
通过本章学习, 您已经可以: 
• 对具有层次关系的数据创建树形报表
• 指定遍历的始点和方向
• 删除节点和树枝
1-481 Copyright © Oracle Corporation,  2001. All rights reserved.
Oracle9i 对
DML 和DDL 语句的扩展
1-482 Copyright © Oracle Corporation,  2001. All rights reserved.
目标
通过本章学习，您将可以:
• 描述多表插入的特点
• 使用不同类型的多表插入
– 无条件的INSERT
– 旋转INSERT
– 有条件的ALL INSERT
– 有条件的FIRST INSERT
• 创建和使用外部表
• 创建主键约束的同时创建索引
1-483 Copyright © Oracle Corporation,  2001. All rights reserved.
INSERT 语句
• 使用INSERT 语句向表中插入新的数据
• 使用上面的语句每次只能向表中插入一行数据
INSERT INTO table [(column [, column...])]
VALUES (value [, value...]);
INSERT INTO departments(department_id, department_name,
manager_id, location_id)
VALUES      (70, 'Public Relations', 100, 1700);
1 row created.
1-484 Copyright © Oracle Corporation,  2001. All rights reserved.
UPDATE 语句
• 使用UPDATE 语句更新表中的数据
• 使用上面的语句每次可更新表中的一行或多行数据
• 使用WHERE子句指定更新的条件
UPDATE table
SET column = value[,  column = value, ... ]
[WHERE  condition];
UPDATE employees
SET    department_id = 70
WHERE  employee_id = 142;
1 row updated.
1-485 Copyright © Oracle Corporation,  2001. All rights reserved.
多表INSERT 语句
• INSERT...SELECT是使用一个DML语句向多个表中
插入数据的一部分
• 多表INSERT 语句可作为数据仓库应用中向目标数据库
传送数据的一种方法
• 它具有更高的效率:
– 避免使用多各DML 语句
– 使用一个DML 完成IF...THEN 的逻辑处理
1-486 Copyright © Oracle Corporation,  2001. All rights reserved.
多表INSERT 语句的类型
Oracle9i 提供以下四种多表INSERT 语句类型:
• 无条件的INSERT
• 有条件的ALL INSERT
• 有条件的FIRST INSERT
• 旋转INSERT
1-487 Copyright © Oracle Corporation,  2001. All rights reserved.
多表INSERT 语句
INSERT [ALL] [conditional_insert_clause]
[insert_into_clause values_clause] (subquery)
[ALL] [FIRST]
[WHEN condition THEN] [insert_into_clause values_clause]
[ELSE] [insert_into_clause values_clause]
conditional_insert_clause
语法
1-488 Copyright © Oracle Corporation,  2001. All rights reserved.
无条件的INSERT ALL 应用举例
INSERT  ALL
INTO sal_history VALUES(EMPID,HIREDATE,SAL)
INTO mgr_history VALUES(EMPID,MGR,SAL)
SELECT employee_id EMPID, hire_date HIREDATE,
salary SAL, manager_id MGR
FROM  employees
WHERE employee_id > 200;
8 rows created.
1-489 Copyright © Oracle Corporation,  2001. All rights reserved.
有条件的INSERT ALL 应用举例
INSERT ALL
WHEN SAL > 10000 THEN
INTO sal_history VALUES(EMPID,HIREDATE,SAL)
WHEN MGR > 200   THEN
INTO mgr_history VALUES(EMPID,MGR,SAL) 
SELECT employee_id EMPID,hire_date HIREDATE, 
salary SAL, manager_id MGR
FROM   employees
WHERE  employee_id > 200;
4 rows created.
1-490 Copyright © Oracle Corporation,  2001. All rights reserved.
有条件的FIRST INSERT  应用举例
INSERT FIRST
WHEN SAL  > 25000          THEN
INTO special_sal VALUES(DEPTID, SAL)
WHEN HIREDATE like ('%00%') THEN
INTO hiredate_history_00 VALUES(DEPTID,HIREDATE)
WHEN HIREDATE like ('%99%') THEN
INTO hiredate_history_99 VALUES(DEPTID, HIREDATE)
ELSE
INTO hiredate_history VALUES(DEPTID, HIREDATE)
SELECT department_id DEPTID, SUM(salary) SAL,
MAX(hire_date) HIREDATE
FROM   employees
GROUP BY department_id;
8 rows created.
1-491 Copyright © Oracle Corporation,  2001. All rights reserved.
旋转INSERT 应用举例
INSERT ALL
INTO sales_info VALUES (employee_id,week_id,sales_MON)
INTO sales_info VALUES (employee_id,week_id,sales_TUE)
INTO sales_info VALUES (employee_id,week_id,sales_WED)
INTO sales_info VALUES (employee_id,week_id,sales_THUR)
INTO sales_info VALUES (employee_id,week_id, sales_FRI)
SELECT EMPLOYEE_ID, week_id, sales_MON, sales_TUE,
sales_WED, sales_THUR,sales_FRI
FROM sales_source_data;
5 rows created.
1-492 Copyright © Oracle Corporation,  2001. All rights reserved.
外部表
• 外部表是只读的表，其数据存储在数据库外的平面文件中
• 外部表的各种参数在CREATE TABLE语句中指定
• 使用外部表,  数据可以存储到外部文件或从外部文件中上载
数据到数据库
• 数据可以使用SQL访问,  但不能使用DML 后在外部表上创
建索引
1-493 Copyright © Oracle Corporation,  2001. All rights reserved.
创建路径
创建外部表之前应先使用CREATE DIRECTORY语句创建路径
CREATE DIRECTORY emp_dir AS '/flat_files' ;
1-494 Copyright © Oracle Corporation,  2001. All rights reserved.
创建外部表举例
CREATE TABLE oldemp (
empno NUMBER, empname CHAR(20), birthdate DATE)
ORGANIZATION EXTERNAL
(TYPE ORACLE_LOADER
DEFAULT DIRECTORY emp_dir
ACCESS PARAMETERS
(RECORDS DELIMITED BY NEWLINE
BADFILE 'bad_emp'
LOGFILE 'log_emp'
FIELDS TERMINATED BY ','
(empno CHAR,
empname CHAR,
birthdate CHAR date_format date mask "dd -mon -yyyy"))
LOCATION ('emp1.txt'))
PARALLEL 5
REJECT LIMIT 200;
Table created.
1-495 Copyright © Oracle Corporation,  2001. All rights reserved.
查询外部表
SELECT  *
FROM oldemp
emp1.txt
1-496 Copyright © Oracle Corporation,  2001. All rights reserved.
创建主键约束同时创建索引举例
CREATE TABLE NEW_EMP
(employee_id NUMBER(6)
PRIMARY KEY USING INDEX
(CREATE INDEX emp_id_idx ON
NEW_EMP(employee_id)),
first_name  VARCHAR2(20),
last_name   VARCHAR2(25));
Table created.
SELECT INDEX_NAME, TABLE_NAME
FROM   USER_INDEXES
WHERE  TABLE_NAME = 'NEW_EMP';
1-497 Copyright © Oracle Corporation,  2001. All rights reserved.
总结
通过本章学习, 您已经可以:
• 使用多表插入代替多个单独的DML 语句
• 创建外部表
• 创建主键约束同时创建索引