MYSQL/Oracle中常用函数总结

记录在日常工作或者学习中中使用到的函数，以下是做一个备忘~

MySQL：

日期时间：

current_date：返回日期

示例：

select current_date ;

　

now()：返回时间

select now();

获取时间函数：

select current_date ;

SELECT CURRENT_TIMESTAMP ;

SELECT CURRENT_TIMESTAMP ();

SELECT NOW();

SELECT LOCALTIME;

SELECT LOCALTIME();

SELECT LOCALTIMESTAMP;

SELECT LOCALTIMESTAMP();

操作时间/日期：

-- 加一天:
select NOW() + INTERVAL 1  DAY ;

-- 减一天:
select NOW() - INTERVAL 1  DAY ;

-- 加 30 分钟:
select NOW() + INTERVAL 30  MINUTE ;

-- 减 30 分钟:
select NOW() - INTERVAL 30  MINUTE ;

DATE_FORMAT() ：用于以不同的格式显示日期/时间数据

语法：

DATE_FORMAT(date,format)

date 参数是合法的日期。

format 规定日期/时间的输出格式。

 

 示例：

SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s');  -- 结果：2020-12-07 22:18:58
SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i');   -- 结果：2020-12-07 22:18
SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H');     -- 结果：2020-12-07 22
SELECT DATE_FORMAT(NOW(),'%Y-%m-%d');      -- 结果：2020-12-07
SELECT DATE_FORMAT(NOW(),'%H:%i:%s');      -- 结果：22:18:58
SELECT DATE_FORMAT(NOW(),'%H');         -- 结果：22

CAST：格式转换

CAST函数用于将值从一种数据类型转换为表达式中指定的另一种数据类型

语法：

CAST (value  AS datatype)

参数说明：

　　value: 要转换的值

　　datatype: 要转换成的数据类型

datatype参数取值

值	描述
DATE	将value转换成'YYYY-MM-DD'格式
DATETIME	将value转换成'YYYY-MM-DD HH：MM：SS'格式
TIME	将value转换成'HH：MM：SS'格式
CHAR	将value转换成CHAR(固定长度的字符串)格式
SIGNED	将value转换成INT(有符号的整数)格式
UNSIGNED	将value转换成INT(无符号的整数)格式
DECIMAL	将value转换成FLOAT(浮点数)格式
BINARY	将value转换成二进制格式

 示例：

将值转为Date类型：

-- 2017-08-29
SELECT CAST('2017-08-29' AS DATE); 
 
-- 2022-09-14 10:22:29
SELECT NOW();

-- 2022-09-14
SELECT CAST(NOW() AS DATE);

将值转换为DATETIME数据类型

-- 2022-04-27 00:00:00
SELECT CAST('2022-04-27' AS DATETIME);

将值转换为TIME数据类型

-- 14:06:10
SELECT CAST('14:06:10' AS TIME); 
-- 14:06:10
SELECT CAST('2022-04-27 14:06:10' AS TIME);

将date转为char，将某一列的数据类型转为指定数据类型

SELECT
  CAST( date AS CHAR ) AS date,
  IF( STATUS = 1, '正常', '迟到' ) AS STATUS 
FROM
  tb_checkin 
WHERE
  user_id = 6 
  AND date BETWEEN '2022-09-13 00:00:00' 
  AND '2022-09-14 00:00:00';

IF语句：条件判断

IF：如果expr1条件为TRUE，则IF()返回值为expr2，否则返回值为expr3

IF(expr1,expr2,expr3);

IFNULL：假如expr1不为null，则返回expr1，否则返回expr2

IFNULL(expr1,expr2)

 case whth：条件判断

语法：

case
    when 条件1 then '值'
    when 条件2 then '值'
    ……
    else then '值'
end

示例：

SELECT
  CASE
    WHEN status= 1 THEN '正常' 
    WHEN status= 2 THEN '迟到' 
    ELSE THEN '未知状态'
  END AS status
FROM
  tb_checkin 
WHERE
  user_id =6

　　　　

Oracle：

MERGE INTO：条件判断

作用:判断C表(源表)和Ａ表(目标表)是否满足ON中条件，如果满足则用C表去更新A表，如果不满足，则将C表数据插入A表.

　　简单的说就是，判断表中有没有符合on（）条件中的数据，有了就更新数据，没有就插入数据。

# 判断表中有没有符合 on （）条件中的数据，有了就更新数据，没有就插入数据。
MERGE  INTO 目标表 A USING (
 SELECT
    B.ID,
    B.F_LOGINNAME,
    B.F_PASSWORD
 FROM
  T_MEM_MEMBER B  where B.F_LOGINNAME= '???'
) 源表C  ON (A.条件字段 = C.条件字段)
# 匹配成功执行更新语句
WHEN MATCHED  THEN UPDATE SET A.F_PASSWORD = C.F_PASSWORD
# 匹配不成功执行插入语句
WHEN NOT MATCHED  THEN
    INSERT (A.ID, A.F_NAME, A.F_PASSWORD,A.F_CAPTION, A.F_TYPE,A.F_STATE,A.F_NOTE,A.F_ORDER,A.F_DEPT_ID,A.F_DEPT_NAME,A.F_MS_RECEIVED)
    VALUES
    (C.ID, C.F_LOGINNAME, C.F_PASSWORD, '区级管理员/街道管理员/...' , '2' , '0' , '1' , '1' , '0' , '管理员' , '0' );

start with connect by prior：递归查询

基本语法：

select [字段] from [表名] start with ... connect by prior id = pId

• start with：表示以什么为根节点，不加限制可以写1=1，要以id为123的节点为根节点，就写为start with id =123
• connect by：connect by是必须的，start with有些情况是可以省略的，或者直接start with 1=1不加限制
• prior：prior关键字可以放在等号的前面，也可以放在等号的后面，表示的意义是不一样的，比如 prior id = pid，就表示pid就是这条记录的根节点了

举例：

查询指定父栏目(根节点)下所有的一级(子)栏目，一级栏目下可能还有二级栏目，将这些栏目的信息都查询出来

SELECT
  ID,
  F_TITLE 
FROM
  T_MK_CMS_CHANNEL 
START WITH id = '800535' 
CONNECT BY PRIOR ID = F_PARENT_ID

 

 sys_connect_by_path：将递归查到的数据加上特定的符号

按照上面的例子来说，查询到的数据，并不能直观的看到哪些是父节点，哪些是子节点，使用sys_connect_by_path就可以很好的区分了：

sys_connect_by_path第一个参数是形成树形式的字段，第二个参数是父级和其子级分隔显示用的分隔符！

SELECT
  ID,
  sys_connect_by_path ( F_TITLE, '----' )  
FROM
  T_MK_CMS_CHANNEL 
START WITH ID = '800535'
CONNECT BY PRIOR ID = F_PARENT_ID