1.字符串函数
1.1 CONCAT 函数 把传入的参数连接成一个字符串,但是任何字符串与NULL连接都会是NULL
例子：
select concat('aaa','bbb','ccc'),concat('aaa',null);
+---------------------------+--------------------+
| concat('aaa','bbb','ccc') | concat('aaa',null) |
+---------------------------+--------------------+
| aaabbbccc | NULL |
+---------------------------+--------------------+
1.2 INSERT(str,x,y,instr)函数：将字符串 str 从第x位置开始,y个字符长的子串替换为字符串 instr。
例子：把字符串"beijing2008you" 中从第12个字符串开始以后的3个字符替换成"me"。
select INSERT('beijing2008you',12,3,'me');
+------------------------------------+
| INSERT('beijing2008you',12,3,'me') |
+------------------------------------+
| beijing2008me |
+------------------------------------+
1.3 LOWER(str)小写 和 UPPER(str)大写 函数：把字符串转换成小写或大写。
select LOWER('BEIJING2008'),UPPER('beijing2008');
+----------------------+----------------------+
| LOWER('BEIJING2008') | UPPER('beijing2008') |
+----------------------+----------------------+
| beijing2008 | BEIJING2008 |
+----------------------+----------------------+
1.4 LEFT(str,x) 和 RIGHT(str,x)函数：分别返回字符串最左边的x个字符和最右边的x个字符，如果第二个参数是 NULL，那么将不返回任何字符串。
select left('beijing2008',7),left('beijing',null),right('beijing',4);
+-----------------------+----------------------+--------------------+
| left('beijing2008',7) | left('beijing',null) | right('beijing',4) |
+-----------------------+----------------------+--------------------+
| beijing | NULL | jing |
+-----------------------+----------------------+--------------------+
1.5 LPAD(str,n,pad) 和 RPAD(str,n,pad)函数：用字符串pad对str 最左边和最右边进行填充，直到长度为n个字符长度。
select lpad('2008',20,'beijing'),rpad('beijing',20,'2008');
+---------------------------+---------------------------+
| lpad('2008',20,'beijing') | rpad('beijing',20,'2008') |
+---------------------------+---------------------------+
| beijingbeijingbe2008 | beijing2008200820082 |
+---------------------------+---------------------------+
1.6 LTRIM(str)和RTRIM(str)函数：去掉字符串str左侧和右侧空格
select ltrim(' |beijing'),rtrim('beijing| ');
+--------------------+---------------------+
| ltrim(' |beijing') | rtrim('beijing| ') |
+--------------------+---------------------+
| |beijing | beijing| |
+--------------------+---------------------+
1.7 REPEAT(str,x)函数：返回str重复x次的结果。
select repeat('mysql',3);
+-------------------+
| repeat('mysql',3) |
+-------------------+
| mysqlmysqlmysql |
+-------------------+
1.8 REPLACE(str,a,b)函数：用字符串b替换字符串str中所有出现的字符串a。
select replace('beijing2008','i','s');
+--------------------------------+
| replace('beijing2008','i','s') |
+--------------------------------+
| besjsng2008 |
+--------------------------------+
1.9 STRCMP(s1,s2)函数：比较字符串ASCII码值的大小。
如果s1比s2小，那么返回-1，如果s1和s2相等，那么返回0，如果s1大于s2，那么返回1
select strcmp('a','b'),strcmp('b','b'),strcmp('c','b');
+-----------------+-----------------+-----------------+
| strcmp('a','b') | strcmp('b','b') | strcmp('c','b') |
+-----------------+-----------------+-----------------+
| -1 | 0 | 1 |
+-----------------+-----------------+-----------------+
1.10 TRIM(str) 函数：去掉目标字符串的开头和结尾的空格。
select trim(' $aaa$ ');
+------------------+
| trim(' $aaa$ ') |
+------------------+
| $aaa$ |
+------------------+
1.11 SUBSTRING(str,x,y)函数：返回从字符串str中的第x位置起y个字符长度的字串
select substring('beijing2008',8,4),substring('beijing2008',1,7);
+------------------------------+------------------------------+
| substring('beijing2008',8,4) | substring('beijing2008',1,7) |
+------------------------------+------------------------------+
| 2008 | beijing |
+------------------------------+------------------------------+
2.数值函数
Paste_Image.png
3.日期和时间函数
Paste_Image.png
curdate() 返回当前日期 只包含年、月、日
select curdate();
+------------+
| curdate() |
+------------+
| 2017-09-15 |
+------------+
curtime() 反当前时间 只包含 时分秒
select curtime();
+-----------+
| curtime() |
+-----------+
| 08:45:52 |
now() 返回当前时间，包含年月日时分秒
select now();
+---------------------+
| now() |
+---------------------+
| 2017-09-15 08:46:24 |
+---------------------+
unix_timestamp(now()) 返回日期date的unix时间戳
select unix_timestamp(now());
+-----------------------+
| unix_timestamp(now()) |
+-----------------------+
| 1505436536 |
+-----------------------+
from_unixtime 返回时间戳的日期值
select from_unixtime(1184134516);
+---------------------------+
| from_unixtime(1184134516) |
+---------------------------+
| 2007-07-11 14:15:16 |
+---------------------------+
week(date) 返回的是所给日期中一年中的第几周 year(date)函数返回所给的日期是哪一年
select week(now()),year(now());
+-------------+-------------+
| week(now()) | year(now()) |
+-------------+-------------+
| 37 | 2017 |
+-------------+-------------+
hour(time) 返回小时 minute(time) 返回分钟
select hour(now()),minute(now());
+-------------+---------------+
| hour(now()) | minute(now()) |
+-------------+---------------+
| 9 | 2 |
+-------------+---------------+
4.流程函数
Paste_Image.png
以此表实验流程函数
+--------+--------+
| userid | salary |
+--------+--------+
| 1 | 1000 |
| 2 | 2000 |
| 3 | 3000 |
| 4 | 4000 |
| 5 | 5000 |
| 1 | NULL |
+--------+--------+
if(value,t,f) 如果value是真，返回t,否则返回f
select if(salary>2000,'high','low') from salary;
+------------------------------+
| if(salary>2000,'high','low') |
+------------------------------+
| low |
| low |
| high |
| high |
| high |
| low |
+------------------------------+
ifnull(value1,value2) 如果value1不为空，返回value1，否则返回value2
select ifnull(salary,0) from salary;
+------------------+
| ifnull(salary,0) |
+------------------+
| 1000 |
| 2000 |
| 3000 |
| 4000 |
| 5000 |
| 0 |
+------------------+
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 |
+---------------------------------------------------+
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 |
+-----------------------------------------------------------------------+
5.其他常用函数
Paste_Image.png
，
，
，
，
，
，
，
，
，
，
，
，
，
，
，
，
，
