SQLite SQL基础

SQL基础概述

SQL（Structured Query Language）是用于管理关系型数据库的标准语言。SQLite支持大部分标准SQL语法，同时也有一些扩展和特定的语法。掌握SQL基础是使用SQLite的必备技能，无论您是开发人员还是数据库管理员。

数据定义语言（DDL）

数据定义语言用于创建、修改和删除数据库对象，如表、索引、视图等。SQLite的DDL语句具有简洁易用的特点，但也有一些与其他数据库不同的特性。

创建数据库

SQLite数据库是文件级别的数据库，创建数据库实际上就是创建一个文件。

sql

-- 在命令行中打开或创建数据库
.open test.db

-- 或者在命令行参数中指定数据库名
sqlite3 test.db

创建表

创建表是SQLite中最常用的DDL操作之一。以下是创建基本表和带外键的表的示例：

sql

-- 创建基本表
CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT UNIQUE,
    age INTEGER CHECK (age >= 0),
    status TEXT DEFAULT 'active',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- 创建带有外键的表
CREATE TABLE orders (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER,
    total_amount REAL NOT NULL,
    order_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    status TEXT DEFAULT 'pending',
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- 创建产品表
CREATE TABLE products (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    category TEXT,
    price REAL NOT NULL CHECK (price > 0),
    stock INTEGER DEFAULT 0 CHECK (stock >= 0),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- 创建订单项目表
CREATE TABLE order_items (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    order_id INTEGER,
    product_id INTEGER,
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    unit_price REAL NOT NULL CHECK (unit_price > 0),
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE RESTRICT
);

修改表

SQLite对表结构修改的支持相对有限，不直接支持修改列类型或删除列，需要通过创建新表的方式间接实现。

sql

-- 添加列（SQLite 3.1.6+支持）
ALTER TABLE users ADD COLUMN phone TEXT;
ALTER TABLE users ADD COLUMN address TEXT DEFAULT '';

-- 重命名表（SQLite 3.2.0+支持）
ALTER TABLE old_table_name RENAME TO new_table_name;

-- 修改列类型（间接实现）
-- 1. 创建新表
CREATE TABLE users_new (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT UNIQUE,
    age INTEGER CHECK (age >= 0),
    phone TEXT,
    address TEXT DEFAULT '',
    status TEXT DEFAULT 'active',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
-- 2. 复制数据
INSERT INTO users_new SELECT * FROM users;
-- 3. 删除旧表
DROP TABLE users;
-- 4. 重命名新表
ALTER TABLE users_new RENAME TO users;

-- 删除列（间接实现，类似修改列类型）

删除表

删除表是不可逆操作，使用时需要谨慎。

sql

-- 删除单个表
DROP TABLE IF EXISTS orders;

-- 删除多个表
DROP TABLE IF EXISTS users, orders, products;

创建索引

索引是提高查询性能的重要手段，但过多的索引会影响写入性能。

sql

-- 创建普通索引
CREATE INDEX idx_users_email ON users(email);

-- 创建唯一索引
CREATE UNIQUE INDEX idx_users_username ON users(username);

-- 创建复合索引
CREATE INDEX idx_orders_user_id_date ON orders(user_id, order_date);

-- 创建部分索引（SQLite 3.8.0+支持）
CREATE INDEX idx_products_stock_low ON products(stock) WHERE stock <= 10;

删除索引

sql

-- 删除索引
DROP INDEX IF EXISTS idx_users_email;

数据操作语言（DML）

数据操作语言用于查询、插入、更新和删除数据，是日常数据库操作中最常用的SQL语句。

插入数据

插入数据是向数据库中添加新记录的操作。

sql

-- 插入单行数据
INSERT INTO users (name, email, age) VALUES ('Alice', 'alice@example.com', 30);

-- 插入多行数据（SQLite 3.7.11+支持）
INSERT INTO users (name, email, age) VALUES 
    ('Bob', 'bob@example.com', 25),
    ('Charlie', 'charlie@example.com', 35),
    ('David', 'david@example.com', 28);

-- 插入默认值
INSERT INTO users (name, email) VALUES ('Eve', 'eve@example.com');

-- 使用SELECT插入数据
CREATE TABLE users_archive AS SELECT * FROM users;

-- 带RETURNING子句的插入（SQLite 3.35.0+支持）
INSERT INTO users (name, email) VALUES ('Frank', 'frank@example.com')
RETURNING id, name, created_at;

查询数据

查询数据是从数据库中检索信息的操作，是SQL中最复杂也是最强大的部分。

基本查询

sql

-- 查询所有列（生产中不推荐，应指定具体列）
SELECT * FROM users;

-- 查询特定列
SELECT id, name, email FROM users;

-- 使用别名
SELECT 
    id AS user_id,
    name AS user_name,
    email AS user_email 
FROM users;

条件查询

sql

-- 等值条件
SELECT * FROM users WHERE age = 30;

-- 范围条件
SELECT * FROM users WHERE age BETWEEN 25 AND 35;
SELECT * FROM users WHERE age > 25 AND age < 35;

-- 逻辑条件
SELECT * FROM users WHERE age > 25 AND email LIKE '%example.com';
SELECT * FROM users WHERE age = 25 OR age = 35;

-- 空值检查
SELECT * FROM users WHERE phone IS NULL;
SELECT * FROM users WHERE phone IS NOT NULL;

-- IN条件
SELECT * FROM users WHERE age IN (25, 30, 35);

-- LIKE查询
SELECT * FROM users WHERE name LIKE 'A%'; -- 以A开头
SELECT * FROM users WHERE name LIKE '%a%'; -- 包含a
SELECT * FROM users WHERE name LIKE '_l%'; -- 第二个字符是l

-- GLOB查询（SQLite特有，使用通配符*和?）
SELECT * FROM users WHERE name GLOB 'A*'; -- 以A开头

排序

sql

-- 升序排序
SELECT * FROM users ORDER BY age ASC;

-- 降序排序
SELECT * FROM users ORDER BY age DESC;

-- 多列排序
SELECT * FROM users ORDER BY age DESC, name ASC;

-- NULL值排序（默认NULL排在最后）
SELECT * FROM users ORDER BY phone ASC NULLS FIRST;
SELECT * FROM users ORDER BY phone DESC NULLS LAST;

分组与聚合

sql

-- 计数
SELECT COUNT(*) FROM users; -- 所有记录数
SELECT COUNT(DISTINCT age) FROM users; -- 不同年龄的数量
SELECT COUNT(phone) FROM users; -- 非NULL电话数量

-- 求和
SELECT SUM(age) FROM users; -- 年龄总和

-- 平均值
SELECT AVG(age) FROM users; -- 平均年龄

-- 最大值和最小值
SELECT MAX(age) AS max_age, MIN(age) AS min_age FROM users;

-- 分组
SELECT age, COUNT(*) AS user_count FROM users GROUP BY age;

-- 分组筛选
SELECT age, COUNT(*) AS user_count FROM users GROUP BY age HAVING user_count > 1;

-- 分组连接
SELECT 
    u.id,
    u.name,
    GROUP_CONCAT(p.name, ', ') AS order_products
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
GROUP BY u.id, u.name;

连接查询

连接查询用于从多个表中检索相关数据，是SQL中最强大的功能之一。

sql

-- 内连接（只返回匹配的记录）
SELECT 
    u.name AS user_name,
    o.id AS order_id,
    o.total_amount,
    o.order_date
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- 左连接（返回左表所有记录，右表匹配则显示，否则NULL）
SELECT 
    u.name AS user_name,
    o.id AS order_id,
    o.total_amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

-- 右连接（SQLite 3.39.0+支持）
SELECT 
    u.name AS user_name,
    o.id AS order_id,
    o.total_amount
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

-- 交叉连接（返回笛卡尔积）
SELECT u.name, p.name FROM users u CROSS JOIN products p;

-- 自然连接（自动匹配相同列名）
SELECT * FROM users NATURAL JOIN orders;

子查询

子查询是嵌套在其他SQL语句中的查询，用于复杂的数据检索。

sql

-- 作为条件
SELECT * FROM users WHERE age > (SELECT AVG(age) FROM users);

-- 作为数据源
SELECT * FROM (
    SELECT name, age FROM users WHERE age > 30
) AS old_users;

-- EXISTS子查询
SELECT * FROM users 
WHERE EXISTS (
    SELECT * FROM orders 
    WHERE orders.user_id = users.id
);

-- IN子查询
SELECT * FROM users 
WHERE id IN (
    SELECT user_id FROM orders 
    WHERE total_amount > 100
);

限制与偏移

限制与偏移用于分页查询和限制返回结果数量。

sql

-- 限制结果数量
SELECT * FROM users LIMIT 5;

-- 偏移结果
SELECT * FROM users LIMIT 5 OFFSET 5;

-- 分页查询
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 0; -- 第1页，每页10条
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 10; -- 第2页，每页10条

-- 生产环境分页最佳实践
SELECT * FROM users 
WHERE id > 10 
ORDER BY id 
LIMIT 10;

更新数据

更新数据用于修改数据库中现有记录的值。

sql

-- 更新单行数据
UPDATE users SET age = 31 WHERE name = 'Alice';

-- 更新多行数据
UPDATE users SET status = 'inactive' WHERE last_login < '2023-01-01';

-- 更新多列数据
UPDATE users 
SET 
    age = age + 1,
    status = 'active',
    updated_at = CURRENT_TIMESTAMP 
WHERE id = 1;

-- 使用子查询更新
UPDATE users 
SET age = (SELECT MAX(age) FROM users) 
WHERE name = 'Alice';

-- 带RETURNING子句的更新（SQLite 3.35.0+支持）
UPDATE users 
SET status = 'active' 
WHERE id = 1 
RETURNING id, name, status;

删除数据

删除数据用于从数据库中移除记录，是不可逆操作，使用时需要谨慎。

sql

-- 删除特定行
DELETE FROM users WHERE id = 1;

-- 删除符合条件的多行
DELETE FROM users WHERE age > 35;

-- 删除所有行（保留表结构）
DELETE FROM users;

-- 带RETURNING子句的删除（SQLite 3.35.0+支持）
DELETE FROM users WHERE id = 1 
RETURNING id, name;

-- 注意：SQLite不直接支持TRUNCATE语句，使用DELETE替代

数据控制语言（DCL）

数据控制语言用于管理用户权限和事务，确保数据库的安全性和完整性。

事务管理

事务是一组原子性的SQL操作，要么全部成功，要么全部失败。

sql

-- 开始事务
BEGIN TRANSACTION;
BEGIN; -- 简写

-- 提交事务（确认更改）
COMMIT TRANSACTION;
COMMIT; -- 简写

-- 回滚事务（取消更改）
ROLLBACK TRANSACTION;
ROLLBACK; -- 简写

-- 保存点（SQLite 3.6.8+支持）
SAVEPOINT sp1;
-- 执行一些操作
ROLLBACK TO sp1; -- 回滚到保存点
RELEASE sp1; -- 释放保存点

-- 生产环境事务示例：转账操作
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

权限管理

SQLite是文件级别的数据库，不支持传统的用户权限管理，但可以通过文件系统权限和连接配置来控制访问。

sql

-- 附加数据库
ATTACH DATABASE 'other.db' AS other_db;

-- 分离数据库
DETACH DATABASE other_db;

-- 设置连接属性
PRAGMA read_uncommitted = true; -- 允许读取未提交的数据
PRAGMA foreign_keys = on; -- 启用外键约束

版本差异

SQLite在不同版本中引入了许多新功能和改进，了解版本差异对于在不同环境中使用SQLite至关重要。

SQLite 3.7.11 及以上

支持多行INSERT语法
增强了外键约束支持

SQLite 3.8.0 及以上

支持部分索引
优化了查询计划生成
增强了JSON支持

SQLite 3.25.0 及以上

支持窗口函数
增强了CTE（公共表表达式）支持

SQLite 3.31.0 及以上

支持UPDATE FROM语法
增强了触发器功能

SQLite 3.35.0 及以上

支持RETURNING子句
支持MERGE语句（UPSERT功能）

SQLite 3.39.0 及以上

支持RIGHT JOIN和FULL OUTER JOIN
增强了日期时间函数

生产运维最佳实践

SQL编写规范

使用大写关键字：提高可读性，如SELECT、FROM、WHERE
缩进和换行：使用缩进和换行提高可读性
**避免SELECT ***：只查询需要的列，减少数据传输和内存占用
使用参数化查询：防止SQL注入，提高性能
合理使用注释：解释复杂查询的业务逻辑

性能优化

使用LIMIT限制结果数量：减少数据传输和处理
避免在WHERE子句中使用函数：可能导致索引失效
使用JOIN替代子查询：某些情况下JOIN性能更好
合理使用索引：根据查询模式创建适当的索引
优化大表查询：使用分页、索引、分区等技术
使用EXPLAIN分析查询计划：找出性能瓶颈
避免频繁提交事务：合并多个操作到一个事务中

安全性

使用参数化查询：防止SQL注入攻击
限制查询结果：防止敏感数据泄露
使用视图：限制用户访问权限，隐藏敏感列
定期备份：防止数据丢失，采用增量备份和完整备份结合的策略
加密敏感数据：对敏感字段进行加密存储
限制数据库文件权限：只允许必要的用户访问数据库文件

高可用性

使用WAL模式：提高并发性能，支持读写同时进行
定期检查数据库完整性：使用PRAGMA integrity_check
实现复制机制：使用SQLite的复制扩展或自定义复制逻辑
监控数据库性能：跟踪慢查询，监控连接数和资源使用

常见问题（FAQ）

SQLite支持哪些数据类型？

SQLite使用动态类型系统，支持NULL、INTEGER、REAL、TEXT和BLOB五种基本数据类型。同时也支持一些亲和类型，如DATE、DATETIME等，但实际上会被转换为TEXT或INTEGER存储。

如何防止SQL注入？

使用参数化查询，避免直接拼接SQL语句：

python

# 推荐：使用参数化查询
cursor.execute("SELECT * FROM users WHERE name = ?", (name,))

# 不推荐：直接拼接SQL
cursor.execute(f"SELECT * FROM users WHERE name = '{name}'")

如何优化慢查询？

优化慢查询的方法：

使用EXPLAIN分析查询计划
创建必要的索引
优化JOIN操作，确保连接列有索引
限制结果数量，使用LIMIT
避免在WHERE子句中使用函数
考虑使用物化视图缓存频繁使用的复杂查询结果

如何处理NULL值？

处理NULL值的方法：

使用IS NULL或IS NOT NULL检查NULL值
使用COALESCE函数替换NULL值：COALESCE(phone, 'N/A')
为列设置默认值：phone TEXT DEFAULT 'N/A'
使用IFNULL函数：IFNULL(phone, 'N/A')

如何实现分页查询？

使用LIMIT和OFFSET实现分页：

sql

-- 第1页，每页10条
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 0;

-- 第2页，每页10条
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 10;

-- 生产环境优化：使用ID范围查询
SELECT * FROM users WHERE id > 10 ORDER BY id LIMIT 10;

如何使用事务？

使用BEGIN、COMMIT和ROLLBACK语句管理事务：

sql

BEGIN;
-- 执行SQL操作
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT; -- 提交事务
-- 或 ROLLBACK; -- 回滚事务

SQLite支持外键吗？

是的，SQLite支持外键约束，但默认是禁用的，需要手动启用：

sql

PRAGMA foreign_keys = ON;

如何查看SQLite版本？

使用以下命令查看SQLite版本：

sql

SELECT sqlite_version();

-- 或在命令行中
sqlite3 --version

总结

SQL是使用SQLite的基础，掌握SQL基础语法和最佳实践对于开发高效、安全的SQLite应用程序至关重要。本文档介绍了SQLite支持的主要SQL语法，包括数据定义语言、数据操作语言、数据控制语言等，并提供了版本差异和生产实践建议。

在实际应用中，需要根据具体需求和场景，灵活运用SQL语法，优化查询性能，确保数据安全。同时，需要关注SQLite版本差异，选择适合当前环境的功能和语法，以充分发挥SQLite的优势。

通过遵循最佳实践和不断学习，可以提高SQLite数据库的性能、可靠性和安全性，为应用程序提供强大的数据支持。

SQLite SQL基础 ​

SQL基础概述 ​

数据定义语言（DDL） ​

创建数据库 ​

创建表 ​

修改表 ​

删除表 ​

创建索引 ​

删除索引 ​

数据操作语言（DML） ​

插入数据 ​

查询数据 ​

基本查询 ​

条件查询 ​

排序 ​

分组与聚合 ​

连接查询 ​

子查询 ​

限制与偏移 ​

更新数据 ​

删除数据 ​

数据控制语言（DCL） ​

事务管理 ​

权限管理 ​

版本差异 ​

SQLite 3.7.11 及以上 ​

SQLite 3.8.0 及以上 ​

SQLite 3.25.0 及以上 ​

SQLite 3.31.0 及以上 ​

SQLite 3.35.0 及以上 ​

SQLite 3.39.0 及以上 ​

生产运维最佳实践 ​

SQL编写规范 ​

性能优化 ​

安全性 ​

高可用性 ​

常见问题（FAQ） ​

SQLite支持哪些数据类型？ ​

如何防止SQL注入？ ​

如何优化慢查询？ ​

如何处理NULL值？ ​

如何实现分页查询？ ​

如何使用事务？ ​

SQLite支持外键吗？ ​

如何查看SQLite版本？ ​

总结 ​