SQLite数据库使用笔记与文档翻译草稿

清疚 2022-03-22 13:55 132阅读 0赞

SQLite

SQLite是一个SQL数据库引擎，该引擎具有自包含，无服务器，零配置，事务性等特征。SQLite是目前被部署最广泛的一个SQL数据库引擎，并且其是开源的项目。

**开始使用**

首先要下载对应的预编译库，或者下载源码自己进行编译。

*创建新的数据库*：

在shell 或 dos 命令行中，输入：”sqlite3 test.db”，这条命令将创建一个新的数据库，名字为”test.db”。

在命令行行输入SQL命令，创建和使用新的这个数据库。

注：对已有的数据库输入该命令表示进入数据库操作命令行，即可对这个已有的数据库进行操作，否则建立新的数据库。

*SQLite命令*

在SQLite中包含了一个简单的命令行工具，sqlite3.exe，通过它用户可以手动输入SQL命令来操作SQLite数据库。如下提供了对sqlite3编程的简单介绍。

开始

启动sqlite3，直接输入sqlite3 紧跟着SQLite的数据库名字。如果数据库不存在，则自动创建一个新的数据库。SQLite3程序会提示输入SQL语句。输入SQL语句，要以分号结尾，按Enter键，则执行SQL语句。

例如创建一个ex1数据库，并且创建tbl1表如下：

> sqlite3 ex1

SQLite version 3.6.11

Enter ".help" for instructions

Enter SQL statements terminated with a ";"

创建或打开数据库成功，进入SQLite命令行。

创建数据表

sqlite> create table tbl1(one varchar(10), two smallint);

插入数据：

sqlite> insert into tbl1 values('hello!',10);

sqlite> insert into tbl1 values('goodbye', 20);

查询所有数据

sqlite> select * from tbl1;

hello!|10

goodbye|20

sqlite>

可以使用文件结束符，通常是Ctrl-D，来终止sqlite3程序。使用中断字符，通常是Ctrl-C来停止一个正在运行的SQL语句。

确保在SQL语句结束时输入一个分号，SQLite3以分号作为确认SQL命令输入完成的标识。如果没有分号，直接回车，则SQLite3会给出提示符，提示继续输入命令。这样可以将一个SQL语句扩展到多行中。

sqlite> CREATE TABLE tbl2 (

...>   f1 varchar(30) primary key,

...>   f2 text,

...>   f3 real

...> );

sqlite>

查询SQLITE\_MASTER表

SQLite数据库的数据库模式是存在一个特殊表中的，叫做”sqlite\_master”，可以执行Select语句，查询该表中的内容。

sqlite> select * from sqlite_master;

type = table

name = tbl1

tbl_name = tbl1

rootpage = 3

sql = create table tbl1(one varchar(10), two smallint)

sqlite>

不能对sqlite\_master表做Drop table，update，insert，或delete操作。Sqlite\_master表是在创建或删除表的时候自动更新的。不能对该表做手工的修改。

*Sqlite3的特殊命令*

很多时候，sqlite3仅仅读入或输入几行到SQLite库来执行，但是如果一个命令以点开始”.”，那么这一行被截取，将由sqlite3程序自己进行解释。这些点命令用于改变查询的输出格式，或者执行特定的预先打包的查询语句。

可用的特殊命令可以通过.help进行查询：

sqlite>.help

.backup ?DB?FILE 将数据库DB (默认是"main") 备份到文件FILE

.bailON|OFF 在遇到错误时停止，默认是OFF

.databases 列举出附加的数据库的名字和文件。

.dump ?TABLE?... 将数据库表转换为一个SQL文本的格式

.echoON|OFF 关闭命令行的echo输出

.exit 退出SQLite3程序

.explainON|OFF 输出模式符合纯文本是否开启。

.genfkey?OPTIONS? 选项如下：

\--no-drop: 不要烧出老的fkey触发器

\--ignore-errors: 忽略表的fkey错误

\--exec: 立即执行生成的SQL

参考源文件中的tool/genfkey.README

.header(s)ON|OFF 显示头关闭或开启

.help 显示本条信息

.import FILETABLE 从FILE导入数据到TABLE表中

.indicesTABLE 显示表TABLE所有的目录名字

.iotraceFILE 将IO诊断日志写入FILE中

.load FILE?ENTRY? 加载一个扩展库

.mode MODE?TABLE? 设置输出模式，MODE如下：

csv 逗号分隔值的形式

column 左对齐的 (See .width)

html HTML <table>代码形式

insert SQL插入表TABLE的语句的形式

line 每行一个值

list 值有分隔符字符串分割

tabs Tab符分割值的形式

tcl TCL列表元素的模式

.nullvalueSTRING 在NULL位置打印STRING

.outputFILENAME 将输出值写入FILENAME文件中

.outputstdout 将输出写到屏幕

.prompt MAINCONTINUE 替换标准的提示符

.quit 退出本程序

.readFILENAME 执行文件FILENAME的SQL语句

.restore ?DB?FILE 重存来自FILE文件中DB(default"main")的内容

.schema?TABLE? 显示TABLE的创建语句

.separatorSTRING 修改output模式和.import中的分隔符

.show 显示当前各种设置的值。

.tables?PATTERN? 列举符合LIKE模式的表名字。

.timeout MS 尝试打开被锁表的超时时间设置

.timer ON|OFF 开启或关闭CPU定时器测量

.width NUM NUM... 为column模式设置列宽

修改输出模式：

SQLite3可以以八种不同的格式显示查询的结果：csv等，可以使用.mode命令在这八种模式之间进行转换。

默认的模式是list模式。

sqlite> .mode list

sqlite> select \* from tbl1;

hello|10

goodbye|20

sqlite>

将分隔符设置为逗号加一个空格的模式：

sqlite>.separator ", "

sqlite>select \* from tbl1;

hello, 10

goodbye, 20

sqlite>

line模式中，一行中的每一列都是在一个单独行显示。每一行由列名，等号以及列数据组成。连续数据由空行分割。

sqlite>.mode line

sqlite>select \* from tbl1;

one = hello

two = 10

one = goodbye

two = 20

sqlite>

在列模式中，每一记录都是以一行进行显示，例如：

sqlite>.mode column

sqlite>select \* from tbl1;

one two

\---------- ----------

hello 10

goodbye 20

sqlite>

默认情况下每一列是10个字符宽，可以使用”.width”调整列宽来适应内容。例如：

sqlite>.width 12 6

sqlite>select \* from tbl1;

one two

\------------ ------

hello 10

goodbye 20

sqlite>

将结果写入一个文件：

默认的情况下，sqlite3将查询结果写入标准输出，也就是屏幕上，可以使用.output命令将查询结果输出到文件。

sqlite>.mode list

sqlite>.separator |

sqlite>.output test\_file\_1.txt

sqlite>select \* from tbl1;

sqlite> .exit

$ cattest\_file\_1.txt

hello|10

goodbye|20

插叙数据库的模式：

有几个方便的命令可以查询数据库的模式。

例如列举数据库中的表的名字，可以输入”.tables”

sqlite>.tables

tbl1

tbl2

sqlite>

此命令相当于执行了一个对sqlite\_master表的联合查询

SELECTname FROM sqlite\_master

WHERE type IN('table','view') AND name NOT LIKE 'sqlite\_%'

UNION ALL

SELECT name FROMsqlite\_temp\_master

WHERE type IN('table','view')

ORDER BY 1

.schema命令，如果不带有参数，该命令输出原始的Create Table或Create Index语句，如果以数据表作为参数，则给出创建该数据表的原始语句。

sqlite>.schema

create tabletbl1(one varchar(10), two smallint)

CREATE TABLEtbl2 (

f1 varchar(30) primary key,

f2 text,

f3 real

)

sqlite>.schema tbl2

CREATE TABLEtbl2 (

f1 varchar(30) primary key,

f2 text,

f3 real

)

sqlite>

剩余命令详见原文档。

**使用SQLite写程序**

如下是一个简单的TCL程序，说明了如何使用SQLite的TCL接口。这个程序在第一个参数给定的数据库中执行第二个参数给定的给的SQL语句。要查看该命令则由第七行的sqlite3命令给出，本命令打开SQLite数据库，创建爱你一个新的TCL命令“db”去访问数据库。第八行的命令访问数据库，最后一行则是关闭数据库。

01 \#!/usr/bin/tclsh

02 if \{$argc!=2\} \{

03 puts stderr "Usage: %s DATABASESQL-STATEMENT"

04 exit 1

05 \}

06 load /usr/lib/tclsqlite3.so Sqlite3

07 sqlite3 db \[lindex $argv 0\]

08 db eval \[lindex $argv 1\] x \{

09 foreach v $x(\*) \{

10 puts "$v = $x($v)"

11 \}

12 puts ""

13 \}

14 db close

如下是一个简单的C程序表示如何使用SQLite的C/C++接口。数据库的名字由第一个参数给出，第二个参数是一个或更多条的SQl语句。需要注意的函数调用是22行的sqlite3\_open()，它用于打开数据库，28行的sqlite3\_exec()语句用以执行SQL语句，以及33行的sqlite3\_close()语句用于关闭数据库连接。

参考Introduction To The SQLite C/C++Interface中的介绍概述，以及SQLite的十几个接口函数。

01 \#include <stdio.h>

02 \#include <sqlite3.h>

04 static int callback(void \*NotUsed, int argc,char \*\*argv, char \*\*azColName)\{

05 int i;

06 for(i=0; i<argc; i++)\{

07 printf("%s = %s\\n",azColName\[i\], argv\[i\] ? argv\[i\] : "NULL");

08 \}

09 printf("\\n");

10 return 0;

11 \}

13 int main(int argc, char \*\*argv)\{

14 sqlite3 \*db;

15 char \*zErrMsg = 0;

16 int rc;

18 if( argc!=3 )\{

19 fprintf(stderr, "Usage: %s DATABASESQL-STATEMENT\\n", argv\[0\]);

20 return(1);

21 \}

22 rc = sqlite3\_open(argv\[1\], &db);

23 if( rc )\{

24 fprintf(stderr, "Can't open database:%s\\n", sqlite3\_errmsg(db));

25 sqlite3\_close(db);

26 return(1);

27 \}

28 rc = sqlite3\_exec(db, argv\[2\], callback, 0,&zErrMsg);

29 if( rc!=SQLITE\_OK )\{

30 fprintf(stderr, "SQL error:%s\\n", zErrMsg);

31 sqlite3\_free(zErrMsg);

32 \}

33 sqlite3\_close(db);

34 return 0;

35 \}

**SQLite理解的SQL语句**

SQLite可以理解大多数标准的SQL语句，但是它也省略了一些特征，并且加入了一些自己的特征。如下的教程明确地描述了SQLite所支持和不支持的SQL语言。下面提供了一个SQL关键字列表。

具体的SQL语句省略，参见英文的教程中“SQL As Understood By SQLite”一节

**SQLite的C语言接口**

如下的页面描述了SQLite的C语言接口。下面不是一个教程，这些页面中详细描述了接口，不太容易阅读。详细的可以参考。SQLite In 3 Minutes Or Less，或 Introduction To SQLite C/C++ Interface。

因此下面我们也仅对这两个教程进行说明，需要参考完整的SQLite的C语言接口的可以参考SQLite的帮助文档中的接口文档。

如下是：AnIntroduction To The SQLite C/C++ Interface文档：

本文档提供了一个SQLite的 C/C++接口的简单介绍。更早期的SQLite更加容易学习，因为它仅仅有5个C/C++接口。但是SQLite的性能已经增大，加入了新的C/C++接口，因此目前有185个独立的API。这个接口数量对于新人来说太多了。幸好大多数的接口是用于专门的功能的，绝对不需要使用。尽管有这么多的接口，但是核心的API仍然是相对简单，并且很容易用于编码。文章旨在提供易于理解SQLite如何工作的简单的背景信息。

TheSQLite C/C++ Interface一文中提供了所有的C/C++ APIs的详细描述。一旦读者理解了操作SQLite的基本原理，那个文档应该作为一个参考指南。本文仅仅是一个介绍，既不是SQLite的完整的文档也不是权威的参考。

**1.0 核心对象和接口**

SQL数据库引擎最重要的任务就是评估SQL语句。为了完成这个任务，开发人员需要知道两个东西：

数据库连接对象：sqlite3

准备语句对象： sqlite3\_stmt

严格来讲，准备语句对象并不需要，因为这些普通的包装接口，例如sqlite3\_exec或sqlite\_get\_table，已经封装隐藏了准备语句对象。尽管这样，理解准备语句对象可以更好地使用SQLite。

数据库连接对象和准备语句对象有一些撮C/C++接口例程控制：

sqlite3\_open()

sqlite3\_prepare()

sqlite3\_column()

sqlite3\_finalize()

sqlite3\_close()

这六个C/C++接口例程和两个对象构成了SQLite的核心功能。开发者理解这些可以帮助更好地使用SQLite。

注意这个例程列表是概念的，而不是实际的。这些例程中有一些有很多个版本。例如上面列表中有一个例程名字为sqlite3\_open()，实际上有三个独立的例程来完成相同的任务，但是有一些小小的区别：sqlite3\_open()，sqlite3\_open16()，sqlite3\_open\_v2()。列表中有sqlite3\_column()，实际上就没有这个例程存在。本条仅仅用于表明一个完整的例程家族，他们可以用于抽取不同类型的列的数据。

如下是这些核心接口所作工作的总结：

sqlite3\_open()这个例程打开一个到SQLite数据库的连接，返回一个数据库连接对象。这一条往往是应用程序首先实行的例程，是其他SQLite API的预备。许多SQLite的接口要求一个到数据库连接对象的指针作为第一个参数，可以被当作是数据库连接对象的方法。这个例程是数据库连接对象的例程。

Sqlite3\_prepare() 这个函数将SQL的文本转换为一个准备语句对象，返回一个到这个对象的指针。接口要求一个连接对象指针，以及一个SQL语句的文本字符串。这条API并不实际地球SQL 语句的值，它只是准备用于求值的SQL语句。

注意：sqlite3\_prepare()并不推荐用于新的应用程序，推荐使用sqlite3\_prepare\_v2()。

sqlite3\_step() 用于求一个准备语句的值，也即前面sqlite3\_prepare()方法准备的准备语句。这条语句被求值，结果指向可用结果的第一行。要想转向第二行数据，再次调用sqlite3\_step()。继续调用sqlite3\_step()知道语句完成。不返回结果的语句，例如insert，update等，只需要调用一次本方法即可。

sqlite3\_column()方法从结果集的当前行中返回一列，每一次sqlite3\_step()停在一行新的数据时，可以多次调用本方法获取这一行的数据。正如上述注意中描述的，在实际中没有本方法。作为替代，这里调用sqlite3\_column()是作为整个返回一行中单列数据的函数族的占位符。有一些例程返回的是结果的大小，以及结果集中的列数。

sqlite3\_column\_blob()

sqlite3\_column\_bytes()

sqlite3\_column\_bytes16()

sqlite3\_column\_count()

sqlite3\_column\_double()

sqlite3\_column\_int()

sqlite3\_column\_int64()

sqlite3\_column\_text()

sqlite3\_column\_text16()

sqlite3\_column\_type()

sqlite3\_column\_value()

sqlite3\_finalize()这个例程用于销毁一个准备语句对象，每一个准备语句必须通过调用这个例程来进行销毁，以免造成内存泄漏。

sqlite3\_close()这个例程用于关闭数据库连接，与该连接相关的准备语句应该在调用本close函数之前调用finalized方法销毁掉。

**1.1 核心例程与对象的典型使用方法**

一个应用程序想要使用SQLite，那么初始化时需要使用sqlite3\_open()来创建一个简单的数据库连接对象。注意sqlite3\_open();可以用于打开一个存在的数据库文件或者创建和打开一个新的数据库文件爱你。尽管许多应用程序仅仅使用一个数据库连接，但是也没有原因说明为什么不能够多次调用sqlite3\_open()方法打开多个数据库连接，到多个数据库，或到一个数据库的多个连接。有时多线程的应用程序会为每一个线程创建独立的数据库连接。注意为了访问两个或多个数据库，并不需要打开独立的数据库连接。一个单独的数据库连接可以用于访问两个或多个数据库，使用ATTACH SQL命令即可。

许多应用程序在关闭时使用sqlite3\_close()方法来销毁数据库连接，或例如，一个应用程序或许打开数据库文件作为File->Open菜单动作的，在对应的File->Close菜单中销毁相应的数据库连接。

执行SQL语句，可以按照如下的步骤：

1.使用sqlite3\_prepare()方法创建爱你一个准备语句。

2.调用sqlite3\_step()方法一次或多次来求准备语句的值

3.对于查询，可以调用sqlite3\_column()来提取结果中的数据。

4.使用sqlite3\_finalize()来销毁准备语句。

**2.0 对核心例程的方便接口包装**

sqlite3\_exec()接口是一个方便的接口，它包含了上述的四个步骤。传进sqlite3\_exec()的结构是用于处理结果集的每一行数据。sqlite3\_get\_table()是另外一个方便的接口，它也包含了上述的四个步骤，该接口不同于exec，它将查询的结果存在堆中，而不是调用回调函数。

需要注意的是不论sqlite3\_exec()方法或sqlite3\_get\_table()所做的事情，都可以使用核心例程完成。事实上，这些包装是用这些核心例程实现的。

**3.0 绑定参数和重用准备语句**

在前面的讨论中，假设每一个SQL语句只是准备一次，求值，然后销毁。然而，SQLite以内需一些准备语句求值多次，这个功能可以通过如下的方法实现。

sqlite3\_reset();

sqlite3\_bind();

在一个准备语句通过一次或多次的sqlite\_step()的调用求值后，可以通过调用sqlite3\_reset();方法进行重置，这样就可以被再次求值。对已存在的准备语句调用sqlite3\_reset();方法而不是创建爱你一个新的准备语句，就不需要再次调用sqlite3\_prepare()方法了。在许多的SQL语句中，运行sqlite3\_prepare()的次数等于或大于sqlite\_step()的次数。避免调用sqlite3\_prepare()可以得到性能的提升。

通过并不需要对一个相同的SQL语句执行多余一次。更多的是想要执行类似的语句，例如你可能想要执行一个Insert语句多次，来插入不同的值。为了实现灵活性，SQLite允许SQL语句包含一些参数，这些参数与前面求值中的值绑定。这些值可以修改，然后可以对同一个准备语句调用多次。

在SQLite中，无论哪里包含一个字符串，可以按照如下形式使用一个参数：

？

？NNN

:AAA

$AAA

@AAA

在上面的例子中，NNN是一个整数值，AAA是一个标识符，一个参数初始值为NULL，在调用sqlite3\_reset()语句后第一次或立即调用sqlite3\_step()，应用程序可以嗲用sqlite3\_bind()接口去将值附带给参数。每一次调用sqlite3\_bind()将覆盖前面的相同参数的绑定。

应用程序提前允许准备多个SQL语句，然后再需要时进行求值。对于准备语句的数量没有限制。

**4.0 扩展SQLite**

SQLite包含了一些可以扩展它功能的方法，例如：

sqlite3\_create\_collation()

sqlite3\_create\_function()

sqlite3\_create\_module()

sqlite3\_create\_collation()接口用于创建新的校对序列用于排序文本。sqlite3\_create\_function()接口用于注册新的虚拟表实现。sqlite3\_create\_module()接口用于创建新的SQL函数。这个新的函数实现使用如下的额外接口。

sqlite3\_aggregate\_context()

sqlite3\_result()

sqlite3\_user\_data()

sqlite3\_value()

所有的内建SQL功能使用这些接口实现。参考SQLite的源码，特别是date.c和func.c源文件。