Oracle：列操作，增加索引、增加列、删除列和修改列

增加索引：

CREATE INDEX "DBNAME"."TABLENAME_COLUMNNAME" ON "DBNAME"."TABLENAME" ("COLUMNNAME") TABLESPACE "TABLESPACENAME";

CREATE INDEX db.tablename_columnname ON db.tablename (columnname) TABLESPACE abc;
           

增加、修改、删除列

-- 增加列
alter table table_name add column_name NVARCHAR2(16);

-- 修改列
alter table table_name modify column_name NUMBER(16, 0);

-- 删除列
alter table table_name drop column column_name;

-- 给列增加默认值
alter table table_name modify create_time default TO_CHAR(sysdate, 'yyyy-MM-dd HH24:mi:ss');

-- 修改列名
alter table table_name rename column column_name1 to column_name2;

-- 修改表名
alter table table_name1 rename to table_name2; 
           

不同于MySQL，Oracle中增加列无法指定列位置，只能增加到最后面。

查看表的列信息：

SELECT b.column_name AS column_name, b.data_type AS data_type, b.data_length, b.owner, a.comments
FROM all_tab_columns b
	LEFT JOIN all_col_comments a
	ON (b.table_name = a.table_name
		AND b.column_name = a.column_name
		AND b.owner = a.owner)
WHERE b.table_name = upper('table_name')
	AND b.column_name = upper('column_name');