#include <stdio.h>
#include <sqlite3.h>
#include <stdlib.h>
#include <string.h>
int do_delete(sqlite3* db);
int do_insert(sqlite3* db);
int do_update(sqlite3* db);
int do_select(sqlite3* db);
int main(int argc, const char *argv[])
{
//创建并打开数据库
sqlite3 *db = NULL;
if(sqlite3_open("./my.db", &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 open success\n");
//创建一张表格
//sql语句,在数据库中怎么写,在代码中就怎么写,不确定sql语句是否是正确的,
//可以将该sql语句在数据库中执行一遍,看看是不是正确的;
char sql[128] = "create table if not exists zoo (id int primary key, name char, sore float);";
// char* ptr = "create table stu (id int, 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;
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);
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(db);
break;
case '5':
goto END;
default:
printf("输入错误,请重新输入\n");
}
printf("输入任意字符清屏>>>");
while(getchar()!=10);
}
END:
//关闭数据库
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;
}
int do_delete(sqlite3 *db)
{
int id;
char act[128];
char* errmsg = NULL;
while(1)
{
printf("请输入要删除信息的ID:\n如果不需要删除输入99退出\n");
// scanf("%d %s %f",&id,name,&sore);
bzero(act,sizeof(act));
scanf("%d",&id);
if(99 == id)
break;
sprintf(act, "delete from zoo where id=%d", id);
if(sqlite3_exec(db, act, NULL, NULL, &errmsg) != SQLITE_OK)
{
fprintf(stderr, "line:%d sqlite3_exec:%s\n", __LINE__, errmsg);
return -1;
}
printf("database delete success\n");
}
return 0;
}
int do_insert(sqlite3 *db)
{
int id;
char name[20];
float sore;
char act[128];
char* errmsg = NULL;
while(1)
{
printf("请输入要增加的信息:ID name sore\n如果不需要插入输入99退出\n");
// scanf("%d %s %f",&id,name,&sore);
bzero(act,sizeof(act));
scanf("%d",&id);
if(99 == id)
break;
scanf("%s %f",name,&sore);
sprintf(act, "insert into zoo values(%d,'%s',%f )", id, name, sore);
if(sqlite3_exec(db, act, NULL, NULL, &errmsg) != SQLITE_OK)
{
fprintf(stderr, "line:%d sqlite3_exec:%s\n", __LINE__, errmsg);
return -1;
}
printf("database intsert success\n");
}
return 0;
}
int do_update(sqlite3 *db)
{
int id;
char name[20];
float sore;
char act[128];
char* errmsg = NULL;
while(1)
{
printf("请输入要修改信息的ID:\n如果不需要修改输入99退出\n");
// scanf("%d %s %f",&id,name,&sore);
bzero(act,sizeof(act));
scanf("%d",&id);
if(99 == id)
break;
printf("请输入要修改为的name sore:\n");
scanf("%s %f",name,&sore);
while(getchar() != 10);
sprintf(act,"update zoo set name='%s',sore=%f where id=%d", name, sore,id);
if(sqlite3_exec(db, act, NULL, NULL, &errmsg) != SQLITE_OK)
{
fprintf(stderr, "line:%d sqlite3_exec:%s\n", __LINE__, errmsg);
return -1;
}
printf("database updata success\n");
}
return 0;
}
int callback(void *arg, int column, char** column_text, char** column_name) //void* arg = &flag
{
//列数
// printf("column=%d __%d__\n", column, __LINE__);
//column_name
int i = 0;
if(0 == *(int*)arg)
{
for(i=0; i<column; i++)
{
printf("%s\t", column_name[i]);
}
printf("\n");
*(int*)arg = 1;
}
//column_text
for(i=0; i<column; i++)
{
printf("%s\t", column_text[i]);
}
printf("\n");
return 0;
}
int do_select(sqlite3* db)
{
char act[128];
char **pres = NULL;
int row,column;
char* errmsg = NULL;
int flag = 0;
bzero(act,sizeof(act));
sprintf(act,"select * from zoo;");
/*if(sqlite3_exec(db, act, callback, &flag, &errmsg) != SQLITE_OK)
{
fprintf(stderr, "line:%d sqlite3_exec:%s\n", __LINE__, errmsg);
return -1;
}*/
if(sqlite3_get_table(db,act,&pres,&row,&column,&errmsg) != SQLITE_OK)
{
fprintf(stderr, "line:%d sqlite3_exec:%s\n", __LINE__, errmsg);
return -1;
}
int i = 0;
for(i=0; i<(row+1)*column;i++)
{
printf("%s\t",pres[i]);
if(i%column == column-1)
putchar(10);
}
/*
int line,list;
int i = 0;
for(line=0; line<row+1; line++)
{
for(list=0; list<column;list++)
{
printf("%s\t",pres[i]);
i++;
}
putchar(10);
}
*/
printf("database select success\n");
sqlite3_free_table(pres);
return 0;
}
数据库增删改查
于 2022-11-28 21:29:23 首次发布