Oracle

Oracle同义词

select * from tab where tname = 'GRJFMX';
select * from all_objects where object_name='GRZH';
//删除同义词
drop synonym GRZH;
drop synonym CHENGJU.GR_YLZF_FFMX;

where条件

出生日期大于60岁

 months_between(sysdate, a.csrq)+1  >= 60*12+3 
 trunc(months_between(sysdate, a.csrq) / 12) > 60
 select 
       trunc(months_between(sysdate, a.csrq) / 12) age,
       a.csrq
  from 表
 where 1=1
   and months_between(sysdate, a.csrq)+1  >= 60*12+3 or
       months_between(sysdate, a.csrq)+1  >= 55*12+3

查询重复数据

SELECT username from fx_yh group by username having count(username) > 1
//查询
select * from fx_yh t1 where rowid not in (select 
min(rowid) from fx_yh t2 where t1.userid=t2.userid)
//删除
delete from fx_yh t1 where rowid not in (select 
min(rowid) from fx_yh t2 where t1.userid=t2.userid)

删除重复数据

DELETE FROM
 FX_GZ_BM
 WHERE GZBH IN
       (SELECT GZBH FROM FX_GZ_BM GROUP BY GZBH HAVING COUNT(1) > 1)
   AND ROWID NOT IN (SELECT MAX(ROWID) FROM FX_GZ_BM GROUP BY GZBH)
   
SELECT *  FROM
 FX_GZ_BM
 WHERE GZBH IN
       (SELECT GZBH FROM FX_GZ_BM GROUP BY GZBH HAVING COUNT(1) > 1)
   AND ROWID NOT IN (SELECT MAX(ROWID) FROM FX_GZ_BM GROUP BY GZBH)
   AND GZBH= 'WH-20220210-001'

根据id查询所有的子数据

 select * from GD_DM_MX start with DMM = #{dmm}
 connect by prior DM = PID ORDER BY DM;

字段去重复

select distinct gnid from 表名

设置连接超时

 select resource_name,profile from dba_profiles;
 
 select resource_name,limit from dba_profiles where profile='MONITORING_PROFILE';
 
 alter profile MONITORING_PROFILE limit connect_time unlimited;
 
 alter profile MONITORING_PROFILE limit idle_time 30;

参考

oracle数据不一养同步数据

导出sql

进入云数据库选择

这样就有数据插入不进去，没有数据就插入进去了。

字符串截取

select substr(qxdm,0,3)qxdm from st_xtqx

BETWEEN 使用

and grpz.BLRQ BETWEEN TO_DATE(#{dyDateFrom,jdbcType=VARCHAR}, 'yyyy-mm-dd HH24:mi:ss') and TO_DATE(#{dyDateTo,jdbcType=VARCHAR}, 'yyyy-mm-dd HH24:mi:ss')

创建数据库

参考网站

https://www.cnblogs.com/chanshuyi/p/3821023.html

-- 创建表空间
create tablespace VSPN38
datafile 'VSPN38.dbf' size 10M;

-- 查询当前用户拥有的所的有表空间
select tablespace_name from user_tablespaces;

-- 创建用户并指定默认表空间，并为其授予权限
--创建用户
--注意这里的VSPN38必须大写（因为Oracle自动将表空间名字全部转为大写）
create user VSPN38 identified by VSPN38
default tablespace VSPN38;

--赋予用户DBA权限
grant connect,resource,dba to VSPN38;

-- 查看ANIMAL表空间下的所有表
select tablespace_name, table_name from user_tables
where tablespace_name = 'VSPN38';

--删除用户 或者drop user 用户名 cascade;(这将会删除与用户相关联的表)
drop user micro_vsta;

查看sid

-- 查看sid
select instance_name from v$instance; 

-- 查看全局数据库名
select name from v$database;

导出Dmp

expdp \'/ as sysdba\' directroy=d1 dumpfile=vspn38.dmp schemas=vspn38
impdp \'/ as sysdba\' directroy=d1 dumpfile=vspn38.dmp

先备份后删除

create table risk.fx_yj_20221031 as  select * from risk.fx_yj where fxbh='WH-20211227-003' AND ID='CP14203385';
delete from risk.fx_yj where fxbh='WH-20211227-003' AND ID='CP14203385';

oracle包的导入方式

右键查看

然后ctrl+s 保存到桌面

给表添加字段

alter table BT_YT add LSBZ VARCHAR2(3000); 
comment on column BT_YT.LSBZ is '落实备注';

主副表关键字查询

select *
  from BT_HY hy
 where hy.hymc like '%2019年%'
    or exists (select 1
          from BT_YT yt
         where hy.HYID = yt.HYID
           and yt.ytmc like '%fg%')

参考sql计算

    <select id="getAisjGzNum" parameterType="map" resultType="java.lang.Integer">
             select sum(mknum * sl)
        from (select count(ywid) mknum,
                     (select count(1)
                        from bt_aisj_fkmx b
                       where b.ywlx = a.ywlx
                         and b.dwid = a.dwid
                         and b.yxzt = '1') sl
                from bv_aisj_yw a
               where dwid = '1000000142'
                 and ywfsrq >= to_date('20220201', 'yyyymmdd')
                 group by ywlx,dwid)
    </select>

添加索引和主键

alter table BT_AISJ_JCSL
  add constraint PK_BT_AISJ_JCSL primary key (ID);

create index BT_AISJ_JCSL_aisjid on BT_AISJ_JCSL (aisjid)   ;

达梦数据库

查询数据字按照逗号分割

select wm_concat(yhid) from bt_yh where bmmc = '法律审计部'

更新数据尾号加1

select sjh,substr(sjh,1,lengthb(sjh)-1) from bt_yh;
update bt_yh set sjh=sjh || '1'
update bt_yh set sjh=substr(sjh,1,lengthb(sjh)-1)

获取昨天sql

select TO_CHAR(DATEADD(day,-1,GETDATE()),'yyyy.MM.dd') from dual

查询ver值最大的数据

SELECT t1.*  
FROM WF_RE_PROCDEF t1  
JOIN (  
    SELECT key, MAX(ver) AS max_ver  
    FROM WF_RE_PROCDEF
    where  yxzt='1' and name like '%收文%'  
    GROUP BY key  
) t2 ON t1.key = t2.key AND t1.ver = t2.max_ver  
where yxzt='1' and t1.name like '%收文%'