ORACLE SQL 基础

xulz1984

贡献于2014-07-12

字数:0 关键词: SQL

ORACLE 交流第一群:48949977 ORACLE SQL 基础 [群共享读物第四期] 3435626 不靠谱 2009-9-15 编者不靠谱按:天堂向左,DBA 往右,没啥说的,玩命学吧~~ ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 2 目录 第 1 章 编写基本的 SQL SELECT 语句 ....................................................................................... 9 1.1 SQL 的起源和分类 ....................................................................................................... 10 1.1.1 SQL 的起源 ........................................................................................................ 10 1.1.2 SQL 的分类 ........................................................................................................ 11 1.2 本章学习目的 ................................................................................................................ 13 1.3 SQL SELECT 语句的功能 ............................................................................................ 14 1.4 基本 SELECT 语句 ....................................................................................................... 15 1.4.1 选择所有列 ......................................................................................................... 16 1.4.2 选择指定的列 ..................................................................................................... 17 1.4.3 SQL 语句的编辑 .................................................................................................. 17 1.4.4 列标题默认属性 ................................................................................................. 18 1.4.5 算术表达式 ......................................................................................................... 18 1.4.6 定义空值 ............................................................................................................. 20 1.4.7 定义列别名 ......................................................................................................... 23 1.4.8 连字运算符 ......................................................................................................... 25 1.4.9 文字字符串 ......................................................................................................... 26 1.4.10 相同的行 ........................................................................................................... 27 1.5 iSQL*Plus 简介 ............................................................................................................ 29 1.5.1 iSQL*Plus 概览 ................................................................................................ 29 1.5.2 SQL 和 iSQL*Plus 交互作用 .......................................................................... 30 第 2 章 约束和排序 ....................................................................................................................... 31 2.1 本章学习目的 ................................................................................................................ 32 2.2 用选择限定行 ................................................................................................................ 33 2.2.1 限定语法 ............................................................................................................. 34 2.2.2 使用 WHERE 字句 ................................................................................................. 34 2.2.3 条件中使用字符串和日期 ................................................................................. 35 2.2.4 比较条件 ............................................................................................................. 35 2.2.5 使用比较条件 ..................................................................................................... 36 2.2.6 使用 BETWEEN 条件 ............................................................................................. 37 2.2.7 使用 IN 条件 ....................................................................................................... 37 2.2.8 使用 LIKE 条件 ................................................................................................... 38 2.2.9 使用 NULL/NOT NULL 条件 ................................................................................. 40 2.2.10 逻辑条件 ........................................................................................................... 41 2.2.11 优先规则 ........................................................................................................... 44 2.3 排序 ................................................................................................................................ 45 2.3.1 降序排序 ........................................................................................................... 46 2.3.2 用列别名排序 ................................................................................................... 48 2.3.3 多列排序 ........................................................................................................... 48 2.4 总结 ................................................................................................................................ 49 第 3 章 ORACLE SQL 单行函数 ................................................................................................ 50 3.1 学习目的 ........................................................................................................................ 51 3.2 SQL 函数介绍 ............................................................................................................... 52 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 3 3.3 SQL 函数分类 ............................................................................................................... 53 3.4 单行函数 ........................................................................................................................ 54 3.5 介绍内容 ........................................................................................................................ 55 3.6 DUAL 表介绍 ................................................................................................................ 56 3.7 字符函数 ........................................................................................................................ 57 3.7.1 大小写处理函数 ............................................................................................... 57 3.7.2 字符处理函数 ................................................................................................... 58 3.8 数字函数 ........................................................................................................................ 61 3.9 日期函数 .......................................................................................................................... 62 3.9.1 系统日期 ........................................................................................................... 63 3.9.2 用日期计算 ....................................................................................................... 63 3.9.3 日期函数 ........................................................................................................... 64 3.10 转换函数 ...................................................................................................................... 66 3.10.1 隐式数据类型转换 ......................................................................................... 66 3.10.2 显式数据类型转换 ......................................................................................... 67 3.10.3 日期格式模板 ................................................................................................. 68 3.10.4 时间格式模板 ................................................................................................. 68 3.10.5 数字格式模板 ................................................................................................. 69 3.11 嵌套函数 ...................................................................................................................... 70 3.12 通用函数 ...................................................................................................................... 71 3.13 条件表达式 .................................................................................................................. 73 3.13.1 CASE 表达式 .................................................................................................... 73 3.13.2 DECODE 函数 .................................................................................................... 74 3.14 总结 .............................................................................................................................. 76 第 4 章 从多表中查询数据 ........................................................................................................... 77 4.1 从多表中查询数据 ........................................................................................................ 78 4.2 有效连接条件与笛卡儿积 ............................................................................................ 80 4.3 连接的类型 .................................................................................................................... 82 4.4 表连接的原则 ................................................................................................................ 83 4.5 等值连接 ........................................................................................................................ 84 4.6 等值连接中的列别名、表别名 .................................................................................... 87 4.7 多于两个表的等值连接 ................................................................................................ 89 4.8 非等值连接 .................................................................................................................... 91 4.9 外连接 ............................................................................................................................ 93 4.10 自连接 .......................................................................................................................... 97 4.11 SQL 1999 连接语法介绍........................................................................................... 100 4.11.1 交叉连接 ....................................................................................................... 100 4.11.2 自然连接 ....................................................................................................... 100 4.11.3 ON 子句 ........................................................................................................ 101 4.12 总结 ............................................................................................................................ 102 第 5 章 用组函数合计数据 ......................................................................................................... 103 5.1 学习目的 ...................................................................................................................... 103 5.2 组函数的概念 .............................................................................................................. 104 5.3 组函数使用 .................................................................................................................. 106 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 4 5.3.1 使用 AVG、SUM、MAX、MIN ............................................................................ 106 5.3.2 使用 COUNT ...................................................................................................... 107 5.3.3 使用 DISTINCT 关键字 ................................................................................... 108 5.3.4 组函数与空值 ................................................................................................. 108 5.3.5 GROUP BY 子句 ............................................................................................... 108 5.3.6 HAVING 子句 ................................................................................................... 111 5.3.7 嵌套组函数 ..................................................................................................... 112 5.4 总结 .............................................................................................................................. 113 第 6 章 子查询 ............................................................................................................................. 114 6.1 学习目的 ...................................................................................................................... 114 6.2 子查询介绍 .................................................................................................................. 115 6.3 使用子查询的原则 ...................................................................................................... 117 6.4 子查询分类 .................................................................................................................. 118 6.5 单行子查询 .................................................................................................................. 119 6.6 单行子查询错误 .......................................................................................................... 122 6.7 多行子查询 .................................................................................................................. 124 6.8 总结 .............................................................................................................................. 126 第 7 章 操纵数据 ......................................................................................................................... 127 7.1 学习目的 ...................................................................................................................... 127 7.2 数据操纵语言 .............................................................................................................. 128 7.3 INSERT 插入数据的语法 ........................................................................................... 130 7.4 INSERT 插入值的变化 ............................................................................................... 132 7.4.1 插入特殊值 ..................................................................................................... 132 7.4.2 插入指定日期 ................................................................................................. 133 7.4.3 插入可输入变量 ............................................................................................. 133 7.4.4 从另外一个表复制 ......................................................................................... 133 7.4.5 在 INSERT 语句中使用子查询 ....................................................................... 134 7.5 UPDATE 更新数据的语法 ......................................................................................... 136 7.6 UPDATE 更新的例子和问题 ..................................................................................... 138 7.6.1 用子查询更新两列 ......................................................................................... 138 7.6.2 更新基于另一个表的行 ................................................................................. 139 7.6.3 更新导致完整性约束报错 ............................................................................. 139 7.7 DELETE 删除数据的语法 .......................................................................................... 140 7.8 DELETE 更新的例子和问题 ...................................................................................... 142 7.8.1 从表中删除行 ................................................................................................. 142 7.8.2 删除基于另一个表的行 ................................................................................. 143 7.8.3 删除数据导致的完整性约束 ......................................................................... 143 7.9 显式默认值 .................................................................................................................. 144 7.10 MERGE 合并语句语法 ............................................................................................. 145 7.11 数据库事务处理 ........................................................................................................ 148 7.11.1 数据库事务开始与结束 ............................................................................... 148 7.11.2 COMMIT 和 ROLLBACK 语句 ............................................................................ 149 7.11.3 事务的控制 ................................................................................................... 150 7.11.4 事务的控制示例 ........................................................................................... 151 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 5 7.11.5 隐式事务处理 ............................................................................................... 151 7.12 读一致性 .................................................................................................................... 152 7.13 锁定 ............................................................................................................................ 153 7.13.1 显式锁定 ....................................................................................................... 154 7.13.2 隐式锁定 ....................................................................................................... 155 7.13.3 按照对象分类锁定 ....................................................................................... 155 7.14 本章总结 .................................................................................................................... 155 第 8 章 创建和管理表 ................................................................................................................. 156 8.1 数据库对象及表介绍 .................................................................................................. 157 8.2 表的分类 ...................................................................................................................... 158 8.3 表和列的命名规则 ...................................................................................................... 159 8.4 CREATE TABLE 语句创建表 .................................................................................... 160 8.5 用子查询语法创建表 .................................................................................................. 162 8.6 DEFAULT 选项 ........................................................................................................... 164 8.7 引用表 .......................................................................................................................... 165 8.8 数据类型介绍 .............................................................................................................. 166 8.9 时间数据类型 .............................................................................................................. 168 8.10 TIMESTAMP 类型 .................................................................................................... 169 8.11 INTERVAL YEAR TO MONTH 数据类型 ..................................................... 171 8.12 INTERVAL DAY TO SECOND 数据类型....................................................... 173 8.13 ALTER TABLE 语句 .............................................................................................. 175 8.13.1 添加列 ........................................................................................................... 176 8.13.2 修改列 ........................................................................................................... 176 8.13.3 删除列 ........................................................................................................... 176 8.14 删除表 ........................................................................................................................ 178 8.15 改变对象名称 ............................................................................................................ 178 8.16 截断表 ........................................................................................................................ 180 8.17 给表添加注释 ............................................................................................................ 181 8.18 总结 ............................................................................................................................ 182 第 9 章 内置约束 ......................................................................................................................... 183 9.1 约束的概念和原则 ...................................................................................................... 183 9.1.1 约束的概念 ..................................................................................................... 184 9.1.2 约束的原则 ..................................................................................................... 184 9.2 定义约束 ...................................................................................................................... 185 9.2.1 定义约束的语法 ............................................................................................. 185 9.2.2 约束分类 ......................................................................................................... 186 9.3 几种常见约束 .............................................................................................................. 187 9.3.1 NOT NULL 约束 ................................................................................................ 187 9.3.2 UNIQUE 约束 .................................................................................................... 188 9.3.3 PRIMARY KEY 约束 .......................................................................................... 189 9.3.4 FOREIGN KEY 约束 .......................................................................................... 189 9.3.5 CHECK 约束 ...................................................................................................... 190 9.4 管理约束 ...................................................................................................................... 192 9.4.1 添加约束 ......................................................................................................... 192 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 6 9.4.2 删除约束 ......................................................................................................... 193 9.4.3 禁用、启用约束 ............................................................................................. 194 9.4.4 级联约束 ......................................................................................................... 194 9.5 查看约束 ...................................................................................................................... 196 9.6 总结 .............................................................................................................................. 197 第 10 章 创建视图 ....................................................................................................................... 198 10.1 视图的概念和优势 .................................................................................................... 199 10.1.1 视图的概念 ................................................................................................... 199 10.1.2 使用视图的优势 ........................................................................................... 200 10.2 简单视图和复杂视图 ................................................................................................ 200 10.2.1 创建简单视图 ............................................................................................... 201 10.2.2 视图中查询数据 ........................................................................................... 202 10.2.3 创建复杂视图 ............................................................................................... 203 10.3 视图中的规则 ............................................................................................................ 204 10.3.1 视图中 DML 操作的执行规则 ...................................................................... 204 10.3.2 With Check OPTION 子句 ............................................................................ 205 10.3.3 With READ ONLY 子句 .................................................................................. 205 10.4 管理视图 .................................................................................................................... 207 10.4.1 修改视图 ....................................................................................................... 207 10.4.2 删除视图 ....................................................................................................... 207 10.4.3 内建视图 ....................................................................................................... 208 10.5 Top-N 分析 ............................................................................................................... 209 10.6 物化视图简介 ............................................................................................................ 211 10.7 总结 ............................................................................................................................ 213 第 11 章 其他数据库对象 ........................................................................................................... 214 11.1 其他数据库对象的作用 ............................................................................................ 215 11.2 序列 ............................................................................................................................ 216 11.2.1 序列的语法 ................................................................................................... 216 11.2.2 定义及查询序列 ........................................................................................... 217 11.2.3 NEXTVAL 和 CURRVAL 伪列 ............................................................................ 218 11.2.4 使用序列 ....................................................................................................... 219 11.2.5 修改序列 ....................................................................................................... 220 11.2.6 删除序列 ....................................................................................................... 220 11.3 索引 ............................................................................................................................ 221 11.3.1 创建索引 ....................................................................................................... 221 11.3.2 创建索引的时机 ........................................................................................... 222 11.3.3 查询索引 ....................................................................................................... 223 11.3.4 基于函数的索引 ........................................................................................... 223 11.3.5 删除索引 ....................................................................................................... 224 11.4 同义词 ........................................................................................................................ 225 11.4.1 概念语法 ....................................................................................................... 225 11.4.2 创建删除同义词 ........................................................................................... 226 11.5 总结 ............................................................................................................................ 226 第 12 章 控制用户访问 ............................................................................................................... 227 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 7 12.1 控制用户访问的意义 ................................................................................................ 228 12.2 用户及权限 ................................................................................................................ 229 12.2.1 权限介绍 ....................................................................................................... 229 12.2.2 系统权限 ....................................................................................................... 229 12.2.3 创建用户 ....................................................................................................... 230 12.2.4 授权系统权限 ............................................................................................... 230 12.3 角色 ............................................................................................................................ 231 12.3.1 角色概念 ....................................................................................................... 231 12.3.2 创建并授权角色 ........................................................................................... 232 12.4 对象的维护 ................................................................................................................ 233 12.4.1 改变口令 ....................................................................................................... 233 12.4.2 对象权限的意义 ........................................................................................... 233 12.4.3 授权对象权限 ............................................................................................... 234 12.4.4 WITH GRANT OPTION 和 PUBLIC .......................................................... 235 12.4.5 确认已授予的权限 ....................................................................................... 236 12.4.6 撤销对象权限 ............................................................................................... 236 12.5 数据库链接 ................................................................................................................ 237 12.6 总结 ............................................................................................................................ 238 第 13 章 SQL*Plus ...................................................................................................................... 240 13.1 SQL 语句与 SQL*Plus ............................................................................................. 241 13.1.1 关系对比 ....................................................................................................... 241 13.1.2 关系图 ........................................................................................................... 242 13.2 SQL*Plus 概览 ......................................................................................................... 243 13.2.1 登录 SQL*Plus .............................................................................................. 243 13.2.2 显示表结构 ..................................................................................................... 244 13.2.2 SQL*Plus 编辑命令 ....................................................................................... 244 13.2.3 使用 LIST, n, 和 APPEND .......................................................................... 245 13.2.4 SQL*Plus 文件命令 ...................................................................................... 246 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 8 序 言 本篇主要介绍 Oracle SQL 运行环境及 SQL 语法相关内容。通过本篇的学习,必须掌 握 ORACLE SQL 的使用。如同一个文员必须掌握 office 的使用一样。 SQL,结构化查询语言,是用于访问关系型数据库的语言,它提供一系列的表达式用 于从关系数据库中存储、更新、删除、取得数据。自从关系型数据库发布以来,它逐步流行 了起来。其他的语言也被提出用语关系型数据库,但是 SQL 作为几乎所有关系型数据库的 标准语言已经被大众所接受,这其中包含大家正在学习的 ORACLE。 SQL 与其他的程序设计语言不同,因为它是非程序化的。不像在其他编程语言中,我 们必须按部就班执行每个步骤,SQL 程序的目的是为了快速的得到结果。 如果你正在使用一个 ORACLE 数据库,SQL 就是你和存储在数据库中数据的接口。 SQL 允许你构造诸如表、视图、索引等数据结构。同时它还允许你向数据库中插入数据、 以可定制的格式(比如说,你希望排序)从数据库中查到数据。最后,SQL 还允许你修改、删 除、操作你所存储的数据。SQL 是你和数据库交互的关键。掌握这个关键对于使用数据库 来说是非常重要的。掌握 SQL,对于数据库管理员以及数据库工程师来说,它犹如古代剑 客手中的剑、现代士兵手中的枪一样绝对是不可或缺的。 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 9 第 1 章 编写基本的 SQL SELECT 语句 第 一 章 编写基本的 SQL  SQL 的起源和分类  SQL SELECT 语句功能  基本 SELECT 语句  iSQLPLUS 简介 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 10 1.1 SQL 的起源和分类 1.1.1 SQL 的起源 SQL(Structure Query Language)语言是数据库的核心语言。 SQL 的发展是从 1974 年开始的,其发展过程如下: 1974 年-----由 Boyce 和 Chamberlin 提出,当时称 SEQUEL。 1976 年-----IBM 公司的 Sanjase 研究所在研制 RDBMS SYSTEM R 时改为 SQL。 1979 年-----ORACLE 公司发表第一个基于 SQL 的商业化 RDBMS 产品。 1982 年-----IBM 公司出版第一个 RDBMS 语言 SQL/DS。 1985 年-----IBM 公司出版第一个 RDBMS 语言 DB2。 1986 年-----美国国家标准化组织 ANSI 宣布 SQL 作为数据库工业标准。 SQL 是一个标准的数据库语言,是面向集合的描述性非过程化语言。它功能强,效率 高,简单易学易维护(迄今为止,我还没见过比它还好学的语言)。然而 SQL 语言由于以上 优点,同时也出现了这样一个问题: 第 一 节 SQL 的起源和分类 SQL 全称 Structure Query Language,是数据库的核 心语言,它包括如下三大类:  数据操纵语言 DML  数据定义语言 DDL  数据控制语言 DCL ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 11 它是非过程性语言,即大多数语句都是独立执行的,与上下文无关,而绝大部分应用都 是一个完整的过程,显然用 SQL 完全实现这些功能是很困难的。所以大多数数据库公司为 了解决此问题,作了如下两方面的工作: (1)扩充 SQL,在 SQL 中引入过程性结构;(2)把 SQL 嵌入到高级语言中,以便一起完 成一个完整的应用。 1.1.2 SQL 的分类 SQL 语言共分为三大类:数据操纵语言 DML,数据定义语言 DDL,数据控制语言 DCL。 数据操纵语言,数据操纵语言 DML 主要有四种形式: 1) 插入:INSERT 2) 更新:UPDATE 3) 删除:DELETE 4) 选择:SELECT 数据定义语言 DDL,数据定义语言 DDL 用来创建数据库中的各种对象-----表、视图、 索引、同义词、聚簇等(TABLE/VIEW/INDEX/SYN/CLUSTER) 语句: 语 句 名 称 含 义 CREATE DATABASE 创建数据库 CREATE TABLE 创建表 DROP TABLE 删除表 ALTER TABLE 修改表的结构 CREATE VIEW 创建视图 DROP VIEW 删除视图 CREATE INDEX 创建索引 DROP INDEX 删除索引 数据控制语言 DCL,数据控制语言 DCL 用来授予或回收访问数据库的某种特权,并控 制,数据库操纵事务发生的时间及效果,对数据库实行监视等。如: 1) GRANT:授权 2) REVOKE:取消权限 3) ROLLBACK [WORK] TO [SAVEPOINT]:回退到某一点。 回滚---ROLLBACK ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 12 回滚命令使数据库状态回到上次最后提交的状态。其格式为: SQL>ROLLBACK; 4) COMMIT [WORK]:提交。 在数据库的插入、删除和修改操作时,只有当事务在提交到数据库时才算完成。在事务 提交前,只有操作数据库的这个人才能有权看到所做的事情,别人只有在最后提交完成后才 可以看到。提交数据有三种类型:显式提交、隐式提交及自动提交。下面分别说明这三种类 型。 (1) 显式提交 用 COMMIT 命令直接完成的提交为显式提交。其格式为: SQL>COMMIT; 2) 隐式提交 用 SQL 命令间接完成的提交为隐式提交。这些命令是: ALTER,AUDIT,COMMENT,CONNECT,CREATE,DISCONNECT,DROP,EXIT, GRANT,NOAUDIT,QUIT,REVOKE,RENAME。 3) 自动提交 若把 AUTOCOMMIT 设置为 ON,则在插入、修改、删除语句执行后,系统将自动进 行提交,这就是自动提交。其格式为: SQL>SET AUTOCOMMIT ON; ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 13 1.2 本章学习目的 为了从数据库中查询数据,你需要使用结构化查询语言 SQL(Structured Query Language SQL) 的SELECT语句,你还可能需要限制查询所返回的行和列。本章程描述了用于执行这 个任务所需要的所有SQL语句。 你也能够创建可以多次使用的SELECT语句。该语句可以保存为文本文件,比如 TEST.SQL,多次执行。 本章还包括用来执行SQL语句的iSQL*Plus环境的使用介绍。 注:iSQL*Plus 是在 Oracle9i 产品中新增的,并在 Oracle10g 中得到增强,它是一个执行 SQL 命令的浏览器环境。Oracle 以前发布的 SQL*Plus 是执行 SQL 命令的默认的环境。SQL*Plus 现在任然可用,并且在 21 章中讲述。 完成本章后, 您应当能够:列出 SQL SELECT 语句的功能,执行基本的 SELECT 语句, 区分 SQL 语句和 iSQL*Plus 命令 第 二 节 本章学习目的  SQL 中使用最多的语句:SELECT  使用 SQL*Plus 执行 SQL  使用 iSQLPLUS 执行 SQL  SQL*Plus 和 iSQLPLUS 的不同 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 14 1.3 SQL SELECT 语句的功能 SELECT 语句从数据库中查询并返回信息。使用一个 SELECT 语句,你可以做下面的 事: 列选择:你能够使用 SELECT 语句的列选择功能选择数据库表中的列,这些列是你想 要用查询返回的。当你查询时,你能够选择你查询的表中指定的列。列在数据库中是以垂直 的形式存在的。任何一个列在查询返回的数据中都是一列。 行选择:你能够使用 SELECT 语句的行选择功能选择数据库表中的行,这些行是你想 要用查询返回的。你能够使用不同的标准限制你查询的行。比如你选择查询前 5 行、你选择 查询后 5 行,你选择查询中间的某些行,你选择使用某个条件的限制来返回某些行,你选择 使用几个条件的限制来返回几个行。行是以水平的形式存在的,任何的水平的一个单位都是 数据库表中的一行。 连接:你能够使用 SELECT 语句的连接功能来进行数据库对象的连接,并返回几个对 象的集合数据,这些数据被存储在不同的表、视图中(视图是什么呢,跟表差不多,只是一 般不存储实际的数据),在它们之间可以创建连接。连接可以针对两个表或视图,也可以针 第 三 节 SQL SELECT 语句的功能  从数据库中查询数据  列选择:选择表的部分或所有列  行选择:设定条件选择表的部分或所有行  连接:将多个表组合起来进行选择。 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 15 对多个表或视图。连接还有不同的类型,比如外连接、内连接、做连接、右连接等。在后面 的课程中你将学到更多关于连接的内容。 1.4 基本 SELECT 语句 SELECT * |{[DISTINCT] column | expression [alias],...} FROM table; SELECT 用语确定查询哪些列,如果是查询所有的列,则使用*号。FROM 确定 查询哪张表,必须至少指定一张表来查询。 在最简单的形式中,SELECT 语句必须包含下面的内容: 一个 SELECT 子句,指定被显示的列,列名不可以重复。 一个 FROM 子句,指定表,该表包含 SELECT 子句中的字段列表。可以指定相同的表, 但是需要用表的缩写区分开来。 在语法中: SELECT 是一个或多个字段的列表 * 选择所有的列 第 四 节 基本 SELECT 语句  选择列  编辑 SQL 语句  算术表达式与空值  列别名  运算符与字符串  取相同的行 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 16 DISTINCT 禁止重复 column|expression 选择指定的字段或表达式 alias 给所选择的列不同的标题 FROM table 指定包含列的表 例如: Select * from tab Select distinct col1,col2 from tab1 Select col1 c1 ,col2 c2 from tab2 单词 keyword(关键字), clause(子句)和 statement(语句)被如下使用: keyword 引用一个单个的 SQL 元素。 例如,SELECT 和 FROM 是关键字。 clause 是 SQL 语句的一个部分。 例如,SELECT col1, col2, ...是一个子句。 statement 是两个或多个子句的组合。 例如,SELECT * FROM employees 是一个 SQL语句。 1.4.1 选择所有列 SQL> select * 2 from test; DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID ------------- --------------- ---------- ----------- 10 IT 200 1700 20 DBA 201 1800 50 BI 124 1500 60 SA 103 1400 80 ADM 149 2600 5 rows selected 用跟在 SELECT 关键字后面的星号 (*),你能够显示表中数据的所有列。在上面的例子 中,TEST 表包含 4 个列:DEPARTMENT_ID、DEPARTMENT_NAM、MANAGER_ID 和 LOCATION_ID。该表包含 5 行,每个部门一行。 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 17 在 SELECT 关键字后面列出所有列,你也能够显示所有的列。下面的 SQL 语句,显示 TEST 表的所有列和所有行: SELECT department_id, department_name, manager_id, location_id FROM TEST; 1.4.2 选择指定的列 Select department_id, department_name from test; SQL> Select department_id, department_name from test; DEPARTMENT_ID DEPARTMENT_NAME ------------- --------------- 10 IT 20 DBA 50 BI 60 SA 80 ADM 你能够用SELECT语句来显示表的指定列,指定列名之间用逗号分隔。例子显示 DEPARTMENTS表中所有的部门号和位置号。 在SELECT子句中,指定你想要的列,其顺序是你想要在输出中呈现的。例如,为了从左 到右在部门号之前显示位置号,你可以用下面的语句: SELECT location_id, department_id FROM test; 除了可以选择表中的列之外,我们还可以选择伪列。一个伪列的行为像表中的列一样, 但不是真的存储在表中。你不能插入、删除伪列的值。一些可用的为列是 CURRVAL、 NEXTVAL、LEVEL、ROWID 和 ROWNUM。CURRVAL、NEXTVAL 来自于序列(SEQUENCE), LEVEL 来自于分析函数,ROWID 显示了该列的实际存储位置,ROWNUM 显示了该列在当 前条件下的顺序号。 1.4.3 SQL 语句的编辑 SQL 语句的编辑遵循下面的规则: SQL 语句对大小写不敏感,除非指出。 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 18 SQL 语句可以写成一行或多行。 关键字不能简写或分开折行。 为了易于阅读和编辑,子句通常放在不同的行。 应该使用缩进,以使代码更易阅读。 关键字一般用大写输入;所有其它的名字和列,用小写输入。 使用 iSQL*Plus,单击 执行 按钮来运行编辑窗口中的命令。在 iSQL*Plus 中,在子句 的最后,分号 (;) 不是必须的,但还是推荐使用。在其它环境中,例如,PL/SQL 程序,需 要在每条语句的结尾加一个分号。 1.4.4 列标题默认属性 列标题默认属性• iSQL*Plus: –默认列标题位置: 居中 –默认列标题显示: 大写 SQL*Plus: –字符和日期列标题位置居左 –数字列标题居右 –默认列标题显示: 大写 在iSQL*Plus中,列标题用大写字母居中显示。 SELECT last_name, hire_date, salary FROM employees; 可以使用别名覆盖列标题: SELECT last_name ln, hire_date hd, salary sr FROM employees; 1.4.5 算术表达式 你可能需要修改数据显示方式,如执行计算,或者作假定推测,这些都可能用到算术表 达式。一个算术表达式可以包含列名、固定的数字值和算术运算符。 算术运算符包含一下的内容: ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 19 + 加、- 减、* 乘 、/ 除。 可以对 Number、DATE 和 TIMESTAMP 使用加减的操作。 下面的语句使用算术表达式: Select 2 + department_id, department_id-2, department_id*3, department_id/5 from test; 2+DEPARTMENT_ID DEPARTMENT_ID-2 DEPARTMENT_ID*3 DEPARTMENT_ID/5 --------------- --------------- --------------- --------------- 12 8 30 2 22 18 60 4 52 48 150 10 62 58 180 12 82 78 240 16 对 department_id 进行加 2、减 2、乘 3、除以 5 的操作。数据库会忽略在算术运算符前 面和后面的空格。 算术运算符的优先等级与数学中的一致: 乘法和除法比加法和减法的优先级高;相同优先级的运算符从左到右计算;圆括号用 于强制优先计算,并且使语句更清晰 运算符的优先级: Select department_name ,2*department_id+200-2 from test; SQL> Select department_name ,2*department_id+200-2 from test; DEPARTMENT_NAME 2*DEPARTMENT_ID+200-2 --------------- --------------------- IT 218 DBA 238 BI 298 SA 318 ADM 358 Id 先乘以 2 然后加 200,最后减 2 Select department_name ,(2*department_id)+200-2 from test; ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 20 该语句不改变查询的结果,只是让查询比较清晰 Select department_name ,2*(department_id+200-2) from test; SQL> Select department_name ,2*(department_id+200-2) from test; DEPARTMENT_NAME 2*(DEPARTMENT_ID+200-2) --------------- ----------------------- IT 416 DBA 436 BI 496 SA 516 ADM 556 该语句通过圆括号改变了查询的结果。因为有圆括号,加减法的优先级就高于乘法。 1.4.6 定义空值 SQL> select department_id,department_name ,testn from test; DEPARTMENT_ID DEPARTMENT_NAME TESTN ------------- --------------- ---------------------- 10 IT 20 DBA 50 BI 60 SA 80 ADM 如果一行中的某个列缺少数据值,该值被置为null, 或者说包含一个空。 空是一个难以获得的、未分配的、未知的,或不适用的值。空和0或者空格不相同。0是一个 数字,而空格是一个字符。 任何数据类型的列都可以包含空。可是,某些约束,如,NOT NULL和PRIMARY KEY,防 止在列中使用空。 在 EMPLOYEES 表的 COMMISSION_PCT 列中,我们注意到只有销售经理或销售代表 才有佣金,其他的雇员没有佣金,空表示了这个事实。 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 21 空值无法和数字型的列和常量进行计算,但是可以和字符串类型的列和常量进行连接。 如下例: NULL 减去 number 型: SQL> select department_id,department_name ,testn-2 from test; DEPARTMENT_ID DEPARTMENT_NAME TESTN-2 ------------- --------------- ---------- 10 IT 20 DBA 50 BI 60 SA 80 ADM NULL 加上 number 型: SQL> select department_id,department_name ,testn+2 from test; DEPARTMENT_ID DEPARTMENT_NAME TESTN+2 ------------- --------------- ---------- 10 IT 20 DBA 50 BI 60 SA 80 ADM NULL 值连接上一个字符: SQL> select department_id,department_name ,testn||'test' from test; DEPARTMENT_ID DEPARTMENT_NAME TESTN||'TEST' ------------- --------------- -------------------------- 10 IT test 20 DBA test 50 BI test 60 SA test 80 ADM test ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 22 NULL 值乘以 2: SQL> select department_id,department_name ,testn*2 from test; DEPARTMENT_ID DEPARTMENT_NAME TESTN*2 ------------- --------------- ---------- 10 IT 20 DBA 50 BI 60 SA 80 ADM NULL 值除以 2: SQL> select department_id,department_name ,testn/2 from test; DEPARTMENT_ID DEPARTMENT_NAME TESTN/2 ------------- --------------- ---------- 10 IT 20 DBA 50 BI 60 SA 80 ADM 2 除以 NULL 值: SQL> select department_id,department_name ,2/testn from test; DEPARTMENT_ID DEPARTMENT_NAME 2/TESTN ------------- --------------- ---------- 10 IT 20 DBA 50 BI 60 SA 80 ADM 在对空值 TESTN 进行加减乘除操作之后,返回的是空值或者未知。对空值进行连接字 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 23 符串的操作之后,返回为被连接的字符串。 1.4.7 定义列别名 在显示查询结果时,iSQL*Plus通常用被选择列的名字作为列标题,该标题的描述可能 不确切,因此可能难于理解,这时你可以用列别名改变列标题。 在 SELECT 列表中的列名后面指定别名,列名和别名之间用空格分开。默认情况下, 别名标题用大写字母显示。如果别名中包含空格或者特殊字符(例如 # 或 &),或者大小 写敏感,将别名放在双引号 (“”) 中。 列别名有如下的作用: 改变列标题的名字 可用于计算结果 紧跟在列名后面–在列名和别名之间可以有选项 AS 关键字 如果别名中包含有空格、或者特殊字符、或者大小写敏感,要求用双引号 我们可以通过如下的方法使用列别名: SQL> select department_id did,department_name dname from test; DID DNAME ---------- ---------------------------------------- 10 IT 20 DBA 50 BI 60 SA SQL> select department_id as did,department_name as dname from test; DID DNAME ---------- ---------------------------------------- 10 IT 20 DBA ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 24 50 BI 60 SA 80 ADM 80 ADM SQL> select department_id "did",department_name "dn ame" from test; did dname ---------- ---------------------------------------- 10 IT 20 DBA 50 BI 60 SA 80 ADM SQL> select department_id as 部门码,department_name 部门名称 from test; 部门码 部门名称 ---------- ---------------------------------------- 10 IT 20 DBA 50 BI 60 SA 80 ADM 第一个例子直接在列后面加空格与列别名,第二个例子是加上 as 之后加空格与列别名, 注意此处可选项 AS 关键字被用于别名 name 前面。查询结果与是否使用 AS 关键字无关。 列别名 name 和 comm 用小写字母定义,但是在查询显示结果中,列标题用大写字母显示。 因为前面说过,在默认情况下,列标题用大写字母显示。 第三个例子使用双引号来显示列别名,因为 dn ame 中包含一个空格,所以必须放在双 引号中,注意,输出的列标题与列名的定义完全相同。 一般来说列别名最好使用大写字母。当然也可以使用中文。如第四个例子。 在一个 SQL 语句中,一个列别名既能用在 SELECT 子句也能用在 ORDER BY 子句中。 你不能在 WHERE 子句中使用列别名。别名遵循 ANSI SQL 92 标准。如下: ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 25 SQL> select department_id did,department_name dname ,2/testn ot from test 2 order by did; DID DNAME OT ---------- ---------------------------------------- ---------- 10 IT 20 DBA 50 BI 60 SA 80 ADM SQL> select department_id did,department_name dname ,2/testn ot from test 2 order by did where did >0; order by did where did >0 * 第 2 行出现错误: ORA-00933: SQL 命令未正确结束 1.4.8 连字运算符 连字运算符: 连接列或者字符串到其它的列 用两个竖线表示(||) 构造一个字符表达式的合成列 你能够用连字运算符 (||) ,进行列与列之间、列与算术表达式之间或者列与常数值之 间的连接,来创建一个字符表达式。连字运算符两边的列被合并成一个单个的输出列。 使用连字符: SQL> select department_id||department_name as "depart" from test; depart ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 26 -------------------- 10IT 20DBA 50BI 60SA 80ADM 在例子中 department_id 和 department_name 被连接,并且指定列别名 depart。这两个 字段被合并到一个单个的输出列中。 1.4.9 文字字符串 文字字符串是包含在SELECT列表中的一个字符串,一个数字或者一个日期,并且不是 列名或别名。对每个返回行打印一次。任意格式文本的文字字符串能够被包含在查询结果中, 并且作为SELECT列表中的列处理。日期和字符文字 必须放在单但引号 (' ') 中;数字不需 要。 SQL> select department_id ||' is a '||department_name ||' s id' as "id" from test; id ----------------------------------------------------------------------- 10 is a IT s id 20 is a DBA s id 50 is a BI s id 60 is a SA s id 80 is a ADM s id SQL> select department_id,2,345 from test; DEPARTMENT_ID 2 345 ------------- ---------- ---------- 10 2 345 20 2 345 50 2 345 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 27 60 2 345 80 2 345 1.4.10 相同的行 除非你另外指出,iSQL*Plus显示的查询结果中不限制重复的行,如下面的例子: SQL> select * from numtest; NUMT ---------- 10 10 20 20 30 30 30 15 15 已选择9行。 那如何去除相同的行呢?在SELECT子句中用DISTINCT关键字除去相同的行。为了在结果 中除去相同的行,在SELECT子句中的SELECT关键字后面紧跟DISTINCT关键字。在下面的例子 中,numtest表实际有8行,但表中只有4个不同的人名。 在DISTINCT限定词后面,你可以指定多个列。DISTINCT限定词影响所有被选定的列,并 且结果是每个不相同的列的组合。 SQL> select * from numtest; NUMT NAME ---------- ------------------------ 10 MARK 10 MARK 20 MARY 20 MARY 30 JOB 30 JOB ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 28 30 JOB 15 ZERO 15 ZERO 已选择9行。 SQL> select distinct * from numtest; NUMT NAME ---------- ------------------------------ 10 MARK 30 JOB 20 MARY 15 ZERO SQL> select distinct numt from numtest; NUMT ---------- 30 20 10 15 SQL> select distinct name from numtest; NAME ---------------------------------------- ZERO MARK MARY JOB ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 29 1.5 iSQL*Plus 简介 1.5.1 iSQL*Plus 概览 在登录进入iSQL*Plus 后,你能够: 描述表结构 编辑SQL 语句 从iSQL*Plus 中执行SQL 保存SQL 语句到文件中,或者添加SQL 语句到文件中 执行保存在文件中的语句 从文本文件中装载命令到iSQL*Plus 编辑窗口中 iSQL*Plus命令能够被分为下面几种: 环境:影响会话期间SQL语句的总体行为 格式化:格式化查询结果 文件处理:保存语句到脚本文件中,从文本脚本文件中运行语句 第 五 节 iSQL*Plus 概览 iSQL*Plus 概览:  iSQL*Plus 是基于网页的 SQL*Plus。它可以实现 SQL*Plus 的大部分功能,它的界面更加友好。 SQL 和 iSQL*Plus 交互作用:  从浏览器访问、输入要执行的 SQL 语句。 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 30 执行:从浏览器发送SQL语句到Oracle服务器 编辑:在编辑窗口中修改SQL语句 交互:允许你创建并传递变量到SQL语句,打印变量值和打印信息到屏幕上 其他:还有各种连接数据库、操作iSQL*Plus环境和显示字段定义的命令 1.5.2 SQL 和 iSQL*Plus 交互作用 SQL是从一个工具或应用程序与Oracle服务器通信的一种命令语言,Oracle SQL包含许 多扩展。 iSQL*Plus是一个Oracle工具,用来识别并发送SQL语句到Oracle服务器执行。iSQL*Plus 有它自己的命令语言。 SQL 的特性: 能够被一个用户范围使用,包括那些有一点或根本没有编程经验的人,是一种非过程语 言减少创建和维护系统所需的时间,是一种类似英语的语言。SQL是一种语言,遵循ANSI 标 准,关键字不能缩写,SQL 语句操纵定义在数据库中的数据和表。用函数完成一些格式化。 iSQL*Plus 的特性: 从浏览器访问,接受语句的输入,提供联机编辑修改 SQL 语句,控制环境设置,格式 化查询结果到一个基本的报告中,访问本地和远程数据库。iSQL*Plus是一个Oracle所有的 环境,关键字可以缩写,命令不允许操纵数据库中的值,运行在浏览器上,处理集中在服务 器上,不在客户机上实现。如果命令长于一行,用破折号 (-) 折行。用命令格式化数据。 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 31 第 2 章 约束和排序 第 二 章 条件和排序  条件: - 在语句的 Where 子句之后。 - 限制查询的行数。  排序: - 在语句的 Order by 子句后 - 决定了行的输出顺序 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 32 2.1 本章学习目的 第 一 节 SQL 的起源和分类 从数据库选择数据的时候,你可能需要限制所显示的数据行,或 者将显示的结果行排序。本章介绍用来完成这些工作的 SQL 语句。 所谓限制,就是条件,比如我们从班级中选出体重超过 150 斤的 同学。 所谓排序就是按照某种属性进行排序之后,显示出查询的结果, 比如我们按照生日的大小进行排序。 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 33 2.2 用选择限定行 首先查询 EMPLOYEES 表: Select Employee_Id, Last_Name, Job_Id, Department_Id From Employees; EMPLOYEE_ID LAST_NAME JOB_ID DEPARTMENT_ID 100 King AD_PRES 90 101 Kochhar AD_VP 90 102 De Haan AD_VP 90 103 Hunold IT_PROG 60 104 Ernst IT_PROG 60 105 Austin IT_PROG 60 106 Pataballa IT_PROG 60 107 Lorentz IT_PROG 60 108 Greenberg FI_MGR 100 109 Faviet FI_ACCOUNT 100 110 Chen FI_ACCOUNT 100 111 Sciarra FI_ACCOUNT 100 112 Urman FI_ACCOUNT 100 113 Popp FI_ACCOUNT 100 如果我们需要选择出部门 100 的员工,如何做呢? 第 二 节 用选择限定行  语法介绍  使用 WHERE 子句  比较条件和 BETWEEN 条件  IN、LIKE 条件  NULL/NOT NULL 条件  逻辑条件与优先规则 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 34 EMPLOYEE_ID LAST_NAME JOB_ID DEPARTMENT_ID 108 Greenberg FI_MGR 100 109 Faviet FI_ACCOUNT 100 110 Chen FI_ACCOUNT 100 111 Sciarra FI_ACCOUNT 100 112 Urman FI_ACCOUNT 100 113 Popp FI_ACCOUNT 100 2.2.1 限定语法 用 WHERE 子句限制返回的行,WHERE 子句跟着 FROM 子句: SELECT * |{[DISTINCT] column | expression [alias] ,... }FROM table [WHEREcondition(s)]; 限制选择的行: 你能够用WHERE子句限制从查询返回的行。一个WHERE子句包含一个必须满足的条 件,WHERE子句紧跟着FROM子句。如果条件是true,返回满足条件的行。 在语法中: WHERE 限制查询满足条件的行 condition 由列名、表达式、常数和比较操作组成 WHERE子句能够比较列值、文字值、算术表达式或者函数,WHERE子句由三个元素 组成: 列名,比较条件,列名、常量或值列表。 2.2.2 使用 WHERE 字句 假定你想要显示部门 100 中所有的雇员。只返回 DEPARTMENT_ID 列值为 100 的行, 这种限制方法基于 SQL 的 WHERE 子句。 Select Employee_Id, Last_Name, Job_Id, Department_Id From Employees Es Where Es.Department_Id = 100 EMPLOYEE_ID LAST_NAME JOB_ID DEPARTMENT_ID 108 Greenberg FI_MGR 100 109 Faviet FI_ACCOUNT 100 110 Chen FI_ACCOUNT 100 111 Sciarra FI_ACCOUNT 100 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 35 112 Urman FI_ACCOUNT 100 113 Popp FI_ACCOUNT 100 在例子中,SELECT 语句取回所有雇员的 name,job ID 和 department 号,这些雇员的 department_id 是 100。 2.2.3 条件中使用字符串和日期 在WHERE子句中字符串和日期必须包含在单引号 (‘ ’) 中。但是,数字常数不应该包含 在单引号中。 所有的字符搜索是大小写敏感的。在下面的例子中,没有行返回,因为 EMPLOYEES 表存储的所有名字都是大小写混合的: Select Last_Name, Job_Id, Department_Id From Employees Where Last_Name = 'WHALEN'; 对于大小写敏感的字符串,是可以通过UPPER和LOWER函数来处理的,在后续的章节会有 描述。 Oracle数据库以内部数字格式存储日期,表示为:世纪、年、月、日、小时、分和秒。 默认的日期显示是DD-MON-YYYY。 改变默认的日期格式的方法在后面的章节中介绍。如 下: SQL> Select Sysdate From Dual; SYSDATE ---------------- 06-9 月 -2008 SQL> 2.2.4 比较条件 ORACLE SQL中的比较条件包含以下几种运算符号: = 等于 > 大于 >= 大于等于 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 36 < 小于 <= 小于等于 <> 不等于 != 不等于 ^= 不等于 BETWEEN...AND... 在两个值之间(包含) IN(set) 匹配一个任意值列表 LIKE 匹配一个字符模板 IS NULL 是一个空值 IS NOT NULL 不是一个空值 比较条件被用于一个表达式与一个值或与另一个表达式的比较。他们以下面的格式被用 于WHERE子句中: 语法:... WHERE expr operator value 例如: ... WHERE hire_date='01-1 月 -1995' ... WHERE salary>=6000 ... WHERE last_name='Smith' 2.2.5 使用比较条件 Select Last_Name, Salary From Employees Where Salary <= 2300; LAST_NAME SALARY 3 月 kle 2200 Olson 2100 Philtanker 2200 在例子中,SELECT从EMPLOYEES表语句取回last name和salary,在这里这些雇员的薪 水小于等于2300。注意,有一个直接的值2300提供给WHERE子句。该直接值2300与 EMPLOYEES表中的SALARY列的薪水值进行比较。 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 37 2.2.6 使用 BETWEEN 条件 Select Last_Name, Salary From Employees Where Salary Between 3000 And 3500; LAST_NAME SALARY Khoo 3100 Nayer 3200 Bissot 3300 Mallin 3300 Stiles 3200 Rajs 3500 Davies 3100 Taylor 3200 Fleaur 3100 Dellinger 3400 Cabrio 3000 McCain 3200 Walsh 3100 Feeney 3000 你可以用BETWEEN范围条件显示基于一个值范围的行。你指定的范围包含一个下限和一 个上限。上例中的SELECT语句从EMPLOYEES表中返回薪水在$3,000和$3,500之间的那些雇员。 BETWEEN条件包括指定的上下限值,必须先指定下限。如果上限小于下限,将不返回值。 BETWEEN „ AND „实际上是由 Oracle服务器转变为一对AND条件:(a >= 下限) AND (a <= 上 限),所以使用BETWEEN „ AND „ 并没有性能的提高,只是逻辑上简单。 2.2.7 使用 IN 条件 Select Employee_Id, Last_Name, Salary, Manager_Id From Employees Where Manager_Id In (100, 101, 201); EMPLOYEE_ID LAST_NAME SALARY MANAGER_ID 101 Kochhar 17000 100 102 De Haan 17000 100 108 Greenberg 12000 101 114 Raphaely 11000 100 120 Weiss 8000 100 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 38 121 Fripp 8200 100 122 Kaufling 7900 100 123 Vollman 6500 100 124 Mourgos 5800 100 145 Russell 14000 100 146 Partners 13500 100 147 Errazuriz 12000 100 148 Cambrault 11000 100 149 Zlotkey 10500 100 200 Whalen 4400 101 201 Hartstein 13000 100 202 Fay 6000 201 203 Mavris 6500 101 204 Baer 10000 101 205 Higgins 12000 101 用IN条件在指定的一组值中进行测试。IN条件也就是成员条件。 在例子中显示所有经理号为100、101或201的雇员的employee numbers, last names, salaries和经理的employee numbers。在IN条件中可以使用任何数据类型。下面的例子从 EMPLOYEES表返回雇员信息行,这些雇员的名字包括在WHERE子句的名字列表中: Select Employee_Id, Manager_Id, Department_Id From Employees Where Last_Name In ('Hartstein', 'Vargas'); 如果IN条件中的成员是字符或日期,它们必须放在单引号 („‟) 中。 使用IN ( ... )条件,实际上是由Oracle服务器转变为一组OR条件:a = value1 OR a = value2 OR a = value3,所以使用 IN ( ... ) 并没有得到性能的提高,只是逻辑上简单。 2.2.8 使用 LIKE 条件 我们也许不知道要搜索的确切的值,你能够选择那些用LIKE条件匹配一个字符模板的行。 字符模板匹配运算涉及通配符查询。有两个符号 % 和 _ 可以用来构造搜索串。使用LIKE 条件执行有效搜索串值的通配符搜索,搜索条件既可以包含文字也可以包含数字:%表示零 个或多个字符,_表示一个字符。如下例子: Select First_Name From Employees ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 39 Where First_Name Like 'S%'; FIRST_NAME Steven Shelli Sigal Shanta Steven Stephen Sarath Sundar Sundita Sarah Samuel Susan Shelley 组合模式的匹配字符串: Select Last_Name From Employees Where Last_Name Like '_o%'; LAST_NAME Kochhar Lorentz Popp Tobias Colmenares Vollman Mourgos Rogers Doran Fox Johnson Jones 如何搜索实际的%和_ 符号,使用ESCAPE标识符可以搜索实际的%和_符号。 如果你想要搜索包含„SA_‟的字符串,可以用下面的SQL语句: Select Employee_Id, Last_Name, Job_Id From Employees Where Job_Id Like '%SA\_%AN' Escape '\'; ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 40 EMPLOYEE_ID LAST_NAME JOB_ID 145 Russell SA_MAN 146 Partners SA_MAN 147 Errazuriz SA_MAN 148 Cambrault SA_MAN 149 Zlotkey SA_MAN 如果你想搜索包含%SA的字符串,可以使用下面的SQL语句: Select * From test Where department_name LIKE '\%SA%' ESCAPE '\'; ESCAPE选项指定反斜线 (\) 为换码符,在模板中,换码符字符在下划线 (_)和百分号 (%) 的前面,原因是Oracle服务器逐字地解释字符串。 DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID TESTN 60 %SA 103 1400 2.2.9 使用 NULL/NOT NULL 条件 Select Last_Name, Manager_Id From Employees Where Manager_Id Is Null; LAST_NAME MANAGER_ID King NULL条件,包括IS NULL条件和IS NOT NULL条件。 IS NULL条件用于空值测试。空 值的意思是难以获得的、未指定的、未知的或者不适用的。因此,你不能用 = ,因为NULL 不能等于或不等于任何值。例子取回所有没有主管经理的雇员的名字和他的主管经理。 Select Last_Name, Job_Id, Commission_Pct From Employees Where Commission_Pct Is Null; LAST_NAME JOB_ID COMMISSION_PCT King AD_PRES Kochhar AD_VP De Haan AD_VP ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 41 Hunold IT_PROG Ernst IT_PROG Austin IT_PROG Pataballa IT_PROG Lorentz IT_PROG Greenberg FI_MGR Faviet FI_ACCOUNT Chen FI_ACCOUNT Sciarra FI_ACCOUNT Urman FI_ACCOUNT Popp FI_ACCOUNT Raphaely PU_MAN Select Last_Name, Job_Id, Commission_Pct From Employees Where Commission_Pct Is Not Null And Rownum <10; LAST_NAME JOB_ID COMMISSION_PCT Russell SA_MAN 0.4 Partners SA_MAN 0.3 Errazuriz SA_MAN 0.3 Cambrault SA_MAN 0.3 Zlotkey SA_MAN 0.2 Tucker SA_REP 0.3 Bernstein SA_REP 0.25 Hall SA_REP 0.25 Olsen SA_REP 0.2 2.2.10 逻辑条件 逻辑条件组合两个比较条件的结果来产生一个基于这些条件的单个的结果,或者逆转一 个单个条件的结果。当所有条件的结果为真时,返回行。SQL的三个逻辑运算符是: AND 、OR 、NOT 至此所有的例子仅在WHERE子句中指定唯一的条件,你可以在WHERE子句中用AND 和OR运算符使用多个条件。 使用AND操作: Select Employee_Id, Last_Name, Job_Id, Salary From Employees Where Salary >= 10000 And Job_Id Like '%MAN%'; ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 42 EMPLOYEE_ID LAST_NAME JOB_ID SALARY 114 Raphaely PU_MAN 11000 145 Russell SA_MAN 14000 146 Partners SA_MAN 13500 147 Errazuriz SA_MAN 12000 148 Cambrault SA_MAN 11000 149 Zlotkey SA_MAN 10500 201 Hartstein MK_MAN 13000 在该例中,对于被选择的任何记录两个条件必须为真,因此,只有工作岗位包含字符串 MAN并且收入大于等于$10,000的那些雇员被选择。 所有字符搜索是大小写敏感的,如果MAN不是大写,则没有行返回。字符串必须放在 引号中。 使用OR操作: Select Employee_Id, Last_Name, Job_Id, Salary From Employees Where Salary >= 10000 Or Job_Id Like '%MAN%'; EMPLOYEE_ID LAST_NAME JOB_ID SALARY 100 King AD_PRES 24000 101 Kochhar AD_VP 17000 102 De Haan AD_VP 17000 108 Greenberg FI_MGR 12000 114 Raphaely PU_MAN 11000 120 Weiss ST_MAN 8000 121 Fripp ST_MAN 8200 122 Kaufling ST_MAN 7900 123 Vollman ST_MAN 6500 124 Mourgos ST_MAN 5800 145 Russell SA_MAN 14000 146 Partners SA_MAN 13500 在上面的例子中,两个条件之一为真的那些记录都被选择,因此,任何job ID中包含MAN 或者收入大于等于$10,000的雇员都被选择。 使用NOT操作: ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 43 Select Last_Name, Job_Id From Employees Where Job_Id Not In ('IT_PROG', 'ST_CLERK', 'SA_REP'); LAST_NAME JOB_ID King AD_PRES Kochhar AD_VP De Haan AD_VP Greenberg FI_MGR Faviet FI_ACCOUNT Chen FI_ACCOUNT Sciarra FI_ACCOUNT Urman FI_ACCOUNT Popp FI_ACCOUNT Raphaely PU_MAN Khoo PU_CLERK 例子显示那些工作岗位不是IT_PROG、ST_CLERK或SA_REP的雇员的名字和工作岗位。 Select Last_Name, Job_Id From Employees Where Job_Id Not Like '%A%' LAST_NAME JOB_ID Hunold IT_PROG Ernst IT_PROG Austin IT_PROG Pataballa IT_PROG Lorentz IT_PROG Greenberg FI_MGR Khoo PU_CLERK Baida PU_CLERK Tobias PU_CLERK Himuro PU_CLERK Colmenares PU_CLERK NOT运算符也可以用于另一个SQL运算符,例如,IN、BETWEEN和NULL。 ... WHERE job_id NOT IN ('AC_ACCOUNT', 'AD_VP') ... WHERE salary NOT BETWEEN 10000 AND 15000 ... WHERE commission_pct IS NOT NULL ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 44 2.2.11 优先规则 优先规则定义表达式求值和计算的顺序,表中列出了默认的优先顺序。你可以用圆括号 括住你想要先计算的表达式来覆盖默认的优先顺序: 1 算术运算 2 连字操作 3 比较操作 4 IS[NOT]NULL, LIKE, [NOT]IN5 5 [NOT] BETWEEN 6 NOT 逻辑条件 7 AND 逻辑条件 8 OR 逻辑条件 Select Last_Name, Job_Id, Salary From Employees Where Job_Id = 'SA_REP' Or Job_Id = 'AD_PRES' And Salary > 15000; LAST_NAME JOB_ID SALARY King AD_PRES 24000 Tucker SA_REP 10000 Bernstein SA_REP 9500 Hall SA_REP 9000 Olsen SA_REP 8000 Cambrault SA_REP 7500 Tuvault SA_REP 7000 King SA_REP 10000 Sully SA_REP 9500 在例子中,有两个条件: 第一个条件是job_id是AD_PRES并且薪水高于15,000。 第二个条件是job_id是SA_REP。 因此,该SELECT语句读作:“选择符合下面条件的行,雇员是董事长 (president) , 并且收入超过$15,000,或雇员是销售代表。” 使用圆括号强制优先权: ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 45 Select Last_Name, Job_Id, Salary From Employees Where (Job_Id = 'SA_REP' Or Job_Id = 'AD_PRES') And Salary > 15000; LAST_NAME JOB_ID SALARY King AD_PRES 24000 在上面的例子中有两个条件: 第一个条件是job_id是AD_PRES或者SA_REP 。 第二个条件是薪水高于$15,000 因此,该SELECT语句读作: “选择符合下面条件的行,雇员是董事长 (president) 或 者销售代表,并且收入超过$15,000。” 2.3 排序 第 三 节 排序  升、降序排序 - 升序:按列值由小到大 - 降序:按列值由大到小  用列别名排序  多列排序。多个排序列由逗号分隔,首先以最左边 列的顺序为准,如遇到重复值再以接下来的一列为 准。 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 46 语法: SELECT expr FROM table [WHERE condition(s)] [ORDER BY {column, expr} [ASC|DESC]]; ORDER BY 指定排序显示返回的行,ASC 以升序排序行 (这是默认排序),DESC 以降序 排序行。ORDER BY子句在SELECT语句的最后。可以指定一个表达式,或者一个别名,作为排 序条件。 如果未使用ORDER BY子句,排序次序就未定义,并且Oracle服务器可能对于相同查询的 两次执行取回行的顺序不同。ORDER BY子句以指定的顺序显示返回的行。ORDER BY子句在查 询完成后执行,除非使用了FOR UPDATE子句,ORDER BY子句应放在最后。 Select Last_Name, Job_Id, Department_Id, Hire_Date From Employees Order By Hire_Date; LAST_NAME JOB_ID DEPARTMENT_ID HIRE_DATE King AD_PRES 90 1987-6-17 Whalen AD_ASST 10 1987-9-17 Kochhar AD_VP 90 1989-9-21 Hunold IT_PROG 60 1990-1-3 Ernst IT_PROG 60 1991-5-21 De Haan AD_VP 90 1993-1-13 Mavris HR_REP 40 1994-6-7 Baer PR_REP 70 1994-6-7 Higgins AC_MGR 110 1994-6-7 Gietz AC_ACCOUNT 110 1994-6-7 2.3.1 降序排序 Select Last_Name, Job_Id, Department_Id, Hire_Date From Employees Order By Hire_Date Desc; LAST_NAME JOB_ID DEPARTMENT_ID HIRE_DATE Ku3 月 SA_REP 80 2000-4-21 Banda SA_REP 80 2000-4-21 Ande SA_REP 80 2000-3-24 3 月 kle ST_CLERK 50 2000-3-8 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 47 Lee SA_REP 80 2000-2-23 Philtanker ST_CLERK 50 2000-2-6 Geoni SH_CLERK 50 2000-2-3 Zlotkey SA_MAN 80 2000-1-29 3 月 vins SA_REP 80 2000-1-24 Grant SH_CLERK 50 2000-1-13 Johnson SA_REP 80 2000-1-4 Perkins SH_CLERK 50 1999-12-19 Gee ST_CLERK 50 1999-12-12 Popp FI_ACCOUNT 100 1999-12-7 数据的默认排序,默认的排序顺序是升序: 对于数字值,小的只值在前面显示,例如,1–999。 对于日期,早的日期在前面显示,例如,01-JAN-92 在 01-JAN-95 。 对于字符值,依字母顺序显示,例如,A第一,Z最后。 对于空值,升序排序时显示在最后,降序排序时显示在最前面。 反转默认排序: 为了显示倒序排序的行,在ORDER BY子句的列名后面指定DESC关键字。例子中,按 照雇员的受雇日期从近到远排序。 也能够以SELECT列表中的列号排序。下面的例子用 salary以逆序排序输出: Select Last_Name, Salary From Employees Order By 2 Desc; LAST_NAME SALARY King 24000 Kochhar 17000 De Haan 17000 Russell 14000 Partners 13500 Hartstein 13000 Greenberg 12000 Errazuriz 12000 Higgins 12000 Ozer 11500 Abel 11000 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 48 2.3.2 用列别名排序 Select Employee_Id, Last_Name, Salary * 12 Annsal From Employees Order By Annsal; EMPLOYEE_ID LAST_NAME ANNSAL 132 Olson 25200 128 3 月 kle 26400 136 Philtanker 26400 135 Gee 28800 127 Landry 28800 119 Colmenares 30000 131 3 月 low 30000 140 Patel 30000 144 Vargas 30000 182 Sullivan 30000 191 Perkins 30000 143 Matos 31200 能够在ORDER BY子句中使用列别名。例子用年薪排序数据。在内部,SELECT语句的 执行顺序如下:FROM 子句、WHERE 子句、SELECT 子句、ORDER BY 子句。 2.3.3 多列排序 Select Last_Name, Department_Id, Salary From Employees Order By Department_Id, Salary Desc; LAST_NAME DEPARTMENT_ID SALARY Whalen 10 4400 Hartstein 20 13000 Fay 20 6000 Raphaely 30 11000 Khoo 30 3100 Baida 30 2900 Tobias 30 2800 Himuro 30 2600 Colmenares 30 2500 Mavris 40 6500 Fripp 50 8200 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 49 Weiss 50 8000 Kaufling 50 7900 例子中首先按照Department_Id排序,在Department_Id相同的基础上,按照Salary排序。 如果你想要倒许序排序一个列,在该列名后面指定DESC。你也可以用没有包括在SELECT 子句中的列排序。例如: Select Last_Name, Salary From Employees Order By Department_Id, Salary Desc; LAST_NAME SALARY Whalen 4400 Hartstein 13000 Fay 6000 Raphaely 11000 Khoo 3100 Baida 2900 Tobias 2800 Himuro 2600 2.4 总结 在本章中, 您应该已经学会如何:使用WHERE子句限制输出行,使用比较条件–使用 BETWEEN, IN, LIKE, 和NULL条件,应用逻辑AND, OR, 和NOT操作,使用ORDER BY子 句排序输出的行。 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 50 第 3 章 ORACLE SQL 单行函数 第 三 章 ORACLE SQL 单行 函数  SQL 函数介绍和分类  单行函数  主要内容及 DUAL 表  字符函数、数字函数、日期函数  转换函数、嵌套函数  通用函数、条件表达式 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 51 3.1 学习目的 第 一 节 SQL 的起源和分类 函数使得基本查询块更强大,函数用于操纵数据值。本 章是探索函数的两节课程之一,内容集中在单行字符、数字 和日期函数,以及转换数据类型的函数,例如,字符数据到 数字数据的转换。学习本章后,您应当能够执行下列操作: 描述在 SQL 中可用的函数的变量类型,在 SELECT 语句中 使用字符,数字和日期函数,描述转换函数的使用。 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 52 3.2 SQL 函数介绍 第 二 节 SQL 函数介绍 函数是 SQL 的一个非常强有力的特性,函数能够用于下面的目的: 执行数据计算,修改单个数据项,操纵输出进行行分组,格式化 显示的日期和数字,转换列数据类型。 SQL 函数有输入参数,并且总有一个返回值。 本章中讲述的大多数函数是针对 SQL 的 Oracle 版的。本章没有非 常详细地讨论所有的函数,课程中用简要的说明介绍了最常用的函数。 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 53 3.3 SQL 函数分类 第 三 节 SQl 函数分类 SQL函数分为:单行函数和多行函数 单行函数:这些函数仅对单个行进行运算,并且每行返回一个结果。 有不同类型的单行函数,本章下面的函数类型:字符、数字、日期、 转换 多行函数:这些函数能够操纵成组的行,每个行组给出一个结果, 这些函数也被称为组函数。多行函数在后面的课程中介绍。 本章重点介绍单行函数。 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 54 3.4 单行函数 语法:function_name[(arg1, arg2,...)] 单行函数操纵数据项,接受多个参数并返回一个值,作用于每一个返回行,每行返回一 个结果,可以修改数据类型,可以嵌套,接受多个参数,参数可以是一个列或者一个表达式。 单行函数用于操纵数据项,他们接受一个或多个参数,并且对查询的每个返回行返回 一个值。一个参数可以是下列数据之一: 用户提供的常数、变量值、列名、表达式。 单行函数的特性包括: 作用于查询中返回的每一行、每行返回一个结果、可能返回一个与参数不同类型的数 据值、可能需要一个或多个参数、能够用在SELECT、WHERE和ORDER BY子句中、可以嵌套。 在语法中: function_name 是函数的名字。 arg1, arg2 是由函数使用的任意参数,可以由一个列名或者一个表达式提供。 第 四 节 单行函数  单行函数语法: - 只对一行值进行操作 - 如有多行值,将分别对多行值进行操作,而不 会将多行值作为一个整体进行操作。  单行函数用法  单行函数的参数 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 55 3.5 介绍内容 本章包括下面的单行函数: 字符函数:接受字符输入,可以返回字符或者数字值 数字函数:接受数字输入,返回数字值 日期函数:对 DATE 数据类型的值进行运算 (除了 MONTHS_BETWEEN 函数返回一个 数字,所有日期函数都返回一个 DATE 数据类型的值。) 转换函数:从一个数据类型到另一个数据类型转换一个值 通用函数: NVL NVL2 NULLIF COALSECE CASE DECODE 第 五 节 介绍内容  字符函数 - 对字符型参数进行操作 - 返回结果是字符型或数字型  数字函数  日期函数  转换函数  通用函数 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 56 3.6 DUAL 表介绍 第 六 节 DUAL 表介绍 本章所采用的例子多数是基于 DUAL 表的,因此在介绍本章的函数 之前,介绍一下 DUAL 表。DUAL 表的所有者是用户 SYS,并且可以被所 有的用户访问。它只包含一列,DUMMY,和带有值 X 的一行。当你想要 返回一个值仅一次时,DUAL 表是有用的,例如,常数值、伪列或者不 是来自用户数据表的表达式。DUAL 表通常用于 SELECT 子句语法的完 整,因为不管是 SELECT 还是 FROM 子句都是强制的,并且一些计算不 需要从实际的表中选择。 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 57 3.7 字符函数 字符函数包括:大小写处理函数和字符处理函数 大小写处理函数:LOWER、UPPER、INITCAP 字符处理函数:CONCAT、SUBSTR、LENGTH、INSTR、LPAD|RPAD、TRIM 、REPLACE 3.7.1 大小写处理函数 LOWER(c) 将指定字符串内字符变为小写,支持 CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB,NCLOB 类型 例如: Select Lower('WhaT is tHis') From Dual; UPPER(c) 将指定字符串内字符变为大写,支持 CHAR, VARCHAR2, NCHAR, NVARCHAR2,CLOB,NCLOB 类型 例如: Select Upper('WhaT is tHis') From Dual; INITCAP(c) 将字符串中单词的第一个字母转换为大写,其它则转换为小写 第 七 节 字符函数  大小写处理函数 - 数量较少,常用的只有三个:LOWER(c) 、 UPPER(c)、INITCAP(c) - 作用非常简单,对大小写进行转换处理  字符串处理函数 - 数量很多,且功能强大 - 对字符串进行连接、搜索、 截取等操作 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 58 例如: Select Initcap('whaT is this') From Dual; 3.7.2 字符处理函数 CONCAT(c1,c2) 连接字符串,等同于|| 例如: Select Concat('aa', 'bb') From Dual; SUBSTR(c1,n1[,n2]) 截取指定长度的字符串。稍不注意就可能充满了陷阱的函数。 n1=开始长度; n2=截取的字符串长度,如果为空,默认截取到字符串结尾; 如果 n1=0 then n1=1 如果 n1>0,则 oracle 从左向右确认起始位置截取 例如: Select Substr('What is this', 5, 3) From Dual; 如果 n1<0,则 oracle 从右向左数确认起始位置 例如: Select Substr('What is this', -5, 3) From Dual; 如果 n1>c1.length 则返回空 例如: Select Substr('What is this', 50, 3) From Dual; 然后再请你猜猜,如果 n2<1,会如何返回值呢 LENGTH(c) 返回指定字符串的长度。如果 例如: Select Length('A123 中') From Dual; 猜猜 SELECT LENGTH('') FROM DUAL;的返回值是什么 INSTR(c1,c2[,n1[,n2]]) 返回c2在c1中位置  c1:原字符串  c2:要寻找的字符串  n1:查询起始位置,正值表示从左到右,负值表示从右到左 (大小表示位置,比如3 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 59 表示左面第3处开始,-3表示右面第3处开始)。黑黑,如果为0的话,则返回的也是0  n2:第几个匹配项。大于0 例如: Select Instr('abcdefg', 'e', -3) From Dual; LPAD(c1,n[,c2]) 返回指定长度=n的字符串,需要注意的有几点: 如果nc1.length and c2 is null,以空格从左向右补充字符长度至n并返回; 如果n>c1.length and c2 is not null,以指定字符c2从左向右补充c1长度至n并返回; 例如: Select Lpad('WhaT is tHis', 5), Lpad('WhaT is tHis', 25), Lpad('WhaT is tHis', 25, '-') From Dual; 最后大家再猜一猜,如果n<0,结果会怎么样。 RPAD(c1,n[,c2]) 返回指定长度=n的字符串,基本与上同,不过补充字符是从右向左 方向正好与上相反; 例如: Select Rpad('WhaT is tHis', 5), Rpad('WhaT is tHis', 25), Rpad('WhaT is tHis', 25, '-') From Dual; TRIM([[LEADING||TRAILING||BOTH] c2 FROM] c1) 哈哈,被俺无敌的形容方式搞晕 头了吧,这个地方还是看图更明了一些。 看起来很复杂,理解起来很简单: ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 60 如果没有指定任何参数则 oracle 去除 c1 头尾空格 例如: Select Trim(' WhaT is tHis ') From Dual; 如果指定了 c2 参数,则 oracle 去掉 c1 头尾 c2(这个建议细致测试,有多种不同情形的 哟) 例如: Select Trim('W' From 'WhaT is tHis w W') From Dual; 如果指定了 leading 参数则会去掉 c1 头部 c2 例如: Select Trim(Leading 'W' From 'WhaT is tHis w W') From Dual; 如果指定了 trailing 参数则会去掉 c1 尾部 c2 例如: Select Trim(Trailing 'W' From 'WhaT is tHis w W') From Dual; 如果指定了 both 参数则会去掉 c1 头尾 c2(跟不指定有区别吗?没区别!) 例如: Select Trim(Both 'W' From 'WhaT is tHis w W') From Dual; 注意:c2 长度=1 REPLACE(c1,c2[,c3]) 将 c1 字符串中的 c2 替换为 c3,如果 c3 为空,则从 c1 中删除所 有 c2。 例如: Select Replace('WWhhhhhaT is tHis w W', 'W', '-') From Dual; ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 61 3.8 数字函数 数字函数包括ROUND、TRUNC、MOD。 ROUND(n1[,n2]) 返回四舍五入小数点右边 n2 位后 n1 的值,n2 缺省值为 0,如果 n2 为负数就舍入到小数点左边相应的位上(虽然 oracle documents 上提到 n2 的值必须为整数, 事实上执行时此处的判断并不严谨,即使 n2 为非整数,它也会自动将 n2 取整后做处理,但 是我文档中其它提到必须为整的地方需要特别注意,如果不为整执行时会报错的)。 例如: Select Round(23.56), Round(23.56, 1), Round(23.56, -1) From Dual; TRUNC(n1[,n2] 返回截尾到 n2 位小数的 n1 的值,n2 缺省设置为 0,当 n2 为缺省设置 时会将 n1 截尾为整数,如果 n2 为负值,就截尾在小数点左边相应的位上。 例如: Select Trunc(23.56), Trunc(23.56, 1), Trunc(23.56, -1) From Dual; MOD(n1,n2) 返回 n1 除 n2 的余数,如果 n2=0 则返回 n1 的值。 例如: Select Mod(24, 5) From Dual; 第 八 节 数字函数  对数字进行操作,是加、减、乘、除的补充  ROUND:四舍五入函数。  TRUNC:截断函数,和 Round 的唯一区别是不进行 四舍五入  MOD:求余数操作,很多文献中也称为求模,是除 法操作的很好的补充 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 62 3.9 日期函数 Oracle 日期格式 Oracle数据库以内部数字格式存储日期,表示世纪、年、月、日、小时、分和秒。 对于任何日期默认显示和输入格式是DD-MON-RR。有效的Oracle日期在公元前4712年1月1 日和公元9999年12月31日之间。 完整的日期可能是1994年7月7日 5:10:43 p.m (June 7th, 1994 5:10:43 p.m)。 该日期在内部存储如下: CENTURY YEAR MONTH DAY HOUR MINUTE SECOND 19 94 06 07 5 10 43 Oracle服务器是2000年兼容的。当一个带有日期字段的记录被插入到表中时,世纪信息 可从SYSDATE函数获得,可是,当日期字段显示到屏幕上时,默认情况下世纪部分不显示。 DATE数据类型总是以4位内部数字存储年信息:两位数字代表世纪,两位数字代表年。 例如,Oracle数据库存储年为1996或2001,而不是仅仅存96或01。 第 九 节 日期函数  系统日期 - 每个系统日期都被分为 7 部分进行存储 - 世纪、年份、月、日、小时、分钟、秒  用日期计算 - 日期可以加减少数字 - 两个日期间可以相减  日期函数 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 63 3.9.1 系统日期 CURRENT_DATE 返回当前 session 所在时区的默认时间 例如: SQL> alter session set nls_date_format = 'mm-dd-yyyy' ; SQL> select current_date from dual; SYSDATE 功能与上相同,返回当前 session 所在时区的默认时间。但是需要注意的一 点是,如果同时使用 sysdate 与 current_date 获得的时间不一定相同,某些情况下 current_date 会比 sysdate 快一秒。经过与 xyf_tck(兄台的大作 ORACLE 的工作机制写的很好,深入浅出) 的短暂交流,我们认为 current_date 是将 current_timestamp 中毫秒四舍五入后的返回,虽然 没有找到文档支持,但是想来应该八九不离十。同时,仅是某些情况下会有一秒的误差,一 般情况下并不会对你的操作造成影响,所以了解即可。 例如: Select Sysdate, Current_Date From Dual; 3.9.2 用日期计算 既然数据库以数字方式存储日期,你就可以用算术运算符进行计算,例如,加或减。 你可以加或减数字常数以及日期。从日期加或者减一个数,结果是一个日期值,两个日期相 减,得到两个日期之间的天数,用小时数除以24,可以加小时到日期上。 运算 结果 说明 date + number 日期 加一个天数到一个日期上 date - number 日期 从一个日期上减一个天数 date - date 天数 用一个日期减另一个日期 date + number/24 日期 加一个小时数到一个日期上 Select Last_Name, (Sysdate - Hire_Date) / 7 As Weeks From Employees Where Department_Id = 90; LAST_NAME WEEKS King 1107.516286 Kochhar 989.3734292 De Haan 816.5162864 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 64 3.9.3 日期函数 MONTHS_BETWEEN(d1,d2) 返回 d1 与 d2 间的月份差,视 d1,d2 的值大小,结果可正 可负,当然也有可能为 0 例如: Select Months_Between(Sysdate, Sysdate), Months_Between(Sysdate, Add_Months(Sysdate, -1)), Months_Between(Sysdate, Add_Months(Sysdate, 1)) From Dual; ADD_MONTHS() 返回指定日期月份+n 之后的值,n 可以为任何整数。 例如: Select Add_Months(Sysdate, 12), Add_Months(Sysdate, -12) From Dual; NEXT_DAY(d,n) 返回指定日期后第一个 n 的日期,n 为一周中的某一天。但是,需要 注意的是 n 如果为字符的话,它的星期形式需要与当前 session 默认时区中的星期形式相同。 例如:本文的例子 nls_language 值为 SIMPLIFIED CHINESE Select Next_Day(Sysdate, 5) From Dual; Select Next_Day(Sysdate, '星期四') From Dual; 两种方式都可以取到正确的返回,但是: Select Next_Day(Sysdate, 'Thursday') From Dual; 则会执行出错,提供你说周中的日无效,就是这个原因了。 LAST_DAY(d) 返回指定时间所在月的最后一天 例如: Select Last_Day(Sysdate) From Dual; ROUND(d[,fmt]) 前面讲数值型函数的时候介绍过 ROUND,此处与上功能基本相似, 不过此处操作的是日期。如果不指定 fmt 参数,则默认返回距离指定日期最近的日期。 例如: Select Round(Sysdate, 'HH24') From Dual; ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 65 TRUNC(d[,fmt]) 与前面介绍的数值型TRUNC原理相同,不过此处也是操作的日期型。 例如: Select Trunc(Sysdate, 'HH24') From Dual; 如果遇到英文的日期格式,需要了解月的 3 字母缩写: JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 66 3.10 转换函数 除Oracle数据类型之外,Oracle9i/10g数据库中表的列可以用ANSI、DB2和SQL/DS 数 据类型定义。不管用何种方法,Oracle服务器内部将转换这些数据类型为Oracle数据类型。 在某些情况下,Oracle服务器使用一种数据类型的数据,而在另外一种情况下我们希望 使用一种不同数据类型的数据,如果这种情况发生,Oracle服务器自动转换数据为期望的数 据类型。这种数据类型的转换可以被Oracle服务器隐式进行,或由用户显式进行。 3.10.1 隐式数据类型转换 隐式数据类型转换工作依照下面的规则进行。 显式数据类型转换用转换函数进行。转换函数转换从一种数据类型转换值到另一种数据 类型。通常,函数名的构成遵循 数据类型 到 数据类型 的约定,第一个数据类型是输入数 第 十 节 转换函数  隐式数据类型转换  显示数据类型转换  日期格式末班  时间格式末班  数字格式末班 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 67 据类型;后一个数据类型是输出数据类型。 尽管隐式数据类型转换是可用的,但建议你做显式数据类型转换以确保SQL语句的可靠 性。 对于直接赋值,ORACLE SQL 可以进行以下的隐式转换: 从 到 VARCHAR2 or CHAR NUMBER VARCHAR2 or CHAR DATE NUMBER VARCHAR2 DATE VARCHAR2 对于表达式赋值,ORACLE SQL可以进行以下的隐式转换: 从 到 VARCHAR2 or CHAR NUMBER VARCHAR2 or CHAR DATE 3.10.2 显式数据类型转换 SQL 提供三种函数来从一种数据类型转换值到另一种:TO_CHAR、TO_NUMBER、 TO_DATE。 TO_CHAR() 函数又可以分三小类,分别是 转换字符->字符 TO_CHAR(c):将 nchar,nvarchar2,clob,nclob 类型转换为 char 类型; 例如: Select To_Char('AABBCC') From Dual; 转 换 时 间 -> 字符 TO_CHAR(d[,fmt]) : 将 指 定 的 时 间 (data, timestamp, timestamp with time zone)按照指定格式转换为 varchar2 类型; 必须加单引号,并且区分大小写,能够包含任一有效的日期格式元素,有一个 fm 元素 用来删除填补的空,或者前导零,用一个逗号与日期值分开。 例如: Select To_Char(Sysdate, 'yyyy-mm-dd hh24:mi:ss') From Dual; 转换数值->字符 TO_CHAR(n[,fmt]):将指定数值 n 按照指定格式 fmt 转换为 varchar2 类型并返回; 例如: Select To_Char(-100, 'L99G999D99MI') From Dual; ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 68 TO_DATE(c[,fmt[,nls]]) 将 char,nchar,varchar2,nvarchar2 转换为日期类型,如果 fmt 参数 不为空,则按照 fmt 中指定格式进行转换。注意这里的 fmt 参数。如果 ftm 为'J'则表示按照 公元制(Julian day)转换,c 则必须为大于 0 并小于 5373484 的正整数。 例如: Select To_Date(2454336, 'J') From Dual; Select To_Date('2007-8-23 23:25:00', 'yyyy-mm-dd hh24:mi:ss') From Dual; TO_NUMBER(c[,fmt[,nls]]) 将 char,nchar,varchar2,nvarchar2 型字串按照 fmt 中指定格式 转换为数值类型并返回。 例如: Select To_Number('-100.00', '9G999D99') From Dual; 3.10.3 日期格式模板 YYYY 数字全写年 YEAR 年的拼写 MM 月的两数字值 MONTH 月的全名 MON 月的三字母缩写 DY 周中天的三字母缩写 DAY 周中天的全名 DD 月的数字天 3.10.4 时间格式模板 元素 说明 AM 或 PM 正午指示 A.M.或 P.M. 带句点的正午指示 HH 或 HH12 或 HH24 天的小时,或小时(1–12),或小时(0–23) MI 分钟 (0–59) SS 秒 (0–59) SSSSS 午夜之后的秒 (0–86399) ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 69 3.10.5 数字格式模板 9 表示一个数 0 强制显示为零 $ 放置一个浮动美元符号 L 使用浮动本地货币符号 . 打印一个小数点 , 打印一个千位指示 MI 右边减号 (负值) PR 将负数加上括号 EEEE 科学计数法 (格式化必须指定四个 E) V 乘 10,n 次 (n = V 后面 9 的个数) B 将 0 显示为空格 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 70 3.11 嵌套函数 Select Last_Name, Nvl(To_Char(Manager_Id), 'No Manager') From Employees Where Manager_Id Is Null; 第 十一 节 嵌套函数 单行函数能够被嵌套任意层次,嵌套函数的计算是 从最里层到最外层。单行函数可以嵌套任意深度。嵌套 的函数按从最里层到最外层的顺序求值。下面的一些例 子显示展示了这些函数的灵活性。 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 71 3.12 通用函数 这些函数可用于任意数据类型,并且适用于空值, NVL (expr1, expr2),NVL2 (expr1, expr2, expr3),NULLIF (expr1, expr2),COALESCE (expr1, expr2, ..., exprn) NVL(c1,c2) 逻辑等价于 IF c1 is null THEN c2 ELSE c1 END。c1,c2 可以是任何类型。如 果两者类型不同,则 oracle 会自动将 c2 转换为 c1 的类型。 例如: Select Nvl(Null, '12') From Dual; NVL2(c1,c2,c3) 大家可能都用到 nvl,但你用过 nvl2 吗?如果 c1 非空则返回 c2,如果 c1 为空则返回 c3 例如: Select Nvl2('a', 'b', 'c') , Nvl2(Null, 'b', 'c') From Dual; 第 十二 节 通用函数 通用函数并不针对某种具体的数据类型,它们可以 几乎所有的数据类型进行操作,因此称为通用函数, 常用的有:  NVL、NVL2  NULLIF  COALESCE ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 72 NULLIF(c1,c2) Nullif 也是个很有意思的函数。逻辑等价于:CASE WHEN c1 = c2 THEN NULL ELSE c1 END 例如: SELECT NULLIF('a','b'),NULLIF('a','a') FROM DUAL; COALESCE(n1,n2,....n) 返回序列中的第一个非空值 例如: Select Coalesce(Null, 5, 6, Null, 9) From Dual; ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 73 3.13 条件表达式 在SQL 语句中提供IF-THEN-ELSE逻辑的使用,两种用法:CASE表达式,DECODE函数。CASE 表达式与 ANSI SQL 兼容;DECODE 是特殊的 Oracle 语法。 3.13.1 CASE 表达式 CASE 表达式的语法: ENDCASE expr WHEN comparison_expr1THEN return_expr1 [WHEN comparison_expr2 THENreturn_expr2 WHEN comparison_exprn THEN return_exprn ELSE else_expr] END CASE表达式可以让你在SQL语句中使用IF-THEN-ELSE逻辑,而不必调用过程。 在简 单的 CASE 表达式中,Oracle 查找每一个 WHEN ... THEN 对,如果 expr 等于 第 十三 节 条件表达式  常用的条件表达式有 CASE 表达式、DECODE 函 数  CASE 是 SQL 标准协会规定的条件表达式,每种 数据库中都提供 CASE,但各自的使用可能会略有 差异。  DECODE 是 Oracle 独有的条件表达式,功能和 CASE 类似。 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 74 comparison_expr ,返回相应的 return_expr。如果没有 WHEN ... THEN 对满足条件,并且 ELSE 子句存在,Oracle 返回 else_expr。否则,Oracle 返回 null。你不能对所有的 return_exprs 和 else_expr 指定文字 NULL。 所有的表达式 ( expr、comparison_expr 和 return_expr) 必须是相同的数据类型,可以 是 CHAR、VARCHAR2、NCHAR 或 NVARCHAR2。 还有一种 CASE 查找表达式,Oracle 从左到右查找直到找到一个为真的条件,然后返 回 return_expr。如果没有找到为真的条件,而且 ELSE 子句存在,Oracle 返回 else_expr。 否则 Oracle 返回空。 例子: 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; LAST_NAME JOB_ID SALARY REVISED_SALARY King AD_PRES 24000 24000 Kochhar AD_VP 17000 17000 De Haan AD_VP 17000 17000 Hunold IT_PROG 9000 9900 Ernst IT_PROG 6000 6600 Austin IT_PROG 4800 5280 Pataballa IT_PROG 4800 5280 Lorentz IT_PROG 4200 4620 Greenberg FI_MGR 12000 12000 3.13.2 DECODE 函数 DECODE 函数以一种类似于在多种语言中使用的 IF-THEN-ELSE 逻辑的方法解码一 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 75 个表达式。 DECODE 函数在比较表达式 (expression) 和每个查找 (search) 值后解码表达 式,如果表达式与查找相同,返回结果。 如果省略默认值,当没有查找值与表达式相匹配 时返回一个空值。 语法: DECODE(exp,s1,r1,s2,r2..s,r[,def]) 可以把它理解成一个增强型的 if else,只不过它并不 通过多行语句,而是在一个函数内实现 if else 的功能。 exp 做为初始参数。s 做为对比值,相同则返回 r,如果 s 有多个,则持续遍历所有 s, 直到某个条件为真为止,否则返回默认值 def(如果指定了的话),如果没有默认值,并且前 面的对比也都没有为真,则返回空。 毫无疑问,decode 是个非常重要的函数,在实现行转列等功能时都会用到,需要牢记 和熟练使用。 例如: Select Decode('a2', 'a1', 'true1', 'a2', 'true2', 'default') From Dual; 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; LAST_NAME JOB_ID SALARY REVISED_SALARY King AD_PRES 24000 24000 Kochhar AD_VP 17000 17000 De Haan AD_VP 17000 17000 Hunold IT_PROG 9000 9900 Ernst IT_PROG 6000 6600 Austin IT_PROG 4800 5280 Pataballa IT_PROG 4800 5280 Lorentz IT_PROG 4200 4620 Greenberg FI_MGR 12000 12000 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 76 Faviet FI_ACCOUNT 9000 9000 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; LAST_NAME SALARY TAX_RATE Russell 14000 0.45 Partners 13500 0.44 Errazuriz 12000 0.44 Cambrault 11000 0.42 Zlotkey 10500 0.42 Tucker 10000 0.42 Bernstein 9500 0.4 Hall 9000 0.4 Olsen 8000 0.4 Cambrault 7500 0.3 Tuvault 7000 0.3 King 10000 0.42 3.14 总结 在本章中, 您应该已经学会如何: 用函数执行对数据的计算,用函数修饰不同的数据项,用函数操纵行组的输出,用函数 改变数据格式,用函数转换列数据类型,使用 NVL 函数,使用 IF-THEN-ELSE 逻辑。 单行函数可以被嵌套任意级别,单行函数可以进行下面的操作: ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 77 字符数据:LOWER、UPPER、INITCAP、CONCAT、SUBSTR、INSTR、LENGTH。 数字数据:ROUND、TRUNC、MOD。 日期数据:MONTHS_BETWEEN、ADD_MONTHS、NEXT_DAY、LAST_DAY、ROUND、TRUNC。 日期值也可以用算术运算。 转换函数可以转换字符、日期和数字值:TO_CHAR、TO_DATE、TO_NUMBER。 有几个函数处理空值,它们包括 NVL、NVL2、NULLIF 和 COALESCE。 IF-THEN-ELSE 逻辑可以在 SQL 语句中用 CASE 表达式或 DECODE 函数来代替。 SYSDATE 和 DUAL:SYSDATE 是一个日期函数,它返回当前日期和时间。从一个 称为 DUAL 的虚拟表中选择 SYSDATE 是惯例。 第 4 章 从多表中查询数据 第 四 章 从多表中查询数据 在很多时候一个表的数据已经无法满足我们对查询 和统计的要求,这个时候就需要从不同的表、视图中选 择数据,那么我们就需要从多表中选择数据。 多表选择数据可以是两个表或者视图,也可以是两 个以上的表或者视图。 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 78 4.1 从多表中查询数据 首先我们看看下面的数据: Select e.Employee_Id, e.Last_Name, e.Department_Id From Employees e; EMPLOYEE_ID LAST_NAME DEPARTMENT_ID 100 King 90 101 Kochhar 90 102 De Haan 90 103 Hunold 60 104 Ernst 60 105 Austin 60 106 Pataballa 60 107 Lorentz 60 108 Greenberg 100 109 Faviet 100 110 Chen 100 111 Sciarra 100 Select d.Department_Id, d.Department_Name, d.Location_Id From Departments d; DEPARTMENT_ID DEPARTMENT_NAME LOCATION_ID 第 一 节 从多表中查询数据  从多表查询数据也被称为多表连接  连接查询的目的是为用户提供现详细的数据  有各种各样的连接方式,将在本章中讲述  连接是数据库中常用的查询数据的方法 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 79 10 Administration 1700 20 Marketing 1800 30 Purchasing 1700 40 Human Resources 2400 50 Shipping 1500 60 IT 1400 70 Public Relations 2700 80 Sales 2500 90 Executive 1700 100 Finance 1700 110 Accounting 1700 我们需要查询来自多表的数据。上面的数据显示了来自单独的两个表的数据。 Employee ID 在 EMPLOYEES 表中。 Department ID 在 EMPLOYEES 和 DEPARTMENTS 表中都有。 Location IDs 在 DEPARTMENTS 表中。 为达到需求,我们需要使用 Department ID 连接 EMPLOYEES 和 DEPARTMENTS 表, 并从两个表中访问数据。如下面的例子: Select e.Employee_Id, e.Last_Name, d.Department_Id, d.Department_Name From Employees e, Departments d Where e.Department_Id = d.Department_Id; EMPLOYEE_ID LAST_NAME DEPARTMENT_ID DEPARTMENT_NAME 100 King 90 Executive 101 Kochhar 90 Executive 102 De Haan 90 Executive 103 Hunold 60 IT 104 Ernst 60 IT 105 Austin 60 IT 106 Pataballa 60 IT 107 Lorentz 60 IT 108 Greenberg 100 Finance 109 Faviet 100 Finance 110 Chen 100 Finance 111 Sciarra 100 Finance 112 Urman 100 Finance 113 Popp 100 Finance 114 Raphaely 30 Purchasing ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 80 4.2 有效连接条件与笛卡儿积 当一个连接条件无效或被遗漏时,其结果是一个笛卡尔乘积 (Cartesian product),其中 所有行的组合都被显示。第一个表中的所有行连接到第二个表中的所有行。 一个笛卡尔乘 积会产生大量的行,其结果没有什么用。你应该在 WHERE 子句中始终包含一个有效的连 接条件,除非你有特殊的需求,需要从所有表中组合所有的行。 对于一些测试笛卡尔乘积是有用的,例如你需要产生大量的行来模拟一个相当大的数据 量。 如下面的例子,笛卡儿积的结果对于大多数的查询来说是不需要的。 Select e.Employee_Id, e.Last_Name, e.Department_Id From Employees e; …… 107 行 Select d.Department_Id, d.Department_Name, d.Location_Id From Departments d; …… 27 行 Select * From Departments, Employees; …… 第二节 有效连接条件与笛卡儿积 当一个连接条件无效或被遗漏时,其结果是一个笛 卡尔乘积 (Cartesian product),其中所有行的组合都 被显示。第一个表中的所有行连接到第二个表中的所有 行。 一个笛卡尔乘积会产生大量的行,其结果没有什么 用。你应该在 WHERE 子句中始终包含一个有效的连接条 件,除非你有特殊的需求,需要从所有表中组合所有的 行。 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 81 2889 行 上面的例子中因为没有 WHERE 条件,表 Employees(107 行)与表 Departments (27 行) 连接,因此产生 2889 行的输出。 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 82 4.3 连接的类型 第三节 连接的类型 ORACLE SQL 的连接类型有下面几种:EQUJION 等值 连接,NON-EQUJION 非等值连接,Outer join 外连接, Self join 自连接。外连接分为:左外连接,右外连接, 全外连接。 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 83 4.4 表连接的原则 当数据从多表中查询时,要使用连接 (join) 条件。一个表中的行按照存在于相应列中 的公值被连接到另一个表中的行,即,通常所说的主键和外键。从多个表中显示数据,在 WHERE 子句中写一个简单的连接条件。 表连接原则: 在写一个连接表的 SELECT 语句时,在列名前面用表名可以使语义清楚,并且加快数 据库访问。如果相同的列名出现在多个表中,列名必须前缀表名。 为了连接 n 个表在一起,你最少需要 n-1 个连接条件。例如,为了连接 4 个表,最 少需要 3 个连接条件。如果表中有一个连接主键,该规则可能不适用,其中可能有多行用 来唯一地标识每一行。 第四节 表连接的原则 在写一个连接表的 SELECT 语句时,在列名前面用表名可以使语 义清楚,并且加快数据库访问。如果相同的列名出现在多个表中,列 名必须前缀表名。 为了连接 n 个表在一起,你最少需要 n-1 个连接条件。例如, 为了连接 4 个表,最少需要 3 个连接条件。如果表中有一个连接主 键,该规则可能不适用,其中可能有多行用来唯一地标识每一行。 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 84 4.5 等值连接 为了确定一个雇员的部门名,需要比较 EMPLOYEES 表中的 DEPARTMENT_ID 列 与 DEPARTMENTS 表 中 的 DEPARTMENT_ID 列 的 值 。 在 EMPLOYEES 和 DEPARTMENTS 表之间的关系是一个相等 (equijoin) 关 系 , 即 , 两 个 表 中 DEPARTMENT_ID 列的值必须相等。通常,这种连接类型包括主键和外键。 如下面的例子就是等值连接: 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; EMPLOYEE_ID LAST_NAME DEPARTMENT_ID DEPARTMENT_ID LOCATION_ID 100 King 90 90 1700 101 Kochhar 90 90 1700 102 De Haan 90 90 1700 第五节 等值连接  等值连接是使用最多的一种连接方式。  连接条件是两个表的某些列相等 可以把等值连接想像成本来是一个大表,将其拆分为 两个子表,并且两个子表都包含大表的某一列(也可能 是多列),而等值连接就是将两个子表重新组合为一个大 表。 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 85 103 Hunold 60 60 1400 104 Ernst 60 60 1400 105 Austin 60 60 1400 106 Pataballa 60 60 1400 107 Lorentz 60 60 1400 108 Greenberg 100 100 1700 109 Faviet 100 100 1700 …… 106 行 也可以写成如下的写法: Select Employee_Id, Last_Name, Employees.Department_Id, Departments.Department_Id, Location_Id From Employees, Departments Where Employees.Department_Id = Departments.Department_Id; 因为 Employee_Id,Last_Name 属于 Employees 表,而 Location_Id 属于 Departments, 因此这三个字段是可以不以表作为前缀的,而 Department_Id 在两个表中都有,因此必须以 表名作为前缀,否则会报错。下面的语句是不符合语法的: Select Employee_Id, Last_Name, Department_Id, Department_Id, Location_Id From Employees, Departments Where Employees.Department_Id = Departments.Department_Id; 除了使用必须的等值条件之外,还可以使用 AND 来附加条件,如下例: Select Employee_Id, Last_Name, Employees.Department_Id, Departments.Department_Id, Location_Id From Employees, Departments Where Employees.Department_Id = Departments.Department_Id And Last_Name Like 'A%'; ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 86 EMPLOYEE_ID LAST_NAME DEPARTMENT_ID DEPARTMENT_ID LOCATION_ID 130 Atkinson 50 50 1500 105 Austin 60 60 1400 174 Abel 80 80 2500 166 Ande 80 80 2500 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 87 4.6 等值连接中的列别名、表别名 限制不明确的列名: 你需要在 WHERE 子句中用表的名字限制列的名字以避免含糊不清。没有表前缀, DEPARTMENT_ID 可能来自 DEPARTMENTS 表,也可能来自 EMPLOYEES 表,这种情 况下需要添加表前缀来执行查询。 如果列名在两个表之间不相同,就不需要限定列。但是,使用表前缀可以改善性能,因 为你确切地告诉 Oracle 服务器在那里找到列。 必须限定不明确的列名也适用于在其它子句中可能引起混淆的那些列,例如 SELECT 子句或 ORDER BY 子句。 表别名: 用表名限制列名可能是非常耗时的,特别是当表名字很长时,你可以使用表别名代替表 名。就象列别名给列另一个名字一样,表别名给表另一个名字。表别名有助于保持 SQL 代 码较小,因此使用的存储器也少。 注意在例子中表的 FROM 子句中怎样定义表别名。表名完全指定,然后跟着别名。 EMPLOYEES 表被给予别名 e,DEPARTMENTS 表被给予别名 d。 第 六 节 等值连接中的列别名、表别名  列别名 - 如果连接的两个(或两个以上)表中有名字相 同的列,那么此列的形式是“表名.列名” - 对于同名列表名不可以省略  列别名和表别名使用的原则 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 88 表别名的使用原则: 表别名最多可以有 30 个字符,但短一些更好。 如果在 FROM 子句中表别名被用于指定的表,那么在整个 SELECT 语句中都要使用表别 名。 表别名应该是有意义的。 表别名只对当前的 SELECT 语句有效。 不能对不同的表使用相同的表别名,如果使用,则只对使用的第一个表起作用。 表别名如下面的例子: 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; ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 89 4.7 多于两个表的等值连接 为了连接 n 个表,你最少需要 n-1 个连接条件。例如,为了连接 3 个表,最少需要两个 连接。 有时你可能需要连接两个以上的表。例如,为了显示每个雇员的 last name、department name 和 city,你必须连接 EMPLOYEES、DEPARTMENTS 和 LOCATIONS 表。如下例: Select e.Last_Name, d.Department_Name, l.City From Employees e, Departments d, Locations l Where e.Department_Id = d.Department_Id And d.Location_Id = l.Location_Id; LAST_NAME DEPARTMENT_NAME CITY King Executive Seattle Kochhar Executive Seattle De Haan Executive Seattle Hunold IT Southlake Ernst IT Southlake Austin IT Southlake Pataballa IT Southlake Lorentz IT Southlake 第 七 节 多于两个表的等值连接  在三个表以上的连接也被称为多表连接  多表连接和两个表连接是相同的  为了连接 n 个表,你最少需要 n-1 个连接条件  多表连接的目的是组合更多的表,以提供更详细的 信息。 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 90 Greenberg Finance Seattle Faviet Finance Seattle …… 106 行 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 91 4.8 非等值连接 一个非等值连接是一种不同于等值操作的连接条件。 EMPLOYEES 表和 JOBS A 表 之间的关系有一个非等值连接例子。在两个表之间的关系是 EMPLOYEES 表中的 SALARY 列必须是 JOBS 表的 NIN_SALARY 和 MAX_SALARY 列之间的值。使用不同 于等于 (=) 的操作符获得关系。 如下例子: Select e.Last_Name, e.Salary, j.Job_Title From Employees e, Jobs j Where e.Salary Between j.Min_Salary And j.Max_Salary; LAST_NAME SALARY JOB_TITLE 3 月 kle 2200 Stock Clerk 3 月 low 2500 Shipping Clerk 3 月 low 2500 Stock Clerk 3 月 low 2500 Purchasing Clerk 3 月 vins 7200 Stock Manager 3 月 vins 7200 Programmer 3 月 vins 7200 Marketing Representative 3 月 vins 7200 Human Resources Representative 第 八 节 非等值连接  连接条件不是两个表的某列(或某些列)相等,就 是非等值连接  非等值连接使用并不是很多 除连接条件不同外,其余注意事项都和等值连接相同 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 92 3 月 vins 7200 Accountant ……645 行 也可以如下面的写法,与上面是等价的。 Select e.Last_Name, e.Salary, j.Job_Title From Employees e, Jobs j Where e.Salary >= j.Min_Salary And e.salary <= j.Max_Salary; ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 93 4.9 外连接 如果一个行不满足连接条件,该行将不出现在查询结果中。例如,在 EMPLOYEES 和 DEPARTMENTS 表的等值连接条件中,雇员 Grant 不出现,因为在 EMPLOYEES 表中没有她的 department ID 记录。在结果集中有 107 个雇员,你只看得见 106 个记录。 Select e.Last_Name, e.Department_Id, d.Department_Name From Employees e, Departments d Where e.Department_Id = d.Department_Id; 为了看到这个员工的信息,我们需要使用外连接。外连接运算符是加号(+)。你可以用 一个外连接查看那些不满足连接条件的行。 语法如下: 显示 TABLE2 中不满足条件的行。 SELECT table1.column, table2.column FROM table1, table2 WHERE table1.column(+)=table2.column; 第 九 节 外连接  普通的连接被称为内连接。  内连接的特点是如果一个行不满足连接条件,该行 将不出现在查询结果中。  使用外连接,不满足连接条件的行也会在输出结果 中。 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 94 显示 table1 中不满足条件的行。 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 table1.column, table2.column FROM table1 full outer join table2 On (table1.column = table2.column); 左外连接示例: Select e.Last_Name, d.Department_Name From Employees e, Departments d Where e.Department_Id = d.Department_Id(+); 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); LAST_NAME DEPARTMENT_NAME King Executive Popp Finance Urman Finance Sciarra Finance Chen Finance Faviet Finance Greenberg Finance Gietz Accounting Higgins Accounting Grant …… 107 行 结果为:所有员工及对应部门的记录,包括没有对应部门编号 department_id 的员工记 录。 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 95 右外连接示例: Select e.Last_Name, d.Department_Id, d.Department_Name From Employees e, Departments d Where e.Department_Id(+) = d.Department_Id; 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); LAST_NAME DEPARTMENT_ID DEPARTMENT_NAME Hartstein 20 Marketing Fay 20 Marketing Mavris 40 Human Resources Baer 70 Public Relations Higgins 110 Accounting Gietz 110 Accounting NOC Manufacturing Government Sales …… 122 行 结果为:所有员工及对应部门的记录,包括没有任何员工的部门记录。 全外连接示例: 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); LAST_NAME DEPARTMENT_ID DEPARTMENT_NAME Greenberg 100 Finance Gietz 110 Accounting Higgins 110 Accounting Grant NOC Manufacturing Government Sales IT Support Benefits …… 123 行 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 96 结果为:所有员工及对应部门的记录,包括没有对应部门编号 department_id 的员工记 录和没有任何员工的部门记录。 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 97 4.10 自连接 连接一个表到它自己,有时你需要连接一个表到它自己。为了找到每个雇员的经理的名 字,你需要连接 EMPLOYEES 表到它自己,或执行一个自连接。例如,为了找到 Whalen 的 经理的名字,你需要: 在 EMPLOYEES 的 LAST_NAME 列找到 Whalen。 在 MANAGER_ID 列找到 Whalen 的经理号。Whalen 的经理号是 101。 用 EMPLOYEE_ID 101 在 LAST_NAME 列找到经理的名字。Kochhar 的雇员号是 101,所 以 Kochhar 是 Whalen 的经理。 在这个过程中,你要查找表两次,第一次你在 LAST_NAME 列中查找 Whalen 并且在找 到 对应 的 MANAGER_ID 列 的值 101。第 二次 你在 EMPLOYEE_ID 列查 找 101 并且在 LAST_NAME 列找到 Kochhar。每个经理同时也是雇员。 我们先看下面的数据: Select E1.Employee_Id, E1.Last_Name, E1.Manager_Id From Employees E1 第 十 节 自连接  自连接就是连接的两个表均来自于同一个列  自连接是一种使用很少的连接形式  自连接可以是等值或非等值的连接  一个自连接示例: Select w.Last_Name || ' works for ' || m.Last_Name From Employees w, Employees m Where w.Manager_Id = m.Employee_Id; ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 98 EMPLOYEE_ID LAST_NAME MANAGER_ID 100 King 101 Kochhar 100 102 De Haan 100 103 Hunold 102 104 Ernst 103 105 Austin 103 106 Pataballa 103 107 Lorentz 103 108 Greenberg 101 109 Faviet 108 Select E2.Employee_Id, E2.Last_Name From Employees E2 EMPLOYEE_ID LAST_NAME 100 King 101 Kochhar 102 De Haan 103 Hunold 104 Ernst 105 Austin 106 Pataballa 107 Lorentz 108 Greenberg 109 Faviet 自连接示例: Select w.Last_Name || ' works for ' || m.Last_Name From Employees w, Employees m Where w.Manager_Id = m.Employee_Id; W.LAST_NAME||'WORKSFOR'||M.LAS Hartstein works for King Zlotkey works for King Cambrault works for King Errazuriz works for King Partners works for King Russell works for King Mourgos works for King Vollman works for King Kaufling works for King Fripp works for King ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 99 ……106 行 可以给查询结果列起一个别名,方便查看: Select w.Last_Name || ' works for ' || m.Last_Name As Leaderinfo From Employees w, Employees m Where w.Manager_Id = m.Employee_Id; 例子中的连接 EMPLOYEES 表到它自己。为了在 FROM 子句中模拟两个表,对于相同的 表 EMPLOYEES,用两个别名,分别为 w 和 m。 在该例中,WHERE 子句包含的连接意味着 “一个工人的经理号匹配该经理的雇员号”。 在查询的输出中只有 106 行,但在 EMPLOYEES 表中有 107 行。产生这个结果是因为雇员 King,他是总经理,他上面没有经理。 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 100 4.11 SQL 1999 连接语法介绍 4.11.1 交叉连接 交叉连接: Select Last_Name, Department_Name From Employees Cross Join Departments; 等价于笛卡儿积。 4.11.2 自然连接 自然连接: Select Department_Id, Department_Name, Location_Id, City From Departments Natural Join Locations; 等价于等值连接,前提是两个表有相同名称的列。如果没有,则为交叉连接。 如果列名称相同,但是列类型不同,会报错。可以使用 USING 子句来处理这种情况。自 第 十一 节 SQL1999 连接语法介绍  SQL1999 标准,也简称 SQL99,是国际标准组织于 1999 年定义的 SQL 标准,目前所有大、中型数据 库都支持 SQL99。  前几节中的连接语法是 Oracle 独有的,这一节介 绍 Oracle 中 SQL99 的连接语法。 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 101 然连接 (Natural joins) 用具有相匹配的名字和数据类型的所有列来连接表。USING 子句可 以被用来指定那些将被用语一个等值连接的列中的唯一列。在 USING 子句中引用的列不 应该在 SQL 语句的任何地方用表名或表别名限制 (前缀)。 如下: Select l.City, d.Department_Name From Locations l Join Departments d Using (Location_Id) Where Location_Id = 1400; Select e.Employee_Id, e.Last_Name, d.Location_Id From Employees e Join Departments d Using (Department_Id); 4.11.3 ON 子句 对于自然连接的连接条件,基本上是带有相同名字的所有列的等值连接,为了指定任意 条件,或者指定要连接的列,可以使用 ON 子句连接条件从另一个搜索条件中被分开,ON 子句使得代码易懂。 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 子句等价于等值连接和不等值连接。 用 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; 等价于多表连接。 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 102 4.12 总结 有多种方法可以进行表的连接:等值 Equijoins、非等值 Non-equijoins、外 连接 Outer joins、自连接 Self joins、交叉连接 Cross joins、自然连接 Natural joins、全外连接 Full or outer joins。需要注意查询最好不要产生笛卡儿积,性能会非常差。 使用表别名加速数据库的访问。表别名有助于保持 SQL 代码较小,并节省存储器。 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 103 第 5 章 用组函数合计数据 5.1 学习目的 识别可用的组函数,描述组函数的使用,用GROUP BY 子句分组数据,用HAVING 子 句包含或排除分组的行。本章进一步讲解函数。本章的重点是关于获得行聚集的概要信息, 例如平均值。将讨论怎样聚合表中的行到较小的集合中,怎样指定用于行聚合的搜索条件。 第 五 章 用组函数合计数据  组函数的概念及应用  AVG、SUM、MAX、MIN  COUNT  DISTINCT  GROUP BY HAVING  嵌套组函数 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 104 5.2 组函数的概念 组函数操作行集,给出每组的结果。比如某个组的最大值、某个组的最小值、某个组的 平均值、某个组的总行数、某个组的合计、某个组的方差、某个组的标准差…… 语法: SELECT[column,] group_function(column), ... FROM table[WHERE condition] [GROUP BY column] [ORDER BY column]; 使用组函数的原则: DISTINCT 使得函数只考虑不重复的值;ALL 使得函数考虑每个值,包括重复值。默认 值是 ALL ,因此不需要指定。 用于函数的参数的数据类型可以是 CHAR、VARCHAR2、NUMBER 或 DATE。 所有组函数忽略空值。为了用一个值代替空值,用 NVL、NVL2 或 COALESCE 函数。 第二节 组函数的概念  组函数又被称为多行函数  和单行函数不同,组函数将把多行值当作一个整体 进行处理  注意 DISTINCT 对组函数的影响  注意空值对组函数结果的影响 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 105 当使用 GROUP BY 子句时,Oracle 服务器隐式以升序排序结果集。为了覆盖该默认顺 序,DESC 可以被用于 ORDER BY 子句。 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 106 5.3 组函数使用 5.3.1 使用 AVG、SUM、MAX、MIN AVG([distinct|all]n) 求取记录集中的平均值。 例如: Select Col, Avg(Value) From Tmp1 Group By Col Order By Col; SUM([distinct|all] col) 取指定列所在分组序列的值的和并返回。 例如: Select Col, Sum(Value) "Sum Value" From Tmp1 Group By Col; Select Avg(Salary), Max(Salary), Min(Salary), Sum(Salary) From Employees where Job_Id Like '%REP%'; AVG(SALARY) MAX(SALARY) MIN(SALARY) SUM(SALARY) 8272.727273 11500 6000 273000 MAX (col) OVER (analytic_clause) 获取分组序列中的最大值。 第三节 组函数的使用  AVG、SUM、MAX、MIN  使用 COUNT  使用 DISTINCT 关键字  组函数与空值  GROUP BY 子句、HAVING 子句  嵌套组函数 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 107 MIN (col) OVER (analytic_clause) 获取分组序列中的最小值。 例如: Select Col, Max(Value) "Max", Min(Value) "Min" From Tmp1 Group By Col; Select Min(Hire_Date), Max(Hire_Date) From Employees; MIN(HIRE_DATE) MAX(HIRE_DATE) 1987-6-17 0:00 2000-4-21 0:00 5.3.2 使用 COUNT COUNT([distinct|all]col) 该函数返回查询涉及到的记录行数。COUNT 函数有三中格式: COUNT(*)、COUNT(expr)、COUNT(DISTINCT expr) COUNT(*) 返回表中满足 SELECT 语句标准的行数,包括重复行,包括有空值列的行。 如果 WHERE 子句包括在 SELECT 语句中,COUNT(*) 返回满足 WHERE 子句条件的行 数。对比,COUNT(expr) 返回在列中的由 expr 指定的非空值的数。COUNT (DISTINCT expr) 返回在列中的由 expr 指定的唯一的非空值的数。 的例子显示部门 50 中的雇员人数。 例如: Select Col, Count(0) From Tmp1 Group By Col Order By Col; Select Count(Col), Count(Distinct Col) From Tmp1; Select Count(*) From Employees Where Department_Id = 50; COUNT(*) 45 Select Department_Id, Count(*) From Employees Group By Department_Id; DEPARTMENT_ID COUNT(*) 100 6 30 6 1 90 3 20 2 70 1 110 2 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 108 50 45 80 34 40 1 60 5 10 1 5.3.3 使用 DISTINCT 关键字 COUNT(DISTINCT expr) 返回对于表达式expr 非空并且值不相同的行数,使用 DISTINCT 关键字禁止计算在一列中的重复值。显示EMPLOYEES 表中不同部门数的值。 Select Count(Distinct Department_Id) From Employees; COUNT(DISTINCTDEPARTMENT_ID) 11 5.3.4 组函数与空值 所有组函数忽略列中的空值。在下面的例子中,平均值只基于表中的那些 COMMISSION_PCT 列的值有效的行的计算。NVL 函数强制组函数包括空值。在例子中, 平均值被基于所有表中的行来计算,不管 COMMISSION_PCT 列是否为空。 Select Avg(Nvl(Commission_Pct, 0)) From Employees; AVG(NVL(COMMISSION_PCT,0)) 0.072897196 5.3.5 GROUP BY 子句 所有组函数是将表作为一个大的信息组进行处理,有时,你需要将表的信息划分为较小 的组,可以用 GROUP BY 子句实现。你可以用 GROUP BY 子句把表中的行划分为组。然后 你可以用组函数返回每一组的摘要信息。group_by_expression 指定那些用于将行分组的列, 这些列的值作为行分组的依据。 语法: SELECT ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 109 column, group_function(column) FROM Table [WHERE condition] [GROUP BYgroup_by_expression] [ORDER BYcolumn]; 原则 如果在 SELECT 子句中包含了组函数,就不能选择单独的结果,除非单独的列出现在 GROUP BY 子句中。如果你未能在 GROUP BY 子句中包含一个字段列表,你会收到一个错误 信息。 使用 WHERE 子句,你可以在划分行成组以前过滤行。 在 GROUP BY 子句中必须包含列。 在 GROUP BY 子句中你不能用列别名。 默认情况下,行以包含在 GROUP BY 列表中的字段的升序排序。你可以用 ORDER BY 子 句覆盖这个默认值。 Select Department_Id, Avg(Salary) From Employees Group By Department_Id; DEPARTMENT_ID AVG(SALARY) 100 8600 30 4150 7000 90 19333.33 20 9500 70 10000 110 10150 50 3475.556 80 8955.882 40 6500 60 5760 10 4400 当使用 GROUP BY 子句时,确保在 SELECT 列表中的所有没有包括在组函数中的列必须 在 GROUP BY 子句中。分组结果被以分组列隐式排序,可以用 ORDER BY 指定不同的排序 顺序,但只能用组函数或分组列。GROUP BY 列不必在 SELECT 子句中。分组可以包含 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 110 多个列。 例子显示每个部门的部门号和薪水的平均值。下面是包含一个 GROUP BY 子句 SELECT 语句的求值过程: SELECT 子句指定要返回的列: 在 EMPLOYEES 表中的部门号: 你在 GROUP BY 子句中指定分组的所有薪水的平均值 FROM 子句指定数据库必须访问的表:EMPLOYEES 表。 WHERE 子句指定被返回的行。因为无 WHERE 子句默认情况下所有行被返回。 GROUP BY 子句指定行怎样被分组。行用部门号分组,所以 AVG 函数被应用于薪水列, 以计算每个部门的平均薪水。 Select Avg(Salary) From Employees Group By Department_Id; AVG(SALARY) 8600 4150 7000 19333.33 9500 10000 10150 3475.556 8955.882 6500 Select Department_Id Dept_Id, Job_Id, Sum(Salary) From Employees Group By Department_Id, Job_Id; DEPT_ID JOB_ID SUM(SALARY) 110 AC_ACCOUNT 8300 90 AD_VP 34000 50 ST_CLERK 2052-6-30 0:00 80 SA_REP 243500 50 ST_MAN 36400 80 SA_MAN 61000 110 AC_MGR 12000 90 AD_PRES 24000 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 111 60 IT_PROG 28800 100 FI_MGR 12000 30 PU_CLERK 1938-1-20 0:00 50 SH_CLERK 64300 在SELECT 列表中的任何列或表达式(非计算列)必须在GROUP BY 子句中,在GROUP BY 子句中的列或表达式不必在SELECT 列表中。不能使用WHERE 子句来约束分组,可以使用 HAVING 子句来约束分组结果,在WHERE 子句中不能使用组函数作为条件,只能用非计算列 下面的语句是非法的: Select Department_Id, Count(Last_Name) From Employees; 缺少 GROUP BY department_id; Select Department_Id, Avg(Salary) From Employees Where Avg(Salary) > 8000 Group By Department_Id; 5.3.6 HAVING 子句 约束分组结果: HAVING 子句,行被分组,应用组函数,匹配 HAVING 子句的组被显示。 使用 HAVING 子句指定哪个组将被显示,并且进一步基于聚集信息约束分组。 group_condition 限制行分组,返回那些指定条件为 true 的组 当你使用 HAVING 子句时,Oracle 服务器执行下面的步骤: 1. 行被分组。 2. 组函数被用于分组。 3. 匹配 HAVING 子句的标准的组被显示。 HAVING 子句可以优先于 GROUP BY 子句,但建议你先用 GROUP BY 子句,因为 这样更合逻辑。在 HAVING 子句被用于 SELECT 列表的组之前,分组已形成,并且组函 数已被计算。 语法: SELECT column, group_function FROM ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 112 Table [WHERE condition] [GROUP BYgroup_by_expression] [HAVING group_condition] [ORDER BYcolumn]; Select Department_Id, Max(Salary) From Employees Group By Department_Id Having Max(Salary) > 10000; DEPARTMENT_ID MAX(SALARY) 100 12000 30 11000 90 24000 20 13000 110 12000 80 14000 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); JOB_ID PAYROLL PU_CLERK 13900 AD_PRES 24000 IT_PROG 28800 AD_VP 34000 ST_MAN 36400 FI_ACCOUNT 39600 ST_CLERK 55700 SA_MAN 61000 SH_CLERK 64300 5.3.7 嵌套组函数 组函数可以被嵌套两层深度,比如显示最高平均薪水: Select Max(Avg(Salary)) From Employees Group By Department_Id; ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 113 MAX(AVG(SALARY)) 19333.33 5.4 总结 完成本章之后,你应该能完成以下内容:使用组函数COUNT, MAX, MIN, AVG,用GROUP BY 子句写查询,用HAVING 子句写查询 在 SQL 中有 7 个组函数可用: AVG 平均值、COUNT 计数、MAX 最大值、MIN 最小值、SUM 合计、STDDEV 标准差、VARIANCE 方差 你可以用 GROUP BY 子句创建子分组。分组可以用 HAVING 子句约束。 在语句中将 HAVING 和 GROUP BY 子句放在 WHERE 子句的后面。将 ORDER BY 子句放 在最后。 Oracle 服务器按下面的顺序求子句的值: 1. 如果语句包含一个 WHERE 子句,服务器建立侯选行。 2. 服务器确定在 GROUP BY 子句中指定的组。 3. HAVING 子句进一步约束不满足在 HAVING 子句中分组标准的结果分组。 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 114 第 6 章 子查询 6.1 学习目的 完成本章后, 您应当能够执行下列操作:描述字查询能够解决的问题类型,定义子查询, 列出子查询的类型,写单行和多行子查询。你可以在另一个SQL 语句的WHERE子句中编写子 查询以获得基于未知条件值。 第 六 章 子查询  子查询介绍  使用子查询原则  子查询分类  单行子查询  单行子查询错误  多行子查询 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 115 6.2 子查询介绍 假想你想要写一个查询来找出挣钱比 Abel 的薪水还多的人。为了解决这个问题,我们 有两个办法来解决这个问题:第一,可以使用第四章讲的自关联,如果有心的朋友,现在可 以考虑一下如何使用自关联解决这个问题。第二我们可以使用子查询。该查询需要两个步骤: 一个找出 Abel 的收入,第二个查询找出收入高于 Abel 的人。 你可以用组合两个查询的 方法解决这个问题,放置一个查询到另一个查询中。 内查询或子查询向外查询或主查询返回一个值。使用子查询相当于执行两个连续查询并 且用第一个查询的结果作为第二个查询的搜索值。 子查询的语法: SELECT select_list FROM table WHERE expr operator (SELECT select_list FROM table where expr operator); 子查询是一个SELECT 语句,它是嵌在另一个 SELECT 语句中的子句。使用子查询你可 以用简单的语句构建功能强大的语句。当你需要从表中用依赖于表本身的数据选择行时它们 是非常有用的。 第 二 节 子查询介绍  一个查询被嵌套在另一个查询中,就是子子查询  子查询作用巨大且非常常用  子查询在很多情况下可以代替自连接  相比自连接,子查询的语句语法更清晰 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 116 你可以将子查询放在许多的 SQL 子句中,包括: WHERE 子句,HAVING 子句,FROM 子句,CREATE VIEW 语句中、CREATE TABLE 语句、 UPDATE 语句、INSERT 语句的 INTO 子句和 UPDATE 语句的 SET 子句中。 在语法中: operator 包括比较条件,例如 >、= 或 IN。注:比较条件分为两个种类:单行运算符 (>, =, >=, <, <>, <=) 和多行运算符 (IN, ANY, ALL)。 子查询通常涉及一个嵌套的 SELECT、子 SELECT 或内 SELECT 语句。字查询通常执行一 次。并且它的输出被用于完成主或外查询的查询条件。 子查询示例: Select Last_Name,e.employee_id From Employees e Where Salary > (Select Salary From Employees Where Last_Name = 'Abel'); LAST_NAME EMPLOYEE_ID King 100 Kochhar 101 De Haan 102 Greenberg 108 Russell 145 Partners 146 Errazuriz 147 Ozer 168 Hartstein 201 示例中 内查询返回Abel的薪水,外查询利用内查询返回的结果,查询大于该薪水的员 工的id和名字。 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 117 6.3 使用子查询的原则 一个子查询必须放在圆括号中。 将子查询放在比较条件的右边以增加可读性。 Oracle8i 以前的版本中,子查询不包含 ORDER BY 子句。对一个 SELECT 语句只能 用一个 ORDER BY 子句,并且如果指定了它就必须放在主 SELECT 语句的最后。从 Oracle8i 开始,ORDER BY 子句可以使用,并且在进行 Top-N 分析时是必须的。 在子查询中可以使用两种比较条件:单行运算符和多行运算符。 一个子查询可以在有相互关系的子查询中被多次执行 A。学生可能会问可以写多少子查 询, Oracle 服务器没有强制限制子查询的数目;限制只与查询所需的缓冲区大小有关。 第 三 节 使用子查询的原则  一个子查询必须放在括号里  将查询放在比较条件的右边  子查询可以使用的比较条件  子查询可以多次执行 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 118 6.4 子查询分类 单行子查询:从内 SELECT 语句只返回一行的查询 多行子查询:从内 SELECT 语句返回多行的查询 多列子查询:从内 SELECTY 语句返回多列(单行、多行)的查询。 第 四 节 子查询分类  单行子查询:即子查询只返回一行  多行子查询:子查询将返回多行  多列子查询:子查询返回的不至一列。多列子查询 的返回行数可以是单行也可以是多行 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 119 6.5 单行子查询 单行子查询仅返回一行数据,需要使用单行比较符来进行比较。单行比较符如下: = 等于 > 大于 >= 大于等于 < 小于 <= 小于等于 <> 不等于 != 不等于 ^= 不等于 当然单行子查询也可以使用in、any、all子句。大家有兴趣可以测试一下in、any、all 和单行比较符的效率。 单行子查询示例(一个内查询): Select Last_Name, Job_Id From Employees Where Job_Id = (Select Job_Id From Employees Where Employee_Id = 141); LAST_NAME JOB_ID 第 五 节 单行子查询  单行子查询:子查询只返回一行  针对单行子查询的条件和普通条件一样  单行子查询中使用 HAVING  可以把单行子查询想像作一个可变的值。它和一个 单一值没什么区别 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 120 Nayer ST_CLERK Mikkilineni ST_CLERK Landry ST_CLERK 3 月 kle ST_CLERK Bissot ST_CLERK Atkinson ST_CLERK 3 月 low ST_CLERK Olson ST_CLERK Mallin ST_CLERK Rogers ST_CLERK …… 20行 单行子查询示例(两个个内查询): 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); LAST_NAME JOB_ID SALARY Nayer ST_CLERK 3200 Mikkilineni ST_CLERK 2700 Bissot ST_CLERK 3300 Atkinson ST_CLERK 2800 Mallin ST_CLERK 3300 Rogers ST_CLERK 2900 Ladwig ST_CLERK 3600 Stiles ST_CLERK 3200 Seo ST_CLERK 2700 Rajs ST_CLERK 3500 Davies ST_CLERK 3100 子查询中使用函数示例: Select Last_Name, Job_Id, Salary From Employees Where Salary = (Select Min(Salary) From Employees); LAST_NAME JOB_ID SALARY Olson ST_CLERK 2100 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 121 带子查询的HAVING 子句示例: Select Department_Id, Min(Salary) From Employees Group By Department_Id Having Min(Salary) > (Select Min(Salary) From Employees Where Department_Id = 50); DEPARTMENT_ID MIN(SALARY) 100 6900 30 2500 7000 90 17000 20 6000 70 10000 110 8300 80 6100 40 6500 60 4200 上面的SQL语句显示所有其最低薪水小于部门50的最低薪水的部门号和最低薪水。 Select Job_Id, Avg(Salary) From Employees Group By Job_Id Having Avg(Salary) = (Select Min(Avg(Salary)) From Employees Group By Job_Id); 上面的SQL语句找出平均薪水为最低平均薪水的工作岗位。 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 122 6.6 单行子查询错误 使用子查询的一个常见的错误是单行子查询返回了多行。如下例子: Select Employee_Id, Last_Name From Employees Where Salary = (Select Min(Salary) From Employees Group By Department_Id); 子查询结果如下: (Select Min(Salary) From Employees Group By Department_Id); MIN(SALARY) 6900 2500 7000 17000 6000 10000 8300 2100 6100 6500 4200 第 六 节 单行子查询错误 因为针对单行子查询的条件只是普通的条件,没有 特别的形式,因此单行子查询不能返回多行,否则会 报出如下错误: ORA-01427: 单行子查询返回多个行 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 123 4400 对于外查询来说,因为使用比较符,这里只需要一个值,返回多个值的时候,就会报错 “ORA-01427: 单行子查询返回多个行”。这个错误在很多软件开发过程中经常会遇到。如 果是普通的查询,为了查询出结果可以将“=”改为“IN” 如果是系统报错,就要查看原因了。 通常还会产生的一个错误是子查询并没有返回预期的行: Select Last_Name, Job_Id From Employees Where Job_Id = (Select Job_Id From Employees Where Last_Name = 'Haas'); 这种情况,有两种原因:第一就是子查询没有返回值,第二就是子查询返回了值,但是 在外查询中无法查询到。 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 124 6.7 多行子查询 多行子查询返回多行,需要使用多行比较符: IN 等于列表中的任何成员 ANY 比较子查询返回的每个值 ALL 比较子查询返回的全部值 例如,查询公司里面薪水是每个部门最低的员工的姓名、薪水、部门id: Select Last_Name, Salary, Department_Id From Employees Where Salary In (Select Min(Salary) From Employees Group By Department_Id); 该查询等价于下面的查询: Select Last_Name, Salary, Department_Id From Employees Where Salary In (2500, 4200, 4400, 6000, 7000, 8300, 8600, 17000); 在多行子查询中使用ANY、ALL运算符示例: 第 七 节 多行子查询 子查询要想返回多行,需要使用多行比较符:  IN 等于列表中的任何成员  ANY 比较子查询返回的每个值  ALL 比较子查询返回的全部值 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 125 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'; 本例的意思是查询公司中薪水小于任何一个工种为IT_PROG的员工薪水的人的员工号、 姓名、工种、薪水,其实就是小于该工种最大薪水的人。 再看一个例子: 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'; 本例的意思是查询公司中薪水小于所有工种为IT_PROG的员工薪水的人的员工号、姓名、 工种、薪水,其实就是小于该工种最小薪水的人。 再看下面的例子: 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'; 本例的意思是查询公司中薪水大于任何一个工种为IT_PROG的员工薪水的人的员工号、 姓名、工种、薪水,其实就是大于该工种最小薪水的人。 再看一个例子 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'; 本例的意思是查询公司中薪水大于所有工种为IT_PROG的员工薪水的人的员工号、姓名、 工种、薪水,其实就是大于该工种最大薪水的人。 多行子查询也会出现空值的现象,需要注意。如下例子: Select Emp.Last_Name From Employees Emp Where Emp.Employee_Id Not In (Select Mgr.Manager_Id From Employees Mgr); ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 126 6.8 总结 在本章中,你应该已经学会了怎样使用子查询。子查询是一个被嵌在另一个 SQL 语句 中的 SELECT 语句。当一个查询基于一个带有未知中间值的查找标准时,子查询是有用的。 子查询有下面的特性: 能传递一个数据行到包含一个单行运算符的主语句,例如 =, <>, >, >=, <, or <=。 能传递多行数据到包含一个多行运算符的主语句,例如 IN。首先被 Oracle 服务器处 理,并且由 WHERE 或 HAVING 子句使用其结果可以包含组函数。 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 127 第 7 章 操纵数据 7.1 学习目的 完成本章后, 您应当能够执行下列操作:描述每个DML 语句,插入行到表中,更新表中 的行,从表中删除行,合并表中的行,控制事务(COMMIT、SAVEPOINT和ROLLBACK)。 第 七 章 操作数据  数据操作语言  INSERT 插入数据语法、变化  UPDATE 更新数据的语法、例子和问题  DELETE 删除数据的语法、例子和问题  显示默认值  数据库事务、读一致性、锁定 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 128 7.2 数据操纵语言 数据操纵语言 (Data manipulation language DML) 是SQL的一个核心部分。当你想要 添加、更新、合并或者删除数据库中的数据时,你在执行DML语句。DML依据的一个集合构成 了一个被称为事务的逻辑单元。 考虑一个银行数据库,当一个银行客户从一个储蓄存款账户转账到一个经常账户时,事 务可以由三个单独的操作组成:减少存款账户,增加经常账户,在交易帐中记录交易。Oracle 服务器必须保证所有这三个SQL语句被执行以维护账目余额的正确。当由于某种原因阻碍了 交易中一条语句的执行,那么其它的交易语句都必须被撤消。 DML语句能够直接在iSQL*Plus、SQL*Plus、第三方工具中发布,由类似Oracle Forms Services这样的工具自动执行,或者用类似3GL预编译程序编程。 每一个表都有其关联的INSERT、UPDATE和DELETE权限,这些权限被自动地授予表的创建 者,但在通常情况下这些权限必须被明确地授予其它用户。 从Oracle 7.2开始,你能够用一个子查询代替UPDATE语句中的表名,本质上和视图的使 用方式一样。 比如公司某个部门撤销了,需要从部门表中删除该部门,则需要使用delete语句,如果 第 二 节 数据库操作语言  数据库操作语言,Data manipulation language,简 称 DML,主要包括插入、删除、更新。  一条 DML 将开始一个事务,接下来的 DML 都是 同一事务中的语句,直到提交或回滚。  在 DML 中也可以使用子查询。 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 129 该部门名称变化了,需要改名称,则需要使用update语句,如果新增了一个部门则需要使用 insert语句。 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 130 7.3 INSERT 插入数据的语法 INSERT语句的语法: INSERT INTO table [(column [, column...])] VALUES (value [, value...]); 你可以用INSERT语句添加新行到表中。 在语法中: table 是表的名字 column 是表中的列名 value 是列的相应值 注:该语句用VALUES子句添加行到表中,一次仅一行。 因为你能够插入一个包含每个列的新行,因此在INSERT子句中字段列表不是必须的,可 是,如果你不用字段列表,值必须按照表中字段的默认顺序排列,并且必须为每个列提供一 个值。为使语句更清楚,在INSERT子句中使用字段列表。字符和日期值应放在单引号中;数 字值不需要。数字值不应放在单引号中,因为对于指定为NUMBER数据类型的字段,如果使用 了单引号,可能会发生数字值的隐式转换。 第 三 节 INSERT 插入语句的语法  INSERT 语句的语法: INSERT INTO table [(column [, column...])] VALUES (value [, value...]);  在插入时,可以有选择的只提供部分列的值,未在 插入语句中提供值的列,将是空值或由 Defaulte 子句定义的缺省值。 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 131 例如: Insert Into Departments (Department_Id, Department_Name, Manager_Id, Location_Id) Values (70, 'Public Relations', 100, 1700); 对于可以为空的列,我们可以显式的插入空值或者隐式的插入空值: 隐式插入空值: Insert Into Departments (Department_Id, Department_Name) Values (30, 'Purchasing'); 显式插入空值: Insert Into Departments Values (100, 'Finance', Null, Null); INSERT的时候可能出现如下的错误: 对于NOT NULL列缺少强制的值,重复值违反了唯一性约束,违反外键约束,违反CHECK 约束,数据类型不匹配,值的宽度超过了列的限制。 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 132 7.4 INSERT 插入值的变化 7.4.1 插入特殊值 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); 你可以使用函数输入特殊值到表中。 第 四 节 INSERT 插入语句的变化  插入特殊值  插入指定日期  插入可输入变量  从另外一个表复制  在 INSERT 语句中使用子查询 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 133 例子录入雇员Popp的信息到EMPLOYEES表中,在INSERT语句中提供当前日期时间到 HIRE_DATE列中,用SYSDATE函数得到当前日期时间。 在插入行到表中时,你也可以用 USER 函数,USER 函数记录当前用户名。当然我们可 以将 Sysdate 换成其他函数。 7.4.2 插入指定日期 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); 这里 To_Date('FEB 3, 1999', 'MON DD, YYYY')即为指定日期。To_date 是为了满足当前的 日期格式的要求。 7.4.3 插入可输入变量 Insert Into Departments (Department_Id, Department_Name, Location_Id) Values (&Department_Id, '&department_name', &Location); 在执行该语句之后,会提示我们依次输入变量的值,利用输入变量的值运行该脚本,这 样一来,大家就可以多次输入不同的值来执行该语句。大家可以做一下测试。 7.4.4 从另外一个表复制 我们可以用 INSERT 语句添加行到表中,插入的值来自已存在的表,在 VALUES 子句 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 134 的位置用一个子查询。 语法:INSERT INTO table [column (, column) ] subquery; table 是表名 column 是表中的列名 subquery 是返回行的子查询 在 INSERT 子句的字段列表中列的数目和它们的数据类型必须与子查询中的值的数目 及其数据类型相匹配。为了创建一个表的行的拷贝,在子查询中用 SELECT * 。 INSERT INTO copy_emp SELECT * FROM employees; 从另外的一个表复制分两种情况。 一是从相关的一个表或者多个表查询需要插入的数据,如下: Insert Into Sales_Reps (Id, Name, Salary, Commission_Pct) Select Employee_Id, Last_Name, Salary, Commission_Pct From Employees Where Job_Id Like '%REP%'; 第二种是从 DUAL 表查询特定的数据、子查询,取得插入的数据。如下: Insert Into Departments Select 999 'TEST_PART', (Select 400 From Dual), 4422 From Dual; 7.4.5 在 INSERT 语句中使用子查询 我们可以在INSERT语句的INTO子句中用一个子查询代替表名,该子查询的选择列表必 须与VALUES子句的字段列表有相同的字段数,基表的列上的所有规则必须遵循INSERT语 句的顺序。例如,你不可能插入一个重复的employee_id,也不能遗漏强制为非空列的值。 子查询用于为INSERT语句标识所要操作的表。 例如: Insert Into (Select Employee_Id, Last_Name, Email, Hire_Date, Job_Id, Salary, Department_Id From Employees ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 135 Where Department_Id = 50) Values (99999, 'Taylor', 'DTAYLOR', To_Date('07-6 月-1999', 'DD-MON-RR'), 'ST_CLERK', 5000, 50); ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 136 7.5 UPDATE 更新数据的语法 当公司有一名雇员从一个部门内部跳到另外一个部门的时候,我们需要在员工信息表里 面更改该员工的部门编码。这时候我们就需要使用更新数据(UPDATE)单列数据来完成这个操 作。同时,在该员工换部门之后,他的leader也换了,很多朋友该疑惑了,那么我们是不是 需要写两条UPDATE语句来更新呢?完全不用的。我们可以在一条UPDATE语句中同时更新很多 值。 UPDATE的语法: UPDATE table SET column= value[, column = value, ...] [WHERE condition]; table 是表的名字 column 是表中列的名字 value 是相应的值或对应列的子查询 condition 确定要被更新的行,由列名、表达式、常数和比较操作符组成 用查询表来显示受更新的行以确认更新操作。 切记:通常,用主键标识一个单个的行,如果用其他列,可能会出乎意料的引起另一些 第 四 节 UPDATE 更新数据的语法  插入特殊值  插入指定日期  插入可输入变量  从另外一个表复制  在 INSERT 语句中使用子查询 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 137 行被更新。例如,在EMPLOYEES表中用名字标识一个单个的行是危险的,因为不同的雇 员可能有相同的名字。 例如: Update Employees Set Department_Id = 70 Where Employee_Id = 113; 如果写成: Update Employees Set Department_Id = 70; 则会导致所有的数据被更新。切,怎么可能,我怎么可能犯这种错误。不信,走着瞧。 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 138 7.6 UPDATE 更新的例子和问题 7.6.1 用子查询更新两列 语法: UPDATE table SET column = (SELECT column FROM table WHERE condition) [ , column = (SELECT column FROM table WHERE condition)] [WHERE condition ] ; 例如: Update Employees set Job_Id = (Select Job_Id From Employees Where Employee_Id = 205), 第六节 UPDATE 更新的例子和问题  在 Update 中,多处可以使用子查询: - 表名处可以使用子查询 - 新的列值处也可以使用子查询  更新基于另外一个表的行  更新导致完整性约束将导致错误 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 139 Salary = (Select Salary From Employees Where Employee_Id = 205) Where Employee_Id = 114; 7.6.2 更新基于另一个表的行 下面的例子用基于来自EMPLOYEES表的值更新COPY_EMP表,它用employee 100的部 门号;改变所有工作岗位与employee 200的工作岗位相同的那些雇员的部门号。例如: 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); 7.6.3 更新导致完整性约束报错 下面的更新会报错: Update Employees Set Employee_Id = 100 Where Employee_Id = 101; “ORA-00001: 违反唯一约束条件(TEST.EMP_EMP_ID_PK)” 员工号为101的员工是存在的,因此不能更新为该员工号。 下面的更新会报错: Update Employees Set Department_Id = 55 Where Department_Id = 110; “ORA-02291: 违反完整性约束条件(TEST.EMP_DEPT_FK)-未找到父项关键字” 部门号55在父表DEPARTMENTS中不存在,所以,你会收到违反父键约束错误 ORA-02291。 主键是该表中的唯一约束,不能重复,外键是该表中的字段与另外一个表的主键字段名 相同的情况下设置的一种约束,外键约束不满足的时候,数据也无法更新或者插入。 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 140 7.7 DELETE 删除数据的语法 当某个员工离职之后,我们需要从员工表中删除该员工的信息,这时我们需要使用 DELETE来删除数据。 在所有的行用DELETE语句删除后,只有表的数据结构被保留,清空表的另一种更有效的 方法是用TRUNCATE语句。 你可以用TRUNCATE语句快速删除表中所有的行,用TRUNCATE语句删除行比用DELETE语句 更快一些,原因如下: TRUNCATE语句是数据定义语言 (statement is a data definition language DDL) 语 句,不产生回退信息,TRUNCATE语句在子查询课程中讲述。 截断表不触发表的删除触发器。 如果表是引用完整性约束的父表,你不能截断该表,在发布TRUNCATE语句之前先禁用约 束。 语法: DELETE [FROM] table [WHER Econdition]; 第七节 DELETE 删除数据的语法  DELETE,删除语句,此命令可以删除表中的一行或 多行。  如省略 Where 条件子句,将删除表的所有行。和没 有条件的 Update 一样,这也常常是误操作的原因 之一。 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 141 table 是表名 condition 标识被删除的行,由字段名、表达式、常数和比较操作符组成 注:如果没有行被删除,消息 “0 rows deleted.” 被返回。 DELETE语句执行前没有确认提示,但是,删除操作直到数据处理事务被提交之前不会被 持久化,因此,如果你做错了,在提交(commit)之前,可以用ROLLBACK语句撤消删除操作。 但是如果提交了,那只能通过恢复手段来取回删除掉的数据了。 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 142 7.8 DELETE 更新的例子和问题 7.8.1 从表中删除行 Delete From Departments Where Department_Name = 'Finance'; Delete From Employees Where Employee_Id = 114; Delete From Departments Where Department_Id In (30, 40); 上面的三个语句可以正常的从数据库中删除数据。 在DELETE语句中你可以用WHERE子句删除指定的行。上面的例子从DEPARTMENTS 表中删 除Finance (财务) 部门。你可以用SELECT语句显示被删除的行以确认删除操作。 SELECT * FROM departments WHERE department_name = 'Finance'; 如果你遗漏了WHERE子句,在表中的所有行都被删除。下面的例子从COPY_EMP表中删除 所有的行,因为没有指定WHERE子句。 Delete From Copy_Emp; 第八节 DELETE 更新的例子和问题  删除的条件中可以使用子查询,子查询可以是针对 任意表的,可以不是被删除行的表。  注意删除行将可能导致完整性约束报错: “ORA-02292:违反完整性约束条件„„ ” ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 143 切记:在提交 DELETE 之前,多看一眼,自己到底有没有加条件,条件是否是唯一约 束,是否真的是要删除条件约束的数据。 7.8.2 删除基于另一个表的行 下面的例子删除基于另外一个表的行: Delete From Employees Where Department_Id = (Select Department_Id From Departments Where Department_Name Like '%Public%'); 7.8.3 删除数据导致的完整性约束 下面的删除语句会报错: DELETE FROM departments WHERE department_id = 60; “ORA-02292:违反完整性约束条件(TEST.EMP_DEPT_FK)- 已找到记录” 不能删除包含主键的行,该主键被用做另一个表的外键。 如果你尝试删除一条记录,该记录中的值依赖一个完整性约束,一个错误被返回。上面 例子试图从DEPARTMENTS表中删除部门号60,但执行该语句将返回一个错误,因为部门号在 EMPLOYEES表中被用做外键。如果你试图删除的父记录有子记录,那么,你将收到ORA-02292。 下面的语句可以正常工作,因为在部门70中没有雇员: DELETE FROM departments WHERE department_id = 70; 如果使用了引用完整性约束,当你试图删除一行时,你可能收到一个Oracle服务器错误 信息。但是,如果引用完整性约束包含了ON DELETE CASCADE选项,那么,可以删除行,并 且所有相关的子表记录都被删除。 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 144 7.9 显式默认值 示例: Insert Into Departments (Department_Id, Department_Name, Manager_Id) Values (300, 'Engineering', Default); Update Departments Set Manager_Id = Default Where Department_Id = 10; 第九节 显式默认值 显式默认特性,我们能够用 DEFAULT 关键字作为一 个列值,该列要求一个默认值,该特性允许用户控制在 什么地方和什么时候默认值应该被提供给数据,DEFAULT 关键字可以被用于 INSERT 和 UPDATE 语句来确定默认的 列值。如果不存在默认值,将使用空值。 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 145 7.10 MERGE 合并语句语法 SQL的扩展包括了MERGE语句,使用该语句,你可以有条件地更新或插入行到表中, 这样能避免多重UPDATE语句。是执行对目的表的更新操作还是执行对目的表的插入操作, 取决于基于ON子句中的条件。 由于MERGE命令组合了INSERT和UPDATE命令,你需要有对目的表的INSERT和 UPDATE权限,以及对源表的SELECT权限。 MERGE语句确定性的。在同一个MERGE语句中,你不能多次更新目的表中相同的行。 一个可供选择近似方法是用PL/SQL循环和多重DML语句,然而,MERGE语句易于使 用,并且作为一条单个的SQL语句会更简单。 MERGE语句在许多数据仓库应用中是适用的。例如,在一个数据仓库应用程序中,你 可能需要用来自多个源的数据工作,其中的一些可能是完全相同的。用MERGE语句,你可 以有条件地添加或修改行。 MERGE有以下特点:避免分散更新,增进性能和易用性,在数据仓库应用中有用。 MERGE的语法: MERGE INTO table_nametable_alias 第十节 MERGE 合并语句语法  MERGE 语句,可以称其为“融合”语句。  它相当于插入与更新语句的综合。  通常是对两个表进行操作  要在语句中提供类似连接那样的条件 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 146 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); INTO子句 指定你正在更新或插入的目的表。 USING子句 指定数据源要被更新或插入的数据的源;可以是一个表、视图或者字查询。 ON 子句 是一个条件,在此条件上MERGE操作即可以更新也可以插入。 WHEN MATCHED | 通知服务器怎样响应连接条件的结果。 WHEN NOT MATCHED 合并行的例子: 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, ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 147 e.Salary, e.Commission_Pct, e.Manager_Id, e.Department_Id); 该例子显示匹配COPY_EMP表中的EMPLOYEE_ID列与EMPLOYEES表中的 EMPLOYEE_ID列。如果找到了一个匹配,用EMPLOYEES表中匹配行的列值更新 COPY_EMP表中匹配的列值。如果相匹配行没有找到,EMPLOYEES表中的列值被插入到 COPY_EMP表中。 在数据仓库环境中,你可能有一个大的事实表和一个较小维数的表,小表中的行需要有 条件地插入到大的事实表中。在这种情况下,MERGE语句是有用的。 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 148 7.11 数据库事务处理 Oracle服务器基于事务处理确保数据的一致性。在改变数据时,事务给你更多的灵活性 和可控性,如果用户程序失败或者系统失败,事务可以确保数据的一致性。 事务由包装成一个整体更改数据的 DML 语句组成,例如,一个在两个帐号之间的解款业务 应该包括相同数量的一个借方帐目和一个贷方帐目,这两个动作应该同时失败或者同时成功; 即没有记入借方,贷方不应该提交。 数据库事务处理由下面的语句组成: DML 语句:数据操纵语言 (Data manipulation language DML)。 DDL 语句:数据定义语言 (Data definition language DDL)。 DCL 语句:数据控制语言 (Data control language DCL)。 7.11.1 数据库事务开始与结束 什么时候开始和结束一个事务? 当遇到第一个 DML 语句一个事务开始。 第十一节 数据库事务处理  数据库事务的开始与结束  COMMIT 和 ROLLBACK 语句  事务控制  事务的控制示例  隐式事务处理 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 149 当下面的情况发生时,事务结束: 一个 COMMIT 或 ROLLBACK 语句被发布 一个 DDL 语句,例如,CREATE 被发布一个 DCL 语句被发布 用户推出 SQL*Plus 工具。 机器失效或者系统崩溃 在一个事务结束以后,下一个可执行的 SQL 语句自动开始下一个事务。一个 DDL 语句 或者一个 DCL 语句自动提交,并且因此一个事务隐式结束。 7.11.2 COMMIT 和 ROLLBACK 语句 用 COMMIT 和 ROLLBACK 语句,你能够:确保数据的一致性,在数据永久改变之前 进行预览,分组逻辑相关的操作。 在事务中所做的每一个数据改变在事务被提交之前都是临时的。 COMMIT 或 ROLLBACK 之前数据的状态,数据操纵操作首先影响数据库缓冲区,因 此,数据以前的状态可以被恢复。 当前用户可以查询表观察到数据操纵操作的结果。 其他用户不能观察到当前用户所做的数据操纵操作的结果。Oracle 服务器用读一致性来 确保每个用户看到的数据和上次提交时相同。 受影响的行被锁定;其他用户不能改变受影响的行中的数据。 就 Oracle 服务器来说,数据的改变在事务被提交之前可能实际上已被写入数据库文件, 但他们仍然是临时的。 如果许多用户同时对相同的表作了修改,那么,直到用户提交他们的修改之前,每个用 户只能看到他自己的修改。 默认情况下,Oracle 服务器有行级 (row-level locking)。改变默认的锁机制是可能的。 用 COMMIT 语句使得未决的改变永久化,在 COMMIT 语句执行后: 数据的改变被写到数据库中。数据以前状态永久地丢失。所有用户都可以观察到事务的 结果。受影响的行上的所被释放;其他用户现在可以对行进行新的数据改变。所有保存点被 释放。 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 150 用 ROLLBACK 语句放弃所有未决的改变,在一个 ROLLBACK 语句执行后: 数据的改变被还原。数据以前的状态被恢复。受影响的行上的锁被释放。 如果一个语句的执行错误被发现,一个事务的一部分可以用隐式的回退丢弃。如果一个 单个的 DML 语句在一个事务的执行期间失败,它的影响被一个语句级的回退撤消,但在事 务中的以前已经由 DML 语句完成的改变不能丢弃,他们可以由用户显示地提交或回滚。 Oracle 在任何数据定义语言 (data definition language DDL) 语句之前和之后发布一个隐 式的提交。所以,即使你的 DDL 语句执行不成功,你也不能回退前面的语句,因为服务器 已经发布了提交命令。 执行 COMMIT 或 ROLLBACK 语句来明确地结束事务。 Oracle 服务器在数据上实现锁以防止对数据库中数据的并发操作,当某些事件发生时 (例如系统故障) 或当事务完成时,那些锁被释放。当一个 DML 语句成功执行时,数据库上 的隐式锁被获得,默认情况下,Oracle 服务器在尽可能的最低级别锁定数据。 执行带 FOR UPDATE 子句的 LOCK TABLE 语句或 SELECT 语句可以手动获得数据库 表上的锁。从 Oracle9i 开始,DBA 有管理回退段的选择,或让 Oracle 自动管理在回退表空 间中的回退数据。 例子: DELETE FROM employeesWHERE employee_id = 99999; 1 row deleted. INSERT INTO departments VALUES (290, 'Corporate Tax', NULL, 1700); 1 row inserted. COMMIT; Commit complete. DELETE FROM copy_emp; 22 rows deleted. ROLLBACK; Rollback complete. 7.11.3 事务的控制 显式控制语句: ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 151 语句 说明 COMMIT 结束当前事务,使得所有未决的数据永久改变。 SAVEPOINT name 在当前事务中标记保存点。 ROLLBACK ROLLBACK 结束当前事务,丢弃所有未决的数据改变。 ROLLBACK TO SAVEPOINT name 回滚当前事务到指定的保存点,从而丢弃保存点创建 后的任何改变。如果忽略了 TO SAVEPOINT 子句, ROLLBACK 语句回滚整个事务。由于保存点是逻辑的, 因此,没有办法列出已经创建的保存点。 7.11.4 事务的控制示例 你能够在当前事务中用SAVEPOINT语句创建一个标记,它把事务分为较小的部分。你 可以用ROLLBACK TO SAVEPOINT语句丢弃未决的改变到该标记。 如果你用与前面的保存点相同的名字创建了另一个保存点,哪个早一点时间创建的保存 点就被删除了。 回退到一个标记:用 SAVEPOINT 语句在当前事务中创建一个标记,用 ROLLBACK TOSAVEPOINT 语句回退到该标记,如下: UPDATE...SAVEPOINT update_done; INSERT... ROLLBACK TO update_done; 7.11.5 隐式事务处理 在下面的情况下,一个自动提交发生:DDL 语句被发送,DCL 语句被发送,正常退出 iSQL*Plus,没有明确地发送 COMMIT 或 ROLLBACK 语句。 当 iSQL*Plus 非正常退出时,或者发生系统故障时,一个自动回退发生。 当一个事务被系统故障中断时,整个事务被自动回滚。该回滚防止不必要的数据改变错 误发生,并且返回表到他们上一次提交时的状态,以这种方式,Oracle 服务器保护表的完整 性。 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 152 7.12 读一致性 数据库用户用两种方法访问数据库:读操作 (SELECT 语句),写操作 (插入、更新、 删除 语句)。 数据库读一致性的作用是: 数据库读者和写者被确保对数据观察的一致性。读者不能观察正在变化过程中的数据。 写者被确保对数据库的改变以一致的方式进行。一个写者所做的改变不破坏另一个写者所做 的改变或与其冲突。 读一致性的目的是确保每个用户看到的数据和他最后一次提交,并且在一个 DML 操作 开始之前的数据一样。 第十二节 读一致性  用户访问数据库的方式  读一致性的作用  读一致性的实现  读一致性示例 一致读是 Oracle 重要的特性,它使得了读、写互不 阻塞,从而提高了并发性。 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 153 图7.1 读一致性 读一致性是一种自动的执行,该操作在回退段保持一个数据库的局部的拷贝。 在对数据库进行一个插入、更新或者删除时,Oracle服务器在数据改变之前获得相关数 据的拷贝,并且将这些数据写到一个回退段 (undo segment)。在9i之前,回滚段都是手动管 理的,9i开始出现了回滚表空间,回滚段开始自动管理。 所有读数据者,除了发布修改命令的用户,看到的数据还是改变之前的状态;他们看到 的数据是回退段中的数据快照 (snapshot)。在改变被提交到数据库之前,只有正在修改数据 的用户能看见数据库的改变;除他之外的任何人看到的是回退段中的快照,这样就确保数据 的读者读到一致的数据,而不是当前正在被修改的数据。 当一个DML语句被提交时,对数据库所做的改变对任何执行SELECT语句的人成为可见 的。在回退段中文件中被 旧 数据占用的空间被释放以重新使用。 如果事务被回滚,修改就被回退,在回退段中原来的,更旧的数据版本被写回到表中。 所有用户看到的数据库就像事务开始之前那样。 当你提交一个事务时,Oracle服务器释放回滚信息,但并不立即销毁它,该信息保留在 回退段中用来为事务提交之前就已经启动的查询创建相应数据的读一致查看。 7.13 锁定 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 154 锁是防止访问相同资源的事务之间的破坏性交互的机制。既可以是用户对象 (例如表或 行),也可以是对用户不可见的系统对象 (例如共享数据结构和数据字典行)。 有两种类型:显式锁定和隐式锁定。Oracle锁被自动执行,并且不要求用户干预的锁为 隐式锁。对于SQL语句隐式锁是必须的,依赖被请求的动作。隐式锁定除SELECT外,对所 有的SQL语句都发生。用户也可以手动锁定数据,这是显式锁定。 锁的作用:在并发事务之间防止破坏性的交互作用,不需要用户的动作,自动使用最低 的限制级别,在事务处理期间保持。 7.13.1 显式锁定 lock table table_name in row share mode lock table table_name in row exclusive mode lock table table_name in share mode lock table table_name in share row exclusive mode lock table table_name in exclusive mode 第十三节 锁定  锁是防止访问相同资源的事务之间的破坏性交互 的机制  隐式锁定:这是 Oracle 中使用最多的锁。通常用 户不必声明要对谁加锁,Oracle 自动可以为操作 的对象加锁,这就是隐式锁定。  显示锁定:用户可以使用命令明确的要求对某一对 象加锁。显示锁定很少使用。 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 155 7.13.2 隐式锁定 两种锁模式,独占锁:不允许其他用户访问,对于用 DML 语句修改的每一行,独占锁 被自动获得。独占锁在本事务被提交或被回滚之前防止行被其他事务修改。该锁确保无其他 用户能够在相同的时间修改相同的行,并且覆盖另一个用户还没有提交的改变。 共享所:允许其他用户访问,共享所是在表级在 DML 操作期间自动获得的。用共享锁 模式,几个事务可以在相同的资源上获得共享锁。 高级数据并发操作:DML: 表共享,行独占 查询: 不需要锁 DDL: 保护对象定义 锁保持直到 commit 或 rollback。 隐式锁定: Select * from table_name…… Insert into table_name…… Update table_name…… Delete from table_name…… Select * from table_name for update 7.13.3 按照对象分类锁定 DML lock(data locks,数据锁):用于保护数据的完整性; DDL lock(dictionary locks,字典锁):用于保护数据库对象的结构(例如表、视图、索 引的结构定义); internal locks 和 l a t c h es(内部锁与闩):保护内部数据库结构; distributed locks(分布式锁):用于 OPS(并行服务器)中; PCM locks(并行高速缓存管理锁):用于 OPS(并行服务器)中。 7.14 本章总结 在本章中,你应该已经学会如何用INSERT、UPDATE和DELETE语句操纵Oracle数据库 中的数据。用COMMIT、SAVEPOINT和ROLLBACK语句控制数据的改变。 Oracle服务器保证在所有时间数据的一致性观察。锁定可以是隐式的或显式的。 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 156 第 8 章 创建和管理表 完成本章后, 您应当能够执行下列操作:描述主要数据库对 象,创建表,描述列定义时可用的数据类型,改变表的定义,删 除、改名和截断表。 第 八 章 创建和管理表  数据库对象及表介绍  表的分类、命名规则、创建语句  用子查询语法创建表、DEFAULT 选项  引用表、数据类型介绍、时间数据类型介绍  TIMESTAMP  ALTER table 语句、表维护语句 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 157 8.1 数据库对象及表介绍 Oracle 数据库能够包含多种数据结构。每一种结构应该在数据设计中描述,以使它能够 在数据库开发阶段被创建。如下表: 对象 说明 表 基本存储单元; 由行和列组成 视图 逻辑地从一个或多个表中表示数据子集 序列 数字值发生器 索引 改善一些查询的性能 同义词 给对象可选择的名字 数据库的表以下特征:表可以在任何时间被创建,即使用户正在使用数据库。你不需要 指定表的大小,表的大小最终由作为一个整体分配给数据库的空间的数量定义。但是随着时 间的过去一个表将使用多少空间是重要的。表结构能够被联机修改。 表能够有多达1,000个列,并且必须符合标准的数据库对象命名约定。当使用AS子查询 子句时,列定义可以忽略。表在创建时没有数据,除非指定了一个查询。行通常用INSERT 语句添加。 第 一 节 数据库对象及表介绍  常用的数据库对象有:表、视图、序列、索引、同 义词。  表:也可以被称为堆组织表(后面的章节中会讲到 其他组织形式的表)  表由多列组成,至少一列,最多可以有 1000 列。 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 158 8.2 表的分类 用户表:由用户创建和维护的表的集合,包含用户信息,用户表由用户创建,例如 EMPLOYEES。 数据字典:由Oracle 服务器创建和维护的表的集合–包含数据库信息。在Oracle数据库 中有另一个表和视图的集合称为数据字典 (data dictionary),该集合由Oracle服务器创建和维 护,其中包含有关数据库的信息。 全部数据字典表的所有者是用户SYS。数据字典表的基表很少被用户访问,因为其中的 信息不容易理解,因此,用户一般是访问数据字典视图,因为视图中的信息是以容易理解的 格式表示的。存储在数据字典中的信息包括Oracle服务器用户的名字,被授予用户的权限, 数据库对象名,表结构和审计信息。 有四种数据字典视图,每一种有一个特定的前缀来反映其不同的目的。 USER_,这些视图包含关于用户所拥有的对象的信息。 ALL_,这些视图包含所有用户可访问的表 (对象表和相关的表) 的信息。 DBA_,这些视图是受限制的视图,它们只能被分配有 DBA 角色的用户所访问。 V$,这些视图是动态执行的视图,包含数据库服务器的性能、存储器和锁的信息。 第 二 节 表的分类  用户表:由用户创建和维护的表  数据字典:由 Oracle 服务器创建和维护的表  数据字典表的 Owner 是 SYS,普通用户无法直接访 问。数据字典表是 Oracle 正常运行的基础。 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 159 8.3 表和列的命名规则 第 三 节 表和列的命名规则 表命名和列命名:必须以字母开始,必须是1–30 个字符长度, 只能包含A–Z, a–z, 0–9, _, $, 和#,同一个用户所拥有的对象之 间不能重名,不能用Oracle 服务器的保留字。使用描述性的名字为表 和其他数据库对象命名。 表和列名是大小写不敏感的。eMPloyees 或 eMpLOYEES是算做同 一个表的。EMPLOYEE_ID和EMPLOYee_ID是算做一个列的。 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 160 8.4 CREATE TABLE 语句创建表 语法: CREATE TABLE [schema.] Table (column datatype [DEFAULT expr][, ...]); schema 与所有者的名字一样 table 表的名字 DEFAULT expr 指定默认值,在NSERT语句省略值时使用 column 列的名字 datatype 列的数据类型和长度 用SQL的CREATE TABLE语句创建表以存储数据,该语句是数据定义语言 (DDL) 语句 之一,其它的 (DDL) 语句将在后面讲述。DDL语句是SQL语句的一个子集,用来创建、修 改或删除Oracle9i 数据库的结构。这些语句会立即作用于数据库,并且他们还将信息记录在 数据字典中。 第四节 CREATE TABLE 语句创建表  创建表命令的语法: CREATE TABLE [schema.]Table (column datatype [DEFAULT expr][, ...]);  创建表命令是 DDL 命令(数据定义语言) ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 161 要执行CREATE TABLE脚本,需要满足下面的条件: CREATE TABLE权限,一个存储区域,指定表名和列名,指定列数据类型和列的大小。 下面是CREATE TABLE的详细语法,各位朋友有兴趣可以搜索一下每个关键字所包含 的内容,该内容不在本章的范围之内: create table table_name (column datatype,column datatype]....) tablespace tablespace_name [pctfree integer] [pctused integer] [initrans integer] [maxtrans integer] storage(initial 200k next 200k pctincrease 0 maxextents 50) [logging|nologging] [cache|nocache] 下面的语句创建一个表 dept: CREATE TABLE dept (deptno NUMBER(2),dname VARCHAR2(14),loc VARCHAR2(13)); 因为创建表是一个DDL语句,当该语句执行时将会发生一个自动提交。 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 162 8.5 用子查询语法创建表 创建表的第二种方法是用AS subquery子句,该方法既可以创建表还可以将从子查询返 回的行插入新创建的表中。用子查询选项组合CREATE TABLE语句创建表并插入行,用列 名和默认值定义列,语法: CREATE TABLE table[(column, column...)]AS subquery; table 是表的名字 column 是列的名字,默认值和完整性约束 subquery 是 SELECT 语句,用来定义将要被插入到新表中的行集 创建原则: 被创建的表要带指定的列名,并且由SELECT语句返回的行被插入到新表中。 字段的定义只能包括列名和默认值。 如果给出了指定的列,列的数目必须等于子查询的SELECT列表的列数目。 如果没有给出了指定的列,表的列名应和子查询中的列名是相同的。 完整性规则不会被传递到新表中,仅列的数据类型被定义。 如果不需要在创建的时候插入数据,则可以在子查询的WHERE 子句里面使条件为 第五节 用子查询语法创建表  使用子查询创建表: CREATE TABLE table[(column, column...)] AS subquery;  此种形式被简称为 CTAS。  可以在创建表的同时为表插入多行 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 163 FALSE。 例子: CREATE TABLE dept80 AS SELECT employee_id, last_name, salary*12 ANNSAL, hire_date FROM employees WHERE department_id = 80; CREATE TABLE deptnull AS SELECT employee_id, last_name, salary*12 ANNSAL, hire_date FROM employees WHERE 1=2; ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 164 8.6 DEFAULT 选项 第 六 节 DEFAULT 选项 一个列可以用DEFAULT选项给予一个默认值,列该选 项防止插入时输入空值到列中。默认值可以是文字、表 达式或SQL函数,例如用SYSDATE和USER。 但默认值不能 是另一个列的名字或伪列,例如NEXTVAL或CURRVAL。默 认表达式必须与列的数据类型相匹配。 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 165 8.7 引用表 引用当前用户的表: Select * from employees 引用其他用户的表: Select * from test.employees 引用其他用户的表需要有相关的权限。 查询数据字典表: Select Table_Name From User_Tables; 查看本用户所拥有的不同的对象类型: SELECT DISTINCT object_type FROM user_objects ; 查看本用户所拥有的表、视图、同义词和序列: SELECT* FROM user_catalog ; 第 七 节 引用表  引用当前用户的表  引用其他用户的表  查看数据字典  查看用户所拥有的不同的对象类型  查看本用户所拥有的表、视图、同义词、序列 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 166 8.8 数据类型介绍 第 八 节 数据类型介绍  最为常用的数据类型有三种,字符型、数值型和日 期型。  VARCHAR2、CHAR2 可以定义字符型数据  NUMBER 定义整数或小数的数值型数据  DATE 定义日期型数据 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 167 ORACLE 的基本数据类型见下表: 数据类型 说明 VARCHAR2(size) 可变长度字符数据(必须指定最大字符数:最小字符数是 1;最 大字符数是 4000) CHAR [(size)] 固定长度字符数据,长度的大小以字节为单位(默认和最小字符 数为 1;最大字符数为 2000) NUMBER [(p,s)] 数字,精度为 p,小数为 s (p 是小数数字的总长度,s 是小数点 右边的数字长度;p 的范围从 1 到 38,s 的范围从-84 到 127) DATE 日期和时间值,从公元前 4712.1.1 到公元 9999.12.31 LONG 最大 2G 的可变长度字符数据 CLOB 最大 4G 的字符数据 RAW(size) 原始二进制数据 (必须指定最大长度,最大长度为 2000) LONG RAW 可变长度原始二进制数据,最大 2G BLOB 二进制数据,最大 4G BFILE 二进制数据存储在一个外部文件中;最大到 4G ROWID 十六进制串,表示行在所在的表中唯一的行地址。该数据类型主 要用于返回 ROWID 伪列 在用子查询创建表时,LONG列不会被复制。LONG 列不能包括在GROUP BY或ORDER BY子句中。在每个表中只能有一个LONG列。 在LONG列上不能定义约束。通常用情况下使用CLOB列而不是LONG列。Oracle8引入 了大对象 (LOB) 数据类型,它可以存储大的和非结构化的数据,例如文本、图象、视频和 空间数据,最大4G。LONG列可以容易地移动到LOB列。 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 168 8.9 时间数据类型 Oracle9i 对日期时间的增强:引入了新的日期时间数据类型,可用新数据类型存储,对 时区和本地时区的增强。 DATE,时间数据类型。日期和时间值,从公元前4712.1.1到公元9999.12.31。 TIMESTAMP,允许带小数秒的时间被作为日期存储。有一些变异的数据类型。它是 DATE数据类型的一种扩展,它存储DATE数据类型的年、月和日,加小时、分和秒值,以 及秒的小数值,TIMESTAMP数据类型被指定如下: TIMESTAMP[(fractional_seconds_precision)] INTERVAL YEAR TO MONTH,允许时间作为年和月的间隔被存储。 INTERVAL DAY TO SECOND,允许时间作为天、小时、分和秒的间隔被存储。 第 九 节 时间数据类型  DATE 类型,时间范围:从公元前 4712.1.1 到公元 9999.12.31  TIMESTAMP 类型:精度更高的日期时间型  INTERVAL 类型:间隔型,又分为两种 - INTERVAL YEAR TO MONTH - INTERVAL DAY TO SECOND ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 169 8.10 TIMESTAMP 类型 我们看看下面的试验: Create Table ts_test (dt Date, ts Timestamp, ts1 TIMESTAMP(7), ts2 Timestamp WITH TIME Zone, ts3 Timestamp WITH LOCAL Time Zone); Insert Into Ts_Test Select Sysdate, Sysdate, Sysdate, Sysdate, Sysdate From Dual; Select * From Ts_Test DT TS TS1 TS2 TS3 2008-9-7 17:29 07-9 月 -08 05.29.50.000000 下午 07-9 月 -08 05.29.50.0000000 下午 07-9 月 -08 05.29.50.000000 下午 +08:00 07-9 月 -08 05.29.50.000000 下午 第 十 节 TIMESTAMP 类型  它是精度更高的时间期数据,使用示例:  Timestamp :缺省时小数秒精度为 6  TIMESTAMP(n) :指定小数秒精度为 7  Timestamp WITH TIME Zone  Timestamp WITH LOCAL Time Zone ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 170 在上面的例子中,我们创建了一个表TS_TEST,其中字段date的数据类型是date,ts 的 数据类型为Timestamp,ts1 的数据类型为Timestamp(7),ts2 的数据类型为Timestamp WITH TIME ZONE。 精度7指示小数秒的精度,如果不指定,小数秒的默认精度是6。 TIMESTAMP WITH TIME ZONE是TIMESTAMP的一个变量,它对TIMESTAMP值进行 一个时区转换,在本地时间和UTC 之间,小时和分钟的时区转换是不同的。 UTC代表协调世界时—以前的格林尼治标准时间。如果两个TIMESTAMP WITH TIME ZONE在UTC中代表同一时刻,它们的值被认为是相同的,而不管存储在数据中的TIME ZONE偏移。 因为TIMESTAMP WITH TIME ZONE也可以存储时区信息,它特别适合记录那些必须 组合或协调地理区域的日期信息。 例如, TIMESTAMP '1999-04-15 8:00:00 -8:00' 与 TIMESTAMP '1999-04-15 11:00:00 -5:00' 是相同的。 美国西部标准时间 8:00 a.m. 和东部标准时间 11:00 a.m. 是相同的。 该时间也可以被指定为: TIMESTAMP '1999-04-15 8:00:00 US/Pacific' 注:小数秒精度指定SECOND日期时间字段的小数部分数字的数目,其范围是0到9,默 认是6。 TIMESTAMP WITH LOCAL TIME数据类型,TIMESTAMP WITH LOCAL TIME ZONE 是TIMESTAMP的另一个变量,它对TIMESTAMP值进行一个时区转换,存储在数据 库中的数据被格式化为数据库时区,时区的转换不被作为列数据的一部分存储;Oracle 以 本地会话时区返回数据,TIMESTAMP WITH LOCAL TIME ZONE数据类型被如下指定: TIMESTAMP[(fractional_seconds_precision)]WITH LOCAL TIME ZONE。 不像TIMESTAMP WITH TIME ZONE,你可以指定TIMESTAMP WITH LOCAL TIME ZONE类型作为一个主键或唯一键的一部分。在本地时间和UTC之间的时区转换 (小时或分 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 171 钟) 是不同的,对于TIMESTAMP WITH LOCAL TIME ZONE是非文字的。 注:小数秒精度指定SECOND日期时间字段的小数部分数字的数目,其范围是0到9,默 认是6。 例如: CREATE TABLE time_example (order_date TIMESTAMP WITH LOCAL TIME ZONE); INSERT INTO time_example VALUES('15-NOV-00 09:34:28 AM'); SELECT * FROM time_example; order_date ---------------------------- 15-NOV-00 09.34.28.000000 AM TIMESTAMP WITH LOCAL TIME ZONE类型适合于两层应用程序,在其中你可以用客 户系统的时区显示日期和时间。 8.11 INTERVAL YEAR TO MONTH 数据类型 第 十一 节 INTERVAL YEAR TO MONTH  使用语法如下: INTERVAL YEAR [(year_precision)] TO MONTH  主要用于求日期 n 年、m 月后的日期是那一天  是普通 Data 日期型的补充 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 172 INTERVAL YEAR TO MONTH用年和月日期时间字段存储一段时间。 用INTERVAL YEAR TO MONTH表示两个日期时间值的差,该差值只有年和月的部分。 例如,你可能用该值设置一个往后120个月的提醒日期,或检查是否 从某个特定的日期后6 月已过去。 指定 INTERVAL YEAR TO MONTH 如下语法: INTERVAL YEAR [(year_precision)] TO MONTH year_precision 是在YEAR日期时间字段中数字的数目,年精度的默认值是2。 例如: CREATE TABLE te1 (loan_duration INTERVAL YEAR (3) TO MONTH); INSERT INTO te1 (loan_duration) VALUES (INTERVAL '120' MONTH(3)); SELECT TO_CHAR( sysdate+loan_duration, 'dd-mon-yyyy') FROM te1; TO_CHAR(SYSDATE+LOAN_DURATION, 07-9 月 -2018 前面的部分要大于后面的部分,例如:INTERVAL '0-1' MONTH TO YEAR 是无效 的,必须写成:INTERVAL '0-1' YEAR TO MONTH。 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 173 8.12 INTERVAL DAY TO SECOND 数据类型 INTERVAL DAY TO SECOND根据天、小时、分和秒存储一段时间。用INTERVAL DAY TO SECOND来表示两个日期时间值精确的差。例如,你可能用该值设置一个往后36个小时 的提醒,或记录一个赛跑的开始和结束之间的时间。为了表示很长的时间跨度,包括很多年, 用很高的精度,你可以用一个很大的值表示天的一部分。 指定 INTERVAL DAY TO SECOND 如下: INTERVAL DAY [(day_precision)] TO SECOND [(fractional_seconds_precision)] day_precision 是在DAY日期时间字段中数字的数目,可接受的值的范围是0到9,默认 是2。 fractional_seconds_precision 是在SECOND日期时间字段中数字的数目,可接受的值的 范围是0到9,默认是6。 例如: CREATE TABLE te2 (day_duration INTERVAL DAY (3) TO SECOND); 第 十二 节 INTERVAL DAY TO SECOND  使用语法: INTERVAL DAY [(day_precision)] TO SECOND [(fractional_seconds_precision)]  主要用于求日期 n 天、m 小时后的准确时间  间隔型数据无法直接查看,必须和一个日期进行运 行后才有效。 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 174 INSERT INTO te2 (day_duration) VALUES (INTERVAL '180' DAY(3)); SELECT sysdate + day_duration "Half Year" FROM te2; Half Year 2009-3-6 17:38 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 175 8.13 ALTER TABLE 语句 在你创建一个表后,你可能需要改变表的结构,因为,你可能遗漏了一个列,或者列的 定义需要改变,或者某些列需要删除,你可以用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); table 是表的名字 ADD|MODIFY|DROP 是修改类型 column 是新列的名字 datatype 是新列的数据类型和长度 DEFAULT expr 为一个新行指定默认值 第 十三 节 ALTER TABLE 语句  ALTER TABLE 语句是经常使用的修改表定义的 语句  它是一条 DDL 语句  它可以完成如下操作: - 添加列 - 修改列 - 删除列 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 176 8.13.1 添加列 你可以添加列,你不能指定新添加的列出的位置,新列将成为最后一列。例如: ALTER TABLE dept80 ADD(job_id VARCHAR2(9)); 8.13.2 修改列 你可以用带MODIFY子句的ALTER TABLE语句修改一个列,列的修改包括修改列的数 据类型,大小和默认值。 你可以增加宽度或一个数字列的精度。 你可以增加数字列或字符列的宽度。 你可以减少一个列的宽度,但仅在列中只包含空值或表中没有行时。 你可以改变数据类型,但仅在列中只包含空值时。 你可以转换一个CHAR列到VARCHAR2数据类型或转换一个VARCHAR2列到 CHAR 数据类型仅当列中只包含空值时,或者你不改变列的大小时。 对默认值的改变仅影响以后插入的列。例如: ALTER Table dept80 Modify (last_name VARCHAR2(30)); 8.13.3 删除列 你可以用带DROP COLUMN子句的ALTER TABLE语句从表中删除列,该特性在Oracle8i及以 后的版本中可用。 列可以有也可以没有数据。用ALTER TABLE语句,一次只能有一列被删除。表被修改后 必须至少保留一列。一旦一列被删除,它不能再恢复。当一列从表中被删除时,该表中任何 其他的被用SET UNUSED选项标记列也被删除。 SET UNUSED选项标记一个或多个列作为不使用的,所以,当需求的系统资源较低时 他们可以被删除,该特性在Oracle8i和以后的版本中有效。指定该子句不会真的从表的每一 行中删除目标列 (即,它不会恢复这些列所使用的磁盘空间),因此,SET UNUSED选项标 记的执行响应时间会比执行DROP子句快一些。不使用的列就好象它被删除了一样的被处理, 即使他们的列数据还保留在表的行中。在一列已经被标记为不使用后,你就不能访问该列了。 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 177 一个SELECT *查询不会从标记为不使用的列返回数据。另外,在使用DESCRIBE命令时, 被标记为不使用的列的名字和类型将不再显示,并且你可以用一个与不使用列相同的名字添 加一个新列到表中。SET UNUSED信息被存储在USER_UNUSED_COL_TABS字典视图中。 DROP UNUSED COLUMNS选项 DROP UNUSED COLUMNS从表中删除当前所有被标记为不使用的列,当你想要从表 中的不使用列回收额外的磁盘空间时你可以用该语句,如果表中不包含不使用列,该语句不 返回错误。例如: Alter Table dept80 Set Unused (last_name); Alter Table dept80 Drop Unused COLUMNS; ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 178 8.14 删除表 DROP TABLE 语句删除 Oracle 表定义,当你删除一个表时,数据库丢失表中所有的数 据,并且所有与其相关的索引也被删除。语法: DROP TABLE table table 是表的名字。 所有的数据从表中删除。任何视图和同义词被保留但无效。任何未决的事务被提交。只 有表的创建者或具有 DROP ANY TABLE 权限的用户才能 删除表。 DROP TABLE 语句,一旦被执行,就不能撤回。当你发布 DROP TABLE 语句时,Oracle 服务器不询问其行为,如果你拥有该表或有一个高级权限,那么,该表立即被删除。当使用 所有 DDL 语句时,DROP TABLE 被自动提交。例如: Drop Table dept80 PURGE; ORACLE 10g 中由于引入了回收站,因此要彻底删除一个表需要加 PURGE 关键字。 8.15 改变对象名称 第 十四 节 删除表  删除表的语法:DROP TABLE 表名  表的索引将被随之删除  表相关的视图和同义词被保留但都处于无效状态, 无法再正常使用。  在 10g 中要彻底删除一个表需要加 PURGE 关键 字,否则被删除的表被入闪回区,而不是真正删除。 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 179 第 十五 节 改变对象名称 执行 RENAME 语句,改变一个表、视图、序列或同义词,你必须 是对象的所有者。外的 DDL 语句包括 RENAME 语句,该语句被用于 改变表、视图序列或同义词的名字。 RENAME old_name TO new_name; old_name 是表、视图序列或同义词的旧名字。 new_name 是表、视图序列或同义词的新名字。 你必须是你重命名的对象的所有者。 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 180 8.16 截断表 另一个 DDL 语句是 TRUNCATE TABLE 语句,该语句被用于从表中删除所有的行,并 且释放该表所使用的存储空间。在使用 TRUNCATE TABLE 语句时,你不能回退已删除的 行。语法 TRUNCATE TABLE table; table 是表的名字 你必须是表的所有者,或者有 DELETE TABLE 系统权限来截断表。DELETE 语句也可 以从表中删除所有的行,但它不能释放存储空间。TRUNCATE 命令更快一些,用 TRUNCATE 语句删除行比用 DELETE 语句删除同样的行快一些,原因如下:TRUNCATE 语句是数据定 义 (DDL) 语句。并且不产生回滚信息。截断一个表不触发表的删除触发器。 如果表是一个引用完整性约束的父表,你不能截断该表,在发布 TRUNCATE 语句之前 禁用约束。例如: TRUNCATE TABLE detail_dept; 第 十六 节 截断表  截断表的语法:TRUNCATE TABLE 表名  截断表的作用是删除表的所有行并释放表所占用 的空间  截断表和删除数据所有行区别就是,使用 Delete 删除所有行并不会释放表所占用的空间。 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 181 8.17 给表添加注释 你可以用 COMMENT 语句给一个列、表、视图或快照添加一个最多 2K 字节的注释。 注释被存储在数据字典中,并且可以通过下面的数据字典视图查看 COMMENTS 列: ALL_COL_COMMENTS USER_COL_COMMENTS ALL_TAB_COMMENTS USER_TAB_COMMENTS 语法: COMMENT ON TABLE table | COLUMN table.column IS 'text'; table 是表的名字 column 是表中列的名字 text 是注释的文本。你可以用设置注释为空 串 ('') 的办法从数据库中删除一个注释: COMMENT ON TABLE employees IS ''; COMMENT ON TABLE employees IS 'Employee Information'; 第 十七 节 给表添加注释  可以使用 COMMENT 给表、列、视图等对象添加 注释  可以通过如下视图查看添加到表或列的注释: - ALL_COL_COMMENTS - USER_COL_COMMENTS - ALL_TAB_COMMENTS - USER_TAB_COMMENTS ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 182 8.18 总结 在本章中,您应该已经学会如何使用 DDL 命令来创建、修改、删除和重命名表。你也 学会了怎样截断表和添加注释到一个表。 创建表。 用子查询基于另一个表创建表。 修改表 修改表结构。 修改列宽,改变列数据类型和添加列。 删除表 删除行和表结构。 一旦执行,该语句不能回滚。 重命名 重命名一个表、视图、序列或同义词。 截断 从表中删除所有行,并且释放该表已使用的存储空间。 DELETE 语句只删除行。 注释 添加注释到表或列。查询数据字典来查看注释。 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 183 第 9 章 内置约束 9.1 约束的概念和原则 第 九 章 内置约束  数据库对象及表介绍  表的分类、命名规则、创建语句  用子查询语法创建表、DEFAULT 选项  引用表、数据类型介绍、时间数据类型介绍  TIMESTAMP  ALTER table 语句、表维护语句 第 一 节 约束的概念和原则  约束的作用:来防止无效数据输入到表中  约束存储在数据字典中  建议为约束定一个有意义的名字,这样例于管理  使用 USER_CONSTRAINTS 视图可以查看约束的定 义。 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 184 9.1.1 约束的概念 Oracle 服务器用约束 (constraints) 来防止无效数据输入到表中。 你可以使用约束做下面的事:在插入、更新行或者从表中删除行的时候强制表中的数据 遵循规则。对于成功的操作,约束必须被满足。如果表之间有依赖关系,防止表的删除。为 Oracle 工具提供规则,例如 Oracle Developer。 数据一致性约束: 约束 说明 NOT NULL 指定列不能包含空值 UNIQUE 指定列的值或者列的组合的值对 于表中所有的行必须是唯一的 PRIMARY KEY 表的每行的唯一性标识 FOREIGN KEY 在列和引用表的一个列之间建立 并且强制一个外键关系 CHECK 指定一个必须为真的条件 9.1.2 约束的原则 所有的约束存储在数据字典中。如果给约束一个有意义的名字,约束易于引用,约束命 名必须遵守标准的对象命名规则。如果不命名你的约束,Oracle 服务器将用格式 SYS_Cn 产生一个名字,这里 n 是一个唯一的整数,所以约束名是唯一的。约束可以在创建表时定 义,也可以在表创建之后定义。你可以用 USER_CONSTRAINTS 数据字典表查看对一个表 的约束的定义。 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 185 9.2 定义约束 9.2.1 定义约束的语法 定义约束的语法: CREATE TABLE [schema.]table (columndatatype[DEFAULT expr] [column_constraint],... [table_constraint][,...]); schema 与所有者同名 table 表的名字 DEFAULT expr 指定一个默认值。如果在插入语句中省略了一个值,在省略处使用该 默认值 column 列的名字 datatype 列的数据类型和长度 第 二 节 定义约束  定义约束的方法有两种 - 在创建表时定义约束 - 在创建表后添加约束  约束可以针对单列或多列  单列约束直接在列后定义 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 186 column_constraint 是一个作为列定义一部分的完整性约束 table_constraint 是一个作为表定义一部分的完整性约束 例如: CREATE TABLE "TEST"."EMPLOYEES" ( "EMPLOYEE_ID" NUMBER(6,0), "FIRST_NAME" VARCHAR2(20), "LAST_NAME" VARCHAR2(25) CONSTRAINT "EMP_LAST_NAME_NN" NOT NULL ENABLE, "EMAIL" VARCHAR2(25) CONSTRAINT "EMP_EMAIL_NN" NOT NULL ENABLE, "PHONE_NUMBER" VARCHAR2(20), "HIRE_DATE" DATE CONSTRAINT "EMP_HIRE_DATE_NN" NOT NULL ENABLE, "JOB_ID" VARCHAR2(10) CONSTRAINT "EMP_JOB_NN" NOT NULL ENABLE, "SALARY" NUMBER(8,2), "COMMISSION_PCT" NUMBER(2,2), "MANAGER_ID" NUMBER(6,0), "DEPARTMENT_ID" NUMBER(4,0), CONSTRAINT "EMP_SALARY_MIN" CHECK (salary > 0) ENABLE, CONSTRAINT "EMP_EMAIL_UK" UNIQUE ("EMAIL"); 9.2.2 约束分类 约束通常在创建表的同时被创建。在表创建后约束能够被添加,并且约束可以可以被临 时禁用。 约束可以在两个级别上定义。 列,只涉及一个单个的列,对于该列用规范定义;能够定义完整性约束的任何类型 列级约束: column[CONSTRAINT constraint_name] constraint_type,column[CONSTRAINT constraint_name] constraint_type, 表,涉及一个或多个列,表中的列被分别定义;除了 NOT NULL,能够定义任意约束 表级约束: column,...[CONSTRAINT constraint_name] constraint_type(column, ...) ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 187 9.3 几种常见约束 9.3.1 NOT NULL 约束 NOT NULL 约束确保列无不包含空值。在默认情况下,列没有 NOT NULL 约束,可以 包含空值。 下面例子: CREATE TABLE "TEST"."EMPLOYEES" ( "EMPLOYEE_ID" NUMBER(6,0), "FIRST_NAME" VARCHAR2(20), "LAST_NAME" VARCHAR2(25) CONSTRAINT "EMP_LAST_NAME_NN" NOT NULL ENABLE, "EMAIL" VARCHAR2(25) CONSTRAINT "EMP_EMAIL_NN" NOT NULL ENABLE, "PHONE_NUMBER" VARCHAR2(20), "HIRE_DATE" DATE CONSTRAINT "EMP_HIRE_DATE_NN" NOT NULL ENABLE, "JOB_ID" VARCHAR2(10) CONSTRAINT "EMP_JOB_NN" NOT NULL ENABLE, "SALARY" NUMBER(8,2), 第 三 节 几种常见的约束  NOT NULL 约束  UNIQUE 约束  PRIMARY KEY 约束  FOREIGN KEY 约束  CHECK 约束 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 188 "COMMISSION_PCT" NUMBER(2,2), "MANAGER_ID" NUMBER(6,0), "DEPARTMENT_ID" NUMBER(4,0), CONSTRAINT "EMP_SALARY_MIN" CHECK (salary > 0) ENABLE, CONSTRAINT "EMP_EMAIL_UK" UNIQUE ("EMAIL"); LAST_NAME、EMAIL 就有 NOT NULL 约束,他们的名字是由系统指定的。而 EMP_SALARY_MIN 约束则是由用户指定名字。 9.3.2 UNIQUE 约束 UNIQUE 键完整性约束,要求列或者列的组合中 (键) 的每个值是唯一的,既在表中指 定的列或列组合中不能有两行有相同的值。定义 UNIQUE 键约束的列 (或列组合) 被称为唯 一键 (unique key)。 除非你对相同的列也定义了 NOT NULL 约束,UNIQUE 约束允许输入空值,事实上, 对于无 NOT NULL 约束的列,能包含空值的行可以是任意数目,因为空不等于任何事。在 一个列 (或者在一个复合 UNIQUE 键中的所有列) 中的空总是满足 UNIQUE 约束。 注:因为在多于一列上的 UNIQUE 约束的搜索机制原因,在一个部分为空的组合 UNIQUE 键约束的非空列中你不能有相同的值。例如: Insert Into Departments (Department_Id, Department_Name, Manager_Id, Location_Id) Values (444, 'Test Part', 200, 1700); 该语句第一次执行是可以的,但是第二次执行则会报错: “ORA-00001: 违反唯一约束条件(TEST.DEPT_ID_PK)” UNIQUE 约束既可以在列级也可以在表级定义。使用表级定义时一个复合唯一键被创 建。下面的例子应用 UNIQUE 约束到 EMPLOYEES 表的 EMAIL 列。约束的名字是 EMP_EMAIL_UK。 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,... ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 189 CONSTRAINT emp_email_uk UNIQUE(email)); 9.3.3 PRIMARY KEY 约束 PRIMARY KEY 约束为表创建一个主键。每个表只能创建一个主键。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)); PRIMARY KEY 约束既可以定义在列级也可以定义在表级。用表级定义创建一个组合 PRIMARY KEY。一个表只能有一个 PRIMARY KEY 约束,但可以有多个 UNIQUE 约束。 上面的例子在 DEPARTMENTS 表的 DEPARTMENT_ID 列上定义了一个 PRIMARY KEY 约束。约束的名字是 DEPT_ID_PK。 注:对于一个 PRIMARY KEY 列 UNIQUE 索引被自动创建。 9.3.4 FOREIGN KEY 约束 FOREIGN KEY,引用完整性约束,指明一个列或者列的组合作为一个外键,并且在相 同表或者不同表的主键或者唯一键和外键之间建立一个关系。前提是被引用的外键必须是另 外一个表的主键。 在下面的例子中,DEPARTMENT_ID 已经在 EMPLOYEES 表 (依赖表或子表) 中被定 义为外键;它引用 DEPARTMENTS 表 (引用表或父表) 的 DEPARTMENT_ID 列。一个外键 值必须匹配一个在父表中存在的值或者空值。外键基于数据值,并且纯粹是逻辑的,不是物 理的,指针。 CREATE TABLE "TEST"."DEPARTMENTS" ( "DEPARTMENT_ID" NUMBER(4,0), "DEPARTMENT_NAME" VARCHAR2(30) CONSTRAINT "DEPT_NAME_NN" NOT NULL ENABLE, ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 190 "MANAGER_ID" NUMBER(6,0), "LOCATION_ID" NUMBER(4,0), CONSTRAINT "DEPT_ID_PK" PRIMARY KEY ("DEPARTMENT_ID"); CREATE TABLE "TEST"."EMPLOYEES" ( "EMPLOYEE_ID" NUMBER(6,0), "FIRST_NAME" VARCHAR2(20), "LAST_NAME" VARCHAR2(25) CONSTRAINT "EMP_LAST_NAME_NN" NOT NULL ENABLE, "EMAIL" VARCHAR2(25) CONSTRAINT "EMP_EMAIL_NN" NOT NULL ENABLE, "PHONE_NUMBER" VARCHAR2(20), "HIRE_DATE" DATE CONSTRAINT "EMP_HIRE_DATE_NN" NOT NULL ENABLE, "JOB_ID" VARCHAR2(10) CONSTRAINT "EMP_JOB_NN" NOT NULL ENABLE, "SALARY" NUMBER(8,2), "COMMISSION_PCT" NUMBER(2,2), "MANAGER_ID" NUMBER(6,0), "DEPARTMENT_ID" NUMBER(4,0), CONSTRAINT "EMP_SALARY_MIN" CHECK (salary > 0) ENABLE, CONSTRAINT "EMP_EMAIL_UK" UNIQUE ("EMAIL") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 CONSTRAINT "EMP_EMP_ID_PK" PRIMARY KEY ("EMPLOYEE_ID")); FOREIGN KEY 约束的原则: 外键被定义在子表中,包含引用列的表是父表。外键用下面关键字的组合定义: FOREIGN KEY 被用于在表约束级定义子表中的列。 REFERENCES 确定父表中的表和列。 ON DELETE CASCADE 指出当父表中的行被删除时,子表中相依赖的行也将被级联删 除。 ON DELETE SET NULL 当父表的值被删除时,转换外键值为空。 默认行为被称为约束规则,该规则不允许引用数据的更新或删除。 无 ON DELETE CASCADE 或 ON DELETE SET NULL 选项,如果父表中的行在子表中 引用,则它不能被删除。 9.3.5 CHECK 约束 CHECK 约束定义一个每行都必须满足的条件,该条件可以用和查询条件一样的结构, 下面的情况例外: ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 191 引用 CURRVAL, NEXTVAL, LEVEL 和 ROWNUM 伪列。调用 SYSDATE, UID, USER 和 USERENV 函数。查询涉及其它行中的其它值。伪列不是表中实际的列,但它们的行为 象列一样,例如,你能从伪列中选择够选择值,可是,你不能插入、更新或从伪列中删除。 伪列能被用于 SQL 语句中。 一个单个列在它的定义中可以有多个 CHECK 约束,在一个列上能够定义的 CHECK 约 束的数目无限制。 CHECK 约束能够被定义在列级或表级。 CREATE TABLE employees (... salary NUMBER(8,2) CONSTRAINT emp_salary_min CHECK (salary > 0), ... ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 192 9.4 管理约束 9.4.1 添加约束 你可以用带 ADD 子句的 ALTER TABLE 语句为已经存在的表添加一个约束。语法: ALTER TABLE table ADD [CONSTRAINT constraint] type (column); table 是表的名字 constraint 是约束的名字 type 是约束的类型 column 是受约束影响的列的名字 尽管建议命名约束,但约束名在语法中是个选项。如果你不命名约束,系统将产生约束 名。 添加的原则: 你可以添加、删除或禁用一个约束,但你不能修改它的结构。 第 四 节 管理约束 约束的常用管理操作有:  添加约束  删除约束  禁用、启用约束  在删除对象或在表中删除行时级联约束 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 193 你可以用 ALTER TABLE 语句的 MODIFY 子句添加一个 NOT NULL 约束到一个已经存 在的列。只有在表是空的或者每个行的该列都有非空值的情况下,你才可以定义一个 NOT NULL 列。 你可以延迟检查约束的有效,直到事务结束。 如果仅在提交的时候进行系统检查,约束将被延迟 (deferred)。如果一个延迟的约束被 违反,则该提交导致事务回退。 如果在每条语句结束时进行检查,约束是立即的 (immediate)。如果该约束被违反,则 该语句被立即回退。 下面的例子添加一个约束到 EMPLOYES 表: ALTER TABLE employees ADD CONSTRAINT emp_manager_fk FOREIGN Key (manager_id) REFERENCES employees (employee_id); 9.4.2 删除约束 为了删除约束,你可以先从 USER_CONSTRAINTS 和 USER_CONS_COLUMNS 数据 字典视图中确定约束的名字,然后使用带 DROP 子句的 ALTER TABLE 语句。DROP 子句的 CASCADE 选项导致任何与其相依赖的约束也被删除。删除约束的语法: ALTER TABLE table DROP PRIMARY KEY | UNIQUE (column) | CONSTRAINT constraint [CASCADE]; table 是表的名字 column 是受约束影响的列的名字 constraint 是约束的名字 当你删除一个完整性约束时,约束不再由 Oracle 服务器强制,并且在数据字典中不 再可用。看看下面的例子: 从 EMPLOYEES 表中删除经理约束: ALTER TABLE employees DROP CONSTRAINT emp_manager_fk; 删除 DEPARTMENTS 表上的 PRIMARY KEY 约束,并且删除相关联的在 EMPLOYEES.DEPARTMENT_ID 列上的 FOREIGN KEY 约束: ALTER TABLE departments DROP PRIMARY KEY Cascade; ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 194 9.4.3 禁用、启用约束 你可以禁用一个约束而不删除它,或者用带 DISABLE 子句的 ALTER TABLE 语句重新 创建它。语法 ALTER TABLE table DISABLE CONSTRAINT constraint [CASCADE]; table 是表的名字 constraint 是约束的名字 我们即可以在 CREATE TABLE 语句也可以在 ALTER TABLE 语句中使用 DISABLE 子 句。CASCADE 子句禁用相依赖的完整性约束。禁用唯一或主键约束会移除唯一性索引。 例如: ALTER Table employees DISABLE Constraint emp_emp_id_pk CASCADE; 你可以用带 ENABLE 子句的 ALTER TABLE 语句启用一个禁用的约束,而不需要重新 创建它。语法 ALTER TABLE table ENABLE CONSTRAINT constraint; table 是表的名字 constraint 是约束的名字 如果启用一个约束,约束将应用于表中所有的数据,所有在表中的数据都必须适合该约 束。如果你启用一个 UNIQUE 键或者 PRIMARY KEY 约束,一个 UNIQUE 或 PRIMARY KEY 索引将被自动地创建。 你即可以 CREATE TABLE 语句也可以在 ALTER TABLE 语句中使用 ENABLE 子句。 启用一个带 CASCADE 选项的被禁用的主键约束不会起用任何依赖于该主键的外键。 例如: ALTER Table employees ENABLE Constraint emp_emp_id_pk; 9.4.4 级联约束 CASCADE CONSTRAINTS 子句连同 DROP COLUMN 子句一起被使用,CASCADE CONSTRAINTS 子句删除所有定义在被删除列上的涉及主键和唯一键的引用完整性约束, ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 195 CASCADE CONSTRAINTS 子句也删除所有定义在被删除列上的多列约束。下面的例子说 明级联约束: 建表: CREATE TABLE test1 ( pk NUMBER PRIMARY KEY, fk NUMBER, col1 NUMBER, col2 NUMBER, CONSTRAINT fk_constraint FOREIGN KEY (fk) REFERENCES test1, CONSTRAINT ck1 CHECK (pk > 0 and col1 > 0), CONSTRAINT ck2 CHECK (col2 > 0)); 执行下面的语句会报错: ALTER TABLE test1 DROP (pk); “ORA-12992:无法删除父项关键字列” ALTER TABLE test1 DROP (col1); “ORA-12991:引用的列处于多列约束条件” 提交下面的语句删除行 PK,主键约束,fk_constraint 外键约束和检查约束 CK1: ALTER TABLE test1 DROP (pk) CASCADE CONSTRAINTS; 如果所有由定义在已删除列上的约束引用的列也被删除,那么就不需要 CASCADE CONSTRAINTS ,例如,假设没有其它表引用 PK , 提 交 下 面 的 不 带 CASCADE CONSTRAINTS 子句的语句是恰当的: ALTER TABLE test1 DROP (pk, fk, col1); 如果任何约束被来自其它表中的列或在目的表中的保留列所引用,那么必须指定 CASCADE CONSTRAINTS,否则,该语句异常中断,并且返回错误提示 ORA-12991:在多 列约束中的列被引用。 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 196 9.5 查看约束 在创建表之后,你可以用 DESCRIBE 命令来确认它的存在。你唯一能够校验的约束是 NOT NULL 约束。为了查看表上所有的约束,查询 USER_CONSTRAINTS 表。 下面的例子显示了 EMPLOYEES 表上的约束。那些没有被表的所有者命名的约束将收 到系统指定的约束名。在约束类型中,C 代表 CHECK,P 代表 PRIMARY KEY,R 代表引 用完整性, U 代表 UNIQUE 键。注意 NOT NULL 约束实际上是一个 CHECK 约束。 NOT NULL 约束在数据字典中被作为 CHECK 约束存储。将他们的注意力引到约束类 型,对于幻灯片中的 NOT NULL 约束,在 constraint_type 域输入 C (表示 CHECK)。 Select Constraint_Name, Constraint_Type, Search_Condition From User_Constraints Where Table_Name = 'EMPLOYEES'; CONSTRAINT_NAME CONSTRAINT_TYPE SEARCH_CONDITION EMP_LAST_NAME_NN C EMP_EMAIL_NN C EMP_HIRE_DATE_NN C EMP_JOB_NN C 第 五 节 查看约束  约束相关的视图有: - USER_CONSTRAINTS - USER_CONS_COLUMNS  注意 USER_***只是显示当前用户中的约束, DBA_***可以显示所有用户中的约束 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 197 EMP_SALARY_MIN C EMP_EMAIL_UK U EMP_EMP_ID_PK P EMP_DEPT_FK R EMP_JOB_FK R EMP_MANAGER_FK R 可以用 USER_CONS_COLUMNS 数据字典视图查看与约束相关的列名。该视图对于那 些由系统指定名字的约束特别有用。 Select Constraint_Name, Column_Name From User_Cons_Columns Where Table_Name = 'EMPLOYEES'; CONSTRAINT_NAME COLUMN_NAME EMP_EMAIL_UK EMAIL EMP_SALARY_MIN SALARY EMP_JOB_NN JOB_ID EMP_HIRE_DATE_NN HIRE_DATE EMP_EMAIL_NN EMAIL EMP_LAST_NAME_NN LAST_NAME EMP_MANAGER_FK MANAGER_ID EMP_JOB_FK JOB_ID EMP_DEPT_FK DEPARTMENT_ID EMP_EMP_ID_PK EMPLOYEE_ID 9.6 总结 您应该已经学会如何创建约束:约束类型:NOT NULL、UNIQUE、PRIMARY KEY、 FOREIGN KEY、CHECK。你能够通过查询 USER_CONSTRAINTS 表来观察所有约束定义 和命名。 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 198 第 10 章 创建视图 视图是数据来自一个或者多个表的数据子集的逻辑表示。本章将学习描述视图,创建视 图,修改视图的定义,删除视图,通过视图取回数据,通过视图插入、更新和删除数据,创 建和使用内嵌视图,执行“Top-N”分析。 第 十 章 创建视图  视图的概念和优势  简单视图和复杂视图的创建和查询  视图中的规则  管理视图  TOP-N 分析  物化视图简介 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 199 10.1 视图的概念和优势 10.1.1 视图的概念 我们可以通过创建表的视图来表现数据的逻辑子集或数据的组合。视图是基于表或另一 个视图的逻辑表,一个视图并不包含它自己的数据,它象一个窗口,通过该窗口可以查看或 改变表中的数据。视图基于其上的表称为基表。视图在数据字典中作为一个 SELECT 语句 存储。我们看一下下面这个视图: CREATE OR REPLACE VIEW simple_vu AS SELECT employee_id, last_name, salary FROM employees; Select * From simple_vu EMPLOYEE_ID LAST_NAME SALARY 100 King 24000 101 Kochhar 17000 102 De Haan 17000 103 Hunold 9000 第 一 节 视图的概念和优势  视图是表现数据的逻辑子集或数据的组合  就象一个窗口,通过该窗口可以查看或改变表中的 数据  视图可以基于表或基于视图  视图的两大作用: - 限制数据访问 - 简化命令文本 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 200 104 Ernst 6000 105 Austin 4800 106 Pataballa 4800 107 Lorentz 4200 108 Greenberg 12000 109 Faviet 9000 …… 106 行 10.1.2 使用视图的优势 视图限制数据的访问,因为视图能够选择性的显示表中的列。 视图可以用来构成简单的查询以取回复杂查询的结果。例如,视图能用于从多表中查询 信息,而用户不必知道怎样写连接语句。 视图对特别的用户和应用程序提供数据独立性,一个视图可以从几个表中取回数据。 视图提供用户组按照他们的特殊标准访问数据。 10.2 简单视图和复杂视图 第 二 节 简单视图和复杂视图  简单视图: - 数据仅来自一个表且不包含函数或数据分组 - 能通过视图执行所允许的任何操作。  复杂视图 - 视图中有连接、分组或使用了分组函数 - 除查询外,只能进行少数 DML 操作 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 201 视图有两种分类:简单和复杂,基本区别涉及 DML (INSERT、UPDATE 和 DELETE) 操 作。 下面是简单视图:数据仅来自一个表,不包含函数或数据分组,能通过视图执行 DML 操作。 下面是复杂视图:数据来自多个表,包含函数或数据分组,不总是允许通过视图进行 DML 操作。 10.2.1 创建简单视图 我们可以在 CREATE VIEW 语句中嵌入一个子查询来创建一个视图。创建视图的语法: CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view [(alias[, alias]...)]AS subquery [WITH CHECK OPTION [CONSTRAINT constraint]] [WITH READ ONLY [CONSTRAINT constraint]]; OR REPLACE 如果视图已经存在重新创建它 FORCE 创建视图,而不管基表是否存在 NOFORCE 只在基表存在的情况下创建视图(这是默认值) view 视图的名字 alias 为由视图查询选择的表达式指定名字(别名的个数必须与由视图选择的表达式的 个数匹配) subquery 是一个完整的 SELECT 语句(对于在 SELECT 列表中的字段你可以用别名) WITH CHECK OPTION 指定只有可访问的行在视图中才能被插入或修改 constraint 为 CHECK OPTION 约束指定的名字 WITH READ ONLY 确保在该视图中没有 DML 操作被执行 创建视图的原则: 定义一个视图的子查询可以包含复杂的 SELECT 语法,包括连分组和多个字查询。 定义视图的子查询不能包含 ORDER BY 子句,当你从视图取回数据时可以指定 ORDER BY 子句。 如果你没有为用 WITH CHECK OPTION 选项创建的视图指定一个约束名字,系统将以 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 202 SYS_Cn 格式指定一个默认的名字。 你可以用 OR REPLACE 选项改变视图的定义而无须删除和重新创建它,或重新授予以 前已经授予它的对象权限。 下面例子告诉我们如何定义和描述普通视图: 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; 10.2.2 视图中查询数据 一旦视图被创建,你就可以查询数据字典视图 USER_VIEWS 来看视图的名字和视图定 义。构成视图的 SELECT 语句的文本被存储在一个 LONG 列中。 当你用视图存取数据时,Oracle 服务器执行下面的操作: 1. 从数据字典表 USER_VIEWS 中取回视图定义。 2. 检查对视图的基表的数据存取权限。 3. 转换视图查询为一个在基表或表上的等价操作,换句话说,数据从基表得到,或更 新基表。 我们可以象从任何表中取回数据一样从视图取回数据,你既可以显示整个视图的内容, 也可以仅显示指定的行和列。例如: SELECT *FROM salvu50; ID_NUMBER NAME ANN_SALARY 120 Weiss 96000 121 Fripp 98400 122 Kaufling 94800 123 Vollman 78000 124 Mourgos 69600 125 Nayer 38400 126 Mikkilineni 32400 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 203 …… 45 行 10.2.3 创建复杂视图 下面的例子创建一个部门名称、最低薪水、最高薪水和部门平均薪水的复杂视图。注意, 例子中指定了表别名。如果视图中的任何列源自函数或表达式,别名是必需的。 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; Select * From Dept_Sum_Vu; NAME MINSAL MAXSAL AVGSAL Administration 4400 4400 4400 Accounting 8300 12000 10150 Executive 17000 24000 19333.33 IT 4200 9000 5760 Purchasing 2500 11000 4150 Human Resources 6500 6500 6500 Public Relations 10000 10000 10000 Shipping 2100 8200 3475.556 Finance 6900 12000 8600 Sales 6100 14000 8955.882 Marketing 6000 13000 9500 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 204 10.3 视图中的规则 10.3.1 视图中 DML 操作的执行规则 你可以通过视图对数据进行执行 DML 操作,但那些操作必须符合下面的规则。 如果视图中不包含下面的部分,你就可以从视图中删除数据: 组函数、GROUP BY 子句、DISTINCT 关键字、伪列 ROWNUM 关键字。对于查询返 回的每一个行,ROWNUM 伪列返回一个数,该数指示 Oracle 服务器从表或连接集合中选 择的行的顺序。选择的首行 ROWNUM 是 1,第二行是 2,等等。 如果视图中包含下面的部分就不能修改数据:组函数,GROUP BY 子句,DISTINCT 关键字,伪列 ROWNUM 关键字,用表达式定义的列 如果视图中包含下面的部分就不能通过视图添加数据:组函数,GROUP BY 子句, DISTINCT 关键字,伪列 ROWNUM 关键字,用表达式定义的列,基表中的 NOT NULL 列 第 三 节 视图中的规则 如对视图进行 DML 操作,要注意下列操作、函数对 DML 的限制:  组函数  GROUP BY 子句  DISTINCT 关键字  伪列 ROWNUM 关键字  With Check OPTION 子句 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 205 不在视图中 用Oracle7.3和以后版本,你可以修改包括带一些连接的视图。在幻灯片中描述的对DML 操作的约束也将应用于连接视图。在一个连接视图上,只有在一个基表上,任何 UPDATE、 INSERT 或 DELETE 语句才能够执行。如果在子查询连接中至少有一行有一个唯一索引, 那么在一个连接视图中修改基表是可能的。你可以查询 USER_UPDATABLE_COLUMNS 来 看是否在连接视图中的列可以被更新。 10.3.2 With Check OPTION 子句 使用 WITH CHECK OPTION 子句,可以通过视图执行引用完整性检查,你也可以在数 据库级别强制约束。视图能用于保护数据的完整性,但用途非常有限。 WITH CHECK OPTION 子句指出通过视图执行的 INSERTs 和 UPDATEs 不能创建视图 不能选择的行,因此,该子句在数据开始插入或更新时允许完整性约束和数据验证检查。 如果在没有选择的行上有一个执行 DML 操作的尝试,将会显示错误,如果指定了约束 名,会带在错误提示中。 UPDATE empvu20 SET department_id = 10 WHERE employee_id = 201; UPDATE empvu20 * ERROR at line 1: ORA-01402: view WITH CHECK OPTION where-clause violation 没有行被更新,因为如果部门号变成 10,该视图将不再能够看到雇员,因此,带 WITH CHECK OPTION 子句,视图只能看到部门 20 中的雇员,并且不允许通过视图改变那些看 不到的部门中的雇员。使用例子: CREATE OR REPLACE VIEW empvu20 AS Select * FROM employees WHERE department_id = 20 WITH CHECK OPTION CONSTRAINT empvu20_ck ; 10.3.3 With READ ONLY 子句 我们可在创建视图时带上WITH READ ONLY选项,以确保无DML操作发生。下面的例 子修改EMPVU10视图,以防止任何在视图上进行的DML操作。 如果用户没有提供约束名, ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 206 系统将以SYS_Cn格式指定一个名字,这里n是一个整数,在系统中约束名是唯一的。 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; ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 207 10.4 管理视图 10.4.1 修改视图 用 OR REPLACE 选项,允许创建视图时同名的视图已经存在,这样旧版本的视图会被 替换,这意味着视图可以在不被删除、重新创建和再次授予对象权限的情况下修改。当在 CREATE VIEW 子句中指定列别名时,不要望了别名的列表顺序与子查询的列顺序一样。 OR REPLACE 选项是从 Oracle7 开始有的,用 Oracle 的早期版本,如果要改变视图, 先要删除旧视图,再重新创建。例如: 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; 10.4.2 删除视图 我们可以用 DROP VIEW 语句来删除视图。该语句从数据库中删除视图定义。删除视图 不影响用于建立视图的基表。基于已删除视图上的其它视图或应用程序将无效。只有创建者 第 四 节 管理视图  视图无法在创建后修改  要修改视图只能使用重建命令以新的定义重建视 图  重建视图命令语法如下: CREATE OR REPLACE VIEW 视图名  注意,FROM 后的子查询可以被称为内建视图。 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 208 或具有 DROP ANY VIEW 权限的用户才能删除视图。view 是视图的名字。例如: DROP VIEW empvu80; 10.4.3 内建视图 内建视图由位于 FROM 子句中命名了别名的字查询创建。该字查询定义一个可以在主 查询中引用数据源。在下面的例子中,内建视图 b 从 EMPLOYEES 表中返回每一个部门的 部门号和最高薪水。主查询显示雇员的名字、薪水、部门号和该部门的最高薪水,WHERE a.department_id = b.department_id AND a.salary < b.maxsal 子句取回所有收入少于该部门最 高薪水的雇员。 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; LAST_NAME SALARY DEPARTMENT_ID MAXSAL Popp 6900 100 12000 Urman 7800 100 12000 Sciarra 7700 100 12000 Chen 8200 100 12000 Faviet 9000 100 12000 Colmenares 2500 30 11000 Himuro 2600 30 11000 Tobias 2800 30 11000 Baida 2900 30 11000 Khoo 3100 30 11000 …… 95 行 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 209 10.5 Top-N 分析 Top-N 查询在需要基于一个条件,从表中显示最前面的 n 条记录或最后面的 n 条记录时 是有用的。该结果可以用于进一步分析,例如,用 Top-N 分析你可以执行下面的查询类型: 在中挣钱最多的三个人,公司中最新的四个成员,销售产品最多的两个销售代表,过去 6 个月中销售最好的 3 种产品。 Top-N 查询使用一个带有下面描述的元素的一致的嵌套查询结构: 子查询或者内建视图产生数据的排序列表,该子查询或者内建视图包含 ORDER BY 子 句来确保排序以想要的顺序排列。为了取回最大值,需要用 DESC 参数。 在最后的结果集中用外查询限制行数。外查询包括下面的组成部分: ROWNUM 伪列,它为从子查询返回的每一行指定一个从 1 开始的连续的值一个 WHERE 子句,它指定被返回的 n 行,外 WHERE 子句必须用一个<或者<=操作。语法: Select Column_List, Rownum From (Select Column_List From Tableorder By Top - n_Column) Where Rownum <= n 从 EMPLOYEES 表中显示挣钱最多的 3 个人的名字及其薪水: 第 五 节 Top-N 分析  Top-N 分析就是查询前几名的意思  Top-N 分析通过 ROWNUM 实现  Top-N 分析中必须使用 Order by 排序子句  Top-N 分析中通常会有内建视图,一般的方法是先 对内建视图的某一列或某些列排序,然后对此内建 视图使用 ROWNUM 取前多少行。 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 210 Select Rownum As Rank, Last_Name, Salary From (Select Last_Name, Salary From Employees order By Salary Desc) Where Rownum <= 3; RANK LAST_NAME SALARY 1 King 24000 2 Kochhar 17000 3 De Haan 17000 子用内建视图 E 显示公司中 4 个资格最老的雇员: Select Rownum As Senior, e.Last_Name, e.Hire_Date From (Select Last_Name, Hire_Date From Employees Order By Hire_Date) e Where Rownum <= 4; SENIOR LAST_NAME HIRE_DATE 1 King 1987-6-17 2 Whalen 1987-9-17 3 Kochhar 1989-9-21 4 Hunold 1990-1-3 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 211 10.6 物化视图简介 物化视图首先在 Oracle8i 中引入,物化视图应看作是一种特殊的视图,它物理上存在 于数据库内部,可以包括联接和/或聚合。它能够在执行之前预先计算开销大的联接和聚合 操作,因此它的存在缩短了查询执行时间。 现在 DBA 将仅须在开始时创建物化视图,而无论数据源何时发生变化,它都将被自动 更新。此外还有一个概要顾问组件,它向 DBA 推荐创建、删除和保留哪些物化视图。 数据仓库或数据库用户将可以体会到使用物化视图的最大好处之一,DBA 无须再告诉他们 存在哪些物化视图。他们可以对数据库中的表或视图编写自己的查询。然后 Oracle 服务器 的查询重写机制将自动重写 SQL 查询以使用物化视图。这样就大大缩短了查询响应时间, 终端用户无须“了解概要”。 当向数据仓库终端用户问起他们希望从中获得什么,大部分人都会回答:快速准确的信 息。但是这也给数据仓库设计者出了个大难题:为了回答“在 y 地点我们卖出多少件 x 产 品”,同时希望避免读取表中的每一行,必须建立一条到数据的快速路由。 解决此问题最常见的办法之一就是创建概要表,Oracle 将其称为物化视图。这一工作 包括首先要理解典型负荷,然后创建规模非常小的物化视图,物化视图中可以包含所需信息 第 六 节 物化视图简介  物化视图和视图的区别是,视图并不真正的包含数 据,而物化视图则真正的包含数据  物化视图等于是对其基表的一种预处理  物化视图中的数据可以随基表的变化而变化  物化视图可以加快某些查询操作的速度,但它减慢 了 DML 的速度 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 212 的联接和/或聚合。例如,为了回答前面的问题,物化视图中每种产品对应于一行,指明每 个区域的销售量。因此如果一家公司在 5 个地点销售 2000 件产品,则将要读取的最大行数 始终为 10000,而无论已经售出多少商品。 很明显,物化视图必须保证精确,但该技术意味着终端用户现在需要读取的行数很少, 因此可以始终快速地接收结果。数据库容量已经增长到兆兆字节,因此使用这样的方法来缩 短查询响应时间就显得越来越重要。今天许多站点都创建了自己的概要表,因此使用 Oracle 概要管理所带来的额外好处是: 1、Oracle 中的查询重写机制是透明的并采用物化视图(即使它仅能部分满足查询的需要)。 2、具有高级的查询重写,可以使用物化视图对不同聚合级别(例如按照星期、月和年)进 行报告。 3、自动化机制刷新物化视图,单个请求刷新所有物化视图。 4、DBA 不再需要花时间查找应创建哪些物化视图。系统将基于过去对数据库或数据仓库 的查询,向 DBA 提供有关需要哪些概要的信息。 组成概要管理的有五个组件: 1、维度。 2、物化视图。 3、刷新。 4、查询重写。 5、概要顾问。 并不需要使用所有组件,但所选用的组件越多,获得的优势就越多。现在我们将详细探 讨这些组件。 用于物化视图的模式类型或设计没有什么限制。因此在数据仓库环境中,模 式可以是雪花式的设计,但这并不是必须的。 对于熟悉产品系统中数据库设计技术的设计者来说,在一个数据仓库中必须使用不同的 规则和技术。例如,产品数据库通常是规范化的,因此在这种情况下,时间维的表示方法最 好是采用三个表:日、月、年。联接条件应该满足:将每个日期行连接到一个(仅一个)月 份行,每个月份行连接到一个(仅一个)年份行。数据仓库实现通常将导致一个完全非规范 化的的时间维表,其中日期、月份、年份栏都处于同一个表中。不过,无论设计使用的是规 范化还是非规范化表,都可以使用物化视图。 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 213 10.7 总结 什么是视图? 一个视图基于一个表或者另一个视图,其作用好象是一个窗口,通过该窗口表中的数据 能够被观察或修改。视图不包含数据,视图的定义被存储在数据字典中,你可以在 USER_VIEWS 数据字典表中查看视图的定义。 视图的优点: 限制数据访问 简化查询 提供数据独立性 提供相同数据的多种视图 能够在不影响底层数据的情况下被删除 视图选项: 可以是基于一个表的简单视图 可以是基于多表或包含函数组的复杂视图 可以用相同的名字替换其它视图 可以包含检查约束 可以是只读的 什么是物化视图 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 214 第 11 章 其他数据库对象 完成本章后, 您应当能够执行下列操作:创建、维护和使用序列,创建和维护索引,创 建私有和公有同义词。 第 十一 章 其他数据库对象  其他数据库对象的作用  序列:产生有规律的顺序值  索引:加快查询少量行的速度  同义词:降低访问长对象名时的复杂度 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 215 11.1 其他数据库对象的作用 第 一 节 其他数据库对象的作用 许多应用程序要求使用唯一的数字作为主键 的值。你即可以在应用程序中构建代码来处理这种 需求,也可以用一个序列来产生唯一的数字。 如果你想要增进某些查询的性能,你应该考虑 创建一个索引。你也可以用索引在列或列的集合上 强制唯一性。你可以用同义词为对象提供可替代的 名字。 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 216 11.2 序列 11.2.1 序列的语法 序列是用户创建的数据库对象,序列可以被多个用户共享以产生唯一的整数。 序列的 一个典型的用途是创建一个主键的值,它对于每一行必须是唯一的。序列由一个Oracle内部 程序产生并增加或减少。 序列是一个节省时间的对象,因为它可以减少应用程序中产生序 列程序的代码量。 序列号独立于表被存储和产生,因此,相同的序列可以被多个表使用。 用 CREATE SEQUENCE 语句自动产生序列数。语法: CREATE SEQUENCE Sequence [INCREMENT BY n] [START WITH n] [{MAXVALUE n| NOMAXVALUE}] [{MINVALUE n| NOMINVALUE}] [{CYCLE | NOCYCLE}] [{CACHE n| NOCACHE}]; 第 二 节 序列  序列的创建命令:CREATE SEQUENCE Sequence  查询序列主要通过:NEXTVAL 和 CURRVAL 两个 伪列实现  可以使用 ALTER SEQUENCE sequence 修改序列  DROP SEQUENCE sequence 命令将删除某个指定 的序列 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 217 sequence 是序列发生器的名字 INCREMENT BY n 指定序列号之间的间隔,在这儿 n 是一个整数 (如果该子句 被省略,序列增量为 1) 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 个值) 如果 INCREMENT BY 值是负数,序列是降序。另外,ORDER | NOORDER 选项可用, ORDER 选项保证序列值按顺序产生,如果你将序列用于产生主键值它是不重要的,该选项 仅与 Parallel Server(并行服务)选项有关。 如果序列值被高速缓冲,如果系统故障它们将被丢失。 11.2.2 定义及查询序列 下面的例子创建一个序列,并被命名为 DEPT_DEPTID_SEQ , 该 序 列 用 于 DEPARTMENTS 表的 DEPARTMENT_ID 列,该序列从 120 开始,不允许高速缓冲的,不循 环。如果序列用于产生主键值,不使用 CYCLE 选项,除非你有一个可靠的机制比序列循环 更快地清除旧的行。序列不依赖于一个表,通常,你应该命名序列;可是序列可以被用在任 何地方,而不管它的名字。 CREATE SEQUENCE dept_deptid_seq INCREMENT BY 10 START WITH 120 MAXVALUE 9999 Nocache ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 218 NOCYCLE; 一旦创建了序列,它就被文本化在数据字典中。因为序列是一个数据库对象,你可以在 USER_OBJECTS数据字典表中识别它。 Select object_name,object_id,object_type From USER_OBJECTS Where object_name = 'DEPT_DEPTID_SEQ'; 你也可以从 USER_SEQUENCES 数据字典视图中用选择确认序列的设置。 Select * From USER_SEQUENCES Where sequence_name = 'DEPT_DEPTID_SEQ' 11.2.3 NEXTVAL 和 CURRVAL 伪列 在创建序列后,它产生连续的数给你在表中使用。用 NEXTVAL 和 CURRVAL 伪列引 用序列值。NEXTVAL 伪列用于从指定的序列中取回连续的序列数的下一个值。你必须用序 列名限定 NEXTVAL,当你引用 sequence.NEXTVAL 时,一个新的序列数被产生并且当前的 序列数被放入 CURRVAL。CURRVAL 伪列被用于查阅当前当前用户刚才产生的序列数, NEXTVAL 必须被在 CURRVAL 可以被引用之前用于在当前用户的会话中产生一个序列数, 你必须用序列名限定 CURRVAL,当 sequence.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 表达式 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 219 11.2.4 使用序列 在内存中缓冲序列可以对序列值更快地存取,在你第一次使用到序列时被移入缓存。对 于下一个序列值的每次请求从缓存的序列中找回。在最后一个序列值被使用后,对于序列的 下一个请求拉出另一个在内存中的序列缓存。 尽管序列发生器发布无间隙的序列数,但因为这种行为不依赖于提交和回退,如果你回 退包含一个序列的语句,该序列数将丢失。 另一个能在序列中导致间隙产生的事件是系统崩溃,如果序列在内存中缓冲值,那么, 如果系统崩溃那些值将丢失。 因为序列不直接依赖于表,所以,相同的序列可以被用于多个表,如果你这样做了,每 个表都将包含序列数的间隙。 查看下一个可用的序列值,而不增加它 如果序列用带 NOCACHE 的选项创建的,就可以在序列值不增加的情况下用查询 USER_SEQUENCES 表的方法,查看下一个可用的序列值。 经常使用的序列用带缓存创建将增进效率,对于缓存的序列,没有办法找出下一个可用 的序列将是什么,该值不实际获得和使用,因此,建议用户不要查找下一个序列值,而相信 每次一个序列被用于一个 INSERT 语句时系统会提供一个唯一值。 假定一个称为 EMPLOYEE_SEQ 序列已经被创建用来产生新的雇员号。下面的例子在 DEPARTMENTS 表中插入一个新的部门。在该例子中使用 DEPT_DEPTID_SEQ 序列产生一 个新的部门号: 你可以查看当前的序列值: SELECT dept_deptid_seq.CURRVAL FROM dual; CURRVAL --------- 120 现在设想你想要雇用雇员充当新部门的职员,对所有新雇员被执行的 INSERT 语句可以 包含下面的代码: INSERT INTO employees (employee_id, department_id, ...) VALUES (employees_seq.NEXTVAL, dept_deptid_seq .CURRVAL, ...); 假定一个称为 EMPLOYEE_SEQ 序列已经被创建用来产生新的雇员号。 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 220 11.2.5 修改序列 如果序列达到 MAXVALUE 限制,将再无来自序列的新值产生,并且你将收到一个序 列已经超过 MAXVALUE 的错误指示。为了继续使用序列,你可以用 ALTER SEQUENCE 语句修改该序列。 语法 ALTER SEQUENCE sequence [INCREMENT BY n] [{MAXVALUE n | NOMAXVALUE}] [{MINVALUE n | NOMINVALUE}] [{CYCLE | NOCYCLE}] [{CACHE n | NOCACHE}]; 例如: ALTER SEQUENCE dept_deptid_seq INCREMENT BY 20 MAXVALUE 999999 Nocache; 修改序列的原则 你必须是被修改序列的所有者,或者有ALTER权限。 用ALTER SEQUENCE语句,只有以后的序列数会受影响。 用ALTER SEQUENCE语句,START WITH选项不能被改变。为了以不同的数重新开始 一个序列,该序列必须被删除和重新创建。 一些验证被执行,例如,一个新 MAXVALUE 如果小于当前的序列值就不能用。 11.2.6 删除序列 使用DROP SEQUENCE语句从数据字典中删除一个序列。你必须是被删除序列的所有 者或者有DROP ANY SEQUENCE权限来删除它。语法: DROP SEQUENCE sequence; 例如: DROP SEQUENCE dept_deptid_seq; ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 221 11.3 索引 Oracle 服务器索引是一个方案对象,索引能用指针加速行的取回,索引可以被显式创建, 也可以被自动创建,如果你在列上没有索引,那么将发生全表扫描。索引提供对表中行的直 接和快速访问,它的目的是用已索引的路径快速定位数据以减少磁盘 I/O。索引由 Oracle 服 务器自动使用和维护,一旦一个索引被创建,它就不再需要用户直接管理。 索引逻辑地和物理地独立于他们索引的表,这意味者索引可以在任何时候被创建或删除, 并且不影响基表或其它的索引。当你删除表时,相应的索引也被删除。 创建索引的决定是是一个全局的、高级的决定,对于数据库管理员,索引的创建与维护 创建是一个经常性的工作。在谓词 WHERE 字句中引用有索引的列,如果没有修改带函数 或表达式的列的索引。ROWID 是一个十六进制的串,表示包含块定义的行地址,行的位置 在块中,并且有数据库文件标识符,访问任何指定行的最快的方法是引用它的 ROWID。 11.3.1 创建索引 可以创建两种类型的索引,一种是唯一性索引:当你在一个表中定义一个列为主键,或 第 三 节 索引  索引目的是加快在一个巨大的表中查询少量行的 速度  索引中的值将会随表的改变而改变,因此索引增加 了 DML 操作时的负担  索引的使用是透明的,只要有合适的索引,Oracle 会自动的使用。  不必要的索引应即时删除 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 222 者定义一个唯一键约束时 Oracle 服务器自动创建该索引,索引的名字习惯上是约束的名字。 另一种索引类型是非唯一索引,它可以由用户创建,例如,你可以创建一个 FOREIGN KEY 列索引用于一个查询中的连接来改进数据取回的速度。 可以手工创建唯一索引,但建议创建一个唯一约束,这样会隐式创建一个唯一索引 为了在你自己的方案中创建索引,你必须有 CREATE TABLE 权限。为了在任何方案中 创建索引,你需要 CREATE ANY INDEX 权限或者在正在创建索引的表上的 CREATE TABLE 权限。 在语法中的另一个选项是 UNIQUE 关键字,强调你应该在表上显式地定义唯一索引。 在表中可用约束代替定义唯一性索引,Oracle 服务器用自动在唯一键上定义一个唯一索在引 强制唯一完整性约束。 创建索引的语法: CREATE INDEX index ON table(column[, column]...); index 是索引的名字 table 是表的名字 column 是表中被索引的列的名字 例如: CREATE Index emp_last_name_idx ON employees(last_name); 11.3.2 创建索引的时机 在表上建立更多的索引并不意味者更快地查询,在带索引的表上被提交的每个 DML 操 作意味者索引必须更新;与表联系的索引越多,对 Oracle 服务器的影响越大,Oracle 服务 器在每次 DML 操作之后必须更新所有的索引。 因此,你应该只在下面的情况下创建索引: 列包含一个大范围的值、列包含大量的空值、一个或多个列在 WHERE 子句或连接条件中 被频繁使用、表很大并且大多数查询所期望的返回行数少于总行数的 2–4%。 不要忘记,如果你想要强制非唯一,你应该在表中定义一个唯一的约束,然后唯一索引 被自动创建。 复合索引 (也称为连接索引) 是在一个表中的多个列上创建的索引,在复合索引中的列 可以任何顺序出现,并且不需要与在表中的列相一致。复合索引可以加速 SELECT 语句的数 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 223 据取回速度,在 SELECT 语句中 WHERE 子句引用复合索引的所有列或部分主要的列。 下面的情况通常不值得创建索引: 表很小,不经常在查询中作为条件被使用的列,大多数查询期望取回多于表中百分之 2 到 4 的行,表经常被更新,被索引的列作为表达式的的一部分被引用。 11.3.3 查询索引 从 USER_INDEXES 数据字典视图可以确认索引的存在。你也可以查询 USER_IND_COLUMNS 视图,检查与索引有关的列。 下面的例子查询 EMPLOYEES 表上所有已创建的索引,包括受影响的列的名字和索引 的唯一性。 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'; INDEX_NAME COLUMN_NAME COL_POS UNIQUENESS EMP_EMAIL_UK EMAIL 1 UNIQUE EMP_EMP_ID_PK EMPLOYEE_ID 1 UNIQUE EMP_LAST_NAME_IDX LAST_NAME 1 NONUNIQUE 11.3.4 基于函数的索引 基于函数的索引用 UPPER (列名)或 LOWER (列名)关键字定义,它允许大小写敏感的查 询,例如,下面的索引: CREATE INDEX upper_last_name_idx ON employees (UPPER(last_name)); 使得处理查询容易,例如: SELECT * FROM employees WHERE UPPER(last_name) = 'KING'; 确保 Oracle 服务器使用索引而不是执行一个全表扫描,注意,在子查询中的函数值不 能为空,例如,下面的语句保证使用索引,但如果没有 WHERE 子句,Oracle 服务器还可能 执行一个全表扫描: ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 224 SELECT * FROM employees WHERE UPPER (last_name) IS NOT NULL ORDER BY UPPER (last_name); 下面的例子产生一个基于函数的索引: CREATE INDEX upper_dept_name_idx ON departments (UPPER(department_name)); 为了创建基于函数的索引,你必须有 CREATE INDEX 和 QUERY REWRITE 系统权限。 为了在另一个方案或另一个方案的表上创建索引,你必须有 CREATE ANY INDEX 和 GLOBAL QUERY REWRITE 权限。在基于函数的索引中,表的所有者也必须有关于函数的 使用 EXECUTE 对象的权限。 同事确保下面的两个参数符合显示出来的值: SQL> show parameter query NAME TYPE VALUE ------------------------------------ ----------- query_rewrite_enabled string TRUE query_rewrite_integrity string enforced 11.3.5 删除索引 你不能修改索引,为了改变索引,你必须先删除它,然后重新创建它。用 DROP INDEX 语 句从数据字典中删除索引,为了删除索引,你必须是索引的所有者,或者有 DROP ANY INDEX 权限。果你删除一个表,相关的索引和约束将被自动删除,但视图和序列将保留。 DROP INDEX index; index 是索引的名字 例如: DROP INDEX upper_last_name_idx; ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 225 11.4 同义词 11.4.1 概念语法 为一个对象创建同义词,为了查阅另一个用户所拥有的表,你需要将创建该表的用户名 加句点作为前缀加在表名前面。创建一个同义词可以除去对象名必须带的方案限制,并提供 给你一个可替换表名、视图名、序列名和过程名或其它对象名。该方法对具有特别长的对象 的名字很有用。语法: CREATE [PUBLIC] SYNONYM synonym FOR object; PUBLIC 创建一个可以被所有用户访问的同义词 synonym 是要被创建的同义词的名字 object 指出要创建同义词的对象 原则 对象不能包含包。 一个私有同义词名字对于同一个用必须与所有其它的对象不同。 第 四 节 同义词  语法形式如下: CREATE [PUBLIC] SYNONYM synonym FOR object  同义词其实在使用时是一种替换操作,Oracle 会自 动将同义词替换为它所代表的对象名,再对对象进 行操作  如果有表和同义词相同,Oracle 优化操作表 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 226 11.4.2 创建删除同义词 下面的例子创建了一个 DEPT_SUM_VU 视图的用于快速引用的同义词。 数据库管理员可以创建一个可为所有用户访问的公共同义词。下面的例子为 Alice 的 DEPARTMENTS 表创建一个公共同义词: CREATE PUBLIC SYNONYM dept FOR alice.departments; Synonym created. 删除同义词 用 DROP SYNONYM 语句删除一个同义词,只有数据库管理员可以删除一个公共同义词。 DROP PUBLIC SYNONYM dept; Synonym dropped. 更多信息,见 Oracle9i SQL Reference,“删除同义词” 在 Oracle 服务器中,DBA 可以明确授予 CREATE PUBLIC SYNONYM 权限给任何用户,这 样普通用户就可以创建公共同义词。 11.5 总结 在本章中你应该已经学习了关于另外的一些数据库对象,包括序列、索引和同义词。 序列发生器可以用来为表中的行自动产生序列数,这样可以节省时间并且减少应用程序 的代码量。序列是可以与其它用户共享的数据库对象,关于序列的信息可以在数据字典的 USER_SEQUENCES 表中找到。为了使用一个序列,用 NEXTVAL 或 CURRVAL 伪列引用它。引用 sequence.NEXTVAL 可以取回序列的下一个数。引用 sequence.CURRVAL 可以返回当前可用序 列数。 索引被用于改进查询的速度,用户可以在 USER_INDEXES 数据字典视图中查看索引的定 义。索引可以被创建者或者其它有 DROP ANY INDEX 权限的用户用 DROP INDEX 语句删除。 为了方便,使用 CREATE SYNONYM 语句,数据库管理员可以创建公共同义词,普通用户 可以创建私有同义词。同义词允许短名字或者用于对象的替换名。用 DROP SYNONYM 语句删 除同义词。 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 227 第 12 章 控制用户访问 本章学习以下内容:创建用户,创建角色使得安全模式的设 置和管理容易,使用 GRANT 和 REVOKE 语句授予和撤消对象权限, 创建和访问数据库链接。 第 十二 章 控制用户访问  用户与创建用户  权限、授权、角色  维护用户及授权  数据库链接 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 228 12.1 控制用户访问的意义 第 一 节 控制用户访问的意义 在多用户环境中,你想要控制数据库访问和使用的安全,可以用 下面 Oracle 服务器提供的数据库安全措施: 控制数据库访问、在数据库中只允许访问指定的对象、用 Oracle 数据字典确认给予的和收回的权限、创建数据库对象的同义词 数据库安全可以被划分为两个范畴:系统安全和数据安全。系统 安全在系统级别访问和使用数据库,例如,用户名和口令,分配给用 户的磁盘空间和用户能够执行的系统操作;数据库安全包括访问和使 用数据库对象和用户能够对数据库对象进行的操作。 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 229 12.2 用户及权限 12.2.1 权限介绍 权限是执行特殊 SQL 语句的权利。数据库管理员 (DBA) 是一个具有授予用户访问数 据库及其对象的能力的高级用户。用户需要系统权限来访问数据库,需要对象权限来操纵数 据库中对象的内容。用户也可以将被授予的权限给其它用户或者角色,角色是相关权限的命 名分组。 方案是对象的集合,例如,视图和序列的集合。方案被数据库用户多拥有,并且与用户 有相同的名字。 12.2.2 系统权限 对用户和角色有 100 多个不同的可用系统权限,系统权限有数据库管理员(DBA)提供。 下面是典型的 DBA 权限: 系统权限 授权的操作 第 二 节 用户及权限  权限:它用来控制用户对数据库中数据的使用权  权限有两大类: - 系统权限 - 对象权限  刚创建的用户没有任何权限  要为用户分配一定的权限后,用户才能使用 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 230 CREATE USER 受让人可以创建其他 Oracle 用户 (需要有 DBA 角色权限)。 DROP USER 受让人可以删除另一个用户。 DROP ANY TABLE 受让人可以删除在任意方案中的表。 BACKUP ANY TABLE 受让人用导出实用程序可以备份在任何方案中的任何表。 SELECT ANY TABLE 受让人可以查询在任何方案中的表、视图或快照。 CREATE ANY TABLE 受让人可以在任何方案中创建表。 12.2.3 创建用户 DBA 通过执行 CREATE USER 语句来创建用户,在这时用户没有任何权限。DBA 可以 给用户授予权限,这些权限决定用户能够在数据库级别做什么。有删节的语法: CREATE USER userIDENTIFIED BY password; user 是被创建的用户的名字 password 指定用户必须用该口令登录 例如: CREATE USER scott IDENTIFIED BY tiger; 12.2.4 授权系统权限 现在已经创建了一个用户,DBA 可以指定权限给该用户。使用下面的语法: GRANT privilege[, privilege...]TO user [, user| role, PUBLIC...]; 应用程序的开发者,例如,可能有下面的系统权限:CREATE SESSION 连接到数据库, CREATE TABLE 在用户的方案中创建表,CREATE SEQUENCE 在用户的方案中创建序列, CREATE VIEW 在用户的方案中创建视图,CREATE PROCEDURE 在用户的方案中创建存 储过程、函数或包。 当前会话的系统权限可以在字典视图 SESSION_PRIVS 中找到。 下面的例子进行系统权限的授权: GRANT create session, create table, create sequence, create View TO scott; ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 231 12.3 角色 12.3.1 角色概念 角色是命名的可以授予用户的相关权限的组,该方法使得授予、撤回和维护权限容易的 多。一个用户可以使用几个角色,并且几个用户也可以被指定相同的角色。角色典型地为数 据库应用程序创建。 创建和分配角色,首先,DBA 必须创建角色,然后,DBA 可以分配角色给角色和用户。 语法: CREATE ROLE role; role 要被创建的角色的名字 现在角色已被创建,DBA 可以用 GRANT 语句给用户指定角色,也可以指定权限给角 色。一个命名的相关权限组、可以授予用户、简化授予和撤消权限的过程、由 DBA 创建。 第 三 节 角色  角色是多个权限的组合  作用是方便权限的管理  角色可以包含角色,也就是角色可以嵌套  将角色授予某个用户后,用户将可使用角色中的所 有权限 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 232 12.3.2 创建并授权角色 下面的例子创建一个经理角色,然后允许经理创建表和视图,然后授予该DeHaan和 Kochhar经理角色,现在DeHaan和Kochhar可以创建表和视图。如果用户被授予多个角色, 他们收到所有角色的联合权限。 CREATE ROLE manager; GRANT create table, create view TO manager; GRANT create table, create view TO manager; GRANT manager TO DEHAAN, KOCHHAR; GRANT manager TO DEHAAN, KOCHHAR ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 233 12.4 对象的维护 12.4.1 改变口令 DBA 创建一个帐号并为每个用户初始化一个口令,你可以用 ALTER USER 语句改变你 的口令。语法: ALTER USER user IDENTIFIED BY password; user 是用户的名字 password 指定新的口令 尽管该语句可以用于改变你的口令,还有许多其它的选择,为了改变任何其它的选项你 必须有 ALTER USER 权限。例如 ALTER USER scott IDENTIFIED BY lion; ALTER USER scott IDENTIFIED BY "lion"; 12.4.2 对象权限的意义 对象权限 是在指定的表、视图、序列或过程上执行指定动作的权限或权利。每个对象 第 四 节 对象的维护  改变口令  对象权限的意义  授权对象权限  WITH GRANT OPTION 和 PUBLIC  确认授权的权限  撤销对象权限 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 234 都有一个特殊的可授予的权限集。幻灯片上的表列出了各种对象的权限注意,可用于序列的 权限只有 SELECT 和 ALTER。UPDATE、REFERENCES 和 INSERT 权限可以通过指定一个 可更新列的子集被限制,SELECT 的权限可以通过创建带一个列子集的视图并且只授予 SELECT 权限来限制。一个在同义词上被授予的权限将转换为由同义词引用的基表上的权限。 可以如下表来理解对象权限: 对象权限 TABLE VIEW SEQUENCE PROCEDURE ALTER √ √ DELETE √ √ EXECUTE √ INDEX √ INSERT √ √ REFERENCES √ √ SELECT √ √ √ UPDATE √ √ 12.4.3 授权对象权限 不同的对象权限对于不同类型的方案对象的是有用的,一个用户自动拥有包含在该用户 的方案中的所有对象权限,一个用户可以授予该用户所拥有的任何方案对象上任何对象权限 给另一个用户或角色。如果授权包括 WITH GRANT OPTION 选项,那么,得到权限的用户可以 再将权限授予其他的用户;否则,受让人可以使用权限,但不能授予它给其他用户。语法: GRANT object_priv[(columns)] ON object TO {user|role|PUBLIC} [WITH GRANT OPTION]; object_priv 是将被授予的对象权限 ALL 指定所有对象权限 columns 从一个表或视图中指定被授予权限的列 ON object 是权限被授予的对象 TO 指定权限被授予谁 PUBLIC 授予权限给所有用户 WITH GRANT OPTION 允许被授予权限的人再授予对象权限给其他用户和角色 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 235 为了授予权限到一个对象上,对象必须在你自己拥有的方案中,或者你必须被用 WITH GRANT OPTION 选项授予了对象权限。 一个对象所有者可以授予任何该对象上的对象权限给任何其他的用户或者数据库的角 色。 任何对象的所有者自动地获得该对象所有对象权限。 第一个例子授予用户 Sue 和 Rich 查询当前用户 EMPLOYEES 表的权限。第二个例子授予 在 DEPARTMENTS 表中指定列上的 UPDATE 权限给 Scott 和经理角色。 如果 Sue 或 Rich 现在想要从雇员表中 SELECT 数据,他们必须用的语法是: SELECT * FROM scott.employees; 作为选择。他们可以为该表创建一个同义词并且从同义词中选择: CREATE SYNONYM emp FOR scott.employees; SELECT * FROM emp; 注:DBAs 通常分配系统权限;任何拥有对象的用户都可以授予对象权限。 授予查询权限到 EMPLOYEES 表上: GRANT Select ON employees TO sue, rich; 授予权限到以更新指定的列到用户和角色: GRANT update (department_name, location_id) ON departments TO scott, manager; 12.4.4 WITH GRANT OPTION 和 PUBLIC 带 WITH GRANT OPTION 子句被授予的权限可以被受让人传递到其他的用户和角色。当授 予者的权限被撤消时,用 WITH GRANT OPTION 子句授予的对象权限也被撤消。 如果一个语句不用对象的全名,Oracle 服务器隐式地用当前的用户名 (或方案) 作为 对象名的前缀。例如,如果用户 Scott 查询 DEPARTMENTS 表,系统将从 SCOTT.DEPARTMENTS 表中做选择。 如果一个语句不用对象的全名,并且当前用户不拥有该名字的对象,系统将用 PUBLIC. 作为对象名的前缀。例如,如果用户 Scott 查询 USER_OBJECTS 表,但 Scott 不拥有该表, 系统将用 PUBLIC.USER_OBJECTS 公共同义词的方法从数据字典视图中进行选择。 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 236 第一个例子给予用户Scott访问你的DEPARTMENTS表的权限,包括查询表和添加表中行, 该例子允许 Scott 再给予其他用户这些权限。 表的所有者可以用 PUBLIC 关键字给所有用户授权。 第二个例子允许所有用户从 Alice 的 DEPARTMENTS 表中查询数据。 给一个用户授权以级联权限授予: GRANT select, Insert ON departments TO scott WITH GRANT OPTION; 允许所有在系统上的用户从 Alice 的 DEPARTMENTS 表中查询数据: GRANT Select On alice.departments To PUBLIC; 12.4.5 确认已授予的权限 如果你试图执行一个为授权的操作,例如从你没有删除权限的表中删除行,Oracle 服 务器将不允许该操作发生。如果你收到 Oracle 服务器错误信息 “table or view does not exist,”说明发生了下面的错误: 指定的表或视图不存在,试图在一个你没有适当权限的表或视图上执行一个操作。 你可以通过访问数据字典来查看你所有的权限。下表中描述了各种数据字典视图。 数据字典视图 说明 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 授予用户的系统权限 12.4.6 撤销对象权限 可以用 REVOKE 语句撤消授予其他用户的对象权限。当你用 REVOKE 语句时,你指 定要从用户那里撤消的权限,并且通过 WITH GRANT OPTION 子句被级联授权的那些用户 的权限也将被撤消。语法: REVOKE {privilege [, privilege...]|ALL} ON object FROM {user[, user...]|role|PUBLIC} ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 237 [CASCADE CONSTRAINTS]; CASCADE CONSTRAINTS 用于删除任何与该对象相关的约束和对象,例如索 引、触发器、权限、完整性约束等。 下面的例子撤消给予用户 Scott 在 DEPARTMENTS 表上的 SELECT 和 INSERT 权限。 Revoke select, Insert ON departments FROM scott; 如果一个用户被用 WITH GRANT OPTION 子句授予权限,那么,该用户用 WITH GRANT OPTION 子句授予权限给其他用户,所以可能产生一个很长的受让人的链,但该链不允许循环。如果 所有者从撤消了一个用户的权限,那么,所有授予的权限将级联地被撤消。 例如,如果用户 A 用 WITH GRANT OPTION 子句授予表上的 SELECT 权限给用户 B,用户 B 也可以再用 WITH GRANT OPTION 子句授予用户 C SELECT 权限,然后用户 C 还可以授予用户 D SELECT 权限,如果用户 A 撤消了用户 B 的权限,那么,被授予用户 C 和 D 的权限也被撤 消。 12.5 数据库链接 一个数据库链接是一个指针,该指针定义一条从 Oracle 数据库服务器到另一个数据库 第 五 节 数据库链接  使用数据库链接可以访问其他数据库中的数据  数据库链接可以用来构建分布式数据库  Oracle 数据库链接的使用非常简单,下面的语句创 建一个数据库链接: CREATE PUBLIC DATABASE LINK hq.acme.com USING 'sales'; ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 238 服务器的通信路径。链接指针实际上在一个数据字典表中被定义为一个条目,为了访问该链 接,你必须被连接到包含数据字典条目的本地数据库。 一个数据库链接是一条有感知的路径,一个客户可以通过该路径连接到本地数据库 A, 再用存储在数据库 A 中的链接访问数据库 B 中的信息,但连接到数据库 B 的用户不能使用同 一个链接来访问数据库 A 中的数据,他们必须定义一个链接,并存储在数据库 B 的数据字典 中。 一个数据库链接的连接提供本地用户访问远程数据库上数据的能力,为了产生这种连接, 在分布式计算机系统中的每个数据库必须有一个唯一的全局数据库名,全局数据库名唯一地 标识一个在分布式系统中的数据库服务器。 数据库链接最大的好处是,允许用户访问在远程数据库中的另一个用户的对象,但是他 们被该对象所拥有的权限集合所限制,换句话说,一个本地用户可以访问远程数据库而不需 要是远程数据库上的用户。典型地,DBA 负责创建数据库链接。字典视图 USER_DB_LINKS 包 含有关用户可以访问的链接信息。你不能在远程对象上授予权限。 下面的例子创建了用户 SCOTT 用全局名 HQ.ACME.COM 访问在远程数据库上的 EMP。 CREATE PUBLIC DATABASE LINK hq.acme.com USING 'sales'; 下面的例子使用数据库连接: SELECT * FROM emp@HQ.ACME.COM; 下面的例子删除一个数据连接: Drop DATABASE LINK hq.acme.com; 12.6 总结 DBA 用指定权限给用户来为用户建立初始数据库安全。 DBA 创建的用户必须有一个口令。DBA 也负责为用户建立初始系统权限。 一旦用户已经创建了一个对象,用户可以用 GRANT 语句传递任何可用的对象权限给另一 个用户或所有用户。 DBA 可以用 CREATE ROLE 语句创建角色来传递系统权限或对象权限的集合给多个用户。 角色使得授予或撤消权限更容易。 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 239 用户可以 ALTER USER 语句改变他们的口令。 你可以用 REVOKE 语句删除用户的权限。 用数据字典视图,用户可以查看已授给他们的权限,并且那些权限被授予在他们的哪些 对象上。 用数据库链接,你可以访问在远程数据库上的数据。权限不能被授予远程对象。 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 240 第 13 章 SQL*Plus OK,有人该问了,SQL*Plus 是什么东西?为什么会专门讲呢。很简单,它 是一个工具,一个很古老的工具,自从有 ORACLE 天起,它就诞生了。 它是一个类似 DOS、Linux 的一个环境,这个环境,有一些自己的规则、自 己的命令,它的主要功能呢,就是用于使用 SQL 来操作(几乎所有的操作)数据库。 当然能用于和数据库交互的工具很多,PL/SQL Developer,TOAD、ORACLE SQL Developer……等等,但是,只有这个工具,是不受操作系统限制的,在任何操 作系统下面,不需要任何配置,只要安装了 ORACLE 数据库或者 ORACLE 数据 库客户端,就可以马上使用。 第十三章 SQL 与 SQL*Plus ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 241 13.1 SQL 语句与 SQL*Plus 13.1.1 关系对比 SQL: 一种语言 ANSI 标准 关键字不能缩写 语句操纵数据,表定义在 数据库中 SQL*Plus: 一个环境 Oracle 所有 关键字可以缩写 其命令不能操纵数据库中的值 第 一 节 SQL 语句与 SQL*Plus  SQL 是一种由 ANSI 标准制定的标准语言,用来 操作数据库中的各种数据对象。  SQL*Plus 是 Oracle 公司开发的客户端软件之一, 它是 DBA 最常用的工具。 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 242 13.1.2 关系图 使用 PC 或者服务器的交互终端我们输入 SQL,SQL 存放在 SQL*Plus 的 Buffer 中, 通过 SQL*Plus 与数据库服务器进行交互,从操作数据库的数据或者查询数据,查询及操作 的结果会通过 SQL*Plus 返回。 SQL 和 SQL*Plus 交互 SSQQLL**PPlluuss BBuuffffeerr Server SQL 语句 查询结果 SQL 脚本 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 243 13.2 SQL*Plus 概览 13.2.1 登录 SQL*Plus 有两种方式可以登陆 SQL*Plus 第一种是使用图形界面登陆 SQL*Plus。Windows 下点击程序/ORACLE_HOME/应用程序开 发/SQL Plus: 第二种是使用命令行登陆,Windows 下 CMD,Linux 下的终端: sqlplus [username[/password [@Host String]]] 第 二 节 SQL*Plus 概览  登陆  显示表结构  编辑命令  LIST、APPEND  文件命令 ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 244 例如:sqlplus test/test@zero 此处 Host String 为主机字符串,也可以理解为 TNSNAME。TNSNAME 是 ORACLE 为了方 便客户端登陆数据库服务器而设置的一种网络名称。 13.2.2 显示表结构 用 SQL*Plus DESCRIBE 命令显示表结构 DESCRIBE [user].tablename DESC [user].tablename SQL> desc test.ut 名称 是否为空? 类型 ---------------------- -------- --------------------- USERNAME NOT NULL VARCHAR2(30) USER_ID NOT NULL NUMBER CREATED NOT NULL DATE 13.2.2 SQL*Plus 编辑命令 A[PPEND] text C[HANGE] / old / new C[HANGE] / text / CL[EAR] BUFF[ER] DEL DEL n DEL m n I[NPUT] I[NPUT] text L[IST] L[IST] n L[IST] m n R[UN] n n text 0 text ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 245 13.2.3 使用 LIST, n, 和 APPEND SQL> list 1* select * from test.ut SQL> select count(1) from test.ut; COUNT(1) ---------- 48 SQL> list 1* select count(1) from test.ut SQL> 1 1* select count(1) from test.ut SQL> select count(1) 2 from all_tables; COUNT(1) ---------- 1637 SQL> 1 1* select count(1) SQL> 2 2* from all_tables SQL> SQL> A,all_users 2* from all_tablescount,all_users SQL> L 1 select count(1) 2* from all_tablescount,all_users SQL> C /all_users/all_triggers 2* from all_tablescount,all_triggers SQL> L 1 select count(1) 2* from all_tablescount,all_triggers ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 246 13.2.4 SQL*Plus 文件命令 SAVE filename GET filename START filename @ filename EDIT filename SPOOL filename EXIT SQL> select t.owner,s.created,t.table_name,t.tablespace_name from all_tables t , all_users s 2 where t.owner = s.username and rownum <5; OWNER CREATED TABLE_NAME ---------------------- -------------- ------------------------------ TABLESPACE_NAME ------------------------------ SYS 19-11 月-06 ICOL$ SYSTEM SYS 19-11 月-06 CON$ SYSTEM SYS 19-11 月-06 UNDO$ SYSTEM OWNER CREATED TABLE_NAME ---------------------- -------------- ------------------------------ TABLESPACE_NAME ------------------------------ SYS 19-11 月-06 PROXY_ROLE_DATA$ SYSTEM SQL> SAVE my_query 已创建 file my_query.sql SQL> START my_query OWNER CREATED TABLE_NAME --------------------- -------------- ------------------------------ ORACLE 交流第一群:48949977 TG-522 Oracle SQL 基础 247 TABLESPACE_NAME ------------------------------ SYS 19-11 月-06 ICOL$ SYSTEM SYS 19-11 月-06 CON$ SYSTEM SYS 19-11 月-06 UNDO$ SYSTEM OWNER CREATED TABLE_NAME ----------------------- -------------- ------------------------------ TABLESPACE_NAME ------------------------------ SYS 19-11 月-06 PROXY_ROLE_DATA$ SYSTEM SQL> exit 从 Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production With the Partitioning, OLAP and Data Mining options 断开

下载文档,方便阅读与编辑

文档的实际排版效果,会与网站的显示效果略有不同!!

需要 8 金币 [ 分享文档获得金币 ] 1 人已下载

下载文档

相关文档