函数

1. 什么是函数？

函数在计算机中有时被称为“方法”，英文function。
体现“一个功能”或“做某件事情”
函数一件工具，无需知道它的工作细节，只需要知道它能做什么

image

2. 单行函数

单行函数：每一行数据都会做为参数，得到自己对应的输出

2.1 文本函数

2.1.1 CHAR_LENGTH(s)

返回文本s的字符个数（长度）
示例1：查询ename和ename的字符个数（1个中文算1个）

select ename, CHAR_LENGTH(ename) from emp

image

2.1.2 CONCAT(s1,s2...sn)

将参数s1,s2...sn合并成一个字符串
示例2：查询ename和job，显示的格式是“TOM的职位是PROGRAMER”

select ename, job, CONCAT(ename,'的职位是',job)
from emp

image

2.1.3 LOWER(s)和UPPER(s)

将文本转换为全部小写或全部大写
LOWER(s) 转小写
UPPER(s) 转大写
示例3：将文本'Haha',转换成全部小写或全部大写的方式

select 'Haha', LOWER('Haha'), UPPER('Haha')

image

2.1.4 SUBSTR(s, start, length)

将文本s从start位置起，截取length个长度后返回结果
示例4：将ename从第2个字符起，截取3个字符

select ename, SUBSTR(ename,2,3)
from emp;

image

2.1.5 TRIM(s)

去掉文本首尾两端的多余空格
示例5：去掉' abc '文本中两端多余的空格（前面2个空格，后面3个空格）

select CHAR_LENGTH('  abc   '),  CHAR_LENGTH(TRIM('  abc   '))

image

2.2 数值函数

2.2.1 CEIL(n)和FLOOR(n)和ROUND(n,m)

CEIL：表示进一法
FLOOR： 表示退一法
ROUND： 四舍五入
示例6： 数值的进位

select ROUND(2.5), CEIL(2.1), FLOOR(2.9)

image

select ROUND(-2.5), CEIL(-2.9), FLOOR(-2.1)

image

ROUND函数
示例7：ROUND函数

SELECT ROUND(15.573,2), ROUND(15.573,0), ROUND(15.573,-1), ROUND(15.573,-2)

image

2.2.2 MOD(x,y)

计算x除以y之后的余数，这个操作一般被称为取余。
取余结果的符号与被除数是一致的
示例8： 取余操作

select MOD(5,3),MOD(5,-3),MOD(-5,3),MOD(-5,-3)

image

2.2.3 POW(x,y)和SQRT(x)

POW(x,y) 计算x的y次方
SQRT(x) 计算x的开平方结果
示例9： 2的3次方和16开平方

select POW(2,3), SQRT(16)

image

2.3 时间函数

2.3.1 当前时间

CURDATE() 获取当前日期 “年-月-日”
CURTIME() 获取当前时间 “时:分:秒”
NOW() 获取当前日期+时间 “年-月-日 时:分:秒”
示例10：获取当前服务器的系统时间

select CURDATE(), CURTIME(), NOW()

image

2.3.2 追加时间

DATE_ADD(d, INTERVAL n TYPE)
TYPE: YEAR,MONTH,DAY,HOUR,MINUTE,SECOND

示例11： 当前时间之后1小时的时间

SELECT NOW(), DATE_ADD(NOW(), INTERVAL 1 HOUR)

image

2.3.3 计算两个日期之间的天数

DATEDIFF(d1,d2)：计算d1与d2之间相差的天数
在Oracle中有一个函数 Months_between(d1,d2)：d1和d2之间的月数差
d1晚于d2 得到正数
d1早于d2 得到负数
示例12： 计算员工入职了多少天

select ename, hiredate, DATEDIFF(CURDATE(),hiredate)/365
from emp;

image

2.3.4 计算日期所在月份的最后一天

示例13：计算员工入职日期当月的最后一天

select ename,hiredate,LAST_DAY(hiredate)
from emp;

image

2.4 转换函数

2.4.1 数字转特定格式字符串

FORMAT(x,n) 将x转换成"#,###,###.##"格式 n表示小数点保留几位
如果n为负数，与n=0结果一致，只保留整数部分
示例14：将数字进行转换

select FORMAT(1234.567,2),FORMAT(1234.567,0),FORMAT(1234.567,-2)

image

2.4.2 日期转换成特定格式字符串（重要）

DATE_FORMAT(d,f): 将日期d按照f的格式进行转换
特定格式f：
year: %Y 四位数年
month: %m 数字表示月份
day: %d 数字表示天数
hour: %H 24进制小时的数字表示
minute: %i 数字表示分钟
second: %s 数字表示秒
week: %w 数字表示星期（0=星期日, 6=星期六）

示例15：按照特定格式显示员工入职日期

select ename, hiredate, DATE_FORMAT(hiredate, '%Y年%m月%d日')
from emp;

image

3. 分组函数（重要）

分组函数：多行数据做为参数得到统计后的结果，一般用于数据统计

3.1 平均值

AVG(expr) 求表达式的平均值
示例16：计算所有员工月收入的平均值

select AVG(sal+IFNULL(comm,0))
from emp;

image

因为平均值不属于某个人，所以不要以下述方式进行查询

select ename, AVG(sal+IFNULL(comm,0))
from emp;

3.2 总数值

SUM(expr) 求表达式的总和

示例17：计算所有员工月薪总数

select SUM(sal) from emp;

image

3.3 计数值

COUNT(expr) 求符合表达式的记录的个数

示例18：计算员工人数

select COUNT(empno) from emp;

image

3.4 最大值

MAX(expr) 求表达式的最大值

示例19： 查询入职最晚的员工的入职日期

select MAX(hiredate) from emp;

image

3.5 最小值

MIN(expr) 求表达式的最小值

示例20： 查询最低月薪

select MIN(sal) from emp;

image

3.6 分组语句（重要）

group by... 以...进行分组统计
select...from...where...group by...order by...

示例21：查询各个部门的最高月薪

select deptno, MAX(sal)
from emp
group by deptno;

出现在group by中的列，可以写在select中，逻辑上是OK的

分组函数可以作用在多个列上，比如group by A,B 先按照A进行分组，A分组的内部再按照B分组

示例22：查询各部门内各职位的平均月薪

select deptno, job, AVG(sal)
from emp
group by deptno, job

image

3.7 分组函数的条件筛选

示例23： 查询平均月薪高于4000的部门编号和平均月薪数

下面的语句执行出错

select deptno, AVG(sal)
from emp
where AVG(sal) > 4000
group by deptno

原因是SQL语句执行顺序导致
SQL语句的执行顺序： from...where...group by...select...order by...
分组函数AVG是在group by期间完成计算，where语句执行时AVG还没有计算出结果，所以在where中不能使用分组函数！！

新的语句
having... 专门负责分组函数的条件筛选
标准书写顺序： select...from...where...group by...having...order by...
标准执行顺序： from...where...group by...having...select...order by...

MySQL执行顺序：from...where...group by...select...having...order by...

示例23的正确写法

select deptno, AVG(sal)
from emp
group by deptno
having AVG(sal) > 4000