SQLite数据库入门
2012-05-18 15:23:55 
阿炯
1. 介绍
SQLite 是一个开源的嵌入式关系数据库，实现自包容、零配置、支持事务的SQL数据库引擎，其特点是高度便携、使用方便、结构紧凑、高效、可靠。 与其他数据库管理系统不同，其安装和运行非常简单，在大多数情况下 - 只要确保SQLite的二进制文件存在即可开始创建、连接和使用数据库。如果正在寻找一个嵌入式数据库项目或解决方案，SQLite是绝对值得考虑。
在SQLite中的SQL92不支持的特性如下：
2. 安装
SQLite on Windows
1. 进入 SQL 下载页面：http://www.sqlite.org/download.html
2. 下载 Windows 下的预编译二进制文件包：
* sqlite-shell-win32-x86-<build#>.zip
* sqlite-dll-win32-x86-<build#>.zip
3. 注意: <build#> 是 sqlite 的编译版本号 将 zip 文件解压到你的磁盘，并将解压后的目录添加到系统的 PATH 变量中，以方便在命令行中执行 sqlite 命令。
4. 可选: 如果你计划发布基于 sqlite 数据库的应用程序，你还需要下载源码以便编译和利用其 API
* sqlite-amalgamation-<build#>.zip
SQLite on Linux
在多个 Linux 发行版提供了方便的命令来获取 SQLite：
/* For Debian /*
$ sudo apt-get install sqlite3 sqlite3-dev
/* For RedHats/*
$ yum install SQLite3 sqlite3-dev
3. 创建首个 SQLite 数据库
现在你已经安装了 SQLite 数据库，接下来我们创建首个数据库。在命令行窗口中输入如下命令来创建一个名为 test.db 的数据库。
sqlite3 test.db
创建表：
sqlite> create table mytable(id integer primary key, value text);
2 columns were created.
该表包含一个名为 id 的主键字段和一个名为 value 的文本字段。
注意: 最少必须为新建的数据库创建一个表或者视图，这么才能将数据库保存到磁盘中，否则数据库不会被创建。
接下来往表里中写入一些数据：
sqlite> insert into mytable(id, value) values(1, 'Micheal');
sqlite> insert into mytable(id, value) values(2, 'Jenny');
sqlite> insert into mytable(value) values('Francis');
sqlite> insert into mytable(value) values('Kerk');
查询数据：
sqlite> select * from test;
1|Micheal
2|Jenny
3|Francis
4|Kerk
设置格式化查询结果：
sqlite> .mode column;
sqlite> .header on;
sqlite> select * from test;
id value
----------- -------------
1 Micheal
2 Jenny
3 Francis
4 Kerk
.mode column 将设置为列显示模式，.header 将显示列名。
修改表结构，增加列：
sqlite> alter table mytable add column email text not null '' collate nocase;;
创建视图：
sqlite> create view nameview as select * from mytable;
创建索引：
sqlite> create index test_idx on mytable(value);
4. 一些有用的 SQLite 命令
显示表结构：
sqlite> .schema [table]
获取所有表和视图：
sqlite > .tables
获取指定表的索引列表：
sqlite > .indices [table ]
导出数据库到 SQL 文件：
sqlite > .output [filename ]
sqlite > .dump
sqlite > .output stdout
从 SQL 文件导入数据库：
sqlite > .read [filename ]
格式化输出数据到 CSV 格式：
sqlite >.output [filename.csv ]
sqlite >.separator ,
sqlite > select * from test;
sqlite >.output stdout
从 CSV 文件导入数据到表中：
sqlite >create table newtable ( id integer primary key, value text );
sqlite >.import [filename.csv ] newtable
备份数据库：
/* usage: sqlite3 [database] .dump > [filename] */
sqlite3 mytable.db .dump > backup.sql
恢复数据库：
/* usage: sqlite3 [database ] < [filename ] */
sqlite3 mytable.db < backup.sql
5. 常用命令盘点
注意：所有的命令开头都是一个点。确保 sqlite> 提示符与点命令之间没有空格，否则将无法正常工作。
进入命令行环境：sqlite3
打开一个控制台窗口，输入 sqlite3 回车，这时你就进入了 SQLite 命令行环境，其显示了版本号，并告诉你每一条 SQL 语句必须用分号(;)来结尾。
命令行帮助：.help
在命令行环境下输入 .help 回车，显示所有可使用的命令以及这些命令的帮助。
退出命令行环境
.quit 或者 .exit 都可以退出。
sqlite3命令的基本语法如下：
sqlite3 testDB.db
通常情况下，数据库名称在 RDBMS 内应该是唯一的。上面的命令将在当前目录下创建一个文件 testDB.db，该文件将被 SQLite 引擎用作数据库。命令在成功创建数据库文件之后将提供一个 sqlite> 提示符。另外也可以使用 .open 来建立新的数据库文件：
sqlite>.open test.db
上面的命令创建了数据库文件 test.db，位于 sqlite3 命令同一目录下。打开已存在数据库也是用 .open 命令，以上命令如果 test.db 存在则直接会打开，不存在就创建它。
一旦数据库被创建，就可以使用 SQLite 的 .databases 命令来检查它是否在数据库列表中。
导入数据：.read 数据文件
打开记事本，并将下列 SQL 语句复制到记事本中，保存为 freeoa.sql 到目录下，在命令行环境中输入
.read freeoa.sql
即将所有的数据导入到 freeoa.db 数据库中。
列出所有的数据表：.tables
显示数据库结构：.schema
显示表的结构：.schema 表名
显示语句执行时间：.timer on
导出某个表的数据：.dump 表名
整个表以 SQL 语句的形式为导出来了，但是只是显示在终端上，如何把它导出到文件中可见如下。
设置导出目标：
.output 文件名
或者
.output stdout
先运行.output cars.sql ，然后再运行.dump 命令试试看？如果要回复成导出到终端（标准输出），则运行 .output stdout。
数据显示相关命令
设置分隔符：.separator 分隔符
可以首先运行 SELECT * FROM Names，可以看到默认的分隔符是 |
运行.separator : 以后，再执行上述语句，可以看到分隔符已经变成 : 了。
设置显示模式：.mode 模式
有好几种显示模式，默认的是 list 显示模式，一般使用 column 显示模式，还有其他几种显示模式可以 .help 看 mode 相关内容。
显示标题栏：.headers on
设置每一列的显示宽度：.width w1,w2,w3...
一些内容，默认的宽度显示不下，这个命令就有用了
设置 NULL 值显示成什么样子(默认情况下NULL值什么也不显示)：.nullvalue 想要的NULL值格式
列出当前显示格式设置情况：.show
配置文件 .sqliterc
如果每次进入命令行都要重新设置显示格式，很麻烦，其实 .show 命令列出的所有设置项都可以保存到一个 .sqliterc 文件中，这样每次进入命令行就自动设置好了。该文件在 Linux 下保存在用户的 Home 目录下，在 Windows 下可以保存到任何目录下，但是需要设置环境变量让数据库引擎能找到它。
sqlite_master 表格
主表中保存数据库表的关键信息，并把它命名为 sqlite_master。
sqlite>.schema sqlite_master
CREATE TABLE sqlite_master (
type text,
name text,
tbl_name text,
rootpage integer,
sql text
);
SQLite 是不区分大小写的，但也有一些命令是大小写敏感的，比如 GLOB 和 glob 在 SQLite 的语句中有不同的含义。SQL 注释以两个连续的 "-" 字符（ASCII 0x2d）开始，并扩展至下一个换行符（ASCII 0x0a）或直到输入结束，以先到者为准，但不能嵌套。所有的 SQLite 语句可以以任何关键字开始，如 SELECT、INSERT、UPDATE、DELETE、ALTER、DROP 等，所有的语句以分号 ; 结束。
在 sqlite3 中，SQL 语句需以分号 ; 结尾才会执行，允许跨行输入。特殊的点命令（如 .help 和 .tables）以小数点 . 开头，不需要分号。
在查询数据时，SQLite 默认使用 | 分割每列数据，这可能不便于阅读。实际上，sqlite3 工具支持多种输出格式，默认为 list 模式。以下是可用的输出格式：ascii、box、csv、column、html、insert、json、line、list、markdown、quote、table。
可以使用 .mode 命令更改输出格式：
> .mode xxx
sqlite_master 是 SQLite 中的一个特殊表，其中包含了数据库的 schema 信息。可以查询这个表以获取表的创建语句和索引信息。使用 .indexes 查看索引，使用 .schema 查看 schema 详情。
使用 .output filename 命令将查询结果写入指定文件。可先使用 .mode json 更改输出为 JSON 格式，然后在查询表写出到 sql_result.json 文件中。
使用 .excel 会让下一个查询语句输出到 Excel 中。
> .excel
> select * from sqlite_schema;
使用 .read 可以读取指定文件中的 SQL 语句并运行，这在需要批量执行 SQL 脚本的场景中非常有用。
备份与恢复
在涉及数据库操作时，备份和恢复是至关重要的步骤，可用于防止数据丢失并确保数据的持续性；SQLite 提供了简单的方法来备份和恢复数据库。在 SQLite 中可以通过导出整个数据库为一个 SQL 脚本来备份数据库。此功能使用 .dump 命令实现。
$ ./sqlite3 my_sqlite.db
sqlite> .output backup.sql
sqlite> .dump
sqlite> .exit
$ cat backup.sql
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
...SQLS...
COMMIT;
这将导出整个 my_sqlite.db 数据库到 backup.sql 文件中。此 SQL 文件包含了重建数据库所需的所有 SQL 语句。要恢复数据库，只需在 sqlite3 中运行这个脚本。如恢复数据到库 my_sqlite_2 中。
$ ./sqlite3 my_sqlite_2.db
sqlite> .read backup.sql
sqlite> select * from user;
...
这将执行 backup.sql 文件中的所有 SQL 语句，重建数据库。通过以上的备份与恢复方法，可以确保你的 SQLite 数据库资料得到可靠的保护，且在需要时能够迅速恢复。
如果喜欢可视化操作，可以尝试 SQLite Database Browser 进行操作或使用DBeaver工具。
何时应该使用SQLite
嵌入式应用程序：所有需要可移植性、不需要扩展的应用程序，例如单用户的本地应用、移动应用或者游戏。
替代磁盘访问：在很多情况下，需要直接读写磁盘文件的应用程序可以切换到SQLite从而受益于SQLite提供的额外功能以及使用结构化查询语言(SQL)所带来的简便性。
测试：对大部分应用程序而言没必要使用额外的进程测试业务逻辑（例如应用程序的主要目标：功能）。
何时不应该使用SQLite
多用户应用程序：如果有多个客户端需要访问并使用同一个数据库，那么最好使用功能完整的关系型数据库(例如PostgreSQL)，而不是选择SQLite。
需要高写入量的应用程序：写操作是SQLite的一个局限。该DBMS在同一时刻仅允许一个写操作，因而也限制了其吞吐量。
SQLite 是一个开源的嵌入式关系数据库，实现自包容、零配置、支持事务的SQL数据库引擎，其特点是高度便携、使用方便、结构紧凑、高效、可靠。 与其他数据库管理系统不同，其安装和运行非常简单，在大多数情况下 - 只要确保SQLite的二进制文件存在即可开始创建、连接和使用数据库。如果正在寻找一个嵌入式数据库项目或解决方案，SQLite是绝对值得考虑。
在SQLite中的SQL92不支持的特性如下：
| 特性 | 描述 |
|---|---|
| RIGHT OUTER JOIN | 只实现了 LEFT OUTER JOIN。 |
| FULL OUTER JOIN | 只实现了 LEFT OUTER JOIN。 |
| ALTER TABLE | 支持 RENAME TABLE 和 ALTER TABLE 的 ADD COLUMN variants 命令，不支持 DROP COLUMN、ALTER COLUMN、ADD CONSTRAINT。 |
| Trigger 支持 | 支持 FOR EACH ROW 触发器，但不支持 FOR EACH STATEMENT 触发器。 |
| VIEWs | 视图是只读的，不可以在视图上执行 DELETE、INSERT 或 UPDATE 语句。 |
| GRANT 和 REVOKE | 可以应用的唯一的访问权限是底层操作系统的正常文件访问权限。 |
2. 安装
SQLite on Windows
1. 进入 SQL 下载页面：http://www.sqlite.org/download.html
2. 下载 Windows 下的预编译二进制文件包：
* sqlite-shell-win32-x86-<build#>.zip
* sqlite-dll-win32-x86-<build#>.zip
3. 注意: <build#> 是 sqlite 的编译版本号 将 zip 文件解压到你的磁盘，并将解压后的目录添加到系统的 PATH 变量中，以方便在命令行中执行 sqlite 命令。
4. 可选: 如果你计划发布基于 sqlite 数据库的应用程序，你还需要下载源码以便编译和利用其 API
* sqlite-amalgamation-<build#>.zip
SQLite on Linux
在多个 Linux 发行版提供了方便的命令来获取 SQLite：
/* For Debian /*
$ sudo apt-get install sqlite3 sqlite3-dev
/* For RedHats/*
$ yum install SQLite3 sqlite3-dev
3. 创建首个 SQLite 数据库
现在你已经安装了 SQLite 数据库，接下来我们创建首个数据库。在命令行窗口中输入如下命令来创建一个名为 test.db 的数据库。
sqlite3 test.db
创建表：
sqlite> create table mytable(id integer primary key, value text);
2 columns were created.
该表包含一个名为 id 的主键字段和一个名为 value 的文本字段。
注意: 最少必须为新建的数据库创建一个表或者视图，这么才能将数据库保存到磁盘中，否则数据库不会被创建。
接下来往表里中写入一些数据：
sqlite> insert into mytable(id, value) values(1, 'Micheal');
sqlite> insert into mytable(id, value) values(2, 'Jenny');
sqlite> insert into mytable(value) values('Francis');
sqlite> insert into mytable(value) values('Kerk');
查询数据：
sqlite> select * from test;
1|Micheal
2|Jenny
3|Francis
4|Kerk
设置格式化查询结果：
sqlite> .mode column;
sqlite> .header on;
sqlite> select * from test;
id value
----------- -------------
1 Micheal
2 Jenny
3 Francis
4 Kerk
.mode column 将设置为列显示模式，.header 将显示列名。
修改表结构，增加列：
sqlite> alter table mytable add column email text not null '' collate nocase;;
创建视图：
sqlite> create view nameview as select * from mytable;
创建索引：
sqlite> create index test_idx on mytable(value);
4. 一些有用的 SQLite 命令
显示表结构：
sqlite> .schema [table]
获取所有表和视图：
sqlite > .tables
获取指定表的索引列表：
sqlite > .indices [table ]
导出数据库到 SQL 文件：
sqlite > .output [filename ]
sqlite > .dump
sqlite > .output stdout
从 SQL 文件导入数据库：
sqlite > .read [filename ]
格式化输出数据到 CSV 格式：
sqlite >.output [filename.csv ]
sqlite >.separator ,
sqlite > select * from test;
sqlite >.output stdout
从 CSV 文件导入数据到表中：
sqlite >create table newtable ( id integer primary key, value text );
sqlite >.import [filename.csv ] newtable
备份数据库：
/* usage: sqlite3 [database] .dump > [filename] */
sqlite3 mytable.db .dump > backup.sql
恢复数据库：
/* usage: sqlite3 [database ] < [filename ] */
sqlite3 mytable.db < backup.sql
5. 常用命令盘点
注意：所有的命令开头都是一个点。确保 sqlite> 提示符与点命令之间没有空格，否则将无法正常工作。
进入命令行环境：sqlite3
打开一个控制台窗口，输入 sqlite3 回车，这时你就进入了 SQLite 命令行环境，其显示了版本号，并告诉你每一条 SQL 语句必须用分号(;)来结尾。
命令行帮助：.help
在命令行环境下输入 .help 回车，显示所有可使用的命令以及这些命令的帮助。
退出命令行环境
.quit 或者 .exit 都可以退出。
sqlite3命令的基本语法如下：
sqlite3 testDB.db
通常情况下，数据库名称在 RDBMS 内应该是唯一的。上面的命令将在当前目录下创建一个文件 testDB.db，该文件将被 SQLite 引擎用作数据库。命令在成功创建数据库文件之后将提供一个 sqlite> 提示符。另外也可以使用 .open 来建立新的数据库文件：
sqlite>.open test.db
上面的命令创建了数据库文件 test.db，位于 sqlite3 命令同一目录下。打开已存在数据库也是用 .open 命令，以上命令如果 test.db 存在则直接会打开，不存在就创建它。
一旦数据库被创建，就可以使用 SQLite 的 .databases 命令来检查它是否在数据库列表中。
导入数据：.read 数据文件
打开记事本，并将下列 SQL 语句复制到记事本中，保存为 freeoa.sql 到目录下，在命令行环境中输入
.read freeoa.sql
即将所有的数据导入到 freeoa.db 数据库中。
列出所有的数据表：.tables
显示数据库结构：.schema
显示表的结构：.schema 表名
显示语句执行时间：.timer on
导出某个表的数据：.dump 表名
整个表以 SQL 语句的形式为导出来了，但是只是显示在终端上，如何把它导出到文件中可见如下。
设置导出目标：
.output 文件名
或者
.output stdout
先运行.output cars.sql ，然后再运行.dump 命令试试看？如果要回复成导出到终端（标准输出），则运行 .output stdout。
数据显示相关命令
设置分隔符：.separator 分隔符
可以首先运行 SELECT * FROM Names，可以看到默认的分隔符是 |
运行.separator : 以后，再执行上述语句，可以看到分隔符已经变成 : 了。
设置显示模式：.mode 模式
有好几种显示模式，默认的是 list 显示模式，一般使用 column 显示模式，还有其他几种显示模式可以 .help 看 mode 相关内容。
显示标题栏：.headers on
设置每一列的显示宽度：.width w1,w2,w3...
一些内容，默认的宽度显示不下，这个命令就有用了
设置 NULL 值显示成什么样子(默认情况下NULL值什么也不显示)：.nullvalue 想要的NULL值格式
列出当前显示格式设置情况：.show
配置文件 .sqliterc
如果每次进入命令行都要重新设置显示格式，很麻烦，其实 .show 命令列出的所有设置项都可以保存到一个 .sqliterc 文件中，这样每次进入命令行就自动设置好了。该文件在 Linux 下保存在用户的 Home 目录下，在 Windows 下可以保存到任何目录下，但是需要设置环境变量让数据库引擎能找到它。
sqlite_master 表格
主表中保存数据库表的关键信息，并把它命名为 sqlite_master。
sqlite>.schema sqlite_master
CREATE TABLE sqlite_master (
type text,
name text,
tbl_name text,
rootpage integer,
sql text
);
SQLite 是不区分大小写的，但也有一些命令是大小写敏感的，比如 GLOB 和 glob 在 SQLite 的语句中有不同的含义。SQL 注释以两个连续的 "-" 字符（ASCII 0x2d）开始，并扩展至下一个换行符（ASCII 0x0a）或直到输入结束，以先到者为准，但不能嵌套。所有的 SQLite 语句可以以任何关键字开始，如 SELECT、INSERT、UPDATE、DELETE、ALTER、DROP 等，所有的语句以分号 ; 结束。
| 命令 | 描述 |
|---|---|
| .backup ?DB? FILE | 备份 DB 数据库（默认是 "main"）到 FILE 文件。 |
| .bail ON|OFF | 发生错误后停止。默认为 OFF。 |
| .databases | 列出数据库的名称及其所依附的文件。 |
| .dump ?TABLE? | 以 SQL 文本格式转储数据库。如果指定了 TABLE 表，则只转储匹配 LIKE 模式的 TABLE 表。 |
| .echo ON|OFF | 开启或关闭 echo 命令。 |
| .exit | 退出 SQLite 提示符。 |
| .explain ON|OFF | 开启或关闭适合于 EXPLAIN 的输出模式。如果没有带参数，则为 EXPLAIN on，即开启 EXPLAIN。 |
| .header(s) ON|OFF | 开启或关闭头部显示。 |
| .help | 显示消息。 |
| .import FILE TABLE | 导入来自 FILE 文件的数据到 TABLE 表中。 |
| .indices ?TABLE? | 显示所有索引的名称。如果指定了 TABLE 表，则只显示匹配 LIKE 模式的 TABLE 表的索引。 |
| .load FILE ?ENTRY? | 加载一个扩展库。 |
| .log FILE|off | 开启或关闭日志。FILE 文件可以是 stderr（标准错误）/stdout（标准输出）。 |
| .mode MODE | 设置输出模式，MODE 可以是下列之一：
|
| .nullvalue STRING | 在 NULL 值的地方输出 STRING 字符串。 |
| .output FILENAME | 发送输出到 FILENAME 文件。 |
| .output stdout | 发送输出到屏幕。 |
| .print STRING... | 逐字地输出 STRING 字符串。 |
| .prompt MAIN CONTINUE | 替换标准提示符。 |
| .quit | 退出 SQLite 提示符。 |
| .read FILENAME | 执行 FILENAME 文件中的 SQL。 |
| .schema ?TABLE? | 显示 CREATE 语句。如果指定了 TABLE 表，则只显示匹配 LIKE 模式的 TABLE 表。 |
| .separator STRING | 改变输出模式和 .import 所使用的分隔符。 |
| .show | 显示各种设置的当前值。 |
| .stats ON|OFF | 开启或关闭统计。 |
| .tables ?PATTERN? | 列出匹配 LIKE 模式的表的名称。 |
| .timeout MS | 尝试打开锁定的表 MS 毫秒。 |
| .width NUM NUM | 为 "column" 模式设置列宽度。 |
| .timer ON|OFF | 开启或关闭 CPU 定时器。 |
在 sqlite3 中，SQL 语句需以分号 ; 结尾才会执行，允许跨行输入。特殊的点命令（如 .help 和 .tables）以小数点 . 开头，不需要分号。
在查询数据时，SQLite 默认使用 | 分割每列数据，这可能不便于阅读。实际上，sqlite3 工具支持多种输出格式，默认为 list 模式。以下是可用的输出格式：ascii、box、csv、column、html、insert、json、line、list、markdown、quote、table。
可以使用 .mode 命令更改输出格式：
> .mode xxx
sqlite_master 是 SQLite 中的一个特殊表，其中包含了数据库的 schema 信息。可以查询这个表以获取表的创建语句和索引信息。使用 .indexes 查看索引，使用 .schema 查看 schema 详情。
使用 .output filename 命令将查询结果写入指定文件。可先使用 .mode json 更改输出为 JSON 格式，然后在查询表写出到 sql_result.json 文件中。
使用 .excel 会让下一个查询语句输出到 Excel 中。
> .excel
> select * from sqlite_schema;
使用 .read 可以读取指定文件中的 SQL 语句并运行，这在需要批量执行 SQL 脚本的场景中非常有用。
备份与恢复
在涉及数据库操作时，备份和恢复是至关重要的步骤，可用于防止数据丢失并确保数据的持续性；SQLite 提供了简单的方法来备份和恢复数据库。在 SQLite 中可以通过导出整个数据库为一个 SQL 脚本来备份数据库。此功能使用 .dump 命令实现。
$ ./sqlite3 my_sqlite.db
sqlite> .output backup.sql
sqlite> .dump
sqlite> .exit
$ cat backup.sql
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
...SQLS...
COMMIT;
这将导出整个 my_sqlite.db 数据库到 backup.sql 文件中。此 SQL 文件包含了重建数据库所需的所有 SQL 语句。要恢复数据库，只需在 sqlite3 中运行这个脚本。如恢复数据到库 my_sqlite_2 中。
$ ./sqlite3 my_sqlite_2.db
sqlite> .read backup.sql
sqlite> select * from user;
...
这将执行 backup.sql 文件中的所有 SQL 语句，重建数据库。通过以上的备份与恢复方法，可以确保你的 SQLite 数据库资料得到可靠的保护，且在需要时能够迅速恢复。
如果喜欢可视化操作，可以尝试 SQLite Database Browser 进行操作或使用DBeaver工具。
何时应该使用SQLite
嵌入式应用程序：所有需要可移植性、不需要扩展的应用程序，例如单用户的本地应用、移动应用或者游戏。
替代磁盘访问：在很多情况下，需要直接读写磁盘文件的应用程序可以切换到SQLite从而受益于SQLite提供的额外功能以及使用结构化查询语言(SQL)所带来的简便性。
测试：对大部分应用程序而言没必要使用额外的进程测试业务逻辑（例如应用程序的主要目标：功能）。
何时不应该使用SQLite
多用户应用程序：如果有多个客户端需要访问并使用同一个数据库，那么最好使用功能完整的关系型数据库(例如PostgreSQL)，而不是选择SQLite。
需要高写入量的应用程序：写操作是SQLite的一个局限。该DBMS在同一时刻仅允许一个写操作，因而也限制了其吞吐量。