unix c mysql_C连MYSQL(转载)

转自：http://blog.chinaunix.net/u/4502/showart_13278.html

用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 #include #include

#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!!!

");

exit(0);

}

strcpy(query,"SELECT `FNUMBER`, `FNAME`, `FSEX`, `FPAY` FROM `PAY` WHERE `FAGE` > 20"); /*查询*/

if (mysql_query(&mysql,query) != 0) { /*执行SQL语句*/

printf("SELECT ERROR!!!

");

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 name: %s sex: %s pay: %f

",number,name,sex,pay);

}

printf("max_pay:%f

",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!!!

");

mysql_close(&mysql);

exit(0);

}

else

printf("DELETE SUCCEED!!!

");

strcpy(insert,"INSERT

INTO `PAY` (`FNUMBER`, `FNAME`, `FSEX`, `FAGE`, `FPAY`) VALUES

('000002', 'Aaron', 'men', 30, '6000.00')");

if (mysql_query(&mysql,insert) != 0) {

printf("INSERT ERROR!!!

");

mysql_close(&mysql);

exit(0);

}

else

printf("INSERT SUCCEED!!!

");

strcpy(update,"UPDATE `PAY` SET `FPAY`=`FPAY` + 1000 WHERE 1");

if (mysql_query(&mysql,update) != 0) {

printf("UPDATA ERROR!!!

");

mysql_close(&mysql);

exit(0);

}

else

printf("UPDATA SUCCEED!!!

");

mysql_close(&mysql);

}

4、

编译：

gcc -I/usr/local/mysql/include/mysql -lmysqlclient -o c_mysql.o

c_mysql.c    其中"/usr/local/mysql/include/mysql "为系统中MYSQL的路径。