【SQLite数据库】—— 概念及基本操作
本文摘要：本文系统介绍了数据库基础知识与应用操作。主要内容包括：1）数据库分类与核心概念，重点解析了SQLite嵌入式数据库的特点；2）SQLite常用指令与基本操作，涵盖表创建、增删改查、排序等语法；3）数据库维护命令（导入导出）与可视化工具；4）C语言操作SQLite的编程接口，包括打开/关闭数据库和执行SQL语句的函数使用示例。全文以SQLite为例，详细说明了轻量级数据库的实际应用方法，适
本文系统介绍了数据库基础知识与应用操作。主要内容包括：1）数据库分类与核心概念，重点解析了SQLite嵌入式数据库的特点；2）SQLite常用指令与基本操作，涵盖表创建、增删改查、排序等语法；3）数据库维护命令（导入导出）与可视化工具；4）C语言操作SQLite的编程接口，包括打开/关闭数据库和执行SQL语句的函数使用示例。全文以SQLite为例，详细说明了轻量级数据库的实际应用方法，适合开发者快速掌握数据库基础操作。
一、数据库
数据库是结构化的数据集合，由数据库管理系统统一存储、管理，数据以表、行、列的形式组织。
1.分类
| 分类 | 代表数据库 |
|---|---|
| 大型数据库 | ORACLE |
| 中型数据库 | MYSQL、MSSQL（SQL Server） |
| 小型数据库 | SQLITE、DBII、powdb |
2.核心名词解释
| 缩写 | 英文全称 | 中文含义 | 补充说明 |
|---|---|---|---|
| DB | Database | 数据库 | 存储数据的仓库，是数据的集合 |
| DBMS | Database Management System | 数据库管理系统 | 管理数据库的软件（如 MySQL、Oracle、SQLite） |
| MIS | Management Information System | 管理信息系统 | 利用数据库进行业务管理的系统（如企业 ERP、学生管理系统） |
| OA | Office Automation | 办公自动化 | 用于日常办公流程管理的系统（如审批、文档管理） |
| SQL | Structured Query Language | 结构化查询语言 | 操作关系型数据库的标准语言，核心操作：SELECT（查询）、UPDATE（更新）等 |
3.嵌入式数据库 SQLlite
1）核心特点：
- 开源 & 开发语言：完全开源，使用 C 语言 开发，性能高效且可移植性强。
- 轻量级：核心代码约 1 万行，总大小在 10MB 以内，非常小巧。
- 无需安装：是绿色软件，解压即可使用，不依赖系统环境。
- 文件型数据库：整个数据库就是一个独立文件，可直接复制、移动，部署极其方便。
- 容量上限：单数据库文件最大可支持 2TB 的数据存储。
2）与其他数据库对比：
| 特性 | SQLite | MySQL | Oracle |
|---|---|---|---|
| 部署方式 | 单文件，无需服务 | 需要独立服务进程 | 企业级服务集群 |
| 并发能力 | 适合低并发读写 | 高并发，支持多用户 | 极高并发，企业级 |
| 主要场景 | 嵌入式、本地存储 | 中小型网站、Web 应用 | 大型企业核心系统 |
| 学习难度 | 极低，上手快 | 中等 | 高，体系复杂 |
二、数据库的指令
| 指令格式 | 功能说明 |
|---|---|
.database |
列出当前数据库及关联的系统文件 |
.tables |
列出当前数据库中的所有表 |
.schema 表名 |
查看指定表的结构（建表语句） |
.headers on |
开启查询结果的列名显示（让输出更易读） |
.exit / .quit / .q |
退出 SQLite 控制台（三种写法效果相同） |
三、数据库的基本操作
操作台用：“sqlite3 文件名.db”唤出；所有的sql语句必须以分号结尾；SQLite 不区分大小写
1.创建一个表
create table 表名（表字段1 类型， 表字段2 类型， ...);
eg: create teable user(id int，name char, age int);
注：以上表的表字段，支持以下数据类型：
数据类型 说明 适用场景 示例写法 INT 整数类型（自动适配大小） 年龄、ID、数量、序号 id INTREAL 双精度浮点数（小数） 身高、体重、温度、普通小数 height REALDECIMAL 精确小数（可指定精度） 金额、价格、财务数据 price DECIMAL(10,2)TEXT 字符型 存储长字符串、非英文字符串 mean TEXTBOOLEAN 布尔值（存 1/0） 状态、开关、是否启用 is_vip BOOLEAN
2.删除一个表
drop table 表名；
eg: drop table user;
3.向表中增加数据
insert into 表名（字段名称） values (值名称)；
eg:insert into user (age) values (12)； //指定表字段增加数据
intsert into use values (12,"wang",11); //整体表字段增加数据，字符串用单引号或者双引号括起来
4.查询一个表
select 列名 from 表名 条件；
eg:select * from user; //查看所有表数据
select id from user; //查看id字段的数据
select id,name from user where not age < 30; //在where后加筛选条件，数字可以直接加条件
//字符串有格式要求：where like '张三' 等效于 where = ’张三‘;
//字符串还可以模糊查找：where like '张%'；（通配符%，表示张后面可跟任意多字符；通配符_表示一个任意字符）。
5.对表数据排序
select * from 表名 by 表字段； //默认升序排列，在表字段后加desc为降序排列
eg:select * from user order by id;
select * from user oder by id desc;
select * from user limit 3;//将表id限制在3以下
6.更新表数据
update 表名 set 表字段 = 新数据 where 表字段 = 数据；
eg:update user set id = 3 where age = 23;//将age=23的那一行的id列数据改为3
7.删除表中数据
delete from 表名 满足条件；
eg:delete from user; //删除表中的所有数据
delete from user where id = 1; //删除表中id = 1的数据
delete from user where id =1 and name = "zhang"; //删除表中id = 1 且 name = 'zhang'的数据
delete from user where id = 1 or id = 2; //删除 user 表中，id 等于 1 或者 id 等于 2 的所
有数据
8.插入时间列
CREATE TABLE user1(id int, name char, age int, dt datetime);
insert into user1 values (2, '张三', 23, datetime('now', '+8 hours'));
datetime()函数,datetime('now')：获取当前的 UTC 时间（世界协调时间），比北京时间晚 8 小时。datetime('now', '+8 hours')：在 UTC 时间基础上增加 8 小时，得到北京时间，这是在国内开发时最常用的写法,8 和 hours之间必须要空格！
9.自动增长列
CREATE TABLE user3(id INTEGER PRIMARY KEY ASC,name char,age int,dt datetime);
insert into user3 values (NULL,'李四',23,datetime('now'));
- PRIMARY KEY 主键是表中能唯一标识一条记录的字段。主键的值不可重复，也不能为空，保证每条数据唯一可识别。主键用于精准定位数据，方便查询、修改、删除。主键查询比普通字段快很多，因为主键默认自带索引，像目录一样直接定位，比普通字段查询效率高很多。
- 主键 id 设为 INTEGER PRIMARY KEY 时，插入写 NULL 就是让数据库自动生成自增编号，不用自己管。
四、数据库的维护命令
1.数据的导出：
sqlite3 xxx.db .dump > xxx.sql
//将数据库名称为xxx的数据库整体导出到脚本中，可以直接通过vim指令查看。导出的数据库不会实时同步数据库的修改，在数据库改动后要再次导出才能看得见改变
2.数据的导入：
sqlite3 xxx.db < xxx.sql
注意：数据库的导出和输入是在系统终端命令使用的
3.可视化工具安装：
sudo apt-get install sqlitebrowser
五、相关函数
数据库编程步骤：打开数据库、读写数据库（增、删、改、查），关闭数据库
1. 打开数据库：
sqlite3_open (char * path,sqlite3 ** db);
功能：打开指定 path 路径 + 文件名称的数据库，并将打开的地址指向 db 变量的句柄。
参数：path 要打开的数据库路径 + 名称
db 用于接收数据库句柄的二级指针
返回值：成功 0；失败 非 0；
2. 关闭数据库：
sqlite3_close (sqlite3 * db);
功能：关闭指定的数据库，释放资源。
参数：db 已经打开的数据库句柄
返回值：成功 0；失败 非 0；
3. 执行 sql 语句：
sqlite3_exec (sqlite3 *db,char *sql,callback fun,void * arg,char **errmsg);
功能：在 db 数据库上执行 sql 语句，并将结果返回。
参数：db 要执行 sql 的数据库句柄sql 要执行的 sql 语句
fun 查询操作的回调函数，用于接收结果
arg 传递给回调函数的参数，无回调则填 NULL
errmsg 用于接收执行过程中的错误信息
返回值：执行成功 0；失败 非 0；
示例：
#include <sqlite3.h>
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
int main(int argc, char **argv)
{
//定义SQLite数据库句柄指针(相当于文件描述符),初始化为NULL
sqlite3 *db = NULL;
// 1.打开数据库,获得数据的句柄
int ret = sqlite3_open("aaa.db", &db);
//判断是否打开成功,SQLITE_OK等于0,sqlite3_open成功返回0
if (SQLITE_OK != ret)
{
fprintf(stderr, "open db error %s\n", sqlite3_errstr(ret));
sqlite3_close(db);
return 1;
}
//定义错误信息指针,用于接收sqlite3_exec执行失败的错误描述
char *errmsg = NULL;
//存储需要执行的指令
char sql_cmd[512] = "insert into test values(7,'shuaige')";
// 2.执行SQL语句,向指定表中插入一条数据
ret = sqlite3_exec(db, sql_cmd, NULL, NULL, &errmsg);
if (SQLITE_OK != ret)
{
fprintf(stderr, "sqlite3_exec sql_cmd:[%s], %s\n", sql_cmd, errmsg);
sqlite3_free(errmsg);
sqlite3_close(db);
return 1;
}
// 3.关闭数据,释放资源
sqlite3_close(db);
return 0;
}
注意：
1.插入数据前，要清除数据字符串里的换行符，或者会格式错乱
2.要将单词和释义中的单引号修改为双引号因为单引号是SQL的边界符号，防止程序误判
3.执行sqlite3_exec（)数据库会：写入数据、刷新到磁盘、确认写入成功、继续下一条。因此如果放在while（1）循环中程序最多插入5000多条，很容易被卡死
4.在循环中反复调用 `sqlite3_exec` 执行单条 `INSERT` 语句时，SQLite 默认会为每条插入操作自动开启并提交一次独立事务。每次事务提交都会触发磁盘同步I/O、数据持久化刷盘、日志写入等操作，导致大量磁盘随机I/O与系统开销。 随着插入数据量增大，磁盘I/O性能瓶颈凸显，程序出现严重卡顿、吞吐量急剧下降，通常仅能稳定插入数千条数据便无法继续执行。
解决方案1：显式事务控制： 在批量插入循环前执行 `BEGIN TRANSACTION` 开启显式事务，所有插入操作在内存中完成；循环后执行 `COMMIT` 提交事务，将所有数据**一次性批量刷入磁盘。
解决方案2：关闭同步模式：执行 `PRAGMA synchronous = OFF;` 关闭数据库强制同步写盘机制。数据库不再等待数据完全写入磁盘，依赖操作系统缓存异步落盘，进一步降低I/O等待耗时，提升插入速度。
六、编程数据库的查询
当需要对数据库中的数据，进行查询的时候。
sqlite3_exec(）中的回调函数(第三个参数)就不能为空了。
通过回调函数反馈查询到结果。回调函数是用来返回结果。
如果结果集有5条记录，那么回调函数会调用5次。如果结果集有0条记录，那么回调函数会调用0次。show函数中，result 参数每次都会执行对应的记录。如果有多条记录，数据库会分多次把数据传递给result指针。
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sqlite3.h>
int flag = 0;
// arg sqlite3_exec 传递过来的参数
// col 结果集列数
// result查询到的结果
// title 标题
// 这个函数的的调用次数，由 结果集决定 。如果结果由10 条，函数就会调用10次。
// result 对指向对应一条记录。
int show(void *arg, int col, char **result, char **title)
{
int i = 0;
if (0 == flag) //只需要输出一次
{
flag = 1;
for (i = 0; i < col; i++)
{
printf("%s\t", title[i]);
}
printf("\n");
}
for (i = 0; i < col; i++)
{
printf("%s\t", result[i]);
}
printf("\n");
// 一定加上，否则查询语句只会运行一次 return 0
return 0;
}
int main(int argc, char **argv)
{
sqlite3 *db = NULL;
// 1 打开数据库，获得数据的句柄(相当于linux 中的文件描述符)
int ret = sqlite3_open("aaa.db", &db);
if (SQLITE_OK != ret)
{
fprintf(stderr, "open db error %s\n", sqlite3_errstr(ret));
sqlite3_close(db);
return 1;
}
char *errmsg = NULL;
char sql_cmd[512] = "select * from user;";
// 2 执行sql语句(对数据库的读写)
ret = sqlite3_exec(db, sql_cmd, show, NULL, &errmsg);
if (SQLITE_OK != ret)
{
fprintf(stderr, "sqlite3_exec sql_cmd:[%s] ,%s\n", sql_cmd, errmsg);
sqlite3_free(errmsg);
sqlite3_close(db);
return 1;
}
// 3. 关闭数据 释放资源
sqlite3_close(db);
return 0;
}
综合练习：将字典文件插入到数据库中，并实现 在数据库中查询目标单词 并反馈给终端
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sqlite3.h>
int find_word(void*arg,int col,char**result,char**title)
{
*(int*)arg = 1;
printf("%s:%s\n",result[1],result[2]);
return 0;
}
int main(int argc, char **argv)
{
sqlite3 *db = NULL;
// 1 打开数据库，获得数据的句柄(相当于linux 中的文件描述符)
int ret = sqlite3_open("aaa.db", &db);
if (SQLITE_OK != ret)
{
fprintf(stderr, "open db error %s\n", sqlite3_errstr(ret));
sqlite3_close(db);
return 1;
}
char sql_cmd[1024] = {0};
char *errmsg = NULL;
// 创建表
bzero(sql_cmd, sizeof(sql_cmd));
strcpy(sql_cmd,
"create table IF NOT EXISTS dict(id INTEGER PRIMARY KEY ASC,word "
"char,mean text);");
// 2 执行sql语句(对数据库的读写)
ret = sqlite3_exec(db, sql_cmd, NULL, NULL, &errmsg);
if (SQLITE_OK != ret)
{
fprintf(stderr, "sqlite3_exec sql_cmd:[%s] ,%s\n", sql_cmd, errmsg);
sqlite3_free(errmsg);
sqlite3_close(db);
return 1;
}
bzero(sql_cmd, sizeof(sql_cmd)); // 清空表
strcpy(sql_cmd, "delete from dict");
ret = sqlite3_exec(db, sql_cmd, NULL, NULL, &errmsg);
if (SQLITE_OK != ret)
{
fprintf(stderr, "sqlite3_exec sql_cmd:[%s] ,%s\n", sql_cmd, errmsg);
sqlite3_free(errmsg);
sqlite3_close(db);
return 1;
}
bzero(sql_cmd, sizeof(sql_cmd)); // 为了提高效率，批量操作 ，开启事务
strcpy(sql_cmd, "BEGIN TRANSACTION;");
ret = sqlite3_exec(db, sql_cmd, NULL, NULL, &errmsg);
if (SQLITE_OK != ret)
{
fprintf(stderr, "sqlite3_exec sql_cmd:[%s] ,%s\n", sql_cmd, errmsg);
sqlite3_free(errmsg);
sqlite3_close(db);
return 1;
}
FILE *fp = fopen("/home/linux/dict.txt", "r");
if (NULL == fp)
{
perror("fopen");
return 1;
}
int i = 1;
while (1)
{
char line_buf[1024] = {0};
if (NULL == fgets(line_buf, sizeof(line_buf), fp))
{
break;
}
char *word = strtok(line_buf, " ");
char *mean = strtok(NULL, "\r");
bzero(sql_cmd, sizeof(sql_cmd)); // 清空表
sprintf(sql_cmd, "insert into dict values(NULL,\"%s\",\"%s\");", word,
mean);
ret = sqlite3_exec(db, sql_cmd, NULL, NULL, &errmsg);
if (SQLITE_OK != ret)
{
fprintf(stderr, "sqlite3_exec sql_cmd:[%s] ,%s\n", sql_cmd,
errmsg);
sqlite3_free(errmsg);
sqlite3_close(db);
return 1;
}
// printf("num:%d\n", i++);
}
bzero(sql_cmd, sizeof(sql_cmd)); // 提交事务 ，相当于保存文件
strcpy(sql_cmd, "COMMIT;");
ret = sqlite3_exec(db, sql_cmd, NULL, NULL, &errmsg);
if (SQLITE_OK != ret)
{
fprintf(stderr, "sqlite3_exec sql_cmd:[%s] ,%s\n", sql_cmd, errmsg);
sqlite3_free(errmsg);
sqlite3_close(db);
return 1;
}
while (1)
{
printf("input word:");
char word[50] = {0};
fgets(word, sizeof(word), stdin);
word[strlen(word) - 1] = '\0';
if(0 == strcmp("#quit",word))
{
break;
}
int flag = 0 ;
bzero(sql_cmd, sizeof(sql_cmd)); // 清空表
sprintf(sql_cmd,"select * from dict where word like \"%s\";",word);
ret = sqlite3_exec(db, sql_cmd, find_word,&flag , &errmsg);
if (SQLITE_OK != ret)
{
fprintf(stderr, "sqlite3_exec sql_cmd:[%s] ,%s\n", sql_cmd,
errmsg);
sqlite3_free(errmsg);
sqlite3_close(db);
return 1;
}
if(0 == flag)
{
printf("can't find\n");
}
}
// 3. 关闭数据 释放资源
sqlite3_close(db);
return 0;
}
腾讯云面向开发者汇聚海量精品云计算使用和开发经验，营造开放的云计算技术生态圈。
更多推荐
21
0- 0
已为社区贡献1条内容
所有评论(0)