sqlite3 操作
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sqlite3.h>
int table_add(sqlite3 *db);
int table_del(sqlite3 *db);
int table_change(sqlite3 *db);
int table_find(sqlite3 *db);
int main(int argc, const char *argv[])
{
sqlite3 *db = NULL;
if (sqlite3_open("mysqlite.db", &db) != SQLITE_OK)
{
printf("sqlite3_open: %s %d\n", sqlite3_errmsg(db), sqlite3_errcode(db));
return -1;
}
printf("数据库打开成功\n");
char buf[128] = "create table if not exists stu(id int,name char)";
char *errmsg = NULL;
if (sqlite3_exec(db, buf, NULL, NULL, &errmsg) != SQLITE_OK)
{
printf("表格创建失败:%s\n", errmsg);
return -1;
}
char c;
while (1)
{
printf("------------\n");
printf("--1.增--\n");
printf("--2.删--\n");
printf("--3.改--\n");
printf("--4.查--\n");
printf("--5.退--\n");
printf("------------\n");
printf("请输入选项>>>: \t");
c = getchar();
while (getchar() != 10)
;
switch (c)
{
case '1':
table_add(db);
break;
case '2':
table_del(db);
break;
case '3':
break;
case '4':
table_find(db);
break;
case '5':
return 0;
default:
printf("请重新输入\n");
continue;
break;
}
}
if (sqlite3_close(db) != SQLITE_OK)
{
printf("sqlite3_close: %s %d\n", sqlite3_errmsg(db), sqlite3_errcode(db));
return -1;
}
printf("数据库关闭成功\n");
return 0;
}
int table_add(sqlite3 *db)
{
char buf[128] = "", str[64] = "";
printf("请输入要插入的数据项(只支持全字段插入),如: 2, 'ls', 99 \n");
scanf("%s", str);
sprintf(buf, "%s%s%s%s", "insert into stu values ", "(", str, ")");
printf("%s\n", buf);
char *errmsg = NULL;
if (sqlite3_exec(db, buf, NULL, NULL, &errmsg) != SQLITE_OK)
{
printf("增加项失败:%s\n", errmsg);
return -1;
}
printf("插入成功");
return 0;
}
int table_del(sqlite3 *db)
{
char *errmsg = NULL;
char c = 0, buf[128] = "", str[64] = "";
while (1)
{
printf("请选择: 1.删除所有表格数据 2.删除表格数据 3.返回上一级");
c = getchar();
while (getchar() != 10)
;
switch (c)
{
case '1':
strcpy(buf, "delete from stu");
if (sqlite3_exec(db, buf, NULL, NULL, &errmsg) != SQLITE_OK)
{
printf("删除所有表格数据失败:%s\n", errmsg);
return -1;
}
printf("删除所有数据成功\n");
break;
case '2':
printf("请输入要删除的表格数据,如: id=1");
scanf("%s", str);
sprintf(buf, "%s%s", "DELETE FROM stu WHERE ", str);
if (sqlite3_exec(db, buf, NULL, NULL, &errmsg) != SQLITE_OK)
{
printf("删除指定表格数据失败:%s\n", errmsg);
return -1;
}
printf("删除置顶数据成功\n");
break;
case '3':
return 0;
default:
break;
}
}
return 0;
}
int table_find(sqlite3 *db)
{
char c = 0, buf[128] = "";
char *errmsg = NULL;
while (1)
{
printf("请输入需要查找的项目>>> 1.查看全部 2.查看某行 3.查看某列 4.返回上一层菜单\n");
c = getchar();
while (getchar() != 10)
;
switch (c)
{
case '1':
strcpy(buf, "select * from stu;");
printf("%s\n", buf);
if (sqlite3_exec(db, buf, NULL, NULL, &errmsg) != SQLITE_OK)
{
printf("查看全部失败:%s\n", errmsg);
return -1;
}
break;
case '2':
break;
case '3':
break;
case '4':
return 0;
default:
break;
}
}
}
int table_change(sqlite3 *db)
{
char buf[128] = "", str1[32] = "", str2[32] = "";
printf("请输入要修改的数值,如: score=60\n");
scanf("%s", str1);
printf("请输入限制条件,如: id=1\n");
scanf("%s", str2);
sprintf(buf, "%s%s%s%s", "update stu set ", str1, " where ", str2);
char *errmsg = NULL;
if (sqlite3_exec(db, buf, NULL, NULL, &errmsg) != SQLITE_OK)
{
printf("修改数值失败:%s\n", errmsg);
return -1;
}
printf("修改成功\n");
return 0;
}