SQL_修改表结构

阅读量：7098 次

发布时间：2019-06-28

本文共 19387 字，大约阅读时间需要 64 分钟。

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

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

深蓝的blog：

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

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 selectedSQL> 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 selectedSQL> 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 alteredSQL> alter table emp1 modify (workscore varchar2(20));--改变列的数据类型Table alteredSQL> 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) YSQL> alter table emp1 modify (workscore number(20));--改变列的数据类型Table alteredSQL> alter table emp1 modify (workscore number(4));--降低列的宽度Table alteredSQL> 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 alteredSQL> 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 alteredSQL> alter table emp1 modify (evaluatetime default sysdate);--改动评估时间的默认值为sysdateTable alteredSQL> alter table emp1 modify (hiredate default sysdate);Table altered--改动雇佣时间的默认值为sysdateSQL> 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 selectedSQL> 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 insertedSQL> 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 selectedSQL> alter table emp1 drop column EVALUATETIME,CITY;--尝试一次删除两个列，报错了alter table emp1 drop column EVALUATETIME,CITYORA-00933: SQL command not properly endedSQL> alter table emp1 drop column EVALUATETIME;--删除EVALUATETIME列Table alteredSQL> alter table emp1 drop column CITY;--删除CITY列Table alteredSQL> alter table emp1 drop column WORKSCORE;--删除WORKSCORE列Table alteredSQL> 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 selectedSQL> alter table emp1 set unused (comm,sal);--将表中要删除的两列设置为unusedTable alteredSQL> 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 selectedSQL> 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：

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

系列链接_20150523：