数据库编程

浏览数：26 / 时间：2015年06月12日

头文件包含

#include <mysql/mysql.h>

增加gcc的编译链接选项

gcc -lmysqlclient

连接到数据库

mysql_init(MYSQL *pmysql);

MYSQL *mysql_real_connect(MYSQL *pmysql,const char *hostname, ,const char *usename, const char *passwd,const char *dbname,0,0,0);

连接到MySQL必须先调用 mysql_init初始化

之后调用mysql_real_connect连接到数据库。

mysql_real_connect成功返回指向MySQL连接的指针，失败返回NULL。

连接数据库的例子

int main(int arg, char *args[])
{
    MYSQL *connection, mysql;
    mysql_init(&mysql);
    connection = mysql_real_connect(&mysql, "localhost", "dbuser1", "dbuser1", "db1",0,0,0);
    if (connect == NULL)
    {
        printf(mysql_error(&mysql));
        return EXIT_FAILURE;
    }
    mysql_close(connection);
    return EXIT_SUCCESS;
}

执行SQL语句

int mysql_query(MYSQL *pmysql,const char *sql);

参数pmysql是连接到MySQL的指针。

参数sql是要执行的SQL语句。

成功返回0,失败返回非0。

插入数据例子

int main(int arg, char *args[])
{
    MYSQL *connection, mysql;
    mysql_init(&mysql);
    connection = mysql_real_connect(&mysql, "localhost", "dbuser", "dbuser", "db1",0,0,0);
    if (connect == NULL)
    {
        printf(mysql_error(&mysql));
        return EXIT_FAILURE;
    }
    int state = mysql_query(connection, "INSERT INTO table1 (NAME,SEX,AGE,CLASS) VALUES (‘HAHA‘,1,30,‘666‘)");
    if (state != 0)
    {
        printf(mysql_error(connection));
        return EXIT_FAILURE;
    }
    mysql_close(connection);
    return EXIT_SUCCESS;
}

修改数据例子

int main(int arg, char *args[])
{
    MYSQL *connection, mysql;
    mysql_init(&mysql);
    connection = mysql_real_connect(&mysql, "localhost", "dbuser1", "dbuser1", "db1",0,0,0);
    if (connect == NULL)
    {
        printf(mysql_error(&mysql));
        return EXIT_FAILURE;
    }
    int state = mysql_query(connection, "UPDATE table1 SET CLASS = ‘250‘ WHERE NAME = ‘HAHA‘");
    if (state != 0)
    {
        printf(mysql_error(connection));
        return EXIT_FAILURE;
    }
    mysql_close(connection);
    return EXIT_SUCCESS;
}

删除数据例子

int main(int arg, char *args[])
{
    MYSQL *connection, mysql;
    mysql_init(&mysql);
    connection = mysql_real_connect(&mysql, "localhost", "dbuser1", "dbuser1", "db1",0,0,0);
    if (connect == NULL)
    {
        printf(mysql_error(&mysql));
        return EXIT_FAILURE;
    }
    int state = mysql_query(connection, “DELETE FROM table1 WHERE NAME = ‘HAHA‘");
    if (state != 0)
    {
        printf(mysql_error(connection));
        return EXIT_FAILURE;
    }
    mysql_close(connection);
    return EXIT_SUCCESS;
}

执行SELECT语句得到查询结果

MYSQL_RES *mysql_store_result(MYSQL *pmysql);

成功返回一个查询结果指针，查询无结果或者错误返回NULL。

mysql_free_result(MYSQL_RES *res)

调用完mysql_store_result，一定要用mysql_free_result释放相关的资源。

查看查询结果

MYSQL_ROW mysql_fetch_row(MYSQL_RES *result);

例子

while(row = mysql_fetch_row(result)) != NULL)

{

printf(“name:%s,sex:%s,age:%s,class:%s\n”,row[0], row[1], row[2],row[3]

}

尽管在表中age是数字，但mysql返回的只不过是以NULL结尾的字符串。

查看查询结果中的字段信息

MYSQL_FIELD *mysql_fetch_field (MYSQL_RES *result);

例子

while ((sqlField = mysql_fetch_field(result)) != NULL)

{

printf("%s\n", sqlField->name);

}

执行SELECT例子1

int main(int arg, char *args[])
{
    MYSQL *connection, mysql;
    mysql_init(&mysql);
    connection = mysql_real_connect(&mysql, "localhost", "dbuser1", "dbuser1", "db1",0,0,0);
    if (connect == NULL)
    {
        printf(mysql_error(&mysql));
        return EXIT_FAILURE;
    }

    int state = mysql_query(connection, "SELECT NAME,SEX,AGE,CLASS FROM table1");
    if (state != 0)
    {
        printf(mysql_error(connection));
        return EXIT_FAILURE;
    }

    MYSQL_RES *result = mysql_store_result(connection);
    if (result == (MYSQL_RES *)NULL)
    {
        printf(mysql_error(connection));
        return EXIT_FAILURE;
    }

    MYSQL_ROW row;
    while((row = mysql_fetch_row(result)) != NULL)
    {
        printf("name %s, sex %s, age %s, class %s\n",
                row[0],row[1],row[2],row[3]);
    }
    mysql_free_result(result);
    mysql_close(connection);
    return EXIT_SUCCESS;
}

执行SELECT例子2

int main(int arg, char *args[])
{
    if (arg < 5)
        return 0;

    MYSQL *connection, mysql;
    mysql_init(&mysql);
    connection = mysql_real_connect(&mysql, args[1], args[2], args[3], args[4],
            0, 0, 0);
    if (connection == NULL)
    {
        printf("%s\n", mysql_error(&mysql));
        return EXIT_FAILURE;
    }
    printf("success connect to mysql\n");
    int state = mysql_query(connection, "SET NAMES utf8");

    char buf[1024];
    while (1)
    {
        memset(buf, 0, sizeof(buf));
        read(STDIN_FILENO, buf, sizeof(buf));
        if (strncmp(buf, "exit", 4) == 0)
        {
            break;
        }
        state = mysql_query(connection, buf);
        if (state != 0)
        {
            printf("%s\n", mysql_error(connection));
        }

        MYSQL_RES *result = mysql_store_result(connection);
        if (result == NULL)
        {
            printf("%s\n", mysql_error(connection));
            break;
        }

        MYSQL_FIELD *sqlField;
        int iFieldCount = 0;
        while ((sqlField = mysql_fetch_field(result)) != NULL)
        {
            printf("%s\t", sqlField->name);
            iFieldCount++;
        }
        printf("\n");

        MYSQL_ROW row;
        while ((row = mysql_fetch_row(result)) != NULL)
        {
            int i;
            for (i = 0; i < iFieldCount; i++)
            {
                printf("%s\t", row[i]);
            }
            printf("\n");
        }
        mysql_free_result(result);
    }
    mysql_close(connection);
    return EXIT_SUCCESS;
}