介绍

在SQL语言中，包含了内置函数和自定义函数，内置函数是系统内置的通用函数，而自定义函数是我们根据自
己的需要编写的

内置函数分为两类： 单行函数 、 聚合函数（或分组函数） 。
1
2
3
4

数值函数

基本函数

角度与弧度互换函数

三角函数

指数与对数

进制间的转换

字符串函数

日期和时间函数

获取日期、时间

SELECT CURDATE(),CURTIME(),NOW(),SYSDATE(),UTC_DATE(),UTC_DATE(),UTC_TIME(),UTC_TIME()
FROM DUAL;
1
2
3

日期与时间戳的转换

SELECT UNIX_TIMESTAMP(),UNIX_TIMESTAMP('2022-06-01 12:12:32'),
FROM_UNIXTIME(1654056752),FROM_UNIXTIME(1654056752)
FROM DUAL;
1
2
3

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

SELECT YEAR(CURDATE()),MONTH(CURDATE()),DAY(CURDATE()),
HOUR(CURTIME()),MINUTE(NOW()),SECOND(SYSDATE())
FROM DUAL;


SELECT MONTHNAME('2022-10-26'),DAYNAME('2022-10-26'),WEEKDAY('2022-10-26'),
QUARTER(CURDATE()),WEEK(CURDATE()),DAYOFYEAR(NOW()),
DAYOFMONTH(NOW()),DAYOFWEEK(NOW())
FROM DUAL;
1
2
3
4
5
6
7
8
9

日期的操作函数

SELECT EXTRACT(SECOND FROM NOW()),EXTRACT(DAY FROM NOW()),
EXTRACT(HOUR_MINUTE FROM NOW()),EXTRACT(QUARTER FROM '2021-05-12')
FROM DUAL;
1
2
3

时间和秒数转换的函数

SELECT TIME_TO_SEC(CURTIME()),
SEC_TO_TIME(83355)
FROM DUAL;
1
2
3

计算日期和时间的函数

SELECT NOW(),DATE_ADD(NOW(),INTERVAL 1 YEAR),
DATE_ADD(NOW(),INTERVAL -1 YEAR),
DATE_SUB(NOW(),INTERVAL 1 YEAR)
FROM DUAL;


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;
1
2
3
4
5
6
7
8
9
10
11
12

SELECT ADDTIME(NOW(),20),SUBTIME(NOW(),30),SUBTIME(NOW(),'1:1:3'),DATEDIFF(NOW(),'2021-10-01'),
TIMEDIFF(NOW(),'2021-10-25 22:10:10'),FROM_DAYS(366),TO_DAYS('0000-12-25'),
LAST_DAY(NOW()),MAKEDATE(YEAR(NOW()),32),MAKETIME(10,21,23),PERIOD_ADD(20200101010101,10)
FROM DUAL;
1
2
3
4

日期的格式化与解析

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

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

格式化：日期 -> 字符串
解析：  字符串 -> 日期

此时我们谈的是日期的显式格式化和解析

之前，我们接触过隐式的格式化或解析
SELECT *
FROM employees
WHERE hire_date = '1993-01-13';

格式化：
SELECT DATE_FORMAT(CURDATE(),'%Y-%M-%D'),
DATE_FORMAT(NOW(),'%Y-%m-%d'),TIME_FORMAT(CURTIME(),'%h:%i:%S'),
DATE_FORMAT(NOW(),'%Y-%M-%D %h:%i:%S %W %w %T %r')
FROM DUAL;

解析：格式化的逆过程
SELECT STR_TO_DATE('2021-October-25th 11:37:30 Monday 1','%Y-%M-%D %h:%i:%S %W %w')
FROM DUAL;

SELECT GET_FORMAT(DATE,'USA')
FROM DUAL;

SELECT DATE_FORMAT(CURDATE(),GET_FORMAT(DATE,'USA'))
FROM DUAL;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

流程控制函数

加密与解密函数

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

1
2
3

PASSWORD()在mysql8.0中弃用。
SELECT MD5('mysql'),SHA('mysql'),MD5(MD5('mysql'))
FROM DUAL;

ENCODE()\DECODE() 在mysql8.0中弃用。
SELECT ENCODE('atguigu','mysql'),DECODE(ENCODE('atguigu','mysql'),'mysql')
FROM DUAL;

1
2
3
4
5
6
7
8

MySQL信息函数

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

1
2
3

SELECT VERSION(),CONNECTION_ID(),DATABASE(),SCHEMA(),
USER(),CURRENT_USER(),CHARSET('fsp'),COLLATION('fsp')
FROM DUAL;
1
2
3

其他函数

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

如果n的值小于或者等于0，则只保留整数部分
SELECT FORMAT(123.125,2),FORMAT(123.125,0),FORMAT(123.125,-2)
FROM DUAL;

SELECT CONV(16, 10, 2), CONV(8888,10,16), CONV(NULL, 10, 2)
FROM DUAL;

以“192.168.1.100”为例，计算方式为192乘以256的3次方，加上168乘以256的2次方，加上1乘以256，再加上100。
SELECT INET_ATON('192.168.1.100'),INET_NTOA(3232235876)
FROM DUAL;

BENCHMARK()用于测试表达式的执行效率
SELECT BENCHMARK(100000,MD5('mysql'))
FROM DUAL;

CONVERT():可以实现字符集的转换
SELECT CHARSET('atguigu'),CHARSET(CONVERT('atguigu' USING 'gbk'))
FROM DUAL;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

单行函数练习

1.显示系统时间(注：日期+时间)

select now()
from dual;
1
2

2.查询员工号，姓名，工资，以及工资提高百分之20%后的结果（newsalary）

SELECT employee_id, last_name, salary, salary * 1.2 "new salary"
FROM employees;
1
2

3.将员工的姓名按首字母排序，并写出姓名的长度（length）

SELECT last_name,LENGTH(last_name)
FROM employees
ORDER BY last_name DESC;
1
2
3

4.查询员工id,last_name,salary，并作为一个列输出，别名为OUT_PUT

SELECT CONCAT(employee_id, ',' , last_name , ',', salary) OUT_PUT
FROM employees;
1
2

5.查询公司各员工工作的年数、工作的天数，并按工作年数的降序排序。

SELECT DATEDIFF(SYSDATE(), hire_date) / 365 worked_years, DATEDIFF(SYSDATE(),
hire_date) worked_days
FROM employees
ORDER BY worked_years DESC
1
2
3
4

6.查询员工姓名，hire_date , department_id，满足以下条件：雇用时间在1997年之后，department_id 为80 或 90 或110, commission_pct不为空

SELECT last_name, hire_date, department_id
FROM employees
#WHERE hire_date >= '1997-01-01'
#WHERE hire_date >= STR_TO_DATE('1997-01-01', '%Y-%m-%d')
WHERE DATE_FORMAT(hire_date,'%Y') >= '1997'
AND department_id IN (80, 90, 110)
AND commission_pct IS NOT NULL
1
2
3
4
5
6
7

7.查询公司中入职超过10000天的员工姓名、入职时间

SELECT last_name,hire_date
FROM employees
#WHERE TO_DAYS(NOW()) - to_days(hire_date) > 10000;
WHERE DATEDIFF(NOW(),hire_date) > 10000;
1
2
3
4

8.做一个查询，产生下面的结果

 <last_name> earns `` monthly but wants <salary*3>
 Dream Salary
 King earns 24000 monthly but wants 72000
1
2
3

SELECT CONCAT(last_name, ' earns ', TRUNCATE(salary, 0) , ' monthly but wants ',
TRUNCATE(salary * 3, 0)) "Dream Salary"
FROM employees
limit 0,1;
1
2
3
4

9.使用CASE-WHEN，按照下面的条件：

 job grade
 AD_PRES A
 ST_MAN B
 IT_PROG C
 SA_REP D
 ST_CLERK E
 产生下面的结果
 Last_name Job_id Grade
 king AD_PRES A
1
2
3
4
5
6
7
8
9

SELECT last_name Last_name, job_id 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 'F'
END "grade"
FROM employees
LIMIT 0,1；
1
2
3
4
5
6
7
8
9