详细分析Mysql常用函数（附Demo）

前言

由于实战中经常运用，索性来一个总结文

创建一个名为 employees 的表，包含以下字段：

employee_id：员工ID，整数类型
first_name：员工名，字符串类型
last_name：员工姓，字符串类型
salary：工资，整数类型
hire_date：入职日期，日期类型

具体创建表格内容：

CREATE TABLE employees (
    employee_id INT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    salary INT,
    hire_date DATE
);

数据内容：

INSERT INTO employees (employee_id, first_name, last_name, salary, hire_date) VALUES
(1, 'Alice', 'Smith', 60000, '2020-03-15'),
(2, 'Bob', 'Johnson', 75000, '2019-07-20'),
(3, 'Charlie', 'Brown', 50000, '2021-01-10'),
(4, 'David', 'Lee', 80000, '2018-11-05'),
(5, 'Emma', 'Garcia', 55000, '2022-05-28');

1. 聚合函数

以下函数比较简单，就不放结果图了

• COUNT()：计算行数或非空值的数量
  SELECT COUNT(*) AS total_employees FROM employees;
• SUM()：计算指定列的总和
  SELECT SUM(salary) AS total_salary FROM employees;
• AVG()：计算指定列的平均值
  SELECT AVG(salary) AS average_salary FROM employees;
• MIN()：找到指定列的最小值
  SELECT MIN(salary) AS min_salary FROM employees;
• MAX()：找到指定列的最大值
  SELECT MAX(salary) AS max_salary FROM employees;

2. 字符串函数

• CONCAT()：连接两个或多个字符串
  SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees;
  

• SUBSTRING()：从字符串中提取子字符串
  SELECT SUBSTRING(first_name, 1, 2) AS initials FROM employees;
  

• LENGTH()：计算字符串的长度
  SELECT first_name, LENGTH(first_name) AS name_length FROM employees;
  

• UPPER()：将字符串转换为大写
  SELECT UPPER(last_name) AS last_name_upper FROM employees;
  

• LOWER()：将字符串转换为小写
  SELECT LOWER(first_name) AS first_name_lower FROM employees;
  

• TRIM()：去除字符串两端的空格
  SELECT TRIM(last_name) AS last_name_trimmed FROM employees;
  

• REPLACE()：替换字符串中的子字符串
  SELECT REPLACE(first_name, 'a', 'A') AS replaced_name FROM employees;
  

3. 日期函数

• NOW()：返回当前日期和时间

• SELECT NOW() AS current_datetime;

• DATE()：从日期时间值中提取日期部分
  SELECT hire_date, DATE(hire_date) AS hire_date_only FROM employees;
  

• YEAR()：从日期中提取年份
  SELECT hire_date, YEAR(hire_date) AS hire_year FROM employees;

• MONTH()：从日期中提取月份
  SELECT hire_date, MONTH(hire_date) AS hire_month FROM employees;

• DAY()：从日期中提取天数
  SELECT hire_date, DAY(hire_date) AS hire_day FROM employees;

• DATEDIFF()：计算两个日期之间的天数差异
  SELECT hire_date, DATEDIFF(NOW(), hire_date) AS days_since_hire FROM employees;
  

4. 条件函数

• case结构：

SELECT
    employee_id,
    first_name,
    last_name,
    salary,
    CASE
        WHEN salary < 1000 THEN 'A'
        WHEN salary < 2000 THEN 'B'
        WHEN salary < 3000 THEN 'C'
        ELSE 'D'
    END AS salary_level
FROM employees;

截图如下：

• if结构如下：

SELECT
    employee_id,
    first_name,
    last_name,
    salary,
    IF(salary < 60000, '需要关注', '正常') AS status
FROM employees;

截图如下：

5. 数值函数

• ROUND()：四舍五入到指定的小数位数

SELECT
    employee_id,
    first_name,
    last_name,
    salary,
    ROUND(salary) AS rounded_salary
FROM employees;

• ABS()：返回数的绝对值

SELECT
    employee_id,
    first_name,
    last_name,
    salary,
    ABS(salary) AS absolute_salary
FROM employees;

• SQRT()：返回数的平方根

SELECT
    employee_id,
    first_name,
    last_name,
    salary,
    SQRT(salary) AS sqrt_salary
FROM employees;

以上三个执行代码，数据都一样，截图如下：

6. 类型转换函数

CAST()：将一个数据类型转换为另一个数据类型

（证书转换为浮点数，此处使用DECIMAL不是FLOAT）

SELECT
    employee_id,
    first_name,
    last_name,
    salary,
    CAST(salary AS DECIMAL) AS salary_decimal
FROM employees;

截图如下：

其他函数：
IFNULL()：如果表达式为 NULL，则返回替代值

SELECT
    employee_id,
    first_name,
    IFNULL(last_name, 'Unknown') AS last_name_fixed,
    salary,
    hire_date
FROM employees;