C++连接数据库

一、sql.h

#ifndef SQL_H
#define SQL_H
#include <mysql.h>
#include <iostream>

typedef struct Data {
    std::string name;
    double price;
    int bookCount;
    std::string author;
};

class sql {
public:
    sql();
    ~sql();
    void add();  //增
    void del();  //删
    void display();  //查（选择全部）
    void correct();  //改
private:
    MYSQL mysql;  //数据库连接句柄
    MYSQL_RES* result;  //执行数据库语言结果
    MYSQL_ROW sql_row;  //存放一个数据记录
    Data data;
};

#endif  //sql.h

二、sql.cpp

#include "sql.h"
#include <iostream>
using std::cout;
using std::endl;
using std::cin;
using std::string;
#include <sstream>
using std::stringstream;

sql::sql() {
    mysql_init(&mysql); 
    mysql_options(&mysql, MYSQL_SET_CHARSET_NAME, "gbk");  //连接之前用
    result = nullptr;  //结果集
    sql_row = nullptr;  //得到结果集的下一行
    if (mysql_real_connect(&mysql, "localhost", "root", "root", "db_study", 3306, NULL, 0)) {
        //mysql_query(&mysql, "SET NAMES GBK");  //连接之后用
        cout << "数据库连接成功！" << endl;
    }
    else {
        cout << "数据库连接失败！" << endl;
        cout << "error reason: " << mysql_error(&mysql) << endl;
    }
}

sql::~sql() {
    mysql_free_result(result);
    mysql_close(&mysql);
    cout << "数据库已断开！" << endl;
}

void sql::add() {
    cout << "请输入您要添加的图书名称、价格、数量和作者：";
    cin >> data.name >> data.price >> data.bookCount >> data.author;
    stringstream oss;
    oss << "insert into tb_books(name,price,bookCount,author) values('" << data.name << "'," << data.price << "," << data.bookCount << ",'" << data.author << "')";
    string temp = oss.str();
    const char* query = temp.data();
    if (!mysql_query(&mysql, query)) {
        cout << "添加成功！" << endl;
    }
    else {
        cout << "添加失败！" << endl;
        cout << "error reason: " << mysql_error(&mysql) << endl;
    }
}

void sql::display() {
    if (!mysql_query(&mysql, "select * from tb_books")) {
        result = mysql_store_result(&mysql);  //得到结果集
        cout << "————————————" << endl;
        cout << "结果集数量：" << mysql_num_rows(result) << endl;  //得到结果集数量
        if (result) {
            while (sql_row = mysql_fetch_row(result)) {
                cout << sql_row[0] << ' ' << sql_row[1] << ' ' << sql_row[2] << ' ' << sql_row[3] << ' ' << sql_row[4] << endl;
            }
        }
        cout << "————————————" << endl;
    }
    else {
        cout << "查询失败！" << endl;
        cout << "error reason: " << mysql_error(&mysql) << endl;
    }
}

void sql::del() {
    cout << "请输入您要删除的id：";
    int id;
    cin >> id;
    stringstream oss;
    oss << "DELETE FROM tb_books WHERE id = " << id;
    string temp = oss.str();
    const char* query = temp.data();
    if (!mysql_query(&mysql, query)) {
        cout << "删除成功！" << endl;
        if (!mysql_query(&mysql, "alter table tb_books auto_increment = 1;")) {
            cout << "重新排序成功！" << endl;
        }
        else { cout << "重新排序失败！" << endl; cout << "error reason" << mysql_error(&mysql) << endl; }
    }
    else {
        cout << "删除失败！" << endl;
        cout << "error reason: " << mysql_error(&mysql) << endl;
    }
}

void sql::correct() {
    /*输入*/
    cout << "请输入您要修改的id：";
    int id;
    cin >> id;
    cout << "请选择您要修改的值(name、price...)：";
    string value_pre, value_aft;
    while (1) {
        cin >> value_pre;
        if (value_pre != "name" && "price" && "bookCount" && "author") {
            cout << "不存在该值！请重新输入：";
        }
        else break;
    }
    cout << "请输入 " << value_pre << " 修改后的值：";
    cin >> value_aft;
    /*执行*/
    stringstream oss;
    oss << "UPDATE tb_books SET " << value_pre << " = '" << value_aft << "' WHERE id = " << id;
    string temp = oss.str();
    const char* query = temp.data();
    if (!mysql_query(&mysql, query)) {
        cout << "修改成功！" << endl;
    }
    else {
        cout << "修改失败！" << endl;
        cout << "error reason: " << mysql_error(&mysql) << endl;
    }
}

三、main.cpp

//Author:zouyujie
//Date:2023.1.18
#include "sql.h"
#include <iostream>
using std::cout;
using std::endl;
using std::string;
#include <mysql.h> 
/*主函数*/
int main()
{
    sql sql;
    while (1) {
        cout << "1.增" << endl;
        cout << "2.删" << endl;
        cout << "3.改" << endl;
        cout << "4.查" << endl;
        cout << "5.退出" << endl;
        cout << "请输入您的指令：";
        int order;
        std::cin >> order;
        switch (order) {
        case 1:sql.add(); break;
        case 2:sql.del(); break;
        case 3:sql.correct(); break;
        case 4:sql.display(); break;
        case 5:return 0;
        }
    }

}

四、配置：

1. 包含目录：MYSQL\include

2. 库目录：MYSQL\lib

3. 调试环境：PATH=...\MYSQL\bin

4. 链接器->输入->附加依赖项：libmysql.lib

5. 将libmysql.dll添加到项目文件夹中