Linux---sqlite3数据库

一、数据库分类

1. 按数据关系分类

2. 按功能规模分类

注：以上分类中的代表产品均为关系型数据库。

二、核心概念与术语

1. 基础术语

• DB（Database）：存储数据的集合（如：用户表、订单表）。
• DBMS（Database Management System）：管理数据库的软件（如：MySQL、Oracle）。
• MIS（Management Information System）：集成数据库的管理信息系统（如：ERP系统）。
• OA（Office Automation）：基于数据库的办公自动化系统（如：审批流程系统）。

2. 数据库结构层次

数据库（DB）
├── 表（Table）
│   ├── 字段（Field，最小单位，如：姓名、年龄）
│   └── 记录（Record，由字段组成的一行数据）
└── 其他表...

• 字段：表的列，定义数据类型（如：VARCHAR、INT）。
• 记录：表的行，表示一条完整数据。

三、SQLite 数据库特点

1. 核心特性

2. 适用场景

• 移动应用（如：Android/iOS 本地存储）
• 嵌入式设备（如：智能硬件）
• 小型项目原型开发

四、数据库文件操作

1. 创建/打开数据库

sqlite3 test.db    # 创建或打开 test.db 数据库文件

提示符变为 sqlite> 表示进入交互模式

2. 导入导出数据

# 导出数据库到 SQL 脚本
sqlite3 test.db .dump > backup.sql

# 从 SQL 脚本导入数据
sqlite3 new.db < backup.sql

五、数据表操作 (DDL)

1. 创建表

-- 基础表结构
CREATE TABLE users (
    id INTEGER PRIMARY KEY,   -- 主键约束
    name TEXT NOT NULL,       -- 非空约束
    age INTEGER,
    reg_date DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- 带自增主键
CREATE TABLE user3 (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name CHAR(20),
    age INT
);

SQLite 是弱类型数据库，字段类型仅为建议类型

2. 删除表

DROP TABLE users;  -- 慎用！会删除表结构和所有数据

六、数据操作 (DML)

1. 插入数据

-- 全字段插入
INSERT INTO users VALUES (1, '张三', 25, '2023-08-20 10:00:00');

-- 指定字段插入
INSERT INTO users (name, age) VALUES ('李四', 30);

-- 插入当前时间
INSERT INTO user1 VALUES (2, '王五', 28, datetime('now','+8 hours'));

2. 更新数据

-- 修改指定记录
UPDATE users SET age = 26 WHERE name = '张三';

-- 多条件更新
UPDATE users SET age = 27 WHERE name = '李四' AND id = 2;

3. 删除数据

-- 清空表 (保留结构)
DELETE FROM users;

-- 条件删除
DELETE FROM users WHERE age < 18;
DELETE FROM users WHERE id = 3 OR name = 'test';

七、数据查询 (DQL)

1. 基础查询

-- 查询所有字段
SELECT * FROM users;

-- 查询指定字段
SELECT name, age FROM users;

-- 条件查询
SELECT * FROM users WHERE age BETWEEN 20 AND 30;

2. 复杂查询

-- 排序查询
SELECT * FROM users ORDER BY age DESC;

-- 模糊查询
SELECT * FROM users WHERE name LIKE '张%';

-- 分页查询
SELECT * FROM users LIMIT 10 OFFSET 5;  -- 第6-15条记录

八、系统命令（以 . 开头）

九、数据类型与约束

1. 常用数据类型

2. 常用约束

十、SQLite C 语言接口编程

（一）核心操作流程

graph TD
    A[打开数据库 sqlite3_open] --> B[执行操作]
    B --> C{操作类型}
    C -->|查询| D[使用 sqlite3_get_table]
    C -->|增删改| E[使用 sqlite3_exec]
    B --> F[关闭数据库 sqlite3_close]

（二）核心函数详解

1. 打开数据库 sqlite3_open

int sqlite3_open(const char *filename, sqlite3 **ppDb);

返回值：

• SQLITE_OK (0)：成功
• 其他值：错误代码 (需用 sqlite3_errmsg() 获取错误信息)

示例：

sqlite3 *db = NULL;
int rc = sqlite3_open("test.db", &db);
if (rc != SQLITE_OK) {
    fprintf(stderr, "无法打开数据库: %s\n", sqlite3_errmsg(db));
    exit(1);
}

2. 执行查询操作 sqlite3_get_table

int sqlite3_get_table(
    sqlite3 *db,          // 数据库句柄
    const char *zSql,     // SQL 语句
    char ***pazResult,    // 结果集指针
    int *pnRow,           // 返回行数
    int *pnColumn,        // 返回列数
    char **pzErrmsg       // 错误信息指针
);

结果集结构：

// 假设查询返回 2 列 (name, age) 和 3 行数据
pazResult[0] = "name"
pazResult[1] = "age"
pazResult[2] = "Alice"
pazResult[3] = "25"
pazResult[4] = "Bob"
pazResult[5] = "30"

示例：

char **result;
int rows, cols;
char *errmsg = NULL;

rc = sqlite3_get_table(db, "SELECT name, age FROM users", 
                      &result, &rows, &cols, &errmsg);

if (rc == SQLITE_OK) {
    // 处理结果集
    for (int i = 0; i <= rows; i++) { // 第0行是列名
        for (int j = 0; j < cols; j++) {
            printf("%s\t", result[i*cols + j]);
        }
        printf("\n");
    }
    sqlite3_free_table(result); // 必须释放内存！
} else {
    fprintf(stderr, "SQL错误: %s\n", errmsg);
    sqlite3_free(errmsg);
}

3. 执行非查询操作 sqlite3_exec

int sqlite3_exec(
    sqlite3 *db,               // 数据库句柄
    const char *sql,           // SQL 语句
    int (*callback)(void*,int,char**,char**), // 回调函数
    void *arg,                 // 回调函数参数
    char **errmsg              // 错误信息指针
);

回调函数原型：

int callback(
    void *arg,        // 用户传递的参数
    int num_cols,     // 结果列数
    char **col_values, // 当前行的列值
    char **col_names   // 列名数组
);

示例：

// 插入数据
rc = sqlite3_exec(db, "INSERT INTO users VALUES(3, 'Charlie', 28)", 
                  NULL, NULL, &errmsg);

// 带回调的查询
int print_callback(void *arg, int cols, char **values, char **names) {
    for (int i = 0; i < cols; i++) {
        printf("%s: %s\t", names[i], values[i]);
    }
    printf("\n");
    return 0; // 必须返回0
}

rc = sqlite3_exec(db, "SELECT * FROM users", 
                 print_callback, NULL, &errmsg);

4. 关闭数据库 sqlite3_close

int sqlite3_close(sqlite3 *db);

注意事项：

• 关闭前必须完成所有事务
• 返回 SQLITE_BUSY 表示仍有未完成的查询

（三）内存管理与错误处理

1. 释放资源

sqlite3_free_table(char **result);  // 释放 sqlite3_get_table 的结果
sqlite3_free(void *ptr);           // 释放错误信息内存

2. 错误信息获取

const char *errmsg = sqlite3_errmsg(db);
printf("错误信息: %s\n", errmsg);

实例：

程序用于将一个文本格式的字典文件（每行包含一个单词及其解释）转换为 SQLite 数据库格式，便于后续的查询和管理。

#include <stdio.h>
#include <sqlite3.h>
#include <string.h>
#include <unistd.h>

// 主函数，参数为字典文本文件和目标数据库文件
int main(int argc, const char *argv[])
{
    // 检查命令行参数是否正确
    if (argc != 3)
    {
        printf("Usage: %s <*.txt> <*.db>\n", argv[0]);
        return -1;
    }

    // 打开字典文本文件
    FILE *fp = fopen(argv[1], "r");
    if (fp == NULL)
    {
        perror("fopen fail");
        return -1;
    }

    // 初始化 SQLite 数据库
    sqlite3 *db;
    int ret = sqlite3_open("dict.db", &db); // 打开或创建数据库文件
    if (ret != SQLITE_OK)
    {
        printf("sqlite3_open fail:%s\n", sqlite3_errmsg(db));
        return -1;
    }
    printf("sqlite3_open success\n");

    // 创建字典表
    char sql[1024] = {0};
    printf("Please create table:");
    fgets(sql, sizeof(sql), stdin); // 从标准输入读取创建表的 SQL 语句
    sql[strlen(sql) - 1] = '\0';     // 去除换行符

    char *errmsg = NULL;
    ret = sqlite3_exec(db, sql, NULL, NULL, &errmsg); // 执行 SQL 语句
    if (ret != SQLITE_OK)
    {
        printf("sqlite3_exec fail : %s \n", errmsg);
        return -1;
    }

    // 读取字典文本文件并插入数据库
    char buf[1024] = {0};
    int i = 0;
    while (1)
    {
        char *pret = fgets(buf, sizeof(buf), fp); // 逐行读取文本文件
        if (pret == NULL)
        {
            break; // 读取完成
        }

        // 解析单词和解释
        char *pword = strtok(buf, " ");    // 提取单词
        char *pexplain = strtok(NULL, "\r"); // 提取解释

        printf("pword    = %s\n", pword);
        printf("pexplain = %s\n", pexplain);

        // 构造插入 SQL 语句
        sprintf(sql, "insert into dict values (%d,\"%s\",\"%s\");", i, pword, pexplain);

        ret = sqlite3_exec(db, sql, NULL, NULL, &errmsg); // 执行插入操作
        if (ret != SQLITE_OK)
        {
            printf("sqlite3_exec fail : %s \n", errmsg);
            return -1;
        }
        ++i;
        // sleep(1); // 如果需要控制插入速度，可以取消注释
    }

    // 关闭文件和数据库连接
    fclose(fp);
    sqlite3_close(db);

    return 0;
}