文章目录
一、函数的理解
1.1什么是函数
函数在计算机语言使用中贯穿始终,函数的作用是可以把我们经常使用的代码封装起来,需要的时候调用即可,这样提高了代码的效率而且提高了可维护性。在SQL中我们也可以使用函数对检索出来的数据进行函数操作,使用这些函数,可以极大的提高用户对数据库的管理效率
从函数的定义角度出发,函数可以分成内置函数,自定义函数
1.2不同的DBMS函数的差异
DBMS之间的差异很大,远大于同一个语言不同版本之间的差异。实际上很少的函数被DBMS同时支持的。大部分DBMS会有自己特定的函数,这意味着采用SQL函数的代码可移植性很差,因此在使用函数的时候需要特别注意。
1.3MySQL的内置函数及分类
MySQL提供了丰富的内置函数,从实现功能的角度来看可以分为:
- 数值函数
- 字符串函数
- 日期和时间函数
- 流程控制函数
- 加密与解密函数
- 获取MySQL信息函数
- 聚合函数等
可以将这些丰富的内置函数再分为两类:单行函数和聚合函数(分组函数)
二、数值函数
函数 | 用法 |
---|---|
ABS(X) | 返回X的绝对值 |
SIGN(X) | 返回X的符号,正数返回1,复数返回-1,0返回0 |
PI() | 返回圆周率的值 |
CEIL(X) | 返回大于或等于某个值的最小整数 |
FLOOR(X) | 返回小于或等于某个值的最大整数 |
LEAST(X1,X2,X3) | 返回列表中的最小值 |
GREATEST(X1,X2,X3) | 返回列表中最大值 |
MOD(X,Y) | 返回X除以Y以后的余数 |
RAND() | 返回0~1的随机值 |
RAND(X) | 返回0~1的随机值,其中X的值作种子值,相同的X值会产生相同的随机数 |
ROUND(X) | 返回一个对X的值进行四舍五入后,最接近于X的整数 |
ROUND(X,Y) | 返回一个对X的值进行四舍五入后最接近X的值,并保留到小数点后面Y位 |
TRUNCATE(X,Y) | 返回数字X截断为Y位小数的结果 |
SQRT(X) | 返回X的平方根,当X的值为负数时,返回NULL |
- 简单举例
- ABS(X)函数
- CEIL(X)函数
FLOOR(X)函数,和CEIL用法刚好相反
MOD(X,Y)返回X/Y的模
和x%y的结果相同,如果被模数和模数任何一个为NULL结果都为NULL
RAND()返回0~1的随机值
ROUND(X,Y)函数:返回参数X的四舍五入的有Y位小数的值
如果是整数,将会保留Y位数量0;如果不写y则默认y为0,即将x四舍五入后取整
TRUNCATE(X,Y)函数:返回数字X截断为Y位小数的结果
仅仅只是截断而不是四舍五入
三、字符串函数
函数 | 功能 |
---|---|
CONCAT(x1,x2,xn…) | 连接x1,x2,xn为一个字符串 |
INSERT(str,x,y,instr) | 将字符串从第X位置开始,Y个字符长的子串替换为字符串instr |
LOWER(str) | 将字符串str中全转为小写 |
UPPER(str) | 将字符串str中全转为大写 |
LEFT(str,x) | 返回字符串str最左边的x个字符 |
RIGHT(str,x) | 返回字符串str最右边的x个字符 |
LPAD(str,n,pad) | 用字符串对左边填充,直到长度为N个字符长度 |
RPAD(str,n,pad) | 用字符串对右边填充,直到长度为N个字符长度 |
LTRIN(str) | 去掉字符串str左边空格 |
RTRIM(str) | 去掉字符串srt右边空格 |
REPEAT(str,x) | 返回str重复x次结果 |
REPLACE(str,a,b) | 用字符串b替换字符串str中出现所有的字符串a |
STRCMP(x1,x2) | 比较字符串x1和x2 |
TRIM(str) | 去掉行尾和行头空格 |
SUBSTRING(str,y) | 返回从字符串str x 位置起y个字符长度的字串 |
- 示范
|CONCAT(x1,x2,xn…)|连接x1,x2,xn为一个字符串|
如果加入NULL的话结果都将会是NULL
|INSERT(str,x,y,instr)|将字符串从第X位置开始,Y个字符长的子串替换为字符串instr|
|LOWER(str)|将字符串str中全转为小写|
|UPPER(str)|将字符串str中全转为大写|
|LEFT(str,x)|返回字符串str最左边的x个字符|
|RIGHT(str,x)|返回字符串str最右边的x个字符|
如果第二个参数是NULL则返回NULL
|LPAD(str,n,pad)|用字符串对左边填充,直到长度为N个字符长度|
|RPAD(str,n,pad)|用字符串对右边填充,直到长度为N个字符长度|
|LTRIN(str)|去掉字符串str左边空格|
|RTRIM(str)|去掉字符串右边空格|
|REPEAT(str,x)|返回str重复x次结果|
|REPLACE(str,a,b)|用字符串b替换字符串str中出现所有的字符串a|
用2000替换掉字符串中的2001
|STRCMP(x1,x2)|比较字符串x1和x2|
如果S1比S2小返回-1.如果相等返回0,如果S1大于S2返回1
|TRIM(str)|去掉行尾和行头空格|
|SUBSTRING(str,x,y)|返回从字符串str x 位置起y个字符长度的字串|
四、日期和时间函数
函数 | 功能 |
---|---|
CURDATE() | 返回当前日期 |
CURTIME() | 返回当前时间 |
NOW() | 返回当前的日期和时间 |
UNIX_TIMESTAMP(date) | 返回日期date的UNIX时间戳 |
FROM_UNIXTIME | 返回UNIX时间戳的日期值 |
WEEK(date) | 返回日期date为一年中的第几周 |
YEAR(date) | 返回日期date的年份 |
HOUR(time) | 返回time的小时值 |
MINUTE(time) | 返回time的分钟值 |
MONTHNAME(date,fmt) | 返回date的月份名 |
DATE_FORMAT(date,fmt) | 返回按字符串fmt格式化日期date值 |
DATE_ADD(date,INTERVAL expr type) | 返回一个日期或时间值加上一个时间间隔的时间值 |
DATEDIFF(expr,expr2) | 返回起始时间expr和结束时间expr2之间的天数 |
- 举例
|CURDATE()|返回当前日期|
|CURTIME()|返回当前时间|
|NOW()|返回当前的日期和时间|
|UNIX_TIMESTAMP(date)|返回日期date的UNIX时间戳|
Unix 时间戳是从1970年1月1日(UTC/GMT的午夜)开始所经过的秒数,不考虑闰秒
|FROM_UNIXTIME|返回UNIX时间戳的日期值|
和UNIX_TIMESTAMP(date)互逆操作
|WEEK(date)|返回日期date为一年中的第几周|
|YEAR(date)|返回日期date的年份|
|HOUR(time)|返回time的小时值|
|MINUTE(time)|返回time的分钟值|
|MONTHNAME(date,fmt)|返回date的月份名|
|DATE_FORMAT(date,fmt)|返回按字符串fmt格式化日期date值|
将当前时间显示为“年,月,日”格式:
|DATE_ADD(date,INTERVAL expr type)|返回一个日期或时间值加上一个时间间隔的时间值|
31天后&31天前
|DATEDIFF(expr,expr2)|返回起始时间expr和结束时间expr2之间的天数|
五、流程函数
函数 | 功能 |
---|---|
IF(value,t,f) | 如果是value是真,返回t;如果是假返回f |
IFNULL | 如果value1不为空,返回value1,否则返回value2 |
CASR WHEN[value1] THEN[result] …ELSE [default]END | 如果value1是真,返回result1,否则返回default |
CASE[expr]WHEN[value1]THEN[result1]…ELSE[default]END | 如果expr等于value1,返回result1,否则返回default |
- 首先我们创建一个员工薪水表
salary(薪水)设置为decimal(定点数(M.D)M为总位数,D为小数位数)
mysql> create table salary (userid int,salary decimal(9,2));
Query OK, 0 rows affected (0.01 sec)
插入一些数值
mysql> insert into salary values(1,1000),(2,2000),(3,3000),(4,4000),(5,5000),(1,null);
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> select * from salary;
+--------+---------+
| userid | salary |
+--------+---------+
| 1 | 1000.00 |
| 2 | 2000.00 |
| 3 | 3000.00 |
| 4 | 4000.00 |
| 5 | 5000.00 |
| 1 | NULL |
+--------+---------+
6 rows in set (0.00 sec)
接下来通过这个表来介绍各个函数的应用
|IF(value,t,f)|如果是value是真,返回t;如果是假返回f|
高于2000属于高薪用high表示,2000以下属于低薪用low表示
mysql> select userid,salary,if(salary>2000,'high','low') as leave_1 from salary;
+--------+---------+---------+
| userid | salary | leave_1 |
+--------+---------+---------+
| 1 | 1000.00 | low |
| 2 | 2000.00 | low |
| 3 | 3000.00 | high |
| 4 | 4000.00 | high |
| 5 | 5000.00 | high |
| 1 | NULL | low |
+--------+---------+---------+
6 rows in set (0.00 sec)
|IFNULL|如果value1不为空,返回value1,否则返回value2|
这个函数一般用来替换NULL值,因为我们前面有介绍到NULL值是不能参与运算的。
mysql> select userid,salary,ifnull(salary,0) from salary;
+--------+---------+------------------+
| userid | 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 |
| 1 | NULL | 0.00 |
+--------+---------+------------------+
6 rows in set (0.00 sec)
|CASR WHEN[value1] THEN[result] …ELSE [default]END|如果value1是真,返回result1,否则返回default|
还是实现上面高低薪水需求
case后面跟列名或者简单列的表达式,when后面枚举这个表达式所有可能的值,但不能是值的范围。
mysql> select userid,salary,case salary when 1000 then 'low' when 2000 then 'low'else 'high' end leve_1 from salary;
+--------+---------+--------+
| userid | salary | leve_1 |
+--------+---------+--------+
| 1 | 1000.00 | low |
| 2 | 2000.00 | low |
| 3 | 3000.00 | high |
| 4 | 4000.00 | high |
| 5 | 5000.00 | high |
| 1 | NULL | high |
+--------+---------+--------+
6 rows in set (0.00 sec)
|CASE[expr]WHEN[value1]THEN[result1]…ELSE[default]END|如果expr等于value1,返回result1,否则返回default|
这是case的搜索用法,直接再when后面写表达式,并且只返回第一个符合条件的值,使用起来更加灵活
mysql> select userid,salary,case when salary<=2000 then 'low'else 'high' end as leve_1 from salary;
+--------+---------+--------+
| userid | salary | leve_1 |
+--------+---------+--------+
| 1 | 1000.00 | low |
| 2 | 2000.00 | low |
| 3 | 3000.00 | high |
| 4 | 4000.00 | high |
| 5 | 5000.00 | high |
| 1 | NULL | high |
+--------+---------+--------+
6 rows in set (0.00 sec)
六、加密与解密函数
函数 | 功能 |
---|---|
PASSWORD(str) | 返回字符串加密版本,加密结果不可逆,常用于用户密码加密 |
MD5(str) | 返回字符串的md5加密后的值,也是一种加密方式,若参数为null,则会返回null |
SHA(str) | 从明文密码str计算并返回加密后密码字符串,当参数为null时,返回null,SHA加密算法比MD5更加安全 |
ENCODE(value,password_seed) | 返回使用password_seed作为密码加密value |
DECODE(value,password_seed) | 返回使用password_seed作为密码解密value |
MySQL8.0不支持已经被弃用PASSWORD(str)和ENCODE(value,password_seed)DECODE(value,password_seed)
|MD5(str)|返回字符串的md5加密后的值,也是一种加密方式,若参数为null,则会返回null|
|SHA(str)|从明文密码str计算并返回加密后密码字符串,当参数为null时,返回null,SHA加密算法比MD5更加安全|
注意!都是不可逆的!注意!都是不可逆的!注意!都是不可逆的!
mysql> select MD5('zhy'),SHA('ZHY') FROM DUAL;
+----------------------------------+------------------------------------------+
| MD5('zhy') | SHA('ZHY') |
+----------------------------------+------------------------------------------+
| 3faf86140dea5edd5a06cb6715ee97a9 | d5463d6875d48f3393248cddcd2b0b9daadbed32 |
+----------------------------------+------------------------------------------+
1 row in set (0.01 sec)
七、信息函数
函数 | 功能 |
---|---|
VERSION() | 返回MySQL的版本号 |
CONNECTION_ID() | 返回MySQL的服务器连接数 |
DATABASE(),SCHEMA() | 返回MySQL命令行所在当前数据库 |
USER(),CURRENT_USER(),SYSTEM_USER(),SESSION_USER() | 返回连接MySQL的用户名 |
CHARSET(value) | 返回字符串value自变量字符集 |
COLLATION(value) | 返回字符串value的比较规则 |
mysql> select VERSION(),CONNECTION_ID(),DATABASE(),USER(),CHARSET('zhy'),COLLATION('zhy')from dual;
+------------+-----------------+------------+------------------+----------------+------------------+
| VERSION() | CONNECTION_ID() | DATABASE() | USER() | CHARSET('zhy') | COLLATION('zhy') |
+------------+-----------------+------------+------------------+----------------+------------------+
| 5.6.50-log | 62 | jasmyn | jasmyn@localhost | utf8 | utf8_general_ci |
+------------+-----------------+------------+------------------+----------------+------------------+
1 row in set (0.00 sec)
八、其他常用函数
函数 | 功能 |
---|---|
FORMAT(value,n) | 返回对数字value进行格式化后的结果数据。n表示四舍五入后保留到小数点的n位 |
CONV(value,from,to) | 将value的值进行不同进制之间的转换 |
INET_ATON(ipvalue) | 将以点分隔的IP地址转化为一个数字 |
INET_NTOA(value) | 将数字形式的IP地址转化为以点分割的IP地址 |
BENCHMARK(n,expr) | 将表达式expr重复执行n次,用于测试MySQL处理expr表达式的所耗费时间 |
CONVERT(value USING char_code) | 将value所使用的字符编码修改为char_code |
|FORMAT(value,n)|返回对数字value进行格式化后的结果数据。n表示四舍五入后保留到小数点的n位|
mysql> select FORMAT(123.123,2),FORMAT(123.123,0),FORMAT(123.125,-2);
+-------------------+-------------------+--------------------+
| FORMAT(123.123,2) | FORMAT(123.123,0) | FORMAT(123.125,-2) |
+-------------------+-------------------+--------------------+
| 123.12 | 123 | 123 |
+-------------------+-------------------+--------------------+
1 row in set (0.00 sec)
如果N的值小于或等于0,则只保留整数部分
|CONV(value,from,to)|将value的值进行不同进制之间的转换|
mysql> select CONV(16,10,2),CONV(88,10,16),CONV(NULL,10,2);
+---------------+----------------+-----------------+
| CONV(16,10,2) | CONV(88,10,16) | CONV(NULL,10,2) |
+---------------+----------------+-----------------+
| 10000 | 58 | NULL |
+---------------+----------------+-----------------+
1 row in set (0.00 sec)
|INET_ATON(ipvalue)|将以点分隔的IP地址转化为一个数字|
|INET_NTOA(value)|将数字形式的IP地址转化为以点分割的IP地址|
mysql> select INET_ATON('172.168.0.255'),INET_NTOA(2896691455);
+----------------------------+-----------------------+
| INET_ATON('172.168.0.255') | INET_NTOA(2896691455) |
+----------------------------+-----------------------+
| 2896691455 | 172.168.0.255 |
+----------------------------+-----------------------+
1 row in set (0.00 sec)
|BENCHMARK(n,expr)|将表达式expr重复执行n次,用于测试MySQL处理expr表达式的所耗费时间|
mysql> SELECT BENCHMARK(1000000000,'ZHY');
+-----------------------------+
| BENCHMARK(1000000000,'ZHY') |
+-----------------------------+
| 0 |
+-----------------------------+
1 row in set (4.15 sec)
|CONVERT(value USING char_code)|将value所使用的字符编码修改为char_code|
mysql> SELECT CONVERT('ZHY' USING 'UTF8MB4');
+--------------------------------+
| CONVERT('ZHY' USING 'UTF8MB4') |
+--------------------------------+
| ZHY |
+--------------------------------+
1 row in set (0.00 sec)
感谢小江@Maskfanatics赞助测试用服务器