1.把单词表导入数据库
#include <myhead.h>
int do_insert(sqlite3 *db);
int do_delete(sqlite3 *db);
int do_update(sqlite3 *db);
int do_select(sqlite3 *db);
int main(int argc, const char *argv[])
{
//创建并打开一个数据库
sqlite3 *db=NULL;
if(sqlite3_open("./dict.db", &db)!=SQLITE_OK)
{
printf("sqlite3 open failed __%d__\n",__LINE__);
return -1;
}
printf("打开数据库成功\n");
//创建一张表格
char sql[128]="create table if not exists dict (id int,word char, mean char)";
char *errmsg=NULL;
if(sqlite3_exec(db,sql,NULL,NULL,&errmsg)!=SQLITE_OK)
{
printf("sqlite3_exec : %s __%d__\n",errmsg,__LINE__);
return -1;
}
printf("创建词典 dict 成功\n");
char c=0;
while(1)
{
printf("=========================\n");
printf(" 1. 插入 \n");
printf(" 2. 删除 \n");
printf(" 3. 查询 \n");
printf(" 4. 退出 \n");
printf("=========================\n");
printf("请输入>>>");
c=getchar();
while(getchar()!=10);
switch(c)
{
case '1':
do_insert(db);
break;
case '2':
do_delete(db);
break;
case '3':
do_select(db);
break;
case '4':
goto END;
default:
printf("输入错误,请重新输入\n");
}
}
END :
//关闭数据库
if(sqlite3_close(db)!=SQLITE_OK)
{
printf("sqlite3 close failed __%d__\n",__LINE__);
return -1;
}
printf("关闭数据库成功\n");
return 0;
}
int do_insert(sqlite3 *db)
{
char *errmsg=NULL;
char buf[128]="";
char word[128]="";
char mean[128]="";
char sql[128]="";
char *p=buf;
char *q=word;
char *s=mean;
int id=1;
//打开文件
FILE *fp;
fp=fopen("./dict.txt","r");
if(fp==NULL)
{
perror("open file");
return -1;
}
//读取字典内容
while(1)
{
//重置
bzero(buf,sizeof(buf));
bzero(word,sizeof(word));
bzero(mean,sizeof(mean));
p=buf;
q=word;
s=mean;
if(fgets(buf,sizeof(buf),fp)==NULL)
{
break;
}
buf[strlen(buf)-1]=0;
//获取单词
while(*p!=' '||*(p+1)!=' ')
{
*(q++)=*(p++);
}
//获取中文
p+=3;
while(*p!=0)
{
*(s++)=*(p++);
}
//插入数据库词典
sprintf(sql,"insert into dict values(%d,\"%s\",\"%s\")",id++,word,mean);
if(sqlite3_exec(db,sql,NULL,NULL,&errmsg)!=SQLITE_OK)
{
printf("sqlite3_exec : %s __%d__\n",errmsg,__LINE__);
return -1;
}
printf("插入成功!\n");
}
printf("写入完毕!\n");
//关闭文件
if(fclose(fp)!=0)
{
perror("close file");
}
return 0;
}
int do_delete(sqlite3 *db)
{
char *errmsg=NULL;
int id;
printf("请输入需要删除的单词序号\n");
scanf("%d",&id);
getchar();
char sql[128]="";
sprintf(sql,"delete from stu where id=%d",id);
if(sqlite3_exec(db,sql,NULL,NULL,&errmsg)!=SQLITE_OK)
{
printf("sqlite3_exec : %s __%d__\n",errmsg,__LINE__);
return -1;
}
return 0;
}
/*int do_update(sqlite3 *db){{{
{
char *errmsg=NULL;
int id;
printf("请输入需要修改的记录\n");
scanf("%d",&id);
getchar();
printf("请输入需要修改的值 :");
scanf("%s",word);
getchar();
char sql[128]="";
sprintf(sql,"update stu set word=\"%s\" where id=%d",word,id);
if(sqlite3_exec(db,sql,NULL,NULL,&errmsg)!=SQLITE_OK)
{
printf("sqlite3_exec : %s __%d__\n",errmsg,__LINE__);
return -1;
}
return 0;
}*//*}}}*/
/*{{{
int callback(void* arg,int n_column,char** column_value,char** column_name) //void * para=&flag{{{
{
if(*(int *)arg==0)
{
for(int i=0;i<n_column;i++)
{
printf("%s\t",column_name[i]);
}
puts("");
*(int*)arg=1;
}
for(int i=0;i<n_column;i++)
{
printf("%s\t",column_value[i]);
}
puts("");
return 0;
}
int do_select(sqlite3 *db)
{
char *errmsg=NULL;
int id,flag=0;
char name[10];
float score;
printf("请输入需要查找的记录\n");
scanf("%d",&id);
getchar();
char sql[128]="";
sprintf(sql,"select * from stu where id=%d",id);
if(sqlite3_exec(db,sql,callback,&flag,&errmsg)!=SQLITE_OK)
{
printf("sqlite3_exec : %s __%d__\n",errmsg,__LINE__);
return -1;
}
return 0;
}*//*}}}*//*}}}*/
int do_select(sqlite3 *db)
{
char sql[128]="select * from dict";
int row,column;
char **pres=NULL;
char *errmsg=NULL;
if(sqlite3_get_table(db,sql,&pres,&row,&column,&errmsg)!=SQLITE_OK)
{
printf("sqlite3_get_table: __%d__\n",__LINE__);
}
printf("row=%d column=%d\n",row,column);
for(int i=0;i<(row+1)*column;i++)
{
printf("%s\t",pres[i]);
if((i+1)%column==0)
puts("");
}
sqlite3_free_table(pres);
pres=NULL;
printf("查询成功\n");
return 0;
}