Oracle10g SQL基础-1

yangjueshe

贡献于2010-10-07

字数:0 关键词: Oracle 数据库服务器 SQL

Oracle Database 10g:SQL 基础 I 电子演示 D17108CN20 版本 2.0 2006 年 11 月 D47504 ® 版 权所有© 2006,Oracle。保留 所有 权 利 。 免 责声明 本文档包 含专有 权信息, 并受版 权法和其 它知识 产权法的 保护。 您可以复 制和打 印本文 档供在 Oracle 培 训课程中 单独使 用。不得 以任何 方式修改 或变更 本文档。 除了在 依照版 权法中制 定的“合理使用”范围 内使用本 文档外 ,在未经Oracle 明确授权 的情况 下,您 不得以全 部或部 分的形式 使用、 共享、下 载、上 载、复制 、打印 、显示、 展示、 再版、 发布、许 可、张 贴、传播 或散布 本文档。 本文档中 包含的 信息如有 更改, 恕不另行 通知。 如果您在 本文档 中发现任 何问题 ,请书 面通知:Oracle University, 500 Oracle Parkway, Redwood Shores, California 94065 USA.。不 能保证 本文档中 没有错 误。 有 限权利 声明 如果将本 文档交 付给美国 政府或 代表美国 政府使 用本文档 的任何 人,请适 用下列 通知中 的规定: 美国政府 权利 美国政府 使用、 修改、再 版、发 行、展示 、显示 或公开这 些培训 资料的权 利必须 遵守 Oracle 许可协 议 和(或) 美国政 府合同法 中适用 的条款。 商 标声明 Oracle、JD Edwards、PeopleSoft 和 Siebel 是 Oracle 公司和 (或 )其分公 司的注 册商 标。其它 名称可 能是其各 自拥有 者的商标 。 作者 Chaitanya Koratamaddi Nancy Greenberg 技 术撰稿 人和 审稿人 Wayne Abbott Christian Bauwens Claire Bennett Perry Benson Brian Boxx Zarko Cesljas Dairy Chan Laszlo Czinkoczki Joel Goodman Matthew Gregory Sushma Jagannath Angelika Krupp Isabelle Marchand Malika Marghadi Valli Pataballa Bryan Roberts Helen Robertson Lata Shivaprasad John Soltani Priya Vennapusa 编辑 Arijit Ghosh Raj Kumar 制图 员 Rajiv Chandrabhanu 出版 商 Veena Narasimhan 版权所有 © 2006,Oracle。保留所有权利。 简介 版权所有 © 2006,Oracle。保留所有权利。 课程目标 学完本课后,应能完成以下工作: • 列举 Oracle10g 的功能 • 从理论和实际情况两方面论述关系数据库 • 描述 Oracle 的 RDBMS 和 ORDBMS 实施 • 了解本课程的目标 版权所有 © 2006,Oracle。保留所有权利。 本课程的目标 学完本课程后,应能完成以下工作: • 确定 Oracle Database 10g 的主要结构组件 • 使用 SELECT 语句在表中检索行数据和列数据 • 创建一些报表,用于显示已排序的数据和受限制的数据 • 利用 SQL 函数生成和检索自定义的数据 • 运行数据操纵语言 (DML) 语句来更新 Oracle Database 10g 中的数据 • 通过查询字典视图获得元数据 版权所有 © 2006,Oracle。保留所有权利。 Oracle10g 单一开发模型 公用的多种技能 可靠性 统一管理 可伸缩性 一个供应商 版权所有 © 2006,Oracle。保留所有权利。 Oracle10g 版权所有 © 2006,Oracle。保留所有权利。 Oracle Database 10g 多媒体 对象关系数据 消息 文档 版权所有 © 2006,Oracle。保留所有权利。 Oracle Application Server 10g 应用程序开发框架 应用程序服务器 商业智能 门户 集成 事务处理应用程序 版权所有 © 2006,Oracle。保留所有权利。 Oracle Enterprise Manager 10g Grid Control • 软件预配 • 应用程序服务水平监视 版权所有 © 2006,Oracle。保留所有权利。 关系和对象关系数据库管理系统 • 关系模型和对象关系模型 • 用户定义的数据类型和对象 • 与关系数据库完全兼容 • 支持多媒体和大对象 • 高质量的数据库服务器功能 版权所有 © 2006,Oracle。保留所有权利。 Oracle Internet 平台 系统管理 网络服务 数据库 应用程序 服务器 Internet 应用程序 任何 浏览器 任何 FTP 客户机 任何邮件 客户机 SQL PL/SQL Java 客户机 演示和 业务逻辑 业务逻辑 和数据 开发工具 版权所有 © 2006,Oracle。保留所有权利。 系统开发周期 策略和分析 设计 构建和建档 转换 生产 版权所有 © 2006,Oracle。保留所有权利。 在不同介质中存储数据 电子表格 档案柜 数据库 版权所有 © 2006,Oracle。保留所有权利。 关系数据库概念 • E. F. Codd 博士在 1970 年提出了数据库系统的关系 模型。 • 它是关系数据库管理系统 (RDBMS) 的基础。 • 关系模型由以下各项组成: – 对象或关系的集合 – 处理关系的一组运算符 – 可保证精度和一致性的数据完整性 版权所有 © 2006,Oracle。保留所有权利。 关系数据库的定义 关系数据库是关系或二维表的集合。 Oracle 服务器 表名:EMPLOYEES 表名:DEPARTMENTS …… 版权所有 © 2006,Oracle。保留所有权利。 数据模型 客户心目中的 系统模型 客户模型的 实体模型 磁盘中的表 Oracle 服务器 实体模型的 表模型 版权所有 © 2006,Oracle。保留所有权利。 实体关系模型 • 根据业务规范或报告创建实体关系图: • 方案 – “. . . 将一名或多名雇员分配到某个部门 . . . ” – “. . . 尚未为某些部门分配雇员 . . . ” EMPLOYEE #* 编号 * 姓名 o 职务 DEPARTMENT #* 编号 * 名称 o 位置 分配至 包括 版权所有 © 2006,Oracle。保留所有权利。 实体关系建模惯例 属性 • 特有的名称 • 小写 • 必需属性以“*”标记 • 可选属性以“o”标记 唯一标识符 (UID) 主标识符以“#”标记 辅助标识符以“(#)”标记 EMPLOYEE #* 编号 * 姓名 o 职务 DEPARTMENT #* 编号 * 名称 o 位置 分配至 包括 实体 • 特有的唯一名称 • 大写 • 软边框 • 将同义词放在括号中 版权所有 © 2006,Oracle。保留所有权利。 关联多个表 • 表中的每行数据都由主键 (PK) 唯一地进行标识。 • 可以使用外键 (FK) 对多个表中的数据进行逻辑关联。 表名:EMPLOYEES 表名:DEPARTMENTS 主键 主键外键 … 版权所有 © 2006,Oracle。保留所有权利。 关系数据库术语 1 2 3 4 6 5 版权所有 © 2006,Oracle。保留所有权利。 关系数据库属性 关系数据库: • 可通过执行结构化查询语言 (SQL) 语句来进行访问和 修改 • 包含一系列没有物理指针的表 • 使用一组运算符 版权所有 © 2006,Oracle。保留所有权利。 使用 SQL 与 RDBMS 通信 输入 SQL 语句。 将语句发送到 Oracle 服务器。 Oracle 服务器 SELECT department_name FROM departments; 版权所有 © 2006,Oracle。保留所有权利。 Oracle 的关系数据库管理系统 用户表 数据字典 Oracle 服务器 版权所有 © 2006,Oracle。保留所有权利。 SQL 语句 数据操纵语言 (DML) 数据定义语言 (DDL) 事务处理控制 数据控制语言 (DCL) SELECT INSERT UPDATE DELETE MERGE CREATE ALTER DROP RENAME TRUNCATE COMMENT GRANT REVOKE COMMIT ROLLBACK SAVEPOINT 版权所有 © 2006,Oracle。保留所有权利。 本课程使用的表 EMPLOYEES DEPARTMENTS JOB_GRADES 版权所有 © 2006,Oracle。保留所有权利。 小结 • Oracle Database 10g 是用于网格计算的数据库。 • 该数据库基于对象关系数据库管理系统。 • 关系数据库由关系组成,并受数据完整性约束条件所 控制。可以通过关系操作对其进行管理。 • 利用 Oracle 服务器,可以通过使用 SQL 语言和 PL/SQL 引擎存储和管理信息。 版权所有 © 2006,Oracle。保留所有权利。 使用 SQL SELECT 语句检索数据 版权所有 © 2006,Oracle。保留所有权利。 课程目标 学完本课后,应能完成以下工作: • 列举 SQL SELECT 语句的功能 • 执行基本的 SELECT 语句 • 区分 SQL 语句和 iSQL*Plus 命令 版权所有 © 2006,Oracle。保留所有权利。 SQL SELECT 语句的功能 选择映射 表 1 表 2 表 1表 1 联接 版权所有 © 2006,Oracle。保留所有权利。 基本 SELECT 语句 • SELECT 标识要显示的列 • FROM 标识包含上述各列的表 SELECT *|{[DISTINCT] column|expression [alias],...} FROM table; 版权所有 © 2006,Oracle。保留所有权利。 选择所有列 SELECT * FROM departments; 版权所有 © 2006,Oracle。保留所有权利。 选择特定列 SELECT department_id, location_id FROM departments; 版权所有 © 2006,Oracle。保留所有权利。 编写 SQL 语句 • SQL 语句不区分大小写。 • SQL 语句可以包含一行或多行。 • 关键字不能是缩写词,也不能跨行分开写。 • 子句通常显示在单独的行中。 • 应使用缩进来提高可读性。 • 在 iSQL*Plus 中,可以选择以分号 (;) 来终止 SQL 语句。 如果执行多条 SQL 语句,则需要使用分号来分隔这些语句。 • 在 SQL*Plus 中,必须使用分号 (;) 来结束每条 SQL 语句。 版权所有 © 2006,Oracle。保留所有权利。 列标题的默认设置 • iSQL*Plus: – 默认的标题对齐方式:居中 – 默认的标题显示方式:大写 • SQL*Plus: – 字符和日期列标题是左对齐的 – 数字列标题是右对齐的 – 默认的标题显示方式:大写 版权所有 © 2006,Oracle。保留所有权利。 算术表达式 使用算术运算符为数字和日期数据创建表达式。 乘* 除/ 减- 加+ 说明运算符 版权所有 © 2006,Oracle。保留所有权利。 使用算术运算符 SELECT last_name, salary, salary + 300 FROM employees; … 版权所有 © 2006,Oracle。保留所有权利。 运算符优先级 … … SELECT last_name, salary, 12*salary+100 FROM employees; 1 SELECT last_name, salary, 12*(salary+100) FROM employees; 2 版权所有 © 2006,Oracle。保留所有权利。 定义空值 • 空值是不可用的、未分配的、未知的或不适用的值。 • 空值不同于零或空格。 SELECT last_name, job_id, salary, commission_pct FROM employees; … … 版权所有 © 2006,Oracle。保留所有权利。 算术表达式中的空值 包含空值的算术表达式的计算结果也为空。 SELECT last_name, 12*salary*commission_pct FROM employees; … … 版权所有 © 2006,Oracle。保留所有权利。 定义列别名 列别名具有以下特征和用途: • 可重命名列标题。 • 有助于计算。 • 紧跟在列名后面(列名和别名之间还可以有可选的关键 字 AS)。 • 如果别名包含空格或特殊字符,或者区分大小写,则需 要使用双引号。 版权所有 © 2006,Oracle。保留所有权利。 使用列别名 SELECT last_name AS name, commission_pct comm FROM employees; … … SELECT last_name "Name" , salary*12 "Annual Salary" FROM employees; 版权所有 © 2006,Oracle。保留所有权利。 连接运算符 连接运算符具有以下特征和用途: • 将列或字符串链接到其它列 • 由两条竖线 (||) 表示 • 创建一个由字符表达式生成的列 SELECT last_name||job_id AS "Employees" FROM employees; … 版权所有 © 2006,Oracle。保留所有权利。 文字字符串 • 文字是指 SELECT 语句中包含的字符、数字或日期。 • 日期和字符文字值必须包含在单引号内。 • 每个字符串都会针对每个返回的行输出一次。 版权所有 © 2006,Oracle。保留所有权利。 使用文字字符串 … SELECT last_name ||' is a '||job_id AS "Employee Details" FROM employees; 版权所有 © 2006,Oracle。保留所有权利。 其它引号 (q) 运算符 • 可指定您自己的引号分隔符 • 可选择任意分隔符 • 提高了可读性和可用性 … SELECT department_name || q'[, it's assigned Manager Id: ]' || manager_id AS "Department and Manager" FROM departments; 版权所有 © 2006,Oracle。保留所有权利。 重复的行 默认情况下会显示查询的所有行,包括重复的行。 SELECT department_id FROM employees; … SELECT DISTINCT department_id FROM employees; … 1 2 版权所有 © 2006,Oracle。保留所有权利。 SQL 和 iSQL*Plus 的交互 SQL 语句 查询结果iSQL*Plus 命令 客户机 格式化的报表 Internet 浏览器 Oracle 服务器 版权所有 © 2006,Oracle。保留所有权利。 SQL 语句与 iSQL*Plus 命令 SQL • 是一种语言 • 符合 ANSI 标准 • 关键字不能是缩写词 • 其语句可以操纵数据库中的 数据和表定义 SQL 语句 iSQL*Plus • 是一种环境 • 是 Oracle 专用的 • 关键字可以是缩写词 • 不能通过命令来操纵数据库 中的值 • 在浏览器上运行 • 集中加载,而不必在每台机 器上分别实现 iSQL*Plus 命令 版权所有 © 2006,Oracle。保留所有权利。 iSQL*Plus 概览 登录到 iSQL*Plus 之后,可以完成以下任务: • 描述表结构 • 输入、执行和编辑 SQL 语句 • 保存 SQL 语句,或将其附加到文件 • 执行或编辑存储在保存的脚本文件中的语句 版权所有 © 2006,Oracle。保留所有权利。 登录到 iSQL*Plus 从浏览器环境中进行登录: 版权所有 © 2006,Oracle。保留所有权利。 iSQL*Plus 环境 6 3 4 5 1 2 8 9 7 版权所有 © 2006,Oracle。保留所有权利。 显示表结构 使用 iSQL*Plus DESCRIBE 命令可以显示表的结构: DESC[RIBE] tablename 版权所有 © 2006,Oracle。保留所有权利。 显示表结构 DESCRIBE employees 版权所有 © 2006,Oracle。保留所有权利。 与脚本文件进行交互 SELECT last_name, hire_date, salary FROM employees; 1 2 版权所有 © 2006,Oracle。保留所有权利。 与脚本文件进行交互 版权所有 © 2006,Oracle。保留所有权利。 与脚本文件进行交互 1 版权所有 © 2006,Oracle。保留所有权利。 与脚本文件进行交互 2 3 D:\TEMP\emp_data.sql 版权所有 © 2006,Oracle。保留所有权利。 iSQL*Plus 历史记录页 1 2 3 版权所有 © 2006,Oracle。保留所有权利。 iSQL*Plus 历史记录页 3 4 版权所有 © 2006,Oracle。保留所有权利。 设置 iSQL*Plus 首选项 2 3 1 版权所有 © 2006,Oracle。保留所有权利。 设置输出位置首选项 1 2 版权所有 © 2006,Oracle。保留所有权利。 小结 在本课中,应该已经学会如何: • 编写执行以下任务的 SELECT 语句: – 从表中返回所有行和列 – 从表中返回指定列 – 使用列别名显示更具描述性的列标题 • 使用 iSQL*Plus 环境编写、保存和执行 SQL 语句以及 iSQL*Plus 命令 SELECT *|{[DISTINCT] column|expression [alias],...} FROM table; 版权所有 © 2006,Oracle。保留所有权利。 练习 1:概览 本练习包含以下主题: • 从不同的表中选择所有数据 • 描述表的结构 • 执行算术计算并指定列名 • 使用 iSQL*Plus 版权所有 © 2006,Oracle。保留所有权利。 对数据进行限制和排序 版权所有 © 2006,Oracle。保留所有权利。 课程目标 学完本课后,应能完成以下工作: • 对查询检索的行进行限制 • 对查询检索的行进行排序 • 在 iSQL*Plus 中使用“与”字符 (&) 替换,以在运行时 对输出进行限制和排序 版权所有 © 2006,Oracle。保留所有权利。 有选择性地对行进行限制 “检索部门 90 中的所有雇员” EMPLOYEES … 版权所有 © 2006,Oracle。保留所有权利。 对所选行进行限制 • 使用 WHERE 子句可以限制返回的行: • WHERE 子句在 FROM 子句之后。 SELECT *|{[DISTINCT] column|expression [alias],...} FROM table [WHERE condition(s)]; 版权所有 © 2006,Oracle。保留所有权利。 使用 WHERE 子句 SELECT employee_id, last_name, job_id, department_id FROM employees WHERE department_id = 90 ; 版权所有 © 2006,Oracle。保留所有权利。 字符串和日期 • 字符串和日期值应被放置在单引号内。 • 字符值区分大小写,日期值区分格式。 • 默认的日期格式为 DD-MON-RR。 SELECT last_name, job_id, department_id FROM employees WHERE last_name = 'Whalen' ; 版权所有 © 2006,Oracle。保留所有权利。 比较条件 不等于<> 两值之间(包含这两个值)BETWEEN ...AND... 与任意值列表相匹配IN(set) 与字符模式相匹配LIKE 为空值IS NULL 小于< 小于或等于<= 大于或等于>= 大于> 等于= 含义运算符 版权所有 © 2006,Oracle。保留所有权利。 使用比较条件 SELECT last_name, salary FROM employees WHERE salary <= 3000 ; 版权所有 © 2006,Oracle。保留所有权利。 使用 BETWEEN 条件 使用 BETWEEN 条件可基于值范围显示行: SELECT last_name, salary FROM employees WHERE salary BETWEEN 2500 AND 3500 ; 下限 上限 版权所有 © 2006,Oracle。保留所有权利。 使用 IN 条件 使用 IN 成员条件可测试列表中的值: SELECT employee_id, last_name, salary, manager_id FROM employees WHERE manager_id IN (100, 101, 201) ; 版权所有 © 2006,Oracle。保留所有权利。 使用 LIKE 条件 • 使用 LIKE 条件可对有效的搜索字符串值执行通配符 搜索。 • 搜索条件可以包含文字字符,也可以包含数字: – % 表示零个或多个字符。 – _ 表示一个字符。 SELECT first_name FROM employees WHERE first_name LIKE 'S%' ; 版权所有 © 2006,Oracle。保留所有权利。 使用 LIKE 条件 • 可将模式匹配字符组合在一起使用: • 可使用 ESCAPE 标识符来搜索实际的 % 和 _ 符号。 SELECT last_name FROM employees WHERE last_name LIKE '_o%' ; 版权所有 © 2006,Oracle。保留所有权利。 使用 NULL 条件 可使用 IS NULL 运算符测试空值。 SELECT last_name, manager_id FROM employees WHERE manager_id IS NULL ; 版权所有 © 2006,Oracle。保留所有权利。 逻辑条件 如果相应条件为假,则返回 TRUENOT 如果其中一个条件为真,则返回 TRUEOR 如果两个条件都为真,则返回 TRUEAND 含义运算符 版权所有 © 2006,Oracle。保留所有权利。 使用 AND 运算符 AND 要求两个条件都为真: SELECT employee_id, last_name, job_id, salary FROM employees WHERE salary >=10000 AND job_id LIKE '%MAN%' ; 版权所有 © 2006,Oracle。保留所有权利。 使用 OR 运算符 OR 要求其中一个条件为真: SELECT employee_id, last_name, job_id, salary FROM employees WHERE salary >= 10000 OR job_id LIKE '%MAN%' ; 版权所有 © 2006,Oracle。保留所有权利。 使用 NOT 运算符 SELECT last_name, job_id FROM employees WHERE job_id NOT IN ('IT_PROG', 'ST_CLERK', 'SA_REP') ; 版权所有 © 2006,Oracle。保留所有权利。 优先级规则 可以使用括号更改优先级规则。 不等于6 NOT 逻辑条件7 AND 逻辑条件8 OR 逻辑条件9 IS [NOT] NULL、LIKE、[NOT] IN4 [NOT] BETWEEN5 比较条件3 连接运算符2 算术运算符1 含义运算符 版权所有 © 2006,Oracle。保留所有权利。 优先级规则 SELECT last_name, job_id, salary FROM employees WHERE job_id = 'SA_REP' OR job_id = 'AD_PRES' AND salary > 15000; 1 SELECT last_name, job_id, salary FROM employees WHERE (job_id = 'SA_REP' OR job_id = 'AD_PRES') AND salary > 15000; 2 版权所有 © 2006,Oracle。保留所有权利。 使用 ORDER BY 子句 • 使用 ORDER BY 子句对检索的行进行排序: – ASC:升序,默认设置 – DESC:降序 • ORDER BY 子句位于 SELECT 语句的最后: SELECT last_name, job_id, department_id, hire_date FROM employees ORDER BY hire_date ; … 版权所有 © 2006,Oracle。保留所有权利。 排序 • 按降序排序: • 按列别名排序: • 按多个列排序: SELECT last_name, job_id, department_id, hire_date FROM employees ORDER BY hire_date DESC ; 1 SELECT employee_id, last_name, salary*12 annsal FROM employees ORDER BY annsal ; 2 SELECT last_name, department_id, salary FROM employees ORDER BY department_id, salary DESC; 3 版权所有 © 2006,Oracle。保留所有权利。 替代变量 ... salary = ?… … department_id = ? … ... last_name = ? ... 我需要查询 不同的值 版权所有 © 2006,Oracle。保留所有权利。 替代变量 • 使用 iSQL*Plus 替代变量可以执行以下操作: – 使用单“与”号 (&) 及双“与”号 (&&) 替换临时存储值 • 使用替代变量可以补充以下内容: – WHERE 条件 – ORDER BY 子句 – 列表达式 – 表名 – 整个 SELECT 语句 版权所有 © 2006,Oracle。保留所有权利。 使用 & 替代变量 使用以“与”号 (&) 为前缀的变量可以提示用户输入一个值: SELECT employee_id, last_name, salary, department_id FROM employees WHERE employee_id = &employee_num ; 版权所有 © 2006,Oracle。保留所有权利。 使用 & 替代变量 101 1 2 版权所有 © 2006,Oracle。保留所有权利。 使用替代变量指定字符值和日期值 对日期值和字符值使用单引号: SELECT last_name, department_id, salary*12 FROM employees WHERE job_id = '&job_title' ; 版权所有 © 2006,Oracle。保留所有权利。 指定列名、表达式和文本 SELECT employee_id, last_name, job_id,&column_name FROM employees WHERE &condition ORDER BY &order_column ; salary salary > 15000 last_name 版权所有 © 2006,Oracle。保留所有权利。 SELECT employee_id, last_name, job_id, &&column_name FROM employees ORDER BY &column_name ; 使用 && 替代变量 如果要重复使用变量值,但不是每次都得到提示,请使用双 “与”号 (&&): … 版权所有 © 2006,Oracle。保留所有权利。 使用 iSQL*Plus DEFINE 命令 • 使用 iSQL*Plus DEFINE 命令可以创建变量并为其分配 一个值。 • 使用 iSQL*Plus UNDEFINE 命令可以删除变量。 DEFINE employee_num = 200 SELECT employee_id, last_name, salary, department_id FROM employees WHERE employee_id = &employee_num ; UNDEFINE employee_num 版权所有 © 2006,Oracle。保留所有权利。 使用 VERIFY 命令 在 iSQL*Plus 使用值替换替代变量前后,可使用 VERIFY 命令触发替代变量的显示: SET VERIFY ON SELECT employee_id, last_name, salary, department_id FROM employees WHERE employee_id = &employee_num; old 3:WHERE employee_id = &employee_num new 3:WHERE employee_id = 200 版权所有 © 2006,Oracle。保留所有权利。 小结 在本课中,您应该已经学会: • 使用 WHERE 子句来限制输出的行: – 使用比较条件 – 使用 BETWEEN、IN、LIKE 和 NULL 条件 – 应用 AND、OR 和 NOT 逻辑运算符 • 使用 ORDER BY 子句对输出的行进行排序: • 在 iSQL*Plus 中使用 & 替换,以在运行时对输出进行 限制和排序 SELECT *|{[DISTINCT] column|expression [alias],...} FROM table [WHERE condition(s)] [ORDER BY {column, expr, alias} [ASC|DESC]] ; 版权所有 © 2006,Oracle。保留所有权利。 练习 2:概览 本练习包含以下主题: • 选择数据并更改显示的行的顺序 • 使用 WHERE 子句对行进行限制 • 使用 ORDER BY 子句对行进行排序 • 使用替代变量增加 SQL SELECT 语句的灵活性 版权所有 © 2006,Oracle。保留所有权利。 使用单行函数自定义报表 版权所有 © 2006,Oracle。保留所有权利。 课程目标 学完本课后,应能完成以下工作: • 描述可在 SQL 中使用的各种类型的函数 • 在 SELECT 语句中使用字符、数字和日期函数 • 描述转换函数的用途 版权所有 © 2006,Oracle。保留所有权利。 SQL 函数 函数 输入 参数 1 参数 2 参数 n 函数执行操作 输出 结果值 版权所有 © 2006,Oracle。保留所有权利。 SQL 函数的两种类型 单行函数 多行函数 每行返回一个结果 每个行集返回一个结果 函数 版权所有 © 2006,Oracle。保留所有权利。 单行函数 单行函数: • 处理数据项 • 接受参数并返回一个值 • 对每个返回的行进行处理 • 为每行返回一个结果 • 可能会修改数据类型 • 可以嵌套 • 接受参数,这些参数可以是列或表达式 function_name [(arg1, arg2,...)] 版权所有 © 2006,Oracle。保留所有权利。 单行函数 转换 字符 数字 日期 常规 单行函数 版权所有 © 2006,Oracle。保留所有权利。 字符函数 LOWER UPPER INITCAP CONCAT SUBSTR LENGTH INSTR LPAD | RPAD TRIM REPLACE 字符函数 大小写处理函数 字符处理函数 版权所有 © 2006,Oracle。保留所有权利。 大小写处理函数 以下函数用于转换字符串的大小写: sql course LOWER('SQL Course') Sql Course INITCAP('SQL Course') SQL COURSE UPPER('SQL Course') 结果函数 版权所有 © 2006,Oracle。保留所有权利。 使用大小写处理函数 可使用以下语句显示雇员 Higgins 的雇员编号、姓名和部门 编号: SELECT employee_id, last_name, department_id FROM employees WHERE LOWER(last_name) = 'higgins'; SELECT employee_id, last_name, department_id FROM employees WHERE last_name = 'higgins'; no rows selected 版权所有 © 2006,Oracle。保留所有权利。 字符处理函数 下面的函数用于以如下方式处理字符串: BLACK and BLUE REPLACE ('JACK and JUE','J','BL') 10 LENGTH('HelloWorld') 6INSTR('HelloWorld', 'W') *****24000 LPAD(salary,10,'*') 24000***** RPAD(salary, 10, '*') HelloWorld CONCAT('Hello', 'World') elloWorld TRIM('H' FROM 'HelloWorld') Hello SUBSTR('HelloWorld',1,5) 结果函数 版权所有 © 2006,Oracle。保留所有权利。 使用字符处理函数 SELECT employee_id, CONCAT(first_name, last_name) NAME, job_id, LENGTH (last_name), INSTR(last_name, 'a') "Contains 'a'?" FROM employees WHERE SUBSTR(job_id, 4) = 'REP'; 2 31 2 1 3 版权所有 © 2006,Oracle。保留所有权利。 数字函数 • ROUND:将值舍入到指定的小数位 • TRUNC:将值截断到指定的小数位 • MOD:返回除法运算的余数 100MOD(1600, 300) 45.93ROUND(45.926, 2) 45.92TRUNC(45.926, 2) 结果函数 版权所有 © 2006,Oracle。保留所有权利。 使用 ROUND 函数 SELECT ROUND(45.923,2), ROUND(45.923,0), ROUND(45.923,-1) FROM DUAL; DUAL 是用于查看函数和计算结果的空表。 3 31 2 1 2 版权所有 © 2006,Oracle。保留所有权利。 使用 TRUNC 函数 SELECT TRUNC(45.923,2), TRUNC(45.923), TRUNC(45.923,-1) FROM DUAL; 3 31 2 1 2 版权所有 © 2006,Oracle。保留所有权利。 使用 MOD 函数 为职务为销售代表的所有雇员,计算薪金除以 5,000 后的 余数。 SELECT last_name, salary, MOD(salary, 5000) FROM employees WHERE job_id = 'SA_REP'; 版权所有 © 2006,Oracle。保留所有权利。 处理日期 • Oracle 数据库以内部数字格式存储日期:世纪、年、月、 日、小时、分钟和秒。 • 默认的日期显示格式为 DD-MON-RR。 – 通过仅指定年份的后两位,可以在 20 世纪存储 21 世纪 的日期。 – 同样,也可以在 21 世纪存储 20 世纪的日期。 SELECT last_name, hire_date FROM employees WHERE hire_date < ''01-FEB-88';'; 版权所有 © 2006,Oracle。保留所有权利。 处理日期 SYSDATE 是返回以下对象的函数: • 日期 • 时间 版权所有 © 2006,Oracle。保留所有权利。 与日期有关的运算 • 对日期加上或减去一个数字,就可以获得一个新的日 期值。 • 将两个日期相减,就可以得出它们之间的天数。 • 通过将小时数除以 24,可以将小时添加到日期中。 版权所有 © 2006,Oracle。保留所有权利。 使用算术运算符处理日期 SELECT last_name, (SYSDATE-hire_date)/7 AS WEEKS FROM employees WHERE department_id = 90; 版权所有 © 2006,Oracle。保留所有权利。 日期函数 日期后的下一个特定日NEXT_DAY 当月最后一天LAST_DAY 舍入日期ROUND 截断日期TRUNC 两个日期之间的月数MONTHS_BETWEEN 将日历月添加到日期ADD_MONTHS 结果函数 版权所有 © 2006,Oracle。保留所有权利。 使用日期函数 '08-SEP-95'NEXT_DAY ('01-SEP-95','FRIDAY') '28-FEB-95'LAST_DAY ('01-FEB-95') 19.6774194MONTHS_BETWEEN ('01-SEP-95','11-JAN-94') '11-JUL-94'ADD_MONTHS ('11-JAN-94',6) 结果函数 版权所有 © 2006,Oracle。保留所有权利。 使用日期函数 假设 SYSDATE = '25-JUL-03': 01-JUL-03TRUNC(SYSDATE ,'MONTH') 01-JAN-03TRUNC(SYSDATE ,'YEAR') 01-AUG-03ROUND(SYSDATE,'MONTH') 01-JAN-04ROUND(SYSDATE ,'YEAR') 结果函数 版权所有 © 2006,Oracle。保留所有权利。 练习 3,第 1 部分:概览 本练习包含以下主题: • 编写一个显示当前日期的查询 • 创建要求使用数字、字符和日期函数的查询 • 计算某位雇员服务的年数和月数 版权所有 © 2006,Oracle。保留所有权利。 转换函数 隐式数据类型转换 显式数据类型转换 数据类型转换 版权所有 © 2006,Oracle。保留所有权利。 隐式数据类型转换 对于赋值操作,Oracle 服务器可以自动执行以下转换: VARCHAR2 NUMBER VARCHAR2 DATE NUMBER VARCHAR2 或 CHAR DATE VARCHAR2 或 CHAR 至从 版权所有 © 2006,Oracle。保留所有权利。 隐式数据类型转换 对于表达式计算,Oracle 服务器可以自动执行以下转换: NUMBER VARCHAR2 或 CHAR DATE VARCHAR2 或 CHAR 至从 版权所有 © 2006,Oracle。保留所有权利。 显式数据类型转换 NUMBER CHARACTER TO_CHAR TO_NUMBER DATE TO_CHAR TO_DATE 版权所有 © 2006,Oracle。保留所有权利。 显式数据类型转换 NUMBER CHARACTER TO_CHAR TO_NUMBER DATE TO_CHAR TO_DATE 版权所有 © 2006,Oracle。保留所有权利。 使用 TO_CHAR 函数处理日期 格式样式具有以下特点: • 必须包含在单引号内 • 区分大小写 • 可以包含任何有效的日期格式元素 • 具有一个 fm 元素,用于删除填充的空格或隐藏前导零 • 与日期值之间用逗号分隔 TO_CHAR(date, 'format_model') 版权所有 © 2006,Oracle。保留所有权利。 日期格式样式的元素 一周中某日的三个字母缩写DY 一周中某日的完整名称DAY 月份的两位数值MM 月份的完整名称MONTH 月份的三个字母缩写MON 用数字表示的月份中某日DD 用数字表示的完整年份YYYY 拼写出的年份(用英文表示)YEAR 结果元素 版权所有 © 2006,Oracle。保留所有权利。 日期格式样式的元素 • 时间元素可设置日期的时间部分的格式: • 通过将字符串放置在双引号内可以添加字符串: • 为拼写的数字指定后缀: DD "of" MONTH 12 of OCTOBER ddspth fourteenth HH24:MI:SS AM 15:45:32 PM 版权所有 © 2006,Oracle。保留所有权利。 使用 TO_CHAR 函数处理日期 SELECT last_name, TO_CHAR(hire_date, 'fmDD Month YYYY') AS HIREDATE FROM employees; … 版权所有 © 2006,Oracle。保留所有权利。 使用 TO_CHAR 函数处理数字 使用 TO_CHAR 函数将数字值显示为字符时,可以使用以下 格式元素: 显示小数点. 显示作为千位指示符的逗号, 放置一个浮动的美元符号$ 使用浮动的本地货币符号L 代表一个数字9 强制显示零0 结果元素 TO_CHAR(number, 'format_model') 版权所有 © 2006,Oracle。保留所有权利。 使用 TO_CHAR 函数处理数字 SELECT TO_CHAR(salary, '$99,999.00') SALARY FROM employees WHERE last_name = 'Ernst'; 版权所有 © 2006,Oracle。保留所有权利。 使用 TO_NUMBER 和 TO_DATE 函数 • 使用 TO_NUMBER 函数可将字符串转换为数字格式: • 使用 TO_DATE 函数可将字符串转换为日期格式: • 这些函数都有一个 fx 限定符。此限定符指定与 TO_DATE 函数的字符参数和日期格式样式完全匹配的项。 TO_NUMBER(char[, 'format_model']) TO_DATE(char[, 'format_model']) 版权所有 © 2006,Oracle。保留所有权利。 RR 日期格式 当前年份 1995 1995 2001 2001 指定的日期 27-OCT-95 27-OCT-17 27-OCT-17 27-OCT-95 RR 格式 1995 2017 2017 1995 YY 格式 1995 1917 2017 2095 如果当前年份 的两位数为: 0–49 0–49 50–99 50–99 返回的日期在当前世 纪中 返回的日期在当前世 纪的下一个世纪中 返回的日期在当前世 纪的前一个世纪中 返回的日期在当前世 纪中 如果指定的两位数年份为: 版权所有 © 2006,Oracle。保留所有权利。 RR 日期格式示例 要查找在 1990 年之前聘用的雇员,请使用 RR 日期格式, 无论命令是在 1999 年运行还是在现在立即运行,此格式都 会产生相同的结果: SELECT last_name, TO_CHAR(hire_date, 'DD-Mon-YYYY') FROM employees WHERE hire_date < TO_DATE('01-Jan-90','DD-Mon-RR'); 版权所有 © 2006,Oracle。保留所有权利。 嵌套函数 • 单行函数可以被嵌套到任意层。 • 嵌套函数的计算顺序是从最深层开始,直到最外层。 F3(F2(F1(col,arg1),arg2),arg3) 步骤 1 = 结果 1 步骤 2 = 结果 2 步骤 3 = 结果 3 版权所有 © 2006,Oracle。保留所有权利。 嵌套函数 SELECT last_name, UPPER(CONCAT(SUBSTR (LAST_NAME, 1, 8), '_US')) FROM employees WHERE department_id = 60; 版权所有 © 2006,Oracle。保留所有权利。 常规函数 下列函数可以使用任何数据类型,并适合使用空值: • NVL (expr1, expr2) • NVL2 (expr1, expr2, expr3) • NULLIF (expr1, expr2) • COALESCE (expr1, expr2, ..., exprn) 版权所有 © 2006,Oracle。保留所有权利。 NVL 函数 将空值转换为实际的值: • 可以使用的数据类型为日期、字符和数字。 • 数据类型必须匹配: – NVL(commission_pct,0) – NVL(hire_date,'01-JAN-97') – NVL(job_id,'No Job Yet') 版权所有 © 2006,Oracle。保留所有权利。 使用 NVL 函数 … 1 2 SELECT last_name, salary, NVL(commission_pct, 0), (salary*12) + (salary*12*NVL(commission_pct, 0)) AN_SAL FROM employees; 1 2 版权所有 © 2006,Oracle。保留所有权利。 使用 NVL2 函数 1 2 SELECT last_name, salary, commission_pct, NVL2(commission_pct, 'SAL+COMM', 'SAL') income FROM employees WHERE department_id IN (50, 80); 2 1 版权所有 © 2006,Oracle。保留所有权利。 使用 NULLIF 函数 … 1 2 3 SELECT first_name, LENGTH(first_name) "expr1", last_name, LENGTH(last_name) "expr2", NULLIF(LENGTH(first_name), LENGTH(last_name)) result FROM employees; 1 2 3 版权所有 © 2006,Oracle。保留所有权利。 使用 COALESCE 函数 • COALESCE 函数优于 NVL 函数之处在于 COALESCE 函 数可以使用多个备选值。 • 如果第一个表达式不为空,则 COALESCE 函数返回该表 达式,否则,将对余下的表达式执行 COALESCE 运算。 版权所有 © 2006,Oracle。保留所有权利。 使用 COALESCE 函数 … SELECT last_name, COALESCE(manager_id,commission_pct, -1) comm FROM employees ORDER BY commission_pct; 版权所有 © 2006,Oracle。保留所有权利。 条件表达式 • 使您可以在 SQL 语句中使用 IF-THEN-ELSE 逻辑 • 使用下面两种方法: – CASE 表达式 – DECODE 函数 版权所有 © 2006,Oracle。保留所有权利。 CASE 表达式 通过执行 IF-THEN-ELSE 语句的任务,可以简化条件查询: CASE expr WHEN comparison_expr1 THEN return_expr1 [WHEN comparison_expr2 THEN return_expr2 WHEN comparison_exprn THEN return_exprn ELSE else_expr] END 版权所有 © 2006,Oracle。保留所有权利。 使用 CASE 表达式 通过执行 IF-THEN-ELSE 语句的任务,可以简化条件查询: … … 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; 版权所有 © 2006,Oracle。保留所有权利。 DECODE 函数 通过执行 CASE 表达式或 IF-THEN-ELSE 语句的任务,可 以简化条件查询: DECODE(col|expression, search1, result1 [, search2, result2,...,] [, default]) 版权所有 © 2006,Oracle。保留所有权利。 使用 DECODE 函数 … … SELECT last_name, job_id, salary, DECODE(job_id, 'IT_PROG', 1.10*salary, 'ST_CLERK', 1.15*salary, 'SA_REP', 1.20*salary, salary) REVISED_SALARY FROM employees; 版权所有 © 2006,Oracle。保留所有权利。 使用 DECODE 函数 显示部门 80 中每位雇员的适用税率: 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; 版权所有 © 2006,Oracle。保留所有权利。 小结 在本课中,您应该已经学会: • 使用函数对数据执行计算 • 使用函数修改单个数据项 • 使用函数处理成组行的输出 • 使用函数更改显示的日期格式 • 使用函数转换列数据类型 • 使用 NVL 函数 • 使用 IF-THEN-ELSE 逻辑 版权所有 © 2006,Oracle。保留所有权利。 练习 3,第 2 部分:概览 本练习包含以下主题: • 创建要求使用数字、字符和日期函数的查询 • 通过函数使用连接 • 编写不区分大小写的查询,以测试字符函数的用途 • 计算一个雇员的服务年数和月数 • 确定雇员的复核日期 版权所有 © 2006,Oracle。保留所有权利。 使用分组函数报告汇总数据 版权所有 © 2006,Oracle。保留所有权利。 课程目标 学完本课后,应能完成以下工作: • 确定可用的分组函数 • 描述分组函数的使用方法 • 通过使用 GROUP BY 子句对数据进行分组 • 通过使用 HAVING 子句包括或排除分组的行 版权所有 © 2006,Oracle。保留所有权利。 什么是分组函数 分组函数对行集进行操作,以便为每组输出一个结果。 EMPLOYEES EMPLOYEES 表中 的最高薪金 … 版权所有 © 2006,Oracle。保留所有权利。 分组函数的类型 • AVG • COUNT • MAX • MIN • STDDEV • SUM • VARIANCE 分组函数 版权所有 © 2006,Oracle。保留所有权利。 分组函数:语法 SELECT [column,] group_function(column), ... FROM table [WHERE condition] [GROUP BY column] [ORDER BY column]; 版权所有 © 2006,Oracle。保留所有权利。 使用 AVG 和 SUM 函数 您可以对数值数据使用 AVG 和 SUM 函数。 SELECT AVG(salary), MAX(salary), MIN(salary), SUM(salary) FROM employees WHERE job_id LIKE '%REP%'; 版权所有 © 2006,Oracle。保留所有权利。 使用 MIN 和 MAX 函数 您可以对数值、字符和日期数据类型使用 MIN 和 MAX 函数。 SELECT MIN(hire_date), MAX(hire_date) FROM employees; 版权所有 © 2006,Oracle。保留所有权利。 使用 COUNT 函数 COUNT(*) 将返回表中的行数: COUNT(expr) 将返回 expr 为非空值的行的数量: SELECT COUNT(*) FROM employees WHERE department_id = 50; 1 SELECT COUNT(commission_pct) FROM employees WHERE department_id = 80; 2 版权所有 © 2006,Oracle。保留所有权利。 使用 DISTINCT 关键字 • COUNT(DISTINCT expr) 将返回 expr 的不同非空值 的数量。 • 要显示 EMPLOYEES 表中不同部门值的数量,请使用以 下语句: SELECT COUNT(DISTINCT department_id) FROM employees; 版权所有 © 2006,Oracle。保留所有权利。 分组函数和空值 分组函数将忽略列中的空值: • • NVL 函数会强制分组函数包括空值: SELECT AVG(commission_pct) FROM employees; 1 SELECT AVG(NVL(commission_pct, 0)) FROM employees; 2 版权所有 © 2006,Oracle。保留所有权利。 创建数据组 EMPLOYEES … 4400 9500 3500 6400 10033 EMPLOYEES 表 中每个部门的 平均薪金 版权所有 © 2006,Oracle。保留所有权利。 创建数据组:GROUP BY 子句语法 您可以通过使用 GROUP BY 子句将表中的行分成较小的组。 SELECT column, group_function(column) FROM table [WHERE condition] [GROUP BY group_by_expression] [ORDER BY column]; 版权所有 © 2006,Oracle。保留所有权利。 使用 GROUP BY 子句 SELECT 列表中不在分组函数中的所有列必须包含在 GROUP BY 子句中。 SELECT department_id, AVG(salary) FROM employees GROUP BY department_id ; 版权所有 © 2006,Oracle。保留所有权利。 使用 GROUP BY 子句 GROUP BY 列不一定在 SELECT 列表中。 SELECT AVG(salary) FROM employees GROUP BY department_id ; 版权所有 © 2006,Oracle。保留所有权利。 按多个列进行分组 EMPLOYEES 将 EMPLOYEES 表中按部门分组 的每个职务的 薪金相加 … 版权所有 © 2006,Oracle。保留所有权利。 对多个列使用 GROUP BY 子句 SELECT department_id dept_id, job_id, SUM(salary) FROM employees GROUP BY department_id, job_id ; 版权所有 © 2006,Oracle。保留所有权利。 使用分组函数的非法查询 SELECT 列表中不是聚合函数的所有列或表达式都必须出现 在 GROUP BY 子句中 : SELECT department_id, COUNT(last_name) FROM employees; SELECT department_id, COUNT(last_name) * ERROR at line 1: ORA-00937: not a single-group group function 缺少 GROUP BY 子句列 版权所有 © 2006,Oracle。保留所有权利。 使用分组函数的非法查询 • 不能使用 WHERE 子句限定组。 • 可以使用 HAVING 子句限定组。 • 不能在 WHERE 子句中使用分组函数。 SELECT department_id, AVG(salary) FROM employees WHERE AVG(salary) > 8000 GROUP BY department_id; WHERE AVG(salary) > 8000 * ERROR at line 3: ORA-00934: group function is not allowed here 不能使用 WHERE 子句限定组 版权所有 © 2006,Oracle。保留所有权利。 限定组结果 EMPLOYEES … 最高薪金大于 $10,000 的每 个 部门的最高薪金 版权所有 © 2006,Oracle。保留所有权利。 使用 HAVING 子句限定组结果 在使用 HAVING 子句时,Oracle 服务器将执行以下步骤来 对组进行限定: 1. 对行进行分组。 2. 应用分组函数。 3. 显示符合 HAVING 子句的组。 SELECT column, group_function FROM table [WHERE condition] [GROUP BY group_by_expression] [HAVING group_condition] [ORDER BY column]; 版权所有 © 2006,Oracle。保留所有权利。 使用 HAVING 子句 SELECT department_id, MAX(salary) FROM employees GROUP BY department_id HAVING MAX(salary)>10000 ; 版权所有 © 2006,Oracle。保留所有权利。 使用 HAVING 子句 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); 版权所有 © 2006,Oracle。保留所有权利。 嵌套分组函数 下列语句显示了最高平均薪金: SELECT MAX(AVG(salary)) FROM employees GROUP BY department_id; 版权所有 © 2006,Oracle。保留所有权利。 小结 在本课中,您应该已经学会: • 使用分组函数 COUNT、MAX、MIN 和 AVG • 编写包含 GROUP BY 子句的查询 • 编写包含 HAVING 子句的查询 SELECT column, group_function FROM table [WHERE condition] [GROUP BY group_by_expression] [HAVING group_condition] [ORDER BY column]; 版权所有 © 2006,Oracle。保留所有权利。 练习 4:概览 本练习包含以下主题: • 编写包含分组函数的查询 • 按行进行分组以获得多个结果 • 使用 HAVING 子句对组进行限定 版权所有 © 2006,Oracle。保留所有权利。 显示多个表中的数据 版权所有 © 2006,Oracle。保留所有权利。 课程目标 学完本课后,应能完成以下工作: • 编写 SELECT 语句,以使用等值联接和非等值联接访问 多个表中的数据 • 使用自联接将表联接到自身 • 使用外部联接查看通常不满足联接条件的数据 • 生成两个或多个表中的所有行的笛卡尔积 版权所有 © 2006,Oracle。保留所有权利。 获取多个表中的数据 EMPLOYEES DEPARTMENTS … … 版权所有 © 2006,Oracle。保留所有权利。 联接类型 符合 SQL:1999 标准的联接包括: • 交叉联接 • 自然联接 • USING 子句 • 完全(或双边)外部联接 • 外部联接的任意联接条件 版权所有 © 2006,Oracle。保留所有权利。 使用 SQL:1999 语法将表联接起来 使用联接可查询多个表中的数据: SELECT table1.column, table2.column FROM table1 [NATURAL JOIN table2] | [JOIN table2 USING (column_name)] | [JOIN table2 ON (table1.column_name = table2.column_name)]| [LEFT|RIGHT|FULL OUTER JOIN table2 ON (table1.column_name = table2.column_name)]| [CROSS JOIN table2]; 版权所有 © 2006,Oracle。保留所有权利。 创建自然联接 • NATURAL JOIN 子句基于两个表中具有相同名称的所有列。 • 它从两个表中选择在所有相匹配的列中具有相同值的行。 • 如果名称相同的列具有不同的数据类型,则返回一个错误。 版权所有 © 2006,Oracle。保留所有权利。 使用自然联接检索记录 SELECT department_id, department_name, location_id, city FROM departments NATURAL JOIN locations ; 版权所有 © 2006,Oracle。保留所有权利。 使用 USING 子句创建联接 • 如果多个列具有相同的名称但数据类型不同,则可以使 用 USING 子句修改 NATURAL JOIN 子句,以指定将用 于等值联接的列。 • 当有多个列相匹配时,可使用 USING 子句来仅与一列相 匹配。 • 不要在被引用的列中使用表名或别名。 • NATURAL JOIN 和 USING 语句是互相排斥的。 版权所有 © 2006,Oracle。保留所有权利。 联接列名 EMPLOYEES DEPARTMENTS 外键 主键 … … 版权所有 © 2006,Oracle。保留所有权利。 使用 USING 子句检索记录 SELECT employees.employee_id, employees.last_name, departments.location_id, department_id FROM employees JOIN departments USING (department_id) ; … 版权所有 © 2006,Oracle。保留所有权利。 限定不确定的列名 • 使用表前缀可以限定多个表中的列名。 • 使用表前缀可以提高性能。 • 使用列别名可以区分具有相同名称但位于不同表中的列。 • 不要对 USING 子句中标识的列以及在 SQL 语句中其它 位置处列出的列使用别名。 版权所有 © 2006,Oracle。保留所有权利。 SELECT e.employee_id, e.last_name, d.location_id, department_id FROM employees e JOIN departments d USING (department_id) ; 使用表别名 • 使用表别名可以简化查询。 • 使用表别名可以提高性能。 版权所有 © 2006,Oracle。保留所有权利。 使用 ON 子句创建联接 • 自然联接的联接条件基本上是具有相同名称的所有列的 等值联接。 • 使用 ON 子句指定任意条件或指定要联接的列。 • 联接条件独立于其它搜索条件。 • 使用 ON 子句可使代码易于理解。 版权所有 © 2006,Oracle。保留所有权利。 使用 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); … 版权所有 © 2006,Oracle。保留所有权利。 使用 ON 子句进行自联接 WORKER 表中的 MANAGER_ID 等于 MANAGER 表中的 EMPLOYEE_ID EMPLOYEES (WORKER) EMPLOYEES (MANAGER) … … 版权所有 © 2006,Oracle。保留所有权利。 使用 ON 子句进行自联接 SELECT e.last_name emp, m.last_name mgr FROM employees e JOIN employees m ON (e.manager_id = m.employee_id); … 版权所有 © 2006,Oracle。保留所有权利。 对联接应用附加条件 SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_id FROM employees e JOIN departments d ON (e.department_id = d.department_id) AND e.manager_id = 149 ; 版权所有 © 2006,Oracle。保留所有权利。 使用 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; … 版权所有 © 2006,Oracle。保留所有权利。 非等值联接 EMPLOYEES JOB_GRADES EMPLOYEES 表中的薪金必须 介于 JOB_GRADES 表中的最 低薪金和最高薪金之间… 版权所有 © 2006,Oracle。保留所有权利。 使用非等值联接检索记录 SELECT e.last_name, e.salary, j.grade_level FROM employees e JOIN job_grades j ON e.salary BETWEEN j.lowest_sal AND j.highest_sal; … 版权所有 © 2006,Oracle。保留所有权利。 外部联接 EMPLOYEESDEPARTMENTS 部门 190 中没有雇员 … 版权所有 © 2006,Oracle。保留所有权利。 内部联接与外部联接 • 在 SQL:1999 中,如果两个表的联接只返回相匹配的行, 则称该联接为内部联接。 • 如果两个表之间的连接不仅返回内部联接的结果,还返 回左(或右)表中不匹配的行,则称该联接为左(或右) 外部联接。 • 如果两个表之间的连接不仅返回内部联接的结果,还返 回左和右联接的结果,则称该联接为完全外部联接。 版权所有 © 2006,Oracle。保留所有权利。 LEFT OUTER JOIN 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) ; … 版权所有 © 2006,Oracle。保留所有权利。 RIGHT OUTER JOIN 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) ; … 版权所有 © 2006,Oracle。保留所有权利。 FULL OUTER JOIN SELECT e.last_name, d.department_id, d.department_name FROM employees e FULL OUTER JOIN departments d ON (e.department_id = d.department_id) ; … 版权所有 © 2006,Oracle。保留所有权利。 笛卡尔积 • 出现以下情况时,将形成笛卡尔积: – 联接条件被省略 – 联接条件无效 – 第一个表中的所有行被联接到第二个表中的所有行上 • 为了避免笛卡尔积,请始终包括有效的联接条件 版权所有 © 2006,Oracle。保留所有权利。 生成笛卡尔积 笛卡尔积: 20 x 8 = 160 行 EMPLOYEES(20 行) DEPARTMENTS(8 行) … … 版权所有 © 2006,Oracle。保留所有权利。 创建交叉联接 • CROSS JOIN 子 句 将生成两个表的交叉结果。 • 这也称为两个表间的笛卡尔积。 SELECT last_name, department_name FROM employees CROSS JOIN departments ; … 版权所有 © 2006,Oracle。保留所有权利。 小结 在本课中,您应该已经学会如何通过以下方式使用联接来显 示多个表中的数据: • 等值联接 • 非等值联接 • 外部联接 • 自联接 • 交叉联接 • 自然联接 • 完全(或双边)外部联接 版权所有 © 2006,Oracle。保留所有权利。 练习 5:概览 本练习包含以下主题: • 使用等值联接将表联接起来 • 执行外部联接和自联接 • 添加条件 版权所有 © 2006,Oracle。保留所有权利。 使用子查询来解析查询 版权所有 © 2006,Oracle。保留所有权利。 课程目标 学完本课后,应能完成以下工作: • 定义子查询 • 描述子查询可以解决的问题类型 • 列出子查询的类型 • 编写单行和多行子查询 版权所有 © 2006,Oracle。保留所有权利。 使用子查询解决问题 谁的薪金高于 Abel 的薪金? 哪些雇员的薪金高于 Abel 的薪金? 主查询: Abel 的薪金是多少? 子查询: 版权所有 © 2006,Oracle。保留所有权利。 子查询语法 • 子查询(内部查询)在主查询(外部查询)执行之前执 行一次。 • 主查询会使用子查询的结果。 SELECT select_list FROM table WHERE expr operator (SELECT select_list FROM table); 版权所有 © 2006,Oracle。保留所有权利。 使用子查询 SELECT last_name, salary FROM employees WHERE salary > (SELECT salary FROM employees WHERE last_name = 'Abel'); 11000 版权所有 © 2006,Oracle。保留所有权利。 使用子查询的准则 • 将子查询括在括号中。 • 将子查询放置在比较条件的右侧。 • 只有在执行 Top-N 分析时,子查询中才需要使用 ORDER BY 子句。 • 对单行子查询使用单行运算符,对多行子查询使用多行 运算符。 版权所有 © 2006,Oracle。保留所有权利。 子查询的类型 • 单行子查询 • 多行子查询 主查询 子查询 返回 ST_CLERK ST_CLERK SA_MAN 主查询 子查询 返回 版权所有 © 2006,Oracle。保留所有权利。 单行子查询 • 仅返回一行 • 使用单行比较运算符 大于或等于>= 小于< 小于或等于<= 等于= 不等于<> 大于> 含义运算符 版权所有 © 2006,Oracle。保留所有权利。 执行单行子查询 SELECT last_name, job_id, salary FROM employees WHERE job_id = (SELECT job_id FROM employees WHERE employee_id = 141) AND salary > (SELECT salary FROM employees WHERE employee_id = 143); ST_CLERK 2600 版权所有 © 2006,Oracle。保留所有权利。 在子查询中使用分组函数 SELECT last_name, job_id, salary FROM employees WHERE salary = (SELECT MIN(salary) FROM employees); 2500 版权所有 © 2006,Oracle。保留所有权利。 带有子查询的 HAVING 子句 • Oracle 服务器会先执行子查询。 • Oracle 服务器会将结果返回到主查询的 HAVING 子 句中。 SELECT department_id, MIN(salary) FROM employees GROUP BY department_id HAVING MIN(salary) > (SELECT MIN(salary) FROM employees WHERE department_id = 50); 2500 版权所有 © 2006,Oracle。保留所有权利。 此语句中有什么错误 ERROR at line 4: ORA-01427: single-row subquery returns more than one row 对多行子查询使用了单行运算符 SELECT employee_id, last_name FROM employees WHERE salary = (SELECT MIN(salary) FROM employees GROUP BY department_id); 版权所有 © 2006,Oracle。保留所有权利。 此语句是否会返回行 no rows selected 子查询没有返回任何值 SELECT last_name, job_id FROM employees WHERE job_id = (SELECT job_id FROM employees WHERE last_name = 'Haas'); 版权所有 © 2006,Oracle。保留所有权利。 多行子查询 • 返回多个行 • 使用多行比较运算符 将值与子查询返回的每个值进行比较ALL 等于列表中的任意一个成员IN 将值与子查询返回的任意一个值进行比较ANY 含义运算符 版权所有 © 2006,Oracle。保留所有权利。 在多行子查询中使用 ANY 运算符 … SELECT employee_id, last_name, job_id, salary FROM employees WHERE salary < ANY (SELECT salary FROM employees WHERE job_id = 'IT_PROG') AND job_id <> 'IT_PROG'; 9000, 6000, 4200 版权所有 © 2006,Oracle。保留所有权利。 在多行子查询中使用 ALL 运算符 SELECT employee_id, last_name, job_id, salary FROM employees WHERE salary < ALL (SELECT salary FROM employees WHERE job_id = 'IT_PROG') AND job_id <> 'IT_PROG'; 9000, 6000, 4200 版权所有 © 2006,Oracle。保留所有权利。 子查询中的空值 SELECT emp.last_name FROM employees emp WHERE emp.employee_id NOT IN (SELECT mgr.manager_id FROM employees mgr); no rows selected 版权所有 © 2006,Oracle。保留所有权利。 小结 在本课中,您应该已经学会: • 确定在什么情况下子查询可以帮助解决问题 • 在查询基于未知值的情况下,编写子查询 SELECT select_list FROM table WHERE expr operator (SELECT select_list FROM table); 版权所有 © 2006,Oracle。保留所有权利。 练习 6:概览 本练习包含以下主题: • 创建根据未知标准查询值的子查询 • 使用子查询找出那些仅存在于一个数据集而没有在其它 数据集中的值 版权所有 © 2006,Oracle。保留所有权利。 使用集合运算符 版权所有 © 2006,Oracle。保留所有权利。 课程目标 学完本课后,应能完成以下工作: • 描述集合运算符 • 使用集合运算符将多个查询组成一个查询 • 控制返回行的顺序 版权所有 © 2006,Oracle。保留所有权利。 集合运算符 UNION/UNION ALL A B AB AB INTERSECT AB MINUS 版权所有 © 2006,Oracle。保留所有权利。 本课中使用的表 本课中使用的表为: • EMPLOYEES:提供有关所有当前雇员的详细资料 • JOB_HISTORY:记录雇员更换职务时先前职务的开始 日期和结束日期、职务标识号及部门的详细资料 版权所有 © 2006,Oracle。保留所有权利。 UNION 运算符 A B UNION 运算符将从两个查询中返回不包含重复项的结果 版权所有 © 2006,Oracle。保留所有权利。 使用 UNION 运算符 显示所有雇员的当前职务和先前职务的详细资料。每位雇员 仅显示一次。 SELECT employee_id, job_id FROM employees UNION SELECT employee_id, job_id FROM job_history; … … 版权所有 © 2006,Oracle。保留所有权利。 UNION ALL 运算符 UNION ALL 运算符将从两个查询中返回结果,包括所有 重复的行。 A B 版权所有 © 2006,Oracle。保留所有权利。 使用 UNION ALL 运算符 显示所有雇员的当前部门和先前部门。 SELECT employee_id, job_id, department_id FROM employees UNION ALL SELECT employee_id, job_id, department_id FROM job_history ORDER BY employee_id; … … 版权所有 © 2006,Oracle。保留所有权利。 INTERSECT 运算符 INTERSECT 运算符将返回两个查询的共同行 A B 版权所有 © 2006,Oracle。保留所有权利。 使用 INTERSECT 运算符 显示符合以下条件的雇员的雇员 ID 和职务 ID:这些雇员的 当前职务与其最初被聘用时的职务相同,就是说这些雇员曾 担任过别的职务,但现在又再次担任了最初的职务。 SELECT employee_id, job_id FROM employees INTERSECT SELECT employee_id, job_id FROM job_history; 版权所有 © 2006,Oracle。保留所有权利。 MINUS 运算符 A B MINUS 运算符将返回出现在第一个查询中而没有出现在 第二个查询中的行。 版权所有 © 2006,Oracle。保留所有权利。 MINUS 运算符 显示从未更换过职务的雇员的雇员 ID。 SELECT employee_id FROM employees MINUS SELECT employee_id FROM job_history; … 版权所有 © 2006,Oracle。保留所有权利。 集合运算符准则 • SELECT 列表中的表达式在数量和数据类型上必须是相 匹配的。 • 可以使用括号更改执行顺序。 • ORDER BY 子句: – 只能出现在语句的最后 – 将接受第一个 SELECT 语句中的列名、别名或位置记号 版权所有 © 2006,Oracle。保留所有权利。 Oracle 服务器和集合运算符 • 除非使用了 UNION ALL 运算符,否则会自动删除重复 的行。 • 第一个查询的列名将显示在结果中。 • 除非使用了 UNION ALL 运算符,否则默认情况下输出 按升序进行排序。 版权所有 © 2006,Oracle。保留所有权利。 匹配 SELECT 语句 使用 UNION 运算符,可以显示所有雇员的部门 ID、位置和 聘用日期。 SELECT department_id, TO_NUMBER(null) location, hire_date FROM employees UNION SELECT department_id, location_id, TO_DATE(null) FROM departments; … 版权所有 © 2006,Oracle。保留所有权利。 匹配 SELECT 语句:示例 使用 UNION 运算符,可以显示所有雇员的雇员 ID、职务 ID 和薪金。 SELECT employee_id, job_id,salary FROM employees UNION SELECT employee_id, job_id,0 FROM job_history; … 版权所有 © 2006,Oracle。保留所有权利。 控制行的顺序 使用两个 UNION 运算符生成一个英文句子。 COLUMN a_dummy NOPRINT SELECT 'sing' AS "My dream", 3 a_dummy FROM dual UNION SELECT 'I''d like to teach', 1 a_dummy FROM dual UNION SELECT 'the world to', 2 a_dummy FROM dual ORDER BY a_dummy; 版权所有 © 2006,Oracle。保留所有权利。 小结 在本课中,您应该已经学会: • 使用 UNION 返回所有不同的行 • 使用 UNION ALL 返回所有行,包括重复的行 • 使用 INTERSECT 返回两个查询所共有的所有行 • 使用 MINUS 返回由第一个查询选定的且没有出现在第 二个查询中的所有不同行 • 仅在语句的最后使用 ORDER BY 版权所有 © 2006,Oracle。保留所有权利。 练习 7:概览 在本练习中,您将使用以下集合运算符创建报表: • 使用 UNION 运算符 • 使用 INTERSECTION 运算符 • 使用 MINUS 运算符 版权所有 © 2006,Oracle。保留所有权利。 处理数据 版权所有 © 2006,Oracle。保留所有权利。 课程目标 学完本课后,应能完成以下工作: • 描述各个数据操纵语言 (DML) 语句 • 在表中插入行 • 更新表中的行 • 从表中删除行 • 控制事务处理 版权所有 © 2006,Oracle。保留所有权利。 数据操纵语言 • 在进行以下操作时,会执行 DML 语句: – 在表中添加新行 – 修改表中现有的行 – 从表中删除现有的行 • 事务处理由 DML 语句的集合组成,这些语句构成一个 逻辑工作单元。 版权所有 © 2006,Oracle。保留所有权利。 在表中添加新行 DEPARTMENTS 在 DEPARTMENTS 表中插入新行 新行 版权所有 © 2006,Oracle。保留所有权利。 INSERT 语句语法 • 使用 INSERT 语句在表中添加新行: • 使用此语法一次只能插入一行。 INSERT INTO table [(column [, column...])] VALUES (value [, value...]); 版权所有 © 2006,Oracle。保留所有权利。 插入新行 • 插入一个新行,此行的每一列都含有值 • 按照表中列的默认顺序列出这些值 • (可选)在 INSERT 子句中列出列 • 将字符和日期值包含在单引号中 INSERT INTO departments(department_id, department_name, manager_id, location_id) VALUES (70, 'Public Relations', 100, 1700); 1 row created. 版权所有 © 2006,Oracle。保留所有权利。 插入带有空值的行 • 隐式方法:在列的列表中省略该列 • 显式方法:在 VALUES 子句中指定 NULL 关键字 INSERT INTO departments (department_id, department_name ) VALUES (30, 'Purchasing'); 1 row created. INSERT INTO departments VALUES (100, 'Finance', NULL, NULL); 1 row created. 版权所有 © 2006,Oracle。保留所有权利。 插入特殊值 SYSDATE 函数记录当前日期和时间 INSERT INTO employees (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id) VALUES (113, 'Louis', 'Popp', 'LPOPP', '515.124.4567', SYSDATE, 'AC_ACCOUNT', 6900, NULL, 205, 100); 1 row created. 版权所有 © 2006,Oracle。保留所有权利。 插入特定日期值 • 添加新雇员 • 确认添加的内容 INSERT INTO employees VALUES (114, 'Den', 'Raphealy', 'DRAPHEAL', '515.127.4561', TO_DATE('FEB 3, 1999', 'MON DD, YYYY'), 'AC_ACCOUNT', 11000, NULL, 100, 30); 1 row created. 版权所有 © 2006,Oracle。保留所有权利。 INSERT INTO departments (department_id, department_name, location_id) VALUES (&department_id, '&department_name',&location); 创建脚本 • 在 SQL 语句中使用 & 替代来提示用户输入值 • & 是变量值的占位符 1 row created. 版权所有 © 2006,Oracle。保留所有权利。 从其它表中复制行 • 编写带有子查询的 INSERT 语句: • 不要使用 VALUES 子句。 • 使 INSERT 子句中的列数与子查询中的列数匹配。 INSERT INTO sales_reps(id, name, salary, commission_pct) SELECT employee_id, last_name, salary, commission_pct FROM employees WHERE job_id LIKE '%REP%'; 4 rows created. 版权所有 © 2006,Oracle。保留所有权利。 更改表中的数据 EMPLOYEES 更新 EMPLOYEES 表中的行: 版权所有 © 2006,Oracle。保留所有权利。 UPDATE 语句语法 • 使用 UPDATE 语句修改现有的行: • 如果需要,可以一次更新多行。 UPDATE table SET column = value [, column = value, ...] [WHERE condition]; 版权所有 © 2006,Oracle。保留所有权利。 更新表中的行 • 如果指定了 WHERE 子句,则可以修改特定的一行或 多行: • 如果省略 WHERE 子句,则会修改表中的所有行: UPDATE employees SET department_id = 70 WHERE employee_id = 113; 1 row updated. UPDATE copy_emp SET department_id = 110; 22 rows updated. 版权所有 © 2006,Oracle。保留所有权利。 使用子查询更新两个列 更新雇员 114 的职务和薪金,使其与雇员 205 的职务和薪 金相匹配。 UPDATE employees SET job_id = (SELECT job_id FROM employees WHERE employee_id = 205), salary = (SELECT salary FROM employees WHERE employee_id = 205) WHERE employee_id = 114; 1 row updated. 版权所有 © 2006,Oracle。保留所有权利。 根据另一个表更新行 根据另一个表中的值,在 UPDATE 语句中使用子查询来更 新表中的行: UPDATE copy_emp SET department_id = (SELECT department_id FROM employees WHERE employee_id = 100) WHERE job_id = (SELECT job_id FROM employees WHERE employee_id = 200); 1 row updated. 版权所有 © 2006,Oracle。保留所有权利。 从 DEPARTMENTS 表中删除一行: 从表中删除行 DEPARTMENTS 版权所有 © 2006,Oracle。保留所有权利。 DELETE 语句 使用 DELETE 语句,可以从表中删除现有的行: DELETE [FROM] table [WHERE condition]; 版权所有 © 2006,Oracle。保留所有权利。 从表中删除行 • 如果指定 WHERE 子句,则可以删除特定的行: • 如果省略 WHERE 子句,则会删除表中的所有行: DELETE FROM departments WHERE department_name = 'Finance'; 1 row deleted. DELETE FROM copy_emp; 22 rows deleted. 版权所有 © 2006,Oracle。保留所有权利。 根据另一个表删除行 根据另一个表中的值在 DELETE 语句中使用子查询来删除 表中的行: DELETE FROM employees WHERE department_id = (SELECT department_id FROM departments WHERE department_name LIKE '%Public%'); 1 row deleted. 版权所有 © 2006,Oracle。保留所有权利。 TRUNCATE 语句 • 从表中删除所有行,使表为空并保留表结构不变 • 是数据定义语言 (DDL) 语句而不是 DML 语句,无法轻 易地将其还原。 • 语法: • 示例: TRUNCATE TABLE table_name; TRUNCATE TABLE copy_emp; 版权所有 © 2006,Oracle。保留所有权利。 在 INSERT 语句中使用子查询 INSERT INTO (SELECT employee_id, last_name, email, hire_date, job_id, salary, department_id FROM employees WHERE department_id = 50) VALUES (99999, 'Taylor', 'DTAYLOR', TO_DATE('07-JUN-99', 'DD-MON-RR'), 'ST_CLERK', 5000, 50); 1 row created. 版权所有 © 2006,Oracle。保留所有权利。 在 INSERT 语句中使用子查询 验证结果: SELECT employee_id, last_name, email, hire_date, job_id, salary, department_id FROM employees WHERE department_id = 50; 版权所有 © 2006,Oracle。保留所有权利。 数据库事务处理 数据库事务处理由以下语句之一组成: • 对数据进行一次一致更改的 DML 语句 • 一条 DDL 语句 • 一条数据控制语言 (DCL) 语句 版权所有 © 2006,Oracle。保留所有权利。 数据库事务处理 • 在执行第一条 DML SQL 语句时开始 • 在发生下列事件之一时结束: – 发出 COMMIT 或 ROLLBACK 语句 – 执行 DDL 或 DCL 语句(自动提交) – 用户退出 iSQL*Plus – 系统崩溃 版权所有 © 2006,Oracle。保留所有权利。 COMMIT 和 ROLLBACK 语句的优点 使用 COMMIT 和 ROLLBACK 语句,您可以: • 确保数据的一致性 • 在使更改变成永久性更改之前预览数据更改 • 按逻辑关系对相关操作进行分组 版权所有 © 2006,Oracle。保留所有权利。 控制事务处理 SAVEPOINT B SAVEPOINT A DELETE INSERT UPDATE INSERT COMMIT时间 事务处理 ROLLBACK to SAVEPOINT B ROLLBACK to SAVEPOINT A ROLLBACK 版权所有 © 2006,Oracle。保留所有权利。 将更改回退到某个标记 • 使用 SAVEPOINT 语句在当前事务处理中创建一个标记 • 使用 ROLLBACK TO SAVEPOINT 语句回退到该标记 UPDATE... SAVEPOINT update_done; Savepoint created. INSERT... ROLLBACK TO update_done; Rollback complete. 版权所有 © 2006,Oracle。保留所有权利。 隐式事务处理 • 在下列情况下将发生自动提交: – 发出 DDL 语句 – 发出 DCL 语句 – 从 iSQL*Plus 中正常退出,而没有显式发出 COMMIT 或 ROLLBACK 语句 • 在 iSQL*Plus 异常终止或出现系统故障的情况下会发生 自动回退。 版权所有 © 2006,Oracle。保留所有权利。 执行 COMMIT 或 ROLLBACK 操作之前的数据状态 • 可以将数据还原到以前的状态。 • 当前用户可以使用 SELECT 语句查看 DML 操作的结果。 • 其他用户不能查看当前用户的 DML 语句的结果。 • 受影响的行将被锁定,其他用户不能更改受影响的行中 的数据。 版权所有 © 2006,Oracle。保留所有权利。 执行 COMMIT 操作之后的数据状态 • 数据库中的数据更改将变成永久性的更改。 • 以前的数据状态将永久丢失。 • 所有用户都可以查看结果。 • 受影响的行上的锁将被释放,其他用户可以对这些行进 行处理。 • 所有保存点都将被清除。 版权所有 © 2006,Oracle。保留所有权利。 提交数据 • 进行更改: • • • • • • 提交更改: DELETE FROM employees WHERE employee_id = 99999; 1 row deleted. INSERT INTO departments VALUES (290, 'Corporate Tax', NULL, 1700); 1 row created. COMMIT; Commit complete. 版权所有 © 2006,Oracle。保留所有权利。 执行 ROLLBACK 操作之后的数据状态 使用 ROLLBACK 语句放弃所有待定更改之后,会有如下结果: • 数据更改将被还原 • 数据将还原到以前的状态 • 受影响的行上的锁将被释放 DELETE FROM copy_emp; 20 rows deleted. ROLLBACK ; Rollback complete. 版权所有 © 2006,Oracle。保留所有权利。 执行 ROLLBACK 操作之后的数据状态 DELETE FROM test; 25,000 rows deleted. ROLLBACK; Rollback complete. DELETE FROM test WHERE id = 100; 1 row deleted. SELECT * FROM test WHERE id = 100; No rows selected. COMMIT; Commit complete. 版权所有 © 2006,Oracle。保留所有权利。 语句级回退 • 如果在执行期间单个 DML 语句失败,则只需回退该语句。 • Oracle 服务器将实现隐式保存点。 • 将保留其它所有更改。 • 用户应通过执行 COMMIT 或 ROLLBACK 语句显式终止事务 处理。 版权所有 © 2006,Oracle。保留所有权利。 读一致性 • 读一致性可以确保用户所看到的数据始终是一致的。 • 一个用户进行的更改不会与另一个用户进行的更改相冲突。 • 读一致性可以确保对于同一数据: – 读取者不必等待写入者完成操作即可进行读取 – 写入者不必等待读取者完成操作即可进行写入 版权所有 © 2006,Oracle。保留所有权利。 实现读一致性 SELECT * FROM userA.employees; UPDATE employees SET salary = 7000 WHERE last_name = 'Grant'; 数据块 还原段 已更改的和 未更改的数据 在更改 (“旧”数据) 之前 用户 A 用户 B 读一致镜像 版权所有 © 2006,Oracle。保留所有权利。 小结 在本课中,您应该已经学会如何使用以下语句: 在表中添加新行INSERT 修改表中现有的行UPDATE 从表中删除现有的行DELETE 使所有待定的更改变成永久性的COMMIT 放弃所有待定的数据更改ROLLBACK 用于回退到保存点标记SAVEPOINT 说明语句 版权所有 © 2006,Oracle。保留所有权利。 练习 8:概览 本练习包含以下主题: • 在表中插入行 • 更新和删除表中的行 • 控制事务处理 版权所有 © 2006,Oracle。保留所有权利。 使用 DDL 语句创建和管理表 版权所有 © 2006,Oracle。保留所有权利。 课程目标 学完本课后,应能完成以下工作: • 对主要的数据库对象进行分类 • 查看表结构 • 列举列可以使用的数据类型 • 创建简单的表 • 说明创建表时如何创建约束条件 • 描述方案对象如何工作 版权所有 © 2006,Oracle。保留所有权利。 数据库对象 逻辑上代表一个或多个表中数据的子集视图 生成数字值序列 基本的存储单元,由行组成表 给出对象的替代名称同义词 提高某些查询的性能索引 说明对象 版权所有 © 2006,Oracle。保留所有权利。 命名规则 表名和列名: • 必须以字母开头 • 长度必须为 1–30 个字符 • 只能包含 A–Z、a–z、0–9、_、$ 和 # • 不能与同一用户拥有的其它对象重名 • 不能是 Oracle 服务器的保留字 版权所有 © 2006,Oracle。保留所有权利。 CREATE TABLE 语句 • 您必须具有以下条件才能使用此语句: – CREATE TABLE 权限 – 一个存储区 • 可以指定如下要素: – 表名 – 列名、列数据类型和列大小 CREATE TABLE [schema.]table (column datatype [DEFAULT expr][, ...]); 版权所有 © 2006,Oracle。保留所有权利。 引用其他用户的表 • 属于其他用户的表不在该用户的方案中。 • 应使用所有者的姓名作为那些表的前缀。 USERBUSERA SELECT * FROM userB.employees; SELECT * FROM userA.employees; 版权所有 © 2006,Oracle。保留所有权利。 DEFAULT 选项 • 指定插入过程中列的默认值。 • 文字值、表达式或 SQL 函数都是合法的值。 • 其它列的名称或假列是非法值。 • 默认数据类型必须与列的数据类型相匹配。 ... hire_date DATE DEFAULT SYSDATE, ... CREATE TABLE hire_dates (id NUMBER(8), hire_date DATE DEFAULT SYSDATE); Table created. 版权所有 © 2006,Oracle。保留所有权利。 创建表 • 创建表 • 确认表的创建 DESCRIBE dept CREATE TABLE dept (deptno NUMBER(2), dname VARCHAR2(14), loc VARCHAR2(13), create_date DATE DEFAULT SYSDATE); Table created. 版权所有 © 2006,Oracle。保留所有权利。 数据类型 原始二进制数据RAW 和 LONG RAW 二进制数据(最多 4 GB)BLOB 存储在外部文件中的二进制数据(最多 4 GB)BFILE 日期和时间值DATE 长度可变的字符数据(最多 2 GB)LONG 字符数据(最多 4 GB)CLOB 64 位基本编号系统,表示行在表中的唯一地址ROWID 固定长度的字符数据CHAR(size) 长度可变的数字数据NUMBER(p,s) 长度可变的字符数据VARCHAR2(size) 说明数据类型 版权所有 © 2006,Oracle。保留所有权利。 日期时间数据类型 可以使用以下几种日期时间数据类型: 作为间隔进行存储,以年和月表示INTERVAL YEAR TO MONTH 作为间隔进行存储,以天数、小时数、 分钟数和秒数表示 INTERVAL DAY TO SECOND 精确到零点几秒的日期TIMESTAMP 说明数据类型 版权所有 © 2006,Oracle。保留所有权利。 日期时间数据类型 • TIMESTAMP 数据类型是 DATE 数据类型的扩展。 • 该数据类型可以存储 DATE 数据类型的年、月和日,还 可以精确到小时、分钟和秒值以及零点几秒的值。 • (可选)可以指定时区。 TIMESTAMP[(fractional_seconds_precision)] TIMESTAMP[(fractional_seconds_precision)] WITH TIME ZONE TIMESTAMP[(fractional_seconds_precision)] WITH LOCAL TIME ZONE 版权所有 © 2006,Oracle。保留所有权利。 日期时间数据类型 • INTERVAL YEAR TO MONTH 数据类型将使用 YEAR 和 MONTH 日期时间字段存储一个时段: • INTERVAL DAY TO SECOND 数据类型将存储一个用天 数、时数、分钟数和秒数表示的时段: INTERVAL YEAR [(year_precision)] TO MONTH INTERVAL DAY [(day_precision)] TO SECOND [(fractional_seconds_precision)] 版权所有 © 2006,Oracle。保留所有权利。 包括约束条件 • 约束条件会在表级别上强制执行规则。 • 约束条件可以防止在存在相关性时删除表。 • 下列约束条件类型是有效的: – NOT NULL – UNIQUE – PRIMARY KEY – FOREIGN KEY – CHECK 版权所有 © 2006,Oracle。保留所有权利。 约束条件准则 • 可以为约束条件命名,也可以由 Oracle 服务器使用 SYS_Cn 格式生成一个名称。 • 可用以下两种方式之一创建约束条件: – 创建表的同时创建约束条件 – 创建表之后创建约束条件 • 可以在列或表级别定义约束条件。 • 可以在数据字典中查看约束条件。 版权所有 © 2006,Oracle。保留所有权利。 定义约束条件 • 语法: • 列级别的约束条件: • 表级别的约束条件: CREATE TABLE [schema.]table (column datatype [DEFAULT expr] [column_constraint], ... [table_constraint][,...]); column,... [CONSTRAINT constraint_name] constraint_type (column, ...), column [CONSTRAINT constraint_name] constraint_type, 版权所有 © 2006,Oracle。保留所有权利。 定义约束条件 • 列级别的约束条件: • 表级别的约束条件: 1 2 CREATE TABLE employees( employee_id NUMBER(6) CONSTRAINT emp_emp_id_pk PRIMARY KEY, first_name VARCHAR2(20), ...); CREATE TABLE employees( employee_id NUMBER(6), first_name VARCHAR2(20), ... job_id VARCHAR2(10) NOT NULL, CONSTRAINT emp_emp_id_pk PRIMARY KEY (EMPLOYEE_ID)); 版权所有 © 2006,Oracle。保留所有权利。 NOT NULL 约束条件 确保列不会有空值: NOT NULL 约束 条件(任何行的 该列都不能包含 空值) 没有 NOT NULL 约束 条件(任何行的该列 都可以包含空值) NOT NULL 约束条件 … 版权所有 © 2006,Oracle。保留所有权利。 UNIQUE 约束条件 EMPLOYEES UNIQUE 约束条件 INSERT INTO 不允许: 已经存在 允许 … 版权所有 © 2006,Oracle。保留所有权利。 UNIQUE 约束条件 可以在表级别也可以列级别定义此约束条件: CREATE TABLE employees( employee_id NUMBER(6), last_name VARCHAR2(25) NOT NULL, email VARCHAR2(25), salary NUMBER(8,2), commission_pct NUMBER(2,2), hire_date DATE NOT NULL, ... CONSTRAINT emp_email_uk UNIQUE(email)); 版权所有 © 2006,Oracle。保留所有权利。 PRIMARY KEY 约束条件 DEPARTMENTS PRIMARY KEY INSERT INTO不允许 (空值) 不允许 (50 已经存在) … 版权所有 © 2006,Oracle。保留所有权利。 FOREIGN KEY 约束条件 DEPARTMENTS EMPLOYEES FOREIGN KEY INSERT INTO 不允许 (9 不存在) 允许 PRIMARY KEY … … 版权所有 © 2006,Oracle。保留所有权利。 FOREIGN KEY 约束条件 可以在表级别也可以列级别定义此约束条件: CREATE TABLE employees( employee_id NUMBER(6), last_name VARCHAR2(25) NOT NULL, email VARCHAR2(25), salary NUMBER(8,2), commission_pct NUMBER(2,2), hire_date DATE NOT NULL, ... department_id NUMBER(4), CONSTRAINT emp_dept_fk FOREIGN KEY (department_id) REFERENCES departments(department_id), CONSTRAINT emp_email_uk UNIQUE(email)); 版权所有 © 2006,Oracle。保留所有权利。 FOREIGN KEY 约束条件:关键字 • FOREIGN KEY:在表约束条件级别定义子表中的列 • REFERENCES:标识父表中的表和列 • ON DELETE CASCADE:删除父表中的行时,也删除子 表中的相关行 • ON DELETE SET NULL:将相关外键值转换为空值 版权所有 © 2006,Oracle。保留所有权利。 CHECK 约束条件 • 定义每行都必须满足的一个条件 • 以下表达式是不允许的: – 引用了 CURRVAL、NEXTVAL、LEVEL 和 ROWNUM 假列 的表达式 – 调用了 SYSDATE、UID、USER 和 USERENV 函数的表 达式 – 引用了其它行中的其它值的查询 ..., salary NUMBER(2) CONSTRAINT emp_salary_min CHECK (salary > 0),... 版权所有 © 2006,Oracle。保留所有权利。 CREATE TABLE:示例 CREATE TABLE employees ( employee_id NUMBER(6) CONSTRAINT emp_employee_id PRIMARY KEY , first_name VARCHAR2(20) , last_name VARCHAR2(25) CONSTRAINT emp_last_name_nn NOT NULL , email VARCHAR2(25) CONSTRAINT emp_email_nn NOT NULL CONSTRAINT emp_email_uk UNIQUE , phone_number VARCHAR2(20) , hire_date DATE CONSTRAINT emp_hire_date_nn NOT NULL , job_id VARCHAR2(10) CONSTRAINT emp_job_nn NOT NULL , salary NUMBER(8,2) CONSTRAINT emp_salary_ck CHECK (salary>0) , commission_pct NUMBER(2,2) , manager_id NUMBER(6) , department_id NUMBER(4) CONSTRAINT emp_dept_fk REFERENCES departments (department_id)); 版权所有 © 2006,Oracle。保留所有权利。 违反约束条件 部门 55 不存在。 UPDATE employees * ERROR at line 1: ORA-02291: integrity constraint (HR.EMP_DEPT_FK) violated - parent key not found UPDATE employees SET department_id = 55 WHERE department_id = 110; 版权所有 © 2006,Oracle。保留所有权利。 违反约束条件 如果某行中包含用作其它表中的外键的主键,则不能删除 该行。 DELETE FROM departments WHERE department_id = 60; DELETE FROM departments * ERROR at line 1: ORA-02292: integrity constraint (HR.EMP_DEPT_FK) violated - child record found 版权所有 © 2006,Oracle。保留所有权利。 使用子查询创建表 • 通过组合 CREATE TABLE 语句和 AS subquery 选项 创建表并插入行。 • 使指定列的数量与子查询列的数量相匹配。 • 定义具有列名和默认值的列。 CREATE TABLE table [(column, column...)] AS subquery; 版权所有 © 2006,Oracle。保留所有权利。 使用子查询创建表 DESCRIBE dept80 CREATE TABLE dept80 AS SELECT employee_id, last_name, salary*12 ANNSAL, hire_date FROM employees WHERE department_id = 80; Table created. 版权所有 © 2006,Oracle。保留所有权利。 ALTER TABLE 语句 使用 ALTER TABLE 语句可以完成以下任务: • 添加新列 • 修改现有的列 • 为新列定义默认值 • 删除列 版权所有 © 2006,Oracle。保留所有权利。 删除表 • 删除表时也会删除该表中的所有数据和结构。 • 同时也会提交所有待定的事务处理。 • 同时也会删除所有索引。 • 同时也会删除所有约束条件。 • 无法回退 DROP TABLE 语句。 DROP TABLE dept80; Table dropped. 版权所有 © 2006,Oracle。保留所有权利。 小结 在本课中,您应该已经学会了如何使用 CREATE TABLE 语 句创建表和包括约束条件。 • 对主要的数据库对象进行分类 • 查看表结构 • 列举列可以使用的数据类型 • 创建简单的表 • 说明创建表时如何创建约束条件 • 描述方案对象如何工作 版权所有 © 2006,Oracle。保留所有权利。 练习 9:概览 本练习包含以下主题: • 创建新表 • 使用 CREATE TABLE AS 语法创建新表 • 验证表的存在 • 删除表 版权所有 © 2006,Oracle。保留所有权利。 创建其它方案对象 版权所有 © 2006,Oracle。保留所有权利。 课程目标 学完本课后,应能完成以下工作: • 创建简单的视图和复杂的视图 • 从视图中检索数据 • 创建、维护和使用序列 • 创建和维护索引 • 创建私用和公用同义词 版权所有 © 2006,Oracle。保留所有权利。 数据库对象 逻辑上代表一个或多个表中数据的子集视图 用于生成数字值序列 基本的存储单元,由行组成表 给出对象的替代名称同义词 提高某些查询的性能索引 说明对象 版权所有 © 2006,Oracle。保留所有权利。 什么是视图 EMPLOYEES 表 版权所有 © 2006,Oracle。保留所有权利。 视图的优点 限制 数据访问 使复杂的 查询变得容易 提供 数据独立性 提供相同 数据的不同视图 版权所有 © 2006,Oracle。保留所有权利。 简单视图和复杂视图 是 否 否 一个 简单视图 是是否包含函数 是是否包含数据组 一个或多个表的数量 不一定是否通过视图执行 DML 操作 复杂视图特点 版权所有 © 2006,Oracle。保留所有权利。 创建视图 • 在 CREATE VIEW 语句中嵌入一个子查询: • 该子查询可以包含复杂的 SELECT 语法。 CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view [(alias[, alias]...)] AS subquery [WITH CHECK OPTION [CONSTRAINT constraint]] [WITH READ ONLY [CONSTRAINT constraint]]; 版权所有 © 2006,Oracle。保留所有权利。 创建视图 • 创建视图 EMPVU80,其中包括部门 80 中的雇员的详细 资料: • 使用 iSQL*Plus DESCRIBE 命令描述视图的结构: DESCRIBE empvu80 CREATE VIEW empvu80 AS SELECT employee_id, last_name, salary FROM employees WHERE department_id = 80; View created. 版权所有 © 2006,Oracle。保留所有权利。 创建视图 • 在子查询中使用列别名创建视图: • 按给定的别名从该视图中选择列: CREATE VIEW salvu50 AS SELECT employee_id ID_NUMBER, last_name NAME, salary*12 ANN_SALARY FROM employees WHERE department_id = 50; View created. 版权所有 © 2006,Oracle。保留所有权利。 SELECT * FROM salvu50; 从视图中检索数据 版权所有 © 2006,Oracle。保留所有权利。 修改视图 • 可以使用 CREATE OR REPLACE VIEW 子句修改视图 EMPVU80。为每个列名添加一个别名: • CREATE OR REPLACE VIEW 子句中列出的列别名与子 查询中的列具有相同的顺序。 CREATE OR REPLACE VIEW empvu80 (id_number, name, sal, department_id) AS SELECT employee_id, first_name || ' ' || last_name, salary, department_id FROM employees WHERE department_id = 80; View created. 版权所有 © 2006,Oracle。保留所有权利。 创建复杂视图 创建包含分组函数的复杂视图以显示两个表中的值: CREATE OR REPLACE 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 JOIN departments d ON (e.department_id = d.department_id) GROUP BY d.department_name; View created. 版权所有 © 2006,Oracle。保留所有权利。 对视图执行 DML 操作的规则 • 通常可以对简单视图执行 DML 操作。 • 如果视图包含以下内容,则不能删除行: – 分组函数 – GROUP BY 子句 – DISTINCT 关键字 – 伪列 ROWNUM 关键字 版权所有 © 2006,Oracle。保留所有权利。 对视图执行 DML 操作的规则 如果视图包含以下内容,则不能修改视图中的数据: • 分组函数 • GROUP BY 子句 • DISTINCT 关键字 • 伪列 ROWNUM 关键字 • 由表达式定义的列 版权所有 © 2006,Oracle。保留所有权利。 对视图执行 DML 操作的规则 如果视图包括以下内容,则不能向视图添加数据: • 分组函数 • GROUP BY 子句 • DISTINCT 关键字 • 伪列 ROWNUM 关键字 • 由表达式定义的列 • 基表中未被视图选中的 NOT NULL 列 版权所有 © 2006,Oracle。保留所有权利。 使用 WITH CHECK OPTION 子句 • 使用 WITH CHECK OPTION 子句可确保对视图执行的 DML 操作只在视图范围内起作用: • 尝试在视图中更改任一行的部门编号都不会成功,因为 此操作违反了 WITH CHECK OPTION 约束条件。 CREATE OR REPLACE VIEW empvu20 AS SELECT * FROM employees WHERE department_id = 20 WITH CHECK OPTION CONSTRAINT empvu20_ck ; View created. 版权所有 © 2006,Oracle。保留所有权利。 拒绝 DML 操作 • 通过在视图定义中添加 WITH READ ONLY 选项可以确 保系统不会执行 DML 操作。 • 尝试对视图中的行执行 DML 操作会导致 Oracle 服务器 错误。 版权所有 © 2006,Oracle。保留所有权利。 拒绝 DML 操作 CREATE OR REPLACE VIEW empvu10 (employee_number, employee_name, job_title) AS SELECT employee_id, last_name, job_id FROM employees WHERE department_id = 10 WITH READ ONLY ; View created. 版权所有 © 2006,Oracle。保留所有权利。 删除视图 因为视图是基于数据库中的基表,所以删除视图不会导致丢 失数据。 DROP VIEW view; DROP VIEW empvu80; View dropped. 版权所有 © 2006,Oracle。保留所有权利。 练习 10,第 1 部分:概览 本练习包含以下主题: • 创建简单的视图 • 创建复杂的视图 • 创建具有检查约束条件的视图 • 尝试修改视图中的数据 • 删除视图 版权所有 © 2006,Oracle。保留所有权利。 序列 逻辑上代表一个或多个表中数据的子集视图 用于生成数字值序列 基本的存储单元,由行组成表 给出对象的替代名称同义词 提高某些查询的性能索引 说明对象 版权所有 © 2006,Oracle。保留所有权利。 序列 序列具有如下特点: • 可以自动生成唯一编号 • 是一个可共享的对象 • 可用于创建主键值 • 替换应用程序代码 • 如果将序列高速缓存到内存中,则访问序列值的效率 会有所提高 1 2 4 3 5 6 8 7 10 9 版权所有 © 2006,Oracle。保留所有权利。 CREATE SEQUENCE 语句:语法 定义一个可以自动生成序号的序列: CREATE SEQUENCE sequence [INCREMENT BY n] [START WITH n] [{MAXVALUE n | NOMAXVALUE}] [{MINVALUE n | NOMINVALUE}] [{CYCLE | NOCYCLE}] [{CACHE n | NOCACHE}]; 版权所有 © 2006,Oracle。保留所有权利。 创建序列 • 创建一个名为 DEPT_DEPTID_SEQ 的序列,将其用作 DEPARTMENTS 表的主键。 • 不使用 CYCLE 选项。 CREATE SEQUENCE dept_deptid_seq INCREMENT BY 10 START WITH 120 MAXVALUE 9999 NOCACHE NOCYCLE; Sequence created. 版权所有 © 2006,Oracle。保留所有权利。 NEXTVAL 和 CURRVAL 伪列 • NEXTVAL 会返回下一个可用的序列值。每次被引用时 NEXTVAL 都会返回一个唯一值,即使对于不同的用户也 是如此。 • CURRVAL 会获得当前序列值。 • 只有对序列发出 NEXTVAL 之后,CURRVAL 才能包含值。 版权所有 © 2006,Oracle。保留所有权利。 使用序列 • 在位置 ID 2500 中插入一个名为“Support”的新部门: • 查看 DEPT_DEPTID_SEQ 序列的当前值: SELECT dept_deptid_seq.CURRVAL FROM dual; INSERT INTO departments(department_id, department_name, location_id) VALUES (dept_deptid_seq.NEXTVAL, 'Support', 2500); 1 row created. 版权所有 © 2006,Oracle。保留所有权利。 高速缓存序列值 • 将序列值高速缓存在内存中,这样便可以更快地对这些 值进行访问。 • 在发生以下情况时,序列值中会出现间断: – 发生回退 – 系统崩溃 – 在其它表中使用了序列 版权所有 © 2006,Oracle。保留所有权利。 修改序列 更改增量值、最大值、最小值、循环选项或高速缓存选项: ALTER SEQUENCE dept_deptid_seq INCREMENT BY 20 MAXVALUE 999999 NOCACHE NOCYCLE; Sequence altered. 版权所有 © 2006,Oracle。保留所有权利。 修改序列的准则 • 您必须是序列的所有者或对该序列具有 ALTER 权限。 • 修改只会影响以后生成的序列号。 • 如果要从另一编号处重新开始,则必须删除原有的序列 然后进行重新创建。 • 会执行一些验证操作。 • 要删除序列,请使用 DROP 语句: DROP SEQUENCE dept_deptid_seq; Sequence dropped. 版权所有 © 2006,Oracle。保留所有权利。 索引 逻辑上代表一个或多个表中数据的子集视图 用于生成数字值序列 基本的存储单元,由行组成表 给出对象的替代名称同义词 提高某些查询的性能索引 说明对象 版权所有 © 2006,Oracle。保留所有权利。 索引 索引具有以下特点: • 是一个方案对象 • Oracle 服务器可以使用它来通过指针加速对行的检索 • 通过使用快速路径访问方法来迅速地找到数据,可以减 少磁盘 I/O • 与其索引的表无关 • Oracle 服务器会自动使用并维护它 版权所有 © 2006,Oracle。保留所有权利。 如何创建索引 • 自动创建:如果在表定义中定义了 PRIMARY KEY 或 UNIQUE 约束条件,则系统会自动创建一个唯一的索引。 • 手动创建:用户可以对列创建非唯一的索引,以加速对 行的访问。 版权所有 © 2006,Oracle。保留所有权利。 创建索引 • 对一个或多个列创建索引: • 提高对 EMPLOYEES 表中 LAST_NAME 列的查询访问 速度: CREATE INDEX emp_last_name_idx ON employees(last_name); Index created. CREATE INDEX index ON table (column[, column]...); 版权所有 © 2006,Oracle。保留所有权利。 索引创建准则 不要在以下情况下创建索引: 在查询中列没有被频繁用作条件 表比较小,或者预计大多数查询要检索的行将超过表中行数的 2% 至 4% 表更新频繁 列包含大量空值 在 WHERE 子句或联接条件中频繁使用了一个或多个列 列所包含的值的范围很大 系统作为表达式的一部分引用了被索引的列 表很大,但是预计大多数查询要检索的行将小于表中行数的 2% 至 4% 在以下情况下,应该创建索引: 版权所有 © 2006,Oracle。保留所有权利。 删除索引 • 使用 DROP INDEX 命令可以从数据字典中删除索引: • 从数据字典中删除 UPPER_LAST_NAME_IDX 索引: • 要删除索引,您必须是该索引的所有者或具有 DROP ANY INDEX 权限。 DROP INDEX emp_last_name_idx; Index dropped. DROP INDEX index; 版权所有 © 2006,Oracle。保留所有权利。 同义词 逻辑上代表一个或多个表中数据的子集视图 用于生成数字值序列 基本的存储单元,由行组成表 给出对象的替代名称同义词 提高某些查询的性能索引 说明对象 版权所有 © 2006,Oracle。保留所有权利。 同义词 通过创建同义词(对象的另一个名称)可以简化对对象的访 问。使用同义词,您可以: • 更方便地引用其他用户拥有的表 • 缩短冗长的对象名 CREATE [PUBLIC] SYNONYM synonym FOR object; 版权所有 © 2006,Oracle。保留所有权利。 创建和删除同义词 • 为 DEPT_SUM_VU 视图创建一个简短的名称: • 删除同义词: CREATE SYNONYM d_sum FOR dept_sum_vu; Synonym Created. DROP SYNONYM d_sum; Synonym dropped. 版权所有 © 2006,Oracle。保留所有权利。 小结 在本课中,您应该已经学会: • 创建、使用和删除视图 • 使用序列生成器自动生成序列号 • 创建索引以提高查询检索速度 • 使用同义词为对象提供替代名称 版权所有 © 2006,Oracle。保留所有权利。 练习 10,第 2 部分:概览 本练习包含以下主题: • 创建序列 • 使用序列 • 创建非唯一索引 • 创建同义词 版权所有 © 2006,Oracle。保留所有权利。 使用数据字典视图管理对象 版权所有 © 2006,Oracle。保留所有权利。 课程目标 学完本课后,应能完成以下工作: • 使用数据字典视图研究有关对象的数据 • 对多种数据字典视图进行查询 版权所有 © 2006,Oracle。保留所有权利。 数据字典 Oracle 服务器 包含业务数据的表: EMPLOYEES DEPARTMENTS LOCATIONS JOB_HISTORY ... 数据字典视图: DICTIONARY USER_OBJECTS USER_TABLES USER_TAB_COLUMNS ... 版权所有 © 2006,Oracle。保留所有权利。 数据字典结构 Oracle 服务器 由以下几部分组成: – 基表 – 用户可以访问的视图 版权所有 © 2006,Oracle。保留所有权利。 数据字典结构 视图命名惯例: 数据库管理员的视图(每个人的方案中的 视图) DBA 性能相关的数据V$ 用户的视图(您的方案中的视图,您所拥 有的视图) USER 扩展的用户的视图(您可以访问的视图)ALL 用途视图前缀 版权所有 © 2006,Oracle。保留所有权利。 如何使用字典视图 以 DICTIONARY 为开始。它包含字典表和视图的名称和说明。 DESCRIBE DICTIONARY SELECT * FROM dictionary WHERE table_name = 'USER_OBJECTS'; 版权所有 © 2006,Oracle。保留所有权利。 USER_OBJECTS 和 ALL_OBJECTS 视图 USER_OBJECTS: • 查询 USER_OBJECTS 可以查看您所拥有的所有对象 • 是一种很有用的方法,可以获得您的方案中的所有对象 名列表和类型以及下列信息: – 创建日期 – 上次修改日期 – 状态(有效或无效) ALL_OBJECTS: • 查询 ALL_OBJECTS 可以查看您对其有访问权限的所有 对象 版权所有 © 2006,Oracle。保留所有权利。 USER_OBJECTS 视图 SELECT object_name, object_type, created, status FROM user_objects ORDER BY object_type; … 版权所有 © 2006,Oracle。保留所有权利。 表信息 USER_TABLES: … DESCRIBE user_tables SELECT table_name FROM user_tables; 版权所有 © 2006,Oracle。保留所有权利。 列信息 USER_TAB_COLUMNS: DESCRIBE user_tab_columns … 版权所有 © 2006,Oracle。保留所有权利。 列信息 SELECT column_name, data_type, data_length, data_precision, data_scale, nullable FROM user_tab_columns WHERE table_name = 'EMPLOYEES'; 版权所有 © 2006,Oracle。保留所有权利。 约束条件信息 • USER_CONSTRAINTS 描述有关表的约束条件定义。 • USER_CONS_COLUMNS 描述您所拥有的列和约束条件中 指定的列。 DESCRIBE user_constraints … 版权所有 © 2006,Oracle。保留所有权利。 约束条件信息 SELECT constraint_name, constraint_type, search_condition, r_constraint_name, delete_rule, status FROM user_constraints WHERE table_name = 'EMPLOYEES'; 版权所有 © 2006,Oracle。保留所有权利。 约束条件信息 DESCRIBE user_cons_columns SELECT constraint_name, column_name FROM user_cons_columns WHERE table_name = 'EMPLOYEES'; … 版权所有 © 2006,Oracle。保留所有权利。 视图信息 DESCRIBE user_views SELECT DISTINCT view_name FROM user_views; SELECT text FROM user_views WHERE view_name = 'EMP_DETAILS_VIEW'; 1 2 3 版权所有 © 2006,Oracle。保留所有权利。 序列信息 DESCRIBE user_sequences 版权所有 © 2006,Oracle。保留所有权利。 序列信息 • 验证 USER_SEQUENCES 数据字典表中的序列值。 • 如果指定了 NOCACHE,则 LAST_NUMBER 列将显示下 一个可用的序列号。 SELECT sequence_name, min_value, max_value, increment_by, last_number FROM user_sequences; 版权所有 © 2006,Oracle。保留所有权利。 同义词信息 DESCRIBE user_synonyms SELECT * FROM user_synonyms; 版权所有 © 2006,Oracle。保留所有权利。 向表添加注释 • 可以通过使用 COMMENT 语句将注释添加到表或列: • 可以在以下数据字典视图中查看注释: – ALL_COL_COMMENTS – USER_COL_COMMENTS – ALL_TAB_COMMENTS – USER_TAB_COMMENTS COMMENT ON TABLE employees IS 'Employee Information'; Comment created. 版权所有 © 2006,Oracle。保留所有权利。 小结 在本课中,您应该已经学会了如何通过以下字典视图找到 对象的相关信息: • DICTIONARY • USER_OBJECTS • USER_TABLES • USER_TAB_COLUMNS • USER_CONSTRAINTS • USER_CONS_COLUMNS • USER_VIEWS • USER_SEQUENCES • USER_TAB_SYNONYMS 版权所有 © 2006,Oracle。保留所有权利。 练习 11:概览 本练习包含以下主题: • 查询字典视图以获得表和列信息 • 查询字典视图以获得约束条件信息 • 查询字典视图以获得视图信息 • 查询字典视图以获得序列信息 • 查询字典视图以获得同义词信息 • 向表中添加注释,然后查询字典视图以获得注释信息 版权所有 © 2006,Oracle。保留所有权利。 Oracle 联接语法 版权所有 © 2006,Oracle。保留所有权利。 课程目标 学完本课后,应能完成以下工作: • 编写 SELECT 语句,以使用等值联接和非等值联接访问 多个表中的数据 • 使用外部联接查看通常不符合联接条件的数据 • 使用自联接将表联接到自身 版权所有 © 2006,Oracle。保留所有权利。 获取多个表中的数据 EMPLOYEES DEPARTMENTS … … 版权所有 © 2006,Oracle。保留所有权利。 笛卡尔积 • 出现以下情况时,将生成笛卡尔积: – 联接条件被省略 – 联接条件无效 – 第一个表中的所有行被联接到第二个表中的所有行上 • 为了避免生成笛卡尔积,请始终在 WHERE 子句中包括 有效的联接条件 版权所有 © 2006,Oracle。保留所有权利。 生成笛卡尔积 笛卡尔积: 20 x 8 = 160 行 EMPLOYEES(20 行) DEPARTMENTS(8 行) … … 版权所有 © 2006,Oracle。保留所有权利。 联接类型 • 等值联接 • 非等值联接 • 外部联接 • 自联接 • 交叉联接 • 自然联接 • Using 子句 • 完全(或双边)外部 联接 • 外部联接的任意联接 条件 符合 SQL:1999 的联接Oracle 专用联接 (8i 及更早的版本) 版权所有 © 2006,Oracle。保留所有权利。 使用 Oracle 语法联接表 使用联接可查询多个表中的数据: • 在 WHERE 子句中编写联接条件 • 如果在多个表中出现了相同的列名,则在列名的前面加 上表名作为前缀 SELECT table1.column, table2.column FROM table1, table2 WHERE table1.column1 = table2.column2; 版权所有 © 2006,Oracle。保留所有权利。 等值联接 EMPLOYEES DEPARTMENTS 外键 主键 … … 版权所有 © 2006,Oracle。保留所有权利。 使用等值联接检索记录 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; … 版权所有 © 2006,Oracle。保留所有权利。 使用 AND 运算符的附加搜索条件 EMPLOYEES DEPARTMENTS … … 版权所有 © 2006,Oracle。保留所有权利。 限定不确定的列名 • 使用表前缀可以限定多个表中的列名 • 使用表前缀可以提高性能 • 使用列别名可以区分具有相同名称但位于不同表中的列 版权所有 © 2006,Oracle。保留所有权利。 使用表别名 • 使用表别名可以简化查询 • 使用表前缀可以提高性能 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; 版权所有 © 2006,Oracle。保留所有权利。 联接两个以上的表 EMPLOYEES LOCATIONSDEPARTMENTS … 要将 n 个表联接在一起,最少需要 n–1 个联接条件。 例如,要联接三个表,最少需要两个联接。 版权所有 © 2006,Oracle。保留所有权利。 非等值联接 EMPLOYEES JOB_GRADES EMPLOYEES 表 中的薪金必须介于 JOB_GRADES 表中的 最低薪金和最高薪金 之间。 … 版权所有 © 2006,Oracle。保留所有权利。 使用非等值联接检索记录 … SELECT e.last_name, e.salary, j.grade_level FROM employees e, job_grades j WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal; 版权所有 © 2006,Oracle。保留所有权利。 外部联接 EMPLOYEESDEPARTMENTS 部门 190 中没有雇员。 … 版权所有 © 2006,Oracle。保留所有权利。 外部联接语法 • 使用外部联接可以查看不符合联接条件的行 • 外部联接运算符是加号 (+) 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(+); 版权所有 © 2006,Oracle。保留所有权利。 使用外部联接 SELECT e.last_name, e.department_id, d.department_name FROM employees e, departments d WHERE e.department_id(+) = d.department_id ; … 版权所有 © 2006,Oracle。保留所有权利。 自联接 EMPLOYEES (WORKER) EMPLOYEES (MANAGER) WORKER 表中的 MANAGER_ID 等于 MANAGER 表中的 EMPLOYEE_ID。 … … 版权所有 © 2006,Oracle。保留所有权利。 将表联接到自身 SELECT worker.last_name || ' works for ' || manager.last_name FROM employees worker, employees manager WHERE worker.manager_id = manager.employee_id ; … 版权所有 © 2006,Oracle。保留所有权利。 小结 在此附录中,您应该已经学会了如何通过版本 8i 和更早版 本的 Oracle 专用语法,使用联接显示多个表中的数据。 版权所有 © 2006,Oracle。保留所有权利。 练习 C:概览 在本练习中,您将使用 Oracle 语法编写查询来联接表。 版权所有 © 2006,Oracle。保留所有权利。 使用 SQL*Plus 版权所有 © 2006,Oracle。保留所有权利。 课程目标 学完本附录后,应能完成以下工作: • 登录到 SQL*Plus • 编辑 SQL 命令 • 使用 SQL*Plus 命令设置输出格式 • 与脚本文件交互 版权所有 © 2006,Oracle。保留所有权利。 SQL 和 SQL*Plus 交互 缓冲区 服务器 SQL 语句 查询结果 SQL 脚本 SQL*Plus 版权所有 © 2006,Oracle。保留所有权利。 SQL 语句和 SQL*Plus 命令 SQL 语句 SQL • 一种语言 • ANSI 标准 • 关键字不能缩写 • 其语句可以处理数据库中 的数据和表定义 SQL*Plus • 一种环境 • Oracle 专用 • 关键字可以缩写 • 不允许使用命令处理 数据库中的值 SQL 缓冲区 SQL*Plus 命令 SQL*Plus 缓冲区 版权所有 © 2006,Oracle。保留所有权利。 SQL*Plus 概览 • 登录到 SQL*Plus • 描述表结构 • 编辑您的 SQL 语句 • 在 SQL*Plus 中执行 SQL • 将 SQL 语句保存到文件,并将 SQL 语句附加到文件 • 执行已保存的文件 • 将命令从文件加载到缓冲区,以进行编辑 版权所有 © 2006,Oracle。保留所有权利。 登录到 SQL*Plus • 在 Windows 环境中: • 在命令行中: sqlplus [username[/password [@database]]] 版权所有 © 2006,Oracle。保留所有权利。 显示表结构 使用 SQL*Plus DESCRIBE 命令可以显示表的结构: DESC[RIBE] tablename 版权所有 © 2006,Oracle。保留所有权利。 显示表结构 Name Null? Type ----------------------- -------- ------------ DEPARTMENT_ID NOT NULL NUMBER(4) DEPARTMENT_NAME NOT NULL VARCHAR2(30) MANAGER_ID NUMBER(6) LOCATION_ID NUMBER(4) SQL> DESCRIBE departments 版权所有 © 2006,Oracle。保留所有权利。 SQL*Plus 编辑命令 • A[PPEND] text • C[HANGE] / old / new • C[HANGE] / text / • CL[EAR] BUFF[ER] • DEL • DEL n • DEL m n 版权所有 © 2006,Oracle。保留所有权利。 SQL*Plus 编辑命令 • I[NPUT] • I[NPUT] text • L[IST] • L[IST] n • L[IST] m n • R[UN] • n • n text • 0 text 版权所有 © 2006,Oracle。保留所有权利。 SQL> 1 SQL> L SQL> LIST 1 SELECT last_name 2* FROM employees 1* SELECT last_name SQL> A , job_id 1* SELECT last_name, job_id 1 SELECT last_name, job_id 2* FROM employees 使用 LIST、n 和 APPEND 版权所有 © 2006,Oracle。保留所有权利。 使用 CHANGE 命令 SQL> L 1* SELECT * from employees SQL> c/employees/departments 1* SELECT * from departments SQL> L 1* SELECT * from departments 版权所有 © 2006,Oracle。保留所有权利。 SQL*Plus 文件命令 • SAVE filename • GET filename • START filename • @ filename • EDIT filename • SPOOL filename • EXIT 版权所有 © 2006,Oracle。保留所有权利。 使用 SAVE 和 START 命令 Created file my_query SQL> START my_query SQL> L 1 SELECT last_name, manager_id, department_id 2* FROM employees SQL> SAVE my_query LAST_NAME MANAGER_ID DEPARTMENT_ID ------------------------- ---------- ------------- King 90 Kochhar 100 90 ... 20 rows selected. 版权所有 © 2006,Oracle。保留所有权利。 小结 在本附录中,您应该已经学会了如何将 SQL*Plus 作为一种 环境来执行以下任务: • 执行 SQL 语句 • 编辑 SQL 语句 • 设置输出的格式 • 与脚本文件交互 版权所有 © 2006,Oracle。保留所有权利。 使用 SQL Developer 版权所有 © 2006,Oracle。保留所有权利。 课程目标 学完本附录后,应能完成以下工作: • 列举 Oracle SQL Developer 的主要功能 • 安装 Oracle SQL Developer • 确定 Oracle SQL Developer 的菜单项 • 创建数据库连接 • 管理数据库对象 • 使用 SQL 工作表 • 执行 SQL 语句和 SQL 脚本 • 创建和保存报表 版权所有 © 2006,Oracle。保留所有权利。 什么是 Oracle SQL Developer • Oracle SQL Developer 是一种图形工具,可以提高工 作效率并简化数据库开发任务。 • 使用标准的 Oracle 数据库验证可以连接到任何目标 Oracle 数据库方案。 版权所有 © 2006,Oracle。保留所有权利。 主要功能 • 用 Java 进行开发 • 支持 Windows、Linux 和 Mac OS X 平台 • 使用 JDBC 瘦驱动程序进行默认连接 • 不需要安装程序 • 可以连接到任意 Oracle 数据库版本 9.2.0.1 和更高版本 • 与 JRE 1.5 捆绑在一起 版权所有 © 2006,Oracle。保留所有权利。 安装 SQL Developer 下载 Oracle SQL Developer 工具包,并将其解压缩到您的计算机上 的任意目录中。 版权所有 © 2006,Oracle。保留所有权利。 SQL Developer 的菜单 1 2 3 4 5 6 版权所有 © 2006,Oracle。保留所有权利。 创建数据库连接 • 要使用 SQL Developer,必须至少有一个数据库连接。 • 可以创建和测试连接: – 对于多个数据库 – 对于多个方案 • SQL Developer 会自动导入在系统的 tnsnames.ora 文件中定义的任何连接。 • 可以将连接导出到 XML 文件。 • 所创建的每个附加数据库连接都会在连接导航器层次中 列出。 版权所有 © 2006,Oracle。保留所有权利。 创建数据库连接 版权所有 © 2006,Oracle。保留所有权利。 浏览数据对象 使用数据库导航器可以执行以下操作: • 浏览数据库方案中的许多对象 • 快速查看这些对象的定义 版权所有 © 2006,Oracle。保留所有权利。 创建方案对象 • SQL Developer 支持通过以下方式创建任意方案对象: – 在 SQL 工作表中执行 SQL 语句 – 使用上下文菜单 • 可以通过使用编辑对话框或某个上下文相关的菜单来编 辑对象。 • 可以查看用来作出调整(如创建一个新对象或编辑一个 现有的方案对象)的 DDL。 版权所有 © 2006,Oracle。保留所有权利。 创建新表:示例 版权所有 © 2006,Oracle。保留所有权利。 使用 SQL 工作表 • 使用 SQL 工作表输入并执行 SQL、PL/SQL 和 SQL *Plus 语句。 • 指定可以由与工作表相关的数据库连接处理的所有操作。 版权所有 © 2006,Oracle。保留所有权利。 使用 SQL 工作表 1 2 3 4 5 6 7 8 版权所有 © 2006,Oracle。保留所有权利。 执行 SQL 语句 使用“Enter SQL Statement(输入 SQL 语名)”框可以 输入一条或多条 SQL 语句。 版权所有 © 2006,Oracle。保留所有权利。 查看执行计划 版权所有 © 2006,Oracle。保留所有权利。 格式化 SQL 代码 格式化之前 格式化之后 版权所有 © 2006,Oracle。保留所有权利。 使用片段 片段可能是语法或示例的代码段。 版权所有 © 2006,Oracle。保留所有权利。 使用片段:示例 插入一个片段 编辑片段 版权所有 © 2006,Oracle。保留所有权利。 使用 SQL*Plus • SQL 工作表并不支持所有的 SQL*Plus 语句。 • 可以从 SQL Developer 中调用 SQL*Plus 命令行接口。 版权所有 © 2006,Oracle。保留所有权利。 数据库报表 SQL Developer 将提供有关数据库及其对象的大量预定义 报表。 版权所有 © 2006,Oracle。保留所有权利。 创建用户定义的报表 创建并保存用户定义的报表以供反复使用。 版权所有 © 2006,Oracle。保留所有权利。 小结 在本附录中,您应该已经学会了如何使用 SQL Developer 执行以下任务: • 浏览、创建和编辑数据库对象 • 在 SQL 工作表中执行 SQL 语句和脚本 • 创建和保存自定义的报表

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

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

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

下载文档

相关文档