如表:
create table TEST_FOR_CYCLE_TASK_MANAGER(
"ID" NUMBER not null,
"SITE" VARCHAR2(1024),
"ACCESS_DATE" VARCHAR2(16),
"LINECOUNT" NUMBER,
"VALID_LINECOUNT" NUMBER,
"ALL_404" NUMBER,
"GGBOT_404" NUMBER,
"DAY_IP_COUNT" NUMBER,
"ALL_IP_COUNT" NUMBER,
"BANDWIDTH" NUMBER,
"GGSEARCH_COUNT" NUMBER,
"GGSEARCH_WPERCENT" NUMBER,
"GGBOT_COUNT" NUMBER,
"GGBOT_WPERCENT" NUMBER,
"URL_COUNT" NUMBER,
"SPIDER_COUNT" NUMBER,
"NEW_SPIDER_COUNT" NUMBER
);
现需要将 access_date 转换为 日期格式 ,那么如下:
--增加日期临时列
alter table TEST_FOR_CYCLE_TASK_MANAGER add (date_temp date);
--修改临时日期列的数据为 access_date 字符转换而来的日期
update TEST_FOR_CYCLE_TASK_MANAGER
set date_temp = to_date(ACCESS_DATE,'yy-mm-dd');
--验证是否修改成功
select * from TEST_FOR_CYCLE_TASK_MANAGER;
--删除access_date列
alter table TEST_FOR_CYCLE_TASK_MANAGER drop column access_date
--修改列名 ALTER TABLE [方案名.]TABLE_NAME RENAME COLUMN OLD_COLUMN_NAME TO NEW_COLUMN_NAME
alter table TEST_FOR_CYCLE_TASK_MANAGER rename column date_temp to access_date
--确认列名修改成功
select * from TEST_FOR_CYCLE_TASK_MANAGER;
另外:查询一个表的列名及对应的数据类型:
select column_name,data_type from user_tab_columns where table_name='TEST_FOR_CYCLE_TASK_MANAGER'
ps:
1.修改数据表名
ALTER TABLE [方案名.]OLD_TABLE_NAME RENAME TO NEW_TABLE_NAME;
2.修改列名
ALTER TABLE [方案名.]TABLE_NAME RENAME COLUMN OLD_COLUMN_NAME TO NEW_COLUMN_NAME;
3.修改列的数据类型
ALTER TABLE [方案名.]TABLE_NAME MODIFY COLUMN_NAME NEW_DATATYPE;
4.插入列
ALTER TABLE [方案名.]TABLE_NAME ADD COLUMN_NAME DATATYPE;
5.删除列
ALTER TABLE [方案名.]TABLE_NAME DROP COLUMN COLUMN_NAME;