SQL_修改表结构

 ***********************************************声明*********************************************************************** 

原创作品,出自 “深蓝的blog” 博客,欢迎转载,转载时请务必注明出处,否则追究版权法律责任。

深蓝的blog:http://blog.csdn.net/huangyanlong/article/details/39758393

****************************************************************************************************************************

SQL_修改表结构

说明

实验环境:利用scott用户数据,简单举例修改表结构范例
SQL> create table emp1 as select * from emp;
Table created
--创建实验表

1. 表中添加新列

(1)、语法
SQL> alter table 表名 add (列名 数据类型 [,列名 数据类型] ...);

 

(2)、注意事项
新添加的列会按顺序续接原表的列进行排列。

 

(3)、实验演示

SQL> alter table emp1 add (evaluatetime DATE);
Table altered
--添加分析时间列
SQL> select * from emp1;
--查看表数据内容
EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO EVALUATETIME
----- ---------- --------- ----- ----------- --------- --------- ------ ------------
 7369 SMITH      CLERK      7902 1980/12/17     800.00               20 
 7499 ALLEN      SALESMAN   7698 1981/2/20     1600.00    300.00     30 
 7521 WARD       SALESMAN   7698 1981/2/22     1250.00    500.00     30 
 7566 JONES      MANAGER    7839 1981/4/2      2975.00               20 
 7654 MARTIN     SALESMAN   7698 1981/9/28     1250.00   1400.00     30 
 7698 BLAKE      MANAGER    7839 1981/5/1      2850.00               30 
 7782 CLARK      MANAGER    7839 1981/6/9      2450.00               10 
 7788 SCOTT      ANALYST    7566 1987/4/19     3000.00               20 
 7839 KING       PRESIDENT       1981/11/17    5000.00               10 
 7844 TURNER     SALESMAN   7698 1981/9/8      1500.00      0.00     30 
 7876 ADAMS      CLERK      7788 1987/5/23     1100.00               20 
 7900 JAMES      CLERK      7698 1981/12/3      950.00               30 
 7902 FORD       ANALYST    7566 1981/12/3     3000.00               20 
 7934 MILLER     CLERK      7782 1982/1/23     1300.00               10 
14 rows selected

SQL> desc emp1;
--查看表结构
Name         Type         Nullable Default Comments 
------------ ------------ -------- ------- -------- 
EMPNO        NUMBER(4)    Y                         
ENAME        VARCHAR2(10) Y                         
JOB          VARCHAR2(9)  Y                         
MGR          NUMBER(4)    Y                         
HIREDATE     DATE         Y                         
SAL          NUMBER(7,2)  Y                         
COMM         NUMBER(7,2)  Y                         
DEPTNO       NUMBER(2)    Y                         
EVALUATETIME DATE         Y  
 
SQL> alter table emp1 add (city varchar2(10) , workscore number(4));
Table altered
--添加城市列、工作得分列
SQL> select * from emp1;
--查看现在数据的内容,内容没有变化,但看以发现表结构已经发生变化,多了两个列
EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO EVALUATETIME CITY       WORKSCORE
----- ---------- --------- ----- ----------- --------- --------- ------ ------------ ---------- ---------
 7369 SMITH      CLERK      7902 1980/12/17     800.00               20                         
 7499 ALLEN      SALESMAN   7698 1981/2/20     1600.00    300.00     30                         
 7521 WARD       SALESMAN   7698 1981/2/22     1250.00    500.00     30                         
 7566 JONES      MANAGER    7839 1981/4/2      2975.00               20                         
 7654 MARTIN     SALESMAN   7698 1981/9/28     1250.00   1400.00     30                         
 7698 BLAKE      MANAGER    7839 1981/5/1      2850.00               30                         
 7782 CLARK      MANAGER    7839 1981/6/9      2450.00               10                         
 7788 SCOTT      ANALYST    7566 1987/4/19     3000.00               20                         
 7839 KING       PRESIDENT       1981/11/17    5000.00               10                         
 7844 TURNER     SALESMAN   7698 1981/9/8      1500.00      0.00     30                         
 7876 ADAMS      CLERK      7788 1987/5/23     1100.00               20                         
 7900 JAMES      CLERK      7698 1981/12/3      950.00               30                         
 7902 FORD       ANALYST    7566 1981/12/3     3000.00               20                         
 7934 MILLER     CLERK      7782 1982/1/23     1300.00               10                         
14 rows selected
SQL> desc emp1;
--查看表结构
Name         Type         Nullable Default Comments 
------------ ------------ -------- ------- -------- 
EMPNO        NUMBER(4)    Y                         
ENAME        VARCHAR2(10) Y                         
JOB          VARCHAR2(9)  Y                         
MGR          NUMBER(4)    Y                         
HIREDATE     DATE         Y                         
SAL          NUMBER(7,2)  Y                         
COMM         NUMBER(7,2)  Y                         
DEPTNO       NUMBER(2)    Y                         
EVALUATETIME DATE         Y                         
CITY         VARCHAR2(10) Y                         
WORKSCORE    NUMBER(4)    Y    

2. 修改表中存在的列

(1)、语法
SQL> alter table 表名 modify (列名 数据类型 [,列名 数据类型] ...);

(2)、注意事项
可以增加字符类型的列宽度;
可以增加数字类型的列的宽度、精度;
减少列的宽度,需要该列的值为空;
改变某一列数据类型,需要该列的值为空;
如果改变某列的默认值,只会影响以后的操作。

(3)、实验演示

SQL> desc emp1;
Name         Type         Nullable Default Comments 
------------ ------------ -------- ------- -------- 
EMPNO        NUMBER(4)    Y                         
ENAME        VARCHAR2(10) Y                         
JOB          VARCHAR2(9)  Y                         
MGR          NUMBER(4)    Y                         
HIREDATE     DATE         Y                         
SAL          NUMBER(7,2)  Y                         
COMM         NUMBER(7,2)  Y                         
DEPTNO       NUMBER(2)    Y                         
EVALUATETIME DATE         Y                         
CITY         VARCHAR2(10) Y                         
WORKSCORE    NUMBER(4)    Y       
SQL> alter table emp1 modify (workscore number(10));
--增加列的宽度
Table altered
SQL> alter table emp1 modify (workscore varchar2(20));
--改变列的数据类型
Table altered

SQL> desc emp1;
--查询表结构
Name         Type         Nullable Default Comments 
------------ ------------ -------- ------- -------- 
EMPNO        NUMBER(4)    Y                         
ENAME        VARCHAR2(10) Y                         
JOB          VARCHAR2(9)  Y                         
MGR          NUMBER(4)    Y                         
HIREDATE     DATE         Y                         
SAL          NUMBER(7,2)  Y                         
COMM         NUMBER(7,2)  Y                         
DEPTNO       NUMBER(2)    Y                         
EVALUATETIME DATE         Y                         
CITY         VARCHAR2(10) Y                         
WORKSCORE    VARCHAR2(20) Y

SQL> alter table emp1 modify (workscore number(20));
--改变列的数据类型
Table altered

SQL> alter table emp1 modify (workscore number(4));
--减少列的宽度
Table altered

SQL> desc emp1;
--查询表结构
Name         Type         Nullable Default Comments 
------------ ------------ -------- ------- -------- 
EMPNO        NUMBER(4)    Y                         
ENAME        VARCHAR2(10) Y                         
JOB          VARCHAR2(9)  Y                         
MGR          NUMBER(4)    Y                         
HIREDATE     DATE         Y                         
SAL          NUMBER(7,2)  Y                         
COMM         NUMBER(7,2)  Y                         
DEPTNO       NUMBER(2)    Y                         
EVALUATETIME DATE         Y                         
CITY         VARCHAR2(10) Y                         
WORKSCORE    NUMBER(4)    Y       

SQL> alter table emp1 modify (job char(9));
--将列为varchar2类型变为char类型
Table altered

SQL> desc emp1;
Name         Type         Nullable Default Comments 
------------ ------------ -------- ------- -------- 
EMPNO        NUMBER(4)    Y                         
ENAME        VARCHAR2(10) Y                         
JOB          CHAR(9)      Y                         
MGR          NUMBER(4)    Y                         
HIREDATE     DATE         Y                         
SAL          NUMBER(7,2)  Y                         
COMM         NUMBER(7,2)  Y                         
DEPTNO       NUMBER(2)    Y                         
EVALUATETIME DATE         Y                         
CITY         VARCHAR2(10) Y                         
WORKSCORE    NUMBER(4)    Y                         

SQL> alter table emp1 modify (job varchar2(9));
--将列有char类型改回varchar2类型
Table altered

SQL> alter table emp1 modify (evaluatetime default sysdate);
--修改评估时间的默认值为sysdate
Table altered

SQL> alter table emp1 modify (hiredate default sysdate);
Table altered
--修改雇佣时间的默认值为sysdate

SQL> select * from emp1;
--改变了评估时间的默认值,但该列仍然为空,因为改变只针对修改后的插入的数据
EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO EVALUATETIME CITY       WORKSCORE
----- ---------- --------- ----- ----------- --------- --------- ------ ------------ ---------- ---------
 7369 SMITH      CLERK      7902 1980/12/17     800.00               20                         
 7499 ALLEN      SALESMAN   7698 1981/2/20     1600.00    300.00     30                         
 7521 WARD       SALESMAN   7698 1981/2/22     1250.00    500.00     30                         
 7566 JONES      MANAGER    7839 1981/4/2      2975.00               20                         
 7654 MARTIN     SALESMAN   7698 1981/9/28     1250.00   1400.00     30                         
 7698 BLAKE      MANAGER    7839 1981/5/1      2850.00               30                         
 7782 CLARK      MANAGER    7839 1981/6/9      2450.00               10                         
 7788 SCOTT      ANALYST    7566 1987/4/19     3000.00               20                         
 7839 KING       PRESIDENT       1981/11/17    5000.00               10                         
 7844 TURNER     SALESMAN   7698 1981/9/8      1500.00      0.00     30                         
 7876 ADAMS      SALESMAN   7788 1987/5/23     1100.00               20                         
 7900 JAMES      CLERK      7698 1981/12/3      950.00               30                         
 7902 FORD       ANALYST    7566 1981/12/3     3000.00               20                         
 7934 MILLER     CLERK      7782 1982/1/23     1300.00               10                         
14 rows selected

SQL> insert into emp1(empno,ename,job,mgr,hiredate,sal,comm,deptno,evaluatetime,city,workscore) values (8000,'HYL','DBA','7839','','5000','','10',default,'beijing','90');
--插入一条实验数据
1 row inserted
SQL> insert into emp1 (empno,ename,job,evaluatetime) values (8001,'HF','DBA',default);
1 row inserted
--再插入一条实验数据

SQL> select * from emp1;
--可以发现实验数据中的结果,两条默认值更改为sysdate的数据,在新插入数据时相应的列已经变为了系统时间
EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO EVALUATETIME CITY       WORKSCORE
----- ---------- --------- ----- ----------- --------- --------- ------ ------------ ---------- ---------
 7369 SMITH      CLERK      7902 1980/12/17     800.00               20                         
 7499 ALLEN      SALESMAN   7698 1981/2/20     1600.00    300.00     30                         
 7521 WARD       SALESMAN   7698 1981/2/22     1250.00    500.00     30                         
 7566 JONES      MANAGER    7839 1981/4/2      2975.00               20                         
 7654 MARTIN     SALESMAN   7698 1981/9/28     1250.00   1400.00     30                         
 7698 BLAKE      MANAGER    7839 1981/5/1      2850.00               30                         
 7782 CLARK      MANAGER    7839 1981/6/9      2450.00               10                         
 7788 SCOTT      ANALYST    7566 1987/4/19     3000.00               20                         
 7839 KING       PRESIDENT       1981/11/17    5000.00               10                         
 7844 TURNER     SALESMAN   7698 1981/9/8      1500.00      0.00     30                         
 7876 ADAMS      SALESMAN   7788 1987/5/23     1100.00               20                         
 7900 JAMES      CLERK      7698 1981/12/3      950.00               30                         
 7902 FORD       ANALYST    7566 1981/12/3     3000.00               20                         
 7934 MILLER     CLERK      7782 1982/1/23     1300.00               10                         
 8000 HYL        DBA        7839               5000.00               10 2014/10/3 19 beijing           90
 8001 HF         DBA             2014/10/3 1                            2014/10/3 19            
16 rows selected

3. 表中删除一个列

(1)、语法
SQL> alter table 表名 drop column 列名;

(2)、注意事项
一次只能删除一列;
所删除的列不是表中的唯一列;
删除列将无法回滚,所以删除前请确认无误再进行操作。

(3)、实验演示

SQL> select * from emp1;
--查询表中的数据,计划将列EVALUATETIME、CITY、WORKSCORE删除掉
EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO EVALUATETIME CITY       WORKSCORE
----- ---------- --------- ----- ----------- --------- --------- ------ ------------ ---------- ---------
 7369 SMITH      CLERK      7902 1980/12/17     800.00               20                         
 7499 ALLEN      SALESMAN   7698 1981/2/20     1600.00    300.00     30                         
 7521 WARD       SALESMAN   7698 1981/2/22     1250.00    500.00     30                         
 7566 JONES      MANAGER    7839 1981/4/2      2975.00               20                         
 7654 MARTIN     SALESMAN   7698 1981/9/28     1250.00   1400.00     30                         
 7698 BLAKE      MANAGER    7839 1981/5/1      2850.00               30                         
 7782 CLARK      MANAGER    7839 1981/6/9      2450.00               10                         
 7788 SCOTT      ANALYST    7566 1987/4/19     3000.00               20                         
 7839 KING       PRESIDENT       1981/11/17    5000.00               10                         
 7844 TURNER     SALESMAN   7698 1981/9/8      1500.00      0.00     30                         
 7876 ADAMS      SALESMAN   7788 1987/5/23     1100.00               20                         
 7900 JAMES      CLERK      7698 1981/12/3      950.00               30                         
 7902 FORD       ANALYST    7566 1981/12/3     3000.00               20                         
 7934 MILLER     CLERK      7782 1982/1/23     1300.00               10                         
 8000 HYL        DBA        7839               5000.00               10 2014/10/3 19 beijing           90
 8001 HF         DBA             2014/10/3 1                            2014/10/3 19            
16 rows selected
SQL> alter table emp1 drop column EVALUATETIME,CITY;
--尝试一次删除两个列,报错了
alter table emp1 drop column EVALUATETIME,CITY
ORA-00933: SQL command not properly ended

SQL> alter table emp1 drop column EVALUATETIME;
--删除EVALUATETIME列
Table altered

SQL> alter table emp1 drop column CITY;
--删除CITY列
Table altered

SQL> alter table emp1 drop column WORKSCORE;
--删除WORKSCORE列
Table altered

SQL> select * from emp1;
--再次查询,删除的三个列已经被成功删除了
EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7369 SMITH      CLERK      7902 1980/12/17     800.00               20
 7499 ALLEN      SALESMAN   7698 1981/2/20     1600.00    300.00     30
 7521 WARD       SALESMAN   7698 1981/2/22     1250.00    500.00     30
 7566 JONES      MANAGER    7839 1981/4/2      2975.00               20
 7654 MARTIN     SALESMAN   7698 1981/9/28     1250.00   1400.00     30
 7698 BLAKE      MANAGER    7839 1981/5/1      2850.00               30
 7782 CLARK      MANAGER    7839 1981/6/9      2450.00               10
 7788 SCOTT      ANALYST    7566 1987/4/19     3000.00               20
 7839 KING       PRESIDENT       1981/11/17    5000.00               10
 7844 TURNER     SALESMAN   7698 1981/9/8      1500.00      0.00     30
 7876 ADAMS      SALESMAN   7788 1987/5/23     1100.00               20
 7900 JAMES      CLERK      7698 1981/12/3      950.00               30
 7902 FORD       ANALYST    7566 1981/12/3     3000.00               20
 7934 MILLER     CLERK      7782 1982/1/23     1300.00               10
 8000 HYL        DBA        7839               5000.00               10
 8001 HF         DBA             2014/10/3 1                     
16 rows selected

4. 把表的列设置成无用(UNUSED)

(1)、语法
SQL> alter table 表名 set unused(列名);

SQL> alter table 表名 set unused column 列名;

SQL> drop table 表名 drop unused columns;

(2)、注意事项
    把表的列设置成unused是为了解决在业务高峰期的删除操作任务。因为在业务高峰期对数据量较大的某列执行删除操作的话,将影响系统的运行效率。此时我们就可以使用设置unused的方法临时性迂回的完成删除任务。待过了高峰期后,再手工使用drop命令删除原计划删除的列。因为设置unused只是做了一个删除的标记,实际的数据依然存在在数据库中,但是这样的数据是查询不到的,因此之后还需要手工删除。

(3)、实验演示

SQL> select * from emp1;
--先来看看表中的数据内容
EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7369 SMITH      CLERK      7902 1980/12/17     800.00               20
 7499 ALLEN      SALESMAN   7698 1981/2/20     1600.00    300.00     30
 7521 WARD       SALESMAN   7698 1981/2/22     1250.00    500.00     30
 7566 JONES      MANAGER    7839 1981/4/2      2975.00               20
 7654 MARTIN     SALESMAN   7698 1981/9/28     1250.00   1400.00     30
 7698 BLAKE      MANAGER    7839 1981/5/1      2850.00               30
 7782 CLARK      MANAGER    7839 1981/6/9      2450.00               10
 7788 SCOTT      ANALYST    7566 1987/4/19     3000.00               20
 7839 KING       PRESIDENT       1981/11/17    5000.00               10
 7844 TURNER     SALESMAN   7698 1981/9/8      1500.00      0.00     30
 7876 ADAMS      SALESMAN   7788 1987/5/23     1100.00               20
 7900 JAMES      CLERK      7698 1981/12/3      950.00               30
 7902 FORD       ANALYST    7566 1981/12/3     3000.00               20
 7934 MILLER     CLERK      7782 1982/1/23     1300.00               10
 8000 HYL        DBA        7839               5000.00               10
 8001 HF         DBA             2014/10/3 1                     
16 rows selected

SQL> alter table emp1 set unused (comm,sal);
--将表中要删除的两列设置为unused
Table altered

SQL> select * from emp1;
--删除两个列后,查询表的数据,你会发现这两个已经无法查看到
EMPNO ENAME      JOB         MGR HIREDATE    DEPTNO
----- ---------- --------- ----- ----------- ------
 7369 SMITH      CLERK      7902 1980/12/17      20
 7499 ALLEN      SALESMAN   7698 1981/2/20       30
 7521 WARD       SALESMAN   7698 1981/2/22       30
 7566 JONES      MANAGER    7839 1981/4/2        20
 7654 MARTIN     SALESMAN   7698 1981/9/28       30
 7698 BLAKE      MANAGER    7839 1981/5/1        30
 7782 CLARK      MANAGER    7839 1981/6/9        10
 7788 SCOTT      ANALYST    7566 1987/4/19       20
 7839 KING       PRESIDENT       1981/11/17      10
 7844 TURNER     SALESMAN   7698 1981/9/8        30
 7876 ADAMS      SALESMAN   7788 1987/5/23       20
 7900 JAMES      CLERK      7698 1981/12/3       30
 7902 FORD       ANALYST    7566 1981/12/3       20
 7934 MILLER     CLERK      7782 1982/1/23       10
 8000 HYL        DBA        7839                 10
 8001 HF         DBA             2014/10/3 1 
16 rows selected

SQL> desc emp1;
--即使查看表结构,也不会发现被设置为unused的两个列,因为目前对于数据库而言,这两个列已经是删除的列了
Name     Type         Nullable Default Comments 
-------- ------------ -------- ------- -------- 
EMPNO    NUMBER(4)    Y                         
ENAME    VARCHAR2(10) Y                         
JOB      CHAR(9)      Y                         
MGR      NUMBER(4)    Y                         
HIREDATE DATE         Y        sysdate          
DEPTNO   NUMBER(2)    Y     

SQL> alter table emp1 drop unused columns;
--之后手工删除掉emp1表中标记为unused的列的数据
Table altered

***********************************************声明*********************************************************************** 

原创作品,出自 “深蓝的blog” 博客,欢迎转载,转载时请务必注明出处,否则追究版权法律责任。

深蓝的blog:http://blog.csdn.net/huangyanlong/article/details/39758393

**************************************************************************************************************************** 

SQL_修改表结构

上一篇:SQL语法


下一篇:ORACLE列值合併