一个简单用c和mysql的示范程序

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <mysql/mysql.h>
#include <signal.h>

#define  error(x) printf("ERROR %d: %s\n", mysql_errno(x), mysql_error(x))
#define SQL_LEN 256

/*
 * create table BEAL(
 * NAME CHAR(8) NOT NULL,
 * MONEY INTEGER NOT NULL,
 * DATE DATE NOT NULL,
 * TIME TIME NOT NULL
 * )
 */



MYSQL mysql;

void DEAL();
void insert(MYSQL *mysql);
void delete(MYSQL *mysql);
void show_user(MYSQL *mysql);
void show(MYSQL *mysql, char *name);
void help();
void bye();

void help()
{
        printf("\n------------------------------------------\n");
        printf("输入 0    : 显示用户\n");
        printf("输入 1    : 显示数据\n");
        printf("输入 2    : 插入数据\n");
        printf("输入 3    : 删除数据\n");
        printf("输入用户名: 显示该用户的数据\n");
        printf("Ctrl + C  : 退出\n");
        printf("------------------------------------------\n");

}

void bye()
{
        mysql_close(&mysql);
        printf("\nbye!\n");
        exit(0);
}

void insert(MYSQL *mysql)
{
        char sql[SQL_LEN];
        char name[8];
        float money;

        printf("插入格式: lulu 100\n");
        scanf("%s%f", name, &money);
        memset(sql, 0, SQL_LEN);
        sprintf(sql, "insert into DEAL values('%s', %f, curdate(), curtime())", name, money);
        printf("插入数据 [%s]\n", sql);

        if(mysql_real_query(mysql, sql, strlen(sql)))
        {
                error(mysql);
                return;
        }
}

void delete(MYSQL *mysql)
{
        char sql[SQL_LEN];
        char name[8];
        char date[16];
        char time[16];

        printf("删除格式: lulu 2012-11-06 09:45:10\n");
        scanf("%s%s%s", name, date, time);
        memset(sql, 0, SQL_LEN);
        sprintf(sql, "delete from DEAL where NAME = '%s' AND DATE = '%s' AND TIME = '%s'", name, date, time);

        printf("删除数据 [%s]\n", sql);

        if(mysql_real_query(mysql, sql, strlen(sql)))
        {
                error(mysql);
                return;
        }
}

void show(MYSQL *mysql, char *name)
{
        MYSQL_RES *res;
        MYSQL_ROW row;
        char sql[SQL_LEN];
        memset(sql, 0, SQL_LEN);
        if(name == NULL)
                sprintf(sql, "select * from DEAL");
        else
                sprintf(sql, "select * from DEAL where NAME = '%s'", name);
        printf("显示数据 [%s]\n", sql);

        if(mysql_real_query(mysql, sql, strlen(sql)))
        {
                error(mysql);
                return;
        }

        res = mysql_store_result(mysql);
        printf("+----------+------------+--------------+------------+\n");
        printf("|  %8s  |   %8s   |    %8s    |   %8s   |\n",  "用户", "金额", "日期", "时间");
        printf("+----------+------------+--------------+------------+\n");
        float sum = 0;
        while ((row = mysql_fetch_row(res)))
        {
                printf("|");
                int i;
                int num = mysql_num_fields(res);
                for(i = 0; i < num; i++)
                {
                        if(i == 1)
                                sum += atof(row[i]);
                        printf("%8s  |  ", row[i]);
                }
                printf("\n");
        }
        printf("+----------+------------+--------------+------------+\n");
        printf("%s 总金额: %.2f\n\n", (name == NULL ? "" : name), sum);
        mysql_free_result(res);
}

void show_user(MYSQL *mysql)
{
        MYSQL_RES *res;
        MYSQL_ROW row;
        char sql[SQL_LEN];
        memset(sql, 0, SQL_LEN);
                sprintf(sql, "select NAME from DEAL GROUP BY NAME");
        printf("显示数据 [%s]\n", sql);

        if(mysql_real_query(mysql, sql, strlen(sql)))
        {
                error(mysql);
                return;
        }

        res = mysql_store_result(mysql);
        printf("+----------+\n");
        printf("|  %8s  |\n",  "用户");
        printf("+----------+\n");
        float sum = 0;
        while ((row = mysql_fetch_row(res)))
        {
                printf("|");
                int i;
                int num = mysql_num_fields(res);
                for(i = 0; i < num; i++)
                {
                        if(i == 1)
                                sum += atof(row[i]);
                        printf("%8s  |  ", row[i]);
                }
                printf("\n");
        }
        printf("+----------+\n");
        mysql_free_result(res);
}

void DEAL()
{
        mysql_init(&mysql);
        if(!mysql_real_connect(&mysql, 
                                "localhost", "coolulu", "猜猜看", "BANK", 0, NULL, 0))
                error(&mysql);
        else
                printf("success connect...\n");
        help();
        char name[8];

        while(1)
        {
                printf("$ 输入数字或用户名:");
                scanf("%s", name);

                switch(name[0])
                {
                case '0':
                        show_user(&mysql);break;
                case '1':
                        show(&mysql, NULL);break;
                case '2':
                        insert(&mysql);break;
                case '3':
                        delete(&mysql);break;
                default:
                        show(&mysql, name);
                }

                memset(name, 0, strlen(name));
        }
}

int main()
{
        signal(SIGINT, bye);
        DEAL();
        return 0;
}

数据库叫 BANK

表 DEAL

1. /*
   
2.  * create table BEAL( 
3.  * NAME CHAR(8) NOT NULL, 
4.  * MONEY INTEGER NOT NULL, 
5.  * DATE DATE NOT NULL, 
6.  * TIME TIME NOT NULL 
7.  * ) 
8.  */ 
   

coolulu@ubuntu:~/code/mysql/BANK$ cat makefile
deal:
        gcc -o deal deal.c `mysql_config --cflags --libs`
clean:
        rm deal