Sqlite3瞎捣鼓笔记

作者：忧之灵_435 | 来源：互联网 | 2023-06-17 13:44

目录目录Sqlite3数据库SELECTINSERTUPDATE

- - 目录
  - Sqlite3 数据库SELECT INSERT UPDATE
  - Sqlite3 数据库UPDATE OF 触发器INSERT 触发器
  - Sqlite3 sum函数求某记录的比例
  - Sqlite3 substr字符串截取函数获取未知长度的前n-1个字符
  - Sqlite3 创建内存数据库
  - Sqlite3 导出内存数据库到文件

首先我要安利一个免费开源的工具软件,sqlitestudio.用来查看sql数据库的db文件的.
界面是Qt做的,漂亮,而且功能丰富实用.中文化程度高.
里面的sql编辑器可以直接写sql操作数据库.带语法检查.
这样你执行之后得到了什么结果就能一眼看出来.
(记得点击刷新按钮,不然看到的是上次的结果)
这年头,没个可视化还算什么数据库工具.有图:
sqlitestudio

##################正文#####################

一个朋友的一个课后作业,从文本文件依次读n阶字符串,写入数据库,统计每个字符串出现的次数与条件概率和联合概率只统计文件中的英文字符,并统一转成大写字母

假如样本为:abcd9abcdefg

则统计的字符串为
0阶 a b c d a b c d e f g
1阶 ab bc cd da ab bc cd de ef fg
2阶 abc bcd cda dab abc bcd cde def efg
….
联合概率: 该字符串在文件中出现的次数/所有字符串的统计次数
条件概率: 该字符串在本阶的联合概率/ 该字符串前n-1个字符组成的字符串,在上一阶的联合概率

另一个用于概率计算演示的样本: aB2cA4b9789

由样本生成的0阶表

key	count	joinprobility	conprobility
A	2	0.4	NULL(0阶没有)
B	2	0.4	NULL(0阶没有)
C	1	0.2	NULL(0阶没有)

由样本生成的1阶表

key	count	joinprobility	conprobility
AB	2	0.5 (2/(2+1+1))	1.25 (0.5/0.4)
BC	1	0.25 (1/(2+1+1))	0.625 (0.25/0.4)
CA	1	0.25 (同上)	1.25 (0.25/0.2)

* 括号里的内容是不在数据库里的

Sqlite3 数据库SELECT INSERT UPDATE

初步方案是,先将文本文件解析并写到数据库里,只写key(主键)和count字段.
然后通过sql语句,由数据库计算联合概率和条件概率.
首先想到的是先 SELECT 查找该记录是否已存在,如果已存在则执行 UPDATE 否则执行 INSERT.

const char* table_name = "mytable"; // 表名前缀

/*查找记录回调函数 *有查找结果时才会触发该回调函数,用于显示查询结果 *这里没有使用具体的查询结果,只要该函数被调用就说明该记录已存在.直接置1 */
int find_key_callback(void* data, int count, char** value, char** name)
{
    *(int*)data = 1;
    return 0;
}

/*查找记录是否已经存在 *db 数据库句柄 *order 当前阶数 *str 当前阶数长度的字符串 */
int find_key(sqlite3* db, int order, char* str)
{
    int is_exist = 0;   // 该字符串是否存在数据库中(1,存在. 0,不存在)
    char* err = NULL;
    char sql[1024] = { 0 };
    sprintf(sql, "SELECT key FROM %s%d WHERE key='%s'", table_name, order, str);
    int res = sqlite3_exec(db, sql, find_key_callback, &is_exist, &err);
    if (res != SQLITE_OK)
    {
        ERROR_MESSAGE(sqlite3_errstr(res));
        return -1;
    }
    return is_exist;
}

/*更新对应阶数(order)的表中的数据(str)*/
int update_key(sqlite3* db, int order, char* str)
{
    char* err = NULL;
    char sql[1024] = { 0 };
    sprintf(sql, "UPDATE %s%d SET count=count+1 WHERE key='%s'", table_name, order, str);
    int res = sqlite3_exec(db, sql, NULL, NULL, &err);
    if (res != SQLITE_OK)
    {
        ERROR_MESSAGE(sqlite3_errstr(res));
        return -1;
    }
    return 0;
}
/*向对应阶数(order)的表里插入新数据(str)*/
int insert_key(sqlite3* db, int order, char* str)
{
    char* err = NULL;
    char sql[1024] = { 0 };
    sprintf(sql, "INSERT INTO %s%d (key,count) VALUES ('%s',1)", table_name, order, str);
    int res = sqlite3_exec(db, sql, NULL, NULL, &err);
    if (res != SQLITE_OK)
    {
        ERROR_MESSAGE(sqlite3_errstr(res));
        return -6;
    }
    return 0;
}

因此我们向数据库写入一条记录的程序就变成了这样

int input_record(sqlite3* db, size_t order, char* str)
{
    if (find_key(db, order, str))    // 该记录是否已经存在数据库中
    {
        update_key(db, order, str);  // 已存在,则更新count = count+1
    }
    else
    {
        insert_key(db, order, str);  // 不存在,则插入str,同时将count设为1
    }
    return 0;
}

这个方法有个弊端,无论记录存不存在,都执行了两条sql语句.
下面针对sqlite3的C语言接口函数进行优化:
直接 INSERT 如果记录已存在,则sqlite3_exec函数会返回一个错误码,那我们只要在执行完 INSERT之后,根据sqlite3_exec的返回值来决定是否执行 UPDATE
如此一来就不需要 SELECT 了,新的代码为

int input_record(sqlite3* db, size_t order, char* str)
{
    char* err = NULL;
    char sql[256] = { 0 };
    sprintf(sql, "INSERT INTO %s%d (key,count) VALUES ('%s',1)", table_name, order, str);
    int res = sqlite3_exec(db, sql, NULL, NULL, &err);
    if (res == SQLITE_CONSTRAINT)  // 已有记录,则 INSERT 失败,改为执行 UPDATE 操作
    {
        sprintf(sql, "UPDATE %s%d SET count=count+1 WHERE key ='%s'", table_name, order, str);
        res = sqlite3_exec(db, sql, NULL, NULL, &err);
    }
    if (res != SQLITE_OK)
    {
        ERROR_MESSAGE(sqlite3_errstr(res));
        return -3;
    }
    return 0;
}

如此一来,在最佳情况下,只会执行一条 INSERT 语句.

Sqlite3 数据库UPDATE OF 触发器,INSERT 触发器

Sqlite3 sum函数求某记录的比例

联合概率的计算可以通过sqlite的触发器来完成

/* 更新触发器 * 检测到 count字段更新动作,自动计算联合概率,更新joinprobility字段 */
int update_trigger(sqlite3* db, int order)
{
    char* err = NULL;
    char sql[1024] = { 0 };
    char table[256] = { 0 };
    // 需要注意一点,更新触发器有两种模式,一种是对任何记录的更新都会触发
    // 另一种是 只有指定的字段被更新才会触发
    // 这里使用第二种,只有count被更新才会触发 触发器
    // 因为我们在更新触发器里执行了更新动作,
    // 使用第一种触发方式会导致触发器自触发,sqlite对此会提供相关的报错信息.
    sprintf(table, "%s%d", table_name, order);
    sprintf(sql, "CREATE TRIGGER update_trigger%d "\
            "AFTER UPDATE OF count "\
            "ON %s "\
            "BEGIN "\
            "UPDATE %s "\
            "SET joinprobility = (count * 1.0 / (SELECT sum(count)FROM %s)); "\
            "END;", order, table, table, table);
    int res = sqlite3_exec(db, sql, NULL, NULL, &err);
    if (res != SQLITE_OK)
    {
        ERROR_MESSAGE(sqlite3_errstr(res));
        return -6;
    }
    return 0;
}

/* 插入触发器*/
int insert_trigger(sqlite3* db, int order)
{
    char* err = NULL;
    char sql[1024] = { 0 };
    char table[256] = { 0 };

    sprintf(table, "%s%d", table_name, order);
    sprintf(sql, "CREATE TRIGGER insert_trigger%d "\
            "AFTER INSERT "\
            "ON %s "\
            "BEGIN "\
            "UPDATE %s "\
            "SET joinprobility = (count * 1.0 / (SELECT sum(count)FROM %s)); "\
            "END;", order, table, table, table);
    int res = sqlite3_exec(db, sql, NULL, NULL, &err);
    if (res != SQLITE_OK)
    {
        ERROR_MESSAGE(sqlite3_errstr(res));
        return -6;
    }
    return 0;
}

Sqlite3 substr字符串截取函数,获取未知长度的前n-1个字符

最后计算条件概率,条件概率需要一个sql字符串截取函数,截取一串长度为n的字符的前n-1位,n未知.而且涉及两个表的数据

int update_conprobility(sqlite3* db, int order)
{
    char* err = NULL;
    char sql[256] = { 0 };

    if (order != 0) // 不计算第0阶的联合概率
    {
        sprintf(sql, "UPDATE %s%d SET cOnprobility="\
                "(joinprobility*1.0/"\
                "(SELECT joinprobility FROM %s%d WHERE"\
                "key = substr(%s%d.key,1,length(%s%d.key)-1)))", \
                table_name, order, table_name, order - 1, \
                table_name, order, table_name, order);
        int res = sqlite3_exec(db, sql, NULL, NULL, &err);
        if (res != SQLITE_OK)
        {
            ERROR_MESSAGE(sqlite3_errstr(res));
            return -1;
        }

        sprintf(sql, "UPDATE %s%d SET cOnprobility= 1.0 "\
                    "WHERE conprobility > 1", table_name, order);// 数据修复,大于1的置1 
        res = sqlite3_exec(db, sql, NULL, NULL, &err);
        if (res != SQLITE_OK)
        {
            ERROR_MESSAGE(sqlite3_errstr(res));
            return -1;
        }
    }
    return 0;
}

至此基本功能完成.
结果一个388KB的txt文件, 获取0~10阶的数据足足跑了一晚上.
这让我很不开心

于是我去掉了触发器,因为每一个插入,更新记录,都会执行触发器计算,速度肯定会有影响.
同时改成了多线程,
首先通过fopen打开同一个文件10次,获取10个只读的文件句柄
然后建立10个线程同时操作一个数据库句柄,负责向该数据库中的10个表同时写数据
结果处理速度并没有明显提高,因为数据库都是线程安全的,内部肯定是有加锁处理的.
就在苦恼之时,发现了sqlite3有内存数据库

Sqlite3 创建内存数据库

获取内存数据库句柄可以按照和文件数据库一样的方法来操作.

sqlite3_open(":memory:",db);

而且内存数据库是没有锁的,而我这个需求每个线程操作一个表,表与表之间基本没有关联性,而且线程只负责写,因次,不需要线程锁.内存数据库换上之后,速度飞快.
原来需要一晚上的数据现在50分钟就跑完了.
需要注意的一点是,内存数据库在内存里,退出就没了.

Sqlite3 导出内存数据库到文件

需要将sqlite3的内存数据库导出到文件数据库,
在sqlite3的官网有一段例程,去看网页右下角的 loadOrSaveDb 函数,
网上有人中文翻译过,可以去搜一下
第一个参数是内存数据库句柄,第二个参数是文件地址,第三个参数用来设置是导出还是导入.

推荐阅读

join
解决PHP及Web开发中的UTF-8乱码问题

本文详细探讨了在Web开发中常见的UTF-8编码问题及其解决方案，包括HTML页面、PHP脚本、MySQL数据库以及JavaScript和Flash应用中的乱码问题。 ... [详细]

蜡笔小新 2024-11-20 10:58:03
metadata
Python全栈之旅：SQLAlchemy ORM中的外键与关系

本文探讨了SQLAlchemy ORM框架中如何利用外键和关系（relationship）来建立表间联系，简化复杂的查询操作。通过示例代码详细解释了relationship的定义、使用方法及其与外键的相互作用。 ... [详细]

蜡笔小新 2024-11-27 11:20:01
list
Android 中SQLite技术实例详解

Android和iOS的数据库都是用SQLite来实现.一，SQLite数据库简介:轻量级：SQLite数据库是一个轻量级的数据库，适用于少量数据的CURD;文件本质:SQL ... [详细]

蜡笔小新 2023-10-13 12:59:38
metadata
没有这样的表android_metadata，有什么问题？ - No such table android_metadata, what's the problem?

Iamcopyingapre-existingdatabasetodatadatapackagenamedatabasesusingcodelearnedfromusi ... [详细]

蜡笔小新 2023-10-13 11:58:30
metadata
无法在SQLite中插入，错误代码:19 - Unable to INSERT in SQLite, Error code:19

WhenImtryingtorunthefollowing:当我试图运行以下内容时:ContentValuescvnewContentValues();cv ... [详细]

蜡笔小新 2023-10-12 19:27:09
metadata
因未捕获的异常导致崩溃后SQLite日志文件行为

我正在一个涉及SQLite的项目中，我只有一个数据库文件，现在我正在测试我的应 ... [详细]

蜡笔小新 2023-10-12 03:04:18
metadata
在Android Studio中查看SQLite数据库

原来查看数据库内容，我们一般都是将数据库文件从手机导出，再用专门的软件打开查看，比较繁琐。最近发现了一个比较方便的方法：使用工具stetho。使用方式在gradle中配置depen ... [详细]

蜡笔小新 2023-10-11 18:37:00
metadata
SQLiteCONSTRAINTS(约束)

SQLite–CONSTRAINTS(约束)约束是数据列在表上执行的规则。这些是用来限制的数据类型可以进入一个表。这样可以确保数据的准确性和可靠性在数据库中。　　　　级或表级约束可 ... [详细]

蜡笔小新 2023-10-11 16:34:10
format
有没有人用过sqlite?关于'table' has no column named 'column'

有没有人用过sqlite?关于tablehasnocolumnnamedcolumn插入数据的时候报上边的错。问题是我明明有这一列。直接在sqlitedevoloper里执 ... [详细]

蜡笔小新 2023-09-25 17:43:26
format
POJ 题目2892 Tunnel Warfare（线段树单点更新查询，求单点所在最大连续区间长度）

TunnelWarfareTimeLimit:1000MS MemoryLimit:131072KTotalSubmissions:7307 ... [详细]

蜡笔小新 2024-11-29 09:33:01
list
解决JavaScript中法语字符排序问题

在开发一个使用JavaScript、HTML和CSS的Web应用时，遇到从SQLite数据库中提取的法语词汇排序不正确的问题，特别是带重音符号的字母未按预期排序。 ... [详细]

蜡笔小新 2024-11-21 09:08:57
list
SQLite3 SqlCipher windows下的编译

SQLite3是一个广泛使用的数据库，从linux，windows到安卓都有SQLite的应用。本文介绍SQLite3在windows上的编译。SQLite3提供了多种源代码的下载 ... [详细]

蜡笔小新 2023-10-11 12:56:01
list
Easyui + asp.net mvc + sqlite 开发教程（录屏）适合入门

第一节：前言（技术简介）EasyUI是一套js的前端框架利用它可以快速的开发出好看的前端系统web它是在jquery的框架基础上面现在越来越多的企业用它来开发web系统 ... [详细]

蜡笔小新 2023-09-25 14:43:37
format
在SQLite Order By Datetime中，desc返回错误的数据 - In SQLite Order By Datetime desc returning wrong data

IhaveonedoubtinSqlite.dteTimeDataTypeisVarchar(200)inTablestructure.Iwanttogetresult ... [详细]

蜡笔小新 2023-09-24 14:50:25
format
小巧、快速、稳定！我为什么选择使用Sqlite?

州的先生（https:zmister.com）在很多项目中都有使用到SQLite数据库作为数据存储的工具，其中包括一些桌面图形界面程序和线上的Web应用程序。至今为止，它们都运行良 ... [详细]

蜡笔小新 2023-09-24 10:45:45

忧之灵_435

这个家伙很懒，什么也没留下！

Tags | 热门标签

RankList | 热门文章