嵌入式学习第二十八天！（数据库）

1. 关系型数据库：Mysql、Oracle、SqlServer、2. 非关系型数据库：Redis、NoSQL。

weixin_52799506

1323人浏览 · 2024-03-12 22:41:09

weixin_52799506 · 2024-03-12 22:41:09 发布

数据库：

1. 数据库软件：

1. 关系型数据库：Mysql、Oracle、SqlServer、Sqlite

2. 非关系型数据库：Redis、NoSQL

2. 数组、链表、文件、数据库

数组、链表：内存存放数据的方式（代码运行结束、关机数据丢失）

文件、数据库：外存存放数据的方式（代码运行结束、关机数据不会丢失）

文件：数据量很小，处理效率很低

数据库：数据量很大，处理数据（增删改查）效率高

3. 安装Sqlite数据库：

1. 让虚拟机能够哦上网

2. apt-get工具集配置好

3. sudo apt-get install sqlite3

4. 启动sqlite3

sqlite3

5. 输入.quit退出

4. SQL命令：

1. 打开/关闭数据库中列名称

.headers on/off

2. 设置输出模式，MODE可以是csv逗号分隔的值、column左对齐的列等

.mode csv/column/html/insert/line/list/tabs/tcl

3. 查看表名对应的表的形式（有哪几列？每一列类型？）

.schema 表名称

4. 设置显示时的数据宽带

.width 宽度

5. 查看数据库文件中的所有表

.tables

这里只展示了几个常用的命令，其他命令可以查看SQLite参考手册中的SQLite命令

5. SQL语言：

数据的：增、删、改、查

1. 使用sqlite3打开数据库文件

sqlite3 数据库文件名
eg: 
    sqlite3 ./student.db

2. 创建/删除表：

create table 表名
eg:
    sqlite> create table student (id integer primary key asc, name varchar(255), sex varchar(32), age integer, score integer);

drop table 表名
eg:
    sqlite> drop table student

3. 插入数据：

insert into
eg:
    sqlite> insert into student values (1001, "张三", "男", 19, 80);
    sqlite> insert into student (id, name, score) values (1002, "李四", 100);
    sqlite> insert into student values (1002, "王二", "女", 18, NULL);

4. 查找数据：

select
eg:
    sqlite> select * from student;
    sqlite> select name, score from student;

5. 内容匹配：

where
eg:
    sqlite> select * from student where name like "%张%";

练习:数据库完成对订单的管理:

1. 创建一张订单表,由 ID, 订单号, 货物信息, 生产厂商, 联系人, 联系电话, 订单时间

sqlite> create table older_manage
   ...> (ID integer,
   ...> ordername varchar(32),
   ...> orderinfo varchar(50),
   ...> factoryname varchar(50),
   ...> contactperson varchar(32),
   ...> contactphone varchar(32),
   ...> oldertime varchar(32));

2. 在表中插入随机给定的10条数据

sqlite> insert into older_manage values(1, "D202403120001", "Mate60 手机", "华为", "张三", "19829147641", "20240312 22:04:00");
sqlite> insert into older_manage values(2, "D202403120002", "Mate 平板", "华为", "张三", "13819724571", "20240312 18:34:10");
sqlite> insert into older_manage values(3, "D202403120003", "GTWatch 手表", "华为", "张三", "13947231554", "20240312 10:14:15");
sqlite> insert into older_manage values(4, "D202403120004", "Iphone14 手机", "苹果", "李四", "13145721214", "20240311 12:31:42");
sqlite> insert into older_manage values(5, "D202403120005", "Ipad 平板", "苹果", "李四", "13147561243", "20240311 13:13:12");
sqlite> insert into older_manage values(6, "D202403120006", "Iwatch 手表", "苹果", "李四", "13147561478", "20240310 11:13:12");
...

3. 查找联系人为 "张三" 的所有订单号

select ordername from older_manage where contactperson="张三";

查找订单号为 "D202403120001" 的所有订单信息

select * from older_manage where oldername="D202403120001"；

查找订单时间截止到当天的所有订单号及联系人、联系电话

select oldername,contactperson,contactphone from older_manage where oldertime<"20240311 24:00:00";

查找货物信息包含 "手机" 内容的所有订单信息及货物信息

select oldername,olderinfo from older_manage where olderinfo like "%手机%";

查找生产厂商是"华为" 的所有货物信息包含 "平板" 的订单信息

select * from order_manage where factoryname="华为" and orderinfo like "%d平板%";

6. 主键：

key：键值

內键：在表中唯一标识一条数据称为內键

外键：与外部表进行标识的数据称为外键

7. 删除：

delete from
eg:
    delete from student where name="李四";

8. 修改：

update
eg:
    update student set sex="男", age="19" where name "王二";

9. 排序：

older by
eg:
    select * from order_manage order by ordertime desc;

10. 多表联合查询：

1. 交叉连接：

cross join
eg:
    sqlite> select student.name as 学生姓名, lesson.name as 课程名
       ...> from score cross join lesson;

2. 内连接：

inner join
eg:
    sqlite> select score.id as 成绩编号, student.name as 学生姓名, score.score as 成绩        
       ...> from score inner join student on score.stuid=student.id;

3. 外连接：

outer join
eg:
    sqlite> select score.id as 成绩编号, student.name as 学生姓名, score.score as 成绩
       ...> from student left outer join score on score.stuid=stdent.id;

示例：

sqlite> select score.id as 成绩编号, student.name as 学生姓名, score.score as 成绩
   ...> from student left outer join score on score.stuid=student.id
   ...> where name="张三"
   ...> order by 成绩 desc;

6. 在程序中使用Sqlite3

可以寻找Sqlite3官网SQLite Home Page，主页选择Documentation，再选择Programming Interfaces中C/C++ API Reference去寻找Sqlite3数据库使用的函数接口，在调用时需要加上<sqlite3.h>，在这里讲解一些常用的函数接口：

1. sqlite3_open：

int sqlite3_open(
        const char *filename,   /* Database filename (UTF-8) */
        sqlite3 **ppDb          /* OUT: SQLite db handle */
);

功能：打开数据库文件（创建一个数据库连接）

参数：

failname：数据库文件路径

ppDb：操作数据库指针存放空间的首地址

返回值：

成功返回SQLITE_OK
失败返回错误码

2. sqlite3_close：

int sqlite3_close(sqlite3*);

功能：关闭数据库连接

3. 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时才会使用，其余调用时传递NULL

void*：给回调函数的传参

errmsg：出错信息存放空间首地址（使用sqlite3_free释放空间）

返回值：

成功返回SQLITE_OK
失败返回错误码

练习：

1. 从终端输入一个单词，在文件中查找单词含义并打印在终端：

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

int main(void)
{
	FILE * fd = NULL;
	char str[64] = {0};
	char tmpbuff[1024] = {0};
	char *pread = NULL;
	char *pstr = NULL;

	
	fd = fopen("dict.txt", "r");
	if(fd == NULL)
	{
		perror("fail to fopen");
		return -1;
	}

	while(1)
	{
		gets(str);
		
		if(!strcmp(str, ".quit"))
		{
			break;
		}

		while(1)
		{
			pread = fgets(tmpbuff, sizeof(tmpbuff), fd);
			if(pread == NULL)
			{
				perror("fail to fgets");
				rewind(fd);
				break;
			}
			
			pstr = pread;

			while(*pread != ' ' && *pread != '\0')
			{
				pread++;
			}
			*pread = '\0';
			pread++;

			if(!strcmp(str, pstr))
			{
				while(*pread == ' ')
				{
					pread++;
				}
				printf("%s\n", pread);
				rewind(fd);
				break;
			}
		}
	}

	fclose(fd);
	return 0;
}

2. 实现将dict.txt文件中的所有单词和含义插入到数据库中，并实现单词的查找

#include "head.h"
#include <sqlite3.h>

int LoadDictToDB(void)
{
	int ret = 0;
	int eret = 0;
	FILE *fp = NULL;
	char *pstr = NULL;
	char *pword = NULL;
	char *pmean = NULL;
	char tmpbuff[4096] = {0};
	char cmpbuff[1024] = {0};
	sqlite3 *pdb;
	char *perrmsg;


	fp = fopen("dict.txt", "r");
	if(fp == NULL)
	{
		perror("fail to fopen");
		return -1;
	}
	
	ret = sqlite3_open("dictionary.db", &pdb);
	if(ret != SQLITE_OK)
	{
		fprintf(stderr, "sqlite3_open failed:%s\n", sqlite3_errmsg(pdb));
		return -1;
	}
	
	sprintf(cmpbuff, "create table if not exists dict (id integer primary key asc, word varchar(50), mean varchar(1024));");
	eret = sqlite3_exec(pdb, cmpbuff, NULL, NULL, &perrmsg);
	if(eret !=SQLITE_OK)
	{
		fprintf(stderr, "sqlite3_exec failed:%s\n", perrmsg);
		sqlite3_free(perrmsg);
		sqlite3_close(pdb);
		return -1;
	}

	while(1)
	{
		memset(tmpbuff, 0, sizeof(tmpbuff));
		pword = fgets(tmpbuff, sizeof(tmpbuff), fp); 
		if(pword == NULL)
		{
			break;
		}
		
		pstr = tmpbuff;
		while(*pstr != ' ')
		{
			pstr++;
		}
		*pstr = '\0';
		pstr++;
		
		while(*pstr == ' ')
		{
			pstr++;
		}
		pmean = pstr;
		while(*pstr != '\r')
		{
			pstr++;
		}
		*pstr = '\0';
		
		memset(cmpbuff, 0, sizeof(cmpbuff));
		sprintf(cmpbuff, "insert into dict values(NULL, \"%s\", \"%s\");", pword, pmean);
		eret = sqlite3_exec(pdb, cmpbuff, NULL, NULL, &perrmsg);
		if(eret != SQLITE_OK)
		{
			printf("%s\n", cmpbuff);
			fprintf(stderr, "sqlite3_exec failed:%s\n", perrmsg);
			sqlite3_free(perrmsg);
			sqlite3_close(pdb);
			return -1;
		}
	}
	
	fclose(fp);
	sqlite3_close(pdb);
	return 0;

}

int callback(void *arg, int col, char **pcontent, char **ptitle)
{

	printf("%s:  %s\n", ptitle[1], pcontent[2] );

	return 0;
}

int main(void)
{
	sqlite3 *pdb;
	int ret = 0;
	char str[256] = {0};
	char cmpbuff[1024] = {0};
	char *perrmsg = NULL;

	if(access("dictionary.db", F_OK))
	{
		LoadDictToDB();
	}
	
	gets(str);

	ret = sqlite3_open("dictionary.db", &pdb);
	if(ret != SQLITE_OK)
	{
		fprintf(stderr, "sqlite3_open failed:%s\n", sqlite3_errmsg(pdb));
		return -1;
	}

	sprintf(cmpbuff, "select * from dict where word = \"%s\";", str);
	ret = sqlite3_exec(pdb, cmpbuff, callback, NULL, &perrmsg);
	if(ret != SQLITE_OK)
	{
		fprintf(stderr, "sqlite3_exec failed:%s\n", perrmsg);
		sqlite3_free(perrmsg);
		sqlite3_close(pdb);
		return -1;
	}
	
	sqlite3_close(pdb);
	return 0;

}

3. 1037 在霍格沃茨找零钱 - PAT (Basic Level) Practice （中文） (pintia.cn)

#include <stdio.h>

struct money
{
	int Galleon;
	int Sickle;
	int Knut;
};

void IntMoneyInfo(struct money *pay)
{
	scanf("%d.%d.%d", &pay->Galleon, &pay->Sickle, &pay->Knut);
	return;
}

void CountMoney(struct money pmax, struct money pmin, int *galleon, int *sickle, int *knut)
{
	
	*knut = pmax.Knut - pmin.Knut;
	if(*knut < 0)
	{
		pmax.Sickle -= 1;
		*knut = (pmax.Knut + 29) - pmin.Knut;
	}

	*sickle = pmax.Sickle - pmin.Sickle;
	if(*sickle < 0)
	{
		pmax.Galleon -= 1;
		*sickle = (pmax.Sickle + 17) - pmin.Sickle;
	}

	*galleon = pmax.Galleon - pmin.Galleon;
	
	return;
}

int main(void)
{
	int galleon = 0;
	int sickle = 0;
	int knut = 0;

	struct money accountspay;
	struct money actualpay;
	
	IntMoneyInfo(&accountspay);
	IntMoneyInfo(&actualpay);

	if(actualpay.Galleon > accountspay.Galleon)
	{
		CountMoney(actualpay, accountspay, &galleon, &sickle, &knut);
		printf("%d.%d.%d\n", galleon, sickle, knut);
	}
	else
	{
		CountMoney(accountspay, actualpay, &galleon, &sickle, &knut);
		printf("-%d.%d.%d\n", galleon, sickle, knut);
	}

	return 0;
}