20230413作业(数据库，sqlite3_exec)

最新推荐文章于 2024-05-21 02:06:32 发布

q813601848

最新推荐文章于 2024-05-21 02:06:32 发布

阅读量14

点赞数

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

本文链接：https://blog.csdn.net/q813601848/article/details/130140565

版权

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

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

	//在数据库中创建一个表
	//注意c代码中编写的sql语句与数据库中编写的一致
	//CREAT TABLE if not exists stu1 (id int,name char,score float);
	
	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  squlite3_exe:%s\n",__LINE__,errmsg);
		return -1;
	}
	printf("create table stu success\n");


	char c = 0;
	while(1)
	{
		system("clear");

		printf("---------------------------------\n");
		printf("-------------1.增加--------------\n");
		printf("-------------2.删除--------------\n");
		printf("-------------3.修改--------------\n");
		printf("-------------4.查找--------------\n");
		printf("-------------5.退出--------------\n");
		printf("---------------------------------\n");

		printf("请输入>>>>>");
		c=getchar();
		while(getchar()!=10);

		switch(c)
		{
		case '1':
			//do_insert;
			{
				int id1;
				char name1[20]="";
				float score1;
				char sql[128]="INSERT INTO stu VALUES";
				fprintf(stderr,"请依次输入学生的id号、姓名和分数：");
				scanf("%d %s %f",&id1,name1,&score1);
				sprintf(sql,"%s(%d,'%s',%f)",sql,id1,name1,score1);
			//	printf("%s\n",sql);
			// 	char sql[128]="INSERT INTO stu VALUES (10,'zs',90);";
				char *errmsg=NULL;
				if(sqlite3_exec(db,sql,NULL,NULL,&errmsg)!= SQLITE_OK)
				{
					fprintf(stderr,"line = %d  squlite3_exe:%s\n",__LINE__,errmsg);
					return -1;
				}
				printf("insert table stu success\n");
			}
			break;
		case '2':
			//do_delete;
			{	
				printf("请输入删除条件：\n");
				char sql[128]="DELETE FROM stu where ";
				char where[50]="";
				fgets(where,sizeof(where),stdin);
				where[strlen(where)-1]=0;
				sprintf(sql,"%s %s",sql,where);
				char *errmsg=NULL;
				if(sqlite3_exec(db,sql,NULL,NULL,&errmsg)!= SQLITE_OK)
				{
					fprintf(stderr,"line = %d  squlite3_exe:%s\n",__LINE__,errmsg);
				//	fprintf(stderr,"squlites3_score=%d\n",errcode);
					return -1;
				}
				printf("delete stu data success\n");
			}

			break;
		case '3':
			//do_update;
			{	
				char sql[128]="UPDATE stu set";
				char buf1[30]="";
				char buf2[30]="";
				fprintf(stderr,"请输入要修改的项目和修改后的值：");
				fgets(buf1,sizeof(buf1),stdin);
				fprintf(stderr,"请输入限制条件：");
				fgets(buf2,sizeof(buf2),stdin);
				sprintf(sql,"%s %s where %s",sql,buf1,buf2);
				char *errmsg=NULL;
				if(sqlite3_exec(db,sql,NULL,NULL,&errmsg)!= SQLITE_OK)
				{
					fprintf(stderr,"line = %d  squlite3_exe:%s\n",__LINE__,errmsg);
					return -1;
				}
				printf("update table stu success\n");
			}

			break;
		case '4':
			//do_select;
			{
				char sql[128]="SELECT * FROM stu;";
				char *errmsg=NULL;
				if(sqlite3_exec(db,sql,NULL,NULL,&errmsg)!= SQLITE_OK)
				{
					fprintf(stderr,"line = %d  squlite3_exe:%s\n",__LINE__,errmsg);
					return -1;
				}
				printf("select table stu success\n");
			}
			break;
		case '5':
			goto END;
			break;
		default:
			printf("输入错误，请重新输入\n");
		}

		printf("输入任意字符清屏>>>>>>");
		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;
}