03-012 oracle中alter的用法

对表常用的alter语句：

ALTER TABLE (表名) ADD (列名 数据类型);
ALTER TABLE (表名) MODIFY (列名 数据类型);
ALTER TABLE (表名) RENAME COLUMN (当前列名) TO (新列名); 
ALTER TABLE (表名) DROP COLUMN (列名); 
ALTER TABLE (当前表名) RENAME TO (新表名);

//建测试表
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)
);

–

//1.重命名
	//1.1 表：
			rename dept to dt; 
		  	rename dt to dept;
		  		
	//1.2 列：
			alter  table  dept  rename column  loc  to location;
			alter table dept rename column location to loc;
			
//2.添加约束
	//2.1 primary key
			alter table employee_info add constraint pk_emp_info primary key(empno);
	//2.2 foreign key
			alter table employee_info add constraint fk_emp_info foreign key(deptno) 
			references dept(deptno);
	//2.3 check
			alter table employee_info add constraint ck_emp_info check (sex in ('F','M'));
	//2.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';
			
//3.列
	//3.1 添加列
		alter table employee_info add id varchar2(18);
		alter table employee_info add hiredate date default sysdate not null;
	//3.2 删除列
		alter table employee_info drop column introduce;
	//3.3 修改列
		//3.3.1  修改列的长度
			alter table dept modify loc varchar2(50);
		//3.3.2 修改列的精度
			alter table employee_info modify empno number(2);
		//3.3.3 修改列的数据类型
			alter table employee_info modify sex char(2);
		//3.3.4 修改默认值
			alter table employee_info modify hiredate default sysdate+1;
//4. 约束
	//41. 禁用约束
		alter table employee_info disable constraint uq_emp_info;
	//4.2 启用约束
		alter table  employee_info enable constraint uq_emp_info;
	//4.3 延迟约束
		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;

//5. 注释
	//5.1 向表中添加注释
		comment on table employee_info is 'information of employees';
	//5.2 向列添加注释
		comment on column employee_info.ename is 'the name of employees';
		comment on column dept.dname is 'the name of department';
//6 表
	//6.1 清除表中所有数据
		truncate table employee_info;
	//6.2 删除表
		drop table employee_info;

–
//下面来看看刚刚才我们对表dept和表employee_info所做的更改
//user_constraints视图里面包含了刚刚创建的所有约束，以及其它信息

//可以使用desc user_constraints 命令查看其详细说明

[oracle@HXQ-CMS-TEST ~]$ sqlplus amsshorcl_test
SQL*Plus: Release 11.2.0.4.0 Production on 1 17:34:31 2019

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Enter password: 

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select constraint_name,constraint_type,status,deferrable,deferred from user_constraints where table_name = 'EMPLOYEE_INFO';

CONSTRAINT_NAME                C STATUS   DEFERRABLE     DEFERRED
------------------------------ - -------- -------------- ---------
PK_EMP_INFO                    P ENABLED  NOT DEFERRABLE IMMEDIATE
FK_EMP_INFO                    R ENABLED  NOT DEFERRABLE IMMEDIATE
CK_EMP_INFO                    C ENABLED  NOT DEFERRABLE IMMEDIATE
NOT_NULL_EMP_INFO              C ENABLED  NOT DEFERRABLE IMMEDIATE
UQ_EMP_INFO                    U ENABLED  NOT DEFERRABLE IMMEDIATE

SQL> 

//可以通过user_cons_columns视图查看有关列的约束信息；

 column column_name format a15;
SQL> select owner,constraint_name,table_name,column_name from user_cons_columns where table_name = 'EMPLOYEE_INFO';

OWNER                          CONSTRAINT_NAME
------------------------------ ------------------------------
TABLE_NAME                     COLUMN_NAME
------------------------------ ---------------
AMSSHORCL_TEST                 PK_EMP_INFO
EMPLOYEE_INFO                  EMPNO

AMSSHORCL_TEST                 FK_EMP_INFO
EMPLOYEE_INFO                  DEPTNO

AMSSHORCL_TEST                 CK_EMP_INFO
EMPLOYEE_INFO                  SEX


OWNER                          CONSTRAINT_NAME
------------------------------ ------------------------------
TABLE_NAME                     COLUMN_NAME
------------------------------ ---------------
AMSSHORCL_TEST                 NOT_NULL_EMP_INFO
EMPLOYEE_INFO                  PHONE

AMSSHORCL_TEST                 UQ_EMP_INFO
EMPLOYEE_INFO                  PHONE

//将user_constraints视图与user_cons_columns视图连接起来
//查看约束都指向那些列

SQL> column column_name format a15;  
SQL> select ucc.column_name, ucc.constraint_name, uc.constraint_type, uc.status
  2  from user_constraints uc, user_cons_columns ucc
  3  where uc.table_name = ucc.table_name
  4  and uc.constraint_name = ucc.constraint_name
  5  and ucc.table_name = 'EMPLOYEE_INFO';

COLUMN_NAME     CONSTRAINT_NAME                C STATUS
--------------- ------------------------------ - --------
EMPNO           PK_EMP_INFO                    P ENABLED
DEPTNO          FK_EMP_INFO                    R ENABLED
SEX             CK_EMP_INFO                    C ENABLED
PHONE           NOT_NULL_EMP_INFO              C ENABLED
PHONE           UQ_EMP_INFO                    U ENABLED

SQL> 

//这里有个constraint_type，它具体指下面几种类型：
//C：check,not null
//P：primari 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’;

SQL> select * from user_tab_comments where table_name='EMPLOYEE_INFO';

TABLE_NAME                     TABLE_TYPE
------------------------------ -----------
COMMENTS
--------------------------------------------------------------------------------
EMPLOYEE_INFO                  TABLE
information of employees

//可以通过user_col_commnets 视图获得对表列的注释；

SQL> 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

TABLE_NAME                     COLUMN_NAME
------------------------------ ---------------
COMMENTS
--------------------------------------------------------------------------------
EMPLOYEE_INFO                  SEX
EMPLOYEE_INFO                  PHONE
EMPLOYEE_INFO                  ADDRESS

TABLE_NAME                     COLUMN_NAME
------------------------------ ---------------
COMMENTS
--------------------------------------------------------------------------------
EMPLOYEE_INFO                  INTRODUCE

7 rows selected.


SQL> 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

//最后看一下修改后的表：

SQL> desc employee_info;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPNO                                     NOT NULL NUMBER(3)
 DEPTNO                                             NUMBER(3)
 ENAME                                              VARCHAR2(10)
 SEX                                                CHAR(2)
 PHONE                                     NOT NULL NUMBER(11)
 ADDRESS                                            VARCHAR2(50)
 INTRODUCE                                          VARCHAR2(100)

SQL> desc dept;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 DEPTNO                                    NOT NULL NUMBER(3)
 DNAME                                              VARCHAR2(10)
 LOC                                                VARCHAR2(13)

			

---

查询表字段

-- user_tab_cols 包含oracle创建的隐藏字段
SELECT column_name FROM user_tab_cols where table_name = upper('表名') 

-- user_tab_columns 自定义的字段
SELECT column_name FROM user_tab_columns where table_name = upper('表名')


将表所有列名查出，并拼成字符串

```sql
select Listagg(column_name, ',') WITHIN GROUP(ORDER BY column_name)
from user_tab_columns 
where table_name = upper('表名') 
--不想查询的字段名
and column_name not in ('字段名','字段名');