截图
简介
这是一个关于Oracle数据库培训PPT（部分ppt内容已做更新升级）课件，主要介绍了基本的SELECT语句、约束和排序数据、单行函数、多表显示数据、组函数合计数据、创建和管理表等内容。列选择：你能够使用SELECT语句的列选择功能选择表中的列，这些列是你想要用查询返回的。当你查询时，你能够选择你查询的表中指定的列。行选择：你能够使用SELECT语句的行选择功能选择表中的行，这些行是你想要用查询返回的。你能够使用不同的标准限制你看见的行。连接：你能够使用SELECT语句的连接功能来集合数据，这些数据被存储在不同的表中，在它们之间可以创建连接。在后面的课程中你将学到更多关于连接的内容，欢迎点击下载Oracle数据库培训PPT（部分ppt内容已做更新升级）课件哦。
Oracle数据库培训PPT课件是由红软PPT免费下载网推荐的一款培训教程PPT类型的PowerPoint.
SQL 语言基础
主要内容
基本的SELECT语句
约束和排序数据
单行函数
多表显示数据、组函数合计数据
创建和管理表
子查询&操纵数据
内置约束
创建视图
其他数据库对象
基本的select语句
目标
完成本课后， 您应当能够：
•列出SQL SELECT语句的功能
•执行基本的SELECT语句
SQL SELECT语句的功能
列选择：你能够使用SELECT语句的列选择功能选择表中的列，这些列是你想要用查询返回的。当你查询时，你能够选择你查询的表中指定的列。
行选择：你能够使用SELECT语句的行选择功能选择表中的行，这些行是你想要用查询返回的。你能够使用不同的标准限制你看见的行。
连接：你能够使用SELECT语句的连接功能来集合数据，这些数据被存储在不同的表中，在它们之间可以创建连接。在后面的课程中你将学到更多关于连接的内容。
基本SELECT语句
SELECT *| {[DISTINCT]
column|expression[alias]，...}
FROM table;
•SELECT 确定哪些列
•FROM 确定哪张表
选择所有列与指定列
SELECT * FROM departments;
用跟在SELECT关键字后面的星号 (*)，你能够显示表中数据的所有列。
SELECT department_id， location_id
FROM departments;
你能够用SELECT语句来显示表的指定列，指定列名之间用逗号分隔。
写SQL 语句
•SQL 语句对大小写不敏感
•SQL 语句可以写成一行或多行
•关键字不能简写或分开折行
•子句通常放在不同的行
•缩进用于增强可读性
算术表达式
用算术运算符创建数字和日期数据的表达式
操作 说明
+ 加
- 减
* 乘
/ 除
使用算术运算符
SELECT last_name， salary， salary + 300
FROM employees;
优先级:
•乘法和除法比加法和减法的优先级高
•相同优先级的运算符从左到右计算
•圆括号用于强制优先计算，并且使语句更 清晰
SELECT last_name， salary， 12*salary+100
FROM employees;
空值
•null 是一个未分配的、未知的，或不适用的值
•null不是0，也不是空格
•包含空值的算术表达式计算结果为空
SELECT last_name， job_id， salary，
commission_pct
FROM employees;
定义列别名
列别名：
•改变列标题的名字
•可用于计算结果
•紧跟在列名后面–在列名和别名之间可以有选项AS 关键字
•如果别名中包含有空格、或者特殊字符、或者大小写敏感，要求用双引号
SELECT last_name AS name， commission_pct comm FROM employees;
连字运算符
连字运算符：
•连接列或者字符串到其它的列
•用两个竖线表示(||)
•构造一个字符表达式的合成列
SELECT first_name||last_name AS Employees
FROM employees;
文字字符串
•文字字符串是包含在SELECT列表中的一个字符串，一个数字或者一个日期
•日期和字符的文字字符串值必须用单引号括起来
•每个文字字符串在每行输出一次
SELECT last_name||' is a '||job_id
AS "Employee Details”
FROM employees;
约束和排序数据
目标
完成本课后， 您应当能够执行下列操作：
•用一个查询限制返回的行
•用一个查询分类返回的行
限制选择的行
用WHERE子句限制返回的行
SELECT * |{[DISTINCT]
column|expression[alias]，...}
FROM table
[WHERE condition(s)];
WHERE子句跟着FROM子句
WHERE 限制查询满足条件的行
condition 由列名、表达式、常数和比较操作组成
WHERE子句能够比较列值、文字值、算术表达式或者函
数，WHERE子句由三个元素组成：
列名 ， 比较条件 ， 列名、常量或值列表 。
使用WHERE子句
SELECT employee_id， last_name， job_id，
department_id]
FROM employees
WHERE department_id = 90 ;
字符串和日期
•字符串和日期的值放在单引号中
•字符值区分大小写，日期值是格式敏感的
•日期的默认格式是DD-MON-RR.
SELECT last_name， job_id， department_id
FROM employees
WHERE last_name = 'Whalen';
比较条件
运算 含义
= 等于
> 大于
>= 大于等于
< 小于
<= 小于等于
<> 不等于
比较条件被用于一个表达式与一个值或与另一个表达式的比较。
... WHERE hire_date='01-JAN-95'
... WHERE salary>=6000
... WHERE last_name='Smith'
其它比较条件
操作 含义
BETWEEN...AND... 在两个值之间(包含)
IN(set) 匹配一个任意值列表
LIKE 匹配一个字符模板
IS NULL 是一个空值
使用BETWEEN条件:
SELECT last_name， salary
FROM employees
WHERE salary BETWEEN 2500 AND 3500;
使用IN条件
SELECT employee_id， last_name， salary， manager_id
FROM employees
WHERE manager_id IN (100， 101， 201);
其它比较条件
使用LIKE条件
•使用LIKE条件执行有效搜索串值的通配符搜索
•搜索条件既可以包含文字也可以包含数字：
–%表示任意顺序的零个或多个字符
–_表示一个字符
SELECT first_name
FROM employees
WHERE first_name LIKE 'S%';
使用NULL条件
•用IS NULL操作来测试空值
SELECT last_name， manager_id
FROM employees
WHERE manager_id IS NULL;
NULL条件，包括IS NULL条件和IS NOT NULL条件。
逻辑条件
运算 含义
AND 如果两个组成部分的条件都为真，返回TRUE
OR 如果两个组成部分中的任一个条件为真，返回TRUE
NOT 如果跟随的条件为假，返回TRUE
可以在WHERE子句中用AND和OR运算符使用多个条件。
使用AND操作：AND要求两个条件同时为真
SELECT employee_id， last_name， job_id， salary
FROM employees
WHERE salary >=10000 AND job_id LIKE '%MAN%';
逻辑条件
使用OR操作：OR操作要求两者之一为真即可
SELECT employee_id， last_name， job_id， salary
FROM employees
WHERE salary >= 10000 OR job_id LIKE '%MAN%';
使用NOT操作
SELECT last_name， job_id
FROM employees
WHERE job_id
NOT IN ('IT_PROG'， 'ST_CLERK'， 'SA_REP');
注：NOT运算符也可以用于另一个SQL运算符，例如，BETWEEN、LIKE、和NULL。
优先规则
求值顺序
1 算术运算
2 连字操作
3 比较操作
4 IS[NOT]NULL， LIKE， [NOT]IN
5 [NOT] BETWEEN
6 NOT逻辑条件
7 AND逻辑条件
8 OR逻辑条件
使用圆括号改变优先规则
ORDER BY子句
•用ORDER BY子句排序行
–ASC: 升序排序，默认
–DESC: 降序排序
•ORDER BY子句在SELECT语句的最后
SELECT last_name， job_date
FROM employees
ORDER BY hire_date ;
语法 :
SELECT expr
FROM table
[WHERE condition(s)]
[ORDER BY {column， expr} [ASC|DESC]];
单行函数
目标
完成本课后，您应当能够执行下列操作：
•描述在SQL 中可用的函数的变量类型
•在SELECT语句中使用字符，数字和日期函数
•描述转换函数的使用
SQL 函数
函数是SQL的一个非常强有力的特性，函数能够用于下面的目的：
执行数据计算
修改单个数据项
操纵输出进行行分组
格式化显示的日期和数字
转换列数据类型
SQL函数有输入参数，并且总有一个返回值。
注：在本课中讲述的大多数函数是针对SQL的Oracle版的。
SQL 函数 (续)
有两种截然不同的函数：
单行函数
多行函数
单行函数
这些函数仅对单个行进行运算，并且每行返回一个结果。有不同类型的单行函数，本课下面的函数类型：
字符
数字
日期
转换
多行函数
这些函数能够操纵成组的行，每个行组给出一个结果，这些函数也被称为组函数。多行函数在后面的课程中介绍。
单行函数
单行函数：
•操纵数据项
•接受多个参数并返回一个值
•作用于每一个返回行
•每行返回一个结果
•可以修改数据类型
•可以嵌套
•接受多个参数，参数可以是一个列或者一个表达式
单行函数（续）
单行函数的特性包括：
作用于查询中返回的每一行
每行返回一个结果
可能返回一个与参数不同类型的数据值
可能需要一个或多个参数
能够用在SELECT、WHERE和ORDER BY子句中，可以嵌套 。
function_name[(arg1， arg2，...)]
function_name 是函数的名字。
arg1， arg2 是由函数使用的任意参数，可以由一个列名或者一个表达式提供。
单行函数（续）
本课包括下面的单行函数：
字符函数：接受字符输入，可以返回字符或者数字值
数字函数：接受数字输入，返回数字值
日期函数：对DATE数据类型的值进行运算 (除了MONTHS_BETWEEN函数返回一个数字，所有日期函数都返回一个DATE数据类型的值。)
转换函数：从一个数据类型到另一个数据类型转换一个值
通用函数：
NVL 、 NVL2、 NULLIF、 COALSECE、 CASE 、 DECODE
字符函数
单行字符函数接受字符数据作为输入，既可以返回字符值也
可以返回数字值。字符函数可以被分为下面两种：
大小写处理函数
字符处理函数
大小写处理函数如下：
LOWER(column|expression) 转换字符值为小写
UPPER(column|expression) 转换字符值为大写
INITCAP(column|expression) 转换每个单词的首字母值为大写，所有
其它值为小写
字符处理函数如下：
CONCAT(column1|expression1 ，column2|expression2)
连接第一个字符值到第二个字符值；等价于连接运算符 (||)
SUBSTR(column|expression，m [，n])
从字符值中返回指定的字符，开始位置在 m，n字符长度 (如果 m 是负数，计数从字符值末尾开始；如果 n 被忽略，返回到串结束的所有字符)。
LENGTH(column|expression) 返回表达式中的字符数
INSTR(column|expression， ‘string’， [，m]， [n] )
返回一个命名串的数字位置。随意地，你可以提供一个位置m作为查找的开始，在字符串中第n次发现的位置。m和n的默认值是1，意味着在起始开始查找，并且报告第一个发现的位置。
LPAD(column|expression， n， 'string')
RPAD(column|expression， n， 'string')
填充字符值左、右调节到n字符位置的总宽度
TRIM(leading|trailing|both ， trim_character FROM trim_source)
使你能够从一个字符串修整头或尾字符(或两者)。如果trim_character或trim_source是字符文字，你必须放在单引号中。
REPLACE(text， search_string， replacement_string)
从字符串查找一个文本表达式，如果找到，用指定的值串代替它
大小写处理函数
这些函数转换字符串的大小写
函 数 结 果
LOWER('SQL Course') sql course
UPPER('SQL Course') SQL COURSE
INITCAP('SQL Course') Sql Course
LOWER：转换大小写混合的字符串为小写字符串
UPPER：转换大小写混合的字符串为大写字符串
INITCAP：将每个单词的首字母转换为大写，其他字母为小写
SELECT ‘The job id for ’||UPPER(last_name)||‘ is ’
||LOWER(job_id) AS "EMPLOYEE DETAILS"
FROM employees;
字符处理函数
函 数 结 果
CONCAT('Hello'， 'World') HelloWorld
SUBSTR('HelloWorld'，1，5) Hello
LENGTH('HelloWorld') 10
INSTR('HelloWorld'， 'W') 6
LPAD(salary， 10，'*') *****24000
RPAD(salary， 10， '*') 24000 *****
TRIM('H' FROM 'HelloWorld') elloWorld
数字函数
ROUND:四舍五入指定小数的值
ROUND(45.926， 2) 45.93
TRUNC(45.926， 2) 45.92
MOD(1600， 300) 100
ROUND(column|expression， n)
四舍五入列、表达式或值为n位小数位，或者，如果n被忽略，无小数位。(如果n是负值，小数点左边的数被四舍五入)
TRUNC(column|expression，n)
截断列、表达式或值到n位小数，或者，如果n被忽略，那么n默认为0
MOD(m，n) 返回m除以n的余数
使用ROUND函数
SELECT ROUND(45.923，2)， ROUND(45.923，0)，ROUND(45.923，-1) FROM DUAL;
使用TRUNC函数
SELECT TRUNC(45.923，2)， TRUNC(45.923)，TRUNC(45.923，-2) FROM DUAL;
使用MOD函数
SELECT last_name， salary， MOD(salary， 5000) FROM employees WHERE job_id = 'SA_REP';
注：MOD函数经常用于确定一个值是奇数还是偶数
日期的使用
•Oracle 数据库用内部数字格式存储日期：世纪，年，月，日，小时，分钟和秒
•默认日期显示格式是DD-MON-RR.
SELECT last_name， hire_date
FROM employees
WHERE last_name like ''G%';';
SYSDATE函数返回：
•Date
•Time
SYSDATE是一个日期函数，它返回当前数据库服务器的日期和时间。
SELECT SYSDATE FROM DUAL;
用日期计算
•从日期加或者减一个数，结果是一个日期值
•两个日期相减，得到两个日期之间的天数
•用小时数除以24，可以加小时到日期上
既然数据库以数字方式存储日期，你就可以用算术运算符进行计算，例如，加或减。你可以加或减数字常数以及日期。
你可以进行下面的运算：
运算 结果 说明
date + number 日期 加一个天数到一个日期上
date - number 日期 从一个日期上减一个天数
date - date 天数 用一个日期减另一个日期
date + number/24 日期 加一个小时数到一个日期上
日期函数
函 数 说 明
MONTHS_BETWEEN 两个日期之间的月数
ADD_MONTHS 加日历月到日期
NEXT_DAY 下个星期几是几号
LAST_DAY 指定月的最后一天
ROUND 四舍五入日期
TRUNC 截断日期
日期函数（续）
MONTHS_BETWEEN(date1， date2)：计算date1和date2之间的月数，其结果可以是正的也可以是负的。如果date1大于date2，结果是正的，反之，结果是负的。结果的小数部分表示月的一部分。
ADD_MONTHS(date， n)：添加n个日历月到date。n的值必须是整数，但可以是负的。
NEXT_DAY(date， ‘char’)：计算在date之后的下一个周(‘char’)指定天的日期。char的值可能是一个表示一天的数或者是一个字符串。
LAST_DAY(date)：计算包含date的月的最后一天的日期
ROUND(date[，‘fmt’])：返回用格式化模式fmt四舍五入到指定单位的 date ，如果格式模式 fmt 被忽略，date被四舍五入到最近的天。
TRUNC(date[， ‘fmt’])：返回用格式化模式fmt截断到指定单位的带天的时间部分的date，如果格式模式fmt被忽略，date被截断到最近的天。
使用日期函数
•MONTHS_BETWEEN (‘01-SEP-95’，‘11-JAN-94’)
19.6774194
•ADD_MONTHS (‘11-JAN-94’，6)
'11-JUL-94'
•NEXT_DAY (‘01-SEP-95’，‘2’)
下个星期五是几号 '08-SEP-95'
•LAST_DAY('01-FEB-95')
'28-FEB-95'
使用日期函数（续）
假定SYSDATE = '25-JUL-95':
•ROUND(SYSDATE，'MONTH') 01-AUG-95
•ROUND(SYSDATE ，'YEAR') 01-JAN-96
•TRUNC(SYSDATE ，'MONTH') 01-JUL-95
•TRUNC(SYSDATE ，'YEAR') 01-JAN-95
•TRUNC(TO_DATE('25-JUL-95') ，'YEAR')
01-JAN-95
转换函数
数据类型转换
•隐式数据类型转换
•显式数据类型转换
对于直接赋值，Oracle 服务器能够自动地进行下面
的转换：
从 到
VARCHAR2 or CHAR NUMBER
VARCHAR2 or CHAR DATE
NUMBER VARCHAR2
DATE VARCHAR2
对于表达式赋值，Oracle 服务器能自动地进行下面的转换：
从 VARCHAR2 or CHAR 到 DATE
从 VARCHAR2 or CHAR 到 NUMBER
转换函数(续）
显式数据类型转换
SQL 提供三种函数来从一种数据类型转换值到另一种：
TO_CHAR(number|date，[ fmt]， [nlsparams])
转换一个数字或日期值为一个VARCHAR2字符串，带格式
化样式fmt。
数字转换：nlsparams 参数指定下面的字符，它由数字格式
化元素返回：
小数字符 99999.99
前导0 09999
本地货币符号 L9999
国际货币符号 $9999
如果忽略nlsparams或其它参数，该函数在会话中使用默认参数值。
TO_CHAR(number|date，[ fmt]， [nlsparams])
指定返回的月和日名字及其缩写的语言。如果忽略该参数，
该函数在会话中使用默认日期语言 。
TO_NUMBER(char，[fmt]， [nlsparams])
用由可选格式化样式fmt指定的格式转换包含数字的字符串为
一个数字。Nlsparams参数在该函数中的目的与TO_CHAR
函数用于数字转换的目的相同 。
TO_DATE(char，[fmt]，[nlsparams])
按照fmt指定的格式转换表示日期的字符串为日期值。如果忽
略fmt，格式是 DD-MON-YY。Nlsparams参数的目的与
TO_CHAR函数用于日期转换时的目的相同。
对日期使用TO_CHAR函数
TO_CHAR(date， 'format_model')
格式模板
•必须加单引号，并且区分大小写
•能够包含任一有效的日期格式元素
•有一个fm元素用来删除填补的空，或者前导零
•用一个逗号与日期值分开
SELECT employee_id，
TO_CHAR(hire_date， 'MM/YY') Month_Hired
FROM employees
WHERE last_name = 'Higgins';
日期格式模板的元素
YYYY 数字全写年
YEAR 年的拼写
MM 月的两数字值
MONTH 月的全名
DY 周中天的三字母缩写
DAY 周中天的全名
MON 月的三字母缩写
DD 月的数字天
使用TO_NUMBER和TO_DATE函数
•转换字符串到数字，用TO_NUMBER函数格式化：
TO_NUMBER(char[， 'format_model'])
•转换字符串到日期，用TO_DATE函数格式化：
TO_DATE(char[， 'format_model'])
Select to_number(‘12345’)
from dual;
Select to_date(‘20000810’，’yyyy-mm-dd’
fromdual;
通用函数
这些函数可用于任意数据类型，并且适用于空值
•NVL (expr1， expr2)
•NVL2 (expr1， expr2， expr3)
•NULLIF (expr1， expr2)
•COALESCE (expr1， expr2， ...， exprn)
NVL 转换空值为一个实际值
NVL2 如果expr1非空，NVL2返回expr2；如果expr1为空
，NVL2返回expr3。参数expr1可以是任意数据类型
NULLIF 比较两个表达式，如果相等返回空；如果不相等
，返回第一个表达式
COALESCE 返回表达式列表中的第一个非空表达式
NVL函数
转换一个空值到一个实际的值
•可用的数据类型可以是日期、字符和数字
•数据类型必须匹配：
–NVL(commission_pct，0)
–NVL(hire_date，'01-JAN-97')
–NVL(job_id，'No Job Yet')
语法:
NVL (expr1， expr2)
在语法中：
expr1 是包含空值的源值或者表达式
expr2 是用于转换空值的目的值
Select nvl(‘1’，’not null’ from dual;
注:如果expr1为空则返回expr2r的值
使用NVL2函数
NVL2 函数检查第一个表达式，如果第一个表达式不为空，
那么 NVL2 函数返回第二个表达式；如果第一个表达式为
空，那么第三个表达式被返回。
expr2 expr1 非空时的返回值
语法
NVL2(expr1， expr2， expr3)
在语法中：
expr1 是可能包含空的源值或表达式
expr3 expr1 为空时的返回值
Select NVL2(‘1’，’not null’，’null’) from dual;
Select NVL2(‘1’，’not null’，’null’) from dual;
使用NULLIF函数
NULLIF 函数比较两个表达式，如果相等，函数返回空，如果不相等，
函数返回第一个表达式。第一个表达式不能为 NULL。
语法
NULLIF (expr1， expr2)
在语法中：
expr1 是对于 expr2 的被比较原值
expr2 是对于 expr1 的被比较原值。(如果它不等于 expr1，
expr1 被返回)。
Select nullif(‘abc’，’abcd’) from dual;
使用COALESCE函数
• COALESCE函数超过NVL函数的优点是COALESCE函数
能够接受多个交替的值。
•如果第一个表达式非空，它返回该表达式；否则，它做一个
保留表达式的结合 。
COALESCE 函数返回列表中的第一个非空表达式。
语法
COALESCE (expr1， expr2， ... exprn)
在语法中：
expr1 如果它非空，返回该表达式
expr2 如果第一个表达式为空并且该表达式非空，返回该表达式
exprn 如果前面的表达式都为空，返回该表达式
Select coalesce(‘ '，‘ '，'bca') from dual;
条件表达式
在SQL 语句中提供IF-THEN-ELSE 逻辑的使用。
两种用法：
–CASE表达式
–DECODE函数
CASE表达式
CASE expr WHEN comparison_expr1 THEN return_expr1
WHEN comparison_expr2 THEN return_expr2
WHEN comparison_exprn THEN return_exprn
ELSE else_expr]
END
DECODE函数
DECODE(col|expression， search1， result1
[， search2， result2，...，][， default])
DECODE 函数在比较表达式 (expression) 和每个查找
(search) 值后解码表达式，如果表达式与查找相同，返
回结果。
如果省略默认值，当没有查找值与表达式相匹配时返回
一个空值。
多表显示数据&组函数合计数据
目标
完成本课后， 您应当能够执行下列操作：
•写SELECT 语句使用等值和非等值连接
从多个表中访问数据
•使用外连接查看不满足连接条件的数据
•识别可用的组函数
•描述组函数的使用
•用GROUP BY 子句分组数据
•用HAVING 子句包含或排除分组的行
笛卡尔乘积
•笛卡尔乘积的形成，当：
–一个连接条件被遗漏时
–一个连接条件不正确时
–在第一个表中的所有行被连接到第二个表的所有行时
•为了避免笛卡尔乘积的形成，在WHERE 子句中应当总是包
含正确的连接条件 。
用Oracle 语法连接表
使用一个连接从多个表中查询数据
SELECT table1.column， table2.column
FROM table1， table2
WHERE table1.column1 =table2.column2;
•在WHERE 子句中写连接条件
•当多个表中有相同的列名时，将表名作为列名的前缀定义连接
•当数据从多表中查询时，要使用连接 (join) 条件。一个表中
的行按照存在于相应列中的公值被连接到另一个表中的行，
即，通常所说的主键和外键列。
什么是等值连接?
EMPLOYEES DEPARTMENTS
用等值连接返回记录
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;
使用表别名
•使用表别名简化查询
•使用表别名改善性能
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;
原则
表别名最多可以有 30 个字符，但短一些更好。
如果在 FROM 子句中表别名被用于指定的表，那么在整个 SELECT 语句中都要使用表别名。
表别名应该是有意义的。
表别名只对当前的 SELECT 语句有效。
多于两个表的连接
EMPLOYEES DEPARTMENTS LOCATIONS
非等值连接
EMPLOYEES JOB_GRADES
用非等值连接返回记录
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;
外连接
DEPARTMENTS EMPLOYEES
外连接语法
•你可以用一个外连接查看那些不满足连接条件的行
•外连接运算符是加号(+)
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(+);
•用外连接返回不直接匹配的记录
•如果在连接条件中使用外连接操作，缺少的行就可以被返
回。操作符是一个在圆括号中的加号 (+)，它被放置在连接
的缺少信息的一侧。为了使来自不完善表的一行或多行能够
被连接，该操作符有产生一个或多个空行的作用。
使用外连接
SELECT e.last_name， e.department_id， d.department_name
FROM employees e， departments d
WHERE e.department_id (+) = d.department_id ;
什么是组函数?
组函数操作行集，给出每组的结果
EMPLOYEES
组函数的类型
•AVG 平均值
•COUNT 计数
•MAX 最大值
•MIN 最小值
•STDDEV 标准差
•SUM 合计
•VARIANCE 方差
组函数 (续)
每个函数接收一个参数，下面的表确定你可以在语法中使用的选项：
函 数 说 明
AVG([DISTINCT|ALL]n) n 的平均值，忽略空值
COUNT({*|[DISTINCT|ALL]expr}) 用 * 计数所有行，包括重复和带空值
的行。expr 求除了空计算
MAX([DISTINCT|ALL]expr) expr的最大值，忽略空值
MIN([DISTINCT|ALL]expr) expr的最小值，忽略空值
STDDEV([DISTINCT|ALL]x) n 的标准差，忽略空值
SUM([DISTINCT|ALL]n) 合计 n 的值，忽略空值
VARIANCE([DISTINCT|ALL]x) n 的方差，忽略空值
组函数的语法
SELECT[column，] group_function(column)， ...
FROM table
[WHERE condition]
[GROUP BYcolumn]
[ORDER BYcolumn];
使用组函数的原则
DISTINCT 使得函数只考虑不重复的值；ALL 使得函数考虑每个值，包括重复值。默认值是 ALL ，因此不需要指定。
用于函数的参数的数据类型可以是 CHAR、VARCHAR2、NUMBER 或 DATE。
所有组函数忽略空值。为了用一个值代替空值，用 NVL、NVL2 或 COALESCE 函数。
当使用 GROUP BY 子句时，Oracle 服务器隐式以升序排序结果集。为了覆盖该默认顺序，DESC 可以被用于 ORDER BY 子句。
使用AVG 、SUM、MIN、MAX 函数
你可以使用AVG 和SUM 用于数字数据
SELECT AVG(salary)， MAX(salary)，
MIN(salary)， SUM(salary)
FROM employees
WHERE job_id LIKE '%REP%';
使用COUNT 函数
COUNT(*) 返回一个表中的行数
COUNT 函数有三中格式：
COUNT(*)
COUNT(expr)
COUNT(DISTINCT expr)
COUNT(*) 返回表中满足 SELECT 语句标准的行数，包括重复行，包括有空值列的行。如果 WHERE 子句包括在 SELECT 语句中，COUNT(*) 返回满足 WHERE 子句条件的行数。
COUNT(expr) 返回在列中的由 expr 指定的非空值的数。
COUNT(DISTINCT expr) 返回在列中的由 expr 指定的唯一的非空值的数。
注：expr为列名
组函数和Null 值
所有组函数忽略列中的空值。在幻灯片的例子中，
平均值只基于表中的那些 COMMISSION_PCT 列
的值有效的行的计算。平均值计算是用付给所有雇
员的总佣金除以接受佣金的雇员数 (4)。
SELECT AVG(commission_pct)
FROM employees;
在组函数中使用NVL 函数
NVL 函数强制组函数包括空值。在幻灯片的例子中，平均值
被基于所有表中的行来计算，不管 COMMISSION_PCT 列
是否为空。平均值的计算是用付给所有雇员的总佣金除以公
司的雇员总数 (20)。
SELECT AVG(NVL(commission_pct， 0))
FROM employees;
创建数据组:GROUP BY 子句语法
用GROUP BY 子句划分表中的行到较小的组中
SELECT column， group_function(column)
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[ORDER BY column];
在语法中， group_by_expression 指定那些用于将行分组的
列，这些列的值作为行分组的依据。
使用 WHERE 子句，你可以在划分行成组以前过滤行。
在 GROUP BY 子句中必须包含列。
在 GROUP BY 子句中你不能用列别名。
默认情况下，行以包含在 GROUP BY 列表中的字段的升序排序。你可以用 ORDER BY 子句覆盖这个默认值。
如果在 SELECT 子句中包含了组函数，就不能选择单独的结果，除非单独的列出现在 GROUP BY 子句中。如果你未能在 GROUP BY 子句中包含一个字段列表，你会收到一个错误信息。
约束分组结果: HAVING 子句
用HAVING 子句约束分组：
1.行被分组
2.应用组函数
3.匹配HAVING 子句的组被显示
SELECT column， group_function
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[HAVING group_condition]
[ORDER BY column];
使用HAVING 子句
SELECT department_id， MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary)>10000 ;
嵌套组函数
显示最大平均薪水
SELECT MAX(AVG(salary))
FROM employees
GROUP BY department_id;
创建和管理表
目标
完成本课后， 您应当能够执行下列操作：
•描述主要数据库对象
•创建表
•描述列定义时可用的数据类型
•改变表的定义
•删除、改名和截断表
数据库对象
表 基本存储单元， 由行和列组成
视图 逻辑地从一个或多个表中表示数据子集
序列 数字值发生器
索引 改善一些查询的性能
同义词 给对象可选择的名字
命名规则
表命名和列命名：
•必须以字母开始
•必须是1–30 个字符长度
•只能包含A–Z， a–z， 0–9， _， $， 和#
•同一个用户所拥有的对象之间不能重名
•不能用Oracle 服务器的保留字
注：名字是大小写不敏感的，例如， EMPLOYEES 与
eMPloyees 或 eMpLOYEES 作为同一个名字来处理。
CREATE TABLE语句
•用户必须有：
–CREATE TABLE权限
–一个存储区域
CREATE TABLE [schema.]table
(column datatype[DEFAULT expr][， ...]);
•必须指定：
–表名
–列名、列数据类型和列的大小
schema 与所有者的名字一样
table 表的名字
DEFAULT expr 指定默认值
column 列的名字
datatype 列的数据类型和长度
引用另一个用户的表
•表属于另一个用户，不在该用户的方案中
•在那些表名字的前面使用所有者的名字作为
前缀
如果一个表不属于本用户，那么，其所有者的名字
必须放在表名的前面
SELECT *
FROM user_b.employees;
创建表
•创建表
CREATE TABLE dept(
deptno NUMBER(2)，
dname VARCHAR2(14)，
loc VARCHAR2(13));
•确认表的创建
DESCRIBE dept
Oracle 数据库中的表
•用户表：
–由用户创建和维护的表的集合
–包含用户信息
•数据字典：
–由Oracle 服务器创建和维护的表的集合
–包含数据库信息
有四种数据字典视图，每一种有一个特定的前缀来反映其不
同的目的。
USER_ 这些视图包含关于用户所拥有的对象的信息。
ALL_ 这些视图包含所有用户可访问的表 (对象表和相关的表) 的信息。
DBA_ 这些视图是受限制的视图，它们只能被分配有 DBA 角色的用户所
访问。
V$ 这些视图是动态执行的视图，包含数据库服务器的性能、存储器
和锁的信息。
查询数据字典
•查看本用户所拥有的表的名称
SELECT table_name
FROM user_tables ;
•查看本用户所拥有的不同的对象类型
SELECT DISTINCT object_type
FROM user_objects ;
•查看本用户所拥有的表、视图、同义词和序列
SELECT * FROM user_catalog ;
数据类型
数据类型 说 明
VARCHAR2(size) 可变长度的字符数据
CHAR(size) 固定长度的字符数据
NUMBER(p，s) 可变长度的数字数据
DATE 日期和时间值
LONG 最大2G的可变长度字符数据
CLOB 最大4G的字符数据
RAW and LONG RAW 原始二进制数据
BLOB 最大4G的二进制数据
BFILE 最大4G的，存储在外部文件中的二
进制数据
ROWID 一个64进制的数制系统，表示表中
一行的唯一地址
用子查询创建表
该方法既可以创建表还可以将从子查询返回的行插入新创建
的表中。
CREATE TABLE dept80 AS
SELECT employee_id， last_name， salary*12
ANNSAL， hire_date
FROM employees
WHERE department_id = 80;
原则
被创建的表要带指定的列名，并且由SELECT语句返回的行被插入到新表中。
字段的定义只能包括列名和默认值。
如果给出了指定的列，列的数目必须等于子查询的SELECT列表的列数目。
如果没有给出了指定的列，表的列名应和子查询中的列名是相同的。
完整性规则不会被传递到新表中，仅列的数据类型被定义。
ALTER TABLE语句
用ALTERTABLE语句来：
•添加一个新列
•修改一个已存在的列
•为新列定义一个默认值
•删除一个列
添加列
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);
添加新列
•用ADD字句添加列
ALTER TABLE dept80 ADD (job_id VARCHAR2(9));
添加新列的原则
你可以添加或修改列。
你不能指定新添加的列的位置，新列将成为最后一列。
修改列
•可以改变列的数据类型、大小和默认值
ALTER TABLE dept80 MODIFY(last_name VARCHAR2(30));
•对默认值的改变只影响后来插入表中的数据
原则
你可以增加宽度或一个数字列的精度。
你可以增加数字列或字符列的宽度。
你可以减少一个列的宽度，但仅在列中只包含空值或表中没有行时。
你可以改变数据类型，但仅在列中只包含空值时。
你可以转换一个CHAR列到VARCHAR2数据类型或转换一个VARCHAR2列到 CHAR 数据类型仅当列中只包含空值时，或者你不改变列的大小时。
对默认值的改变仅影响以后插入的列。
删除列
用DROP COLUMN子句从表中删除列
ALTER TABLE dept80
DROP COLUMN job_id;
原则
列可以有也可以没有数据。
用ALTER TABLE语句，一次只能有一列被删除。
表被修改后必须至少保留一列。
一旦一列被删除，它不能再恢复。
删除表
•在表中的所有数据和结构都被删除
•任何未决的事务都被提交
•所有的索引被删除
•你不能回退DROP TABLE语句
DROP TABLE dept80;
DROP TABLE语句删除Oracle表定义，当你删除一个表时，
数据库丢失表中所有的数据，并且所有与其相关的索引也被
删除。
改变一个对象的名字
•执行RENAME语句，改变一个表、视图、序列或
同义词
RENAME dept TO detail_dept;
注:你必须是对象的所有者
截断表
•TRUNCATE TABLE语句：
–删除表中所有的行
–释放该表所使用的存储空间
TRUNCATE TABLE detail_dept;
•不能回退用TRUNCATE删除的行
•作为选择，可以用DELETE语句删除行
子查询&操纵数据
目标
完成本课后， 您应当能够执行下列操作：
•描述子查询能够解决的问题类型
•定义子查询
•列出子查询的类型
•写单行和多行子查询
•描述每个DML 语句
•插入行到表中
•更新表中的行
•从表中删除行
•控制事务
用子查询解决问题
用子查询解决问题
假想你想要写一个查询来找出挣钱比 Abel 的薪水还多的人。为了解决这个问题，你需要两个查询：一个找出 Abel 的收入，第二个查询找出收入高于 Abel 的人。
你可以用组合两个查询的方法解决这个问题，放置一个查询到另一个查询中。
内查询或子查询返回一个值给外查询或主查询。使用一个子查询相当于执行两个连续查询并且用第一个查询的结果作为第二个查询的搜索值。
子查询语法
SELECT select_list
FROM table
WHERE exproperator
(SELECTselect_list FROM table);
•子查询(内查询) 在主查询之前执行一次
•子查询的结果被用于主查询(外查询)
子查询可以被放在 CREATE VIEW 语句中、CREATE
TABLE 语句、UPDATE 语句、INSERT 语句的 INTO 子句
和 UPDATE 语句的 SET 子句中。
使用子查询
SELECT last_name
FROM employees
WHERE salary >
(SELECT salary
FROM employees
WHERE last_name = 'Abel');
使用子查询的原则
•子查询放在圆括号中
•将子查询放在比较条件的右边
•在子查询中的ORDER BY 子句一般不需要。
•在单行子查询中用单行运算符，在多行子查询中用
多行运算符 。
子查询的类型
•单行子查询：从内 SELECT 语句只返回一行的查询
•多行子查询：从内 SELECT 语句返回多行的查询
单行子查询
•仅返回一行
•使用单行比较符
运算符 含义
= 等于
> 大于
>= 大于或等于
< 小于
<= 小于或等于
<> 不等于
执行单行子查询
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);
多行子查询
•返回多于一行
•使用多行比较符
操作 含义
IN 等于列表中的任何成员
ANY 比较子查询返回的每个值
ALL 比较子查询返回的全部值
在多行子查询中使用ANY 运算符
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';
在多行子查询中使用ALL 运算符
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';
数据操纵语言
•数据操纵语言 (Data manipulation language DML)
是SQL的一个核心部分
•当你做下面操作时，DML 语句被执行：
–添加新行到表中
–修改表中的行
–删除表中的行
•事务由DML 语句的集合组成，它组成工作的逻辑
单元
INSERT语句语法
•使用INSERT语句添加新行到表中
INSERT INTO table [(column [， column...])]
VALUES(value [， value...]);
在语法中：
table 是表的名字
column 是表中的列名
value 是列的相应值
•用该语法一次只能插入一行
插入新行
•插入一个包含每一个列值的新行
•值以表中列的默认顺序列表
•在INSERT子句中字段可以随意列表
INSERT INTO departments(department_id， department_name，
manager_id， location_id)
VALUES (70， 'Public Relations'， 100， 1700);
•字符和日期要用单引号括起来
插入带空值的行
•隐式方法: 省略字段列表中的列
INSERT INTO departments (department_id，
epartment_name )
VALUES(30， 'Purchasing');
•显式方法: 在VALUES子句中指定NULL关键字
INSERT INTO departments
VALUES(100， 'Finance'， NULL， NULL);
使用替换变量
•在SQL 语句中用&替换变量提示用户输入值
•&是一个用于变量值的占位符
INSERT INTO departments (department_id，
department_name， location_id)
VALUES (&department_id， '&department_name'，&location);
从另一个表中复制行
•用一个子查询写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%';
•不用VALUES子句
•在子查询中列的数目要匹配INSERT子句中列的数目
在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);
改变表中的数据
•用UPDATE语句修改已存在的行
UPDATE table
SET column= value[， column = value， ...]
[WHERE condition];
table 是表的名字
column 是表中列的名字
value 是相应的值或对应列的子查询
condition 确定要被更新的行，由列名、表达式、常数和比较
操作符组成 。用查询表来显示受更新的行以确认更新操作。
•如果需要，一次更新多行
更新表中的行
•如果使用了WHERE子句，指定的一行或多行将被
修改
UPDATE employees
SET department_id = 70
WHERE employee_id = 113;
•如果遗漏WHERE子句，表中所有的行都会被修改
UPDATE copy_emp
SET department_id = 110;
用子查询更新两列
更新雇员114 的工作和薪水，使其和雇员205 相同
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;
更新基于另一个表的行
在UPDATE语句中用子查询来更新基于另一个表中值的那些
行 。
UPDATE copy_emp
SET department_id = (SELECT department_id
FROM employees
WHERE employee_id = 100)
WHERE job_id = any(SELECT job_id
FROM employees
WHERE employee_id = 200);
从表中删除行
使用DELETE语句从表中删除已存在的行
•如果指定了WHERE子句，则指定的行被删除
DELETE FROM departments
WHERE department_name = 'Finance';
•如果遗漏了WHERE子句，表中所有的行都被删除
DELETE FROM copy_emp;
注：删除所有行后，表的数据结构被保留。
建议的方法是：
Truncate table copy_emp;
删除基于另一个表的行
在DELETE语句中用子查询来删除表中的基于另一个表中值
的行。
DELETE FROM employees
WHERE department_id =any(SELECT department_id
FROM departments
WHERE department_name
LIKE '%Public%');
MERGE语句
•提供有条件地更新和插入数据到数据库表中的能力
•如果行存在，执行UPDATE；如果是一个新行，执
行INSERT：
–避免分散更新
–增进性能和易用性
–在数据仓库应用中有用
MERGE语句的语法
你能够用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);
MERGE语句的语法（续）
• INTO子句 指定你正在更新或插入的目的表
• USING子句 指定数据源要被更新或插入的数据的源；可以
是一 个表、视图或者子查询。
• ON 子句 是一个条件，在此条件上MERGE操作即可以更新
也可以插入
• WHEN MATCHED | 通知服务器怎样响应连接条件的结果
MERGE语句的例子
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.email = e.email，
c.phone_number = e.phone_number，
c.hire_date = e.hire_date， c.job_id = e.job_id，
c.salary = e.salary， c.commission_pct = e.commission_pct，
c.manager_id = e.manager_id，
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);
数据库事务处理
Oracle服务器基于事务处理确保数据的一致性。在
改变数据时，事务给你更多的灵活性和可控性，如
果用户程序失败或者系统失败，事务可以确保数据
的一致性。
数据库事务处理由下面的语句组成：
•（数据操纵语言）DML语句，对数据进行永久的改变
•（数据定义语言）DDL 语句
•（数据控制语言）DCL 语句
数据库事务处理（续）
•执行第一个DML SQL 语句时开始
•遇到下面事件之一结束：
–一个COMMIT或ROLLBACK语句被发布
–一个DDL 或DCL 语句执行(自动提交)
–用户退出 iSQL*Plus
–系统崩溃
COMMIT和ROLLBACK语句优点
用COMMIT和ROLLBACK语句，你能够：
•确保数据的一致性
•在数据永久改变之前进行预览
•分组逻辑相关的操作
控制事务
你能够用COMMIT、SAVEPOINT和ROLLBACK语句控制事
务逻辑。
COMMIT 结束当前事务，使得所有未决的数据永久改变。
SAVEPOINT name 在当前事务中标记保存点。
ROLLBACK 结束当前事务，丢弃所有未决的数据改变。
ROLLBACK TO SAVEPOINT name
回滚当前事务到指定的保存点，从而丢弃保存点创建后的任何改变。如果忽略了 TO SAVEPOINT 子句，ROLLBACK 语句回滚整个事务。由于保存点是逻辑的，因此，没有办法列出已经创建的保存点。
回退改变到一个标记
•用SAVEPOINT语句在当前事务中创建一个标记
•用ROLLBACK TO SAVEPOINT语句回退到该标记
UPDATE...SAVEPOINT update_done;
Savepoint created.
INSERT...ROLLBACK TO update_done;
Rollback complete.
隐式事务处理
•在下面的情况下，一个自动提交发生：
–DDL 语句被发送
–DCL 语句被发送
–正常退出iSQL*Plus，没有明确地发送COMMIT或ROLLBACK语句
•当iSQL*Plus 非正常退出时，或者发生系统故障
时，一个自动回退发生
COMMIT或ROLLBACK之前数据的状态
•以前的数据状态能够被恢复
•当前用户能用SELECT语句查看DML 操作的结果
•其他用户不能观察当前用户DML 语句的结果
•受影响的行被锁定，其他用户不能改变受影响的行
中数据 。
在COMMIT之后数据的状态
•数据在数据库中被永久地改变
•数据的以前状态被永久地丢失
•所有用户都能观察该结果
•受影响行的锁定被释放，其它用户可以操纵那些行
•所有保存点被擦除
提交改变
•产生改变
DELETE FROM employees
WHERE employee_id = 99999;
INSERT INTO departments
VALUES (290， 'Corporate Tax'， NULL， 1700);
•提交改变
COMMIT;
ROLLBACK 后的数据状态
用ROLLBACK语句丢弃所有未决的改变：
•数据的改变被撤消
•数据的以前状态被恢复
•受影响行的锁定被释放
DELETE FROM copy_emp;
ROLLBACK;
读一致性
目的是确保每个用户看到的数据和他最后一次提交，并
且在一个DML操作开始之前的数据一样。
•读一致性在所有时间保证对数据的一致的观察
•一个用户所做的改变不与另一个用户所做的改变冲突
•读一致性确保下面的操作有同样的数据：
–读者不等待写者
–写者不等待读者
•数据库用户用两种方法访问数据库：
读操作 (SELECT 语句)
写操作 (插入、更新、删除 语句)
锁定
锁是防止访问相同资源的事务之间的破坏性交互的
机制。既可以是用户对象 (例如表或行)，也可以是
对用户不可见的系统对象 (例如共享数据结构和数据
字典行)。
在Oracle 数据库中，锁：
•在并发事务之间防止破坏性的交互作用
•不需要用户的动作
•自动使用最低的限制级别
•在事务处理期间保持
•有两种类型：显式锁定和隐式锁定
隐式锁定
•两种锁模式：
–独占锁：不允许其他用户访问
–共享所：允许其他用户访问
•高级数据并发操作：
–DML: 表共享，行独占–查询: 不需要锁
–DDL: 保护定义对象
•锁保持直到commit 或rollback
内置约束
目标
完成本课后， 您应当能够执行下列操作：
•描述约束
•创建和维护约束
什么是约束?
•约束强制规则在表级
•如果有从属关系，约束防止表的删除
•下面的约束类型是有效的：
–NOT NULL
–UNIQUE
–PRIMARY KEY
–FOREIGN KEY
–CHECK
• Oracle服务器用约束 (constraints) 来防止无效数据输入到表中。
•你可以使用约束做下面的事：
在插入、更新行或者从表中删除行的时候强制表中的数据
遵循规则。对于成功的操作，约束必须被满足 。
如果表之间有依赖关系，防止表的删除 。
数据一致性约束
约 束 说 明
NOT NULL 指定列不能包含空值
UNIQUE 指定列的值或者列的组合的值对于表中
所有的行必须是唯一的
PRIMARY KEY 表的每行的唯一性标识
FOREIGN KEY 在列和引用表的一个列之间建立并且强
制一个外键关系
CHECK 指定一个必须为真的条件
约束原则
•命名一个约束，或者由Oracle 用SYS_Cn格式产生
一个名字
•创建一个约束：
–在创建表的同时
–在创建表之后
•在列或者表级定义一个约束
•在数据字典中查看约束
定义约束
CREATE TABLE [schema.]table
(columndatatype[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));
在语法中：
schema 与所有者同名
table 表的名字
DEFAULT expr 指定一个默认值。如果在插入语句中省略了一个值，在省略处使用该默认值
column 列的名字
datatype 列的数据类型和长度
column_constraint 是一个作为列定义一部分的完整性约束
table_constraint 是一个作为表定义一部分的完整性约束
定义约束（续）
•列级约束：
只涉及一个单个的列，对于该列用规范定义；能够定义完整
性约束的任何类型
column[CONSTRAINT constraint_name]
constraint_type，
•表级约束
涉及一个或多个列，表中的列被分别定义；除了 NOT NULL，
能够定义任意约束
column，...[CONSTRAINT constraint_name]
constraint_type(column， ...)，
NOT NULL约束
确保某些列不允许空值：定义在列级
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
);
UNIQUE约束
UNIQUE键完整性约束，要求列或者列的组合中 (键) 的每个
值是唯一的，既在表中指定的列或列组合中不能有两行有
相同的值。定义UNIQUE键约束的列 (或列组合) 被称为唯一
键 (unique key)。
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));
PRIMARYKEY约束
PRIMARY KEY约束为表创建一个主键。每个表只能创建一
个主键。PRIMARY KEY约束是表中的对行唯一标识的一个
列或者列组合，该约束强制列或列组合的唯一性，并且确保
作为主键一部分的列不能包含空值。
既可以定义在表级也可以定义在列级：
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));
FOREIGNKEY约束
FOREIGN KEY约束（语法）
既可以定义在表级也可以定义在列级：
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));
FOREIGN KEY约束 (续)
外键被定义在子表中，包含引用列的表是父表。外键用下面关键字的组合定义：
FOREIGN KEY 被用于在表约束级定义子表中的列。
REFERENCES 确定父表中的表和列。
ON DELETE CASCADE 指出当父表中的行被删除时，子表中相依赖的行也将被级联删除。
ON DELETE SET NULL 当父表的值被删除时，转换外键值为空。
默认行为被称为约束规则，该规则不允许引用数据的更新或删除。
无ON DELETE CASCADE或ON DELETE SET NULL选项，如果父表中的行在子表中引用，则它不能被删除
CHECK约束
•定义每行必须满足的条件
• CHECK约束能够被定义在列级或表级。
...， salary NUMBER(2)
CONSTRAINT emp_salary_min
CHECK (salary > 0)，...
添加约束语法
用ALTER TABLE语句：
•添加或删除约束，但不修改它的结构
•启用或禁用约束
•用MODIFY子句添加一个NOT NULL约束
ALTER TABLE table
ADD [CONSTRAINT constraint] type (column);
table 是表的名字
constraint 是约束的名字
type 是约束的类型
column 是受约束影响的列的名字
删除约束
•从EMPLOYEES表中删除经理约束
ALTER TABLE employees
DROP CONSTRAINT emp_manager_fk;
•删除DEPARTMENTS表上的PRIMARY KEY约束，并且删
除相关联的在EMPLOYEES.DEPARTMENT_ID列上的
FOREIGN KEY约束。
ALTER TABLE departments
DROP PRIMARY KEY CASCADE;
禁用约束
•执行ALTERTABLE语句的DISABLE子句来禁用完整性约
束。
•应用CASCADE选项禁用相依赖的完整性约束
ALTER TABLE employees
DISABLE CONSTRAINT emp_emp_id_pk CASCADE;
原则
你即可以在CREATE TABLE语句也可以在ALTER TABLE语句中使用 DISABLE子句。
CASCADE子句禁用相依赖的完整性约束。
禁用唯一或主键约束会移除唯一性索引。
启用约束
•用ENABLE字句启用一个在表中定义的当前禁用的完整性 约束。
ALTER TABLE employees
ENABLE CONSTRAINT emp_emp_id_pk;
•如果启用一个UNIQUE键或PRIMARY KEY约束一个
UNIQUE或PRIMARY KEY索引被自动创建。
原则：
如果启用一个约束，约束将应用于表中所有的数据，所有在表中的数据都必须适合该约束。
如果你启用一个UNIQUE键或者PRIMARY KEY约束，一个UNIQUE或PRIMARY KEY索引将被自动地创建。
你即可以CREATE TABLE语句也可以在ALTER TABLE语句中使用ENABLE子句。
启用一个带CASCADE选项的被禁用的主键约束不会起用任何依赖于该主键的外键。
查看约束
查询USER_CONSTRAINTS表来查看所有约束定义和命名。
SELECT constraint_name， constraint_type，search_condition
FROM user_constraints
WHERE table_name = 'EMPLOYEES';
注：那些没有被表的所有者命名的约束将收到系统指定的约束
名。在约束类型中，C代表CHECK，P代表PRIMARY KEY，
R代表引用完整性， U代表UNIQUE键。注意NOT NULL约束
实际上是一个CHECK约束。
查看约束关联的列
观察在USER_CONS_COLUMNS视图中与约束名关联的列
SELECT constraint_name， column_name
FROM user_cons_columns
WHEREtable_name = 'EMPLOYEES';
创建视图
目标
完成本课后， 您应当能够执行下列操作：
•描述视图
•创建视图，修改视图的定义，删除视图
•通过视图取回数据
•通过视图插入、更新和删除数据
•创建和使用内嵌视图
•执行“Top-N”分析
数据库对象
对 象 说 明
Table (表) 基本存储单元，由行和列组成
View (视图) 数据来自一个或者多个表的数
据子集的逻辑表示
Sequence (序列) 产生主键的值
Index (索引) 改善某些查询的性能
Synonym (同义词) 一个对象的替换名字
什么是视图?
你可以通过创建表的视图来表现数据的逻辑子集或数据的组
合。视图是基于表或另一个视图的逻辑表，一个视图并不包
含它自己的数据，它象一个窗口，通过该窗口可以查看或改
变表中的数据。视图基于其上的表称为基表。视图在数据字
典中作为一个SELECT语句存储。
为什么用视图?
•限制数据访问
•使得复杂的查询容易
•提供数据的独立性
•表现相同数据的不同观察
简单视图和复杂视图
下面是简单视图：
数据仅来自一个表
不包含函数或数据分组
能通过视图执行DML操作
下面是复杂视图：
数据来自多个表
包含函数或数据分组
不总是允许通过视图进行DML操作
创建视图
创建一个视图，EMPVU80，其中包含了在部门80 中雇员
的详细信息。
CREATE VIEW empvu80
AS SELECT employee_id， last_name， salary
FROM employees
WHERE department_id = 80;
查看视图的结构
DESCRIBE empvu80
创建视图
•用子查询中的列别名创建视图
CREATE VIEW salvu50
AS SELECT employee_id ID_NUMBER，
last_name NAME，salary*12 ANN_SALARY
FROM employees
WHERE department_id = 50;
或
CREATE VIEW salvu50 (ID_NUMBER， NAME， ANN_SALARY)
AS SELECT employee_id， last_name， salary*12
FROM employees
WHERE department_id = 50;
•从该视图中选择列，视图中的列使用别名命名
DESCRIBE SQLVU50
查询视图
•一旦视图被创建，你就可以查询数据字典视图USER_VIEWS
来看视图的名字和视图定义。构成视图的SELECT语句的文本
被存储在一个LONG列中。
•用视图存取数据
当你用视图存取数据时，Oracle 服务器执行下面的操作：
从数据字典表USER_VIEWS中取回视图定义。
检查对视图的基表的数据存取权限。
转换视图查询为一个在基表或表上的等价操作，换句话说，数据从基表得到，或更新基表。
修改视图
•用CREATE OR REPLACE 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;
•在CREATE VIEW子句中的字段别名列表，按照与子查询中的字段相同的顺序排列
创建复杂视图
创建包含组函数的复杂视图，以从两个表中显示值
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;
视图中DML 操作的执行规则
•只能在简单视图上执行DML 操作
•如果视图中包含下面的部分就不能删除行：
–组函数
–GROUP BY子句
–DISTINCT关键字
–伪列ROWNUM关键字
视图中DML 操作的执行规则
如果视图中包含下面的部分就不能修改数据：
•组函数
•GROUP BY子句
•DISTINCT关键字
•伪列ROWNUM关键字
•用表达式定义的列
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 ;
•任何改变视图的任意行中部门号的企图都会失败，因为它违
反了WITH CHECK OPTION约束。
可以通过视图执行引用完整性检查，你也可以在数据库级别
强制约束。
•添加WITH READ ONLY选项到视图定义中，能够确保无
DML 操作发生。
删除视图
删除视图不会丢失数据，因为视图是基于数据库中
的基本表的。
DROP VIEW empvu80;
内建视图
•内建视图是一个带有别名(或相关名) 的可以在SQL 语句中
使用的子查询。
•一个主查询的在FROM 子句中指定的子查询就是一个内建
视图的离子。
•内建子查询不是方案对象
SELECT a.last_name，a.salary，a.department_id，b.maxsal
FROM employees a，
(SELECT department_id，max(salary) maxsal
FROM employees
GROUP BY department_id) b
WHERE a.department_id = b.department_id
AND a.salary < b.maxsal;
Top-N 分析
•Top-N 查询寻找一列的n个最大或最小值，例如：
–销售最好的前10 位产品是什么?
–销售最差的前10 位产品是什么?
•最大值和最小值在Top-N 查询中设置
• Top-N查询在需要基于一个条件，从表中显示最前面的n条
记录或最后面的n条记录时是有用的。
执行Top-N 分析
Top-N 分析查询的高级结构是：
SELECT [column_list]， ROWNUM
FROM (SELECT [column_list]
FROM table ORDER BY Top-N_column)
WHERE ROWNUM <= N;
Top-N 分析的例子
为了从EMPLOYEES表中显示挣钱最多的3 个人的名字及其
薪水：
SELECT ROWNUM as RANK， last_name， salary
FROM (SELECT last_name，salary
FROM employees ORDER BY salary DESC)
WHERE ROWNUM <= 3;
其它数据库对象
目标
完成本课后， 您应当能够执行下列操作：
•创建、维护和使用序列
•创建和维护索引
•创建私有和公有同义词
数据库对象
对 象 说 明
Table (表) 基本存储单元，由行和列组成
View (视图) 数据来自一个或者多个表的数
据子集的逻辑表示
Sequence (序列) 产生主键的值
Index (索引) 改善某些查询的性能
Synonym (同义词) 一个对象的替换名字
什么是序列?
序列：
•是自动产生的唯一的数
•是可共享的对象
•典型的用途是创建一个主键值
•可以代替应用程序编号
•当使用高速缓存存储器时，访问序列值的效率提高
CREATE SEQUENCE语句语法
定义一个序列来自动产生有顺序的数：
CREATE SEQUENCE sequence
[INCREMENT BY n]
[START WITH n]
[{MAXVALUE n| NOMAXVALUE}]
[{MINVALUE n| NOMINVALUE}]
[{CYCLE | NOCYCLE}]
[{CACHE n| NOCACHE}];
• sequence 是序列发生器的名字
INCREMENT BY n 指定序列号之间的间隔，在这儿n是一个整数 (如果该子句 被省略，序列增量为1)
CREATE SEQUENCE语句语法（续）
• START WITH n 指定要产生的第一个序列数 (如果该子句
被省略，序列 从1 开始)
• MAXVALUE n 指定序列能产生的最大值
• NOMAXVALUE 对于升序序列指定10^27为最大值，对于
降序序列指定-1 为最大值 (这是默认选项)
• MINVALUE n 指定最小序列值
• NOMINVALUE 对于升序序列指定1为最小值，对于降序序
列指定 -(10^26) 为最小值 (这是默认选项)
• CYCLE|NOCYCLE 指定序列在达到它的最大或最小值之
后，是否继续产生(NOCYCLE是默认选项)
• CACHE n|NOCACHE 指定Oracle服务器预先分配多少值，
并且保持在内存中(默认 情况下，Oracle服务器缓冲20个值)
创建序列
创建一个序列，命名为DEPT_DEPTID_SEQ。
CREATE SEQUENCE dept_deptid_seq
INCREMENT BY 10
START WITH 120
MAXVALUE 9999
NOCACHE
NOCYCLE;
确认序列
•校验在USER_SEQUENCES数据字典表中的序列值。
SELECT sequence_name， min_value， max_value，
increment_by， last_number
FROM user_sequences;
•如果NOCACHE被指定，LAST_NUMBER列显示
下一个可用序列数。
NEXTVAL和CURRVAL伪列
在创建序列后，它产生连续的数给你在表中使用。用
NEXTVAL和CURRVAL伪列引用序列值。你必须用序列名
限定NEXTVAL，当你引用sequence.NEXTVAL时，一个新
的序列数被产生并且当前的序列数被放入CURRVAL。
•NEXTVAL返回下一个可用的序列值，它每次返回
一个唯一的被引用值，即使对于不同的用户也是如
此。
•CURRVAL获得当前的序列值
•在CURRVAL获得一个值以前，NEXTVAL对该序
列必须发布。
使用NEXTVAL和CURRVAL的规则
你可以在下面的上下文中使用NEXTVAL和CURRVAL：
一个不是子查询的一部分的SELECT语句的SELECT列表
在一个INSERT语句中子查询的SELECT列表
一个INSERT语句中的VALUES子句
一个UPDATE语句的SET子句
你不能在下面的上下文中使用NEXTVAL和CURRVAL：
一个视图的SELECT列表
一个带DISTINCT关键字的SELECT语句
一个带GROUP BY、HAVING或ORDER BY子句的SELECT语句
一个在SELECT、DELETE或UPDATE语句中的子句
在CREATE TABLE或ALTER TABLE语句中的DEFAULT表达式
使用序列
•在location ID 2500 中插入一个新部门名称“Support”
INSERT INTO departments
(department_id， department_name， location_id)
VALUES (dept_deptid_seq.NEXTVAL，
'Support'， 2500);
•查看当前的DEPT_DEPTID_SEQ序列值
SELECT dept_deptid_seq.CURRVAL
FROM dual;
使用序列（续）
•可以更快地访问缓存在存储器中的序列值
•序列值可能产生间隙，由于：
–一个回退发生
–系统崩溃
–一个序列被用于另一个表
•如果带NOCACHE创建序列，查询
USER_SEQUENCES 表，可以查看下一个可用值 。
修改序列
创建增量值，最大值，最小值，循环选项和缓存选项
ALTER SEQUENCE dept_deptid_seq
INCREMENT BY 20
MAXVALUE 999999
NOCACHE
NOCYCLE;
如果序列达到MAXVALUE限制，将再无来自序列的新值产
生，并且你将收到一个序列已经超过MAXVALUE的错误指
示。为了继续使用序列，你可以用ALTER SEQUENCE语句
修改该序列。
修改序列的原则
•你必须是该序列的所有者，或者有ALTER该序列的权限
•只有未来的序列数受影响
•为了以不同的数字重新开始一个序列，该序列必须被删除并
且被重新创建
•一些确认被执行
注：用ALTER SEQUENCE语句，START WITH选项不能被
改变。为了以不同的数重新开始一个序列，该序列必须被删
除和重新创建。
删除序列：
•用DROP SEQUENCE语句从数据字典中删除序列
•序列一旦不再被引用可以被删除
DROP SEQUENCE dept_deptid_seq;
什么是索引?
索引：
•是一个方案对象
•由Oracle 服务器使用，索引用一个指针来加速行的取回
•用快速路径访问方法来快速定位数据，减小磁盘I/O
•表和它的索引是无关的
•被Oracle 服务器自动地使用和维护
索引怎样被创建?
•自动：在一个表的定义中，当定义一个PRIMARY KEY或
UNIQUE约束时，一个唯一索引被自动创建。
•手动：用户能够在列上创建非唯一的索引来加速对 行的访
问。
索引的类型
•唯一索引
当你在一个表中定义一个列为主键，或者定义一个唯一键约
束时Oracle服务器自动创建该索引，索引的名字习惯上是约
束的名字。
•非唯一索引
由用户来创建
创建索引
•在一个或多个列上创建索引
CREATE INDEX index ON table(column[， column]...);
•改善EMPLOYEES表中LAST_NAME列的查询访 问速度。
CREATE INDEX emp_last_name_idx
ON employees(last_name);
index 是索引的名字
table 是表的名字
column 是表中被索引的列的名字
什么时候创建索引
你应该创建索引，如果：
•一个列包含一个大范围的值
•一个列包含很多的空值
•一个或多个列经常同时在一个WHERE子句中或一
个连接条件中被使用
•表很大，并且经常的查询期望取回少于百分之2 到
4 的行。
确认索引
•USER_INDEXES数据字典视图包含索引和它唯一的名字
•USER_IND_COLUMNS视图包含索引名、表名和列名
SELECT ic.index_name，ic.column_name，
ic.column_positioncol_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视图，检查与索引有关
的列。
删除索引
•用DROP INDEX命令从数据字典中删除索引
DROP INDEX index;
注：你不能修改索引，为了改变索引，你必须先删
除它，然后重新创建它。
同义词
创建同义词可以简化对象访问(对象的另一个名字)。同义词
能够：
•另一个用户易于查阅表的所有者
•使对象名字变短
CREATE [PUBLIC] SYNONYM synonym
FOR object;
PUBLIC 创建一个可以被所有用户访问的同义词
synonym 是要被创建的同义词的名字
object 指出要创建同义词的对象
创建和删除同义词
•为DEPT_SUM_VU视图创建一个短名字
CREATE SYNONYM d_sum
FORM dept_sum_vu;
•删除同义词
DROP SYNONYM d_sum;
练习题
创建employee表
(emp_id number(6)，emp_name varchar2(20)，
work_date date，job_id varchar(10)，dept_id (4)，
salary number(8，2)，remark varchar2(10))
创建jobs表
(job_id varchar2(10)，job_desc varchar2(40)，
min_salary number(6)， max_salary number(6 ))
创建dept表
(dept_id number(4)，dept_name varchar2(30)，
dept_type varchar2(8))
定义约束条件
分别在employee表的emp_id列、jobs表的job_id列、dept表的dept_id列上定义主键约束条件。
分别在employee表的job_id列参照jobs(job_id)、
dept_id列参照dept(dept_id)创建外键。
表中插入数据
查询表中按部门分组的收入总数、最大值、最小值、平均值。
查询表中收入在2000至3000的员工收入
将收入小于2000的员工工资*1.5，在2000至4000之间的*1.3，高于4000的*1.1
展开
