SQL LIKE 实战：常见场景与案例分析 – wiki基地

作者： /

SQL LIKE 实战：常见场景与案例分析

在 SQL 查询中，LIKE 运算符是一个强大的工具，用于在 WHERE 子句中搜索指定模式的列。它允许我们进行模糊匹配，而不仅仅是精确匹配，这在处理文本数据时非常有用。本文将深入探讨 LIKE 运算符的各种用法、常见场景，并通过丰富的案例分析来帮助您掌握其实战技巧。

1. LIKE 运算符基础

LIKE 运算符用于在 WHERE 子句中比较一个列的值与一个指定的模式。它支持两个通配符：

  • % (百分号)： 表示零个、一个或多个字符。
  • _ (下划线)： 表示单个字符。

基本语法：

sql
SELECT column1, column2, ...
FROM table_name
WHERE columnN LIKE pattern;

示例：

假设我们有一个名为 employees 的表，其中包含 first_name 和 last_name 列。

  • 查找所有名字以 “J” 开头的员工：

    sql
    SELECT first_name, last_name
    FROM employees
    WHERE first_name LIKE 'J%';

  • 查找所有姓氏包含 “son” 的员工：

    sql
    SELECT first_name, last_name
    FROM employees
    WHERE last_name LIKE '%son%';

  • 查找名字以 “A” 开头且长度为 3 个字符的员工：

    sql
    SELECT first_name, last_name
    FROM employees
    WHERE first_name LIKE 'A__';

  • 查找名字第二个字母是”a”的员工：
    sql
    SELECT first_name, last_name
    FROM employees
    WHERE first_name LIKE '_a%';

2. 常见场景与案例分析

LIKE 运算符在各种场景中都非常有用，以下是一些常见的应用场景及案例分析：

2.1 模糊搜索

这是 LIKE 最常见的用途，允许用户根据部分信息查找数据。

案例： 在一个产品目录中，用户可能只记得产品名称的一部分，例如 “笔记本电脑”。

sql
SELECT product_name, description, price
FROM products
WHERE product_name LIKE '%笔记本电脑%';

这个查询将返回所有产品名称中包含 “笔记本电脑” 的产品，例如 “苹果笔记本电脑”、”联想笔记本电脑” 等。

2.2 查找特定模式

LIKE 可以用于查找符合特定模式的数据，例如特定格式的电话号码、邮箱地址或邮政编码。

案例： 查找所有以 “138” 开头的手机号码：

sql
SELECT phone_number
FROM customers
WHERE phone_number LIKE '138%';

案例：查找所有包含@example.com的邮箱地址：

“`sql
SELECT email
FROM users
WHERE email LIKE ‘%@example.com’;

“`
案例：查找所有美国邮编(格式为5个数字，例如90210)：

sql
SELECT zip_code
FROM addresses
WHERE zip_code LIKE '_____'; -- 五个下划线

2.3 数据清理

LIKE 可以帮助识别和清理数据中的不一致性或错误。

案例： 查找所有包含多余空格的产品名称：

sql
SELECT product_name
FROM products
WHERE product_name LIKE '% %'; -- 两个空格

这个查询可以帮助我们找到包含多个连续空格的产品名称，然后我们可以进一步清理这些数据。

案例：查找所有不符合邮箱格式的数据（简单示例，实际情况更复杂，需要更精准的正则表达式）

sql
SELECT email
FROM users
WHERE email NOT LIKE '%@%.%'; -- 粗略检查, 必须有@和.

2.4 数据分组与统计

LIKE 可以结合 GROUP BY 和聚合函数进行数据分组和统计。

案例： 统计每个姓氏以 “S” 开头的员工数量：

sql
SELECT COUNT(*) AS employee_count, LEFT(last_name, 1) AS first_letter
FROM employees
WHERE last_name LIKE 'S%'
GROUP BY LEFT(last_name, 1); -- 按照首字母分组

案例： 按产品名称的首字母分组，统计每组产品的数量：

sql
SELECT COUNT(*) AS product_count, SUBSTRING(product_name, 1, 1) AS first_letter
FROM products
GROUP BY SUBSTRING(product_name, 1, 1);

2.5 查找空值或非空值 (结合 IS NULL)

虽然 LIKE 主要用于文本匹配，但它可以与 IS NULL 或 IS NOT NULL 结合使用，来处理空值或非空值。

案例： 查找所有 description 字段不为空的记录：

“`sql
— 方法1: 使用 NOT LIKE ”
SELECT *
FROM products
WHERE description NOT LIKE ”;

— 方法2: 更常用的方法，使用 IS NOT NULL
SELECT *
FROM products
WHERE description IS NOT NULL;

“`

案例： 查找 description 字段为空的记录：

sql
SELECT *
FROM products
WHERE description IS NULL;

2.6 复杂模式匹配

LIKE 可以结合多个通配符和条件来实现更复杂的模式匹配。

案例： 查找所有以 “A” 或 “B” 开头，并且包含 “apple” 或 “banana” 的产品名称：

sql
SELECT product_name
FROM products
WHERE (product_name LIKE 'A%' OR product_name LIKE 'B%')
AND (product_name LIKE '%apple%' OR product_name LIKE '%banana%');

案例: 查找所有以”Dr.”或者”Mr.”开头的名字：
sql
SELECT name
FROM persons
WHERE name LIKE 'Dr.%' OR name LIKE 'Mr.%';

3. 转义特殊字符

如果需要搜索的模式中包含 _ 或 ％ 本身，我们需要使用转义字符。不同的数据库系统可能有不同的转义字符，常见的有：

  • 反斜杠 (\)： MySQL、PostgreSQL、SQLite 等。
  • ESCAPE 关键字： SQL Server、Oracle 等。

案例 (MySQL)： 查找所有包含 “_” 的产品名称：

sql
SELECT product_name
FROM products
WHERE product_name LIKE '%\_%'; -- 使用反斜杠转义

案例 (SQL Server)： 查找所有包含 “%” 的产品名称：

sql
SELECT product_name
FROM products
WHERE product_name LIKE '%[%]%' ESCAPE '['; -- 使用 ESCAPE 关键字和自定义转义字符
也可以写成如下：
sql
SELECT product_name
FROM products
WHERE product_name LIKE '%#%%' ESCAPE '#'; -- 使用 ESCAPE 关键字和自定义转义字符

通用转义方法(使用ESCAPE关键字):

“`sql
SELECT product_name
FROM products
WHERE product_name LIKE ‘%!_%’ ESCAPE ‘!’; — 查找包含_的产品名称，!作为转义符

SELECT product_name
FROM products
WHERE product_name LIKE ‘%!%%’ ESCAPE ‘!’; — 查找包含%的产品名称，!作为转义符
“`

4. 性能考虑

虽然 LIKE 很方便，但在使用时需要注意性能问题，尤其是在大型数据集上。

  • 前缀匹配最快： LIKE 'prefix%' 形式的查询通常比 %suffix% 或 %substring% 快，因为数据库可以使用索引来优化前缀匹配。
  • 避免在开头使用通配符： LIKE '%substring%' 形式的查询通常无法使用索引，会导致全表扫描，效率较低。 如果必须，可以考虑全文索引。
  • 减少通配符的使用： 通配符越多，匹配越慢。尽量使用更精确的模式。
  • 考虑全文索引： 对于大量的文本搜索，使用数据库的全文索引功能（如 MySQL 的 FULLTEXT 索引）通常比 LIKE 更高效。
  • 使用更精确的匹配: 如果可能, 使用 = 替代 LIKE。

5. 其他注意事项

  • 大小写敏感性： LIKE 运算符的比较是否区分大小写取决于数据库的配置和排序规则 (collation)。大多数数据库默认是不区分大小写的，但可以通过设置排序规则来改变。
  • 不同数据库的差异： 不同的数据库系统在 LIKE 的实现和支持上可能略有差异，例如转义字符、通配符的行为等。在编写跨数据库的 SQL 语句时，需要注意这些差异。

6. 总结

LIKE 运算符是 SQL 中一个非常实用的工具，可以帮助我们进行各种文本模式匹配。通过掌握其基本语法、通配符、转义字符以及常见的使用场景，我们可以更有效地处理和查询文本数据。同时，我们也需要注意性能问题，尽量避免使用低效的模式，并在必要时考虑使用全文索引等更高级的搜索技术。希望通过本文的详细介绍和案例分析，您能够更好地理解和运用 LIKE 运算符，提高 SQL 查询的效率和灵活性。

发表评论

您的邮箱地址不会被公开。 必填项已用 * 标注

滚动至顶部