03-MySQL常见函数

小鱼儿 2022-09-08 03:37 154阅读 0赞

### 文章目录 ###

*   *  1. 单行函数
     *   *  1.1 字符函数
         *  1.2 数学函数
         *  1.3 日期函数
         *  1.4 流程控制函数
         *  1.5 其他函数
     *  2. 分组函数

--------------------

## 1. 单行函数 ##

### 1.1 字符函数 ###

**length**：获取字节个数

**concat**：字符串拼接

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

**upper、lower**：字符转换为大小写

SELECT UPPER('john');
    SELECT LOWER('john');
    
    # 将姓变大写，名变小写，然后拼接
    SELECT CONCAT(UPPER(last_name), LOWER(first_name)) 姓名
    FROM employees;

**substr**：截取子串（注意：索引从1开始）

# 截取从指定索引处开始的字符
    SELECT SUBSTR('ABCDEFGHIGKLMN', 7) out_put     'GHIGKLMN'
    
    # 截取从指定索引处开始指定字符长度的字符
    SELECT SUBSTR('ABCDEFGHIGKLMN', 1, 3) out_put   'ABC'
    
    # 姓名中首字母大写，其他字符小写然后用_拼接，显示出来
    SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),'_' LOWER(SUBSTR(last_name,2)))

**instr**：返回子串第一次出现的索引，如果找不到则返回0

INSTR('HelloWorld','W')         #6

**trim**：去**前后**指定的**空格和字符**

SELECT TRIM('a' FROM 'aaaaa123aaa456aaaaaaa') AS out_put;      #123aaa456

**ltrim**：去左边空格

**rtrim**：去右边空格

**replace**：替换字符串中指定字符

REPLACE('abcd', 'b', 'm');     #amcd

**lpad**：用指定的字符实现左填充指定长度

**rpad**：用指定的字符实现右填充指定长度

LPAD(salary, 10, '*')      #**********2400
    RPAD(salary, 10, '*')      #2400**********

### 1.2 数学函数 ###

**round**：四舍五入

ROUND(45.926, 2)  #45.93

**rand**：随机数

**floor**：向下取整

**ceil**：向上取整

**mod**：取余

MOD(a, b)原理：  a-a/b*b
    MOD(-10, -3):   -10-(-10)/(-3)*(-3)=-1
    
    # 被除数如果为正，结果为正，被除数为负，结果为负
    MOD(-10, -3)      -1
    MOD(10, -3)        1

**truncate**：截断，小数点后保留几位

TRUNCATE(45.926, 2)   # 45.92

### 1.3 日期函数 ###

**now**：获取当前系统**日期+时间**

**curdate**：获取当前系统**日期**

**curtime**：获取当前系统**时间**

# 可以获取指定的部分，年，月，日，小时，分钟，秒
    SELECT YEAR(NOW())  
    SELECT YEAR('1998-1-1')
    
    SELECT YEAR(hiredate) 年
    FROM employees;
    
    SELECT MONTH(NOW())

**str\_to\_date**：将日期格式的字符转换成指定格式的日期

STR_TO_DATE('9-13-1999','%m-%d-%Y')         #1999-09-13

**date\_format**：将日期转换成字符

DATE_FORMAT('2018/6/6','%Y年%m月%d日')       #2018年06月06日

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_Q1NETiBAU3dlZXRfcGlu_size_22_color_FFFFFF_t_70_g_se_x_16]  
![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAU3dlZXRfcGlu_size_11_color_FFFFFF_t_70_g_se_x_16]

# 查询入职日期为1992-4-3的员工信息
    SELECT *
    FROM employees
    WHERE hiredate='1992-4-3'
    
    SELECT *
    FROM employees
    WHERE hiredate=STR_TO_DATE('4-3 1992', '%c-%d %Y')

### 1.4 流程控制函数 ###

**if 处理双分支**

SELECT IF(10 < 5, '大'， '小');
    
    SELECT last_name,commission_pct, IF(commission_pct IS NULL, '没奖金'，'有奖金') 备注
    FROM employees;

**case语句 处理多分支**

（1）情况1：处理等值判断

java中
    switch(变量或表达式){
          case 常量1：
               语句1；
               break；
          ...
          default:语句；break；
    }
    
    mysql中
    case 要判断的字段或表达式
    when 常量1 then 语句1；
    when 常量2 then 语句2；
    ...
    else 语句n；
    end

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_Q1NETiBAU3dlZXRfcGlu_size_18_color_FFFFFF_t_70_g_se_x_16]

（2）情况2：处理条件判断  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_Q1NETiBAU3dlZXRfcGlu_size_15_color_FFFFFF_t_70_g_se_x_16]

### 1.5 其他函数 ###

version版本

database当前库

user当前连接用户

## 2. 分组函数 ##

**AVG() 平均值**

**SUM() 求和**

可以对**数值型数据**使用AVG 和SUM 函数。

SELECT AVG(salary), MAX(salary), MIN(salary), SUM(salary)
    FROM employees
    WHERE job_idLIKE '%REP%';

![在这里插入图片描述][79d4eb376f254eddaccdab046a75c3ff.png]

**MAX() 最大值**

**MIN() 最小值**

可以对**任意数据类型的数据**使用 MIN 和MAX 函数。

SELECT MIN(hire_date), MAX(hire_date)
    FROM employees;

![在这里插入图片描述][252432b0098e4e3dbd322016b7c7e351.png]

**COUNT() 计数**

（1）COUNT(\*) 返回表中记录总数，适用于**任意数据类型**。

SELECT COUNT(*)
    FROM employees
    WHERE department_id = 50;

（2）COUNT(expr) 返回expr不为空的记录总数。

SELECT COUNT(commission_pct)
    FROM employees
    WHERE department_id = 50;

特点：

1、以上五个分组函数都忽略null值，除了count(\*)

2、count的参数可以支持：字段、常量值，一般放1。建议使用 count(\*)

3、都可以搭配distinct使用，用于统计去重后的结果

SELECT SUM(DISTINCT salary), SUM(salary)
    FROM employees
    
    SELECT SUM(DISTINCT salary), COUNT(salary)
    FROM employees

测验题：

# 查询公司员工工资的最大值，最小值，平均值，总和
    SELECT MAX(salary), MIN(salary), ROUND(AVG(salary),2), SUM(salary)
    FROM employees;
    
    # 查询员工表中的最大入职时间和最小入职时间的相差天数
    SELECT DATEDIFF(MAX(hiredate), MIN(hiredate)) DIFFRENCE
    FROM employees;
    
    # 查询部门编号为90的员工个数
    SELECT COUNT(*)
    FROM employees
    WHERE department_id = 90;

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_Q1NETiBAU3dlZXRfcGlu_size_22_color_FFFFFF_t_70_g_se_x_16]: /images/20220829/cfd7bf1fb692417caa2ccfcf2a8fe3cf.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAU3dlZXRfcGlu_size_11_color_FFFFFF_t_70_g_se_x_16]: /images/20220829/c7c173af792a43b58c80502e86e42589.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_Q1NETiBAU3dlZXRfcGlu_size_18_color_FFFFFF_t_70_g_se_x_16]: /images/20220829/d0cb35762af44a01beaac5c0c8608fcb.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_Q1NETiBAU3dlZXRfcGlu_size_15_color_FFFFFF_t_70_g_se_x_16]: /images/20220829/7281803ab47b4504acbbd5591ce13137.png
[79d4eb376f254eddaccdab046a75c3ff.png]: /images/20220829/fcd4a8551fc54d448f5cae7290b46c1c.png
[252432b0098e4e3dbd322016b7c7e351.png]: /images/20220829/4cc6c1f482f144d7900d8155b400b54f.png