网络编程4

操作数据库

/**对数据库进行增删改查*/
#include <stdio.h>
#include <sqlite3.h>
#include <string.h>
#include <stdlib.h>

void Show(int pages);
int callback(void *arg,int num,char **f_name,char **f_num);
int main()
{
	int num;
	int pages;
	int flag=0;
	//1.打开数据库
	sqlite3 *mydb = NULL;
	sqlite3_open("./Library.db",&mydb);
	char **result;	
	int row,column;
	//SQL语句
	char * sql_s1=malloc(100);
	char * sql_s2=malloc(100);
	char * sql_s3=malloc(100);
	char * sql=malloc(300);
	char *errmsg = NULL;
	while(1){
		if(flag==0){
	Show(0);
	scanf("%d",&num);
		flag=1;}
		switch(num){
			case 1 :
			   Show(1);
			   scanf("%d",&pages);
			   switch(pages){
					case 1 :
					memset(sql_s1,0,100);
					memset(sql_s2,0,100);
					memset(sql,0,300);
					printf("select %%s from %%s;\n");
					scanf("%s %s",sql_s1,sql_s2);
					getchar();
					snprintf(sql,300,"select %s from %s;",sql_s1,sql_s2);
					sqlite3_get_table(mydb,sql,&result, &row,&column,NULL);
					for(int i = 0;i <(row+1)*column;i++){
						printf("%-18s",result[i]);
							if((i+1)%column == 0)
							{
								printf("\n");
							}
					}
					break;
					case 2 :
					memset(sql_s1,0,100);
					memset(sql_s2,0,100);
					memset(sql_s3,0,100);
					memset(sql,0,300);
					printf("select %%s from %%s where %%s;\n");
					scanf("%s %s %s",sql_s1,sql_s2,sql_s3);
					getchar();
					snprintf(sql,300,"select %s from %s where %s;",sql_s1,sql_s2,sql_s3);
					sqlite3_exec(mydb,sql,callback, NULL, NULL);
					break;
					default :
					flag=0;
					pages=0;
					break;
			   }
			  break; 
			case 2 :
			   Show(2);
			   scanf("%d",&pages);
			   switch(pages){
				   	case 1 :
						memset(sql_s1,0,100);
						memset(sql,0,300);
						printf("delete from %%s;\n");
						scanf("%s",sql_s1);
						getchar();
						snprintf(sql,300,"delete from %s;",sql_s1);
						sqlite3_exec(mydb,sql,NULL, NULL, NULL);
						printf("操作完成\n");
						break;
				   	case 2 :
						memset(sql_s1,0,100);
						memset(sql_s2,0,100);
						memset(sql,0,300);
						printf("delete from %%s;\n");
						scanf("%s %s",sql_s1,sql_s2);
						getchar();
						snprintf(sql,300,"delete from %s where %s; ",sql_s1,sql_s2);
						sqlite3_exec(mydb,sql,NULL, NULL, NULL);
						printf("操作完成\n");
						break;
					default :
					flag=0;
					pages=0;
						break;
			   }
			  break;
			case 3 :
			   Show(3);
			   scanf("%d",&pages);
			     switch(pages){
					case 1 :
					memset(sql_s1,0,100);
					memset(sql_s2,0,100);
					memset(sql_s3,0,100);
					memset(sql,0,300);
					printf("update %%s set %%s where %%s;\n");
					scanf("%s %s %s",sql_s1,sql_s2,sql_s3);
					getchar();
					snprintf(sql,300,"update %s set %s where %s",sql_s1,sql_s2,sql_s3);
					sqlite3_exec(mydb,sql,NULL, NULL, NULL);
					printf("操作完成\n");
						break;
					case 2 :
					memset(sql_s1,0,100);
					memset(sql_s2,0,100);
					memset(sql_s3,0,100);
					memset(sql,0,300);
					printf("alter table %%s add %%s %%s;\n");
					scanf("%s %s %s",sql_s1,sql_s2,sql_s3);
					getchar();
					snprintf(sql,300,"alter table %s add %s %s;",sql_s1,sql_s2,sql_s3);
					sqlite3_exec(mydb,sql,NULL, NULL, NULL);
					printf("操作完成\n");
						break;
					default  :
					flag=0;
					pages=0;
						break; 
				 }						
			  break;
			 
			case 4 :
			//printf("select %%s  from %%s where %%s;\n");
					flag=0;
					//pages=0;
			  break;
			  
			default  :
				return 0;
			  break;
		}
	}
}

//回调函数
int callback(void *arg,int num,char **f_name,char **f_num)//char **f_name, char *f_name[]
{
	static int flag=0;
	//数据个数，//每一个数据，//每一个表头
	//打印查询结果
	if(flag == 0){
	for(int i = 0;i < num;i++)
	{
		printf("%-16s",f_num[i]);
	}
	printf("\n");
	flag=1;
	}
	for(int i = 0;i < num;i++)
	{
		printf("%-16s",f_name[i]);
		
	}
	printf("\n");
	
	return 0;
}
void Show(int pages){
	switch (pages) {
		case 0 :
		   	printf("***********************************\n输入对应功能的数字\n\t1.查询表\n\t2.删除表\n\t3.修改表内容\n\t4.**** \n\t5.退出程序\n***********************************\n");
		   break; 
		case 1 :
		   	printf("***********************************\n查询表-----输入对应功能的数字\n\t1.查询表所有内容：\n select %%s from %%s;\n\t2.条件查询：\nselect %%s from %%s where %%s;\n\t3.退出当前界面\n***********************************\n");
		   break; 
		case 2 :
			printf("***********************************\n删除表-----输入对应功能的数字\n\t1.删除所有内容：\n delete from %%s;\n\t2.删除行内容：\ndelete from %%s where %%s;\n\t3.退出当前界面\n***********************************\n");
			break;
		case 3 :
		printf("***********************************\n修改表-----输入对应功能的数字\n\t1.1.更新表内容：\nupdate %%s set %%s where %%s;\n\t2.添加表列：\nalter table %%s add %%s %%s;\n\t3.退出当前界面\n***********************************\n");
			break;
		case 4 :
		//printf("***********************************\n删除表-----输入对应功能的数字\n\t1.删除所有内容：\n delete from %%s;\n\t2.删除行内容：\ndelete from %%s where %%s;\n\t3.退出当前界面\n***********************************\n");
			break;
		case 5 :
		//printf("***********************************\n删除表-----输入对应功能的数字\n\t1.删除所有内容：\n delete from %%s;\n\t2.删除行内容：\ndelete from %%s where %%s;\n\t3.退出当前界面\n***********************************\n");
			break;
		default : 
			break;
	}

}