SQLite数据库入门

2019-10-27

关键字：sqlite3、Linux数据库

嵌入式设备通常都不会有太丰富的计算与内存资源，为了能在这种资源紧张型的嵌入式设备中也能正常使用与PC端类似的数据库软件，就必须专门针对嵌入式设备开发轻量级数据库。目前嵌入式领域的数据库软件主要有以下几种：

1、SQLite

一种关系型数据库，体积小且支持 ACID 事务。

2、Firebird

一种关系型数据库，功能强大，支持存储过程、SQL兼容等特性。

3、Berkeley DB

这种数据库中并不像常规数据库一样有服务器的概念。它的程序库是直接链接到应用程序中的。

4、eXtremeDB

一种内存数据库，运行效率较高。

SQLite 是一种在嵌入式设备领域比较常见的一种轻量级数据库软件。它诞生于 2000 年，是一个基于C语言的开源软件。目前 SQLite 数据库的主流版本已经更新到第 3 大版，即我们常见的 sqlite3。

SQLite 数据库具有以下特性：

1、零配置，软件无需安装和管理配置；

2、整个数据库都存储在同一个磁盘中；

3、兼容性好，数据库文件可以在不同字节序的机器间自由共享；

4、支持的数据库大小上限是2TB；

5、轻量化，源码只有3万多行；

6、操作速度较快；

SQLite数据库的创建方式有两种：

1、手工创建

主要是指通过进入 sqlite3 的命令行通过 SQL 命令来创建。

2、代码创建

安装：

如果是 Linux PC系统，可以直接使用 sudo apt install sqlite3 命令来在线安装。软件很小，就几兆字节。

如果是嵌入式设备，通常直接将编译好的 sqlite3 程序下载进去就可以运行的了。

创建数据库：

sqlite3 mydb.db

上述命令可以直接在当前目录下创建一个名称为 mydb 的 SQLite 数据库文件，并进入到该数据库中。

常用命令：

SQLite中的命令主要可以分为两种：一种是以 . 开头的命令，这种命令称为“系统命令”，主要用于对 SQLite 软件发号施令。另一种是普通的 SQL 命令，这种命令是必须要以 ; 号结尾的，这种命令主要用于操作数据库或数据表。

创建数据表：

create table stu(id Integer, name char, score Float, address string);

创建一张名称为 stu 的数据表，表的结构如命令括号中所示。

删除数据表：

drop table tbname;

delete table tbname;

增删改查：

insert into stu values(1001, ‘lemontea1‘, 76.2, "guangdong.zhongshan");

insert into stu values(1002, "lemontea2", 86.2, ‘sicuan.leshan‘);

insert into stu(id, name) values(1003, ‘lemontea3‘);

以上是插入数据的几种主要的命令形式。SQLite命令中字符串的引号单双引号可以通用。指定字段插入的语法与普通 SQL 是如出一辙的。

delete from stu where id=1003;

delete from stu;

删除数据。表名后有条件限制则只删除满足条件的记录项。表名后若无条件限制，则将整张表删除掉。与普通 SQL 基本通用。

update stu set score=96.1 where name=‘lemontea1‘;

update stu set id=1033, score=96.1 where name=‘lemontea1‘;

修改数据记录。既可以修改单个字段的值也可以同时修改多个字段的值。其使用方式也与普通 SQL 基本一致。

select * from stu;

select name from stu;

select name, score from stu;

select * from stu where score < 80 and score > 70;

查询数据的 SQL 语句与普通 SQL 也没太大差别。

修改表：

alter table stu add column desc char;

为已存在的表添加一个字段。

alter table stu rename to stu1;

将数据表 stu 更名为 stu1。

SQLite中虽然可以通过 alter 来新增一个字段，但是却没有可以直接删除某一字段的命令。想要删除数据表中某一字段，可以通过间接的方式来进行：

1、create table stu_tmp as select id, name, score from stu;

2、drop table stu;

3、alter table stu_tmp rename to stu;

.databases：

列出当前所打开的数据库的名称与文件路径。

.tables

列出当前数据库下的所有数据表。

.schema tbname

查询指定数据表的建表语句，或者说查看指定数据表的表结构。

通过代码操作SQLite：

本节主要记载在 Linux 开发中通过C语言操作SQLite的方法。

SQLite 对外开放的函数接口非常多，本节仅记载几个基础的常用的函数，更多函数的接口及释义还需自行参阅SQLite官方文档，这里贴出官方文档的下载路径：https://www.sqlite.org/2019/sqlite-doc-3300100.zip 。另外，官方网站的文档下载的速度实在是太慢了，我这里已将下载好的文档上传至百度云，有需要的同学可以直接下载我百度云上的：https://pan.baidu.com/s/1TYsog_56eorCOXynSAFh4Q  

如果在编译的时候提示找不到 sqlite3.h 头文件的话，只需要安装一下 sqlite3 的SDK即可： sudo apt install libsqlite3-dev 

打开SQLite：

int sqlite3_open(char *path, sqlite3 **db);

参数 path 表示数据库文件的路径。

参数 db 表示指向 sqlite3 的句柄指针。

该函数执行成功时返回值0，失败时返回相应错误码。

关闭SQLite：

int sqlite3_close(sqlite3 *db);

执行成功时返回值0，失败时返回相应错误码。

提取错误信息：

const char* sqlite3_errmg(sqlite3 *db);

将操作SQLite时产生的错误码转换成字符串信息。

执行SQL语句：

int sqlite3_exec(sqlite3 *db, const char *sql, int (*callback)(void *para, int f_num, char **f_value, char **f_name), void *args, char **errmsg);

参数 callback 是一个查询执行结果的回调函数的指针。

int (*callback)(void *para, int f_num, char **f_value, char **f_name);

这个函数主要用于执行SQLite查询时的结果回调。每找到一条记录就会自动调用一次这个回调函数。

参数 para 表示传递给回调函数的参数。

参数 f_name 表示记录中包含的字段数目。

参数 f_value 表示包含每个字段值的指针数组。

参数 f_name 表示包含每个字段名称的指针数组。

这个函数执行成功时会返回值0，失败时返回-1。

参数 args 是给前一个参数 callback 的函数传参。

参数 errmsg 就是错误信息。

这个函数执行成功时会返回 SQLITE_OK。

不使用回调的方式来查询数据：

上面的 sqlite3_exec() 函数通过执行 SQL 的方式来查询数据，查询结果只能通过回调来拿到。SQLite 还提供了另外一种查询方式，这种查询方式不需要回调函数来接收查询结果。

int sqlite3_get_table(sqlite3 *db, const char *sql, char ***resultp, int *nrow, int *ncolumn, char **errmsg);

参数 resultp 表示用来指向查询结果的指针。三级指针，通常我们需要定义一个二级指针，然后取地址传过去。

参数 nrow 表示满足条件的记录数目。

参数 ncolumn 表示每条记录所包含的字段数目。

参数 errmsg 用于存储错误信息。

执行成功时返回值0，失败时返回相应错误码。

以下是一个简单的SQLite实现的管理学生成绩的小示例，这个示例包含了创建数据库、数据表以及对数据表的增删改查。同时还应用了两种查询方式，其源码如下所示：

#include <stdio.h>
#include <sqlite3.h>
#include <stdlib.h>

#define DB "stu.db"

//compile:  gcc student.c -lsqlite3

int do_insert(sqlite3 *db)
{
    int id;
    char name[32] = {};
    float score;
    char sql[128] = {};
    char *errmsg;
    
    printf("id:");
    scanf("%d", &id);
    getchar(); //消化掉 \n 字符.
    
    printf("name:");
    scanf("%s", &name);
    getchar(); //消化掉 \n 字符.
    
    printf("score:");
    scanf("%f", &score);
    getchar(); //消化掉 \n 字符.
    
    sprintf(sql, "insert into stu values(%d, ‘%s‘, %f)", id, name, score);
    
    if(sqlite3_exec(db, sql, NULL, NULL, &errmsg) != SQLITE_OK)
    {
        printf("insert error:%s\n", errmsg);
    }
    else
    {
        printf("insert done\n");
    }
    
    return 0;
}

int do_delete(sqlite3 *db)
{
    int id;
    char sql[128] = {};
    char *errmsg;
    
    printf("id:");
    scanf("%d", &id);
    getchar(); //消化掉 \n 字符.
    
    sprintf(sql, "delete from stu where id=%d", id);
    
    if(sqlite3_exec(db, sql, NULL, NULL, &errmsg) != SQLITE_OK)
    {
        printf("delete error:%s\n", errmsg);
    }
    else
    {
        printf("delete done\n");
    }
    
    return 0;
}

int do_update(sqlite3 *db)
{
    int id;
    float score;
    char sql[128] = {};
    char *errmsg;
    
    printf("id:");
    scanf("%d", &id);
    getchar(); //消化掉 \n 字符.
    
    printf("score:");
    scanf("%f", &score);
    getchar(); //消化掉 \n 字符.
    
    sprintf(sql, "update stu set score=%f where id=%d", score, id);
    
    if(sqlite3_exec(db, sql, NULL, NULL, &errmsg) != SQLITE_OK)
    {
        printf("update error:%s\n", errmsg);
    }
    else
    {
        printf("update done\n");
    }
    
    return 0;
}

int callback(void *para, int f_num, char **f_value, char **f_name)
{
    int i = 0;
    
    for(i = 0; i < f_num; i++)
    {
        printf("%-11s ", f_value[i]);
    }
    putchar(‘\n‘);
    
    return 0;
}

int do_query(sqlite3 *db)
{
    char sql[128] = {};
    char *errmsg;
    
    sprintf(sql, "select * from stu");
    
    if(sqlite3_exec(db, sql, callback, NULL, &errmsg) != SQLITE_OK)
    {
        printf("query error:%s\n", errmsg);
    }
    else
    {
        printf("query done\n");
    }
    
    return 0;
}

int do_query_no_callback(sqlite3 *db)
{
    char sql[128] = {};
    char *errmsg;
    char **resultp;
    int nrow;
    int ncoloumn;
    int index;
    
    int i, j;
    
    sprintf(sql, "select * from stu");
    
    if(sqlite3_get_table(db, sql, &resultp, &nrow, &ncoloumn, &errmsg) != SQLITE_OK)
    {
        printf("query error:%s\n", errmsg);
    }
    else
    {
        printf("query done\n");
    }
    
    for(j = 0; j < ncoloumn; j++)
    {
        printf("%-11s ", resultp[j]);
    }
    putchar(‘\n‘);
    
    index = ncoloumn;
    for(i = 0; i < nrow; i++)
    {
        for(j = 0; j < ncoloumn; j++)
        {
            printf("%-11s ", resultp[index++]);
        }
        putchar(‘\n‘);
    }
    
    return 0;
}

int main(int argc, const char *argv[])
{
    sqlite3 *db;
    char *errmsg;
    int cmd;
    
    if(sqlite3_open(DB, &db) != 0)
    {
        printf("open error\n");
        return -1;
    }
    
    if(sqlite3_exec(db, "create table stu(id Integer, name char, score Float)", NULL, NULL, &errmsg) != SQLITE_OK)
    {
        printf("create table error:%s\n", errmsg);
    }
    
    while(1)
    {
        printf("\n**************************************\n");
        printf("1:insert\n2:delete\n3:query\n4:update\n5:quit\n6:query2\n");
        printf("**************************************\n\n");
        
        scanf("%d", &cmd);
        switch(cmd)
        {
            case 1:
                do_insert(db);
            break;
            case 2:
                do_delete(db);
            break;
            case 3:
                do_query(db);
            break;
            case 4:
                do_update(db);
            break;
            case 5:
                return 0;
        case 6:
        do_query_no_callback(db);
        break;
            default:break;
        }
    }

    return 0;
}