如何创建sqlite数据库

方法/步骤

首先下载 Navicat for SQLite，下载后解压缩。

运行navicat.exe 这个主程序，在左上角点击连接

在d出窗口中输入连接名，选择类型，然后在下面选择数据库文件，或者数据库保存位置。

这样就在指定位置创建了一个Sqlite数据库文件了

SQLite库包含一个名字叫做sqlite3的命令行,它可以让用户手工输入并执行面向SQLite数据库的SQL命令。本文档提供一个样使用sqlite3的简要说明。

开始

启动sqlite3程序，仅仅需要敲入带有SQLite数据库名字的"sqlite3"命令即可。如果文件不存在，则创建一个新的（数据库）文件。然后 sqlite3程序将提示你输入SQL。敲入SQL语句（以分号“；”结束），敲回车键之后，SQL语句就会执行。

例如，创建一个包含一个表"tb11"名字为"ex1"的SQLite数据库，你可以这样做：

$sqlite3 ex1

SQLite version 3.3.17

Enter ".help" for instructions

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）或者中断字符（通常是Ctrl + C）。来终止sqlite3程序。确定你在每个SQL语句结束敲入分号！sqlite3程序通过查找分号来决定一个SQL语句的结束。如果你省略分号，sqlite3将给你一个连续的命令提示符并等你给当前的SQL命令添加更多的文字。这个特点让你输入多行的多个SQL语句，例如：

sqlite>create table tbl2(

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

...>f2 text,

...>f3 real

...>)

sqlite>

题外话：查询SQLITE_MASTER表

SQLite数据库的框架被保存在一个名叫"sqlite_master"的特殊的表中。你可以像查询其它表一样通过执行“SELECT”查询这个特殊的表。例如：

$ sqlite3 ex1

SQlite vresion 3.3.10

Enter ".help" for instructions

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表在你创建、删除和索引数据库时自动更新这个表。你不能手工更改sqlite_master表。

TEMPORARY表的结构没有存储在"sqlite_master"表中，由于TEMPORARY表对应用是不可见的，而不是应用程序创建这个表。 TEMPORARY表结构被存储在另外一个名叫"sqlite_temp_master"的特定的表中。"sqlite_temp_master"表是临时表自身。

sqlite3的特殊命令

大多数候，sqlite3读入输入行，并把它们传递到SQLite库中去运行。但是如果输入行以一个点（“.”）开始，那么这行将被sqlite3程序自己截取并解释。这些“点命令”通常被用来改变查询输出的格式，或者执行鞭个预封包（预定义prepackaged）的查询语句。

你可以在任何时候输入“.help”，列出可用的点命令。例如

sqlite>.help

.bail ON|OFF Stop after hitting an error. Default OFF

.databases List names and files of attached databases

.dump ?TABLE? ... Dump the database in an SQL text format

.echo ON|OFF Turn command echo on or off

.exit Exit this program

.explain ON|OFFTurn output mode suitable for EXPLAIN on or off.

.header(s) ON|OFF Turn display of headers on or off

.help Show this message

.import FILE TABLE Import data from FILE into TABLE

.indices TABLE Show names of all indices on TABLE

.load FILE ?ENTRY? Load an extension library

.mode MODE ?TABLE? Set output mode where MODE is one of:

csv Comma-separated values

column Left-aligned columns. (See .width)

html HTML <table>code

insert SQL insert statements for TABLE

line One value per line

list Values delimited by .separator string

tabs Tab-separated values

tcl TCL list elements

.nullvalue STRING Print STRING in place of NULL values

.output FILENAME Send output to FILENAME

.output stdout Send output to the screen

.prompt MAIN CONTINUE Replace the standard prompts

.quit Exit this program

.read FILENAME Execute SQL in FILENAME

.schema ?TABLE?Show the CREATE statements

.separator STRING Change separator used by output mode and .import

.show Show the current values for various settings

.tables ?PATTERN? List names of tables matching a LIKE pattern

.timeout MSTry opening locked tables for MS milliseconds

.width NUM NUM ... Set column widths for "column" mode

sqlite>

改变输出格式

sqlite3程序可以以八种不同的格式显示一个查询的结果："csv", "列", "html", "插入", "行", "制表"和"tcl"。你可以用".mode"点命令在这些输出格式之间切换。

默认的输出格式是“列表”。在列表模式下，每条查询结果记录被写在一行中并且每列之间以一个字符串分割符隔开。默认的分隔符是一个管道符号（“|”）。列表符号在当你输出查询结果到另外一个符加处理的程序（如AWK）中去是尤为有用。

sqlite>.mode list

sqlite>select * from tbl1

hello|10

goodbye|20

sqlite>

你可以用“.separator”点命令来改变分界符。例如，为了把分割符改为一个逗号和一个空格，你可以这样做：

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>

上面例子中".width"命令设置第一列宽为12第二列宽为6。其它的列宽不变。你可以指定与你查询结果需要的列数一样多的“.width”参数。

如果你指定一列宽为0，那么这个列宽将自动以下面三个数字中的最大值做为列宽：10、表头宽度和最宽的数据列的宽度。这可以让列自动调整宽度。每列的默认设置为自动调整的0值。

出现在输出开头两行的列标示可以用".header"点命令关闭。在上面的例子中，列标示是打开的。可以用下面的方法关闭列标示：

sqlite>.header off

sqlite>select * from tbl1

hello 10

goodbye 20

sqlite>

另外一个有用的输出模式是"insert"。在插入模式下，被子格式化为看起来像SQL INSERT语句的样式。你可以用插入模式来产生文件（便于）以后用于不同数据库的输入。

当指定插入模式时，你必须给定一个特定参数就是要插入的表名。例如：

sqlite>.mode insert new_table

sqlite>select * from tbl1

INSERT INTO 'new_table' VALUES('hello',10)

INSERT INTO 'new_table' VALUES('goodbye',20)

sqlite>

最新的输出格式是“html”。在这种模式下，sqlite3把查询的结果写做XHTML表。开始的<TABLE>和结束的</TABLE>（标记）没有写出，但有<TR>、<TH>和<TD>等分界符。html输出对 CGI来说是相当有用地。

把结果写到文件中

默认情况下，sqlte3把结送到标准输出。你可以用“.output”命令改变它。只须把输出文件名做为.output命令的输出参数然后所有后续查询结果将被写到那个文件中。用“.output stdout”再一次改为标准输出。例如：

sqlite>.mode list

sqlite>.separator |

sqlite>.output test_file_1.txt

sqlite>select * from tbl1

sqlite>.exit

$ cat test_file_1.txt

hello|10

goodbye|20

$

查询数据库结构

sqlite3程序提供几个有用的用于查询数据库结构的快捷命令。这些不是不可以用别的方式来实现。这些命令仅仅是一个快捷方式而已。

例如，为了查看数据库的表列表，你可以敲入“.tables”。

sqlite>.tables

tbl1

tbl2

sqlite>

“.tables”命令相似于设置列表模式然后执行接下来的查询：

SELECT name FROM sqlite_master

WHERE type IN ('table','view') AND name NOT LIKE 'sqlite_%'

UNION ALL

SELECT name FROM sqlite_temp_master

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

ORDER BY 1事实上，你可以查看sqlite3的源代码（可以在源文件树的src/shell.c中），你可找到上面的具体的查询。“.indices”命令作用类似的方式是列出特定表的所有的索引。“.indics”命令须一个参数即所要索引表的表名。最后，但不是至少，是“.schema”命令。不带任何参数，“.schema”命令显示原始的用于创建当前数据库的CREATE TABLE和CREATE INDEX语句。如果你给".schema"命令一个表名，它显示原始的创建该表和它所有索引的CREATE语句。我们可以：sqlite>.schemacreate table tbl1(one varchar(10), two smallint)CREATE TABLE tbl2 ( f1 varchar(30) primary key, f2 text, f3 real)sqlite>.schema tbl2CREATE TABLE tbl2 ( f1 varchar(30) primary key, f2 text, f3 real)sqlite> ".schema"命令可以用设置列表然后执行以下查询来实现：

SELECT sql FROM

(SELECT * FROM sqlite_master UNION ALL

SELECT * FROM sqlite_temp_master)

WHERE type!='meta'

ORDER BY tbl_name, type DESC, name

.databases 列出数据库文件名

.tables ?PATTERN? 列出?PATTERN?匹配的表名

.import FILE TABLE 将文件中的数据导入的文件中

.dump ?TABLE? 生成形成数据库表的SQL脚本

.output FILENAME 将输出导入到指定的文件中

.output stdout 将输出打印到屏幕

.mode MODE ?TABLE? 设置数据输出模式(csv,html,tcl…

.nullvalue STRING 用指定的串代替输出的NULL串

.read FILENAME 执行指定文件中的SQL语句

.schema ?TABLE? 打印创建数据库表的SQL语句

.separator STRING 用指定的字符串代替字段分隔符

.show 打印所有SQLite环境变量的设置

.quit 退出命令行接口

SQLite3是目前最新的SQLite版本。可以从网站上下载SQLite3的源代码（本书使用的版本是sqlite-3.6.12.tar.gz）。

解压缩后进入sqlite-3.6.12的根目录，首先命令“./configure”生成Makefile文件，接着运行命令“make”对源代码进行编译，最后运行命令“make install”安装SQLite3。安装完毕后，可以运行命令sqlite3查看SQLite3是否能正常运行，如下所示：

[root@localhost ~]# sqlite3

SQLite version 3.6.12

Enter ".help" for instructions

Enter SQL statements terminated with a ""

sqlite>

可以看到，SQLite3启动后会停留在提示符sqlite>处，等待用户输入SQL语句。

在使用SQLite3前需要先了解下SQLite3支持的数据类型。SQLite3支持的基本数据类型主要有以下几类：

NULL

NUMERIC

INTEGER

REAL

TEXT

SQLite3会自动把其他数据类型转换成以上5类基本数据类型，转换规则如下所示：

char、clob、test、varchar—>TEXT

integer—>INTEGER

real、double、float—>REAL

blob—>NULL

其余数据类型都转变成NUMERIC

下面通过一个实例来演示SQLite3的使用方法。

新建一个数据库

新建数据库test.db（使用.db后缀是为了标识数据库文件）。在test.db中新建一个表test_table，该表具有name,、sex、age三列。SQLite3的具体 *** 作如下所示：

[root@localhost home]# sqlite3 test.db

SQLite version 3.6.12

Enter ".help" for instructions

Enter SQL statements terminated with a ""

sqlite>create table test_table(name, sex, age)

如果数据库test.db已经存在，则命令“sqlite3 test.db”会在当前目录下打开test.db。如果数据库test.db不存在，则命令“sqlite3 test.db”会在当前目录下新建数据库test.db。为了提高效率，SQLite3并不会马上创建test.db，而是等到第一个表创建完成后才会在物理上创建数据库。

由于SQLite3能根据插入数据的实际类型动态改变列的类型，所以在create语句中并不要求给出列的类型。

创建索引

为了加快表的查询速度，往往在主键上添加索引。如下所示的是在name列上添加索引的过程。

sqlite>create index test_index on test_table(name)

*** 作数据

如下所示的是在test_table中进行数据的插入、更新、删除 *** 作：

sqlite>insert into test_table values ('xiaoming', 'male', 20)

sqlite>insert into test_table values ('xiaohong', 'female', 18)

sqlite>select * from test_table

xiaoming|male|20

xiaohong|female|18

sqlite>update test_table set age=19 where name = 'xiaohong'

sqlite>select * from test_table

xiaoming|male|20

xiaohong|female|19

sqlite>delete from test_table where name = 'xiaoming'

sqlite>select * from test_table

xiaohong|female|19

批量 *** 作数据库

如下所示的是在test_table中连续插入两条记录：

sqlite>begin

sqlite>insert into test_table values ('xiaoxue', 'female', 18)

sqlite>insert into test_table values ('xiaoliu', 'male', 20)

sqlite>commit

sqlite>select * from test_table

xiaohong|female|19

xiaoxue|male|18

xiaoliu|male|20

运行命令commit后，才会把插入的数据写入数据库中。

数据库的导入导出

如下所示的是把test.db导出到sql文件中：

[root@localhost home]# sqlite3 test.db ".dump" >test.sql

test.sql文件的内容如下所示：

BEGIN TRANSACTION

CREATE TABLE test_table(name, sex, age)

INSERT INTO "test_table" VALUES('xiaohong','female',19)

CREATE INDEX test_index on test_table(name)

COMMIT

如下所示的是导入test.sql文件（导入前删除原有的test.db）：

[root@localhost home]# sqlite3 test.db <test.sql

通过对test.sql文件的导入导出，可以实现数据库文件的备份。

11.2.2 SQLite3的C接口

以上介绍的是SQLite3数据库的命令 *** 作方式。在实际使用中，一般都是应用程序需要对数据库进行访问。为此，SQLite3提供了各种编程语言的使用接口（本书介绍C语言接口）。SQLite3具有几十个C接口，下面介绍一些常用的C接口。

sqlite_open

作用：打开SQLite3数据库

原型：int sqlite3_open(const char *dbname, sqlite3 **db)

参数：

dbname：数据库的名称；

db：数据库的句柄；

sqlite_colse

作用：关闭SQLite3数据库

原型：int sqlite_close(sqlite3 *db)

例如：

test.c：

#include <stdio.h>

#include <sqlite3.h>

static sqlite3 *db=NULL

int main()

{

int rc

rc= sqlite3_open("test.db", &db)

if(rc)

{

printf("can't open database!\n")

}

else

{

printf("open database success!\n")

}

sqlite3_close(db)

return 0

}

运行命令“gcc –o test test.c –lsqlite3”进行编译，运行test的结果如下所示：

[root@localhost home]# open database success!

sqlite_exec

作用：执行SQL语句

原型：int sqlite3_exec(sqlite3 *db, const char *sql, int (*callback)(void*,int,char**,char**), void *, char **errmsg)

参数：

db：数据库；

sql：SQL语句；

callback：回滚；

errmsg：错误信息

例如：

test.c：

#include <stdio.h>

#include <sqlite3.h>

static sqlite3 *db=NULL

static char *errmsg=NULL

int main()

{

int rc

rc = sqlite3_open("test.db", &db)

rc = sqlite3_exec(db,"insert into test_table values('daobao', 'male', 24)", 0, 0, &errmsg)

if(rc)

{

printf("exec fail!\n")

}

else

{

printf("exec success!\n")

}

sqlite3_close(db)

return 0

}

编译完成后，运行test的结果如下所示：

[root@localhost home]# ./test

exec success!

[root@localhost home]# sqlite3 test.db

SQLite version 3.6.11

Enter ".help" for instructions

Enter SQL statements terminated with a ""

sqlite>select * from test_table

daobao|male|24

sqlite3_get_table

作用：执行SQL查询

原型：int sqlite3_get_table(sqlite3 *db, const char *zSql, char ***pazResult, int *pnRow, int *pnColumn, char **pzErrmsg)

参数：

db：数据库；

zSql：SQL语句；

pazResult：查询结果集；

pnRow：结果集的行数；

pnColumn：结果集的列数；

errmsg：错误信息；

sqlite3_free_table

作用：注销结果集

原型：void sqlite3_free_table(char **result)

参数：

result：结果集；

例如：

test.c：

#include <stdio.h>

#include <sqlite3.h>

static sqlite3 *db=NULL

static char **Result=NULL

static char *errmsg=NULL

int main()

{

int rc, i, j

int nrow

int ncolumn

rc= sqlite3_open("test.db", &db)

rc= sqlite3_get_table(db, "select * from test_table", &Result, &nrow, &ncolumn,

&errmsg)

if(rc)

{

printf("query fail!\n")

}

else

{

printf("query success!\n")

for(i = 1i <= nrowi++)

{

for(j = 0j <ncolumnj++)

{

printf("%s | ", Result[i * ncolumn + j])

}

printf("\n")

}

}

sqlite3_free_table(Result)

sqlite3_close(db)

return 0

}

编译完成后，运行test的结果如下所示：

[root@localhost home]# ./test

query success!

xiaohong | female | 19 |

xiaoxue | female | 18 |

xiaoliu | male | 20 |

daobao | male | 24 |

sqlite3_prepare

作用：把SQL语句编译成字节码，由后面的执行函数去执行

原型：int sqlite3_prepare(sqlite3 *db, const char *zSql, int nByte, sqlite3_stmt **stmt, const char **pTail)

参数：

db：数据库；

zSql：SQL语句；

nByte：SQL语句的最大字节数；

stmt：Statement句柄；

pTail：SQL语句无用部分的指针；

sqlite3_step

作用：步步执行SQL语句字节码

原型：int sqlite3_step (sqlite3_stmt *)

例如：

test.c：

#include <stdio.h>

#include <sqlite3.h>

static sqlite3 *db=NULL

static sqlite3_stmt *stmt=NULL

int main()

{

int rc, i, j

int ncolumn

rc= sqlite3_open("test.db", &db)

rc=sqlite3_prepare(db,"select * from test_table",-1,&stmt,0)

if(rc)

{

printf("query fail!\n")

}

else

{

printf("query success!\n")

rc=sqlite3_step(stmt)

ncolumn=sqlite3_column_count(stmt)

while(rc==SQLITE_ROW)

{

for(i=0i<2i++)

{

printf("%s | ", sqlite3_column_text(stmt,i))

}

printf("\n")

rc=sqlite3_step(stmt)

}

}

sqlite3_finalize(stmt)

sqlite3_close(db)

return 0

}

编译完成后，运行test的结果如下所示：

[root@localhost home]# ./test

query success!

xiaohong | female | 19 |

xiaoxue | female | 18 |

xiaoliu | male | 20 |

daobao | male | 24 |

在程序中访问SQLite3数据库时，要注意C API的接口定义和数据类型是否正确，否则会得到错误的访问结果。

---