sql alter table语句用法

ALTER TABLE语句允许您重命名一个现有的表。它也可以被用于从现有的表添加，修改或删除一列,下面我们来看看alter语句的多种用法吧。

重命名表
重命名表的基本语法是：

代码如下	复制代码
ALTER TABLE table_name  RENAME TO new_table_name; For example: ALTER TABLE suppliers

这将重命名的供应商表供应商。

 

表中添加列（S）
语法＃1

要添加到现有的表列，ALTER TABLE的语法是：

代码如下	复制代码
ALTER TABLE table_name  ADD column_name column-definition; For example: ALTER TABLE supplier  ADD supplier_name  varchar2(50);

这会增加供应商表中的列称为supplier_name。

 

语法＃2

要添加到现有表的多个列，ALTER TABLE的语法是：

代码如下	复制代码
ALTER TABLE table_name ADD ( column_1 column-definition,   column_2 column-definition,   ...    column_n column_definition ); For example: ALTER TABLE supplier ADD ( supplier_name varchar2(50),   city varchar2(45) );

这将增加两列（supplier_name市）的供应商表。

 

修改表中的列（S）
语法＃1

要修改现有表列，ALTER TABLE的语法是：

代码如下	复制代码
ALTER TABLE table_name  MODIFY column_name column_type; For example: ALTER TABLE supplier  MODIFY supplier_name   varchar2(100)     not null;

这将修改所谓supplier_name，是一个VARCHAR2数据类型（100），并迫使列不允许空值的列。

 

语法＃2

要修改现有表中的多个列，ALTER TABLE的语法是：

代码如下	复制代码
ALTER TABLE table_name MODIFY ( column_1 column_type,   column_2 column_type,   ...    column_n column_type ); For example: ALTER TABLE supplier MODIFY ( supplier_name varchar2(100) not null,  city varchar2(75)   );

这将修改supplier_name和城市列。

 

（S）在一个表中删除列
语法＃1

要删除一个现有的表列，ALTER TABLE的语法是：

代码如下	复制代码
ALTER TABLE table_name  DROP COLUMN column_name; For example: ALTER TABLE supplier  DROP COLUMN supplier_name;

这将下降supplier_name名为供应商的表列。

 

在表重命名列（S）
（新在Oracle 9i第2版）
语法＃1

在Oracle9i第2版开始，你现在可以重命名列。

要在现有的表重命名列，ALTER TABLE的语法是：

代码如下	复制代码
ALTER TABLE table_name  RENAME COLUMN old_name to new_name; For example: ALTER TABLE supplier  RENAME COLUMN supplier_name to sname;

This will rename the column called supplier_name to sname.

Acknowledgements: Thanks to Dave M., Craig A., and Susan W. for contributing to this solution!

 

Practice Exercise #1:

Based on the departments table below, rename the departments table to depts.

代码如下	复制代码
CREATE TABLE departments ( department_id number(10) not null,   department_name varchar2(50) not null,   CONSTRAINT departments_pk PRIMARY KEY (department_id) );

 

Solution:

The following ALTER TABLE statement would rename the departments table to depts:

代码如下	复制代码
ALTER TABLE departments  RENAME TO depts;

Practice Exercise #2:

Based on the employees table below, add a column called salary that is a number(6) datatype.

代码如下	复制代码
CREATE TABLE employees ( employee_number number(10) not null,   employee_name varchar2(50) not null,   department_id number(10),    CONSTRAINT employees_pk PRIMARY KEY (employee_number) );

Solution:

The following ALTER TABLE statement would add a salary column to the employees table:

代码如下	复制代码
ALTER TABLE employees  ADD salary number(6);

Practice Exercise #3:

Based on the customers table below, add two columns - one column called contact_name that is a varchar2(50) datatype and one column called last_contacted that is a date datatype.

代码如下	复制代码
CREATE TABLE customers ( customer_id number(10) not null,   customer_name varchar2(50) not null,   address varchar2(50),    city varchar2(50),    state varchar2(25),    zip_code varchar2(10),    CONSTRAINT customers_pk PRIMARY KEY (customer_id) );

Solution:

The following ALTER TABLE statement would add the contact_name and last_contacted columns to the customers table:

代码如下	复制代码
ALTER TABLE customers ADD ( contact_name varchar2(50),   last_contacted date );

Practice Exercise #4:

Based on the employees table below, change the employee_name column to a varchar2(75) datatype.

代码如下	复制代码
CREATE TABLE employees ( employee_number number(10) not null,   employee_name varchar2(50) not null,   department_id number(10),    CONSTRAINT employees_pk PRIMARY KEY (employee_number) );

 

Solution:

The following ALTER TABLE statement would change the datatype for the employee_name column to varchar2(75):

代码如下	复制代码
ALTER TABLE employees  MODIFY employee_name varchar2(75);

Practice Exercise #5:

Based on the customers table below, change the customer_name column to NOT allow null values and change the state column to a varchar2(2) datatype.

代码如下	复制代码
CREATE TABLE customers ( customer_id number(10) not null,   customer_name varchar2(50),     address varchar2(50),    city varchar2(50),    state varchar2(25),    zip_code varchar2(10),    CONSTRAINT customers_pk PRIMARY KEY (customer_id) );

Solution:

The following ALTER TABLE statement would modify the customer_name and state columns accordingly in the customers table:

代码如下	复制代码
ALTER TABLE customers MODIFY ( customer_name varchar2(50) not null,   state varchar2(2) );

Practice Exercise #6:

Based on the employees table below, drop the salary column.

代码如下	复制代码
CREATE TABLE employees ( employee_number number(10) not null,   employee_name varchar2(50) not null,   department_id number(10),    salary number(6),    CONSTRAINT employees_pk PRIMARY KEY (employee_number) );

Solution:

The following ALTER TABLE statement would drop the salary column from the employees table:

代码如下	复制代码
ALTER TABLE employees  DROP COLUMN salary;

Practice Exercise #7:

Based on the departments table below, rename the department_name column to dept_name.

代码如下	复制代码
CREATE TABLE departments ( department_id number(10) not null,   department_name varchar2(50) not null,   CONSTRAINT departments_pk PRIMARY KEY (department_id) );

解决方案：

下面的ALTER TABLE语句将重新命名department_name列dept_name部门表：

代码如下	复制代码
ALTER TABLE departments  RENAME COLUMN department_name to dept_name;