C语言简单操作MYSQL数据库的增删查找

下面的代码只是简单的运用MYSQL命令

#include<stdio.h>
#include<mysql.h>   //所需头文件
#include<string.h>
#include<stdlib.h>

MYSQL *conn_prt;     //创造一个MYSQL句柄
MYSQL_RES *res;
MYSQL_ROW row;

/*一些操作的命令起始部分*/
char select_head[50] = "select * from ";
char desc_head[50] = "desc ";
char insert_head[200] = "insert into ";
char drop_msg_head[50] = "delete from ";
char change_base_head[50] = "use ";

/*初始化mysql句柄*/
void self_init()
{
	conn_prt = mysql_init(NULL);
}

/*连接mysql数据库*/
void self_connect()
{
	/*假设我的云服务器IP为118.89.20.60，密码为123456,进入的数据库名字为zje*/
	if(!mysql_real_connect(conn_prt,"118.89.20.60","root",
		"123456","zje",0,NULL,0))
	{
		printf("failed to connect:%s\n",mysql_error(conn_prt));
		exit(0) ;
	}
	printf("connect success!\n");

	/*如果本机测试的话，上面的语句可改为*/
	/*
		printf("本机测试\n");
		if(!mysql_real_connect(conn_prt,"localhost","root",
			"123456","zje",0,NULL,0))
		{
			printf("failed to connect:%s\n",mysql_error(conn_prt));
			exit(0) ;
		}
		printf("connect success!\n");
	*/
}

/*打印某个数据库中的某个表*/
void print_table()
{
	int t;
	char table_name[50];
	char query[50];
	bzero(query,50);
	bzero(table_name,50);

	strcpy(query,select_head);

	puts("please enter table name:");
	scanf("%s",table_name);

	strcat(query,table_name);
	t = mysql_real_query(conn_prt,query,strlen(query));
	if(t)
	{
		printf("failed to query:%s\n",mysql_error(conn_prt));
		return ;
	}
	printf("query success!\n");

	res = mysql_store_result(conn_prt);
	while(row = mysql_fetch_row(res))
	{
		for(t = 0;t<mysql_num_fields(res);t++)
		{
			printf("%s\t",row[t]);
		}
		printf("\n");
	}

	return ;

}

/*打印某个表的字段有哪些*/
void desc_table()
{
	int t;
	char table_name[50];
	char desc_query[50];
	bzero(table_name,50);
	bzero(desc_query,50);

	strcpy(desc_query,desc_head);

	puts("please enter the table name:");
	scanf("%s",table_name);

	strcat(desc_query,table_name);

	t = mysql_real_query(conn_prt,desc_query,strlen(desc_query));
	if(t)
	{
		printf("failed to query:%s\n",mysql_error(conn_prt));
		return ;
	}
	res = mysql_store_result(conn_prt);
	while(row = mysql_fetch_row(res))
	{
		for(t = 0;t<mysql_num_fields(res);t++)
		{
			printf("%s\t",row[t]);
		}
		printf("\n");
	}
	return ;
}

/*往某个表中插入数据*/
void insert_msg()
{
	int t;
	char insert_query[200];
	char table_name[50];
	char field[100];
	char left[5]="(";
	char right[5]=") ";
	char values[50]="values";
	char message[200]={0};

	bzero(field,100);
	bzero(table_name,50);
	bzero(message,200);

	strcpy(insert_query,insert_head);   //insert into

	puts("please enter table_name:");
	scanf("%s",table_name);

	puts("please enter the Field you want to write:(以逗号分隔)");
	scanf("%s",field);

	puts("please write the message to the field:(以逗号分隔)");
	scanf("%s",message);

	/*把几个变量字符串连接成一个完整的mysql命令*/
	strcat(insert_query,table_name);
	strcat(insert_query,left);
	strcat(insert_query,field);
	strcat(insert_query,right);
	strcat(insert_query,values);
	strcat(insert_query,left);
	strcat(insert_query,message);
	strcat(insert_query,right);
	printf("%s\n",insert_query);

	t = mysql_real_query(conn_prt,insert_query,strlen(insert_query));
	if(t)
	{
		printf("failed to query:%s\n",mysql_error(conn_prt));
		return ;
	}

	printf("OK\n");
}

/*删除某个表中的某个数据，以名字为基准*/
void drop_msg_table()
{
	int t;
	char drop_query[200]={0};
	char table_name[20]={0};
	char del_name[20]={0};

	strcpy(drop_query,drop_msg_head);

	puts("please enter the table:");
	scanf("%s",table_name);

	puts("please enter del name:");
	scanf("%s",del_name);

	strcat(drop_query,table_name);
	strcat(drop_query," where name =\"");
	strcat(drop_query,del_name);
	strcat(drop_query,"\"");
	printf("%s\n",drop_query);

	t = mysql_real_query(conn_prt,drop_query,strlen(drop_query));
	if(t)
	{
		printf("failed to query:%s\n",mysql_error(conn_prt));
		return ;
	}

	printf("OK\n");
	return ;
}

/*改变身处的数据库*/
void change_base()
{
	int t;
	char change_based_query[100]={0};
	char base_name[20]={0};
	strcpy(change_based_query,change_base_head);

	puts("please enter the base name you want to change:");
	scanf("%s",base_name);

	strcat(change_based_query,base_name);

	t = mysql_real_query(conn_prt,change_based_query,strlen(change_based_query));
	if(t)
	{
		printf("failed to query:%s\n",mysql_error(conn_prt));
		return ;
	}
	printf("OK\n");
	return ;
}

/*打印某个数据库中有哪些表*/
void show_all_tables()
{
	int t;
	char query[50] ="show tables";
	t = mysql_real_query(conn_prt,query,strlen(query));
	if(t)
	{
		printf("failed to query:%s\n",mysql_error(conn_prt));
		return ;
	}
	
	res = mysql_store_result(conn_prt);
	while(row = mysql_fetch_row(res))
	{
		for(t = 0;t<mysql_num_fields(res);t++)
		{
			printf("%s\t",row[t]);
		}
		printf("\n");
	}
	return ;
}

/*打印存在的所有数据库*/
void show_all_bases()
{
	int t;
	char query[50] ="show databases";
	t = mysql_real_query(conn_prt,query,strlen(query));
	if(t)
	{
		printf("failed to query:%s\n",mysql_error(conn_prt));
		return ;
	}
	
	res = mysql_store_result(conn_prt);
	while(row = mysql_fetch_row(res))
	{
		for(t = 0;t<mysql_num_fields(res);t++)
		{
			printf("%s\t",row[t]);
		}
		printf("\n");
	}
	return ;
}

char dir[200] ={"   \
\t1.打印表       \
\t2.查询某个表的格式 \
\t3.往某个表插入数据\n	\
\t4.删除某表的某一数据		\
\t5.选择另外一个数据库		\
\t\n6.显示所有表  \
\t 7.显示所有数据库	\
"};

int main()
{
	self_init();
	self_connect();
	printf("%s\n",dir);
	int type;
	while(1)
	{
		puts("please choice option:");
		scanf("%d",&type);
		switch(type)
		{
			case 1:print_table();break;
			case 2:desc_table();break;
			case 3:insert_msg();break;
			case 4:drop_msg_table();break;
			case 5:change_base();break;
			case 6:show_all_tables();break;
			case 7:show_all_bases();break;
		}
	}

	return 0;
}