首页 专题 文章 代码 归档
Mysql 常见函数
2020.02.27 20:30 2020.02.27 20:35

1. 常见函数

功能:类似各编程语言中的方法(函数),将一组逻辑语句封装在方法中,对外暴露方法名。

优点:隐藏实现细节;提高代码的重用性。

调用:select 函数名(参数列表) [from 表]

分类:

  • 单行函数:如之前章节中的concat、length、ifnull
  • 分组函数

1.1. 字符函数

1、length

select length('hello'); -- 5

select length('迷思爱'); -- 5 和字符集有关

show variables like '%char%';

2、concat拼接字符串

select concat(first_name, ' ', last_name)
from employees;

3、upper/lower

将字符串转大写/小写;

select upper(concat(first_name, ' ', last_name))
from employees;

截图-1582790721

注:如上,函数可嵌套调用;

4、substr,截取字符串

注意:该方法有四个重载函数(参数不同)

截图-1582790811

select substr('迷思爱网络学习乐园', 2);
# 1、方法一:给出截取位置,默认到最后:思爱网络学习乐园
# 2、中文有字符集问题,所以开始从1的话,是:迷思爱网络学习乐园

select substr('迷思爱网络学习乐园', 2, 4);
# 1、方法二:截取4个:思爱网络

5、instr

返回子串第一次出现的索引;

# 返回子串第一次出现的索引
select instr('迷思爱网络学习乐园','迷思爱') put; -- 1
select instr('迷思爱网络学习乐园','乐园') put; -- 8

6、trim

去除首尾空字符串;

select length('迷思爱'); -- 9
select length(trim('   迷思爱   ') ) put; -- 9

例子2:从某串中去除某子串:

select trim('i' from 'I love u iiiiiii');/*I love u */

7、左填充

# 用过指定的字符填充指定的长度;
select lpad('迷思爱', 10, 'Love'); -- LoveLov迷思爱

8、右填充

# 用过指定的字符填充指定的长度;
select rpad('迷思爱', 10, 'I'); -- 迷思爱IIIIIII

9、replace

替换字符串:

select replace('迷思爱学习乐园', '乐园', '天地'); -- 迷思爱学习天地

1.2. 数学函数

1、round四舍五入

select round(1.4); -- 1
select round(1.5); -- 2

保留小数点2位

# 保留小数点2位
select round(1.789, 2); -- 1.79

2、ceil向上取整

select ceil(1.11); -- 2
select ceil(1.01); -- 2

select ceil(-1.01); -- -1

3、floor向下取整

select floor(1.01); -- 1
select floor(1.11); -- 1
select floor(-1.01); -- -2

4、truncate截断

select truncate(1.8888, 3); -- 1.888

5、mod取余

select mod(10, 3); -- 1
select 10 % -3; -- 1

1.3. 日期函数

1、now

返回当前系统时间

select now(); --2020-02-27 16:28:17

2、curdate/curtime

返回当前系统日期/时间

select curdate(); -- 2020-02-27

select curtime(); -- 16:29:13

3、获取年月日时分秒

select year(now()) year; -- 2020
select year('2019-12-21') year; -- 2019

select month(now()); -- 2

select monthname(now()); -- February

select date(now()); -- 2020-02-27

select hour(now()); -- 16

select minute(now()); -- 31

select second(now()); -- 59

4、str_to_date

将字符格式转换成日期:

select str_to_date('2020-2-27 16:36:00', '%Y-%c-%d'); -- 2020-02-27

# 查入职日期为1992-4-3
select *
from employees
where  hiredate = '1992-4-3';

# 但是,当年在后面时,4-3 1992
# 那么应该这样来:

select *
from employees
where hiredate = str_to_date('4-3 1992','%c-%d %Y'); -- 相当于后面的'%c-%d %Y'指定了前面的日期格式;

5、date_format

将日期转换为字符串:

select date_format(now(), '%Y年%c月%d日'); -- 2020年2月27日

注:一些日期格式化的符号:

格式 描述
%a 缩写星期名
%b 缩写月名
%c 月,数值
%D 带有英文前缀的月中的天
%d 月的天,数值(00-31)
%e 月的天,数值(0-31)
%f 微秒
%H 小时 (00-23)
%h 小时 (01-12)
%I 小时 (01-12)
%i 分钟,数值(00-59)
%j 年的天 (001-366)
%k 小时 (0-23)
%l 小时 (1-12)
%M 月名
%m 月,数值(00-12)
%p AM 或 PM
%r 时间,12-小时(hh:mm:ss AM 或 PM)
%S 秒(00-59)
%s 秒(00-59)
%T 时间, 24-小时 (hh:mm:ss)
%U 周 (00-53) 星期日是一周的第一天
%u 周 (00-53) 星期一是一周的第一天
%V 周 (01-53) 星期日是一周的第一天,与 %X 使用
%v 周 (01-53) 星期一是一周的第一天,与 %x 使用
%W 星期名
%w 周的天 (0=星期日, 6=星期六)
%X 年,其中的星期日是周的第一天,4 位,与 %V 使用
%x 年,其中的星期一是周的第一天,4 位,与 %v 使用
%Y 年,4 位
%y 年,2 位

1.4. 其他函数

select version(); -- 5.7.28

select database(); -- employees

select user(); -- root@localhost

1.5. 流程控制函数

1、if函数

# if,相当于三元运算符的效果

select if(10 > 3, '大', '小'); -- 大

2、case函数

格式:

case 待判断的自读或者表达式
when 常量1 then 要显示的值1或者语句1
when 常量2 then 要显示的值2或者语句2

else 要显示的值n或者语句n
end

案例,查询员工的工资:

部门号=40,显示的工资为1.1倍 部门号=50,显示的工资为1.2倍

select salary     old_salary,
       department_id,
       case department_id
           when 40 then salary * 1.2
           when 40 then salary * 1.2
           else salary
           end as new_salary
from employees;

2. 分组函数

功能:用作统计使用,也称聚合函数、统计函数亦或组函数;

分类:

sum、 avg、 max、 min、 count

1、简单使用

select sum(salary)
from employees;

select avg(salary)
from employees; -- 6461.682243

select min(salary)
from employees; -- 2100

select max(salary)
from employees; -- 24000

综合来写:

select count(*), avg(salary), sum(salary), min(salary), max(salary)
from employees;

2、参数支持的类型

sum、avg用于处理数字型

max、min、count可以处理任何类型;

3、是否忽略null值

都忽略null值!

4、和distinct搭配,去重运算

select sum(distinct commission_pct), sum(salary)
from employees; -- 1.75

5、count函数的研究

select count(salary)
from employees; -- 107

select count(*)
from employees; -- 107

select count(1)
from employees; -- 107

/*相当于加了一列常量列,所以是统计所有列*/
select count('迷思爱')
from employees; -- 107

效率问题:

innodb引擎:count(*)和count(1)差不多,比count(字段)高一点

myisam引擎:count(*)效率高

6、和分组一同查询的字段有要求

比如:

select avg(salary), employee_id
from employees;

查询employee_id没有意义,并且在Mysql5.7.x报错;

和分组函数查询的字段要求是group by 后的字段;

本节阅读完毕! (分享
二维码图片 扫描关注我们哟