C++操作mySql数据库

1.首先下载配置mySql（可参考：http://jingyan.baidu.com/article/f3ad7d0ffc061a09c3345bf0.html）

2.配置VC环境

（1）将libmysql.lib文件拷贝到安装路径VC/bin下

（2）将libmysql.dll文件拷贝到windows/system32下（没有这一步，运行提示会报“无法找到libmysql.dll”的错误）

（3）配置属性C/C++ —> 常规 —> 附件包含目录添加D:\mySQL\mysql_5.6.24_winx64\lib

（4）配置属性链接器 —> 常规 —> 附件包含目录添加D:\mySQL\mysql_5.6.24_winx64\lib

（5）配置属性链接器 —> 输入 —> 附加依赖性添加libmysql.lib

3.写代码

代码示例：

#include <winsock.h>
#include <iostream>
#include <string>
#include <mysql.h>
using namespace std;

int mainconnectMysql()
{
    //必备的一个数据结构
    MYSQL mydata;

    //初始化数据库
    if (0 == mysql_library_init(0, NULL, NULL))
    {
        cout << "mysql_library_init() succeed" << endl;
    }
    else
    {
        cout << "mysql_library_init() failed" << endl;
        return -1;
    }

    //初始化数据结构
    if (NULL != mysql_init(&mydata))
    {
        cout << "mysql_init() succeed" << endl;
    }
    else
    {
        cout << "mysql_init() failed" << endl;
        return -1;
    }

    //在连接数据库之前，设置额外的连接选项
    //可以设置的选项很多，这里设置字符集，否则无法处理中文
//    if (0 == mysql_options(&mydata, MYSQL_SET_CHARSET_NAME, "gbk"))
    if (0 == mysql_options(&mydata, MYSQL_SET_CHARSET_NAME, "gbk"))
    {
        cout << "mysql_options() succeed" << endl;
    }
    else
    {
        cout << "mysql_options() failed" << endl;
        return -1;
    }

    //连接数据库
    if (NULL != mysql_real_connect(&mydata, "127.0.0.1", "root", "110", "test", 3306, NULL, 0))  //"127.0.0.1" <=====> "localhost"
        //这里的地址，用户名，密码，端口可以根据自己本地的情况更改
    {
        cout << "mysql_real_connect() succeed" << endl;
    }
    else
    {
        cout << "mysql_real_connect() failed" << endl;
        return -1;
    }



    //sql字符串
    string sqlstr;

    //创建一个表
//    sqlstr = "CREATE TABLE IF NOT EXISTS Heros";
//    sqlstr += "(ID char(1) not null,NAME varchar(10) not null,SEX char(1) not null,AGE int(2) not null);";

    //====================
        sqlstr = "CREATE TABLE IF NOT EXISTS Heros";
        sqlstr += "(";
        sqlstr +="id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT 'Unique User ID',";
        sqlstr +="name VARCHAR(100) CHARACTER SET gb2312 COLLATE gb2312_chinese_ci NULL COMMENT 'Name Of User',";  //gb2312_chinese_CI : 只支持简体中文
        sqlstr += "sex Char(1) NOT NULL,";
        sqlstr +="age INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'The Summation Of Using Time'";
        sqlstr += ");";

    if (0 == mysql_query(&mydata, sqlstr.c_str()))
    {
        cout << "mysql_query() create table succeed" << endl;
    }
    else
    {
        cout << "mysql_query() create table failed" << endl;
        mysql_close(&mydata);
        return -1;
    }

    //===========================
    //mysql_query(&mydata, "SET NAMES latin1");
    //===========================

    //向表中插入数据
//    sqlstr = "INSERT INTO Heros(ID,NAME,SEX,AGE) VALUES('1','宋江','m',45),('2','卢俊义','f',40), ('3', '吴用', 'm', 39),('4','公孙胜','m',36),('5', '关胜', 'm', 38), ('6', '林冲', 'f', 34);";
    sqlstr = "INSERT INTO Heros(NAME,SEX,AGE) VALUES('宋江','m',45),('卢俊义','f',40), ('吴用', 'm', 39),('公孙胜','m',36),('关胜', 'm', 38), ('林冲', 'f', 34);";
//    sqlstr = "INSERT INTO Heros(NAME,SEX) VALUES('宋江','m'),('卢俊义','f'), ('吴用', 'm'),('公孙胜','m'),('关胜', 'm'), ('林冲', 'f');";

    
    if (0 == mysql_query(&mydata, sqlstr.c_str()))
    {
        cout << "mysql_query() insert data succeed" << endl;
    }
    else
    {
        cout << "mysql_query() insert data failed" << endl;
        mysql_close(&mydata);
        return -1;
    }

    //显示刚才插入的数据
    sqlstr = "SELECT id,name,sex,age FROM Heros";
    MYSQL_RES *result = NULL;
    if (0 == mysql_query(&mydata, sqlstr.c_str()))
    {
        cout << "mysql_query() select data succeed" << endl;

        //一次性取得数据集
        result = mysql_store_result(&mydata);
        //取得并打印行数
        int rowcount = mysql_num_rows(result);
        cout << "row count: " << rowcount << endl;

        //取得并打印各字段的名称
        unsigned int fieldcount = mysql_num_fields(result);
        MYSQL_FIELD *field = NULL;
        for (unsigned int i = 0; i < fieldcount; i++)
        {
            field = mysql_fetch_field_direct(result, i);
            cout << field->name << "\t\t";
        }
        cout << endl;

        //打印各行
        MYSQL_ROW row = NULL;
        row = mysql_fetch_row(result);
        while (NULL != row)
        {
            for (int i = 0; i < fieldcount; i++)
            {
                cout << row[i] << "\t\t";
            }
            cout << endl;
            row = mysql_fetch_row(result);
        }

    }
    else
    {
        cout << "mysql_query() select data failed" << endl;
        mysql_close(&mydata);
        return -1;
    }

//    //删除刚才建的表
//    sqlstr = "DROP TABLE user_info";
//    if (0 == mysql_query(&mydata, sqlstr.c_str()))
//    {
//        cout << "mysql_query() drop table succeed" << endl;
//    }
//    else
//    {
//        cout << "mysql_query() drop table failed" << endl;
//        mysql_close(&mydata);
//        return -1;
//    }
//    mysql_free_result(result);
//    mysql_close(&mydata);
//    mysql_server_end();

    system("pause");
    return 0;
}