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

  1 #include <stdio.h>
  2 #include <stdlib.h>
  3 #include <string.h>
  4 #include <mysql/mysql.h>
  5 #include <signal.h>
  6 
  7 #define  error(x) printf("ERROR %d: %s\n", mysql_errno(x), mysql_error(x))
  8 #define SQL_LEN 256
  9 
 10 /*
 11  * create table BEAL(
 12  * NAME CHAR(8) NOT NULL,
 13  * MONEY INTEGER NOT NULL,
 14  * DATE DATE NOT NULL,
 15  * TIME TIME NOT NULL
 16  * )
 17  */
 18 
 19 
 20 
 21 MYSQL mysql;
 22 
 23 void DEAL();
 24 void insert(MYSQL *mysql);
 25 void delete(MYSQL *mysql);
 26 void show_user(MYSQL *mysql);
 27 void show(MYSQL *mysql, char *name);
 28 void help();
 29 void bye();
 30 
 31 void help()
 32 {
 33         printf("\n------------------------------------------\n");
 34         printf("输入 0    : 显示用户\n");
 35         printf("输入 1    : 显示数据\n");
 36         printf("输入 2    : 插入数据\n");
 37         printf("输入 3    : 删除数据\n");
 38         printf("输入用户名: 显示该用户的数据\n");
 39         printf("Ctrl + C  : 退出\n");
 40         printf("------------------------------------------\n");
 41 
 42 }
 43 
 44 void bye()
 45 {
 46         mysql_close(&mysql);
 47         printf("\nbye!\n");
 48         exit(0);
 49 }
 50 
 51 void insert(MYSQL *mysql)
 52 {
 53         char sql[SQL_LEN];
 54         char name[8];
 55         float money;
 56 
 57         printf("插入格式: lulu 100\n");
 58         scanf("%s%f", name, &money);
 59         memset(sql, 0, SQL_LEN);
 60         sprintf(sql, "insert into DEAL values('%s', %f, curdate(), curtime())", name, money);
 61         printf("插入数据 [%s]\n", sql);
 62 
 63         if(mysql_real_query(mysql, sql, strlen(sql)))
 64         {
 65                 error(mysql);
 66                 return;
 67         }
 68 }
 69 
 70 void delete(MYSQL *mysql)
 71 {
 72         char sql[SQL_LEN];
 73         char name[8];
 74         char date[16];
 75         char time[16];
 76 
 77         printf("删除格式: lulu 2012-11-06 09:45:10\n");
 78         scanf("%s%s%s", name, date, time);
 79         memset(sql, 0, SQL_LEN);
 80         sprintf(sql, "delete from DEAL where NAME = '%s' AND DATE = '%s' AND TIME = '%s'", name, date, time);
 81 
 82         printf("删除数据 [%s]\n", sql);
 83 
 84         if(mysql_real_query(mysql, sql, strlen(sql)))
 85         {
 86                 error(mysql);
 87                 return;
 88         }
 89 }
 90 
 91 void show(MYSQL *mysql, char *name)
 92 {
 93         MYSQL_RES *res;
 94         MYSQL_ROW row;
 95         char sql[SQL_LEN];
 96         memset(sql, 0, SQL_LEN);
 97         if(name == NULL)
 98                 sprintf(sql, "select * from DEAL");
 99         else
100                 sprintf(sql, "select * from DEAL where NAME = '%s'", name);
101         printf("显示数据 [%s]\n", sql);
102 
103         if(mysql_real_query(mysql, sql, strlen(sql)))
104         {
105                 error(mysql);
106                 return;
107         }
108 
109         res = mysql_store_result(mysql);
110         printf("+----------+------------+--------------+------------+\n");
111         printf("|  %8s  |   %8s   |    %8s    |   %8s   |\n",  "用户", "金额", "日期", "时间");
112         printf("+----------+------------+--------------+------------+\n");
113         float sum = 0;
114         while ((row = mysql_fetch_row(res)))
115         {
116                 printf("|");
117                 int i;
118                 int num = mysql_num_fields(res);
119                 for(i = 0; i < num; i++)
120                 {
121                         if(i == 1)
122                                 sum += atof(row[i]);
123                         printf("%8s  |  ", row[i]);
124                 }
125                 printf("\n");
126         }
127         printf("+----------+------------+--------------+------------+\n");
128         printf("%s 总金额: %.2f\n\n", (name == NULL ? "" : name), sum);
129         mysql_free_result(res);
130 }
131 
132 void show_user(MYSQL *mysql)
133 {
134         MYSQL_RES *res;
135         MYSQL_ROW row;
136         char sql[SQL_LEN];
137         memset(sql, 0, SQL_LEN);
138                 sprintf(sql, "select NAME from DEAL GROUP BY NAME");
139         printf("显示数据 [%s]\n", sql);
140 
141         if(mysql_real_query(mysql, sql, strlen(sql)))
142         {
143                 error(mysql);
144                 return;
145         }
146 
147         res = mysql_store_result(mysql);
148         printf("+----------+\n");
149         printf("|  %8s  |\n",  "用户");
150         printf("+----------+\n");
151         float sum = 0;
152         while ((row = mysql_fetch_row(res)))
153         {
154                 printf("|");
155                 int i;
156                 int num = mysql_num_fields(res);
157                 for(i = 0; i < num; i++)
158                 {
159                         if(i == 1)
160                                 sum += atof(row[i]);
161                         printf("%8s  |  ", row[i]);
162                 }
163                 printf("\n");
164         }
165         printf("+----------+\n");
166         mysql_free_result(res);
167 }
168 
169 void DEAL()
170 {
171         mysql_init(&mysql);
172         if(!mysql_real_connect(&mysql, 
173                                 "localhost", "coolulu", "猜猜看", "BANK", 0, NULL, 0))
174                 error(&mysql);
175         else
176                 printf("success connect...\n");
177         help();
178         char name[8];
179 
180         while(1)
181         {
182                 printf("$ 输入数字或用户名:");
183                 scanf("%s", name);
184 
185                 switch(name[0])
186                 {
187                 case '0':
188                         show_user(&mysql);break;
189                 case '1':
190                         show(&mysql, NULL);break;
191                 case '2':
192                         insert(&mysql);break;
193                 case '3':
194                         delete(&mysql);break;
195                 default:
196                         show(&mysql, name);
197                 }
198 
199                 memset(name, 0, strlen(name));
200         }
201 }
202 
203 int main()
204 {
205         signal(SIGINT, bye);
206         DEAL();
207         return 0;
208 }

 

 

 

 

 1 create database BANK;
 2 
 3 use BANK;
 4 
 5 create table DEAL( 
 6 NAME CHAR(8) NOT NULL, 
 7 MONEY INTEGER NOT NULL, 
 8 DATE DATE NOT NULL, 
 9 TIME TIME NOT NULL 
10 );
11 
12 insert into DEAL values('A', 10, curdate(), curtime());
13 insert into DEAL values('B', 11, curdate(), curtime());
14 insert into DEAL values('C', 12, curdate(), curtime());
15 insert into DEAL values('D', 13, curdate(), curtime());
16 insert into DEAL values('E', 14, curdate(), curtime());
17 insert into DEAL values('F', 15, curdate(), curtime());

 

 

 

数据库叫 BANK

表 DEAL

  1. /*
  2.  * create table DEAL( 
  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

或者

 $gcc -o deal deal.c -I/usr/include/mysql -lmysqlclient -L/usr/lib/mysql

 

posted @ 2012-11-07 22:51  l_u_l_u  阅读(308)  评论(0)  编辑  收藏  举报