sqlite3编程笔记

   SQLite不是一个用于连接到大型数据库服务器的客户端，而是非常适合桌面程序和小型网站的数据库服务器。SQLite直接读写在硬盘上的数据库文件。

   SQlite3实现了绝大多数SQL92标准。整个数据库存储在一个单一的文件中，数据库文件可以在不同字节序的机器之间自由地共享，并支持最大可达2T的数据库。

一、安装

下载 http://www.sqlite.org/sqlite-autoconf-3070500.tar.gz

1、PC机安装

  ./configure

  make

  make install

  相关文件将在/usr/local生成

  /usr/local/bin ：可执行文件

  /usr/local/include：头文件

  /usr/local/lib：库文件

  也可以在configure时用prefix指定安装目录

2、交叉编译（以freescale mpc8308为例）

 (1) ./configure --host=powerpc-e300c3-linux-gnu --prefix=/home/SQLite/build/ 

    --prefix可以随意指定，只要方便使用。

   (2) make&&make install  

   (3) 此时可以使用file命令查看库文件是否符合要求

  如作者机器上显示如下：

  [wl_haanel@localhost sqlite-autoconf-3070500]$ file ../sqlite_build/lib/libsqlite3.so.0.8.6
  ../sqlite_build/lib/libsqlite3.so.0.8.6: ELF 32-bit MSB shared object, PowerPC or cisco 4500, version 1 (SYSV), not stripped
  可以看到libsqlite3.so的文件信息为PowerPC

   (4)此时生成的SQLite库文件是还未strip过的，需要剥离调试信息

先看一下此时的文件大小：

[wl_haanel@localhost sqlite-autoconf-3070500]$ ls -al ../sqlite_build/lib/libsqlite3.so.0.8.6
  -rwxr-xr-x 1 wl_haanel wl_haanel 1807102 03-08 14:51 ../sqlite_build/lib/libsqlite3.so.0.8.6

用strip进行剥离：
 [wl_haanel@localhost sqlite-autoconf-3070500]$ powerpc-e300c3-linux-gnu-strip ../sqlite_build/lib/libsqlite3.so.0.8.6

再看一下文件大小：
 [wl_haanel@localhost sqlite-autoconf-3070500]$ ls -al ../sqlite_build/lib/libsqlite3.so.0.8.6
  -rwxr-xr-x 1 wl_haanel wl_haanel 601488 03-08 15:03 ../sqlite_build/lib/libsqlite3.so.0.8.6

差不多缩小2/3。

至此编译就完成了。

二、开发板运行程序

 

把编译目录（configure参数prefix指定）的lib、bin、include目录拷贝到开发板上，在开发板上指定LD_LIBRARY_PATH和PATH。在PC机上将程序进行交叉编译，放到开发板上就可以运行了。

三、sqlite3支持的数据类型

NULL
INTEGER
REAL
TEXT
BLOB
但实际上，sqlite3也接受如下的数据类型：
smallint 16 位元的整数。
interger 32 位元的整数。
decimal(p,s) p 精确值和 s 大小的十进位整数，精确值p是指全部有几个数(digits)大小值，s是指小数点後有几位数。如果没有特别指定，则系统会设为 p=5; s=0 。
float   32位元的实数。
double   64位元的实数。
char(n)   n 长度的字串，n不能超过 254。
varchar(n) 长度不固定且其最大长度为 n 的字串，n不能超过 4000。
graphic(n) 和 char(n) 一样，不过其单位是两个字元 double-bytes， n不能超过127。这个形态是为了支援两个字元长度的字体，例如中文字。
vargraphic(n) 可变长度且其最大长度为 n 的双字元字串，n不能超过 2000
date   包含了 年份、月份、日期。
time   包含了 小时、分钟、秒。
timestamp 包含了 年、月、日、时、分、秒、千分之一秒。

四、sqlite3命令

  sqlite下除了特殊命令都要以分号 “;” 结尾，否则它将一直等待第一个分号的出现才判断这条命令结束。

  SQL语句直接输入，属于sqlite3本身的命令需要前面加 . 才能执行。

  可以在任何时候输入“.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|OFF        Turn 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 MS            Try 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>

   

    在“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>

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

sqlite> .separator ", "
sqlite> select * from tbl1;
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编程

注意：在sqlite3的回调函数中，一定要有return 0，不要省略，否则回调函数只执行一次，不能将所有记录扫描。

下面给出一个例子，包含了创建数据库、表、插入、查询、删除操作

#include <stdio.h>
#include <stdlib.h> //exit等函数的声明
#include "/home/wl_haanel/software/sqlite_build/include/sqlite3.h"

int displaycb(void *para,int n_column,char **column_value,char **column_name);
int inquire_Usecb(sqlite3 *db);
int inquire_nocb(sqlite3 *db);
int createTable(sqlite3 *db);
int insertRecord(sqlite3 *db);
int deleteRecord(sqlite3 *db);
int displaycb(void *para,int n_column,char **column_value,char **column_name)
{
        int i = 0;
        printf("Total column is %d/n",n_column);
        for(i = 0;i<n_column;i++)
        {
                printf("字段名: %s---->字段值:%s/n",column_name[i],column_value[i]);

        }
        printf("==========================/n");
        return 0;
}
int inquire_Usecb(sqlite3 *db)
{
        char *sql;
        char *zErrMsg;
        sql = "select * from 'SensorData';";
        if(SQLITE_OK != sqlite3_exec(db,sql,displaycb,NULL,&zErrMsg))
        {
                printf("operate failed: %s/n",zErrMsg);
        }
        return 0;
}
int inquire_nocb(sqlite3 *db)
{

        int nrow = 0,ncolumn = 0;
        char **azResult=0;
        int i = 0;
        char *sql;
        char *zErrMsg;
        sql = "SELECT * FROM SensorData";
        if(SQLITE_OK != sqlite3_get_table(db,sql,&azResult,&nrow,&ncolumn,&zErrMsg))
        {
                printf("operate failed: %s/n",zErrMsg);
        }

        printf("row:%d column = %d/n",nrow,ncolumn);
        printf("The result of querying is :/n");
        for ( i = 0;i < ( nrow + 1) * ncolumn; i++)
                printf("azResult[%d] = %s/n",i,azResult[i]);
        sqlite3_free_table(azResult);
        return 0;
}
//创建表函数
int createnTable(sqlite3 *db)
{
        char *zErrMsg = 0;
        char *sql = "CREATE TABLE SensorData(/
                                 ID INTEGER PRIMARY KEY,/
                                 SensorID INTEGER,/
                                 SiteNum INTERER,/
                                 Time VARCHAR(12),/
                                 SensorParameter REAL /
                                 );";
        if(SQLITE_OK != sqlite3_exec(db,sql,0,0,&zErrMsg))
        {
                printf("operate failed: %s/n",zErrMsg);
        }
}
//插入数据函数
int insertRecord(sqlite3 *db)
{
        char *sql;
        char *zErrMsg;
        sql = "INSERT INTO 'SensorData' VALUES(NULL,1,1,'20100314',18.9);";
        if(SQLITE_OK != sqlite3_exec(db,sql,0,0,&zErrMsg))
        {
                printf("operate failed: %s/n",zErrMsg);
        }

        sql = "INSERT INTO 'SensorData' VALUES(NULL,1,1,'20100616',111);";
        if(SQLITE_OK != sqlite3_exec(db,sql,0,0,&zErrMsg))
        {
                printf("operate failed: %s/n",zErrMsg);
        }

        sql = "INSERT INTO 'SensorData' VALUES(NULL,1,1,'20110315',222);";
        if(SQLITE_OK != sqlite3_exec(db,sql,0,0,&zErrMsg))
        {
                printf("operate failed: %s/n",zErrMsg);
        }

}
//删除数据函数
int deleteRecord(sqlite3 *db)
{
        char *sql;
        char *zErrMsg;
        char **azResult=0;
        int nrow = 0,ncolumn = 0;
        int i;
        sql = "DELETE  FROM SensorData WHERE ID=1;";
        if(SQLITE_OK != sqlite3_exec(db,sql,0,0,&zErrMsg))
        {
                printf("operate failed: %s/n",zErrMsg);
        }

        sql = "SELECT * FROM SensorData;";
        if(SQLITE_OK != sqlite3_get_table(db,sql,&azResult ,&nrow,&ncolumn,&zErrMsg))
        {
                printf("operate failed: %s/n",zErrMsg);
        }
        printf("row:%d column:%d/n",nrow,ncolumn);
        printf("After deleting,the result of querying is :/n");
        for(i=0;i<(nrow+1)*ncolumn;i++)
                printf("azResult[%d] = %s/n",i,azResult[i]);
        sqlite3_free_table(azResult);
}
int main(void)
{
        sqlite3 *db = NULL;
        int rc;

        //打开指定的数据库文件
        rc = sqlite3_open("wldatabase.db",&db);
        if(rc)
        {
                fprintf(stderr,"can't open database: %s",sqlite3_errmsg(db));
                sqlite3_close(db);
                exit(1);
        }
        else
                printf("You have opened a sqlite3 database successfully!/n");

        createTable(db);
        insertRecord(db);
        //查询
        //使用sqlite3_get_table实现查询
        inquire_nocb(db);
        printf("ppppppppppppppppppppppppppppppppppp/n");
        //使用回调函数实现查询
        inquire_Usecb(db);
        deleteRecord(db);

        sqlite3_close(db);
        return 0;
}