day 6

//用c语言实现对sqlite3数据库的插入删除修改和查找

//头文件
#ifndef __HEAD__
#define __HEAD__
#include <quick.h>
#define sqname "my.db"
#define tabname "stu"
void Sq_Insert(sqlite3 *db);
void Sq_Delete(sqlite3 *db);
void Sq_Update(sqlite3 *db);
void Sq_Search(sqlite3 *db);
#endif
//主函数
#include "head.h"
int main(int argc, const char *argv[])
{
	//打开数据库
	sqlite3 *db;
	if(sqlite3_open(sqname,&db)!=0)
	{
		fprintf(stdout,"sqlite3_open : [%d] : %s\n",sqlite3_errcode(db),sqlite3_errmsg(db));
		return -1;
	}
	printf("my.db open success\n");
	//变量
	char comnum = 0;
	//创建表格
	char *command = "create table if not exists stu(id int primary key,name char,score float);";
	char *errmsg;
	if(sqlite3_exec(db,command,NULL,NULL,&errmsg)!=0)
	{
		fprintf(stdout,"create table : %s\n",errmsg);
		return -1;
	}
	printf("table create success\n");
	//任务选择
	while(1)
	{
		printf("-----1 insert-----\n");
		printf("-----2 delete-----\n");
		printf("-----3 update-----\n");
		printf("-----4 search-----\n");
		printf("-----5  quit -----\n");
		//终端输入任务码
		printf("please enter command num : ");
		comnum = getchar();
		while(getchar()!=10);
		//选择入口
		switch(comnum)
		{
		case '1': //插入
			Sq_Insert(db);
			break;
		case '2': //删除
			Sq_Delete(db);
			break;
		case '3': //修改
			Sq_Update(db);
			break;
		case '4': //查找
			Sq_Search(db);
			break;
		case '5': //退出
			goto END;
			break;
		default :
			printf("format error,please enter again\n");
			break;
		}
	}

END:
	//关闭数据库
	if(sqlite3_close(db)!=0)
	{	
		fprintf(stdout,"sqlite3_close : [%d] : %s\n",sqlite3_errcode(db),sqlite3_errmsg(db));
		return -1;
	}
	return 0;
}
//子函数
#include "head.h"
//插入
void Sq_Insert(sqlite3 *db)
{
	//从终端获得插入的数据
	printf("id:  name:  score:\n");
	printf("please enter one set of data :");
	int id;
	char name[20];
	float score;
	scanf("%d %s %f",&id,name,&score);
	while(getchar()!=10);
	//将数据组合成数据库命令
	char command[128] = "";
	sprintf(command,"insert into %s values (%d,\"%s\",%f)",tabname,id,name,score);
	//写入数据库
	char *errmsg;
	if(sqlite3_exec(db,command,NULL,NULL,&errmsg)!=0)
	{
		fprintf(stdout,"insert into : %s\n",errmsg);
		return ;	
	}
	printf("insert into success\n");
	return ;
}
//删除
void Sq_Delete(sqlite3 *db)
{
	//获取删除条件
	printf("please enter delete limit : ");
	char limit[20] = "";
	scanf("%s",limit);
	while(getchar()!=10);
	//将数据组合成数据库命令
	char command[128] = "";
	sprintf(command,"delete from %s where %s",tabname,limit);
	//写入数据库
	char *errmsg;
	if(sqlite3_exec(db,command,NULL,NULL,&errmsg)!=0)
	{
		fprintf(stdout,"delete : %s\n",errmsg);
		return ;	
	}
	printf("delete success\n");
	return ;
}
//修改
void Sq_Update(sqlite3 *db)
{
	//获取修改条件
	printf("please enter update limit : ");
	char limit[20] = "";
	scanf("%s",limit);
	while(getchar()!=10);
	//获取修改数据
	printf("please enter update data : (eg:score = 100)");
	char update[20] = "";
	scanf("%s",update);
	while(getchar()!=10);
	//将数据组合成数据库命令
	char command[128] = "";
	sprintf(command,"update %s set %s where %s",tabname,update,limit);
	//写入数据库
	char *errmsg;
	if(sqlite3_exec(db,command,NULL,NULL,&errmsg)!=0)
	{
		fprintf(stdout,"update : %s\n",errmsg);
		return ;	
	}
	printf("update success\n");
	return ;
}
//查询
void Sq_Search(sqlite3 *db)
{
	printf("buildding\n");
	return;
}

将词典带编号导入到数据库中

#include <quick.h>
#define sqname "dict.db"
#define tabname "dict"
int main(int argc, const char *argv[])
{
	//打开词典.txt
	int dfd = open("./dict.txt",O_RDONLY);
	if(dfd < 0)
	{
		perror("open");
		return -1;
	}
	printf("dict.txt is opened\n");
	//打开词典.db
	sqlite3 *db;
	if(sqlite3_open(sqname,&db) != 0)
	{
		fprintf(stdout,"sqlite3_open [%d] : %s\n",sqlite3_errcode(db),sqlite3_errmsg(db));
		return -1;
	}
	printf("dict.db is opened\n");
	//创建一个表
	char command[128] = "";
	char *errmsg = NULL;
	sprintf(command,"create table if not exists %s (seq int primary key,word char,mean char)",tabname);
	if(sqlite3_exec(db,command,NULL,NULL,&errmsg) != 0)
	{
		printf("table create : %s\n",errmsg);
		return -1;
	}
	printf("table is created\n");
	//将词典单词写入数据库
	char buf;
	char word[20] = "";
	char mean[50] = "";
	int seq = 0;
	int i = 0;
	ssize_t res = 0;
	//从文件夹中读取单词、意思
	while(1)
	{	
		seq++;
		bzero(word,sizeof(word));
		bzero(mean,sizeof(mean));
		i=0;
		//写入word
		while(1)
		{
			res = read(dfd,&buf,1);
			//判断是否写入结束
			if(res == 0)
			{
				printf("insert into finished\n");
				goto END;
			}
			else if(buf == ' ')
				break;
			else
			{
				word[i]=buf;
				i++;
			}
		}
		i=0;
		//写入mean
		while(1)
		{
			read(dfd,&buf,1);
			if(buf == ' ')
				continue;
			else if(buf == '\n')
				break;
			else
			{
				mean[i]=buf;
				i++;
			}
		}
		//一个单词信息读取完毕
		//组合数据库命令
		sprintf(command,"insert into %s values (%d,\"%s\",\"%s\")",tabname,seq,word,mean);
		//将数据写入数据库
		if(sqlite3_exec(db,command,NULL,NULL,&errmsg) != 0)
		{
			printf("insert into : %s\n",errmsg);
			return -1;	
		}
		printf("[%d] insert into success\n",seq);
	}
END:
	//关闭文件
	close(dfd);
	//关闭数据库
	if(sqlite3_close(db) != 0)
	{
		fprintf(stdout,"sqlite3_open [%d] : %s\n",sqlite3_errcode(db),sqlite3_errmsg(db));
		return -1;	
	}
	return 0;
}