一、数据表管理
1、创建表
create table person(
id number(4),
name varchar2(10),
sex char(2),
birthdate date
) tablespace tbs01
2、管理表中的列
--增加列
alter table person add email varchar2(30);
--重命名列
alter table person rename column email to mail;
--更改列的数据类型
alter table person modify mail char(30);
--删除列
alter table person drop column mail;
alter table person drop (mail,birthday);
3、设置unused列
--设置表中的列为unused状态
alter table person set unused(mail);
--查看unused列状态信息
select o.obj#,col#,intcol#,c.name,property from obj$ o,col$ c
where o.obj#=c.obj# and o.name='PERSON';
OBJ# COL# INTCOL# NAME PROPERTY
69820 1 1 ID 0
69820 2 2 NAME 0
69820 3 3 SEX 0
69820 4 4 BIRTHDATE 0
69820 0 5 SYS_C00005_系统产生 32800
select cols from tab$ where obj#=69820--结果比原来的值小1
--恢复unused列
update col$ set col#=intcol# where obj#=69820;
update tab$ set cols=cols+1 where obj#=69820;
update col$ set name='MAIL' where obj#=69820 and col#=5;
update col$ set property=0 where obj#=69820;
最后提交,关闭数据库,然后重启即可。
--删除unused列
alter table person drop unused columns;
4、重命名表
alter table person rename to person01;
rename person01 to person;
5、移动表
--查看表所在的表空间
SQL> select table_name,tablespace_name from user_tables
where table_name='PERSON';
TABLE_NAME TABLESPACE_NAME
PERSON TBS01
–移动到特定的表空间
alter table person move tablespace users;
6、截断表(不同于delete,不记录日志信息,不能撤销和恢复,速度快)
truncate table person;
7、删除表
DROP TABLE table_name [CASCADE CONSTRAINTS] [PURGE];
drop table person cascade constraints purge;
二、完整性约束
1、not null 约束
--添加not null
alter table person modify column name not null;--可以指定约束名
--删除not null
alter table person modify column name null;
2、unique约束
--添加unique
alter table person add constraint cons_name_uq unique(name);
--查看unique
select a.table_name,a.column_name,a.constraint_name,b.constraint_type
from user_cons_columns a, user_constraints b
where a.table_name='PERSON' and a.column_name='NAME'
and b.constraint_type='U'
and a.constraint_name=b.constraint_name--两个数据字典需结合使用
--删除unique
alter table person drop constraint constraint_name;或者
alter table person drop unique(col_name);
3、primary key约束
--添加primary key
alter table person add constraint cons_id_pk primary key(id);
--查看primary key(同unique)
--删除primary key
alter table person drop constraint constraint_name;只有这一种方法
4、check约束
--添加check约束
alter table person add constraint cons_sex_ck check(sex in('男','女'));
插入数据测试约束是否起作用
--删除check约束(同primary key)
5、foreign key约束
--使用书的例子创建两个表
--主表
create table type(
tid number(4) primary key,
tname varchar2(10) not null
)
--引用表
create table book(
bid number(4) primary key,
bname varchar2(20) not null,
tid number(4),
constraint book_type foreign key (tid) references type(tid)
on delete set NULL
)
三、约束的验证状态
1、 建表
SQL> create table test(id int, name varchar2(10));
Table created
SQL> alter table test add constraint ck_id check(id > 10);
Table altered
2、 测试1: Enable Validate
SQL> alter table test modify constraint ck_id Enable validate;
Table altered
SQL> insert into test values(5, 'Oracle');
insert into test values(5, 'Oracle')
ORA-02290: 违反检查约束条件 (MYHR.CK_ID)
SQL> insert into test values(17,'ERP');
1 row inserted
SQL> commit;
Commit complete
3、 测试2: Enable Novalidate
SQL> alter table test modify constraint ck_id disable;
Table altered
SQL> insert into test values(5, 'Oracle');
1 row inserted
SQL> commit;
Commit complete
SQL> select * from test;
ID NAME
17 ERP
5 Oracle
SQL> alter table test modify constraint ck_id enable novalidate;
Table altered
SQL> insert into test values(32, 'SAP');
1 row inserted
SQL> insert into test values(3, 'Linux');
insert into test values(3, 'Linux')
ORA-02290: 违反检查约束条件 (MYHR.CK_ID)
SQL> commit;
Commit complete
4 、测试3: Disable Validate
SQL> delete from test where id < 10;
1 row deleted
SQL> commit;
Commit complete
SQL> alter table test modify constraint ck_id disable validate;
Table altered
SQL> select * from test;
ID NAME
17 ERP
32 SAP
SQL> update test set name = 'Change' where id = 17;
update test set name = 'Change' where id = 17
ORA-25128: 不能对带有禁用和验证约束条件 (MYHR.CK_ID) 的表进行插入/更新/删除
5 、测试4: Disable Novalidate
SQL> alter table test modify constraint ck_id disable novalidate;
Table altered
SQL> insert into test values(2, 'Linux');
1 row inserted
SQL> insert into test values(13, 'Windows');
1 row inserted
SQL> update test set name = 'Change' where id = 17;
1 row updated
SQL> commit;
Commit complete
SQL> select * from test;
ID NAME
17 Change
13 Windows
32 SAP
2 Linux