***********************************************声明***********************************************************************
原创作品,出自 “深蓝的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;
--查看如今数据的内容。内容没有变化,但看以发现表结构已经发生变化。多了两个列
<span style="font-size:10px;">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
</span>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
****************************************************************************************************************************
系列链接_20150523:
蓝的成长记——追逐DBA(2):安装!安装!久违的记忆,引起我对DBA的又一次认知
蓝的成长记——追逐DBA(3):古董上操作,数据导入导出成了问题
蓝的成长记——追逐DBA(4):追忆少年情愁。再探oracle安装(Linux下10g、11g)
蓝的成长记——追逐DBA(5):不谈技术谈业务,恼人的应用系统
蓝的成长记——追逐DBA(6): 做事与做人:小技术。大为人
蓝的成长记——追逐DBA(8):重拾SP报告。回顾oracle的STATSPACK实验
蓝的成长记— —追逐DBA(9):国庆渐去,追逐DBA,新规划,新启程
蓝的成长记——追逐DBA(10):飞刀防身。熟络而非专长:摆弄中间件Websphere
蓝的成长记——追逐DBA(11):回家后的安逸,晕晕乎乎醒了过来
蓝的成长记——追逐DBA(13):协调硬件厂商。六个故事:所见所感的“server、存储、交换机......”
蓝的成长记——追逐DBA(14):难忘的“云”端,起步的hadoop部署
蓝的成长记——追逐DBA(15):以为FTP非常“简单”,谁成想一波三折
版权声明:本文博客原创文章。博客,未经同意,不得转载。