Mysql 内置系统函数
丰富的函数往往能使工作事半功倍，函数能帮助我们做很多事情，比如字符串的处理，数值的运算，日期的运算等等
在这方面Mysql提供了多种内置函数，帮助开发人员编写简单快捷的sql语句
其中Mysql中常用的内置函数有三类
1.字符串函数
2.数值函数
3.日期函数
这些函数可以用在SELECT语句，及他的子句where、order by、having等等
也可以用在UPDATE、DELETE这些语句以及他们的子句中
一、什么是内置函数？
函数可以直接在select语句里面用，比如字符串的链接函数 concat()
字符串'abc'链接'cfg'
select concat('abc', 'efg');+----------------------+
| concat('abc', 'efg') |
+----------------------+
| abcefg |
+----------------------+
再链接第三个字符串"kil"
select concat('abc', 'efg', "kil");+-----------------------------+
| concat('abc', 'efg', "kil") |
+-----------------------------+
| abcefgkil |
+-----------------------------+
再链接第四个"hello"
select concat('abc', 'efg', "kil", "hello");在select中用concat()函数里，把所有的参数链接在一起
+--------------------------------------+
| concat('abc', 'efg', "kil", "hello") |
+--------------------------------------+
| abcefgkilhello |
+--------------------------------------+
我们也可以在concat()函数里面直接用变量，对于表来说"列"就是变量
比如创建一个user用户表，插入两条数据
create table users( id int, name char(30), age tinyint ); insert into users values(1, '张三', 40),(2, '李四', 39); select * from users;
+------+------+------+
| id | name| age |
+------+------+------+
| 1 | 张三 | 40 |
| 2 | 李四 | 39 |
+------+------+------+
concat(name, age)函数里面写字段名，那整列都作为变量
select concat(name, "年龄是：", age) from users;
+-------------------------------+
| concat(name, "年龄是：", age) |
+-------------------------------+
| 张三年龄是：40 |
| 李四年龄是：39 |
+-------------------------------+
函数中可以将字段名作为变量来用，变量的值就是这个列对应的每一行记录
函数也可以在where字句，ordre by字句中都可以用
基本上PHP中用的函数，Mysql中大部分也提供了
Mysql手册 -> 函数和操作符 -> 字符串函数/数值函数/日期函数
二、字符串函数
字符串函数是最常用的一种函数
| 1 | concat(str1, str2...strn) | 把传入的参数链接成一个字符串 |
| 2 | insert(str, x, y, insert) | 将字符串str，从x的位置开始，y个字符串长度的字符串，替换为"字符串insert" |
| 3 | lower(str) | 将传入的字符串转成小写 |
| upper(str) | 将传入的字符串转成大写 | |
| 4 | left(str, x) | 返回字符串最左边的x字符 |
| right(str, x) | 返回字符串最右边的x字符 | |
| 如果第二个参数传null，则返回null | ||
| 5 | lpad(str, n, pad) | l是left的缩写 |
| rpad(str, n, pad) | r是rigth的缩写 | |
| 用字符串pad，对字符串st对左边或最右边进行填充，直到长度为n个字符长度为止 | ||
| 6 | trim(str) | 去掉字符串两边空格 |
| ltrim(str) | 去左边空格 | |
| rtrim(str) | 去掉右边的空格 | |
| 7 | replace(str, a, b); | 用字符串b替换字符串str中所有出现的字符串a |
| 8 | strcmp(str1, str2) |
比较函数
按ACSII码字节比较函数，比较的是str1和str2 ACSII码的值大小 如果str1比str2小返回-1 如果str1比str2大返回1 如果str1等于str2小返回0 |
| 9 | substr(str, x, y) | 返回字符串str中第x的位置开始，y个字符串长度的字符串 |
insert( str, x, y, insert )
作用：将字符串str，从x开始的位置，y个字符串长度，替换为字符串insert
select insert("abcdefg", 2, 3, 'hellow');从第2个b开始（默认从1开始），往后三个字符的长度，换成hellow
+-----------------------------------+
| insert("abcdefg", 2, 3, 'hellow') |
+-----------------------------------+
| ahellowefg |
+-----------------------------------+
先修改一下表的内容
update users set name = '张三大兄弟' where id = 1; update users set name = '李四大兄弟' where id = 2; select * from users;
+------+------------+------+
| id | name | age |
+------+------------+------+
| 1 | 张三大兄弟 | 40 |
| 2 | 李四大兄弟 | 39 |
+------+------------+------+
将名字从第3个字符后面的3个字符换成"是中国人"，中文字符也是可以的
select insert(name, 3, 3, "是中国人") from users;
将"大兄弟"这三个字符换了
+--------------------------------+
| insert(name, 3, 3, "是中国人") |
+--------------------------------+
| 张三是中国人 |
| 李四是中国人 |
+--------------------------------+
对比原来没有换的名字
select insert(name, 3, 3, "是中国人"), name from users;
+--------------------------------+------------+
| insert(name, 3, 3, "是中国人") | name |
+--------------------------------+------------+
| 张三是中国人 | 张三大兄弟 |
| 李四是中国人 | 李四大兄弟 |
+--------------------------------+------------+
lower() 将传入的字符串转成小写
upper() 将传入的字符串转成大写
select lower("HELLO"), upper('hello');+----------------+----------------+
| lower("HELLO") | upper('hello') |
+----------------+----------------+
| hello | HELLO |
+----------------+----------------+
name字段插入小写英文 monica
insert into users values(3, 'monica', 37); select * from users;
+------+------------+------+
| id | name | age |
+------+------------+------+
| 1 | 张三大兄弟 | 40 |
| 2 | 李四大兄弟 | 39 |
| 3 | monica | 37 |
+------+------------+------+
查询大写的MONICA，将name字段转成大写的
select * from users where upper(name) = 'MONICA';
换成大写的查询出来了
+------+--------+------+
| id | name | age |
+------+--------+------+
| 3 | monica | 37 |
+------+--------+------+
对于数据表字段就是变量（variables）
select concat(name, '的年龄是', age) from users;
函数中可以将列（字段名）作为变量来用，变量的值就是这个列对应的每一行记录
也可以在where字句中和order by字句中用等
left(str, x)
right(str, x)
返回字符串最左边或最右边的x字符，如果第二个参数为null，则什么都不返回
select left('abcdefg', 3), right('abcdefg', 3);+--------------------+---------------------+
| left('abcdefg', 3) | right('abcdefg', 3) |
+--------------------+---------------------+
| abc | efg |
+--------------------+---------------------+
如果最后一个参数是null，以left()为例第二个参数传null，返回的就是null
select left('abcdefg', 3), right('abcdefg', 3), left('abcdefg', null);+--------------------+---------------------+-----------------------+
| left('abcdefg', 3) | right('abcdefg', 3) | left('abcdefg', null) |
+--------------------+---------------------+-----------------------+
| abc | efg | NULL |
+--------------------+---------------------+-----------------------+
lpad(str, n, pad) l是left的缩写
rpad(str, n, pad) r是rigth的缩写
用字符串pad对str最左边和最右边进行填充，直到个长度为n个字符长度为止
查看users表
select name from users;
+------------+
| name |
+------------+
| 张三大兄弟 |
| 李四大兄弟 |
| monica |
+------------+
把name字段都补足10个字符长度，左边用"#"号补充，然后右边用"@"号补充
select lpad(name, 10, '*'), rpad(name, 10, '@') from users;
+---------------------+---------------------+
| lpad(name, 10, '*') | rpad(name, 10, '@') |
+---------------------+---------------------+
| *****张三大兄弟 | 张三大兄弟@@@@@ |
| *****李四大兄弟 | 李四大兄弟@@@@@ |
| ****monica | monica@@@@ |
+---------------------+---------------------+
trim(str) 去掉字符串两边空格
ltrim(str) 去左边空格
rtrim(str) 去掉右边的空格
select trim(" abc "), ltrim(" abc "), rtrim(" abc ");这样有点看不出来
+-------------------+--------------------+--------------------+
| trim(" abc ") | ltrim(" abc ") | rtrim(" abc ") |
+-------------------+--------------------+--------------------+
| abc | abc | abc |
+-------------------+--------------------+--------------------+
用concat()函数在两边链接字符串"#"再查询，使用 \G 立起来查看，不然字段名称太长了
select concat("#", trim(" abc "), "#"), concat("#", ltrim(" abc "), "#"), concat("#", rtrim(" abc "), "#") \G*************************** 1. row ***************************
concat("#", trim(" abc "), "#"): #abc#
concat("#", ltrim(" abc "), "#"): #abc #
concat("#", rtrim(" abc "), "#"): # abc#
给字段起一个别名，看的更清晰
select concat("#", trim(" abc "), "#") one, concat("#", ltrim(" abc "), "#") two, concat("#", rtrim(" abc "), "#") three;+-------+----------+----------+
| one | two | three |
+-------+----------+----------+
| #abc# | #abc # | # abc# |
+-------+----------+----------+
replace(str, a, b);
字符串str里，所有的"a"都替换城"-"
select replace("希望a世界更好a所有的不如意a都消散", "a", " - ");+----------------------------------------------------------+
| replace("希望a世界更好a所有的不如意a都消散", "a", " - ") |
+----------------------------------------------------------+
| 希望 - 世界更好 - 所有的不如意 - 都消散 |
+----------------------------------------------------------+
strcmp(str1, str2) 字符串比较函数，
是按ACSII码字节比较函数，比较的是str1和str2的ACSII码的值大小
如果str1比str2小返回 -1
如果str1比str2大返回 1
如果str1等于str2小返回0
select strcmp("a", "b"), strcmp("b", "a"), strcmp("a", "a");+------------------+------------------+------------------+
| strcmp("a", "b") | strcmp("b", "a") | strcmp("a", "a") |
+------------------+------------------+------------------+
| -1 | 1 | 0 |
+------------------+------------------+------------------+
substr(str, 3, 9) 截取字符串
从第3个字符截取，截取9个字符
select substr("学习Mysql，学习了两三部教程后，感觉入门了", 3, 9);+-----------------------------------------------------------+
| substr("学习Mysql，学习了两三部教程后，感觉入门了", 3, 9) |
+-----------------------------------------------------------+
| Mysql，学习了 |
+-----------------------------------------------------------+
三、数值函数
| 1 | abs(x) | 返回x的绝对值 |
| 2 | ceil(x) |
返回大于x的最小整数， 比如 2.1, 2.5, 2.9返回的都是3， 进一取整法 |
| floor(x) | 返回小于x的最大整数， 比如 2.1, 2.5, 2.9返回的都是2， 割舍法 | |
| 3 | mod(x, y) | 返回x/y的模 |
| 4 | rand() | 返回0~1之间的随机数 |
| 5 | round(x, y) | 四舍五入函数，返回参数x的四舍五入的，有y位小数的值 |
| 6 | truncate(x, y) | 截断的函数，返回数字x，截断为y位小数的结果 |
abs() 返回绝对值函数
select abs(10), abs(-10);
负数10返回的是直接去掉负号的绝对值
+---------+----------+
| abs(10) | abs(-10) |
+---------+----------+
| 10 | 10 |
+---------+----------+
比如年龄字段，当然年龄没有负数，假如就想取一个正数
select abs(age) from users;
+----------+
| abs(age) |
+----------+
| 40 |
| 39 |
| 37 |
+----------+
ceil() 向上取整函数
floor() 向下取整函数
对比一下，ceil函数返回的是进1取整，floor函数的是割舍法
select ceil(2.1), ceil(2.5), ceil(2.9), floor(2.1), floor(2.5), floor(2.9);
+-----------+-----------+-----------+------------+------------+------------+
| ceil(2.1) | ceil(2.5) | ceil(2.9) | floor(2.1) | floor(2.5) | floor(2.9) |
+-----------+-----------+-----------+------------+------------+------------+
| 3 | 3 | 3 | 2 | 2 | 2 |
+-----------+-----------+-----------+------------+------------+------------+
mod() 取模函数
10%4模的结果是2
select mod(10, 4);
+------------+
| mod(10, 4) |
+------------+
| 2 |
+------------+
10%5模的结果是
select mod(10, 5);
+------------+
| mod(10, 5)|
+------------+
| 0 |
+------------+
1%11模的结果是1
select mod(1, 11);
+------------+
| mod(1, 11)|
+------------+
| 1 |
+------------+
如果前面是null，返回结果也是空
select mod(null, 11);
+---------------+
| mod(null, 11) |
+---------------+
| NULL |
+---------------+
rand() 随机数函数
返回0~1之间的随机数，是随机的是浮动数
每次的值都是不一样的
select rand();
+--------------------+
| rand() |
+--------------------+
| 0.9642467442566964 |
+--------------------+
想要1~100之间的随机数怎么办呢？
1). rend()乘以100，
2). 再用ceil或是floor取整
select ceil(rand()*100);
+------------------+
| ceil(rand()*100) |
+------------------+
| 33 |
+------------------+
round(x, y) 四舍五入函数
返回参数x的四舍五入的有y位小数的值
不加第二个参数，浮点数1.1四舍五入返回1
select round(1.1);
+------------+
| round(1.1) |
+------------+
| 1 |
+------------+
1.5 四舍五入返回2
select round(1.5);
+------------+
| round(1.5) |
+------------+
| 2 |
+------------+
1.4 四舍五入返回1
select round(1.4);
+------------+
| round(1.4) |
+------------+
| 1 |
+------------+
把第二个参数加上
浮点数 1.489 第二个参数是2，返回1.49，小数点后是2位的
select round(1.489, 2);
+-----------------+
| round(1.489, 2) |
+-----------------+
| 1.49 |
+-----------------+
truncate(x, y) 截断函数
第一个参数：12.35
第二个参数：2
truncate()函数和round()四舍五入函数对比
select truncate(1.235, 2), round(1.235, 2);
round()截断函数，小数超过两位直接截断，返回1.23
truncate()四舍五入函数，如果超过5或是5就进一位，返回1.24
+--------------------+-----------------+
| truncate(1.235, 2) | round(1.235, 2) |
+--------------------+-----------------+
| 1.23 | 1.24 |
+--------------------+-----------------+
四、日期函数
有时候会遇到这样的需求：
当前时间是多少？
下个月的今天是星期几？
统计截止到当前日期前三天的收入总和等等……
这些需求都需要时间日期函数来实现
当然我们可以用PHP的时间戳来完成，比如三天前，用当前时间戳减去3天的秒数
数据库里面的时间日期函数
| 1 | curdate() | 返回当前的日期 |
| 2 | curtime() | 返回当前的时间 |
| 3 | now() | 返回当前日期时间 |
| 4 | unix_timestamp(now()) | 返回unix时间戳 |
| 5 | from_unixtime(1632130879) | 返回unix时间戳日期的值 |
| 6 | week(now()) | 通过时间返回当前的周，一年的第几周 |
| 7 | year(now()) | 通过时间返回的的年 |
| 8 | hour(now()) | 通过时间返回当前的小时 |
| 9 | minute(now()) | |
| 10 | date_format(now(), "%Y-%m-%d %H:%i:%s") | 格式化日期时间 |
返回当前的日期
select curdate();
+------------+
| curdate() |
+------------+
| 2021-09-20|
+------------+
返回当前的时间
select curtime();
+-----------+
| curtime() |
+-----------+
| 17:16:47 |
+-----------+
返回既有日期又有时间
select now();
+---------------------+
| now() |
+---------------------+
| 2021-09-20 17:39:50 |
+---------------------+
返回unix时间戳
select unix_timestamp(now());
+-----------------------+
| unix_timestamp(now()) |
+-----------------------+
| 1632130879 |
+-----------------------+
返回unix时间戳中，对应的日期时间
select from_unixtime(1632130879);
返回1632130879对应的日期时间
+---------------------------+
| from_unixtime(1632130879) |
+---------------------------+
| 2021-09-20 17:41:19 |
+---------------------------+
从当前时间戳里面返回周，年，小时，是从当前时间now()里返回
select week(now()), year(now()), hour(now());
+-------------+-------------+-------------+
| week(now()) | year(now()) | hour(now()) |
+-------------+-------------+-------------+
| 38 | 2021 | 18 |
+-------------+-------------+-------------+
从时间curtime()里面获取当前的小时，分钟，秒
select hour(curtime()) '小时', minute(curtime()) '分钟', second(curtime()) '秒';
+------+------+------+
| 小时 | 分钟 | 秒 |
+------+------+------+
| 15 | 23 | 25 |
+------+------+------+
monthname()返回当前英文的月份
select monthname(now());
+------------------+
| monthname(now()) |
+------------------+
| September |
+------------------+
自定义日期格式，将当前的日期格式化
select date_format(now(), "%Y-%m-%d %H:%i:%s");
+-----------------------------------------+
| date_format(now(), "%Y-%m-%d %H:%i:%s") |
+-----------------------------------------+
| 2021-09-20 18:12:02 |
+-----------------------------------------+
五、流程控制函数
流程控制函数也是很常用到的一类函数，
用户可以使用这类函数，在一个sql语句中实现条件选择，这样就能做到提高语句的效率
先创建一个薪水表
create table salary( id int not null, salary decimal(9, 2) ); insert into salary values(1, 1000),(2, 2000),(3, 3000),(4, 4000),(5, 5000),(6, null); select id, salary from salary;
+----+---------+
| id | salary |
+----+---------+
| 1 | 1000.00 |
| 2 | 2000.00 |
| 3 | 3000.00 |
| 4 | 4000.00 |
| 5 | 5000.00 |
| 6 | NULL |
+----+---------+
流程控制函数：
| 1 | if(value, t, f) | 如果value值是真返回t，如果是假返回f |
| 2 | ifnull(value1, value2) | 如果value1不为空就返回value1，否则返回value2 |
| 3 | case when [value1] then [result1]...else[default]end | 跟程序里用到的switch case一样， 意思是如果value1是真的时候，返回result1，否则返回缺省值default |
if(value, t, f)
月薪在3000元以上的用hight表示，2000以下的用low表示
select id, salary, if(salary > 3000, 'hight', 'low') from salary;
+----+---------+-----------------------------------+
| id | salary | if(salary > 3000, 'hight', 'low') |
+----+---------+-----------------------------------+
| 1 | 1000.00 | low |
| 2 | 2000.00 | low |
| 3 | 3000.00 | low |
| 4 | 4000.00 | hight |
| 5 | 5000.00 | hight |
| 6 | NULL | low |
+----+---------+-----------------------------------+
ifnull(value1, value2)
如果薪水不为空（null）就返回薪水，如果薪水为空就返回0
select id, salary, ifnull(salary, 0) from salary;
+----+---------+-------------------+
| id | salary | ifnull(salary, 0) |
+----+---------+-------------------+
| 1 | 1000.00 | 1000.00 |
| 2 | 2000.00 | 2000.00 |
| 3 | 3000.00 | 3000.00 |
| 4 | 4000.00 | 4000.00 |
| 5 | 5000.00 | 5000.00 |
| 6 | NULL | 0.00 |
+----+---------+-------------------+
这个函数是替换空(null)值的，null值是不能参与数值运算的，所以可以通过这个函数把null值转为0来代替
case when [value1] then [result1]...else[default] end
select case when salary<=3000 then '薪水小于等于三千' else '高薪水' end from salary;
+------------------------------------------------------------------+
| case when salary<=3000 then '薪水小于等于三千' else '高薪水' end |
+------------------------------------------------------------------+
| 薪水小于等于三千 |
| 薪水小于等于三千 |
| 薪水小于等于三千 |
| 高薪水 |
| 高薪水 |
| 高薪水 |
+------------------------------------------------------------------+
六、其它函数
| 1 | database() | 当前的数据库名 |
| 2 | version() | 当前Mysql的版本 |
| 3 | user() | 当前的数据库用户 |
| 4 | inet_aton(ip) | 返回ip的数字表示 |
| 5 | inet_ntoa() | |
| 6 | md5() |
查看当前的数据库名
select database();
+------------+
| database() |
+------------+
| dbshop |
+------------+
其实 \s 看状态也可以
查看当前的数据库版本
select version();
+------------------+
| version() |
+------------------+
| 5.7.18-cynos-log|
+------------------+
查看当前的用户
select user();
root用户在本地登录的
+------------------+
| user() |
+------------------+
| root@localhost |
+------------------+
返回ip地址的网络字节顺序
select inet_aton("192.168.1.1");+--------------------------+
| inet_aton("192.168.1.1")|
+--------------------------+
| 3232235777 |
+--------------------------+
返回网络字节序列3232235777，代表的ip地址，
select inet_ntoa(3232235777);
+-----------------------+
| inet_ntoa(3232235777)|
+-----------------------+
| 192.168.1.1 |
+-----------------------+
inet_ntoa()与inet_aton()是互逆的，
主要用途是将字符串的ip地址转为数字表示网络字节顺序，这样更方便的进行ip地址或者网络端的比较
password(str)加密
select password('123456');+-------------------------------------------+
| password('123456') |
+-------------------------------------------+
| *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-------------------------------------------+
大概是41位长的字符串
其实password()函数是设置Mysql系统里面的用户密码，不要用来应用的数据加密，比如网站的用户加密不要用他
password()函数是给Mysql本身系统用户用的，比如给Mysql用户设置密码用password()加密
如果应用方面加密要用md5()函数，比如网站用户密码加密
select md5('123456');+----------------------------------+
| md5('123456') |
+----------------------------------+
| e10adc3949ba59abbe56e057f20f883e |
+----------------------------------+
查看Mysql用户表的加密方式是passwork()加密
select * from mysql.user; select * from mysql.user \G