|
---- -------- ------- --------
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
eva lUATETIME 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
eva lUATETIME 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
eva lUATETIME 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
eva lUATETIME 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 (eva luatetime 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 eva lUATETIME 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,eva luatetime,city,workscore) values (8000,'HYL','DBA','7839','','5000','','10',default,'beijing','90');
--插入一条实验数据
1 row inserted
SQL> insert into emp1 (empno,ename,job,eva luatetime) values (8001,'HF','DBA',default);
1 row inserted
--再插入一条实验数据
SQL> select * from emp1;
--可以发现实验数据中的结果，两条默认值更改为sysdate的数据，在新插入数据时相应的列已经变为了系统时间
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO eva lUATETIME CITY WORKSCORE
----- ---------- --------- ----- ----------- --------- --------- ------ ------------ --------- |