linux C读取数据库

上次我们已经共同学习了在Linux下C连接数据库，下面一起学习用C语言来操作数据库。

1,首先要打开mysql的服务

[root@bogon ~]# service mysqld status
mysqld 已停
[root@bogon ~]# service mysqld start
启动 MySQL： [确定]
[root@bogon ~]#
此时mysql服务已打开

下面我们来读取数据库的内容

mysql> show databases;
+--------------------+
| Database             |
+--------------------+
| information_schema |
| data                |
| mysql                  |
| test                 |
+--------------------+
4 rows in set (0.00 sec)
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from product;
+------+-------+-------+------+
| code | name | price | num |
+------+-------+-------+------+
| 1001 | apple |   2.5   |    8   |
| 1003 | cake |   2.5 |    1   |
| 1002 | pen   |     1    |    5   |
+------+-------+-------+------+
3 rows in set (0.00 sec)
mysql>

好了，下面我们用代码来测试，命名为 readdata.c

  1 #include<stdio.h>
  2 #include<stdlib.h>
  3 #include "mysql.h"
  4 
  5 #define N 10
  6 typedef struct Data
  7 {
  8     char code[50];
  9     char name[50];
 10         float price;
 11     int num;
 12 }Data;
 13 MYSQL *conn_ptr;
 14 int main()
 15 {
 16     void update_data(Data obj);
 17     void conn_data(MYSQL *conn_ptr);
 18     
 19     printf("Reading database...
");
 20     read_data();
 21 22     return 0;
 23 }
 24 
 25 /***********************/
 26 /**  Connect Database    **/
 27 /***********************/
 28 void conn_data(MYSQL *conn_ptr)
 29 {
 30     conn_ptr=mysql_init(NULL); //连接初始化
 31     if(!conn_ptr)
 32     {
 33         fprintf(stderr, "mysql_init failed
");
 34         exit (0);
 35         //return EXIT_FAILURE;
 36     }
 37     conn_ptr = mysql_real_connect(conn_ptr, "localhost", "root","","test", 0, NULL, 0); //建立实际连接
 38     //参数分别为：初始化的连接句柄指针，主机名（或者IP），用户名，密码，数据库名，0，NULL，0）后面三个参数在默认安装mysql>的情况下不用改
 39     if(conn_ptr)
 40     {
 41         printf("Connection success
");
 42     }
 43     else 
 44     {
 45         printf("Connection failed
");
 46     }
 47     mysql_close(conn_ptr); 
 48 }
 49 
 50 /*******************/
 51 /** read delete        **/
 52 /******************/
 53 int read_data()
 54 {
 55     MYSQL *conn_ptr; 
 56     conn_ptr=mysql_init(NULL); //连接初始化
 57     if(!conn_ptr)
 58     {
 59         fprintf(stderr, "mysql_init failed
");
 60         exit (0);
 61         return EXIT_FAILURE;
 62     }
 63     conn_ptr = mysql_real_connect(conn_ptr, "localhost", "root","","test", 0, NULL, 0); //建立实际连接
 64        //参数分别为：初始化的连接句柄指针，主机名（或者IP），用户名，此处密码为空，数据库名，0，NULL，0）后面三个参数在默认安装mysql>的情况下不用改
 65     if(conn_ptr)
 66     {
 67         printf("Connection success
");
 68     }
 69     else 
 70     {
 71         printf("Connection failed
");
 72     }
 73     
 74     MYSQL_RES *res_ptr;
 75     MYSQL_ROW sqlrow;
 76     int res = mysql_query(conn_ptr, "select * from product ");
 77     if(res)
 78     {
 79            printf("select error: %s
", mysql_error(conn_ptr));
 80     }
 81     else {
 82             res_ptr = mysql_store_result(conn_ptr);
 83             if(res_ptr)
 84             {
 85                 printf("********************
");
 86                 printf("Retrieved %lu rows
", (unsigned long)mysql_num_rows (res_ptr)); 
 87                 printf("********************
");
 88                 printf("Code	Name	Price	Number
");
 89                 while (sqlrow = mysql_fetch_row(res_ptr))
 90                 {    
 91                     unsigned int field_count;
 92                     field_count =0;
 93                     unsigned int field_num=0;
 94                 
 95                     while(field_count < mysql_field_count(conn_ptr))
 96                     {
 97                         printf("%s	", sqlrow[field_count]);
 98                         field_count++;
 99                     }
100                     printf("
");
101                 }
102                 if(mysql_errno(conn_ptr))
103                 {
104                     printf("Retrive error : %s
", mysql_error(conn_ptr));
105                 }
106             }
107     mysql_free_result(res_ptr);
108     mysql_close(conn_ptr); //关闭连接
109     return EXIT_SUCCESS;
110     }
111 }

那就可以运行程序了

[root@bogon ~]# gcc -I/usr/include/mysql readdata.c -lmysqlclient -L/usr/lib/mysql -o read
[root@bogon ~]# ./read
Reading database...
Connection success
********************
Retrieved 3 rows
********************
Code    Name    Price   Number
1001    apple   2.5     8
1003    cake    2.5     1
1002    pen     1        5
[root@bogon ~]#

实验发现和从数据库中读取的完全相同，说明读取数据库已成功。

下面我们可以对其进行典型的“增删查改”了。