main.cpp

一个简单的基于ODBC的mysql操作

#include "RctData.h"

MySqlConn msc;
string dataBase="test";
string createTableSql="create table stu_info (sno char(10) primary key,sname nvarchar(8) not null,sex nchar(2) not null,dataStruct tinyint,operationSystem tinyint,softWareProcess tinyint,compileTheory tinyint,averageScore tinyint)";

void createTable()
{
	msc.execute(createTableSql);
	system("pause");
}
void paintMainMenu()
{
	system("cls");
	cout<<"\n\n\t\t\t  功能列表\n";
	cout<<"\n\t基本功能...\t\t\t管理功能\n";

	cout<<"\n\t1 : 添加学生记录\t\t5 : 查看平均成绩并按其排序";
	cout<<"\n\t2 : 删除学生记录\t\t6 : 查看科目平均成绩";
	cout<<"\n\t3 : 修改学生记录\t\t7 : 查找最高分(按个人平均分)";
	cout<<"\n\t4 : 显示成绩列表\t\t8 : 查找最高分(按指定科目)";
	cout<<"\n\t                \t\t9 : 筛选(按性别)";
	cout<<"\n\t                \t\t10 : 筛选(按高于指定学生平均成绩)";
	cout<<"\n\n\t\t\t 0 : 退出";
	cout<<"\n\t您的选择 : ";

}

void add()
{
	string sno,sname,sex, querySql="\0";
	int  dataStruct,operationSystem,softWareProcess,compileTheory,averageScore;
	system("cls");
	cout<<"\n\n\t\t输入要添加的学生信息\n";
	cout<<"学号 : ";
	cin>>sno;
	cout<<"姓名 : ";
	cin>>sname;
	cout<<"性别 : ";
	cin>>sex;
	cout<<"数据结构 : ";
	cin>>dataStruct;
	cout<<"操作系统 : ";
	cin>>operationSystem;
	cout<<"软件过程 : ";
	cin>>softWareProcess;
	cout<<"编译原理 : ";
	cin>>compileTheory;
	averageScore=(dataStruct+operationSystem+softWareProcess+compileTheory)/4;
	char a[10],b[10],c[10],d[10],e[10];
	itoa(dataStruct,a,10);
	itoa(operationSystem,b,10);
	itoa(softWareProcess,c,10);
	itoa(compileTheory,d,10);
	itoa(averageScore,e,10);

	querySql+="insert into stu_info values ('";
	querySql+=sno;
	querySql+="','";
	querySql+=sname;
	querySql+="','";
	querySql+=sex;
	querySql+="',";
	querySql+=a;
	querySql+=",";
	querySql+=b;
	querySql+=",";
	querySql+=d;
	querySql+=",";
	querySql+=d;
	querySql+=",";
	querySql+=e;
	querySql+=")";

	if(msc.execute(querySql))
	{
		if(int tempint=msc.affectedRows())
		{
			cout<<"语句的执行影响 "<<tempint<<"行数据\n";	
		}
		else
		{
			cout<<"语句执行没对数据库造成影响"<<endl;
		}
	}
	system("pause");
}
void del()
{
	system("cls");
	string name;
	cout<<"\n\n\t\t删除同学信息\n";
	cout<<"输入要删除同学的姓名 :";
	cin>>name;
	string querySql="\0";
	querySql+="delete from stu_info where sname='";
	querySql+=name;
	querySql+="'";

	if(msc.execute(querySql))
	{
		if(int tempint=msc.affectedRows())
		{
			cout<<"语句的执行影响 "<<tempint<<"行数据\n";	
		}
		else
		{
			cout<<"语句执行没对数据库造成影响"<<endl;
		}
	}
	system("pause");
}
void update()
{
	system("cls");
	cout<<"\n\n\t\t\t\t修改信息"<<endl;
	cout<<"别修改了,删除后再删入得了.\n";
	system("pause");
}
void showTableExceptAvgScore()
{
	string querySql="select sno '学生学号',sname '姓名',sex '性别',dataStruct '数据结',operationSystem '操作系',softWareProcess '软件过' ,compileTheory '编译原' from stu_info";
	msc.showQuery(querySql);
	system("pause");
}
void showAndOrderAvgScoreByPerson()  //降序排列 desc
{
	string querySql="select sno '学生学号',sname '姓名',sex '性别',averageScore '平均分' from stu_info order by averageScore desc";
	msc.showQuery(querySql);
	system("pause");
}
void showAvgScoreBySubject()
{
	string querySql="select avg(dataStruct) '数据结',avg(operationSystem) '操作系',avg(softWareProcess) '软件过',avg(compileTheory)'编译原' from stu_info  ";
	msc.showQuery(querySql);
	system("pause");
}
void showTopScoreByPerson()
{
	string querySql="select sno '学生学号',sname '姓名',sex '性别',dataStruct '数据结',operationSystem '操作系',softWareProcess '软件过' ,compileTheory '编译原',averageScore '平均分'  from stu_info  where averageScore =( select max(averageScore) from stu_info ) ";
	msc.showQuery(querySql);
	system("pause");	
}
void showTopScoreBySubject()
{
	int choice;
	string subject;
	cout<<"选择科目(1 : 数据结构  2 : 操作系统  3 : 软件过程  4 : 编译原理)  :"<<endl;
	cin>>choice;
	switch(choice)
	{
		case 1:
			subject="dataStruct";
			break;
		case 2:
			subject="operationSystem";
			break;
		case 3:
			subject="softWareProcess";
			break;
		case 4:
			subject="compileTheory";
			break;
		default:
			break;
	}
	string querySql="\0";

	querySql+="select sno '学生学号',sname '姓名',sex '性别',dataStruct '数据结',operationSystem '操作系',softWareProcess '软件过' ,compileTheory '编译原',averageScore '平均分'  from stu_info  where ";
	querySql+=subject; 
	querySql+="=( select max(" ;

	querySql+=subject;
	querySql+=") from stu_info) ";

	msc.showQuery(querySql);
	system("pause");

}
void selectBySex()
{
	int choice;
	string sex;
	cout<<"按性别筛选(1 : 男  2:  女)  :";
	cin>>choice;

	switch(choice)
	{
		case 1:
			sex="男";
			break;
		case 2:
			sex="女";
			break;
		default:
			break;
	}

	string querySql="\0";
	querySql+="select sno '学生学号',sname '姓名',sex '性别',dataStruct '数据结',operationSystem '操作系',softWareProcess '软件过' ,compileTheory '编译原' from stu_info where sex= '";
	querySql+=sex;
	querySql+="'";       //字符串在Sql语句中应该用''引起来
	
	msc.showQuery(querySql);
	system("pause");
}
void selectByOneAvgScore()
{
	string name;
	cout<<"输入作基准的学生的名字 :";
	cin>>name;

	string querySql="\0";
	querySql+="select sno '学生学号',sname '姓名',sex '性别',dataStruct '数据结',operationSystem '操作系',softWareProcess '软件过' ,compileTheory '编译原' from stu_info where averageScore>(select averageScore from stu_info where sname='";
	querySql+=name;
	querySql+="')";  
	msc.showQuery(querySql);
	system("pause");
}


int main()
{	
	//连接数据库
	msc.connect("root","123321",dataBase);
	/*第一次执行时要建立一个学业生信息表*/
    //createTable();
	
	int choice;
	paintMainMenu();
	cin>>choice;
	while(choice)
	{
		switch(choice)
		{
			case 1:
				add();
				break;
			case 2:
				del();
				break;
			case 3:
				del();
				break;
			case 4:
				showTableExceptAvgScore();
				break;
			case 5:
				showAndOrderAvgScoreByPerson();
				break;
			case 6:
				showAvgScoreBySubject();
				break;
			case 7:
				showTopScoreByPerson();
				break;
			case 8:
				showTopScoreBySubject();
				break;
			case 9:
				selectBySex();
				break;
			case 10:
				selectByOneAvgScore();
				break;
			default:
				break;
		}
		paintMainMenu();
		cin>>choice;
	}
	

	msc.close();
	system("pause");
	return 0;	
}