ALTER TABLE 语句
ALTER TABLE 语句用于在已有的表中添加、修改或删除列。
SQL ALTER TABLE 语法
如需在表中添加列,请使用下列语法:
ALTER TABLE table_name ADD column_name datatype
要删除表中的列,请使用下列语法:
ALTER TABLE table_name DROP COLUMN column_name
注释:某些数据库系统不允许这种在数据库表中删除列的方式 (DROP COLUMN column_name)。
要改变表中列的数据类型,请使用下列语法:
ALTER TABLE table_name ALTER COLUMN column_name datatype
原始的表 (用在例子中的):
Persons 表:
Id | LastName | FirstName | Address | City |
---|---|---|---|---|
1 | Adams | John | Oxford Street | London |
2 | Bush | George | Fifth Avenue | New York |
3 | Carter | Thomas | Changan Street | Beijing |
SQL ALTER TABLE 实例
现在,我们希望在表 "Persons" 中添加一个名为 "Birthday" 的新列。
我们使用下列 SQL 语句:
ALTER TABLE Persons ADD Birthday date
请注意,新列 "Birthday" 的类型是 date,可以存放日期。数据类型规定列中可以存放的数据的类型。
新的 "Persons" 表类似这样:
Id | LastName | FirstName | Address | City | Birthday |
---|---|---|---|---|---|
1 | Adams | John | Oxford Street | London | |
2 | Bush | George | Fifth Avenue | New York | |
3 | Carter | Thomas | Changan Street | Beijing |
改变数据类型实例
现在我们希望改变 "Persons" 表中 "Birthday" 列的数据类型。
我们使用下列 SQL 语句:
ALTER TABLE Persons ALTER COLUMN Birthday year
请注意,"Birthday" 列的数据类型是 year,可以存放 2 位或 4 位格式的年份。
DROP COLUMN 实例
接下来,我们删除 "Person" 表中的 "Birthday" 列:
ALTER TABLE Person DROP COLUMN Birthday
Persons 表会成为这样:
Id | LastName | FirstName | Address | City |
---|---|---|---|---|
1 | Adams | John | Oxford Street | London |
2 | Bush | George | Fifth Avenue | New York |
3 | Carter | Thomas | Changan Street | Beijing |
1.如果表已经装载了大量数据应该是用视图来代替最好。
alter table TableNM add NewField varchar2(10)
rename TableNM to TableNMX
create or replace view TableNM
as
select Field1,Field2,NewField ,Field3 from TableNMX
2.不是很大的数据,可以
alter table TableNM rename column Field3 to NewField
alter table TableNM add Field3 .....
此方法涉及到Field3 ,NewField 的数据类型,数据字段间转移
3.小的数据量,倒可以考虑drop 再create
示例:
alter table INV_SALESSETTLEMENTDETAIL add
(realTimeQuantityKg number(13,2) default '0',
realTimeQuantityLiter number(13,2) default '0'
)
;
create table INV_SALESSETTLEMENTDETAIL1 as
select
"SALESDETAILGID",
"SETTLEMENTGID",
"OILARCHGID",
"ARCHNAME",
"ARCHSPEC",
"SALESTIME",
"LOADINGBILLGID",
"DENSITY",
"TINGID",
"SITENO",
"OUTAMOUNTKG",
"OUTAMOUNTLITER",
realTimeQuantityKg,
realTimeQuantityLiter,
"TAXRATE",
"SETTLEMENTPRICEKGTAX",
"BUSINESSMARK",
"SETTLEMENTPRICEKG",
"SETTLEMENTPRICELITERTAX",
"SETTLEMENTPRICELITER",
"OTHERMONEYAMOUNT",
"PAYMENTAMOUNT",
"REMARK",
"CREATETIME",
"CREATOR"
from
"TRANSPORTOIL"."INV_SALESSETTLEMENTDETAIL"
;
drop table INV_SALESSETTLEMENTDETAIL;
rename INV_SALESSETTLEMENTDETAIL1 to INV_SALESSETTLEMENTDETAIL;
1.oracle中创建一张表,写法与sql server中的一样。
SQL> create table Course
2 ( cno char(8),
3 cname varchar2(20),
4 ccredit int,
5 cpno char(8)
6 );
表已创建。
但是,在写的过程中,发现cno这列的属性少写了一个not null。于是试着用原来sql server中的修改列属性的写法试了下,发现不行。
SQL> alter table Course alter cno char(8) not null;
alter table Course alter cno char(8) not null
*
第 1 行出现错误:
ORA-01735: 无效的 ALTER TABLE 选项
网上找了下,oracle中,修改列的属性的写法有点不同。
SQL>alter table Course MODIFY(cno char(8) not null)
SQL> /
表已更改。
SQL> desc course
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
CNO NOT NULL CHAR(8)
CNAME VARCHAR2(20)
CCREDIT NUMBER(38)
CPNO CHAR(8)
2.如果要添加一列怎么写呢?
sql server中的写法是:
Alter table course cdept char(8);
在oralce中写的试了下,能够运行成功,如下:
SQL> alter table course
2 add cdept char(8);
表已更改。
查看下此时的表的结构,如下:
SQL> desc course;
名称 是否为空? 类型
----------------------------------------- -------- ---------------------
CNO NOT NULL CHAR(8)
CNAME VARCHAR2(20)
CCREDIT NUMBER(38)
CPNO CHAR(8)
CDEPT CHAR(8)
3.如果要删除一列要怎么写呢?
sql server中的写法大家应该挺熟悉的,如下:
Alter table Course drop cpno;
在oracle中试了下,有错误,说缺少关键字:
SQL> alter table course
2 drop cdept;
drop cdept
*
第 2 行出现错误:
ORA-00905: 缺失关键字
那么缺少的是什么关键字呢?网上找了下原因,好像是column。这个关键字,添加进去再试了下,发现就可以了:
SQL> alter table course
2 drop column cdept;
表已更改。
此时再看下表的结构,cdept列真的删除了。
以下几个也是在创建表后,修改表结构是比较常用的,这边不一个个试过了,给大家参考:
问题:使用scott登录Oracle以后,创建视图,提示“权限不够”,怎么解决?
回答:
这是因为scott这个帐户目前没有创建视图的权限。
解决方法为:
首先使用system帐户进行登录,其中“tigertiger”为安装Oracle时所指定的密码(可修改):
sqlplus system/tigertiger
然后执行:
grant create any view to scott
提示:授权成功。
执行:
exit
退出当前system帐户。
再使用sqlplus登录就可以创建视图了,如:
sqlplus scott/tigert
下面创建一个最简单视图:
create or replace view v1
as
select * from t1;