查询数据SELECT - 元素码农

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

↑

# 查询数据SELECT

本文将详细介绍如何在SQLite数据库中查询数据，包括基本查询语法、条件查询、结果排序等内容，帮助你掌握数据查询的各种技巧。

## 基本查询语法

### 查询所有列

1. **基本语法**
   ```sql
   SELECT * FROM table_name;
   ```

2. **示例**
   ```sql
   -- 查询用户表所有数据
   SELECT * FROM users;
   ```

### 查询指定列

1. **选择特定列**
   ```sql
   SELECT column1, column2 FROM table_name;
   ```

2. **列别名**
   ```sql
   SELECT 
       username AS user,
       email AS contact
   FROM users;
   ```

## 结果处理

### 去重查询

1. **DISTINCT关键字**
   ```sql
   SELECT DISTINCT column_name FROM table_name;
   ```

2. **多列去重**
   ```sql
   SELECT DISTINCT city, country FROM addresses;
   ```

### 结果计算

1. **算术运算**
   ```sql
   SELECT 
       product_name,
       price,
       quantity,
       price * quantity AS total
   FROM order_items;
   ```

2. **函数使用**
   ```sql
   SELECT 
       COUNT(*) AS total_count,
       AVG(price) AS avg_price,
       MAX(price) AS max_price,
       MIN(price) AS min_price
   FROM products;
   ```

## 条件查询

### 比较运算符

1. **基本比较**
   ```sql
   SELECT * FROM products
   WHERE price > 100;
   ```

2. **范围查询**
   ```sql
   SELECT * FROM products
   WHERE price BETWEEN 50 AND 200;
   ```

### 逻辑运算符

1. **AND运算**
   ```sql
   SELECT * FROM users
   WHERE age >= 18
   AND country = 'China';
   ```

2. **OR运算**
   ```sql
   SELECT * FROM products
   WHERE category = 'Electronics'
   OR category = 'Computers';
   ```

### 模式匹配

1. **LIKE操作符**
   ```sql
   -- 以'A'开头的用户名
   SELECT * FROM users
   WHERE username LIKE 'A%';
   
   -- 包含'test'的邮箱
   SELECT * FROM users
   WHERE email LIKE '%test%';
   ```

2. **正则表达式**
   ```sql
   SELECT * FROM users
   WHERE email REGEXP '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$';
   ```

## 结果排序

### 基本排序

1. **单列排序**
   ```sql
   -- 升序
   SELECT * FROM products
   ORDER BY price ASC;
   
   -- 降序
   SELECT * FROM products
   ORDER BY price DESC;
   ```

2. **多列排序**
   ```sql
   SELECT * FROM orders
   ORDER BY order_date DESC, total_amount ASC;
   ```

### NULL值处理

1. **NULL排序**
   ```sql
   SELECT * FROM users
   ORDER BY last_login DESC NULLS LAST;
   ```

2. **COALESCE使用**
   ```sql
   SELECT *,
       COALESCE(last_login, '1970-01-01') AS effective_last_login
   FROM users
   ORDER BY effective_last_login DESC;
   ```

## 分页查询

### LIMIT和OFFSET

1. **基本分页**
   ```sql
   -- 获取前10条记录
   SELECT * FROM products
   LIMIT 10;
   
   -- 跳过前10条，获取接下来的10条
   SELECT * FROM products
   LIMIT 10 OFFSET 10;
   ```

2. **带排序的分页**
   ```sql
   SELECT * FROM products
   ORDER BY created_at DESC
   LIMIT 10 OFFSET 20;
   ```

## 高级查询技巧

### 条件表达式

1. **CASE语句**
   ```sql
   SELECT 
       product_name,
       price,
       CASE 
           WHEN price < 50 THEN 'Low'
           WHEN price < 100 THEN 'Medium'
           ELSE 'High'
       END AS price_category
   FROM products;
   ```

2. **IIF函数**
   ```sql
   SELECT 
       username,
       IIF(age >= 18, 'Adult', 'Minor') AS age_category
   FROM users;
   ```

### 聚合函数

1. **基本聚合**
   ```sql
   SELECT 
       category,
       COUNT(*) AS product_count,
       AVG(price) AS avg_price,
       SUM(stock) AS total_stock
   FROM products
   GROUP BY category;
   ```

2. **条件聚合**
   ```sql
   SELECT 
       department,
       COUNT(*) AS total_employees,
       SUM(CASE WHEN salary > 50000 THEN 1 ELSE 0 END) AS high_paid_count
   FROM employees
   GROUP BY department;
   ```

## 性能优化

### 索引使用

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. **避免SELECT ***
   ```sql
   -- 只选择需要的列
   SELECT id, username, email
   FROM users
   WHERE status = 'active';
   ```

2. **使用EXISTS**
   ```sql
   -- 检查关联数据存在性
   SELECT * FROM orders
   WHERE EXISTS (
       SELECT 1 FROM order_items
       WHERE order_items.order_id = orders.id
   );
   ```

## 最佳实践

### 查询设计

1. **清晰的列名**
   - 使用有意义的别名
   - 避免歧义的命名
   - 保持命名一致性

2. **合理的条件**
   - 优化WHERE子句
   - 使用合适的操作符
   - 注意条件顺序

### 性能考虑

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

2. **资源管理**
   - 合理使用缓存
   - 避免大结果集
   - 适时释放资源

## 总结

查询数据需要注意：

- 选择合适的查询方式
- 优化查询条件
- 注意性能影响
- 处理好特殊情况

建议：

- 只查询需要的数据
- 合理使用索引
- 优化查询语句
- 注意查询性能