C 语言操作 MySQL 数据库代码,C代码库,德仔网

代码语言
代码分类
【C】 C 语言操作 MySQL 数据库
作者:tang0pan / 发布于2014/6/9/ 836
我的编译器是ubuntu ,函数的设计是根据自己的使用习惯来设计的，拓展方便

	#include <stdio.h>
	#include <stdlib.h>
	#include "/usr/include/mysql/mysql.h" //我的ubuntu下是在这个位置
	#include <memory.h>
	#include <string.h>

	#ifndef mysql_api_h
	#define mysql_api_h
	#define HOST "localhost"
	#define USERNAME "root"
	#define PASSWORD "244372551"
	#define DATABASE "test"
	typedef unsigned int U8;
	typedef char C2;
	typedef char * PC2;

	typedef enum EBool
	{
	EBool_false = 0,
	EBool_true = 1,
	EBool_debug = 1
	}EBool;

	typedef struct SSqlLinkList
	{
	PC2 name;
	PC2 value;
	struct SSqlLinkList *next;
	}SSqlLinkList;
	#endif

	MYSQL my_connection;

	/**
	* the function is initialise the linkList
	* @return <head node>
	*/
	struct SSqlLinkList *InitialiazeSqlLinkList( SSqlLinkList *headPstr)
	{
	SSqlLinkList * head = NULL;
	head = ( SSqlLinkList *)malloc(sizeof(SSqlLinkList));
	if ( ! head)
	{
	 printf("Error:the head node is empty !\n");
	 exit(-1);
	}
	memset(head,0,sizeof(SSqlLinkList));
	head->value = NULL;
	head->name = NULL;
	head->next = NULL;
	headPstr = head;
	return headPstr;
	
	}

	/**
	* the function is get the input data and deposit into the linkList
	* @return <head node>
	*/
	struct SSqlLinkList *CreateSqlLinkList( SSqlLinkList *headPstr)
	{
	SSqlLinkList * head;
	SSqlLinkList * newNode = NULL;
	C2 val[100];
	C2 names[20];
	printf("\n\nthe input end by input: ##\n\n");
	printf("please input name , value : ");
	scanf("%s%s",names,val);
	getchar(); //eat the enter
	head = headPstr;
	while( strcmp(val,"##") != 0 )
	{
	 newNode = (SSqlLinkList *)malloc(sizeof(SSqlLinkList));
	 if ( !newNode)
	 {
	 printf("Error:the insert node is emoty !\n");
	 exit(-1);
	 }
	 newNode->name = (char *)malloc(sizeof(names));
	 newNode->value = (char *)malloc(sizeof(val));
	 head->next = newNode;
	 strcpy(newNode->name,names);
	 strcpy(newNode->value,val);
	 printf("please input name , value : ");
	 scanf("%s%s",names,val);
	 getchar(); //eat the enter
	 head = newNode;
	}
	return headPstr;
	}

	/**
	* the funtion is insert a node into the linkList
	* @return <head node>
	*/
	struct SSqlLinkList *InsertNodeToSingleLinkList( SSqlLinkList *headPstr,PC2 names, PC2 data)
	{
	SSqlLinkList * newNode = NULL;
	SSqlLinkList * p = NULL;
	p = headPstr;
	if( p != NULL )
	{
	 newNode = (SSqlLinkList *)malloc(sizeof(SSqlLinkList));
	 if ( !newNode)
	 {
	 printf("Error:the insert node is emoty !\n");
	 exit(-1);
	 }
	 if ( names != NULL )
	 {
	 newNode->name = (PC2)malloc(sizeof(names));
	 strcpy(newNode->name,names);
	 }
	 else
	 {
	 strcpy(newNode->name,"\0");
	 }
	 if ( data != NULL )
	 {
	 newNode->value = (PC2)malloc(sizeof(data));
	 strcpy(newNode->value,data);
	 }
	 else
	 {
	 strcpy(newNode->value,"\0");
	 }
	 newNode->next = p->next;
	 p->next = newNode;
	}
	return headPstr;
	}

	/**
	* the function is printf the linkList
	*/
	void DisplaySqlLinkList( SSqlLinkList *headPstr)
	{
	SSqlLinkList *p = NULL;
	p = headPstr->next;
	while( p != NULL )
	{
	 printf("%10s:\t",p->name);
	 printf("%s\t",p->value);
	 p = p->next;
	 printf("\n");
	}
	printf("\n");
	}

	/**
	* the function is free the struct memery
	*/
	void DestoryLinkList( SSqlLinkList *headPstr )
	{
	SSqlLinkList * p = NULL;
	SSqlLinkList * head = NULL;
	head = (SSqlLinkList *) headPstr;
	p = head->next;
	while( p != NULL )
	{
	 free(p->name);
	 free(p->value);
	 free(p);
	 p = p->next;
	}
	free(head);
	printf("\033[33mfree the memery Ok!\033[0m\n");
	}

	
	/**
	* the function is connect the database
	* return <EBool>
	*/
	EBool connect_sql()
	{
	EBool flag = EBool_false;
	
	//initialize the sql connect
	mysql_init(&my_connection);
	
	//create the database connect
	if ( NULL != mysql_real_connect( &my_connection, HOST, USERNAME, PASSWORD, DATABASE, 0, NULL, CLIENT_FOUND_ROWS ) )
	{
	 //set the encoding
	 mysql_query(&my_connection,"set names utf8");
	 //printf("connect success!\n");
	 flag = EBool_true;
	}
	else
	{
	 mysql_close( &my_connection );
	 printf("Sorry, the database connect fail! --%s\n",mysql_error(&my_connection));
	 exit(-1);
	}
	return flag;
	}

	/**
	* the function is send the instruct to the database
	* @return <EBool>
	*/
	EBool query( PC2 sql )
	{
	EBool flag = EBool_false;
	EBool debug = EBool_debug;
	if ( connect_sql())
	{
	 if ( debug )
	 {
	 printf("\nquery: %s\n",sql);
	 }
	 return mysql_query(&my_connection, sql);
	}
	return flag;
	}
	/**
	* the function is get one row recordes from database
	* @param sql -> the sql sentence
	* @return <SSqlLinkList type>
	*/
	struct SSqlLinkList *getOneRow( PC2 sql )
	{
	PC2 sql1;
	sql1 = sql;
	MYSQL_RES *res_ptr; /*指向查询结果的指针*/
	MYSQL_FIELD *field; /**/
	MYSQL_ROW result_row; /**/
	U8 colunm = 0;
	U8 i = 0,j = 0;

	SSqlLinkList *head = NULL;
	SSqlLinkList *p = NULL;
	head = InitialiazeSqlLinkList(head);
	
	if( ! query( sql1 ) )
	{
	 res_ptr = mysql_store_result(&my_connection);
	 if ( res_ptr )
	 {
	 colunm = mysql_num_fields(res_ptr);
	 if ( result_row = mysql_fetch_row(res_ptr) )
	 {
	 while( field = mysql_fetch_field(res_ptr) )
	 {
	 InsertNodeToSingleLinkList(head,field->name,result_row[i]);
	 i++;
	 };
	 }
	 }
	}
	return head;
	}

	/**
	* the function is insert data into the database
	* @param table -> table name
	* @param datas -> the insert datas,the type is a pointer array
	* @param filed -> the table filed name,teh type is a pointer array
	* @param num -> the filed's number
	* @return <EBool>
	*/
	EBool insert( C2 table[], PC2 datas[], PC2 filed[] ,U8 num )
	{
	EBool flag = EBool_false;
	U8 i = 0;
	U8 countSize = 0;
	C2 query_sql[] = "INSERT INTO ";
	C2 str1[] = " (";
	C2 str2[] = ")";
	C2 str3[] = ",";
	C2 str4[] = " VALUES(";
	C2 str5[] = "\'";
	PC2 *tempdatas;
	PC2 *tempfiled;
	tempdatas = datas;
	tempfiled = filed;
	if ( connect_sql())
	{
	 //to calculate the length of the string size
	 countSize = strlen(query_sql) + strlen(table) + strlen(str1)+ strlen(str2)*2 + strlen(str2)*num*2;
	 for(i = 0; i < num; i++)
	 {
	 countSize = countSize + strlen(*tempfiled) + strlen(*tempdatas);
	 tempdatas++;
	 tempfiled++;
	 }
	
	 C2 sqls[countSize];
	 sqls[0] = '\0';
	
	 /*connect the insert sql sentence*/
	 strcat(sqls,query_sql);
	 strcat(sqls,table);
	 strcat(sqls,str1);
	 for ( i = 0; i < num; i++ )
	 {
	 strcat(sqls,*filed);
	 if ( i+1 != num )
	 {
	 strcat(sqls,str3);
	 }
	 filed++;
	 }
	
	 strcat(sqls,str2);
	 strcat(sqls,str4);
	 for ( i = 0; i < num; i++ )
	 {
	
	 strcat(sqls,*datas);
	 if ( i+1 != num )
	 {
	 strcat(sqls,str3);
	 }
	 datas++;
	 }
	 strcat(sqls,str2);
	 if ( ! query(sqls) )
	 {
	 flag = EBool_true;
	 }
	
	}
	return flag;
	}

	/**
	* the function is update the tables's data
	* @param table -> table name
	* @param value -> the insert datas
	* @param filed -> the table filed name
	* @param where -> the condition
	* @return <EBool>
	*/
	EBool update( C2 table[],C2 filed[],C2 value[], C2 where[] )
	{
	EBool flag = EBool_false;
	C2 str0[] = "UPDATE ";
	C2 str1[] = " SET ";
	C2 str2[] = " WHERE ";
	C2 str3[] = " = ";
	U8 countSize = 0;
	if ( connect_sql() )
	{
	 //to calculate the length of the string size
	 countSize = strlen(str0) + strlen(str1) + strlen(str2) + strlen(str3) + strlen(table) + strlen(value)+ strlen(filed)+ strlen(where);
	 C2 sqls[countSize];
	 sqls[0] = '\0';
	
	 //mosaic the sql
	 strcat(sqls,str0);
	 strcat(sqls,table);
	 strcat(sqls,str1);
	 strcat(sqls,filed);
	 strcat(sqls,str3);
	 strcat(sqls,value);
	 strcat(sqls,str2);
	 strcat(sqls,where);
	
	 if ( ! query(sqls) )
	 {
	 flag = EBool_true;
	 }
	}
	return flag;
	}

	/**
	* the function is delete the specially recorde
	* @param table[] -> the database's table name
	* @param where[] -> delete conditions
	* @return <EBool>
	*/
	EBool deleteData( C2 table[], C2 where[] )
	{
	EBool flag = EBool_false;
	C2 str0[] = "DELETE FROM ";
	C2 str1[] = " WHERE ";
	U8 countSize = 0;
	if ( connect_sql() )
	{
	 //to calculate the length of the string size
	 countSize = strlen(table) + strlen(str0) + strlen(str1) + strlen(where);
	 C2 sqls[countSize];
	 sqls[0] = '\0';
	
	 //mosaic the sql
	 strcat(sqls,str0);
	 strcat(sqls,table);
	 strcat(sqls,str1);
	 strcat(sqls,where);
	
	 if ( ! query(sqls) )
	 {
	 flag = EBool_true;
	 }
	}
	return flag;
	}

	int main()
	{
	PC2 querySql;
	U8 rets = 0;
	SSqlLinkList *head;
	 querySql = "select * from user where Id = 1";
	head = getOneRow(querySql);
	DisplaySqlLinkList(head);
	DestoryLinkList(head);
	
	PC2 datas[] = {"\'tang\'","123456789"};
	PC2 filed[] = {"name","password"};
	//rets = insert("user",datas,filed,2);
	//
	rets = update("user","name","\'tang0pan\'","Id = 1");
	//printf("%d",rets);
	rets = deleteData("user", "Id = 4");
	printf("%d\n",rets);

	}
试试其它关键字
　MySQL　　操作MySQL　
同语言下
可能有用的
tang0pan贡献的其它代码(2)