VS MFC MySQL创建，增改删查

MySQL函数说明：25.2.2. C API函数概述_MySQL 中文文档      

window cmd 创建数据库指令：

登录：mysql -u root -p 密码
创建数据库： create database test;
查看创建的数据库：show databases;
使用创建的数据库：use test;
查看当前使用的数据库：select database();
创建数据表：create table students(
               id int(4) primary key auto_increment, //设为主键 自动增加  
               name varchar(40) not null, 
               age int(4) not null, 
               class varchar(40) not null);
查看创建的数据表：desc students;
添加数据：insert into student(name, age, class) values('姓名', '年龄', '班级');
查询数据：select * from students;
删除数据：delete from student where name='姓名'
更新数据：update student set age='新年龄', class='新班级' where name='姓名';
删除数据库：drop database test;

vs实现对数据库增改删查操作：

//增加数据
void NewDialog::OnBnClickedButton1()
{
	// TODO: 在此添加控件通知处理程序代码
	CString str_name, str_age, str_class, sql;

	GetDlgItemText(IDC_EDIT_NAME, str_name);
	GetDlgItemText(IDC_EDIT_AGE, str_age);
	GetDlgItemText(IDC_EDIT_CLASS, str_class);

	MYSQL mysql;
	mysql_init(&mysql);
	if (!mysql_real_connect(&mysql, "localhost", "root", "123456", "test", 3306, NULL, 0)) {
		MessageBox(_T("数据库链接失败！失败原因：" + CString(mysql_error(&mysql))));
	}

	MYSQL_RES *result;
	char *ch_query;
	mysql_query(&mysql, "SET NAMES GB2312");

	sql.Format(_T("insert into student(name, age, class) values('%s', '%s', '%s');"), str_name, str_age, str_class);

	int len = WideCharToMultiByte(CP_ACP, 0, sql, sql.GetLength(), NULL, 0, NULL, NULL);
	char *p = new char[len + 1];
	WideCharToMultiByte(CP_ACP, 0, sql, sql.GetLength(), p, len, NULL, NULL);
	p[len] = '\0';
	int r = mysql_real_query(&mysql, p, strlen(p));
	delete p;

	result = mysql_store_result(&mysql);
	mysql_free_result(result);

	mysql_close(&mysql);
}

//删除数据
void NewDialog::OnBnClickedButton2()
{
	// TODO: 在此添加控件通知处理程序代码
	CString str_name, str_age, str_class, sql;

	GetDlgItemText(IDC_EDIT_NAME, str_name);
	GetDlgItemText(IDC_EDIT_AGE, str_age);
	GetDlgItemText(IDC_EDIT_CLASS, str_class);

	MYSQL mysql;
	mysql_init(&mysql);
	if (!mysql_real_connect(&mysql, "localhost", "root", "123456", "test", 3306, NULL, 0)) {
		MessageBox(_T("数据库链接失败！失败原因：" + CString(mysql_error(&mysql))));
	}

	MYSQL_RES *result;
	char *ch_query;
	mysql_query(&mysql, "SET NAMES GB2312");

	sql.Format(_T("delete from student where name = '%s'"), str_name);

	int len = WideCharToMultiByte(CP_ACP, 0, sql, sql.GetLength(), NULL, 0, NULL, NULL);
	char *p = new char[len + 1];
	WideCharToMultiByte(CP_ACP, 0, sql, sql.GetLength(), p, len, NULL, NULL);
	p[len] = '\0';
	int r = mysql_real_query(&mysql, p, strlen(p));
	delete p;

	result = mysql_store_result(&mysql);
	mysql_free_result(result);

	mysql_close(&mysql);
}

//修改数据
void NewDialog::OnBnClickedButton3()
{
	// TODO: 在此添加控件通知处理程序代码
	CString str_name, str_age, str_class, sql;

	GetDlgItemText(IDC_EDIT_NAME, str_name);
	GetDlgItemText(IDC_EDIT_AGE, str_age);
	GetDlgItemText(IDC_EDIT_CLASS, str_class);

	MYSQL mysql;
	mysql_init(&mysql);
	if (!mysql_real_connect(&mysql, "localhost", "root", "123456", "test", 3306, NULL, 0)) {
		MessageBox(_T("数据库链接失败！失败原因：" + CString(mysql_error(&mysql))));
	}

	MYSQL_RES *result;
	char *ch_query;
	mysql_query(&mysql, "SET NAMES GB2312");

	sql.Format(_T("update student set age = '%s', class = '%s' where  name = '%s';"), str_age, str_class, str_name);

	int len = WideCharToMultiByte(CP_ACP, 0, sql, sql.GetLength(), NULL, 0, NULL, NULL);
	char *p = new char[len + 1];
	WideCharToMultiByte(CP_ACP, 0, sql, sql.GetLength(), p, len, NULL, NULL);
	p[len] = '\0';
	int r = mysql_real_query(&mysql, p, strlen(p));
	delete p;

	result = mysql_store_result(&mysql);
	mysql_free_result(result);

	mysql_close(&mysql);
}

//查找显示数据
void NewDialog::OnBnClickedButton4()
{
	// TODO: 在此添加控件通知处理程序代码
	CString str_name, str_age, str_class, sql;

	GetDlgItemText(IDC_EDIT_NAME, str_name);
	GetDlgItemText(IDC_EDIT_AGE, str_age);
	GetDlgItemText(IDC_EDIT_CLASS, str_class);

	MYSQL mysql;
	mysql_init(&mysql);
	if (!mysql_real_connect(&mysql, "localhost", "root", "123456", "test", 3306, NULL, 0)) {
		MessageBox(_T("数据库链接失败！失败原因：" + CString(mysql_error(&mysql))));
	}

	MYSQL_RES *result;
	char *ch_query;
	mysql_query(&mysql, "SET NAMES GB2312");

	sql.Format(_T("select * from student;"));

	int len = WideCharToMultiByte(CP_ACP, 0, sql, sql.GetLength(), NULL, 0, NULL, NULL);
	char *p = new char[len + 1];
	WideCharToMultiByte(CP_ACP, 0, sql, sql.GetLength(), p, len, NULL, NULL);
	p[len] = '\0';
	int r = mysql_real_query(&mysql, p, strlen(p));
	delete p;

	result = mysql_store_result(&mysql);
	
	MYSQL_ROW row;

	CString show_srt;
	while (row = mysql_fetch_row(result))
	{
		char str[1024] = "";
		sprintf_s(str, "id: %d name: %s age: %d class: %s\r\n", atoi(row[0]), row[1], atoi(row[2]), row[3]);

		int len = MultiByteToWideChar(CP_UTF8, 0, str, strlen(str), NULL, 0);
		wchar_t *p = new wchar_t[len + 1];
		MultiByteToWideChar(CP_UTF8, 0, str, strlen(str), p, len);
		p[len] = '\0';
		delete p;

		show_srt += str ;
	}

	SetDlgItemText(IDC_EDIT_SHOW, show_srt);

	mysql_free_result(result);

	mysql_close(&mysql);
}

界面如下：

 edit换行显示设置：选择显示区域->属性->Multiline->True

 VS 配置使用mysql库：

#pragma lib(lib, "libmysql.lib");

头文件：#include <mysql.h>

编译器选择：x64

项目->属性->常规->字符集->使用Unicode字符集

项目->属性->VC++目录->包含目录->C:\Program Files\MySQL\MySQL Server 8.0\include（mysql的库文件安装的位置）

项目->属性->VC++目录->库目录->C:\Program Files\MySQL\MySQL Server 8.0\lib（mysql的库文件安装的位置）

项目->属性->链接器->输入->附加依赖项->libmysql.lib

函数说明（具体含义百度即可）：

WideCharToMultiByte()：该函数映射一个unicode字符串到一个多字节字符串

MultiByteToWideChar()：该函数映射一个字符串到一个宽字符（unicode）的字符串。由该函数映射的字符串没必要是多字节字符组

头文件名：

#include <Windows.h>
#include <wchar.h>