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;

注：如上，函数可嵌套调用；

4、substr，截取字符串

注意：该方法有四个重载函数（参数不同）

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 后的字段；