- //建测试表
-
create table dept(
-
deptno number(3) primary key,
- dname varchar2(10),
- loc varchar2(13)
- );
-
create table employee_info(
- empno number(3),
- deptno number(3),
- ename varchar2(10),
-
sex char(1),
- phone number(11),
- address varchar2(50),
- introduce varchar2(100)
- );
-
- //0.重命名
-
//0.1 表:rename dept to dt;
-
rename dt to dept;
-
//0.2 列:alter table dept rename column loc to location;
-
alter table dept rename column location to loc;
- //1.添加约束
-
//1.1 primary key
-
alter table employee_info add constraint pk_emp_info primary key(empno);
-
//1.2 foreign key
-
alter table employee_info add constraint fk_emp_info foreign key(deptno)
-
references dept(deptno);
-
//1.3 check
-
alter table employee_info add constraint ck_emp_info check
-
(sex in (‘F‘,‘M‘));
-
//1.4 not null
-
alter table employee_info modify phone constraint not_null_emp_info not null;
-
//1.5 unique
-
alter table employee_info add constraint uq_emp_info unique(phone);
-
//1.6 default
-
alter table employee_info modify sex char(2) default ‘M‘;
- //2.添加列
-
alter table employee_info add id varchar2(18);
-
alter table employee_info add hiredate date default sysdate not null;
- //3.删除列
-
alter table employee_info drop column introduce;
- //3.修改列
- //3.1 修改列的长度
-
alter table dept modify loc varchar2(50);
- //3.2 修改列的精度
-
alter table employee_info modify empno number(2);
- //3.3 修改列的数据类型
-
alter table employee_info modify sex char(2);
- //3.4 修改默认值
-
alter table employee_info modify hiredate default sysdate+1;
- //4.禁用约束
-
alter table employee_info disable constraint uq_emp_info;
- //5.启用约束
-
alter table employee_info enable constraint uq_emp_info;
- //6.延迟约束
-
alter table employee_info drop constraint fk_emp_info;
-
alter table employee_info add constraint fk_emp_info foreign key(deptno)
-
references dept(deptno)
- deferrable initially deferred;
- //7.向表中添加注释
-
comment on table employee_info is ‘information of employees‘;
- //8.向列添加注释
-
comment on column employee_info.ename is ‘the name of employees‘;
-
comment on column dept.dname is ‘the name of department‘;
- //9.清除表中所有数据
-
truncate table employee_info;
- //10.删除表
-
drop table employee_info;
-
- //下面来看看刚刚才我们对表dept和表employee_info所做的更改
- //user_constraints视图里面包含了刚刚才我们创建的所有约束，以及其他信息，
-
//你可以用desc user_constraints命令查看其详细说明
-
select constraint_name,constraint_type,status,deferrable,deferred
-
from user_constraints
-
where table_name=‘EMPLOYEE_INFO‘;
-
- CONSTRAINT_NAME CONSTRAINT_TYPE STATUS DEFERRABLE DEFERRED
-
-
PK_EMP_INFO P ENABLED NOT DEFERRABLE IMMEDIATE
- FK_EMP_INFO R ENABLED DEFERRABLE DEFERRED
-
NOT_NULL_EMP_INFO C ENABLED NOT DEFERRABLE IMMEDIATE
-
SYS_C005373 C ENABLED NOT DEFERRABLE IMMEDIATE
-
UQ_EMP_INFO U ENABLED NOT DEFERRABLE IMMEDIATE
-
CK_EMP_INFO C ENABLED NOT DEFERRABLE IMMEDIATE
- //我们可以通过user_cons_columns视图查看有关列的约束信息；
-
select owner,constraint_name,table_name,column_name
-
from user_cons_columns
-
where table_name=‘EMPLOYEE_INFO‘;
-
- OWNER CONSTRAINT_NAME TABLE_NAME COLUMN_NAME
-
- YEEXUN PK_EMP_INFO EMPLOYEE_INFO EMPNO
- YEEXUN CK_EMP_INFO EMPLOYEE_INFO SEX
- YEEXUN NOT_NULL_EMP_INFO EMPLOYEE_INFO PHONE
- YEEXUN SYS_C005373 EMPLOYEE_INFO HIREDATE
- YEEXUN UQ_EMP_INFO EMPLOYEE_INFO PHONE
- YEEXUN FK_EMP_INFO EMPLOYEE_INFO DEPTNO
- //我们将user_constraints视图与user_cons_columns视图连接起来
- //查看约束都指向哪些列
-
column column_name format a15;
-
select ucc.column_name,ucc.constraint_name,uc.constraint_type,uc.status
-
from user_constraints uc,user_cons_columns ucc
-
where uc.table_name=ucc.table_name and
-
uc.constraint_name=ucc.constraint_name and
-
ucc.table_name=‘EMPLOYEE_INFO‘;
-
- COLUMN_NAME CONSTRAINT_NAME CONSTRAINT_TYPE STATUS
-
- EMPNO PK_EMP_INFO P ENABLED
- DEPTNO FK_EMP_INFO R ENABLED
- PHONE NOT_NULL_EMP_INFO C ENABLED
- HIREDATE SYS_C005373 C ENABLED
- PHONE UQ_EMP_INFO U ENABLED
- SEX CK_EMP_INFO C ENABLED
-
- //这里有个constraint_type，他具体指下面几种类型:
-
//C:check,not null
-
//P:primary key
-
//R:foreign key
-
//U:unique
-
//V:check option
-
//O:read only
-
- //我们可以通过user_tab_comments视图获得对表的注释
-
select * from user_tab_comments
-
where table_name=‘EMPLOYEE_INFO‘;
- TABLE_NAME TABLE_TYPE COMMENTS
-
-
EMPLOYEE_INFO TABLE information of employees
-
- //我们还可以通过user_col_comments视图获得对表列的注释：
-
select * from user_col_comments
-
where table_name=‘EMPLOYEE_INFO‘;
-
- TABLE_NAME COLUMN_NAME COMMENTS
-
- EMPLOYEE_INFO EMPNO
- EMPLOYEE_INFO DEPTNO
-
EMPLOYEE_INFO ENAME the name of employees
- EMPLOYEE_INFO SEX
- EMPLOYEE_INFO PHONE
- EMPLOYEE_INFO ADDRESS
- EMPLOYEE_INFO ID
- EMPLOYEE_INFO HIREDATE
-
-
select * from user_col_comments
-
where table_name=‘EMPLOYEE_INFO‘ and
-
comments is not null;
-
- TABLE_NAME COLUMN_NAME COMMENTS
-
-
EMPLOYEE_INFO ENAME the name of employees
-
- //最后我们来查看一下修改后的表:
-
desc employee_info;
-
Name Type Nullable Default Comments
-
- EMPNO NUMBER(2)
- DEPTNO NUMBER(3) Y
-
ENAME VARCHAR2(10) Y the name of employees
-
SEX CHAR(2) Y ‘M‘
- PHONE NUMBER(11)
- ADDRESS VARCHAR2(50) Y
- ID VARCHAR2(18) Y
-
HIREDATE DATE sysdate+1
-
-
desc dept;
-
Name Type Nullable Default Comments
-
- DEPTNO NUMBER(3)
-
DNAME VARCHAR2(10) Y the name of department
- LOC VARCHAR2(50) Y
oracle create table(转),布布扣,bubuko.com
oracle create table(转)