mysql函数数据集_MYSQL函数的使用，获取数据库名，数据库表。

http://blog.chinaunix.net/uid-677314-id-379541.html

摘在网上，对于编写程序可以作为参考。首先，包括头文件。#include 编译方法：$gcc -I/usr/include/mysql -L/usr/lib/mysql myapp.c -o myapp  -lmysqlclient程序：connect_simplest.c#include #include MYSQL *conn;/* pointer to connection handler */intmain (intargc,char*argv[] ){//初始化mysql的句柄connconn = mysql_init (NULL);//连接mysql数据库mysql_real_connect (conn,/* pointer to connection handler */"localhost",/* host to connect to */"user_name",/* user name */"password",/* password */"test",/* database to use */0,/* port (default 3306) */NULL,/* socket or /var/lib/mysql.sock */0);/* flags (none) *///关闭数据库，句柄mysql_close ( conn );return 0;}compilation:gcc -Wall connect_simplest.c -lmysqlclientIf mysql.h is not in a standard place: -I/path/to/the/mysql.If libmysqlclient.so or libmysqlclient.a is not in a standardplace: -L/path/to/the/mysqlclient/library.(match better use Makefile)For more secure connection you should usestruct optionfrom conn.hMYSQL *conn;MYSQL_RES *res;MYSQL_ROW row;const char* groups[] = {"client",NULL};structoption long_options[] ={{"host",required_argument,NULL,'h'},{"user",required_argument,NULL,'u'},{"password",optional_argument,NULL,'p'},{"port",required_argument,NULL,'P'},{"socket",required_argument,NULL,'S'},{0,0,0,0}};unsigned longhow_rows (void);voidprint_error(char*);conn.c#include #include #include #include  // for getopt_long#include "conn.h"intmain (intargc,char* argv[]){inti, c, option_index;chardatabases[15][30];char*pass =NULL;char*user =NULL;char*host =NULL;char*sock =NULL;unsigned intport =0;// needs for operations required by load_defaults().my_init();// reads /etc/.my.cnf or ${HOME}/.my.cnf fileload_defaults("my",groups,&argc,&argv);// allows assigning values for host, pass...while((c=getopt_long(argc,argv,"h:p:u:P:S:",long_options,&option_index)) !=EOF){switch(c){case 'h':host = optarg;break;case 'u':user = optarg;break;case 'p':pass = optarg;break;case 'P':port = (unsigned int) atoi(optarg);break;case 'S':sock = optarg;break;}}//=================================================if( (conn = mysql_init(NULL)) ==NULL){print_error("mysql_init()");exit(EXIT_FAILURE);}if( (mysql_real_connect(conn,host,user,pass,"",0,NULL,0))==NULL){print_error("mysql_real_connect()");exit(EXIT_FAILURE);}mysql_query(conn,"show databases");res = mysql_store_result(conn);i=0;system("clear");printf("\nThere are %lu databases\n",how_rows());puts("=====================");while( row = mysql_fetch_row(res) ){strcpy(databases[i],row[0]);puts(databases[i]);i++;}mysql_close(conn);return 0;}//----------------------------------------------------------unsigned longhow_rows (void)//----------------------------------------------------------{return(unsigned long) mysql_num_rows(res);}//----------------------------------------------------------voidprint_error(char* msg)//----------------------------------------------------------{fprintf(stderr,"%s\n",msg);if( conn !=NULL){fprintf(stderr,"Error: %u %s\n",mysql_errno(conn),mysql_error(conn));}}OUTPUT:// There are 16 databases// =====================// School// Student// TransAct// ana// bank// billing// helpfile// helpfile1// mysql// phone// sales_analis// samp_db// student// studentdb// test// user1Of course, you have to hide your password from the direct viewing.I usually use that I createdfor similar purposes.simp_conn_pass.c#include #include MYSQL *conn;MYSQL_RES *res;MYSQL_ROW row;intmain (){conn = mysql_init (NULL);mysql_real_connect ( conn,"localhost","ana",password(),"bank",0,NULL,0);mysql_query(conn,"show tables");res = mysql_store_result(conn);while( row = mysql_fetch_row(res) ){fprintf (stdout,"%s\n", row[0]);}fprintf (stdout,"\n%lu rows affected\n",(unsigned long) mysql_num_rows(res));mysql_close(conn);exit(0);}OUTPUT:// BANK_WORKER// BRANCHES// CHECKING_ACCT// CHECKING_REP// CLOSE_CHECK_ACCT// CLOSE_CUSTOMERS// CLOSE_SAVINGS_ACCT// CUSTOMERS// LOAN_ACCT// SAVINGS_ACCT// SAVINGS_REP 11 rows affectedSome Usagesome_usage.c #include #include #include #include MYSQL *conn;MYSQL_RES *res;MYSQL_ROW row;unsigned longhow_rows (void);voidprint_data(void);char* to_upper (char*);voidprint_table(void);voidprint_line (void);//----------------------------------------------------------intmain (intargc,char* argv[]){unsigned longul;inti;chardatabases[25][30];charquery[30];chartemp[30], table[30];conn = mysql_init (NULL);mysql_real_connect (conn,"","ana",password(),"",3306,NULNULL;mysql_query(conn,"show databases");res = mysql_store_result(conn);ul = how_rows();i=0;system("clear");printf("\nThere are %lu databases\n",ul);puts("=====================");while( row = mysql_fetch_row(res) ){strcpy(databases[i],row[0]);puts(databases[i]);i++;}for( i =0; i < ul; i++ ){if( strcmp(databases[i],"mysql") ==0)continue;sprintf(query,"use %s",databases[i]);mysql_query(conn,query);strcpy(temp,databases[i]);fprintf(stdout,"\nThe database %s consists tables:\n",to_upper(temp));puts("==========================================");print_data();getchar();// usleep(900000);}while(1){fprintf(stdout,"What database do you want to use? (0-quit): ");fgets(temp,29,sstdin;temp[strlen(temp)-1] ='\0';if( strcmp(temp,"0") ==0)break;for( i =0; i < ul; i++ ){if( strcmp(temp,databases[i]) ==0){while(1){fprintf(stdout,"What table do you want to see? ");fprintf(stdout,"(0-quit): ");fgets(table,29,sstdin;table[strlen(table)-1] ='\0';if( strcmp(table,"0") ==0)break;sprintf(query,"select * from %s.%s",temp,table);mysql_query(conn,query);print_table();}}}}mysql_close(conn);exit(0);}//----------------------------------------------------------unsigned longhow_rows (void)//----------------------------------------------------------{return(unsigned long) mysql_num_rows(res);}//----------------------------------------------------------voidprint_data(void)//----------------------------------------------------------{mysql_query(conn,"show tables");res = mysql_store_result(conn);while( row = mysql_fetch_row(res) ){fprintf(stdout,"%s\n",row[0]);}}//----------------------------------------------------------char* to_upper (char* name)//----------------------------------------------------------{char*p = name;while(*p){*p = toupper(*p);p++;}returnname;}//----------------------------------------------------------voidprint_table(void)//----------------------------------------------------------{MYSQL_FIELD *field;unsigned inti, j, column_length;res = mysql_store_result(conn);mysql_field_seek(res,0);for( i =0; i < mysql_num_fields(res); i++ ){field = mysql_fetch_field(res);column_length = strlen(field->name);if( column_length < field->max_length )column_length = field->max_length;if( column_length <4&& !IS_NOT_NULL(field->flags) )column_length =4;field->max_length = column_length;}print_line();fputc('\n',stdstdoutmysql_field_seek(res,0);for( i =0; i < mysql_num_fields(res); i++ ){field = mysql_fetch_field(res);fprintf(stdout," %-*s ",field->max_length,field->name);}fputc('\n',stdstdoutprint_line();fputc('\n',stdstdoutwhile((row = mysql_fetch_row(res)) !=NULL){mysql_field_seek(res,0);for( i =0; i < mysql_num_fields(res); i++ ){field = mysql_fetch_field(res);if( row[i] ==NULL)fprintf(stdout," %-*s ",field->max_length,"NULL");elseif( IS_NUM(field->type) )fprintf(stdout," %*s ",field->max_length,row[i]);elsefprintf(stdout," %-*s ",field->max_length,row[i]);}fputc('\n',stdstdout}fputc('\n',stdstdoutmysql_free_result(res);}//----------------------------------------------------------voidprint_line (void)//----------------------------------------------------------{MYSQL_FIELD *field;unsigned inti, j;mysql_field_seek(res,0);for( i =0; i < mysql_num_fields(res); i++ ){field = mysql_fetch_field(res);for( j =0; j < field->max_length +2; j++ )fputc('=',ststdout}}OUTPUT:There are16databases=====================SchoolStudentTransActanabankbillinghelpfilehelpfile1mysqlphonesales_analissamp_dbstudentstudentdbtestuser1The database SCHOOL consists tables:==========================================school_studentThe database STUDENT consists tables:==========================================studentsThe database TRANSACT consists tables:==========================================destinationpurposetesttranskepperThe database ANA consists tables:==========================================addition_infocasesstudent_infostudent_recordsteacherteacher_roomtemptemp_studenttemp_teachertemp_teacher_roomThe database BANK consists tables:==========================================BANK_WORKERBRANCHESCHECKING_ACCTCHECKING_REPCLOSE_CHECK_ACCTCLOSE_CUSTOMERSCLOSE_SAVINGS_ACCTCUSTOMERSLOAN_ACCTSAVINGS_ACCTSAVINGS_REPThe database BILLING consists tables:==========================================The database HELPFILE consists tables:==========================================datafilehelpfileThe database HELPFILE1 consists tables:==========================================datafileThe database PHONE consists tables:==========================================addressphoneThe database SALES_ANALIS consists tables:==========================================departmentsquotesalesThe database SAMP_DB consists tables:==========================================absenceeventmemberpresidentscorestudentThe database STUDENT consists tables:==========================================studentsubjectsThe database STUDENTDB consists tables:==========================================studentsubjectsThe database TEST consists tables:==========================================tt1testtest1test2The database USER1 consists tables:==========================================What database do you want to use? (0-quit): bankWhat table do you want to see? (0-quit): BRANCHES================================================================BRANCH_NAME BRANCH_NUM BRANCH_ADDRESS================================================================BROOKLYN_BRANCH001 718Ave N, Brooklyn, NY11234MANHATTAN_BRANCH002 291 5th Ave, New York, NY,10070What table do you want to see? (0-quit): BANK_WORKER=============================================================B_SSN B_FNAME B_LNAME B_ID B_PHONE B_EXT=============================================================277-65-8877John Smith001 718-999-6655 313665-66-4423Kevin Walters001 718-999-6655 444775-99-7543Peter Kats002 212-999-8655 111777-65-6654Anna Katsnelson001 718-999-6655 222765-87-6540Sam Jefferson002 212-999-8655 123What table do you want to see? (0-quit):0What database do you want to use? (0-quit):0

//MYSQL m_sqlCon;

extern CDBConnectDlg dbConnectDlg;

dbConnectDlg.m_edit_dbip.GetWindowText(dbConnectDlg.m_strdbip);

dbConnectDlg.m_edit_dbuser.GetWindowText(dbConnectDlg.m_strdbuser);

dbConnectDlg.m_edit_dbpwd.GetWindowText(dbConnectDlg.m_strdbpwd);

dbConnectDlg.m_edit_dbport.GetWindowText(dbConnectDlg.m_strdbport);

dbConnectDlg.intport=atoi(dbConnectDlg.m_strdbport);

bool testcon=false;

MYSQL *conn;

MYSQL_RES *res;

MYSQL_ROW row;

conn=mysql_init(&dbConnectDlg.m_sqlCon);

testcon=mysql_real_connect(&dbConnectDlg.m_sqlCon,dbConnectDlg.m_strdbip,dbConnectDlg.m_strdbuser,dbConnectDlg.m_strdbpwd,"",dbConnectDlg.intport,NULL,0);

if (testcon)

{

mysql_query(conn,"show databases;");

res = mysql_store_result(conn);

int i=0;

//char m_databases[MAX_PATH][MAX_PATH];

memset(m_databases,0,MAX_PATH*MAX_PATH);

while ( row = mysql_fetch_row(res) )

{

strcpy(m_databases[i],row[0]);

puts(m_databases[i]);

i++;

}

m_dbcount=i;

mysql_close(&dbConnectDlg.m_sqlCon);

}

else

{

memset(m_databases,0,MAX_PATH*MAX_PATH);

}