mysql中常用的sql函数_Mysql中的常用函数:

Mysql中的常用函数:

1.字符串函数:

(1).合并字符串

concat():// concat('M','y',"SQL",'5.5');== MySQL5.5//当传入的参数有一个值为NULL,返回的结果值为NULL

concat_ws();// 指定分隔符插入到字符串中:concat_ws(":",'2017','09');==2017:09,但如果分隔符是NULL的话,结果就为NULL,参数是NULL,不影响结果

(2).比较字符串大小的函数:

select strcmp(字符串1,字符串2);大于返回1,小于返回-1,等于返回0

(3).获取字符串长度的函数:

length(); // 计算空格,指的是传入参数的长度(字节长度),一个汉字是两个字节.

char_length();// 传入参数的字符数(字符长度)

(4).字母大小写转换函数:

UPPER();或者是UCASE()转化为大写

LOWER();

(5).查找字符串:

select FIND_IN_SET(查找字符串,目标字符串集合);// 返回字符串位置的函数,目标字符串是以逗号分割的字符串集合,下标从1开始

select FIELD(查找字符串,目标字符串1,目标字符串2,...........);// 返回字符串位置的函数

LOCATE(str1,str);// 返回str中字符串str1的开始位置

POSITION(str1 IN str);// 返回str中字符串str1的开始位置

INSTR(str,str1); // 返回str中字符串str1的开始位置

ELT(n,str1,str2......,strn);// 返回第n个字符串

MAKE_SET(num,str1,str2,...strn) ;// 先将数值num转化为二进制,然后按照二进制数从参数str1,str2,....,strn中选取相应的字符串

例子:select BIN(5) as 二进制数,MAKE_SET(5,'MYSQL','ORACLE','SQL SERVER','POSTGRESQL') as 选取后的字符串;// 结果为MYSQL,SQL SERVER

(6).截取字符串函数:

LEFT(str,num);// 从左边截取,字符串是str,长度是num

RIGHT(str,num);// 从右边截取,字符串是str,长度是num

SUBSTRING(str,num,len);// 字符串是str,从num位置开始,长度是len,包含num位置

MID(str,num,len);// 字符串是str,从num位置开始,长度是len,包含num位置

(7),去除字符串的首尾空格:

LTRIM(str);// 去除字符串开始处空格

RTRIM(str);// 去除字符串结束处空格

TRIM(str);// 去除字符串首尾空格

(8).替换字符串函数:

INSERT(str,pos,len,newstr);// str是原字符串,pos是需要替换的开始位置,len是替换的长度(根据需要来定),newstr是替换的新字符串

REPLACE(str,substr,newstr);// str是原字符串,substr是需要替换的字符串,newstr是替换substr的字符串,替换所有匹配的字符串

注意:比较以下两个结果:

select replace("THIS IS TEST STRING","IS","IS NOT");// THIS NOT IS NOT TEST STRING,不区分单词,切记

select replace("THIS IS TEST STRING"," IS "," IS NOT ");//THIS IS NOT TEST STRING

2.数值函数:

(1).获取随机数:

RAND(); // 返回的数完全随机

RAND(x); // x表示数字,返回的随机数值是相同的(确定的)

(2).获取整数的函数:

CEIL(x);// 上取整

FLOOR(x);// 下取整

(3).截取数值函数:

TRUNCATE(x,y);// x表示要截取的数值,y表示小数点后几位,也就是保留几位小数,是直接舍去,而不是四舍五入.

(4).四舍五入函数:

ROUND(x);

ROUND(x,y);// 返回数值x保留到小数点后y位

3.日期函数:

(1).获取当前日期和时间函数:

now();// 当前日期和时间

locatime();// 当前日期和时间

current_timestamp();// 当前日期和时间

SYSDTAE();// 当前日期和时间

(2).当前日期:

curdate();// 当前日期

current_date();// 当前日期

(3).当前时间:

curtime();

current_time();

(4).各种方式显示日期和时间:

unix_timestamp(日期类型的字符串);// 将日期类型的字符串转化为整形(秒数),UNIX格式

from_unixtime("整形");// 将整形字符串转化为日期,普通格式

UTC_DATE();// utc日期,只有日期

UTC_TIME();// utc时间 ,只有时间

(5).获取日期和时间各部分值:

SELECT NOW() 当前日期和时间,

YEAR(NOW()) 年,

QUARTER(NOW()) 季度,

MONTH(NOW()) 月,

WEEK(NOW())星期,

DAYOFMONTH(NOW()) 天,

HOUR(NOW()) 小时,

MINUTE(NOW()) 分,

SECOND(NOW()) 秒;

(6).月(参数都是日期类型的字符串):

MONTH(date);

MONTHNAME(date);

(7).星期:

week(date);// 年中第几个星期

weekofyear(date);// 年中第几个星期

dayname(date);// 星期,英文显示

dayofweek(date);// 星期(不理解,用下面的)

weekday(date);// 星期,数字表示

(8).天

dayofyear(date);// 年中的第几天

dayofmonth(date);// 月中的第几天

(9).EXTRACT函数

EXTRACT(YEAR from NOW()); // 年

EXTRACT(month from NOW()); // 月

EXTRACT(day from NOW()); // 日

EXTRACT(hour from NOW()); // 时

EXTRACT(minutefrom NOW()); // 分

EXTRACT(second from NOW()); // 秒

(10).计算日期和时间的函数:

SELECT NOW() 当前日期和时间,

TO_DAYS(NOW()) 相隔天数, // 距离0000年1月1日

FROM_DAYS(TO_DAYS(NOW())) 一段时间后日期和时间,

DATEDIFF(NOW(),'2000-12-01') 相隔天数;

(11).与指定日期和时间操作:

ADDDATE(date,n); // 计算date加上n天后的日期

SUBDATE(date,n); // 计算date减去n天后的日期

SUBDATE(d,INTERVAL expr type);

例子:

select adddate(now(),interval '2,3' year_month ) '2年3个月后的日期';

4.系统信息函数

(1).获取MySQL系统信息: 使用\h

SELECT

VERSION() 版本号,

DATABASE() 数据库名,

USER() 用户名;

(2).获取自增长约束的最后ID值

SELECT LAST_INSERT_ID();

(3).其他函数:

IF(value,t f)

如果value是真，返回t，否则返回f

IFNULL(value1,value2)

如果value1不为空返回value1，否则返回value2

CASE WHEN [value1] THEN[result1]…ELSE[default]

END

如果value1是真，返回result1，否则返回default

CASE [expr]WHEN[value1] THEN[result1]…ELSE[default]

END

如果expr等于value1，返回result1，否则返回default

函数

作用

PASSWORD(str)

实现对字符串str进行加密

FORMAT(x,n)

实现将数字x进行格式化，保留n位小数

INET_ATON(ip)

实现将IP地址转换成数字

INET_NTOA(x)

实现将数字转换成IP

GET_LOCT(name,time)

创建一个持续时间为time的名为name的锁

RELEASE_LOCT(name)

为名为name的锁进行解锁

BENCHMARK(count,expr)

实现将表达式重复执行count次

CONVERT(s USING cs)

实现将字符串s的字符集变成cs

CONVERT(x,type)

实现将x变成type类型