20250402_144040 mysql日期函数字符串函数时间函数分支结构

日期函数

在MySQL中，处理日期和时间有多种函数。以下是你需要的日期相关函数的详细说明和示例：

1. 求日期差

DATEDIFF(date1, date2)

• 功能：返回两个日期之间的天数差。
• 语法：DATEDIFF(date1, date2)
• 示例：

  SELECT DATEDIFF('2025-04-10', '2025-04-02') AS DiffDays;
  -- 结果: 8
  

2. 获得年

YEAR(date)

• 功能：提取日期中的年份部分。
• 语法：YEAR(date)
• 示例：

  SELECT YEAR('2025-04-02') AS YearPart;
  -- 结果: 2025
  

3. 获得月

MONTH(date)

• 功能：提取日期中的月份部分。
• 语法：MONTH(date)
• 示例：

  SELECT MONTH('2025-04-02') AS MonthPart;
  -- 结果: 4
  

4. 获得日

DAY(date)

• 功能：提取日期中的天数部分。
• 语法：DAY(date)
• 示例：

  SELECT DAY('2025-04-02') AS DayPart;
  -- 结果: 2
  

5. 日期格式化

DATE_FORMAT(date, format)

• 功能：根据指定的格式返回格式化的日期字符串。
• 语法：DATE_FORMAT(date, format)
• 常用格式说明：
  • %Y：四位数的年份 (例如：2025)
  • %m：两位数的月份 (01…12)
  • %d：两位数的日期 (01…31)
  • %H：两位数的小时 (00…23)
  • %i：两位数的分钟 (00…59)
  • %s：两位数的秒 (00…59)
• 示例：

  SELECT DATE_FORMAT('2025-04-02 15:30:00', '%Y-%m-%d %H:%i:%s') AS FormattedDate;
  -- 结果: '2025-04-02 15:30:00'
  
  SELECT DATE_FORMAT('2025-04-02', '%d/%m/%Y') AS FormattedDate;
  -- 结果: '02/04/2025'
  

其他相关函数

• CURDATE()：返回当前日期。

  SELECT CURDATE();
  -- 结果: '2025-04-02'
  

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

  SELECT NOW();
  -- 结果: '2025-04-02 15:30:00'
  

• TIMESTAMPDIFF(unit, datetime_expr1, datetime_expr2)：返回两个日期时间之间的差值，可以指定单位（如秒、分钟、小时、天、月、年等）。

  SELECT TIMESTAMPDIFF(DAY, '2025-04-02', '2025-04-10') AS DiffDays;
  -- 结果: 8
  

示例综合应用

假设有一个名为 orders 的表，包含 order_date 字段，以下是一些常见的日期操作示例：

1. 查询订单日期在2025年4月1日到2025年4月30日之间的订单：

   SELECT * FROM orders
   WHERE order_date BETWEEN '2025-04-01' AND '2025-04-30';
   

2. 获取每个月的订单数量：

   SELECT YEAR(order_date) AS Year, MONTH(order_date) AS Month, COUNT(*) AS OrderCount
   FROM orders
   GROUP BY YEAR(order_date), MONTH(order_date);
   

3. 格式化订单日期：

   SELECT DATE_FORMAT(order_date, '%d/%m/%Y') AS FormattedOrderDate
   FROM orders;
   

数学函数

在MySQL中，有许多数学相关的函数可以帮助你进行各种数值计算。以下是你需要的四个函数的详细说明和示例：

1. 向上取整

CEILING(X) 或 CEIL(X)

• 功能：返回大于或等于X的最小整数值，即向上取整。
• 语法：
  • CEILING(X)
  • CEIL(X)（CEIL是CEILING的简写）
• 示例：

  SELECT CEILING(3.2) AS CeilValue;
  -- 结果: 4
  
  SELECT CEIL(-3.7) AS CeilValue;
  -- 结果: -3
  

2. 向下取整

FLOOR(X)

• 功能：返回小于或等于X的最大整数值，即向下取整。
• 语法：
  • FLOOR(X)
• 示例：

  SELECT FLOOR(3.7) AS FloorValue;
  -- 结果: 3
  
  SELECT FLOOR(-3.2) AS FloorValue;
  -- 结果: -4
  

3. 四舍五入

ROUND(X, D)

• 功能：将数值X四舍五入到小数点后D位。如果省略D，则默认D为0，即四舍五入到最接近的整数。
• 语法：
  • ROUND(X)
  • ROUND(X, D)
• 示例：

  SELECT ROUND(3.456) AS RoundValue;
  -- 结果: 3
  
  SELECT ROUND(3.556) AS RoundValue;
  -- 结果: 4
  
  SELECT ROUND(3.456, 2) AS RoundValue;
  -- 结果: 3.46
  
  SELECT ROUND(-3.556) AS RoundValue;
  -- 结果: -4
  

4. 转百分号

MySQL没有专门用于将数值转换为百分比的函数，但你可以使用数学运算和字符串函数来实现这一点。以下是几种常见的方法：

a. 将数值转换为百分比字符串

假设你有一个数值0.1234，你想将其转换为"12.34%"：

SELECT CONCAT(ROUND(0.1234 * 100, 2), '%') AS Percentage;
-- 结果: '12.34%'

b. 使用FORMAT函数

FORMAT(X, D)函数可以将数值X格式化为带有D位小数的字符串：

SELECT CONCAT(FORMAT(0.1234 * 100, 2), '%') AS Percentage;
-- 结果: '12.34%'

c. 示例：计算百分比并格式化

假设你有一个销售表 sales，包含 sold 和 total 字段，你想计算销售百分比并格式化为字符串：

SELECT 
    CONCAT(ROUND((sold / total) * 100, 2), '%') AS SalesPercentage
FROM 
    sales;

其他相关函数

• ABS(X)：返回X的绝对值。

  SELECT ABS(-3.7) AS AbsoluteValue;
  -- 结果: 3.7
  

• POWER(X, Y)：返回X的Y次幂。

  SELECT POWER(2, 3) AS PowerValue;
  -- 结果: 8
  

• SQRT(X)：返回X的平方根。

  SELECT SQRT(16) AS SqrtValue;
  -- 结果: 4
  

示例综合应用

假设有一个名为 products 的表，包含 price 和 discount 字段，以下是一些常见的数学操作示例：

1. 计算每个产品的折扣价格（四舍五入到小数点后两位）：

   SELECT 
       price, 
       discount, 
       ROUND(price * (1 - discount), 2) AS DiscountedPrice
   FROM 
       products;
   

2. 计算每个产品的销售百分比，并格式化为字符串：

   SELECT 
       product_id, 
       sold, 
       total, 
       CONCAT(FORMAT((sold / total) * 100, 2), '%') AS SalesPercentage
   FROM 
       products;
   

3. 向上取整和向下取整示例：

   SELECT 
       price, 
       CEILING(price) AS CeilPrice, 
       FLOOR(price) AS FloorPrice
   FROM 
       products;
   

字符串相关用法

在MySQL中，字符串处理是非常常见的需求，MySQL提供了丰富的字符串函数来帮助你进行各种字符串操作。以下是关于字符串拼接和字符串切片的详细介绍和示例：

1. 字符串拼接

a. CONCAT(str1, str2, …)

• 功能：将多个字符串连接成一个字符串。如果任何一个参数为 NULL，则结果为 NULL。
• 语法：CONCAT(str1, str2, ...)
• 示例：

  SELECT CONCAT('Hello', ' ', 'World') AS Greeting;
  -- 结果: 'Hello World'
  
  SELECT CONCAT(first_name, ' ', last_name) AS FullName
  FROM employees;
  

b. CONCAT_WS(separator, str1, str2, …)

• 功能：使用指定的分隔符将多个字符串连接起来。CONCAT_WS 中的 WS 代表 With Separator。
• 语法：CONCAT_WS(separator, str1, str2, ...)
• 示例：

  SELECT CONCAT_WS(', ', 'John', 'Doe', 'New York') AS FullAddress;
  -- 结果: 'John, Doe, New York'
  
  SELECT CONCAT_WS(' - ', 'Category', 'Subcategory', 'Item') AS CategoryPath
  FROM products;
  

c. 使用 || 运算符（MySQL 8.0.19 及以上版本）

• 功能：在MySQL 8.0.19及更高版本中，可以使用 || 作为字符串连接运算符。
• 语法：str1 || str2
• 示例：

  SELECT 'Hello' || ' ' || 'World' AS Greeting;
  -- 结果: 'Hello World'
  

2. 字符串切片

字符串切片通常指的是从字符串中提取子字符串。在MySQL中，这主要通过 SUBSTRING 函数或其别名 SUBSTR 来实现。

a. SUBSTRING(str, pos) 或 SUBSTR(str, pos)

• 功能：从字符串 str 的位置 pos 开始提取子字符串，直到字符串结束。
• 语法：
  • SUBSTRING(str, pos)
  • SUBSTR(str, pos)
• 示例：

  SELECT SUBSTRING('Hello World', 7) AS SubstringResult;
  -- 结果: 'World'
  
  SELECT SUBSTR('Hello World', 7) AS SubstringResult;
  -- 结果: 'World'
  

b. SUBSTRING(str, pos, len) 或 SUBSTR(str, pos, len)

• 功能：从字符串 str 的位置 pos 开始，提取长度为 len 的子字符串。
• 语法：
  • SUBSTRING(str, pos, len)
  • SUBSTR(str, pos, len)
• 示例：

  SELECT SUBSTRING('Hello World', 7, 5) AS SubstringResult;
  -- 结果: 'World'
  
  SELECT SUBSTR('Hello World', 1, 5) AS SubstringResult;
  -- 结果: 'Hello'
  

c. SUBSTRING_INDEX(str, delim, count)

• 功能：返回字符串 str 中第 count 个分隔符 delim 之前的子字符串。如果 count 为正数，则返回左侧的子字符串；如果为负数，则返回右侧的子字符串。
• 语法：SUBSTRING_INDEX(str, delim, count)
• 示例：

  SELECT SUBSTRING_INDEX('www.example.com', '.', 2) AS SubstringResult;
  -- 结果: 'www.example'
  
  SELECT SUBSTRING_INDEX('www.example.com', '.', -1) AS SubstringResult;
  -- 结果: 'com'
  

其他相关函数

• LEFT(str, len)：从字符串 str 的左侧提取长度为 len 的子字符串。

  SELECT LEFT('Hello World', 5) AS LeftPart;
  -- 结果: 'Hello'
  

• RIGHT(str, len)：从字符串 str 的右侧提取长度为 len 的子字符串。

  SELECT RIGHT('Hello World', 5) AS RightPart;
  -- 结果: 'World'
  

• LENGTH(str)：返回字符串 str 的长度（以字节为单位）。

  SELECT LENGTH('Hello World') AS Length;
  -- 结果: 11
  

• CHAR_LENGTH(str)：返回字符串 str 的字符数。

  SELECT CHAR_LENGTH('Hello World') AS CharLength;
  -- 结果: 11
  

示例综合应用

假设有一个名为 employees 的表，包含 first_name 和 last_name 字段，以下是一些常见的字符串操作示例：

1. 拼接姓名：

   SELECT CONCAT(first_name, ' ', last_name) AS FullName
   FROM employees;
   

2. 提取姓氏的第一个字母：

   SELECT SUBSTRING(last_name, 1, 1) AS FirstLetterOfLastName
   FROM employees;
   

3. 格式化姓名：

   SELECT CONCAT(UPPER(SUBSTRING(first_name, 1, 1)), LOWER(SUBSTRING(first_name, 2)), ' ', UPPER(SUBSTRING(last_name, 1, 1)), LOWER(SUBSTRING(last_name, 2))) AS FormattedName
   FROM employees;
   

分支结构 case when

在MySQL中，CASE 表达式是一种多路分支控制结构，类似于其他编程语言中的 switch 或 if-else 语句。CASE 表达式允许你在查询中根据不同的条件返回不同的结果。它有两种形式：简单 CASE 表达式和搜索 CASE 表达式。下面将详细介绍这两种形式及其用法，并提供相应的示例。

1. 简单 CASE 表达式

语法：

CASE expression
    WHEN value1 THEN result1
    WHEN value2 THEN result2
    ...
    ELSE default_result
END

• 说明：expression 与每个 WHEN 子句中的 value 进行比较。如果匹配，则返回对应的 result。如果没有匹配的值，则返回 ELSE 子句中的 default_result。如果没有 ELSE 子句且没有匹配的值，则返回 NULL。

示例：

假设有一个 employees 表，包含 employee_id, name, 和 department_id 字段。我们希望根据 department_id 显示部门名称：

SELECT 
    employee_id,
    name,
    CASE department_id
        WHEN 1 THEN '人力资源'
        WHEN 2 THEN '财务'
        WHEN 3 THEN '技术'
        WHEN 4 THEN '市场'
        ELSE '其他部门'
    END AS department_name
FROM 
    employees;

在这个例子中，department_id 的值与 WHEN 子句中的值进行比较，并返回相应的部门名称。

2. 搜索 CASE 表达式

语法：

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE default_result
END

• 说明：每个 WHEN 子句包含一个条件。如果条件为真，则返回对应的 result。如果没有条件为真，则返回 ELSE 子句中的 default_result。如果没有 ELSE 子句且没有条件为真，则返回 NULL。

示例：

假设有一个 students 表，包含 student_id, name, 和 score 字段。我们希望根据 score 显示成绩等级：

SELECT 
    student_id,
    name,
    score,
    CASE
        WHEN score >= 90 THEN 'A'
        WHEN score >= 80 THEN 'B'
        WHEN score >= 70 THEN 'C'
        WHEN score >= 60 THEN 'D'
        ELSE 'F'
    END AS grade
FROM 
    students;

在这个例子中，score 的值与每个 WHEN 子句中的条件进行比较，并返回相应的成绩等级。

3. 在 ORDER BY 子句中使用 CASE

CASE 表达式也可以在 ORDER BY 子句中使用，以实现复杂的排序逻辑。例如，假设我们希望首先按部门排序，然后按工资降序排序，但某些部门需要特殊处理：

SELECT 
    employee_id,
    name,
    department_id,
    salary
FROM 
    employees
ORDER BY 
    CASE department_id
        WHEN 1 THEN 1
        WHEN 2 THEN 2
        ELSE 3
    END,
    salary DESC;

在这个例子中，department_id 为1和2的记录会首先排序，然后是其他部门，最后按工资降序排列。

4. 嵌套 CASE 表达式

CASE 表达式可以嵌套使用，以处理更复杂的条件逻辑。例如：

SELECT 
    employee_id,
    name,
    salary,
    CASE
        WHEN salary > 10000 THEN '高收入'
        WHEN salary > 5000 THEN 
            CASE
                WHEN department_id = 3 THEN '技术中收入'
                ELSE '其他中收入'
            END
        ELSE '低收入'
    END AS salary_level
FROM 
    employees;

在这个例子中，首先判断 salary 是否大于10000，然后判断是否大于5000，并进一步根据 department_id 进行分类。

总结

CASE 表达式在MySQL中是一个强大的工具，可以用于各种条件判断和数据转换。通过合理使用 CASE 表达式，可以使查询更加灵活和强大。以下是一些关键点：

• 简单 CASE 表达式：适用于基于单一表达式的多值比较。
• 搜索 CASE 表达式：适用于基于多个条件的复杂判断。
• 在 ORDER BY 中使用 CASE：可以实现复杂的排序逻辑。
• 嵌套 CASE 表达式：可以处理更复杂的条件逻辑。

分支结构 if语句

在MySQL中，IF 语句主要用于两种场景：

1. 在SQL查询中使用 IF 函数：用于条件判断并返回不同的值。
2. 在存储过程和函数中使用 IF 语句：用于控制流程，实现更复杂的逻辑。

下面将详细介绍这两种用法，并提供相应的示例。

---

1. 在SQL查询中使用 IF 函数

语法：

IF(condition, value_if_true, value_if_false)

• 说明：如果 condition 为真（非零），则返回 value_if_true；否则，返回 value_if_false。

示例：

假设有一个 employees 表，包含 employee_id, name, 和 salary 字段。我们希望根据 salary 判断员工是否为高薪：

SELECT 
    employee_id,
    name,
    salary,
    IF(salary > 10000, '高薪', '普通薪资') AS salary_level
FROM 
    employees;

在这个例子中，如果 salary 大于10000，则 salary_level 显示为 ‘高薪’；否则，显示为 ‘普通薪资’。

另一个示例：

假设有一个 students 表，包含 student_id, name, 和 score 字段。我们希望根据 score 判断学生是否及格：

SELECT 
    student_id,
    name,
    score,
    IF(score >= 60, '及格', '不及格') AS pass_status
FROM 
    students;

在这个例子中，如果 score 大于或等于60，则 pass_status 显示为 ‘及格’；否则，显示为 ‘不及格’。

使用 IF 函数进行数学运算：

SELECT 
    order_id,
    order_amount,
    IF(order_amount > 500, order_amount * 0.9, order_amount) AS final_amount
FROM 
    orders;

在这个例子中，如果 order_amount 大于500，则应用10%的折扣；否则，保持原价。

---

2. 在存储过程和函数中使用 IF 语句

语法：

IF condition THEN
    -- 执行的语句
[ELSEIF condition THEN
    -- 执行的语句]
[ELSE
    -- 执行的语句]
END IF;

• 说明：类似于其他编程语言中的 if-else 语句，用于在存储过程或函数中控制流程。

示例：

假设我们要创建一个存储过程，根据员工的 salary 调整其职位：

DELIMITER //

CREATE PROCEDURE UpdateEmployeePosition(IN emp_id INT)
BEGIN
    DECLARE emp_salary DECIMAL(10,2);

    SELECT salary INTO emp_salary FROM employees WHERE employee_id = emp_id;

    IF emp_salary > 15000 THEN
        UPDATE employees
        SET position = '高级经理'
        WHERE employee_id = emp_id;
    ELSEIF emp_salary > 10000 THEN
        UPDATE employees
        SET position = '经理'
        WHERE employee_id = emp_id;
    ELSE
        UPDATE employees
        SET position = '普通员工'
        WHERE employee_id = emp_id;
    END IF;
END //

DELIMITER ;

在这个例子中：

1. DELIMITER //：更改语句结束符，以便在存储过程中使用 ;。
2. CREATE PROCEDURE：创建一个名为 UpdateEmployeePosition 的存储过程，接受一个输入参数 emp_id。
3. DECLARE：声明一个变量 emp_salary 来存储员工的工资。
4. SELECT INTO：将查询结果赋值给变量 emp_salary。
5. IF, ELSEIF, ELSE：根据 emp_salary 的值更新员工的职位。
6. END IF：结束 IF 语句。
7. DELIMITER ;：恢复默认的语句结束符。

调用存储过程：

CALL UpdateEmployeePosition(123);

---

3. 使用 IF 语句的注意事项

• 返回值：IF 函数总是返回一个值，而 IF 语句用于控制流程，不返回值。
• 嵌套使用：可以在 IF 语句中嵌套另一个 IF 语句，以处理更复杂的条件。
• ELSEIF：在存储过程中，可以使用 ELSEIF 来处理多个条件。

嵌套示例：

SELECT 
    employee_id,
    name,
    salary,
    IF(salary > 15000, '高级经理',
        IF(salary > 10000, '经理', '普通员工')) AS position
FROM 
    employees;

在这个例子中，首先判断 salary 是否大于15000，如果是，则返回 ‘高级经理’；否则，进一步判断是否大于10000，如果是，则返回 ‘经理’；否则，返回 ‘普通员工’。

---

总结

• IF 函数：适用于在SQL查询中进行简单的条件判断和值返回。
• IF 语句：适用于在存储过程和函数中进行复杂的流程控制。
• 灵活性：通过组合 IF, ELSEIF, 和 ELSE，可以实现复杂的逻辑判断。