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加密之后的内容解密
