4、（常练习）单行函数（包含数值函数、字符串函数、日期和时间函数） -mysql

单行函数

• 单行函数
• • 一、函数的理解
  • 二、数值函数
  • • • 1、基本函数
      • 2、角度与弧度互换函数
      • 3、三角函数
      • 4、指数与对数
      • 5、进制间的转换
  • 三、字符串函数
  • 四、日期和时间函数
  • • • 1、获取日期、时间
      • 2、日期与时间戳的转换
      • 3、获取月份、星期、星期数、天数等函数
      • 4、日期的操作函数
      • 5、时间和秒钟转换的函数
      • 6、计算日期和时间的函数
      • 7、日期的格式化与解析
  • 五、流程控制函数
  • 六、加密与解密函数
  • 七. MySQL信息函数
  • 八、其他函数
  • 九、Mysql的truncate、format、convert和round保留小数（四舍五入）的区别
  • 十、课后练习

单行函数

————————————————————————————————————————————————————

一、函数的理解

可以把我们经常使用的代码 封装 起来，需要的时候直接调用即可。

这样既 提高了代码效率 ，又 提高了可维护性 。

在 SQL 中我们也可以使用函数对检索出来的数据进行函数操作。使用这些函数，可以极大地 提高用户对数据库的管理效率 。

从函数定义的角度出发，我们可以将函数分成 内置函数 和 自定义函数 。

• MySQL的内置函数及分类

  • 单行函数
  • 聚合函数（或分组函数）

二、数值函数

1、基本函数

mysql> SELECT ABS(-123),ABS(32),SIGN(-23),SIGN(43),PI(),CEIL(32.32),CEILING(-43.23),FLOOR(32.32),
    ->  FLOOR(-43.23),MOD(12,5),12 MOD 5, 12 % 5
    ->  FROM DUAL;
+-----------+---------+-----------+----------+----------+-------------+-----------------+--------------+---------------+-----------+----------+--------+
| ABS(-123) | ABS(32) | SIGN(-23) | SIGN(43) | PI()     | CEIL(32.32) | CEILING(-43.23) | FLOOR(32.32) | FLOOR(-43.23) | MOD(12,5) | 12 MOD 5 | 12 % 5 |
+-----------+---------+-----------+----------+----------+-------------+-----------------+--------------+---------------+-----------+----------+--------+
|       123 |      32 |        -1 |        1 | 3.141593 |          33 |             -43 |           32 |           -44 |         2 |        2 |      2 |
+-----------+---------+-----------+----------+----------+-------------+-----------------+--------------+---------------+-----------+----------+--------+
1 row in set (0.00 sec)
# RAND() 不带参数的随机数会一直变化 
mysql> SELECT RAND(),RAND(),RAND(10),RAND(10), RAND(-1),RAND(-1)
    -> FROM DUAL;
+--------------------+--------------------+--------------------+--------------------+--------------------+--------------------+
| RAND()             | RAND()             | RAND(10)           | RAND(10)           | RAND(-1)           | RAND(-1)           |
+--------------------+--------------------+--------------------+--------------------+--------------------+--------------------+
| 0.7666654240029542 | 0.4990492272181895 | 0.6570515219653505 | 0.6570515219653505 | 0.9050373219931845 | 0.9050373219931845 |
+--------------------+--------------------+--------------------+--------------------+--------------------+--------------------+
1 row in set (0.00 sec)

• round 四舍五入

  mysql> select round(123.556),round(123.456,0),round(123.456, 1),round(123.456,2),round(123.456,-1),round(123.456, -2)
      -> from dual;
  +----------------+------------------+-------------------+------------------+-------------------+--------------------+
  | round(123.556) | round(123.456,0) | round(123.456, 1) | round(123.456,2) | round(123.456,-1) | round(123.456, -2) |
  +----------------+------------------+-------------------+------------------+-------------------+--------------------+
  |            124 |              123 |             123.5 |           123.46 |               120 |                100 |
  +----------------+------------------+-------------------+------------------+-------------------+--------------------+
  1 row in set (1.65 sec)

• truncate 截断

  mysql> select truncate(123.456, 0),truncate(123.496, 1),truncate(129.45, -1) from dual;
  +----------------------+----------------------+----------------------+
  | truncate(123.456, 0) | truncate(123.496, 1) | truncate(129.45, -1) |
  +----------------------+----------------------+----------------------+
  |                  123 |                123.4 |                  120 |
  +----------------------+----------------------+----------------------+

• 单行函数可以嵌套

  mysql> select truncate(round(123.456, 2), 0) from dual;
  +--------------------------------+
  | truncate(round(123.456, 2), 0) |
  +--------------------------------+
  |                            123 |
  +--------------------------------+

2、角度与弧度互换函数

3、三角函数

4、指数与对数

5、进制间的转换

mysql> SELECT BIN(10),HEX(10),OCT(10),CONV(10,2,8)
    -> FROM DUAL;
+---------+---------+---------+--------------+
| BIN(10) | HEX(10) | OCT(10) | CONV(10,2,8) |
+---------+---------+---------+--------------+
| 1010    | A       | 12      | 2            |
+---------+---------+---------+--------------+
1 row in set (0.00 sec)

三、字符串函数

• insert / replace 替换

  mysql> select insert(‘helloworld’, 2, 3, ‘aaaaa’),replace(‘hello’,’ll’,’mmm’) from dual;
  +——————————————————-+——————————————-+
  | insert(‘helloworld’, 2, 3, ‘aaaaa’) | replace(‘hello’,’ll’,’mmm’) |
  +——————————————————-+——————————————-+
  | haaaaaoworld | hemmmo |
  +——————————————————-+——————————————-+

注意：MySQL中，字符串的位置是从1开始的。

四、日期和时间函数

1、获取日期、时间

2、日期与时间戳的转换

3、获取月份、星期、星期数、天数等函数

4、日期的操作函数

SELECT EXTRACT(MINUTE FROM NOW()),EXTRACT( WEEK FROM NOW()),
EXTRACT( QUARTER FROM NOW()),EXTRACT( MINUTE_SECOND FROM NOW())
FROM DUAL;

5、时间和秒钟转换的函数

mysql> SELECT TIME_TO_SEC(NOW());
+--------------------+
| TIME_TO_SEC(NOW()) |
+--------------------+
|               78774 |
+--------------------+
1 row in set (0.00 sec)
#----------------
mysql> SELECT SEC_TO_TIME(78774);
+--------------------+
| SEC_TO_TIME(78774) |
+--------------------+
| 21:52:54            |
+--------------------+
1 row in set (0.12 sec)

6、计算日期和时间的函数

上述函数中type的取值：

SELECT DATE_ADD(NOW(), INTERVAL 1 DAY) AS col1,DATE_ADD('2021-10-21 23:32:12',INTERVAL 1 SECOND) AS col2,
ADDDATE('2021-10-21 23:32:12',INTERVAL 1 SECOND) AS col3,
DATE_ADD('2021-10-21 23:32:12',INTERVAL '1_1' MINUTE_SECOND) AS col4,
DATE_ADD(NOW(), INTERVAL -1 YEAR) AS col5, #可以是负数
DATE_ADD(NOW(), INTERVAL '1_1' YEAR_MONTH) AS col6 #需要单引号
FROM DUAL;
SELECT DATE_SUB('2021-01-21',INTERVAL 31 DAY) AS col1,
SUBDATE('2021-01-21',INTERVAL 31 DAY) AS col2,
DATE_SUB('2021-01-21 02:01:01',INTERVAL '1 1' DAY_HOUR) AS col3
FROM DUAL;

#查询 7 天内的新增用户数有多少？
SELECT COUNT(*) as num FROM new_user WHERE TO_DAYS(NOW())-TO_DAYS(regist_time)<=7

7、日期的格式化与解析

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

GET_FORMAT函数中date_type和format_type参数取值如下：

mysql> SELECT DATE_FORMAT(NOW(), '%H:%i:%s');
+--------------------------------+
| DATE_FORMAT(NOW(), '%H:%i:%s') |
+--------------------------------+
| 22:57:34                        |
+--------------------------------+
1 row in set (0.00 sec)
SELECT STR_TO_DATE('09/01/2009','%m/%d/%Y')
FROM DUAL;
SELECT STR_TO_DATE('20140422154706','%Y%m%d%H%i%s')
FROM DUAL;
SELECT STR_TO_DATE('2014-04-22 15:47:06','%Y-%m-%d %H:%i:%s')
FROM DUAL;
mysql> SELECT GET_FORMAT(DATE, 'USA');
+-------------------------+
| GET_FORMAT(DATE, 'USA') |
+-------------------------+
| %m.%d.%Y                |
+-------------------------+
1 row in set (0.00 sec)
SELECT DATE_FORMAT(NOW(),GET_FORMAT(DATE,'USA')),
FROM DUAL;
mysql> SELECT STR_TO_DATE('2020-01-01 00:00:00','%Y-%m-%d'); 
+-----------------------------------------------+
| STR_TO_DATE('2020-01-01 00:00:00','%Y-%m-%d') |
+-----------------------------------------------+
| 2020-01-01                                    |
+-----------------------------------------------+
1 row in set, 1 warning (0.00 sec)

五、流程控制函数

MySQL中的流程处理函数主要包括IF()、IFNULL()和CASE()函数。

SELECT IF(1 > 0,'正确','错误')    
->正确
SELECT IFNULL(null,'Hello Word')
->Hello Word
SELECT CASE 
　　WHEN 1 > 0
　　THEN '1 > 0'
　　WHEN 2 > 0
　　THEN '2 > 0'
　　ELSE '3 > 0'
　　END
->1 > 0
SELECT CASE 1 
　　WHEN 1 THEN '我是1'
　　WHEN 2 THEN '我是2'
ELSE '你是谁'
SELECT employee_id,salary, CASE WHEN salary>=15000 THEN '高薪' 
                  WHEN salary>=10000 THEN '潜力股'  
                  WHEN salary>=8000 THEN '屌丝' 
                  ELSE '草根' END  "描述"
FROM employees;
SELECT oid,`status`, CASE `status` WHEN 1 THEN '未付款' 
                                   WHEN 2 THEN '已付款' 
                                   WHEN 3 THEN '已发货'  
                                   WHEN 4 THEN '确认收货'  
                                   ELSE '无效订单' END 
FROM t_order;
mysql> SELECT CASE WHEN 1 > 0 THEN 'yes' WHEN 1 <= 0 THEN 'no' ELSE 'unknown' END;
+---------------------------------------------------------------------+
| CASE WHEN 1 > 0 THEN 'yes' WHEN 1 <= 0 THEN 'no' ELSE 'unknown' END |
+---------------------------------------------------------------------+
| yes                                                                  |
+---------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT CASE WHEN 1 < 0 THEN 'yes' WHEN 1 = 0 THEN 'no' ELSE 'unknown' END;  
+--------------------------------------------------------------------+
| CASE WHEN 1 < 0 THEN 'yes' WHEN 1 = 0 THEN 'no' ELSE 'unknown' END |
+--------------------------------------------------------------------+
| unknown                                                             |
+--------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT CASE 1 WHEN 0 THEN 0 WHEN 1 THEN 1 ELSE -1 END;
+------------------------------------------------+
| CASE 1 WHEN 0 THEN 0 WHEN 1 THEN 1 ELSE -1 END |
+------------------------------------------------+
|                                               1 |
+------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT CASE -1 WHEN 0 THEN 0 WHEN 1 THEN 1 ELSE -1 END;
+-------------------------------------------------+
| CASE -1 WHEN 0 THEN 0 WHEN 1 THEN 1 ELSE -1 END |
+-------------------------------------------------+
|                                               -1 |
+-------------------------------------------------+
1 row in set (0.00 sec)
SELECT employee_id,12 * salary * (1 + IFNULL(commission_pct,0))
FROM employees;
SELECT last_name, job_id, salary,
       CASE job_id WHEN 'IT_PROG'  THEN  1.10*salary
                   WHEN 'ST_CLERK' THEN  1.15*salary
                   WHEN 'SA_REP'   THEN  1.20*salary
                      ELSE  salary END "REVISED_SALARY"
FROM   employees;

六、加密与解密函数

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

可以看到，ENCODE(value,password_seed)函数与DECODE(value,password_seed)函数互为反函数。

举例：

mysql> SELECT PASSWORD('mysql'), PASSWORD(NULL);
+-------------------------------------------+----------------+
| PASSWORD('mysql')                         | PASSWORD(NULL) |
+-------------------------------------------+----------------+
| *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA |                |
+-------------------------------------------+----------------+
1 row in set, 1 warning (0.00 sec)
SELECT md5('123')
->202cb962ac59075b964b07152d234b70
SELECT SHA('Tom123')
->c7c506980abc31cc390a2438c90861d0f1216d50
mysql> SELECT ENCODE('mysql', 'mysql');
+--------------------------+
| ENCODE('mysql', 'mysql') |
+--------------------------+
| íg　¼　ìÉ                  |
+--------------------------+
1 row in set, 1 warning (0.01 sec)
mysql> SELECT DECODE(ENCODE('mysql','mysql'),'mysql');
+-----------------------------------------+
| DECODE(ENCODE('mysql','mysql'),'mysql') |
+-----------------------------------------+
| mysql                                   |
+-----------------------------------------+
1 row in set, 2 warnings (0.00 sec)

七. MySQL信息函数

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

八、其他函数

MySQL中有些函数无法对其进行具体的分类，但是这些函数在MySQL的开发和运维过程中也是不容忽视的。

九、Mysql的truncate、format、convert和round保留小数（四舍五入）的区别

参考地址：https://blog.csdn.net/qq_37493556/article/details/98079305

十、课后练习

# 1、显示系统时间（注：日期 + 时间）
SELECT NOW(),SYSDATE(),CURRENT_TIME(),LOCALTIME(),LOCALTIMESTAMP() FROM DUAL;
# 2、查询员工号，姓名，工资以及工资提高20%后的结果（new salary）
SELECT employee_id,last_name,salary,salary * (1+0.2) AS "new salary" FROM employees;
# 3、将员工的姓名按首字母排序，并写出姓名的长度（length）
SELECT last_name,LENGTH(last_name) AS "name_length" FROM employees 
# order by last_name ASC;
ORDER BY name_length ASC;
# 4、查询员工 id,last_name,salary 并作为一个列输出，别名为 out_put
SELECT CONCAT(employee_id,'-',last_name,'-',salary) AS "out_put"
FROM employees;
# 5、查询公司各员工工作的年数、工作的天数、并按工作年数的降序排序
SELECT employee_id,DATEDIFF(CURDATE(),hire_date)/365 AS "worked_years",DATEDIFF(CURDATE(),hire_date) AS "worded_days",
TO_DAYS(CURDATE()) - TO_DAYS(hire_date) AS "worked_days1"
FROM employees
ORDER BY worked_years DESC;
# 6、查询员工姓名，hire_date,department_id,满足以下条件：
# 雇用时间在1997年之后，department_id为80或90或110,commission_pct不为空
SELECT last_name,hire_date,department_id
FROM employees
WHERE department_id IN (80,90,110) 
AND commission_pct IS NOT NULL
# and hire_date >= '1997-01-01' # 存在着隐式转换
# and date_format(hire_date,'%Y-%m-%d') >= '1997-01-01' # 显式转换操作，格式化：日期--> 字符串
# and date_format(hire_date,'%Y') >= '1997' # 显式转换操作，格式化
# and hire_date >= str_to_date('1997-01-01','%Y-%m-%d')
AND YEAR(hire_date) >= '1997' 
ORDER BY hire_date;
# 7、查询公司中入职超过10000天的员工姓名、入职时间
SELECT last_name,hire_date, DATEDIFF(NOW(),hire_date) AS "hire_days"
FROM employees
WHERE DATEDIFF(NOW(),hire_date) >= 10000 
ORDER BY hire_days DESC; 
# 8、做一个查询，产生下面的结果
# <last_name> earns <salary> monthly but wants <salary * 3>
SELECT CONCAT(last_name, ' earns ', TRUNCATE(salary,0), ' monthly but wants ', TRUNCATE((salary * 3),0)) AS "Dream Salary"
FROM employees;
# 9、使用case-when,按照下面的条件
/* job           grade
AD_PRES          A
ST_MAN          B
IT_PROG          C
SA_PER          D
ST_CLERK      E      
产生下面的结果：*/
SELECT last_name,job_id, CASE job_id WHEN 'AD_PRES' THEN 'A'
                 WHEN 'ST_MAN'  THEN 'B'
                 WHEN 'IT_PROG' THEN 'C'
                 WHEN 'SA_PER'  THEN 'D'
                 WHEN 'ST_CLERK'THEN 'E'
                 ELSE 'undefined' END AS "grade"    
FROM employees;