一、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;
}
}
}
四、配置:
包含目录:MYSQL\include
库目录:MYSQL\lib
调试环境:PATH=...\MYSQL\bin
链接器->输入->附加依赖项:libmysql.lib
将libmysql.dll添加到项目文件夹中