MySQL - 基础函数

LTRIM(str ) 函数使用说明：返回字符串 str ，其引导空格字符被删除。
REPEAT(str ,count )函数使用说明：返回一个由重复的字符串str 组成的字符串，字符串str 的数目等于count 。
RTRIM(str ) 函数使用说明：返回字符串 str ，结尾空格字符被删去。
TRIM([{BOTH | LEADING | TRAILING} [remstr ] FROM] str ) TRIM(remstr FROM] str )
函数使用说明：返回字符串 str ， 其中所有remstr 前缀和/ 或后缀都已被删除。若分类符BOTH 、LEADIN 或TRAILING 中没有一个是给定的, 则假设为BOTH 。 remstr 为可选项，在未指定情况下，可删除空格

CONCAT(str1,str2,...) 
LENGTH(str) 
LOCATE(substr,str,pos) 
返回子串substr在字符串str第一个出现的位置，从位置pos开始。如果substr不是在str里面，返回0。


INSTR(str,substr) 
返回子串substr在字符串str中的第一个出现的位置。


LPAD(str,len,padstr) 
返回字符串str，左面用字符串padstr填补直到str是len个字符长。


RPAD(str,len,padstr) 
返回字符串str，右面用字符串padstr填补直到str是len个字符长。


LEFT(str,len) 
返回字符串str的最左面len个字符。


RIGHT(str,len) 
返回字符串str的最右面len个字符。 


SUBSTRING(str,pos,len) 


MID(str,pos,len) 
从字符串str返回一个len个字符的子串，从位置pos开始。


REPLACE(str,from_str,to_str) 
返回字符串str，其字符串from_str的所有出现由字符串to_str代替。


REVERSE(str) 
返回颠倒字符顺序的字符串str。


INSERT(str,pos,len,newstr) 
返回字符串str，在位置pos起始的子串且len个字符长得子串由字符串newstr代替。 


ELT(N,str1,str2,str3,...) 
如果N= 1，返回str1，如果N= 2，返回str2，等等。如果N小于1或大于参数个数，返回NULL。ELT()是FIELD()反运算。


FIELD(str,str1,str2,str3,...) 
返回str在str1, str2, str3, ...清单的索引。如果str没找到，返回0。


FIND_IN_SET(str,strlist) 
如果字符串str在由N子串组成的表strlist之中，返回一个1到N的值。一个字符串表是被“,”分隔的子串组成的一个字符串。如果第一个参数是一个常数字符串并且第二个参数是一种类型为SET的列，FIND_IN_SET()函数被优化而使用位运算！如果str不是在strlist里面或如果strlist是空字符串，返回0。如果任何一个参数是NULL，返回NULL。如果第一个参数包含一个“,”，该函数将工作不正常。 


MAKE_SET(bits,str1,str2,...) 
返回一个集合 (包含由“,”字符分隔的子串组成的一个字符串)，由相应的位在bits集合中的的字符串组成。str1对应于位0，str2对应位1，等等。在str1, str2, ...中的NULL串不添加到结果中。
例如：
SELECT MAKE_SET(5,'a','b','c','d','e');
返回结果是：
a,c
因为5 的二进制表达为 101，所以，取1,3位，为 "a,c"


大小写转换：
  UCASE(str) / UPPER(str) 
  LCASE(str) / LOWER(str) 


LOAD_FILE(file_name) 
   读入文件并且作为一个字符串返回文件内容。文件必须在服务器上，你必须指定到文件的完整路径名，而且你必须有file权限。文件必须所有内容都是可读的并且小于max_allowed_packet。如果文件不存在或由于上面原因之一不能被读出，函数返回NULL。 
mysql> UPDATE table_name
       SET blob_column=LOAD_FILE("/tmp/picture")
       WHERE id=1;


日期&时间：
select abs(TO_DAYS(now())-TO_DAYS(STR_TO_DATE('1997-07-1','%Y-%m-%d')));  -- 计算天数，差
select CURDATE();  
CURTIME()
SYSDATE()
CURRENT_TIMESTAMP
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), 这里星期一是星期的第一天  
　　%% 一个文字“%”。 
1. MySQL 为日期增加一个时间间隔：date_add()


set @dt = now();


select date_add(@dt, interval 1 day);        -- add 1 day
select date_add(@dt, interval 1 hour);       -- add 1 hour
select date_add(@dt, interval 1 minute);     -- ...
select date_add(@dt, interval 1 second);
select date_add(@dt, interval 1 microsecond);
select date_add(@dt, interval 1 week);
select date_add(@dt, interval 1 month);
select date_add(@dt, interval 1 quarter);
select date_add(@dt, interval 1 year);
UNIX_TIMESTAMP(date)  
　　如果没有参数调用，返回一个Unix时间戳记(从'1970-01-01 00:00:00'GMT开始的秒数)。如果UNIX_TIMESTAMP()用一个date参数被调用，它返回从'1970-01-01 00:00:00' GMT开始的秒数值。date可以是一个DATE字符串、一个DATETIME字符串、一个TIMESTAMP或以YYMMDD或YYYYMMDD格式的本地时间的一个数字。  
　　mysql> select UNIX_TIMESTAMP();  
　　-> 882226357  
　　mysql> select UNIX_TIMESTAMP('1997-10-04 22:23:00');  
　　-> 875996580  
　　当UNIX_TIMESTAMP被用于一个TIMESTAMP列，函数将直接接受值，没有隐含的“string-to-unix-timestamp”变换。 


FROM_UNIXTIME(unix_timestamp)  
　　以'YYYY-MM-DD HH:MM:SS'或YYYYMMDDHHMMSS格式返回unix_timestamp参数所表示的值，取决于函数是在一个字符串还是或数字上下文中被使用。  
　　mysql> select FROM_UNIXTIME(875996580);  
　　-> '1997-10-04 22:23:00'  
　　mysql> select FROM_UNIXTIME(875996580) + 0;  
　　-> 19971004222300  


SEC_TO_TIME(seconds)  
　　返回seconds参数，变换成小时、分钟和秒，值以'HH:MM:SS'或HHMMSS格式化，取决于函数是在一个字符串还是在数字上下文中被使用。  
　　mysql> select SEC_TO_TIME(2378);  
　　-> '00:39:38'  
TIME_TO_SEC(time)  
　　返回time参数，转换成秒。  
　　mysql> select TIME_TO_SEC('22:23:00');  
　　-> 80580  




===============数学函数
ABS(N)    
SIGN(N)  
MOD(N,M)    
　取模运算,返回N被M除的余数(同%操作符)     
FLOOR(N)  
CEILING(N)  
ROUND(N,D)  
　返回N的四舍五入值,保留D位小数(D的默认值为0)  
EXP(N)  
　返回值e的N次方(自然对数的底)  
LOG(N)  
LOG10(N)  
POW(X,Y)    
POWER(X,Y)    
　返回值X的Y次幂  
SQRT(N)  
　返回非负数N的平方根
PI()      
COS(N)  
SIN(N)   
TAN(N)   
ACOS(N)  
ASIN(N)  
ATAN(N)  
ATAN2(X,Y)    
COT(N)  
RAND()  
RAND(N)    
　返回在范围0到1.0内的随机浮点值(可以使用数字N作为初始值) 
DEGREES(N)  
　把N从弧度变换为角度并返回  
mysql> select DEGREES(PI());    
　　-> 180.000000    
RADIANS(N)  
　把N从角度变换为弧度并返回   
mysql> select RADIANS(90);    
　　-> 1.570796     
TRUNCATE(N,D)    
　保留数字N的D位小数并返回  
LEAST(X,Y,...)    
　返回最小值(如果返回值被用在整数(实数或大小敏感字串)上下
文或所有参数都是整数(实数或大小敏感字串)则他们作为整数(实
数或大小敏感字串)比较,否则按忽略大小写的字符串被比较)  
mysql> select LEAST(2,0);    
　　-> 0    
mysql> select LEAST(34.0,3.0,5.0,767.0);    
　　-> 3.0    
mysql> select LEAST("B","A","C");    
　　-> "A"    
GREATEST(X,Y,...)    
　返回最大值(其余同LEAST())  




============== 聚合函数：
count（）avg（）max（）min（）sum（）distinct 
avg（）忽略值为null的行
count（*）时统计所有行，count（列）时忽略为null的行


============== 控制流函数
a) CASE value WHEN [compare-value] THEN result [WHEN [compare-value] THEN result ……] [ELSE result ]END


    或：  CASE WHEN [condition] THEN result [WHEN[condition] THEN result ……] [ELSE result] END ；


两种方案中，如果都不满足而且如果没有ELSE 部分，则返回值为NULL


b) IF(expr1,expr2,expr3)


函数用法说明：如果 expr1 是 TRUE (expr1 <> 0 and expr1 <> NULL) ，则 IF() 的返回值为 expr2 ; 否则返回值则为 expr3 。


c) IFNULL(expr1,expr2)


函数用法说明：假如 expr1 不为 NULL ，则 IFNULL() 的返回值为 expr1 ; 否则其返回值为 expr2 。 IFNULL() 的返回值是数字或是字符串，具体情况取决于其所使用的语境


============== 加密函数
 AES_ENCRYPT(str ,key_str ) ,    AES_DECRYPT(crypt_str ,key_str )
 ENCODE(str ,pass_str )          DECODE(crypt_str ,pass_str )
 DES_ENCRYPT(str [,(key_num |key_str )])    DES_DECRYPT(crypt_str [,key_str ])
 ENCRYPT(str [,salt ])
 MD5(str )
 OLD_PASSWORD(str )
 PASSWORD(str )
 
============== 其他函数
a) 函数 DEFAULT(col_name )：返回一个表列的默认值。若该列没有默认值则会产生错误。


b) 函数 FORMAT(X ,D )：将数字 X 的格式写为 '#,###,###.##', 以四舍五入的方式保留小数点后 D 位， 并将结果以字符串的形式返回。若 D 为 0, 则返回结果不带有小数点，或不含小数部分。
c) 函数 GET_LOCK(str ,timeout )
函数使用说明：设法使用字符串 str 给定的名字得到一个锁， 超时为 timeout 秒。若成功得到锁，则返回 1 ，若操作超时则返回 0 ( 例如 , 由于另一个客户端已提前封锁了这个名字 ), 若发生错误则返回 NULL ( 诸如缺乏记忆或线程 mysqladmin kill 被断开 ) 。假如你有一个用 GET_LOCK() 得到的锁，当你执行 RELEASE_LOCK() 或你的连接断开 ( 正常或非正常 ) 时，这个锁就会解除


d) 函数 INET_ATON(expr )
函数使用说明：给出一个作为字符串的网络地址的点地址表示，返回一个代表该地址数值的整数。地址可以是 4 或 8 比特地址。


e) 函数 INET_NTOA(expr )
函数使用说明：给定一个数字网络地址 (4 或 8 比特 ), 返回作为字符串的该地址的电地址表示


f) 函数 IS_FREE_LOCK(str )
函数使用说明：检查名为 str 的锁是否可以使用 ( 换言之 , 没有被封锁 ) 。若锁可以使用，则返回 1 ( 没有人在用这个锁 ), 若这个锁正在被使用，则返回 0 ，出现错误则返回 NULL ( 诸如不正确的参数 ) 。


g) 函数 IS_USED_LOCK(str )
函数使用说明：检查名为 str 的锁是否正在被使用 ( 换言之 , 被封锁 ) 。若被封锁，则返回使用该锁的客户端的连接标识符。否则返回 NULL 。


h) 函数 MASTER_POS_WAIT(log_name ,log_pos [,timeout ])
函数使用说明：该函数对于控制主从同步很有用处。它会持续封锁，直到从设备阅读和应用主机记录中所有补充资料到指定的位置。返回值是其为到达指定位置而必须等待的记录事件的数目。若从设备 SQL 线程没有被启动、从设备主机信息尚未初始化、参数不正确或出现任何错误，则该函数返回 NULL 。若超时时间被超过，则返回 -1 。若在 MASTER_POS_WAIT() 等待期间，从设备 SQL 线程中止，则该函数返回 NULL 。若从设备由指定位置通过，则函数会立即返回结果。


i) 函数 NAME_CONST(name ,value )
函数使用说明：返回给定值。 当用来产生一个结果集合列时 , NAME_CONST() 促使该列使用给定名称。


j) 函数 RELEASE_LOCK(str )
函数使用说明：解开被 GET_LOCK() 获取的，用字符串 str 所命名的锁。若锁被解开，则返回 1 ，若改线程尚未创建锁，则返回 0 ( 此时锁没有被解开 ), 若命名的锁不存在，则返回 NULL 。若该锁从未被对 GET_LOCK() 的调用获取，或锁已经被提前解开，则该锁不存在。


k) 函数 SLEEP(duration )
函数使用说明：睡眠 ( 暂停 ) 时间为 duration 参数给定的秒数，然后返回 0 。若 SLEEP() 被中断 , 它会返回 1 。 duration 或许或包括一个给定的以微秒为单位的分数部分。


l) 函数 UUID()
函数使用说明：返回一个通用唯一标识符 (UUID) ， UUID 被设计成一个在时间和空间上都独一无二的数字。 2 个对 UUID() 的调用应产生 2 个不同的值，即使这些调用的执行是在两个互不相连的单独电脑上进行。


m) 函数 VALUES(col_name )
：在一个 INSERT … ON DUPLICATE KEY UPDATE … 语句中，你可以在 UPDATE 子句中使用 VALUES(col_name ) 函数，用来访问来自该语句的 INSERT 部分的列值。
换言之， UPDATE 子句中的 VALUES(col_name ) 访问需要被插入的 col_name 的值 , 并不会发生重复键冲突。
这个函数在多行插入中特别有用。 VALUES() 函数只在 INSERT ... UPDATE 语句中有意义，而在其它情况下只会返回 NULL