GaussDB SQL基础语法示例:常见条件表达式详解
GaussDB SQL基础语法示例:常见条件表达式详解
条件表达式是SQL查询中控制数据筛选逻辑的核心要素。本文聚焦GaussDB支持的常见条件表达式,通过真实业务场景示例解析其用法与最佳实践。
一、基础比较运算符
- 等值与不等值判断
-- 精确匹配
SELECT user_id, username
FROM users
WHERE account_status = 'ACTIVE';
-- 不等值判断
SELECT product_name, unit_price
FROM products
WHERE stock_quantity <> 0; -- 排除库存为0的商品
- 范围查询(BETWEEN)
-- 查询有效会员
SELECT member_id, register_date
FROM members
WHERE registration_time BETWEEN '2023-01-01' AND '2023-12-31';
二、集合匹配表达式
- IN操作符
-- 多选一查询
SELECT order_id, payment_method
FROM orders
WHERE payment_method IN ('CREDIT_CARD', 'ALIPAY', 'WECHAT_PAY');
-- 结合子查询动态匹配
SELECT product_name
FROM products
WHERE category_id IN (
SELECT category_id
FROM categories
WHERE parent_id = 1001
);
- NOT IN排除查询
-- 排除特定记录
SELECT employee_name
FROM employees
WHERE department_id NOT IN (3, 5, 7)
AND employment_status = 'ACTIVE';
三、模糊匹配表达式
- LIKE通配符查询
-- 匹配以"A"开头的产品
SELECT product_code, product_name
FROM products
WHERE product_name LIKE 'A%';
-- 匹配包含"2023"的订单号
SELECT order_id, order_date
FROM orders
WHERE order_id LIKE '%2023%';
- 特殊字符处理
-- 匹配包含下划线的记录
SELECT student_id, student_name
FROM students
WHERE student_name LIKE '%\_%' ESCAPE '\';
四、空值处理表达式
- IS NULL/IS NOT NULL
-- 查询未填写邮箱的用户
SELECT user_id, register_time
FROM users
WHERE email_address IS NULL;
-- 排除缺失关键数据的记录
SELECT product_id
FROM products
WHERE specification IS NOT NULL;
- COALESCE空值转换
-- 将NULL转换为默认值
SELECT
order_id,
COALESCE(delivery_remark, '无物流信息') AS logistics_status
FROM orders;
五、复杂逻辑组合
- 多条件混合查询
-- 查询特定价格区间且评价达标的商品
SELECT product_name, avg_rating
FROM products
WHERE unit_price BETWEEN 200 AND 500
AND (review_count > 100 AND avg_rating >= 4.5);
- 嵌套逻辑运算
-- 组合条件查询
SELECT customer_id, total_purchase
FROM customers
WHERE (total_purchase > 10000 OR vip_level >= 3)
AND registration_date < '2023-01-01';
六、特殊场景处理
- JSON字段条件查询
-- 查询JSON属性满足条件的记录
SELECT user_id, profile->>'mobile' AS mobile
FROM users
WHERE profile->>'is_verified' = 'true'
AND (profile->'address'->>'city') = '深圳';
- NULL安全比较
-- 使用<=>运算符处理NULL
SELECT *
FROM products
WHERE price <=> NULL; -- 精确匹配NULL值
七、关键注意事项
运算符优先级
NOT > AND > OR,建议显式使用括号明确优先级:
-- 明确优先级
WHERE (A OR B) AND C
索引优化建议
在WHERE条件高频字段创建B-tree索引
避免在索引列上使用函数转换:
-- 有效索引使用
CREATE INDEX idx_price ON products(unit_price);
-- 索引失效写法
SELECT * FROM products WHERE unit_price * 0.8 > 200;
NULL处理规范
建议使用IS NULL代替= NULL,并配合COALESCE函数处理默认值
八、典型错误案例
错误示例:OR优先级问题
SELECT *
FROM orders
WHERE status = 'SHIPPED'
OR payment_status = 'SUCCESS'
AND delivery_date < CURRENT_DATE;
-- 正确写法(添加括号)
SELECT *
FROM orders
WHERE status = 'SHIPPED'
OR (payment_status = 'SUCCESS' AND delivery_date < CURRENT_DATE);
通过合理运用条件表达式,可以精准控制数据筛选逻辑。