SELECT CHAR_LENGTH('success'),LENGTH('success');// 结果:7,7 字符串长度 SELECT CHAR_LENGTH('测试'),LENGTH('测试');//结果:2,6 一个汉字的length是3 SELECT CONCAT('A','B','C','D');//结果:ABCD SELECT CONCAT('A','B','C',NULL);//结果:null SELECT CONCAT_WS('<>>><<<>','A','B','C');//结果:A<>>><<<>B<>>><<<>C SELECT CONCAT_WS('<>>><<<>','A','B',NULL);//结果:A<>>><<<>B 忽略null SELECT UPPER('this is test'),UCASE('this is test');//转换成大写 SELECT LOWER('This IS Test'),LCASE('This IS Test');//转换成小写 SELECT LEFT('ABCDEFG',2),RIGHT('ABCDEF',2);//结果:AB,EF 取右边两个值,取左边两个值 SELECT LPAD('A',5,'*'),RPAD('A',5,'!');//结果:****A,A!!!! A的右边用*填满5个字符,A的左边用!填写成5个字符(如果原来字符串有多位则截取) SELECT LPAD('ABBBBggg',5,'*'),RPAD('A',5,'!'); SELECT ' AB CF ',CONCAT('_',TRIM(' AB CF '),'_'),CONCAT('_',LTRIM(' AB CF '),'_'),CONCAT('_',RTRIM(' AB CF '),'_');//去空格 SELECT TRIM('A' FROM 'ABFDDAASHaAA');//去字符串头和尾的A 结果:BFDDAASHa SELECT REPEAT('AB',5);//AB重复五次 SELECT CONCAT('_',SPACE(5),'_'); SELECT REPLACE('AhhhaAAFF','A','*');//将A替换成* SELECT STRCMP('A','A'),STRCMP('A','a'),STRCMP('A','B'),STRCMP('B','a'); SELECT SUBSTRING('ABCDEFG',2,2);//结果:BC 从第二位截2位 SELECT REVERSE('ABCDEFG');//反转 SELECT ELT(2,'A','B','C','D');//取第二位 结果:B /** *日期相关操作 */ SELECT CURDATE(),CURRENT_DATE(),CURTIME(),CURRENT_TIME(),NOW(); SELECT MONTH('2015-01-03'),MONTHNAME(NOW()); SELECT DAYNAME(NOW()),DAYOFWEEK(NOW()); SELECT YEAR(NOW()),HOUR(NOW()),MINUTE(NOW()),SECOND(NOW()),DATEDIFF(NOW(),'2015-09-24'); SELECT DATEDIFF(NOW(),create_time)>2 FROM apply_info; /** */ SELECT IF(a.`appl_amt`>=10000000,'大额','小额') FROM apply_info a; SELECT IFNULL(NULL,'1000'); SELECT a.id,CASE WHEN a.id>851 THEN '偏高' WHEN (a.id IN (850,851)) THEN '刚刚好' ELSE '偏低' END FROM apply_info a; SELECT FORMAT(3.14159675,2); SELECT ASCII('abc');//返回守卫字母的ascii值 SELECT BIN(123); SELECT HEX(123); SELECT OCT(123); SELECT CONV(123,10,8); SELECT INET_ATON('192.168.146.139'); SELECT INET_NTOA(3232273035); |
mysql中一些常用函数用法
最新推荐文章于 2024-07-07 15:20:33 发布