MySQL数据库C接口的数据查询操作示例

命令行操作

（1）建库建表（已有则不用）

连接MySQL服务器，并登录之。选择某个数据库。假设用户在这个数据库下已有一个表了。

（2）查询所有数据的方法

SELECT * FROM table_name;

实际业务中不会这么查，数据太多会造成严重后果。

（3）查询指定列（字段）的数据

SELECT column_name1, column_name2 FROM table_name;

（4）在查询中使用别名

SELECT column_name1 as other_name1, column_name2 as other_name2 FROM table_name;

（5）单一条件查询

SELECT * FROM table_name WHERE column_name = value;

（6）多条件查询

SELECT * FROM table_name WHERE column_name1 = value1 and(or) column_name2 = value2;

（7）模糊查询

SELECT * FROM table_name WHERE column_name like value;

（8）对查询结果进行排序

SELECT * FROM table_name ORDER BY column_name DESC/ASC;（降序/升序）

（9）限制查询行数

SELECT * FROM table_name LIMIT num;(num是行数)

C代码操作示例

#include <mysql/mysql.h>
#include <stdio.h>
#include <stdlib.h>
#include <string.h>

void main() {
	MYSQL *conn;
     	MYSQL_RES *resultset;
     	MYSQL_ROW row;
     	char *server = "127.0.0.1";
     	char *user = "root";
     	char *password = "Bintu2018$"; 
     	char *database = "ecommerce";
     	char emailaddress[30], sqlquery[255];
     	conn = mysql_init(NULL);
     	if (!mysql_real_connect(conn, server, user, password, database, 0, NULL, 0)) {
        		fprintf(stderr, "%s\n", mysql_error(conn));
        		exit(1);
    	}
    	printf("Enter email address to search: ");
    	scanf("%s", emailaddress);
	strcpy(sqlquery,"SELECT * FROM users where email_address like \'");
	strcat(sqlquery,emailaddress);
	strcat(sqlquery,"\'");
 	if (mysql_query(conn, sqlquery) != 0)                  
  	{                                                                                                  
    		fprintf(stderr, "No row found in the users table with this email address\n");                                                              
        		exit(1);                                                                      
  	}   
	printf("The details of the user with this email address are as follows:\n"); 
   	resultset = mysql_use_result(conn);
    	while ((row = mysql_fetch_row(resultset)) != NULL)
	{
        		printf("Email Address: %s \n", row[0]);
 		printf("Password: %s \n", row[1]);
 		printf("Address of delivery: %s \n", row[2]);
	}
    	mysql_free_result(resultset);
    	mysql_close(conn);
}