mysql/oracle 常用函数

---

mysql

---

1、日期函数

Date类型日期数据加减

-- 加法  +15天
DATE_ADD(t.APPLY_DATE,INTERVAL 15 DAY)
-- 月改为MONTH
-- 减法
DATE_SUB(NOW(),INTERVAL 1 MONTH)

timestamp类型日期数据加减

-- 计算月份
SELECT TIMESTAMPDIFF(MONTH, '2022-01-13 13:13:13','2022-10-01 16:16:16');
-- 计算天数等类似

获取当前时间

SELECT NOW(), SYSDATE();
-- now()更为常用

-- 获取当前date
SELECT CURRENT_DATE,CURRENT_DATE(),CURDATE();

将时间转为相应格式

date_format(ahc.time_, '%Y-%m-%d') as tdate
或者
date_format(t1.start_time_, '%Y-%m-%d %H:%i:%S') 

在java的 .mapper文件进行时间比较时，需注意一下

date_format(t1.start_time_, '%Y-%m-%d %H:%i:%S') <= #{endTime}

< 表示小于，>表示大于

2、group_concat函数

(select GROUP_CONCAT(a.supply_name ORDER BY a.create_date DESC SEPARATOR ',') from purchase_platform_quoted_detail a where ppbbm.id = a.main_id and a.del_flag='0'
        and (a.status != 'offerNew' AND a.status != 'offerCancel')) as supplyList,

将查询所得的多条数据按照时间排序后，取supply_name字段用符号 ，连接合并为一个字符串。

3、死锁

-- 查看当前运行所有事务
select * from  information_schema.INNODB_TRX;
-- 查看所有进程
show processlist;
-- 查看是否有表发生死锁
show open tables where in_use > 0;

SELECT 
    a.id,a.user,a.host,b.trx_started,b.trx_query 
FROM information_schema.processlist a RIGHT OUTER JOIN information_schema.innodb_trx b
ON a.id = b.trx_mysql_thread_id;

4、表结构/数据量

SELECT
    COLUMN_NAME 字段,
    COLUMN_COMMENT 名称,
    COLUMN_TYPE 类型,
IF
    ( column_key = 'PRI', '是', '否' )  主键,
IF
    ( IS_NULLABLE = 'YES', '是', '否' ) 是否为空,
    COLUMN_COMMENT 注释 
FROM
    INFORMATION_SCHEMA.COLUMNS 
WHERE
    table_schema = 'srm' 
    AND table_name = 'quasi_admittance_main'
	
	
	
	
	
-- oracle 所有表数据量
SELECT '--'||b.comments||'--数据总量:'|| T.NUM_ROWS||'条',chr(10)||
'select * from '||T.TABLE_NAME||';' FROM USER_TABLES T,user_tab_comments b
where t.TABLE_NAME=b.table_name and T.NUM_ROWS!=0
order by  T.NUM_ROWS desc


-- 查看数据量
select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables where table_schema='home';

---

oracle

---

1、时间格式处理

oracle中无法像mysql直接用>, <等判断时间数据的大小

to_char(now(), ‘yyyy-MM-dd hh24:mi:ss’)

2、 instr(字符串，目标字符串)函数

结果为第一次出现 目标字符串的位置（从1开始）

select instr(nofirm.DIGITAL_SALES_ID, ',') from nofirm

3、split函数

oracle没有分割取数函数，需要自己新建一个function

create or replace function SPLITER(p_value varchar2 ,
                                    p_split varchar2 := ',' ,times integer := 1)
--参数1 表示字符串，参数2 为分隔符，参数3 为第几个
 return  varchar2 as
  v_idx1       integer;
  v_idx2       integer;
  v_strs_last varchar2(4000 ) := p_value;
begin
  if times= 1 then
    return substr(v_strs_last,1 ,instr(v_strs_last,p_split,1, 1)-1 );
  end if;
    v_idx1 := instr(v_strs_last, p_split, 1,times-1 );
    v_idx2 := instr(v_strs_last, p_split, 1,times);
  if v_idx2= 0 then
    return substr(v_strs_last,v_idx1+1 );
  end if;
  return substr(v_strs_last,v_idx1+ 1,v_idx2-v_idx1-1 );
end SPLITER;

用法如下：

select SPLITER('afageaf,sfeege,1344332', ',' , 1);

4、去除重复数据

DELETE 
FROM
  supplier_data
WHERE
  ID IN (
  SELECT
    ID 
  FROM
    (
    SELECT
      eol.*,
      row_number ( ) over ( partition BY eol.supplier_code ORDER BY eol.create_date DESC ) rn 
    FROM
      supplier_data eol 
    -- WHERE  条件
    )  where rn != 1
  ) 

5、生成id

--生成uuid
SELECT SYS_GUID() from dual;

6、查看表创建时间

select * from all_objects a
 where a.OBJECT_NAME
=  'ZPM_SELL_PRICE_I';

7、2个表关联更新数据

MERGE INTO Zpm_Price_Noticecode_Itf e
USING ZPM_PRICE_NOTICECODE dc
ON (e.id = dc.id)
WHEN MATCHED THEN
    UPDATE SET e.end_date = dc.end_date;

---

mybatis plus

---

1、批量更新

传参为实体list，注意：oracle数据库list大小超过1000时会报错

UPDATE ZPM_PACKAGE_AGREEMENT t
        SET
        t.DEL_FLAG = '1',
        t.DEAD_TIME = sysdate
        WHERE t.DEL_FLAG = '0'
        and (t.materite_code, t.supplier_code, t.is_quotation, t.type) in
        <foreach collection="list" item="item" index="index" separator="," open="(" close=")">
         (#{item.materiteCode}, #{item.supplierCode}, #{item.isQuotation}, #{item.type})
        </foreach>