MySQL——MySQL中的常用函数

来源：互联网发布：js获取标签classname 编辑：程序博客网时间：2024/05/16 06:58

字符串函数

字符串函数是字符串函数是最常用的一种函数。

函数描述CONCAT(S1,S2,…,Sn)连接S1，S2，…,Sn为一个字符串CONTACT_WS(separator,S1,S2,…,Sn)以separator为分隔符连接S1，S2，…,SnINSERT(str,x,y,instr)将字符串从第x位置开始，y个字符长的子串替换为字符串instrLOWER(str)将字符串中所有字符变为小写UPPER(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)去掉字符串str左侧的空格RTRIM(str)去掉字符串str右侧的空格TRIM(str)去掉字符串行尾和行头的空格REPEAT(str,x)返回str重复x次的结果REPLACE(str,a,b)用字符串b替换字符串str中所有出现的字符串aSTRCMP(s1,s2)比较字符串s1和s2SUBSTRING(str,x,y)返回从字符串str x位置起y个字符长度的子串

CONCAT上一步
CONCAT(s1,s2,…,sn)函数：把传入的参数连接成为一个字符串。可接收一个或多个参数。如果所有的参数都是二进制字符串，返回的结果是一个二进制字符串。如果参数包括任何二进制字符串，那么结果就是二进制字符串。数值参数转换为等价的二进制字符串形式。
如果参数中包含NULL，则结果就是NULL。
mysql> SELECT CONCAT(‘My’, ‘S’, ‘QL’);
+————————-+
| CONCAT(‘My’, ‘S’, ‘QL’) |
+————————-+
| MySQL |
+————————-+
1 row in set (0.00 sec)
mysql> SELECT CONCAT(‘My’, NULL, ‘QL’);
+————————–+
| CONCAT(‘My’, NULL, ‘QL’) |
+————————–+
| NULL |
+————————–+
1 row in set (0.00 sec)
带引号的字符串，拼接操作可以简化为依次放置的SELECT语句：mysql> SELECT ‘My’ ‘S’ ‘QL’;
+——-+
| My |
+——-+
| MySQL |
+——-+
1 row in set (0.00 sec)
CONTACT_WS上一步
CONTACT_WS是CONTACT With Separator的简写，意思就是以指定的分隔符进行拼接。分隔符可以是一个字符串，如果分隔符是NULL，结果为NULL。mysql> SELECT CONCAT_WS(‘NULL’,’First name’,’Second name’,’Last Name’);
+———————————————————-+
| CONCAT_WS(‘NULL’,’First name’,’Second name’,’Last Name’) |
+———————————————————-+
| First nameNULLSecond nameNULLLast Name |
+———————————————————-+
1 row in set (0.00 sec)
mysql> SELECT CONCAT_WS(NULL,’First name’,’Second name’,’Last Name’);
+——————————————————–+
| CONCAT_WS(NULL,’First name’,’Second name’,’Last Name’) |
+——————————————————–+
| NULL |
+——————————————————–+
1 row in set (0.00 sec)
注意：第一个sql中的’NULL’不是NULL值，只是一个字符串。concat_ws()不跳过空字符串。但是会跳过分隔符参数后的任何NULL值。mysql> SELECT CONCAT_WS(‘,’,’First name’,’Second name’,’Last Name’);
+——————————————————-+
| CONCAT_WS(‘,’,’First name’,’Second name’,’Last Name’) |
+——————————————————-+
| First name,Second name,Last Name |
+——————————————————-+
1 row in set (0.00 sec)
mysql> SELECT CONCAT_WS(‘,’,’First name’,NULL,’Last Name’);
+———————————————-+
| CONCAT_WS(‘,’,’First name’,NULL,’Last Name’) |
+———————————————-+
| First name,Last Name |
+———————————————-+
1 row in set (0.00 sec)
INSERT上一步
INSERT(str,pos,len,instr)函数：将字符串从第pos位置开始，len个字符长的子串替换为字符串instr。如果pos不在str的长度内，则返回原字符串；pos后面的字符串长度小于len，返回instr替换pos后的字符串；如果任何参数为NULL，则返回值为NULL。mysql> SELECT INSERT(‘Quadratic’, 3, 4, ‘What’);
-> ‘QuWhattic’
mysql> SELECT INSERT(‘Quadratic’, -1, 4, ‘What’);
-> ‘Quadratic’
mysql> SELECT INSERT(‘Quadratic’, 3, 100, ‘What’);
-> ‘QuWhat’
LOWER上一步
LOWER(str)函数：将str转换成小写。在字符串比较中，通常要将比较的字符串转换成大写或者小写。
UPPER上一步
UPPER(str)函数：将str转换成大写。
LEFT上一步
LEFT(str,len)函数：返回字符串最左边的len个字符，如果参数中有NULL，则返回NULL。
RIGHT上一步
RIGHT(str,len)函数：返回字符串最右边的len个字符，如果参数中有NULL，则返回NULL。
LPAD上一步
LPAD(str,len,padstr)函数：以padstr左填充字符串str到len个字符长度。如果str大于len，则返回str截断到len长度的字符串。 mysql> SELECT LPAD(‘hi’,4,’??’);
-> ‘??hi’
mysql> SELECT LPAD(‘hi’,1,’??’);
-> ‘h’
RPAD上一步
类似于LPAD。
LTRIM上一步
LTRIM(str)函数：返回去掉左边空格的字符串。
RTRIM上一步
RTRIM(str)函数：返回去掉右边空格的字符串。
TRIM上一步
TRIM(str)函数：返回去掉两边空格的字符串。
REPEAT上一步
REPEAT(str,count)函数：返回str重复count次的字符串；如果count小于1，返回一个空串；如果count为NULL，则返回NULL。mysql> SELECT REPEAT(‘MySQL’, 3);
-> ‘MySQLMySQLMySQL’
mysql> SELECT REPEAT(‘MySQL’, 0);
->
mysql> SELECT REPEAT(‘MySQL’, NULL);
->NULL
REPLACE上一步
REPLACE(str,from_str,to_str)函数：用字符串to_str替换字符串str中所有出现的字符串from_str。
STRCMP上一步
STRCMP(s1,s2)函数：比较字符串s1和s2的ASCII码值的大小。相等返回0；s1大于s2返回1；s1小于s2返回-1.
STRCMP使用参数的排序规则进行比较。mysql> SET @s1 = _latin1 ‘x’ COLLATE latin1_general_ci;
mysql> SET @s2 = _latin1 ‘X’ COLLATE latin1_general_ci;
mysql> SET @s3 = _latin1 ‘x’ COLLATE latin1_general_cs;
mysql> SET @s4 = _latin1 ‘X’ COLLATE latin1_general_cs;
mysql> SELECT STRCMP(@s1, @s2), STRCMP(@s3, @s4);
+——————+——————+
| STRCMP(@s1, @s2) | STRCMP(@s3, @s4) |
+——————+——————+
| 0 | 1 |
+——————+——————+
如果两个参数的比较规则不同，两个参数必须转换为相同的排序规则：mysql> SELECT STRCMP(@s1, @s3);
ERROR 1267 (HY000): Illegal mix of collations (latin1_general_ci,IMPLICIT)
and (latin1_general_cs,IMPLICIT) for operation ‘strcmp’
mysql> SELECT STRCMP(@s1, @s3 COLLATE latin1_general_ci);
+——————————————–+
| STRCMP(@s1, @s3 COLLATE latin1_general_ci) |
+——————————————–+
| 0 |
+——————————————–+
SUBSTRING上一步
SUBSTRING(str,pos), SUBSTRING(str FROM pos), SUBSTRING(str,pos,len), SUBSTRING(str FROM pos FOR len)函数：返回从字符串str中的第pos位置开始len长度的字符串。 mysql> SELECT SUBSTRING(‘Quadratically’,5);
-> ‘ratically’
mysql> SELECT SUBSTRING(‘foobarbar’ FROM 4);
-> ‘barbar’
mysql> SELECT SUBSTRING(‘Quadratically’,5,6);
-> ‘ratica’
mysql> SELECT SUBSTRING(‘Sakila’, -3);
-> ‘ila’
mysql> SELECT SUBSTRING(‘Sakila’, -5, 3);
-> ‘aki’
mysql> SELECT SUBSTRING(‘Sakila’ FROM -4 FOR 2);
-> ‘ki’

数值函数

MySQL中另外一类很重要的函数就是数值函数，这些函数能处理很多数值方面的运算。

函数描述ABS(x)返回x的绝对值 CONV(N,from_base,to_base)进制转换函数CEIL(x)进一法取近似值，返回大于或等于x的最小整数值FLOOR(x)去尾法取近似值，返回小于或等于x的最大整数值POW(x,y)x的y次幂运算ROUND(x,y)返回参数x的四舍五入的有y位小数的值MOD(x,y)返回x/y的模RAND()返回0~1内的随机数TRUNCATE(x,y)返回数字截断为y位小数的结果

ABS上一步
ABS(x)函数：返回x的绝对值。
CONV上一步
CONV(N,from_base,to_base)函数：返回将N从from_base进制转换为to_base的字符串表示形式。任一参数为NULL，则返回值为NULL。参数N将被解释为一个整数，但是可以传入整数或是字符串。from_base和to_base取值范围为2~36.如果，参数from_base是负数，N将视为有符号数，否则N被视为无符号数，CONV()函数的精度为64位。 mysql> SELECT CONV(‘a’,16,2);
-> ‘1010’
mysql> SELECT CONV(‘6E’,18,8);
-> ‘172’
mysql> SELECT CONV(-17,10,-18);
-> ‘-H’
mysql> SELECT CONV(10+’10’+’10’+X’0a’,10,10);
-> ‘40’
CEIL上一步
CEIL(x)函数：同CEILING(x)，进一法取近似值，这种说法更专业一些。官网给出的解释是“返回不小于x的最小整数值。”但是有些资料甚至是教材中的解释“返回大于x的最小整数值”，这种说法是错误的，例如：mysql> SELECT CEIL(1);
->1按照有些教材中的解释，得到的结果应该是2，但是实际返回1，有点编程基础的朋友都知道，ceil在多数语言中都表示进一法；同理，floor表示去尾法取近似值；round表示标准四舍五入近似值。
FLOOR上一步
FLOOR(x)函数：去尾法取近似值。
ROUND上一步
ROUND(x,y)函数，返回x四舍五入后保留y位小数的近似值。
MOD上一步
MOD(x,y)函数：返回x/y的模，和x%y的结果相同，参数中任一为NULL，则结果为NULL，若y为0，结果返回NULL：mysql> SELECT MOD(234, 10);
-> 4
mysql> SELECT 253 % 7;
-> 1
mysql> SELECT MOD(29,9);
-> 2
mysql> SELECT 29 MOD 9;
-> 2
mysql> SELECT MOD(234, NULL);
-> NULL
mysql> SELECT MOD(234, 0);
-> NULLMOD函数也适用于小数取模，返回整数部分的模加上小数部分：mysql> SELECT MOD(34.5,3);
-> 1.5
RAND上一步
RAND()函数：返回0~1随机的浮点数。利用此函数，可以取任意范围内的随机数，取值范围R:[i,j)的表达公式为： FLOOR(i + RAND() * (j − i))，比如[50,150)：SELECT FLOOR(50+RAND()*(150-50));
->102
TRUNCATE上一步
TRUNCATE(x,y)函数：返回数字x截断为y位小数的结果。如果y为0，则返回值中没有小数部分，y可以是负数，此时返回值对应的位上为0：mysql> SELECT TRUNCATE(1.223,1);
-> 1.2
mysql> SELECT TRUNCATE(1.999,1);
-> 1.9
mysql> SELECT TRUNCATE(1.999,0);
-> 1
mysql> SELECT TRUNCATE(-1.999,1);
-> -1.9
mysql> SELECT TRUNCATE(122,-2);
-> 100
mysql> SELECT TRUNCATE(10.28*100,0);
-> 1028

日期和时间函数

注：日期和时间函数比较复杂，详情见官网或使用MySQL帮助命令查看说明

1.加减函数

函数描述 ADDDATE()、ADDTIME()，DATE_ADD() 参数时间表达式加指定时间间隔 DATE_SUB()，SUBDATE() 时间减指定时间间隔计算 DATEDIFF() 两个日期表达式间的减计算 PERIOD_ADD() 月份加计算 PERIOD_DIFF() 两个日期表达式的月份差 SUBTIME() 计算两个时间点的差值 TIMEDIFF() 计算两个时间点的差值 TIMESTAMPDIFF() 计算两个时间点的差值，返回值按指定时间单位计算 TIMESTAMPADD() 返回参数加一个时间间隔后的时间戳表达形式

2.日期提取函数

函数描述 CURDATE()，CURRENT_DATE(), CURRENT_DATE 当前日期 CURTIME()，CURRENT_TIME(), CURRENT_TIME 当前时间 DATE() 提取参数时间表达式中的日期 DAY()，DAYOFMONTH() 一个月中的第几天 DAYNAME() 星期几，如Saturday DAYOFWEEK() 一周中的第几天， (1 = Sunday, 2 = Monday, …, 7 = Saturday). DAYOFYEAR() 一年中的第几天(1-366) EXTRACT() 按指定的时间单位提取时间表达式中的日期 HOUR() 提取参数时间表达式中的小时 LAST_DAY 返回参数中指定月份的最后一天的日期表达式 NOW()，LOCALTIME(), LOCALTIME，LOCALTIMESTAMP, LOCALTIMESTAMP() 返回当前时间戳 MICROSECOND() 提取参数中的微秒 MINUTE() 提取参数中的分钟 MONTH() 提取参数中的月份 MONTHNAME() 提取参数表达式中的月份并返回对应的月份名称 QUARTER() 提取参数表达式中的季度 SECOND() 提取参数表达式中的秒 SYSDATE() 系统时间 TIME() 提取参数中的时间 WEEK() 返回星期名中的数值 WEEKDAY() 一周中的第几天(0 = Monday, 1 = Tuesday, … 6 = Sunday). WEEKOFYEAR() 一年中的第几周 YEAR() 提取参数表达式中的年数值 YEARWEEK() 提取参数表达式中的年和一年中的第几周

3.格式转换函数

函数描述 CONVERT_TZ() 不同时区之间时间转换 SEC_TO_TIME() 将参数给定的秒转换为时分秒表达形式HH:MM:SS TIMESTAMP() 一个参数时，返回参数的时间戳表达形式，两个参数时，返回两个参数的和的时间戳表达形式 TO_DAYS() 将参数转换为以天为单位计算的数值 TIME_FORMAT() 格式化日期显示 TIME_TO_SEC() 将给定的时间表达式转换为秒 MAKEDATE() 根据给定的参数生成日期表达式 MAKETIME() 根据给定的时分秒参数生成时间表达式 FROM_DAYS() 将给定的值转换为日期 GET_FORMAT() 返回指定的日期格式字符串 DATE_FORMAT() 格式化日期显示 STR_TO_DATE() 字符串转换成日期

流程函数

函数描述 IF(value,result_true,result_false) 如果value是真，返回result_true,否则返回result_false IFNULL(value1,value2) 如果value1不为NULL，返回value1，否则，返回value2 CASE WHEN [value1] THEN[result1]…ELSE[default] END 依次判断WHEN中的value是否为真，是真，返回紧随其后的THEN中的值，否则继续下一个WHEN，直至没有符合条件的，返回default CASE[expr] WHEN[value1] THEN[result1] … ELSE[default]END 依次判断WHEN中的value是否与expr相等，若相等，则返回紧随其后的THEN中的值，否则继续下一个WHEN，直至没有符合条件的，返回default

附录

日期格式

格式符号说明 %a Abbreviated weekday name (Sun..Sat) %b Abbreviated month name (Jan..Dec) %c Month, numeric (0..12) %D Day of the month with English suffix (0th, 1st, 2nd, 3rd, …) %d Day of the month, numeric (00..31) %e Day of the month, numeric (0..31) %f Microseconds (000000..999999) %H Hour (00..23) %h Hour (01..12) %I Hour (01..12) %i Minutes, numeric (00..59) %j Day of year (001..366) %k Hour (0..23) %l Hour (1..12) %M Month name (January..December) %m Month, numeric (00..12) %p AM or PM %r Time, 12-hour (hh:mm:ss followed by AM or PM) %S Seconds (00..59) %s Seconds (00..59) %T Time, 24-hour (hh:mm:ss) %U Week (00..53), where Sunday is the first day of the week; WEEK() mode 0 %u Week (00..53), where Monday is the first day of the week; WEEK() mode 1 %V Week (01..53), where Sunday is the first day of the week; WEEK() mode 2; used with %X %v Week (01..53), where Monday is the first day of the week; WEEK() mode 3; used with %x %W Weekday name (Sunday..Saturday) %w Day of the week (0=Sunday..6=Saturday) %X Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V %x Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v %Y Year, numeric, four digits %y Year, numeric (two digits) %% A literal % character %x x, for any “x” not listed above

0 0