#字段级变更
#新增字段
ALTER TABLE TABLE_NAME ADD email VARCHAR(255);
#验证
SELECT COLUMN_NAME, DATA_TYPE, DATA_LENGTH FROM USER_TAB_COLUMNS WHERE TABLE_NAME = 'TABLE_NAME';
#删除字段
ALTER TABLE TABLE_NAME DROP COLUMN email;
#验证
SELECT COLUMN_NAME, DATA_TYPE, DATA_LENGTH FROM USER_TAB_COLUMNS WHERE TABLE_NAME = 'TABLE_NAME';
#字段类型修改
ALTER TABLE TABLE_NAME MODIFY age FLOAT;
#验证
SELECT COLUMN_NAME, DATA_TYPE, DATA_LENGTH FROM USER_TAB_COLUMNS WHERE TABLE_NAME = 'TABLE_NAME';
#字段长度修改
ALTER TABLE TABLE_NAME MODIFY NAME VARCHAR(64);
#字段名称修改
ALTER TABLE TABLE_NAME RENAME COLUMN NAME TO USERNAME;
#添加字段注释
COMMENT ON COLUMN TABLE_NAME.USERNAME IS '用户名';
#验证,获取字段注释
select * from user_col_comments where TABLE_NAME='TABLE_NAME';
#增加非空约束
ALTER TABLE TABLE_NAME MODIFY USERNAME VARCHAR(64) NOT NULL;
#验证
SELECT COLUMN_NAME, DATA_TYPE, DATA_LENGTH, NULLABLE FROM USER_TAB_COLUMNS WHERE TABLE_NAME = 'TABLE_NAME';
#删除非空约束
ALTER TABLE TABLE_NAME MODIFY USERNAME VARCHAR(64) NULL;
#验证
SELECT COLUMN_NAME, DATA_TYPE, DATA_LENGTH, NULLABLE FROM USER_TAB_COLUMNS WHERE TABLE_NAME = 'TABLE_NAME';
#增加默认值
ALTER TABLE TABLE_NAME MODIFY USERNAME VARCHAR(64) DEFAULT 'MYDEFULT';
#验证
select COLUMN_NAME, DATA_TYPE,DATA_DEFAULT from USER_TAB_COLUMNS WHERE TABLE_NAME = 'TABLE_NAME';
#删除默认值
ALTER TABLE TABLE_NAME MODIFY USERNAME VARCHAR(64) DEFAULT NULL;
#删除数据
delete from TABLE_NAME where id=1;
#.新增数据
insert into TABLE_NAME(id,USERNAME,age) values(1,'User1',40),(2,'UserName2',60)
#验证
select * from TABLE_NAME;
#.修改数据
update TABLE_NAME set USERNAME='UPDATE_BY_ME' where id = 2;
#表级变更
#添加主键约束
alter table TABLE_NAME add constraint TABLE_NAME_PK primary key ( ID );
#检查主键是否创建成功
select * from user_constraints where TABLE_NAME='TABLE_NAME' and CONSTRAINT_TYPE='P';
#删除主键约束
ALTER TABLE TABLE_NAME DROP CONSTRAINT CONS134219385;
#检查外键是否创建成功
select * from user_constraints where TABLE_NAME='TABLE_NAME' and CONSTRAINT_TYPE='P';
#添加外键约束
ALTER TABLE TABLE_NAME ADD CONSTRAINT TABLE_NAME_ID_FKEY FOREIGN KEY (PID) REFERENCES TABLE_NAME(ID);
#检查外键是否创建成功
select * from user_constraints where TABLE_NAME='TABLE_NAME' and CONSTRAINT_TYPE='R';
#删除外键约束
ALTER TABLE TABLE_NAME DROP CONSTRAINT TABLE_NAME_ID_FKEY;
#检查外键是否删除
select * from user_constraints where TABLE_NAME='TABLE_NAME' and CONSTRAINT_TYPE='R';
#添加索引
CREATE INDEX NAME_IDX ON TABLE_NAME(NAME);
#查询已经创建的索引
select * from user_indexes where TABLE_NAME='TABLE_NAME' and INDEX_NAME='NAME_IDX';
#删除索引
DROP INDEX NAME_IDX;
#验证是否删除
select * from user_indexes where TABLE_NAME='TABLE_NAME' and INDEX_NAME='NAME_IDX';
#修改表名
ALTER TABLE TABLE_NAME RENAME TO TABLE_NAME_1;
#验证是否修改(通过查询当前用户的表)
select table_name from user_tables;
#删除表
(先复制一张表:create table TABLE_NAME like TABLE_NAME_1;)
drop table TABLE_NAME_1;
#验证是否删除(通过查询当前用户的表)
select table_name from user_tables;
#添加表注释
COMMENT ON TABLE TABLE_NAME IS '人员信息主表';
#验证注释是否添加成功
select * from user_tab_comments;
#清空表
(先插入两行:insert into TABLE_NAME(id,name,age) values(1,'User1',40),(2,'UserName2',60);)
TRUNCATE TABLE TABLE_NAME;
#验证
select count(1) from TABLE_NAME;
参考: