GaussDB SQL基础语法示例:常见条件表达式详解

GaussDB SQL基础语法示例:常见条件表达式详解
条件表达式是SQL查询中控制数据筛选逻辑的核心要素。本文聚焦GaussDB支持的常见条件表达式,通过真实业务场景示例解析其用法与最佳实践。

一、基础比较运算符

  1. 等值与不等值判断
-- 精确匹配
SELECT user_id, username 
FROM users 
WHERE account_status = 'ACTIVE';

-- 不等值判断
SELECT product_name, unit_price 
FROM products 
WHERE stock_quantity <> 0;  -- 排除库存为0的商品
  1. 范围查询(BETWEEN)
-- 查询有效会员
SELECT member_id, register_date 
FROM members 
WHERE registration_time BETWEEN '2023-01-01' AND '2023-12-31';

二、集合匹配表达式

  1. 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
);
  1. NOT IN排除查询
-- 排除特定记录
SELECT employee_name 
FROM employees 
WHERE department_id NOT IN (3, 5, 7)
  AND employment_status = 'ACTIVE';

三、模糊匹配表达式

  1. 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%';
  1. 特殊字符处理
-- 匹配包含下划线的记录
SELECT student_id, student_name 
FROM students 
WHERE student_name LIKE '%\_%' ESCAPE '\';

四、空值处理表达式

  1. 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;
  1. COALESCE空值转换
-- 将NULL转换为默认值
SELECT 
    order_id,
    COALESCE(delivery_remark, '无物流信息') AS logistics_status
FROM orders;

五、复杂逻辑组合

  1. 多条件混合查询
-- 查询特定价格区间且评价达标的商品
SELECT product_name, avg_rating 
FROM products 
WHERE unit_price BETWEEN 200 AND 500
  AND (review_count > 100 AND avg_rating >= 4.5);
  1. 嵌套逻辑运算
-- 组合条件查询
SELECT customer_id, total_purchase 
FROM customers 
WHERE (total_purchase > 10000 OR vip_level >= 3)
  AND registration_date < '2023-01-01';

六、特殊场景处理

  1. JSON字段条件查询
-- 查询JSON属性满足条件的记录
SELECT user_id, profile->>'mobile' AS mobile 
FROM users 
WHERE profile->>'is_verified' = 'true'
  AND (profile->'address'->>'city') = '深圳';
  1. 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);

通过合理运用条件表达式,可以精准控制数据筛选逻辑。

posted @ 2025-05-27 15:05  喜酱喜酱  阅读(9)  评论(0)    收藏  举报