oracle 简单的增删改查

原创

sutiezheng 2013-01-23 22:38:45 ©著作权

文章标签 增删改查 文章分类 Oracle 数据库

©著作权归作者所有：来自51CTO博客作者sutiezheng的原创作品，请联系作者获取转载授权，否则将追究法律责任

1、创建电商和店面的两个表

create table dm (cp char(20),jg int, sj date);

create table wl (cp char(20),jg int, sj date);

2、向表内插入信息

insert into dm values ('a',2000,to_date('2012-11-11 11:11:11','YYYY-MM-DD HH24:MI:SS'));

insert into dm values ('b',3000,to_date('2012-12-11 11:11:11','YYYY-MM-DD HH24:MI:SS'));

insert into dm values ('b',3000,to_date('2012-11-11 11:11:11','YYYY-MM-DD HH24:MI:SS'));

insert into dm values ('a',2000,to_date('2012-12-11 11:11:11','YYYY-MM-DD HH24:MI:SS'));

insert into wl values ('a',1000,to_date('2012-11-11 11:11:11','YYYY-MM-DD HH24:MI:SS'));

insert into wl values ('b',200,to_date('2012-12-11 11:11:11','YYYY-MM-DD HH24:MI:SS'));

insert into wl values ('b',1000,to_date('2012-11-11 11:11:11','YYYY-MM-DD HH24:MI:SS'));

insert into wl values ('a',1000,to_date('2012-12-11 11:11:11','YYYY-MM-DD HH24:MI:SS'));

3、查看to_char格式的日期

select to_char(sj,'yyyy-mm-dd') from dm ;

select sum(jg) from dm where cp='a';

4、分别查看电商和店面的12和11月的销售总额

select sum(jg) from dm where substr(to_char(sj,'yyyy-mm-dd'),1,7)='2012-12';

select sum(jg) from wl where substr(to_char(sj,'yyyy-mm-dd'),1,7)='2012-12';

select sum(jg) from dm where substr(to_char(sj,'yyyy-mm-dd'),1,7)='2012-11';

select sum(jg) from wl where substr(to_char(sj,'yyyy-mm-dd'),1,7)='2012-11';

5、电商和店面的12月销售总额

select (select sum(jg) from dm where substr(to_char(sj,'yyyy-mm-dd'),1,7)='2012-12') + (select sum(jg) from wl where substr(to_char(sj,'yyyy-mm-dd'),1,7)

='2012-12') as hh from dual;

6、电商和店面的11月销售总额

select (select sum(jg) from dm where substr(to_char(sj,'yyyy-mm-dd'),1,7)='2012-11') + (select sum(jg) from wl where substr(to_char(sj,'yyyy-mm-dd'),1,7)

='2012-11') as 11yue from dual;

7、11月和12月相比销售的差额

select (select (select sum(jg) from dm where substr(to_char(sj,'yyyy-mm-dd'),1,7)='2012-12') + (select sum(jg) from wl where substr(to_char(sj,'yyyy-mm-

dd'),1,7)='2012-12') as hh from dual) - (select (select sum(jg) from dm where substr(to_char(sj,'yyyy-mm-dd'),1,7)='2012-11') + (select sum(jg) from wl where

substr(to_char(sj,'yyyy-mm-dd'),1,7)='2012-11') as yue from dual) as lirun from dual;