深入浅出mysql-常用函数
5.常用函数
5.1 字符串函数
concat 字符串连接函数
是什么
CONCAT(S1,S2,…Sn)函数，把传入的参数连接成为一个字符串
范例
mysql> select concat('aaa','bbb','ccc') ,concat('aaa',null);
+---------------------------+--------------------+
| concat('aaa','bbb','ccc') | concat('aaa',null) |
+---------------------------+--------------------+
| aaabbbccc | NULL |
+---------------------------+--------------------+
1 row in set (0.05 sec)
INSERT(str,x,y,insterStr)
是什么
将str 从x位置开始的y个字符替换为insertStr
范例
下面的例子把字符串“beijing2008you”中的从第 12 个字符开始以后的 3 个字符替换成“me”。
mysql> select INSERT('beijing2008you',12,3, 'me') ;
+-------------------------------------+
| INSERT('beijing2008you',12,3, 'me') |
+-------------------------------------+
| beijing2008me |
+-------------------------------------+
1 row in set (0.00 sec)
LOWER(str) 和UPPER(str)
是什么
将字符串转换为小写或者大写
范例
在字符串比较中，通常要将比较的字符串全部转换为大写或者小写，如下例所示：
mysql> select LOWER('BEIJING2008'), UPPER('beijing2008');
+----------------------+----------------------+
| LOWER('BEIJING2008') | UPPER('beijing2008') |
+----------------------+----------------------+
| beijing2008 | BEIJING2008 |
+----------------------+----------------------+
1 row in set (0.00 sec)
LEFT(str,x), RIGHT(str，x)
是什么
返回字符串最左边x个字符 或字符串最右边x个字符
范例
mysql> SELECT LEFT('beijing2008',7),LEFT('beijing',null),RIGHT('beijing2008',4);
+-----------------------+----------------------+------------------------+
| LEFT('beijing2008',7) | LEFT('beijing',null) | RIGHT('beijing2008',4) |
+-----------------------+----------------------+------------------------+
| beijing | | 2008 |
+-----------------------+----------------------+------------------------+
1 row in set (0.00 sec)
LPAD(str,n,pad) 和RPAD(str,n,pad)
是什么
用字符串pad 对str最左边或者最右边进行填充，知道长度达到n个字符
范例
mysql> select lpad('2008',20,'beijing'),rpad('beijing',20,'2008');
+---------------------------+---------------------------+
| lpad('2008',20,'beijing') | rpad('beijing',20,'2008') |
+---------------------------+---------------------------+
| beijingbeijingbe2008 | beijing2008200820082 |
+---------------------------+---------------------------+
LTRIM(str) 和RTRIM(str)
是什么
去掉字符串左侧或者右侧的空格
范例
mysql> select ltrim(' |beijing'),rtrim('beijing| ');
+---------------------+------------------------+
| ltrim(' |beijing') | rtrim('beijing| ') |
+---------------------+------------------------+
| |beijing | beijing| |
+---------------------+------------------------+
1 row in set (0.00 sec)
REPEAT(str,x)
是什么
返回字符串str 重复X次的结果
范例
mysql> select repeat('mysql ',3);
+--------------------+
| repeat('mysql ',3) |
+--------------------+
| mysql mysql mysql |
+--------------------+
1 row in set (0.00 sec)
REPLACE(str，a,b）
是什么
用字符串b替换 字符串str中所有字符串 a
范例
mysql> select replace('beijing_2010','_2010','2008');
+----------------------------------------+
| replace('beijing_2010','_2010','2008') |
+----------------------------------------+
| beijing2008 |
+----------------------------------------+
1 row in set (0.00 sec)
STRCMP(str1,str2)
是什么
比较字符串str1和字符串str2的大小 s1<s2 返回-1 相等返回 0 大于返回1
范例
mysql> select strcmp('a','b'),strcmp('b','b'),strcmp('c','b');
+-----------------+-----------------+-----------------+
| strcmp('a','b') | strcmp('b','b') | strcmp('c','b') |
+-----------------+-----------------+-----------------+
| -1 | 0 | 1 |
+-----------------+-----------------+-----------------+
1 row in set (0.00 sec)
TRIM(str)
是什么
去掉目标字符串两端的空格
范例
mysql> select trim(' $ beijing2008 $ ');
+-----------------------------+
| trim(' $ beijing2008 $ ') |
+-----------------------------+
| $ beijing2008 $
SUBSTRING（str,x,y）
是什么
返回从字符串 str 中的第 x 位置起 y 个字符长度的字串。此函数经常用来对给定字符串进行字串的提取，
范例
mysql> select substring('beijing2008',8,4),substring('beijing2008',1,7);
+------------------------------+------------------------------+
| substring('beijing2008',8,4) | substring('beijing2008',1,7) |
+------------------------------+------------------------------+
| 2008 | beijing |
+------------------------------+------------------------------+
5.2 数值函数
ABS（x）
是什么
返回x的绝对值
范例
mysql> select ABS(-0.8) ,ABS(0.8);
+-----------+----------+
| ABS(-0.8) | ABS(0.8) |
+-----------+----------+
| 0.8 | 0.8 |
+-----------+----------+
1 row in set (0.09 sec)
CEIL(x)
是什么
返回大于X的最小整数
范例
mysql> select CEIL(-0.8),CEIL(0.8);
+------------+-----------+
| CEIL(-0.8) | CEIL(0.8) |
+------------+-----------+
| 0 | 1 |
+------------+-----------+
1 row in set (0.03 sec)
FLOOR(x)
是什么
返回小于x的最大整数
范例
mysql> select FLOOR(-0.8), FLOOR(0.8);
+-------------+------------+
| FLOOR(-0.8) | FLOOR(0.8) |
+-------------+------------+
| -1 | 0 |
+-------------+------------+
1 row in set (0.00 sec)
MOD(x,y) x/y的模
是什么
返回 x/y 的模。
范例
mysql> select MOD(15,10),MOD(1,11),MOD(NULL,10);
+------------+-----------+--------------+
| MOD(15,10) | MOD(1,11) | MOD(NULL,10) |
+------------+-----------+--------------+
| 5 | 1 | NULL |
+------------+-----------+--------------+
1 row in set (0.00 sec)
RAND()
是什么
返回0-1内的随机数
范例
比如需要产生 0～100 内的任意随机整数mysql> select ceil(100*rand()),ceil(100*rand());
+------------------+------------------+
| ceil(100*rand()) | ceil(100*rand()) |
+------------------+------------------+
| 91 | 15 |
+------------------+------------------+
1 row in set (0.00 sec)
ROUND(x,y)
是什么
返回参数 x 的四舍五入的有 y 位小数的值。如果是整数，将会保留 y 位数量的 0；如果不写 y，则默认 y 为 0，即将 x 四舍五入后取整。
范例
mysql> select ROUND(1.1),ROUND(1.1,2),ROUND(1,2);
+------------+--------------+------------+
| ROUND(1.1) | ROUND(1.1,2) | ROUND(1,2) |
+------------+--------------+------------+
| 1 | 1.10 | 1.00 |
+------------+--------------+------------+
1 row in set (0.00 sec)
TRUNCATE(x，y)
是什么
返回数字 x 截断为 y 位小数的结果。
范例
mysql> select ROUND(1.235,2),TRUNCATE(1.235,2);
+----------------+-------------------+
| ROUND(1.235,2) | TRUNCATE(1.235,2) |
+----------------+-------------------+
| 1.24 | 1.23 |
+----------------+-------------------+
1 row in set (0.00 sec)
5.3 日期和时间函数
CURDATE()
是什么
当前日期，只包含年月日： 2007-08-22
范例
mysql> select CURDATE();
+------------+
| CURDATE() |
+------------+
| 2007-07-11 |
+------------+
1 row in set (0.03 sec)
CURTIME()
是什么
当前时间，时分秒：12:33:33
范例
mysql> select CURTIME();
+-----------+
| CURTIME() |
+-----------+
| 14:13:46 |
+-----------+
1 row in set (0.00 sec)
NOW()
是什么
返回当前的日期和时间，年月日时分秒全都包含
范例
mysql> select NOW();
+---------------------+
| NOW() |
+---------------------+
| 2007-07-11 14:14:06 |
+---------------------+
1 row in set (0.00 sec)
UNIX_TIMESTAMP(date)
是什么
返回日期date的unix 时间戳
范例
mysql> select UNIX_TIMESTAMP(now());
+-----------------------+
| UNIX_TIMESTAMP(now()) |
+-----------------------+
| 1184134516 |
+-----------------------+
1 row in set (0.02 sec)
FROM_UNIXTIME（unixtime
是什么
返 回 UNIXTIME 时 间 戳 的 日 期 值 ， 和UNIX_TIMESTAMP(date)互为逆操作。
范例
mysql> select FROM_UNIXTIME(1184134516) ;
+---------------------------+
| FROM_UNIXTIME(1184134516) |
+---------------------------+
| 2007-07-11 14:15:16 |
+---------------------------+
1 row in set (0.00 sec)
WEEK(date)和 YEAR(date)
是什么
前者返回所给的日期是一年中的第几周，后者返回所给的日期是哪一年。
范例
mysql> select WEEK(now()),YEAR(now());
+-------------+-------------+
| WEEK(now()) | YEAR(now()) |
+-------------+-------------+
| 27 | 2007 |
+-------------+-------------+
1 row in set (0.02 sec)
HOUR(time) 和MINUTE(time)
是什么
前者返回所给时间的小时，后者返回所给时间的分钟
范例
mysql> select HOUR(CURTIME()),MINUTE(CURTIME());
+-----------------+-------------------+
| HOUR(CURTIME()) | MINUTE(CURTIME()) |
+-----------------+-------------------+
| 14 | 18 |
+-----------------+-------------------+
1 row in set (0.00 sec)
MONTHNAME(date)
是什么
返回 date 的英文月份名称。
范例
mysql> select MONTHNAME(now());
+------------------+
| MONTHNAME(now()) |
+------------------+
| July |
+------------------+
1 row in set (0.00 sec)
DATE_FORMAT(date,fmt)
是什么
按字符串 fmt 格式化日期 date 值，此函数能够按指定的格式显示日期，
可用格式
%S,%s 两位数字形式的秒（00,01,...,59）
%i 两位数字形式的分（00,01,...,59）
%H 两位数字形式的小时，24 小时（00,01,...,23）
%h,%I 两位数字形式的小时，12 小时（01,02,...,12）
%k 数字形式的小时，24 小时（0,1,...,23）
%l 数字形式的小时，12 小时（1,2,...,12）
%T 24 小时的时间形式（hh:mm:ss）
%r 12 小时的时间形式（hh:mm:ssAM 或 hh:mm:ssPM）
%p AM 或 PM
%W 一周中每一天的名称（Sunday,Monday,...,Saturday）
%a 一周中每一天名称的缩写（Sun,Mon,...,Sat）
%d 两位数字表示月中的天数（00,01,...,31）
%e 数字形式表示月中的天数（1,2，...,31）
%D 英文后缀表示月中的天数（1st,2nd,3rd,...）
%w 以数字形式表示周中的天数（0=Sunday,1=Monday,...,6=Saturday）
%j 以 3 位数字表示年中的天数（001,002,...,366）
%U 周（0,1,52），其中 Sunday 为周中的第一天
%u 周（0,1,52），其中 Monday 为周中的第一天
%M 月名（January,February,...,December）
%b 缩写的月名（January,February,...,December）
%m 两位数字表示的月份（01,02,...,12）
%c 数字表示的月份（1,2,...,12）
%Y 4 位数字表示的年份
%y 两位数字表示的年份
%% 直接值“%”
范例
mysql> select DATE_FORMAT(now(),'%M,%D,%Y');
+-------------------------------+
| DATE_FORMAT(now(),'%M,%D,%Y') |
+-------------------------------+
| July,11th,2007 |
+-------------------------------+
1 row in set (0.00 sec)
DATE_ADD(date, INTERVAL expr type)
是什么
返回一个日期或时间值加上一个时间间隔的时间，值其中 INTERVAL 是间隔类型关键字，expr 是一个表达式，这个表达式对应后面的类型，type 是间隔类型，MySQL 提供了 13 种间隔类型
13 种间隔类型
HOUR 小时 hh
MINUTE 分 mm
SECOND 秒 ss
YEAR 年 YY
MONTH 月 MM
DAY 日 DD
YEAR_MONTH 年和月 YY-MM
DAY_HOUR 日和小时 DD hh
DAY_MINUTE 日和分钟 DD hh:mm
DAY_ SECOND 日和秒 DD hh:mm:ss
HOUR_MINUTE 小时和分 hh:mm
HOUR_SECOND 小时和秒 hh:ss
MINUTE_SECOND 分钟和秒 mm:ss
范例
在这个例子中第 1 列返回了当前日期时间，第 2 列返回距离当前日期
31 天后的日期时间，第 3 列返回距离当前日期一年两个月后的日期时间。
mysql> select now() current,date_add(now(),INTERVAL 31 day) after31days,
date_add(now(),INTERVAL '1_2' year_month) after_oneyear_twomonth;
+---------------------+---------------------+------------------------+
| current | after31days | after_oneyear_twomonth |
+---------------------+---------------------+------------------------+
| 2007-09-03 11:30:48 | 2007-10-04 11:30:48 | 2008-11-03 11:30:48 |
+---------------------+---------------------+------------------------+
1 row in set (0.01 sec)
第 1 列返回了当前日期时间，第 2
列返回距离当前日期 31 天前的日期时间，第 3 列返回距离当前日期一年两个月前的日期时
间。
mysql> select now() current,date_add(now(),INTERVAL -31 day) after31days,date_a
dd(now(),INTERVAL '-1_-2' year_month) after_oneyear_twomonth;
+---------------------+---------------------+------------------------+
| current | after31days | after_oneyear_twomonth |
+---------------------+---------------------+------------------------+
| 2007-09-03 11:36:35 | 2007-08-03 11:36:35 | 2006-07-03 11:36:35 |
+---------------------+---------------------+------------------------+
1 row in set (0.00 sec)
DATADIFF(expr,expr1)
是什么
用来计算两个日期之间相差的天数。
范例
ysql> select DATEDIFF('2008-08-08',now());
+------------------------------+
| DATEDIFF('2008-08-08',now()) |
+------------------------------+
| 328 |
+------------------------------+
1 row in set (0.01 sec)
5.4 流程函数
IF(value,t,f)
是什么
如果 value 是真，返回 t；否则返回 f
范例
我们认为月薪在 2000 元以上的职员属于高薪，用“high”表示；而2000 元以下的职员属于低薪，用“low”表示。
mysql> select if(salary>2000,'high','low') from salary;
+------------------------------+
| if(salary>2000,'high','low') |
+------------------------------+
| low |
| low |
| high |
| high |
| high |
+------------------------------+
5 rows in set (0.01 sec)
IFNULL(v1,v2)
是什么
如果 value1 不为空返回 value1，否则返回 value2
范例
个函数一般用来替换 NULL 值的，我们知道 NULL 值是不能参与数值运算的，下面这个语句就是把 NULL 值用 0 来替换。
mysql> select ifnull(salary,0) from salary;
+------------------+
| ifnull(salary,0) |
+------------------+
| 1000.00 |
| 2000.00 |
| 3000.00 |
| 4000.00 |
| 5000.00 |
| 0.00 |
+------------------+
6 rows in set (0.00 sec)
CASE WHEN [v1] THEN [result] ... ELSE [default] END
是什么
如果 value1 是真，返回 result1，否则返回 default
范例
我们用 casewhen…then 函数实现上面例子中高薪低薪的问题。
mysql> select case when salary<=2000 then 'low' else 'high' end from salary;
+---------------------------------------------------+
| case when salary<=2000 then 'low' else 'high' end |
+---------------------------------------------------+
| low |
| low |
| high |
| high |
| high |
| high |
+---------------------------------------------------+
6 rows in set (0.00 sec)
CASE [expr] WHEN [v1] THEN [result] .... ELSE [default]
是什么
如果 expr 等于 value1，返回 result1，否则返回 default
范例
mysql> select case salary when 1000 then 'low' when 2000 then 'mid' else 'high' end from salary;
+-----------------------------------------------------------------------+
| case salary when 1000 then 'low' when 2000 then 'mid' else 'high' end |
+-----------------------------------------------------------------------+
| low |
| mid |
| high |
| high |
| high |
| high |
+-----------------------------------------------------------------------+
6 rows in set (0.00 sec)
5.5 其他常用函数
DATABASE()
是什么
返回当前数据库的名称
范例
mysql> select DATABASE();
+------------+
| DATABASE() |
+------------+
| test |
+------------+
1 row in set (0.00 sec)
VERSION()
是什么
返回当前数据库的版本
范例
mysql> select VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.0.18-nt |
+-----------+
1 row in set (0.00 sec)
USER()
是什么
返回当前登录的用户名称
范例
mysql> select USER();
+----------------+
| USER() |
+----------------+
| [email protected] |
+----------------+
1 row in set (0.03 sec)
INET_ATON(IP)
是什么
返回ip地址的数字（ip地址的网络字节序）表示
范例
mysql> select INET_ATON('192.168.1.1');
+--------------------------+
| INET_ATON('192.168.1.1') |
+--------------------------+
| 3232235777 |
+--------------------------+
1 row in set (0.00 sec)
INET_NTOA(num)
是什么
返回数字代表的ip地址
范例
mysql> select INET_NTOA(3232235777);
+-----------------------+
| INET_NTOA(3232235777) |
+-----------------------+
| 192.168.1.1 |
+-----------------------+
1 row in set (0.00 sec)
PASSWORD(str)
是什么
返回字符串 str 的加密版本，一个 41 位长的字符串。
范例
mysql> select PASSWORD('123456');
+-------------------------------------------+
| PASSWORD('123456') |
+-------------------------------------------+
| *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
MD5()
是什么
返回字符串d的MD5值
范例
mysql> select MD5('123456');
+----------------------------------+
| MD5('123456') |
+----------------------------------+
| e10adc3949ba59abbe56e057f20f883e |
+----------------------------------+
1 row in set (0.06 sec)