执行SQL语句---SELECT

1、通常从MySQL数据库中检索数据有4个步骤：

（1）发出查询: 用mysql_query发出查询。

（2）检索数据: 用mysql_store_result/mysql_use_result

（3）处理数据：用mysql_fetch_row, mysql_fetch_field

（4）整理所需要的数据:用mysql_free_result允许MySQL进行必要的整理工作。

 

2、例子：结构化的数据库查询输出

/*
 * MysqlQuery2.c
 *
 *  Created on: Sep 8, 2013
 *      Author: root
 */
#include <stdlib.h>
#include <stdio.h>
#include <mysql/mysql.h>
MYSQL my_connection;
MYSQL_RES * res_ptr;
MYSQL_ROW sqlrow;
void display_header();
void display_row(MYSQL * ptr);

int main(){
    int res;
    int first_row = 1;
    mysql_init(&my_connection);
    if(mysql_real_connect(&my_connection, "localhost", "root", "ROOT123456", "icmp", 0, NULL, 0)){
        printf("Connection success.\n");
        res = mysql_query(&my_connection, "select childno, fname, age from children where age > 5");
        if(res){
            printf("Select error:%s \n", mysql_error(&my_connection));
            return -2;
        }else{
            res_ptr = mysql_use_result(&my_connection);
            if(res_ptr){
                display_header();
                while((sqlrow = mysql_fetch_row(res_ptr))){
                    if(first_row){
                        display_header();
                        first_row = 0;
                    }
                    display_row(&my_connection);
                }
                if(mysql_errno(&my_connection)){
                    fprintf(stderr, "Retrieve error:%s\n", mysql_error(&my_connection));
                    return -3;
                }
            }
            mysql_free_result(res_ptr);
        }
        mysql_close(&my_connection);
        printf("Connection closed.\n");
    }else{
        fprintf(stderr, "Connection failed.\n");
        if(mysql_errno(&my_connection)){
            fprintf(stderr, "Connection error %d %s\n", mysql_errno(&my_connection), mysql_error(&my_connection));
            return -1;
        }
    }
    return 0;
}

void display_header(){
    MYSQL_FIELD * field_ptr;
    printf("Column details:\n");
    while((field_ptr = mysql_fetch_field(res_ptr)) != NULL){
        printf("\tName: %s\n", field_ptr->name);
        printf("\tType:");
        if(IS_NUM(field_ptr->type)){
            printf("Numeric field\n");
        }else{
            switch(field_ptr->type){
            case FIELD_TYPE_VAR_STRING:
                printf("VARCHAR\n");break;
            case FIELD_TYPE_LONG:
                printf("LONG\n");break;
            default:
                printf("Type is %d, check in mysql_com.h\n", field_ptr->type);
            }
        }

        printf("\tMax width %d\n", field_ptr->length);
        if(field_ptr->flags & AUTO_INCREMENT_FLAG){
            printf("\t Auto increments\n");
        }
        printf("\n");
    }
}

void display_row(MYSQL * ptr){
    unsigned int field_count;
    field_count = 0;
    while(field_count < mysql_field_count(ptr)){
        printf("%s ", sqlrow[field_count]);
        field_count++;
    }
    printf("\n");
}

运行结果：

Connection success.
Column details:
    Name: childno
    Type:Numeric field
    Max width 11
     Auto increments

    Name: fname
    Type:VARCHAR
    Max width 30

    Name: age
    Type:Numeric field
    Max width 11

Column details:
1 wangle 28
2 xuyehui 29
Connection closed.