MySQL常用函数整理

在通过sql处理一些业务场景中，除了使用关联查询、子查询等一些常用sql方法外，还可以使用sql函数去处理，相比于在代码中直接处理，函数的使用清晰简单，往往能达到事半功倍的效果。

sql函数分类

常用SQL函数主要包括以下几类：数值类、字符串类、时间日期类、数据转换、流程控制。以下主要叙述以下流程控制类函数和字符串类函数，使用比较广泛，对于业务处理也最实用

一、流程控制

首先先列举一下比较运算符，可以使用在函数语句中

<=、>=、>、<>、
IS NULL、IS NOT NULL、
BETWEEN . . . AND. . . 、IN、NOT IN、LIKE、REGEXP（正则）
LEAST :当有两个或者多个参数时，返回其中的最小值。如果其中一个值为NULL，则
返回结果就为NULL。
GREATEST :当有两个或者多个参数时，返回其中的最大值。如果其中一个值为NULL.
则返回结果就为NULL。

1、判断值为null或空字符串

ISNULL(NAME)=1) //判断值为null，为NULL返回1，否则返回0
(LENGTH(trim(NAME))=0 //判断是否为空字符串，为空等于0，否则大于0

2、IF函数

语法：IF(表达式1,表达式2,表达式3)
如果表达式1为true，则执行表达式2，否则执行表达式3。类似于三目运算
作用于：select 查询语句：

SELECT
IF
	( ITASWITCH = 'Y', '是', '否' ) ITALEVEL 
FROM
	t_logsmgt_rule

返回结果：

作用于：where条件语句：

SELECT
	* 
FROM
	t_logsmgt_rule 
WHERE
IF
	( ITASWITCH = 'N', 1, 0 )

只是用于模拟，具体使用还是得结合业务条件，结果如下：

3、IFNULL函数

IFNULL() 函数用于判断第一个表达式是否为 NULL，如果为 NULL 则返回第二个参数的值，如果不为 NULL 则返回第一个参数的值。
语法：IFNULL(表达式, 备用值)
这个有点类似于IF(ISNULL(xx),xx,yy)。就不过的赘述了。

SELECT
	IFNULL( PID, ID ) 
FROM
	t_logsmgt_rule;

4、CASE函数

(1) 相当于switch case的作用

CASE开始，END结束
WHEN条件，TEHN执行符合条件的表达式
ELSE以上条件都不符合执行的表达式
最简单的例子：

SELECT NAME,account,
CASE
	sex 
	WHEN 1 THEN
	'男' 
	WHEN 0 THEN
	'女' ELSE '不男不女' 
	END AS sex 
FROM
	t_user

结果如下：

(2) 相当于if elseif的作用

SELECT NAME,account,
CASE
	
	WHEN LENGTH( trim( NAME ) ) > 4 THEN
	'名字长的' ELSE '名字短的' 
	END AS nameLen 
FROM
	t_user

case函数当然也可以作用于where子句中，例如

SELECT column1, column2
FROM table_name
WHERE
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
ELSE result3
END;

其中，condition1和condition2是条件表达式，result1、result2和result3是对应条件的结果。如果条件表达式为真，则返回对应的结果，否则返回ELSE子句中的结果。chatGBT告诉我的。。。

5、COALESCE函数

COALESCE 函数接受多个参数，返回其中第一个非 NULL 的参数值。

SELECT COALESCE(name, email, 'Unknown') AS contact FROM users;

在上面的示例中，如果 name 列的值不为空，则返回 name 列的值；如果 name 列的值为空，但 email 列的值不为空，则返回 email 列的值；如果 name 和 email 列的值都为空，则返回字符串 ‘Unknown’。

二、字符串类（GBT答案）

1、用于select、insert等子句中

例如，假设有一张名为"users"的表，包含了用户的姓名和邮箱信息。我们可以使用上述字符串函数对这些信息进行处理和查询，例如：

连接字符串：
SELECT CONCAT(first_name, ’ ', last_name) AS full_name
FROM users;

截取字符串：
SELECT SUBSTRING(email, 1, 5) AS domain
FROM users;

计算字符串长度：
SELECT LENGTH(first_name) AS name_length
FROM users;

替换字符串：
SELECT REPLACE(email, ‘@’, ‘at’) AS modified_email
FROM users;

这些例子只是展示了如何在MySQL表中使用字符串函数。在实际的数据处理中，我们可以根据具体的需求和情况使用不同的字符串函数，以提高数据处理效率。

2、用于where子句中

使用LIKE和CONCAT函数筛选名字以"John"开头的数据行：
SELECT *
FROM users
WHERE CONCAT(first_name, ’ ', last_name) LIKE ‘John%’;

使用LIKE和SUBSTRING函数筛选邮箱域名为"email.com"的数据行：
SELECT *
FROM users
WHERE SUBSTRING(email, -10) LIKE ‘email.com’;

INSTR(str, substr)
INSTR函数用于返回字符串中子字符串的位置，如果未找到则返回0。例如：

SELECT *
FROM users
WHERE INSTR(email, ‘john’) > 0; – 匹配包含"john"的邮箱

TRIM(str)
TRIM函数用于删除字符串中的空格或指定的字符。例如：

SELECT *
FROM users
WHERE TRIM(first_name) = ‘John’; – 匹配名字为"John"的用户，忽略前后的空格

ASCII(char)
ASCII函数返回给定字符的ASCII码值。例如：

SELECT *
FROM users
WHERE ASCII(SUBSTR(first_name, 1, 1)) = 65; – 匹配名字首字母为’A’的用户

其他函数

列举一下：
聚合函数（Aggregate Functions）：对一组数据进行聚合计算，如 SUM、AVG、MAX、MIN 等。
数学函数（Mathematical Functions）：执行数学计算，如 ABS、CEILING、FLOOR、LOG、POWER、ROUND、SQRT 等。
日期和时间函数（Date and Time Functions）：对日期和时间进行操作，如 CURDATE、CURTIME、DATE、TIME、YEAR、MONTH、DAY、HOUR、MINUTE、SECOND、NOW 等。