第6章数据库对象的创建与管理主要内容Oracle数据库对象概述表的创建与管理索引的创建与管理视图的创建与管理序列分区表与分区索引6.1 O
racle数据库对象概述模式的概念案例数据库模式的创建6.1.1 模式的概念模式概念是指一系列逻辑数据结构或对象的集合。 模式与
用户的关系模式与用户相对应，一个模式只能被一个数据库用户所拥有，并且模式的名称与这个用户的名称相同。通常情况下，用户所创建数据库对
象都保存在与自己同名的模式中。同一模式中数据库对象的名称必须惟一，而在不同模式中的数据库对象可以同名。默认情况下，用户引用的对象是
与自己同名模式中的对象，如果要引用其他模式中的对象，需要在该对象名之前指明对象所属模式。 模式选择与切换如果用户以NORMAL身
份登录，则进入同名模式；若以SYSDBA身份登录，则进入SYS模式；如果以SYSOPER身份登录，则进入PUBLIC模式。6.1.
2 案例数据库模式的创建创建ehr用户，并为其授权，方法为：CONNECT sys/tiger @HUMAN_RESOURCE
AS SYSDBACREATE USER ehr IDENTIFIED BY ehr DEFAULT TABLESPACE USE
RS;GRANT CONNECT,RESOURCE,CREATE VIEW TO ehr;6.2 表的创建与管理利用CREATE
TABLE语句创建表案例数据库中表的创建利用子查询创建表修改表修改约束查询表删除表6.2.1 利用CREATE TABLE语句
创建表CREATE TABLE语句表名数据类型约束表参数（1） CREATE TABLE语句 使用CREATE TABLE语句 创
建表CREATE TABLE table_name(column_name datatype [column_level_con
straint][，column_name datatype [column_level_constraint]…][，table
_level_constraint]) [parameter_list];CREATE TABLE emp ( emp_id
NUMBER(6,0) PRIMARY KEY, first_name VARCHAR2(20),
last_name VARCHAR2(25),
email VARC
HAR2(25) UNIQUE,
job_id VARCHAR2(10),
salary N
UMBER(8,2),
commis
sion_pct NUMBER(2,2),
man
ager_id NUMBER(6,0),
department_id NUMBER(4,0), CONSTRAINT c_salary CHECK(salar
y>0)) TABLESPACE USERS;（2）表名必须是合法标识符，长度为1～30 字节，并且以字母开头，可以包含字母(A～
Z，a～z)、数字（0～9），下划线（_）、美元符号（$）和井号（#）。此外，表名称不能是Oracle数据库的保留字 （3）数据类
型字符类型数字类型日期类型LOB类型二进制类型 行类型 CHAR（n）定长字符串，n的取值范围为1-2000字节VARCHAR2（
n）可变字符串，n取值范围为1-4000字节自动调整数据长度字符类型数字类型NUMBER（m，n）用于存储整数和实数。m表示数值的
总位数（精度），取值范围为1～38，默认为38；n表示小数位数，若为负数则表示把数据向小数点左边舍入，默认值为0。 日期类型DAT
E可以存储的日期范围为公元前4712年1月1日到公元4712年1月1日，由世纪、年、月、日、时、分、秒组成。可以在用户当前会话中使
用参数NLS_DATE_FORMAT指定日期和时间的格式，或者使用TO_DATE函数，将表示日期和时间的字符串按特定格式转换成日期
和时间。TIMESTAMP[(n)]：表示时间戳，是DATE 数据类型的扩展，允许存储小数形式的秒值。n表示秒的小数位数，取值范围
为1～9，默认值为6。LOB类型CLOB用于存储可变长度的字符数据，如文本文件等，最大数据量为4 GB。NCLOB用于存储可变长度
的Unicode字符数据，最大数据量为4 GB。BLOB用于存储大型的、未被结构化的可变长度的二进制数据（如二进制文件、图片文件、
音频和视频等非文本文件），最大数据量为4 GB。BFILE用于存储指向二进制格式文件的定位器，该二进制文件保存在数据库外部的操作系
统中，文件最大为4 GB。二进制类型 RAW(n)用于存储可变长度的二进制数据，n表示数据长度，取值范围为1～2000字节；LON
G RAW用于存储可变长度的二进制数据，最大存储数据量为2 GB。行类型ROWID行标识符，表中行的物理地址的伪列类型。ROWID
类型数据由18位十六进制数构成，包括对象编号、文件编号、数据块编号和块内行号。UROWID行标识符，用于表示索引化表中行的逻辑地址
。（4）约束约束作用是在表中定义的用于维护数据库完整性的一些规则。通过对表中列定义约束，可以防止在执行DML操作时，将不符合要求的
数据插入到表中。约束类型PRIMARY KEYUNIQUECHECKFOREIGN KEY NULL/NOT NULL PRIMA
RY KEY特点定义主键，起惟一标识作用，其值不能为NULL，也不能重复；一个表中只能定义一个主键约束；建立主键约束的同时，在该列
上建立一个惟一性索引，可以为它指定存储位置和存储参数；主键约束可以是列级约束，也可以是表级约束。UNIQUE特点定义为惟一性约束的
某一列或多个列的组合的取值必须惟一；如果某一列或多个列仅定义惟一性约束，而没有定义非空约束，则该约束列可以包含多个空值；Oracl
e自动在惟一性约束列上建立一个惟一性索引，可以为它指定存储位置和存储参数；惟一性约束可以是列级约束，也可以是表级约束。 在一个基本
表中只能定义一个PRIMARY KEY约束，但可定义多个UNIQUE约束；对于指定为PRIMARY KEY的一个列或多个列的组合，
其中任何一个列都不能出现空值，而对于UNIQUE所约束的唯一键，则允许为空。不能为同一个列或一组列既定义UNIQUE约束，又定义P
RIMARY KEY约束。PRIMARY KEY与UNIQUE比较CHECK 特点检查约束用来限制列值所允许的取值范围，其表达式中
必须引用相应列，并且表达式的计算结果必须是一个布尔值；约束表达式中不能包含子查询，也不能包含SYSDATE、USER等SQL函数，
和ROWID、ROWNUM等伪列；一个列可以定义多个检查约束；检查约束可以是列级约束，也可以是表级约束。 概念FOREIGN KE
Y约束指定某一个列或一组列作为外部键，其中，包含外部键的表称为从表，包含外部键所引用的主键或唯一键的表称主表。系统保证从表在外部键
上的取值要么是主表中某一个主键值或唯一键值，要么取空值。以此保证两个表之间的连接，确保了实体的参照完整性。FOREIGN KEY特
点定义外键约束的列的取值要么是主表参照列的值，要么为空；外键列只能参照于主表中的主键约束列或惟一性约束列；可以在一列或多列组合上定
义外键约束；外键约束可以是列级约束，也可以是表级约束。NULL/NOT NULL 特点在同一个表中可以定义多个NOT NULL约束
；只能是列级约束。定义约束列约束对某一个特定列的约束，包含在列定义中，直接跟在该列的其他定义之后，用空格分隔,不必指定列名；表约束
与列定义相互独立，不包括在列定义中。通常用于对多个列一起进行约束，与列定义用’,’分隔。定义表约束时必须指出要约束的那些列的名称。
定义列级约束的语法为：[CONSTRAINT constraint_name] constraint_type [conditio
in]; 定义表级约束的语法为：[CONSTRAINT constraint_name]constraint_type([colu
mn1_name,column2_name,…]|[condition]);注意Oracle约束通过名称进行标识。在定义时可以通过
CONSTRAINT关键字为约束命名。如果用户没有为约束命名，Oracle将自动为约束命名。 创建一个student表。CREAT
E TABLE student( sno NUMBER(6) CONSTRAINT S_PK
PRIMARY KEY,sname
VARCHAR2(10) NOT NULL,sex CHAR(2) CONSTRAINT S_CK1
CHECK(sex in(''M'', ''F'')),sage NUMBER(6,2),C
ONSTRAINT S_CK2 CHECK( sage between
18 and 60));创建一个course表，同时为主键约束列上的唯一性索引设置存储位置和存储参数，语句为 CREATE TA
BLE course( cno NUMBER(6) PRIMARY KEY, cname CHAR(20) UNIQUE
USING INDEX TABLESPACE indx STORAGE( INITIAL 64K NEXT 64
K)); 创建一个SC表，语句为CREATE TABLE SC( sno NUMBER(6) REFERENCES stude
nt(sno), cno NUMBER(6) REFERENCES course(cno), grade NUMBER(5,2
), CONSTRAINT SC_PK PRIMARY KEY(sno, cno) ); （5）表参数TABLESPACETA
BLESPACE子句用于指定表存储的表空间。 STORAGE STORAGE子句用于设置表的存储参数。若不指定，则继承表空间的存储
参数设置。 NITIAL NEXTPCTINCREASEMINEXTENTSMAXEXTENTSBUFFER_POOL (KEEP
、RECYCLE、DEFAULT) STORAGE参数设置需注意如果表空间管理方式为EXTENT MANAGEMENT LOCAL
AUTOALLOCATE，则在STORAGE中只能指定INITIAL，NEXT和MINEXTENTS这3个参数；如果表空间管理方
式为EXTENT MANAGEMENT LOCAL UNIFORM，则不能指定任何STORAGE子句；如果表空间管理方式为EXTE
NT MANAGEMENT DICTIONARY，则在STORAG中可以设置任何参数。数据块管理参数 PCTFREE：用于指定数据
块中必须保留的最小空闲空间。PCTUSED：用于指定当数据块空闲空间达到PCTFREE参数的限制后，数据块能够被再次使用前，已占用
的存储空间必须低于的比例。INITRANS：用于指定能够并发访问同一个数据块的事务的数量。MAXTRANS：用于指定能够并发访问同
一个数据块的事务的最大数量。LOGGING与NOLOGGING子句默认为NOLOGGING，即表的创建操作不会记录到重做日志文件中
，尤其适合通过查询创建表的情况。使用LOGGING子句，表的创建操作（包括通过查询创建表时的插入记录操作）都将记录到重做日志文件中
。PARALLEL、NOPARALLELCACHE、NOCACHEMONITORING、NOMONITORING6.2.2 案例
数据库中表的创建CREATE TABLE regions( region_id NUMBER PRIMARY KEY, reg
ion_name VARCHAR2(25))TABLESPACE USERS;CREATE TABLE countries
( country_id CHAR(2) PRIMARY KEY, country_name VARCHAR2(
40), region_id NUMBER REFERENCES
regions(region_id) ) TABLESPACE USERS;CRE
ATE TABLE locations ( location_id NUMBER(4) PRIMARY KEY,
street_address VARCHAR2(40), postal_code VARCHAR2(12),
city VARCHAR2(30) NOT NULL, state_province VARCHAR2(25),
country_id CHAR(2) REFERENCES
countries(country_id) )TABLESPACE USERS;CREATE TAB
LE departments ( department_id NUMBER(4) PRIMARY KEY, depa
rtment_name VARCHAR2(30) NOT NULL, manager_id NUMBER(6),
location_id NUMBER(4) REFERENCES
locations (location_id) ) TABLESPACE USERS;CREATE
TABLE jobs ( job_id VARCHAR2(10) PRIMARY KEY, job_title V
ARCHAR2(35) NOT NULL, min_salary NUMBER(6), max_salar
y NUMBER(6) ) TABLESPACE USERS;CREATE TABLE employees (
employee_id NUMBER(6) PRIMARY KEY, first_name VARCHAR2(20),
last_name VARCHAR2(25) NOT NULL, email VARCHAR2(25)
NOT NULL UNIQUE, phone_number VARCHAR2(20), hire_da
te DATE NOT NULL, job_id VARCHAR2(10) NOT NULL
REFERENCES jobs (job_id), salary NUMBER(8,2) CHECK (sal
ary > 0), commission_pct NUMBER(2,2), manager_id NUMB
ER(6), department_id NUMBER(4) REFERENCES
departments(department_id) ) TABLESPACE USERS;CRE
ATE TABLE job_history ( employee_id NUMBER(6) NOT NULL
REFERENCES employees(employee_id), start_date DATE
NOT NULL, end_date DATE NOT NULL, job_id VARCHAR2(10)
NOT NULL REFERENCES jobs(job_id), dep
artment_id NUMBER(4) REFERENCES departments(
department_id), CONSTRAINT jhist_date_interval
CHECK (end_date > start_date), CONSTRAINT jhist_emp_id_
st_date_pk PRIMARY KEY (employee_id, start_date)
) TABLESPACE USERS;CREATE TABLE sal_grades ( grade NUMBE
R PRIMARY KEY, min_salary NUMBER(8,2), max_salary NUM
BER(8,2) ) TABLESPACE USERS;CREATE TABLE users( user_id
NUMBER(2)PRIMARY KEY, user_name CHAR(20), password VARCHAR
2(20) NOT NULL ) TABLESPACE USERS;6.2.3 利用子查询创建表语法CREATE
TABLE table_name(column_name [column_level_constraint][，column_na
me [column_level_constraint]…][，table_level_constraint]) [paramet
er_list]AS subquery; 利用子查询创建表需要注意以下事项。通过该方法创建表时，可以修改表中列的名称，但是不
能修改列的数据类型和长度。源表中的约束条件和列的默认值都不会复制到新表中子查询中不能包含LOB类型和LONG类型列。当子查询条件为
真时，新表中包含查询到的数据；当子查询条件为假时，则创建一个空表。创建一个表，保存工资高于15000元的员工的员工号、员工姓名和部
门号。 CREATE TABLE sub_emp1l(empno,fname,lname,deptno) AS SELEC
T employee_id,first_name, last_name,department_id FROM employ
ees WHERE salary>15000;创建一个包含员工号、员工EMAIL、员工工资及部门号信息的空表，其中员工号为主
键、EMAIL唯一。CREATE TABLE sub_emp2( employee_id PRIMARY KEY, ema
il UNIQUE, salary,department_id) AS SELECT emp_id,email,salar
y,department_id FROM emp WHERE 1=2;6.2.4 修改表基本语法列的添加、删除、修改表参数修
改表结构重组表重命名等为表和列添加注释 （１）修改表基本语法ALTER TABLE <表名>ADD <新列名><数据类型>[
<完整性约束定义]MODIFY <列名><数据类型> RENAME COLUMN oldname TO newnameSET
UNUSED COLUMN column //single columnSET UNUSED COLUMNS（column1，c
olumn2…）DROP COLUMN //single columnDROP < co
l1，col2… > //multi columnDROP UNUSED COLUMNS（２）列
的添加、删除、修改 添加列 语法ALTER TABLE table_name ADD(new_column_name dataty
pe[NOT NULL][DEFAULT value]); 示例ALTER TABLE employee ADD(phone VA
RCHAR2(11),hiredate DATE DEFAULT SYSDATE NOT NULL);修改列类型 语法ALTER
TABLE table_name MODIFY column_name new_datatype; 修改表中列类型时，必须满足下列
条件：可以增大字符类型列的长度和数值类型列的精度；如果字符类型列、数值类型列中数据满足新的长度、精度，则可以缩小类型的长度、精度；
如果不改变字符串的长度，可以将VARCHAR2类型和CAHR类型转换；如果更改数据类型为另一种非同系列类型，则列中数据必须为NUL
L。 示例ALTER TABLE employee MODIFY ename CHAR(20);ALTER TABLE emplo
yee MODIFY phone NUMBER; 修改列名 语法ALTER TABLE table_name RENAME COL
UMN oldname TO newname;示例 ALTER TABLE employee RENAME COLUMN enam
e TO employee_name； 删除列 直接删除列语法ALTER TABLE table_name DROP [COLUM
N column_name]|[(column1_name,column2_name,…)] [CASCADE CONSTRAIN
TS]; 直接删除列示例ALTER TABLE sc DROP COLUMN sno CASCADE CONSTRAINTS;AL
TER TABLE employee DROP (phone,hiredate);将列标记为UNUSED，然后进行删除。ALTER
TABLE table_name SET UNUSED [COLUMN column_name]| [(column1_name
,column2_name,…)][CASCADE CONSTRAINTS]; 示例ALTER TABLE player SET
UNUSED COLUMN sage;ALTER TABLE player SET UNUSED (sname,resume);A
LTER TABLE player DROP UNUSED COLUMNS; 6.2.5 修改约束修改约束语法为：ALTER T
ABLE table_name [ADD [CONSTRAINT constraint_name] constraint_ty
pe(column1,…)[condition]]| [MODIFY column [NOT NULL]|[NULL]]| [DR
OP [CONSTRAINT constraint_name]| [PRIMARY KEY]|[UNIQUE(column)]]创
建一个player表CREATE TABLE player( ID NUMBER(6), sno
NUMBER(6), sname VARCHAR2(10), sage NUMBER(6,2),
resume VARCHAR2(1000) ); 添加主键约束ALTER TABLE player ADD CONS
TRAINT P_PK PRIMARY KEY(ID);添加惟一性约束ALTER TABLE player ADD CONSTRA
INT P_UK UNIQUE(sname);添加检查约束ALTER TABLE player ADD CONSTRAINT P_
CK CHECK(sage BETWEEN 20 AND 30);添加外键约束ALTER TABLE player ADD
CONSTRAINT P_FK FOREIGN KEY(sno)REFERENCES student(sno)
ON DELETE CASCADE;添加空/非空约束为表列添加空/非空约束时必须使用MODIFY子句代替ADD子句ALTER T
ABLE player MODIFY resume NOT NULL;ALTER TABLE player MODIFY resu
me NULL;删除约束使用ALTER TABLE…DROP语句删除已经定义的约束。可以通过直接指定约束的名称来删除约束，或指定
约束的内容来删除约束。删除指定内容的约束ALTER TABLE player DROP UNIQUE(sname);删除指定名称的
约束ALTER TABLE player DROP CONSTRAINT P_CK; 6.2.6 查询表DBA_TABLESA
LL_TABLESUSER_TABLESDBA_TAB_COLUMNSALL_TAB_COLUMNSUSER_TAB_COLUMN
S查询当前用户拥有的所有表的信息。SELECT table_name,tablespace_name,status,logging
FROM user_tables;查询约束ALL_CONSTRAINTSUSER_CONSTRAINTSDBA_CONSTRAI
NTSALL_CONS_COLUMNSUSER_CONS_COLUMNSDAB_CONS_COLUMNS查询employees表中
所有约束的名称与类型。SELECT constraint_name,constraint_type,status FROM use
r_constraints WHERE table_name=''EMPLOYEES'';语法DROP TABLE table_nam
e[CASCADE CONSTRAINTS][PURGE] 删除一个表同时，Oracle将执行下列操作：删除该表中所有记录；从数据
字典中删除该表定义；删除与该表相关的所有索引和触发器；回收为该表分配的存储空间；依赖于该表的数据库对象处于INVALID状态。6.
2.7 删除表注意在Oracle 11g中，使用DROP TABLE语句删除一个表时，并不立即回收该表的空间，而只是将表及其关联
对象的信息写入一个称为“回收站”（RECYCLEBIN）的逻辑容器中，从而可以实现闪回删除表操作。如果要回收该表空间，可以采用清空
“回收站”（PURGE RECYCLEBIN）或在DROP TABLE语句中使用PURGE语句。6.3 索引的创建与管理索引概述
使用CREATE INDEX语句创建索引案例数据库中索引的创建删除索引查询索引6.3.1 索引概述索引是为了加速对表中元组的检索
而创建的一种分散存储结构；是对表而建立的，由除存放表的数据页面以外的索引页面组成，独立于被索引的表；通过使用索引加速行的检索，但减
慢更新的速度；快速定位数据，减少磁盘 I/O；Oracle自动使用、维护索引索引类型B-树索引位图索引函数索引唯一性索引与非唯一性
索引单列索引与复合索引索引使用原则导入数据后再创建索引 在适当的表和字段上创建索引 合理设置复合索引中列的顺序限制表中索引的数目选
择存储索引的表空间6.3.2 使用CREATE INDEX语句创建索引创建索引修改索引监视索引 删除索引索引的查询语法CREAT
E [UNIQUE]|[BITMAP] INDEX index_nameON table_name([column_name[AS
C|DESC],…]|[expression]) [REVERSE][parameter_list]；说明UNIQUE表示建立惟一
性索引；BITMAP表示建立位图索引；ASC/DESC用于指定索引值的排列顺序，ASC表示按升序排序，DESC表示按降序排序，缺省
值为ASC；REVERSE表示建立反键索引；parameter_list用于指定索引的存放位置、存储空间分配和数据块参数设置。6.
3.2 使用CREATE INDEX语句创建索引在emp表的last_name列上创建一个非唯一性索引。CREATE INDEX
emp_lname_indx ON emp (last_name) TABLESPACE indx;在emp表的email列上创
建一个唯一性索引。CREATE UNIQUE INDEX emp_email_indx ON emp(email) TABLESP
ACE indx;在emp表的job_id列上创建一个位图索引。CREATE BITMAP INDEX emp_job_indx
ON emp(job_id) TABLESPACE indx;基于emp表的first_name列创建一个函数索引。CREATE
INDEX emp_fname_indx ON emp (UPPER(first_name)) TABLESPACE indx;6
.3.3 案例数据库中索引的创建（1）在employees 表的department_id列上创建一个名为emp_departm
ent_ix的平衡树索引。（2）在employees 表的job_id列上创建一个名为emp_job_ix的平衡树索引。（3）在e
mployees 表的manager_id列上创建一个名为emp_manager_ix的平衡树索引。（4）在employees 表
的last_name, first_name列上创建一个名为emp_name_ix的复合索引。（5）在departments 表的
location_id列上创建一个名为dept_location_ix的平衡树索引。（6）在job_history 表的job_i
d列上创建一个名为jhist_job_ix 的平衡树索引。（7）在job_history表的empoyee_id列上创建一个名为j
hist_emp_ix的平衡树索引。（8）在job_history表的department_id列上创建一个名为jhist_dep
t_ix的平衡树索引。（9）在locations表的city列上创建一个名为loc_city_ix的平衡树索引。（10）在loca
tions表的state_province列上创建一个名为loc_state_province_ix的平衡树索引。（11）在loc
ations表的country_id列上创建一个名为loc_country_ix的平衡树索引。CREATE INDEX emp_d
epartment_ix ON employees (department_id) TABLESPACE indx;CREATE
INDEX emp_job_ix ON employees(job_id) TABLESPACE indx;CREATE INDE
X emp_manager_ix ON employees (manager_id) TABLESPACE indx;CREATE
INDEX emp_name_ix ON employees (last_name, first_name) TABLESPAC
E indx;CREATE INDEX dept_location_ix ON departments (location_id)
TABLESPACE indx;CREATE INDEX jhist_job_ix ON job_history (job_id
) TABLESPACE indx;CREATE INDEX jhist_emp_ix ON job_history (emplo
yee_id) TABLESPACE indx;CREATE INDEX jhist_dept_ix ON job_history
(department_id) TABLESPACE indx;CREATE INDEX loc_city_ix ON loca
tions (city) TABLESPACE indx;CREATE INDEX loc_state_province_ix O
N locations (state_province) TABLESPACE indx;CREATE INDEX loc_cou
ntry_ix ON locations (country_id) TABLESPACE indx;6.3.4 删除索引语法DR
OP INDEX index_name;在下面几种情况下，可以考虑删除索引 该索引不再使用。通过一段时间监视，发现几乎没有查询或只
有极少数查询会使用该索引。由于索引中包含损坏的数据块或包含过多的存储碎片等，需要删除该索引，然后重建索引。由于移动了表数据而导致索
引失效 6.3.5 查询索引DBA_INDEXESALL_INDEXESUSER_INDEXESDBA_ IND_COLUMNS
ALL_ IND_COLUMNSUSER_ IND_COLUMNS查询employees表中所有索引的名称与类型。SELECT i
ndex_name,index_typeFROM dba_indexes WHERE table_name=''EMPLOYEES''
;6.4 视图的创建与管理视图概述使用CREATE VIEW语句创建视图案例数据库中视图的创建视图操作的限制修改视图定义删除视图
查询视图信息6.4.1 视图概述视图是从一个或多个表或视图中提取出来的数据的一种逻辑表现形式。使用视图具有下列优点：可以限制对基
表数据的访问，只允许用户通过视图看到表中的一部分数据。可以使复杂的查询简单化。提供了数据的透明性，用户并不知道数据来自于何处。提供
了对相同数据的不同显示。根据视图定义时复杂程度的不同，视图分为简单视图和复杂视图两类。在简单视图定义中，数据来源于一个基表，不包含
函数、分组等，可以直接进行DML操作。在复杂视图定义中，数据来源于一个或多个基表，可以包含连接、函数、分组、伪列、表达式等元素，能
否直接进行DML操作取决于视图的具体定义。6.4.2 使用CREATE VIEW语句创建视图语法为：CREATE [OR REP
LACE] [FORCE| NOFORCE] view[(alias[, alias]…)]AS Subquery[WITH CH
ECK OPTION [CONSTRAINT constraint]][WITH READ ONLY [CONSTRAINT co
nstraint]];其中FORCE：不管基表是否存在都创建视图。NOFORCE：仅当基表存在时才创建视图（默认）。WITH CH
ECK OPTION：指明对视图操作时，必须满足子查询中的约束条件。WITH READ ONLY：指明该视图为只读视图，不能修改。
创建一个视图，包含员工号、员工名、工资和部门号等员工基本信息。SQL>CREATE VIEW emp_base_info_view
(empno,fename,lname,sal,deptno)ASSELECT employee_id,first_name,l
ast_name,salary,department_id FROM employees;创建一个视图，包含各个部门中不同职位的员
工人数、平均工资。CREATE VIEW dept_job_stat_viewASSELECT department_id,job
_id,count() num,avg(salary) avgsalFROM employees GROUP BY depart
ment_id,job_id;创建一个视图，包含工资大于2000的员工的员工号、员工名及员工的年工资。CREATE VIEW em
p_sal_view ASSELECT employee_id,first_name,last_name,salary12 ye
ar_salary FROM employees WHERE salary>2000 WITH CHECK OPTION;创建一
个包含员工号、员工名、员工工资以及员工所在部门名的只读视图。CREATE VIEW emp_dept_view ASSELECT
employee_id,first_name,last_name,salary,department_name FROM empl
oyees e,departments d WHERE e.department_id=d.department_id WITH
READ ONLY;6.4.3 案例数据库中视图的创建创建一个名为“EMP_DETAILS_VIEW”的视图，用于员工信息综合查
询，包括员工编号、员工名、工资、奖金、职位编号、职位名称、部门编号、部门名称、部门所在地信息、国家信息、区域信息等。创建一个名为“
DEPT_STAT_VIEW”的视图，包含部门号、部门人数、部门平均工资、部门最高工资、部门最低工资以及部门工资总和。6.4.4
视图操作的限制如果视图定义包括下列任何一项，则不可直接对视图进行插入、删除和修改等操作，需要通过触发器来实现：：集合操作符（UN
ION，UNION ALL，MINUS，INTERSECT）；聚集函数（SUM，AVG等）；GROUP BY，CONNECT BY
或START WITH子句DISTINCT操作符；由表达式定义的列；伪列ROWNUM；（部分）连接操作。6.4.5 修改视图定义
使用CREATE OR REPLACE VIEW语句重建视图，其实质是删除原来视图并重建该视图，但是会保留该视图上授予的各种权限。
修改视图dept_job_stat_view，增加各个部门中不同职位的工资总和。CREATE OR REPLACE VIEW de
pt_job_stat_viewASSELECT department_id,job_id,count() num,avg(sa
lary) avgsal,sum(salary) total FROM employees GROUP BY department
_id,job_id;6.4.6 删除视图如果视图不再使用了，可以使用DROP VIEW语句删除视图。视图的删除对基表及其数据没
有任何影响。删除视图dept_job_stat_view。DROP VIEW dept_job_stat_view;6.4.7
查询视图信息数据字典DBA_VIEWSALL_VIEWSUSER_VIEWS查询当前用户所有视图名称及视图定义信息。SELECT
view_name,text FROM user_views;6.5 序列序列的概念使用CREATE SEQUENCE语句创建序列
案例数据库中序列的创建序列的使用修改序列查看序列信息删除序列6.5.1 序列的概念序列是一种用于产生唯一数字序号的数据库对象。序
列具有下列特点：可以为表中的记录自动产生唯一序号。由用户创建并且可以被多个用户共享。典型应用是生成主键值，用于标识记录的唯一性。允
许同时生成多个序列号，而每一个序列号是唯一的。使用缓存可以加速序列的访问速度。6.5.2 使用CREATE SEQUENCE语句
创建序列CREATE SEQUENCE sequence[START WITH integer][INCREMENT BY int
eger][MAXVALUE integer|NOMAXVALUE][MINVALUE integer|NOMINVALUE][C
YCLE|NOCYCLE][CACHE integer|NOCACHE];START WITH：设置序列初始值，默认值为1。INC
REMENT BY：设置相邻两个元素之间的差值，即步长，默认值为1。MAXVALUE：设置序列最大值。NOMAXVALUE：默认情
况下，递增序列的最大值为1028-1，递减序列的最大值为-1。MINVALUE：设置序列最小值。NOMINVALUE：默认情况下，
递增序列的最小值为1， 递减序列的最小值为-(1027 -1)。CYCLE：当序列达到其最大值或最小值后，开始新的循环。NOCYC
LE：当序列达到其最大值或最小值后，序列不再生成值。默认选项。CACHE：设置Oracle服务器预先分配并保留在内存中的序列值的个
数，默认为20。NOCACHE：不缓存序列值创建一个序列，用于产生学生号码。CREATE SEQUENCE student_seq
START WITH 1000 INCREMENT BY 2 MAXVALUE 1000000 CACHE 10
;6.5.3 案例数据库中序列的创建创建一个名为“EMPLOYEES_SEQ”的序列，用于产生员工编号，起始值为100，步长为1
，不缓存，不循环。创建一个名为“DEPARTMENTS_SEQ”的序列，用于产生部门编号，起始值为10，步长为10，最大值为999
0，不缓存，不循环。创建一个名为“LOCATIONS_SEQ”的序列，用于产生位置编号，起始值为1000，步长为100，最大值为9
990，不缓存，不循环。CREATE SEQUENCE employees_seq START WITH 100 INCRE
MENT BY 1 NOCACHE NOCYCLE;CREATE SEQUENCE departments_seq
START WITH 10 INCREMENT BY 10 MAXVALUE 9990 NOCACHE NOCYCLE
;CREATE SEQUENCE locations_seq START WITH 1000 INCREMENT BY 10
0 MAXVALUE 9900 NOCACHE NOCYCLE;6.5.4 序列的使用序列具有CURRVAL和NEXTVA
L两个伪列。CURRVAL返回序列的当前值，NEXTVAL在序列中增加新值并返回此值。可用通过sequence_name.CURR
VAL和sequence_name.NEXTVAL形式来应用序列。在下列语句中可使用序列的NEXTVAL和CURRVAL伪列：SE
LECT语句的目标列中。INSERT语句的子查询的目标列中。INSERT语句的VALUES子句中。UPDATE语句的SET子句中。
在下列语句中不允许使用序列的NEXTVAL和CURRVAL伪列：对视图查询的SELECT目标列中。使用了DISTINCT命令的SE
LECT语句中。SELECT语句中使用了GROUP BY、HAVING或ORDER BY子句时。在SELECT、DELETE或UP
DATE语句的子查询中。在CREATE TABLE或ALTER TABLE语句中的默认值表达式中。利用student_seq序列产
生学号并插入到student表中。CREATE TABLE students(sno NUMBER PRIMARY KEY,sna
me CHAR(20));INSERT INTO students(sno,sname) VALUES(student_seq.n
extval,''Joan'');INSERT INTO students(sno,sname) VALUES(student_seq
.nextval,''Mary'');SELECT students_seq.currval FROM dual;6.5.5 修改序
列序列创建完成后，可以使用ALTER SEQUENCE语句修改序列。除了不能修改序列的START WITH参数外，可以对序列其他参
数进行修改。如果要修改MAXVALUE参数，需要保证修改后的最大值大于序列的当前值（CURRVAL）。序列的修改只影响以后生成的序
列值。6.5.6 查看序列信息DBA_SEQUENCESALL_SEQUENCESUSER_SEQUENCES6.5.7 删除
序列可以使用DROP SEQUENCE语句删除序列删除序列时，系统将序列的定义从数据字典中删除，对于之前序列的应用没有任何影响。6
.6 分区表与分区索引分区的概念分区方法创建分区表创建分区索引6.6.1 分区的概念所谓分区就是将一个巨型表分成若干个独立的组
成部分进行存储和管理，每一个相对小的、可以独立管理的部分，称为原来表的分区。表分区后，可以对表的分区进行独立的存取和控制。每个分区
都具有相同的逻辑属性，但物理属性可以不同。对巨型表进行分区具有下列优点：提高数据的安全性，一个分区的损坏不影响其他分区中数据的正常
使用。将表的各个分区存储在不同磁盘上，提高数据的并行操作能力。简化数据的管理，可以将某些分区设置为不可用状态，某些分区设置为可用状
态，某些分区设置为只读状态，某些分区设置为读写状态。操作的透明性，对表进行分区并不影响对数据进行操作的SQL语句。6.6.2 分
区方法范围分区：根据分区列值的范围对表进行分区，每条记录根据其分区列值所在的范围决定存储到哪个分区中。范围分区是最常用的分区方法，
特别适合根据日期进行分区的情况。列表分区：如果分区列的值不能划分范围（非数值类型或日期类型），同时分区列的取值是一个包含少数值的集
合，可以采用列表分区，将特定分区列值的记录保存到特定分区中。散列分区：又称HASH分区，是采用基于分区列值的HASH算法，将数据均
匀分布到指定的分区中。一个记录到底分布到哪个分区是由HASH函数决定的。复合分区：结合两种基本分区方法，先采用一个分区方法对表或索
引进行分区，然后再采用另一个分区方法将分区再分成若干个子分区。每个分区的子分区都是数据的一个逻辑子集。复合分区包括范围-范围复合分
区、范围-散列复合分区、范围-列表复合分区、列表-范围复合分区、列表-散列复合分区、列表-列表复合分区等多种分区方法。6.6.3
创建分区表创建范围分区表创建列表分区表创建散列分区表创建复合分区表（1）创建范围分区表CREATE TABLE table(…)
PARTITION BY RANGE (column1[,column2,…])( PARTITION partition1 VA
LUES LESS THAN(literal|MAXVALUE) [TABLESPACE tablespace][,PARTITI
ON partition2 VALUES LESS THAN(literal|MAXVALUE) [TABLESPACE tabl
espace],…])…PARTITION BY RANGE：指明采用范围分区方法。column：分区列，可以是单列分区，也可以是
多列分区。PARTITION partition1 ：设置分区名称。VALUES LESS THAN：设置分区列值的上界。TABL
ESPACE：设置分区对应的表空间。示例创建一个分区表，将学生信息根据其出生日期进行分区，将1980年1月1日前出生的学生信息保存
在ORCLTBS1表空间中，将1980年1月1日到1990年1月1日出生的学生信息保存在ORCLTBS2表空间中，将其他学生信息保
存在ORCLTBS3表空间中。CREATE TABLE student_range( sno NUMBER(6) PRIMA
RY KEY, sname VARCHAR2(10), sage int, birthday DATE)
PARTITION BY RANGE(birthday)?( PARTITION p1 VALUES LESS THAN
(TO_DATE(''1980-1-1'', ''YYYY-MM-DD'')) TABLESPACE OR
CLTBS1, PARTITION p2 VALUES LESS THAN (TO_DATE(''1990-1-1''
, ''YYYY-MM-DD'')) TABLESPACE ORCLTBS2, PARTITION p3 VAL
UES LESS THAN(MAXVALUE) TABLESPACE ORCLTBS3 ?);（2）创建列表分区表C
REATE TABLE table(…)PARTITION BY LIST(column)( PARTITION partitio
n1 VALUES([literal|NULL]|[DEFAULT]) [TABLESPACE
tablespace][,PARTITION partition2 VALUES([literal|NULL
]|[DEFAULT]) [TABLESPACE tablespace],…])…示例创建一个分区表，将学生信息按
性别不同进行分区，男学生信息保存在表空间ORCLTBS1中，而女学生信息保存在ORCLTBS2中。 CREATE TABLE st
udent_list(? sno NUMBER(6) PRIMARY KEY,? sname VARCHAR2(10),?
sex CHAR(2) CHECK(sex in (''M'', ''F'')))PARTITION BY LIST(sex)(
PARTITION student_male VALUES(''M'') TABLESPACE ORCLTBS1, P
ARTITION student_female VALUES(''F'') TABLESPACE ORCLTBS2);（3）创建散列分
区表CREATE TABLE table(…)PARTITION BY HASH (column1[,column2,…]) [(
PARTITION partition [TABLESPACE tablespace][,…])]|[PARTITIO
NS hash_partition_quantity STORE IN (tablespace1[,…])]…示例创建
一个分区表，根据学号将学生信息均匀分布到ORCLTBS1和ORCLTBS2两个表空间中。CREATE TABLE student_
hash ( sno NUMBER(6) PRIMARY KEY, sname VARCHAR2(10))PART
ITION BY HASH(sno)( PARTITION p1 TABLESPACE ORCLTBS1,? PARTITI
ON p2 TABLESPACE ORCLTBS2); CREATE TABLE student_hash2 ( sno
NUMBER(6) PRIMARY KEY, sname VARCHAR2(10) )PARTITION BY HASH(
sno)PARTITIONS 2 STORE IN(ORCLTBS1,ORCLTBS2); （4）创建复合分区表复合分区包括：范围
-列表复合分区范围-散列复合分区。 创建复合分区时需要指定分区方法（PARTITION BY RANGE）分区列子分区方法（SUB
PARTITION BY HASH， SUBPARTITION BY LIST）子分区列每
个分区中子分区数量或子分区的描述。 范围-列表复合分区范围-列表复合分区先对表进行范围分区，然后再对每个分区进行列表分区，即在一个
范围分区中创建多个列表子分区。范围-列表复合分区示例创建一个范围-列表复合分区表，将1980年1月1日前出生的男、女学生信息分别保
存在ORCLTBS1和ORCLTBS2表空间中，1980年1月1日到1990年1月1日出生的男、女学生信息分别保存在ORCLTBS
3和ORCLTBS4表空间中，其他学生信息保存在ORCLTBS5表空间中。 CREATE TABLE student_range_
list( sno NUMBER(6) PRIMARY KEY, sname VARCHAR2(10), sex CHAR(2
) CHECK(sex IN (''M'',''F'')), sage NUMBER(4), birthday DATE ) PARTI
TION BY RANGE(birthday) SUBPARTITION BY LIST(sex) (PARTITION p1 V
ALUES LESS THAN(TO_DATE(''1980-1-1'', ''YYYY-MM-DD'')) (SUBPA
RTITION p1_sub1 VALUES(''M'') TABLESPACE ORCLTBS1, SUBPART
ITION p1_sub2 VALUES(''F'') TABLESPACE ORCLTBS2), PARTITION p2
VALUES LESS THAN(TO_DATE(''1990-1-1'', ''YYYY-MM-DD'')) (SUBP
ARTITION p2_sub1 VALUES(''M'') TABLESPACE ORCLTBS3, SUBPARTITION p2_sub2 VALUES(''F'') TABLESPACE ORCLTBS4), PARTITION p3 VALUES LESS THAN(MAXVALUE) TABLESPACE ORCLTBS5 ); 范围-散列复合分区范围-散列复合分区先对表进行范围分区，然后再对每个分区进行散列分区，即在一个范围分区中创建多个散列子分区。示例创建一个范围-散列复合分区表，将1980年1月1日前出生的学生信息均匀地保存在ORCLTBS1和ORCLTBS2表空间中，1980年1月1日到1990年1月1日出生的学生信息保存在ORCLTBS3和ORCLTBS4表空间中，其他学生信息保存在ORCLTBS5表空间中。 CREATE TABLE student_range_hash(? sno NUMBER(6) PRIMARY KEY,? sname VARCHAR2(10),? sage NUMBER(4),? birthday DATE? )? PARTITION BY RANGE(birthday)? SUBPARTITION BY HASH(sage)? (PARTITION p1 VALUES LESS THAN(TO_DATE(''1980-1-1'', ''YYYY-MM-DD'')) (SUBPARTITION p1_sub1 TABLESPACE ORCLTBS1, SUBPARTITION p1_sub2 TABLESPACE ORCLTBS2), PARTITION p2 VALUES LESS THAN(TO_DATE("1990-1-1", "YYYY-MM-DD")) (SUBPARTITION p2_sub1 TABLESPACE ORCLTBS3, SUBPARTITION p2_sub2 TABLESPACE ORCLTBS4), PARTITION p3 VALUES LESS THAN(MAXVALUE) TABLESPACE ORCLTBS5? );6.6.4 创建分区索引分区索引介绍 创建分区索引 （1）分区索引介绍本地分区索引 本地分区索引是指为分区表中的各个分区单独建立索引分区，各个索引分区之间是相互独立的。全局分区索引 全局分区索引是指先对整个分区表建立索引，然后再对索引进行分区。 全局非分区索引 全局非分区索引是指对整个分区表创建标准的未分区的索引。（2）创建分区索引 创建本地分区索引 使用LOCAL关键字标识本地分区索引。例如，在student_range分区表的sname列上创建本地分区索引。CREATE INDEX student_range_local ON student_range(sname) LOCAL; 创建全局分区索引与表分区方法类似，索引分区方法也包括范围分区、列表分区、散列分区和复合分区4种。在指明分区方法时使用GLOBAL关键字标识全局分区索引。例如，为分区表student_list的sage列建立基于范围的全局分区索引。CREATE INDEX student_range_globalON student_range(sage)GLOBAL PARTITION BY RANGE(sage)(PARTITION p1 VALUES LESS THAN (80) TABLESPACE ORCLTBS1,?PARTITION p2 VALUES LESS THAN (MAXVALUE) TABLESPACE ORCLTBS2); 全局非分区索引 为分区表创建全局非分区索引与为标准表创建索引一样。例如，为分区表student_list_index创建全局非分区索引。CREATE INDEX student_list_index ON student_list(sname);总结表的创建与管理索引的创建与管理视图的创建与管理序列的创建与管理分区表与分区索引的管理 |
|
