首页 > 代码库 > SQL_修改表结构
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 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：http://blog.csdn.net/huangyanlong/article/details/39758393
****************************************************************************************************************************
SQL_修改表结构
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。