通讯录3.0（数据库编程）

三个文件 ： address_list.h address_list.c function.c

address_list.h

#ifndef _ADDRESS_LIST_H_
#define _ADDRESS_LIST_H_

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

enum return_val{
    CREATE_OK = 100,
    CREATE_NO,
};

extern void welcome();   //欢迎界面

extern void interface();  //登录界面

extern int create_address(sqlite3 **address);   //create database

extern void select_function(int *select);  //select function

extern void insert_data(sqlite3 *address);  //insert data

extern void display_data(sqlite3 *address);  //display data

extern void select_data(sqlite3 *address);  //select data

extern void delete_data(sqlite3 *address); //delete data
            
extern void empty_data(sqlite3 *address);  //empty data

extern void exit_address(sqlite3 *address);   //exit

#endif

address_list.c

#include "address_list.h"

int main(int argc, char *argv[])
{
    sqlite3 *address = NULL;
    int select;

    welcome();   //create welcome interface
    sleep(1);
    system("clear");

    interface();   //register interface


    if(CREATE_NO == create_address(&address))   //create database
    {
        return 0;
    }

    while (1)
    {
        select_function(&select);

        switch (select)
        {
        case 1:
            insert_data(address);  //insert data
            break;

        case 2:
            display_data(address);  //display data
            break;

        case 3:
            select_data(address);  //select data
            break;

        case 4:
            delete_data(address); //delete data
            break;

        case 5:
            empty_data(address);  //empty data
            break;

        case 6:
            exit_address(address);  //exit
            break;
        
        default:
            printf("    请重新输入！\n");
            break;
        }   

        if(select == 6)
        {
            break;
        }   
    }
    
    return 0;
}

function.c

#include "address_list.h"

void welcome()   //欢迎界面
{
    printf("    ");
    printf("*******************************************************");
    printf("\n\n");
 
    printf("                       Welcome you!");
    printf("\n\n");
 
    printf("    ");
    printf("*******************************************************");
    printf("\n\n");
}


//通讯录界面
void interface()
{
    printf("    ");
    printf("*******************************************************");
    printf("\n\n");
    
    printf("    ");
    printf("************************目录**************************");
    printf("\n\n");
    
    printf("    ");
    printf("******************* 1、输入数据 ***********************");
    printf("\n\n");
 
    printf("    ");
    printf("******************* 2、显示数据 ***********************");
    printf("\n\n");
 
    printf("    ");
    printf("******************* 3、查找数据 ***********************");
    printf("\n\n");
 
    printf("    ");
    printf("******************* 4、删除数据 ***********************");
    printf("\n\n");
 
    printf("    ");
    printf("******************* 5、清空数据 ***********************");
    printf("\n\n");
 
    printf("    ");
    printf("****************** 6、退出通讯录 **********************");
    printf("\n\n");
 
    printf("    ");
    printf("*******************************************************");
    printf("\n\n");
}

int create_address(sqlite3 **address)   //create database
{
    char *errmsg = NULL;
    char *sql = NULL;

    if(SQLITE_OK != sqlite3_open("database.db", address))
    {
        printf("    ");
        printf("open database error!%s\n", sqlite3_errmsg(*address));
        exit(EXIT_FAILURE);
    }

    sql = "create table if not exists mytable(id int primary key, name text, sex char(1), number varchar(11));";
    if(SQLITE_OK != sqlite3_exec(*address, sql, NULL, NULL, &errmsg))
    {
        printf("    ");
        printf("create table error!%s\n", errmsg);
        sqlite3_close(*address);
        exit(EXIT_FAILURE);
    }

    return CREATE_OK;
}

void select_function(int *select)    //select funtion
{
    printf("\n\n    ");
    printf("please select function number:");
    scanf("%d", select);
}

void insert_data(sqlite3 *address)  //insert data
{
    char sql[100];
    char *errmsg = NULL;
    int id;
    int i;
    int num;
    char name[10];
    char sex[5];
    char number[15];

    system("clear");
    interface();   //register interface

    printf("    ");
    printf("please input data number:");
    scanf("%d", &num);

    for(i = 0; i < num; i++)
    {
        printf("    ");
        printf("please input id, name, sex, number:\n");
        printf("    ");
        scanf("%d%s%s%s", &id, name, sex, number);
        getchar();

        sprintf(sql, "insert into mytable(id, name, sex, number) values (%d, '%s', '%s', '%s');", id, name, sex, number);

        if(SQLITE_OK != sqlite3_exec(address, sql, NULL, NULL, &errmsg))
        {
            printf("    ");
            printf("insert error!%s\n", errmsg);
            sqlite3_close(address);
            exit(EXIT_FAILURE);
        }
    }

}

void display_data(sqlite3 *address)  //display data
{
    char *sql = NULL;
    char *errmsg = NULL;
    char **result = NULL;
    int row_num;
    int col_num;
    int i;

    system("clear");
    interface();   //register interface

    sql = "select *from mytable;";
    if(SQLITE_OK != sqlite3_get_table(address, sql, &result, &row_num, &col_num, &errmsg))
    {
        printf("display error!%s\n", errmsg);
        sqlite3_close(address);
        exit(EXIT_FAILURE);
    }   
    else
    {
        for(i = 0; i < (row_num + 1)*col_num; i++)
        {
            printf("    ");
            printf("%6s", result[i]);

            if((i + 1)%col_num == 0)
            {
                printf("\n");
            }

        }
    } 

    sqlite3_free_table(result);
}

int display(void *para, int col_num, char **col_val, char **col_name)
{
    int i;
    int *flag = NULL;
    flag = (int *)para;

    if(0 == *flag)
    {
        *flag = 1;

        for(i = 0; i < col_num; i++)
        {
            printf("    ");
            printf("%6s", col_name[i]);
        }
        printf("\n");
    }

    for(i = 0; i < col_num; i++)
    {
        printf("    ");
        printf("%6s", col_val[i]);
    }
    printf("\n");

    return SQLITE_OK;
}

void select_data(sqlite3 *address)  //select data
{
    char sql[100];
    char *errmsg = NULL;
    int flag = 0;
    int id;

    system("clear");
    interface();   //register interface
    
    printf("    ");
    printf("please input id of select data:");
    scanf("%d", &id);

    sprintf(sql, "select *from mytable where id = %d;", id);
    if(SQLITE_OK != sqlite3_exec(address, sql, display, (void *)&flag, &errmsg))
    {
        printf("select data error!%s\n", errmsg);
        sqlite3_close(address);
        exit(EXIT_FAILURE);
    }
}

void delete_data(sqlite3 *address) //delete data
{
    char sql[100];
    char *errmsg = NULL;
    int flag = 0;
    char name[10];

    system("clear");
    interface();   //register interface
    
    printf("    ");
    printf("please input name of delete data:");
    scanf("%s", name);

    sprintf(sql, "delete from mytable where name = '%s';", name);
    if(SQLITE_OK != sqlite3_exec(address, sql, NULL, NULL, &errmsg))
    {
        printf("delete data error!%s\n", errmsg);
        sqlite3_close(address);
        exit(EXIT_FAILURE);
    }
    printf("    ");
    printf("删除成功！\n");
}

void empty_data(sqlite3 *address)  //empty data
{
    char *sql = NULL;
    char *errmsg = NULL;

    system("clear");
    interface();   //register interface

    sql = "delete from mytable;";
    if(SQLITE_OK != sqlite3_exec(address, sql, NULL, NULL, &errmsg))
    {
        printf("empty data error!%s\n", errmsg);
        sqlite3_close(address);
        exit(EXIT_FAILURE);
    }
    printf("    ");
    printf("清空成功！\n");
}

void exit_address(sqlite3 *address)   //exit
{
    system("clear");
    interface();   //register interface

    printf("    ");
    printf("退出成功！\n");
    sqlite3_close(address);
}