SQLite 入门

什么是SQLite

SQLite是一个轻型的数据库，它包含在一个相对小的C库中，遵守ACID的关系型数据库管理系统。SQLite的设计目标是嵌入式的，适用于许多嵌入式产品，并且占用资源非常低，可能只需要几百K的内存就足够了。它支持Windows、Linux、Unix等主流操作系统，并且可以与很多程序语言相结合，如C#、PHP、Java等，还有ODBC接口。与Mysql和PostgreSQL这两款开源的世界著名数据库管理系统相比，它的处理速度更快。SQLite的特点是它不需要一个单独的服务器进程或系统操作，它直接访问存储文件。它也具有zero-configuration的特点，意味着不需要安装或管理，一个完整的SQLite数据库是存储在一个跨平台的磁盘文件中的。

为什么需要SQLite

可扩展性和可控制性：由于SQLite是通过应用程序嵌入进去的，所以它运行在相同的地址空间里，并且可以代你执行应用程序代码。
管理简单：SQLite的数据库以一种单个文件的形式存在，且具备稳定的格式，易于管理。
稳定性高：SQLite由一些真正了不起的软件工程师积极开发，高品质的新功能正以一种惊人的速度被添加进来，使得SQLite具有高度的稳定性。
高效：SQLite具有高端数据库中的功能，例如全文本索引和对JSON数据的支持。它还提供了一种快速而强大的方法来存储程序的配置数据。
方便使用：SQLite可以像对待本地数据库表一样使用内存中的数据或外部文件（例如CSV文件），从而提供了一种方便的方式来查询该数据。
易于部署：由于SQLite是单个独立的二进制文件，因此很容易与应用程序一起部署并根据需要随应用程序一起移动。

SQLite：SQLite数据库是与文件关联的，不需要一个独立的服务器进程。只需要使用SQLite命令行工具或SQLite库提供的API，就可以打开和操作数据库。

MySQL：MySQL是一个服务器型数据库系统，需要通过MySQL命令行工具或MySQL提供的API（如libmysqlclient）来连接和启动。

怎么使用SQLite

sqlite3

SQLite 使用时关键的文件在于db文件和驱动程序，驱动程序的安装我们后面讲，这里先安装一个sqlite工具，可以根据 .sql 文件生成 .db文件，以及操作sqlite数据。

任何工具使用之前避免不了安装，通过brew、apt-get等管理工具安装sqlite3即可，例如

$ brew install sqlite3

安装之后准备一个SQL文件，如下

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT,
    age INTEGER,
    salary REAL
);

INSERT INTO employees (name, age, salary) VALUES ('Alice', 25, 5000);
INSERT INTO employees (name, age, salary) VALUES ('Bob', 30, 6000);
INSERT INTO employees (name, age, salary) VALUES ('Charlie', 35, 7000);

使用sqlite命令生成db文件

$ sqlite3 test.db < sqlfile.sql

使用以下sqlite3 xx.db命令访问数据库数据，使用.exit语句退出程序。

$ sqlite3 test.db
SQLite version 3.43.1 2023-09-11 12:01:27
Enter ".help" for usage hints.
sqlite> .exit

使用sqlite3执行常用的SQL语句，例如

$ sqlite3 test.db
SQLite version 3.43.1 2023-09-11 12:01:27
Enter ".help" for usage hints.
sqlite> SELECT * FROM employees;
1|Alice|27|5000.0
2|Bob|30|6000.0
3|Charlie|35|7000.0
sqlite> UPDATE employees SET age = 25 WHERE id = 1;
sqlite> SELECT * FROM employees;
1|Alice|25|5000.0
2|Bob|30|6000.0
3|Charlie|35|7000.0
sqlite> DELETE FROM employees WHERE id = 3;
sqlite> SELECT * FROM employees;
1|Alice|25|5000.0
2|Bob|30|6000.0
sqlite> .exit

使用.exit可以退出。

SQL语句这里不多赘述，基本上是通用的。

驱动程序

上面的sqlite3 只是作为一个工具使用，最常用的使用sqlite方式还是通过NodeJS、C、Java等依赖包进行数据库操作。

以 NodeJS 为例，首先安装依赖

$ npm install sqlite3

常用的API有：

sqlite3.Database(filename, callback): 创建一个新的SQLite数据库连接。filename是数据库文件的路径，callback是连接成功后的回调函数。
db.run(sql, [values...,] callback): 执行一条SQL语句并且不返回结果。sql是SQL语句，values...是可选的参数，callback是执行完成后的回调函数。
db.all(sql, [values...,] callback): 执行一条SQL语句并且返回所有结果。sql是SQL语句，values...是可选的参数，callback是执行完成后的回调函数。
db.close(callback): 关闭数据库连接。callback是关闭成功后的回调函数。
db.serialize(callback): 开始一个新的事务，可以保证一系列的SQL语句的原子性。
db.deserialize(callback): 提交之前通过db.serialize()开始的事务。
db.prepare(sql, [bindParams...,] callback): 创建一个新的预处理语句。sql是SQL语句，bindParams...是可选的参数，callback是执行完成后的回调函数。可以使用bind()方法绑定参数。
db.lastInsertRowID(): 获取最后插入的行的ROWID。
db.changes(): 获取自事务开始以来进行的更改数量。
db.totalChanges(): 获取自数据库打开以来进行的更改总数。

const sqlite3 = require('sqlite3').verbose();
const db = new sqlite3.Database('./test.db');

const fetchAll = () => db.all('SELECT * FROM employees', (err, rows) => {
  if (err) {
    return console.error(err.message);
  }
  console.log('Employees:');
  rows.forEach(row => {
    console.log(row.id, row.age, row.salary);
  });
});

db.serialize(() => {
  // 在这里执行 SQL 语句  
  fetchAll()
  
  db.all('SELECT * FROM employees WHERE id = 1', (err, [row]) => {
    if (err) {
      return console.error(err.message);
    }

    db.run('UPDATE employees SET age = ? WHERE id = ?', [row.age + 1 , row.id], () => {
      fetchAll()

      db.close()
    })
  })
});

all和run的区别是run的回调中没有传入执行结果，常用于更新、删除等，all常用于查询。

更多常用API见文档

工具推荐

Web端可视化工具：sqliteviz
chrome 浏览器插件：sqlite viewer
数据库管理工具：DBeaver