c语言访问MySQL数据库

1、 以用户名root和密码yxk的mysql数据库来连接本机服务器上名为test的数据库（test必须为mysql数据库中的一个存在的数据库）：

//connect1.c

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

int main()
{
    MYSQL *conn_ptr;
    conn_ptr = mysql_init(NULL);
    if(!conn_ptr)
    {
        fprintf(stderr,"mysql_init failed\n");
    return EXIT_FAILURE;
    }
    conn_ptr = mysql_real_connect(conn_ptr,"localhost","root","yxk","test",0,NULL,0);
    if(conn_ptr)
    {
        printf("Connection success\n");
    }
    else
    {
        printf("Connection failed\n");
    }
    mysql_close(conn_ptr);
    return EXIT_SUCCESS;
}

编译命令为 $gcc –I /usr/include/mysql connect1.c –lmysqlclient –o connect1

执行： $ ./connect1

       Connection success

       $

注：如找不到mysql.h则查看《ubuntu12.04中找不到mysql.h》博文。

2、反馈链接失败错误：用户名root和密码yxk的mysql数据库来连接本机服务器上名为test的数据库，密码空着：

//connect2.c

#include <stdlib.h>
#include <stdio.h>
#include "mysql.h"
int main(int argc,char *argv[])
{
    MYSQL my_connection;
    mysql_init(&my_connection);
    if(mysql_real_connect(&my_connection,"localhost","root"," ","test",0,NULL,0))
    {
        printf("Connection success\n");
        mysql_close(&mysql_connection);
    }
    else
    {
        printf("Connect 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;
}

执行：

$ ./connect2

Connection filed

Connection error 1045: Access denied foruser: ‘neusoft@localhost’ (Using password: YES)

$

3、插入数据操作（用户名root和密码yxk的mysql数据库来连接本机服务器上名为test的数据库）：

//insertdata.c

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

int main(int argc,char *argv[])
{
    MYSQL my_connection;
    int res;
    mysql_init(&my_connection);
    if(mysql_real_connect(&my_connection,"localhost","root","yxk","test",0,NULL,0))
    {
        printf("Connection success\n");
        res = mysql_query(&my_connection,"INSERT INTO children(fname,age) VALUES('Ann',13)");
        if(!res)
       {
           printf("Insert %lu rows\n",(unsigned long)mysql_affected_rows(&my_connection));
        else
        {
            fprintf(stderr,"Insert error %d: %s\n",mysql_errno(&my_connection),mysql_error(&my_connection));
        mysql_close(&my_connection);
    else
    {
        fprintf(stderr,"Connection failed\n");
        if(mysql_errno(&my_conncetion))
        {
            fprintf(stderr,"Connection error %d: %s\n",mysql_errno(&my_connection),mysql_error(&my_connection));
        }
    }
    return EXIT_SUCCESS;
}  

编译命令为 $gcc –I /usr/include/mysql insertdata.c –lmysqlclient –o insertdata

执行： $ ./insertdata

   Connect success

   Insert 1 rows;

4、查找插入数据内容（用户名root和密码yxk的mysql数据库来连接本机服务器上名为test的数据库）：

//insertdata.c

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

int main(int argc,char *argv[])
{
    MYSQL my_connection;
    MYSQL_RES *res_ptr;
    MYSQL_ROW sqlrow;
    int res;
    mysql_init(&my_connection);
    if(mysql_real_connect(&my_connection,"localhost","root","yxk","test",0,NULL,0))
    {
        printf("Connection success\n");
        res = mysql_quenry(&my_connection,"INSERT INTO children(fname,age) VALUES('Robert',8)");
        if(!res)
        {
            printf("Inserted %lu rows\n",(unsigned long)mysql_affected_rows(&my_connection));
        }
        else
        {
            fprintf(stderr,"Insert error %d: %s\n",mysql_errno(&my_connection),mysql_error(&my_connection));
        }
        res = mysql_query(&my_connection,"SELECT LAST_INSERT)ID()");
        if(res)
        {
            printf("SELECT error:%s\n",mysql_error(&my_connection));
        }
        else
        {
            res_ptr = mysql_use_result(&my_connection);
            if(res_ptr)
            {
                 while(sqlrow = mysql_fetch_row(res_ptr)))
                {
                    printf("We inserted childNo %s\n",sqlrow[0]);
                }
                mysql_free_result(res_ptr);
            }
        }
        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;
}        

编译：

$ gcc –I/usr/include/mysql insertdate.c –lmysqlclient –o insertdate
$ ./insertdate
Connection success
Inserted 1 rows
We inserted childNo 3

$./insertdate
Connection success 
Inserted 1 rows
We inserted childNo 4

5、提取数据（ 用户名root和密码yxk的mysql数据库来连接本机服务器上名为test的数据库）

//selectdata.c

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

MYSQL my_connection;
MYSQL_RES *res_ptr;
MYSQL_ROW sqlrow;

int main(int argc,char *argv[])
{
    int res;
    mysql_init(&my_connection);
    if(mysql_real_connect(&my_connection,"localhost","root","yxk","test",0,NULL,0));
    printf("Connection success\n");
    res = mysql_query(&my_connection,"SELECT chileNo,fname,age FROM children WHERE age > 5");
    if(res)
    {
        printf("SELECT error:%s\n",mysql_error(&my_connection));
    }
    else
    {
        res_ptr = mysql_store_result(&my_connection);
        if(res_ptr)
        {
            printf("Retrived %lu rows\n",(unsigned long)mysql_num_rows(res_ptr));
            while((sqlrow = mysql_fetch_row(res_ptr)))
            {
                printf("Fetched data...\n");
             }
            if(mysql_errno(&my_connection))
            {
                fprintf(stderr,"Retrive error:%s\n",mysql_error(&my_connection));
             }
        }
        mysql_free_result(res_ptr);
      }
    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;
}