常见函数

进阶4：常见函数

函数概念

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

好处：

• 隐藏了实现细节
• 提高代码的重用性

调用：select 函数名（实参列表）【from 表】；

特点：

• 叫什么（函数名）
• 干什么（函数功能）

分类：

• 单行函数，如concat、length、ifnull等
• 分组函数，主要功能做统计使用，又称为统计函数

单行函数

1、字符函数

（1）length 获取参数的字节各数

SELECT LENGTH('john')#返回是4
SELECT LENGTH('张三丰hahaha')#返回是15，因为汉字是3个字符，一个字母是一个字符

（2）concat 拼接字符串

SELECT 
  CONCAT(last_name, '_', first_name) 
FROM
  employees ;

结果：

（3）upper（变成大写）、lower（变成小写）

SELECT UPPER('john');#结果是JOHN
SELECT LOWER('joHn');结果是john

实例：将姓变成大写，名变成小写，然后拼接

SELECT 
  CONCAT(UPPER(last_name),LOWER(first_name))
FROM
  employees ;

结果：

（4）substr、substring
注意：索引是从1开始的

#截取从指定索引处后面所有字符
SELECT SUBSTR('李莫愁爱上了陆展元',7) out_put;#返回的是陆展元
#截取从指定索引处指定长度的字符
SELECT SUBSTR('李莫愁爱上了陆展元',1，3) out_put;#返回的是李莫愁

案例：姓名中首字母大写，其他字符小写然后用_拼接，显示出来

SELECT 
  CONCAT(
    UPPER(SUBSTR(last_name, 1, 1)),
    '_',
    LOWER(SUBSTR(last_name, 2))
  ) 
FROM
  employees ;

结果：

（5）instr
instr返回的是字符串第一次出现的索引，如果找不到返回的是0

SELECT INSTR('杨不悔爱上了殷六侠','殷六侠') AS out_put;#返回的是7

（6）trim
trim去字符串前后指定的东西

SELECT TRIM('   张翠山   ') AS out_put;#去前后的空格
SELECT TRIM('a' FROM 'aaaa张翠山aaaaa') AS out_put;#去字符串前后的a

（7）lpad 用指定的字符实现左填充指定长度

SELECT LPAD('殷素素',10,'*') AS out_put;

结果：

（8）rpad 用指定的字符实现右填充指定长度

SELECT RPAD('殷素素',12,'ab') AS out_put;

结果：

（9）replace 替换

SELECT REPLACE('张无忌爱上了周芷若','周芷若','赵敏');

结果：

2、数学函数

（1）round 四舍五入
保留整数：

保留指定位数：

（2）ceil 向上取整，返回>=该参数的最小整数

（3）floor 向下取整，返回<=该参数的最大整数

（4）truncate 截断

（5）mod 取余
mod(a,b) : a - a / b * b(其中a/b就去整)

3、日期函数

（1）now 返回当前系统日期+时间

（2）curdate 返回当前系统日期，不包含时间

（3）curtime 返回当前时间，不包含日期

（4）可以获取指定部分，年、月、日（day）、小时（hour）、分钟（minute）、秒（second）
年：year

月：month

（5）str_to_date:将日期格式的字符转换成指定格式的日期

（6）date_format:将日期转换成字符

4、其他函数

SELECT VERSION();#查看当前版本
SELECT DATABASE();#查看当前的库
SELECT USER();#查看当前的用户

5、流程控制函数

（1）if 函数：if else 的效果

（2）case 函数的使用一：switch case 的效果
case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1；
when 常量2 then 要显示的值2或语句2；
……
else 要显示的值n或语句n;
end

案例：查询员工的工资，要求部门号=30，显示的工资为1.1倍，部门号=40，显示的工资为1.2倍，部门号=50，显示的工资为1.3倍，其他部门，显示的工资为原工资

SELECT 
  salary 原始工资,
  department_id,
  CASE
    department_id 
    WHEN 30 
    THEN salary * 1.1 
    WHEN 40 
    THEN salary * 1.2 
    WHEN 50 
    THEN salary * 1.3 
    ELSE salary 
  END AS 新工资 
FROM
  employees ;

结果：

case 函数的使用二：类似于 多重If
case
when 条件1 then 要显示的值1或语句1
when 条件2 then 要显示的值2或语句2
……
else 要显示的值n 或语句n
end

案例：查询员工工资的情况，如果工资>20000,显示A级别，工资>15000，显示B级别，工资>10000,显示C级别，否则显示D级别。

SELECT 
  salary,
  CASE
    WHEN salary > 20000 
    THEN 'A' 
    WHEN salary > 15000 
    THEN 'B' 
    WHEN salary > 10000 
    THEN 'C' 
    ELSE 'D' 
  END AS 工资级别
FROM
  employees ;

结果：

分组函数

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

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

1、简单使用

SELECT SUM(salary) FROM employees;
SELECT AVG(salary) FROM employees;
SELECT MAX(salary) FROM employees;
SELECT MIN(salary) FROM employees;
SELECT COUNT(salary) FROM employees;

SELECT 
  SUM(salary) 总和,
  AVG(salary) 平均,
  MAX(salary) 最大,
  MIN(salary) 最小,
  COUNT(salary) 计数 
FROM
  employees ;

结果：

2、参数支持哪些类型

特点：
（1）sum 和 avg 一般只适合数值型，max、min、count可以处理任何类型
（2）以上的分组函数都忽略null值
（3）可以和distinct搭配实现去重的运算

SELECT 
  SUM(DISTINCT salary) 去重求和,
  SUM(salary) 求和 
FROM
  employees ;

结果：

（4）count函数的详细介绍

SELECT COUNT(salary) FROM employees;
SELECT COUNT(*) FROM employees;#一般用于统计行数      
SELECT COUNT(1) FROM employees;#统计1的个数，有多少行，就有多少个1

（5）和分组函数一同查询的字段有限制

SELECT 
  SUM(salary),
  employee_id 
FROM
  employees ;

结果：

3、测试

（1）查询公司员工工资的最大值，最小值，平均值，总和。

SELECT 
  MAX(salary) 最大值,
  MIN(salary) 最小值,
  AVG(salary) 平均值,
  SUM(salary) 总计 
FROM
  employees ;

结果：

（2）查询员工表中的最大入职时间和最小入职时间的相差天（DIFFRENCE）

SELECT 
  DATEDIFF(MAX(hiredate), MIN(hiredate)) 相差天数
FROM
  employees ;

#注：datadiff(日期1，日期2) 可以计算相差的天数

结果：

（3）查询部门编号为90的员工个数

SELECT 
  COUNT(*) 个数 
FROM
  employees 
WHERE department_id = 90 ;

结果：