linux c mysql ping_linux下c操作mysql之增删改查

书接上文,继续进行linux 下c操作mysql。

1.创建表/插入数据

mysql> desc children

-> ;

+---------+-------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+---------+-------------+------+-----+---------+----------------+

| childno | int(11) | NO | PRI | NULL | auto_increment |

| fname | varchar(32) | YES | | NULL | |

| age | int(11) | YES | | NULL | |

+---------+-------------+------+-----+---------+----------------+

3 rows in set (0.00 sec)

mysql> select * from children;

+---------+---------+------+

| childno | fname | age |

+---------+---------+------+

| 1 | Jenny | 21 |

| 2 | Andrew | 17 |

| 3 | Gavin | 8 |

| 4 | Duncan | 6 |

| 5 | Emma | 4 |

| 6 | Alex | 15 |

| 7 | Adrian | 9 |

| 8 | jeffsui | 5 |

+---------+---------+------+

8 rows in set (0.00 sec)

2.文件清单

demo.c

/************************************************************

FileName : demo.c

FileFunc : C语言接口访问MySQL

Version : V0.1

Author : JeffSui

Date : 2014-02-20

Descp : c查询mysql数据库遍历输出

*************************************************************/

#include

#include

#include "/usr/include/mysql/mysql.h"

int main(int argc,char *argv[])

{

MYSQL my_connection,*conn_ptr;

MYSQL_RES *res_ptr;

MYSQL_ROW sqlrow;

int iRet;

int iTableRow,iTableCol,i,j;

char *server = "localhost";

char *user = "root";

char *password = "";

char *database = "csql";

unsigned int uiTimeOut = 7;//设置连接超时7s

conn_ptr = mysql_init(&my_connection);//初始化连接句柄

if( !conn_ptr )

{

fprintf(stderr,"mysql_init failed ! \n");

return EXIT_FAILURE;

}

iRet = mysql_options(&my_connection,MYSQL_OPT_CONNECT_TIMEOUT,(const char *)&uiTimeOut);//设置连接超时

if( iRet )

{

fprintf(stderr,"Connection is timeout! \n");

return EXIT_FAILURE;

}

conn_ptr = mysql_real_connect(&my_connection,server,user,password,database,0,NULL,0);//连接数据库

if( conn_ptr )

{

printf("Connection success!\n");

iRet = mysql_query(&my_connection,"select * from children");//执行SQL语句

if( iRet )

{

fprintf(stderr,"select error %d: %s !\n",mysql_errno(&my_connection),mysql_error(&my_connection));//>打印错误处理具体信息

return EXIT_FAILURE;

}

res_ptr = mysql_store_result(&my_connection);//集合

if( res_ptr )

{

iTableRow = mysql_num_rows(res_ptr);//行

iTableCol = mysql_num_fields(res_ptr);//列

for(i=0; i

{

sqlrow = mysql_fetch_row(res_ptr);

for(j=0; j

{

printf("%-8s ",sqlrow[j]);//字符串向左靠，右补空格

}

printf("\n");

}

mysql_free_result(res_ptr);//完成对数据的所有操作后,调用此函数来让MySQL库清理它分配的对象

}

mysql_close(&my_connection);//关闭连接

} 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 EXIT_SUCCESS;

}

insert.c

/******************************************************

FileName : insert.c

FileFunc : C语言接口访问MySQL

Version : V0.1

Author : JeffSui

Date : 2014-02-24

Descp : c插入mysql数据库

******************************************************/

#include

#include

#include "/usr/include/mysql/mysql.h"

int main() {

MYSQL *conn_ptr;

int res;

conn_ptr = mysql_init(NULL);

if (!conn_ptr) {

printf("mysql_init failed\n");

return EXIT_FAILURE;

}

conn_ptr = mysql_real_connect(conn_ptr, "localhost", "root", "", "csql", 0, NULL, 0);

if (conn_ptr) {

res = mysql_query(conn_ptr, "insert into children values(null,'jeffsui',5)"); //可以把insert语句替换成delete或者update语句，都一样的

if(!res){

printf("Insert %lu rows\n",(unsigned long)mysql_affected_rows(conn_ptr));

}else{

fprintf(stderr,"Insert error %d: %s\n",mysql_error(conn_ptr),mysql_error(conn_ptr));

}

}else{

printf("Connection failed!");

}

mysql_close(conn_ptr);

return EXIT_SUCCESS;

}

makefile

#makefile开始

c_mysql_demo:demo.c

@gcc -lm -lpthread -ldl -I/usr/include/mysql demo.c -o demo -L/usr/lib64/mysql -lmysqlclient

@gcc -lm -lpthread -ldl -I/usr/include/mysql insert.c -o insert -L/usr/lib64/mysql -lmysqlclient

clean:

@rm -rf demo

@rm -rf insert

#makefile结束

3.执行make 指令

4.执行目标路径下的

[root@tf-test c_pro]# ./insert

Insert 1 rows

[root@tf-test c_pro]# ./demo

Connection success!

1         Jenny     21

2         Andrew    17

3         Gavin     8

4         Duncan    6

5         Emma      4

6         Alex      15

7         Adrian    9

8         jeffsui   5

9         kelly     22