操作数据库
/**对数据库进行增删改查*/
#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;
}
}