1. 函数的理解

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

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

2. 不同DBMS之间函数的差异性

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

3. MySQL函数及分类

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

两种SQL函数

单行函数

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

4. 数值函数

4.1 基本函数

常见函数

# ABS(X) 绝对值函数
# SIGN() 判断正负函数，正数返回1，负数返回-1，零返回0
# PI() 返回π的值
# CEILING(X) 求大于等于某个值的最小整数
# FLOOR(X) 求小于等于某个值的最大整数
# MOD(N,M) 求N除以M的余数
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)
FROM DUAL;

随机数函数

相同的随机数种子生成的随机数也一定是一样的

SELECT RAND(),RAND(),RAND(10),RAND(10),RAND(-1),RAND(-1)
FROM DUAL;

四舍五入函数

round(x,y)对数据x保留y位有效数字，也可以省略y不写，默认保留到整数位

SELECT ROUND(12.33),ROUND(12.343,2),ROUND(12.324,-1),TRUNCATE(12.66,1),TRUNCATE(12.66,-1)
FROM DUAL;

截断函数

TRUNCATE(x,y)对小数点后第y位之后的数据全部置0处理

SELECT TRUNCATE(123.456,0),TRUNCATE(123.456,1),TRUNCATE(123.456,2),TRUNCATE(123.456,-1)
FROM DUAL;

4.2 三角函数

4.2.1 角度和弧度互换函数

4.2.2 三角函数

4.3 指数和对数

SELECT POW(2,3),EXP(2),LN(2.98),LOG10(10),LOG10(-1),LOG2(4),LOG2(-4)
FROM DUAL;

4.4 进制转换函数

SELECT BIN(16),HEX(16),OCT(16),CONV(16,10,2)
FROM DUAL;

5. 字符串函数

注意：在MySQL中，字符串索引的开始位置为1！！！

5.1 字符串长度函数

CHAR_LENGTH()返回的字符串的长度
LENGTH()返回的字符串存储占用的字节数

5.2 字符串连接函数

CONCAT(str1,str2,...)连接多个字符串

SELECT
    CONCAT('hello ','world',',hello ','China') str1,
    CONCAT_WS(' ','hello','world','hello','China') str2
FROM
    DUAL;

应用举例

SELECT
    CONCAT(emp.last_name,' work for ',man.last_name) str
FROM employees emp,employees man
WHERE emp.manager_id = man.employee_id;

5.3 字符串插入函数

insert(str,idx,len,newStr)在索引为idx后的长度为len的字符串替换为newStr

SELECT
    INSERT('helloworld',2,3,'aaa') str
FROM
    DUAL;

5.4 字符串替换函数

replace(str,old_str,new_str)将str中的old_str全部替换为new_str

SELECT
    REPLACE('helloworld','world',' China') str
FROM
    DUAL;

5.5 大小写转换函数

UPPER(S)字符串S中所有字母转换成大写字母
LOWER(S)字符串S中所有字母转换成小写字母

SELECT
    UPPER('hello,World,gooD,nIght') upper,
    LOWER('HELLO,WORLD!') lower
FROM
    DUAL;

5.6 字符串填充函数

LPAD(str,len,pad)使用字符串pad对字符串str的最左边进行填充，直到str长度为len
RPAD(str,len,pad)使用字符串pad对字符串str的最右边进行填充，直到str长度为len

5.7 去除空格函数

LTRIM(s)去除字符串S左边的所有空格
RTRIM(s)去除字符串S右边的所有空格
TRIM(s) 去除字符串开始和结尾的所有空格

SELECT
    TRIM('   hello, world    ') str1,
    LTRIM('   hello, world    ') str2,
    RTRIM('   hello, world    ') str3
FROM
    DUAL;

5.8 字符串比较函数

STRCMP()依次比较两个字符串的ASCII码

SELECT
    STRCMP('apple','banana') str1,
    STRCMP('apple','appl') str2,
    STRCMP('apple','apple2') str3
FROM
    DUAL;

5.9 字符串截取函数

SUBSTR(s,index,len)截取字符串位置index处长度为len的子串

SELECT
    SUBSTR('hello,world',7,5)
FROM
    DUAL;

5.10 字符串查找函数

LOCATE(subStr,str)查找subStr在str中首次出现的位置，如果未找到，则返回 0

SELECT
    LOCATE('world','hello,world')
FROM
    DUAL;

5.11 字符串翻转函数

REVERSE(str) 返回str翻转后的字符串

SELECT
    REVERSE('hello,world')
FROM
    DUAL;

6. 日期和时间函数

6.1 获取日期和时间

6.1.1 当前日期和时间

CURDATE()当前日期，包含年、月、日
CURTIME()当前时间，包含时、分、秒

SELECT
    CURDATE() cur_date,
    CURTIME() cur_time
FROM
    DUAL;

6.1.2 当前系统日期和时间

NOW() 返回当前系统日期和时间

6.1.3 世界标准时间和日期

UTC_DATE() UTC日期
UTC_TIME() UTC时间

6.2 日期和时间戳的转换

SELECT
    UNIX_TIMESTAMP() `UNIX_TIMESTAMP1`,
    UNIX_TIMESTAMP('2022-01-08 00:00:00') `UNIX_TIMESTAMP2`,
    FROM_UNIXTIME(UNIX_TIMESTAMP()) `now`
FROM
    DUAL;

6.3 获取月份、星期、星期数、天数

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

SELECT MONTHNAME('2021-10-26'),DAYNAME('2021-10-26'),WEEKDAY('2021-10-26'),
QUARTER(CURDATE()),WEEK(CURDATE()),DAYOFYEAR(NOW()),
DAYOFMONTH(NOW()),DAYOFWEEK(NOW())
FROM DUAL;

6.4 时间和秒之间的转换

TIME_TO_SEC(time)时间转换为对应的秒数
SEC_TO_TIME(sec)秒数转换为对应的时间

SELECT
    TIME_TO_SEC('21:34:00') `second`,
    SEC_TO_TIME(TIME_TO_SEC('21:34:00')) `time`
FROM
    DUAL;

6.5 日期的格式化与解析

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

示例：

SELECT
    DATE_FORMAT(NOW(),'%H:%i:%S') `time`
FROM
    DUAL;

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;

7. 流程控制函数

7.1 IF

SELECT
    emp.last_name,
    emp.salary,
    IF(emp.salary >= 6000,'高工资','低工资') `details`
FROM
    employees emp;

7.2 IFNULL

SELECT
    emp.last_name,
    emp.department_id,
    IFNULL(emp.department_id,'没有部门') `datails`
FROM
    employees emp;

7.3 CASE-WHEN-THEN

SELECT
    employee_id,
    salary,
CASE
        WHEN salary >= 15000 THEN
        '高薪' 
        WHEN salary >= 10000 THEN
        '潜力股' 
        WHEN salary >= 8000 THEN
        '屌丝' ELSE '草根' 
    END '描述' 
FROM
    employees;

7.4 CASE-EXPR-WHEN-THEN

SELECT CASE WHEN 1 < 0 THEN 'yes' WHEN 1 = 0 THEN 'no' ELSE 'unknown' END;

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;

练习：查询部门号为 10,20, 30 的员工信息, 若部门号为 10, 则打印其工资的 1.1 倍, 20 号部门, 则打印其工资的 1.2 倍, 30 号部门打印其工资的 1.3 倍数。

SELECT
    department_id,
    salary,
CASE
        department_id 
        WHEN 10 THEN
        1.1 * salary 
        WHEN 20 THEN
        1.2 * salary 
        WHEN 30 THEN
        1.3 * salary ELSE salary 
    END "tips"
FROM
    employees;

8. 加密解密函数

实际上，现在基本不会在MySQL中去进行加密操作，一般在前端或者后端，就已经对要存储的信息进行了一次或者多次加密。

注意：PASSWORD()、ENCODE()、DECODE()在MySQL8.0以及后续版本中已经被弃用了。

MD5()使用md5算法进行加密
SHA()使用sha算法进行加密

SELECT
    SHA('hello') `sha`,
    MD5('hello') `md5`
FROM
    DUAL;

9. 信息函数

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

SELECT
    VERSION() `version`,
    CONNECTION_ID() `connection_sum`,
    DATABASE() `database`,
    USER() `user1`,
    CURRENT_USER() `user2`,
    CHARSET('你好') `str1`,
    CHARSET('hello,world') `str2`,
    COLLATION('good day')
FROM
    DUAL;

10. 单行函数练习

# 1.显示系统时间(注：日期+时间)
SELECT 
    NOW()
FROM
    DUAL;
# 2.查询员工号，姓名，工资，以及工资提高百分之20%后的结果（new salary）
SELECT
    employee_id,
    last_name,
    salary,
    salary*1.2 `new salary`
FROM
    employees;
# 3.将员工的姓名按首字母排序，并写出姓名的长度（length）
SELECT
    last_name,
    LENGTH(last_name)
FROM
    employees
ORDER BY last_name;
# 4.查询员工id,last_name,salary，并作为一个列输出，别名为OUT_PUT
SELECT
    CONCAT(employee_id, '  ' ,last_name, '  ' ,salary) `OUT_PUT`
FROM
    employees;
# 5.查询公司各员工工作的年数、工作的天数，并按工作年数的降序排序
SELECT
    DATEDIFF(CURDATE(),hire_date) / 365 `years`,
    DATEDIFF(CURDATE(),hire_date) `days`
FROM
    employees
ORDER BY
    `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 STR_TO_DATE('1997-01-01','%Y-%m-%d') <= hire_date;
#  AND YEAR(hire_date) >= '1997';
# 7.查询公司中入职超过10000天的员工姓名、入职时间
SELECT
    last_name,
    hire_date
FROM
    employees
WHERE
    DATEDIFF(CURDATE(),hire_date) >= 10000;