mysql常见函数

常见函数

概念：类似于java的方法，将一组逻辑语句封装在方法体中，对外暴露方法名。

好处：

1. 隐藏了实现细节
2. 提高代码的重用性

调用:select 函数名（实参列表） [from 表];

分类：

1. 单行函数

   如 concat、length、ifnull 等

2. 分组函数

   功能：做统计使用，又称为统计函数、聚合函数、组函数。

1. 字符函数

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

  select length('hello');
  select length('学习MySQL'); -- utf-8编码中文占三字节，gbk编码中文占两字节
  

• concat() 拼接字符串

  -- 查询学生表中，所有学生班级学号姓名并用'_'连接，以学生信息命名
  select concat(class_name,'_',std_id,'_',`name`) as '学生信息' from std_message;
  

• upper、lower 小写转大写，大写转小写

  select upper('hello');
  select lower('HELLo');
  select concat(upper('hello'),'_',lower('HELLo'));
  

• substr、substring

  -- 截取指定索引处后面所有字符
  select substr('helloMySQL',6);
  -- 截取从指定索引处指定字符长度的字符
  select substr('helloMySQL',1,5);
  

• instr

  -- 返回子串第一次出现的索引，如果找不到返回0
  select instr('helloMySQL','MySQL');
  

• trim

  -- 去除字符串前后空格
  select TRIM('     helloMySQL     ');
  select TRIM('     hello   MySQL     ');
  -- 去除字符串前后指定字符
  select trim('d' FROM 'ddddhelloMySQLdddd') AS output;
  

• lpad

  -- 用指定的字符左填充到指定长度
  select lpad('hello',10,'m') as output;
  select lpad('hello',2,'m') as output;
  

• rpad

  -- 用指定的字符右填充到指定长度
  select rpad('hello',10,'m') as output;
  

• repalce

  -- 将字符串中字符串替换为指定字符串
  select replace('helloWord','Word','MySQL') as output;
  

2. 数字函数

• round 四舍五入

  select round(1.55);
  select round(-1.55);
  select round(1.555,2);
  

• ceil 向上取整

  -- 返回>=该参数的最小整数
  select ceil(1.2);
  select ceil(-1.2);
  

• floor向下取整

  -- 返回<=该参数的最大整数
  select floor(9.99);
  select floor(-9.99);
  

• truncate 截断

  -- 保留小数点后指定位数
  select truncate(1.6666,1);
  

• mod 取余

  select mod(10,3);
  

3.日期函数

• now 返回当前系统日期+时间

  select now();
  

• curdate 返回当前系统日期，不包含时间

  select curdate();
  

• curtime 返回当前时间，不包含日期

  select curtime();
  

• 获取指定的部分，年、月、日

  select year(order_time) from enter_order;
  select month(order_time) from enter_order;
  select monthname(order_time) from enter_order;
  select day(order_time) from enter_order;
  
  select hour(order_time) from enter_order;
  select minute(order_time) from enter_order;
  select second(order_time) from enter_order;
  

• str_to_date 将字符通过指定的格式转换为日期

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-unrvXUmr-1609052478682)(C:\Users\lixiaoshan\AppData\Roaming\Typora\typora-user-images\image-20201224134545129.png)]

  -- 查询订单日期为'2020-12-20 09:52:20'的订单信息
  select * from enter_order where order_time = '2020-12-20 09:52:20';
  select * from enter_order where order_time = str_to_date('2020/12/20 09:52:20','%Y/%c/%d %h:%i:%s');
  

• date_format 将日期转换为字符

  -- 获取当前时间，并将时间格式转换为'%y年%m月%d日'输出
  select DATE_FORMAT(now(),'%y年%m月%d日');
  

4. 系统函数

-- 查看MySQL版本
select version();
-- 查看当前数据库
select database();
-- 查看当前用户
select user();

5. 流程控制函数

• if 函数

  -- 案例1：查询社团分类表图标，社团名称，如果图标为Null或为' '提示暂无图标，如果不为null则显示其图标路径
  select if(icon is null or icon = ' ','暂无图标',icon),klasifiko_name from club_klasifiko; 
  

• case 结构

  方式一：

  /*
  	case 要判断的字段或表达式
  	when 常量1 then 要显示的值1或语句1;
  	when 常量2 then 要显示的值2或语句2;
  	...
  	else 要显示的值n或语句n;
  	end
  */
  -- 案例1：查询社团信息表中，社团名称，社团类别，社团报名费。且根据社团类别id，显示其类别名称
  select name,cost,case klasifiko_id 
  when 1 then '文化艺术类'
  when 2 then '文化体育'
  when 3 then '创业创新'
  when 4 then '学术科技'
  when 5 then '学术研究'
  when 6 then '志愿公益'
  when 7 then '其他'
  when 9 then '手工制作类'
  when 11 then '学术科技类'
  when 12 then '体育健身类' 
  end as '社团类别' from club;
  

  方式二：

  /*
  	case 
  	when 条件1 then 要显示的值1或语句1;
  	when 条件2 then 要显示的值2或语句2;
  	...
  	else 要显示的值n或语句n;
  	end
  */
  -- 案例2：查询社团信息表中，社团名称，社团类别，社团报名费。且根据报名费判断，在0~10之前为低报名费，在20~30为中等报名费，30以上为高报名费
  select `name`,klasifiko_id,cost, 
  case
  when cost between 0 and 10 then '低报名费'
  when cost between 20 and 30 then '中等报名费'
  when cost > 30 then '高报名费'
  end as '报名费等级' from club;  
  

6. 分组函数

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

分类：

• sum 求和函数
• avg 平均值
• max 最大值
• min 最小值
• count 计算个数

简单使用：

select sum(order_fee) as 订单金额总和 from enter_order;
select avg(order_fee) as 订单金额平均值 from enter_order;
select min(order_fee) as 订单金额最小值 from enter_order;
select max(order_fee) as 订单金额最大值 from enter_order;
-- 计算非空值有几个
select count(order_fee) as 订单数量 from enter_order;
-- 计算表行数
select count(*) from enter_order;
-- 嵌套使用，平均值保留两位小数
select round(avg(order_fee),2) as 订单金额平均值 from enter_order;

特点：

1. sum、avg一般用于处理数值型，max、min、count可以处理任何类型
2. 以上 分组函数都忽略null值
3. 可以和distinct搭配实现去重
4. 和分组函数一同查询的字段要求是group by后的字段

分组查询

语法： select 分组函数（要求出现在group by的后面）from 表名 【where 筛选条件】 group by 分组的列表 【order by 子句】

注意：查询列表比较特殊，要求是分组函数和group by后出现的字段。

使用：

1. 简单查询

-- 案例1：查询每个社团类别中价格最高的报名费
select max(cost),klasifiko_id as 社团列表id from club group by klasifiko_id; 
-- 案例2：查询每个社团类别中社团的个数
select count(*),klasifiko_id from club group by klasifiko_id;

2. 添加分组前的筛选条件

-- 案例1：查询学生信息表中，每个班级性别为男的人数
select count(*),class_name from std_message where sex = 1 group by class_name;
-- 案例2：查询订单表中，有订单编号的订单中，每个用户的金额最高订单
select max(order_fee),user_id from enter_order where order_number is not null group by user_id;

3. 添加分组后的筛选条件

-- 案例1：查询社团数超过1的社团类别
select count(*), klasifiko_id from club group by klasifiko_id having count(*) > 1;
-- 案例2：查询订单表中，有订单编号的订单中，每个用户的金额最高订单，且最高金额大于20
select max(order_fee),user_id from enter_order where order_number is not null group by user_id having max(order_fee) > 20;

分组前筛选与分组后筛选的区别：

筛选种类	数据源	位置	关键字
分组前筛选	原始表	group by 子句的前面	where
分组后筛选	分组后的结果集表	group by 子句的后面	having

4. 按表达式或函数分组

-- 查询学生信息表，按学生名字长度分组，查询每一组学生个数，筛选个数大于10的
-- 1. 查询每个长度的学生个数
select count(*), length(`name`) from std_message group by length(`name`);
-- 2. 添加筛选条件
select count(*), length(`name`) from std_message group by length(`name`) having count();

5. 按多个字段分组

-- 案例1：查询学生表中，每个班级，男女学生数量
select count(*), class_name, sex from std_message group by class_name, sex;

6. 添加排序

-- 案例1：查询学生表中，每个班级，男女学生数量，并按人数从高到底排序
select count(*), class_name, sex from std_message group by class_name, sex order by count(*) desc;