mysql基本常用函数

一、mysql基本常用函数
1.字符串类
CHARSET(str) – 返回字符串编码集
SELECT CHARSET(‘abc’);

CONCAT(str1,..) – 连接字符串
SELECT CONCAT(‘abc’,’1’);

INSTR(str, sub1) –返回sub1首次出现的位置，不存在返回0
SELECT INSTR(‘abc’,’c’); – 第几个位置

LCASE(str)– 转换小写
SELECT LCASE(‘ABC’);

LEFT(str1,LENGTH) – 从str1中的左起第length个字符
SELECT LEFT(‘abc’,2); 左起第1个到第2个字符

LOAD_FILE(file_name) – 从文件读取内容
SELECT LOAD_FILE(‘d:/123.txt’);

– 返回子串 substr 在字符串 str 中第一次出现的位置。如果子串 substr 在 str 中不存在，返回值为 0
LOCATE(SUBSTR,str[,startposition]);
SELECT LOCATE(‘a’,’abc’);
SELECT LOCATE(‘bar’, ‘foobarbar’,5);

LPAD(str,LENGTH, pad);– 重复用pad加str开头，直到字符串长度为length
SELECT LPAD(‘abc’,10,’z’);

LTRIM(st); – 去掉前端空格
SELECT LTRIM(’ abc’);

REPEAT(str, COUNT); – 重复多少次
SELECT REPEAT(‘abc’);

REPLACE (str ,search_str ,replace_str ) //在str中用replace_str替换search_str
SELECT REPLACE(‘abcdef’,’abc’,’dd’);

RPAD (string2 ,LENGTH ,pad) //在str后用pad补充,直到长度为length
SELECT RPAD(‘abc’, 10,’dd’);

RTRIM (string2 ) //去除后端空格
SELECT RTRIM(‘acbc ‘);

STRCMP (string1 ,string2 ) //逐字符比较两字串大小,相等0 str1>str2 1 否则-1
SELECT STRCMP(‘abcd’,’abcd’);

SUBSTRING (str , POSITION [,LENGTH ]) //从str的position开始,取length个字符
SELECT SUBSTRING(‘abcdefg’,2);
注：mysql中处理字符串时，默认第一个字符下标为1，即参数position必须大于等于1

TRIM([[BOTH|LEADING|TRAILING] [padding] FROM]string2) //去除指定位置的指定字符
SELECT TRIM(’ abc ‘);//去前后空格
SELECT TRIM(LEADING’,’ FROM ‘,,barxxx’); //删除指定首字符如’,’
SELECT TRIM(BOTH ‘,’ FROM ‘,,barxxx,,,’); //删除指定首尾字符
SELECT TRIM(TRAILING ‘,’ FROM ‘barxxyz,,’); //删除尾部字符’,’

UCASE(str) //转换成大写
SELECT UCASE(‘abc’);

RIGHT(str,LENGTH);// 取str最后length个字符
SELECT RIGHT(‘abc’,2);

SPACE(COUNT);//生成count个空格
SELECT SPACE(2);

SELECT REPLACE(’ abc ‘, SPACE(1), ‘s’);
SELECT LENGTH(SPACE(2));

1. 数字类
   ABS();//取绝对值
   SELECT ABS(‘-256411.25’);
   SELECT ABS(‘256411.25’);

BIN(decimal_num);// 十进制转二进制
SELECT BIN(8);

CEILING(num);// 向上取整
SELECT CEILING(2.6);
SELECT CEILING(2.1);

CONV(number2,from_base,to_base) //进制转换
SELECT CONV(8,10,2);

SELECT CONV(8,2,10); //转换错误0

FLOOR (number2 ) //向下取整
SELECT FLOOR(2.4);
SELECT FLOOR(2.6);

FORMAT (number,decimal_places ) //保留小数位数
SELECT FORMAT(12.232325,2);

HEX (DecimalNumber ) //转十六进制
SELECT HEX(8);
SELECT HEX(15);
注：HEX()中可传入字符串，则返回其ASC-11码，如HEX(‘DEF’)返回4142143
也可以传入十进制整数，返回其十六进制编码，如HEX(25)返回19

LEAST (number , number2 [,..]) //求最小值
SELECT LEAST(1,3,3,8,-1);

MOD (numerator ,denominator ) //求余
SELECT MOD(2,5);
SELECT MOD(5,1);

POWER (number ,POWER ) //求指数
SELECT POWER(2.232323, 2);
SELECT POWER(2,3);

RAND([seed]) //随机数
SELECT RAND(2);
SELECT RAND(0);
SELECT RAND(9);
SELECT RAND();

ROUND (number [,decimals ]) //四舍五入,decimals为小数位数]
SELECT ROUND(4.5);
SELECT ROUND(4.4);
注：返回类型并非均为整数，如：

符号函数,根据数值返回1(正数) 0(零) -1(负数).
SELECT SIGN(2.5544); //符号函数
SELECT SIGN(-32); // -1
SELECT SIGN(0); // 0
SELECT SIGN(234); // 1

3.日期类
ADDTIME (date2 ,time_interval ) //将time_interval加到date2
SELECT ADDTIME(‘12:00:00’, 102030); // 分别给 时 分 秒加对应时间

CONVERT_TZ (datetime2 ,fromTZ ,toTZ ) //转换时区
SELECT CONVERT_TZ(‘2004-01-01 12:00:00’,’BST’,’IDLE’);
SELECT CONVERT_TZ(‘2004-01-01 12:00:00’,’+00:00’,’+10:00’)

CURRENT_DATE ( ) //当前日期
SELECT CURRENT_DATE(); //年月日

CURRENT_TIME ( ) //当前时间
SELECT CURRENT_TIME();

CURRENT_TIMESTAMP ( ) //当前时间戳
SELECT CURRENT_TIMESTAMP();

DATE (DATETIME ) //返回datetime的日期部分
SELECT DATE(‘2010-12-12 12:12:12’);

DATE_ADD (date2 , INTERVAL d_value d_type ) //在date2中加上日期或时间
SELECT DATE_ADD(‘2010-12-12 12:12:12’, INTERVAL 2 DAY);
SELECT DATE_ADD(‘2010-12-12 12:12:12’, INTERVAL 2 HOUR);

DATE_FORMAT (DATETIME ,FormatCodes ) //使用formatcodes格式显示datetime
SELECT DATE_FORMAT(‘2010-12-12 12:12:12’,’%b %d %Y %h:%i %p’);
SELECT DATE_FORMAT(‘2010-12-12 12:12:12’,’%Y年 %c月 %d日 %h时%m分%s秒’);

DATE_FORMAT(NOW(),’%b %d %Y %h:%i %p’)
DATE_FORMAT(NOW(),’%m-%d-%Y’)
DATE_FORMAT(NOW(),’%d %b %y’)
DATE_FORMAT(NOW(),’%d %b %Y %T:%f’)

DATE_SUB (date2 , INTERVAL d_value d_type ) //在date2上减去一个时间
SELECT DATE_SUB(‘2010-12-12 12:12:12’, INTERVAL 1 DAY);

DATEDIFF (date1 ,date2 ) //两个日期差
SELECT DATEDIFF(‘2010-12-15’, ‘2010-12-12’);

DAY (DATE ) //返回日期号
SELECT DAY(‘2010-12-15’);

DAYNAME (DATE ) //英文星期
SELECT DAYNAME(‘2016-9-21’);

DAYOFWEEK (DATE ) //星期(1-7) ,1为星期天
SELECT DAYOFWEEK(‘2016-9-18’);

DAYOFYEAR (DATE ) //一年中的第几天
SELECT DAYOFYEAR(‘2016-9-21’);

EXTRACT (interval_name FROM DATE ) //从date中提取日期的指定部分
SELECT EXTRACT(DAY FROM ‘2016-9-21’);

MAKEDATE (YEAR ,DAY ) //给出年及年中的第几天,生成日期串
SELECT MAKEDATE(‘2016’,265);

MAKETIME (HOUR ,MINUTE ,SECOND ) //生成时间串
SELECT MAKETIME(12,12,12);

MONTHNAME (DATE ) //英文月份名
SELECT MONTHNAME(‘2016-9-21’);

NOW ( ) //当前时间
SELECT NOW();

SEC_TO_TIME (seconds ) //秒数转成时间
SELECT SEC_TO_TIME(‘201010’);

STR_TO_DATE (STRING ,FORMAT ) //字串转成时间,以format格式显示
SELECT STR_TO_DATE(‘2016-9-21 12:12:12’,’%Y-%m-%d %h:%i:%s’); 12进制

TIMEDIFF (datetime1 ,datetime2 ) //两个时间差
SELECT TIMEDIFF(‘2016-9-21 12:12:12’,’2016-9-21 13:12:12’);

TIME_TO_SEC (TIME ) //时间转秒数]
SELECT TIME_TO_SEC(‘2016-9-21 12:12:12’);

WEEK (date_time [,start_of_week ]) //第几周
SELECT WEEK(‘2016-9-21 12:12:12’);

YEAR (DATETIME ) //年份
SELECT YEAR(‘2016-9-21 12:12:12’);

DAYOFMONTH(DATETIME) //月的第几天
SELECT DAYOFMONTH(‘2016-9-21 12:12:12’);

HOUR(DATETIME) //小时
SELECT HOUR(‘2016-9-21 12:12:12’);

LAST_DAY(DATE) //date的月的最后日期
SELECT LAST_DAY(‘2016-9-21 12:12:12’);

MICROSECOND(DATETIME) //微秒
SELECT MICROSECOND(‘2016-9-21 12:12:12’);

MONTH(DATETIME) //月
SELECT MONTH(‘2016-9-21 12:12:12’);

MINUTE(DATETIME) //分返回符号,正负或0
SELECT MINUTE(‘2016-9-21 12:12:12’);

SQRT(number2) //开平方
SELECT SQRT(8);