无服务器 SQLite 所有主题初学者中级 SQL Windows

在 Windows 上安装和使用 SQLite

约翰·阿滕

4.91/5 (24投票s)

2014年12月7日

CPOL

12分钟阅读

122181

如何在 Windows 上安装和使用 SQLite

在使用 Visual Studio 和其他 Microsoft 工具进行 .NET 开发时，很容易忽略常见问题的替代解决方案。微软在创建紧密集成的开发工具链方面做得非常出色，现有的微软产品（免费和付费）提供了合理的默认选择，通常能够完成任务。

因此，.NET 开发人员常常不愿探索此领域之外的解决方案，或尝试可能同样出色甚至更好的替代方案来解决手头的问题。此外，学习新事物总是有学习曲线的，我们常常出于方便而选择熟悉的。

图片作者：shinichi | 保留部分权利

一些背景信息

SQLite 是一个很棒的、开源的、跨平台的、免费的文件式关系数据库。在 Windows 上创建的数据库文件可以无缝迁移到 OSX 或 Linux 操作系统。这些工具（尤其是我们在此考察的 SQLite3 命令行 CLI）在不同环境下的工作方式相同。

它也不是新事物。如果您在这里待了很长时间，您肯定知道 SQLite 已经活跃且公开开发了十多年，并在许多不同的场景和操作系统环境中使用。事实上，SQLite.org 估计 SQLite 实际上是全球部署最广泛的 SQL 数据库解决方案。他们最新的数据（尽管是 2006 年的）表明，SQLite 的部署量超过 5 亿（这个数字现在肯定更高了）。

SQLite 文档也被广泛认为在完整性和可用性方面高于平均水平，为新老用户提供了完善的规范资源，用于学习和故障排除。

SQLite 最初由 D. Richard Hipp 于 2000 年为美国海军设计，目标是允许基于 SQLite 的程序在不安装数据库管理系统的情况下运行，并且不需要系统管理员（来自 Wikipedia）。这些设计要求使得 SQLite 网站将其描述为“一个实现自包含、无服务器、零配置、事务性 SQL 数据库引擎的软件库。”

直到最近，我都没有花太多时间在 SQLite 上。然而，在开发 Biggy 项目时，我们决定核心支持的数据库系统应该是跨平台和开源的。我们想要一个完整的客户端/服务器选项，以及一个文件式关系数据库选项。在探索了其他替代方案后，我们选择了 SQLite 作为我们的文件式关系数据库。

在将 SQLite 整合到 Biggy 工作流程中时，我有机会熟悉了 SQLite，它的优点、一些缺点、需要注意的一些特殊情况，以及一些充分利用该产品的小技巧。

在本帖中，我们将熟悉在 Windows 环境中使用数据库的基础知识。下一篇帖我们将探讨与 .NET 开发和 Visual Studio 的集成。但是，俗话说得好，先吃苦再享福，所以……让我们开始命令行操作吧。

入门 - 在 Windows 上使用 SQLite

在我们在 Visual Studio 中使用 SQLite 之前，让我们先在 IDE 之外的 Windows 环境中走一遍使用 SQLite 的基本步骤。

首先，从 SQLite 下载页面下载预编译的二进制文件。至少，您需要 Win32 x86 SQLite DLL 的二进制文件，以及 SQLite x86 Command Shell 的二进制文件。将文件内容解压到一个名为 C:\SQLite3（或其他适合您需求的目录）的文件夹中。然后 将 C:\SQLite3 添加到您的 PATH 变量，这样您就可以直接从 Windows 控制台调用 SQLite Command Shell。

在您的新目录 C:\SQLite3 中，您现在应该有以下文件：

sqlite3.def
sqlite3.dll
sqlite3.exe

如果我们运行 sqlite3.exe，我们会看到一个控制台应用程序，它允许我们处理 SQLite 数据库。

SQLite 控制台

命令提示符易于使用。未带 "." 限定符输入的文本将被视为 SQL（并相应地成功或失败）。有一组以 "." 限定符开头的命令，它们是应用程序命令。上面的控制台窗口中显示了一个示例，其中指示我们使用 .open 命令打开一个数据库文件。

完整的 SQLite 控制台命令列表超出了本文的范围，但我们将在此处介绍其中最常用的命令。

打开新数据库并从 SQLite3 控制台创建一些表

SQLite3 控制台将在当前目录（或如果您在 GUI 中双击 .exe 文件，则在 .exe 文件所在的目录）中打开。让我们开始打开一个新的 Windows 终端（通常会在我们的主目录中打开），创建一个名为 sqlite_data 的新子目录，然后导航到该文件夹

C:\Users\John> mkdir sqlite_databases
C:\Users\John> cd sqlite_databases

接下来，让我们尝试使用 .open 命令。打开 sqlite3 并打开我们在刚创建的目录中的新数据库

打开 SQlite3.exe 并打开新数据库文件

C:\Users\John\sqlite_databases>sqlite3
sqlite> .open test.sqlite

您的控制台输出现在应该如下所示：

打开 SQLite3 并创建新数据库文件后的控制台输出

接下来，让我们创建几个表来玩玩。

在 SQLite 控制台中输入 SQL

请记住，不带 "." 限定符输入的纯文本将被 SQLite 控制台解释为 SQL。有几点需要注意：

SQL 文本可能跨越多行 - **按 Enter 键不会导致提示符后的文本执行**，直到用分号结束。
您可以通过不以分号结束语句而直接按 Enter 键来创建多行 SQL 语句。
SQLite 使用方括号或双引号作为对象名称的分隔符，以防文字列名无效。例如，Last Name 将 NOT 是一个有效的列名，但 [Last Name] 可以。同样，关键字 Group 不能用作列名，但 "Group" 可以。
SQLite 不区分大小写。与某些其他数据库（最著名的是 Postgresql）不同，SQL 语法和对象名称中的大小写都会被忽略。

那么，话不多说，让我们创建一个或两个表。我们将保持简单，因为我们更关心控制台如何工作，而不是 SQLite SQL 语法教程。

在单行语句中创建表

上面，我们一直输入整个 SQL 语句，并让控制台在需要时自动换行（可怜的 Windows 控制台，显示特性欠发达……）。看起来很丑，难以阅读。让我们尝试一个多行语句。

使用多行语句创建表

除了 Windows 控制台的丑陋之外，这稍微容易阅读一些。

现在让我们添加一些记录。

将记录插入测试数据库

注意到上面的 SQL 中的大小写不重要吗？是的，事实上，其中有一个语法错误。我不小心用了一个尖括号而不是括号……

现在，我们添加了一些数据。让我们把它读出来。

从 Users 表中选择数据

这里我们看到，由于不相关的原因（咳咳……我关错了窗口……），我不得不退出应用程序，然后重新进入。然而，一旦我打开了 test.sqlite 数据库，我就可以输入标准的 SELECT 语句，并返回数据。

看到了那个 ...>？那是我忘记在 SELECT 语句末尾添加分号的结果。如果您这样做了（而且您会的……），只需在继续的行上添加一个分号，语句就会执行（记住，直到 SQLite3 看到分号，它将继续将文本输入解释为更多的 SQL）。

格式化控制台输出

我们可以告诉 SQLite3 我们希望如何显示数据。例如，我们可能更喜欢表格显示，带有列和标题。为此，我们使用一些以句点开头的应用程序命令。

更改 SQLite 控制台的显示模式

我们可以使用以下两个命令来更改显示模式并在控制台输出中使用列和标题

在 SQLite3 中使用带标题的列显示模式

sqlite> .mode column
sqlite> .headers on

如果我们再次运行 SELECT 语句，输出将如下所示：

使用列和标题的控制台输出

使用 .Read 命令从 SQLite 控制台执行脚本文件

当然，在控制台中输入 SQL 会很痛苦。虽然它适用于快速粗略的查询和维护任务，但大量的工作最好通过在文本文件中编写所需的脚本，然后从控制台执行。

为了演示这一点，我们将下载我个人最喜欢的测试数据库，Chinook 数据库。Chinook 为大多数流行数据库平台提供了数据库脚本，从而提供了一种方便的方式来使用相同的数据集来评估多个平台（以及其他用途）。下载 Chinook 数据库，解压 .zip 文件，并找到 Chinook_Sqlite_AutoIncrementPKs.sql 文件。为了简单起见，将其复制到您的 sqlite_databases 文件夹中，使其位于当前目录。然后，为了简化输入，将您刚刚移动的文件重命名为“Chinook.sql”。

我们可以使用 SQLite 的 .read 命令执行 SQL 脚本。为了说明，我们将读入 Chinook 数据库。

执行此操作时，您会注意到几点。首先，控制台可能会显示错误（您可以在下面的图片中看到），但脚本仍在运行 - 错误会记录到控制台中。

其次，以当前形式执行此脚本非常慢。**这是由于 SQLite 的一个特殊情况，我们稍后会处理**，但 Chinook 数据库脚本的创建者没有解决这个问题。

使用 .Read 命令从 SQLite 控制台执行 SQL 脚本

sqlite> .read Chinook.sql

**脚本可能需要运行很长时间**，所以去喝杯咖啡或别的什么吧。您的计算机没有死机。脚本完成后，控制台将返回（实际上，我的机器上花了大约 10 分钟，但我们会解决这个问题……

<咖啡休息时间 . . .>

好的。脚本运行完毕后，让我们使用 .tables 命令查看数据库中的表列表。如果一切按预期工作，我们应该会看到我们自己的 users 和 groups 表，以及一大堆用 Chinook 数据填充的新表。

使用 .Tables 命令列出表

sqlite> .tables

我们应该看到类似这样的内容：

来自 .Tables 命令的控制台输出

那么，为什么运行那个脚本花了这么长时间？？！！

将多个操作包装在事务中以获得即时性能提升

SQLite 本质上是基于事务的。这意味着，除非您另有说明，否则每个语句都将被视为一个单独的事务，该事务必须成功或回滚。

事务是关系数据库的关键特性，并且在宏观上至关重要。然而，单独的事务会增加显著的性能开销，当我们插入（或更新，或以其他方式修改）数千条记录到多个表中时，将每次插入视为单独的事务会使速度大大降低。

这是 SQLite 的一个已知问题。我说“问题”是因为，尽管实现是故意的，但“为什么 SQLite 的插入如此慢”的解决方案并不显而易见，而且互联网上充斥着关于这个问题的各种变体。

同样，Chinook 数据库实现忽略了这一点，用于填充 Chinook 数据的大量插入被视为单独的事务，因此运行得非常慢。

这是修复方法：

如果我们查看 Chinook.sql 脚本，在每个表的插入语句之前加上 BEGIN; 语句，并在每个表的 INSERT 语句之后加上 COMMIT; 语句，我们将看到该脚本的性能提高几个数量级。

我们可以跳过将 DROP 和 CREATE 表语句包装在事务中。例如，在您喜欢的文本编辑器中打开文件，找到 Genre 表的 INSERT 语句的开头。添加一个 BEGIN; 和 COMMIT; 子句，如下所示：

将表插入包装在事务中

BEGIN;
INSERT INTO [Genre] ([Name]) VALUES ('Rock');
INSERT INTO [Genre] ([Name]) VALUES ('Jazz');
... Etc ...
INSERT INTO [Genre] ([Name]) VALUES ('Alternative');
INSERT INTO [Genre] ([Name]) VALUES ('Classical');
INSERT INTO [Genre] ([Name]) VALUES ('Opera');
COMMIT;

现在向下滚动，对每个表执行相同的操作。完成后，让我们创建一个专用的 Chinook 数据库来尝试一下。

打开 Windows 控制台，导航回 sqlite_databases 目录，运行 sqlite3，然后打开一个名为 chinook.db 的新数据库。然后使用 .read 再次执行 chinook.sql 脚本

将 Chinook 脚本读入 Chinook.db

C:\Users\John>cd sqlite_databases
C:\Users\John\sqlite_databases>sqlite3
sqlite> .open chinook.db
sqlite> .read chinook.sql

接下来，再次使用 .tables 命令查看所有表是否已创建。控制台输出应如下所示：

包装表插入到事务中后执行的控制台输出

我们看到第 1 行仍然有一些小的错误（最可能是由于文件开头的某些 Unicode 问题 - 欢迎来到脚本的世界）。但是，我们可以很容易地查看数据是否已导入：

从 Chinook Artists 表中选择艺术家

基于 GUI 的工具

我们在这里介绍的内容足以让我们从 Windows 控制台探索 SQLite 的功能，并熟悉这个很棒的小型数据库。当然，还有其他工具可用于处理 SQLite 数据库，包括一个出色的跨平台 GUI 界面，SQLiteBrowser，这是一个非常称职的 SQLite 数据库管理界面。

如前所述，SQL.org 提供的文档是一流的，还有许多其他资源。

SQLite 是一个方便、成熟、高性能的数据库，易于使用，并且可以在所有主要操作系统上运行。在 Windows 计算机上创建的数据库文件可以无缝地在 OSX 和 *Nix 操作系统之间迁移，大多数用于处理它们的工具也是如此。

我喜欢从最基本的可用工具开始，一旦我对系统有了扎实的理解，就转向更高级的工具。花些时间通过基本的 CLI 界面来了解 SQLite。您不会后悔的。

其他资源和感兴趣的项目

John on Google

CodeProject