c 查询mysql_C/C++连接MySQL数据库执行查询（对employees进行查询）

1 /*

2 C/C++连接MySQL数据库时，需要包含一个*.h的mysql头文件和一个mysql的lib文件3 1、初始化；4 2、连接数据库；5 3、执行sql查询语句；6 4、获取查询值；7 5、关闭8 */

9 #include

10 #include

11 #include

12 #include

13 #pragma comment(lib,"wsock32.lib")

14 #pragma comment(lib,"libmysql.lib")

15

16 MYSQL mysql;17 MYSQL_FIELD *fd; //字段列数组

18 char field[32][32]; //存字段名二维数组

19 MYSQL_RES *res; //行的一个查询结果集

20 MYSQL_ROW column; //数据行的列

21 char query[150]; //查询语句22

23 //函数声明

24 boolConnectDatabase();25 voidFreeConnect();26 boolQueryDatabase();27 boolInsertData();28 boolModifyData();29 boolDeleteData();30

31 int main(int argc, char **argv){32 ConnectDatabase();33 QueryDatabase();34 InsertData();35 QueryDatabase();36 ModifyData();37 QueryDatabase();38 //DeleteData();39 //QueryDatabase();

40 FreeConnect();41 system("pause");42 return 0;43 }44

45 //连接数据库

46 boolConnectDatabase(){47 //Gets or initializes a MYSQL structure

48 mysql_init(&mysql);49

50 //Connects to a MySQL server

51 const char host[] = "localhost";52 const char user[] = "root";53 const char passwd[] = "root";54 const char db[] = "employees";55 unsigned int port = 3306;56 const char *unix_socket =NULL;57 unsigned long client_flag = 0;58

59 //A MYSQL* connection handler if the connection was successful,60 //NULL if the connection was unsuccessful. For a successful connection,61 //the return value is the same as the value of the first parameter.

62 if (mysql_real_connect(&mysql, host, user, passwd, db, port, unix_socket, client_flag)){63 printf("The connection was successful.\n");64 return true;65 }66 else{67 //const char *mysql_error(MYSQL *mysql)68 //Returns the error message for the most recently invoked MySQL function69 //A null-terminated character string that describes the error.70 //An empty string if no error occurred.

71 printf("Error connecting to database:%s\n", mysql_error(&mysql));72 return false;73 }74 }75

76 //释放资源77 //void mysql_free_result(MYSQL_RES *result)78 //Frees the memory allocated for a result set by mysql_store_result(),79 //mysql_use_result(), mysql_list_dbs(), and so forth.80 //When you are done with a result set, you must free the memory it81 //uses by calling mysql_free_result().82 //Do not attempt to access a result set after freeing it.83

84 //void mysql_close(MYSQL *mysql)85 //Closes a previously opened connection.mysql_close() also deallocates86 //the connection handler pointed to by mysql if the handler was allocated automatically87 //by mysql_init() or mysql_connect().

88 voidFreeConnect(){89 mysql_free_result(res);90 mysql_close(&mysql);91 }92

93 //查询数据

94 boolQueryDatabase(){95 //将数据格式化输出到字符串

96 sprintf_s(query, "select * from departments");97 //设置编码格式

98 mysql_query(&mysql, "set names gbk");99

100 //int mysql_query(MYSQL *mysql, const char *stmt_str)101 //Executes an SQL query specified as a null-terminated string102 //Executes the SQL statement pointed to by the null-terminated string stmt_str.103 //Normally, the string must consist of a single SQL statement without a terminating semicolon (;) or \g.104 //If multiple-statement execution has been enabled, the string can contain several statements separated by semicolons.105 //Return Values:Zero for success.Nonzero if an error occurred.

106 if (mysql_query(&mysql, query)){107 printf("Query failed (%s)\n", mysql_error(&mysql));108 return false;109 }110 else{111 printf("query success\n");112 }113

114 //MYSQL_RES *mysql_store_result(MYSQL *mysql)115 //Retrieves a complete result set to the client116 //mysql_store_result() reads the entire result of a query to the client, allocates a MYSQL_RES structure, and places the result into this structure.117 //mysql_store_result() returns a null pointer if the statement did not return a result set(for example, if it was an INSERT statement).118 //mysql_store_result() also returns a null pointer if reading of the result set failed.119 //You can check whether an error occurred by checking whether mysql_error() returns a nonempty string.120 //Return Values: A MYSQL_RES result structure with the results.NULL(0) if an error occurred.

121 res = mysql_store_result(&mysql);122 if (!res){123 printf("Couldn‘t get result from %s\n", mysql_error(&mysql));124 return false;125 }126

127 //my_ulonglong mysql_affected_rows(MYSQL *mysql)128 //It returns the number of rows changed, deleted,129 //or inserted by the last statement if it was an UPDATE, DELETE, or INSERT.130 //For SELECT statements, returns the number of rows in the result set.

131 printf("number of dataline returned: %d\n", mysql_affected_rows(&mysql));132

133 //获取字段的信息134 //MYSQL_FIELD *mysql_fetch_field(MYSQL_RES *result)135 //Returns the definition of one column of a result set as a MYSQL_FIELD structure.136 //Call this function repeatedly to retrieve information about all columns in the result set.137

138 //获取列数

139 int j =mysql_num_fields(res);140

141 char *str_field[32]; //存储字段信息142

143 //获取字段名

144 for (int i = 0; i < j; i++){145 str_field[i] = mysql_fetch_field(res)->name;146 }147

148 //打印字段

149 for (int i = 0; i < j; i++)150 printf("%10s\t", str_field[i]);151 printf("\n");152

153 //打印查询结果154 //MYSQL_ROW mysql_fetch_row(MYSQL_RES *result)155 //Fetches the next row from the result set

156 while (column =mysql_fetch_row(res)){157 printf("%10s\t%10s\n", column[0], column[1]);158 }159 return true;160 }161

162 //插入数据

163 boolInsertData()164 {165 sprintf_s(query, "insert into departments values (‘dddd‘, ‘xxxxx‘);");166 if (mysql_query(&mysql, query)) {167 printf("Query failed (%s)\n", mysql_error(&mysql));168 return false;169 }170 else{171 printf("Insert success\n");172 return true;173 }174 }175

176 //修改数据

177 boolModifyData(){178 sprintf_s(query, "update departments set dept_name=‘yyyyy‘ where dept_no=‘dddd‘");179 if (mysql_query(&mysql, query)) {180 printf("Query failed (%s)\n", mysql_error(&mysql));181 return false;182 }183 else{184 printf("Insert success\n");185 return true;186 }187 }188

189 //删除数据

190 boolDeleteData()191 {192 sprintf_s(query, "delete from departments where dept_no=‘dddd‘;");193 //char query[100];194 //printf("please input the sql:\n");195 //gets_s(query);//手动输入sql语句

196 if (mysql_query(&mysql, query)) {197 printf("Query failed (%s)\n", mysql_error(&mysql));198 return false;199 }200 else{201 printf("Insert success\n");202 return true;203 }204 }