个人技术分享

MySQL常用函数

在数据库中，函数是一种能够接收参数并返回单个值的数据库对象。函数可以在 SQL 查询中用于执行特定的计算、转换数据或执行其他操作。数据库系统通常提供了许多内置函数，同时也允许用户创建自定义函数以满足特定需求。这里就只对一些常用的内置函数进行一下简单的介绍：

假如我们有一张表，表名为 example_table，如下所示：

+----+---------------+----------------+-------------+-------------+--------------+
| id | string_column | numeric_column | date_column | bool_column | array_column |
+----+---------------+----------------+-------------+-------------+--------------+
|  1 | Apple         |          10.50 | 2024-06-10  |           1 | [1, 2, 3]    |
|  2 | Banana        |          20.75 | 2024-06-11  |           0 | [4, 5, 6]    |
|  3 | Orange        |          15.00 | 2024-06-12  |           1 | [7, 8, 9]    |
+----+---------------+----------------+-------------+-------------+--------------+

• CONCAT()：将 string_column 和 numeric_column 连接起来。

  SELECT CONCAT(string_column, ' - $', numeric_column) AS concatenated_string 
  FROM example_table;
  
  +---------------------+
  | concatenated_string |
  +---------------------+
  | Apple - $10.50      |
  | Banana - $20.75     |
  | Orange - $15.00     |
  +---------------------+
  3 rows in set (0.00 sec)
  

• UPPER() / LOWER()：将 string_column 转换为大写和小写。

  SELECT UPPER(string_column) AS uppercase_string, 
  LOWER(string_column) AS lowercase_string 
  FROM example_table;
  
  +------------------+------------------+
  | uppercase_string | lowercase_string |
  +------------------+------------------+
  | APPLE            | apple            |
  | BANANA           | banana           |
  | ORANGE           | orange           |
  +------------------+------------------+
  3 rows in set (0.00 sec)
  

• LENGTH()：计算 string_column 的长度。

   SELECT string_column, LENGTH(string_column) AS string_length 
   FROM example_table;
   
  +---------------+---------------+
  | string_column | string_length |
  +---------------+---------------+
  | Apple         |             5 |
  | Banana        |             6 |
  | Orange        |             6 |
  +---------------+---------------+
  3 rows in set (0.00 sec)
  

• ROUND()：对 numeric_column 进行四舍五入。

  SELECT numeric_column, ROUND(numeric_column) AS rounded_number 
  FROM example_table;
  
  +----------------+----------------+
  | numeric_column | rounded_number |
  +----------------+----------------+
  |          10.50 |             11 |
  |          20.75 |             21 |
  |          15.00 |             15 |
  +----------------+----------------+
  3 rows in set (0.00 sec)
  

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

  SELECT CURDATE();
  
  +------------+
  | CURDATE()  |
  +------------+
  | 2024-06-10 |
  +------------+
  1 row in set (0.00 sec)
  

• DATE_FORMAT()：格式化 date_column。

  SELECT date_column, DATE_FORMAT(date_column, '%W, %M %e, %Y') AS formatted_date 
  FROM example_table;
  
  +-------------+--------------------------+
  | date_column | formatted_date           |
  +-------------+--------------------------+
  | 2024-06-10  | Monday, June 10, 2024    |
  | 2024-06-11  | Tuesday, June 11, 2024   |
  | 2024-06-12  | Wednesday, June 12, 2024 |
  +-------------+--------------------------+
  3 rows in set (0.00 sec)
  

• IF()：根据 bool_column 返回不同的值。

  SELECT bool_column, IF(bool_column, 'Yes', 'No') AS result 
  FROM example_table;
  
  +-------------+--------+
  | bool_column | result |
  +-------------+--------+
  |           1 | Yes    |
  |           0 | No     |
  |           1 | Yes    |
  +-------------+--------+
  3 rows in set (0.00 sec)
  

• CASE WHEN：根据条件返回不同的值。

  SELECT 
      numeric_column,
      CASE 
          WHEN numeric_column > 15 THEN 'High'
          WHEN numeric_column > 10 THEN 'Medium'
          ELSE 'Low'
      END AS value_category
  FROM example_table;
  
  +----------------+----------------+
  | numeric_column | value_category |
  +----------------+----------------+
  |          10.50 | Medium         |
  |          20.75 | High           |
  |          15.00 | Medium         |
  +----------------+----------------+
  3 rows in set (0.00 sec)
  

由于函数众多，我们将不再逐一列举。