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 }