SQL中常用SQL函数

1.字符串函数

CONCAT(S1,S2,...Sn) 连接S1,S2,...Sn中所有非NULL值为一个字符串

INSERT(str,x,y,instr) 将字符串str从第x位置开始，y个字符长的字符串换为字符串instr

LOWER(str) 将字符串str中所有字符变为小写

UPPER(str) 将字符串str中所有字符变为大写

LEFT(str,x) 返回字符串str最右边的x个字符

RIGHT(str,x) 返回字符串str最右边的x个字符

LPAD(str,n,pad) 用字符串pad对str最左边进行填充，直到长度为n个字符长度

RPAD(str,n,pad) 用字符串pad对str最右边进行填充，直到长度为n个字符长度

LTRIM(str,char=' ') 去掉str左侧的char中所有的字符

RTRIM(str,char=' ') 去掉str右侧的char中所有的字符

TRIM(str,char=' ') 去掉str左右两侧的char中所有的字符

REPEAT(str,x) 返回str重复x次的结果

REPLACE(str,a,b) 用字符串b替换字符串str中所有出现的字符串a

STRCMP(s1,s2) 比较字符串s1和s2

SUBSTRING(str,x,y) 返回从字符串str x位置起y个字符长度的字符串

返回从str的索引x位置起长度为y的字符串
索引从1开始
Y为负数则从右开始
Z为负数则从索引Y处向左截取
省略Z截取到末尾

LENGTH(str) 返回字符串长度

2.数值函数

ABS(x) 返回x的绝对值

CEIL(x) 返回大于x的最小整数值

FLOOR(x) 返回小于x的最大整数值

MOD(x,y) 返回x/y的模

RAND() 返回0-1内的随机值

ROUND(x,y) 返回参数x的四舍五入的有y位小数的值

TRUNCATE(x,y) 返回数字x截断为y位小数的结果

MAX 返回最大值

MIN 返回最小值

AVG 返回平均值

SUM 返回计算的总和

3.日期和时间函数

CURDATE() 返回当前日期

CURTIME() 返回当前时间

NOW() 返回当前的日期和时间

UNIX_TIMESTAMP(date) 返回日期date的UNIX时间戳

FROM_UNIXTIME() 返回UNIX时间戳的日期值

WEEK(date) 返回日期date为一年中的第几周

YEAR(date) 返回日期date的年份

HOUR(time) 返回time的小时值

MINUTE(time) 返回time的分钟值

MONTHNAME(date) 返回date的月份名

DATE_FORMAT(date,fmt) 返回按字符串fmt格式化日期date值

DATE_ADD(add,INTERVAL expr type) 返回一个日期或时间值加上一个时间间隔的时间值

DATEDIFF(expr,expr2) 返回起始时间expr和结束时间expr2之间的天数

4.流程函数

IF(value,t,f) 如果value是真，返回t,否则返回f

IFNULL(value1,value2) 如果value1不为空(null)，返回value1本身，否则返回value2

CASE WHEN [value1] THEN [result1]...ELSE[default]END 如果value1是真，返回result1，否则返回default(与if语句相似)

CASE [expr] WHEN [value1] THEN[result1]...ELSE[default]END 如果expr等于value1，返回result1，否则返回default(与switch语句相似)

例：case 1 when 1 then ‘一‘ when 2 then ‘二‘ when 3 then ‘三‘ else ‘未知数字‘end;

5.其他常用函数

COUNT 返回行数

DATABASE() 返回当前数据库名

VERSION() 返回当前数据库版本

USER() 显示当前登陆的用户名与它对应的host

CURRRENT_USE() 显示当前登陆用户对应在user表中的哪一个

INET_ATON(IP) 返回IP地址的数字表示

INET_NTOA(num) 返回数字代表的IP地址

PASSWORD(str) 返回字符串str的加密版本

MD5() 返回字符串str的MD5值

RAND() 随机排序返回数据

sqlite专有的函数

RANDOM() ：函数返回一个介于 -9223372036854775808 和 +9223372036854775807 之间的伪随机整数

changes() 返回由最近完成的UPDATE、UPDATE或UPDATE语句更改、插入或删除的数据库行数

total_changes() 自打开数据库以来的行数

coalesce(X,Y,...) 返回第一个非NULL的值，如果都是null返回null，至少需要2个参数

concat_ws(SEP,X,...) 用SEP连接第2个参数开始的所有非NULL值为一个字符串，SEP为null返回null

iif(X,Y,Z) ：如果X为true返回Y，否则返回Z，类似于三元运算

instr(X,Y) ：返回字符串X中第一次出现Y的索引，索引从1开始，未找到返回0，有一个为null返回null

typeof(X) ：获取X的数据类型，可能的值"null", "integer", "real", "text", or "blob"

SQLITE_VERSION() :返回 SQLite 库的版本

sqlite本身没有DATE数据类型，但是确可以识别并解析一些有特定格式的数据作为日期进行解析，支持的数据类型

1、TEXT，格式：YYYY-mm-dd HH:MM:SS.SSS，如"2025-05-29 14:16:00.546"，秒的小数位可以省略
2、REAL，一个小数，表示从"-4713-11-24 12:00:00"以来经过的天数，如2460447.031672986
3、INT，表示从"1970-01-01 00:00:00"以来经过的秒数，如1715863536

sqlite的时间日期函数

date(time-value[, modifier])  ：返回日期，YYYY-mm-dd
time(time-value[, modifier])   ：返回时间，HH:MM:SS.SSS
datetime(time-value[, modifier])  ：返回日期和时间，YYYY-mm-dd HH:MM:SS.SSS
julianday(time-value[, modifier]) ：返回REAL表示天数的小数
unixepoch(time-value[, modifier]) ：返回INT表示秒数的整数
strftime(format, time-value[, modifier])  ：使用format格式化的字符串

time-value只能接受上面的格式1和格式2，不接受时间戳，但是可选参数modifier设置为 'auto' 或 'unixepoch'即可解析时间戳

format字符串接受的占位符

%Y / %G 4位年份(0000-9999)
%g      年份后2位
%m      月份(01-12)
%d      有前导零的日期  (01-31)
%e      没有前导零的日期(1-31)

%H      时间(00-24)，24小时制有前导零
%k      时间( 0-24)，24小时制无前导零
%I      时间(00-12)，12小时制有前导零
%l      时间( 0-12)，12小时制无前导零
%M      分钟(00-59)
%S      秒(00-59)
%f      带小数的秒，最多3位小数
%p / %P ：AM/PM或am/pm

%j      一年中的第几天(001-366)
%W      一年中的第几周(00-53)，第01周从第一个星期一开始
%U      一年中的第几周(00-53)，第01周从第一个星期日开始
%w      一周中的第几天(0-6)，0表示周日
%u      一周中的第几天(1-7)，1表示周一

%F      %Y-%m-%d
%T      %H:%M:%S
%J      Julian day number (fractional)
%s      seconds since 1970-01-01  自1970-01-01以来的秒数