重命名表
重命名表的基本语法是：
代码如下 复制代码
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;