写在前面
简单地在vs中实现了一下对于mysql的增删改查的实现内容,感觉最重要的一句话api为:
int STDCALL mysql_query(MYSQL *mysql, const char *q);
所有的语句的实现都是经过这一语句!后面的api的参考博客值得收藏!
程序
//初始化
bool ConnectDatabase(MYSQL &mysql)
{
mysql_init(&mysql);
if (!mysql_real_connect(&mysql, "localhost", "root", "password", "database name", 3306, NULL
, 0)) {
printf("Error connecting to database:%s\n", mysql_error(&mysql));
return false;
}
else {
mysql_query(&mysql, "set names gbk");
printf("Connected..\n");
return true;
}
}
//插入数据函数
bool InsertData(MYSQL& mysql,const char *s1,const char *s2, float f1, float f2, float f3, float f4)
{
char insertsql[1024];
int len = sprintf_s(insertsql, 256, "INSERT INTO table_name values ('%s','%s',%f,%f,%f,%f);", s1, s2, f1, f2, f3, f4);
printf("%s\n", insertsql);
if (len < 0) {
printf("Insert data failed!\n");
return false;
}
if (mysql_query(&mysql, insertsql) == 0) {
printf("Insert data success!\n");
return true;
}
else {
printf("Insert failed (%s)\n", mysql_error(&mysql));
return false;
}
}
//选择函数
//没有where的情况
bool SelectData(MYSQL &mysql, const char *columns_name,const char *table_name)
{
char querysql[1024];
int len = sprintf_s(querysql, 256, "SELECT %s FROM %s;", columns_name, table_name);
printf("%s\n", querysql);
if (len < 0) {
printf("Select data failed!\n");
return false;
}
if (mysql_query(&mysql, querysql) == 0) {
printf("Select data success!\n");
}
else {
printf("Select failed (%s)\n", mysql_error(&mysql));
return false;
}
MYSQL_RES *res; //返回行的查询结果集
MYSQL_FIELD *fd; //字段列数组
MYSQL_ROW column;
char field[32][32];
//查询返回的数据
res = mysql_store_result(&mysql);
//打印数据行数
printf("number of dataline returned:%d\n", mysql_affected_rows(&mysql));
//获取列数
int column_num = mysql_num_fields(res);
//输出选择完的列名称
for (int i = 0; fd = mysql_fetch_field(res); i++)
strcpy_s(field[i], strlen(fd->name) + 1, fd->name);
for (int i = 0; i < column_num; i++)
printf("%10s\t", field[i]);
printf("\n");
//输出每一行的数据
while (column = mysql_fetch_row(res))
{
for (int i = 0; i < column_num; i++)
printf("%10s\t", column[i]);
printf("\n");
}
return true;
}
/*
重载有where的情况
*/
bool SelectData(MYSQL &mysql, const char *columns_name, const char *table_name, const char *limited_info)
{
char querysql[1024];
int len = sprintf_s(querysql, 256, "SELECT %s FROM %s WHERE %s;", columns_name, table_name, limited_info);
printf("%s\n", querysql);
if (len < 0) {
printf("Select data failed!\n");
return false;
}
if (mysql_query(&mysql, querysql) == 0) {
printf("Select data success!\n");
}
else {
printf("Select failed (%s)\n", mysql_error(&mysql));
return false;
}
MYSQL_RES *res; //返回行的查询结果集
MYSQL_FIELD *fd; //字段列数组
MYSQL_ROW column;
char field[32][32];
//查询返回的数据
res = mysql_store_result(&mysql);
//打印数据行数
printf("number of dataline returned:%d\n", mysql_affected_rows(&mysql));
//获取列数
int column_num = mysql_num_fields(res);
//输出选择完的列名称
for (int i = 0; fd = mysql_fetch_field(res); i++)
strcpy_s(field[i], strlen(fd->name) + 1, fd->name);
for (int i = 0; i < column_num; i++)
printf("%10s\t", field[i]);
printf("\n");
//输出每一行的数据
while (column = mysql_fetch_row(res)) //返回结果集中的下一行
{
for (int i = 0; i < column_num; i++)
printf("%10s\t", column[i]);
printf("\n");
}
return true;
}
//修改操作
bool UpdateData(MYSQL &mysql, const char *table_name, const char *modified_info, const char *limitde_info)
{
char updatesql[1024];
int len = sprintf_s(updatesql, 256, "UPDATE %s SET %s WHERE %s;", table_name, modified_info, limitde_info);
printf("%s\n", updatesql);
if (len < 0) {
printf("Update data failed!\n");
return false;
}
if (mysql_query(&mysql, updatesql) == 0) {
printf("Update data success!\n");
return true;
}
else {
printf("Update failed (%s)\n", mysql_error(&mysql));
return false;
}
}
//删除数据函数
bool DeleteData(MYSQL &mysql, const char *table_name, const char *limited_info)
{
char deletesql[1024];
int len = sprintf_s(deletesql, 256, "DELETE FROM %s WHERE %s;", table_name, limited_info);
printf("%s\n", deletesql);
if (len < 0) {
printf("Delete data failed!\n");
return false;
}
if (mysql_query(&mysql, deletesql) == 0) {
printf("Delete data success!\n");
return true;
}
else {
printf("Delete failed (%s)\n", mysql_error(&mysql));
return false;
}
}
参考博客
程序参考:https://www.cnblogs.com/47088845/p/5706496.html
api参考:https://blog.csdn.net/lynnucas/article/details/49430039