MySQL为C语言用户提供了连接数据库的API,想要正常使用这些数据库,需要做到以下两点:
1)包含这些API的声明文件,即mysql.h;
2)让编译器找到这些API的可执行程序,即DLL库。
以下在Visual Sudio 2010中,用C语言开发数据库。
第一步,先创建名为xxgl的数据库,下面采用SQL命令的方式创建数据库和表,也可以在MySQL Workbench中采用图形化的方式创建数据库和表
1.启动MySQL的命令行客户端,执行创建数据库的命令:
2.用USE命令选中数据库xxgl:
3.创建四个表,表名和属性如下:
(1) student(sno,sname,ssex,sage,sdept) |
(2) course(cno,cname,cpno,ccredit) |
(3) sc(sno,cno,grade) |
(4) users(uno,uname,upassword,uclass) |
创建表student,
创建表course,
创建表sc,
创建表users,
xxgl数据库创建完毕,结果如下所示:
第二步,创建基于WIN32控制台应用的空项目,并添加一个源文件,源代码如下:
#include <iostream>
#include <stdio.h>
#include <stdlib.h>
#include <WinSock.h>
#include "mysql.h" //包含MySQL的API函数的头文件
#pragma comment(lib,"libmysql.lib") //导入libmysql.lib库
using namespace std;
MYSQL mysql;
int create_student_table();
int insert_rows_into_student_table();
int current_of_update_for_student();
int current_of_delete_for_student();
int using_cursor_to_list_student();
int main()
{
int num=0;
char fu='0';
mysql_init(&mysql);
//mysql_real_connect连接到MySQL数据库服务器,其中localhost为服务器机器名,root为连接用户名,zhangwei为密码,
//xxgl为数据库名,3306为端口号
if(mysql_real_connect(&mysql,"localhost","root","zhangwei","xxgl",3306,0,0))
{
while(1)
{
printf("C语言嵌入式SQL应用程序开发\n");
printf("请选择所需的功能项编号:\n");
printf("0--exit.\n");
printf("1--创建学生表 4--添加学生记录 7--修改学生记录 a--删除学生记录 e--显示学生记录\n");
//fu='0';
//scanf_s("%c",&fu);
cin>>fu;
if(fu=='0') exit(0);
else if(fu=='1') create_student_table();
else if(fu=='4') insert_rows_into_student_table();
else if(fu=='7') current_of_update_for_student();
else if(fu=='a') current_of_delete_for_student();
else if(fu=='e') using_cursor_to_list_student();
else
;//break;
system("pause");
}
}
else
{
printf("数据库不存在!\n");
}
mysql_close(&mysql);
return 0;
}
int create_student_table()
{
char yn;
char tname[21]="xxxxxxxxxx";
if(mysql_list_tables(&mysql,"student"))
{
printf("student表已经存在,是否要将其删除?输入y删除,输入n不删除\n");
//scanf_s("%s",&yn);
cin>>yn;
if(yn=='y' || yn=='Y')
{
if(!mysql_query(&mysql,"drop table student;")) //删除student表
{
printf("成功删除student表!\n");
}
else
{
printf("ERROR:删除student表%d\n");
}
}
}
//创建student表
if(mysql_query(&mysql,"create table student(sno char(5) not null primary key,sname char(6) not null,\
ssex char(2) null,sage int null,sdept char (2) null)engine=MyISAM;")==0)
{
printf("成功创建student表!\n");
}
else
{
printf("ERROR:创建表student %d\n");
}
//插入数据
//mysql_query(&mysql,"insert into student values('95001','李斌','男',16,'CS'),('9502','赵霞','男女,18,'IS'),\
// ('95003','周陶','男',17,'CS'),('95004','钱乐','女',18,'IS'),('95005','孙力','男',16,'MA');")==0
if(mysql_query(&mysql,"insert into student values('95001','李斌','男',16,'CS');")==0)
{
printf("成功插入行到student表!%d\n");
}
else
{
printf("ERROR:插入行失败 %d\n");
}
return 0;
}
int insert_rows_into_student_table()
{
char csage[]="18";
char issex[]="男";
char isno[]="95002";
char isname[]="xxxxxxx";
char isdept[]="CS";
char strquery[100]="insert into student(sno,sname,sage,ssex,sdept)values(' ";
char yn[2];
while(1)
{
printf("请输入学号sno(如:95001):");cin>>isno;strcat_s(strquery,isno);strcat_s(strquery,"','");
printf("请输入姓名sname(如:xxxx):");cin>>isname;strcat_s(strquery,isname);strcat_s(strquery,"','");
printf("请输入年龄sage(如:18):");cin>>csage;strcat_s(strquery,csage);strcat_s(strquery,"','");
printf("请输入性别ssex(如:男):");cin>>issex;strcat_s(strquery,issex);strcat_s(strquery,"','");
printf("请输入专业sdept(如:CS):");cin>>isdept;strcat_s(strquery,isdept);strcat_s(strquery,"');");
if(mysql_query(&mysql,strquery)==0)
{
printf("插入成功!%d\n");
}
else
{
printf("ERROR:插入失败!%d\n");
}
printf("继续插入吗?请输入y插入,输入n不插入:");
scanf_s("%s",&yn);
if(yn[0]=='y' || yn[0]=='Y')
{
continue;
}
else
{
break;
}
}
return 0;
}
int current_of_update_for_student()
{
char yn[2];char deptname[3];char hsno[6];char hsname[7];char hssex[3];
char hsdept[3];char hsage[3];int i;char isage[3]="38";
char issex[4]="男";char isname[7]="xxxxxx";char isdept[3]="CS";
MYSQL_ROW row;
char strquery[100]="select sno,sname,ssex,sage,sdept from student";
printf("请输入专业以便更新:\n");
scanf_s("%s",deptname);
if(strcmp(deptname,"*")!=0 || strcmp(deptname,"**")!=0)
{
strcat_s(strquery,"where sdept like '");
strcat_s(strquery,deptname);
strcat_s(strquery,"%'");
}
mysql_query(&mysql,strquery);
MYSQL_RES *result=mysql_store_result(&mysql);
printf("%s","sno sname ssex sdept");
int num_fields=mysql_field_count(&mysql);
while((row=mysql_fetch_row(result))!=NULL)
{
for(i=0;i<num_fields;i++)
{
switch(i)
{
case 0:
strcpy_s(hsno,row[i]);break;
case 1:
strcpy_s(hsname,row[i]);break;
case 2:
strcpy_s(hssex,row[i]);break;
case 3:
strcpy_s(hsage,row[i]);break;
case 4:
strcpy_s(hsdept,row[i]);break;
}
}
printf("%s",hsno);printf("%s",hsname);printf("%s",hssex);printf("%s",hsage);printf("%s\n",hsdept);
printf("更新?(y/n/0,y--yes.n--no,0--exit)");
cin>>yn;
if(yn[0]=='y' || yn[0]=='Y')
{
char strupdate[100]="update student set sname='";
printf("请到输入新的名字(如:XXXX):");cin>>isname;strcat_s(strupdate,isname);strcat_s(strupdate,"',sage='");
printf("请到输入新的年龄(如:18):");cin>>isage;strcat_s(strupdate,isage);strcat_s(strupdate,"',ssex='");
printf("请到输入新的性别(如:男):");cin>>issex;strcat_s(strupdate,issex);strcat_s(strupdate,"',sdept='");
printf("请到输入新的专业(如:CS):");cin>>isdept;strcat_s(strupdate,isdept);strcat_s(strupdate,"'where sno='");
strcat_s(strupdate,hsno);strcat_s(strupdate,"'");
if(mysql_query(&mysql,strupdate))
{
printf("更新student表成功!%d\n");
}
else
{
printf("ERROR:更新student%d\n");
}
}
if(yn[0]=='0')
break;
}
if(mysql_error(&mysql))
{
fprintf(stderr,"Error:%s\n",mysql_error(&mysql));
}
return 0;
}
int current_of_delete_for_student()
{
char yn[2];char deptname[3];char hsno[6];char hsname[7];char hssex[3];
char hsdept[3];char hsage[3];int i;
MYSQL_ROW row;
char strquery[100]="select sno,sname,ssex,sage,sdept from student";
printf("请输入专业名(CS):\n");scanf_s("%s",deptname);
if(strcmp(deptname,"*")!=0 || strcmp(deptname,"**")!=0)
{
strcat_s(strquery,"where sdept like '");
strcat_s(strquery,deptname);
strcat_s(strquery,"%'");
}
mysql_query(&mysql,strquery);
MYSQL_RES *result=mysql_store_result(&mysql);
printf("%s\n","sno sname ssex sage sdept");
int num_fields=mysql_field_count(&mysql);
while((row=mysql_fetch_row(result))!=NULL)
{
for(i=0;i<num_fields;i++)
{
switch(i)
{
case 0:
strcpy_s(hsno,row[i]);break;
case 1:
strcpy_s(hsname,row[i]);break;
case 2:
strcpy_s(hssex,row[i]);break;
case 3:
strcpy_s(hsage,row[i]);break;
case 4:
strcpy_s(hsdept,row[i]);break;
}
}
printf("%s",hsno);printf("%s",hsname);printf("%s",hssex);printf("%s",hsage);printf("%s\n",hsdept);
printf("删除?(y/n/0,y--yes.n--no,0--exit)");
cin>>yn;
if(yn[0]=='y' || yn[0]=='Y')
{
char strdelete[100]="delete from student where sno='";
strcat_s(strdelete,hsno);strcat_s(strdelete,"'");
if(mysql_query(&mysql,strdelete))
{
printf("删除student表成功!%d\n");
}
else
{
printf("ERROR:删除student%d\n");
}
}
if(yn[0]=='0')
break;
}
if(mysql_error(&mysql))
{
fprintf(stderr,"Error:%s\n",mysql_error(&mysql));
}
return 0;
}
int using_cursor_to_list_student()
{
char isage[3];char sno[6];char issex[3];char isno[6];char isname[7];
char isdept[3];int i;
MYSQL_ROW row;
char strquery[100]="select sno,sname,ssex,sage,sdept from student where sno like '%";
printf("请输入学号:");cin>>sno;strcat_s(strquery,sno);strcat_s(strquery,"%'");
mysql_query(&mysql,strquery);
MYSQL_RES *result=mysql_store_result(&mysql);
printf("%s\n","sno sname ssex sage sdept");
int num_fields=mysql_field_count(&mysql);
while((row=mysql_fetch_row(result))!=NULL)
{
for(i=0;i<num_fields;i++)
{
switch(i)
{
case 0:
strcpy_s(isno,row[i]);break;
case 1:
strcpy_s(isname,row[i]);break;
case 2:
strcpy_s(issex,row[i]);break;
case 3:
strcpy_s(isage,row[i]);break;
case 4:
strcpy_s(isdept,row[i]);break;
}
}
}
printf("%s",isno);printf("%s",isname);printf("%s",issex);printf("%s",isage);printf("%s\n",isdept);
if(mysql_error(&mysql))
{
fprintf(stderr,"Error:%s\n",mysql_error(&mysql));
}
return 0;
}
第三部,项目属性设置
1).执行菜单命令Project->Property命令,打开属性对话框,选择C/C++->General->Additional Include Directories,填上上MySQL\include目录,我的MySQL安装路径为C:\Program Files\MySQL\,其中Include路径为C:\Program Files\MySQL\MySQL Server 5.7\include\,如下图所示:
2).配置链接器。在上图中选择Linker->General->Additional Library Directories,填上MySQL\lib目录,我的lib目录为:C:\Program Files\MySQL\MySQL Server 5.7\lib,如下图所示:
3).选择Linker->Input->Additional Dependencies,输入libmysql.lib,如下图所示:
也可以在源代码中采用预处理指令导入libmysql.lib,方法如下:
#pragma comment(lib,"libmysql.lib") //导入libmysql.lib库
至此,项目属性配置基本完成,保存项目。
第四步:可以运行该项目:
本程序,还有一定的bug,但是大体框架正确。