基于C语言的SQlite3数据库操作指令

基本介绍

数据库的建立流程中的shell命令

#include <stdio.h>
#include <sqlite3.h>
// 回调函数，用于处理查询结果
int mysqlite3_callback(void *para, int f_num, char **f_value, char **f_name)
{
	static int flag = 0;
	int i;
	if (flag == 0)
	{
		for (i = 0; i < f_num; i++)
		{
			printf("%-12s", f_name[i]); // 打印字段名
		}
		printf("\n");
		flag = 1;
	}

	for (i = 0; i < f_num; i++)
	{
		printf("%-12s", f_value[i]); // 打印字段值
	}
	printf("\n");

	return 0;
}
int meun(void)   //打印菜单主页面
{
	int a,b,m ;
	for(a=0;a<30;a++)
	{
		printf("*");
	}
	printf("\n");
	printf("choose the num to choose func:");
	printf("\n");
	printf("0.return");
	printf("\n");
	printf("1.bian li the student data");
	printf("\n");
	printf("2.add a student");
	printf("\n");
	printf("3.delete a student");
	printf("\n");
	printf("4.bian li the teacher data");
	printf("\n");
	printf("5.add a teacher");
	printf("\n");
	printf("6.delete a teacher");
	printf("\n");
	printf("7.choose the students teacher who are fail in exam");
	printf("\n");
	for(b=0;b<30;b++)
	{
		printf("*");
	}
	printf("\n");
	return 0;
}





int main()
{	

	while(1)
	{
		meun();
		int a,b,m;	
		scanf("%d",&m);
		sqlite3 *mydb = NULL;
		int ret = sqlite3_open("./student.db", &mydb); // 打开数据库文件 student.db获取句柄
		if (ret != SQLITE_OK)//判断是否打开成功
		{
			printf("open sqlite failed\n");
			return -1;
		}
		char sql[1024];//定义一个buf


		if(m==0)//退出死循环
		{
			return 0;
		}

		if(m==1)
		{
			sprintf(sql, "select * from student;"); // 准备 SQL 查询语句

			sqlite3_exec(mydb, sql, mysqlite3_callback, NULL, NULL); // 执行查询

			sqlite3_close(mydb); // 关闭数据库
		}

		if(m==2)
		{
			char *errmsg;
			sprintf(sql, "select * from student;"); // 准备 SQL 查询语句

			sqlite3_exec(mydb, sql, mysqlite3_callback, NULL, NULL); // 执行查询
			char name[128];
			char sex[128];
			int num;
			int chenji;
			char sql[128];
			printf("please input the name:");
			scanf("%s",name);
			printf("please input sex:");
			scanf("%s",sex);
			printf("please input num:");
			scanf("%d",&num);
			printf("please input chenji:");
			scanf("%d",&chenji);

			sprintf(sql,"insert into student(name,sex,num,class)values('%s','%s',%d,%d);",name,sex,num,chenji);
			printf("%s\n",sql);
			if (sqlite3_exec(mydb,sql,NULL,NULL,&errmsg)!=SQLITE_OK)
			{
				printf("%s\n",errmsg);
			}

			sqlite3_close(mydb); // 关闭数据库
		
		}
           if(m==3)
		   
		   {
			   char *errmsg;
			   char name[128];
			   printf("请输入要删除的列名:");
			   scanf("%s",name);
			   sprintf(sql, "delete from student where name='%s';",name); // 准备 SQL 查询语句
			   sqlite3_exec(mydb, sql, mysqlite3_callback, NULL, NULL); // 执行查询

			   sqlite3_close(mydb); // 关闭数据库
			   if(sqlite3_exec(mydb,sql,NULL,NULL,&errmsg)!=SQLITE_OK)
			   {
				   printf("%s\n",errmsg);
			   }
		   }




           	if(m==4)
		{
			sprintf(sql, "select * from techar;"); // 准备 SQL 查询语句

			sqlite3_exec(mydb, sql, mysqlite3_callback, NULL, NULL); // 执行查询

			sqlite3_close(mydb); // 关闭数据库
		}



			
		if(m==5)
		{
			char *errmsg;
			sprintf(sql, "select * from techar;"); // 准备 SQL 查询语句

			sqlite3_exec(mydb, sql, mysqlite3_callback, NULL, NULL); // 执行查询
			char name[128];
			char sex[128];
			int n_num;
			int class;
			char sql[128];
			printf("please input the name:");
			scanf("%s",name);
			printf("please input sex:");
			scanf("%s",sex);
			printf("please input  n_num:");
			scanf("%d",&n_num);
			printf("please input class:");
			scanf("%d",&class);

			sprintf(sql,"insert into techar(name,sex,n_num,class)values('%s','%s',%d,%d);",name,sex,n_num,class);
			printf("%s\n",sql);

			if (sqlite3_exec(mydb,sql,NULL,NULL,&errmsg)!=SQLITE_OK)
			{
				printf("%s\n",errmsg);
			}

			sqlite3_close(mydb); // 关闭数据库
		
		}
         if(m==6)
		   
		   {
			   char *errmsg;
			   char name[128];
			   printf("请输入要删除的列名:");
			   scanf("%s",name);
			   sprintf(sql, "delete from techar where name='%s';",name); // 准备 SQL 查询语句
			   sqlite3_exec(mydb, sql, mysqlite3_callback, NULL, NULL); // 执行查询

			   sqlite3_close(mydb); // 关闭数据库
			   if(sqlite3_exec(mydb,sql,NULL,NULL,&errmsg)!=SQLITE_OK)
			   {
				   printf("%s\n",errmsg);
			   }
		   }

		 if(m==7)
		 {
			 char * errmsg;
			 int i;	
			 int row;	
			 int col;
			 char **resultp;//结果
			 char sql[128];
			 if(sqlite3_get_table(mydb, "select techar.name ,student.name, techar.n_num ,student.chenji;",&resultp,&row,&col,&errmsg)!=SQLITE_OK)
			 {
				 printf("%s\n",errmsg);
			 }
			 for(i=0; i<(row+1)*col;i++)
			 {
				 printf("%-18s",resultp[i]);
				 if((i+1)%col == 0)//column   3
				 {
					 printf("\n");
				 }
			 }

		 }
	}
	return 0;
}