MySQL常用内置函数
文章目录
- 数学函数
- ` abs(X)` 返回X的绝对值
- `mod(N,M)` 或 `%` 返回 N 被 M 除的余数
- `floor(X)` 返回不大于X的最大整数值。
- `ceiling(X)` 返回不小于X的最小整数值。
- `round(X)` 返回参数X的四舍五入的一个整数。
- 字符串函数
- `ascii(str)` 返回字符串str的最左面字符的ASCII代码值。如果str是空字符串,返回0。如果str是NULL,返回NULL。
- `concat(str1,str2,...)` 返回来自于参数连结的字符串。如果任何参数是NULL,返回NULL。可以有超过2个的参数。一个数字参数被变换为等价的字符串形式。
- `length(str)` 返回字符串str的长度
- `locate(substr,str)` 返回子串substr在字符串str第一个出现的位置,如果substr不是在str里面,返回0.
- `instr(str,substr)` 返回子串substr在字符串str中的第一个出现的位置。
- `left(str,len)` 返回字符串str的最左面len个字符。
- `right(str,len)` 返回字符串str的最右面len个字符。
- `substr(str,pos)` 从字符串str的起始位置pos返回一个子串,和substr()一样
- `trim(str)` 返回字符串str,所有前缀或后缀被删除了
- `ltrim(str)` 返回删除了其前置空格字符的字符串str
- `rtrim(str)`返回删除了其拖后空格字符的字符串str。
- `replace(str,from_str,to_str)` 返回字符串str,其字符串from_str的所有出现由字符串to_str代替。
- `repeat(str,count)` 返回由重复count次的字符串str组成的一个字符串。如果count <= 0,返回一个空字符串。如果str或count是NULL,返回NULL。
- `reverse(str)` 返回颠倒字符顺序的字符串str
- `insert(str,pos,len,newstr)` 返回字符串str,在位置pos起始的子串且len个字符长的子串由字符串newstr代替。
- `substring_index(str,delim,count)` 从str中找到delim字符串按下标获取字符串
- 日期和时间函数
- 条件判断函数
- 系统信息函数
- `version()` 函数返回数据库的版本号
- `connection_id()` 函数返回服务器的连接数,也就是到现在为止MySQL服务的连接次数
- `database()` 和 `schema()` 返回当前数据库名
- 获取用户名的函数
- 获取字符串的字符集和排序方式的函数
- 获取最后一个自动生成的ID值的函数
- 加密函数
- 格式化函数
- 其他函数
MySQL数据库中提供了很丰富的函数。MySQL函数包括数学函数、字符串函数、日期和时间函数、条件判断函数、系统信息函数、加密函数、格式化函数等。通过这些函数,可以简化用户的操作。例如,字符串连接函数可以很方便的将多个字符串连接在一起。
数学函数
数学函数是MySQL中常用的一类函数。主要用于处理数字,包括整型、浮点数等。数学函数包括绝对值函数、正弦函数、余弦函数、获取随机数的函数等
abs(X)
返回X的绝对值
-- abs(X):返回X的绝对值
select abs(-100)
mod(N,M)
或 %
返回 N 被 M 除的余数
-- mod(N,M)或%:返回N被M除的余数
select mod(10,3);
select 10 % 3;
floor(X)
返回不大于X的最大整数值。
-- floor(X):返回不大于X的最大整数值。
select floor(1.23), floor(-1.23);
ceiling(X)
返回不小于X的最小整数值。
-- ceiling(X):返回不小于X的最小整数值。
select ceiling(1.23), ceiling(-1.23);
round(X)
返回参数X的四舍五入的一个整数。
-- round(X) :返回参数X的四舍五入的一个整数。
select round(1.23), round(-1.53);
字符串函数
ascii(str)
返回字符串str的最左面字符的ASCII代码值。如果str是空字符串,返回0。如果str是NULL,返回NULL。
-- ascii(str):返回字符串str的最左面字符的ASCII代码值。如果str是空字符串,返回0。如果str是NULL,返回NULL。
select ascii('2'),ascii(2),ascii('dx'),ascii(''),ascii(null),ascii('null');
concat(str1,str2,...)
返回来自于参数连结的字符串。如果任何参数是NULL,返回NULL。可以有超过2个的参数。一个数字参数被变换为等价的字符串形式。
-- concat(str1,str2,...):返回来自于参数连结的字符串。如果任何参数是NULL,返回NULL。可以有超过2个的参数。一个数字参数被变换为等价的字符串形式。
select concat('a','b',0),concat(1,2),concat(0.23), concat('a','b',null);
length(str)
返回字符串str的长度
-- length(str):返回字符串str的长度
select length(0), length('abc'),length(null),length(0.001),length('null');
locate(substr,str)
返回子串substr在字符串str第一个出现的位置,如果substr不是在str里面,返回0.
-- locate(substr,str):返回子串substr在字符串str第一个出现的位置(下标从1开始),如果substr不是在str里面,返回0.
select locate('acc', 'abc123acc');
instr(str,substr)
返回子串substr在字符串str中的第一个出现的位置。
-- instr(str,substr):返回子串substr在字符串str中的第一个出现的位置, 与select locate('acc', 'abc123acc');相反。
select instr('abc123acc', 'acc');
left(str,len)
返回字符串str的最左面len个字符。
-- left(str,len):返回字符串str的最左面len个字符。
select left('abcdefg', 3);
right(str,len)
返回字符串str的最右面len个字符。
-- right(str,len):返回字符串str的最右面len个字符
select right('abcdefg', 3);
substr(str,pos)
从字符串str的起始位置pos返回一个子串,和substr()一样
-- substr(str,pos):从字符串str的起始位置pos返回一个子串,和substr()一样。
select substr('abcdefg',3), substring('abcdefg', 3);
trim(str)
返回字符串str,所有前缀或后缀被删除了
-- trim(str):返回字符串str,所有前缀或后缀被删除了
select concat(trim(' ..123... '), 'aaa') as a;
ltrim(str)
返回删除了其前置空格字符的字符串str
-- ltrim(str):返回删除了其前置空格字符的字符串str
select concat(ltrim(' 123 '), 'aa');
rtrim(str)
返回删除了其拖后空格字符的字符串str。
-- rtrim(str):返回删除了其拖后空格字符的字符串str。
select concat(rtrim(' 123 '), 'aa');
replace(str,from_str,to_str)
返回字符串str,其字符串from_str的所有出现由字符串to_str代替。
-- replace(str,from_str,to_str):返回字符串str,其字符串from_str的所有出现由字符串to_str代替。
select replace('abc123', '12', '45');
repeat(str,count)
返回由重复count次的字符串str组成的一个字符串。如果count <= 0,返回一个空字符串。如果str或count是NULL,返回NULL。
-- repeat(str,count):返回由重复count次的字符串str组成的一个字符串。如果count <= 0,返回一个空字符串。如果str或count是NULL,返回NULL。
select repeat('abc', 3);
reverse(str)
返回颠倒字符顺序的字符串str
-- reverse(str):返回颠倒字符顺序的字符串str
select reverse('abc');
insert(str,pos,len,newstr)
返回字符串str,在位置pos起始的子串且len个字符长的子串由字符串newstr代替。
-- insert(str,pos,len,newstr):返回字符串str,在位置pos起始的子串且len个字符长的子串由字符串newstr代替
select insert('abcdefg', 2, 3, '11');
substring_index(str,delim,count)
从str中找到delim字符串按下标获取字符串
select
-- substring_index(str,delim,count),从str中找到delim字符串按下标获取字符串
select
substring_index('abcdecfg', 'c', 1),
substring_index('abcdecfg', 'c', - 1),
substring_index('abcdecfg', 'c', 2),
substring_index('abcdecfg', 'c', 3);
日期和时间函数
-- WEEKDAY(date):返回date的星期索引(0=星期一,1=星期二, ……6= 星期天)。
select weekday(now()) + 1;
-- DAYOFMONTH(date):返回date的月份中的日期,在1到31范围内
select dayofmonth(now());
-- DAYOFYEAR(date):返回date在一年中的日数, 在1到366范围内。
select dayofyear(now());
-- MONTH(date):返回date的月份,范围1到12
select month(now());
-- DAYNAME(date):返回date的星期名字
select dayname(now());
-- MONTHNAME(date) :返回date的月份名字。
select monthname(now());
-- QUARTER(date):返回date一年中的季度,范围1到4。
select quarter(now());
-- WEEK(date,first):对于星期天是一周的第一天的地方,有一个单个参数,返回date的周数,范围在0到52。
-- 2个参数形式WEEK()允许你指定星期是否开始于星期天或星期一。如果第二个参数是0,星期从星期天开始,如果第二个参数是1,从星期一开始。
select week(now());
select week(now(), 0);
select week(now(), 1);
-- YEAR(date):返回date的年份,范围在1000到9999。
select year(now());
-- HOUR(time):返回time的小时,范围是0到23。
select hour(now());
-- MINUTE(time):返回time的分钟,范围是0到59。
select minute(now());
-- SECOND(time):回来time的秒数,范围是0到59。
select second(now());
-- DATE_ADD(date,INTERVAL expr type) ,进行日期增加的操作,可以精确到秒
select date_add(now(), interval 1 day);
select date_add(now(), interval 3 hour);
select date_add(now(), interval 2 minute);
select date_add(now(), interval 3 second);
select date_add(now(), interval '1:1' minute_second); -- 同时加分、秒
select date_add(now(), interval '1:1' hour_minute); -- 同时加时、分
select date_add(now(), interval '1:1' day_hour); -- 同时加天、时
select date_add(now(), interval '1' month);
select date_add(now(), interval '1:1' year_month);-- 同时加年、月
select date_add(now(), interval '1 0:0:3' day_second);-- 同时加年、月
DATE_SUB(date,INTERVAL expr type) ,进行日期减少的操作,可以精确到秒, 和上面相加用法一样
-- 获取当前时间:以‘YYYY-MM-DD HH:MM:SS’或YYYYMMDDHHMMSS格式返回当前的日期和时间
select now();
-- current_date():以‘YYYY-MM-DD’或YYYYMMDD格式返回今天日期值,取决于函数在一个字符串还是数字上下文被使用。
select current_date();
-- CURRENT_TIME:以‘HH:MM:SS’或HHMMSS格式返回当前时间值
select current_time();
-- 时间格式化date_format()
select date_format(now(), '%Y-%m-%d %H:%i:%S');
select date_format('2017-09-20 08:30:45', '%Y-%m-%d %H:%i:%S');
-- TO_DAYS函数 返回一个天数! 啊哈?什么天数? 从年份0开始的天数
select count(1) from `trade$platform_user` as u where to_days(now()) = to_days(u.`addtime`)
条件判断函数
-- IF(expr1,expr2,expr3)如果 expr1 是TRUE (expr1 <> 0 and expr1 <> NULL),则 IF()的返回值为expr2;
-- 否则返回值则为 expr3。IF() 的返回值为数字值或字符串值,具体情况视其所在语境而定。
select if(1 > 2, 'true', 'false');
select if(1 < 2, 'true', 'false');
-- ifnull(expr1, expr2), 如果expr1为null, 则返回expr2
select ifnull(null, 0);
select ifnull('null', 1);
-- Strcmp(str1,str2):如果str1>str2返回1,str1=str2反回0,str1<str2返回-1)
select strcmp('text', 'text2');
select strcmp('text2', 'text');
select strcmp('text', 'text');
-- case when then else end
select
u.type,
case
when u.type = 0
then '用户'
when u.type = 1
then '商户'
else '未知'
end
from
`trade$platform_user` as u ;
系统信息函数
系统信息函数用来查询MySQL数据库的系统信息。例如,查询数据库的版本,查询数据库的当前用户等。本小节将详细讲解系统信息函数的作用和使用方法。
version()
函数返回数据库的版本号
-- version()函数返回数据库的版本号;
select version();
connection_id()
函数返回服务器的连接数,也就是到现在为止MySQL服务的连接次数
-- connection_id()函数返回服务器的连接数,也就是到现在为止MySQL服务的连接次数;
select connection_id();
database()
和 schema()
返回当前数据库名
-- database()和schema()返回当前数据库名
select database(), schema();
获取用户名的函数
-- 获取用户名的函数
select user(), system_user(), session_user(), current_user();
获取字符串的字符集和排序方式的函数
-- 获取字符串的字符集和排序方式的函数
-- CHARSET(str)函数返回字符串str的字符集,一般情况这个字符集就是系统的默认字符集;COLLATION(str)函数返回字符串str的字符排列方式
select charset('abc'), collation('abc');
获取最后一个自动生成的ID值的函数
-- 获取最后一个自动生成的ID值的函数
-- LAST_INSERT_ID()函数返回最后生成的AUTO_INCREMENT值
select last_insert_id();
加密函数
加密函数 md5(str)
-- 加密函数md5(str)
-- md5(str)函数可以对字符串str进行加密。MD5(str)函数主要对普通的数据进行加密。下面使用MD5(str)函数为字符串“abcd”加密。
select md5('111111');
加密函数 encode(str,pswd_str)
-- 加密函数encode(str,pswd_str)
-- encode(str,pswd_str)函数可以使用字符串pswd_str来加密字符串str。加密的结果是一个二进制数,必须使用BLOB类型的字段来保存它。
select encode('aaa', 'abc');
解密函数 decode(crypt_str,pswd_str)
-- 解密函数decode(crypt_str,pswd_str)
-- decode(crypt_str,pswd_str)函数可以使用字符串pswd_str来为crypt_str解密。crypt_str是通过ENCODE(str,pswd_str)加密后的二进制数据。字符串pswd_str应该与加密时的字符串pswd_str是相同的。
-- 下面使用DECODE(crypt_str,pswd_str)为ENCODE(str,pswd_str)加密的数据解密。
select decode('111111', 'adsadskdjkasjdksjdkas');
格式化函数
-- FORMAT(x,n)函数可以将数字x进行格式化,将x保留到小数点后n位。这个过程需要进行四舍五入。
select format(1.23458, 2), format(1.236545, 2);
其他函数
-- ASCII(s)返回字符串s的第一个字符的ASCII码;BIN(x)返回x的二进制编码;
-- HEX(x)返回x的十六进制编码;OCT(x)返回x的八进制编码;CONV(x,f1,f2)将x从f1进制数变成f2进制数。
select ascii('abc'), bin('abc'), hex('abc'), oct('abc');
select conv(2000, 10, 2);
-- IP地址与数字相互转换的函数
-- INET_ATON(IP)函数可以将IP地址转换为数字表示;INET_NTOA(n)函数可以将数字n转换成IP的形式。
-- 其中,INET_ATON(IP)函数中IP值需要加上引号。这两个函数互为反函数。
select inet_aton('192.168.0.118');
select inet_ntoa('3232235638');
-- 加锁函数和解锁函数
-- GET_LOCT(name,time)函数定义一个名称为nam、持续时间长度为time秒的锁。如果锁定成功,返回1;如果尝试超时,返回0;如果遇到错误,返回NULL。
-- RELEASE_LOCK(name)函数解除名称为name的锁。如果解锁成功,返回1;如果尝试超时,返回0;如果解锁失败,返回NULL;
-- IS_FREE_LOCK(name)函数判断是否使用名为name的锁。如果使用,返回0;否则,返回1。
-- BENCHMARK(count,expr)函数将表达式expr重复执行count次,然后返回执行时间。该函数可以用来判断MySQL处理表达式的速度。
select benchmark(1000000, 1*1/1);
-- 改变字符集的函数
-- CONVERT(s USING cs)函数将字符串s的字符集变成cs
select charset('abc'), charset(convert('abc' using 'gbk'));
-- CAST(x AS type)和CONVERT(x,type)这两个函数将x变成type类型。
-- 这两个函数只对BINARY、CHAR、DATE、DATETIME、TIME、SIGNED INTEGER、UNSIGNED INTEGER这些类型起作用。
-- 但两种方法只是改变了输出值的数据类型,并没有改变表中字段的类型。
select cast(111 as char);
原文地址:https://blog.csdn.net/sinat_38899493/article/details/78710482
作者:不敲代码的攻城狮
出处:https://www.cnblogs.com/leigq/
任何傻瓜都能写出计算机可以理解的代码。好的程序员能写出人能读懂的代码。