Ø 简介
我们知道,在 Oracle 中的表相关对象主要包括:表、视图、索引、序列、同义词等。这些对象是我们经常会接触到的,下面就展开对这些对象的学习,包括如下内容:
1. 表
2. 视图
3. 索引
4. 序列
5. 同义词
1. 表
1) 创建表
u 语法:
create table [schema,] table_name(
column_name data_type [constraints]
[,column_name data_type [constraints]
);
参数说明:
[]表示为可选的,否则为必须。解释如下:
scheme: 指定表所属的用户名或者所属的用户模式名称;
table_name: 顾名思义,表名;
column_name: 列名;
data_type: 列的数据类型;
constraints: 约束,可以是多个。
提示:创建数据表也可以采用 PL/SQL Developer 工具可视化创建,该工具只是帮我们根据表的设置,自动生成了创建表的 SQL 语句。不建议使用该方式,因为可控性或维护性较差。
首先,我们创建一张外键表(部门表):
CREATE TABLE T_DEPT(
DEPTID NUMBER(8) NOT NULL,
DNAME VARCHAR2(20),
CONSTRAINT PK_T_DEPT_DEPTID PRIMARY KEY(DEPTID)
);
1. 创建员工表,并添加键和约束(方式一:系统自动命名)
CREATE TABLE T_EMP(
EMPID NUMBER(8) NOT NULL PRIMARY KEY, --主键,系统自动命名,例如:SYS_C0014011
DEPTID NUMBER(8) NOT NULL REFERENCES T_DEPT(DEPTID), --外键,系统自动命名,例如:SYS_C0014013
IDNUMBER CHAR(18) NOT NULL UNIQUE, --唯一键,系统自动命名,例如:SYS_C0014012
SEX NUMBER(1) NOT NULL CHECK(SEX IN(0,1,2)), --检查约束,系统自动命名,例如:SYS_C0014010
AGE NUMBER(2) DEFAULT(22), --默认约束
ENAME VARCHAR2(20) NOT NULL --非空约束
);
n 注意:
1) 被外键关联的表(T_DEPT)的字段(DEPTID),必须为主键或唯一键,想想也是如此(注意思考);
2) 具有默认约束的列,可以省略非空约束;
3) 默认约束不能指定约束名,而非空约束可以,但意义不大(例如:ENAME VARCHAR2(20) CONSTRAINT CON_T_EMP_ENAME NOT NULL);
2. 创建员工表,并添加键和约束(方式二:手动命名)
CREATE TABLE T_EMP(
EMPID NUMBER(8) NOT NULL CONSTRAINT PK_T_EMP_EMPID PRIMARY KEY, --主键,名称:PK_T_EMP_EMPID
DEPTID NUMBER(8) NOT NULL CONSTRAINT FK_T_EMP_DEPTID REFERENCES T_DEPT(DEPTID), --外键,名称:FK_T_EMP_DEPTID
IDNUMBER CHAR(18) NOT NULL CONSTRAINT UQ_T_EMP_IDNUMBER UNIQUE, --唯一键,名称:UQ_T_EMP_IDNUMBER
SEX NUMBER(1) NOT NULL CONSTRAINT CK_T_EMP_SEX CHECK(SEX IN(0,1,2)), --检查约束,名称:CK_T_EMP_SEX
AGE NUMBER(2) DEFAULT(22) --默认约束
);
3. 创建员工表,并添加键和约束(方式三:手动命名)【推荐:可读性好】
CREATE TABLE T_EMP(
EMPID NUMBER(8) NOT NULL,
ENAME VARCHAR2(20) NOT NULL,
DEPTID NUMBER(8) NOT NULL,
IDNUMBER CHAR(18) NOT NULL,
SEX NUMBER(1) NOT NULL,
AGE NUMBER(2) DEFAULT(22), --默认约束,系统自动命名,例如:SYS_C0014008
CONSTRAINT PK_T_EMP_EMPID PRIMARY KEY(EMPID), --主键,名称:PK_T_EMP_EMPID
CONSTRAINT FK_T_EMP_DEPTID FOREIGN KEY(DEPTID) REFERENCES T_DEPT(DEPTID), --外键,名称:FK_T_EMP_DEPTID
CONSTRAINT UQ_T_EMP_IDNUMBER UNIQUE(IDNUMBER), --唯一键,名称:UQ_T_EMP_IDNUMBER
CONSTRAINT CK_T_EMP_SEX CHECK(SEX IN(0,1,2)) --检查约束,名称:CK_T_EMP_SEX
);
4. 拷贝表
CREATE TABLE Table05 AS SELECT * FROM Table02; --如果不需要拷贝数据,加上 1<>1 的条件即可
2) 修改表结构(ALTER TABLE)
1. 修改表名
RENAME table_name TO new_table_name;
2. 添加键和约束
ALTER TABLE T_EMP ADD CONSTRAINT PK_T_EMP_EMPID PRIMARY KEY(EMPID); --添加主键,名称:PK_T_EMP_EMPID
ALTER TABLE T_EMP ADD CONSTRAINT FK_T_EMP_DEPTID FOREIGN KEY(DEPTID) REFERENCES T_DEPT(DEPTID); --添加外键,名称:FK_T_EMP_DEPTID
ALTER TABLE T_EMP ADD CONSTRAINT UQ_T_EMP_IDNUMBER UNIQUE(IDNUMBER); --添加唯一键,名称:UQ_T_EMP_IDNUMBER
ALTER TABLE T_EMP ADD CONSTRAINT CK_T_EMP_SEX CHECK(SEX IN(0,1,2)); --添加检查约束,名称:CK_T_EMP_SEX
ALTER TABLE T_EMP MODIFY(AGE NUMBER(2) DEFAULT(22)); --添加默认约束
ALTER TABLE T_EMP MODIFY(ENAME VARCHAR2(20) NOT NULL); --添加非空约束
3. 添加联合/复合键
--联合主键
ALTER TABLE TABLE_NAME ADD CONSTRAINT PK_TABLE_NAME_Column1_Column2 PRIMARY KEY(Column1, Column2);
--联合外键
ALTER TABLE TABLE_NAME ADD CONSTRAINT FK_TABLE_NAME_Column1_Column2 FOREIGN KEY(Column1, Column2) REFERENCES TABLE_NAME(Column1, Column2);
--联合唯一键
ALTER TABLE TABLE_NAME ADD CONSTRAINT UQ_TABLE_NAME_Column1_Column2 UNIQUE(Column1, Column2);
4. 添加列
ALTER TABLE table_name ADD column1 data_type [constraints];
或者一次添加多列:
ALTER TABLE table_name ADD (
column1 data_type [constraints],
column2 data_type [constraints]
…
);
5. 修改列名
ALTER TABLE table_name RENAME COLUMN column_name TO new_column_name;
6. 修改列的类型
ALTER TABLE table_name MODIFY column_name data_type;
7. 删除列
ALTER TABLE table_name DROP COLUMN column_name;
3) 删除表
1. 基本用法
DROP TABLE table_name; --Oracle 中只能一次删除一张表,而 SQL Server 中可以同时删除多张表
2. 删除表,并删除与之关联的约束(比如:引用该表的外键约束)、视图等
DROP TABLE table_name CASCADE CONSTRAINTS;
3. 删除表,并释放资源,不经过回收站
DROP TABLE table_name PURGE;
2. 视图
视图可以看做一张虚拟表,它通常用于返回对一张表或多张表的查询结果,取出所需的字段,当作为一些通用的查询时,可以帮我们减少代码量;但同时也增加了对视图的维护成本,比如视图中的表字段进行了修改或删除,视图也需要做相应的更改。
u 语法
CREATE [OR REPLACE] VIEW view_name [(COLUMN_ALIASES)] AS
defining-query
[WITH READ ONLY]
[WITH CHECK OPTION]
参数说明:
OR REPLACE: 表示该语句具有创建和替换原有视图的能力;
COLUMN_ALIASES: 用于定义视图查询时显示的列名,当然也可以使用 AS 在 SELECT 与 FROM 之间定义列的别名;
defining-query: 定义视图的查询 SELECT 语句,定义了视图的列和数据;
WITH READ ONLY: 防止底层表通过视图进行更改;
WITH CHECK OPTION: 子句保护视图免受对基础表的任何更改,这些更改将生成未包含在定义查询中的行。
权限:创建视图的用户必须具有 CREATE VIEW 的系统权限。
1) 基本用法
CREATE OR REPLACE VIEW V_EMP
AS
SELECT * FROM EMP WHERE 1=1;
执行查询:
2) 指定列的别名
CREATE OR REPLACE VIEW V_EMP(员工编号,姓名)
AS
SELECT EMPNO, ENAME FROM EMP WHERE 1=1;
执行查询:
SELECT * FROM V_EMP;
3) 编译视图
ALTER VIEW V_EMP COMPILE;
当视图中引用的表发生改变时,可以使用编译视图来查看是否成功
3. 索引
1) 普通索引
CREATE INDEX IDX_T_EMP_ENAME ON T_EMP(ENAME);
2) 唯一索引
CREATE UNIQUE INDEX IDX_T_EMP_IDNUMBER ON T_EMP(IDNUMBER);
3) 联合索引
CREATE INDEX IDX_T_DEPT_DEPTID_DNAME ON T_DEPT(DEPTID,DNAME);
4) 反向键索引
4. 序列
u 语法
CREATE SEQUENCE <seq_name>
[START WITH n]
[INCREMENT BY n]
[MINVALUE n | NOMINVALUE]
[MAXVALUE n | NOMAXVALUE]
[CACHE n | NOCACHE]
[CYCLE | NOCYCLE]
[ORDER | NOORDER];
参数说明:
seq_name: 序列名;
START WITH: 设置序列起始值,默认情况下,递增序列起始值为 MINVALUE,递减序列起始值为 MAXVALUE,可选参数,默认为1;
INCREMENT BY: 设置序列的增量,正数表示递增,而负数表示递减。可选参数,默认为1;
MINVALUE: 设置序列生成的最小值,可选参数,默认为 NOMINVALUE(不限制最小值);
MAXVALUE: 设置序列生成的最大值,可选参数,默认为 NOMAXVALUE(不限制最大值);
CACHE: 是否产生序列号预分配,并存储在内存中。数据库异常终止,可能会导致序列中断不连续的情况,默认为20;
CYCLE: 指定序列到达最大值(MAXVALUE)或最小值(MINVALUE)时,是否可复位并继续生成序列。如果为 NOCYCLE,当到达最大值或最小值时,如果再视图获取序列的下一个值将出错;
ORDER: 用于确保序列是按顺序生成的,比如:第一个请求获取1,第二个请求就会获取2,以此类推;如果是 NOORDER,只能保证序列唯一,不能确保序列的顺序。
1) 创建序列
CREATE SEQUENCE SEQ_T_DEPT_DEPTID
START WITH 2
INCREMENT BY 2
MAXVALUE 10000
CACHE 10
ORDER;
2) 查询序列
SELECT SEQ_T_DEPT_DEPTID.NEXTVAL FROM DUAL; --下一个序列值
SELECT SEQ_T_DEPT_DEPTID.CURRVAL FROM DUAL; --当前序列值
3) 使用序列插入数据
INSERT INTO T_DEPT VALUES(SEQ_T_DEPT_DEPTID.NEXTVAL, '研发部');
4) 修改序列
ALTER SEQUENCE SEQ_T_DEPT_DEPTID MAXVALUE 9999999;
5) 删除序列
DROP SEQUENCE SEQ_T_DEPT_DEPTID;
6) 查询所有序列
SELECT * FROM USER_SEQUENCES;
7) 解决新表第一次使用新的序列时,序列值为2的问题
原因:从Oracle 11.2.0.1开始,提供了一个"延迟段创建"特性,在做插入时,序列会跳过第一个值1,从而插入的是2。可以采用下面两种办法:
1. 更改数据库的"延时段创建"特性为false(需要相应的权限)
ALTER SYSTEMSET deferred_segment_creation = FALSE;
2. 创建表时让序列立即执行
--系统配置
CREATE TABLE Sys_Config(
ConfigId NUMBER(16) NOT NULL, --配置Id
ConfigName VARCHAR2(64) NOT NULL, --配置名
ConfigValue VARCHAR2(4000) NOT NULL, --配置值
CreateUserId NUMBER(4) NOT NULL, --创建人Id
CreateTime DATE NOT NULL, --创建时间
UpdateUserId NUMBER(4) NULL, --修改人Id
UpdateTime DATE NULL, --修改时间
CONSTRAINT PK_Config_ConfigId PRIMARY KEY(ConfigId),
CONSTRAINT UQ_Config_ConfigName UNIQUE(ConfigName)
)
SEGMENT CREATION IMMEDIATE;
n 注意事项:
1. 调用 SEQ_EMP_ID.CURRVAL 前,必须先初始化,即调用 SEQ_EMP_ID.NEXTVAL;
2. 序列不受事务的控制,也就是回滚事务并不会回滚序列。
5. 同义词
语法:
https://baijiahao.baidu.com/s?id=1617567365234678799&wfr=spider&for=pc
https://www.cnblogs.com/wushuang0608/p/8405281.html
https://blog.csdn.net/u013752926/article/details/79869040