SQL 常用函数总结（二）

字符串处理函数

1. `CONCAT()`

函数功能：将两个或多个字符串合并成一个字符串。

函数语法：

CONCAT(string1, string2, ...)

string1、string2 等的数量可以是零个或多个，分别表示需要合并的字符串。

使用示例：

假设现在有一个名为 employees 的表格，其中包含员工的名字（first_name 和 last_name），需要将它们合并为一个字段（full_name）。

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

输出：

+---------------------+
|      full_name      |
+---------------------+
|      John Smith     |
|    Barbara Johnson  |
|      Mark Davis     |
+---------------------+

此示例使用了 CONCAT() 函数将 first_name 和 last_name 合并为一个名为 full_name 的新字段，并在两个字符串之间添加一个空格。

2. `SUBSTRING()`

函数功能：用于从字符串中提取子字符串。

函数语法：

SUBSTRING(string, start_position, length)

string 表示需要从中提取字符的字符串，start_position 表示子字符串在原始字符串中的起始位置（从 1 开始），length (可选) 表示需要提取的字符数。

如果省略 length 参数，则会提取从 start_position 开始的所有字符。

使用示例：

假设现在有一个名为 students 的表格，其中包含学生的姓名（name），需要从姓名字段中提取出每个学生的姓氏。

SELECT SUBSTRING(name, 1, 3) AS last_name FROM students;

输出：

+-----------+
| last_name |
+-----------+
|  Li       |
|  Wang     |
|  Zhang    |
+-----------+

此示例使用了 SUBSTRING() 函数从 name 字段中提取每个学生的姓氏，并将其作为新的 last_name 字段输出。

3. `LOWER()` 和 `UPPER()`

LOWER() 函数用于将字符串中的所有字母转换为小写字母。UPPER() 函数用于将字符串中的所有字母转换为大写字母。

函数语法：

LOWER(string)
UPPER(string)

string 表示需要转换的字符串。

使用示例：

假设现在有一个名为 users 的表格，其中包含用户的名字（name），需要将它们转换为小写。

SELECT LOWER(name) AS lowercase_name FROM users;

输出：

+-----------------------+
|    lowercase_name     |
+-----------------------+
|       john smith      |
|     barbara johnson   |
|       mark davis      |
+-----------------------+

此示例使用了 LOWER() 函数将 name 中的所有字符转换为小写，并将结果存储在名为 lowercase_name 的新字段中。

4. `LEFT()` 和 `RIGHT()`

函数功能：从左侧或右侧提取字符串中的字符。

函数语法：

LEFT(string, length)
RIGHT(string, length)

string 表示需要提取字符的字符串，length 表示从字符串左边或右边提取的字符数量。

使用示例：

假设现在有一个名为 books 的表格，其中包含书的标题（title），需要提取它们的前三个字符以及最后三个字符。

SELECT LEFT(title, 3) AS beginning, RIGHT(title, 3) AS end FROM books;

输出：

+-----------+--------+
| beginning |  end   |
+-----------+--------+
|   The     | man.   |
|   Mak     | must.  |
|   Pra     | be.    |
+-----------+--------+

此示例使用了 LEFT() 函数提取每个书的标题前三个字符，并使用 RIGHT() 函数提取标题的最后三个字符，并将它们分别存储在由 beginning 和 end 组成的新字段中。

5. `REVERSE()`

函数功能：将一个字符串反转。

函数语法：

REVERSE(string)

string 表示需要反转的字符串。

使用示例：

假设现在有一个名为 words 的表格，其中包含单词（word），需要将它们反转。

SELECT REVERSE(word) AS reversed_word FROM words;

输出：

+------------------+
|  reversed_word   |
+------------------+
|       gninrom    |
|       raebdnas   |
|       loopkcalb  |
+------------------+

此示例使用了 REVERSE() 函数将每个单词反转，并将结果存储在名为 reversed_word 的新字段中。

6. `LIKE`

函数功能：用于在 WHERE 子句中筛选特定模式的数据。

函数语法：

SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern

column_name 表示需要搜索的列名，table_name 表示需要搜索的表格名，pattern 表示需要匹配的模式，模式可以包括百分号（%）和下划线（_）。

% 表示匹配零个或多个字符。例如，“ %s” 会匹配以“s”结尾的任何字符串，例如“dogs”、“cats”、“bats”等。

_ 表示匹配一个字符。例如，“__a%” 会匹配长度为至少三个字符且以“a”作为第三个字符的任何字符串，如“lava”、“wava”、“mamma”、“papa”等。

使用示例：

假设现在有一个名为 products 的表格，其中包含产品的名称（product_name）和是否存在缺货的布尔值（out_of_stock），需要找出所有名称以“car”开头且没有缺货的产品。

SELECT product_name FROM products WHERE product_name LIKE 'car%' AND out_of_stock = 0;

输出：

+------------------+
|   product_name   |
+------------------+
|    Car Charger   |
|   Car Accessories|
+------------------+

此示例使用了 LIKE 运算符，以“car%”作为模式筛选出产品名称以“car”开头的记录，以及一个布尔条件筛选出没有缺货的产品。

7. `LENGTH()`

函数功能：返回字符串的长度（字符数）。

函数语法：

LENGTH(string)

string 表示需要计算长度的字符串。

使用示例：

假设现在有一个名为 messages 的表格，其中包含一些电子邮件消息（message），需要计算它们的长度。

SELECT LENGTH(message) AS message_length FROM messages;

输出：

+-----------------+
| message_length  |
+-----------------+
|        234      |
|        150      |
|        442      |
|        97       |
+-----------------+

此示例使用了 LENGTH() 函数计算每个邮件消息的长度，并将结果存储在名为 message_length 的新字段中。

8. `TRIM()`

函数功能：用于删除字符串开头和结尾的空格或其他指定字符。

函数语法：

TRIM( [ [ BINARY ] [ LEADING | TRAILING | BOTH ] [ characters ] FROM ] string )

string 表示需要修剪的字符串，BINARY 表示该函数将针对二进制字符串进行操作，LEADING 表示仅在字符串开头删除字符，TRAILING 表示仅在字符串结尾删除字符，BOTH 表示在字符串开头和结尾都删除字符， characters 表示需要删除的字符或字符序列。

如果省略 characters 参数，则默认删除空格。

使用示例：

假设现在有一个名为 customers 的表格，其中包含客户的名称（name），需要删除在名字开头和结尾的空格。

SELECT TRIM(name) AS trimmed_name FROM customers;

输出：

+---------------------+
|     trimmed_name    |
+---------------------+
|       John Smith    |
|     Barbara Johnson |
|       Mark Davis    |
+---------------------+

此示例使用了 TRIM() 函数删除每个客户姓名开头和结尾的空格，并将结果存储在名为 trimmed_name 的新字段中。

9. `REPLACE()`

函数功能：用一个字符串替换另一个字符串。

函数语法：

REPLACE(string, old_string, new_string)

string 表示需要执行替换操作的字符串，old_string 表示需要被替换的子字符串，new_string 表示用于替换的新字符串。

使用示例：

假设现在有一个名为 sentences 的表格，其中包含一些句子（sentence），需要将其中每个单词“the”替换为“a”。

SELECT REPLACE(sentence, 'the', 'a') AS new_sentence FROM sentences;

输出：

+-----------------------------+
|          new_sentence        |
+-----------------------------+
|    A quick brown fox jumps   |
|    over a lazy dog.          |
|    A stitch in time saves a  |
|    nine.                     |
+-----------------------------+

此示例使用了 REPLACE() 函数替换每个句子中的单词“the”，并将替换后的新句子存储在名为 new_sentence 的新字段中。

10. `LOCATE()`

LOCATE() 函数用于在指定字符串中查找子字符串，并返回它在字符串中的起始位置。它的语法格式为：

LOCATE(substring, string, position)

其中，substring 表示需要查找的子字符串，string 表示被查找的原始字符串，position 表示查找的起始位置。

如果找到了子字符串，则返回它在原始字符串中的起始位置，如果没有找到，则返回 0。
如果省略 position 参数，则从字符串开始查找子字符串。

以下是一个 LOCATE() 函数的示例：

假设有如下的 SQL 语句：

SELECT LOCATE('cd', 'abcdefg') AS position;

执行上述语句的结果应该为：

+----------+
| position |
+----------+
|       3  |
+----------+

在该示例中，LOCATE() 函数依次传入子字符串 'cd' 和原始字符串 'abcdefg'，并查找 'cd' 是否在 'abcdefg' 中出现。由于 'cd' 并不在 'abcdefg' 中出现，因此 LOCATE() 函数返回 0。

下面我们再看一个更真实的示例。假设有如下的 products 表格：

id	product_name	price
1	iPhone 13 - Jet Black	1000
2	Galaxy S21 - Phantom	900
3	Macbook Air - Silver	1200

现在我们想要分别提取 product_name 中的产品名称和颜色信息。我们可以使用 LOCATE() 函数和 SUBSTRING() 函数来实现：

SELECT SUBSTRING(product_name, 1, LOCATE(' - ', product_name)-1) AS product, 
SUBSTRING(product_name, LOCATE(' - ', product_name) + 3) AS color 
FROM products;

上述 SQL 语句将 product_name 字段分别以 ' - ' 为分隔符，提取出产品名称和颜色信息，并使用 SUBSTRING() 函数来去掉相应的分隔符。LOCATE() 函数返回分隔符在 product_name 中的起始位置，从而帮助 SUBSTRING() 函数正确提取相应的信息。执行上述 SQL 语句的结果如下：

product	color
iPhone 13	Jet Black
Galaxy S21	Phantom
Macbook Air	Silver

在上述结果中，产品名称和颜色信息已经正确分隔出来，并以不同的列分别显示。