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$ cwhere 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 nullalter table person modify column name not null;--可以指定约束名--删除not nullalter table person modify column name null;

2、unique约束

--添加uniquealter table person add constraint cons_name_uq unique(name);--查看uniqueselect a.table_name,a.column_name,a.constraint_name,b.constraint_typefrom user_cons_columns a, user_constraints bwhere a.table_name='PERSON' and a.column_name='NAME'and b.constraint_type='U'and a.constraint_name=b.constraint_name--两个数据字典需结合使用--删除uniquealter table person drop constraint constraint_name;或者alter table person drop unique(col_name);

3、primary key约束

--添加primary keyalter table person add constraint cons_id_pk primary key(id);--查看primary key（同unique）--删除primary keyalter 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 createdSQL> 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 alteredSQL> 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 insertedSQL> commit;Commit complete

3、 测试2: Enable Novalidate

SQL> alter table test modify constraint ck_id disable;Table alteredSQL> insert into test values(5, 'Oracle');1 row insertedSQL> commit;Commit completeSQL> select * from test;

       ID NAME

---

      17 ERP        5 Oracle

SQL> alter table test modify constraint ck_id enable novalidate;Table alteredSQL> insert into test values(32, 'SAP');1 row insertedSQL> 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 deletedSQL> commit;Commit completeSQL> alter table test modify constraint ck_id disable validate;Table alteredSQL> 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 alteredSQL> insert into test values(2, 'Linux');1 row insertedSQL> insert into test values(13, 'Windows');1 row insertedSQL> update test set name = 'Change' where id = 17;1 row updatedSQL> commit;Commit completeSQL> select * from test;

      ID NAME

---

      17 Change      13 Windows      32 SAP       2 Linux