作业1
题目
完成数据库的增删改查
效果
增
删
改
代码
#include <sqlite3.h>
#include <stdio.h>
#include <string.h>
#include <uuid/uuid.h>
#define MAXSIZE 1024
int do_insert(sqlite3 *db)
{
char sql[MAXSIZE] = "";
char name[MAXSIZE] = "";
float score = -1;
char *errmsg = NULL;
uuid_t uuid;
char id[36];
uuid_generate(uuid);
uuid_unparse(uuid, id);
// 初始化
printf("请输入该学生的姓名: ");
fgets(name, sizeof(name), stdin);
name[strlen(name) - 1] = '\0';
printf("请输入该学生的成绩: ");
scanf("%f", &score);
sprintf(sql, "insert into stu values('%s','%s',%f);", id, name, score);
// 使用命令
if (sqlite3_exec(db, sql, NULL, NULL, &errmsg) != SQLITE_OK) {
printf("[line:%d] sqlite3_exec: %s\n", __LINE__, sqlite3_errmsg(db));
return -1;
}
printf("sqlite3_exec success\n");
return 0;
};
int do_delete(sqlite3 *db)
{
char sql[MAXSIZE] = "";
char *errmsg = NULL;
char id[MAXSIZE];
// 初始化
printf("请输入要删除的表单id号: ");
scanf("%s", id);
sprintf(sql, "delete from stu where id = '%s';", id);
// 使用命令
if (sqlite3_exec(db, sql, NULL, NULL, &errmsg) != SQLITE_OK) {
printf("[line:%d] sqlite3_exec: %s\n", __LINE__, sqlite3_errmsg(db));
return -1;
}
printf("sqlite3_exec success\n");
return 0;
}
int do_modify(sqlite3 *db)
{
char sql_name[MAXSIZE] = "";
char sql_score[MAXSIZE] = "";
char *errmsg = NULL;
char id[MAXSIZE];
char name[MAXSIZE];
float score;
// 初始化
printf("请输入要修改的表单id号: ");
scanf("%s", id);
printf("请输入要修改的姓名: ");
scanf("%s", name);
printf("请输入要修改的成绩: ");
scanf("%f", &score);
// sprintf(sql_name, "UPDATE stu SET name = '%s' score = %f where id = '%s';", name, score, id);
sprintf(sql_name, "UPDATE stu SET name = '%s' where id = '%s';", name, id);
sprintf(sql_score, "UPDATE stu SET score = %f where id = '%s';", score, id);
// 使用命令
if (sqlite3_exec(db, sql_name, NULL, NULL, &errmsg) != SQLITE_OK) {
printf("[line:%d] sqlite3_exec: %s\n", __LINE__, sqlite3_errmsg(db));
return -1;
}
if (sqlite3_exec(db, sql_score, NULL, NULL, &errmsg) != SQLITE_OK) {
printf("[line:%d] sqlite3_exec: %s\n", __LINE__, sqlite3_errmsg(db));
return -1;
}
printf("sqlite3_exec success\n");
return 0;
}
/*
exec的回调
void *arg 可以接参数
int column 列数
char **sqlite3_column_text 列内容
*/
int select_callback(void *arg, int ncolumn, char **column_text, char **column_name)
{
// 表头
if (*(int *)arg == 0) {
for (int i = 0; i < ncolumn; i++) {
if (i > 0)
printf("\t\t%s", column_name[i]);
else
printf("\t\t%s\t", column_name[i]);
}
printf("\n-----------------------------------------------------------------\n");
*(int *)arg = 1;
}
// 查询到的结果内容
for (int i = 0; i < ncolumn; i++) {
if (i > 0)
printf("%s\t\t", column_text[i]);
else
printf("%s\t", column_text[i]);
}
putchar('\n');
return 0;
}
int do_select(sqlite3 *db)
{
char sql[MAXSIZE] = "SELECT * FROM stu;";
char *errmsg = NULL;
int flag = 0;
// 使用命令
if (sqlite3_exec(db, sql, select_callback, &flag, &errmsg) != SQLITE_OK) {
printf("[line:%d] sqlite3_exec: %s\n", __LINE__, sqlite3_errmsg(db));
return -1;
}
printf("sqlite3_exec success\n");
return 0;
}
int main(int argc, char const *argv[])
{
/*打开*/
sqlite3 *db = NULL;
if (sqlite3_open("./my.db", &db) != SQLITE_OK) {
printf("[line:%d] sqlite3_open: %s\n", __LINE__, sqlite3_errmsg(db));
return -1;
}
printf("sqlite3_open success\n");
/*
创建表单
*/
char sql[MAXSIZE] = "create table if not exists stu (id char PRIMARY KEY, name char, score float)";
char *errmsg = NULL;
// 会自动申请一个空间给errmsg
if (sqlite3_exec(db, sql, NULL, NULL, &errmsg) != SQLITE_OK) {
printf("[line:%d] sqlite3_exec: %s\n", __LINE__, sqlite3_errmsg(db));
return -1;
}
printf("sqlite3_exec success\n");
/* 查询结构 */
char c = 0;
while (1) {
printf("---------------------\n");
printf("-----学生信息管理----\n");
printf("-------1. 插入-------\n");
printf("-------2. 删除-------\n");
printf("-------3. 修改-------\n");
printf("-------4. 查询-------\n");
printf("-------5. 退出-------\n");
printf("---------------------\n");
printf("请输入>>> ");
c = getchar();
while (getchar() != '\n')
;
switch (c) {
case '1':
do_insert(db);
break;
case '2':
do_delete(db);
break;
case '3':
do_modify(db);
break;
case '4':
do_select(db);
break;
case '5':
goto END;
break;
default:
printf("输入错误,请重新输入\n");
break;
}
printf("输入任意字符清屏>>>\n");
while (getchar() != '\n')
;
}
END:
/*关闭*/
if (sqlite3_close(db) != SQLITE_OK) {
printf("[line:%d] sqlite3_close: %s\n", __LINE__, sqlite3_errmsg(db));
}
printf("sqlite3_close success\n");
db = NULL;
return 0;
}