C语言中嵌入SQL语句

一. 编译环境
visual studio 2017， MySQL Workbench 8.0 CE。创建新项目后，需要在visual studio内完成一系列设置，以实现链接到MySQL。具体步骤如下：
(1) 在"项目"->"属性"栏目中设置平台为X64：

注意平台和"配置管理器"中的"活动解决方案平台"都要设置。

(2) 在"项目"->“属性”->“C/C++”->“常规”->"附加包含目录"中添加MySQL的include文件夹的路径：

注意要在后面加上英文分号。include文件夹在下载的MySQL的文件夹中：

(3) 在"项目"->“属性”->“链接器”->“常规”->“附加库目录"中添加include文件夹的路径(和(2)一样)：

(4) 在"项目”->“属性”->“链接器”->“输入”->“附加依赖项"中添加"libmysql.lib”:

(5) 在lib目录下找到两个文件：libmysql.dll和libmysql.lib，并把它们复制到当前项目的文件夹的"\x64\Debug"目录下。例如当前项目名为Project 9，则应该把它们复制到“Project9\x64\Debug”目录***意是外面的Project 9，而不是里面的Project 9。
当前在Project 9目录下：

应该复制到：

好了，到这一步设置就完成了。我们只需要包含库"mysql.h"并且声明全局变量 MYSQL mysql就可以实现链接数据库了。

二. 代码实例
假设我们需要创建一个叫做"course"的表，包含主键cno(课程号)、cname(课程名，为了方便统一为英文，要支持中文的话还要进行一些设置)、cpno(先修课程，是course表自身的外键，如果一门课不需要先修课程则cpno为0)、ccredit(课程学分)。sql语句是这样的：

create table course (
     cno VARCHAR(2) NOT NULL,
     cname VARCHAR(20),
     cpno VARCHAR(2) NULL,
     ccredit INT,
     primary key(cno),
     foreign key(cpno) references course(cno))；

如果该表已经存在，则可以选择删掉重建或者不操作。再写一个插入数据的函数，按次序输入cno、cname、cpno、ccredit。再写一个打印course表的函数，可以用来查看结果。
首先，通过mysql_real_connect函数连接到MySQL账户：

char fu[2];
mysql_init(&mysql);
//connect to MySQL server
//"localhost": name of server
//"root": username "123456": password
//"hello": name of database, created in MySQ Workbench
//"3306": port id 
if (mysql_real_connect(&mysql, "localhost", "root", "123456", "hello", 3306, 0, 0)) {
	for (;;) {
		printf("Sample Embedded SQL for C application\n");
		printf("Please select one function to execute:\n\n");
		printf("0--exit.\n");
		printf("1--Create Table Course  2--Insert into Course  3--show Table Course\n");
		fu[0] = '0';
		scanf("%s", &fu);
		if (fu[0] == '0') break;
		if (fu[0] == '1') create_course_table();
		if (fu[0] == '2') insert_rows_into_course_table();
		if (fu[0] == '3') display_table_course();
	}
}

其中，“mysql”是全局变量，声明为：MYSQL mysql，“root”为用户名，"123456"为密码，"hello"为数据库名，"3306"为默认端口。1号命令：创建表course；2号命令：插入数据到course表；3号命令：打印course表。输入为0则退出。下面主要详细说明创建表的函数create_course_table():

//whether the table course exists
bool table_course_exist() {
	char strquery[] = "show tables";
	mysql_query(&mysql, strquery);
	//stores the result of query request
	MYSQL_RES *result = mysql_store_result(&mysql);
	//columns of the most recent query, equals 1 in this circumstance
	int num_fields = mysql_field_count(&mysql);
	while ((row = mysql_fetch_row(result)) != NULL) {
		for(int i=0;i<num_fields;++i)
		if (!strcmp(row[i], "course")) {//table course exists
			mysql_free_result(result);
			return true;
		}
	}
	mysql_free_result(result);
	return false;
}

int create_course_table() {
	char yn[2];//delete or not:y/n
	//MYSQL_RES *result= mysql_list_tables(&mysql,"course");
	//printf("%d-----------\n", result->row_count);
	if (table_course_exist()) {//already exists
		printf("The course table already exists, do you want to delete it?\n");
		printf("Delete the table?  (y--yes,n--no);\n");
		scanf("%s", &yn);
		if (yn[0] == 'Y' || yn[0] == 'y') {//delete
			if (!mysql_query(&mysql, "drop table course;")) {
				printf("Drop table course successfully!\n\n");
			}
			else printf("ERROR:drop table course\n\n");
		}
		//if do not delete, return
		else {
			printf("\n");
			return 0;
		}
	}
	//create table course
	if (mysql_query(&mysql, "create table course (cno VARCHAR(2) NOT NULL,"
		"cname VARCHAR(20),cpno VARCHAR(2) NULL, ccredit INT,"
		"primary key(cno),foreign key(cpno) references course(cno)) ENGINE = InnoDB; ")==0){
	printf("create table course successfully!\n\n");
	}
	else printf("ERROR:create table course\n\n");
	//insert initial rows into table
	char insert[] = "insert into course values"
		"('0', 'default', '0', 0);";
	if (mysql_query(&mysql,insert )==0){
		printf("Success to insert rows to course table!\n\n");
	}
	else printf("ERROR:insert rows\n\n");
	return 0;
}

通过mysql_query(&mysql, strquery)函数，我们使const char*类型的sql命令strquery在MySQL中运行，这就是嵌入的原理。
table_course_exist函数用来判断course表是否已经存在，我用的方法是：通过sql语句"show tables"来展示所有表，存储在MYSQL_RES *result这个结构体指针中(它用于存储最近一次查询的结果)，然后逐行查找result是否有"course"。这是我最初的写法，后来发现有一种更简单的写法，我们调用mysql_list_tables函数：
MYSQL_RES *result= mysql_list_tables(&mysql,“course”);
作用是，在所有表中查找有名为"course"的表，结果存储在result中。我们只需判断result->row_count是否为0就知道course表是否已存在。(见create_course_table函数的注释)。
如果course表已经存在，用户选择是否删除course表，不删除则直接退出本函数；删除重建的情况等同于course表不存在的情况。通过mysql_query()函数执行create table course(…)这条语句即可。在创建完毕后，插入一条初始数据(‘0’, ‘default’, ‘0’, 0)。
完整代码：

#define _CRT_SECURE_NO_WARNINGS
#define _WINSOCK_DEPRECATED_NO_WARNINGS
#include<stdio.h>
#include<stdlib.h>
#include<winsock.h>
#include"mysql.h"

MYSQL mysql;
MYSQL_ROW row;
//whether the table course exists
bool table_course_exist() {
	char strquery[] = "show tables";
	mysql_query(&mysql, strquery);
	//stores the result of query request
	MYSQL_RES *result = mysql_store_result(&mysql);
	//columns of the most recent query, equals 1 in this circumstance
	int num_fields = mysql_field_count(&mysql);
	while ((row = mysql_fetch_row(result)) != NULL) {
		for(int i=0;i<num_fields;++i)
		if (!strcmp(row[i], "course")) {//table course exists
			mysql_free_result(result);
			return true;
		}
	}
	mysql_free_result(result);
	return false;
}

int create_course_table() {
	char yn[2];//delete or not:y/n
	//MYSQL_RES *result= mysql_list_tables(&mysql,"course");
	//printf("%d-----------\n", result->row_count);
	if (table_course_exist()) {//already exists
		printf("The course table already exists, do you want to delete it?\n");
		printf("Delete the table?  (y--yes,n--no);\n");
		scanf("%s", &yn);
		if (yn[0] == 'Y' || yn[0] == 'y') {//delete
			if (!mysql_query(&mysql, "drop table course;")) {
				printf("Drop table course successfully!\n\n");
			}
			else printf("ERROR:drop table course\n\n");
		}
		//if do not delete, return
		else {
			printf("\n");
			return 0;
		}
	}
	//create table course
	if (mysql_query(&mysql, "create table course (cno VARCHAR(2) NOT NULL,"
		"cname VARCHAR(20),cpno VARCHAR(2) NULL, ccredit INT,"
		"primary key(cno),foreign key(cpno) references course(cno)) ENGINE = InnoDB; ")==0){
	printf("create table course successfully!\n\n");
	}
	else printf("ERROR:create table course\n\n");
	//insert initial rows into table
	char insert[] = "insert into course values"
		"('0', 'default', '0', 0);";
	if (mysql_query(&mysql,insert )==0){
		printf("Success to insert rows to course table!\n\n");
	}
	else printf("ERROR:insert rows\n\n");
	return 0;
}

//insert into table
int insert_rows_into_course_table() {
	if (!table_course_exist()) {
		printf("There is no such table named course. Please create it first.\n\n");
		return 0;
	}
	//input ccredit as type const char* for convenience
	char cno[] = "00"; char cname[] = "000000000000000000000"; char cpno[] = "00"; char ccredit[] = "00";
	char strquery[100] = "insert into course values('";
	//serve as a copy of strquery
	char copy[100] = "insert into course values('";
	//insert again or not: y/n
	char yn[2];
	while (1) {
		//regain strquery
		strcpy(strquery, copy);
		//input cno
		printf("Please input cno(eg:5):"); scanf("%s", cno);
		strcat(strquery, cno); strcat(strquery, "','");
		//input cname
		printf("Please input cname(eg:DataStructure):"); scanf("%s", cname);
		strcat(strquery, cname); strcat(strquery, "','");
		//input cpno
		printf("Please input cpno(eg:7):(Note:If there is no prerequisity course, please input '0')\n"); scanf("%s", cpno);
		strcat(strquery, cpno); strcat(strquery, "','");
		//input ccredit
		printf("Please input ccredit(eg:4):"); scanf("%s", ccredit);
		strcat(strquery, ccredit); strcat(strquery, "');");
		printf("%s\n", strquery);
		if (mysql_query(&mysql, strquery) == 0) printf("execute successfully!\n\n");
		else printf("ERROR:execute\n\n");
		printf("Insert again?  (y--yes,n--no);\n");
		scanf("%s", &yn);
		if (yn[0] == 'Y' || yn[0] == 'y') {//insert again
			continue;
		}
		else {//end inserting
			printf("\n");
			return 0;
		}
	}
	return 0;
}

//show table course
void display_table_course() {
	if (!table_course_exist()) {
		printf("There is no such table named course. Please create it first.\n\n");
		return ;
	}
	char strquery[] = "select * from course";
	mysql_query(&mysql, strquery);
	MYSQL_RES *result = mysql_store_result(&mysql);
	//columns of the most recent query, equals 1 in this circumstance
	int num_fields = mysql_field_count(&mysql);
	printf("cno             cname         cpno      ccredit\n");
	while ((row = mysql_fetch_row(result)) != NULL) {
		char cno[] = "00", cname[] = "000000000000000000000", cpno[] = "00000", ccredit[]="00";
		for (int i = 0; i < num_fields; ++i) {
			switch (i) {
			case 0: {strcpy(cno, row[i]); break; }
			case 1: {strcpy(cname, row[i]); break; }
			case 2: {strcpy(cpno, row[i]); break; }
			case 3: {strcpy(ccredit, row[i]); break; }
			}
		}
		//adjust the format of output
		printf("%s%20s%10s%10s\n", cno, cname, cpno, ccredit);
	}
	printf("\n");
	mysql_free_result(result);
}

int main() {
	char fu[2];
	mysql_init(&mysql);
	//connect to MySQL server
	//"localhost": name of server
	//"root": username "123456": password
	//"hello": name of database, created in MySQ Workbench
	//"3306": port id 
	if (mysql_real_connect(&mysql, "localhost", "root", "123456", "hello", 3306, 0, 0)) {
		for (;;) {
			printf("Sample Embedded SQL for C application\n");
			printf("Please select one function to execute:\n\n");
			printf("0--exit.\n");
			printf("1--Create Table Course  2--Insert into Course  3--show Table Course\n");
			fu[0] = '0';
			scanf("%s", &fu);
			if (fu[0] == '0') break;
			if (fu[0] == '1') create_course_table();
			if (fu[0] == '2') insert_rows_into_course_table();
			if (fu[0] == '3') display_table_course();
		}
	}
	else printf("There is no available database！\n");
	mysql_close(&mysql);
	return 0;
}

三. 运行结果截图
假如我们要插入以下数据(给出sql语句)：

insert into course values
	('6', '数据处理基础', NULL, 4),
	('7', 'C语言', 6, 3),
	('4', '操作系统原理', 6, 3),
	('5', '数据结构', 7, 4),
	(1, '数据库基础', 5, 4),
    (3, '信息系统导论', 1, 3),
	(2, '数学分析', NULL, 2);

在C程序中输入这些课程的信息(课程用英文名，请忽略我笨拙的翻译：） )。再使用3命令查看打印结果：

在MySQL Workbench 上查看：

---

参考书：《MySQL数据库技术与实验指导》,清华大学出版社