字符串函数
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
网友评论