sqlite3、sqlite3命令以及sqlite3_open、sqlite3_exec、sqlite3_close数据库API的应用---+数据库经典词典项目

---

前言

在嵌入式领域中，数据库也是必备的技能之一，本期主要分享的是sqlite3、sqlite3命令以及aqlite3数据库API应用，那么就让我们认识一下数据库以及数据库的使用吧！

---

一、数据库

数据库就是用来高效的管理数据的一种工具，能够实现对数据的增删改查，主要应用场景有：
(1)大批量数据管理
(2)提高查找数据效率

二、数据库的分类

1、关系型数据库
键值:在数据库中唯一能够找到一条数据标志(不允许重复)，是一对一的；下面列举一些常见的数据库：

		Oracle、DB     		大型数据库
		MySql、SqlServer	中型数据库
		Sqlite				小型数据库
		
		Sqlite特点:
					可以实现大数据量的管理
					读写速度慢
1
2
3
4
5
6
7

2、非关系型数据库

		Redis				内存数据库
		NoSql							
		特点:
				读写速度快(1s实现读写上万次)
1
2
3
4

三、sqlite3的基本命令以及基本使用

3.1 数据库文件基本结构:

	(1)文件名
		filename.db 
	(2)表
		一系列数据的集合
		格式:分为不同的列
1
2
3
4
5

3.2 sqlite3命令:

	打开数据库文件   sqlite3 filename.db 
	(1).headers on|off
	   打开/关闭表字段的显示
	   
	(2).mode + colunm
	   设置显示格式

	(3).quit 
	   退出
	   
	(4).read  filename.sql 
	   加载filename.sql中的语句
	   
	(5).tables 
	   查看文件中所有的表
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

3.3 SQL语句

所有的数据库中均支持SQL语句

	(1)create table 
	   CREATE TABLE table_name(
		   column1 datatype  PRIMARY KEY,
		   column2 datatype,
		   column3 datatype,
		   .....
		   columnN datatype,
		);
	
	比如创建一个表：create table info (学号 interger primary key, 姓名 char (255), 性别 char(32), 年龄 interger);
	创建了一张名字为info的新表；
	(2)insert into 
	   INSERT INTO TABLE_NAME VALUES (value1, value2, value3,...valueN);
	
	比如向表中添加成员：insert into info values (1001, 'zhangsan', 'm', 12);
	(3)select
	   SELECT column1, column2, columnN FROM table_name;
	比如查看表内的所有信息：
	select * from info；
	(4)where
	作用：查找的时候进行筛选
	比如： select * from info where 学号 = 1001; 在info这张表中查找学号等于1001的这个学生；	   
	(5)delete from 
	   DELETE FROM table_name WHERE [condition];
	   作用：删除信息
	   比如：delete from info where  学号 = 1001；删除info表中学号等于1001的这个学生的所有信息；

	(6)update 
	   UPDATE table_name 
	   SET column1 = value1, column2 = value2...., columnN = valueN
	   WHERE [condition];
	   作用：修改某个信息
	   比如：update info set 学号 = 1001 where 学号 = 1003；把学号为1003的学生的学号修改为1001；
		
	(7)order
		SELECT column-list 
		FROM table_name 
		[WHERE condition] 
		[ORDER BY column1, column2, .. columnN] [ASC | DESC];

	(8)drop table 
		作用：删除表
		比如：drop table info; 删除名字为info的这张表；
		
多表联合查询
	(1)CROSS JOIN	交叉连接
		例子：把info和lesson两张表一起查找，把info的姓名修改为名字，课程名修改为科目；（ab两张表各部分做拼接）
		sqlite> select info.姓名 as 名字, lesson.课程名 as 科目
		   ...> from info cross join lesson;		
	
	(2)INNER JOIN  内连接（左边有的右边有的都显示，左边有的右边没有的也显示，但是右边的部分是空的）
		SELECT ... FROM table1 [INNER] JOIN table2 ON conditional_expression ...		
		例子：在info和score两张表的相同部分中进行查找
		sqlite> select info.姓名 as 名字, score.成绩 as 分数
		   ...> from info inner join score on info.学号 = score.学号;				

	
	(3)OUTER JOIN 左连接（以左边为基准）
		sqlite> select info.姓名,score.成绩 
		   ...> from info left outer join score on info.学号 = score.学号;

	(4)三表联合查询
		sqlite> select info.姓名, lesson.课程名, score.成绩
		   ...> from info left outer join score on info.学号 = score.学号
		   ...> left outer join lesson on score.课程号 = lesson.课程号
		   ...> order by 成绩 desc;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66

四、数据库API的基本了解和应用

4.1 函数接口

(1)sqlite3_open
       int sqlite3_open(
		  const char *filename,   /* Database filename (UTF-8) */
		  sqlite3 **ppDb          /* OUT: SQLite db handle */
		);
	 功能:打开数据库文件并返回一个句柄
	 参数:
				filename:数据库文件路径
				ppDb:存放句柄空间的首地址
	 返回值:
				成功返回SQLITE_OK
				失败返回错误码
1
2
3
4
5
6
7
8
9
10
11
12

---

(2)sqlite3_exec 
	   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 */
		);
	  功能:
			加载一条SQL语句
	  参数:
			sqlite3*:数据库文件句柄 
			sql:SQL语句字符串空间首地址
			callback:回调函数(select时使用，这个函数就是查到之后做什么，做的就是callback这个函数)
			void*:给回调函数传参(select时使用)
			errmsg:存储出错信息空间的首地址
	  返回值:
			成功返回SQLITE_OK
			失败返回错误码
注意：
(1)sql的命令当编程创建数据库文件时时可能会重复创建，所以在前面一般加上if not exists(例："create table if not exists info (学号 interger primary key, 姓名 char(255), 性别 char(32), 年龄 interger);")
(2)每找到一次符合的数据会调用一次callback；
(2) int (*callback)(void *arg,int n,char **pcontent ,char **ptitle)；
	该函数返回非0时会导致sqlite3_exec ();函数出错；
	arg：sqlite3_exec中的第四个参数，可用于主函数传参
	n：找到数据的列数
	pcontent：指向内容每一列字符串空间首地址的指针数组（指向第一条数据的第一行的第一个元素）
	ptitle：指向标题每一列字符串空间首地址的指针数组（姓名性别年龄等）
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

---

(3)sqlite3_close
	   int sqlite3_close(sqlite3*);
	   功能：
			关闭数据库句柄
1
2
3
4

---

(4)const char *sqlite3_errmsg(sqlite3*);
	功能：获得sqlite3错误原因
1
2

---

(5)void sqlite3_free(void*);
	功能:释放申请的空间(出错时,出错信息打印完毕后,需要释放)
1
2

4.2 词典项目

本次分享的这个小练习虽然听上去比较简单，但是用到的技术点也是比较多的，那么带大家仔细来看一下；
首先呢还是头文件部分啦：

#ifndef __HEAD_H__
#define __HEAD_H__

#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <sys/types.h>
#include <sys/stat.h>
#include <fcntl.h>
#include <unistd.h>
#include "sqlite3.h"

#endif
1
2
3
4
5
6
7
8
9
10
11
12
13

接下来看一下主程序部分：
主程序实现的功能是循环接收用户需要查找的单词，进而在数据库中查找单词，当用户输入.quit时APP结束；

int main(int argc, const char *argv[])
{

	char word[32] = {0};
	char mean[1024] = {0};
	int ret = 0;
	
//	writeToSql();		//此函数只运行一次，因为我们只需要把所有单词存入数据库一次即可，后续只进行查找即可

	while (1)
	{
		fgets(word, sizeof(word), stdin);	//用户输入单词
		word[strlen(word) - 1] = 0;
		if (!strcmp(word, ".quit"))			//用户输入.quit时退出
		{
			break;
		}
		
		searchWord(word, mean);				//在数据库中查找单词
		if (0 == flag)
		{
			printf("not found!\n");
		}
		if (1 == flag)
		{
			printf("===============================\n");
			printf("word:%s\nmean:%s\n", word, mean);
		}
	}
	return 0;
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

下面来看一下单词和含义是如何写入数据库的：

int writeToSql(void)
{
	FILE *fp = NULL;
	char tmpbuff[1024] = {0};
	char *ptmp = NULL;
	sqlite3 *pdb = NULL;
	int ret = 0;
	char word[32] = {0};
	char mean[1024] = {0};
	char cmdbuf[1024] = {0};

	fp = fopen("./dictionary.txt", "r");		//打开存放单词和含义的文本文件
	if (NULL == fp)
	{
		perror("fail to fp");
		return -1;
	}

	ret = sqlite3_open("./dictionary.db", &pdb);	//打开数据库
	if (SQLITE_OK != ret)
	{
		fprintf(stderr, "%s", sqlite3_errmsg(pdb));
		sqlite3_free(pdb);
		return -1;
	}

	sprintf(cmdbuf, "create table if not exists info (编号 integer primary key, 单词 char(32), 含义 char(1024));");//这里注意一定设置一个主键值(哪怕不用),防止因为单词重复出现的诸多问题
	ret = sqlite3_exec(pdb, cmdbuf, NULL, NULL, NULL);	//创建表
	if (SQLITE_OK != ret)
	{
		fprintf(stderr, "%s", sqlite3_errmsg(pdb));
		sqlite3_free(pdb);
		sqlite3_close(pdb);
		return -1;
	}


	while (1)
	{
		memset(word, 0, sizeof(word));
		memset(mean, 0, sizeof(mean));
		memset(tmpbuff, 0, sizeof(tmpbuff));
		ptmp = fgets(tmpbuff, sizeof(tmpbuff), fp);	//从文本中读取一行数（也就是读取一个单词以及它的含义）
		if (NULL == ptmp)
		{
			break;
		}
		strcpy(word, strtok(tmpbuff, " "));		//按照空格进行分割
		ptmp = strtok(NULL, "\n");				//剩下的按照\n进行分割
		while (*ptmp == ' ')					//分割后的前面也是空格，所以把前面的空格必须去掉
		{
			++ptmp;
		}
		strcpy(mean, ptmp);						//获得含义
//		printf("word:%s\nmean:%s\n", word, mean);
		memset(cmdbuf, 0, sizeof(cmdbuf));
		sprintf(cmdbuf, "insert into info values(NULL, \"%s\", \"%s\");", word, mean);	//向数据库中插入单词和含义，一定注意转义字符的表示（也要注意上面创建表格的注意事项）
		ret = sqlite3_exec(pdb, cmdbuf, NULL, NULL, NULL);
		if (SQLITE_OK != ret)
		{
			fprintf(stderr, "%s", sqlite3_errmsg(pdb));
			sqlite3_free(pdb);
			sqlite3_close(pdb);
			return -1;
		}
	
	}

	fclose(fp);
	sqlite3_close(pdb);
	return 0;
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72

最后呢我们来看一下如何实现查找功能：
注意：这里呢我用了一个flag，每次循环开始置为0，只要找到这个单词那么就使其为1；

int flag = 0;
int callback(void *arg, int n, char **pcontent, char **ptitle)
{
	flag = 1;
	char *ptmp = arg;
	strcpy(ptmp, pcontent[2]);

	return 0;
}

int searchWord(const char *word, char *mean)
{
	sqlite3 *pdb = NULL;
	char cmdbuf[1024] = {0};
	int ret = 0;

	ret = sqlite3_open("./dictionary.db", &pdb);		//打开数据库文件
	if (SQLITE_OK != ret)
	{
		fprintf(stderr, "%s", sqlite3_errmsg(pdb));
		sqlite3_free(pdb);
		return -1;
	}

	memset(cmdbuf, 0, sizeof(cmdbuf));
	sprintf(cmdbuf, "select * from info where 单词 = \"%s\";", word);
	ret = sqlite3_exec(pdb, cmdbuf, callback, mean, NULL);		//查找单词
	if (SQLITE_OK != ret)
	{
		fprintf(stderr, "%s", sqlite3_errmsg(pdb));
		sqlite3_free(pdb);
		return -1;
	}

	sqlite3_close(pdb);						//关闭数据库
	return 0;
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

上述就是一个简单的电子小词典的功能，那么这个小项目就使得我们对数据库的API接口有了进一步的认识，如果大家感兴趣可以尝试使用TCP网络编程实现一个在线词典的功能，并且加上多任务并发，可以尝试做一下哦，我后期也会出这个小项目，敬请期待吧，各位小伙伴们！

五、运行结果

来展示一下词典的运行结果吧：

总结

1.一定不要觉得自己理解了，看会了，就觉得简单不去动手做，这样会大大降低对知识点的巩固，可能根本对这个知识点没有自己想象中的那么了解，必须通过实际操作，动手才能知道自己的问题在哪里；
2.本期分享主要还是对于sqlite3、sqlite3命令以及sqlite3_open、sqlite3_exec、sqlite3_close数据库API的应用，希望各位小伙伴们动起手来，一起加油进步！！！
最后，各位小伙伴们如果喜欢我的分享可以点赞收藏哦，你们的认可是我创作的动力，一起加油！