条件查询WHERE - 元素码农

发布时间: 2025-04-02 22:55

↑

# 条件查询WHERE

本文将详细介绍SQLite中WHERE子句的使用，包括基本条件查询、复杂条件组合以及条件优化等内容。

## 基本语法

### 比较运算符

1. **等于和不等于**
   ```sql
   -- 等于
   SELECT * FROM users WHERE status = 'active';
   
   -- 不等于
   SELECT * FROM users WHERE status <> 'inactive';
   -- 或者
   SELECT * FROM users WHERE status != 'inactive';
   ```

2. **大于和小于**
   ```sql
   -- 大于
   SELECT * FROM products WHERE price > 100;
   
   -- 小于
   SELECT * FROM products WHERE price < 50;
   
   -- 大于等于
   SELECT * FROM products WHERE price >= 100;
   
   -- 小于等于
   SELECT * FROM products WHERE price <= 50;
   ```

### NULL值处理

1. **IS NULL**
   ```sql
   SELECT * FROM users
   WHERE last_login IS NULL;
   ```

2. **IS NOT NULL**
   ```sql
   SELECT * FROM users
   WHERE email IS NOT NULL;
   ```

## 逻辑运算符

### AND运算符

```sql
-- 多条件同时满足
SELECT * FROM products
WHERE category = 'electronics'
AND price > 100
AND stock > 0;
```

### OR运算符

```sql
-- 满足任一条件
SELECT * FROM orders
WHERE status = 'pending'
OR status = 'processing';
```

### NOT运算符

```sql
-- 条件取反
SELECT * FROM products
WHERE NOT category = 'electronics';
-- 或者
SELECT * FROM products
WHERE category <> 'electronics';
```

## 范围查询

### BETWEEN运算符

1. **数值范围**
   ```sql
   SELECT * FROM products
   WHERE price BETWEEN 50 AND 100;
   ```

2. **日期范围**
   ```sql
   SELECT * FROM orders
   WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31';
   ```

### IN运算符

1. **固定值列表**
   ```sql
   SELECT * FROM products
   WHERE category IN ('electronics', 'computers', 'accessories');
   ```

2. **子查询**
   ```sql
   SELECT * FROM orders
   WHERE customer_id IN (
       SELECT id FROM customers
       WHERE country = 'China'
   );
   ```

## 模式匹配

### LIKE运算符

1. **基本模式**
   ```sql
   -- 以'A'开头
   SELECT * FROM users
   WHERE username LIKE 'A%';
   
   -- 以'com'结尾
   SELECT * FROM users
   WHERE email LIKE '%com';
   
   -- 包含'test'
   SELECT * FROM users
   WHERE email LIKE '%test%';
   ```

2. **单字符匹配**
   ```sql
   -- 匹配任意单个字符
   SELECT * FROM users
   WHERE username LIKE '_ohn';
   ```

### GLOB运算符

```sql
-- 使用Unix通配符语法
SELECT * FROM files
WHERE name GLOB '*.txt';
```

## 复杂条件

### 组合条件

1. **AND和OR组合**
   ```sql
   SELECT * FROM products
   WHERE (category = 'electronics' OR category = 'computers')
   AND price < 1000
   AND stock > 0;
   ```

2. **多层条件**
   ```sql
   SELECT * FROM orders
   WHERE (status = 'pending' AND total_amount > 1000)
   OR (status = 'processing' AND shipping_method = 'express');
   ```

### 子查询条件

1. **EXISTS子句**
   ```sql
   SELECT * FROM orders
   WHERE EXISTS (
       SELECT 1 FROM order_items
       WHERE order_items.order_id = orders.id
       AND quantity > 5
   );
   ```

2. **NOT EXISTS**
   ```sql
   SELECT * FROM products
   WHERE NOT EXISTS (
       SELECT 1 FROM order_items
       WHERE order_items.product_id = products.id
   );
   ```

## 性能优化

### 索引使用

1. **使用索引列**
   ```sql
   -- 优先使用索引列作为条件
   SELECT * FROM users
   WHERE email = 'test@example.com';
   ```

2. **避免索引失效**
   ```sql
   -- 不要在索引列上使用函数
   -- 错误示例
   SELECT * FROM users
   WHERE LOWER(email) = 'test@example.com';
   
   -- 正确示例
   SELECT * FROM users
   WHERE email = 'test@example.com';
   ```

### 条件优化

1. **简化条件**
   ```sql
   -- 优化前
   SELECT * FROM products
   WHERE price >= 100 AND price <= 200;
   
   -- 优化后
   SELECT * FROM products
   WHERE price BETWEEN 100 AND 200;
   ```

2. **条件顺序**
   ```sql
   -- 将选择性高的条件放在前面
   SELECT * FROM users
   WHERE email = 'test@example.com'  -- 高选择性
   AND status = 'active';            -- 低选择性
   ```

## 最佳实践

### 条件设计

1. **清晰的条件结构**
   - 使用括号明确优先级
   - 合理换行增加可读性
   - 使用注释说明复杂条件

2. **条件简化**
   - 避免不必要的条件
   - 合并相似条件
   - 使用合适的运算符

### 性能考虑

1. **查询优化**
   - 使用合适的索引
   - 避免全表扫描
   - 控制结果集大小

2. **资源管理**
   - 避免过于复杂的条件
   - 适当使用子查询
   - 注意查询性能

## 总结

使用WHERE子句需要注意：

- 正确使用运算符
- 合理组织条件
- 注意性能优化
- 保持代码可读性

建议：

- 使用合适的条件类型
- 优化查询性能
- 保持条件简洁
- 注意索引使用