1 系统函数综述

系统函数：
● GBase 8a 内置有几百个实用的系统函数，使得数据处理更加方便。

格式：
● 函数名(参数)
● 参数可以是字段，也可以是数值或表达式

位置：
● select 中使用函数，可用于投影列，也可用在查询条件中。
● 举例：select 函数名(参数) from 表名 where …

2 系统函数分类

3 数学函数

—3.1 ROUND(按位数四舍五入)

select  round(456.789), round(456.789,0), round(456.789,-2),  round(456.789,2); 
+----------------+------------------+-------------------+------------------+
| round(456.789) | round(456.789,0) | round(456.789,-2) | round(456.789,2)| 
+----------------+------------------+-------------------+------------------+
| 457 | 457 | 500 | 456.79 | 
+----------------+------------------+-------------------+------------------+

—3.2 TRUNCATE（按位数截取）

select  truncate(456.789,0), truncate(456.789,-2),  truncate(456.789,2); 
+---------------------+----------------------+---------------------+
| truncate(456.789,0) | truncate(456.789,-2) | truncate(456.789,2) | 
+---------------------+----------------------+---------------------+
|                 456 |                  400 |              456.78 |
+---------------------+----------------------+---------------------+

4 时间函数

—4.1 汇总

—4.2 NOW, SYSDATE, CURRENT_DATE, CURRENT_TIME（取gcluster节点当前日期与时间）

select NOW(), SYSDATE(),CURRENT_DATE, CURDATE(),CURRENT_TIME(),CURTIME() ;
+---------------------+---------------------+--------------+------------+----------------+-----------+ 
| NOW() | SYSDATE() | CURRENT_DATE | CURDATE() | CURRENT_TIME() | CURTIME() | 
+---------------------+---------------------+--------------+------------+----------------+-----------+ 
| 2021-04-02 09:34:23 | 2021-04-02 09:34:23 | 2021-04-02   | 2021-04-02 | 09:34:23       | 09:34:23  | 
+---------------------+---------------------+--------------+------------+----------------+-----------+

建议使用now()函数
①SYSDATE()返回的是函数执行时的时间
②now()返回的是语句执行时的时间

—4.3 YEAR, MONTH, DAY，WEEKDAY（选取日期时间的各个部分：日期、 时间、年、季度、月、日、小时、分钟、秒、微秒）

select  now(),YEAR(NOW()), month(now()),day(now()),WEEKDAY(now()); 
+---------------------+-------------+--------------+------------+----------------+
| now()               | YEAR(NOW()) | month(now()) | day(now()) | WEEKDAY(now()) | 
+---------------------+-------------+--------------+------------+----------------+
| 2021-04-02 09:34:52 |        2021 |            4 |          2 |              4 | 
+---------------------+-------------+--------------+------------+----------------+

SELECT now(),quarter(now()); -- 季度
SELECT now(),hour(now()); -- 小时
SELECT now(),minute(now()); -- 分钟
SELECT now(),second(now()); -- 秒
SELECT now(),microsecond(now()); -- 微秒

—4.4 LAST_DAY（返回月份日期的最后一天）

【例子】查询日期月份最后一天，日期支持两位数年份值转换为四位数形式，错误日期返回NULL 
select last_day('2019-2-10') lastday1, last_day('2020-2-10 12:10:30 ') lastday2,last_day('19-5-10')  lastday3, last_day('190510')  lastday4;
+------------+------------+------------+------------+ 
| lastday1   | lastday2   | lastday3   | lastday4   | 
+------------+------------+------------+------------+ 
| 2019-02-28 | 2020-02-29 | 2019-05-31 | 2019-05-31 | 
+------------+------------+------------+------------+

select last_day(now()) lastday1, last_day('2019-2-30') lastday2, last_day('0000-00-00');
+------------+----------+------------------------+
| lastday1 | lastday2 | last_day('0000-00-00') | 
+------------+----------+------------------------+
| 2021-04-30 | NULL     | NULL                   |
+------------+----------+------------------------+

—4.5 TO_DAYS（返回日期对应的天数,从年份 0 开始的天数,日期转换为数字）

【例子】查询一个日期对应的天数，日期支持两位数年份值转换为四位数形式

select TO_DAYS('2018-10-09'), TO_DAYS('18-10-09'),TO_DAYS('181009');
+-----------------------+---------------------+-------------------+
| TO_DAYS('2018-10-09') | TO_DAYS('18-10-09') | TO_DAYS('181009') | 
+-----------------------+---------------------+-------------------+
|                737341 |              737341 |            737341 | 
+-----------------------+---------------------+-------------------+

【例子】查询当天日期和对应的天数，下月第一天的日期和对应天数

select NOW() today, TO_DAYS(NOW()) todaydate, LAST_DAY(NOW())+1 nextmonthfirst,TO_DAYS(LAST_DAY(NOW())+1) nextmonthfirstdate; 
+---------------------+-----------+----------------+--------------------+
|         today       | todaydate | nextmonthfirst | nextmonthfirstdate | 
+---------------------+-----------+----------------+--------------------+
| 2021-04-02 11:32:57 |    738247 | 2021-05-01     |             738276 | 
+---------------------+-----------+----------------+--------------------+

—4.6 DATE_FORMAT 日期的格式

SELECT sysdate(),DATE_FORMAT(sysdate(), '%W %M %Y'); 
+---------------------+------------------------------------+
| sysdate()           | DATE_FORMAT(sysdate(), '%W %M %Y') | 
+---------------------+------------------------------------+
| 2021-04-02 09:33:02 | Friday April 2021                  |
+---------------------+------------------------------------+

SELECT DATE_FORMAT('2010-10-04 22:23:00', '%H:%i:%s') ;
+------------------------------------------------+
| DATE_FORMAT('2010-10-04 22:23:00', '%H:%i:%s') | 
+------------------------------------------------+
| 22:23:00                                       |
+------------------------------------------------+

—4.7 ADD_MONTHS (date, number) ：在一个日期上加上指定的月份数；要减去月份数， 则 number 为负数；

SELECT NOW(), ADD_MONTHS(NOW(),3) AS next3month,ADD_MONTHS(NOW(),-1) as lastmonth;
+---------------------+---------------------+---------------------+
|           NOW()     |     next3month      |     lastmonth       | 
+---------------------+---------------------+---------------------+
| 2021-04-02 09:33:55 | 2021-07-02 09:33:55 | 2021-03-02 09:33:55 | 
+---------------------+---------------------+---------------------+

—4.8 DATE_ADD(date,INTERVAL expr type)，日期加法操作 负值表示减法;DATE_SUB(date,INTERVAL expr type)，日期减法操作;DATEDIFF(expr1, expr2), 开始日期 expr1 和结束日期 expr2 之间的天数。

【示例】取当前日期，后一天，前7天，前30天的日期；

SELECT NOW(),DATE_ADD(now(), INTERVAL 1 DAY) as 后一天, DATE_SUB(date(now()), INTERVAL 7 DAY) as 前七天, DATE_SUB(date(now()), INTERVAL 30 DAY)前30天; 
+---------------------+---------------------+------------+------------+
|        NOW()        |       后一天        |     前七天  |    前30天   | 
+---------------------+---------------------+------------+------------+
| 2021-04-02 09:36:35 | 2021-04-03 09:36:35 | 2021-03-26 | 2021-03-03 | 
+---------------------+---------------------+------------+------------+

【示例】求距离月底还有多少天；

SELECT NOW(), DATEDIFF(LAST_DAY(NOW()),NOW()); 
+---------------------+---------------------------------+
| NOW()               | DATEDIFF(LAST_DAY(NOW()),NOW()) |
+---------------------+---------------------------------+
| 2021-04-02 09:36:56 |                              28 |
+---------------------+---------------------------------+

5 字符串处理函数

—5.1 汇总

—5.2 LOWER, LCASE(全部字母转换为小写)，UPPER（转换为大写）

SELECT upper('gb') || LOWER('ASE') || LCASE(' 8A') ; 
+---------------------------------------------+
| upper('gb') || LOWER('ASE') || LCASE(' 8A') | 
+---------------------------------------------+
| GBase 8a |
+---------------------------------------------+

—5.3 REPLACE（替换字符串中指定的字符串）

select  replace('1234567','23','11'); 
+------------------------------+
| replace('1234567','23','11') | 
+------------------------------+
| 1114567                      |
+------------------------------+

—5.4 ASCII（返回ASCII编码，多个字符时返回首字符的ASCII码）

select ascii('A'), ascii('hello')  from  dual; 
+------------+----------------+
| ascii('A') | ascii('hello') | 
+------------+----------------+
|         65 |            104 |
+------------+----------------+

—5.5 CONCAT，||（字符串连接，将数字转换为字符串）

select  concat('我已使用', 20,'天GBase 8a') as concat_fun; 
+---------------------------+
| concat_fun |
+---------------------------+
| 我已使用20天GBase 8a | 
+---------------------------+

—5.6 NVL （替换NULL值）

select cno,cname,nvl(cpno,'unknow') from school.course;
+------+--------------+--------------------+
| cno | cname | nvl(cpno,'unknow') | 
+------+--------------+--------------------+
| 1 | 数据库 | 5 |
| 2 | 数学 | unknow |
| 3 | 信息系统 | 1 |
| 4 | 操作系统 | 6 |
| 5 | 数据结构 | 7 |
| 6 | 数据处理 | unknow |
| 7 | PASCAL语言 | 6 |
+------+--------------+--------------------+

—5.7 LEFT，RIGHT（从左，右截取指定位数字符串）

select left('1234567',3) , right('1234567',3) ; 
+-------------------+--------------------+
| left('1234567',3) | right('1234567',3) | 
+-------------------+--------------------+
| 123  | 567 |
+-------------------+--------------------+

—5.8 LENGTH ，CHAR_LENGTH（取字符串长度）

select  length('南大通用'), char_length('南大通用'); 
+------------------------+-----------------------------+
| length('南大通用')     | char_length('南大通用')     | 
+------------------------+-----------------------------+
|                     12 |                           4 |
+------------------------+-----------------------------+

6 数据转换函数

—6.1 汇总

—6.2 CAST、CONVERT（数据类型转换）

CAST(expr AS type)，CONVERT(expr,type)，CONVERT(expr USING transcoding_name)
type 可以是下列值之一：
CHAR、DATE、DATETIME、DECIMAL、TIME、NUMERIC、INT、SIGNED INT、SIGNED

SELECT NOW(),CAST(NOW() AS DATE); 
+---------------------+---------------------+
| NOW()               | CAST(NOW() AS DATE) |
+---------------------+---------------------+
| 2021-04-02 11:31:35 | 2021-04-02          | 
+---------------------+---------------------+

SELECT 1+'3'+10 ; 
+----------+
| 1+'3'+10 | 
+----------+
|       14 |
+----------+

字符串和数字类型的转换是隐式操作，条件中右边向左边转换

—6.3 CONV（不同数字进制间的转换）

不同数字进制间的转换。将 N 由 from_base 进制转化为 to_base 进制，返回值为 to_base 进制形式的字符串，如有任意一个参数为 NULL，则返回值为 NULL。

SELECT CONV(5,16,2); 
+--------------+
| CONV(5,16,2) | 
+--------------+
| 101          |
+--------------+

SELECT CONV('a',16,2); 
+----------------+
| CONV('a',16,2) | 
+----------------+
| 1010           |
+----------------+

—6.4 TO_NUMBER（返回数值）

select  to_number('3.14')+3.14;
+------------------------+
| to_number('3.14')+3.14 | 
+------------------------+
|                   6.28 |
+------------------------+

—6.5 TO_CHAR(datetime,[FORMAT]) 将日期时间转换为字符串，并进行格式化输出;TO_CHAR(number,[FORMAT]) 将数字转换为字符串，并进行格式化输出。

select now(),to_char(now(),'YYYY/MM/DD'); 
+---------------------+-----------------------------+
| now()               | to_char(now(),'YYYY/MM/DD') |
+---------------------+-----------------------------+
| 2021-04-02 11:30:53 | 2021/04/02                  |
+---------------------+-----------------------------+

SELECT TO_CHAR(987654321,'999,999,999'); 
+----------------------------------+
| TO_CHAR(987654321,'999,999,999') | 
+----------------------------------+
|  987,654,321                     |
+----------------------------------+

—6.6 TO_DATE(string,format) 将字符串 string 格式化成 format 类型的日期

SELECT TO_DATE('2011/11/15','YYYY/MM/DD') ;
+------------------------------------+
| TO_DATE('2011/11/15','YYYY/MM/DD') | 
+------------------------------------+
| 2011-11-15                         |
+------------------------------------+

7 CASE 用法

—7.1 CASE 子句的两种格式

格式1：
CASE value
WHEN [compare-value] THEN result 
[WHEN [compare-value] THEN result ...]
[ELSE result] END

格式2：
CASE WHEN
[condition] THEN result
[WHEN [condition] THEN  result ...] 
[ELSE result] END

—7.2 CASE 表达式的示例

SELECT Sname,Sage, CASE Ssex WHEN ' 男 '
THEN '帅哥' WHEN '女' THEN '美女' 
ELSE '不明' END sex
FROM student;

8 控制流函数

IF(expr1,expr2,expr3)
expr1 为 TRUE ，则 IF()的返回值为 expr2；否则返回值为 expr3；

SELECT IF(1<2,'yes','no') FROM t;
+--------------------+
| IF(1<2,'yes','no') | 
+--------------------+
| yes                |
+--------------------+

改写：SELECT (CASE WHEN expr1 THEN expr2 ELSE expr3 END) FROM t;

9 函数使用优化建议

下面哪条语句快？

1. select ENAME, DEPTNO from emp where to_char(HIREDATE,'YYYY') >= 1981;
2. select ENAME, DEPTNO from emp where HIREDATE >= to_date('1981-01-01','YYYY-MM-DD');

原理：函数使用在字段上，每一行字段数据都会执行函数； 而函数使用在值上，只执行一次，还能利用智能索引。