C++链接MySQL数据库实例应用

来源：互联网发布：软件是无形资产编辑：程序博客网时间：2024/05/29 19:01

应用需求：将目录表（含有id(编号),tablename(物种表),kind(该表中物种所属的类)等属性））中含有的每个物种表（含有id(编号),name(物种名)等）中所有的物名读出，并插入表keywords（含有id(编号),name(物名),keyword(该物种所属类的编号)）中。

c++实现代码如下：

/*Author:IbsenDate:2015.12.06*/#include <stdio.h>#include <windows.h>#include <mysql.h>MYSQL mysql;MYSQL_RES *res,*res_son; //返回行的一个查询结果MYSQL_ROW column,column_son; //一个行数据的类型安全(type-safe)的表示，表示数据行的列MYSQL_FIELD *fd,*fd_son; //字段列素组char query[200]; //查询语句char str[100][100]; //存放查询结果char str_son[100][100];#pragma comment(lib,"wsock32.lib")#pragma comment(lib,"libmysql.lib")bool Query_son(char *kind){mysql_query(&mysql,"set names gbk"); //设置编码格式int judge=mysql_query(&mysql,query); //查询//获取结果集if(!judge){printf("query succeed!\n");res_son=mysql_store_result(&mysql); //保存查询到的数据到resif(res_son){int i,j;printf("number of results:%d\n",mysql_num_rows(res_son));for(i=0;fd_son=mysql_fetch_field(res_son);i++) //获取列名strcpy(str_son[i],fd_son->name);j=mysql_num_fields(res_son);//printf("j=%d\n",j);            for(i=0;i<j;i++)              printf("%s\t",str_son[i]); //输出列名            printf("\n\n");            while(column_son=mysql_fetch_row(res_son))//获取具体的数据            {                for(i=0;i<j;i++)                {                //printf("%s\n",column_son[i]);                sprintf(query,"insert into keywords values(NULL,'%s','%s')",column_son[i],kind);                //printf("%s\n",query);mysql_query(&mysql,"START TRANSACTION");                 mysql_query(&mysql,query);mysql_query(&mysql,"COMMIT");                 }                //printf("\n");    }}}else{printf("query failed(%s)!\n",mysql_error(&mysql));return false;}}bool Query(){mysql_query(&mysql,"set names gbk"); //设置编码格式int judge=mysql_query(&mysql,"select tablename,id from tableinfo"); //查询//获取结果集if(!judge){printf("query succeed!\n");res=mysql_store_result(&mysql); //保存查询到的数据到resif(res){int i,j;printf("number of results:%d\n",mysql_num_rows(res));for(i=0;fd=mysql_fetch_field(res);i++) //获取列名strcpy(str[i],fd->name);j=mysql_num_fields(res); //j=2//printf("j=%d\n",j);            for(i=0;i<j;i++)              printf("%s\t",str[i]); //输出列名            printf("\n\n\n");            while(column=mysql_fetch_row(res))//获取具体的数据            {printf("%s\n",column[0]);                sprintf(query,"select name from %s",column[0]);Query_son(column[1]);                                printf("\n");    }}}else{printf("query failed(%s)!\n",mysql_error(&mysql));return false;}}int main(){       //MYSQL mysql;       mysql_init(&mysql);  //初始化mysql结构   //// localhost:服务器 root为账号密码 test为数据库名 3306为端口       if(!mysql_real_connect(&mysql,"localhost","root","12345","szlybz",3306,NULL,0))              printf("\n连接数据库时发生错误!\n");       else              printf("\n连接数据库成功!\n");   //mysql_query(&mysql,"create table keywords(id int auto_increment not null primary key,name char(100) not null,keyword char(5) not null)");         Query();       mysql_close(&mysql);  //释放数据库         return 0;}

将目录表中的各表的firstname,secondname,thirdname属性中不为空的数据读出并插入到keywords表中：

/*Author:IbsenDate:2015.12.06*/#include <stdio.h>#include <windows.h>#include <mysql.h>#include <string.h>MYSQL mysql;MYSQL_RES *res,*res_son; //返回行的一个查询结果MYSQL_ROW column,column_son; //一个行数据的类型安全(type-safe)的表示，表示数据行的列MYSQL_FIELD *fd,*fd_son; //字段列素组char query[200]; //查询语句char str[100][100]; //存放查询结果char str_son[100][100];#pragma comment(lib,"wsock32.lib")#pragma comment(lib,"libmysql.lib")bool Query_son(char *kind){mysql_query(&mysql,"set names gbk"); //设置编码格式int judge=mysql_query(&mysql,query); //查询//获取结果集if(!judge){printf("query succeed!\n");res_son=mysql_store_result(&mysql); //保存查询到的数据到resif(res_son){int i,j;printf("number of results:%d\n",mysql_num_rows(res_son));for(i=0;fd_son=mysql_fetch_field(res_son);i++) //获取列名strcpy(str_son[i],fd_son->name);j=mysql_num_fields(res_son);//printf("j=%d\n",j);            for(i=0;i<j;i++)              printf("%s\t",str_son[i]); //输出列名            printf("\n\n");            while(column_son=mysql_fetch_row(res_son))//获取具体的数据            {                for(i=0;i<j;i++)                {                //printf("%s\n",column_son[i]);                sprintf(query,"insert into keywords values(NULL,'%s','%s')",column_son[i],kind);                //printf("%s\n",query);if(strlen(column_son[i])>1&&strcmp(column_son[i],"null")!=0){mysql_query(&mysql,"START TRANSACTION");                 mysql_query(&mysql,query);mysql_query(&mysql,"COMMIT"); }                }                //printf("\n");    }}}else{printf("query failed(%s)!\n",mysql_error(&mysql));return false;}}bool Query(){mysql_query(&mysql,"set names gbk"); //设置编码格式int judge=mysql_query(&mysql,"select tablename,id from tableinfo"); //查询//获取结果集if(!judge){printf("query succeed!\n");res=mysql_store_result(&mysql); //保存查询到的数据到resif(res){int i,j;printf("number of results:%d\n",mysql_num_rows(res));for(i=0;fd=mysql_fetch_field(res);i++) //获取列名strcpy(str[i],fd->name);j=mysql_num_fields(res); //j=2//printf("j=%d\n",j);            for(i=0;i<j;i++)              printf("%s\t",str[i]); //输出列名            printf("\n\n\n");            while(column=mysql_fetch_row(res))//获取具体的数据            {printf("%s\n",column[0]);                sprintf(query,"select firstname,secondname,thirdname from %s",column[0]);Query_son(column[1]);                                printf("\n");    }}}else{printf("query failed(%s)!\n",mysql_error(&mysql));return false;}}int main(){       //MYSQL mysql;       mysql_init(&mysql);  //初始化mysql结构   //// localhost:服务器 root为账号密码 test为数据库名 3306为端口       if(!mysql_real_connect(&mysql,"localhost","root","12345","szlybz",3306,NULL,0))              printf("\n连接数据库时发生错误!\n");       else              printf("\n连接数据库成功!\n");   //mysql_query(&mysql,"create table keywords(id int auto_increment not null primary key,name char(100) not null,keyword char(5) not null)");         Query();       mysql_close(&mysql);  //释放数据库         return 0;}

0 0