C++连接MySQL数据库_c++ mysql

1. 使用Navicat Premium 15在root用户目录下创建一个数据库ship_test，在ship_test下创建一个表ship01，设计数据库如下：
   
2. 暂时手动添加一些数据，方便连接数据库之后进行查询
   
3. 其次，配置当前项目的属性：
   3-1 . 常规->附加包含目录
   C:\Program Files\MySQL\MySQL Server 8.0\include;
   
   3-2. 链接器->常规->附加库目录
   C:\Program Files\MySQL\MySQL Server 8.0\lib;

3-3. 链接器->输入->附加依赖项
添加libmysql.lib;

以上配置好之后即可使用与mysql数据库进行交互了

4. 包含头文件#include<mysql.h>进行测试

5. 出现问题：
   解决方法，找到C:\Program Files\MySQL\MySQL Server 8.0\lib下的libmysql.dll文件，将其复制到当前项目目录的\x64\Debug下，即：
   D:\vs++\CMSql\CMSql\x64\Debug

6. 增删改查

7. 代码集合

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

struct Student
{
	string name;
	int age;
	double score;
};

const char* host = "127.0.0.1";
const char* user = "root";
const char* psw = "123456";
const char* database_name = "ship_test";
const int port = 3306;

class MMSql
{
public:
	MMSql();
	~MMSql();
	MYSQL * getConnect();
	void updateID();
	void addToMySQL();
	void delToMySQL();
	void editToMySQL();
	void findToMySQL();
private:
	MYSQL *con;
};

MMSql::MMSql()
{
	//初始化mysql
	con = mysql_init(0);
	//VS的编译方式为GBK,因此设置MYSQL的编码方式为GBK
	mysql_options(con, MYSQL_SET_CHARSET_NAME, "GBK");

	//连接数据库
	if (!mysql_real_connect(con, host, user, psw, database_name, port, NULL, 0))
	{
		cout << mysql_error(con) << endl;
	}

}

MMSql::~MMSql()
{
	mysql_close(this->getConnect());
}

MYSQL * MMSql::getConnect()
{
	return this->con;
}

void MMSql::updateID()
{
	/删除后重新整理id的编号/
	const char* query[] = {
		//更新id字段的值，重新从1开始编号
		"SET @new_id = 0;",
		"UPDATE ship01 SET id = @new_id:=@new_id+1;",
		//重新设置自增序列的最小值为1
		"ALTER TABLE ship01 AUTO_INCREMENT = 1;"
	};

	// 执行多条Query语句
	int num_queries = sizeof(query) / sizeof(query[0]);
	for (int i = 0; i < num_queries; ++i) {
		if (mysql_query(this->getConnect(), query[i]) != 0) {
			cout << "执行Query语句失败: " << mysql_error(this->getConnect()) << endl;
			return ;
		}
	}
}

void MMSql::addToMySQL()
{
	Student stu;
	stu.name = "xiaowang";
	stu.age = 79;
	stu.score = 88.9;
	/增
	//通过query插入数据
	string query = "INSERT INTO ship01 (`name`, age, score) VALUES ('" + stu.name + "', " + to_string(stu.age) + ", " + to_string(stu.score) + ")";

	if (mysql_query(this->getConnect(), query.c_str()) != 0)
	{
		cout << mysql_error(this->getConnect()) << endl;
		mysql_close(this->getConnect());
		return; // Exit the program with an error code
	}
	cout << "添加成功!" << "当前数据为:" << endl;
	this->updateID();//更新数据
	this->findToMySQL();
}

void MMSql::delToMySQL()
{
	///删
//通过query删除数据
	string query2 = "DELETE FROM ship01 WHERE id=2;";
	if (mysql_query(this->getConnect(), query2.c_str()) != 0)
	{
		cout << mysql_error(this->getConnect()) << endl;
		mysql_close(this->getConnect());
		return; // Exit the program with an error code
	}
	cout << "删除成功!" << "当前数据为:" << endl;
	this->updateID();//更新数据
	this->findToMySQL();
}

void MMSql::editToMySQL()
{
	///改
	int num1 = 39;
	string name1 = "fd";
	string query1 = "update ship01 set score=" + to_string(num1) + " where name = " +"'"+ name1 + "'";
	if (mysql_query(this->getConnect(), query1.c_str()) != 0)
	{
		cout << mysql_error(this->getConnect()) << endl;
		mysql_close(this->getConnect());
		return; // Exit the program with an error code
	}
	cout << "修改成功!" << "当前数据为:" << endl;
	this->findToMySQL();
}

void MMSql::findToMySQL()
{
	查//
//通过query查询数据
	mysql_query(this->getConnect(), "SELECT * FROM ship01");
	//使用res收集查询到的数据
	MYSQL_RES *res = mysql_store_result(this->getConnect());
	if (!res)
	{
		cout << mysql_error(this->getConnect()) << endl;
		mysql_close(this->getConnect());
		return; // Exit the program with an error code
	}
	//取得收集到的数据的行数(便于遍历)
	int num_fields = mysql_num_fields(res);
	//取每一行的数据,打印
	MYSQL_ROW row;
	while ((row = mysql_fetch_row(res)))
	{
		for (int i = 0; i < num_fields; i++)
		{
			cout << row[i] << " ";
		}
		cout << endl;
	}
	//释放收集的数据
	mysql_free_result(res);
}


int main()
{
	MMSql *mysq = new MMSql();
	mysq->addToMySQL();

	//mysq->editToMySQL();

	//mysq->delToMySQL();

	cout << "Hello World!\n";
	return 0;
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176