有時候，根據實際需求，後期還可以對錶與表的結構進行修改維護，包括表的命名與刪除，
欄位的命名，狀態的修改，欄位的增加與刪除等。

1、表 students欄位的管理：

SQL> desc students;

Name Null? Type

----------------------------------------- -------- ----------------------------

STUDENT_ID NOT NULL NUMBER(5)

NAME NOT NULL VARCHAR2(15)

DOB DATE

SEX VARCHAR2(6)

1》新增一列telpho：

SQL> alter table students add telpho number(11);

Table altered.

SQL> desc students;

Name Null? Type

----------------------------------------- -------- ----------------------------

STUDENT_ID NOT NULL NUMBER(5)

NAME NOT NULL VARCHAR2(15)

DOB DATE

SEX VARCHAR2(6)

TELPHO NUMBER(11)
2》刪除新增的列telpho：

SQL> alter table students drop column telpho;

Table altered.

SQL> desc students;

Name Null? Type

----------------------------------------- -------- ----------------------------

STUDENT_ID NOT NULL NUMBER(5)

NAME NOT NULL VARCHAR2(15)

DOB DATE

SEX VARCHAR2(6)

2、重新命名列名：

SQL> desc courses;

Name Null? Type

----------------------------------------- -------- ----------------------------

COURSE_ID NOT NULL NUMBER(5)

COURSE_NMAE NOT NULL VARCHAR2(30)

CREDIT_HOUR NUMBER(2)

CREDIT_HOURS NUMBER(3)

TERM VARCHAR2(4)

原來表courses的課程名的欄位命名錯了，以下為糾正列名的操作

SQL> alter table courses rename column

2 course_nmae to course_name;

Table altered.

SQL> desc courses;

Name Null? Type

----------------------------------------- -------- ----------------------------

COURSE_ID NOT NULL NUMBER(5)

COURSE_NAME NOT NULL VARCHAR2(30)

CREDIT_HOUR NUMBER(2)

CREDIT_HOURS NUMBER(3)

TERM VARCHAR2(4)

3、修改資料型別：
修改前

SQL> desc students;

Name Null? Type

----------------------------------------- -------- ----------------------------

STUDENT_ID NOT NULL NUMBER(5)

NAME NOT NULL VARCHAR2(15)

DOB DATE

SEX VARCHAR2(6)

TELPHO NUMBER(11)

修改後
SQL> alter table students modify telpho varchar2(11);

Table altered.

SQL> desc students;

Name Null? Type

----------------------------------------- -------- ----------------------------

STUDENT_ID NOT NULL NUMBER(5)

NAME NOT NULL VARCHAR2(15)

DOB DATE

SEX VARCHAR2(6)

TELPHO VARCHAR2(11)

4、重新命名錶名：

重新命名前

SQL> select tname from tab;

TNAME

------------------------------

COURSES

EMP

STUDENTS

S_GRADE

TEST

TOTAL

6 rows selected.

重新命名後
SQL> alter table total rename to totalnum;

Table altered.

SQL> select tname from tab;

TNAME

------------------------------

COURSES

EMP

STUDENTS

S_GRADE

TEST

TOTALNUM

6 rows selected.

5、刪除表：drop table table_name
1》普通刪除，刪除的表會先放到recyclebin

SQL> drop table TOTAL;

Table dropped.

SQL> select tname from tab;

TNAME

------------------------------

BIN$Pi+WSgUyFmPgUwEAAH9/yQ==$0 -----------刪除後的表名顯示格式

COURSES

EMP

STUDENTS

S_GRADE

TEST

6 rows selected.

2》徹底刪除，不放回recyclebin

SQL> drop table totalnum purge;

Table dropped.

SQL> select tname from tab;

TNAME

------------------------------

COURSES

EMP

STUDENTS

S_GRADE

TEST

表管理之三：表與表結構的管理

相關文章