22 views

数据库编程

SQLite 简介

目前最新为 3.x 版本的,可以用下面的命令安装也可以克隆源码编译安装。

# 安装终端交互工具和 C 开发库
sudo apt install -y sqlite3 libsqlite3-dev

GitHUb:https://github.com/sqlite/sqlite

官网https://www.sqlite.org/ (这是最适合学习的地方,有需要或者遇到问题都可以在文档查询,特别是各种 API 的原型及使用解释,后面我也只举例几个基本的 API,实际开发还是需要去查文档)

SQLite 是一款轻量级的开源嵌入式数据库,使用方便,性能出众,广泛应用于消费电子、医疗、工业控制和军事等领域。SQLite 具有以下特点。

  • 性能:SQLite 对数据库的访问性能很高,其运行速度比 MySQL 等开源数据库要快很多。
  • 体积:非常小巧,最低只需要几百KB的内存就可以运行。
  • 可移植性:支持 32 位和 64 位体系的硬件平台,也能在 Windows、Linux、BSD、Mac OS 和 Solaries 等平台上运行。
  • 稳定性:支持 ACID 特性,即原子性、一致性、隔离性和持久性。
  • SQL 支持:支持 ANSI SQL92 中的大多数标准,提供了对子查询、视图和触发器机制的支持。
  • 接口:为 C、Java、PHP、Python、Tcl 等多种语言提供了 API 接口。

SQLite 采用了总体模块化设计

  • 接口:接口由 SQLite C API 函数组成。所有的应用程序都必须通过接口访问 SQLite 数据库。
  • 编译器:编译器由词法分析、语法分析和中间代码生成 3 个模块组成。其中,词法分析模块和语法分析模块负责检查 SQL 语句的语法,然后把生成的语法树传递给中间代码生成模块。中间代码生成模块负责生成 SQLite 引擎可以识别的中间代码。
  • 数据库引擎:数据库引擎是 SQlite 的核心,负责运行中间代码,指挥数据库的具体操作。
  • 后台:后台由 B 树、页缓冲和系统调用 3 个模块组成。其中,B模块负责维护索引,页缓冲负责页面数据的传输,系统调用负责和操作系统交互,最终实现数据库的访问。

SQLite 支持的基本数据类型主要有:NULL、NUMERIC、INTEGER、REAL 和 TEXT。SQLite 会自动把其它数据类型转换成以上 5 类基本数据类型,转换规则如下:

  • char、clob、test 和 varchar 转换为 TEXT。
  • integer 转换为 INTEGER。
  • real、double 和 float 转换为 REAL。
  • blob 转换为 NULL。
  • 其余数据类型转换为 NUMERIC。

使用命令

shell 中输入 sqlite3 回车进入交互环境,退出可以执行 .q.quit.exit

新建数据库

shell 执行 sqlite3 数据库文件名 ,如果文件已经存在,则直接打开,如果不存在则创建。SQLite 不会立即创建,而是等第一个表创建完成才会创建出文件,以提高效率。

SQLite 可以根据插入的数据的实际类型动态改变列的类型,所在在 create 语句中并不要求给出列的类型。

示例:创建数据库名字为 test.db ,并新建一个表为 “newTable”,这个表具有 name、sex 和 age 3列。

创建索引

为了加快表的查询速度,往往在主键上添加索引。

示例:在 name 列添加索引”newIndex”。

操作数据

Tip:执行的 SQLite 命令后要加半角分号。

示例:在 newTable 表中进行数据的插入、更新和删除操作。

插入

查看表

更新数据并查看

删除数据并查看

批量操作

示例:连续在 newTable 中插入两条数据。

执行 commit 后,才会把数据写入数据库中。

数据库的导入导出(备份与还原)

先退出交互环境

将 test.db 导出到 test.sql

然后删除 test.db,再将备份的 test.sql 导入

查看 test.sql 的内容,其实就是把执行的命令保存起来了

文件也要小很多

SQLite C 编程接口

数据库的打开与关闭

sqlite3_open 用于打开 SQLite3 数据库

int sqlite3_open(const char *dbname, sqlite3 **db);

dbname 要打开(或创建)数据库的名字,db 创建的数据库对象

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

sqlite3_close 用于关闭 SQLite3 数据库

int sqlite3_close(sqlite3 *db);
/**
 * @author IYATT-yx
 * @date 2021-5-4
 * @brief SQLite3 打开和关闭数据库示例
 */
#include <stdio.h>
#include <sqlite3.h>

int main(void)
{
    sqlite3 *db = NULL;
    
    if (sqlite3_open("test.db", &db))
    {
        printf("打开失败\n");
    }
    else
    {
        printf("打开成功\n");
    }
    sqlite3_close(db);
}

数据库的语句执行

int sqlite3_exec(
  sqlite3*,                                  /* An open database */
  const char *sql,                           /* SQL to be evaluated */
  int (*callback)(void*,int,char**,char**),  /* Callback function */
  void *,                                    /* 1st argument to callback */
  char **errmsg                              /* Error msg written here */
);
/**
 * @author IYATT-yx
 * @date 2021-5-4
 * @brief SQLite3 数据库语句执行
 */
#include <stdio.h>
#include <sqlite3.h>

int main(void)
{
    sqlite3 *db = NULL;
    
    sqlite3_open("test.db", &db);

    sqlite3_exec(db, "create table new(name, age)", NULL, NULL, NULL);
    sqlite3_exec(db, "create index on new(name)", NULL, NULL, NULL);
    sqlite3_exec(db, "insert into new values('IYATT-yx', 20)", NULL, NULL, NULL);

    sqlite3_close(db);
}
/**
 * @author IYATT-yx
 * @date 2021-5-4
 * @brief SQLite3 数据库语句执行 - 回调函数的使用
 */
#include <stdio.h>
#include <sqlite3.h>

/**
 * @brief sqlite3_exec 执行语句输出内容有几行就调用几次本函数
 * @param data 接收 sqlite3_exec 的第 4 个参数传入的数据
 * @param argc 输出内容的列数
 * @param argv 输出内容,argv[0]第一列,argv[1]第二列 ......
 * @param colName 输出内容的列标,colName[0]第一列标,colName·[1]第二列标
 * @return 返回 0 则正常结束,返回非 0 值参看 SQLite3 的错误代码
 */
int callback(void *data, int argc, char **argv, char **colName)
{
    // 显示传入的数据
    printf("%s\n", (char *)data);

    // 显示列标
    for (int i = 0; i < argc; ++i)
    {
        printf("%s\t", colName[i]);
    }
    printf("\n");

    // 显示内容
    for (int i = 0; i < argc; ++i)
    {
        printf("%s\t", argv[i]);
    }
    printf("\n");

    return 0;
}

int main(void)
{
    sqlite3 *db = NULL;

    sqlite3_open("test.db", &db);

    const char *sqlite[] =
    {
        "create table new(name, age)",
        "insert into new values('AB', 20)",
        "insert into new values('yx', 15)"
    };

    for (int i = 0; i < 3; ++i)
    {
        sqlite3_exec(db, sqlite[i], NULL, NULL, NULL);
    }

    char data[] = "传入数据";
    char *msg = NULL;
    if (sqlite3_exec(db, "select * from new", callback, (void *)data, &msg))
    {
        // 返回值非 0 时,执行语句出错,打印错误内容
        printf("%s\n", msg);
        // 释放字符串的内存
        sqlite3_free(msg);
    }

    sqlite3_close(db);
}

数据库查询语句

int sqlite3_get_table(
  sqlite3 *db,          /* An open database */
  const char *zSql,     /* SQL to be evaluated */
  char ***pazResult,    /* Results of the query */
  int *pnRow,           /* Number of result rows written here */
  int *pnColumn,        /* Number of result columns written here */
  char **pzErrmsg       /* Error msg written here */
);
void sqlite3_free_table(char **result);
/**
 * @author IYATT-yx
 * @date 2021-5-4
 * @brief 数据库查询
 */
#include <stdio.h>
#include <sqlite3.h>

int main(void)
{
    sqlite3 *db = NULL;

    sqlite3_open("test.db", &db);
    sqlite3_exec(db, "create table new(name, age)", NULL, NULL, NULL);
    sqlite3_exec(db, "insert into new values('IYATT-yx', 20)", NULL, NULL, NULL);
    sqlite3_exec(db, "insert into new values('李华', 21)", NULL, NULL, NULL);

    char **result = NULL;
    int row = 0, col = 0;
    char *errmsg = NULL;
    if (sqlite3_get_table(db, "select * from new", &result, &row, &col, &errmsg))
    {
        printf("%s\n", errmsg);
        sqlite3_free(errmsg);
    }
    else
    {
        for (int i = 0; i <= row; ++i)
        {
            for (int j = 0; j < col; ++j)
            {
                printf("%s | ", result[i * col + j]);
            }
            printf("\n");
        }
    }

    sqlite3_free_table(result);
    sqlite3_close(db);
}
int sqlite3_prepare(
  sqlite3 *db,            /* Database handle */
  const char *zSql,       /* SQL statement, UTF-8 encoded */
  int nByte,              /* Maximum length of zSql in bytes. */
  sqlite3_stmt **ppStmt,  /* OUT: Statement handle */
  const char **pzTail     /* OUT: Pointer to unused portion of zSql */
);
int sqlite3_step(sqlite3_stmt*);
/**
 * @author IYATT-yx
 * @date 2021-5-4
 * @brief 数据库查询 2
 */
#include <stdio.h>
#include <sqlite3.h>

int main(void)
{
    sqlite3 *db = NULL;

    sqlite3_open("test.db", &db);
    sqlite3_exec(db, "create table new(name, sex, age)", NULL, NULL, NULL);
    sqlite3_exec(db, "insert into new values('IYATT-yx', 'male', 20)", NULL, NULL, NULL);
    sqlite3_exec(db, "insert into new values('李华', 'female', 21)", NULL, NULL, NULL);

    sqlite3_stmt *stmt = NULL;
    int rc = sqlite3_prepare(db, "select * from new", -1, &stmt, NULL);
    if (rc)
    {
        printf("查询失败!\n");
    }
    else
    {
        rc = sqlite3_step(stmt);
        int col = sqlite3_column_count(stmt);
        while (rc == SQLITE_ROW)
        {
            for (int i = 0; i < col; ++i)
            {
                printf("%s | ", sqlite3_column_text(stmt, i));
            }
            printf("\n");
            rc = sqlite3_step(stmt);
        }
    }

    sqlite3_finalize(stmt);
    sqlite3_close(db);
}

发表评论