应用需求:将目录表(含有id(编号),tablename(物种表),kind(该表中物种所属的类)等属性))中含有的每个物种表(含有id(编号),name(物种名)等)中所有的物名读出,并插入表keywords(含有id(编号),name(物名),keyword(该物种所属类的编号))中。
c++实现代码如下:
/*
Author:Ibsen
Date: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); //保存查询到的数据到res
if(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); //保存查询到的数据到res
if(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:Ibsen
Date: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); //保存查询到的数据到res
if(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); //保存查询到的数据到res
if(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;
}