MySQL入门教程(14)MySQL常用函数

Tanglu MySQL 2022-12-09 121 0

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()
#将指定列字符串修改为小写或大写

# 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再进行统计,所以空值也不会有影响


三、时间函数

# 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,多选一
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'


五、加密解密类函数

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

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

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



评论