完成数据库的插入记录、删除记录、修改记录。
代码如下:
#include <sqlite3.h>
#include <stdio.h>
#include <stdlib.h>
#include <unistd.h>
void Insert_Record(sqlite3* ppDb); // 插入记录
void Delete_Record(sqlite3* ppDb); // 删除记录
void Update_Record(sqlite3* ppDb); // 修改记录
int main(int argc, char const* argv[])
{
// 创建并打开数据库
sqlite3* ppDb = NULL;
if (sqlite3_open("./my.db", &ppDb) != SQLITE_OK) {
printf("[line : %d] sqlite3_open error : [errcode: %d] %s\n",
__LINE__, sqlite3_errcode(ppDb), sqlite3_errmsg(ppDb));
return -1;
}
printf("sqlite_open successful\n");
// 创建表格
// 在数据库中怎么写,在C代码中就怎么写
char sql[128] = "create table if not exists stu(id int primary key,name char,score float);";
char* errmsg = NULL;
if (sqlite3_exec(ppDb, sql, NULL, NULL, &errmsg) != SQLITE_OK) {
printf("[line : %d] create table error : %s\n", __LINE__, errmsg);
return -1;
}
printf("create table stu successful\n");
int choice;
while (1) {
printf("\n---------------------\n");
printf("-------1. 插入-------\n");
printf("-------2. 删除-------\n");
printf("-------3. 修改-------\n");
printf("-------4. 查询-------\n");
printf("-------5. 退出-------\n");
printf("---------------------\n\n");
printf("请选择你要执行的功能>>> ");
scanf("%d", &choice);
switch (choice) {
case 1:
Insert_Record(ppDb);
break;
case 2:
Delete_Record(ppDb);
break;
case 3:
Update_Record(ppDb);
break;
case 4:
/* code */
break;
case 5:
exit(EXIT_SUCCESS);
break;
default:
printf("\n输入格式错误!!!\n请在功能1 2 3 4 5中选择!!!\n\n");
sleep(1);
break;
}
}
// 释放数据库内存空间
if (sqlite3_close(ppDb) != SQLITE_OK) {
printf("[line : %d] sqlite3_close error : [errcode: %d] %s\n",
__LINE__, sqlite3_errcode(ppDb), sqlite3_errmsg(ppDb));
return -1;
}
printf("sqlite_close successful\n");
ppDb = NULL;
return 0;
}
// 插入记录
void Insert_Record(sqlite3* ppDb)
{
int ID;
char name[20];
float score;
char sql[128];
char* errmsg = NULL;
printf("请输入ID值:");
scanf("%d", &ID);
printf("请输入姓名:");
scanf("%s", name);
printf("请输入分数:");
scanf("%f", &score);
sprintf(sql, "INSERT INTO stu VALUES (%d,\"%s\",%f);", ID, name, score);
if (sqlite3_exec(ppDb, sql, NULL, NULL, &errmsg) != SQLITE_OK) {
printf("[line : %d] insert record error : %s\n", __LINE__, errmsg);
return;
}
printf("insert record into stu successful\n");
}
// 删除记录
void Delete_Record(sqlite3* ppDb)
{
char sql[128];
int ID;
char* errmsg = NULL;
printf("请输入删除记录的ID值:");
scanf("%d", &ID);
sprintf(sql, "DELETE FROM stu WHERE id=%d", ID);
if (sqlite3_exec(ppDb, sql, NULL, NULL, &errmsg) != SQLITE_OK) {
printf("[line : %d] delete record error %s\n", __LINE__, errmsg);
return;
}
printf("delete record from stu successful\n");
}
// 修改记录
void Update_Record(sqlite3* ppDb)
{
int choice;
char sql[128];
int ID;
char name[20];
int limit_id;
float score;
char* errmsg = NULL;
printf("请输入修改记录的ID值:");
scanf("%d", &limit_id);
printf("\n---------------------\n");
printf("--------1. ID--------\n");
printf("-------2. name-------\n");
printf("-------3. score------\n");
printf("---------------------\n\n");
printf("请选择要修改记录的字段>>> ");
scanf("%d", &choice);
if (choice == 1) {
printf("请输入修改后的id:");
scanf("%d", &ID);
sprintf(sql, "UPDATE stu SET id=%d WHERE id=%d", ID, limit_id);
} else if (choice == 2) {
printf("请输入修改后的name:");
scanf("%s", name);
sprintf(sql, "UPDATE stu SET name=\"%s\" WHERE id=%d", name, limit_id);
} else if (choice == 3) {
printf("请输入修改后的score:");
scanf("%f", &score);
sprintf(sql, "UPDATE stu SET score=%f WHERE id=%d", score, limit_id);
}
if (sqlite3_exec(ppDb, sql, NULL, NULL, &errmsg) != SQLITE_OK) {
printf("[line : %d] update record error: %s\n", __LINE__, errmsg);
return;
}
printf("update record from stu successful\n");
}