2024/4/26作业

小小花生可笑可笑

已于 2024-04-27 23:00:47 修改

阅读量114

点赞数 2

文章标签：数据库 c语言

于 2024-04-26 17:42:30 首次发布

本文链接：https://blog.csdn.net/2401_83485271/article/details/138225683

版权

数据库增删改查

#include <code.h>

int do_select(sqlite3 *db);
int select_callback(void *arg,int column,char **column_text,char**column_name);

int do_insert(sqlite3 *db);
int do_delete(sqlite3 *db);
int do_update(sqlite3 *db);


int main(int argc, const char *argv[])
{
    sqlite3 *db;
    if(sqlite3_open("./sq.db",&db)!=SQLITE_OK)
    {
        fprintf(stderr,"sqlite3_open:%s,errcode:%d __%d__\n",\
                sqlite3_errmsg(db),sqlite3_errcode(db),__LINE__);
        return -1;
    }
    printf("sq.db open success! \n");


    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,"sqlite3_exec:%s __%d__\n",errmsg,__LINE__);
        return -1;
    }
    printf("创建表格成功\n");
    int choose;

    while(1)
    {
        system("clear");
        printf("--------------------\n");
        printf("------1、插入 ------\n");
        printf("------2、删除 ------\n");
        printf("------3、修改 ------\n");
        printf("------4、查询 ------\n");
        printf("------5、退出 ------\n");
        printf("--------------------\n");
        printf("请输入>>>>>");
        scanf("%d",&choose);
        while(getchar()!=10);
        switch(choose)
        {
        case 1:
            do_insert(db);
            break;
        case 2:
            do_delete(db);
            break;
        case 3:
            do_update(db);
            break;
        case 4:
            do_select(db);
            break;
        case 5:
            return 0;
        defalut:
            printf("输入错误！\n"); printf("%s",sql);

        }

        printf("按下回车清屏>>>>\n");
        while(getchar()!=10);
    }


    if(sqlite3_close(db)!=SQLITE_OK)
    {
        fprintf(stderr,"sqlite3_close:%s,errcode:%d __%d__\n",\
                sqlite3_errmsg(db),sqlite3_errcode(db),__LINE__);
        return -1;
    }
    printf("%s",sql);
    printf("sq.db close success! \n");
    return 0;
}


int do_insert(sqlite3 *db)
{
    char sql[128] = "";
    char *errmsg = NULL;

    int id;
    char name[20];
    float score;
    printf("请按以下格式输入插入数据\nid name score\n");
    scanf("%d %s %f",&id,name,&score);

    sprintf(sql,"insert into stu values (%d,'%s',%f);",id,name,score);

    if(sqlite3_exec(db,sql,NULL,NULL,&errmsg)!=SQLITE_OK)
    {
        fprintf(stderr,"sqlite3_exec:%s __%d__\n",errmsg,__LINE__);
        return -1;
    }
    printf("插入数据成功\n");
    return 0;
}


int do_delete(sqlite3 *db)
{
    char sql[128] = "";
    char *errmsg = NULL;

    char limit[64];

    printf("请输入删除数据的限制条件>>>");
    scanf("%s",limit);
    sprintf(sql,"delete from stu where %s;",limit);
    if(sqlite3_exec(db,sql,NULL,NULL,&errmsg)!=SQLITE_OK)
    {
        fprintf(stderr,"sqlite3_exec:%s __%d__\n",errmsg,__LINE__);
        return -1;
    }
    printf("删除数据成功\n");
    return 0;
}

int do_update(sqlite3 *db)
{
    char sql[128];
    char *errmsg = NULL;

    char update[64];
    char limit[64];

    printf("请按照一下格式输入修改内容和限制条件\n<update>  <limiti>\n");

    scanf("%s %s",update,limit);

    sprintf(sql,"update stu set %s where %s;",update,limit);

    if(sqlite3_exec(db,sql,NULL,NULL,&errmsg)!=SQLITE_OK)
    {
        fprintf(stderr,"sqlite3_exec:%s __%d__\n",errmsg,__LINE__);
        return -1;
    }
    printf("修改数据成功\n");
    return 0;

}

int do_select(sqlite3*db)
{
    char sql[128] = "select *from stu;";
    char *errmsg = NULL;
    int flag = 0;

    if(sqlite3_exec(db,sql,select_callback,&flag,&errmsg)!=SQLITE_OK)
    {
        fprintf(stderr,"sqlite3_exec:%s __%d__\n",errmsg,__LINE__);
        return -1;
    }
    printf("查询成功\n");                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
    return 0;

}

int select_callback(void*arg,int column,char **column_text,char**column_name)
{
    if(0==*(int*)arg)
    {
        for(int i=0;i<column;i++)
        {
            printf("%s\t",column_name[i]);
        }
        putchar(10);
        *(int*)arg = 1;
    }

    for(int i= 0;i<column;i++)
    {
        printf("%s\t",column_text[i]);
    }
    putchar(10);
    return 0;
}

运行结果

将dict.txt导入到数据库中

#include <head.h>


int main(int argc, const char *argv[])
{
    sqlite3 *db = NULL;
    if(sqlite3_open("./sq.db", &db) != SQLITE_OK)
    {
        printf("sqlite3_open failed:%s __%d__\n", sqlite3_errmsg(db), __LINE__);
        return -1;
    }
    printf("创建表格 sq.db 成功 __%d__\n", __LINE__);

    char sql[128] = "create table if not exists sq (word char, mean char);";
    char* errmsg = NULL;

    if(sqlite3_exec(db, sql, NULL, NULL, &errmsg) != SQLITE_OK)
    {
        printf("sqlite3_exec failed:%s __%d__\n", errmsg, __LINE__);
        return -1;
    }
    printf("create table sq success __%d__\n", __LINE__);

    FILE* fp = fopen("./dict.txt", "r");
    if(NULL == fp)
    {
        perror("fopen");
        return -1;
    }
    char buf[256] = "";
    char word[32] = "";
    char mean[200] = "";
    int count = 1;
    int i = 0;

    while(1)
    {
        if(fgets(buf, sizeof(buf), fp) == NULL)
            break;
        buf[strlen(buf)-1] = 0;

        bzero(word, sizeof(word));                                                                                                       
        bzero(mean, sizeof(mean));
        for(i=0; i<strlen(buf)-2; i++)
        {
            if(buf[i] != ' ' && buf[i+1] == ' ' && buf[i+2]==' ')
            {
                strncpy(word, buf, i+1);
            }
            else if(buf[i]==' ' && buf[i+1]==' ' && buf[i+2]!=' ')
            {
                strcpy(mean, buf+i+2);
                break;
            }
        }
        sprintf(sql,"insert into sq values(\"%s\", \"%s\");", word, mean);
        printf("sql= %s\n", sql);

        if(sqlite3_exec(db, sql, NULL, NULL, &errmsg) != SQLITE_OK)
        {
            printf("sqlite3_exec failed:%s __%d__\n", errmsg, __LINE__);
            return -1;
        }
    }

    fclose(fp);

    if(sqlite3_close(db) != SQLITE_OK)
    {
        printf("sqlite3_close failed:%s __%d__\n", sqlite3_errmsg(db), __LINE__);
        return -1;
    }
    printf("slqite3 sq.db close success\n");

    return 0;
}

运行结果