SQLite 命令(手把手讲解)

更新时间:

💡一则或许对你有用的小广告

欢迎加入小哈的星球 ,你将获得:专属的项目实战 / 1v1 提问 / Java 学习路线 / 学习打卡 / 每月赠书 / 社群讨论

  • 新项目:《从零手撸:仿小红书(微服务架构)》 正在持续爆肝中,基于 Spring Cloud Alibaba + Spring Boot 3.x + JDK 17...点击查看项目介绍 ;
  • 《从零手撸:前后端分离博客项目(全栈开发)》 2 期已完结,演示链接: http://116.62.199.48/ ;

截止目前, 星球 内专栏累计输出 82w+ 字,讲解图 3441+ 张,还在持续爆肝中.. 后续还会上新更多项目,目标是将 Java 领域典型的项目都整一波,如秒杀系统, 在线商城, IM 即时通讯,权限管理,Spring Cloud Alibaba 微服务等等,已有 2900+ 小伙伴加入学习 ,欢迎点击围观

在编程世界中,数据库是存储和管理数据的核心工具。SQLite 作为一款轻量级、无需配置的嵌入式数据库,凭借其简单易用的特点,成为编程初学者和中小型项目开发者的首选。掌握 SQLite 命令,不仅能帮助开发者高效管理数据,还能为后续学习更复杂的数据库系统(如 MySQL 或 PostgreSQL)打下坚实基础。本文将从基础到进阶,结合实际案例,逐步解析 SQLite 命令 的核心操作,让读者轻松上手这一强大工具。


一、SQLite 命令的基础操作

1.1 创建数据库与表

SQLite 的核心功能是通过 SQL(结构化查询语言)命令实现的。首先,我们需要创建数据库文件并定义表结构。

创建数据库

通过 sqlite3 命令行工具或编程语言的 SQLite 库(如 Python 的 sqlite3 模块),可以快速初始化数据库。例如,在命令行中输入:

sqlite3 my_database.db  

这会创建名为 my_database.db 的文件,并进入交互模式。

定义表结构

使用 CREATE TABLE 命令定义数据表。例如,创建一个记录用户信息的表:

CREATE TABLE users (  
    id INTEGER PRIMARY KEY AUTOINCREMENT,  
    name TEXT NOT NULL,  
    email TEXT UNIQUE,  
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP  
);  

比喻解析

  • id 类似于图书馆书籍的唯一编号,确保每条记录的唯一性。
  • PRIMARY KEY 是表的“主索引”,如同图书馆的分类系统,帮助快速定位数据。
  • AUTOINCREMENT 表示系统自动递增编号,无需手动输入。
  • UNIQUE 约束类似检查邮箱是否已被注册,避免重复。

1.2 插入与查询数据

插入数据

使用 INSERT INTO 命令向表中添加数据:

INSERT INTO users (name, email)  
VALUES ('Alice', 'alice@example.com');  

技巧:若字段顺序与表定义一致,可省略字段名:

INSERT INTO users VALUES (NULL, 'Bob', 'bob@example.com', '2023-09-20');  

查询数据

通过 SELECT 命令检索数据:

SELECT * FROM users WHERE name = 'Alice';  

此命令会返回所有字段(*)中 name 为 Alice 的记录。


二、进阶命令:筛选、排序与聚合

2.1 筛选与条件查询

WHERE 子句用于过滤数据,支持逻辑运算符(AND, OR)和比较运算符(>, <, LIKE)。例如:

-- 查询邮箱以 '@example.com' 结尾的用户  
SELECT * FROM users WHERE email LIKE '%@example.com';  

-- 查询 id 在 1 到 5 之间的用户  
SELECT * FROM users WHERE id BETWEEN 1 AND 5;  

2.2 排序与分组

排序(ORDER BY

SELECT * FROM users ORDER BY created_at DESC;  

此命令按 created_at 字段降序排列,最近添加的记录优先显示。

分组与聚合(GROUP BY

假设有一个订单表 orders,字段包括 user_idamount,计算每个用户的总消费金额:

SELECT user_id, SUM(amount) AS total_spent  
FROM orders  
GROUP BY user_id;  

比喻GROUP BY 类似超市按商品分类统计销量,SUM 是将同一分类下的数值累加。

2.3 联合表数据(JOIN

当多个表存在关联时,可通过 JOIN 合并数据。例如,用户表 users 和订单表 orders 通过 user_id 关联:

SELECT users.name, orders.amount  
FROM users  
JOIN orders ON users.id = orders.user_id;  

此命令返回每个用户的消费金额列表。


三、数据库管理:事务与索引

3.1 事务处理(BEGIN TRANSACTION

SQLite 支持事务以确保操作的原子性。例如,转账操作需同时更新两个账户:

BEGIN TRANSACTION;  
UPDATE accounts SET balance = balance - 100 WHERE id = 1;  
UPDATE accounts SET balance = balance + 100 WHERE id = 2;  
COMMIT;  

若中间出错,可执行 ROLLBACK 撤销所有更改。

3.2 创建索引(CREATE INDEX

索引可加速查询速度,但会占用额外存储空间。例如,为邮箱字段创建索引:

CREATE INDEX idx_email ON users(email);  

比喻:索引如同书籍的目录,通过快速定位关键词,减少翻页时间。

3.3 备份与恢复

通过命令行导出数据库:

sqlite3 my_database.db .dump > backup.sql  

恢复时只需执行:

sqlite3 new_database.db < backup.sql  

四、高级命令与实际案例

4.1 视图(CREATE VIEW

视图是虚拟表,基于查询结果生成。例如,创建一个显示用户及消费总额的视图:

CREATE VIEW user_spending AS  
SELECT users.name, SUM(orders.amount) AS total  
FROM users  
JOIN orders ON users.id = orders.user_id  
GROUP BY users.id;  

之后可通过 SELECT * FROM user_spending 直接使用。

4.2 触发器(CREATE TRIGGER

触发器在特定事件(如插入数据)时自动执行操作。例如,记录用户最后登录时间:

CREATE TRIGGER update_last_login  
AFTER UPDATE ON users  
FOR EACH ROW  
WHEN NEW.last_login IS NOT NULL  
BEGIN  
    UPDATE users SET last_login = CURRENT_TIMESTAMP WHERE id = NEW.id;  
END;  

4.3 实战案例:电商数据库设计

假设需管理用户、商品和订单:

-- 创建商品表  
CREATE TABLE products (  
    id INTEGER PRIMARY KEY,  
    name TEXT,  
    price REAL,  
    stock INTEGER  
);  

-- 创建订单表  
CREATE TABLE orders (  
    id INTEGER PRIMARY KEY,  
    user_id INTEGER REFERENCES users(id),  
    product_id INTEGER REFERENCES products(id),  
    quantity INTEGER,  
    total_price REAL  
);  

-- 查询某用户的订单总金额  
SELECT SUM(o.total_price) AS total  
FROM orders o  
JOIN users u ON o.user_id = u.id  
WHERE u.name = 'Alice';  

五、总结

本文系统梳理了 SQLite 命令 的核心知识点,从基础的增删改查到进阶的事务管理、索引优化,再到视图与触发器的高级应用,结合电商案例展示了实际操作场景。通过掌握这些命令,开发者不仅能高效管理数据,还能为构建复杂应用打下基础。

SQLite 的简洁性使其成为学习数据库的绝佳起点,而其强大的功能(如事务与索引)又能满足中小型项目的实战需求。建议读者通过动手实践(如创建自己的数据库并编写查询语句),逐步巩固对 SQLite 命令 的理解。未来,随着项目复杂度的提升,可进一步探索更专业的数据库系统,但 SQLite 的核心逻辑与 SQL 基础将始终是技术栈中的关键一环。

最新发布