字符串函数
1.ASCII(str)
返回字符串str的第一个字符的ASCII值(str是空串时返回0)
SELECT ASCII('2');
->50
2.ORD(str)
如果字符串str句首是单字节返回与ASCII()函数返回的相同值。
如果是一个多字节字符,以格式返回((first byte ASCII code)*256+(second byte ASCII code))[*256+third byte ASCIIcode...]
SELECT ORD('2');
->50
3.CONV(N,from_base,to_base)
对数字N进制转换,并转换为字串返回(任何参数为NULL时返回NULL,进制范围为2-36进制,当to_base是负数时N作为有符号数否则作无符号数,CONV以64位点精度工作)
SELECT CONV('a', 16, 2);
->1010
SELECT CONV('a', 16, 10);
->10
SELECT CONV('a', 16, 8);
->12
4.BIN(N)
把N转为二进制值并以字串返回(N是BIGINT数字,等价于CONV(N,10,2))
SELECT BIN(12);
->1100
5.OCT(N)
把N转为八进制值并以字串返回(N是BIGINT数字,等价于CONV(N,10,8))
SELECT OCT(12);
->14
6.HEX(N)
把N转为十六进制并以字串返回(N是BIGINT数字,等价于CONV(N,10,16))
SELECT HEX(255);
->FF
7.CHAR(N,...)
返回由参数N,...对应的ASCII代码字符组成的一个字串(参数是N,...是数字序列,NULL值被跳过)
SELECT CHAR(77,121,83,81,'76');
->MySQL
select CHAR(77,77.3,'77.3');
->MMM
8.CONCAT(str1,str2,...)
把参数连成一个长字符串并返回(任何参数是NULL时返回NULL)
SELECT CONCAT('My', 'S', 'QL');
->MySQL
SELECT CONCAT('My', NULL, 'QL');
->NULL
SELECT CONCAT(14.3);
->14.3
9.LENGTH(str)、OCTET_LENGTH(str)、CHAR_LENGTH(str)、CHARACTER_LENGTH(str)
返回字符串str的长度(对于多字节字符CHAR_LENGTH仅计算一次)
SELECT LENGTH('textverylong中文');
SELECT OCTET_LENGTH('textverylong中文');
->18
SELECT CHARACTER_LENGTH('textverylong中文');
SELECT CHAR_LENGTH('textverylong中文');
->14
10.LOCATE(substr,str)、POSITION(substr IN str)
返回字符串substr在字符串str第一次出现的位置(str不包含substr时返回0)
select LOCATE('bar', 'foobarbar');
SELECT POSITION('bar' IN 'foobarbar');
->4
11.LOCATE(substr,str,pos)
返回字符串substr在字符串str的第pos个位置起第一次出现的位置(str不包含substr时返回0)
SELECT LOCATE('bar', 'foobarbar',5);
->7
12.INSTR(str,substr)
返回字符串substr在字符串str第一次出现的位置(str不包含substr时返回0)
SELECT INSTR('foobarbar', 'bar');
->4
SELECT INSTR('xbar', 'foobar');
->0
13.LPAD(str,len,padstr)
用字符串padstr填补str左端直到字串长度为len并返回
SELECT LPAD('hi',4,'??');
->??hi
14.RPAD(str,len,padstr)
用字符串padstr填补str右端直到字串长度为len并返回
SELECT RPAD('hi',5,'?');
->hi???
15.LEFT(str,len)
返回字符串str的左端len个字符
SELECT LEFT('foobarbar', 5);
->fooba
16.RIGHT(str,len)
返回字符串str的右端len个字符
select RIGHT('foobarbar', 4);
->rbar
17.SUBSTRING(str,pos,len)、SUBSTRING(str FROM pos FOR len)、MID(str,pos,len)
返回字符串str的位置pos起len个字符(使用FROM的丑陋语法是ANSI SQL92标准)
SELECT SUBSTRING('Quadratically',5,6);
SELECT MID('Quadratically',5,6);
->ratica
18.SUBSTRING(str,pos)、SUBSTRING(str FROM pos)
返回字符串str的位置pos起的一个子串
SELECT SUBSTRING('Quadratically',5);
->ratically
19.SUBSTRING_INDEX(str,delim,count)
返回从字符串str的第count个出现的分隔符delim之后的子串(count为正数时从左向右查找并且返回左端,否则从右向左查找并返回右端子串)
SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);
->www.mysql
SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);
->mysql.com
20.LTRIM(str)
返回删除了左空格的字符串str
select LTRIM(' barbar');
->barbar
21.RTRIM(str)
返回删除了右空格的字符串str
SELECT RTRIM('barbar ');
->barbar
22.TRIM([[BOTH | LEADING | TRAILING] [remstr] FROM] str)
返回前缀或后缀remstr被删除了的字符串str(位置参数默认BOTH,remstr默认值为空格)
SELECT TRIM(' bar ');
->bar
SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx');
->barxxx
SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx');
->bar
select TRIM(TRAILING 'xyz' FROM 'barxxyz');
->barx
23.SOUNDEX(str)
返回str的一个同音字符串(听起来“大致相同”字符串有相同的同音字符串,非数字字母字符被忽略,在A-Z外的字母被当作元音)
SELECT SOUNDEX('Hello');
->H400
24.SPACE(N)
返回由N个空格字符组成的一个字符串
SELECT SPACE(6);
->
25.REPLACE(str,from_str,to_str)
用字符串to_str替换字符串str中的子串from_str并返回
SELECT REPLACE('www.mysql.com', 'w', 'Ww');
->WwWwWw.mysql.com
26.REPEAT(str,count)
返回由count个字符串str连成的一个字符串(任何参数为NULL时返回NULL,count<=0时返回一个空字符串)
SELECT REPEAT('MySQL', 3);
MySQLMySQLMySQL
27.REVERSE(str)
颠倒字符串str的字符顺序并返回
SELECT REVERSE('abc');
->cba
28.INSERT(str,pos,len,newstr)
把字符串str由位置pos起len个字符长的子串替换为字符串newstr并返回
SELECT INSERT('Quadratic', 3, 4, 'What');
->QuWhattic
29.ELT(N,str1,str2,str3,...)
返回第N个字符串(N小于1或大于参数个数返回NULL)
SELECT ELT(1, 'ej', 'Heja', 'hej', 'foo');
->ej
select ELT(4, 'ej', 'Heja', 'hej', 'foo');
->foo
30.FIELD(str,str1,str2,str3,...)
返回str等于其后的第N个字符串的序号(如果str没找到返回0)
SELECT FIELD('ej', 'Hej', 'ej', 'Heja', 'hej','foo');
->2
31.FIND_IN_SET(str,strlist)
返回str在字符串集strlist中的序号(任何参数是NULL则返回NULL,如果str没找到返回0,参数1包含","时工作异常)
SELECT FIND_IN_SET('b','a,b,c,d');
->2
32.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');
->''
33.EXPORT_SET(bits,on,off,[separator,[number_of_bits]])
按bits排列字符串集,只有当位等于1时插入字串on,否则插入off(separator默认值"," ,number_of_bits参数使用时，bits的实际长度不足number_of_bits时候补0而过长截断)
SELECT EXPORT_SET(5,'Y','N',',',4)
->Y,N,Y,N
SELECT EXPORT_SET(5,'Y','N',',',3)
->Y,N,Y 截断
SELECT EXPORT_SET(5,'Y','N',',',5)
->Y,N,Y,N,N 补0
34.LCASE(str)、LOWER(str)
返回小写的字符串str
SELECT LCASE('QUADRATICALLY');
SELECT LOWER('QUADRATICALLY');
->quadratically
35.UCASE(str)、UPPER(str)
返回大写的字符串str
SELECT UCASE('quadratically');
SELECT UPPER('quadratically');
->QUADRATICALLY
36.LOAD_FILE(file_name)
读入文件并且作为一个字符串返回文件内容(文件无法找到,路径不完整,没有权限,长度大于max_allowed_packet会返回NULL)
UPDATE table_name SET blob_column=LOAD_FILE("/tmp/picture") WHERE id=1;
数学函数
1.ABS(N)
返回N的绝对值
select ABS(2);
->2
select ABS(-32);
->32
2.SIGN(N)
返回参数的符号(为-1、0或1)
select SIGN(-32)
->-1
select SIGN(0)
->0
select SIGN(234)
->1
3.MOD(N,M)
取模运算,返回N被M除的余数(同%操作符)
SELECT MOD(234, 10);
->4
select 234 % 10;
->4
4.FLOOR(N)
返回不大于N的最大整数值
select FLOOR(1.23);
->1
SELECT FLOOR(-1.23);
->-2
5.CEILING(N)
返回不小于N的最小整数值
select CEILING(1.23);
->2
SELECT CEILING(-1.23);
->-1
6.ROUND(N,D)
返回N的四舍五入值,保留D位小数(D的默认值为0)
select ROUND(-1.23);
->-1
select ROUND(-1.58);
->-2
select ROUND(1.58);
->2
select ROUND(1.298, 1);
->1.3
select ROUND(1.298, 0);
->1
7.EXP(N)
返回值e的N次方(自然对数的底)
select EXP(2);
->7.38905609893065
select EXP(-2);
->0.1353352832366127
8.LOG(N)
返回N的自然对数
SELECT LOG(2);
->0.6931471805599453
SELECT LOG(-2);
->NULL
9.LOG10(N)
返回N以10为底的对数
SELECT LOG10(2);
->0.3010299956639812
select LOG10(100);
->2.000000
select LOG10(-100);
->NULL
10.POW(X,Y)、POWER(X,Y)
返回值X的Y次幂
select POW(2,2);
->4
select POW(2,-2);
->0.25
11.SQRT(N)
返回非负数N的平方根
SELECT SQRT(4);
->2
SELECT SQRT(-4);
->NULL
12.PI()
返回圆周率
select PI();
->3.141593
13.COS(N)
返回N的余弦值
SELECT COS(PI());
->-1
14.SIN(N)
返回N的正弦值
SELECT SIN(PI());
->0.000000
15.TAN(N)
返回N的正切值
SELECT TAN(PI()+1);
->1.5574077246549018
16.ACOS(N)
返回N反余弦(N是余弦值,在-1到1的范围,否则返回NULL)
SELECT ACOS(1);
->0
select ACOS(1.0001);
->NULL
select ACOS(0);
->1.5707963267948966
17.ASIN(N)
返回N反正弦值
SELECT ASIN(0.2);
->0.2013579207903308
select ASIN('foo');
->0
18.ATAN(N)
返回N的反正切值
select ATAN(2);
->1.1071487177940904
select ATAN(-2);
->-1.1071487177940904
19.ATAN2(X,Y)
返回2个变量X和Y的反正切(类似Y/X的反正切,符号决定象限)
SELECT ATAN(-2,2);
->-0.7853981633974483
select ATAN(PI(),0);
->1.570796
20.COT(N)
返回X的余切
select COT(12);
->-1.5726734063976893
21.RAND()、RAND(N)
返回在范围0到1.0内的随机浮点值(可以使用数字N作为初始值)
SELECT RAND();
->0.10821635193025353
select RAND(20);
->0.15888261251047497
22.DEGREES(N)
把N从弧度变换为角度并返回
SELECT DEGREES(PI());
->180
23.RADIANS(N)
把N从角度变换为弧度并返回
SELECT RADIANS(90);
->1.5707963267948966
24.TRUNCATE(N,D)
保留数字N的D位小数并返回
select TRUNCATE(1.223,1);
->1.2
SELECT TRUNCATE(1.999,2);
->1.99
select TRUNCATE(1.999,0);
->1
25.LEAST(X,Y,...)
返回最小值(如果返回值被用在整数(实数或大小敏感字串)上下文或所有参数都是整数(实数或大小敏感字串)则他们作为整数(实数或大小敏感字串)比较,否则按忽略大小写的字符串被比较)
SELECT LEAST(2,0);
->0
SELECT LEAST(34.0,3.0,5.0,767.0);
->3.0
select LEAST("B","A","C");
->A
26.GREATEST(X,Y,...)
返回最大值(其余同LEAST())
SELECT GREATEST(2,0);
->2
select GREATEST(34.0,3.0,5.0,767.0);
->767.0
SELECT GREATEST("B","A","C");
->C
时期时间函数
1.DAYOFWEEK(date)
返回日期date是星期几(1=星期天,2=星期一,……7=星期六,ODBC标准)
SELECT DAYOFWEEK('2015-04-07');
->3
2.WEEKDAY(date)
返回日期date是星期几(0=星期一,1=星期二,……6= 星期天)。
SELECT WEEKDAY('2015-04-07');
->1
3.DAYOFMONTH(date)
返回date是一月中的第几日(在1到31范围内)
SELECT DAYOFMONTH('2015-04-07');
->7
4.DAYOFYEAR(date)
返回date是一年中的第几日(在1到366范围内)
SELECT DAYOFYEAR('2015-04-07');
->97
5.MONTH(date)
返回date中的月份数值
SELECT MONTH('2015-04-07');
->4
6.DAYNAME(date)
返回date是星期几(按英文名返回)
SELECT DAYNAME("2015-04-07");
->Tuesday
7.MONTHNAME(date)
返回date是几月(按英文名返回)
SELECT MONTHNAME("2015-04-07");
->April
8.QUARTER(date)
返回date是一年的第几个季度
SELECT QUARTER('2015-04-07');
->2
9.WEEK(date,first)
返回date是一年的第几周(first默认值0,first取值1表示周一是周的开始,0从周日开始)
SELECT WEEK('2015-04-07');
->14
SELECT WEEK('2015-04-07', 0);
->14
SELECT WEEK('2015-04-07', 1);
->15
10.YEAR(date)
返回date的年份(范围在1000到9999)
SELECT YEAR('2015-04-07');
->2015
11.HOUR(time)
返回time的小时数(范围是0到23)
select HOUR('10:05:03');
->10
12.MINUTE(time)
返回time的分钟数(范围是0到59)
SELECT MINUTE('2015-04-07 10:05:03');
->5
13.SECOND(time)
返回time的秒数(范围是0到59)
SELECT SECOND('10:05:03');
->3
14.PERIOD_ADD(P,N)
增加N个月到时期P并返回(P的格式YYMM或YYYYMM)
SELECT PERIOD_ADD(201504,2);
->201506
15.PERIOD_DIFF(P1,P2)
返回在时期P1和P2之间月数(P1和P2的格式YYMM或YYYYMM)
SELECT PERIOD_DIFF(9802,199703);
->11
16.DATE_ADD(date,INTERVAL expr type)、DATE_SUB(date,INTERVAL expr type)、ADDDATE(date,INTERVAL expr type)、SUBDATE(date,INTERVAL expr type)
对日期时间进行加减法运算
ADDDATE()和SUBDATE()是DATE_ADD()和DATE_SUB()的同义词,也可以用运算符+和-而不是函数
date是一个DATETIME或DATE值,expr对date进行加减法的一个表达式字符串type指明表达式expr应该如何被解释
[type值 含义 期望的expr格式]:
SECOND 秒 SECONDS
MINUTE 分钟 MINUTES
HOUR 时间 HOURS
DAY 天 DAYS
MONTH 月 MONTHS
YEAR 年 YEARS
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"
expr中允许任何标点做分隔符,如果所有是DATE值时结果是一个DATE值,否则结果是一个DATETIME值)
如果type关键词不完整,则MySQL从右端取值,DAY_SECOND因为缺少小时分钟等于MINUTE_SECOND)
如果增加MONTH、YEAR_MONTH或YEAR,天数大于结果月份的最大天数则使用最大天数)
SELECT "1997-12-31 23:59:59" + INTERVAL 1 SECOND;
->1998-01-01 00:00:00
SELECT INTERVAL 1 DAY + "1997-12-31";
->1998-01-01
SELECT "1998-01-01" - INTERVAL 1 SECOND;
->1997-12-31 23:59:59
SELECT DATE_ADD("1997-12-31 23:59:59",INTERVAL 1 SECOND);
->1998-01-01 00:00:00
SELECT DATE_ADD("1997-12-31 23:59:59",INTERVAL 1 DAY);
->1998-01-01 23:59:59
SELECT DATE_ADD("1997-12-31 23:59:59",INTERVAL "1:1" MINUTE_SECOND);
->1998-01-01 00:01:00
SELECT DATE_SUB("1998-01-01 00:00:00",INTERVAL "1:1:1:1" DAY_SECOND);
->1997-12-30 22:58:59
SELECT DATE_ADD("1998-01-01 00:00:00", INTERVAL "-1:10" DAY_HOUR);
->1997-12-30 14:00:00
SELECT DATE_SUB("1998-01-02", INTERVAL 31 DAY);
->1997-12-02
SELECT EXTRACT(YEAR FROM "1999-07-02");
->1999
SELECT EXTRACT(YEAR_MONTH FROM "1999-07-02 01:02:03");
->199907
SELECT EXTRACT(DAY_MINUTE FROM "1999-07-02 01:02:03");
->20102
17.TO_DAYS(date)
返回日期date是西元0年至今多少天(不计算1582年以前)
select TO_DAYS(150407);
->736060
SELECT TO_DAYS('2015-04-07');
->736060
18.FROM_DAYS(N)
给出西元0年至今多少天返回DATE值(不计算1582年以前)
SELECT FROM_DAYS(736060);
->2015-04-07
19.DATE_FORMAT(date,format)
根据format字符串格式化date值
format字符串中可用标志符:
%M 月名字(January……December)
%W 星期名字(Sunday……Saturday)
%D 有英语前缀的月份的日期(1st, 2nd, 3rd, 等等。）
%Y 年, 数字, 4 位
%y 年, 数字, 2 位
%a 缩写的星期名字(Sun……Sat)
%d 月份中的天数, 数字(00……31)
%e 月份中的天数, 数字(0……31)
%m 月, 数字(01……12)
%c 月, 数字(1……12)
%b 缩写的月份名字(Jan……Dec)
%j 一年中的天数(001……366)
%H 小时(00……23)
%k 小时(0……23)
%h 小时(01……12)
%I 小时(01……12)
%l 小时(1……12)
%i 分钟, 数字(00……59)
%r 时间,12 小时(hh:mm:ss [AP]M)
%T 时间,24 小时(hh:mm:ss)
%S 秒(00……59)
%s 秒(00……59)
%p AM或PM
%w 一个星期中的天数(0=Sunday ……6=Saturday ）
%U 星期(0……52), 这里星期天是星期的第一天
%u 星期(0……52), 这里星期一是星期的第一天
%% 字符%
SELECT DATE_FORMAT('1997-10-04 22:23:00','%W %M %Y');
->Saturday October 1997
select DATE_FORMAT('1997-10-04 22:23:00','%H:%i:%s');
->22:23:00
SELECT DATE_FORMAT('1997-10-04 22:23:00','%D %y %a %d %m %b %j');
->4th 97 Sat 04 10 Oct 277
SELECT DATE_FORMAT('1997-10-04 22:23:00','%H %k %I %r %T %S %w');
->22 22 10 10:23:00 PM 22:23:00 00 6
20.TIME_FORMAT(time,format)
和DATE_FORMAT()类似,但TIME_FORMAT只处理小时、分钟和秒(其余符号产生一个NULL值或0)
SELECT TIME_FORMAT('1997-10-04 22:23:00','%H');
->22
21.CURDATE()、CURRENT_DATE()
以'YYYY-MM-DD'或YYYYMMDD格式返回当前日期值(根据返回值所处上下文是字符串或数字)
SELECT CURDATE();
->2015-04-08
SELECT CURDATE() + 0;
->20150408
22.CURTIME()、CURRENT_TIME()
以'HH:MM:SS'或HHMMSS格式返回当前时间值(根据返回值所处上下文是字符串或数字)
SELECT CURTIME();
->09:03:48
select CURTIME() + 0;
->90425
23.NOW()、SYSDATE()、CURRENT_TIMESTAMP()
以'YYYY-MM-DD HH:MM:SS'或YYYYMMDDHHMMSS格式返回当前日期时间(根据返回值所处上下文是字符串或数字)
select NOW();
->2015-04-08 09:07:48
select NOW() + 0;
->20150408091501
24.UNIX_TIMESTAMP()、UNIX_TIMESTAMP(date)
返回一个Unix时间戳(从'1970-01-01 00:00:00'GMT开始的秒数,date默认值为当前时间)
select UNIX_TIMESTAMP();
->1428455750
select UNIX_TIMESTAMP('1997-10-04 22:23:00');
->875974980
25.FROM_UNIXTIME(unix_timestamp)
以'YYYY-MM-DD HH:MM:SS'或YYYYMMDDHHMMSS格式返回时间戳的值(根据返回值所处上下文是字符串或数字)
SELECT FROM_UNIXTIME(875996580);
->1997-10-05 04:23:00
SELECT FROM_UNIXTIME(875996580) + 0;
->19971005042300
26.FROM_UNIXTIME(unix_timestamp,format)
以format字符串格式返回时间戳的值
SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(),'%Y %D %M %h:%i:%s %x');
->2015 8th April 09:21:22 2015
27.SEC_TO_TIME(seconds)
以'HH:MM:SS'或HHMMSS格式返回秒数转成的TIME值(根据返回值所处上下文是字符串或数字)
SELECT SEC_TO_TIME(2378);
->00:39:38
select SEC_TO_TIME(2378) + 0;
->3938
28.TIME_TO_SEC(time)
返回time值有多少秒
SELECT TIME_TO_SEC('22:23:00');
->80580