Oracle、PG与Mysql数据库学习_pg nvl

1.substr字符串截取函数

1.substr俗称字符串截取函数，一般用于对字符串（数据库的某字段）进行截取，也就是可以用于对于查询的字段进行截取后注入结果集合中。下面先看看函数的两种用法：

2.substr(string str,int index) 从字符串的index（索引）位置开始截取，截取后面所有的字符串。
substr(string str,int index,int length)从字符串的index（索引）位置开始截取，截取长度为length的字符串。

3.示例：select SUBSTR(“name”,0,3) as str from test;
结果：name=‘zhangsan’ str=‘zha’
4.substr(date,-2)可以表示按倒序截取尾数两位

2.regexp_substr函数

1.regexp_substr函数支持正则表达式来对字符串进行切割。相应很多人都在项目中遇到过在某一个表的某一个字段中根据某些特殊符号比如","对字符串进行的拼接的数据的存储的类型。在本次项目中有这样一次需求，在一个系统之外的数据表格中对车辆的外形参数定义为：长、宽、高的数据用逗号分隔后存入了库中的一个字段中，先需要将该字段的三段分离出来，分别存入到不同的字段中，当然实现的方式可能有很多种，我采用了insert into select的方式，先用regexp_substr将该字段拆分为三个字段，再以insert into select的方式插入到了我们需要的业务表里，全程非常快速。
2.示例

 select num,REGEXP_SUBSTR('num','[^,]+',1,1) as length,
  select num,REGEXP_SUBSTR('num','[^,]+',1,2) as width,
  select num,REGEXP_SUBSTR('num','[^,]+',1,3) as heigth,
  from test where id = 1;
1
2
3
4

regexp_substr第一个参数是源字符串(数据库字段名)，第二个参数是正则表达式截取规则，第三个表示从第几个字符开始匹配正则表达式，第四个参数表示标识第几个匹配组，在本例中也就是说第几个","。

3.LPAD函数

1.lpad(string,n,[pad_string])函数，从左侧追加生成固定长度的字符串。
2.参数说明
string：字符或者参数
n：字符的长度，是返回的字符串的数量，如果这个数量比原字符串的长度要短，lpad函数将会把字符串截取成从左到右的n个字符;
pad_string：可选参数，这个字符串是要粘贴到string的左边，若这个参数未写，lpad函数将会在string的左边粘贴空格。

 @Select("select decode(a.name_list_tag,'GBD','Y','GBDC','Y','GBDL','Y','N') \"isGBD\"," +
        "decode(c.cust_name,null,'',substr(c.cust_name,0,length(c.cust_name)-1||'*')) \"custName\"," +
        "replace(c.id_no,substr(c.id_no,length(c.id_no)-11,8),'********') \"custIdNo\"," +
        "to_char(c.date_first_reg,'YYYY-MM-dd') \"drivingRegistrationDate\"," +
        "(select b.city_name from base_bte_l_region_info b where c.city_code = b.city_code) \"insureCity\"" +
        "from appl_admin a,appl_gbd_car_info c where a.apply_no = c.apply_no and a.apply_no=#{applyNo}")
public Map<String,Object> getGBDCarInfo(Map<String,Object> map);
1
2
3
4
5
6
7

学习decode函数、replace函数和substr函数的使用

4.oracle中实现分页(rownum的使用)

类似于mysql中limit的作用，限制结果行数。

select * from(select  TT.*,rownum as rowno from (select * from appoint_register tab1 order by date_created) TT where rownum <= 10 ) tab2 where tab2 > 0)
1

解释：比较符号后的两个数字差为每页展示数量，tab2后面的数字为起始下标，rownum后面的数字为终止下标。
注意：rownum字段前面不要加表名

5.from dual的使用

启用一个虚拟表

6.“||”字符的使用

SQL语句中的拼接符号。

7.begin…end的使用

@Update("<script> BEGIN" +
        "UPDATE CSP_OLOAN_APPLY_SWITCH a set a.date_update = SYSDATE\n" +
        "<if test \"map.mergeSwitch != null\">,a.MERGE_SWITCH = #{map.mergeSwitch}</if>\n" +
        "where a.ACCOUNT_ID = #{map.accountId};" +
        "IF SQL%NOTFOUND THEN" +
        "INSERT into CSP_OLOAN_APPLY_SWITCH\n" +
        "(APPOINT_NO,ACCOUNT_ID)" +
        "VALUES\n" +
        "(#{map.appointNo},#{accountId});" +
        "END IF;" +
        "END;")
public Map<String,Object> updateSwitch(Map<String,Object> map);
1
2
3
4
5
6
7
8
9
10
11
12

1.学习begin…end语句块能够执行多个语句。

<insert id="copyDate">
    begin
      INSERT INTO TTRD_AUTH_USER_ROLE SELECT * FROM TTRD_AUTH_USER_ROLE_COPY C  WHERE C.USER_ID = #{userId,jdbcType=NUMERIC};
      INSERT INTO TTRD_WF_USER_ROLE SELECT * FROM TTRD_WF_USER_ROLE_COPY C  WHERE C.USER_ID =  #{userId,jdbcType=NUMERIC};
    end;
</insert>
1
2
3
4
5
6

2.学习SQL%NOTFOUND语句。SQL%NOTFOUND返回的数据类型是一个布尔值。布尔值与前一条sql语句相关。当最近的一条sql语句没有操作任何行的时候，返回true。否则返回false。

8.decode函数。decode(条件,值1,返回值1,值2,返回值2,…值n,返回值n,缺省值)

select decode(flag,'Y','成功'，‘N’,失败,‘无记录’) from t_student where name = ‘zhangsan’;
1

解释：当flag字段为Y时，返回成功字符串，当flag字段为N时，返回失败字符串。其余情况表示无记录。

9.nvl函数。NVL(expr1,expr2)，如果oracle数据库中第一个参数为空那么显示第二个参数的值，如果第一个参数的值不为空，则显示第一个参数本来的值。

select nvl(score,'无分数') from t_student where name = 'zhangsan';
1

解释，当score字段为空时，返回无分数这个字符串。

10.NVL2函数。nvl2(expr1,值1，值2)，当表达式expr1不为空时，显示值1；为空时，显示值2

select nvl2(sex,'有填写性别'，‘没有填写性别’) from t_student where name = 'zhangsan';
1

解释：当sex字段为空时，返回没有填写性别，不为空时，返回有填写性别。

11.case when …then …else end函数。

select (case when (select count(1) from appl_credit au where au apply_no = '110') > 0 then 'Y' esle 'N' end) from dual;
1

12.replace函数。replace(原字段，原字段旧内容，原字段新内容)

select replace(c21_id,substr(c21_id,length(c21_id)-11,8),‘********’） “C21Id” from dual;
1

13.trunc函数,对时间类型和数字类型操作，TRUNC（NUMBER）表示截断数字，TRUNC（date）表示截断日期

时间介绍

14.add_months()函数。add_months(date,int);其中第一个参数为日期，第二个为按月增加的幅度。

第二个参数用正负号表示对月份的加减。

15.sys_guid()函数。生成id的函数。

16.对表格字段进行修改、增加的sql语句

增加字段：alter table 表名 add（字段 字段类型） default ‘输入默认值’；
添加字段注释：comment on column 库名.表名.字段名 is ‘输入的备注’；
修改字段：alter table 表名 modify (字段 字段类型 【default ‘输入默认值’】，字段…)；修改多个字段用逗号隔开
删除字段：alter table 表名 drop (字段);
删除索引：

drop index if exists idx_policy_life_rm_cust_no

17.FM990.00格式

select to_char(参数,‘FM990.00’) from dual.
0表示：如果参数(double或者float类型)存在数字就显示数字，不存在数字就显示0
9表示：如果参数(double或者float类型)存在数字就显示数字，不存在数字就显示空格
FM表示：将9带来的空格删除

18.instr()函数

一、instr（‘源字符串’，‘目标字符串’），返回目标字符串出现的下标。（以1开始计算）
select instr(name,‘zhang’) from t_student;
解释：查找name字段中，‘zhang’字符串出现的下标。
二、instr(‘源字符串’，‘目标字符串’,‘开始位置’,‘第几次出现’)
select instr(‘zhangwei’,‘wei’,1,1) from dual;
查找zhangwei字符串中，wei字符串从第一个位置开始，第一次出现的下标。

19.abs函数，绝对值函数

20.months_between函数。返回两个日期值中间含有的月份个数

select months_between(to_date('2019-12-1','YYYY-MM-dd'),to_date('2019-10-1','YYYY-MM-dd')) from dual;
1

返回值有正负之分。

21.分组函数group by 和Oracle中分析函数partition by的区别

请参考

22.查看oracle库的表格

1.查看一张表格的区分度

select c.owner,c.column_name,c.num_distinct from dba_tab_columns c where c.table_name = upper('appoint_regiser');
1

解释：括号为表名
2.查询表名

select * from all_tables t where t.table_name = 'appoint_regiser';
1

3.查询同义词

select * from all_synonyms t where t.synonym_name = 'zw_test';
1

4.删除同义词语句

drop public synonym 同义词
1

5.查询索引

select * from all_indexes t where t.index_name= 'zw_index';
1

23.非标操作语句（以命令的形式执行）

1）从临时表中复制数据

DECLARE
CURSOR cur IS
select sys_guid(),
        id_code,
        attribute1,
        attribute2
        from zwdata.base_common_table_0314
        WHERE id_code in ('CCM','CCC');
TYPE rec is TABLE OF cur%ROWTYPE;-------需要定义一个数据记录的类型
recs rec;
BEGIN
OPEN cur;
while (TRUE) loop             -------loop为循环游标
FETCH  cur bulk collect     -------提取游标的数据
INTO recs limit 100000;
forall i in 1 .. resc.COUNT
INSERT INTO zwdata.base_common_table VALUES recs(i);
COMMIT;
exit WHEN cur%notfound;      -----------查不到就退出
END loop;                               --------退出循环游标
CLOSE cur;
END;
/                      -----------立即执行

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

cursor的介绍和使用

declare
cursor dateList is select id from t_bs_tj;
nums int := 0;
begin 
for item in dataList loop
 nums := nums + 10;
 update t_cltal_bs_tj t set t.status = nums where t.id = item.id ;
 end loop;
 end;
1
2
3
4
5
6
7
8
9

24.从一个数据库复制数据到另一个数据库

SET arraysize 500    -------一次调用复制500行
SET  copycommit 10    -----------一次提交复制至目标数据库10行
SET long 1000
copy from zwdata/password@'ip:port/CFS1' INSERT
zhangdata.base_common_table_0311 USING SELECT * from USING
zhangdata.base_common_table where id_code in ('CCM','CCC')
1
2
3
4
5
6

25.RTRIM、LTRIM、TRIM函数的使用

一、RTRIM（‘A’,‘B’）函数：目标字符串A从右开始，直至没有B字符串中的字符，开始截取

 select Rtrim('xyxxDWEYExyzyx','xyz') FROM dual;
1

输出：xyxxDWEYE

select Rtrim('xyxxDWEYExyzkzyx','xyz') FROM dual;
1

输出：
xyxxDWEYExyzk

二、LTRIM（‘A’,‘B’）函数：目标字符串A从左开始，直至没有B字符串中的字符，开始截取
三、trim（）函数：从左右两边开始截取字符（默认则去掉两边的空字符），此函数只能截取字符

25.取余函数mod()

select MOD(1200,500) from dual;
1

输出：200

26.translate函数

select translate('huangbaokang','ag','89') from dual;
1

输出：hu8n9b8ok8n9
解释：将字符a替换为数字8，字符g替换为9.huangbaokang为目标字符串
特别地

select translate('hello','def','#') from dual;
1

输出：hllo
解释：可以理解为字符d替换为#，字符e和f被空串替换。呈现出删除的效果
translate函数文章

27.带有正则表达式的函数

regexp_replace函数与replace函数用法相似
regexp_like函数与like用法相似
regexp_instr函数与instr函数用法相似
regexp_substr函数与substr函数用法相似
带有正则表达式的函数文章

28.with用法

with base as (select * from csp_appoint_register where appoint_no = '110'),
succecess1000 as (select * from csp_cl_city_channel_info where apply_no = 'NEW120')
select * from base where not exists (select 1 from succecess1000 where apply_no = '666')
1
2
3

解释：with的作用可以理解为将查询的结果当做一张临时表格，然后再关联查询出来的临时表进行关联查询

28.upper()和lower()函数

upper():小写字符转化成大写的函数
select lower(user_name) from user
lower()：大写字符转化成小写的函数
select upper(user_name) from user

29.oracle中，批量插入的写法

一种（与mybatis整合）：

@Insert("<scrpt>" +
            "insert into ss_car_quality_info (name,id_no,id_type)\n" +
            "<foreach collection=\"GBDList\" item=\"item\" index=\"index\" open =\"(\" close=\")\" separator=\"union all\">" +
            "select " +
            "#{item.name,jdbcType = VARCHAR},#{item.idNo,jdbcType=VARCHAR},#{item.idType,jdbcType = VARCHAR}\n" +
            "from dual" +
            "</foreach>" +
            "</script>")
            void batchSaveGBD(@Param("GBDList") List<Map<String,Object>> GBDList);
1
2
3
4
5
6
7
8
9

二种（与ibatis标签整合）

    <insert id="insertSign" parameterClass="java.util.List">
      insert all
      <iterate conjunct=" ">
      into t_student
          (name,age)
          VALUES (
            #list[].name#,
          #list[].age#,
          )
      </iterate>
    </insert>
    select * from dual
1
2
3
4
5
6
7
8
9
10
11
12

30.coalesce函数的用法

SELECT COALESCE (c1,c2,c3,c4,c5,c6) AS c FROM dual;
1

1、coalesce函数是用来获取第一个不为空的列的值

2、coalesce函数里面的数据类型，必须全部都跟第一列的数据类型一致

31.greatest与least函数的用法

作用每一行上取最大值与最小值，而聚合函数max与min是作用于每一列。注意使用greast与least函数的时候要排除数据为空的情况
使用说明

32.order by 1 可以表示以第一列进行排序,oracle默认升序空值在后，降序空值在前。可使用nulls First与nulls last进行控制

33.dbms_randm.value()随机函数

34.表格数据复制语句与WITH CHECK OPTION关键字进行有条件插入数值

create table t_1  as  select * from t_old 
1

对插入的表格的数据进行筛选
使用WITH CHECK OPTION关键字对插入数据进行控制

Insert into (select empno,ename,hiredate from emp where hiredate <= SYSDATE WITH CHECK OPTION) values (9999,'test'.sysydate +1)
1

35.多表插入语句中，有条件的插入insert first

insert first 
when job in ('salesman','manager') then into emp1(empno,ename,job) values (empno,ename,deptno) 
when deptno in ('20','30') then into emp2 (empno,ename,deptno) values (empno,ename,deptno) 
select empno,ename,job,deptno from emp;
1
2
3
4

在insert first语句中，当第一个表符合条件后，第二个表将不再插入对应的行，表emp2中不再有与表emp1相同的数据EMPNO = ‘7654’

36.regexp_like函数

select * from v5 where regexp_like (data,'A')
1

like模糊查询与正则表达式组合

select * from v5 where regexp_like (data,'A6+')
1

+前面是子表达式6，表示至少匹配6一次

select * from v5 where regexp_like (data,'A6*')
1

*前面是子表达式6，表示至少匹配60次，等价于like1%
+表示匹配前面的子表达式一次或多次，*表示匹配前面的子表达式0次或者多次

37.regexp_count函数

select regexp_count('zhangsan,lisi,wangwu',',')  from dual
1

得到目标字符串中，逗号的数量

38.level关键字

select regexp_substr('zhangsan,lisi,wangwu','[^,]+',1,level),level from dual connect by <=length(translate('zhangsan,lisi,wangwu',','||'zhangsan,lisi,wangwu',','))+1
1

切开目标字符串并将切开的字符串按列输出

39.listagg函数

select phone, listagg(log_name, ',') within group(order by phone) logName
from int_phone
where phone = '13350162230'
group by phone
1
2
3
4

将phone列分组并将log_name同属于phone列的数值放在同一行输出。函数第一个参数是列，第二个参数是分隔符
listagg函数

40.rank()与dense_rank()函数

这两种函数排序函数，rank相同的排名会生成同样的序号，而且其后的序号与row_number相同
dense_rank相同的排序会生成同样的函数，而且其后的排序会递增

41.round()函数

这个函数四舍五入。

42.Date类型通过interval进行计算,计算结果扔为DATE

select sysdate + interval '1' day from dual;
1

总结：oralce中，两个DATE类型相减，结果是数值，单位是天。而两个TIMESTAMP类型相减是INTERVAL类型

43.lag()函数与lead()函数

lag（）分析函数直接取得上一个记录的信息
lead（）分析函数取得下一个记录的信息

43.关于日期操作的函数总结

select to_char(created,‘yyyy-MM-dd hh24:mi:ss’) from dual

44.next_day与last_day函数

45.extract()函数

with v1 as (
select timestamp '1981-01-02 11:02:33.55' as t1 timestamp '1981-01-01 16:30:00.30' as t2 from dual)
select extract (DAY from t1-t2)*24*60 + extract(HOUR from t1-t2)*60 + extract(MINUTE from t1-t2) as minutes from v1
1
2
3

总结
1.两次查询sql一起执行
2.extract函数

46.oracle的执行计划解读

按照从左到右，从上至下的方法，了解执行计划的执行步骤执行计划按照层次逐步缩进，从左至右看，缩进最多的那一步，最新执行，如果缩进量相同，则按照从上而下的方法判断执行顺序，可粗略认为上面的步骤都有对应的cost，可从单步cost的高低，以及单步的估计结果集（对应rows/基数），来分析表的访问方式，连接顺序以及连接方式是否合理

47.oracle的调优

merge /*+ full(l) */ 
into
t_basedata_ln l using (
select /*+ parallel(4) full(trx) index(l.t_base_id)*/
sum (trx.trx_amt) putoutAmt ,acct_no from 
cfs_trx trx where trx.procut_mode = 'PL_WS'  and trx.trx_type = 'DB' and trx.is_effective = 'Y'
group by acct_no ) p 
on (l.account = p.acct_no and l.report_date = to_char(P_date,'yyyyMMdd') and 
l.credit_limit > p.putoutAmt and 
l.remark = 'JA_ILOAN_ADD')
when matched then 
update 
set l.shared_credit_limit = round(p.putoutAmt),l.date_updated sysdate;
1
2
3
4
5
6
7
8
9
10
11
12
13

48.使用SQL查询PKG内容

select * from dba_source a where a.name = '储存过程名字' and a.type = 'PACKAGE BODY' order by a.line;
1

49.大数据的造数脚本（connect by语句）

declare
i_max_cnt number := 3000000;
begin
delete SEC_LON_LB_TEMP_FD t where t.apply_no lke 'testAPPLYNO1105%';
commit;
insert into SEC_LON_LB_TEMP_FD （
ID_SEC_LON_LB_TEMP，
APPLY_NO
） (
select sys_guid(),
'testAPPPLYNO1105' || Ipad(rownum,6,0) from dual
connect by rownum <= Imax_cnt);
commit;
1
2
3
4
5
6
7
8
9
10
11
12
13

connect by 语句对返回的结果再次进行筛选。

50.使用rowid对数据进行更改，完成自测

select rowid,t.* from sec_lon_lb_temp t where t.policy_no = '110' 
1

51.关键字minus的使用

对t1表中的名字，除去掉t2表中有的名字操作，与union关键字相反之意

select name from t1 minus select name from t2
1

52.oracle查询锁表对象

select object_name,machine,s.sid,s.serial#     from v$locked_object l,dba_objects o ,v$session s   where l.object_id = o.object_id and l.session_id=s.sid;
1

53.oracle库大表新建索引加并发，执行完去掉并发

create index CMDATA.IX_DETAIL_APPL_NO on CMDATA.CM_ESG_BATCH_DETAIL (APPL_NO) initrans 16 PARALLEL 8 online;
----去掉并发
alter index CMDATA.IX_DETAIL_APPL_NO NOPARALLEL;
1
2
3

大表建立索引介绍

54.PL/SQL的客户端在工具—>会话的界面，可以查看正在运行的会话

55.查看数据库的字符集

select userenv('language') from dual
1

56.查询某张表的授权权限

select * from dba_tab_privs where grantee = 'username';
1

PG数据库学习

1.PG库中，sysdate

select * from tabe_pg where created_date >= now()::timestamp + '-1 day' and status = 'init'
1

PG库中的sysdate等价于now()::timestamp
select now()::timestamp + ‘1 year’ —当前时间加一年
select now()::timestamp + ‘1 month’ —当前时间加一个月
select now()::timestamp + ‘1 day’ —当前时间加一天
select now()::timestamp + ‘1 hour’ —当前时间加一个小时
select now()::timestamp + ‘1 min’ —当前时间加一分钟
select now()::timestamp + ‘1 sec’ —当前时间加一秒钟
select now()::timestamp + ‘1 year 1 month 1 day 1hour 1 min 1 sec’

2.PG库没有则插入，有则更新操作

项目使用的PGsql数据库，需求要求：当A表中没有的主申请号（列名：main_apply_no）则进行插入操作，否则对其主申请号进行更新操作

注意：冲突列main_apply_no需要是唯一索引列或主键列

<insert>
insert into table_A (column_a,column_b,colunmn_c,main_apply_no) values (
#{valueA},#{valueB},#{valueC},#{mainApplyNo} on conflict (main_apply_no) do update set column_a = #{valueA},column_b = #{valueB}
)
</insert>
1
2
3
4
5

3.表字段修改

1、创建主键索引
alter table table_name(表名) add constraint pk_name(索引名) primary key (id_key);
2、创建普通索引
created index concurrently idx_name(索引名) on table_name(表名) （tale_column）;
3、触发器
drop trigger if exists trigger_name(触发器名) on table_name(表名)；
created trigger trigger_name(触发器名) before update on table_name（表名） for each row execute procedure crs_update_now();
4、修改字段类型
alter table table_name alter column table_column type varchar(128);
5、增加字段
alter table table_name drop column if exists user_id;
alter table table_name add column user_id vachar(64);
comment on column table_name.table_column user_id is ‘注释’

4.PG库NVL函数

若putout_amt字段为null，则取值为0

select coalesce(putout_amt,0) from t_table;

5.PG库类型转换函数

将loan_amt字段的varchar类型转换成int类型

select sum(cast(loan_amt as int4)) from t_table;

6.PG库序列号处理

查找当前idx_name的序列号的位置

select nextval(‘idx_name_seq’);

将idx_name_seq序列号调整至50000

alter sequence idx_name_seq restart with 50000;

7.PG库创建分区表

分区讲解

create table fenqu2(
id int,
date varchar
) partition by range(date);

create table fenqu2_y2022m05 partition of public.fenqu2 for values from (‘2022-05-01’) to (‘2022-06-01’)

Mysql数据库学习

1.Mysql建立触发器语句

DELIMITER ;;
   CREATE TRIGGER update_trigger -- 触发器名字
   BEFORE UPDATE ON t_my_table -- 替换为目标表的名称
   FOR EACH ROW
   BEGIN
     SET NEW.create_time = NOW(); -- 设置更新时间字段为当前时间
   END;;
1
2
3
4
5
6
7

2.删除触发器语句

DROP TRIGGER update_trigger;
1

3.mysql时间与String类型转换

1）String类型转换成Date类型

select * from tqjc_lg_auto_task_record   where   load_status = '1' and  create_date >=   STR_TO_DATE('20230822','%Y%m%d') ORDER BY create_date desc ;
1

2）Date类型转换成String类型

select DATE_FORMAT(UPDATE_DATE,'%Y-%m-%d %H:%i:%s') from tqjc_lg_config   
1

4.mysql的数据表中，时间类型字段保留至微秒

datetime（3） 保留秒后面的位数