Oracle数据库基础

一、数据表管理

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