C/C++ MySQL增删改查

测试MySQL连接

参考上一节 🎯C/C++连接MySQL

在配置好之后，测试MySQL是否正常连接

#include <iostream>
#include <string>
#include <mysql.h>
using namespace std;
const char* host = "127.0.0.1";
const char* user = "root";
const char* pw = "123456";
const char* database_name = "sm";
const int port = 3306;


struct Student
{
	int SNo;
	string CLno;
	string SName;
	int SSex;
	string SDate;
};
int main2()
{
	MYSQL* con = mysql_init(NULL);
	//设置字符编码
	mysql_options(con, MYSQL_SET_CHARSET_NAME, "gbk");
	if (!mysql_real_connect(con, host, user, pw, database_name, port, NULL, 0))
	{
		fprintf(stderr, "Failed to connect to database: Error: %s\n", mysql_error(con));
		return -1;
	}
	Student stu{ 20059, "020102", "李明", 1, "2000-04-09"};
	char sql[1024];
	sprintf(sql, "insert into student (SNo, CLno, SName, SSex, SDate) value(%d,'%s','%s',%d,'%s')", stu.SNo, stu.CLno.c_str(), stu.SName.c_str(), stu.SSex, stu.SDate.c_str());
	if (mysql_query(con, sql))
	{
		fprintf(stderr, "Failed to insert data: Error: %s\n", mysql_error(con));
		return -1;
	}
	cout << "insert sucessful\n";
	mysql_close(con);
	return 0;
}	

数据库-学生表

在MySQL中建库建表，表结构如下：

StudentManager.h

#pragma once
#include <iostream>
#include <string>
#include <vector>
#include <mysql.h>
using namespace std;
struct Student
{
	int SNo;
	string CLno;
	string SName;
	int SSex;
	string SDate;
};

class StudentManager
{
public:
	StudentManager();
	~StudentManager();
public:
	//单例模式
	static StudentManager* GetInstance()
	{
		static StudentManager StudentManager;
		return &StudentManager;
	}

public:
	bool insert_student(Student& t);
	bool delete_student(Student& t);
	bool update_student(Student& t);
	vector<Student> get_student(string condition="");

private:
	MYSQL* con;
	const char* host = "127.0.0.1";
	const char* user = "root";
	const char* pw = "123456";
	const char* database_name = "sm";
	const int port = 3306;
};

StudentManager.cpp

#include "StudentManager.h"

StudentManager::StudentManager()
{
	con = mysql_init(NULL);
	//设置字符编码
	mysql_options(con, MYSQL_SET_CHARSET_NAME, "gbk");
	if (!mysql_real_connect(con, host, user, pw, database_name,port, NULL, 0))
	{
		fprintf(stderr, "Failed to connect to database: Error: %s\n", mysql_error(con));
		exit(1);
	}
}

StudentManager::~StudentManager()
{
	mysql_close(con);
}

bool StudentManager::insert_student(Student& stu)
{
	char sql[1024];
	sprintf(sql, "insert into student (SNo, CLno, SName, SSex, SDate) value(%d,'%s','%s',%d,'%s')", stu.SNo, stu.CLno.c_str(), stu.SName.c_str(), stu.SSex, stu.SDate.c_str());
	if (mysql_query(con, sql))
	{
		fprintf(stderr, "Failed to insert data: Error: %s\n", mysql_error(con));
		return false;
	}
	cout << "insert sucessful\n";
	return true;
}

bool StudentManager::delete_student(Student& stu)
{
	char sql[1024];
	sprintf(sql, "delete from student where SNo=%d", stu.SNo);
	if (mysql_query(con, sql))
	{
		fprintf(stderr, "Failed to delete data: Error: %s\n", mysql_error(con));
		return false;
	}
	cout << "delete sucessful\n";
	return true;
}

bool StudentManager::update_student(Student& stu)
{
	char sql[1024];
	sprintf(sql, "update student SET CLno='%s', SName='%s', SSex= %d, SDate='%s' where SNo=%d", stu.CLno.c_str(), stu.SName.c_str(), stu.SSex, stu.SDate.c_str(), stu.SNo);
	if (mysql_query(con, sql))
	{
		fprintf(stderr, "Failed to update data: Error: %s\n", mysql_error(con));
		return false;
	}
	cout << "update sucessful\n";
	return true;
}

vector<Student> StudentManager::get_student(string condition)
{
	vector<Student> stuList;
	char sql[1024];
	sprintf(sql, "select * from student %s", condition.c_str());
	if (mysql_query(con, sql))
	{
		fprintf(stderr, "Failed to select data: Error: %s\n", mysql_error(con));
		return {};
	}
	cout << "select sucessful\n";
	MYSQL_RES* res = mysql_store_result(con);
	MYSQL_ROW row;
	while ((row = mysql_fetch_row(res)))
	{
		Student stu;
		stu.SNo = atoi(row[0]);
		stu.CLno = row[1];
		stu.SName = row[2];
		stu.SSex = atoi(row[3]);
		stu.SDate = row[4];
		stuList.push_back(stu);		
	}
	return stuList;
}

main.cpp

#include "StudentManager.h"

int main()
{
	Student stu{ 20059, "020102", "李龟", 0, "2000-04-09" };
	//增加
	StudentManager::GetInstance()->insert_student(stu);
	//更新
	StudentManager::GetInstance()->update_student(stu);
	//删除
	StudentManager::GetInstance()->delete_student(stu);
	//查询
	vector<Student> ret= StudentManager::GetInstance()->get_student("where SSex=1");
	for (auto& t : ret)
	{
		cout << t.SNo << " " << t.SName << " " << t.SDate << endl;
	}
	return 0;
}