4月13日-CSDN博客

本文链接：https://blog.csdn.net/weixin_73148834/article/details/130139078

1.字典加入数据库 2.数据库的增删改查

#include<stdio.h>
#include<string.h>
#include<sqlite3.h>
#include<unistd.h>
#include<sys/types.h>
#include<fcntl.h>
#include<sys/stat.h>
int main(int argc, const char *argv[])
{
   //如果数据库不存在就创建一个，创建后打开
   sqlite3* db = NULL;
   if (sqlite3_open("./dirt.db", &db) != SQLITE_OK)
   {
       fprintf(stderr, "sqlite3_open : %s errcode : %d\n", sqlite3_errmsg(db), sqlite3_errcode(db));
       return -1;
   }
   printf("sqlite3_open success\n");

//创建一个表

char create_db_shell[128] = "create table if not exists dirt (ENGLISH char, CHINESE char)";
char* errmsg = NULL;

   if (sqlite3_exec(db, create_db_shell, NULL, NULL, NULL) != SQLITE_OK)
   {
       fprintf(stderr, "sqlite3_exec : %s errcode : %d\n", sqlite3_errmsg(db), sqlite3_errcode(db));
       return -1;
   }
   printf("create teble success\n");

   FILE* fd = fopen("./dict.txt", "r");
   if (NULL == fd)
   {
       perror("open");
       return -1;
   }

   char buf_english[64] = "";
   char buf_chinese[64] = "";
   char buf_english_insert[64] = "";
   char buf_chinese_insert[64] = "";
   char insert_db[256] = "";
   char temp = 0;
   int i=0;

int num = 7988;

   while (num > 0)
   {
       bzero(insert_db, sizeof(insert_db));

bzero(buf_english_insert, sizeof(buf_english_insert));
bzero(buf_chinese_insert, sizeof(buf_chinese_insert));

bzero(buf_english, sizeof(buf_english));
bzero(buf_chinese, sizeof(buf_chinese));

i = 0;
temp = 0;

       while (temp != 32)
       {
           temp = fgetc(fd);
           buf_english[i] = temp;
           i++;
       }

fseek(fd, 2, SEEK_CUR);

fgets(buf_chinese ,sizeof(buf_chinese), fd);
buf_chinese[strlen(buf_chinese)-1] = 0;

sprintf(buf_english_insert, "'%s'", buf_english);
sprintf(buf_chinese_insert, "'%s'", buf_chinese);

       sprintf(insert_db, "insert into dirt values (%s, %s);", buf_english_insert, buf_chinese_insert);

       printf("sqlite3_shell = %s\n", insert_db);

char* errmsg = NULL;

       if (sqlite3_exec(db, insert_db, NULL, NULL, &errmsg) != SQLITE_OK)
       {
           fprintf(stderr, "line: __%d__ sqlite3_exec: %s\n", __LINE__, errmsg);
           return -1;
       }

num--;
}

   if (sqlite3_close(db) != SQLITE_OK)
   {
       fprintf(stderr, "sqlite3_close : %s errcode : %d\n", sqlite3_errmsg(db), sqlite3_errcode(db));
       return -1;
   }

return 0;
}

#include<stdio.h>
#include<string.h>
#include<sqlite3.h>
#include<unistd.h>
#include<stdlib.h>

int do_insert(sqlite3* db)
{
   int id = -1;
   char name1[32] = "";
   float score = -1;
   char sqlite3_shell[512] = "";

   printf("请输入插入项的id>>>\n");
   scanf("%d", &id);
   printf("请输入插入项的name>>>\n");
   scanf("%s", name1);
   printf("请输入插入项的分数>>>\n");
   scanf("%f", &score);

   char name[32] = "";
   sprintf(name, "'%s'", name1);
   sprintf(sqlite3_shell, "insert into stu values (%d, %s, %f);", id, name, score);
   printf("sqlite3_shell = %s\n", sqlite3_shell);

char* errmsg = NULL;

   if (sqlite3_exec(db, sqlite3_shell, NULL, NULL, &errmsg) != SQLITE_OK)
   {
       fprintf(stderr, "line: __%d__ sqlite3_exec: %s\n", __LINE__, errmsg);
       return -1;
   }
   printf("insert table success\n");
   return 0;
}

int do_delete(sqlite3* db)
{
int id = -1;
char sqlite3_shell[512] = "";

printf("请输入删除项的id>>>\n");
scanf("%d", &id);

sprintf(sqlite3_shell, "delete from stu WHERE id=%d;", id);
printf("sqlite3_shell = %s\n", sqlite3_shell);

char* errmsg = NULL;

int do_search(sqlite3* db)
{
char sqlite3_shell[512] = "select * from stu;";

char* errmsg = NULL;

   if (sqlite3_exec(db, sqlite3_shell, NULL, NULL, &errmsg) != SQLITE_OK)
   {
       fprintf(stderr, "line: __%d__ sqlite3_exec: %s\n", __LINE__, errmsg);
       return -1;
   }
   printf("search table success\n");
   return 0;
}

int do_change(sqlite3* db)
{
   printf("请输入要修改的学生>>> \n");
   char name1[32] = "";
   scanf("%s", name1);
   char name[32] = "";
   sprintf(name, "'%s'", name1);

   printf("请输入修改项的分数>>>\n");
   int score = -1;
   scanf("%d", &score);

char sqlite3_shell[512] = "";
sprintf(sqlite3_shell, "update stu set score = %d WHERE name = %s;", score, name);

printf("sqlite3_shell = %s\n", sqlite3_shell);

char* errmsg = NULL;

int main(int argc, const char *argv[])
{
   //如果数据库不存在，则创建后打开
   //如果存在就直接打开
   sqlite3* db = NULL;
   if (sqlite3_open("./my.db", &db) != SQLITE_OK)
   {
       fprintf(stderr, "sqlite3_open : %s errcode : %d\n", sqlite3_errmsg(db), sqlite3_errcode(db));
       return -1;
   }
   printf("sqlite3_open success\n");

   //创建一个表
   //注意: c代码中编写的sql语句在数据库中编写的一致

   char sql[128] = "create table if not exists 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("请输入>>> \n");
       c = getchar();
       while (getchar() != 10);

       switch(c)
       {
       case '1':
           do_insert(db);
           break;
       case '2':
           do_delete(db);
           break;
       case '3':
           do_change(db);
           break;
       case '4':
           do_search(db);
           break;
       case '5':
           exit(0);
           break;
       default:
           printf("输入有误,重新输入\n");
       }
       printf("输入任意字符清屏\n");
       while (getchar() != 10);
   }

   //关闭数据库
   if (sqlite3_close(db) != SQLITE_OK)
   {
       fprintf(stderr, "sqlite3_close : %s errcode : %d\n", sqlite3_errmsg(db), sqlite3_errcode(db));
       return -1;
   }
   return 0;
}