mysql查询常用函数介绍

目录

​

单行函数 

 1. 字符函数

2.逻辑处理

3.数学函数 

4.日期函数

 分组函数

---

单行函数 : 对每一行操作    分组函数 : 分组操作

这里我们以t_user表为例: 

单行函数 

 1. 字符函数

length() ：获取参数值的字节个数

char_length() 获取参数值的字符个数

concat(str1,str2,.....) ：拼接字符串

upper()/lower() ：将字符串变成大写 / 小写

substring(str,pos,length) ：截取字符串 位置从 1 开始

instr(str, 指定字符 ) ：返回子串第一次出现的索引，如果找不到返回 0

trim(str) ：去掉字符串前后的空格或子串 ,trim( 指定子串 from 字符串 )

lpad(str,length, 填充字符 ) ：用指定的字符实现左填充将 str 填充为指定长度

rpad(str,length, 填充字符 ) ：用指定的字符实现右填充将 str 填充为指定长度

replace(str,old,new) ：替换，替换所有的子串

示例如下:

length(): 获得参数的字节个数, 一个汉字是3个字节, 一个字母是1个

-- 获得字节个数
SELECT LENGTH(NAME),LENGTH(number) FROM t_user

结果 : 

char_length(): 获得参数的字符个数 

-- 获得字符个数
SELECT CHAR_LENGTH(NAME),CHAR_LENGTH(number) FROM t_user

结果 : 

concat() : 拼接字符串

-- 拼接name,':',age属性
SELECT CONCAT(NAME,':',age) FROM t_user

 结果 :

 substring() : 截取字符串

-- 从name中截取从1开始(包括1)至2位置结束的子串
SELECT SUBSTRING(NAME,1,2) FROM t_user

结果: 

instr() : 返回子串第一次出现的位置

-- 返回'雷'在name中第一次出现的位置
SELECT INSTR(NAME,'雷') FROM t_user

 结果:

trim() : 去除字符串前后空格或特定子串

-- 去除子串'b'从name中
SELECT TRIM('b' FROM NAME) FROM t_user

 结果 : 

lpad() : 左填充至指定长度

-- 向左填充至总长度为4
SELECT LPAD(NAME,4,'a') FROM t_user

 结果: 

 右填充同理

2.逻辑处理

case when 条件 then 结果 1 else 结果 2 end; 可以有多个 when

-- 逻辑处理
SELECT NAME,
  CASE
    WHEN weight >= 150 THEN '偏胖'
    WHEN weight <= 120 THEN '偏瘦'
    ELSE '正常' 
  END weight FROM t_user

查询结果 : 

 ifnull(被检测值,默认值)函数检测是否为null，如果为null，则返回指定的值，否则返回原本的值

SELECT IFNULL(age,'未成年') FROM T_USER

查询结果 : 

 if函数：if else的效果 if(条件,结果1,结果2)

SELECT IF(age>=18,'成年','未成年') FROM T_USER

查询结果 : 

3.数学函数 

round( 数值 ) ：四舍五入

ceil( 数值 ) ：向上取整，返回 >= 该参数的最小整数

floor( 数值 ) ：向下取整，返回 <= 该参数的最大整数

truncate( 数值 , 保留小数的位数 ) ：截断，小数点后截断到几位

mod( 被除数 , 除数 ) ：取余，被除数为正，则为正；被除数为负，则为负

rand() ：获取随机数，返回 0-1 之间的小数

4.日期函数

now() ：返回当前系统日期 + 时间

curdate() ：返回当前系统日期，不包含时间

curtime() ：返回当前时间，不包含日期

可以获取指定的部分，年、月、日、小时、分钟、秒

YEAR( 日期 ),MONTH( 日期 ),DAY( 日期 ) ,HOUR( 日期 ) ,MINUTE( 日期 ) SECOND( 日期 )

str_to_date ：将日期格式的字符转换成指定格式的日期

    SELECT STR_TO_DATE('1998-3-2', '%Y-%m-%d');

date_format ：将日期转换成字符串

    SELECT DATE_FORMAT(NOW(), '%y 年 %m 月 %d 日 )') AS output ;

datediff ：返回两个日期相差的天数

日期格式如下 : 

-- 将字符串转为日期格式
SELECT STR_TO_DATE('2020-03-20','%Y-%m-%d')

-- 将日期转为字符串格式
SELECT DATE_FORMAT(birthday,'%Y-%m') FROM t_user

-- 计算两个日期间隔的天数
SELECT DATEDIFF(NOW(),birthday) FROM t_user

 分组函数

 

此处给表中添加了一些数据 

功能：用作统计使用，又称为聚合函数或统计函数或组函数

分类： sum 求和、 avg 平均值、 max 最大值、 min 最小值、 count 计数（非空）

1.sum ， avg 一般用于处理数值型 max ， min ， count 可以处理任何类型

2. 以上分组函数都忽略 null 值

3.count 函数的一般使用 count （ * ）用作统计行数

4. 和分组函数一同查询的字段要求是 group by 后的字段

sum() : 

-- 通过性别分组,获得体重的总值
SELECT sex,SUM(weight) FROM t_user GROUP BY sex

查询结果 : 

 avg():

-- 分组查询男女生体重平均值
SELECT sex,AVG(weight) FROM t_user GROUP BY sex

查询结果 : 

 max(),min()

-- 分组查询体重最大最小值
SELECT sex,MAX(weight) FROM t_user GROUP BY sex
SELECT sex,MIN(weight) FROM t_user GROUP BY sex

最大值查询结果 :

count()

-- 分组查询总共的数据数量
SELECT sex,COUNT(*) FROM t_user GROUP BY sex

 查询结果 :

 这里需要注意的是, 这里我们可以选择 * 或者主键列 , 因为主键不能为null , 如果我们使用其他列的话,如果为null的话 , 就不会被计算在内 , 所以这里我们最好使用 * 或者主键列来统计