数据库增删改
#include <stdio.h>
#include <sqlite3.h>
#include <stdlib.h>
#include <string.h>char memu_choice_func(char c, sqlite3** db);
void do_insert(sqlite3* db);
void do_delete(sqlite3* db);
void do_update(sqlite3* db);
int main(int argc, const char *argv[])
{
if(2 != argc)
{
printf("please %s <database.db>\n", argv[0]);
return -1;
}
//创建并打开数据库
sqlite3 *db = NULL;
if(sqlite3_open(argv[1], &db) != SQLITE_OK)
{
fprintf(stderr, "line:%d--sqlite3_open:%s\n", __LINE__, sqlite3_errmsg(db));
return -1;
}
printf("database open success\n");//创建表格
char sql[128] = "create table if not exists stu (id int primary key, name char, score float);";char* errmsg = NULL;
if(sqlite3_exec(db, sql, NULL, NULL, &errmsg) != SQLITE_OK)
{
fprintf(stderr, "line:%d-- sqlite3_exec:%s\n", __LINE__, errmsg);
return -1;
}
printf("create table success \n");char c = 0;
int flag = 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);
flag = memu_choice_func(c, &db);
if(!flag)
{
break;
}
}//关闭数据库
if(sqlite3_close(db) != SQLITE_OK)
{
fprintf(stderr, "line:%d sqlite3_open:%s\n", __LINE__, sqlite3_errmsg(db));
fprintf(stderr, "line:%d sqlite3_open:%d\n", __LINE__, sqlite3_errcode(db));
return -1;
}
printf("database close success\n");return 0;
}char memu_choice_func(char c, sqlite3** db)
{
switch(c)
{
case '1': //增 ---> 全字段插入
do_insert(*db);
break;
case '2': //删 ---> 通过id号去删除
do_delete(*db);
break;
case '3': //改 ---> 通过id号修改
do_update(*db);
break;
case '4':
//do_select();
break;
case '5':
return 0;
default:
printf("输入错误,请重新输入\n");
}printf("输入任意字符清屏>>>");
while(getchar()!=10);return 1;
}void do_insert(sqlite3* db)
{
char sql[128] = "";
int id = 0;
char name[20] = "";
float score = 0.0;
char* errmsg = NULL;
printf("please enter id, name, score>>>");
scanf("%d %s %f", &id, name, &score);
while(getchar() != 10);
sprintf(sql, "insert into stu values (%d,\"%s\",%f)", id, name, score);
if(sqlite3_exec(db, sql, NULL, NULL, &errmsg) != SQLITE_OK)
{
fprintf(stderr, "line:%d-- sqlite3_exec:%s\n", __LINE__, errmsg);
return;
}
printf("insert success\n");
}void do_delete(sqlite3* db)
{
char sql[128] = "";
int id = 0;
char *errmsg =NULL;
printf("please enter need delet id>>>");
scanf("%d", &id);
while(getchar() != 10);
sprintf(sql,"DELETE FROM stu WHERE id=%d", id);
if(sqlite3_exec(db, sql, NULL, NULL, &errmsg) !=SQLITE_OK)
{
fprintf(stderr, "line:%d-- sqlite3_exec:%s\n", __LINE__, errmsg);
return ;
}
printf("delet success\n");
}void do_update(sqlite3* db)
{
char sql[128] = "";
char seg[10] = "";
char value[10] = "";int id = 0;
char *errmsg =NULL;
printf("please enter need update id, seg , values>>>");
scanf("%d %s %s", &id, seg, value);
while(getchar() != 10);
if(!strcmp(seg, "name"))
sprintf(sql,"UPDATE stu SET %s=\"%s\" WHERE id=%d", seg, value, id);
else
sprintf(sql,"UPDATE stu SET %s=%s WHERE id=%d", seg, value, id);
if(sqlite3_exec(db, sql, NULL, NULL, &errmsg) !=SQLITE_OK)
{
fprintf(stderr, "line:%d sqlite3_exec:%s\n", __LINE__, errmsg);
return ;
}
printf("uodate success\n");
}