SQLite3介绍及使用方法_sqlite表列名位置

简介

特点

• 软件属于公共财（public domain），SQLite可说是某种「美德软件」（virtueware），作者本人放弃着作权，而给使用SQLite的人以下的「祝福」（blessing）：
  • May you do good and not evil. 愿你行善莫行恶
  • May you find forgiveness for yourself and forgive others. 愿你原谅自己宽恕他人
  • May you share freely, never taking more than you give. 愿你宽心与人分享，所取不多于你所施予
• 支援大多数的SQL指令（下面会简单介绍）。
• 一个文件就是一个数据库。不需要安装数据库服务器软件。
• 完整的Unicode支援（因此没有跨语系的问题）。
• 速度很快。

SQLite顾名思议是以SQL为基础的数据库软件，SQL是一套强大的数据库语言，主要概念是由「数据库」、「资料表」（table）、「查询指令」（queries）等单元组成的「关联性数据库」（进一步的概念可参考网络上各种关于SQL及关联性数据库的文件）。因为SQL的查询功能强大，语法一致而入门容易，因此成为现今主流数据库的标准语言（微软、Oracle等大厂的数据库软件都提供SQL语法的查询及操作）。

Linux 下命令行操作

下面的命令都是在 Linux 下运行过，基于 Debian 5.0，其他 Linux 通常也没有问题。其中 SQL 命令通常是不依赖于任何平台的。

创建数据库

创建数据库就是创建一个 SQLite 文件。

# sqlite3 test.db  # 当前目录下如果有 test.db ，那么就读取这个文件，否则创建。
SQLite version 3.5.9
Enter ".help" for instructions
sqlite> create table mytable(name varchar (40), age smallint);
sqlite> insert into mytable values ('Jian Lee',23);
sqlite> select * from mytable;
Jian Lee|23

上面简单使用几个 SQL 命令创建一个表，并插入一条记录，最后查看创建的表内容。使用 .help 可以列出帮助信息。 .quit 可以退出。

SQL 指令简介

所有的 SQL 指令都以 ";" 号结尾。 ”- -“ 表示注释。

创建 table，插入记录

sqlite> create table 数据库 (名字, 嵌入式);
sqlite> insert into 数据库 values ('PostgreSQL','否');
sqlite> insert into 数据库 values ('MySQL','否');
sqlite> insert into 数据库 values ('SQLite','是');
ssqlite> select * from 数据库;
PostgreSQL|否
MySQL|否
SQLite|是

insert into 命令中不存在的字段可以用 NULL 代替。

瞧，优秀的程序设计的多么好！任何”字符串“都只是一个”标签“，所以都能完美支持！

建立索引

创建索引可以加快访问速度。

sqlite> create index 数据库名字索引 on 数据库 (名字);

查询

更新或修改/删除

更新

sqlite> select * from 数据库;
PostgreSQL|否
MySQL|否
SQLite|是
sqlite> update 数据库 set 名字='SQLite3' where 名字='SQLite';
sqlite> select * from 数据库;
PostgreSQL|否
MySQL|否
SQLite3|是

删除

sqlite> select * from 数据库;
PostgreSQL|否
MySQL|否
SQLite3|是
sqlite> delete from 数据库 where 嵌入式='是';
sqlite> select * from 数据库;
PostgreSQL|否
MySQL|否

SQLite 的特别用法

修改 sqlite3 的默认分隔符（｜）

sqlite> .separator "-"

shell 下访问

# sqlite3 test.db "select * from 数据库;"
PostgreSQL|否
MySQL|否

如果要执行多条 sql 语句，可以先生成这样的sql语句文件，比如 “sql.txt”：

.output Somefile    // 这句话告诉sqlite把sql执行的结果输出到文件
create tab ...        // 正常的sql语句
insert into ....
.quit                // 告诉sqlite退出

句，并把结果保存到Somefile中。

输出 HTML 表格

root@jianlee:~/lab/xml/crepo/test# sqlite3 -html test.db "select * from 数据库;"
<TR><TD>PostgreSQL</TD>
<TD>否</TD>
</TR>
<TR><TD>MySQL</TD>
<TD>否</TD>
</TR>

将数据库倒出来（输出 SQL 指令）

root@jianlee:~/lab/xml/crepo/test# sqlite3 test.db ".dump"  > test.sql
root@jianlee:~/lab/xml/crepo/test# cat test.sql
BEGIN TRANSACTION;
CREATE TABLE mytable(name varchar (40), age smallint);
INSERT INTO "mytable" VALUES('Jian Lee',23);
CREATE TABLE 操作系统 (title, length, year, starring);
INSERT INTO "操作系统" VALUES('Jian Lee',23,33,45);
CREATE TABLE 数据库 (名字, 嵌入式);
INSERT INTO "数据库" VALUES('PostgreSQL','否');
INSERT INTO "数据库" VALUES('MySQL','否');
CREATE INDEX 数据库名字索引 on 数据库 (名字);
COMMIT;

重建数据库（用上面倒出的 SQL 语句）

root@jianlee:~/lab/xml/crepo/test# sqlite3 重建数据库.db < test.sql
root@jianlee:~/lab/xml/crepo/test# sqlite3 重建数据库.db "select * from 数据库;"
PostgreSQL|否
MySQL|否

在大量插入资料时，你可能会需要先打这个指令：

begin;

插入完资料后要记得打这个指令，资料才会写进数据库中：

commit;

begin; 和 commit; 是 SQL 处理一个事务的语法。

数据表结构

SQLite 数据库的数据结构保存在 "sqlite_master" 表中，下例针对 rpm 的yum 源里的 repodata 目录中的 other.sqlite.bz2 数据库进行查询：

# sqlite3 other.sqlite
SQLite version 3.5.9
Enter ".help" for instructions
sqlite> select * from sqlite_master;
table|db_info|db_info|2|CREATE TABLE db_info (dbversion INTEGER, checksum TEXT)
table|packages|packages|3|CREATE TABLE packages (  pkgKey INTEGER PRIMARY KEY,  pkgId TEXT)
table|changelog|changelog|4|CREATE TABLE changelog (  pkgKey INTEGER,  author TEXT,  date INTEGER,  changelog TEXT)
index|keychange|changelog|5|CREATE INDEX keychange ON changelog (pkgKey)
index|pkgId|packages|6|CREATE INDEX pkgId ON packages (pkgId)
trigger|remove_changelogs|packages|0|CREATE TRIGGER remove_changelogs AFTER DELETE ON packages  BEGIN    DELETE FROM changelog WHERE pkgKey = old.pkgKey;  END sqlite>

但是你不能够对sqlite_master 表执行 DROP TABLE, UPDATE, INSERT orDELETE ，当你创建或者删除表的时候，sqlite_master 表会自动更新。你不能手工改变 sqlite_master 表。

临时表的结构不会存贮到 sqlite_master 表中，临时表是存贮在另一个特殊的表，叫做 "sqlite_temp_master"。"sqlite_temp_master" 表本身就是临时的。

将结果写到文件中

取自参考资料2的示例，我现在还用不到：

默认情况下，sqlite3会将结果发送到标准输出，你可以使用 ".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
    $

查询数据库结构

列出所有数据表 (.tables)

sqlite> .tables
changelog  db_info    packages
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

列出特殊表的索引 (.indices)

".indices" 命令以相似的方法列出一个特殊表的索引。".indices" 命令以一个数据表的名字作为参数。

.schema

不带参数的 .schema 命令会显示用来创建数据库的 “CREATE TABLE and CREATEINDEX” 语句，如果你给一个表名为参数，他会显示用来创建表和索引（如果有的话）的 CREATE 语句。

sqlite> .schema
CREATE TABLE changelog (  pkgKey INTEGER,  author TEXT,  date INTEGER,  changelog TEXT);
CREATE TABLE db_info (dbversion INTEGER, checksum TEXT);
CREATE TABLE packages (  pkgKey INTEGER PRIMARY KEY,  pkgId TEXT);
CREATE INDEX keychange ON changelog (pkgKey);
CREATE INDEX pkgId ON packages (pkgId);
CREATE TRIGGER remove_changelogs AFTER DELETE ON packages  BEGIN    DELETE FROM changelog WHERE pkgKey = old.pkgKey;  END;
schema>
sqlite> .schema changelog
CREATE TABLE changelog (  pkgKey INTEGER,  author TEXT,  date INTEGER,  changelog TEXT);
CREATE INDEX keychange ON changelog (pkgKey);

常用命令

.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 退出命令行接口

SQLite 结构分析

每一个 SQLite 数据库都有一个叫 SQLITE_MASTER 的表， 它定义数据库的模式。SQLITE_MASTER 表看起来如下：

CREATE TABLE sqlite_master (
      type TEXT,
      name TEXT,
      tbl_name TEXT,
      rootpage INTEGER,
      sql TEXT
);

对于表来说，type 字段永远是 'table'，name 字段永远是表的名字。所以，要获得数据库中所有表的列表， 使用下列SELECT语句：

SELECT name FROM sqlite_master
WHERE type='table'
ORDER BY name;

对于索引，type 等于 'index', name 则是索引的名字，tbl_name 是该索引所属的表的名字。 不管是表还是索引，sql 字段是原先用 CREATE TABLE 或 CREATEINDEX 语句创建它们时的命令文本。对于自动创建的索引（用来实现 PRIMARYKEY 或 UNIQUE 约束），sql字段为NULL。

SQLITE_MASTER 表是只读的。不能对它使用 UPDATE、INSERT 或 DELETE。 它会被 CREATE TABLE、CREATE INDEX、DROP TABLE 和 DROP INDEX 命令自动更新。

临时表不会出现在 SQLITE_MASTER 表中。临时表及其索引和触发器存放在另外一个叫 SQLITE_TEMP_MASTER 的表中。SQLITE_TEMP_MASTER 跟 SQLITE_MASTER 差不多，但它只是对于创建那些临时表的应用可见。如果要获得所有表的列表， 不管是永久的还是临时的，可以使用类似下面的命令：

SELECT name FROM
    (SELECT * FROM sqlite_master UNION ALL
     SELECT * FROM sqlite_temp_master)
WHERE type='table'
    ORDER BY name

触发器

假设"customers"表存储了客户信息，"orders"表存储了订单信息，下面的触发器确保当用户改变地址时所有的 关联订单地址均进行相应改变：

CREATE TRIGGER update_customer_address UPDATE OF address ON customers
  BEGIN
    UPDATE orders SET address = new.address WHERE customer_name = old.name;
  END;

定义了该触发器后执行如下语句：

UPDATE customers SET address = '1 Main St.' WHERE name = 'Jack Jones';

会使下面的语句自动执行：

UPDATE orders SET address = '1 Main St.' WHERE customer_name = 'Jack Jones';

注意，目前在有INTEGER PRIMARY KEY域的表上触发器可能工作不正常。若BEFORE触发器修改了一行的 INTEGER PRIMARY KEY域，而该域将由触发该触发器的语句进行修改，则可能根本不会修改该域。 可以用PRIMARY KEY字段代替INTEGER PRIMARY KEY字段来解决上述问题。

再举 1 例，在 primary.sqlite.bz2 数据库中：

CREATE TRIGGER removals AFTER DELETE ON packages
  BEGIN
    DELETE FROM files WHERE pkgKey = old.pkgKey;
    DELETE FROM requires WHERE pkgKey = old.pkgKey;
    DELETE FROM provides WHERE pkgKey = old.pkgKey;
    DELETE FROM conflicts WHERE pkgKey = old.pkgKey;
    DELETE FROM obsoletes WHERE pkgKey = old.pkgKey;
  END;

SQLite 的 C 语言编程

执行sql语句

int
sqlite3_exec ( sqlite3 *db,  // 使用 sqlite3_open () 打开的数据库对象。
               const char *sql, // 一条待查询的 SQL 语句
               sqlite3_callback, // 自定义的回调函数，对查询结果每一行都执行一次这个函数
               void *,
               char **errmsg
);
<example>

这是最常用的执行 sql 语句的调用。简单的参数含意标在上面函数中，下面对重
要参数含意详细注释：

 - 第 4 个参数 "void *" 是调用者所提供的指针，可以传递任何一个指针参数到
   这里，这个参数最终会传到回调函数里面，这个指针比较重要，可以用来作参
   数的传递。如果不需要传递指针给回调函数，可以填NULL。等下我们再看回调
   函数的写法，以及这个参数的使用。

 - 第 5 个参数 "char ** errmsg" 是错误信息。注意是指针的指针。sqlite3里
   面有很多固定的错误信息。执行 sqlite3_exec 之后，执行失败时可以查阅这
   个指针（直接 printf(“%s\n”,errmsg)）得到一串字符串信息，这串信息告诉
   你错在什么地方。sqlite3_exec函数通过修改你传入的指针的指针，把你提供
   的指针指向错误提示信息，这样sqlite3_exec函数外面就可以通过这个
   char*得到具体错误提示。

说明：通常， sqlite3_callback 和它后面的 void * 这两个位置都可以填
NULL。填NULL表示你不需要回调。比如你做 insert 操作，做 delete 操作，就
没有必要使用回调。而当你做 select 时，就要使用回调，因为 sqlite3 把数据
查出来，得通过回调告诉你查出了什么数据。

** exec 的回调函数
<example>
typedef int
(*sqlite3_callback) (void *, // 这就是上面函数传递的 void * 参数，需要强制类型转换后才能使用。
                    int, // 查询结果的列数，即有多少个字段数
                    char **, // 保存查询结果
                    char **  // 各个字段的名字
);

回调函数必须定义成上面这个函数的类型。下面给个简单的例子：

不使用回调查询数据库

上面介绍的 sqlite3_exec 是使用回调来执行 select 操作。还有一个方法可以直接查询而不需要回调。虽然回调显得代码整齐，但有时候你还是想要非回调的select 查询。这可以通过 sqlite3_get_table 函数做到。

int
sqlite3_get_table (sqlite3 *,   // 打开的数据库对象指针
                   const char * sql, // 要查询的 sql 语句
                   char *** resultp, // 查询结果
                   int * nrow,   // 查询出多少条记录（即查出多少行）
                   int * ncolumn, // 多少个字段（多少列）
                   char ** errmsg  // 错误信息
);

第3个参数是查询结果，它依然一维数组（不要以为是二维数组，更不要以为是三维数组）。它内存布局是：第一行是字段名称，后面是紧接着是每个字段的值。下面用例子来说事。

下面给个简单例子:

int main( int , char ** )
{
          sqlite3 * db;
          int result;
          char * errmsg = NULL;
          char ** dbResult; //是 char ** 类型，两个*号
          int nRow, nColumn;
          int i , j;
          int index;

          result = sqlite3_open( “c:\\Dcg_database.db”, &db );

         if( result != SQLITE_OK )
        {
               //数据库打开失败
               return -1;
        }

       //数据库操作代码
       //假设前面已经创建了 MyTable_1 表
       //开始查询，传入的 dbResult 已经是 char **，这里又加了一个 & 取地址符，传递进去的就成了 char ***
       result = sqlite3_get_table( db, “select * from MyTable_1”, &dbResult, &nRow, &nColumn, &errmsg );
       if( SQLITE_OK == result )
       {
           //查询成功
          index = nColumn; //前面说过 dbResult 前面第一行数据是字段名称，从 nColumn 索引开始才是真正的数据
     printf( “查到%d条记录\n”, nRow );

     for(  i = 0; i < nRow ; i++ )
     {
         printf( “第 %d 条记录\n”, i+1 );
         for( j = 0 ; j < nColumn; j++ )
         {
              printf( “字段名:%s  ß> 字段值:%s\n”,  dbResult[j], dbResult [index] );
              ++index; // dbResult 的字段值是连续的，从第0索引到第 nColumn - 1索引都是字段名称，从第 nColumn 索引开始，后面都是字段值，它把一个二维的表（传统的行列表示法）用一个扁平的形式来表示
         }
         printf( “-------\n” );
     }
}

     //到这里，不论数据库查询是否成功，都释放 char** 查询结果，使用 sqlite 提供的功能来释放
     sqlite3_free_table( dbResult );

     //关闭数据库
     sqlite3_close( db );
     return 0;
}

操作二进制

sqlite 操作二进制数据需要用一个辅助的数据类型：sqlite3_stmt * 。这个数据类型记录了一个“sql语句”。为什么我把 “sql语句” 用双引号引起来？因为你可以把 sqlite3_stmt * 所表示的内容看成是 sql语句，但是实际上它不是我们所熟知的sql语句。它是一个已经把sql语句解析了的、用sqlite自己标记记录的内部数据结构。正因为这个结构已经被解析了，所以你可以往这个语句里插入二进制数据。当然，把二进制数据插到 sqlite3_stmt 结构里可不能直接 memcpy，也不能像 std::string 那样用 + 号。必须用 sqlite 提供的函数来插入。

写入二进制

要插入二进制，前提是这个表的字段的类型是 blob 类型。假设有这么一张表：

create table Tbl_2( ID integer, file_content  blob )

首先声明

sqlite3_stmt * stat;

然后，把一个 sql 语句解析到 stat 结构里去：

sqlite3_prepare( db, “insert into Tbl_2( ID, file_content) values( 10, ? )”, -1, &stat, 0 );

上面的函数完成 sql 语句的解析。

• 第一个参数跟前面一样，是个 sqlite3 * 类型变量
• 第二个参数是一个 sql 语句。这个 sql 语句特别之处在于 values 里面有个? 号。在sqlite3_prepare函数里，?号表示一个未定的值，它的值等下才插入。
• 第三个参数我写的是-1，这个参数含义是前面 sql 语句的长度。如果小于0，sqlite会自动计算它的长度（把sql语句当成以\0结尾的字符串）。
• 第四个参数是 sqlite3_stmt 的指针的指针。解析以后的sql语句就放在这个结构里。
• 第五个参数我也不知道是干什么的。为0就可以了。

如果这个函数执行成功（返回值是 SQLITE_OK 且 stat 不为NULL ），那么下面就可以开始插入二进制数据。

sqlite3_bind_blob( stat, 1, pdata, (int)(length_of_data_in_bytes), NULL );
// pdata为数据缓冲区，length_of_data_in_bytes为数据大小，以字节为单位

这个函数一共有5个参数。

• 第 1 个参数：是前面prepare得到的 sqlite3_stmt * 类型变量。
• 第 2 个参数：?号的索引。前面prepare的sql语句里有一个?号，假如有多个?号怎么插入？方法就是改变 bind_blob 函数第2个参数。这个参数我写1，表示这里插入的值要替换 stat 的第一个?号（这里的索引从1开始计数，而非从0开始）。如果你有多个?号，就写多个 bind_blob 语句，并改变它们的第2个参数就替换到不同的?号。如果有?号没有替换，sqlite为它取值null。
• 第3个参数：二进制数据起始指针。
• 第4个参数：二进制数据的长度，以字节为单位。
• 第5个参数：是个析够回调函数，告诉sqlite当把数据处理完后调用此函数来析够你的数据。这个参数我还没有使用过，因此理解也不深刻。但是一般都填NULL，需要释放的内存自己用代码来释放。

bind完了之后，二进制数据就进入了你的“sql语句”里了。你现在可以把它保存到数据库里：

int result = sqlite3_step( stat );

通过这个语句，stat 表示的sql语句就被写到了 数据库里。最后，要把 sqlite3_stmt 结构给释放：

sqlite3_finalize( stat ); //把刚才分配的内容析构掉

读出二进制

先声明 sqlite3_stmt * 类型变量：

sqlite3_stmt * stat;

然后，把一个 sql 语句解析到 stat 结构里去：

sqlite3_prepare( db, “select * from Tbl_2”, -1, &stat, 0 );

当 prepare 成功之后（返回值是 SQLITE_OK ），开始查询数据。

int result = sqlite3_step( stat );

这一句的返回值是 SQLITE_ROW 时表示成功（不是 SQLITE_OK ）。

你可以循环执行 sqlite3_step 函数，一次 step 查询出一条记录。直到返回值不为 SQLITE_ROW 时表示查询结束。

然后开始获取第一个字段：ID 的值。ID是个整数，用下面这个语句获取它的值：

int id = sqlite3_column_int( stat, 0 );
//第2个参数表示获取第几个字段内容，从0开始计算，因为我的表的ID字段是第一个字段，因此这里我填0

下面开始获取 file_content 的值，因为 file_content 是二进制，因此我需要得到它的指针，还有它的长度：

const void * pFileContent = sqlite3_column_blob( stat, 1 );
int len = sqlite3_column_bytes( stat, 1 );

这样就得到了二进制的值。把 pFileContent 的内容保存出来之后，不要忘了释放 sqlite3_stmt 结构：

sqlite3_finalize( stat ); //把刚才分配的内容析构掉

重复使用 sqlite3_stmt 结构

如果你需要重复使用 sqlite3_prepare 解析好的 sqlite3_stmt 结构，需要用函数： sqlite3_reset。

result = sqlite3_reset(stat);

这样， stat 结构又成为 sqlite3_prepare 完成时的状态，你可以重新为它 bind 内容。