Oracle数据库知识积累
Oracle 数据库使用SQL语句
表主键自动生成
创建序列:
-- Create sequence
create sequence BXBILL_PK_SEQUENCE
minvalue 1
maxvalue 9999999999999999999999999999
start with 1
increment by 1
cache 20;
创建触发器
create or replace trigger bxbill_pk_tg
before insert
on eam_bxbill
for each row
declare
-- local variables here
begin
select BXBILL_PK_SEQUENCE.NEXTVAL into:new.bxbill_pk from dual;
end ;
insert插入语句
insert into eam_bxbill(BXBILL_DJBH,BXBILL_ZCZZ,BXBILL_SSDW,AMCARDID,BXBILL_COMP,BXBILL_SFJDCL)
values('20181008001','保定善美','保定善美卫生服务有限公司','bssm201809290001','河北保定','是')
表行值创建时间自动生成
在creattime字段，设置为date类型，将default设置为sysdate；
表行值更新时间自动生成
增加触发器：
create or replace trigger update_tg
before insert or update on EAM_bxbill for each row
begin
select sysdate into:new.update_datetime from dual;
end;
其中EAM_bxbill 为要处理的表名， update_datetime 为更新字段；
创建视图
CREATE OR REPLACE VIEW CHENBEN AS
SELECT
COST_ANALYSIS.year 年份,
COST_ANALYSIS.month 月份,
COST_ANALYSIS.center_code 中心项目编码,
project_base_info.center_name 中心项目名称,
COST_ANALYSIS.procom_code 项目公司编码,
project_base_info.procom_name 项目公司名称,
COST_ANALYSIS.prodep_code 项目部编码,
project_base_info.prodep_name 项目部名称,
COST_ANALYSIS.income_rec 应收收入_不含税,
COST_ANALYSIS.income_pay 实收收入_不含税,
COST_ANALYSIS.headcount 总人数,
COST_ANALYSIS.per_capital_output 人均产值,
COST_ANALYSIS.manufa_gz 制造费用_工资,
COST_ANALYSIS.manufa_jbf 制造费用_加班费,
COST_ANALYSIS.manufa_wxy_syx_gzzrx 制造费用_五险一金,
COST_ANALYSIS.manufa_zgflf 制造费用_职工福利费,
COST_ANALYSIS.manufa_zjf 制造费用_折旧费,
COST_ANALYSIS.manufa_txf 制造费用_摊销费,
COST_ANALYSIS.manufa_zlf 制造费用_租赁费,
COST_ANALYSIS.manufa_ylf 制造费用_油料费,
COST_ANALYSIS.manufa_xlf 制造费用_修理费,
COST_ANALYSIS.manufa_bxftx 制造费用_保险费_摊销,
COST_ANALYSIS.manufa_njf 制造费用_年检费,
COST_ANALYSIS.manufa_lqf 制造费用_路桥费,
COST_ANALYSIS.manufa_txf2 制造费用_通讯费,
COST_ANALYSIS.manufa_sf 制造费用_水费,
COST_ANALYSIS.manufa_df 制造费用_电费,
COST_ANALYSIS.manufa_clf_zyyp 制造费用_材料费_作业用品,
COST_ANALYSIS.manufa_clf_wxbj 制造费用_材料费_维修备件,
COST_ANALYSIS.manufa_lbyp 制造费用_劳保用品,
COST_ANALYSIS.manufa_ljqyfy 制造费用_垃圾清运费用,
COST_ANALYSIS.manufa_lwf 制造费用_劳务费,
COST_ANALYSIS.manufa_tmcfy 制造费用_填埋场费用,
COST_ANALYSIS.manufa_sgpc 制造费用_事故赔偿,
COST_ANALYSIS.manufa_ghjf 制造费用_工会经费,
COST_ANALYSIS.manufa_qt 制造费用_其他,
COST_ANALYSIS.manufa_qtdbz 制造费用_其他的备注,
COST_ANALYSIS.manufa_yycbhj 营业成本合计,
COST_ANALYSIS.admicost_gz 管理费用_工资,
COST_ANALYSIS.admicost_jbf 管理费用_加班费,
COST_ANALYSIS.admicost_wxyj_syx_gzzrx 管理费用_五险金,
COST_ANALYSIS.admicost_zgflf 管理费用_职工福利费,
COST_ANALYSIS.admicost_zjf 管理费用_折旧费,
COST_ANALYSIS.admicost_txf 管理费用_摊销费 ,
COST_ANALYSIS.admicost_zlf 管理费用_租赁费,
COST_ANALYSIS.admicost_ylf 管理费用_油料费,
COST_ANALYSIS.admicost_wxf 管理费用_维修费,
COST_ANALYSIS.admicost_bxftx 管理费用_保险费_摊销,
COST_ANALYSIS.admicost_njf 管理费用_年检费,
COST_ANALYSIS.admicost_lqf 管理费用_路桥费,
COST_ANALYSIS.admicost_txf2 管理费用_通讯费,
COST_ANALYSIS.admicost_sf 管理费用_水费,
COST_ANALYSIS.admicost_df 管理费用_电费,
COST_ANALYSIS.admicost_bgf 管理费用_办公费,
COST_ANALYSIS.admicost_yyzdf 管理费用_运营招待费,
COST_ANALYSIS.admicost_clf 管理费用_差旅费,
COST_ANALYSIS.admicost_jtf 管理费用_交通费,
COST_ANALYSIS.admicost_hyf 管理费用_会议费,
COST_ANALYSIS.admicost_ydf 管理费用_邮电费,
COST_ANALYSIS.admicost_ghjf 管理费用_工会经费,
COST_ANALYSIS.admicost_qt 管理费用_其他,
COST_ANALYSIS.admicost_qtdbz 管理费用_其他的备注,
COST_ANALYSIS.admicost_sum 管理费用合计,
COST_ANALYSIS.totalcost_month 本月总成本_不含税,
COST_ANALYSIS.expenses_lastyear 月总成本中上年计入本年费用合计,
COST_ANALYSIS.market_cost 市场_商务费用,
COST_ANALYSIS.taxes 税金及附加,
COST_ANALYSIS.assets_loss 资产减值损失,
COST_ANALYSIS.financial_cost 财务费用,
COST_ANALYSIS.income_tax 所得税,
COST_ANALYSIS.gross_profit 毛利润,
COST_ANALYSIS.gross_profit_margin 毛利润率,
COST_ANALYSIS.retained_profit 净利润,
COST_ANALYSIS.retained_profit_margin 净利润率,
COST_ANALYSIS.Report_notes 报表备注
FROM COST_ANALYSIS join project_base_info on COST_ANALYSIS.PRODEP_CODE=project_base_info.prodep_code
with read only;
创建索引
单一索引:Create Index <Index-Name> On <Table_Name>(Column_Name);
create index INFOR_INDEX on PROJECT_BASE_INFO (USER_CODE)
select * from COST_ANALYSIS
where PROCOM_CODE ='0902'
0.226秒；（建立索引前）
0.170秒；(建立索引后)
create index cost_index on COST_ANALYSIS(PROCOM_CODE)
授权表用户权限给另一用户
命令：grant xxx权限 on Table to USER
grant select,insert,update,delete,all on 表名 to 用户名
例如：将test表的查询权限赋予tom这个用户
grant select on test to tom
用system用户登录，密码 oracle
grant select on lcsg609999.amcard to fanruan;
新建用户授权
grant connect, resource to 用户名;
精度：指定小数点左边和右边可以存储的十进制数字的最大个数
长度：存储此数所占用的字节数
- M：精度，数据的总长度；
- D：标度，小数点后的长度；
- select 资产编码,count(*) from No_JDC_view group by 资产编码 having count(*) > 1
字段是否重复查询
创建外键
ALTER TABLE books ADD CONSTRAINT FK_Book_categoryid FOREIGN KEY(categoryId ) REFERENCES Category(id);
用pl/sql向导更方便一点。
外键字段与引用字段 类型，长度必须一致； 最后名称也一样。
删表
表用右键删除，没有提示，太危险。
用sql：
Drop table 表名；
备份表
create table <备份表名> as select * from <需备份表表名>
例如：
create table project_type201812 as select * from project_type
字段长度设置原则
Oracle的字符集 GBK16， 一个汉字设置2个字节；
UTF-8 一个汉字占3个字节
select u.name, length(u.name), lengthb(u.name) from var u;
字符长度 字节长度
查询当前数据库所用字符集；
select userenv('language') from dual;
计算机编码体系
American ASCII编码 （American Standard Code for Information Interchange，美国信息互换标准代码）
China gbk编码 通称他们叫做 “DBCS“（Double Byte Charecter Set 双字节字符集）
统一 Unicode 统一码、万国码
utf－8 互联网上使用最广的一种unicode的实现方式,为传输而设计的编码。特点：变长编码方式、统一无国界
修改pl/sql sql窗口字体大小
分组 排序
1、概述
Group By”从字面意义上理解就是根据“By”指定的规则对数据进行分组，所谓的分组就是将一个“数据集”划分成若干个“小区域”，然后针对若干个“小区域”进行数据处理。
2、原始表
3、简单Group By
示例1
select 类别, sum(数量) as 数量之和
from A
group by 类别
返回结果如下表，实际上就是分类汇总。
4、Group By 和 Order By
示例2
select 类别, sum(数量) AS 数量之和
from A
group by 类别
order by sum(数量) desc
返回结果如下表
在Access中不可以使用“order by 数量之和 desc”，但在SQL Server中则可以。
Rownum 自动获取行号
Select rownnum 行号, code,name from depetment
获取当前日期
获取日期字段的年select to_char(sysdate,'yyyy') as year from dua
获取日期字段的月select to_char(sysdate,'mm') as month from dual
获取日期字段的日select to_char(sysdate,'dd') as day from dua
获取上月、下月
select to_char(add_months(trunc(sysdate),-1),'yyyymm') from dual;
--上一个月
select to_char(add_months(trunc(sysdate),1),'yyyymm') from dual;
--下一个月
select to_char(add_months(trunc(sysdate),-12),'yyyy') from dual;
--上一年
select to_char(add_months(trunc(sysdate),12),'yyyy') from dual;
--下一年
dual是一个虚拟表，用来构成select的语法规则，oracle保证dual里面永远只有一条记录。我们可以用它来做很多事情，如下：
1、查看当前用户，可以在 SQL Plus中执行下面语句
select user from dual;
2、用来调用系统函数
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;--获得当前系统时间
select SYS_CONTEXT('USERENV','TERMINAL') from dual;--获得主机名
select SYS_CONTEXT('USERENV','language') from dual;--获得当前locale
select dbms_random.random from dual;--获得一个随机数
3、得到序列的下一个值或当前值，用下面语句
select your_sequence.nextval from dual;--获得序列your_sequence的下一个值
select your_sequence.currval from dual;--获得序列your_sequence的当前值
4、可以用做计算器
select 7*9 from dual;
1group by order by 混用，对聚合函数排序
SELECT
b.title as 品类,
sum(a.weight)/1000000 as 回收品重量
FROM
recycle_order_recycle_detail a,
recycle_type b
where a.recycle_type_id=b.id
AND DATE_SUB(CURDATE(), INTERVAL 0 DAY) <= date(a.ctime)
group by b.title,b.title order by 回收品重量 desc
临时表
有时候因为查询结果很大且需要再跟其它表进行不同形式的连表查询，这是如果整个sql写下来不仅看起来臃肿而且查询效率也很低，Oracle提供了一种将查询结果存入到
临时表的写法可以讲查询结果存储到临时表中待用，我这里只记一种会话级别的用法，在当前会话中查询的结果会在临时表中存储数据，结束（关闭）会话数据就被清除
会话级别
CREATE GLOBAL TEMPORARY TABLE tmptable
ON COMMIT PRESERVE ROWS
AS
Select * from table
距离应缴费日期一月内查询条件
select aa.patents_name as 专利名称,
cc.details_name as 类型,
dd.details_name as 费用类型,
bb.expenses_yjfrq as 应缴费日期,
bb.expenses_sjjfrq as 实际缴费日期
from patents aa, expenses bb, record_details cc, record_details dd
where aa.patents_pk = bb.patents_pk
and aa.patents_type = cc.details_pk
and bb.expenses_fylx = dd.details_pk
and bb.expenses_fylx = '53'
and to_char(bb.expenses_yjfrq,'yyyy/mm/dd')=to_char(add_months(trunc(sysdate),1),'yyyy/mm/dd')
当前日期加7天
to_char(trunc(sysdate+7),'yyyy/mm/dd')
sql 关键字做字段名时，需要将字段名加英文双引号，才能使用；
跨表数据更新
update amsubs
set amsubs.AMTXKH=(select amcardsdhz.TXKH from amcardsdhz where amcardsdhz.AMCARDID='b233aab0dfe94520b9d16427d2b405be')
where amsubs.subamcardid='b233aab0dfe94520b9d16427d2b405be'
智能硬件实时数量统计
--智能硬件统计--
select
amtype.typecode 资产类别编码,
amtype.typename 资产类别名称,
sum(amcard.amqty) as 数量之和
from amcard
left join amtype on amcard.AMTypeID=amtype.typeid
left join Amusestate on amcard.Amstate=Amusestate.stateid
left join lsbzdw on amcard.AcctCompID=lsbzdw.lsbzdw_dwbh
left join lsbmzd on (amcard.AcctCompID=lsbmzd.lsbmzd_dwbh) and (amcard.AcctDeptID=lsbmzd.lsbmzd_bmbh)
left join HROrgInfo hr_org on (amcard.Usecompid=hr_org.orgcode) and hr_org.orgtype='0'
left join HROrgInfo hr_dep on (amcard.usedeptid=hr_dep.nm) and hr_dep.orgtype='1'
where amtype.typename in
('卡片机','对讲机','智能手环','桑德盒子','好嘞服务亭','好嘞亭(准备停用)')
/*and
Amusestate.Statecode='1000'
*/
--上边编码为属于闲置状态的资产状态编码
group by amtype.typecode,amtype.typename
union all 连接
SELECT
'维修费' 费用类型,
wxf.amcardid 资产主键,
wxf.wxfy 主费用,
wxf.gsf 工时费,
wxf.bjf 备件费,
'' 加油费,
'' 保险费,
wxf.amwxgd_zdrq 制单日期
FROM
amwxgd wxf
WHERE
wxf.amcardid != ' '
AND wxf.wxfy IS NOT NULL
AND wxf.gsf IS NOT NULL
AND wxf.bjf IS NOT NULL
UNION ALL
SELECT
'保养费' 费用类型,
byf.amcardid 资产主键,
byf.ambyjlnr_jcfy 主费用,
byf.gsf 工时费,
byf.bjf 备件费,
'' 加油费,
'' 保险费,
ambyjl.ambyjl_zdrq 制单日期
FROM
ambyjlnr byf,
ambyjl
WHERE
byf.ambyjlnr_djid = ambyjl.ambyjl_djid
AND byf.amcardid IS NOT NULL
SQL UNION 和 UNION ALL 操作符
UNION 操作符用于合并两个或多个 SELECT 语句的结果集。
请注意，UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。
SQL UNION 语法
SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2
注释：默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。
SQL UNION ALL 语法
SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2
另外，UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。
Order by 与 group by 组合使用
PLSQL美化设计
SELECT
( CASE WHEN zc.acctcompid IS NOT NULL THEN zc.acctcompid WHEN zc.acctcompid IS NULL THEN zc.usecompid END ) 公司主键,
sum( "view_zcwb".主费用 ) + sum( "view_zcwb".工时费 ) + sum( "view_zcwb".备件费 ) 费用合计
FROM
"view_zcwb",
amcard zc
WHERE
zc.amcardid ( + ) = "view_zcwb".资产主键
AND （zc.acctcompid IS NOT NULL or zc.usecompid is not null)
AND to_char("view_zcwb".制单日期,'yyyy')='2018'
GROUP BY
( CASE WHEN zc.acctcompid IS NOT NULL THEN zc.acctcompid WHEN zc.acctcompid IS NULL THEN zc.usecompid END )
ORDER BY
sum( "view_zcwb".主费用 ) + sum( "view_zcwb".工时费 ) + sum( "view_zcwb".备件费 ) desc
Case when 语法
SELECT
( CASE WHEN bb.details_name IS NOT NULL THEN bb.details_name else '软著' END ) 专利类型,
-- bb.details_name,
count( aa.patents_pk )
FROM
patents aa,
record_details bb
WHERE
aa.patents_type = bb.details_pk ( + )
GROUP BY
bb.details_name
SELECT
( CASE WHEN bb.details_name IS NOT NULL THEN bb.details_name WHEN bb.details_name IS NULL THEN '软著' END ) 专利类型,
-- bb.details_name,
count( aa.patents_pk )
FROM
patents aa,
record_details bb
WHERE
aa.patents_type = bb.details_pk ( + )
GROUP BY
bb.details_name
union用法：
union 操作符用于合并两个或多个 select 语句的结果集。union内部的select语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条select语句中的列的顺序必须相同。
默认地，union操作符选取不同的值。如果允许重复的值，请使用
union all。
表连接 多条件时连接方式：
left join record_details r1 on
(SANITATION_DATA.COUNTRY_CLEANING_STANDARD=r1.DETAILS_CODE) and
r1.record_pk='22'
left join record_details r2 on (SANITATION_DATA.City_price_type=r2.DETAILS_CODE)
and r2.record_pk='23'
