SQL 函数

作者: wpf_register | 来源:发表于2019-09-29 09:37 被阅读0次

SQL Date 函数
每天一SQL语句（04）：SQL 语句函数篇
Oracle_SQL函数-单行函数
单行函数
SQL内置函数使用手册
（4）SparkSQL中如何定义UDF和使用UDF
hive窗口函数盘点
学习SQL的第十天（11月18日~11月19日）
SQL Server DATEADD() 函数
mysql操作

字符串函数

ascii(str)
返回字符串str的第一个字符的ascii值(str是空串时返回0)

SELECT ASCII('2'); -- 50
SELECT ASCII(2); -- 50
SELECT ASCII('date'); -- 100

bin(n) ：把n转为二进制值并以字串返回conv(n,10,2)
oct(n) ：把n转为八进制值并以字串返回 conv(n,10,8)
hex(n)：把n转为十六进制并以字串返回 conv(n,10,16)

SELECT BIN(12); -- 1100
SELECT OCT(12);  -- 14
SELECT HEX(255); -- FF

char(n,…)：返回由参数n,…对应的ascii代码字符组成的一个字串(参数是n,…是数字序列,null值被跳过)

SELECT CHAR(77,121,83,81,76);

concat(str1,str2,…)把参数连成一个长字符串并返回(任何参数是null时返回null)

SELECT CONCAT('m','y','sql')

length(str)：返回字符串str的长度
char_length(str)：返回字符串str的长度（多字节）

SELECT LENGTH('fuck'); -- 4
SELECT CHAR_LENGTH('我们'); --2

locate(substr,str)
position(substr in str)
返回字符串substr在字符串str第一次出现的位置(str不包含substr时返回0)
locate(substr,str,pos)
返回字符串substr在字符串str的第pos个位置起第一次出现的位置(str不包含substr时返回0)

SELECT LOCATE('我们','谁说我们没长大'); -- 3
SELECT POSITION('我们' IN '谁说我们没长大'); -- 3
SELECT LOCATE('bar','foobarbar',5); -- 7

instr(str,substr)
返回字符串substr在字符串str第一次出现的位置(str不包含substr时返回0)

SELECT INSTR('foobarbar','bar'); -- 4
SELECT INSTR('xbar','foobar'); -- 0

lpad(str,len,padstr)
用字符串padstr填补str左端直到字串长度为len并返回
rpad(str,len,padstr)
用字符串padstr填补str右端直到字串长度为len并返回

SELECT LPAD('baby',8,'*'); -- ****babay
SELECT RPAD('baby',8,'*'); -- baby****

left(str,len)
返回字符串str的左端len个字符
right(str,len)
返回字符串str的右端len个字符

SELECT LEFT('foobarbar',5); -- fooba
SELECT RIGHT('foobarbar',5); -- arbar

substring(str,pos,len)
substring(str form pos for len)
mid(str,pos,len)
返回字符串str的位置pos起len个字符
substring(str,pos)
substring(str from pos)
返回字符串str的位置pos起的一个子串

SELECT SUBSTRING('quadratically',5,6); -- ratica
SELECT SUBSTRING('quadratically' FROM 5 FOR 6);  -- ratica
SELECT MID('quadratically',5,6);     -- ratica

SELECT SUBSTRING('quadratically' FROM 5); -- ratically
SELECT SUBSTRING('quadratically',5); -- ratically

substring_index(str,delim,count)
返回从字符串str的第count个出现的分隔符delim之后的子串

SELECT SUBSTRING_INDEX('www.fenghoo.net','.',1);  -- www
SELECT SUBSTRING_INDEX('www.fenghoo.net','.',2);  -- www.fenghoo
SELECT SUBSTRING_INDEX('www.fenghoo.net','.',-1);  -- net
SELECT SUBSTRING_INDEX('www.fenghoo.net','.',-2); -- fenghoo.net

SELECT SUBSTRING_INDEX('fenghoo@net','@',1); -- fenghoo
SELECT SUBSTRING_INDEX('fenghoonet','e',2); -- fenghoon

ltrim(str)
返回删除了左空格的字符串str
rtrim(str)
返回删除了右空格的字符串str
trim([[both | leading | trailing] [remstr] from] str)
返回前缀或后缀remstr被删除了的字符串str(位置参数默认both,remstr默认值为空格)

SELECT LTRIM('  barbar'); -- barbar
SELECT RTRIM('barbar   '); -- barbar
SELECT TRIM( BOTH 'x' FROM 'xxxbarxxx'); -- bar
SELECT TRIM( TRAILING 'xyz' FROM 'barxxyz'); -- barx

space(n)
返回由n个空格字符组成的一个字符串

SELECT SPACE(3); -- '   '

replace(str,from_str,to_str)
用字符串to_str替换字符串str中的子串from_str并返回
insert(str,pos,len,newstr)
把字符串str由位置pos起len个字符长的子串替换为字符串newstr并返回

SELECT REPLACE('www.mySQL.com','mySQL','myPHP'); -- www.myPHP.com
SELECT INSERT('mySQL',3,4,'PHP');  -- myPHP

repeat(str,count)
返回由count个字符串str连成的一个字符串(任何参数为null时
返回null,count<=0时返回一个空字符串)

SELECT REPEAT('mysql',3);  -- mysqlmysqlmysql

reverse(str)
颠倒字符串str的字符顺序并返回

SELECT REVERSE('fuck'); -- kcuf

elt(n,str1,str2,str3,…)
返回第n个字符串(n小于1或大于参数个数返回null)
field(str,str1,str2,str3,…)
返回str等于其后的第n个字符串的序号(如果str没找到返回0)
ind_in_set(str,strlist)
返回str在字符串集strlist中的序号(任何参数是null则返回
null,如果str没找到返回0,参数1包含","时工作异常)

SELECT ELT(3,'str1','str2','str3'); -- str3
SELECT FIELD('str3','str1','str2','str3'); -- 3
SELECT FIND_IN_SET('b','a,b,c,d'); -- 2

make_set(bits,str1,str2,…)
把参数1的数字转为二进制,假如某个位置的二进制位等于1,对应位置的字串选入字串集并返回(null串不添加到结果中)

SELECT MAKE_SET(1,'a','b','c');     -- a
SELECT MAKE_SET(1 | 4,'hello','nice','world');  -- hello,world
SELECT MAKE_SET(0,'a','b','c');  -- ''

export_set(bits,on,off,[separator,[number_of_bits]])
按bits排列字符串集,只有当位等于1时插入字串on,否则插入off
separator默认值","
number_of_bits参数使用时长度不足补0而过长截断

SELECT EXPORT_SET(5,'fuck','NOT','-',4); -- fuck-NOT-fuck-NOT

lcase(str) lower(str)
返回小写的字符串str
ucase(str) upper(str)
返回大写的字符串str

SELECT LCASE('FUCK'),LOWER('FACK'),UPPER('fuck'),UCASE('fuck');  -- fuck    fack    FUCK    FUCK

数字函数

abs(n) 返回绝对值
sign(n) 返回参数的符号（1，0，-1）

SELECT abs(-10),abs(12) ; -- 10 12
SELECT SIGN(0),SIGN(12),SIGN(-12); -- 0 1  -1

mod(n,m) 求模

SELECT MOD(234,10);   -- 4
SELECT 234%10 ;

floor(n)
返回不大于n的最大整数值
ceiling(n)
返回不小于n的最小整数值
round(n,d)
返回n的四舍五入值,保留d位小数(d的默认值为0)
truncate(n,d)
保留数字n的d位小数并返回

SELECT FLOOR(1.23), FLOOR(-1.23); -- 1 -2
SELECT CEILING(1.23),CEILING(-1.23); -- 2,-1
SELECT ROUND(12,3);
SELECT ROUND(12.345,2);   -- 12.35

SELECT TRUNCATE(12.3456,3);  -- 12.345

pow(x,y) power(x,y)
返回值x的y次幂
sqrt(n)
返回非负数n的平方根
pi()
返回圆周率

SELECT POW(2,10); -- 1024
SELECT SQRT(16);   -- 4
SELECT PI();    -- 3.141593

rand() rand(n)
返回在范围0到1.0内的随机浮点值(可以使用数字n作为初始值)
order by rand() 随机查询取前几条记录

SELECT RAND(),RAND(20);  -- 0.44084669504393514   0.15888261251047497

least(x,y,…)
返回最小值
greatest(x,y,…)
返回最大值

SELECT LEAST(2,0);  -- 0
SELECT LEAST(23.0,0.5,0,767.0);  -- 0
SELECT LEAST('b','a','c'); -- a

SELECT GREATEST(2,0);  -- 2
SELECT GREATEST(23.0,0.5,0,767.0);  -- 767
SELECT GREATEST('b','a','c'); -- c

转换函数

cast(expr AS type)
convert(expr,type)
更改相应字段数据类型

SELECT CAST(a as unsigned) as b 
FROM cardserver where order by b desc;

SELECT CONVERT(a ,unsigned) as b
FROM cardserver where order by b desc;

日期函数

dayofweek(date) ：一周中的第几日（星期几）
dayofmonth(date)：一月中的第几日(在1到31范围内)
dayofyear(date)：一年中的第几日(在1到366范围内)
weekofyear()：日期的日历星期数（在1到53范围内）

SELECT DAYOFWEEK('2019-09-27');  -- 6 （1 = 星期天）
SELECT DAYOFMONTH('2019-09-27');  -- 27
SELECT DAYOFYEAR('2019-09-27');  -- 270

week(date,first)：返回date是一年的第几周
month(date) ：返回date中的月份数值
quarter(date)：返回date是一年的第几个季度
year(date)：返回date的年份(范围在1000到9999)
hour(time)：返回time的小时数(范围是0到23)
minute(time)：返回time的分钟数(范围是0到59)
second(time)：返回time的秒数(范围是0到59)

(first默认值0,first取值1表示周一是周的开始,0从周日开始)
SELECT WEEK('2019-09-27');  -- 38
SELECT WEEK('2019-09-27',1);  -- 39

SELECT MONTH('2019-09-27'); -- 9
SELECT QUARTER('2019-09-27');       -- 3

SELECT QUARTER('2019-10-27');       -- 4


SELECT YEAR('2019-09-27');  -- 2019
SELECT HOUR('10:05:03');  -- 10
SELECT MINUTE('19-09-27 10:05:23');  -- 5
SELECT SECOND('19-09-28 10:13:11');   -- 11

date_add(date,interval expr type)
date_sub(date,interval expr type)　
adddate(date,interval expr type)
subdate(date,interval expr type)
对日期时间进行加减法运算
expr对date进行加减法的一个表达式字符串
type指明表达式expr应该如何被解释

type值	含义	expr格式
second	秒	second
minute	分	minute
hour	时	hour
day	天	day
month	月	month
year	年	year
minute_second	分钟和秒	minutes:seconds
hour_minute	小时和分钟	hours:minutes
day_hour	天和小时	days hours
year_month	年和月	years-months
hour_second	小时，分钟，秒	hours:minutes:seconds
day_minute	天,小时,分钟	days hours:minutes
day_second	天,小时,分钟,秒	days hours:minutes:seconds

SELECT '2019-09-27 15:44:23' + INTERVAL 1 SECOND ;  -- 2019-09-27 15:44:24
SELECT '2019-09-27 15:44:23' - INTERVAL 1 DAY ;     -- 2019-09-267 15:44:24
SELECT interval 1 day + '2019-09-27 15:44:23';  -- 2019-09-28 15:44:23

SELECT DATE_ADD('2019-09-27 15:44:23',INTERVAL 1 YEAR); 2020-09-27 15:44:23
SELECT DATE_ADD('2019-09-27 15:44:23',INTERVAL '1:1'  minute_second);   -- 2019-09-27 15:45:24
SELECT DATE_SUB('1998-01-01 00:00:00',interval '11:1:1' day_second); -- 1997-12-31 22:58:59
SELECT DATE_ADD('1998-01-01 00:00:00', interval '-110' day_hour); -- 1997-12-27 10:00:00
SELECT DATE_SUB('1998-01-02',INTERVAL 31 DAY);  -- 1997-12-02

SELECT DATE_ADD('1998-01-01 00:00:00', interval '-1 3' day_hour); -- 1997-12-30 21:00:00
SELECT DATE_ADD('2019-09-28 14:23:32',INTERVAL '1:2:3' HOUR_SECOND);  -- 2019-09-28 15:25:35

datediff(date1,date2) 函数返回两个日期之间的天数

SELECT DATEDIFF('2008-12-30','2008-12-29'); -- 1
SELECT DATEDIFF('2008-12-29','2008-12-30'); -- -1

date_format(date,format)
time_format(time,format)
根据format字符串格式化date值，后者只处理时间

%Y 四位数字表示的年份
%y 两位数字表示的年份
%m 两位数字表示的月份（01, 02, …, 12）
%c 数字表示的月份（1, 2, …., 12）
%d 两位数字表示月中的天数（00, 01,…, 31）
%e 数字形式表示月中的天数（1, 2， …, 31）

%H 两位数字形式的小时，24 小时（00,01, …, 23）
%h 两位数字形式的小时，12 小时（01,02, …, 12）
%k 数字形式的小时，24 小时（0,1, …, 23）
%l 数字形式的小时，12 小时（1, 2, …, 12）
%i/I 两位数字形式的分（ 00,01, …, 59）
%s/S 两位数字形式的秒（ 00,01, …, 59）

%T 24 小时的时间形式（hh:mm:ss）
%r 12 小时的时间形式（hh:mm:ss AM 或hh:mm:ss PM）
%p AM或PM

％W 一周中每一天的名称（Sunday, Monday, Saturday）
%a 一周中每一天名称的缩写（Sun, Mon, …, Sat）
%M 月名（January, February, …, December）
%b 缩写的月名（ Jan, Feb,…., Dec）
%D 英文后缀表示月中的天数（1st, 2nd, 3rd,…）

%w 以数字形式表示周中的天数（ 0 = Sunday, 1=Monday, …, 6=Saturday）
%j 以三位数字表示年中的天数（ 001, 002, …, 366）
%U 周（0, 1, 52），其中Sunday 为周中的第一天
%u 周（0, 1, 52），其中Monday 为周中的第一天
%% 字符%

SELECT DATE_FORMAT('2019-02-12 12:32:41','%Y年%m月%d日 %H%i%s'); -- 2019年02月12日 123241
SELECT TIME_FORMAT('2019-02-12 12:32:41','%Y年%m月%d日 %H%i%s');   -- 0000年00月00日 123241

时间函数

curdate() current_date()
以’yyyy-mm-dd’或yyyymmdd格式返回当前日期值
curtime() current_time()
以’hh:mm:ss’或hhmmss格式返回当前时间值
now() sysdate() current_timestamp()
以’yyyy-mm-dd hh:mm:ss’或yyyymmddhhmmss格式返回当前日期

SELECT CURDATE();   -- 2019-09-27
SELECT CURRENT_DATE(); -- 2019-09-27
SELECT CURTIME();   -- 15:37:04
SELECT CURRENT_TIME(); -- 15:37:04

SELECT CURDATE()+1; -- 20190928
SELECT CURRENT_DATE()+2; -- 20190929
SELECT CURTIME()+0; -- 154100
SELECT CURRENT_TIME()+1; -- 154101

SELECT NOW();   -- 2019-09-27 15:38:24
SELECT NOW() + 0;       -- 20190927153848

timestampdiff(interval,datetime_expr1,datetime_expr2)
返回日期或日期时间表达式datetime_expr1 和datetime_expr2the 之间的整数差。
其结果的单位由interval 参数给

TIMESTAMPADD(interval,int_expr,datetime_expr)
将整型表达式int_expr 添加到日期或日期时间表达式 datetime_expr中

- FRAC_SECOND  表示间隔是毫秒
- SECOND  秒
-  MINUTE  分钟
- HOUR  小时
- DAY  天
- WEEK  星期
- MONTH  月
- QUARTER  季度
- YEAR  年

SELECT TIMESTAMPDIFF(day,'2012-08-24','2012-08-30');   -- 6
SELECT TIMESTAMPDIFF(MINUTE,'2012-08-24 09:00:00','2012-08-30 12:00:00'); -- 8820

SELECT TIMESTAMPADD(MINUTE,20,'2012-08-24 09:00:00');  -- 2012-08-24 09:20:00

SQL Date 函数
SQL Date 函数标签（空格分隔）： SQL SQLServer SQL Date 函数 SQL 日期当我...
每天一SQL语句（04）：SQL 语句函数篇
【开篇】SQL 语句函数篇【1】SQL FUNCTIONS（函数） SQL 拥有很多可用于计数和计算的内建函数。...
Oracle_SQL函数-单行函数
SQL函数 SQL函数分类 SQL函数主要有两种，分为单行函数、多行函数单行函数：只对一行进行变换，每行返回一个结...
单行函数
一、SQL函数概述 SQL函数分类单行函数多行函数单行函数语法语法：函数名[(参数1，参数2，….)]注意：...
SQL内置函数使用手册
SQL中有很多特别棒的内置函数。本文SQL内置函数手册中总结了SQL中常用的内置函数，包含通用聚合函数、安全检测函...
（4）SparkSQL中如何定义UDF和使用UDF
Spark SQL中用户自定义函数，用法和Spark SQL中的内置函数类似；是saprk SQL中内置函数无法满...
hive窗口函数盘点
在支持窗口函数里的sql里，善用窗口函数，能降低sql编写复杂度并提高sql执行效率。窗口函数 function...
学习SQL的第十天（11月18日~11月19日）
SQL Date（日期）函数 MySQL Date 函数下表为 MySQL 中最重要的内建日期函数 SQL S...
SQL Server DATEADD() 函数
SQL Server DATEADD() 函数标签（空格分隔）： SQL SQLServer SQL Serve...
mysql操作
sql一些函数,复杂查询等基本操作 sql脚本函数GROUP_CONCAT -GROUP_CONCAT 这个函数...