SQLite数据库入门教程通过简单案例快速掌握轻量级数据库操作技巧与实战应用适合初学者

威震华夏关云长

1. 引言

SQLite是一个轻量级的、基于文件的 relational database management system (RDBMS)，它不需要一个单独的服务器进程或系统来运行。SQLite是一个自包含的、无服务器的、零配置的、事务性的SQL数据库引擎。SQLite是世界上使用最广泛的数据库引擎，它被用于无数的桌面和移动应用程序中。

SQLite的主要特点包括：

• 轻量级：SQLite库很小，完全配置时小于400KB，省略可选功能时小于250KB
• 无需服务器：SQLite不需要一个单独的服务器进程或系统来操作
• 单一文件：整个数据库存储在一个单一的磁盘文件中
• 可移植：SQLite数据库文件可以在不同字节序的机器之间自由共享
• 紧凑：SQLite以紧凑的方式存储数据，使数据库文件较小
• 可靠：SQLite的事务处理完全符合ACID属性，允许从系统崩溃或电源故障中恢复

本教程适合以下读者：

• 数据库初学者
• 需要快速掌握SQLite的开发人员
• 想要在应用程序中嵌入轻量级数据库的开发者
• 对移动应用开发感兴趣的人员

2. SQLite的安装和设置

2.1 Windows系统安装

在Windows系统上，安装SQLite非常简单：

1. 访问SQLite官方网站(https://www.sqlite.org/download.html)
2. 下载适用于Windows的预编译二进制文件（sqlite-tools-win32-.zip 和 sqlite-dll-win32-.zip）
3. 解压下载的文件到一个目录，例如 C:\sqlite
4. 将 C:\sqlite 添加到系统PATH环境变量中

安装完成后，可以通过命令行验证安装：

2. sqlite3

复制代码

如果安装成功，你将看到SQLite的命令行提示符。

2.2 macOS系统安装

在macOS系统上，可以使用Homebrew安装SQLite：

2. brew install sqlite

复制代码

或者，你也可以从SQLite官方网站下载macOS版本的预编译二进制文件。

2.3 Linux系统安装

在Linux系统上，可以使用包管理器安装SQLite：

对于基于Debian的系统（如Ubuntu）：

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

复制代码

对于基于RPM的系统（如Fedora、CentOS）：

2. sudo yum install sqlite3

复制代码

3. SQLite基础概念

在开始使用SQLite之前，我们需要了解一些基本概念：

3.1 数据库（Database）

数据库是一个有组织的数据集合。在SQLite中，数据库通常存储在一个单独的文件中，扩展名为.db、.sqlite或.sqlite3。

3.2 表（Table）

表是数据库中的基本数据存储结构，由行和列组成。每个表都有一个唯一的名称，并且包含一个或多个列。

3.3 字段/列（Field/Column）

列是表中的垂直实体，包含特定类型的数据。每个列都有一个名称和数据类型。

3.4 记录/行（Record/Row）

行是表中的水平实体，表示一个完整的数据记录。

3.5 主键（Primary Key）

主键是表中的一个或多个字段，用于唯一标识表中的每一行记录。主键必须包含唯一的值，且不能包含NULL值。

3.6 外键（Foreign Key）

外键是一个表中的字段，它引用另一个表的主键。外键用于建立两个表之间的链接。

4. 基本的SQL命令

SQL（Structured Query Language）是用于管理关系数据库的标准语言。下面介绍SQLite中最常用的SQL命令。

4.1 CREATE TABLE - 创建表

CREATE TABLE语句用于创建新表。基本语法如下：

2. CREATE TABLE table_name (
3.     column1 datatype PRIMARY KEY,
4.     column2 datatype,
5.     column3 datatype,
6.     .....
7. );

复制代码

例如，创建一个名为”students”的表：

2. CREATE TABLE students (
3.     id INTEGER PRIMARY KEY,
4.     name TEXT NOT NULL,
5.     age INTEGER,
6.     grade TEXT
7. );

复制代码

4.2 INSERT - 插入数据

INSERT INTO语句用于向表中插入新记录。基本语法如下：

2. INSERT INTO table_name (column1, column2, column3, ...)
3. VALUES (value1, value2, value3, ...);

复制代码

例如，向”students”表中插入一条记录：

2. INSERT INTO students (name, age, grade)
3. VALUES ('张三', 20, 'A');

复制代码

4.3 SELECT - 查询数据

SELECT语句用于从数据库中检索数据。基本语法如下：

2. SELECT column1, column2, ...
3. FROM table_name
4. [WHERE condition]
5. [ORDER BY column_name [ASC|DESC]];

复制代码

例如，查询所有学生：

2. SELECT * FROM students;

复制代码

查询年龄大于18岁的学生：

2. SELECT * FROM students WHERE age > 18;

复制代码

4.4 UPDATE - 更新数据

UPDATE语句用于修改表中的现有记录。基本语法如下：

2. UPDATE table_name
3. SET column1 = value1, column2 = value2, ...
4. WHERE condition;

复制代码

例如，更新学生的成绩：

2. UPDATE students
3. SET grade = 'B'
4. WHERE name = '张三';

复制代码

4.5 DELETE - 删除数据

DELETE语句用于从表中删除记录。基本语法如下：

2. DELETE FROM table_name
3. WHERE condition;

复制代码

例如，删除名为”张三”的学生记录：

2. DELETE FROM students
3. WHERE name = '张三';

复制代码

5. SQLite的数据类型

SQLite使用动态类型系统，这意味着数据类型是与值本身相关联的，而不是与容器相关联的。SQLite有以下五种基本数据类型：

5.1 NULL

NULL值表示缺失或未知的数据。

5.2 INTEGER

INTEGER值是带符号的整数，根据值的大小存储在1、2、3、4、6或8字节中。

5.3 REAL

REAL值是浮点值，存储为8字节的IEEE浮点数。

5.4 TEXT

TEXT值是文本字符串，使用数据库编码（UTF-8、UTF-16BE或UTF-16LE）存储。

5.5 BLOB

BLOB（Binary Large Object）值用于存储二进制数据，完全按照输入存储。

5.6 类型亲和性

SQLite使用类型亲和性（Type Affinity）概念，当插入数据时，SQLite会尝试将值转换为列的数据类型。SQLite有以下类型亲和性：

• TEXT：存储文本数据
• NUMERIC：可能包含所有类型的值
• INTEGER：行为类似于NUMERIC，但尝试将值转换为整数
• REAL：行为类似于NUMERIC，但尝试将值转换为浮点数
• BLOB：不尝试转换数据

例如，创建一个带有特定类型亲和性的表：

2. CREATE TABLE products (
3.     id INTEGER PRIMARY KEY,
4.     name TEXT,
5.     price REAL,
6.     description TEXT,
7.     image BLOB
8. );

复制代码

6. 实战案例1：创建一个简单的联系人管理系统

现在，让我们通过一个实际的案例来应用我们学到的知识。我们将创建一个简单的联系人管理系统。

6.1 创建数据库和表

首先，创建一个新的数据库文件，并创建一个联系人表：

2. -- 创建一个新的数据库文件（在命令行中执行）
3. sqlite3 contacts.db
5. -- 创建联系人表
6. CREATE TABLE contacts (
7.     id INTEGER PRIMARY KEY AUTOINCREMENT,
8.     name TEXT NOT NULL,
9.     phone TEXT,
10.     email TEXT,
11.     address TEXT,
12.     created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
13. );

复制代码

6.2 插入联系人数据

向联系人表中插入一些示例数据：

2. -- 插入联系人数据
3. INSERT INTO contacts (name, phone, email, address)
4. VALUES ('张三', '13800138000', 'zhangsan@example.com', '北京市海淀区');
6. INSERT INTO contacts (name, phone, email, address)
7. VALUES ('李四', '13900139000', 'lisi@example.com', '上海市浦东新区');
9. INSERT INTO contacts (name, phone, email, address)
10. VALUES ('王五', '13700137000', 'wangwu@example.com', '广州市天河区');

复制代码

6.3 查询联系人

执行一些查询操作来检索联系人数据：

2. -- 查询所有联系人
3. SELECT * FROM contacts;
5. -- 查询特定联系人
6. SELECT * FROM contacts WHERE name = '张三';
8. -- 查询电话号码以138开头的联系人
9. SELECT * FROM contacts WHERE phone LIKE '138%';
11. -- 查询北京的联系人
12. SELECT * FROM contacts WHERE address LIKE '%北京%';

复制代码

6.4 更新联系人信息

更新联系人的信息：

2. -- 更新张三的电话号码
3. UPDATE contacts
4. SET phone = '13800138001'
5. WHERE name = '张三';
7. -- 更新李四的地址
8. UPDATE contacts
9. SET address = '上海市黄浦区'
10. WHERE name = '李四';

复制代码

6.5 删除联系人

删除联系人记录：

2. -- 删除王五的联系人记录
3. DELETE FROM contacts
4. WHERE name = '王五';

复制代码

6.6 创建索引以提高查询性能

为经常查询的列创建索引，以提高查询性能：

2. -- 为姓名列创建索引
3. CREATE INDEX idx_contacts_name ON contacts(name);
5. -- 为电话号码列创建索引
6. CREATE INDEX idx_contacts_phone ON contacts(phone);

复制代码

6.7 创建视图以简化复杂查询

创建视图来简化常用的复杂查询：

2. -- 创建一个视图，显示联系人的基本信息
3. CREATE VIEW contact_basic_info AS
4. SELECT id, name, phone, email
5. FROM contacts;
7. -- 使用视图查询
8. SELECT * FROM contact_basic_info;

复制代码

7. 实战案例2：创建一个简单的库存管理系统

接下来，我们将创建一个简单的库存管理系统，涉及多个表和关系。

7.1 创建数据库和表

首先，创建一个新的数据库文件，并创建所需的表：

2. -- 创建一个新的数据库文件（在命令行中执行）
3. sqlite3 inventory.db
5. -- 创建产品表
6. CREATE TABLE products (
7.     id INTEGER PRIMARY KEY AUTOINCREMENT,
8.     name TEXT NOT NULL,
9.     description TEXT,
10.     price REAL NOT NULL,
11.     created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
12. );
14. -- 创建库存表
15. CREATE TABLE inventory (
16.     id INTEGER PRIMARY KEY AUTOINCREMENT,
17.     product_id INTEGER NOT NULL,
18.     quantity INTEGER NOT NULL,
19.     location TEXT,
20.     last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
21.     FOREIGN KEY (product_id) REFERENCES products(id)
22. );
24. -- 创建供应商表
25. CREATE TABLE suppliers (
26.     id INTEGER PRIMARY KEY AUTOINCREMENT,
27.     name TEXT NOT NULL,
28.     contact_person TEXT,
29.     phone TEXT,
30.     email TEXT,
31.     address TEXT
32. );
34. -- 创建产品供应商关联表
35. CREATE TABLE product_suppliers (
36.     id INTEGER PRIMARY KEY AUTOINCREMENT,
37.     product_id INTEGER NOT NULL,
38.     supplier_id INTEGER NOT NULL,
39.     supply_price REAL NOT NULL,
40.     FOREIGN KEY (product_id) REFERENCES products(id),
41.     FOREIGN KEY (supplier_id) REFERENCES suppliers(id)
42. );

复制代码

7.2 插入示例数据

向各个表中插入一些示例数据：

2. -- 插入产品数据
3. INSERT INTO products (name, description, price)
4. VALUES ('笔记本电脑', '高性能笔记本电脑', 5999.99);
6. INSERT INTO products (name, description, price)
7. VALUES ('智能手机', '最新款智能手机', 3999.99);
9. INSERT INTO products (name, description, price)
10. VALUES ('平板电脑', '轻薄便携平板电脑', 2999.99);
12. -- 插入库存数据
13. INSERT INTO inventory (product_id, quantity, location)
14. VALUES (1, 50, '仓库A');
16. INSERT INTO inventory (product_id, quantity, location)
17. VALUES (2, 100, '仓库B');
19. INSERT INTO inventory (product_id, quantity, location)
20. VALUES (3, 75, '仓库A');
22. -- 插入供应商数据
23. INSERT INTO suppliers (name, contact_person, phone, email, address)
24. VALUES ('电子科技有限公司', '张经理', '13800138000', 'zhang@example.com', '北京市海淀区');
26. INSERT INTO suppliers (name, contact_person, phone, email, address)
27. VALUES ('数码产品供应商', '李经理', '13900139000', 'li@example.com', '上海市浦东新区');
29. -- 插入产品供应商关联数据
30. INSERT INTO product_suppliers (product_id, supplier_id, supply_price)
31. VALUES (1, 1, 5500.00);
33. INSERT INTO product_suppliers (product_id, supplier_id, supply_price)
34. VALUES (2, 1, 3500.00);
36. INSERT INTO product_suppliers (product_id, supplier_id, supply_price)
37. VALUES (3, 2, 2700.00);

复制代码

7.3 查询库存信息

执行一些查询操作来检索库存信息：

2. -- 查询所有产品及其库存信息
3. SELECT p.id, p.name, p.price, i.quantity, i.location
4. FROM products p
5. JOIN inventory i ON p.id = i.product_id;
7. -- 查询库存数量少于80的产品
8. SELECT p.id, p.name, p.price, i.quantity, i.location
9. FROM products p
10. JOIN inventory i ON p.id = i.product_id
11. WHERE i.quantity < 80;
13. -- 查询特定供应商供应的产品
14. SELECT p.id, p.name, p.price, s.name AS supplier_name, ps.supply_price
15. FROM products p
16. JOIN product_suppliers ps ON p.id = ps.product_id
17. JOIN suppliers s ON ps.supplier_id = s.id
18. WHERE s.name = '电子科技有限公司';

复制代码

7.4 更新库存信息

更新库存信息：

2. -- 更新笔记本电脑的库存数量
3. UPDATE inventory
4. SET quantity = 45, last_updated = CURRENT_TIMESTAMP
5. WHERE product_id = 1;
7. -- 更新智能手机的价格
8. UPDATE products
9. SET price = 3899.99
10. WHERE id = 2;

复制代码

7.5 使用事务处理库存变动

使用事务来确保库存变动的原子性：

2. -- 开始事务
3. BEGIN TRANSACTION;
5. -- 减少笔记本电脑的库存
6. UPDATE inventory
7. SET quantity = quantity - 5, last_updated = CURRENT_TIMESTAMP
8. WHERE product_id = 1;
10. -- 增加平板电脑的库存
11. UPDATE inventory
12. SET quantity = quantity + 10, last_updated = CURRENT_TIMESTAMP
13. WHERE product_id = 3;
15. -- 提交事务
16. COMMIT;
18. -- 如果出现问题，可以回滚事务
19. -- ROLLBACK;

复制代码

7.6 创建触发器自动更新时间戳

创建触发器以自动更新库存的最后更新时间：

2. -- 创建触发器
3. CREATE TRIGGER update_inventory_timestamp
4. AFTER UPDATE ON inventory
5. FOR EACH ROW
6. BEGIN
7.     UPDATE inventory
8.     SET last_updated = CURRENT_TIMESTAMP
9.     WHERE id = NEW.id;
10. END;

复制代码

7.7 生成库存报告

使用SQL查询生成库存报告：

2. -- 生成库存总价值报告
3. SELECT
4.     SUM(p.price * i.quantity) AS total_inventory_value
5. FROM products p
6. JOIN inventory i ON p.id = i.product_id;
8. -- 生成各仓库的库存价值报告
9. SELECT
10.     i.location,
11.     SUM(p.price * i.quantity) AS location_inventory_value
12. FROM products p
13. JOIN inventory i ON p.id = i.product_id
14. GROUP BY i.location;
16. -- 生成低库存产品报告
17. SELECT
18.     p.id,
19.     p.name,
20.     i.quantity,
21.     CASE
22.         WHEN i.quantity < 20 THEN '紧急补货'
23.         WHEN i.quantity < 50 THEN '需要补货'
24.         ELSE '库存充足'
25.     END AS status
26. FROM products p
27. JOIN inventory i ON p.id = i.product_id
28. WHERE i.quantity < 50;

复制代码

8. SQLite的高级特性

SQLite提供了许多高级特性，使其成为一个功能强大的数据库系统。

8.1 索引

索引是提高数据库查询性能的重要工具。索引允许数据库快速定位表中的数据，而不必扫描整个表。

2. -- 创建索引
3. CREATE INDEX idx_products_name ON products(name);
5. -- 创建复合索引
6. CREATE INDEX idx_inventory_product_location ON inventory(product_id, location);
8. -- 删除索引
9. DROP INDEX idx_products_name;

复制代码

8.2 视图

视图是基于SQL语句的结果集的虚拟表。视图可以简化复杂的查询，并提供数据的安全性。

2. -- 创建视图
3. CREATE VIEW product_inventory_view AS
4. SELECT
5.     p.id AS product_id,
6.     p.name AS product_name,
7.     p.price,
8.     i.quantity,
9.     i.location
10. FROM products p
11. JOIN inventory i ON p.id = i.product_id;
13. -- 使用视图
14. SELECT * FROM product_inventory_view WHERE quantity < 50;
16. -- 删除视图
17. DROP VIEW product_inventory_view;

复制代码

8.3 事务

事务是SQLite的一个重要特性，它确保数据库操作的原子性、一致性、隔离性和持久性（ACID属性）。

2. -- 开始事务
3. BEGIN TRANSACTION;
5. -- 执行多个SQL语句
6. UPDATE inventory SET quantity = quantity - 10 WHERE product_id = 1;
7. UPDATE inventory SET quantity = quantity + 10 WHERE product_id = 2;
9. -- 提交事务
10. COMMIT;
12. -- 如果出现问题，回滚事务
13. -- ROLLBACK;

复制代码

8.4 触发器

触发器是在特定数据库事件（如INSERT、UPDATE或DELETE）发生时自动执行的特殊函数。

2. -- 创建触发器，在插入新记录时自动设置创建时间
3. CREATE TRIGGER set_created_at
4. AFTER INSERT ON products
5. FOR EACH ROW
6. BEGIN
7.     UPDATE products
8.     SET created_at = CURRENT_TIMESTAMP
9.     WHERE id = NEW.id;
10. END;
12. -- 删除触发器
13. DROP TRIGGER set_created_at;

复制代码

8.5 子查询

子查询是嵌套在另一个查询中的查询，可以用于复杂的数据检索。

2. -- 使用子查询查找库存量最少的产品
3. SELECT * FROM products
4. WHERE id = (
5.     SELECT product_id FROM inventory
6.     ORDER BY quantity ASC
7.     LIMIT 1
8. );
10. -- 使用子查询查找价格高于平均价格的产品
11. SELECT * FROM products
12. WHERE price > (
13.     SELECT AVG(price) FROM products
14. );

复制代码

8.6 连接（JOIN）

连接用于根据两个或多个表之间的相关字段将它们结合起来。

2. -- 内连接（INNER JOIN）
3. SELECT p.name, i.quantity, i.location
4. FROM products p
5. INNER JOIN inventory i ON p.id = i.product_id;
7. -- 左连接（LEFT JOIN）
8. SELECT p.name, i.quantity, i.location
9. FROM products p
10. LEFT JOIN inventory i ON p.id = i.product_id;
12. -- 交叉连接（CROSS JOIN）
13. SELECT p.name, s.name AS supplier_name
14. FROM products p
15. CROSS JOIN suppliers s;

复制代码

9. 在不同编程语言中使用SQLite

SQLite可以与多种编程语言一起使用。下面介绍如何在几种常见的编程语言中使用SQLite。

9.1 Python中使用SQLite

Python内置了sqlite3模块，可以方便地操作SQLite数据库。

2. import sqlite3
4. # 连接到数据库（如果不存在，则会创建）
5. conn = sqlite3.connect('example.db')
7. # 创建一个游标对象
8. cursor = conn.cursor()
10. # 创建表
11. cursor.execute('''
12. CREATE TABLE IF NOT EXISTS users (
13.     id INTEGER PRIMARY KEY AUTOINCREMENT,
14.     name TEXT NOT NULL,
15.     email TEXT NOT NULL UNIQUE,
16.     age INTEGER
17. )
18. ''')
20. # 插入数据
21. cursor.execute('''
22. INSERT INTO users (name, email, age)
23. VALUES (?, ?, ?)
24. ''', ('张三', 'zhangsan@example.com', 25))
26. # 查询数据
27. cursor.execute('SELECT * FROM users')
28. users = cursor.fetchall()
29. for user in users:
30.     print(user)
32. # 更新数据
33. cursor.execute('''
34. UPDATE users
35. SET age = ?
36. WHERE name = ?
37. ''', (26, '张三'))
39. # 删除数据
40. cursor.execute('''
41. DELETE FROM users
42. WHERE name = ?
43. ''', ('张三',))
45. # 提交事务
46. conn.commit()
48. # 关闭连接
49. conn.close()

复制代码

9.2 Java中使用SQLite

在Java中使用SQLite需要添加SQLite JDBC驱动程序。可以通过Maven或Gradle添加依赖，或者直接下载JAR文件。

2. import java.sql.Connection;
3. import java.sql.DriverManager;
4. import java.sql.PreparedStatement;
5. import java.sql.ResultSet;
6. import java.sql.SQLException;
7. import java.sql.Statement;
9. public class SQLiteExample {
10.     public static void main(String[] args) {
11.         Connection connection = null;
12.         try {
13.             // 连接到SQLite数据库
14.             connection = DriverManager.getConnection("jdbc:sqlite:example.db");
15.             Statement statement = connection.createStatement();
16.             
17.             // 创建表
18.             statement.execute("CREATE TABLE IF NOT EXISTS users (" +
19.                              "id INTEGER PRIMARY KEY AUTOINCREMENT, " +
20.                              "name TEXT NOT NULL, " +
21.                              "email TEXT NOT NULL UNIQUE, " +
22.                              "age INTEGER)");
23.             
24.             // 插入数据
25.             PreparedStatement preparedStatement = connection.prepareStatement(
26.                 "INSERT INTO users (name, email, age) VALUES (?, ?, ?)");
27.             preparedStatement.setString(1, "张三");
28.             preparedStatement.setString(2, "zhangsan@example.com");
29.             preparedStatement.setInt(3, 25);
30.             preparedStatement.executeUpdate();
31.             
32.             // 查询数据
33.             ResultSet resultSet = statement.executeQuery("SELECT * FROM users");
34.             while (resultSet.next()) {
35.                 System.out.println("ID: " + resultSet.getInt("id"));
36.                 System.out.println("Name: " + resultSet.getString("name"));
37.                 System.out.println("Email: " + resultSet.getString("email"));
38.                 System.out.println("Age: " + resultSet.getInt("age"));
39.                 System.out.println("----------------------");
40.             }
41.             
42.             // 更新数据
43.             preparedStatement = connection.prepareStatement(
44.                 "UPDATE users SET age = ? WHERE name = ?");
45.             preparedStatement.setInt(1, 26);
46.             preparedStatement.setString(2, "张三");
47.             preparedStatement.executeUpdate();
48.             
49.             // 删除数据
50.             preparedStatement = connection.prepareStatement(
51.                 "DELETE FROM users WHERE name = ?");
52.             preparedStatement.setString(1, "张三");
53.             preparedStatement.executeUpdate();
54.             
55.         } catch (SQLException e) {
56.             System.err.println(e.getMessage());
57.         } finally {
58.             try {
59.                 if (connection != null) {
60.                     connection.close();
61.                 }
62.             } catch (SQLException e) {
63.                 System.err.println(e.getMessage());
64.             }
65.         }
66.     }
67. }

复制代码

9.3 JavaScript/Node.js中使用SQLite

在Node.js中，可以使用sqlite3包来操作SQLite数据库。

2. const sqlite3 = require('sqlite3').verbose();
4. // 创建数据库连接
5. let db = new sqlite3.Database('./example.db', (err) => {
6.     if (err) {
7.         return console.error(err.message);
8.     }
9.     console.log('Connected to the SQLite database.');
10. });
12. // 创建表
13. db.run(`CREATE TABLE IF NOT EXISTS users (
14.     id INTEGER PRIMARY KEY AUTOINCREMENT,
15.     name TEXT NOT NULL,
16.     email TEXT NOT NULL UNIQUE,
17.     age INTEGER
18. )`, (err) => {
19.     if (err) {
20.         return console.error(err.message);
21.     }
22.     console.log('Table created successfully.');
23. });
25. // 插入数据
26. db.run(`INSERT INTO users (name, email, age) VALUES (?, ?, ?)`,
27.     ['张三', 'zhangsan@example.com', 25],
28.     function(err) {
29.         if (err) {
30.             return console.error(err.message);
31.         }
32.         console.log(`A row has been inserted with rowid ${this.lastID}`);
33.     });
35. // 查询数据
36. db.all(`SELECT * FROM users`, [], (err, rows) => {
37.     if (err) {
38.         throw err;
39.     }
40.     rows.forEach((row) => {
41.         console.log(row);
42.     });
43. });
45. // 更新数据
46. db.run(`UPDATE users SET age = ? WHERE name = ?`,
47.     [26, '张三'],
48.     function(err) {
49.         if (err) {
50.             return console.error(err.message);
51.         }
52.         console.log(`Row(s) updated: ${this.changes}`);
53.     });
55. // 删除数据
56. db.run(`DELETE FROM users WHERE name = ?`,
57.     ['张三'],
58.     function(err) {
59.         if (err) {
60.             return console.error(err.message);
61.         }
62.         console.log(`Row(s) deleted: ${this.changes}`);
63.     });
65. // 关闭数据库连接
66. db.close((err) => {
67.     if (err) {
68.         return console.error(err.message);
69.     }
70.     console.log('Close the database connection.');
71. });

复制代码

9.4 C#中使用SQLite

在C#中使用SQLite需要添加System.Data.SQLite包。可以通过NuGet包管理器安装。

2. using System;
3. using System.Data.SQLite;
5. class SQLiteExample
6. {
7.     static void Main()
8.     {
9.         string connectionString = "Data Source=example.db;Version=3;";
10.         
11.         using (SQLiteConnection connection = new SQLiteConnection(connectionString))
12.         {
13.             connection.Open();
14.             
15.             // 创建表
16.             using (SQLiteCommand command = new SQLiteCommand(connection))
17.             {
18.                 command.CommandText = @"
19.                     CREATE TABLE IF NOT EXISTS users (
20.                         id INTEGER PRIMARY KEY AUTOINCREMENT,
21.                         name TEXT NOT NULL,
22.                         email TEXT NOT NULL UNIQUE,
23.                         age INTEGER
24.                     )";
25.                 command.ExecuteNonQuery();
26.             }
27.             
28.             // 插入数据
29.             using (SQLiteCommand command = new SQLiteCommand(connection))
30.             {
31.                 command.CommandText = "INSERT INTO users (name, email, age) VALUES (@name, @email, @age)";
32.                 command.Parameters.AddWithValue("@name", "张三");
33.                 command.Parameters.AddWithValue("@email", "zhangsan@example.com");
34.                 command.Parameters.AddWithValue("@age", 25);
35.                 command.ExecuteNonQuery();
36.             }
37.             
38.             // 查询数据
39.             using (SQLiteCommand command = new SQLiteCommand("SELECT * FROM users", connection))
40.             {
41.                 using (SQLiteDataReader reader = command.ExecuteReader())
42.                 {
43.                     while (reader.Read())
44.                     {
45.                         Console.WriteLine($"ID: {reader["id"]}");
46.                         Console.WriteLine($"Name: {reader["name"]}");
47.                         Console.WriteLine($"Email: {reader["email"]}");
48.                         Console.WriteLine($"Age: {reader["age"]}");
49.                         Console.WriteLine("----------------------");
50.                     }
51.                 }
52.             }
53.             
54.             // 更新数据
55.             using (SQLiteCommand command = new SQLiteCommand(connection))
56.             {
57.                 command.CommandText = "UPDATE users SET age = @age WHERE name = @name";
58.                 command.Parameters.AddWithValue("@age", 26);
59.                 command.Parameters.AddWithValue("@name", "张三");
60.                 command.ExecuteNonQuery();
61.             }
62.             
63.             // 删除数据
64.             using (SQLiteCommand command = new SQLiteCommand(connection))
65.             {
66.                 command.CommandText = "DELETE FROM users WHERE name = @name";
67.                 command.Parameters.AddWithValue("@name", "张三");
68.                 command.ExecuteNonQuery();
69.             }
70.         }
71.     }
72. }

复制代码

10. 常见问题和解决方法

在使用SQLite的过程中，可能会遇到一些常见问题。下面列出了一些常见问题及其解决方法。

10.1 数据库锁定错误

问题：操作数据库时收到”database is locked”错误。

原因：这通常发生在多个进程或线程尝试同时写入数据库时。

解决方法：

• 确保在完成数据库操作后正确关闭连接
• 使用WAL（Write-Ahead Logging）模式：PRAGMA journal_mode=WAL;
• 使用事务来批量操作，减少锁定时间
• 考虑使用队列来序列化写入操作

10.2 数据库文件损坏

问题：数据库文件损坏，无法访问数据。

原因：可能是由于磁盘故障、系统崩溃或其他意外情况导致的。

解决方法：

• 使用SQLite的命令行工具尝试恢复：sqlite3 corrupted.db ".recover" | sqlite3 recovered.db
• 从备份中恢复数据库
• 使用PRAGMA命令检查完整性：PRAGMA integrity_check;

10.3 性能问题

问题：数据库操作变得缓慢。

原因：可能是由于缺少索引、大量数据或复杂查询导致的。

解决方法：

• 为经常查询的列创建索引
• 使用EXPLAIN QUERY PLAN分析查询执行计划
• 优化SQL查询，避免不必要的复杂操作
• 考虑分表或分库来处理大量数据
• 使用事务批量操作，减少磁盘I/O

10.4 内存不足

问题：处理大型数据库时出现内存不足错误。

原因：SQLite尝试将大量数据加载到内存中。

解决方法：

• 使用分页查询，一次只获取部分数据
• 调整SQLite的缓存大小：PRAGMA cache_size = -2000;（设置为2000KB）
• 使用游标逐步处理数据，而不是一次性加载所有数据

10.5 并发访问问题

问题：多个用户或进程同时访问数据库时出现问题。

原因：SQLite的并发访问能力有限。

解决方法：

• 使用WAL模式提高并发性：PRAGMA journal_mode=WAL;
• 设置适当的超时时间：PRAGMA busy_timeout = 5000;（5秒）
• 考虑使用连接池管理数据库连接
• 对于高并发场景，考虑使用其他数据库系统

11. 总结和进一步学习的资源

本教程介绍了SQLite的基础知识、基本操作和高级特性，并通过两个实际案例展示了如何使用SQLite构建简单的数据库应用程序。SQLite是一个功能强大、易于使用的轻量级数据库系统，适用于各种应用程序，特别是移动应用和桌面应用。

要进一步提高你的SQLite技能，可以参考以下资源：

11.1 官方资源

• SQLite官方网站：提供完整的文档、下载和支持
• SQLite语法文档：详细的SQL语法参考
• SQLite PRAGMA语句：PRAGMA语句的完整参考

11.2 书籍

• 《SQLite权威指南》- Grant Allen和Mike Owens著
• 《The Definitive Guide to SQLite》- Michael Owens著
• 《SQL基础教程》- MICK著

11.3 在线教程和课程

• W3Schools SQLite教程
• Tutorialspoint SQLite教程
• SQLite Python教程

11.4 工具和资源

• DB Browser for SQLite：一个可视化的SQLite数据库管理工具
• SQLiteStudio：另一个免费的SQLite数据库管理工具
• SQLite Explorer：VS Code的SQLite扩展

通过不断实践和学习，你将能够充分利用SQLite的强大功能，构建高效、可靠的应用程序。无论你是初学者还是有经验的开发者，SQLite都是一个值得掌握的数据库系统。

版权声明

1、转载或引用本网站内容(SQLite数据库入门教程通过简单案例快速掌握轻量级数据库操作技巧与实战应用适合初学者)须注明原网址及作者(威震华夏关云长)，并标明本网站网址(https://www.pixtech.cc/)。

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

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

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