关于oracle的相关基础语句
----给表的字段添加描述
COMMENT ON COLUMN 数据库名称.表名.表字段 IS '字段描述';
---设置自动增长
CREATE SEQUENCE emp_sequence
INCREMENT BY 1 -- 每次加几个
START WITH 1 -- 从1开始计数
NOMAXVALUE -- 不设置最大值
NOCYCLE -- 一直累加，不循环
NOCACHE -- 不建缓冲区
------给表增加一个字段
alter table 表名 add 字段名 数据类型（类型长度）;
------给表的某个字段设置默认值
update GOODSINFO set GOODSINFO.ISBATCH=1;
----删除表中的某个字段
ALTER TABLE 表名称 DROP COLUMN 删除的字段名称;
----删除表
drop table 表名
---修改字段类型
alter table tb modify (name nvarchar2(20));
----创建视图
create view V_GOODSINFO as 查询语句
---给字段0和1设置名字
select u.id,u.realname,U.SEX,
( case u.sex
when 1 then '男'
when 2 then '女'
else '空的'
END
) 性别
from users
u;
---按月分组
select to_char(exportDate,'yyyy-mm'),sum(amount) from table1 group
by to_char(exportDate,'yyyy-mm')
--按季度分组
select to_char(exportDate,'yyyy-Q'),sum(amount) from table1 group
by to_char(exportDate,'yyyy-Q')
--按年分组
select to_char(exportDate,'yyyy'),sum(amount) from table1 group by
to_char(exportDate,'yyyy');
--按月份统计分组
select
b.MATERIALname, --商品名称
b.GOODSPEC, -- 规格
b.SAFETYONE, --编码
a.OUTOFSTORAGETYPE, --出入库类型
sum(a.INNUM), --入库总数
sum(a.OUTNUM), --出库总数
sum(((select APRICE from UNMATERIAL where KEYID=a.MATERIAL_ID) * a.INNUM)) as inmoney, --入库总金额
sum(((select APRICE from UNMATERIAL where KEYID=a.MATERIAL_ID) * a.OUTNUM)) as outmoney, --出库总金额
to_char(a.CREDATE,'yyyy-mm')
from UNMATERIAL_Order a ,UNMATERIAL b
where to_char(a.CREDATE,'yyyy')=to_char(sysdate,'yyyy') and a.MATERIAL_id = b.MATERIAL_id
group by to_char(a.CREDATE,'yyyy-mm'),b.MATERIALname,b.GOODSPEC,b.SAFETYONE,a.OUTOFSTORAGETYPE
---- 将 数量、金额、根据十二个月进行统计
select
b.MATERIALname, --商品名称
b.GOODSPEC, -- 规格
b.SAFETYONE, --编码
a.UNMATERIAL_ID,
sum(decode(to_char(a.CREDATE,'mm'),'01',a.INNUM,0)) INNUM1,
sum(decode(to_char(a.CREDATE,'mm'),'02',a.INNUM,0)) INNUM2,
sum(decode(to_char(a.CREDATE,'mm'),'03',a.INNUM,0)) INNUM3,
sum(decode(to_char(a.CREDATE,'mm'),'04',a.INNUM,0)) INNUM4,
sum(decode(to_char(a.CREDATE,'mm'),'05',a.INNUM,0)) INNUM5,
sum(decode(to_char(a.CREDATE,'mm'),'06',a.INNUM,0)) INNUM6,
sum(decode(to_char(a.CREDATE,'mm'),'07',a.INNUM,0)) INNUM7,
sum(decode(to_char(a.CREDATE,'mm'),'08',a.INNUM,0)) INNUM8,
sum(decode(to_char(a.CREDATE,'mm'),'09',a.INNUM,0)) INNUM9,
sum(decode(to_char(a.CREDATE,'mm'),'10',a.INNUM,0)) INNUM10,
sum(decode(to_char(a.CREDATE,'mm'),'11',a.INNUM,0)) INNUM11,
sum(decode(to_char(a.CREDATE,'mm'),'12',a.INNUM,0)) INNUM12,
sum(decode(to_char(a.CREDATE,'mm'),'01',((select APRICE from UNMATERIAL where KEYID=a.MATERIAL_ID)* a.INNUM),0)) INMONEY1,
sum(decode(to_char(a.CREDATE,'mm'),'02',((select APRICE from UNMATERIAL where KEYID=a.MATERIAL_ID)* a.INNUM),0)) INMONEY2,
sum(decode(to_char(a.CREDATE,'mm'),'03',((select APRICE from UNMATERIAL where KEYID=a.MATERIAL_ID)* a.INNUM),0)) INMONEY3,
sum(decode(to_char(a.CREDATE,'mm'),'04',((select APRICE from UNMATERIAL where KEYID=a.MATERIAL_ID)* a.INNUM),0)) INMONEY4,
sum(decode(to_char(a.CREDATE,'mm'),'05',((select APRICE from UNMATERIAL where KEYID=a.MATERIAL_ID)* a.INNUM),0)) INMONEY5,
sum(decode(to_char(a.CREDATE,'mm'),'06',((select APRICE from UNMATERIAL where KEYID=a.MATERIAL_ID)* a.INNUM),0)) INMONEY6,
sum(decode(to_char(a.CREDATE,'mm'),'07',((select APRICE from UNMATERIAL where KEYID=a.MATERIAL_ID)* a.INNUM),0)) INMONEY7,
sum(decode(to_char(a.CREDATE,'mm'),'08',((select APRICE from UNMATERIAL where KEYID=a.MATERIAL_ID)* a.INNUM),0)) INMONEY8,
sum(decode(to_char(a.CREDATE,'mm'),'09',((select APRICE from UNMATERIAL where KEYID=a.MATERIAL_ID)* a.INNUM),0)) INMONEY9,
sum(decode(to_char(a.CREDATE,'mm'),'10',((select APRICE from UNMATERIAL where KEYID=a.MATERIAL_ID)* a.INNUM),0)) INMONEY10,
sum(decode(to_char(a.CREDATE,'mm'),'11',((select APRICE from UNMATERIAL where KEYID=a.MATERIAL_ID)* a.INNUM),0)) INMONEY11,
sum(decode(to_char(a.CREDATE,'mm'),'12',((select APRICE from UNMATERIAL where KEYID=a.MATERIAL_ID)* a.INNUM),0)) INMONEY12
from UNMATERIAL_Order a ,UNMATERIAL b
where to_char(a.CREDATE,'yyyy')=to_char(sysdate,'yyyy') and a.MATERIAL_id = b.MATERIAL_id and a.OUTOFSTORAGETYPE='入库'
group by to_char(a.CREDATE,'yyyy-mm'),b.MATERIALname,b.GOODSPEC,b.SAFETYONE,a.UNMATERIAL_ID
