#include <stdio.h>
#include <sqlite3.h>
#include <string.h>
#include <stdlib.h>
char buf[20];
int show_TableName(sqlite3 * mydb)
{
char ** result = NULL;
int line = 0;
int column = 0;
char *errmsg = NULL;
//---------------查看数据库有哪些表---------------
if(sqlite3_get_table(mydb,"SELECT name FROM sqlite_master WHERE type='table';",&result,&line,&column,&errmsg) != SQLITE_OK)
{
fprintf(stderr,"sqlite3_get_table: %s\n",errmsg);
return -1;
}
for(int i=1;i<(line+1)*column;i++)
{
printf("%s\t",result[i]);
if((i+1)%column == 0)
putchar(10);
}
//---------------判断输入的表名是否存在---------------
printf("你要操作哪个表(表名):\n");
int i;
while(1)
{
bzero(buf,20);
scanf("%s",buf);
for(i=1;i<(line+1)*column;i++)
{
if(strcmp(result[i],buf) == 0)
break;
}
if(i == (line+1)*column)
printf("没有你输入的表名,请重新输入\n");
if(i<(line+1)*column)
break;
}
return 0;
}
//查看数据表
int show_db(sqlite3 * mydb)
{
char ** result = NULL;
int line = 0;
int column = 0;
char *errmsg = NULL;
show_TableName(mydb);
//----------------打印出你选择的数据表------------------
char buffer[100];
bzero(buffer,100);
snprintf(buffer,100,"SELECT * FROM %s;",buf);
if(sqlite3_get_table(mydb,buffer,&result,&line,&column,&errmsg) != SQLITE_OK)
{
fprintf(stderr,"sqlite3_get_table: %s\n",errmsg);
return -1;
}
for(int i=0;i<(line+1)*column;i++)
{
printf("%s\t",result[i]);
if((i+1)%column == 0)
putchar(10);
}
return 0;
}
//添加数据
int add_db(sqlite3 * mydb)
{
char ** result = NULL;
int line = 0;
int column = 0;
char *errmsg = NULL;
show_TableName(mydb);
char buffer[100];
bzero(buffer,100);
snprintf(buffer,100,"SELECT * FROM %s;",buf);
//---------------查看数据库有哪些表---------------
if(sqlite3_get_table(mydb,buffer,&result,&line,&column,&errmsg) != SQLITE_OK)
{
fprintf(stderr,"sqlite3_get_table: %s\n",errmsg);
return -1;
}
printf("请输入对应输入内容(请用逗号,隔开,输入中文用'中文内容 '格式):\n");
for(int i=0;i<column;i++)
{
printf("%s\t",result[i]);
}
putchar(10);
char buf1[100];
bzero(buf1,100);
getchar();
fgets(buf1,100,stdin);
bzero(buffer,100);
snprintf(buffer,100,"INSERT INTO %s values(%s);",buf,buf1);
if(sqlite3_get_table(mydb,buffer,&result,&line,&column,&errmsg) != SQLITE_OK)
{
fprintf(stderr,"sqlite3_get_table: %s\n",errmsg);
return -1;
}
return 0;
}
//修改数据
int change_db(sqlite3 * mydb)
{
char ** result = NULL;
int line = 0;
int column = 0;
char *errmsg = NULL;
show_db(mydb);
printf("你要修改哪个id的数据:\n");
char id[10];
scanf("%s",id);
char buffer[100];
bzero(buffer,100);
snprintf(buffer,100,"SELECT * FROM %s;",buf);
if(sqlite3_get_table(mydb,buffer,&result,&line,&column,&errmsg) != SQLITE_OK)
{
fprintf(stderr,"sqlite3_get_table: %s\n",errmsg);
return -1;
}
for(int i=0;i<(line+1)*column;i++)
{
if(strcmp(id,result[i])==0 || strcmp("id",result[i]) == 0)
{
for(int j =0;j<column;j++)
{
printf("%s\t",result[i]);
i++;
}
putchar(10);
i--;
}
}
printf("请选择你要修改的内容(比如:name = '要修改的内容'):");
char buf1[20];
bzero(buf1,20);
getchar();
fgets(buf1,20,stdin);
bzero(buffer,100);
snprintf(buffer,100,"UPDATE %s SET %s WHERE id = %s;",buf,buf1,id);
if(sqlite3_get_table(mydb,buffer,&result,&line,&column,&errmsg) != SQLITE_OK)
{
fprintf(stderr,"sqlite3_get_table: %s\n",errmsg);
return -1;
}
printf("修改成功\n");
return 0;
}
//删除数据
int del_db(sqlite3 * mydb)
{
char ** result = NULL;
int line = 0;
int column = 0;
char *errmsg = NULL;
show_db(mydb);
printf("你要删除哪个id的数据:\n");
char id[10];
scanf("%s",id);
char buffer[100];
bzero(buffer,100);
snprintf(buffer,100,"DELETE FROM %s WHERE id = %s;",buf,id);
if(sqlite3_get_table(mydb,buffer,&result,&line,&column,&errmsg) != SQLITE_OK)
{
fprintf(stderr,"sqlite3_get_table: %s\n",errmsg);
return -1;
}
printf("删除成功\n");
return 0;
}
//阻塞
void block()
{
while(getchar()!='\n');
while(getchar()!='\n');
system("clear");
}
int main(int argc, const char *argv[])
{
//打开数据库
sqlite3 * mydb;
if(sqlite3_open("./STU.db",&mydb) != SQLITE_OK)
{
fprintf(stderr,"sqlite3_open:%d %s\n",sqlite3_errcode(mydb),sqlite3_errmsg(mydb));
return -1;
}
printf("sqlite3_open sucess\n");
while(1)
{
printf("**********数据库系统**********\n");
printf("**********1.查看数据表\n");
printf("**********2.添加数据\n");
printf("**********3.修改数据\n");
printf("**********4.删除数据\n");
printf("**********5.退出系统\n");
putchar(10);
int choose;
printf("请选择你要操作的选项(1-5):\n");
scanf("%d",&choose);
switch(choose)
{
//查看数据表
case 1:
show_db(mydb);
block();
break;
//添加数据
case 2:
add_db(mydb);
block();
break;
//修改数据
case 3:
change_db(mydb);
block();
break;
//删除数据
case 4:
del_db(mydb);
block;
break;
//退出系统
case 5:
return 0;
break;
default:
printf("你的输入有误,请重新输入\n");
block();
break;
}
}
return 0;
}