C++ Operating MySQL Database

mysql5 安装

用管理员命令行进/bin目录，输入mysqld.exe --install&&net start mysql

修改密码

首次修改输入mysqladmin -u root password "new_password"。
修改旧密码输入首次修改输入mysqladmin -u root -p password “new_password”`，然后输入原有旧密码。

IDE设置

首先需要在include目录中加上mysql5\include
lib目录中加上mysql5\lib\opt
（可选）在链接器的链接的lib文件中，添加libmysql.lib。
如果你找不到怎么添加libmysql.lib，可以跳过这一步

使用C++代码链接并操作数据库

需要的头文件

首先需要包含以下头文件

#include <winsock2.h>
#include <mysql.h>
#pragma comment(lib,"libmysql.lib") //如果你在IDE中设置了加入libmysql.lib,就可以不需要这一行，但是你加上去也没事

链接数据库

MYSQL mysqlconn;
mysql_init(&mysqlconn);
char * host="127.0.0.1";        //服务器地址
int port=3306;                  //端口
char * username="root";         //用户名
char * password="1234567890";   //密码
char * dbname="virus";          //数据库名

(mysql_real_connect(&mysqlconn,host,username,password,dbname,port,NULL,CLIENT_FOUND_ROWS) != NULL)?cout<<"success"<<endl:cout<<"fail"<<endl;

mysql_query(&mysqlconn,"set names gbk");    //链接完之后设置编码为GBK

读取数据库数据

void getdata()
{
    MYSQL_RES *     mysql_res;
    MYSQL_FIELD *   mysql_field;
    MYSQL_ROW       mysql_row;
    char * sql="select * from virus_data where city_deadCount>50";

    if(mysql_query(&mysqlconn,sql)==0)
    {
        cout<<"get data success"<<endl;
        mysql_res=mysql_store_result(&mysqlconn);
        if(mysql_res)
        {
            int fiendcount=mysql_num_fields(mysql_res);
            int rowcount=mysql_num_rows(mysql_res);
            cout<<fiendcount<<endl;
            cout<<rowcount<<endl;

            for(int i=0;i<fiendcount;i++)
            {
                mysql_field=mysql_fetch_field(mysql_res);
                cout<<mysql_field->name<<" ";
            }
            cout<<endl;

            for(int i=0;i<rowcount;i++)
            {
                mysql_row=mysql_fetch_row(mysql_res);
                for(int ja=0;ja<fiendcount;ja++)
                {
                    cout<<mysql_row[ja]<<" ";
                }
                cout<<endl;
            }
        }
    }else{
        cout<<"get data fail"<<endl;
    }
}

修改数据库数据

string sql;
sqla = "update item set qty=" + tmp + " where itemid='" + itemid + "'";   			//修改
sqlb = "delete from user where userid='" + userid + "'";							//删除
sqlc = "insert into datemax(date,value) values('" + date + "','" + "1" + "');";     //插入
mysql_query(&mysqlconn, sql);

项目实例

Github

MySQL 5 Installation

Navigate to the /bin directory using an Administrator command line, then enter mysqld.exe --install&&net start mysql.

Changing Password

For the first-time password change, enter mysqladmin -u root password "new_password".
To change an existing password, enter mysqladmin -u root -p password "new_password", then enter the original old password.

IDE Setup

First, add mysql5\include to the include directories.
Add mysql5\lib\opt to the lib directories.
(Optional) In the linker’s additional libraries, add libmysql.lib.
If you cannot find how to add libmysql.lib, you can skip this step.

Using C++ Code to Connect and Operate the Database

Required Header Files

First, include the following header files.

#include <winsock2.h>
#include <mysql.h>
#pragma comment(lib,"libmysql.lib") // If you have added libmysql.lib in your IDE settings, this line is not necessary, but including it is fine.

Connecting to the Database

MYSQL mysqlconn;
mysql_init(&mysqlconn);
char * host="127.0.0.1";        // Server address
int port=3306;                  // Port
char * username="root";         // Username
char * password="1234567890";   // Password
char * dbname="virus";          // Database name

(mysql_real_connect(&mysqlconn,host,username,password,dbname,port,NULL,CLIENT_FOUND_ROWS) != NULL)?cout<<"success"<<endl:cout<<"fail"<<endl;

mysql_query(&mysqlconn,"set names gbk");    // Set encoding to GBK after connecting

Reading Data from the Database

void getdata()
{
    MYSQL_RES *     mysql_res;
    MYSQL_FIELD *   mysql_field;
    MYSQL_ROW       mysql_row;
    char * sql="select * from virus_data where city_deadCount>50";

    if(mysql_query(&mysqlconn,sql)==0)
    {
        cout<<"get data success"<<endl;
        mysql_res=mysql_store_result(&mysqlconn);
        if(mysql_res)
        {
            int fiendcount=mysql_num_fields(mysql_res);
            int rowcount=mysql_num_rows(mysql_res);
            cout<<fiendcount<<endl;
            cout<<rowcount<<endl;

            for(int i=0;i<fiendcount;i++)
            {
                mysql_field=mysql_fetch_field(mysql_res);
                cout<<mysql_field->name<<" ";
            }
            cout<<endl;

            for(int i=0;i<rowcount;i++)
            {
                mysql_row=mysql_fetch_row(mysql_res);
                for(int ja=0;ja<fiendcount;ja++)
                {
                    cout<<mysql_row[ja]<<" ";
                }
                cout<<endl;
            }
        }
    }else{
        cout<<"get data fail"<<endl;
    }
}

Modifying Database Data

string sql;
sqla = "update item set qty=" + tmp + " where itemid='" + itemid + "'";   			// Update
sqlb = "delete from user where userid='" + userid + "'";							// Delete
sqlc = "insert into datemax(date,value) values('" + date + "','" + "1" + "');";     // Insert
mysql_query(&mysqlconn, sql);

Project Example

Github