MySQL入门教程（14）MySQL常用函数

MySQL提供了丰富的内置函数，通过这些函数能够更好的提供数据分析与统计功能。在使用函数的时候通常会给字段起一个别名方便后面排序等操作。MySQL的内置函数从实现的功能角度可以分为字符串函数、数值函数、日期和时间函数、流程控制函数、加解密函数、聚合函数等，不同的函数可嵌套组合使用，但是目前MySQL不支持对聚合函数进行嵌套，Oracle是可以的

一、字符串类型函数

# CONCAT()函数
#实现字段和字段或者字段和指定字符串的拼接，函数中没有引号的属于表中字段
select concat(name,age) from student #拼接name和age字段
select concat(user,'@',host) from mysql.user;  #将user字段和host字段拼接，中间自定义了一个@符作为分割，这是显示数据库中所有用户的快捷办法
SELECT CONCAT(id,',',name,','salary) "info" FROM employees;

# LEFT()、RIGHT()函数
#从左或从右截取指定长度的字符串，常用于获取文章部分长度，然后拼接省略号作为摘要
select left(name,5) from student;  #截取name列前5个字段

# LENGTH()函数
#显示指定列或者指定内容的字节长度，需要注意字符和字节的区别
SELECT LENGTH('你好')   #UTF8一个汉字为一个字符，一个汉字字符的长度为3个字节，所以最终结果为6
SELECT LENGTH('hello')  #长度为5
SELECT name,LENGTH(name) "name_length" FROM students ORDER BY name_length ASC;  

# CHAR_LENGTH函数
#显示指定列或者指定内容的字符长度
SELECT CHAR_LENGTH('你好')  #长度为2
SELECT CHAR_LENGTH('hello')  #长度为5

# REPLACE()函数
#替换指定字符串的内容

# LOWER()、UPPER()
#将指定列字符串修改为小写或大写，可以用于区分大小查询，因为mysql默认是不区分大小写的，通过该函数就可以精准查询
SELECT name,salary FROM employees WHERE lower(name)='tanglu'

# LEFT()、RIGHT()
#从左或者从右返回指定长度的内容
SELECT LEFT('hello',2),RIGHT('hello',3)  #返回he和llo

# LTRIM()、RTRIM()
#将字符串左边或右边的空格去掉后返回

# SUBSTRING()
#指定需要截取的字符串范围并返回
select substring(name,1,3) from student ;  #返回name列的第一到第三个字符

二、聚合函数

聚合函数作用于一组数据，然后对这组数据进行处理后返回一个值，这些函数也可以组合使用

# AVG()
#计算列的平均值，只适用于数字类型的列，如果遇到NULL则该行不进入计算，所以在进行统计的时候要注意把NULL值也加入进去
select AVG(age) from student  #如果字段存在NULL则会返回错误的平均值 
SEECLT SUM(age) / COUNT(IFNULL(age,0))  #先求和再计算平均值

# SUM()函数
#对查询结果进行求和，只适用于数字类型的列，如果遇到NULL则该行不加入计算
select SUM(age) from student  #从student表中统计age列的合

# MAX()、MIN()函数
#找出指定列中最大或最小的行
select MAX(id) from student;

# COUNT()函数
#通过索引优化查询的方式计算指定字段的行数，可写为count(*)、count(1)、count(字段名)
select count(name) from student  #count(字段名)会判断每一行数据是否为空，如果有null则不会纳入统计，会产生偏差
select count(*) from student;    #count(*)和count(1)一样，不会判空，会统计包含NULL的行数，效率理论上最快，这里count(1)相当于把每一行填充了1再进行统计，所以空值也不会有影响

#组合使用上面函数示例，返回表中物品的数目、最高价、最小价和平均价格
SELECT COUNT(*) AS num_items,
       MAX(price) AS max_price,
       MIN(price) AS min_price,
       AVG(price) AS AVG_price
FROM products

三、时间函数

# NOW()、CURRENT_TIMESTAMP()、SYSDATE()
#返回SQL执行时的系统时间，格式为年月日时分秒
SELECT NOW();
SELECT CURRENT_TIMESTAMP();

#CURDATE、CURRENT_DATE()函数
#返回当前日期，只包含年月日

#CURTIME、CURRENT_TIME()函数
#返回当前时间，只包含时分秒

#DAYOFYEAR()函数  
#返回一年中的第几天,还有其他类似返回周几、第几周等表示时间的函数
SELECT DAYOFYEAR(NOW()) FROM DUAL;

#DATE_ADD()、ADDDATE函数 
#给该函数一个指定日期以及一个表达式并计算出最终日期
SELECT DATE_ADD(NOW(),INTERVAL 1 YEAR) FROM DUAL
SELECT DATE_ADD(NOW(),INTERVAL -1 DAY) FROM DUAL

#DATE_FORMAT()函数
#用于将指定的字符串字段转换为一定格式的时间类型，属于显示转换，避免隐式转换的性能问题
SELECT * FROM student where DATE_FORMAT(birthday,'%Y') >=2000;

四、流程控制函数

流程控制函数可以根据不同的条件执行不同的处理流程，在语句中实现不同的条件选择。

# IF()函数
#IF(value,value1,value2)  #如果value的值为ture则返回value1，否则返回value2，相当于2选1
SELECT name,age,IF(age > 18,'成年人','未成年') FROM students；
SELECT last_name,salary,IF(salary>=10000,'高收入','低收入') FROM employees;


# IFNULL()函数
#IFNULL(value1,value2)  #如果value1不为NULL返回value1，否则返回value2
SELECT last_name,commission_pct,IFNULL(commission_pct,0) "details"  #如果commission_pct有值就返回其值，否则返回0


# CASE()函数
#CASE WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果2 ... [ELSE resultn] END  #相当于if else，多选一，THEN子句可以包含任意合法的表达式，包括对某个字段或列的引用
SELECT name,age,
CASE 
  WHEN age > 18 THEN '成年人'
  WHEN age > 60 THEN '老年人'
ELSE '未成年' END "type"  FROM students;

                       
#查询部门号为10\20\30的员工，如果部门为10的打印其工资的1.1倍，依此类推
SELECT employee_id,name,department_id,salary,CASE department_id WHEN 10 THEN salary * 1.1 WHEN 20 THEN salary *1.2 WHEN 30 THEN salary *1.3 ELSE salary *0.8 END 'details'


#统计每个部门当周和上周的查询数量
SELECT department AS 部门,
SUM(CASE WHEN query_time BETWEEN '2023-04-10' AND '2023-04-16' THEN u.query_count ELSE 0 END) AS 本周请求数,
SUM(CASE WHEN query_time BETWEEN '2023-04-3' AND '2023-04-9' THEN u.query_count ELSE 0 END) AS 上周请求数,
SUM(query_count) AS 总请求数
FROM `employees` e 
JOIN `user_count` u ON e.work_id = u.work_id  
GROUP BY 部门 ORDER BY 总请求数 DESC

五、加密解密类函数

对数据库中的字段进行加密，但是通常加密操作会放在前端完成，而不是落库后再加密，这样的话传输过程就是明文了，这里作为了解即可

#MD5()函数
SELECT MD5('tanglu') FROM DUAL;

#PASSWORD()函数，8.0已经不可用