Day35 4.13 sqlite3数据库

最新推荐文章于 2024-07-11 18:29:00 发布

kcncoooo

最新推荐文章于 2024-07-11 18:29:00 发布

阅读量45

点赞数

文章标签： sqlite 数据库 c语言开发语言

本文链接：https://blog.csdn.net/kcncoooo/article/details/130139897

版权

数据表创建以及增删改查

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

//学生信息结构体
typedef struct student
{
	int id;
	char name[20];
	float score;
}Stu;

//数据表插入记录
int do_insert(sqlite3* db)
{
	Stu s1;
	printf("please enter student's information\n");
	scanf("%d %s %f",&s1.id,s1.name,&s1.score);

	char sql[128]="";
	sprintf(sql,"INSERT INTO stu VALUES(%d,'%s',%.1f)",s1.id,s1.name,s1.score);

	char* errmsg=NULL;
	if(sqlite3_exec(db,sql,NULL,NULL,&errmsg)!=SQLITE_OK){
		fprintf(stderr,"line:%d sqlite3_exec:%s\n",__LINE__,errmsg);
		return -1;
	}
	printf("insert success\n");
	putchar('\n');
	return 0;
}

//数据表删除记录
int do_delete(sqlite3* db)
{
	Stu s1;
	printf("please enter the id of the student you want to delete\n");
	scanf("%d",&s1.id);

	char sql[128]="";
	sprintf(sql,"DELETE FROM stu WHERE id=%d",s1.id);

	char* errmsg=NULL;
	if(sqlite3_exec(db,sql,NULL,NULL,&errmsg)!=SQLITE_OK){
		fprintf(stderr,"line:%d sqlite3_exec:%s\n",__LINE__,errmsg);
		return -1;
	}
	printf("delete success\n");
	putchar('\n');
	return 0;
}

//数据表修改记录
int do_update(sqlite3* db)
{
    Stu s1;
	printf("please enter the id of the student you want to update\n");
	scanf("%d",&s1.id);

	printf("please enter the score of the student you want to update\n");
	scanf("%f",&s1.score);

	char sql[128]="";
	sprintf(sql,"UPDATE stu SET score=%.1f WHERE id=%d",s1.score,s1.id);

	char* errmsg=NULL;
	if(sqlite3_exec(db,sql,NULL,NULL,&errmsg)!=SQLITE_OK){
		fprintf(stderr,"line:%d sqlite3_exec:%s\n",__LINE__,errmsg);
		return -1;
	}
	printf("update success\n");
	putchar('\n');
	return 0;
}

//查找的回调函数
static int callback(void* NotUsed,int argc,char** argv,char** azColName)
{
	int i;
	putchar('\n');
	printf("select result:\n");
	for(i=0;i<argc;i++){
		printf("%s=%s\n",azColName[i],argv[i]?argv[i]:"NULL");
	}
	putchar('\n');
	return 0;
}

//数据表查找记录
int do_select(sqlite3* db)
{
    Stu s1;

	char sql[128]="";
	sprintf(sql,"SELECT* FROM stu");

	char* errmsg=NULL;
	if(sqlite3_exec(db,sql,callback,NULL,&errmsg)!=SQLITE_OK){
		fprintf(stderr,"line:%d sqlite3_exec:%s\n",__LINE__,errmsg);
		return -1;
	}
	printf("select success\n");
	putchar('\n');
	return 0;
}

int main(int argc, const char *argv[])
{
	//如果数据库不存在，则创建后打开
	//如果存在则直接打开
	sqlite3* db =NULL;
	if(sqlite3_open("./my.db",&db)!=SQLITE_OK){
		fprintf(stderr,"sqlite3_open:%s errcode:%d\n",sqlite3_errmsg(db),sqlite3_errcode(db));
		return -1;
	}
	printf("sqlite3_open success\n");

	//创建一个表
	//注意：C中写的sql语句和数据库中写的一致
	char sql[128]="create table if not exists stu(id int,name char,score float);";
	char* errmsg=NULL;

	if(sqlite3_exec(db,sql,NULL,NULL,&errmsg)!=SQLITE_OK){
		fprintf(stderr,"line:%d sqlite3_exec:%s\n",__LINE__,errmsg);
		return -1;
	}
	printf("create table stu success\n");

	char c=0;
	while(1){
		system("clear");
		printf("---------------------------------------\n");
		printf("---------------1.insert----------------\n");
		printf("---------------2.delete----------------\n");
		printf("---------------3.update----------------\n");
		printf("---------------4.select----------------\n");
		printf("---------------5.exit------------------\n");
		printf("---------------------------------------\n");
		printf("choose>>>");
		c=getchar();
		while(getchar()!=10);

		switch(c){
			case '1':
				do_insert(db);
				break;
			case '2':
				do_delete(db);
				break;
			case '3':
				do_update(db);
				break;
			case '4':
				do_select(db);
				break;
			case '5':
				goto END;
				break;
			default:
				printf("input error\n");
		}
		printf("enter any character to clear the screen>>>");
		while(getchar()!=10);
	}

END:
	//关闭数据库
	if(sqlite3_close(db)!=SQLITE_OK){
		fprintf(stderr,"sqlite3_close:%s errcode:%d\n",sqlite3_errmsg(db),sqlite3_errcode(db));
		return -1;
	}

	return 0;
}