mysql 常用函数的使用及示例包括字符串和日期时间

1.SELECT 1+'3MAIZI';   结果为4       SELECT 3/0;结果为null   SELECT 4 DIV 2;结果为2   SELECT 1+NULL;为null

SELECT 1=1,1='1',1=2;
+-----+-------+-----+
| 1=1 | 1='1' | 1=2 |
+-----+-------+-----+
|   1 |     1 |   0 |
+-----+-------+-----+

SELECT CEIL(1.2),CEILING(1.2),FLOOR(1.2);
                         2                     2                   1

SELECT SIGN(12),SIGN(0),SIGN(-12);

+----------+---------+-----------+
| SIGN(12) | SIGN(0) | SIGN(-12) |
+----------+---------+-----------+
|        1 |       0 |        -1 |
+----------+---------+-----------+

SELECT CHAR_LENGTH('啊'),LENGTH('啊');//1  3
SELECT CONCAT('HELLO','WORLD');//HELLOWORLD

SELECT CONCAT_WS('','a','b','c');//abc         SELECT CONCAT_WS(NULL,'a','b','c');//null  

SELECT CONCAT_WS('','a','b','c',null);//abc

//分组  合并

SELECT proId,GROUP_CONCAT(username),GROUP_CONCAT(sex),GROUP_CONCAT(regTime) FROM cms_user GROUP BY proId

SELECT LOWER('HELLO WORLD'),UCASE('HELLO WORLD');//hello world     HELLO WORLD

SELECT LEFT('ABCDEF',2),RIGHT('ABCDEF',2);//AB   EF

SELECT LPAD('A',5,'?'),RPAD('A',5,'!')//????A     A!!!!

SELECT CONCAT('_',TRIM(' ABC '),'_'),CONCAT('_',LTRIM(' ABC '),'_'),CONCAT('_',RTRIM(' ABC '),'_');

                                _ABC_                                               _ABC _                                  _ ABC_

SELECT TRIM('A' FROM 'ABCBCA');   BCBC

SELECT REPEAT('H',5);    HHHHH 

SELECT CONCAT('_',SPACE(5),'_');_     _

SELECT REPLACE('ABCBCA','A','_');_BCBC_

SELECT STRCMP('A','A'),STRCMP('A','a'),STRCMP('B','A');001

SELECT STRCMP('A','A'),STRCMP('A','a'),STRCMP('A','B');00-1

SELECT SUBSTRING('ABCDEF',2,2);BC

SELECT REVERSE('ABC');//CBA

SELECT ELT(2,'A','B','C');B

SELECT CURDATE(),CURRENT_DATE();//2015-01-03 2015-01-03   SELECT NOW();  //2015-01-03 15:59:37

SELECT CURTIME(),CURRENT_TIME();//15:59:32 15:59:32

SELECT MONTH('2015-1-3');//1  SELECT MONTH(NOW());//1

SELECT MONTHNAME(NOW());//January   SELECT DAYNAME(NOW());//Saturday

SELECT WEEKDAY(NOW());//5             SELECT DAYOFWEEK(NOW());//7

SELECT WEEK(NOW());//0     SELECT YEAR(NOW());//2015

SELECT HOUR(NOW());//16   SELECT MINUTE(NOW());//1    SELECT SECOND(NOW());//49

SELECT DATEDIFF(CURRENT_DATE(),'1990-1-1');//9133

if(条件表达式，v1,v2)SELECT id,username,score,IF(score>=60,'及格','不及格')FROM student;

IFNULL     SELECT id,username,age,IFNULL(age,'100')FROM cms_user;

SELECT id,username,score, CASE WHEN score>60 THEN '不错' WHEN score=60 THEN '刚及格' ELSE '没及格' END FROM student;  

 

SELECT CONNECTION_ID(),VERSION(),DATABASE(),SCHEMA(),USER(),STSTEM_USER(),CURRENT_USER()

SELECT CHARSET('AAA'),COLLATION('AAA');//utf8,utf8_general_ci

SELECT LAST_INSERT_ID();//14

SELECT MD5('ADMIN');        SELECT LENGTH(PASSWORD('111111'));//41

SELECT FORMAT(3.14567,2);//3.15        

SELECT ASCII('abc');//97    SELECT BIN(5),HEX(5),OCT(5);//101 5    5    SELECT CONV(5,10,2);  //101进制转换

将ip地址转换为数字 

SELECT INET_ATON('127.0.0.1');//2130706433     SELECT INET_NOTA(2130706433);//127.0.0.1

加锁0为有锁，get_lock或release_lock释放锁

SELECT GET_LOCK('KING',10);//1  SELECT IS_FREE_LOCK('KING');//0   SELECT RELEASE_LOCK('KING');//1

encode（str,pwd_str）加密结果是一二进制数，必须使用blob类型字段存储

decode（crypt_str,pwd_str）对通过encode加密之后的内容解密