SQLite3命令行实战教程 快速上手数据库管理的利器 从基础查询到复杂操作一步到位 轻松应对各种数据管理需求

威震华夏关云长

SQLite3简介

SQLite是一个轻量级的、基于文件的 relational database management system（关系型数据库管理系统）。它无需独立的服务器进程，允许直接读写常规的磁盘文件，因此非常适合嵌入式设备和中小型应用程序。SQLite3是其第三个主要版本，提供了更多的功能和更好的性能。

SQLite3的命令行工具（sqlite3）是一个强大的交互式shell，允许用户直接操作SQLite数据库而无需编写复杂的程序。通过这个工具，你可以执行SQL查询、管理数据库结构、导入导出数据以及执行各种数据库管理任务。

安装SQLite3

在开始使用SQLite3命令行工具之前，你需要先安装它。SQLite3在大多数操作系统上都可以轻松安装：

Linux系统（基于Debian/Ubuntu）

2. sudo apt-get update
3. sudo apt-get install sqlite3

复制代码

Red Hat/CentOS/Fedora

2. sudo yum install sqlite3  # 对于旧版系统
3. # 或者
4. sudo dnf install sqlite3  # 对于新版系统

复制代码

macOS

macOS通常预装了SQLite3，你可以通过以下命令检查：

2. sqlite3 --version

复制代码

如果没有安装，可以使用Homebrew安装：

2. brew install sqlite

复制代码

Windows

1. 访问SQLite官方网站（https://www.sqlite.org/download.html）
2. 下载适用于Windows的预编译二进制文件（sqlite-tools-win32-*.zip）
3. 解压文件并将其中的sqlite3.exe放置在你想要的目录中
4. 将该目录添加到系统的PATH环境变量中

安装完成后，你可以通过在终端输入以下命令来验证安装：

2. sqlite3 --version

复制代码

SQLite3命令行基础

启动SQLite3

要启动SQLite3命令行工具，只需在终端中输入sqlite3：

2. sqlite3

复制代码

这将启动SQLite3并显示一个提示符，如下所示：

2. SQLite version 3.36.0 2021-06-18 18:36:39
3. Enter ".help" for usage hints.
4. Connected to a transient in-memory database.
5. Use ".open FILENAME" to reopen on a persistent database.
6. sqlite>

复制代码

此时，你已经连接到了一个临时的内存数据库。任何在此会话中创建的数据在退出后将会丢失。

创建和打开数据库

要创建一个新的数据库文件或打开现有的数据库，请使用以下命令：

2. sqlite3 mydatabase.db

复制代码

如果mydatabase.db不存在，SQLite3将创建它。如果它已存在，SQLite3将打开它。

退出SQLite3

要退出SQLite3命令行界面，可以使用以下命令之一：

2. .quit
3. -- 或者
4. .exit

复制代码

SQLite3命令与SQL语句的区别

在SQLite3命令行工具中，有两类命令：

1. SQLite3命令：以点（.）开头的命令，用于控制SQLite3命令行工具本身。
2. SQL语句：标准的SQL命令，用于操作数据库。

例如：

2. -- 这是一个SQLite3命令，用于显示数据库中的表
3. .tables
5. -- 这是一个SQL语句，用于查询数据
6. SELECT * FROM users;

复制代码

注意SQL语句通常以分号（;）结尾，而SQLite3命令不需要。

数据库基本操作

创建表

在SQLite中，使用CREATE TABLE语句来创建表。以下是一个创建用户表的示例：

2. CREATE TABLE users (
3.     id INTEGER PRIMARY KEY AUTOINCREMENT,
4.     name TEXT NOT NULL,
5.     email TEXT UNIQUE NOT NULL,
6.     age INTEGER,
7.     created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
8. );

复制代码

让我们来解释一下这个表结构：

• id：整数类型，设置为主键，自动递增
• name：文本类型，不能为空
• email：文本类型，唯一且不能为空
• age：整数类型
• created_at：时间戳类型，默认值为当前时间

查看表结构

要查看表的结构，可以使用SQLite3的.schema命令：

2. .schema users

复制代码

输出将类似于：

2. CREATE TABLE users (
3.     id INTEGER PRIMARY KEY AUTOINCREMENT,
4.     name TEXT NOT NULL,
5.     email TEXT UNIQUE NOT NULL,
6.     age INTEGER,
7.     created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
8. );

复制代码

列出所有表

要查看数据库中的所有表，可以使用.tables命令：

2. .tables

复制代码

修改表结构

SQLite支持有限形式的ALTER TABLE语句，主要用于添加列或重命名表：

2. -- 添加新列
3. ALTER TABLE users ADD COLUMN address TEXT;
5. -- 重命名表
6. ALTER TABLE users RENAME TO customers;

复制代码

注意：SQLite不支持删除列或修改列的数据类型。

删除表

要删除表，使用DROP TABLE语句：

2. DROP TABLE IF EXISTS customers;

复制代码

IF EXISTS选项可以防止在表不存在时出错。

数据操作

插入数据

使用INSERT INTO语句向表中插入数据：

2. INSERT INTO users (name, email, age) VALUES ('John Doe', 'john@example.com', 30);
3. INSERT INTO users (name, email, age) VALUES ('Jane Smith', 'jane@example.com', 25);
4. INSERT INTO users (name, email, age) VALUES ('Bob Johnson', 'bob@example.com', 35);

复制代码

你也可以一次插入多行数据：

2. INSERT INTO users (name, email, age) VALUES
3.     ('Alice Williams', 'alice@example.com', 28),
4.     ('Charlie Brown', 'charlie@example.com', 32),
5.     ('Diana Prince', 'diana@example.com', 29);

复制代码

查询数据

使用SELECT语句查询数据：

2. -- 查询所有数据
3. SELECT * FROM users;
5. -- 查询特定列
6. SELECT name, email FROM users;
8. -- 使用WHERE子句过滤数据
9. SELECT * FROM users WHERE age > 30;
11. -- 使用ORDER BY排序结果
12. SELECT * FROM users ORDER BY age DESC;
14. -- 使用LIMIT限制结果数量
15. SELECT * FROM users LIMIT 2;
17. -- 组合使用
18. SELECT name, email FROM users WHERE age >= 25 ORDER BY age ASC LIMIT 3;

复制代码

更新数据

使用UPDATE语句更新现有数据：

2. -- 更新单个记录
3. UPDATE users SET age = 31 WHERE name = 'John Doe';
5. -- 更新多个记录
6. UPDATE users SET age = age + 1 WHERE age < 30;
8. -- 更新多个列
9. UPDATE users SET name = 'Johnathan Doe', email = 'johnathan@example.com' WHERE id = 1;

复制代码

删除数据

使用DELETE语句删除数据：

2. -- 删除特定记录
3. DELETE FROM users WHERE id = 5;
5. -- 删除满足条件的所有记录
6. DELETE FROM users WHERE age > 40;
8. -- 删除所有记录（保留表结构）
9. DELETE FROM users;

复制代码

高级查询技巧

使用聚合函数

SQLite提供了多种聚合函数，如COUNT、SUM、AVG、MIN和MAX：

2. -- 计算记录总数
3. SELECT COUNT(*) FROM users;
5. -- 计算平均年龄
6. SELECT AVG(age) FROM users;
8. -- 找出最大和最小年龄
9. SELECT MAX(age), MIN(age) FROM users;
11. -- 计算满足条件的记录数
12. SELECT COUNT(*) FROM users WHERE age > 30;

复制代码

使用GROUP BY进行分组

2. -- 按年龄分组并计算每组的用户数
3. SELECT age, COUNT(*) FROM users GROUP BY age;
5. -- 按年龄分组并计算平均年龄
6. SELECT age, AVG(age) FROM users GROUP BY age;

复制代码

使用HAVING过滤分组

2. -- 找出用户数超过1个的年龄组
3. SELECT age, COUNT(*) as user_count FROM users GROUP BY age HAVING user_count > 1;

复制代码

使用JOIN连接表

首先，让我们创建另一个表并插入一些数据：

2. CREATE TABLE orders (
3.     id INTEGER PRIMARY KEY AUTOINCREMENT,
4.     user_id INTEGER NOT NULL,
5.     product TEXT NOT NULL,
6.     amount REAL NOT NULL,
7.     order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
8.     FOREIGN KEY (user_id) REFERENCES users(id)
9. );
11. INSERT INTO orders (user_id, product, amount) VALUES
12.     (1, 'Laptop', 1200.00),
13.     (1, 'Mouse', 25.00),
14.     (2, 'Keyboard', 75.00),
15.     (3, 'Monitor', 300.00),
16.     (3, 'Webcam', 50.00);

复制代码

现在，我们可以使用JOIN来连接这两个表：

2. -- 内连接：获取用户及其订单信息
3. SELECT users.name, orders.product, orders.amount
4. FROM users
5. INNER JOIN orders ON users.id = orders.user_id;
7. -- 左连接：获取所有用户及其订单信息（即使没有订单）
8. SELECT users.name, orders.product, orders.amount
9. FROM users
10. LEFT JOIN orders ON users.id = orders.user_id;
12. -- 使用表别名简化查询
13. SELECT u.name, o.product, o.amount
14. FROM users u
15. INNER JOIN orders o ON u.id = o.user_id;

复制代码

使用子查询

2. -- 查询年龄大于平均年龄的用户
3. SELECT * FROM users WHERE age > (SELECT AVG(age) FROM users);
5. -- 查询下过订单的用户
6. SELECT * FROM users WHERE id IN (SELECT DISTINCT user_id FROM orders);
8. -- 使用EXISTS
9. SELECT * FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

复制代码

索引和性能优化

创建索引

索引可以显著提高查询性能，特别是在大型数据库中：

2. -- 创建单列索引
3. CREATE INDEX idx_users_email ON users(email);
5. -- 创建复合索引
6. CREATE INDEX idx_users_name_age ON users(name, age);
8. -- 创建唯一索引
9. CREATE UNIQUE INDEX idx_users_email_unique ON users(email);

复制代码

查看索引

2. -- 查看表的所有索引
3. .indexes users
5. -- 查看数据库中的所有索引
6. .indexes

复制代码

删除索引

2. DROP INDEX idx_users_name_age;

复制代码

分析查询性能

SQLite3提供了.explain和.explain on命令来分析查询的执行计划：

2. -- 查看单个查询的执行计划
3. EXPLAIN SELECT * FROM users WHERE email = 'john@example.com';
5. -- 开启自动解释模式
6. .explain on
7. SELECT * FROM users WHERE email = 'john@example.com';
8. .explain off

复制代码

数据导入和导出

导出数据为CSV

2. -- 设置输出模式为CSV
3. .mode csv
5. -- 设置输出文件
6. .output users.csv
8. -- 执行查询并将结果导出到文件
9. SELECT * FROM users;
11. -- 恢复输出到屏幕
12. .output stdout

复制代码

从CSV导入数据

假设你有一个名为new_users.csv的文件，内容如下：

2. name,email,age
3. Tom Wilson,tom@example.com,27
4. Sarah Davis,sarah@example.com,33
5. Mike Miller,mike@example.com,41

复制代码

你可以使用以下命令将其导入到数据库：

2. -- 创建临时表
3. CREATE TABLE temp_users (name TEXT, email TEXT, age INTEGER);
5. -- 导入CSV数据
6. .import new_users.csv temp_users
8. -- 将数据插入到主表
9. INSERT INTO users (name, email, age) SELECT name, email, age FROM temp_users;
11. -- 删除临时表
12. DROP TABLE temp_users;

复制代码

导出数据库为SQL文件

2. -- 将整个数据库导出为SQL文件
3. .output backup.sql
4. .dump
5. .output stdout

复制代码

从SQL文件导入数据库

在系统命令行中（不是SQLite3内部），可以使用以下命令：

2. sqlite3 new_database.db < backup.sql

复制代码

事务处理

SQLite支持事务，允许你将多个操作作为一个原子单元执行：

2. -- 开始事务
3. BEGIN TRANSACTION;
5. -- 执行多个操作
6. INSERT INTO users (name, email, age) VALUES ('Eva Green', 'eva@example.com', 26);
7. UPDATE users SET age = age + 1 WHERE name = 'John Doe';
8. DELETE FROM users WHERE name = 'Bob Johnson';
10. -- 提交事务
11. COMMIT;
13. -- 或者回滚事务
14. -- ROLLBACK;

复制代码

事务可以确保数据库的一致性，如果事务中的任何操作失败，你可以回滚所有更改。

实用SQLite3命令

SQLite3提供了许多有用的命令来管理和查看数据库：

设置输出格式

2. -- 设置输出模式为列（对齐显示）
3. .mode column
5. -- 设置列宽
6. .width 5 20 30 10
8. -- 设置输出模式为列表
9. .mode list
11. -- 设置输出模式为HTML
12. .mode html
14. -- 设置输出模式为插入语句
15. .mode insert

复制代码

查看数据库信息

2. -- 显示附加的数据库
3. .databases
5. -- 显示数据库架构
6. .schema
8. -- 显示特定表的架构
9. .schema users
11. -- 显示所有表和视图
12. .tables
14. -- 显示所有索引
15. .indexes

复制代码

导入和导出

2. -- 导入SQL文件
3. .read script.sql
5. -- 导出数据库到文件
6. .backup backup.db
8. -- 从备份恢复数据库
9. .restore backup.db

复制代码

其他实用命令

2. -- 显示帮助
3. .help
5. -- 显示或更改设置
6. .show
8. -- 执行Shell命令
9. .shell ls -l
11. -- 改变输出文件
12. .output output.txt
14. -- 改变当前工作目录
15. .cd /path/to/directory

复制代码

常见问题和解决方案

1. 数据库被锁定

当多个进程尝试同时写入数据库时，可能会遇到”database is locked”错误。解决方案包括：

2. -- 设置超时时间（毫秒）
3. .timeout 5000

复制代码

或者确保没有其他进程正在使用数据库。

2. 外键约束不生效

默认情况下，SQLite不强制执行外键约束。要启用外键约束：

2. PRAGMA foreign_keys = ON;

复制代码

3. 大小写敏感问题

SQLite默认对文本比较不区分大小写。如果需要区分大小写：

2. -- 在查询中使用COLLATE BINARY
3. SELECT * FROM users WHERE name = 'John' COLLATE BINARY;

复制代码

4. 日期和时间处理

SQLite没有专门的日期时间类型，但提供了一些函数来处理存储为TEXT、REAL或INTEGER的日期时间：

2. -- 获取当前日期和时间
3. SELECT date('now');
4. SELECT time('now');
5. SELECT datetime('now');
7. -- 日期时间计算
8. SELECT date('now', '+1 month');
9. SELECT datetime('now', '-5 days');

复制代码

实战示例：构建一个简单的库存管理系统

让我们通过一个实际的例子来综合运用我们学到的知识。我们将创建一个简单的库存管理系统，包括产品、类别和库存记录。

1. 创建数据库和表

2. -- 创建类别表
3. CREATE TABLE categories (
4.     id INTEGER PRIMARY KEY AUTOINCREMENT,
5.     name TEXT NOT NULL UNIQUE,
6.     description TEXT
7. );
9. -- 创建产品表
10. CREATE TABLE products (
11.     id INTEGER PRIMARY KEY AUTOINCREMENT,
12.     name TEXT NOT NULL,
13.     description TEXT,
14.     price REAL NOT NULL,
15.     category_id INTEGER,
16.     FOREIGN KEY (category_id) REFERENCES categories(id)
17. );
19. -- 创建库存表
20. CREATE TABLE inventory (
21.     id INTEGER PRIMARY KEY AUTOINCREMENT,
22.     product_id INTEGER NOT NULL,
23.     quantity INTEGER NOT NULL DEFAULT 0,
24.     last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
25.     FOREIGN KEY (product_id) REFERENCES products(id)
26. );
28. -- 创建交易记录表
29. CREATE TABLE transactions (
30.     id INTEGER PRIMARY KEY AUTOINCREMENT,
31.     product_id INTEGER NOT NULL,
32.     quantity INTEGER NOT NULL,
33.     transaction_type TEXT NOT NULL, -- 'IN' 或 'OUT'
34.     transaction_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
35.     notes TEXT,
36.     FOREIGN KEY (product_id) REFERENCES products(id)
37. );

复制代码

2. 插入示例数据

2. -- 插入类别数据
3. INSERT INTO categories (name, description) VALUES
4.     ('Electronics', 'Electronic devices and gadgets'),
5.     ('Clothing', 'Apparel and accessories'),
6.     ('Books', 'Printed and digital books'),
7.     ('Home & Kitchen', 'Items for home and kitchen use');
9. -- 插入产品数据
10. INSERT INTO products (name, description, price, category_id) VALUES
11.     ('Laptop', 'High-performance laptop', 1200.00, 1),
12.     ('Smartphone', 'Latest smartphone model', 800.00, 1),
13.     ('T-Shirt', 'Cotton t-shirt', 20.00, 2),
14.     ('Jeans', 'Denim jeans', 50.00, 2),
15.     ('Fiction Novel', 'Bestselling fiction novel', 15.00, 3),
16.     ('Cookbook', 'Recipes from around the world', 25.00, 3),
17.     ('Blender', 'High-speed blender', 100.00, 4),
18.     ('Knife Set', 'Professional kitchen knives', 75.00, 4);
20. -- 插入初始库存数据
21. INSERT INTO inventory (product_id, quantity) VALUES
22.     (1, 10),
23.     (2, 25),
24.     (3, 50),
25.     (4, 30),
26.     (5, 40),
27.     (6, 20),
28.     (7, 15),
29.     (8, 10);

复制代码

3. 创建视图简化查询

2. -- 创建产品库存视图
3. CREATE VIEW product_inventory AS
4. SELECT
5.     p.id,
6.     p.name,
7.     p.description,
8.     p.price,
9.     c.name AS category,
10.     i.quantity,
11.     i.last_updated
12. FROM products p
13. JOIN categories c ON p.category_id = c.id
14. JOIN inventory i ON p.id = i.product_id;
16. -- 创建低库存视图（库存少于20的产品）
17. CREATE VIEW low_inventory AS
18. SELECT
19.     p.id,
20.     p.name,
21.     p.price,
22.     c.name AS category,
23.     i.quantity
24. FROM products p
25. JOIN categories c ON p.category_id = c.id
26. JOIN inventory i ON p.id = i.product_id
27. WHERE i.quantity < 20;

复制代码

4. 创建触发器自动更新库存

2. -- 创建触发器：插入交易记录时自动更新库存
3. CREATE TRIGGER update_inventory_after_transaction
4. AFTER INSERT ON transactions
5. FOR EACH ROW
6. BEGIN
7.     UPDATE inventory
8.     SET quantity = quantity +
9.         CASE NEW.transaction_type
10.             WHEN 'IN' THEN NEW.quantity
11.             WHEN 'OUT' THEN -NEW.quantity
12.             ELSE 0
13.         END,
14.         last_updated = CURRENT_TIMESTAMP
15.     WHERE product_id = NEW.product_id;
16. END;

复制代码

5. 执行库存操作

2. -- 记录进货（增加库存）
3. INSERT INTO transactions (product_id, quantity, transaction_type, notes) VALUES
4.     (1, 5, 'IN', 'New stock arrived'),
5.     (3, 20, 'IN', 'Bulk purchase'),
6.     (5, 15, 'IN', 'Reorder');
8. -- 记录出货（减少库存）
9. INSERT INTO transactions (product_id, quantity, transaction_type, notes) VALUES
10.     (2, 3, 'OUT', 'Customer purchase'),
11.     (4, 2, 'OUT', 'Customer purchase'),
12.     (7, 1, 'OUT', 'Customer purchase');

复制代码

6. 查询库存状态

2. -- 查看所有产品库存
3. SELECT * FROM product_inventory;
5. -- 查看低库存产品
6. SELECT * FROM low_inventory;
8. -- 按类别统计库存价值
9. SELECT
10.     c.name AS category,
11.     COUNT(p.id) AS product_count,
12.     SUM(i.quantity * p.price) AS total_value
13. FROM products p
14. JOIN categories c ON p.category_id = c.id
15. JOIN inventory i ON p.id = i.product_id
16. GROUP BY c.name
17. ORDER BY total_value DESC;
19. -- 查看特定产品的交易历史
20. SELECT
21.     t.transaction_date,
22.     t.transaction_type,
23.     t.quantity,
24.     t.notes
25. FROM transactions t
26. JOIN products p ON t.product_id = p.id
27. WHERE p.name = 'Laptop'
28. ORDER BY t.transaction_date DESC;

复制代码

总结

SQLite3是一个功能强大且易于使用的数据库系统，其命令行工具提供了直观的方式来管理和查询数据。本教程从基础概念开始，逐步介绍了如何使用SQLite3命令行工具执行各种数据库操作，包括：

• 创建和管理数据库及表
• 执行基本的数据操作（增删改查）
• 使用高级查询技巧，如连接、子查询和聚合函数
• 优化查询性能通过索引
• 导入和导出数据
• 处理事务
• 解决常见问题

通过最后的实战示例，我们展示了如何结合这些知识来构建一个简单的库存管理系统，这应该能帮助你理解如何在实际应用中使用SQLite3。

随着你对SQLite3的进一步探索，你会发现它是一个灵活且强大的工具，适用于各种规模的项目，从简单的个人应用到复杂的企业系统。希望本教程能帮助你快速上手SQLite3命令行工具，成为你数据管理的得力助手。

版权声明

1、转载或引用本网站内容(SQLite3命令行实战教程 快速上手数据库管理的利器 从基础查询到复杂操作一步到位 轻松应对各种数据管理需求)须注明原网址及作者(威震华夏关云长)，并标明本网站网址(https://pixtech.cc/)。

2、对于不当转载或引用本网站内容而引起的民事纷争、行政处理或其他损失，本网站不承担责任。

3、对不遵守本声明或其他违法、恶意使用本网站内容者，本网站保留追究其法律责任的权利。

本文地址: https://pixtech.cc/thread-39830-1-1.html