有时候，根据实际需求，后期还可以对表与表的结构进行修改维护，包括表的命名与删除，
字段的命名，状态的修改，字段的增加与删除等。

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

ITPub博客

表管理之三：表与表结构的管理

最新文章