Oracle数据库中SQL语句用法（二）

Copyright © 2019 @Linyer. All Rights Reserved

上接 Oracle数据库中SQL语句用法（一）【点击以查看】

第9章：创建和管理表

数据库对象

对象	说明
表	用于存储数据
视图	一个或多个表中数据的子集
序列	数字值生成器
索引	提高某些查询的性能
同义词	给出对象的替代名称

命名规则

• 表名 和 列名
  1. 必须以字母开头
  2. 长度必须是 1-30 个字符
  3. 只能包含A-Z，a-z，0-9，_，$ 和 #
  4. 不能与同一用户拥有的其它对象重名
  5. 不能是Oracle服务器的保留字

创建表

• 使用 CREATE TABLE creat table语句创建表
  • 此命令为一条 DDL 语句

创建具有以下三个列的DEPT表：DEPTNO、DNAME 和 LOC

CREATE TABLE dept
             (deptno NUMBER(2),
             dname VARCHAR2(14),
             loc VARCHAR2(13));

确认表的创建

DESCRIBE dept

• DEFAULT 选项
  • 指定插入时的列的默认值。
  • 文字值、表达式或SQL函数都是合法的值。
  • 其它列的名称或伪列是不合法的值。
  • 默认数据类型必须与列数据类型匹配。

Oracle 数据库中的表

用户表

• 是由用户创建和维护的表的集合
• 包含用户信息

数据字典

• 是由 Oracle Server 创建和维护的表的集合
• 包含数据库信息

• 在 Oracle 数据库中有一个表和视图的集合，称为数据字典
• 此集合是由 Oracle 服务器创建和维护的，并且包含有关数据库的信息
• 存储在数据字典中的信息包括 Oracle 服务器用户的姓名、授予用户的权限、数据库对象名称、表约束和审计信息
• 数据字典视图共有四类，每一类都有反映其预期用途的独特前缀

前缀	说明
USER_	这些视图包含关于用户拥有的对象的信息
ALL_	这些视图包含关于用户可以访问的所有表（对象表和关系表）的信息。
DBA_	这些视图是受限视图，它们只能由被指定了 DBA 角色的人员访问。
v$	这些视图是动态性能视图，反映数据库服务器性能、内存和锁的动态性能。

查询数据字典

查看用户拥有的表的名称

SELECT table_name
FROM user_tables ;

查看用户拥有的独特的对象类型

SELECT DISTINCT object_type
FROM user_objects ;

查看用户拥有的表、视图、同义词和序列

SELECT *
FROM user_catalog ;

数据类型

数据类型	说明
VARCHAR2(size)	可变长度字符数据（必须指定最大size；最小size为1；最大size为4000）
CHAR[(size)]	固定长度字符数据，长度为size字节（默认和最小size为1；最大size为2000）
NUMBER[(D，s)]	精度为p、小数位数为s的数（精度是指十进制数字的总数，而小数位数是指小数点右边的数字的位数；精度范围可以从1到38，而小数位数范围可以从-84到127）
DATE	公元前4712年1月1日到公元9999年12月31日之间的日期和时间值，精确到最接近的那一秒。
LONG	可变长度字符数据，最多为2千兆字节
CLOB	字符数据，最多为4千兆字节
RAW(size)	长度为size的原始二进制数据（必须指定最大size。最大的size为2000）
LONG RAW	可变长度的原始二进制数据，最多为2千兆字节
BLOB	二进制数据，最多为4千兆字节
BFTLE	存储在外部文件的二进制数据，最多为4千兆字节
ROWID	64位基本编号系统，表示行在表中的唯一地址。

• 当使用子查询创建表时，不会复制 LONG 列。
• LONG 列不能包含在 GROUP BY 或 ORDER BY 子句中。
• 每个表只能使用一个 LONG 列。不能在 LONG 列上定义约束。
• 您可能希望使用 CLOB 列，而不使用 LONG 列。

日期时间数据类型

数据类型	说明
TIMESTAMP	允许将时间存储为带有零点几秒的日期。这种数据类型有几种变体。
INTERVAL YEAR TO	允许将时间存储为年和月的间隔。用于表示两个日期时间值之间的
MONTH	差异，其中有效的取值部分只是年和月。
INTERVAL DAY TO	允许将时间存储为天、小时、分钟和秒的间隔。用于表示两个日期
SECOND	时间值之间的精确差异。

TIMESTAMP 数据类型是 DATE 数据类型的扩展。
它可以存储 DATE 数据类型的年、月和日，加上小时、分钟和秒值以及零点几秒值。

CREATE TABLE new_employees
(employee_id NUMBER,
first_name VARCHAR2(15),
last_name VARCHAR2(15),
...
start_date TIMESTAMP(7),
...);

• TIMESTAMP WITH TIME ZONE timestamp with time zone数据类型
  • TIMESTAMP WITH TIME ZONE 是 TIMESTAMP 的变体，它的值中包含了时区偏移量。
  • 时区偏移量是当地时间和 UTC 之间的差值（以小时和分钟数表示）。
• TIMESTAMP WITH LOCAL TIME 数据类型
  • TIMESTAMP WITH LOCAL TIME ZONE是TIMESTAMP 的另一个变体，它的值中包含了时区偏移量。
  • 存储在数据库中的数据会被标准化为数据库时区。时区偏移量不是作为列数据的一部分进行存储的；Oracle 将按用户的本地会话时区返回数据。

CREATE TABLE time_example
(order_date TIMESTAMP WITH LOCAL TIME ZONE)
INSERT INTO time_example VALUES('15-NOV-00 09:34:28 AM');
SELECT *
FROM time_example;

• INTERVAL YEAR TO MONTH interval year to month数据类型
  • 通过使用 YEAR 和 MONTH 日期时间字段存储一个时段。
• INTERVAL DAY TO SECOND interval day to second数据类型
  • INTERVAL DAY TO SECOND用于按照日、小时、分钟和秒存储时段。

使用 子查询语法 创建表

• 通过组合 CREATE TABLE 语句和 AS subguery 选项可以创建表并插入行。

CREATE TABLE table
	   [(column, column...)]
AS subquery;

table 是表的名称
column 是列名、默认值和完整性约束
subquery 是一条 SELECT 语句，它定义要插入到新表中的一组行

• 该表在创建时具有指定的列名，并且 SELECT 语句检索到的行将插入到该表中。
• 列定义只能包含列名和默认值。
• 如果已经给出了列的规格，则列数必须等于子查询 SELECT 列表中的列数。
• 如果没有给出列的规格，则表的列名将和子查询中的列名相同。完整性规则不会传递到新表中，只有列数据类型定义才会被传递。

CREATE TABLE dept80
  AS
     SELECT employee_id, last_name,
            salary*12 ANNSAL,
            hire_date
     FROM employees
     WHERE department_id = 80;

ALTER TABLE 语句

• 使用 ALTER TABLE alter table语句可以执行以下任务：
  • 添加新列
  • 修改现有列
  • 为新列定义默认值
  • 删除列

ALTER TABLE table
ADD (column datatype [DEFAULT expr]
    [, column datatype]...);
    
ALTER TABLE table
MODIFY (column datatype [DEFAULT expr]
       [, column datatype]...);
       
ALTER TABLE table
DROP (column);

• table 是表的名称
• ADD|MODIFY|DROP 是修改的类型
• column 是新列的名称
• datatype 是新列的数据类型和长度
• DEFAULT expr 指定新列的默认值

添加新列

ALTER TABLE dept80
ADD (job_id VARCHAR2(9));

修改列

ALTER TABLE dept80
MODIFY (last_name VARCHAR2(30));

• 可以增大数字列的宽度或精确度。
• 可以增大数字列或字符列的宽度。
• 只有在列仅包含空值或表没有行时，才可以减少列的宽度。
• 只有在列包含空值时，才可以更改数据类型。
• 只有在列包含空值或您没有更改列的大小时，才可以将 CHAR 类型的列转换为 VARCHAR2 数据类型，或将 VARCHAR2 类型的列转换为 CHAR 数据类型。
• 对列的默认值的更改只影响以后对表的插入操作。

删除列

ALTER TABLE dept80
DROP COLUMN job_id;

SET UNUSED 选项

• 可以使用 SET UNUSED 选项将一个或多个列标记为“不使用”。
• 可以使用 DROP UNUSED COLUMNS 选项删除标记为“不使用”的列。

ALTER TABLE table
SET UNUSED (column);

ALTER TABLE table
SET UNUSED COLUMN column;

ALTER TABLE table c
DROP UNUSED COLUMNS;

• DROP UNUSED COLUMINS drop unused columins将从表中删除当前标记为“不使用”的所有列。如果您要从表的“不使用”
  列中回收额外的磁盘空间，则可以使用此语句。如果表不包含“不使用”的列，该语句也不会返回错误。

ALTER TABLE dept80
SET UNUSED (last_name);

ALTER TABLE dept80
DROP UNUSED COLUMNS;

删除表

• 该表的所有数据和结构都会被删除。
• 所有待定事务处理都会被提交。
• 所有索引都会被删除。
• 无法回退 DROP TABLE 语句。

DROP TABLE dept80;

• 所有数据都会从该表中删除。
• 所有视图和同义词都会保留，但却不再有效。
• 所有待定事务处理都会被提交。
• 只有表的创建者或具有 DROP ANY TABLE 权限的用户才能删除表。

更改对象名

• 要更改表、视图、序列或同义词的名称，可以执行 RENAME 语句。

RENAME dept TO detail_dept;

舍去表的内容

• TRUNCATE TABLE 语句
  • 删除表的所有行
  • 释放该表使用的存储空间

TRUNCATE TABLE detail_dept;

向表中添加备注

• 可以使用 COMMENT 语句向表或列添加备注。

COMMENT ON TABLE employees
IS 'Employee Information';

第10章：包括约束

• 您可以使用约束执行以下任务：
  • 在对表执行插入、更新或删除行操作时，对表中的数据强制执行规则。必须满足约束，操作才能成功。
  • 防止在其它表对该表存在依赖性时将其删除
  • 为 Oracle 工具（例如 Oracle Developer ）提供规则
    数据完整性约束

约束	说明
NOT NULL	指定该列不能包含空值
UNIQUE	指明一个列或列组合中的值对于该表中的所有行来说必须是唯一的
PRIMARY KEY	唯一标识表中的每一行
FOREIGN KEY	在列和被引用表的列之间建立并实施一个外键关系
CHECK	指定条件必须为真

• 可以给约束命名，也可以由 Oracle 服务器使用 SYS_Cn 格式产生一个名称。
• 可以在以下的任一时刻创建约束：
  • 在创建表的同时创建约束
  • 在表创建之后创建约束
• 可以在列或表级别上定义约束。
• 可以在数据字典中查看约束。

定义 约束

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));

• schema 是所有者的姓名
• table 是表的名称
• DEFAULT expr 指定一个默认值，当INSERT语句中没有指定值时，则使用该默认值
• column 是列的名称
• datatype 是列的数据类型和长度
• column constraint 是作为列定义的一部分的完整性约束
• table constraint 是作为表定义的一部分的完整性约束

NOT NULL 约束

• 确保该列不允许有空值

将 NOT NULL 约束应用于表 EMPLOYEES的 LAST_NAME 和 HIRE_DATE 列

因为用户没有命名这些约束，所以Oracle服务器为它们创建了名称。

CREATE TABLE employees(
       employee_id NUMBER(6),
       last_name VARCHAR2(25) NOT NULL,
       salary NUMBER(8,2),
       commission_pct NUMBER(2,2),
       hire_date DATE
                 CONSTRAINT emp_hire_date_nn
                 NOT NULL,

UNIQUE 约束

• UNIOUE 关键字完整性约束要求列或列集合中的每个值（关键字）都是唯一的。
  • 即，表的任意两行在指定列或列集合中都没有重复的值。

示例将 UNIQUE 约束应用于 EMPLOYEES 表的 EMAIL 列。该约束的名称为EMP_EMAIL_UK。

CREATE TABLE employees(
   	   employee_id NUMBER(6),
	   last_name VARCHAR2(25) NOT NULL,
	   email VARCHAR2(25),
	   salary NUMBER(8,2),
	   commission_pct NUMBER(2,2),
	   hire_date DATE NOT NULL,
...
	   CONSTRAINT emp_email_uk UNIQUE(email));

PRIMARY KEY 约束

• PRIMARY KEY 约束将为表创建一个主键。
  • 每个表只能创建一个主键。
  • PRIMARY KEY 约束是唯一标识表中每行的一个列或列集合。
  • 此约束将强制实行列或列组合的唯一性，并且确保作为主键的一部分的列不包含空值。
• PRIMARY KEY 约束可以在列级别或表级别上定义。
  • 组合 PRIMARY KEY 是通过使用表级别定义来创建的。
  • 一个表只能有一个 PRIMARY KEY 约束，但是可以有多个 UWIQUE 约束。

示例在表 DEPARTMENTS 的 DEPARTMENT_ID 列上定义了 PRIMARY KEY 约束。该约束的名称为 DEP_TID_PK。

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));

FOREIGN KEY 约束

• FOREIGN KEY（或引用完整性约束）将指定一个列或列组合作为外键，并建立与同一表或不同表中主键或唯一关键字之间的关系。
  • 外键值必须与父表中的现有值匹配或者为 NULL。
  • 外键基于数据值，是纯逻辑指针，而不是实际指针。
• 外键是在子表中定义的，而包含被引用列的表是父表。外键是使用以下关键字组合定义的：
  • FOREIGN KEY 用于在表约束级别上定义子表中的列。
  • REFERENCES 用于标识父表中的表和列。
  • ON DELETE CASCADE 指明当删除父表中的行时，也将同时删除子表中的相关行。
  • ON DELETE SET NULL 在删除父值时将外键值转换为空值。

示例中，DEPARTMENT_ID 定义为表EMPLOYEES（相关表或子表）中的外键，它将引用表 DEPARTMENTS（被引用表或父表）的DEPARTMEINT_ID 列。

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));

CHECK 约束

• 用于定义每行都必须满足的一个条件
• 以下表达式是不允许的：
  • 对 CURRVAL、NEXTVAL、LEVEL 和 ROWNUM 伪列的引用
  • 对 SYSDATE、UID、USER 和 USERENV 函数的调用
  • 涉及到其它行中的其它值的查询

..., salary NUMBER(2)
	CONSTRAINT emp_salary_min
		CHECK (salary > 0),...

添加 约束语法

• 使用 ALTER TABLE 语句可以执行以下任务：
  • 添加或删除约束，但不修改它的结构
  • 启用或禁用约束
  • 使用 MODIFY 子句添加 NOT NULL 约束

ALTER TABLE table
ADD [CONSTRAINT constraint] type (column);

• table 是表的名称
• constraint 是约束的名称
• type 是约束类型
• column 是受约束影响的列的名称

示例将在 EMPLOYEES 表上创建一个 FOREIGN KEY 约束。该约束确保经理作为一个有效的员工存在于 EMPLOYEES 表中

ALTER TABLE employees
ADD CONSTRAINT emp_manager_fk
	FOREIGN KEY(manager_id)
	REFERENCES employees(employee_id);

删除 约束

• 要删除约束，您可以从USER_CONSTRAINTS 和 USER_CONS_COLUMNS 数据字典视图中确定约束名。
• 然后在 ALTER TABLE 语句中使用 DROP 子句。
• 如果在 DROP 子句中使用 CASCADE 选项，将同时删除所有相关的约束。

ALTER TABLE table
DROP PRIMARY KEY | UNIQUE (column) |
	 CONSTRAINT constraint [CASCADE];

• table 是表的名称
• column 是受约束影响的列的名称
• constraint 是约束的名称

从 EMPLOYEES 表中删除经理约束

ALTER TABLE employees
DROP CONSTRAINT emp_manager_fk;

删除 DEPARTMENTS 表上的 PRIMARY KEY 约束，并删除 EMPLOYEES.DEPARTMENT_ID 列上关联的 FOREIGN KEY 约束

ALTER TABLE departments
DROP PRIMARY KEY CASCADE;

禁用 约束

• 执行 ALTER TABLE 语句的 DISABLE 子句可以停用完整性约束。
• 应用 CASCADE 选项可以禁用相关的完整性约束。

ALTER TABLE table
DISABLE CONSTRAINT constraint [CASCADE];

• table 是表的名称
• constraint 是约束的名称

• 在 CREATE TABLE 语句和 ALTER TABLE 语句中都可以使用 DISABLE 子句。
• CASCADE 子句将禁用相关的完整性约束。
• 禁用唯一关键字或主键约束将会删除唯一索引。

ALTER TABLE employees
DISABLE CONSTRAINT emp_emp_id_pk CASCADE;

启用 约束

• 使用 ENABLE 子句可以激活表定义中当前禁用的完整性约束。
• 如果启用了UNIQUE 关键字或 PRIMARY KEY 约束，系统会自动创建一个 UNIQUE 或 PRIMARY KEY 索引。

ALTER TABLE table
ENABLE CONSTRAINT constraint;

• table 是表的名称
• constraint 是约束的名称

ALTER TABLE employees
ENABLE CONSTRAINT emp_emp_id_pk;

级联 约束

• CASCADE CONSTRAINTS 子句是和 DROP COLUN 子句一起使用的。
• CASCADE CONSTRAINTS 子句会删除涉及到在已删除列上定义的主键或唯一关键字的所有引用完整性约束。
• CASCADE CONSTRAINTS 子句还将删除在已删除列上定义的所有多列约束。

下面的语句说明了 CASCADE CONSTRAINTS 子句的用法。假设用下面的语句创建了表 TEST1：

CREATE TABLE test1 (
	pk NUMBER PRIMARY KEY,
	fk NUMBER,
	col1 NUMBER,
	col2 NUMBER,
	CONSTRAINT fk_constraint FOREIGN KEY (fk) REFERENCES test1,
	CONSTRAINT ck1 CHECK (pk > 0 and col1 > 0),
	CONSTRAINT ck2 CHECK (col2 > 0));

以下语句会返回错误：

ALTER TABLE test1 DROP (pk);

pk是一个父键

ALTER TABLE test1 DROP (col1);

col1 被多列约束 ck1 所引用

提交下面的语句将删除列PK、主键约束、fk_constraint外键约束和检查约束CK1：

ALTER TABLE test1
DROP (pk) CASCADE CONSTRAINTS;

如果已删除列上定义的约束所引用的所有列也已被删除，则不需要使用CASCADE CONSTRAINTS。例如，假设其它表的其它引用约束都不涉及PK列，则提交以下没有CASCADE CONSTRAINTS子句的语句是有效的：

ALTER TABLE test1
DROP (pk, fk, col1) CASCADE CONSTRAINTS;

查看 约束

查询 USER_CONSTRAINTS 表可以查看所有约束定义和名称

SELECT constraint_name, constraint_type,
	   search_condition
FROM user_constraints
WHERE table_name = 'EMPLOYEES';

在 USER_CON_SCOLUMNS 视图中查看与约束名关联的列

SELECT constraint_name, column_name
FROM user_cons_columns
WHERE table_name = 'EMPLOYEES';

第11章：创建视图

数据库对象

对象	说明
表	基本的存储单元；由行和列组成
视图	在逻辑上代表来自一个或多个表的数据的子集
序列	生成主键值
索引	提高某些查询的性能
同义词	对象的替代名称

什么是 视图

• 通过创建表的视图可以显示数据的逻辑子集或组合。
• 视图是基于表或另一个视图的逻辑表。
• 视图没有自己的数据，但它如同一个窗口，通过它可以查看或更改表中的数据。
• 视图所基于的表称为基表。
• 视图以 SELECT 语句的形式存储在数据字典中。

视图 的 作用

• 限制数据访问
• 使复杂的查询变得容易
• 提供数据独立性
• 提供相同数据的不同视图

视图 的 优点

• 由于视图能够选择性地显示表中的列，因而可以限制对数据的访问。
• 视图可以用来进行简单的查询，从而检索复杂查询的结果。
  • 例如，用户可以通过视图查询多个表中的信息，而无需了解如何编写联结语句。
• 视图可以为即席用户和应用程序提供数据独立性。可以用一个视图检索多个表中的数据。
• 视图支持用户组根据其特定标准来访问数据。

简单视图 和 复杂视图

特性	简单视图	复杂视图
表的数量	一个	一个或多个
是否包含函数	否	是
是否包含数据组	否	是
能否通过视图执行 DML 操作	能	不一定

• 简单视图有如下特点：
  • 只从一个表中导出数据
  • 不包含函数或数据组
  • 可以通过该视图执行DML操作
• 复杂视图有如下特点：
  • 从多个表中导出数据
  • 包含函数或数据组
  • 不一定能够通过该视图执行DML操作

创建 视图

• 定义视图的子查询可以包含复杂的 SELECT 语法，包括联结、分组和子查询。
• 定义视图的子查询不能包含 ORDER BY 子句。ORDER BY子句是在从视图中检索数据时指定的。
• 如果您不为用 WITH CHECK OPTION 创建的视图指定约束名，系统会以 SYS_Cn 格式指定一个默认名称。
• 可以使用 OR REPLACE 选项更改视图定义，而不必先删除再重新创建它，也不必重新授予以前授予它的对象权限。

创建视图 EMPVU80，该视图要包含部门 80 中员工的详细信息

CREATE VIEW empvu80
 	   AS SELECT employee_id, last_name, salary
		  FROM employees
		  WHERE department_id = 80;

DESCRIBE empvu80

示例创建了一个视图，其中包含部门 50 中每位员工的员工编号（EMPLOYEE_ID）、姓名（LAST_NAME）和年薪（SALARY）；员工编号、姓名和年薪的分别使用了别名 ID_NUMBER、NAME 和 ANN_SALARY。

CREATE VIEW salvu50
	   AS SELECT employee_id ID_NUMBER, last_name NAME,
	             salary*12 ANN_SALARY
   	      FROM employees
	      WHERE department_id = 50;

从 视图 中检索 数据

• 可以从视图中检索数据，就像从任何表中检索数据一样。
• 可以显示整个视图的内容，也可以只显示特定的行或列。

SELECT *
FROM salvu50;

查询 视图

SELECT *
FROM empvu80;

等效于：

SELECT employee_id,
	   last_name, salary
FROM employees
WHERE department_id=80;

• 当使用视图访问数据时，Oracle 服务器将执行下列操作：
  1. 它从 USER_VIEWS 数据字典表中检索视图定义。
  2. 它检查视图基表的访问权限。
  3. 它将视图查询转换成对基表的等同操作。也就是说，数据从基表中检索，或对基表进行更新。

修改 视图

使用 CREATE OR REPLACEVIEW子句修改 EMPVU 80 视图。为每个列名添加一个别名。

CREATE OR REPLACE VIEW empvu80
	(id_number, name, sal, department_id)
AS SELECT employee_id, first_name || ' ' || last_name,
		  salary, department_id
   FROM employees
   WHERE department_id = 80;

创建 复杂 视图

CREATE VIEW dept_sum_vu
	(name, minsal, maxsal, avgsal)
AS SELECT d.department_name, MIN(e.salary),
		  MAX(e.salary),AVG(e.salary)
   FROM employees e, departments d
   WHERE e.department_id = d.department_id
   GROUP BY d.department_name;

在 视图 上执行 DML 操作的 规则

• 可以在简单视图上执行 DML 操作。
• 如果视图包含以下内容，则不能删除行：
  • 分组函数
  • GROUP BY 子句
  • DISTINCT 关键字
  • 伪列 ROWNUM 关键字
• 如果视图包含以下内容，则不能在视图中修改数据：
  • 分组函数
  • GROUP BY 子句
  • DISTINCT 关键字
  • 伪列 ROWNUM 关键字
  • 由表达式定义的列
• 如果视图包含以下内容，则不能通过视图添加数据：
  • 分组函数
  • GROUP BY 子句
  • DISTINCT 关键字
  • 伪列 ROWNUM 关键字
  • 由表达式定义的列
  • 基表中未被视图选中的 NOT NULL 列

使用 WITH CHECK OPTION 子句

使用 WITH CHECK OPTION 子句可以确保在视图上执行的 DML 操作发生在视图的范围内。

CREATE OR REPLACE VIEW empvu20
AS SELECT *
   FROM employees
   WHERE department_id = 20
   WITH CHECK OPTION CONSTRAINT empvu20_ck ;

拒绝 DML 操作

• 通过在视图定义中添加 WITH READ ONLY 选项可以确保不能执行 DML 操作。
• 任何对视图中的行执行 DML 的尝试都会导致 Oracle 服务器错误。

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;

删除 视图

• 因为视图是基于数据库中的基表，所以删除视图不会导致丢失数据。

DROP VIEW empvu80;

内联 视图

• 内联视图是带有可以在 SQL 语句中使用的别名（或相关名称）的子查询。
• 主查询 FROM 子句中的命名子查询就是一个内联视图实例。
• 内联视图不是方案对象。

SELECT a.last_name, a.salary, a.department_id, b.maxsal
FROM employees a, (SELECT department_id, max(salary) maxsal
				   FROM employees
			   	   GROUP BY department_id) b
WHERE a.department_id = b.department_id
AND a.salary < b.maxsal;

排序 Top-N 分析

• 排序 Top-N 查询可以获得某个列的 n 个最大或 n 个最小值。例如：
  • 最畅销的前 10 种产品是什么？
  • 最滞销的前 10 种产品是什么？
• 最大值和最小值的集都被称为排序 Top-N 查询。
• 排序 Top-N 查询使用一致的具有以下元素的嵌套查询结构：
  • 子查询或内联视图，用于生成排序的数据列表。子查询或内联视图包含 ORDER BY 子句，用以确保按照所需顺序排列。对于检索最大值的结果，需要使用 DESC 参数。
  • 外部查询，用于限制最终结果集的行数。外部查询包括以下组成部分：
  • ROWNUM 伪列，为子查询返回的每一行指定一个顺序值，值从1开始。
  • WHERE 子句，指定返回 n 行。外部 WHERE 子句必须使用<或<=运算符

SELECT ROWNUM as RANK, last_name, salary
FROM (SELECT last_name,salary FROM employees
ORDER BY salary DESC)
WHERE ROWNUM <= 3;

第12章：其他数据库对象

• 序列具有以下特性：
  • 自动生成唯一编号
  • 是一个可共享的对象·通常用于创建主键值
  • 替换应用程序代码
  • 如果将序列高速缓存到内存中，则可以提高访问序列值的效率

CREATE SEQUENCE 语句

CREATE SEQUENCE sequence
	   [INCREMENT BY n]
	   [START WITH n]
	   [{MAXVALUE n | NOMAXVALUE}]
	   [{MINVALUE n | NOMINVALUE}]
	   [{CYCLE | NOCYCLE}]
	   [{CACHE n | NOCACHE}];

• sequence 是序列生成器的名称
• INCREMENTBY n 指定序列号之间的间隔，其中 n 是一个整数（如果省略此子句，则序列每次递增1）
• STARTWITH n 指定生成的第一个序列号（如果省略此子句，则序列从 1 开始）
• MAXVALUE n 指定序列可以生成的最大值
• NOMAXVALUE 指定 10²⁷ 作为递增序列的最大值，-1 作为递减序列的最大值（这是默认选项）
• MINVALUE n 指定序列的最小值
• NOMINVALUE 指定 1 作为递增序列的最小值，-10²⁶ 作为递减序列的最小值（这是默认选项）
• CYCLE | NOCYCLE 指定在达到最大值或最小值之后，序列是否继续生成值（NOCYCLE是默认选项）
• CACHE n | NOCACHE 指定 Oracle 服务器预先分配并保留在内存中的值的数量（在默认情况下，Oracle 服务器高速缓存 20 个值）

创建一个名为 DEPT_DEPTID_SEQ 的序列，将它用作表 DEPARTMENTS 的主键（不使用CYCLE选项）

CREATE SEQUENCE dept_deptid_seq
				INCREMENT BY 10
				START WITH 120
				MAXVALUE 9999
				NOCACHE
				NOCYCLE;

在 USER_SEQUENCES 数据字典表中验证序列值

SELECT sequence_name, min_value, max_value,
	   increment_by, last_number
FROM user_sequences;

LAST NUMBER 列将显示下一个可用的序列号（如果指定了 NOCACHE ）

NEXTVAL 和 CURRVAL 伪列

• NEXTVAL 会返回下一个可用的序列值。每次被引用时它都会返回一个唯一的值，即使对于不同的用户也是这样。
• CURRVAL 会获得当前序列值。
• 必须对该序列发出 NEXTVAL，然后 CURRVAL 才能包含值。

• 您可以在以下上下文中使用 NEXTVAL 和 CURRVAL ：
  • 不属于子查询一部分的 SELECT 语句的 SELECT 列表
  • INSERT 语句中的子查询的 SELECT 列表
  • INSERT 语句的 VALUES 子句
  • UPDATE 语句的 SET 子句
• 您不能在以下上下文中使用 NEXTVAL 和 CURRVAL ：
  • 视图的 SELECT 列表
  • 带有 DISTINCT 关键字的 SELECT 语句
  • 带有 GROUP BY、HAVING 或 ORDER BY 子句的 SELECT 语句
  • SELECT、DELETE 或 UPDATE 语句中的子查询
  • CREATE TABLE 或 ALTER TABLE语句中的 DEFAULT 表达式

在地点标识 2500 中插入一个名为 “Support” 的新部门

INSERT INTO departments(department_id,
			department_name, location_id)
VALUES (dept_deptid_seq.NEXTVAL,
	   'Support', 2500);

查看 DEPT_DEPTID_SEQ 序列的当前值

SELECT dept_deptid_seq.CURRVAL
FROM dual;

• 在内存中高速缓存序列值可以更快地访问那些值。
• 当发生以下情况时，序列值会出现间断：
  • 发生回退
  • 系统崩溃
  • 在其它表中使用了序列
• 如果创建序列时使用了 NOCACHE 选项，可以通过查询 USER_SEQUENCES 表来查看下一个可用的值。

修改序列

• 更改增量值、最大值、最小值、循环选项或高速缓存选项

ALTER SEQUENCE dept_deptid_seq
			   INCREMENT BY 20
			   MAXVALUE 999999
			   NOCACHE
			   NOCYCLE;

• 您必须是序列的所有者或对该序列具有 ALTER 权限。
• 修改只会影响以后生成的序列号。
• 如果要从不同的序号处重新开始，则必须删除原有的序列然后重新创建。
• 系统会执行一些验证操作。

删除序列

• 通过使用 DROP SEQUENCE 语句可以从数据字典中删除序列。
• 序列一旦删除，就不能再引用它。

DROP SEQUENCE dept_deptid_seq;

创建索引

• 索引具有以下特性：
  • 它是一个方案对象
  • Oracle 服务器使用它来加速用指针对行的检索
  • 可以通过使用快速路径访问方法来快速查找数据，从而减少磁盘 I/O 操作
  • 与它索引的表无关
  • 由 Oracle 服务器自动使用和维护
• 创建索引
  • 自动创建：如果您在表定义中定义了 PRIMARY KEY 或 UWIQUE 约束，则系统会自动创建一个唯一索引。
  • 手动创建：用户可以在列上创建非唯一的索引，以加速对行的访问。
• 提高对表 EMPLOYEES 的 LAST_NAME 列的查询访问速度。

CREATE INDEX emp_last_name_idx
ON employees(last_name);

• 需要创建索引的情况
  • 列包含较大范围的值
  • 列包含大量空值
  • 在 WHERE 子句或联结条件中频繁使用一个或多个列
  • 表相当大，但是预计多数的查询检索的行不到总行数的百分之二至百分之四
• 不用创建索引的情况
  • 表比较小
  • 在查询中不经常使用列作为条件
  • 预计多数查询检索的行要超过表中总行数的百分之二至百分之四
  • 表更新比较频繁
  • 被索引的列将作为表达式的一部分进行引
• 确认索引
  • USER INDEXES 数据字典视图包含索引的名称及其唯一性。
  • USER_IND_COLUMNS 视图包含索引名、表名和列名。

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';

基于函数的索引

• 基于函数的索引就是基于表达式的索引。
• 索引表达式是用表列、常数、SQL函数和自定义函数构建的。

CREATE INDEX upper_dept_name_idx
ON departments(UPPER(department_name));

SELECT *
FROM departments
WHERE UPPER(department_name) = 'SALES';

删除索引

• 使用 DROP INDEX 命令可以从数据字典中删除索引。
• 从数据字典中删除 UPPER LAST NAME IDX 索引。

DROP INDEX upper_last_name_idx;

• 要删除索引，您必须是该索引的所有者或者具有 DROP ANY INDEX 权限。

同义词

• 通过创建同义词（对象的另一个名称）可以简化对对象的访问。使用同义词具有以下优点：
  • 易于引用其他用户所拥有的表
  • 缩短冗长的对象名
• 该对象不能包含在程序包中。
• 私用同义词名必须与同一用户拥有的所有其它对象都不同。

创建 和 删除 同义词

• 为 DEPT_SUM_VU 视图创建一个简短的名称

CREATE SYNONYM d_sum
FOR dept_sum_vu;

• 删除同义词

DROP SYNONYM d_sum;

第13章：控制用户访问

权限

• 数据库安全性：
  • 系统安全性-数据安全性
  • 系统权限：获得访问数据库的权限
  • 对象权限：处理数据库对象的内容
  • 方案：对象的集合，例如表、视图和序列的集合

系统权限

• 可用的系统权限超过100个。
• 系统管理员对于诸如以下的任务具有高级别的系统权限：
  • 创建新用户
  • 删除用户
  • 删除表-备份表

常见的 DBA 权限

系统权限	授权的操作
CREATE USER	被授予者可以创建其他Oracle用户（DBA角色所需的权限）
DROP USER	被授予者可以删除另一位用户。
DROP ANY TABLE	被授予者可以从任何方案中删除表。
BACKUP ANY TABLE	被授予者可以用导出实用程序备份任何方案中的任何表。
SELECT ANY TABLE	被授予者可以查询任何方案中的表、视图或快照。
CREATE ANY TABLE	被授予者可以在任何方案中创建表。

创建用户

• DBA 可以使用 CREATE USER 语句创建用户。

CREATE USER user
IDENTIFIED BY password;

• user 是要创建的用户名
• password 指定该用户登录所需的口令

CREATE USER scott
IDENTIFIED BY tiger;

用户系统权限

• 一旦创建了用户，DBA 就可以将特定的系统权限授予该用户

系统权限	授权的操作
CREATE SESSION	连接到数据库
CREATE TABLE	在用户方案中创建表
CREATE SEQUENCE	在用户方案中创建序列
CREATE VIEW	在用户方案中创建视图
CREATE PROCEDURE	在用户方案中创建存储过程、函数或程序包

• DBA 可以将特定的系统权限授予用户

示例中，DBA 将创建会话、表、序列和视图的权限授予用户 Scott

GRANT create session, create table,
	  create sequence, create view
TO scott;

创建角色

• 角色是可以授予用户的相关权限的指定组
  • 此方法使得撤消和维护权限变得更容易
• 一个用户可以访问几个角色，而同一角色也可以分配给几个用户。
• 角色通常是为数据库应用程序创建的

• 首先，DBA 必须创建角色。
• 然后，DBA 可以将权限分配给角色，再将用户分配给角色。

语法:

CREATE ROLE role;

role 为要创建的角色

创建角色

CREATE ROLE manager;

将权限授予角色

GRANT create table, create view
TO manager;

将角色授予用户

GRANT manager TO DEHAAN, KOCHHAR;

更改口令

• DBA 会创建您的用户帐户，并为您初始设置一个口令
• 您可以使用 ALTER USER 语句来更改口令

语法：

ALTER USER user IDENTIFIED BY password;

user 是用户名
password 指定新口令

改 scott 用户的口令为 lion

ALTER USER scott
IDENTIFIED BY lion;

对象权限

对象权限	表	视图	序列	过程
ALTER	√		√
DELETE	√	√
EXECUTE			√
INDEX	√
INSERT	√	√
REFERENCES	√	√
SELECT	√	√	√
UPDATE	√	√

• 对象权限会根据对象的不同而变化
• 对象的所有者对其具有全部权限
• 所有者可以将属于他的对象的权限授予其他用户

语法：

GRANT object_priv [(columns)]
ON object
TO {user|role|PUBLIC}
[WITH GRANT OPTION];

object_priv 是要授予的对象权限
ALL 指定所有的对象权限
columns 指定授予权限的表或视图中的列
ON object 是授予权限的对象
TO 说明权限授给谁
PUBLIC 将对象权限授予所有用户
WITH GRANT OPTION 允许被授予者将对象权限授予其他用户和角色

授予对 EMPLOYEES 表的查询权限

GRANT select
ON employees
TO sue, rich;

将更新特定列的权限授予用户和角色

GRANT update (department_name, location_id)
ON departments
TO scott, manager;

• 要授予对某个对象的权限，则该对象必须在您自己的方案中，或者您必须被授予了 WITH GRANT OPTION 对象权限。
• 对象所有者可以将对该对象的任何对象权限授予数据库的任何其他用户或角色。
• 对象所有者会自动获得对该对象的所有对象权限。

使用 WITH GRANT OPTION 和 PUBLIC 关键字

• 使用 WITH GRANT OPTION 子句授予的权限可以由被授予者授予其他用户和角色。如果撤消了授予者的权限，则使用 WITH GRANT OPTION 子句授予的对象权限也将被撤消。
• 表的所有者可以使用 PUBLIC 关键字将访问权限授予所有用户

示例授予了用户 Scott 对您的 DEPARTMENTS 表进行查询和向表中添加行的权限。该示例还允许 Scott 将这些权限授予其他用户。

GRANT select, insert
ON departments
TO scott
WITH GRANT OPTION;

示例允许系统上的所有用户查询 Alice 的 DEPARTMEINTS 表中的数据

GRANT select
ON alice.departments
TO PUBLIC;

确认授予的权限

• 如果您试图执行未授权的操作，例如在对某个表没有 DELETE 权限的情况下，要从该表中删除一行，那么 Oracle 服务器将禁止执行此操作。
• 如果您收到 Oracle 服务器返回的 “table or view does not exist”错误消息，则表明您执行了以下操作之一：
  • 指定了不存在的表或视图
  • 尝试对您不具有相应权限的表或视图执行某个操作
• 您可以访问数据字典来查看您具有的权限。

数据字典视图

数据字典视图	说明
ROLE_SYS_PRIVS	授予角色的系统权限
ROLE_TAB_PRIVS	授予角色的表权限
USER_ROLE_PRIVS	用户可以访问的角色
USER_TAB_PRIVS_MADE	授予的对用户对象的对象权限
USER_TAB_PRIVS_RECD	授予用户的对象权限
USER_ COL_PRIVS_MADE	授予的对用户对象的列的对象权限
USER_COL_PRIVS_RECD	授予用户的对特定列的对象权限
USER_SYS_PRIVS	列出授予用户的系统权限

撤消对象权限

• 使用 REVOKE 语句可以撤消授予其他用户的权限。
• 通过 WITH GRANT OPTION 子句授予其他用户的权限也会被撤消。

语法：

REVOKE {privilege [, privilege...]|ALL}
ON object
FROM {user[, user...]|role|PUBLIC}
[CASCADE CONSTRAINTS];

CASCADE 如果要删除通过 REFERENCES 权限对该对象实行的
CONSTRAINTS 任何引用完整性约束，则此选项是必需的

作为用户 Alice，撤消授予用户 Scott 对 DEPARTMENTS 表的 SELECT 和 INSERT 权限

REVOKE select, insert
ON departments
FROM scott;

数据库链接

• 一旦创建了数据库链接，您就可以编写针对远程站点上的数据的SQL语句。
  如果设置了同义词，则可以使用该同义词编写SOL语句。
• 您不能授予其他用户对远程对象的权限

创建数据库链接，USING 子句标识远程数据库的服务名称

CREATE PUBLIC DATABASE LINK hq.acme.com
USING 'sales';

编写使用数据库链接的SQL语句

SELECT *
FROM emp@HQ.ACME.COM;