掌握SQLite数据库--SQL用法

最新推荐文章于 2025-04-13 08:30:00 发布

Psyduck_ing

最新推荐文章于 2025-04-13 08:30:00 发布

阅读量956

点赞数 20

文章标签： sql sqlite 嵌入式实时数据库

本文链接：https://blog.youkuaiyun.com/Psyduck_ing/article/details/144003421

版权

SQLite：是一款轻型的数据库，是遵守ACID的关系型数据库管理系统，它包含在一个相对小的C库中。它的设计目标是嵌入式的，而且已经在很多嵌入式产品中使用了它，它占用资源非常的低，在嵌入式设备中，可能只需要几百K的内存就够了。它能够支持Windows/Linux/Unix等等主流的操作系统.

数据库wal机制：修改并不直接写入到数据库文件中，而是写入到另外一个称为WAL的文件中；如果事务失败，WAL中的记录会被忽略，撤销修改；如果事务成功，它将在随后的某个时间被写回到数据库文件中，提交修改。同步WAL文件和数据库文件的行为被称为checkpoint（检查点），它由SQLite自动执行，默认是在WAL文件积累到1000页修改的时候；当然，在适当的时候，也可以手动执行checkpoint，SQLite提供了相关的接口。执行checkpoint之后，WAL文件会被清空

WAL模式的优点：

在大多数场景明显更快；

读写操作可并发；

顺序访问磁盘；

使用fsync()操作更少，因此在调用fsync()中断的系统上更不容易发生问题；

int Db_SetDb_PRAGMA(void)
{
    //WAL日志模式，WAL进一步提升了数据库的并发性，因为读不会阻塞写，而写也不会阻塞读。读和写可以并发执行
    int ret = Sqlite3exec(db, "PRAGMA journal_mode = WAL;");
    if(ret != SQLITE_OK){
        printf("PRAGMA journal_mode = WAL; failed. ret = %d", ret);
        return -1;
    }

    /*根据设置，当WAL文件达到SQLITE_DEFAULT_WAL_AUTOCHECKPOINT（默认值是1000）页（默认大小是1KB）时，
    这里设置为10，会自动使用当前COMMIT的线程来执行checkpoint操作
    */
    ret = Sqlite3exec(db, "PRAGMA wal_autocheckpoint = 10;");// 10KB = 10 * page_size(default: 1024)
    if(ret != SQLITE_OK){
        printf("PRAGMA wal_autocheckpoint = 10; failed. ret = %d", ret);
        return -1;
    }

    ret = Sqlite3exec(db, "PRAGMA journal_size_limit = 1000;");// auto flush when > 1k.
    if(ret != SQLITE_OK){
        printf("PRAGMA journal_size_limit = 1000; failed. ret = %d", ret);
        return -1;
    }

    //数据库引擎在紧急时刻会暂停以确定数据已经写入磁盘。这使系统崩溃或电源出问题时能确保数据库在重起后不会损坏。FULL synchronous很安全但很慢
    ret = Sqlite3exec(db, "PRAGMA synchronous = Full;");
    if(ret != SQLITE_OK){
        printf("PRAGMA synchronous = FULL; failed. ret = %d", ret);
        return -1;
    }

    return 0;
}

1. 创建数据库表，涉及命名表、定义列及每一列的数据类型。PRIMARY KEY用于表示主键

CREATE TABLE IF NOT EXISTS devices (uid text,userName text,delFlag integer,createTime long,updateTime long,deviceId text PRIMARY KEY,extAddr text);

创建了表名为devices数据库表，包含如下列

2. 删除数据库表，删除表定义及其所有相关数据、索引、触发器、约束和该表的权限规范

#devices为具体的表名
DROP TABLE devices;

3.删除数据库表记录，用于删除表中已有的记录。可以使用带有 WHERE 子句的 DELETE 查询来删除选定行，否则所有的记录都会被删除

#删除device表中deviceId='e950d433a44a5e859268c135addcea'这条记录
DELETE FROM device WHERE deviceId='e950d433a44a5e859268c135addcea';

#删除device表的所有记录
DELETE FROM device;

4.查询表数据， SELECT 语句用于从 SQLite 数据库表中获取数据，以结果表的形式返回数据。这些结果表也被称为结果集

#查询获取device表中的deviceId和delFlag两列字段所有信息，不带条件
SELECT deviceId,delFlag FROM device;

#查询获取device表中的deviceId和delFlag两列字段所有信息，带条件
SELECT deviceId,delFlag FROM device WHERE uid='f598e8388cc54dedbd02fe1fc1e5a8a2' and delFlag='0';

#查询获取device表所有字段信息，不带条件
SELECT * FROM device;

5.查询更新表数据，UPDATE 查询用于修改表中已有的记录。可以使用带有 WHERE 子句的 UPDATE 查询来更新选定行，否则所有的行都会被更新

#将device表中deviceId为z1833b0e70304701983955fae5934b21的delFlag字段置为0，带条件
UPDATE device SET delFlag=0 WHERE deviceId='z1833b0e70304701983955fae5934b21';

#将device表的所有delFlag字段都置为1，不带条件
UPDATE device SET delFlag=1;

6.执行整个库的完全性检查，会查看错序的记录、丢失的页，毁坏的索引等

PRAGMA integrity_check;

7.修改表名称，重命名表。ALTER TABLE 命令不通过执行一个完整的转储和数据的重载来修改已有的表

ALTER TABLE [旧表名] RENAME TO [新表名];

#将device数据库表重命名为new_device
ALTER TABLE device RENAME TO new_device;

8.数据库表增加字段，在已有的表中添加额外的列

ALTER TABLE [表名]ADD COLUMN [列名] [数据类型] [值];

#device表增加一列为time,字符类型，默认值为20220914
ALTER TABLE device ADD COLUMN time TEXT DEFAULT '20220914';

9.查看表结构

PRAGMA table_info ([表名称]);

#查看new_device表的结构
PRAGMA table_info (new_device);

10.Group by详解：根据by对数据按照哪个字段进行分组，或者是哪几个字段进行分组

select 字段 from 表名 where 条件 group by 字段

或者

select 字段 from 表名 group by 字段 having 过滤条件

或者

SELECT 字段 FROM 表名 WHERE 条件 GROUP BY 字段 ORDER BY 字段

注意：对于过滤条件，可以先用where，再用group by或者是先用group by，再用having；
GROUP BY 子句必须放在 WHERE 子句中的条件之后，必须放在 ORDER BY 子句之前

例：

查出Device表中所有deviceName:

sql: SELECT deviceName FROM Device;

查出Device表中所有deviceName,根据deviceName进行分组，去除重复的

sql: SELECT deviceName FROM Device GROUP BY deviceName;

查询表中某个字段是否重复

sql:SELECT [字段名] FROM [表名] group by [字段名] having count(*) > 1;

查出Device表中所有deviceName,根据deviceName进行分组,名称匹配为色温灯

sql:SELECT deviceName,max(createTime) FROM Device WHERE deviceName like "色温灯%%" GROUP BY deviceName;

配合聚合函数使用：count() , sum() , avg() , max() , min() 其中avg表示平均数

按deviceName分组，并统计数量

sql:SELECT deviceName,count(*) FROM Device GROUP BY deviceName;

按deviceName分组，寻找最大createTime 创建时间

SELECT deviceName,max(createTime) FROM Device GROUP BY deviceName;

11. Order by详解：用来基于一个或多个列按升序或降序顺序排列数据

#ASC 默认值，按从小到大，升序排列
select 字段 from 表名 where 条件 ORDER BY 字段 ASC;

#DESC 从大到小，降序排列
select 字段 from 表名 where 条件 ORDER BY 字段 DESC;

例：

从Device表查找deviceName，createTime，并按createTime升序排列

Sql: SELECT deviceName,createTime FROM Device ORDER BY createTime ASC;

从Device表查找deviceName，createTime，并按createTime降序排列

Sql: SELECT deviceName,createTime FROM Device ORDER BY createTime DESC;

12.INSERT INTO 详解：用于向表中插入新记录

#无需指定要插入数据的列名，只需提供被插入的值即可
INSERT INTO 表名 VALUES (value1,value2,value3,...);

#需要指定列名及被插入的值
INSERT INTO 表名(column1,column2,column3,...) VALUES (value1,value2,value3,...);