数据库常用操作

最新推荐文章于 2024-09-15 23:28:35 发布

LaoFeZhaZn

最新推荐文章于 2024-09-15 23:28:35 发布

阅读量97

点赞数

分类专栏：数据库常见操作文章标签： sql oracle

本文链接：https://blog.csdn.net/LaoFeZhaZn/article/details/108383424

版权

数据库常见操作专栏收录该内容

1 篇文章 0 订阅

订阅专栏

数据库操作

1.创建表

create table SCM_GROUP_PURCHASE(
  GROUP_PURCHASE_ID varchar(10) primary key,
  productp_id varchar(10) not null,
  start_time  date,
  end_time    date,
  purchase_quantity number(14,4),
  purchase_type number(1),
  modify_time date,
  modify_user_id varchar(50),
  delete_flag  number(1),
  delete_user_id varchar(50),
  delete_time date,
  spec_id varchar2(10),
  remark VARCHAR2(250),
  DEPT_ID varchar2(10),
  Class_Name varchar2(50)
 );
 comment on table SCM_GROUP_PURCHASE is '带量维护表';
 comment on column SCM_GROUP_PURCHASE.GROUP_PURCHASE_ID is 'GROUP_PURCHASE_ID';
 comment on column SCM_GROUP_PURCHASE.productp_id is '规格厂商代码';
 comment on column SCM_GROUP_PURCHASE.start_time is '开始日期';
 comment on column SCM_GROUP_PURCHASE.end_time  is '结束日期';
 comment on column SCM_GROUP_PURCHASE.purchase_quantity is '采购量';
 comment on column SCM_GROUP_PURCHASE.purchase_type is '带量类型（0省带量，1市带量）';
 comment on column SCM_GROUP_PURCHASE.modify_user_id is '修改人ID';
 comment on column SCM_GROUP_PURCHASE.modify_time is '修改时间';
 comment on column SCM_GROUP_PURCHASE.delete_flag is '作废标志';
 comment on column SCM_GROUP_PURCHASE.delete_user_id is '作废人';
 comment on column SCM_GROUP_PURCHASE.delete_time is '作废时间';
 comment on column SCM_GROUP_PURCHASE.spec_id is '规格id';
 comment on column SCM_GROUP_PURCHASE.remark is '备注';
 comment on column SCM_GROUP_PURCHASE.Class_Name is '分类';

2.加字段

alter table SCM_GROUP_PURCHASE add type varchar2(10);
comment on column SCM_GROUP_PURCHASE.type is '维护类型';

3.修改字段大小

 alter table SCM_GROUP_PURCHASE modify (SPEC_ID nvarchar2(500));

4.备份表

create table scm_stock_bak_20200903 as select * from scm_stock;
insert into scm_stock_bak_20200903 select * from scm_stock;

5.两个表关联更新字段

 update scm_stock a 
 set a.agent_id=(select max(b.agent_id) from scm_product_produce_agent b  where a.productp_id=b.productp_id )
 where exists(select b.agent_id from scm_product_produce_agent b where a.productp_id=b.productp_id) and a.agent_id is null;