C-07.单行函数1.函数的理解1.1 什么是函数

函数在计算机语言中的使用贯穿始终,函数的作用是什么呢?它可以把我们经常使用的代码封装起来,需要的时候直接调用即可。这样即提高了代码效率,又提高了可维护性。在SQL中我们也可以使用函数对检索出的数据进行函数操作。使用这函数,可以极大地提高用户对数据库的管理效率

从函数的定义角度出发,我们可以将函数分成内置函数自定义函数。在SQL语言中,同样也包括了内置函数的自定义函数。内置函数是系统内置的通用函数,而自定义函数是我们根据自己的需要编写的,本章和下一章讲解的是SQL的内置函数。

1.2 不同DBMS的函数的差异

我们在使用SQL语言时,是和各种不同的DBMS打交道。DBMS之间的差异性很大,远大于同一个语言不同版本之间的差异。实际上,只是很少的函数是被DBMS同时支持的。比如,大多数DBMS使用(||)或(+)来做拼接符,而在MySQL中的字符串拼接函数为concat()。大部分DBMS会有自己特定的函数,这就意味着采用SQL函数的代码可移植性是很差的,因此在使用函数的时候需要特别注意。

1.3 MySQL的内置函数分类

MySQL提供了丰富的内置函数,这些函数使得数据的维护与管理更加方便,能够更好的提供数据的分析和统计能力,在一定程度上提高了开发人员进行数据分析与统计的效率。
MySQL提供的内置函数从实现的功能角度可以分为数值函数,字符串函数,日期与时间函数,流程控制函数,加密与解密函数,获取MySQL信息函数,聚合函数等。也可以分为单行函数,聚合函数(或分组函数)。

两种SQL函数

单行函数

  • 操作数据对象
  • 接收参数返回一个结果
  • 只对一行进行变换
  • 每行返回一个结果
  • 可以嵌套
  • 参数可以一列或一个值

2.数值函数2.1 基本函数


举例:

SELECT ABS(-10.1),SIGN(-23),SIGN(20),PI(),CEIL(-41.2),CEILING(20.8),FLOOR(20.8),FLOOR(-41.2),MOD(12,5)FROM dual;

SELECT RAND(),RAND(),RAND(10),RAND(10),RAND(-2),RAND(-2) FROM dual;

mysql> SELECT ROUND(10.5),ROUND(10.224,2),ROUND(13.325,-1),TRUNCATE(10.66,1),TRUNCATE(12.66,-1) FROM dual;+-------------+-----------------+------------------+-------------------+--------------------+| ROUND(10.5) | ROUND(10.224,2) | ROUND(13.325,-1) | TRUNCATE(10.66,1) | TRUNCATE(12.66,-1) |+-------------+-----------------+------------------+-------------------+--------------------+|          11 |           10.22 |               10 |              10.6 |                 10 |+-------------+-----------------+------------------+-------------------+--------------------+1 row in set (0.00 sec)

2.2 角度与弧度互换函数

mysql> SELECT RADIANS(30),RADIANS(60),RADIANS(90),DEGREES(2*PI()),DEGREES(RADIANS(90)) FROM dual;+--------------------+--------------------+--------------------+-----------------+----------------------+| RADIANS(30)        | RADIANS(60)        | RADIANS(90)        | DEGREES(2*PI()) | DEGREES(RADIANS(90)) |+--------------------+--------------------+--------------------+-----------------+----------------------+| 0.5235987755982988 | 1.0471975511965976 | 1.5707963267948966 |             360 |                   90 |+--------------------+--------------------+--------------------+-----------------+----------------------+1 row in set (0.00 sec)

2.3 三角函数


举例:
ATAN2(m,n)函数返回两个参数的反正切值。与ATAN(x)函数相比,ATAN2(m,n)需要两个参数。例如有两个点point(x1,y1)和point(x2,y2),使用ATAN(x)函数计算反正切值为ATAN((y2-y1)/(x2-x1)),使用ATAN2(m,n)计算反正切值为ATAN2(x2-x1,y2-y1)。

注意分母不能为0的情况。

mysql> SELECT  SIN(RADIANS(30)),DEGREES(ASIN(1)),TAN(RADIANS(45)),DEGREES(ATAN(1)),DEGREES(ATAN2(1,1))    -> FROM dual;+---------------------+------------------+--------------------+------------------+---------------------+| SIN(RADIANS(30))    | DEGREES(ASIN(1)) | TAN(RADIANS(45))   | DEGREES(ATAN(1)) | DEGREES(ATAN2(1,1)) |+---------------------+------------------+--------------------+------------------+---------------------+| 0.49999999999999994 |               90 | 0.9999999999999999 |               45 |                  45 |+---------------------+------------------+--------------------+------------------+---------------------+1 row in set (0.00 sec)

2.4 指数与对数

mysql> SELECT POW(2,3),POWER(2,3),EXP(3),LN(EXP(2)),LOG10(100),LOG2(64) FROM dual;+----------+------------+--------------------+------------+------------+----------+| POW(2,3) | POWER(2,3) | EXP(3)             | LN(EXP(2)) | LOG10(100) | LOG2(64) |+----------+------------+--------------------+------------+------------+----------+|        8 |          8 | 20.085536923187668 |          2 |          2 |        6 |+----------+------------+--------------------+------------+------------+----------+1 row in set (0.00 sec)

2.5 进制间的转换

mysql> SELECT BIN(12),HEX(12),OCT(12),CONV(10,2,8) FROM dual;+---------+---------+---------+--------------+| BIN(12) | HEX(12) | OCT(12) | CONV(10,2,8) |+---------+---------+---------+--------------+| 1100    | C       | 14      | 2            |+---------+---------+---------+--------------+1 row in set (0.00 sec)

3.字符串函数




举例

mysql> SELECT FIELD('mm','hello','mm','asmr'),FIND_IN_SET('mm','hello,mm,asmr')    -> FROM dual;+---------------------------------+-----------------------------------+| FIELD('mm','hello','mm','asmr') | FIND_IN_SET('mm','hello,mm,asmr') |+---------------------------------+-----------------------------------+|                               2 |                                 2 |+---------------------------------+-----------------------------------+1 row in set (0.00 sec)mysql> SELECT REVERSE('asmr'),NULLIF('mysql','mysql'),NULLIF('mysql','mysqld')    -> FROM dual;+-----------------+-------------------------+--------------------------+| REVERSE('asmr') | NULLIF('mysql','mysql') | NULLIF('mysql','mysqld') |+-----------------+-------------------------+--------------------------+| rmsa            | NULL                    | mysql                    |+-----------------+-------------------------+--------------------------+1 row in set (0.00 sec)

4.日期和时间函数4.1 获取日期,时间


举例:

4.2 日期与时间戳的转换

mysql> SELECT UNIX_TIMESTAMP(),FROM_UNIXTIME(now()),FROM_UNIXTIME(UNIX_TIMESTAMP()) FROM dual;+------------------+----------------------+---------------------------------+| UNIX_TIMESTAMP() | FROM_UNIXTIME(now()) | FROM_UNIXTIME(UNIX_TIMESTAMP()) |+------------------+----------------------+---------------------------------+|       1702562470 | NULL                 | 2023-12-14 22:01:10             |+------------------+----------------------+---------------------------------+1 row in set (0.00 sec)

4.3 获取月份,天数,星期数等函数

mysql> SELECT YEAR(CURDATE()),MONTH(CURDATE()),DAY(CURDATE()),HOUR(CURTIME()),MINUTE(NOW()),SECOND(SYSDATE()) FROM dual;+-----------------+------------------+----------------+-----------------+---------------+-------------------+| YEAR(CURDATE()) | MONTH(CURDATE()) | DAY(CURDATE()) | HOUR(CURTIME()) | MINUTE(NOW()) | SECOND(SYSDATE()) |+-----------------+------------------+----------------+-----------------+---------------+-------------------+|            2023 |               12 |             14 |              22 |             5 |                 8 |+-----------------+------------------+----------------+-----------------+---------------+-------------------+1 row in set (0.00 sec)mysql> SELECT MONTHNAME(now()),DAYNAME(now()),WEEKDAY(CURDATE()),QUARTER(SYSDATE()),WEEK(now()),DAYOFYEAR(now()),DAYOFMONTH(now()),DAYOFWEEK(now()) FROM dual;+------------------+----------------+--------------------+--------------------+-------------+------------------+-------------------+------------------+| MONTHNAME(now()) | DAYNAME(now()) | WEEKDAY(CURDATE()) | QUARTER(SYSDATE()) | WEEK(now()) | DAYOFYEAR(now()) | DAYOFMONTH(now()) | DAYOFWEEK(now()) |+------------------+----------------+--------------------+--------------------+-------------+------------------+-------------------+------------------+| December         | Thursday       |                  3 |                  4 |          50 |              348 |                14 |                5 |+------------------+----------------+--------------------+--------------------+-------------+------------------+-------------------+------------------+1 row in set (0.00 sec)

4.4 日期的操作函数

mysql> SELECT EXTRACT(MINUTE FROM now()),EXTRACT(WEEK FROM NOW()),EXTRACT(QUARTER FROM NOW()),EXTRACT(MINUTE_SECOND FROM now()) FROM dual;+----------------------------+--------------------------+-----------------------------+-----------------------------------+| EXTRACT(MINUTE FROM now()) | EXTRACT(WEEK FROM NOW()) | EXTRACT(QUARTER FROM NOW()) | EXTRACT(MINUTE_SECOND FROM now()) |+----------------------------+--------------------------+-----------------------------+-----------------------------------+|                         13 |                       50 |                           4 |                              1311 |+----------------------------+--------------------------+-----------------------------+-----------------------------------+1 row in set (0.00 sec)

4.5 时间和秒钟转换的函数

mysql> SELECT TIME_TO_SEC(NOW());+--------------------+| TIME_TO_SEC(NOW()) |+--------------------+|              85290 |+--------------------+1 row in set (0.00 sec)mysql> SELECT SEC_TO_TIME(85290);+--------------------+| SEC_TO_TIME(85290) |+--------------------+| 23:41:30           |+--------------------+1 row in set (0.00 sec)

4.6 计算日期和时间的函数第一组

# now() 也可以是代表日期的常量,如'2023-12-14 23:46:20'mysql> SELECT DATE_ADD(NOW(),INTERVAL 1 DAY) AS col1,ADDDATE(NOW(),INTERVAL 1 SECOND) AS col2 FROM dual;+---------------------+---------------------+| col1                | col2                |+---------------------+---------------------+| 2023-12-15 23:46:38 | 2023-12-14 23:46:39 |+---------------------+---------------------+1 row in set (0.00 sec) mysql> SELECT DATE_SUB(NOW(),INTERVAL 1 DAY) AS col1,SUBDATE(NOW(),INTERVAL 1 SECOND) AS col2 FROM dual;+---------------------+---------------------+| col1                | col2                |+---------------------+---------------------+| 2023-12-13 23:47:14 | 2023-12-14 23:47:13 |+---------------------+---------------------+1 row in set (0.00 sec)

第二组

mysql> SELECT ADDTIME(NOW(),20),SUBTIME(NOW(),30),SUBTIME(NOW(),'1:1:3') FROM dual;+---------------------+---------------------+------------------------+| ADDTIME(NOW(),20)   | SUBTIME(NOW(),30)   | SUBTIME(NOW(),'1:1:3') |+---------------------+---------------------+------------------------+| 2023-12-14 23:52:38 | 2023-12-14 23:51:48 | 2023-12-14 22:51:15    |+---------------------+---------------------+------------------------+1 row in set (0.00 sec)mysql> SELECT DATEDIFF(NOW(),'2022-10-01'),TIMEDIFF(NOW(),'2023-10-01 00:00:00'),FROM_DAYS(366) FROM dual;+------------------------------+---------------------------------------+----------------+| DATEDIFF(NOW(),'2022-10-01') | TIMEDIFF(NOW(),'2023-10-01 00:00:00') | FROM_DAYS(366) |+------------------------------+---------------------------------------+----------------+|                          439 | 838:59:59                             | 0001-01-01     |+------------------------------+---------------------------------------+----------------+1 row in set, 1 warning (0.00 sec)mysql> SELECT TO_DAYS('0000-12-14'),LAST_DAY(NOW()),MAKEDATE(YEAR(NOW()),122) FROM dual;+-----------------------+-----------------+---------------------------+| TO_DAYS('0000-12-14') | LAST_DAY(NOW()) | MAKEDATE(YEAR(NOW()),122) |+-----------------------+-----------------+---------------------------+|                   348 | 2023-12-31      | 2023-05-02                |+-----------------------+-----------------+---------------------------+1 row in set (0.00 sec)mysql> SELECT PERIOD_ADD(202011,10),MAKETIME(10,25,56) FROM dual;+-----------------------+--------------------+| PERIOD_ADD(202011,10) | MAKETIME(10,25,56) |+-----------------------+--------------------+|                202109 | 10:25:56           |+-----------------------+--------------------+1 row in set (0.00 sec)

注意,图中关于PERIOD_AD输入不同的参数可能有问题,不推荐使用

查询7天内新增用户有多少?

SELECT COUNT(*) AS num FROM user WHERE TO_DAYS(NOW()) - TO_DAYS(regist_time) <= 7;

4.7 日期的格式化与解析

非GET_FORMAT函数中fmt参数常用的格式符


GET_FORMAT函数参数的格式符

mysql> SELECT TIME_FORMAT(CURTIME(),'%H:%i:%s'),DATE_FORMAT(CURDATE(),'%Y-%m-%d') FROM dual;+-----------------------------------+-----------------------------------+| TIME_FORMAT(CURTIME(),'%H:%i:%s') | DATE_FORMAT(CURDATE(),'%Y-%m-%d') |+-----------------------------------+-----------------------------------+| 23:02:36                          | 2023-12-15                        |+-----------------------------------+-----------------------------------+1 row in set (0.00 sec)mysql> SELECT GET_FORMAT(DATE,'USA'),GET_FORMAT(TIME,'USA') FROM dual;+------------------------+------------------------+| GET_FORMAT(DATE,'USA') | GET_FORMAT(TIME,'USA') |+------------------------+------------------------+| %m.%d.%Y               | %h:%i:%s %p            |+------------------------+------------------------+1 row in set (0.00 sec)mysql> SELECT STR_TO_DATE('2021-01-05 00:00:00','%Y-%m-%d') FROM dual;+-----------------------------------------------+| STR_TO_DATE('2021-01-05 00:00:00','%Y-%m-%d') |+-----------------------------------------------+| 2021-01-05                                    |+-----------------------------------------------+1 row in set, 1 warning (0.00 sec)mysql> SELECT STR_TO_DATE('2021-01-05 00:00:00','%Y-%m-%d %H:%i:%s') FROM dual;+--------------------------------------------------------+| STR_TO_DATE('2021-01-05 00:00:00','%Y-%m-%d %H:%i:%s') |+--------------------------------------------------------+| 2021-01-05 00:00:00                                    |+--------------------------------------------------------+1 row in set (0.00 sec

注意,没有STR_TO_TIME函数。

5.流程控制函数

流程控制函数可以根据不同的条件,执行不同的处理流程,可以在SQL语句中,实现不同的条件选择。MySQL中的流程处理函数主要包括IF(),IFNULL()和CASE()函数。

mysql> SELECT IF(1 > 0,'正确','错误'),IFNULL(null,'Hello,World') FROM dual;+-------------------------+----------------------------+| IF(1 > 0,'正确','错误') | IFNULL(null,'Hello,World') |+-------------------------+----------------------------+| 正确                    | Hello,World                |+-------------------------+----------------------------+1 row in set (0.00 sec)mysql> SELECT CASE WHEN 1 > 0 THEN '第一个成立' WHEN 2 > 0 THEN '第二个成立' ELSE '前两个都不成立' END    -> ;+------------------------------------------------------------------------------------------+| CASE WHEN 1 > 0 THEN '第一个成立' WHEN 2 > 0 THEN '第二个成立' ELSE '前两个都不成立' END |+------------------------------------------------------------------------------------------+| 第一个成立                                                                               |+------------------------------------------------------------------------------------------+1 row in set (0.00 sec)mysql> SELECT CASE 1    -> WHEN 1 THEN '值是1'    -> WHEN 2 THEN '值是2'    -> ELSE '值是N'    -> END;+-----------------------------------------------------------------+| CASE 1WHEN 1 THEN '值是1'WHEN 2 THEN '值是2'ELSE '值是N'END |+-----------------------------------------------------------------+| 值是1                                                           |+-----------------------------------------------------------------+1 row in set (0.00 sec)mysql> SELECT last_name,salary,department_id,CASE department_id WHEN 10 THEN 1.1 * salary    -> WHEN 20 THEN (1.2 * salary) WHEN 30 THEN (1.3 * salary) ELSE salary END '薪水'    -> FROM employees;+-------------+----------+---------------+----------+| last_name   | salary   | department_id | 薪水     |+-------------+----------+---------------+----------+| King        | 24000.00 |            90 | 24000.00 || Kochhar     | 17000.00 |            90 | 17000.00 || De Haan     | 17000.00 |            90 | 17000.00 |...| Mavris      |  6500.00 |            40 |  6500.00 || Baer        | 10000.00 |            70 | 10000.00 || Higgins     | 12000.00 |           110 | 12000.00 || Gietz       |  8300.00 |           110 |  8300.00 |+-------------+----------+---------------+----------+107 rows in set (0.00 sec)

6.加密与解密函数

加密与解密函数主要用于对数据库中的数据进行加密和解密处理,以防止数据被他人窃取,这些函数在保证数据库安全时非常有用。

mysql> SELECT PASSWORD('wind');+-------------------------------------------+| PASSWORD('wind')                          |+-------------------------------------------+| *E04A13B4BCD1DCBE73B56F656D27615E2C2BFE55 |+-------------------------------------------+1 row in set, 1 warning (0.00 sec)

注意password函数在MySQL8.0后已经取消支持了。上述时在5.7版本下执行的。

mysql> SELECT PASSWORD('wind');ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '('wind')' at line 1

8.0环境下的执行会报错。

mysql> SELECT MD5('wind');+----------------------------------+| MD5('wind')                      |+----------------------------------+| 7e25b972e192b01004b62346ee9975a5 |+----------------------------------+1 row in set (0.00 sec)mysql> SELECT SHA('WIND');+------------------------------------------+| SHA('WIND')                              |+------------------------------------------+| c514a747e8f0bc9e5d9beba8fe62416cc7b2a66e |+------------------------------------------+1 row in set (0.00 sec)

MD5()和SHA(),5.7和8.0都支持。
ENCODE()和DECODE()也都是在8.0中不支持。

7.MySQL信息函数

MySQL内置了一些可以查询MySQL信息的函数,这些函数主要用于帮助数据库开发或运维人员更好地维护数据库。

mysql> SELECT DATABASE();+------------+| DATABASE() |+------------+| atguigudb  |+------------+1 row in set (0.00 sec)mysql> SELECT USER(),CURRENT_USER(),SYSTEM_USER(),SESSION_USER();+----------------+----------------+----------------+----------------+| USER()         | CURRENT_USER() | SYSTEM_USER()  | SESSION_USER() |+----------------+----------------+----------------+----------------+| root@localhost | root@localhost | root@localhost | root@localhost |+----------------+----------------+----------------+----------------+1 row in set (0.00 sec)mysql> SELECT CHARSET('abc'),COLLATION('abc');+----------------+------------------+| CHARSET('abc') | COLLATION('abc') |+----------------+------------------+| gbk            | gbk_chinese_ci   |+----------------+------------------+1 row in set (0.00 sec)

8.其他函数

MySQL有些函数,无法进行具体的分类,但是使用较为频繁。

# 如果n的值小于或等于0,只保留整数mysql> SELECT FORMAT(123.12,2),FORMAT(123.354,2),FORMAT(12.546,-1);+------------------+-------------------+-------------------+| FORMAT(123.12,2) | FORMAT(123.354,2) | FORMAT(12.546,-1) |+------------------+-------------------+-------------------+| 123.12           | 123.35            | 13                |+------------------+-------------------+-------------------+1 row in set (0.00 sec)mysql> SELECT CONV(10,10,2),CONV(10,10,16),CONV(NULL,10,2);+---------------+----------------+-----------------+| CONV(10,10,2) | CONV(10,10,16) | CONV(NULL,10,2) |+---------------+----------------+-----------------+| 1010          | A              | NULL            |+---------------+----------------+-----------------+1 row in set (0.00 sec)#计算公式192 * 256^3 + 168 * 256^2 + 0 * 256 + 130 = 3232235650mysql> SELECT INET_ATON('192.168.0.130');+----------------------------+| INET_ATON('192.168.0.130') |+----------------------------+|                 3232235650 |+----------------------------+1 row in set (0.00 sec)mysql> SELECT INET_NTOA(3232235650);+-----------------------+| INET_NTOA(3232235650) |+-----------------------+| 192.168.0.130         |+-----------------------+1 row in set (0.00 sec)mysql> SELECT BENCHMARK(10000,MD5('WIND'));+------------------------------+| BENCHMARK(10000,MD5('WIND')) |+------------------------------+|                            0 |+------------------------------+1 row in set (0.00 sec)mysql> SELECT CHARSET('mysql'),CHARSET(CONVERT('mysql' USING 'utf8'));+------------------+----------------------------------------+| CHARSET('mysql') | CHARSET(CONVERT('mysql' USING 'utf8')) |+------------------+----------------------------------------+| gbk              | utf8mb3                                |+------------------+----------------------------------------+1 row in set, 1 warning (0.00 sec)

练习使用的表结构

# Chapter07-- 1.显示系统时间SELECT NOW() FROM DUAL;-- 2.查询员工号,姓名,工资,以及工资提高20%后的结果SELECT employee_id,last_name,salary,1.2 * salary AS new_salary FROM employees;-- 3.将员工的姓名按首字母排序,并写出姓名的长度SELECT last_name,LENGTH(last_name) AS 'name_length'FROM employeesORDER BY last_name;-- 4.查询员工id,last_name,salary,并作为一个列输出,别名为OUT_PUTSELECT CONCAT(employee_id,,'',last_name,',',salary) AS 'OUT_PUt'FROM employees; DESC employees;-- hire_date记录的是各个员工入职的时间-- 年限 = year(now()) - year(date)-- 工作天数 = to_days(year) - TO_days(hire_date)-- 5.查询公司各员工工作的年限,工作的天数,并按工作年数的降序排序SELECT    last_name,    hire_date,    (TO_DAYS(NOW()) - TO_DAYS(hire_date)) / 365 AS 'work_years',    TO_DAYS(NOW()) - TO_DAYS(hire_date) AS 'word_days'FROM    employeesORDER BY work_years DESC;-- 6.查询员工姓名,hire_date,department_id,满足以下条件,入职时间在1997年之后,department_id为80-- or 90 or 110,commission_pct不为空SELECT hire_date,department_id,commission_pctFROM employeesWHERE YEAR(hire_date) >= 1997 AND department_id IN (80,90,110) AND NOT ISNULL(commission_pct);-- 7.查询公司中入职超过10000天的员工姓名,入职时间SELECT hire_date,last_name,TO_DAYS(NOW()) - TO_DAYS(hire_date) AS 'work_days'FROM employees WHERE TO_DAYS(NOW()) - TO_DAYS(hire_date) > 10000;-- 8.做一个查询,产生下面的结果-- Kingearns24000.00monthly but wants72000 别名是DREAM SALARYSELECT CONCAT(last_name,'earns',salary,'monthly but wants',FLOOR(salary * 3)) AS 'DREAM SALARY'FROM employees;DESC employees;-- 9.使用case-when ,按照下面条件-- job grade AD_PRES A ST_MAN B IT_PROG C SA_REP D ST_CLERK ESELECT     last_name,    job_id,    CASE job_id         WHEN 'AD_PRES' THEN 'A'        WHEN 'ST_MAN' THEN 'B'        WHEN 'IT_PROG' THEN 'C'        WHEN 'SA_REP' THEN 'D'        WHEN 'ST_CLERK' THEN 'E'        ELSE '其他'    END 'Grade'FROM employees;

只是为了记录自己的学习历程,且本人水平有限,不对之处,请指正。