SQLite Select 语句

SQLite 的 SELECT 语句用于从一个或多个表中查询数据，是使用最频繁的命令。以下是完整语法与实用示例。

---

基本语法

SELECT [DISTINCT] column1, column2, ...
FROM table_name
[WHERE condition]
[GROUP BY column...]
[HAVING condition]
[ORDER BY column [ASC|DESC]]
[LIMIT count [OFFSET offset]];

---

核心组件详解

组件	说明
SELECT	指定要返回的列
DISTINCT	去重
FROM	数据来源表
WHERE	行过滤
GROUP BY	分组
HAVING	组过滤
ORDER BY	排序
LIMIT	限制行数

---

示例表结构

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT,
    department TEXT,
    salary REAL,
    hire_date DATE,
    active INTEGER  -- 1=在职, 0=离职
);

插入测试数据：

INSERT INTO employees (name, department, salary, hire_date, active) VALUES
('张三', 'IT', 8000, '2023-01-15', 1),
('李四', 'HR', 6000, '2022-06-20', 1),
('王五', 'IT', 9500, '2021-03-10', 1),
('赵六', 'HR', 5500, '2024-02-01', 0),
('陈七', 'Finance', 7200, '2023-09-12', 1);

---

常用 SELECT 示例

1. 查询所有列

SELECT * FROM employees;

2. 查询指定列

SELECT name, salary, department FROM employees;

3. 去重（DISTINCT）

SELECT DISTINCT department FROM employees;
-- 结果: IT, HR, Finance

4. 条件过滤（WHERE）

-- 在职工资 > 7000
SELECT name, salary FROM employees 
WHERE active = 1 AND salary > 7000;

5. 模糊查询（LIKE）

-- 名字以“张”开头
SELECT * FROM employees WHERE name LIKE '张%';

-- 部门包含“IT”或“HR”
SELECT * FROM employees WHERE department LIKE '%IT%' OR department LIKE '%HR%';

6. 范围查询（BETWEEN / IN）

-- 薪资在 6000 到 8000 之间
SELECT * FROM employees WHERE salary BETWEEN 6000 AND 8000;

-- 部门是 IT 或 Finance
SELECT * FROM employees WHERE department IN ('IT', 'Finance');

7. 排序（ORDER BY）

-- 按薪资降序排列
SELECT name, salary FROM employees 
ORDER BY salary DESC;

-- 多字段排序：先部门，再薪资
SELECT * FROM employees 
ORDER BY department ASC, salary DESC;

8. 分页（LIMIT + OFFSET）

-- 第1页，每页3条
SELECT * FROM employees ORDER BY id LIMIT 3;

-- 第2页
SELECT * FROM employees ORDER BY id LIMIT 3 OFFSET 3;

9. 聚合函数

-- 统计、合计、平均等
SELECT 
    COUNT(*) AS total,
    COUNT(CASE WHEN active = 1 THEN 1 END) AS active_count,
    AVG(salary) AS avg_salary,
    MAX(salary) AS max_salary,
    MIN(salary) AS min_salary,
    SUM(salary) AS total_salary
FROM employees;

10. 分组统计（GROUP BY）

-- 每个部门的平均薪资和人数
SELECT 
    department,
    COUNT(*) AS count,
    AVG(salary) AS avg_salary
FROM employees 
WHERE active = 1
GROUP BY department
HAVING COUNT(*) >= 1
ORDER BY avg_salary DESC;

11. 连接查询（JOIN）

-- 假设有 departments 表
CREATE TABLE departments (
    dept_name TEXT PRIMARY KEY,
    manager TEXT
);

-- INNER JOIN
SELECT e.name, e.salary, d.manager
FROM employees e
INNER JOIN departments d ON e.department = d.dept_name;

12. 子查询

-- 薪资高于平均水平的员工
SELECT name, salary FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

13. 条件表达式（CASE）

SELECT 
    name,
    salary,
    CASE 
        WHEN salary >= 8000 THEN '高薪'
        WHEN salary >= 6000 THEN '中薪'
        ELSE '低薪'
    END AS salary_level
FROM employees;

---

常用函数

类型	函数	示例
字符串	UPPER(), LOWER(), LENGTH(), SUBSTR()	UPPER(name)
数字	ROUND(), ABS(), RANDOM()	ROUND(salary, -3)
日期	DATE(), DATETIME(), STRFTIME()	STRFTIME('%Y', hire_date)
聚合	COUNT(), SUM(), AVG(), MAX(), MIN()	COUNT(*)

-- 按年份统计入职人数
SELECT 
    STRFTIME('%Y', hire_date) AS year,
    COUNT(*) AS hires
FROM employees
GROUP BY year;

---

实用技巧

• 使用别名：SELECT name AS 姓名, salary AS 月薪 FROM employees;
• 空值处理：COALESCE(email, '无邮箱')
• 性能提示：为频繁查询的列建索引（如 WHERE、JOIN 字段）

CREATE INDEX idx_salary ON employees(salary);

---

需要我根据你的实际表结构生成具体 SELECT 语句吗？
请提供 CREATE TABLE 或描述字段！