cとmysqlを簡単に使う模範プログラム
35678 ワード
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
", 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("
------------------------------------------
");
34 printf(" 0 :
");
35 printf(" 1 :
");
36 printf(" 2 :
");
37 printf(" 3 :
");
38 printf(" :
");
39 printf("Ctrl + C :
");
40 printf("------------------------------------------
");
41
42 }
43
44 void bye()
45 {
46 mysql_close(&mysql);
47 printf("
bye!
");
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
");
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]
", 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
");
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]
", 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]
", 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("+----------+------------+--------------+------------+
");
111 printf("| %8s | %8s | %8s | %8s |
", " ", " ", " ", " ");
112 printf("+----------+------------+--------------+------------+
");
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("
");
126 }
127 printf("+----------+------------+--------------+------------+
");
128 printf("%s : %.2f
", (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]
", 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("+----------+
");
149 printf("| %8s |
", " ");
150 printf("+----------+
");
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("
");
164 }
165 printf("+----------+
");
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...
");
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
* create table DEAL(
* MONEY INTEGER NOT NULL,
* TIME TIME NOT NULL
*/
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