嵌入式MySQL应用——在C语言中嵌入SQL命令

实验环境：

1、 操作系统：window10
2、 MySQL 5.7

实验内容与完成情况：

1 实现create_course_table()子程序的功能:
1.1 创建表
create_course_table()函数代码：

int create_course_table() {
	char yn[2];
	
	result = mysql_list_tables(&mysql, "course");
	unsigned int rows = mysql_num_rows(result);
	mysql_free_result(result);

	if (rows > 0) {		//删除已存在的表
		printf("The course table already exists, do you want to delete it?\n");
		printf("Delete the table? (y--yes,n--no):");
		scanf("%s", &yn);
		if (yn[0] == 'y' || yn[0] == 'Y') {		//询问是否删除已存在的表
			if (!mysql_query(&mysql, "drop table course")) {
				printf("Drop table course successfully!%d\n\n");
			}
			else {
				printf("ERROR: drop table course%d\n\n");
			}
		}
	}

	//创建表course
	if (mysql_query(&mysql, "create table course(cno char(10) not null primary key,\
		cname char(100) null,cpno char(10) null,ccredit int null)engine=innodb;") == 0) {
		printf("create table course successfully!%d\n\n");
	}
	else {
		printf("ERROR: create table course%d\n\n");
	}
	
	return 0;
}

初始时，数据库xxgl中没有course表：

现在我们进行表的创建。指令如下：

创建后数据库中有course表：

1.2 替换已有表
当数据库xxgl中已有course表时，调用创建课程表函数可以替换已有表。指令如下：

2. 实现insert_rows_into_course_table()子程序的功能
insert_rows_into_course_table()函数代码：

int insert_rows_into_course_table() {
	//记录各个字段取值的变量
	char cno[] = "1";
	char cname[50] = "课程";
	char cpno[] = "2";
	char ccredit[] = "3";
	char yn[2];
	char strquery[200]= "insert into course(cno,cname,cpno,ccredit) values('";		//记录插入对应记录所需的mysql语句

	//记录当前要插入记录的各个字段的值
	printf("Please input cno(eg:1):");scanf("%s", cno);strcat(strquery, cno);
	strcat(strquery, "','");
	printf("Please input cname(eg:数据库系统):");scanf("%s", cname);strcat(strquery, cname);
	strcat(strquery, "','");
	printf("Please input cpno(eg:2):");scanf("%s", cpno);strcat(strquery, cpno);
	strcat(strquery, "','");
	printf("Please input ccredit(eg:3):");scanf("%s", ccredit);strcat(strquery, ccredit);
	strcat(strquery, "');");

	if (mysql_query(&mysql, strquery) == 0) {
		printf("execute successfully!%d\n\n");
	}
	else {
		printf("ERROR:execute%d\n");
	}

	printf("Insert again? (y--yes,n--no):");		//是否一次插入多条记录
	scanf("%s", &yn);

	if (yn[0] == 'y' || yn[0] == 'Y') {
		insert_rows_into_course_table();
	}

	return 0;
}

向已有的课程表中插入一些记录，指令如下：

插入后数据库xxgl中的表有如下所示的对应记录：

源代码：

#include<mysql.h>
#include<stdio.h>
#include<stdlib.h>
#include<winsock.h>
#include<iostream>
using namespace std;
MYSQL mysql;//声明为全局变量，待会在主函数，功能函数中都能对它访问
#pragma warning(disable:4996)
MYSQL_RES* result;

int create_course_table();
int insert_rows_into_course_table();

int main(int argc, char** argv, char** envp)
{
	int num = 0;
	char fu[2];

	mysql_init(&mysql);//获得或初始化一个MYSQL结构

	if (mysql_real_connect(&mysql, "localhost", "root", "ncs54896", "xxgl", 3306, 0, 0)) {
		mysql_query(&mysql, "set names \'GBK\'");		//解决汉字显示为乱码的问题
		for (;;) {
			printf("Sample Embedded SQL for C application\n");
			printf("Please select one function to excute:\n\n");
			printf("0--exit.\n");					//不同序号对应的数据库操作
			printf("1--创建课程表\n");
			printf("2--添加课程记录\n\n");

			fu[0] = '0';
			scanf("%s", &fu);
			if (fu[0] == '0')exit(0);
			if (fu[0] == '1')create_course_table();
			if (fu[0] == '2')insert_rows_into_course_table();
			
		}
	}
	else {
		printf("Error,数据库不存在！\n");
	}

	mysql_close(&mysql);//访问完毕，关闭mysql
	result = mysql_store_result(&mysql);
	mysql_free_result(result);
	system("pause");
	return 0;
}


int create_course_table() {
	char yn[2];
	
	result = mysql_list_tables(&mysql, "course");
	unsigned int rows = mysql_num_rows(result);
	mysql_free_result(result);

	if (rows > 0) {		//删除已存在的表
		printf("The course table already exists, do you want to delete it?\n");
		printf("Delete the table? (y--yes,n--no):");
		scanf("%s", &yn);
		if (yn[0] == 'y' || yn[0] == 'Y') {		//询问是否删除已存在的表
			if (!mysql_query(&mysql, "drop table course")) {
				printf("Drop table course successfully!%d\n\n");
			}
			else {
				printf("ERROR: drop table course%d\n\n");
			}
		}
	}

	//创建表course
	if (mysql_query(&mysql, "create table course(cno char(10) not null primary key,\
		cname char(100) null,cpno char(10) null,ccredit int null)engine=innodb;") == 0) {
		printf("create table course successfully!%d\n\n");
	}
	else {
		printf("ERROR: create table course%d\n\n");
	}
	
	return 0;
}

int insert_rows_into_course_table() {
	//记录各个字段取值的变量
	char cno[] = "1";
	char cname[50] = "课程";
	char cpno[] = "2";
	char ccredit[] = "3";
	char yn[2];
	char strquery[200]= "insert into course(cno,cname,cpno,ccredit) values('";		//记录插入对应记录所需的mysql语句

	//记录当前要插入记录的各个字段的值
	printf("Please input cno(eg:1):");scanf("%s", cno);strcat(strquery, cno);
	strcat(strquery, "','");
	printf("Please input cname(eg:数据库系统):");scanf("%s", cname);strcat(strquery, cname);
	strcat(strquery, "','");
	printf("Please input cpno(eg:2):");scanf("%s", cpno);strcat(strquery, cpno);
	strcat(strquery, "','");
	printf("Please input ccredit(eg:3):");scanf("%s", ccredit);strcat(strquery, ccredit);
	strcat(strquery, "');");

	if (mysql_query(&mysql, strquery) == 0) {
		printf("execute successfully!%d\n\n");
	}
	else {
		printf("ERROR:execute%d\n");
	}

	printf("Insert again? (y--yes,n--no):");		//是否一次插入多条记录
	scanf("%s", &yn);

	if (yn[0] == 'y' || yn[0] == 'Y') {
		insert_rows_into_course_table();
	}

	return 0;
}