vs2019 C++调用mysql记录
配置:
1.mysql==5.5.19
2.vs2019
3.Navicat Premium 16
一、安装Navicat
Navicat一款数据库可视化工具,安装过程参考:https://www.cnblogs.com/kkdaj/p/16260681.html
需要破解。
二、Mysql终端操作
这一部分主要是在mysql中新建一个”表“,便于后期插入数据。
1.查看mysql的databases
输入指令:
show databases;
可以查看现有的databases。
2.新建需要操作的database
同样的在mysql终端输入指令:
create database 数据库名;
例如
create database student_manager;
删除数据库指令
DROP DATABASE <database_name>; -- 直接删除数据库,不检查是否存在
或
DROP DATABASE [IF EXISTS] <database_name>;
3.mysql数据库中新建表格(tabel)
大概思路就是先选择数据库,再新建表格
1)使用数据库
对应指令:
USE database_name;
2)创建表格
对应指令:
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
...
);
例如:
create table student(student_id int,student_name varchar(100),class_name varchar(100));
打开Nvaicat可以查看当前数据库的可视化信息。例如现在在student_manager数据库中建立了一个名字为student的表格。
三、vs2019使用C++操作数据库
主要实现对表格的”增“,”删“,”改“,”查“
1.增-对表格插入数据
对应指令:
INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);
配置好vs2019后,写入如下代码:
#include <stdio.h>
#include <WinSock.h>
#include <Windows.h>
#include <mysql.h>
#include<iostream>
#include <opencv2/core.hpp>
#include <opencv2/imgcodecs.hpp>
#include <opencv2/highgui.hpp>
using namespace cv;
using namespace std;
MYSQL mysql; //mysql连接
MYSQL_RES* res; //一个结果集结构体
MYSQL_ROW row; //char** 二维数组,存放一条条记录
struct Student
{
int student_id;
string student_name;
string class_name;
};
void main()
{
mysql_init(&mysql);//初始化数据库
mysql_options(&mysql, MYSQL_SET_CHARSET_NAME, "GBK");
//连接数据库
if (mysql_real_connect(&mysql, "localhost", "root", "123456", "student_manager", 3306, NULL, 0))
{
printf("Mysql connected \n");
}
else
{
printf("Mysql connected fail :Error:%s\n",mysql_error(&mysql));//mysql_erroe函数可以输出数据库错误记录
}
Student stu;
stu.student_id = 433;
stu.student_name = "gg";
stu.class_name = "342";
char sql[1024];
sprintf(sql, "insert into student (student_id,student_name,class_name) values(%d,'%s','%s')", stu.student_id, stu.student_name.c_str(), stu.class_name.c_str());
if (mysql_query(&mysql, sql))
{
printf("Failed to insert data:Error:%s\n", mysql_error (&mysql));
}
//关闭数据库,释放资源
mysql_close(&mysql);
}
运行后,使用Nvaicat查看
表示插入数据成功。
2.删-删除表格对应信息
mysql中删除指令有三种,具体参考:https://cloud.tencent.com/developer/article/2389933
对应指令:
delete from tb_name where clause;
具体代码:
sprintf(sql, "DELETE FROM student WHERE student_id =%d", stu.student_id);
3.改-修改表格中对应值
参考:https://www.runoob.com/mysql/mysql-insert-query.html
具体代码:
stu.student_name = "hhhh";
stu.class_name = "rrrr";
sprintf(sql, "UPDATE student SET student_name ='%s',class_name = '%s'" "where student_id = %d", stu.student_name.c_str(), stu.class_name.c_str(),stu.student_id);
4.查-查询并输出表格数据
参考:https://www.runoob.com/mysql/mysql-select-query.html
具体代码块:
sprintf(sql, "SELECT * FROM student");
if (mysql_query(&mysql, sql))
{
printf("Failed to insert data:Error:%s\n", mysql_error (&mysql));
}
MYSQL_RES* res = mysql_store_result(&mysql);
MYSQL_ROW row;
while (row = mysql_fetch_row(res))
{
cout << "student_id = " << atoi(row[0]);
}
完整版代码:
#include <stdio.h>
#include <WinSock.h>
#include <Windows.h>
#include <mysql.h>
#include<iostream>
#include <opencv2/core.hpp>
#include <opencv2/imgcodecs.hpp>
#include <opencv2/highgui.hpp>
using namespace cv;
using namespace std;
MYSQL mysql; //mysql连接
MYSQL_RES* res; //一个结果集结构体
MYSQL_ROW row; //char** 二维数组,存放一条条记录
struct Student
{
int student_id;
string student_name;
string class_name;
};
void main()
{
mysql_init(&mysql);//初始化数据库
mysql_options(&mysql, MYSQL_SET_CHARSET_NAME, "GBK");
//连接数据库
if (mysql_real_connect(&mysql, "localhost", "root", "123456", "student_manager", 3306, NULL, 0))
{
printf("Mysql connected \n");
}
else
{
printf("Mysql connected fail :Error:%s\n",mysql_error(&mysql));//mysql_erroe函数可以输出数据库错误记录
}
Student stu;
stu.student_id = 433;
stu.student_name = "hhhh";
stu.class_name = "rrrr";
char sql[1024];
//增
//sprintf(sql, "insert into student (student_id,student_name,class_name) values(%d,'%s','%s')", stu.student_id, stu.student_name.c_str(), stu.class_name.c_str());
//删
//sprintf(sql, "DELETE FROM student WHERE student_id =%d", stu.student_id);
//改
//sprintf(sql, "UPDATE student SET student_name ='%s',class_name = '%s'" "where student_id = %d", stu.student_name.c_str(), stu.class_name.c_str(),stu.student_id);
//查
sprintf(sql, "SELECT * FROM student");
if (mysql_query(&mysql, sql))
{
printf("Failed to insert data:Error:%s\n", mysql_error (&mysql));
}
MYSQL_RES* res = mysql_store_result(&mysql);
MYSQL_ROW row;
while (row = mysql_fetch_row(res))
{
cout << "student_id = " << atoi(row[0]);
}
//关闭数据库,释放资源
mysql_close(&mysql);
}