Oracle SQL语句教学


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

继续阅读

下载pdf到电脑,查找使用更方便

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

需要 20 金币 [ 分享pdf获得金币 ] 2 人已下载

下载pdf

pdf贡献者

qqbsky

贡献于2010-11-05

下载需要 20 金币 [金币充值 ]
亲,您也可以通过 分享原创pdf 来获得金币奖励!
下载pdf