(1)删掉表中的字段
alter table agb_groupbuy drop column discount_price
(2)添加一个字段
alter table agb_groupbuy_car add discount_price number(8, 2) default 0;
备注:
大表修改字段要加默认值 一般都是 先修改字段,然后第二步再多一条修改默认值,最后,再写存储过程,逐个更新旧数据
例如:
alter table agb_groupbuy_car add BUY_TYPE number(2,0) ;
alter table agb_groupbuy_car modify BUY_TYPE number(2,0) default 0;
(3)给一个字段添加注释
comment on column agb_groupbuy_car .discount_price is '优惠保障价';
(4)修改表中一个列的名字
alter table am_nissan_order rename column name2 to name;
(5)复制一个表或者备份一个表
create table am_test as select * from am_nissan_order
(6) 锁定某一个字段,在查询这个的时候,防止其他操作对该字段进行修改
select id,deleted from am_ele_code_storage where deleted=0 and rownum=1 for update of deleted
(7)只复制表结构
create table sss as select * from am_activity where 1=2;
(8)修改某个字段的值
update at_promotion_position set plat = 0;
(9)修改表中某个字段的默认值
alter table at_promotion_position modify plat number(2,0) default 0;
(10)删除同义词
drop synonym emp_ny;
(11)创建同义词(在数据库auto_groupbuy_app创建同义词ap_model,来源于数据库autoprice_nw_live的ap_model表)
create or replace synonym "auto_groupbuy_app"."ap_model" for autoprice_nw_live.ap_model;
(12)给同义词赋予权限
grant select on autoprice_nw_live.ap_dealer to auto_groupbuy_app
(13)添加约束
alter table am_activity_stock add constraint 库存量约束 check (stock_count>=0);
(14)创建索引
create index am_tuangou_dealer_indx1 on am_tuangou_dealer(am_tuangou_id);
(15)创建一个表
create table am_ele_code
(
id number not null primary key
, status number(1, 0)
, code varchar2(100 byte)
, order_id number
, am_activity_sg_id number
, used_dealer_id number
, use_time date
, update_time date
, create_time date
);
(16)插入一条数据
insert into sec_role values(2151,'商城研发','商城研发',1,'autogroupbuy',1);
insert into am_ele_code(id,status,code) values(100,0,'8899008899')