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;
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_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;
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 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;
}