Linux学习笔记之数据库

环境安装

1. 安装DBMS,这里使用SQLite3

   sudo aptitude install sqlite3 
   

2. 安装库文件，不然编译c文件的时候会找不到头文件

   sudo apt-get install libsqlite3-dev 
   

笔记

学生信息管理系统示例代码

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

#define DATABASE "student.db"
char *errmsg;

int do_insert(sqlite3 * db)
{
    int id;
    char name[32] = {};
    int score;
    char sql[128] = {};
    printf("Input id:");    scanf("%d", &id);   getchar();
    printf("Input name:");    scanf("%s", name);   getchar();
    printf("Input score:");    scanf("%d", &score);   getchar();
    
    sprintf(sql, "insert into student values(%d,'%s',%d);", id, name, score);

    if(sqlite3_exec(db, sql, NULL, NULL, &errmsg) != SQLITE_OK)
    {
        printf("%s\n", errmsg);
    }
    else printf("inserted!\n");
    return 0;

}


int do_delete(sqlite3 * db)
{
    char sql[128] = {};
    int cmd;
    int id;
    char name[32]= {};
    int score;
    printf("delete by: 1.id 2.name 3.score\n");
    scanf("%d", &cmd);  getchar();
    switch(cmd){
        case 1:
            printf("id=");
            scanf("%d",&id);    getchar();
            sprintf(sql, "delete from student where id=%d;",id);
            break;
        case 2:
            printf("name=");
            scanf("%s", name); getchar();
            sprintf(sql, "delete from student where name='%s';", name);
            break;
        case 3:
            printf("score=");
            scanf("%d", &score); getchar();
            sprintf(sql, "delete from student where score=%d;", score);
            break;
        default:    printf("wrong cmd\n");
    
    }

    if(sqlite3_exec(db, sql, NULL, NULL, &errmsg) != SQLITE_OK)
    {
        printf("%s\n", errmsg);
    }
    else printf("deleted!\n");
    return 0;

}
int callback(void *para, int f_num, char **f_value,char **f_name)
{
    int i;
    for (i = 0; i < f_num; i++ )
        printf("%-11s", f_value[i]);
    putchar(10);
    return 0;
}

int do_query(sqlite3 * db)
{
    char sql[128] = {};
    //char *cmd;
    //printf("Select ... from student\n");
    //scanf("%s", cmd);  getchar();
    
    sprintf(sql, "select * from student");

    if(sqlite3_exec(db, sql, callback, NULL, &errmsg) != SQLITE_OK)
    {
        printf("%s\n", errmsg);
    }
    else printf("query done!\n");
    return 0;
}



int do_update(sqlite3 * db)
{
    char sql[128] = {};
    char *cmd;
    int id;
    int score;
    printf("id="); scanf("%d",&id); getchar();
    printf("new score="); scanf("%d", &score); getchar();
    sprintf(sql, "update student set score=%d where id=%d;", score, id);
    
    if(sqlite3_exec(db, sql, NULL, NULL, &errmsg) != SQLITE_OK)
    {
        printf("%s\n", errmsg);
    }
    else printf("updated!\n");
    return 0;
}



int main(int argc, const char *argv[])
{
    sqlite3 * db;
  //  char *errmsg;
    int cmd;

    if(sqlite3_open(DATABASE, &db) != SQLITE_OK)
    {
        printf("%s\n", sqlite3_errmsg(db));
        return -1;
    }
    
    //create a database

    if (sqlite3_exec(db,
    "create table student(id Interger, name char, score Interger);", 
    NULL, NULL, &errmsg) != SQLITE_OK)
    {
        printf("%s\n", errmsg);
    }
    else
    {
        printf("create table or open success.\n");
        sleep(1);
        fflush(stdout);
    }
    
    while(1)
    {
        printf("**************************************************\n");
        printf("1:insert 2:delete 3:query 4:update 5:quit.\n");
        printf("**************************************************\n");
        printf("Input command:");
        scanf("%d", &cmd); getchar();

        switch(cmd)
        {
            case 1:
                do_insert(db);    
                break;
            case 2:
                do_delete(db);    
                break;
            case 3:
                do_query(db);     
                break;
            case 4:
                do_update(db);    
                break;
            case 5:
                sqlite3_close(db);
                exit(0);
            default: printf("error cmd.\n");
        }
        fflush(stdout);
    }
    return 0;
}