一个简单用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
- /*
- * create table DEAL(
- * NAME CHAR(8) NOT NULL,
- * MONEY INTEGER NOT NULL,
- * DATE DATE 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
