Oracle中修改表结构的SQL语句是什么

在oracle中，可以使用“ALTER TABLE”语句来修改表结构，该语句可以通过增加或删减列、更改原有列类型、重新命名列或表等方式来改变原有表的结构，语法“ALTER TABLE 表名 [修改选项]”。ALTER TABLE 表名 [修改选项]

本教程操作环境：Windows7系统、Oracle 11g版、Dell G3电脑。

在oracle中，可以使用“ALTER TABLE”语句来修改表结构。

ALTER TABLE 语句可以改变原有表的结构，例如增加或删减列、更改原有列类型、重新命名列或表等。

ALTER TABLE 语句的语法：

ALTER TABLE 表名 [修改选项]

在上面的语句中，

• 首先，指定要修改的表名称。

• 其次，指出想在表名称后执行的修改操作。

ALTER TABLE语句可用来：

• 添加一个或多个列
• 修改列定义
• 删除一列或多列
• 重命名列
• 重命名表

下面来看看一些例子来了解每个操作的工作原理。

Oracle ALTER TABLE示例

我们将使用在上一个教程中创建的persons表进行演示。

-- 12c语法
CREATE TABLE persons(
    person_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
    first_name VARCHAR2(50) NOT NULL,
    last_name VARCHAR2(50) NOT NULL,
    PRIMARY KEY(person_id)
);
    

1. 添加列的例子

要将新列添加到表中，请使用以下语法：

ALTER TABLE table_name
ADD column_name type constraint;
    

例如，以下语句将一个名为birthdate的新列添加到persons表中：

ALTER TABLE persons 
ADD birthdate DATE NOT NULL;
    

如果查看persons表格，会看到birthdate日期列被添加到列列表的末尾：

DESC persons;
     

Name       Null     Type         
---------- -------- ------------ 
PERSON_ID  NOT NULL NUMBER       
FIRST_NAME NOT NULL VARCHAR2(50) 
LAST_NAME  NOT NULL VARCHAR2(50) 
BIRTHDATE  NOT NULL DATE

要同时向表中添加多列，请按如下所示将新列置于括号内：

ALTER TABLE table_name
ADD (
    column_name type constraint,
    column_name type constraint,
    ...
);
    

看下面的例子：

ALTER TABLE persons 
ADD (
    phone VARCHAR(20),
    email VARCHAR(100)
);
    

在这个例子中，语句在users表中添加了两个名为phone和email的新列。

DESC persons

Name       Null     Type          
---------- -------- ------------- 
PERSON_ID  NOT NULL NUMBER        
FIRST_NAME NOT NULL VARCHAR2(50)  
LAST_NAME  NOT NULL VARCHAR2(50)  
BIRTHDATE  NOT NULL DATE          
PHONE               VARCHAR2(20)  
EMAIL               VARCHAR2(100)

2. 修改列的属性的示例

要修改列的属性，请使用以下语法：

ALTER TABLE table_name
  MODIFY column_name type constraint;
    

例如，下面的语句将birthdate列更改为一个空列：

ALTER TABLE persons MODIFY birthdate DATE NULL;
    

我们再次验证persons表结构：

DESC persons

Name       Null     Type          
---------- -------- ------------- 
PERSON_ID  NOT NULL NUMBER        
FIRST_NAME NOT NULL VARCHAR2(50)  
LAST_NAME  NOT NULL VARCHAR2(50)  
BIRTHDATE           DATE          
PHONE               VARCHAR2(20)  
EMAIL               VARCHAR2(100)

正如所看到的，birthdate日期变得无能为力。

要修改多个列，请使用以下语法：

 ALTER TABLE table_name
  MODIFY ( column_1 type constraint,
          column_1 type constraint,
         ...);
    

例如，以下语句将phone和email列更改为NOT NULL列，并将email列的长度扩展为255个字符：

ALTER TABLE persons MODIFY(
    phone VARCHAR2(20) NOT NULL,
    email VARCHAR2(255) NOT NULL
);
    

再次验证persons表结构：

DESC persons;
    

Name       Null     Type          
---------- -------- ------------- 
PERSON_ID  NOT NULL NUMBER        
FIRST_NAME NOT NULL VARCHAR2(50)  
LAST_NAME  NOT NULL VARCHAR2(50)  
BIRTHDATE           DATE          
PHONE      NOT NULL VARCHAR2(20)  
EMAIL      NOT NULL VARCHAR2(255)

3. 删除现有的列的示例

要从表中删除现有的列，请使用以下语法：

ALTER TABLE table_name
DROP COLUMN column_name;
    

该语句从表结构中删除列，并删除存储在该列中的数据。以下示例从persons表中删除birthdate日期类型的列：

ALTER TABLE persons
DROP
  COLUMN birthdate;
    

再次查看persons表结构，就会发现birthdate列已被删除：

DESC persons;
    

Name       Null     Type          
---------- -------- ------------- 
PERSON_ID  NOT NULL NUMBER        
FIRST_NAME NOT NULL VARCHAR2(50)  
LAST_NAME  NOT NULL VARCHAR2(50)  
PHONE      NOT NULL VARCHAR2(20)  
EMAIL      NOT NULL VARCHAR2(255)

要同时删除多个列，请使用以下语法：

ALTER TABLE table_name 
DROP (column_1,column_2,...);
    

例如，以下语句从个人表中删除phone和email列：

ALTER TABLE persons
DROP
  ( email, phone );
    

我们再来看一下persons表：

DESC persons;
    

Name       Null     Type         
---------- -------- ------------ 
PERSON_ID  NOT NULL NUMBER       
FIRST_NAME NOT NULL VARCHAR2(50) 
LAST_NAME  NOT NULL VARCHAR2(50)

email和phone列已按预期删除。

4. 重命名列的示例

从版本9i开始，Oracle添加了一个用于重命名列的子句，如下所示：

ALTER TABLE table_name
RENAME COLUMN column_name TO new_name;
    

例如，以下语句将first_name列重命名为forename列：

ALTER TABLE persons 
RENAME COLUMN first_name TO forename;
    

以下语句检查结果：

DESC persons;
    

Name      Null     Type         
--------- -------- ------------ 
PERSON_ID NOT NULL NUMBER       
FORENAME  NOT NULL VARCHAR2(50) 
LAST_NAME NOT NULL VARCHAR2(50)

5. 重命名表的例子

要将一个表重命名为一个新的name的表名，使用下面的语法：

ALTER TABLE table_name
RENAME TO new_table_name;
    

例如，下面的语句将users表重命名为people表：

ALTER TABLE persons RENAME TO people;