#include <myhead.h>
void Insert_Record(sqlite3* ppDb); // 插入记录
void Delete_Record(sqlite3* ppDb); // 删除记录
void Update_Record(sqlite3* ppDb); // 修改记录
int main(int argc, const char *argv[])
{
//1、定义一个数据库句柄指针
    sqlite3 * ppDb = NULL;

    //2、创建或打开数据库
    if(sqlite3_open("./mydb.db", &ppDb) != SQLITE_OK)
    {
        printf("sqlite3_open error:%s,errcode = %d\n", sqlite3_errmsg(ppDb), sqlite3_errcode(ppDb));
        return -1;
    }
    printf("数据库打开成功\n");

    //3、创建数据表
    //3.1 准备sql语句
    char sql[128] = "create table if not exists Stu(numb int, name char, sex char, socre double);";
    char *errmsg = NULL;

    //3.2 执行sql语句
    if(sqlite3_exec(ppDb, sql, NULL, NULL, &errmsg) != SQLITE_OK)
    {
        printf("%s\n", errmsg);
        return -1;
    }
    printf("数据表创建成功\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:
            break;
        case 5:
            exit(EXIT_SUCCESS);
            break;
        default:
            printf("\n输入格式错误!!!\n请在功能1 2 3 4 5中选择!!!\n\n");
            sleep(1);
            break;
        }
    }

    //关闭数据库
    sqlite3_close(ppDb);
	return 0;
}
// 插入记录
void Insert_Record(sqlite3* ppDb)
{
    int numb;
	char sex[16];
    char name[20];
    double score;
    char sql[128];
    char* errmsg = NULL;
    printf("请输入学号:");
    scanf("%d", &numb);   
	printf("请输入姓名:");
    scanf("%s", name);
	printf("请输入性别:");
	scanf("%s", sex);
    printf("请输入分数:");
    scanf("%lf", &score);
    sprintf(sql, "INSERT INTO stu VALUES (%d,\"%s\",\"%s\",%lf);", numb, name, sex, score);
    if (sqlite3_exec(ppDb, sql, NULL, NULL, &errmsg) != SQLITE_OK) 
	{
        printf("insert record error : %s\n", errmsg);
        return;
    }
    printf("插入成功\n");
}
 
// 删除记录
void Delete_Record(sqlite3* ppDb)
{
    char sql[128];
    int numb;
    char* errmsg = NULL;
    printf("请输入删除记录的numb值:");
    scanf("%d", &numb);
    sprintf(sql, "DELETE FROM stu WHERE numb=%d", numb);
    if (sqlite3_exec(ppDb, sql, NULL, NULL, &errmsg) != SQLITE_OK) {
        printf("delete record error %s\n", errmsg);
        return;
    }
    printf("删除成功\n");
}
 
// 修改记录
void Update_Record(sqlite3* ppDb)
{
    int choice;
    char sql[128];
    int numb;
    char name[20];
	char sex[20];
    int limit_numb;
    double score;
    char* errmsg = NULL;
    printf("请输入修改记录的numb值:");
    scanf("%d", &limit_numb);
 
    printf("\n---------------------\n");
    printf("--------1. numb--------\n");
    printf("-------2. name-------\n");
    printf("-------3. sex-------\n");
	printf("-------4. score------\n");
    printf("---------------------\n\n");
 
    printf("请选择要修改记录的字段>>> ");
    scanf("%d", &choice);
    if (choice == 1)
	{
        printf("请输入修改后的numb:");
        scanf("%d", &numb);
        sprintf(sql, "UPDATE stu SET numb=%d WHERE numb=%d", numb, limit_numb);
    }
	else if (choice == 2)
	{
        printf("请输入修改后的name:");
        scanf("%s", name);
        sprintf(sql, "UPDATE stu SET name=\"%s\" WHERE numb=%d", name, limit_numb);
    }
	else if (choice == 3)
	{
        printf("请输入修改后的sex:");
        scanf("%s", sex);
        sprintf(sql, "UPDATE stu SET sex=\"%s\" WHERE numb=%d", sex, limit_numb);
    }

	else if (choice == 4)
	{
        printf("请输入修改后的score:");
        scanf("%lf", &score);
        sprintf(sql, "UPDATE stu SET score=%lf WHERE numb=%d", score, limit_numb);
    }
 
    if (sqlite3_exec(ppDb, sql, NULL, NULL, &errmsg) != SQLITE_OK) {
        printf("update record error: %s\n", errmsg);
        return;
    }
    printf("修改成功\n");
}