【国产数据库】GBase学习⑧ - 函数

函数

SQL函数的主要分类包括：单行函数、多行函数（SQL 函数指的是数据库内置函数，可以运用在SQL语句中实现特定的功能）
单行函数：对于每一行数据进行计算后得到的一行输出结果
单行函数的基本特性：

• 单行函数对单行操作
• 每行返回一个结果
• 有可能返回值与原参数数据类型不一致
• 单行函数可以写在SELECT、WHERE、ORDER BY字句中
• 有些函数没有参数，有些函数包括一个或多个参数
• 函数可以嵌套

根据数据类型分为：字符函数、数值函数、日期函数、转换函数 以及其他通用的函数。

• 字符函数：主要用于字符串和字符串、字符串与非字符串之间的连接，以及字符串的模式匹配操作。
• 数值函数：主要用于数字操纵和数学计算等操作，如绝对值、平方根、随机值等
• 日期函数：主要用于获取系统时间，日期、时间类型的计算与格式化，如日期差值计算、日期截取等
• 转换函数：主要用于将一种数据类型转换成另一种数据类型，常见如数值与字符类型、字符与日期类型之间的转换以及转换时的格式化方式等

常用字符函数

1. ascii(string):参数string的第一个字符的ASCII码。返回值类型integer

select ascii('xyz');           select ascii('x');

2. btrim(string text[,characters text]):从string开头和结尾删除只包含characters中字符（缺省是空白）的最长字符串。返回值类型：text。

 select btrim('string','ing');
select btrim('stringstring','ing');
select btrim('ingstringstring','ing');

3. ltrim(string[,characters]):从字符串string的开头删除只包含characters中字符（缺省是一个空白）的最长字符串。返回值类型：varchar。

select ltrim('xxxxTRIM','x');
select ltrim('xxxxTRIxMx','x');

4. rtrim(string[,characters]):从字符串string的结尾删除只包含characters中字符（缺省是个空白）的最长字符串。返回值类型：varchar。

select rtrim('xxxxTRIxMxx','x');

5. upper(string):把字符串转化为大写。返回值类型：varchar

 select upper('tom');

6. lower(string):把字符串转化为小写。返回值类型：varchar

select upper('TOM');

7. concat(str1,str2):将字符串str1和str2连接并返回。返回值：varchar

select concat('Hello',' World!');

8. replace(string,substring):删除字符串string里出现的所有字符串substring的内容。string类型：text，substring类型：text，返回值类型：text

select replace('abcdefabcdef','cd');

9. reverse(str):返回颠倒的字符串。返回值类型：text

select reverse('abcde');

10. substrb(text,int,int):提取子字符串，第一个int表示提取的起始位置，第二个表示提取几位字符。返回值类型：text

select substrb('string',2,3);

常用的其他字符函数：

1. bit_length(string)：字符串的位数

select bit_length('world');

2. char_length(string):字符串中字符的个数

select char_length('hello');

3. position(substring in string):指定字符串的位置。区分大小写

select position('ing' in 'string');

4. string || string,string||non-string:连接字符串，或连接字符串和非字符串

select 'GBase'||'8c';

5. initcap(string):将字符串中的每个单词的首字母转化为大写，其他字母转化为小写。

select initcap('hi thomas');

6. right(str text,n int):返回字符串中的后n个字符。

select right('abcde',2);

7. left(str text, n int):返回字符串中的前n个字符

select left('ancde',2);

数字操作符

操作符	描述
+	加
-	减
*	乘
/	除（除法操作符不进行取整）
%	模运算
@	绝对值
|/	平方根
||/	立方根
!!	阶乘（前缀操作符）
|	二进制OR
&	二进制AND
#	二进制XOR
~	二进制NOT
^	幂（指数运算）
<<	左移位
>>	右移位

常用数值函数

1. abs(exp), cos(exp), sin(exp): 返回表达式的绝对值，余弦值，正弦值。

abs(-10),cos(0),sin(0);

3. bitand(integer,integer):计算两个数字与运算（&）的结果。返回值类型：bigint类型数字

select bitand(127,63);
			1111111  --- 127
			 111111  ---  63
			二进制按位与运算结果为  111111  ---  63

4. acos(exp), asin(exp): 返回表达式的反余弦值和反正弦值。

select acos(1),asin(0);

5. random():0.0到1.0之间的随机数。返回值类型： double precision

select random();

6. ceil(x):不小于参数的最小的整数

select ceil(-42.8);

7. floor(x):不大于参数的最大整数

select floor(-42.8);

8. ln(x):自然对数

select ln(2.0);

9. log(x):以10为底的对数

select log(100.0);

10. round(x):离输入参数最近的整数

select round(42.6);

11. sign(x):输出此参数的符号。-1表示负数，0表示0，1表示正数

select sign(-8.4);

12. trunc(x):截断（取整数部分）

select trunc(534.3);

13. trunc(v numeric, s int):截断为s位小数

select trunc(42.34124,2);

常用时间和日期函数

1. current_date:当前时间

select current_date;

2. current_timestamp:当前日期及时间

select current_timestamp;

3. date_trunc(text,timestamp):截取到参数text指定的精度。

select date_trunc('hour',timestamp'2001-02-16 20:38:40');

4. trunc(timestamp):默认按天截取

select trunc('hour',timestamp'2001-02-16 20:38:40');

5. now():当前日期及时间

now();

6. add_months(d,n):用于计算时间点d再加上n个月的时间

select add_months(to_date('2018-5-29','yyyy-mm-dd'),11);

7. last_day(d):用于计算时间点d当月最后一天的时间

select last_day(to_date('2018-5-29','YYYY-MM-DD'));

时间日期操作符–（+、-、*、/）

+  :       SELECT date '2001-9-28' + integer '7' ; 
           SELECT date '2001-09-28' + interval '1 hour' ;
           SELECT date '2001-09-28' + time '03:00' ;
           SELECT interval '1 day' + interval '1 hour' ; 
           SELECT timestamp '2001-09-28 01:00' + interval '23 hours' ; 
           SELECT time '01:00' + interval '3 hours' ; 

-  :       SELECT date '2001-10-01' - date '2001-09-28'; 
           SELECT date '2001-10-01' - integer '7'; 
		   SELECT date '2001-09-28' - interval '1 hour'; 
		   SELECT time '05:00' - time '03:00'
*  :       SELECT 900 * interval '1 second'; 
           SELECT 21 * interval '1 day'; 
		   SELECT double precision '3.5' * interval '1 hour';
/  :       SELECT interval '1 hour' / double precision '1.5'; 

类型转换函数

1. cast(x as y):类型转换函数，将x转换成y指定的类型

select cast('22-oct-1997' as timestamp);

2. to_char(int,fmt):将整数类型的值转换为指定格式的字符串。fmt表示格式化方式。返回值类型：text

select to_char(125,'999');

3. to_date(text,fmt):将字符串类型的值转换为指定格式的日期。fmt表示格式化方式。

select to_date('05 Dec 2000','DD Mon YYYY');

4. to_number(expr[,fmt]):将expr按指定格式转化为一个NUMBER类型的值。fmt表示格式化方式。

select to_number('12,454.8-','99G999D9S');

5. to_timestamp(text,fmt):将字符串类型的值转化为指定格式的时间戳。fmt表示格式化方式。

select to_timestamp('05 Dec 2000','DD Mon YYYY');

6. to_bigint(varchar):将字符类型转换为bigint类型

select to_bigint('1233235235342');

日期、时间格式化模板

类别	模式	描述
小时	HH	一天的小时数（01-12）
	HH12	一天的小时数（01-12）
	H24	一天的小时数（00-23）
分钟	MI	分钟（00-59）
秒	SS	秒（00-59）
	FF	微秒（000000-999999）
	SSSS	午夜后的秒（0-86399）
上午	AM或A.M.	上午标识
下午	PM或P.M.	下午标识
年	Y,YYY	带逗号的年（4和更多位）
	SYYYY	公元前四位年
	YYYY	年（4和更多位）
	YYY	年的后三位
	YY	年的后两位
	Y	年的最后一位
	IYYY	ISO年（4位或更多位）
	IYY	ISO年的最后三位
	IY	ISO年的最后两位
	I	ISO年的最后一位
	RR	年的后两位（可在21世纪存储20世纪的年份）
	RRRR	和YYYY相同。
	BC或B.C.AD或A.D.	纪元标识。BC（公元前），AD（公元后）。
月	MONTH	全长大写月份名（空白填充为9字符）
	MON	大写缩写月份名（3字符）
	MM	月份数（01-12）
	RM	罗马数字的月份（I-XII ；I=JAN）（大写）
天	DAY	全长大写日期名（空白填充为9字符）
	DY	缩写大写日期名（3字符）
	DDD	一年里的日（001-366）
	DD	一个月里的日（01-31）
	D	一周里的日（1-7 ；周日是 1）
周	W	一个月里的周数（1-5）（第一周从该月第一天开始）
	WW	一年里的周数（1-53）（第一周从该年的第一天开始）
	IW	ISO一年里的周数（第一个星期四在第一周里）
世纪	CC	世纪（2位）（21 世纪从 2001-01-01 开始）
儒略日	J	儒略日（自公元前 4712 年 1 月 1 日来的天数）
季度	Q	季度

数值格式化模板

类别	描述
9	数位（如果无意义可以被删除）
0	数位（即便没有意义也不会被删除）
.(句号)	小数点
,(逗号)	分组（千）分隔符
PR	尖括号内的负值
S	带符号的数字（使用区域设置）
L	货币符号（使用区域设置）
D	小数点（使用区域设置）
G	分组分隔符（使用区域设置）
MI	在指明位置的负号（如果数字<0）
PL	在指明位置的正号（如果数字>0）
SG	在指定的位置的正/负号
RN	罗马数字（输入在1~3999之间）
TH	或 th 序数后缀
V	移动指定位（小数）