用C连MYSQL及其简单操作的示例

    1、数据库名：test，表名：PAY(工资表)，结构如下：

    CREATE TABLE PAY (
      FNUMBER varchar(6) NOT NULL default '',
      FNAME varchar(50) default NULL,
      FSEX varchar(10) NOT NULL default '',
      FAGE int(3) default NULL,
      FPAY decimal(7,2) NOT NULL default '0.00',
      PRIMARY KEY  (FNUMBER)
    ) TYPE=MyISAM;

    2、数据如下：

    INSERT INTO PAY VALUES ('000001', 'Jack', 'men', 25, '5000.00');
    INSERT INTO PAY VALUES ('000002', 'Aaron', 'men', 30, '7000.00');
    INSERT INTO PAY VALUES ('000003', 'Ruth', 'women', 22, '4000.00');
    INSERT INTO PAY VALUES ('000004', 'Tom', 'men', 28, '7200.00');
    INSERT INTO PAY VALUES ('000005', 'Pearson', 'men', 20, '4000.00');

    3、源程序如下(c_mysql.c)：

    #include "stdio.h"
    #include "stdlib.h"
    #include "mysql.h"

    #define HOST "127.0.0.1"
    #define USER "root"
    #define PASSWORD "654321"
    #define DB "test"

    main()
    {
            MYSQL mysql;
            MYSQL_RES *result;
            MYSQL_ROW row;

            char number[10]="";
            char name[50]="";
            char sex[10]="";
            int age;
            float pay;

            char query[200]="";
            char delete[200]="";
            char insert[400]="";
            char update[200]="";
            float max_pay;

            mysql_init(&mysql);
            if ( !mysql_real_connect(&mysql,HOST,USER,PASSWORD,DB,0,NULL,0) ) { /*连接*/
                    printf("connect mysql fail!!!\n");
                    exit(0);
            }

            strcpy(query,"SELECT `FNUMBER`, `FNAME`, `FSEX`, `FPAY` FROM `PAY` WHERE `FAGE` > 20"); /*查询*/
            if (mysql_query(&mysql,query) != 0) { /*执行SQL语句*/
                    printf("SELECT ERROR!!!\n");
                    mysql_close(&mysql);
                    exit(0);
            }
            result = mysql_store_result(&mysql);
            while ( ( row = mysql_fetch_row(result) ) ) {
                    strcpy(number,row[0]);
                    strcpy(name,row[1]);
                    strcpy(sex,row[2]);
                    pay = atof(row[3]);
                    if ( pay > max_pay )
                            max_pay=pay;
                    printf("number: %s\t name: %s\t sex: %s\t pay: %f\t\n",number,name,sex,pay);
            }
            printf("max_pay:%f\n",max_pay);
            mysql_free_result(result);

            sprintf(delete,"DELETE FROM `PAY` WHERE `FPAY` = %f",max_pay);
            if (mysql_query(&mysql,delete) != 0) {
                    printf("DELETE ERROR!!!\n");
                    mysql_close(&mysql);
                    exit(0);
            }
            else
                    printf("DELETE SUCCEED!!!\n");