浅析Linux中SQL及其用法

 嵌入式数据库介绍

随着电子技术的飞速发展，嵌入式系统中的存储器容量和性能也在迅速提高，这为扩大嵌入式的应用领域提供了必要的物理基础。展望未来，嵌入式系统正在向网络化、智能化等高端应用方向发展。在这个发展过程中，嵌入式系统内的数据量会急剧膨胀。因此，嵌入式数据库的作用将会变得越来越重要。

由于嵌入式平台和应用领域的多样化，所以嵌入式数据库的体系结构与运行模式和企业级数据库有很大的区别。嵌入式数据库的主要特性如下：

（1）嵌入性

嵌入性是嵌入式数据库的根本特性。嵌入式数据库不但可以嵌入到各种软件中，也能嵌入到硬件中。

（2）可移植性

可移植性是嵌入性的基本保证。嵌入式数据库必须能够支持各种硬件平台。

（3）实时性

在嵌入式领域，实时性是一个重要的指标。所以嵌入式数据库也需要具有较高的实时性能。

（4）伸缩性

伸缩性使嵌入式数据库能够满足各种应用需要，提高嵌入式系统的性能。

（5）可移动性

随着嵌入式系统的网络化发展，可移动性也正变得越来越重要。所以嵌入式数据库需要满足可移动性的要求。

为了更好的满足嵌入式应用的需求，嵌入式数据库本身需要具有企业级数据库的基本功能（比如一致性、安全性等）。此外，嵌入式数据库也必须提供一套完整的SQL接口，以满足应用开发的需要。

总而言之，嵌入式数据库的应用环境是非常苛刻的。嵌入式数据库需要在满足应用要求的前提下保证高效的运行性能。

根据应用方式的不同，嵌入式数据库可以大致分为以下几类：

（1）C/S嵌入式数据库

   C/S嵌入式数据库可以看成是企业级数据库的一个精简版，一般运用在对实时性要求不高的系统中。

（2）面向软件嵌入式数据库

    面向软件嵌入式数据库以组件的形式嵌入到软件中，一般运用在对运行速度和安全性要求较高的系统中。

（3）面向硬件嵌入式数据库

面向硬件嵌入式数据库直接嵌入到硬件设备中，一般运用在对实时性和稳定性要求较高的系统中。

（4）内存嵌入式数据库

    内存嵌入式数据库直接运行在内存中，所以运行性能非常高。但数据无法永久保存。

2. SQLite介绍

SQLite是一款轻量级的开源嵌入式数据库，由D.Richard Hipp在2000年发布。SQLite使用方便，性能出众，广泛应用于消费电子、医疗、工业控制、军事等各种领域。SQLite主要具有以下特点：

l  性能：SQLite对数据库的访问性能很高，其运行速度比Mysql、PostgreSQL等开源数

据库要快很多。

l  体积：SQLite的体积非常小巧，最低只需要几百K的内存就可以运行。

l  可移植性：SQLite的能支持各种32位和64位体系的硬件平台，也能在Windows、Linux、

BSD、Mac OS、Solaries等软件平台中运行。

l  稳定性：SQLite支持事务的ACID特性，既原子性、一致性、隔离性、持久性

l  SQL支持：SQLite支持ANSI SQL92中的大多数标准，提供了对子查询、视图、触发

器等机制的支持。

l  接口：SQLite为C、Java、PHP、Python、Tcl等多种语言提供了API接口。

SQLite总体采用了模块化设计

（1）接口。接口由SQLite C API函数组成。所有的应用程序都必须通过接口访问SQLite数据库。

（2）编译器。编译器由词法分析、语法分析和中间代码生成三个模块组成。其中，词法分析模块和语法分析模块负责检查SQL语句的语法，然后把生成的语法树传递给中间代码生成模块。中间代码生成模块负责生成SQLite引擎可以识别的中间代码。

（3）数据库引擎。数据库引擎是SQLite的核心，负责运行中间代码，指挥数据库的具体操作。

（4）后台。后台由B树、页缓存和系统调用三个模块组成。其中，B树负责维护索引，页缓存负责页面数据的传送，系统调用负责和操作系统交互，最终实现数据库的访问。

11.2 SQLite3的使用

11.2.1 SQLite3的命令

SQLite3是目前最新的SQLite版本。可以从http://www.sqlite.org/download.html网站上下载SQLite3的源代码。

解压缩后进入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 SQLstatements terminated with a ";"

sqlite>

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

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

l  NULL

l  NUMERIC

l  INTEGER

l  REAL

l  TEXT

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

l  char、clob、test、varchar—> TEXT

l  integer—>INTEGER

l  real、double、float—> REAL

l  blob—>NULL

l  其余数据类型都转变成NUMERIC

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

（1）      新建一个数据库

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

[root@localhosthome]# sqlite3 test.db

SQLite version 3.6.12

Enter".help" for instructions

Enter SQLstatements 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语句中并不要求给出列的类型。

（2）      创建索引

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

sqlite>create index test_index on test_table(name);

（3）      操作数据

如下所示的是在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

（4）      批量操作数据库

如下所示的是在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后，才会把插入的数据写入数据库中。

（5）      数据库的导入导出

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

[root@localhosthome]# sqlite3 test.db ".dump" > test.sql;

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

BEGINTRANSACTION;

CREATE TABLEtest_table(name, sex, age);

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

CREATE INDEXtest_index on test_table(name);

COMMIT;

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

[root@localhosthome]# sqlite3 test.db < test.sql;

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

11.2.2 SQLite3的C接口

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

l  sqlite_open

作用：打开SQLite3数据库

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

参数：

dbname：数据库的名称；

db：数据库的句柄；

返回值：返回值的具体含义如表11-1所示：

表11-1

返回值	标识符	含义
0	SQLITE_OK	操作成功
1	SQLITE_ERROR	操作失败
2	SQLITE_INTERNAL	内部逻辑错误
3	SQLITE_PERM	访问权限错误
4	SQLITE_ABORT	操作异常
5	SQLITE_BUSY	数据库被锁定
6	SQLITE_LOCKED	表被锁定
7	SQLITE_NOMEM	内存申请失败
8	SQLITE_READONLY	写只读数据库
9	SQLITE_INTERRUPT	内部中断
10	SQLITE_IOERR	I/O错误
11	SQLITE_CORRUPT	数据库镜像格式错误
12	SQLITE_NOTFOUND	找不到文件或数据
13	SQLITE_FULL	数据库已满
14	SQLITE_CANTOPEN	无法打开数据库文件
15	SQLITE_PROTOCOL	锁协议出错
16	SQLITE_EMPTY	表为空
17	SQLITE_SCHEMA	数据库体系发生改变
18	SQLITE_TOOBIG	记录数据溢出
19	SQLITE_CONSTRAINT	违反约束产生异常
20	SQLITE_MISMATCH	数据类型不匹配
21	SQLITE_MISUSE	数据库使用不正确
22	SQLITE_NOLFS	系统不支持
23	SQLITE_AUTH	未获授权
100	SQLITE_ROW	sqlite_step()
101	SQLITE_DONE	sqlite_step()结束运行

l  sqlite_colse

作用：关闭SQLite3数据库

原型：int sqlite_close(sqlite3 *db)

例如：

l  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@localhosthome]# open database success!

l  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：错误信息

例如：

l  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@localhosthome]# ./test

exec success!

[root@localhosthome]# sqlite3 test.db

SQLite version 3.6.11

Enter".help" for instructions

Enter SQLstatements terminated with a ";"

sqlite>select * from test_table;

daobao|male|24

l  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：错误信息；

l  sqlite3_free_table

作用：注销结果集

原型：void sqlite3_free_table(char **result)

参数：

result：结果集；

例如：

l  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 = 1; i <= nrow; i++)

       {

          for(j = 0; j < ncolumn; j++)

          {

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

          }

          printf("\n");

       }

    }

 

    sqlite3_free_table(Result);

    sqlite3_close(db);

    return 0;

}

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

[root@localhosthome]# ./test

query success!

xiaohong |female | 19 |

xiaoxue | female| 18 |

xiaoliu | male |20 |

daobao | male |24 |

l  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语句无用部分的指针；

l  sqlite3_step

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

原型：int sqlite3_step (sqlite3_stmt *)

例如：

l  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 * fromtest_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=0; i<2; i++)

          {

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

          }

          printf("\n");

          rc=sqlite3_step(stmt);

       }

    }

 

    sqlite3_finalize(stmt);

    sqlite3_close(db);

    return 0;

}

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

[root@localhosthome]# ./test

query success!

xiaohong |female | 19 |

xiaoxue | female| 18 |

xiaoliu | male |20 |

daobao | male |24 |

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