SQL操作符是查询中的隐形工具，它们处理数据、比较条件，就像数据处理器一样。掌握这些操作符，能极大提升查询效率和准确性。本文将47个常用SQL操作符按场景拆解，从基础算术到高级JSON处理，助你快速上手。
47个SQL操作符全景概览
SQL操作符主要用于运算、比较或逻辑组合，是处理数据或条件关系的“工具性符号”。它们分为11大类，共计47个常用符号，涵盖了从基础计算到特殊场景的全面需求。
操作符分类列表
- 算术操作符：5个（
+、-、*、/、%）
- 比较操作符：12个（
=、<>、!=、<、>、<=、>=、BETWEEN ... AND ...、IN、NOT IN、LIKE、IS NULL、IS NOT NULL）
- 逻辑操作符：3个（
AND、OR、NOT）
- 位操作符：6个（
&、|、^、~、<<、>>）
- 字符串连接操作符：2个（
||、CONCAT()）
- 集合操作符：4个（
UNION、UNION ALL、INTERSECT、EXCEPT/MINUS）
- 注：
MINUS为EXCEPT的Oracle等价形式，合计4个
- 特殊比较操作符：3个（
IS TRUE、IS FALSE、IS DISTINCT FROM）
- JSON操作符：3个（
->、->>、#>）
- 空值安全操作符：4个（
<=>、COALESCE()、??、DIV）
- 类型转换操作符：1个（
::）
- 注：其他数据库以函数形式实现，仅PostgreSQL的
::为操作符
- 正则表达式操作符：4个（
REGEXP、RLIKE、~、~*）
下面，我们逐一深入解析这些操作符的核心用法与跨数据库差异。
1. 算术操作符
用于数值计算，所有主流数据库（如 MySQL、PostgreSQL、Oracle、SQL Server、SQLite等）均支持：
+：加法（如：a + b）-：减法（如：a - b）*：乘法（如：a * b）/：除法（如：a / b，注意整数除法可能返回整数，如：5/2在部分数据库返回2）%：取模（返回除法余数，如：5 % 2 结果为1）
2. 比较操作符
用于判断数据关系，所有主流数据库支持，核心功能一致：
- 基础比较：
=（等于）、<>或!=（不等于）、<（小于）、>（大于）、<=（小于等于）、>=（大于等于）
- 范围与列表：
BETWEEN ... AND ...：匹配指定范围（如：age BETWEEN 18 AND 30 等效于 age >=18 AND age <=30）IN：匹配列表中的任意值（如：status IN ('active', 'pending')）NOT IN：排除列表中的值（与IN逻辑相反）
- 模糊与空值：
LIKE：模糊匹配，%代表任意长度字符（包括0个），_代表单个字符（如：name LIKE '张%' 匹配“张三”、“张伟”）IS NULL：判断值为NULL（不能用 = NULL，NULL与任何值比较均为UNKNOWN）IS NOT NULL：判断值不为NULL
3. 逻辑操作符
用于组合条件判断，所有主流数据库支持：
AND：逻辑与（所有条件均满足时为真，如：age >18 AND status = 'active'）OR：逻辑或（至少一个条件满足时为真，如：age <18 OR vip = 1）NOT：逻辑非（取反条件，如：NOT (age > 60) 等效于 age <=60）
注：不同数据库对 !/&&/|| 的支持有差异，建议优先使用标准关键词（NOT/AND/OR）保证兼容性。XOR 运算符仅在部分数据库（如：MySQL）中直接支持。
4. 位操作符（部分数据库支持）
对整数按二进制位进行运算，语法一致但支持范围有限：
&：按位与（如：3 & 5 结果为1，二进制 011 & 101 = 001）|：按位或（如：3 | 5 结果为7，二进制 011 | 101 = 111）^：按位异或（如：3 ^ 5 结果为6，二进制 011 ^ 101 = 110）~：按位取反（如：~3 结果为-4，二进制补码取反）<< / >>：位左移/右移（如：5 << 1 结果为10，二进制 0101 左移1位为 1010）
注：支持MySQL、PostgreSQL、SQL Server。按位取反~的结果与数据库的整数存储方式相关，在 MySQL 中~3结果为-4（补码规则），但在 PostgreSQL 中若使用无符号整数类型，结果可能不同。
5. 字符串连接操作符（数据库差异明显）
用于拼接字符串，语法因数据库而异：
|| 操作符：支持PostgreSQL、Oracle、SQLite，示例：'Hello' || 'World' 结果为：“HelloWorld”CONCAT() 函数：支持MySQL、SQL Server，示例：CONCAT('Hello', 'World') 结果为：“HelloWorld”
注：MySQL中+不能用于字符串连接（会被当作数值相加），必须用CONCAT()。在MySQL中，如果操作数均为字符串，+会返回0，因此必须使用CONCAT()或CONCAT_WS()进行字符串拼接。
示例：
-- MySQL中：
'100' + 50 → 150（数值相加），'abc' + 50 → 50（非数字转0）
6. 集合操作符（通用逻辑，语法/支持度差异）
用于合并多个查询的结果集（要求列数、类型一致）：
UNION：合并结果并去重，所有主流数据库支持UNION ALL：合并结果并保留重复，所有主流数据库支持（效率高于UNION）INTERSECT：取两个结果集的交集（共有的行），注：支持Oracle、PostgreSQL、SQL Server；MySQL 8.0+支持EXCEPT/MINUS：取第一个结果集减去交集的差集，注：标准语法EXCEPT支持PostgreSQL、SQL Server；Oracle用MINUS；MySQL 8.0+支持EXCEPT
7. 特殊比较操作符（特定场景/数据库）
解决常规比较的局限性（如：NULL值判断）：
IS TRUE / IS FALSE：判断布尔值（如：active IS TRUE），所有数据库通用。IS DISTINCT FROM：严格比较（含NULL值），解决a <> b在a或b为NULL时返回UNKNOWN的问题：
- 示例：
a IS DISTINCT FROM b → 当a和b均为NULL时返回FALSE（而a <> b返回UNKNOWN）。IS DISTINCT FROM在PostgreSQL中非常实用，因为它能正确处理NULL值的比较。
- 注：支持PostgreSQL、SQLite；不支持MySQL、Oracle、SQL Server（需用
(a <> b OR a IS NULL <> b IS NULL)替代）。
8. JSON操作符（现代数据库支持）
用于查询JSON类型字段（需数据库支持JSON类型）：
->：获取JSON字段（返回JSON类型），注：MySQL 5.7+、PostgreSQL 9.3+支持。Oracle 12c+也支持JSON操作符，但语法略有不同。->>：获取JSON字段（返回字符串类型），注：MySQL 5.7+、PostgreSQL 9.3+支持#>：按路径获取JSON字段，注：PostgreSQL专有（如：data #> '{user, name}'）
示例：若data为JSON字段{"user": {"name": "张三"}}，则：
data->'user' 返回 {"name": "张三"}（JSON类型）；data->'user'->>'name' 返回 '张三'（字符串类型）。
9. 空值安全操作符（处理NULL值的特殊场景）
解决NULL值比较的歧义（NULL与任何值运算/比较均为NULL）：
<=>：空值安全等于（NULL=NULL返回TRUE），注：MySQL专有（如：a <=> b，替代a = b且支持NULL比较）。COALESCE()：返回参数列表中第一个非NULL值，所有数据库通用（如：COALESCE(a, b, 0) → 若a为NULL则取b，b也为NULL则取0）??：空值合并（等效COALESCE(a, b)），注：SQLite专有（如：a ?? b → 若a为NULL则返回b）DIV：整数除法，忽略NULL（如：a DIV b，若 b 为NULL，结果为0）。注：仅MySQL支持。
10. 类型转换操作符（数据库差异）
隐式转换数据类型：
::：PostgreSQL的隐式转换语法（如：'123'::INT）。
注：仅PostgreSQL支持。其他数据库的类型转换方式：
- MySQL：
CAST('123' AS SIGNED)
- SQL Server：
CAST('123' AS INT)
- Oracle：
TO_NUMBER('123')
11. 正则表达式操作符（部分数据库支持）
用于复杂模式匹配：
REGEXP / RLIKE：正则匹配（如：name REGEXP '^张' 匹配所有姓“张”的人）。~ / ~*：PostgreSQL的正则操作符（~区分大小写，~*不区分）。
注：REGEXP：MySQL、SQL Server支持；Oracle用 REGEXP_LIKE() 函数。RLIKE：MySQL别名，功能同 REGEXP。~ / ~*：PostgreSQL专有。SQLite 3.9.0+也支持正则表达式，但需要通过REGEXP函数实现。
常用SQL操作符的基础应用
1. 算术操作符（数值计算核心）
-
+ (加法/拼接)
-- 数值加法
SELECT price + tax FROM orders;
-- 字符串拼接（Oracle用 ||，MySQL/PostgreSQL 支持 || 或 CONCAT）
SELECT first_name + ' ' + last_name FROM users; -- SQL Server 语法
差异：Oracle 只能用 || 拼接字符串，+ 仅做加法。
-
- (减法)
SELECT salary - 1000 AS net_salary FROM employees;
-
*`` (乘法)**
SELECT quantity * unit_price AS total FROM order_details;
-
/ (除法)
SELECT total_sales / 12 AS monthly_avg FROM reports;
注意：5/2 在 SQLite 中返回 2（整数截断），在 PostgreSQL 中返回 2.5（精确除）。多数数据库整数除法会截断小数部分。
-
% (取模/余数)
SELECT 9 % 4; -- MySQL/PostgreSQL 返回 1
SELECT MOD(9,4) FROM dual; -- Oracle 语法
2. 比较操作符（条件过滤基础）
-
= / <> (等于/不等于)
SELECT * FROM users WHERE status = 'active';
SELECT * FROM products WHERE category_id <> 5; -- 标准语法
注意：!= 是非标准操作符，部分数据库（如：Oracle）可能不支持。
-
> / < / >= / <= (大小比较)
SELECT name FROM students WHERE age >= 18;
-
BETWEEN (闭区间范围)
SELECT * FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31';
-- 等价于 order_date >= '2024-01-01' AND order_date <= '2024-12-31'
-
IN / NOT IN (集合匹配)
SELECT * FROM employees WHERE id IN (101, 205, 309);
SELECT * FROM customers WHERE country NOT IN ('US','JP');
陷阱：NOT IN 列表含 NULL 时条件永不为真（如：country NOT IN ('US', NULL)）。
-
LIKE (模糊匹配)
SELECT title FROM books WHERE title LIKE 'SQL%'; -- 以SQL开头
SELECT phone FROM contacts WHERE phone LIKE '1__-___-____'; -- 1开头的11位号码
转义：ESCAPE 可定义通配符转义（如：LIKE '100#%' ESCAPE '#' 匹配 100%）。
-
IS NULL (空值判断)
SELECT * FROM orders WHERE discount_code IS NULL; -- 正确写法
-- WHERE discount_code = NULL 错误！始终返回空结果集
3. 逻辑操作符（条件组合逻辑）
-
AND (逻辑与)
SELECT * FROM staff
WHERE department = 'Sales' AND salary > 5000; -- 必须同时成立
-
OR (逻辑或)
SELECT * FROM flights
WHERE departure_city = 'Shanghai' OR arrival_city = 'Shanghai';
-
NOT (逻辑非)
SELECT * FROM users WHERE NOT is_deleted; -- 排除已删除用户
-
特殊操作符（子查询关联）
-- EXISTS: 子查询是否返回记录
SELECT * FROM departments d
WHERE EXISTS (SELECT 1 FROM employees e WHERE e.dept_id = d.id);
-- IN 的等效写法
SELECT * FROM products
WHERE category_id IN (SELECT id FROM categories WHERE is_active = 1);
### 4. 位操作符（特定场景使用）
```sql
-- 权限系统示例（假设 flags 是整数位掩码）
SELECT user_id FROM permissions
WHERE flags & 4 = 4; -- 检查第3位是否开启（读权限）
常用SQL操作符的优先级
1. 优先级顺序（从高到低）
() > 算术运算（*,/,%） > 算术运算（+,-） > 比较操作符（=,>,<...） > NOT > AND > OR
2. 优先级金字塔（从高到低）
1. 括号 ()
2. 算术运算符： * / %
3. 算术运算符： + -（正负号）
4. 算术运算符： + -（加减）
5. 位运算符： ~（按位取反）
6. 位运算符： &（按位与）
7. 位运算符： ^（按位异或）
8. 位运算符： |（按位或）
9. 比较运算符： =, >, <, >=, <=, <>, BETWEEN, LIKE, IN, IS NULL
10.逻辑非： NOT
11.逻辑与： AND
12.逻辑或： OR
示例1
SELECT * FROM employees
WHERE salary > 5000 OR department = 'HR' AND hire_date < '2020-01-01';
实际逻辑：salary > 5000 OR (department = 'HR' AND hire_date < '2020-01-01')
因 AND 优先级高于 OR。若需逻辑或优先，必须加括号：
WHERE (salary > 5000 OR department = 'HR') AND hire_date < '2020-01-01';
示例2
SELECT * FROM products
WHERE (category_id = 5 OR discount > 20)
AND NOT is_discontinued
AND price * 0.8 < 100;
执行顺序：
price * 0.8（算术运算）< 100（比较运算）NOT is_discontinued（逻辑非）(category_id = 5 OR discount > 20)（括号优先）AND连接所有条件
3. 跨数据库优先级差异
算术操作符优先级基本一致（* / % > + -），但取模操作符 % 在部分数据库（如：SQL Server）中与 * / 优先级相同，建议用括号明确：
SELECT 10 * 5 % 3; -- 结果可能是 (10*5)%3=50%3=2，或 10*(5%3)=10*2=20
-- 安全写法：
SELECT (10 * 5) % 3;
操作符 vs 关键字的重合边界
SQL 关键字是用于定义 SQL 语句结构的保留词，如：SELECT、FROM、WHERE 等。SQL 操作符用于比较和逻辑运算，如：=、>、AND、OR。关键字和操作符共同构成 SQL 查询的基础，用于数据检索、过滤和操作。
1. 对比示例
-- 示例1
-- 操作符主导表达式
SELECT (unit_price * quantity) * (1 - discount) AS net_total -- 算术操作符
FROM order_details
WHERE status IN ('shipped','delivered') OR is_priority = 1; -- 比较+逻辑操作符
-- 关键字定义语句框架
UPDATE products
SET stock = stock - 10 -- SET 关键字引导赋值
WHERE id = 100; -- WHERE 关键字引导条件
-- 示例2
-- 操作符主导的表达式（产生新值）
SELECT
unit_price * quantity * (1 - discount) AS net_total, -- 算术操作符
CASE WHEN quantity > 100 THEN 1 ELSE 0 END AS bulk_flag -- CASE内部操作符
FROM orders;
-- 关键字控制的语句结构（无返回值）
SELECT product_name -- SELECT子句
FROM inventory -- FROM子句
WHERE stock_count > 0 -- WHERE子句
ORDER BY last_restock_date; -- ORDER BY子句
2. 特殊元素的双重身份
-
BETWEEN / LIKE
-
CASE 表达式
3. 区分原则
操作符处理数据单元间关系（如：price * quantity）。
关键字指挥数据库如何操作数据（如：GROUP BY, ORDER BY, JOIN）。
二者如同句子中的"动词"和"连接词"，共同构建完整的SQL语义。
常用SQL操作符的进阶应用
1. 算术操作符：数值计算核心
（1）加法（+）与拼接陷阱
-- 数值加法（通用）
SELECT unit_price + shipping_fee AS total_cost FROM orders;
-- 字符串拼接（数据库差异）
/* SQL Server */ SELECT first_name + ' ' + last_name AS full_name FROM employees;
/* MySQL/Oracle */ SELECT CONCAT(first_name, ' ', last_name) AS full_name;
/* PostgreSQL */ SELECT first_name || ' ' || last_name AS full_name;
注意：SQL Server中'100' + 50返回150（隐式转换），MySQL返回'10050'（字符串拼接）
（2）除法精度问题实战
-- 整数截断（MySQL/SQL Server）
SELECT 5/2; -- 返回2
-- 精确计算解决方案
SELECT 5.0/2; -- 返回2.5（SQL Server）
SELECT CAST(5 AS FLOAT)/2; -- 通用方案
SELECT 5/2.0; -- PostgreSQL/MySQL返回2.5
（3）取模运算的跨库方案
-- 通用写法（使用函数）
SELECT MOD(17,5); -- 所有数据库支持函数形式
-- 操作符方案
SELECT 17 % 5; -- MySQL/PostgreSQL/SQL Server支持
2. 比较操作符：数据过滤基石
（1）BETWEEN的日期边界陷阱
-- 错误示例（包含2023-12-31 23:59:59）
SELECT * FROM orders
WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31';
-- 正确方案
SELECT * FROM orders
WHERE order_date >= '2023-01-01'
AND order_date < '2024-01-01'; -- 确保不包含次年
（2）NOT IN的NULL灾难
-- 危险查询（当subquery包含NULL时永远返回空）
SELECT * FROM products
WHERE category_id NOT IN (
SELECT category_id FROM discontinued_categories -- 此子查询可能返回NULL
);
-- 安全替代方案
SELECT * FROM products p
WHERE NOT EXISTS (
SELECT 1 FROM discontinued_categories d
WHERE d.category_id = p.category_id
);
（3）LIKE通配符高级用法
-- 匹配带百分号的文件名
SELECT * FROM documents
WHERE filename LIKE 'report\_2023%.pdf' ESCAPE '\';
-- 手机号模糊匹配
SELECT * FROM users
WHERE phone LIKE '138________'; -- 138开头+8位数字
3. 逻辑操作符：组合条件艺术
（1）AND/OR优先级陷阱
-- 错误逻辑（先计算AND）
SELECT * FROM employees
WHERE department = 'IT' OR department = 'HR' AND salary > 8000;
/* 等价于：IT部门全部员工 或 HR部门且薪资>8000的员工 */
-- 正确加括号方案
SELECT * FROM employees
WHERE (department = 'IT' OR department = 'HR')
AND salary > 8000; -- 两个部门中高薪员工
（2）EXISTS性能优化实战
-- 低效IN写法
SELECT * FROM customers
WHERE id IN (SELECT customer_id FROM large_orders_table);
-- 高效EXISTS写法
SELECT * FROM customers c
WHERE EXISTS (
SELECT 1 FROM large_orders_table o
WHERE o.customer_id = c.id
LIMIT 1 -- 找到第一条即停止
);
4. 位操作符：状态管理利器
-- 权限系统实战（权限位：1=读，2=写，4=执行）
/* 检查读写权限 */
SELECT user_id FROM permissions
WHERE (access_flags & 3) = 3; -- 同时具备1和2位
/* 添加执行权限 */
UPDATE permissions
SET access_flags = access_flags | 4
WHERE user_id = 1001;
/* 移除写权限 */
UPDATE permissions
SET access_flags = access_flags & ~2
WHERE user_id = 1002;
跨数据库操作符差异解决方案
| 场景 |
通用解决方案 |
特殊语法说明 |
| 字符串拼接 |
CONCAT(str1, str2) |
MySQL：CONCAT()必需；Oracle：只能用|| |
| 空值安全等于 |
a IS NOT DISTINCT FROM b |
MySQL：a <=> b；Oracle：需用NVL(a,-1)=NVL(b,-1) |
| 取模运算 |
MOD(dividend, divisor) |
MySQL/SQL Server：%支持 |
| 正则匹配 |
REGEXP_LIKE(str, pattern) |
MySQL：REGEXP；PostgreSQL：~；SQLite：需扩展支持 |
| 整数除法 |
CAST(a AS FLOAT)/b |
MySQL：DIV返回整数；SQL Server：a/b自动取整 |
常用SQL操作符的高阶应用
1. NULL安全处理三原则
-- 危险：与NULL比较永远返回未知
SELECT * FROM users WHERE phone = NULL; -- 错误！
-- 方案1：显式NULL检查
SELECT * FROM orders WHERE coupon_code IS NULL;
-- 方案2：COALESCE转换
SELECT * FROM products
WHERE COALESCE(discount, 0) > 10; -- NULL视为0
-- 方案3：NULLIF避免除零错误
SELECT revenue / NULLIF(order_count, 0) AS avg_revenue;
2. 性能优化四准则
-- 准则1：用UNION ALL替代OR（索引优化）
SELECT * FROM log WHERE type = 'login'
UNION ALL
SELECT * FROM log WHERE type = 'logout';
-- 准则2：避免函数包装索引列
/* 低效 */ SELECT * FROM users WHERE YEAR(created_at) = 2023;
/* 高效 */ SELECT * FROM users WHERE created_at >= '2023-01-01' AND created_at < '2024-01-01';
-- 准则3：LIKE左通配优化
/* 无法用索引 */ SELECT * FROM products WHERE name LIKE '%pro%';
/* 可用索引 */ SELECT * FROM products WHERE name LIKE 'pro%';
-- 准则4：批量更新避坑
UPDATE orders SET status = 'processed' WHERE id IN (1,3,5...500); -- IN列表过长
/* 改用临时表 */
CREATE TEMP TABLE ids_to_update(id INT PRIMARY KEY);
INSERT INTO ids_to_update VALUES (1),(3),...(500);
UPDATE orders o JOIN ids_to_update t ON o.id = t.id
SET o.status = 'processed';
3. 类型转换防御策略
-- 隐式转换风险示例
SELECT * FROM transactions WHERE trx_id = '10012'; -- 字符串转数字
-- 防御方案1：严格匹配类型
SELECT * FROM transactions WHERE trx_id = 10012;
-- 防御方案2：显式转换
SELECT * FROM user_actions
WHERE CAST(action_time AS DATE) = '2023-06-01'; -- 可控转换
4. 动态定价算法
SELECT product_id,
base_price *
CASE
WHEN inventory_level < 10 THEN 1.2 -- 低库存溢价
WHEN DATEDIFF(now(), release_date) > 365 THEN 0.8 -- 旧款折扣
ELSE 1.0
END *
(1 - COALESCE(member_discount, 0)) AS dynamic_price
FROM products
WHERE is_active = 1;
5. 多层级权限校验
SELECT u.user_id,
MAX(CASE WHEN p.access_flags & 1 = 1 THEN 1 ELSE 0 END) AS can_read,
MAX(CASE WHEN p.access_flags & 2 = 2 THEN 1 ELSE 0 END) AS can_write
FROM users u
LEFT JOIN permission_groups pg ON u.group_id = pg.id
LEFT JOIN global_permissions p ON pg.permission_mask & p.mask > 0
GROUP BY u.user_id;
总结：SQL操作符实践指南
-
防御性编码
- 始终用
IS NULL/IS NOT NULL处理空值
- 显式类型转换优于隐式转换
- 除法运算明确处理小数
-
优先级管理
- 多用括号明确优先级，复杂逻辑必须使用括号
AND优先级高于OR需警惕- 按位运算符顺序需测试验证
-
跨库兼容
- 拼接注意数据库方言，用
CONCAT()替代+
- 取模用
MOD()函数
- 分页语法封装在抽象层
-
性能意识
EXISTS替代IN处理子查询LIKE左通配规避全表扫描- 函数包装索引列为禁忌
掌握这47个SQL操作符，就像拥有了一套万能工具集。无论是基础数值计算还是高级数据处理，都能游刃有余。合理运用这些符号，结合括号明确优先级，并注意跨数据库差异，你的查询效率将显著提升。想了解更多数据库技术深度内容？欢迎访问云栈社区探索更多资源。
发表于 昨天 10:26
|
查看: 11|
回复: 0
