1、10表及约束表及约束创建简单的表创建简单的表 create table t(a int,b char(15);create table t(a int,b char(15)tablespace tbs 普通用户创建表，并未分配空间 添加记录时，才对表分配空间2015-813-2Oracle数据库系统原理数据库系统原理-第第13章章 位图索引位图索引约束约束 主键(primary key)列值不能为空也不能重复。唯一(unique)外键(foreign key)列值要匹配于其指向的主表的相应列值。检查(check)列值的限制条件。非空(not null)默认(default)添加记录时，若未指定
2、列值，则取默认约束中的值。2015-810-3Oracle数据库系统原理数据库系统原理-第第10章章 表及约束表及约束附加约束的位置附加约束的位置 列级约束 表级约束2015-810-4Oracle数据库系统原理数据库系统原理-第第10章章 表及约束表及约束附加约束的各种语法形式附加约束的各种语法形式 列级约束，未指定名称SQL create table t1 2 (3 a int primary key,4 b int 5 )6 /列级约束，指定名称SQL create table t2 2 (3 a int constraint pk_test primary key,4 b int 5
3、)6 /2015-810-5Oracle数据库系统原理数据库系统原理-第第10章章 表及约束表及约束附加约束的各种语法形式附加约束的各种语法形式 表级约束，未指定名称SQL create table t3 2 (3 a int,4 b int,5 primary key(a)6 )7 /表级约束，指定名称SQL create table t4 2 (3 a int,4 b int,5 constraint pk_test primary key(a)6 )7 /2015-810-6Oracle数据库系统原理数据库系统原理-第第10章章 表及约束表及约束几点主意几点主意 多列复合主键或多列唯一约
4、束只能使用表级约束。列级检查约束不能出现其他列名，表级检查约束可以引用表中任何列。非空约束只能附加在列级而不能附加在表级。2015-810-7Oracle数据库系统原理数据库系统原理-第第10章章 表及约束表及约束Check约束示例约束示例SQL create table t6 2 (3 a int primary key,4 b int,5 constraint ck_test check(b10)6 )7 /2015-810-8Oracle数据库系统原理数据库系统原理-第第10章章 表及约束表及约束外键约束示例外键约束示例 创建主表p，a列为主键SQL create table p(a i
5、nt primary key,b int);对c表的y列附加表级外键约束SQL create table c3 2 (3 x int,4 y int,5 constraint fk_c foreign key(y)references p(a)6 )7 /2015-810-9Oracle数据库系统原理数据库系统原理-第第10章章 表及约束表及约束外键的约束作用外键的约束作用 c表中的y列的值要匹配于p表中a列的值。p表中被引用到的记录不能删除，被引用的a列的值也不能修改。2015-810-10Oracle数据库系统原理数据库系统原理-第第10章章 表及约束表及约束附加外键时的附加外键时的on
6、delete子句子句 on delete cascade 子表中的对应记录会被级联删除。on delete set null 把外键值设置为null。Oracle不支持on update子句2015-810-11Oracle数据库系统原理数据库系统原理-第第10章章 表及约束表及约束附加附加on delete子句子句SQL create table c5 2 (3 x int primary key,4 y int,5 foreign key(y)references p(b)6 on delete cascade -或set null 7 )8 /2015-810-12Oracle数据库系统
7、原理数据库系统原理-第第10章章 表及约束表及约束对表增加约束对表增加约束 表级形式 alter table table_name add constraint constraint_name costraintt_clause 列级形式 alter table table_name modify column_name constraint constraint_name constraint_clause2015-810-13Oracle数据库系统原理数据库系统原理-第第10章章 表及约束表及约束对表增加约束示例对表增加约束示例 emp表的sal列增加检查约束 alter table em
8、p add constraint ck_sal check(sal alter table emp modify sal null;2015-810-15Oracle数据库系统原理数据库系统原理-第第10章章 表及约束表及约束禁用和启用约束禁用和启用约束 禁用约束alter table emp disable constraint fk_deptno;启用约束alter table emp enable constraint fk_deptno;启用时不检查原数据alter table emp enable novalidate constraint fk_deptno;启用时检查原数据(默认
9、)alter table emp enable validate constraint fk_deptno;2015-810-16Oracle数据库系统原理数据库系统原理-第第10章章 表及约束表及约束查询约束信息查询约束信息 查看用户自己的约束 user_constraints user_cons_columns system用户查看数据库中的任何约束 dba_constraints dba_cons_columns 注意：两者的区别是xxx_constraints不包含列的信息2015-810-17Oracle数据库系统原理数据库系统原理-第第10章章 表及约束表及约束修改表的结构修改表的
10、结构 修改表名 alter table dept rename to department;rename department to dept;修改列名 alter table dept rename column loc to location;添加列 alter table dept add phone_number char(12)删除列 alter table dept drop column phone_number 修改列类型 alter table dept modify(deptno number(3),loc varchar2(20);2015-810-18Oracle数据库
11、系统原理数据库系统原理-第第10章章 表及约束表及约束清清空空表表 truncate table emp;truncate与delete的区别 truncate属于DDL语句，执行后事务自动提交，不能回滚，delete操作是DML语句，删除的数据可以执行rollback命令恢复回来。truncate通过释放表占用的空间删除数据，产生的重做数据很少。delete操作删除的数据不会释放空间，删除的记录信息都会记入重做日志文件，产生的重做数据一般会比删除的数据量大。2015-810-19Oracle数据库系统原理数据库系统原理-第第10章章 表及约束表及约束删除表删除表 drop table emp
12、;删除表后，其数据并未清除。可以把删除的表从回收站中恢复出来。2015-810-20Oracle数据库系统原理数据库系统原理-第第10章章 表及约束表及约束回收站回收站 Oracle的回收站实际上是一个数据字典表，其中存储了删除的数据库对象信息，其功能与操作系统的回收站功能类似2015-810-21Oracle数据库系统原理数据库系统原理-第第10章章 表及约束表及约束与回收站有关的数据字典视图与回收站有关的数据字典视图 user_recyclebin recyclebin为其同义词 dba_recyclebin 主要列 object_name：对象被删除后，在回收站中的新名称。origina
13、l_name：对象删除之前的原名称。droptime：对象的删除时间。2015-810-22Oracle数据库系统原理数据库系统原理-第第10章章 表及约束表及约束查询回收站中的数据查询回收站中的数据SQL select object_name,original_name from user_recyclebin;OBJECT_NAME ORIGINAL_NAME-BIN$Hiv6MN2ASTuYRGIyW/YZOg=$0 PK_EMPBIN$+1lzYAAIQ/217zwnNdK59w=$0 EMP2015-810-23Oracle数据库系统原理数据库系统原理-第第10章章 表及约束表及约束
14、查询删除表中的数据查询删除表中的数据 被删除后，转移到回收站中的对象还是属于原用户的，可以以其回收站中的名称来查询其原数据。select*from BIN$+1lzYAAIQ/217zwnNdK59w=$0;如果system用户查询回收站中的表，则需附加表的原属主。select*from scott.BIN$+1lzYAAIQ/217zwnNdK59w=$0;2015-810-24Oracle数据库系统原理数据库系统原理-第第10章章 表及约束表及约束恢复被删除的表恢复被删除的表 指定原表名 flashback table emp to before drop;指定回收站中的表名flashba
15、ck table BIN$Z0kp5fISQka+U+K9rsbc5A=$0 to before drop;恢复后改为其他表名 flashback table t to before drop rename to t1;2015-810-25Oracle数据库系统原理数据库系统原理-第第10章章 表及约束表及约束不使用回收站不使用回收站 附加了purge关键字，直接将表删除 SQL drop table emp purge;关闭回收站功能 SQL alter system set recyclebin=off scope=spfile;2015-810-26Oracle数据库系统原理数据库系统
16、原理-第第10章章 表及约束表及约束清空回收站清空回收站 purge table命令清空指定表 SQL drop table bonus;SQL purge table BIN$Po/lxosvQrOAjvrDHMUWyg=$0;2015-810-27Oracle数据库系统原理数据库系统原理-第第10章章 表及约束表及约束回收站的几个注意事项回收站的几个注意事项 sys或system用户删除的对象不会放入回收站。空闲空间不够时，回收站中的对象所占用的空间会自动释放掉，分配给需要空间的其他对象。恢复表时，其索引也会自动恢复，但会沿用回收站中的名称。可以执行下面命令改为原名称。SQL alter
17、index BIN$xgIxUJLlRj+433nY+RLpMA=$0 2 rename to pk_emp 3 /2015-810-28Oracle数据库系统原理数据库系统原理-第第10章章 表及约束表及约束建表操作的背后建表操作的背后 执行COMMIT操作。检查用户是否有建表权限。确认表要创建到的表空间是否存在。确认在当前模式内不存在与新表同名的对象。对相关数据字典基表添加新表的定义。若建表操作成功，最后执行COMMIT操作，若未成功则执行ROLLBACK操作。2015-810-29Oracle数据库系统原理数据库系统原理-第第10章章 表及约束表及约束Oracle数据库对象数据库对象 t
18、able，index view sequence synonym2015-810-30Oracle数据库系统原理数据库系统原理-第第10章章 表及约束表及约束视图视图-view 视图被定义为一个查询，像表一样操作视图。视图的作用：简化查询编写，把查询限制到列 创建视图语法create view v_emp as select select ename,salename,sal from from empemp 修改视图 Alter view as 删除视图drop view v_emp 查询视图Select*from v_emp_sal2015-810-31Oracle数据库系统原理数据库系统
19、原理-第第10章章 表及约束表及约束同义词同义词-synonym system用户创建同义词 create synonym emp for scott.emp system用户使用同义词进行查询 Select*from emp2015-810-32Oracle数据库系统原理数据库系统原理-第第10章章 表及约束表及约束序列序列-sequence 属于公用对象 一般用于自增列 创建序列CREATE SEQUENCE sequenceINCREMENT BY nSTART WITH nMAXVALUE n|NOMAXVALUEMINVALUE n|NOMINVALUECYCLE|NOCYCLECA
20、CHE n|NOCACHE;2015-810-33Oracle数据库系统原理数据库系统原理-第第10章章 表及约束表及约束创建序列示例创建序列示例SQL create sequence dept_deptid_seq 2 increment by 10 3 start with 50 4 /2015-810-34Oracle数据库系统原理数据库系统原理-第第10章章 表及约束表及约束序列的序列的nextval和和currval属性属性 nextval 每次引用都返回序列的下一个可用值 CURRVAL 返回序列的当前值2015-810-35Oracle数据库系统原理数据库系统原理-第第10章章
21、表及约束表及约束使用序列使用序列SQL insert into dept(deptno,dname,loc)2 values(dept_deptid_seq.nextval,Support,DALLAS)3 /已创建 1 行。SQL select dept_deptid_seq.currval from dual;CURRVAL-502015-810-36Oracle数据库系统原理数据库系统原理-第第10章章 表及约束表及约束管理序列管理序列 修改序列属性alter sequence dept_deptid_seqincrement by 20maxval 999999 删除序列drop sequence dept_deptid_seq2015-810-37Oracle数据库系统原理数据库系统原理-第第10章章 表及约束表及约束
