请选择 进入手机版 | 继续访问电脑版
查看: 452|回复: 0

[云计算] C 语言操作 MySQL 数据库

688

主题

688

帖子

2083

积分

猿er

Rank: 1

积分
2083
发表于 2016-8-14 17:57:27
我的编译器是ubuntu ,函数的设计是根据自己的使用习惯来设计的,拓展方便,

本人还是新手望大神指教。。。
  1. #include <stdio.h>
  2. #include <stdlib.h>
  3. #include "/usr/include/mysql/mysql.h" //我的ubuntu下是在这个位置
  4. #include <memory.h>
  5. #include <string.h>
  6. #ifndef mysql_api_h
  7. #define mysql_api_h
  8. #define HOST "localhost"
  9. #define USERNAME "root"
  10. #define PASSWORD "244372551"
  11. #define DATABASE "test"
  12. typedef unsigned int U8;
  13. typedef char C2;
  14. typedef char * PC2;
  15. typedef enum EBool
  16. {
  17. EBool_false = 0,
  18. EBool_true = 1,
  19. EBool_debug = 1
  20. }EBool;
  21. typedef struct SSqlLinkList
  22. {
  23. PC2 name;
  24. PC2 value;
  25. struct SSqlLinkList *next;
  26. }SSqlLinkList;
  27. #endif
  28. MYSQL my_connection;
  29. /**
  30. * the function is initialise the linkList
  31. * @return <head node>
  32. */
  33. struct SSqlLinkList *InitialiazeSqlLinkList( SSqlLinkList *headPstr)
  34. {
  35. SSqlLinkList * head = NULL;
  36. head = ( SSqlLinkList *)malloc(sizeof(SSqlLinkList));
  37. if ( ! head)
  38. {
  39. printf("Error:the head node is empty !\n");
  40. exit(-1);
  41. }
  42. memset(head,0,sizeof(SSqlLinkList));
  43. head->value = NULL;
  44. head->name = NULL;
  45. head->next = NULL;
  46. headPstr = head;
  47. return headPstr;
  48. }
  49. /**
  50. * the function is get the input data and deposit into the linkList
  51. * @return <head node>
  52. */
  53. struct SSqlLinkList *CreateSqlLinkList( SSqlLinkList *headPstr)
  54. {
  55. SSqlLinkList * head;
  56. SSqlLinkList * newNode = NULL;
  57. C2 val[100];
  58. C2 names[20];
  59. printf("\n\nthe input end by input: ##\n\n");
  60. printf("please input name , value : ");
  61. scanf("%s%s",names,val);
  62. getchar(); //eat the enter
  63. head = headPstr;
  64. while( strcmp(val,"##") != 0 )
  65. {
  66. newNode = (SSqlLinkList *)malloc(sizeof(SSqlLinkList));
  67. if ( !newNode)
  68. {
  69. printf("Error:the insert node is emoty !\n");
  70. exit(-1);
  71. }
  72. newNode->name = (char *)malloc(sizeof(names));
  73. newNode->value = (char *)malloc(sizeof(val));
  74. head->next = newNode;
  75. strcpy(newNode->name,names);
  76. strcpy(newNode->value,val);
  77. printf("please input name , value : ");
  78. scanf("%s%s",names,val);
  79. getchar(); //eat the enter
  80. head = newNode;
  81. }
  82. return headPstr;
  83. }
  84. /**
  85. * the funtion is insert a node into the linkList
  86. * @return <head node>
  87. */
  88. struct SSqlLinkList *InsertNodeToSingleLinkList( SSqlLinkList *headPstr,PC2 names, PC2 data)
  89. {
  90. SSqlLinkList * newNode = NULL;
  91. SSqlLinkList * p = NULL;
  92. p = headPstr;
  93. if( p != NULL )
  94. {
  95. newNode = (SSqlLinkList *)malloc(sizeof(SSqlLinkList));
  96. if ( !newNode)
  97. {
  98. printf("Error:the insert node is emoty !\n");
  99. exit(-1);
  100. }
  101. if ( names != NULL )
  102. {
  103. newNode->name = (PC2)malloc(sizeof(names));
  104. strcpy(newNode->name,names);
  105. }
  106. else
  107. {
  108. strcpy(newNode->name,"\0");
  109. }
  110. if ( data != NULL )
  111. {
  112. newNode->value = (PC2)malloc(sizeof(data));
  113. strcpy(newNode->value,data);
  114. }
  115. else
  116. {
  117. strcpy(newNode->value,"\0");
  118. }
  119. newNode->next = p->next;
  120. p->next = newNode;
  121. }
  122. return headPstr;
  123. }
  124. /**
  125. * the function is printf the linkList
  126. */
  127. void DisplaySqlLinkList( SSqlLinkList *headPstr)
  128. {
  129. SSqlLinkList *p = NULL;
  130. p = headPstr->next;
  131. while( p != NULL )
  132. {
  133. printf("%10s:\t",p->name);
  134. printf("%s\t",p->value);
  135. p = p->next;
  136. printf("\n");
  137. }
  138. printf("\n");
  139. }
  140. /**
  141. * the function is free the struct memery
  142. */
  143. void DestoryLinkList( SSqlLinkList *headPstr )
  144. {
  145. SSqlLinkList * p = NULL;
  146. SSqlLinkList * head = NULL;
  147. head = (SSqlLinkList *) headPstr;
  148. p = head->next;
  149. while( p != NULL )
  150. {
  151. free(p->name);
  152. free(p->value);
  153. free(p);
  154. p = p->next;
  155. }
  156. free(head);
  157. printf("\033[33mfree the memery Ok!\033[0m\n");
  158. }
  159. /**
  160. * the function is connect the database
  161. * return <EBool>
  162. */
  163. EBool connect_sql()
  164. {
  165. EBool flag = EBool_false;
  166. //initialize the sql connect
  167. mysql_init(&my_connection);
  168. //create the database connect
  169. if ( NULL != mysql_real_connect( &my_connection, HOST, USERNAME, PASSWORD, DATABASE, 0, NULL, CLIENT_FOUND_ROWS ) )
  170. {
  171. //set the encoding
  172. mysql_query(&my_connection,"set names utf8");
  173. //printf("connect success!\n");
  174. flag = EBool_true;
  175. }
  176. else
  177. {
  178. mysql_close( &my_connection );
  179. printf("Sorry, the database connect fail! --%s\n",mysql_error(&my_connection));
  180. exit(-1);
  181. }
  182. return flag;
  183. }
  184. /**
  185. * the function is send the instruct to the database
  186. * @return <EBool>
  187. */
  188. EBool query( PC2 sql )
  189. {
  190. EBool flag = EBool_false;
  191. EBool debug = EBool_debug;
  192. if ( connect_sql())
  193. {
  194. if ( debug )
  195. {
  196. printf("\nquery: %s\n",sql);
  197. }
  198. return mysql_query(&my_connection, sql);
  199. }
  200. return flag;
  201. }
  202. /**
  203. * the function is get one row recordes from database
  204. * @param sql -> the sql sentence
  205. * @return <SSqlLinkList type>
  206. */
  207. struct SSqlLinkList *getOneRow( PC2 sql )
  208. {
  209. PC2 sql1;
  210. sql1 = sql;
  211. MYSQL_RES *res_ptr; /*指向查询结果的指针*/
  212. MYSQL_FIELD *field; /**/
  213. MYSQL_ROW result_row; /**/
  214. U8 colunm = 0;
  215. U8 i = 0,j = 0;
  216. SSqlLinkList *head = NULL;
  217. SSqlLinkList *p = NULL;
  218. head = InitialiazeSqlLinkList(head);
  219. if( ! query( sql1 ) )
  220. {
  221. res_ptr = mysql_store_result(&my_connection);
  222. if ( res_ptr )
  223. {
  224. colunm = mysql_num_fields(res_ptr);
  225. if ( result_row = mysql_fetch_row(res_ptr) )
  226. {
  227. while( field = mysql_fetch_field(res_ptr) )
  228. {
  229. InsertNodeToSingleLinkList(head,field->name,result_row[i]);
  230. i++;
  231. };
  232. }
  233. }
  234. }
  235. return head;
  236. }
  237. /**
  238. * the function is insert data into the database
  239. * @param table -> table name
  240. * @param datas -> the insert datas,the type is a pointer array
  241. * @param filed -> the table filed name,teh type is a pointer array
  242. * @param num -> the filed's number
  243. * @return <EBool>
  244. */
  245. EBool insert( C2 table[], PC2 datas[], PC2 filed[] ,U8 num )
  246. {
  247. EBool flag = EBool_false;
  248. U8 i = 0;
  249. U8 countSize = 0;
  250. C2 query_sql[] = "INSERT INTO ";
  251. C2 str1[] = " (";
  252. C2 str2[] = ")";
  253. C2 str3[] = ",";
  254. C2 str4[] = " VALUES(";
  255. C2 str5[] = "\'";
  256. PC2 *tempdatas;
  257. PC2 *tempfiled;
  258. tempdatas = datas;
  259. tempfiled = filed;
  260. if ( connect_sql())
  261. {
  262. //to calculate the length of the string size
  263. countSize = strlen(query_sql) + strlen(table) + strlen(str1)+ strlen(str2)*2 + strlen(str2)*num*2;
  264. for(i = 0; i < num; i++)
  265. {
  266. countSize = countSize + strlen(*tempfiled) + strlen(*tempdatas);
  267. tempdatas++;
  268. tempfiled++;
  269. }
  270. C2 sqls[countSize];
  271. sqls[0] = '\0';
  272. /*connect the insert sql sentence*/
  273. strcat(sqls,query_sql);
  274. strcat(sqls,table);
  275. strcat(sqls,str1);
  276. for ( i = 0; i < num; i++ )
  277. {
  278. strcat(sqls,*filed);
  279. if ( i+1 != num )
  280. {
  281. strcat(sqls,str3);
  282. }
  283. filed++;
  284. }
  285. strcat(sqls,str2);
  286. strcat(sqls,str4);
  287. for ( i = 0; i < num; i++ )
  288. {
  289. strcat(sqls,*datas);
  290. if ( i+1 != num )
  291. {
  292. strcat(sqls,str3);
  293. }
  294. datas++;
  295. }
  296. strcat(sqls,str2);
  297. if ( ! query(sqls) )
  298. {
  299. flag = EBool_true;
  300. }
  301. }
  302. return flag;
  303. }
  304. /**
  305. * the function is update the tables's data
  306. * @param table -> table name
  307. * @param value -> the insert datas
  308. * @param filed -> the table filed name
  309. * @param where -> the condition
  310. * @return <EBool>
  311. */
  312. EBool update( C2 table[],C2 filed[],C2 value[], C2 where[] )
  313. {
  314. EBool flag = EBool_false;
  315. C2 str0[] = "UPDATE ";
  316. C2 str1[] = " SET ";
  317. C2 str2[] = " WHERE ";
  318. C2 str3[] = " = ";
  319. U8 countSize = 0;
  320. if ( connect_sql() )
  321. {
  322. //to calculate the length of the string size
  323. countSize = strlen(str0) + strlen(str1) + strlen(str2) + strlen(str3) + strlen(table) + strlen(value)+ strlen(filed)+ strlen(where);
  324. C2 sqls[countSize];
  325. sqls[0] = '\0';
  326. //mosaic the sql
  327. strcat(sqls,str0);
  328. strcat(sqls,table);
  329. strcat(sqls,str1);
  330. strcat(sqls,filed);
  331. strcat(sqls,str3);
  332. strcat(sqls,value);
  333. strcat(sqls,str2);
  334. strcat(sqls,where);
  335. if ( ! query(sqls) )
  336. {
  337. flag = EBool_true;
  338. }
  339. }
  340. return flag;
  341. }
  342. /**
  343. * the function is delete the specially recorde
  344. * @param table[] -> the database's table name
  345. * @param where[] -> delete conditions
  346. * @return <EBool>
  347. */
  348. EBool deleteData( C2 table[], C2 where[] )
  349. {
  350. EBool flag = EBool_false;
  351. C2 str0[] = "DELETE FROM ";
  352. C2 str1[] = " WHERE ";
  353. U8 countSize = 0;
  354. if ( connect_sql() )
  355. {
  356. //to calculate the length of the string size
  357. countSize = strlen(table) + strlen(str0) + strlen(str1) + strlen(where);
  358. C2 sqls[countSize];
  359. sqls[0] = '\0';
  360. //mosaic the sql
  361. strcat(sqls,str0);
  362. strcat(sqls,table);
  363. strcat(sqls,str1);
  364. strcat(sqls,where);
  365. if ( ! query(sqls) )
  366. {
  367. flag = EBool_true;
  368. }
  369. }
  370. return flag;
  371. }
  372. int main()
  373. {
  374. PC2 querySql;
  375. U8 rets = 0;
  376. SSqlLinkList *head;
  377. querySql = "select * from user where Id = 1";
  378. head = getOneRow(querySql);
  379. DisplaySqlLinkList(head);
  380. DestoryLinkList(head);
  381. PC2 datas[] = {"\'tang\'","123456789"};
  382. PC2 filed[] = {"name","password"};
  383. //rets = insert("user",datas,filed,2);
  384. //
  385. rets = update("user","name","\'tang0pan\'","Id = 1");
  386. //printf("%d",rets);
  387. rets = deleteData("user", "Id = 4");
  388. printf("%d\n",rets);
  389. }
复制代码


回复

使用道具 举报