机房预约—MySQL版本

南徐炼丹大师

已于 2023-10-11 21:27:48 修改

阅读量58

点赞数

分类专栏：数据库 C++ 学习记录文章标签： mysql 数据库

于 2023-10-09 22:27:43 首次发布

本文链接：https://blog.csdn.net/weixin_45699456/article/details/133717819

版权

数据库同时被 3 个专栏收录

5 篇文章 0 订阅

订阅专栏

C++

5 篇文章 0 订阅

订阅专栏

学习记录

2 篇文章 0 订阅

订阅专栏

源代码是黑马程序员的机房预约系统，然后其原来是使用文件进行信息存储的，闲来无事就改成了基于数据库存储的了。

1、数据库创建

1.1表建立

分别创建学生，老师，管理员，机房的信息以及预约信息。

其中学号、职工号、以及管理员用户为唯一键，不可以有重复的数据。

为了方便对预约信息进行审核，使用给预约信息表加了ID。

create table student(
stuid int not null unique comment '学号' primary key,
name varchar(10) not null comment '用户',
pass varchar(20) not null comment '密码'
)comment '学生';

create table teacher(
teacid int not null unique comment '职工号' primary key,
name varchar(10) not null comment '用户',
pass varchar(20) not null comment '密码'
)comment '老师';

create table admin(
name varchar(10) not null unique comment '用户' primary key,
pass varchar(20) not null comment '密码'
)comment '管理员';

create table room(
room int not null comment '机房编号' primary key,
num int not null comment '位置数量'
)comment '机房';

create table order_info(
id int auto_increment comment 'ID' primary key,
time_ int not null comment '时间',
period int not null comment '时段',
stuid int not null comment '学号',
name varchar(10) not null comment '学生姓名',
roomid int not null comment '机房',
status varchar(10) not null comment '状态'
)comment '预约信息';

1.2部分信息插入

插入管理员信息

insert into admin (name,pass) values ('admin', 123);

设置机房信息

insert into room (room,num) values (1, 20),(2, 50),(3, 100);

2、VS2022连接数据库

VS2022的设置：VS2017连接MYSQL 8.0（小白必看！）-CSDN博客

3、代码修改

main()及其部分函数修改

首先修改了登录函数，其次在验证用户信息的时候直接使用数据库语句进行查询，

#include<iostream>
using namespace std;
#include "identity.h"
#include <fstream>
#include <string>
#include "student.h"
#include "teacher.h"
#include "manager.h"
#include "mysql.h"
#include"mysql_option.h"

void LoginIn(int type);
void managerMenu(Identity*& manager); 
void studentMenu(Identity*& student);
void TeacherMenu(Identity*& teacher);
int main() {
	mysql_option();

	int select = 0;

	while (true)
	{

		cout << "======================  欢迎来到机房预约系统  =====================" << endl;
		cout << endl << "请输入您的身份" << endl;
		cout << "\t\t -------------------------------\n";
		cout << "\t\t|                               |\n";
		cout << "\t\t|          1.学生代表           |\n";
		cout << "\t\t|                               |\n";
		cout << "\t\t|          2.老    师           |\n";
		cout << "\t\t|                               |\n";
		cout << "\t\t|          3.管 理 员           |\n";
		cout << "\t\t|                               |\n";
		cout << "\t\t|          0.退    出           |\n";
		cout << "\t\t|                               |\n";
		cout << "\t\t -------------------------------\n";
		cout << "输入您的选择: ";

		cin >> select; //接受用户选择

		switch (select)
		{
		case 1:  //学生身份
			LoginIn(1);
			break;
		case 2:  //老师身份
			LoginIn(2);
			break;
		case 3:  //管理员身份
			LoginIn(3);
			break;
		case 0:  //退出系统
			cout << "欢迎下一次使用" << endl;
			system("pause");
			return 0;
			break;
		default:
			cout << "输入有误，请重新选择！" << endl;
			system("pause");
			system("cls");
			break;
		}

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

void LoginIn(int type)
{
	
	Identity* person = NULL;

	int id = 0;
	string name;
	string pwd;

	if (type == 1) //学生登录
	{
		cout << "请输入你的学号" << endl;
		cin >> id;
	}
	else if (type == 2) //教师登录
	{
		cout << "请输入你的职工号" << endl;
		cin >> id;
	}
	cout << "请输入用户名：" << endl;
	cin >> name;

	cout << "请输入密码： " << endl;
	cin >> pwd;

	if (type == 1)
	{
		//学生登录验证
		string query = "select * from student where stuid = " + to_string(id)  + " and name = '" + name + "'" + "and pass = '" + pwd + "'";
		if (select(query))
		{
			cout << "学生验证登录成功!" << endl;
			system("pause");
			system("cls");
			person = new Student(id, name, pwd);
			studentMenu(person);
			return;
		}
	}
	else if (type == 2)
	{
		//教师登录验证
		string query = "select * from teacher where teacid = " + to_string(id)  + " and name = '" + name + "'" + "and pass = '" + pwd + "'";
		if (select(query))
		{
			cout << "教师验证登录成功!" << endl;
			system("pause");
			system("cls");
			person = new Teacher(id, name, pwd);
			TeacherMenu(person);
			return;
		}
	}
	else if (type == 3)
	{
		//管理员登录验证
		string query = "select * from admin where name = '" + name + "'" + "and pass = '" + pwd + "'";
		if (select(query))
		{
			cout << "管理员验证登录成功!" << endl;
			system("pause");
			system("cls");
			person = new Manager(name, pwd);
			managerMenu(person);
			return;
		}
	}

	cout << "验证登录失败!" << endl;
	system("pause");
	system("cls");
	return;
}

//管理员菜单
void managerMenu(Identity*& manager)
{
	while (1) 
	{
		// 此时还是父类 无法访问子类的函数
		manager->operMenu();

		// 将父类指针转化为子类指针，调用子类其他接口
		Manager* man = (Manager*)manager;
		int select = 0;
		cin >> select;

		if (select == 1)  //添加账号
		{
			cout << "添加账号" << endl;
			man->addPerson();
		}
		else if (select == 2) //查看账号
		{
			cout << "查看账号" << endl;
			man->showPerson();
		}
		else if (select == 3) //查看机房
		{
			cout << "查看机房" << endl;
			man->showComputer();
		}
		else if (select == 4) //清空预约
		{
			cout << "清空预约" << endl;
			man->cleandata();
		}
		else
		{
			delete manager;
			cout << "注销成功" << endl;
			system("pause");
			system("cls");
			return;
		}

	}
}

//学生菜单
void studentMenu(Identity*& student)
{
	while (true)
	{
		//学生菜单
		student->operMenu();

		Student* stu = (Student*)student;
		int select = 0;

		cin >> select;

		if (select == 1) //申请预约
		{
			stu->applyOrder();
		}
		else if (select == 2) //查看自身预约
		{
			stu->showMyOrder();
		}
		else if (select == 3) //查看所有预约
		{
			stu->showAllorder();
		}
		else if (select == 4) //取消预约
		{
			stu->cancelorder();
		}
		else
		{
			delete student;
			cout << "注销成功" << endl;
			system("pause");
			system("cls");
			return;
		}
	}
}
// 教师菜单
void TeacherMenu(Identity * &teacher)
{
	while (true)
	{
		//教师菜单
		teacher->operMenu();

		Teacher* tea = (Teacher*)teacher;
		int select = 0;

		cin >> select;

		if (select == 1)
		{
			//查看所有预约
			tea->showAllorder();
		}
		else if (select == 2)
		{
			//审核预约
			tea->validorder();
		}
		else
		{
			delete teacher;
			cout << "注销成功" << endl;
			system("pause");
			system("cls");
			return;
		}

	}
}

管理员

主要就是将原来从文件读取信息，换成从数据库查询。

//manager.c
#include "manager.h"

//默认构造
Manager::Manager()
{
}

//有参构造
Manager::Manager(string name, string pwd)
{
	this->m_Name = name;
	this->m_Pwd = pwd;
}

//选择菜单
void Manager::operMenu()
{
	cout << "欢迎管理员：" << this->m_Name << "登录！" << endl;
	cout << "\t\t ---------------------------------\n";
	cout << "\t\t|                                |\n";
	cout << "\t\t|          1.添加账号            |\n";
	cout << "\t\t|                                |\n";
	cout << "\t\t|          2.查看账号            |\n";
	cout << "\t\t|                                |\n";
	cout << "\t\t|          3.查看机房            |\n";
	cout << "\t\t|                                |\n";
	cout << "\t\t|          4.清空预约            |\n";
	cout << "\t\t|                                |\n";
	cout << "\t\t|          0.注销登录            |\n";
	cout << "\t\t|                                |\n";
	cout << "\t\t ---------------------------------\n";
	cout << "请选择您的操作： " << endl;
}

//添加账号  
void Manager::addPerson()
{
	cout << "请输入添加账号的类型" << endl;
	cout << "1、添加学生" << endl;
	cout << "2、添加老师" << endl;

	string tip;
	string errorTip; //重复错误提示

	int select = 0;
	cin >> select;

	if (select == 1)
	{
		tip = "请输入学号： ";
		errorTip = "学号重复，请重新输入";
	}
	else
	{
		tip = "请输入职工编号：";
		errorTip = "职工号重复，请重新输入";
	}
	int id;
	string name;
	string pwd;
	cout << tip << endl;
	cin >> id;

	cout << "请输入姓名： " << endl;
	cin >> name;

	cout << "请输入密码： " << endl;
	cin >> pwd;
	if (select == 1)
	{
		string query = "insert into student (stuid,name,pass) values (" + to_string(id) + ",'" + name + "'," + "'" + pwd + "');";
		if(insert(query))
			cout << "添加成功" << endl;
		else {
			cout << errorTip << endl;
		}
	}
	else
	{
		string query = "insert into teacher (teacid,name,pass) values (" + to_string(id) + ",'" + name + "'," + "'" + pwd + "');";
		if (insert(query))
			cout << "添加成功" << endl;
		else {
			cout << errorTip << endl;
		}
	}
	system("pause");
	system("cls");

}

//查看账号
void Manager::showPerson()
{
	cout << "请选择查看内容：" << endl;
	cout << "1、查看所有学生" << endl;
	cout << "2、查看所有老师" << endl;

	MYSQL_RES* res;
	MYSQL_ROW row;

	int select = 0;
	cin >> select;

	if (select == 1)
	{
		cout << "所有学生信息如下： " << endl;
		string query = "select * from student";
		res = showinfo(query);
		while ((row = mysql_fetch_row(res)) != NULL) {//获取结果集(res)中的一行,并且指针指向下一行
			cout << "学号： " << row[0] << " 姓名： " << row[1] << " 密码：" << row[2] << endl;
		}
	}
	else
	{
		cout << "所有老师信息如下： " << endl;
		string query = "select * from teacher";
		res = showinfo(query);
		while ((row = mysql_fetch_row(res)) != NULL) {//获取结果集(res)中的一行,并且指针指向下一行
			cout << "职工号： " << row[0] << " 姓名： " << row[1] << " 密码：" << row[2] << endl;
		}
	}
	system("pause");
	system("cls");
}

//查看机房信息
void Manager::showComputer()
{
	cout << "机房信息如下：" << endl;
	MYSQL_RES* res;
	MYSQL_ROW row;
	string query = "select * from room";
	res = showinfo(query);
	while ((row = mysql_fetch_row(res)) != NULL) {//获取结果集(res)中的一行,并且指针指向下一行
		cout << "机房编号： " << row[0] << " 机房最大容量： " << row[1] <<  endl;
	}
	system("pause");
	system("cls");
}

//清空预约记录
void Manager::cleandata()
{

	string query = "delete from order_info";
	delete_(query);
	system("pause");
	system("cls");
}

学生

#include "student.h"

//默认构造
Student::Student()
{
}

//有参构造(学号、姓名、密码)
Student::Student(int id, string name, string pwd)
{
	//初始化属性
	this->m_Id = id;
	this->m_Name = name;
	this->m_Pwd = pwd;

}

//菜单界面
void Student::operMenu()
{
	cout << "欢迎学生代表：" << this->m_Name << "登录！" << endl;
	cout << "\t\t ----------------------------------\n";
	cout << "\t\t|                                 |\n";
	cout << "\t\t|          1.申请预约              |\n";
	cout << "\t\t|                                 |\n";
	cout << "\t\t|          2.查看我的预约          |\n";
	cout << "\t\t|                                 |\n";
	cout << "\t\t|          3.查看所有预约          |\n";
	cout << "\t\t|                                 |\n";
	cout << "\t\t|          4.取消预约              |\n";
	cout << "\t\t|                                 |\n";
	cout << "\t\t|          0.注销登录              |\n";
	cout << "\t\t|                                 |\n";
	cout << "\t\t ----------------------------------\n";
	cout << "请选择您的操作： " << endl;
}

//申请预约
void Student::applyOrder()
{
	cout << "机房开放时间为周一至周五！" << endl;
	cout << "请输入申请预约的时间：" << endl;
	cout << "1、周一" << endl;
	cout << "2、周二" << endl;
	cout << "3、周三" << endl;
	cout << "4、周四" << endl;
	cout << "5、周五" << endl;
	int date = 0;
	int interval = 0;
	int room = 0;

	while (true) {
		cin >> date;
		if (date >= 1 && date <= 5)
		{
			break;
		}
		cout << "输入有误，请重新输入" << endl;
	}

	cout << "请输入申请预约的时间段：" << endl;
	cout << "1、上午" << endl;
	cout << "2、下午" << endl;
	while (true)
	{
		cin >> interval;
		if (interval >= 1 && interval <= 2)
		{
			break;
		}
		cout << "输入有误，请重新输入" << endl;
	}

	MYSQL_RES* res;
	MYSQL_ROW row;
	string query = "select * from room";
	res = showinfo(query);
	while ((row = mysql_fetch_row(res)) != NULL) {//获取结果集(res)中的一行,并且指针指向下一行
		cout << "机房编号： " << row[0] << " 机房最大容量： " << row[1] << endl;
	}
	while (true)
	{
		cin >> room;
		if (room >= 1 && room <= 3)
		{
			break;
		}
		cout << "输入有误，请重新输入" << endl;
	}
	

	query = "insert into order_info (time_,period,stuid,name,roomid,status) values (" + to_string(date) + "," + to_string(interval) + "," + to_string(this->m_Id) + ",'"+this->m_Name + "'," + to_string(room) + ",'" + "审核中" + "');";
	insert(query);
	cout << "预约成功！审核中" << endl;
	system("pause");
	system("cls");

}

//查看我的预约
void Student::showMyOrder()
{
	MYSQL_RES* res;
	MYSQL_ROW row;
	string query = "select * from order_info where name = "+this->m_Name;
	res = showinfo(query);
	int count = mysql_num_rows(res);
	if (count == 0)
	{
		cout << "无预约记录" << endl;
		system("pause");
		system("cls");
		return;
	}
	while ((row = mysql_fetch_row(res)) != NULL)
	{
		cout << "预约日期:周" << row[1];
		cout << " 时段:" << (row[2] == "1" ? "上午" : "下午");
		cout << " 机房:" << row[5];
		string status = " 状态:";  // 0 取消的预约   1 审核中   2 已预约 -1 预约失败
		cout << status << row[6] <<endl;
	}

	system("pause");
	system("cls");
}

//查看所有预约
void Student::showAllorder()
{
	MYSQL_RES* res;
	MYSQL_ROW row;
	string query = "select * from order_info";
	res = showinfo(query);
	int count = mysql_num_rows(res);
	if (count == 0)
	{
		cout << "无预约记录" << endl;
		system("pause");
		system("cls");
		return;
	}

	while ((row = mysql_fetch_row(res)) != NULL)
	{
		cout << "预约日期:周" << row[1];
		cout << " 时段:" << (row[2] == "1" ? "上午" : "下午");
		cout << " 机房:" << row[5];
		int s = int(row[6]);
		string status = " 状态:";  // 0 取消的预约   1 审核中   2 已预约 -1 预约失败
		cout << status << row[6] << endl;
	}

	system("pause");
	system("cls");

}

//取消预约
void Student::cancelorder()
{
	MYSQL_RES* res;
	MYSQL_ROW row;
	string query = "select * from order_info where name = " + this->m_Name;
	res = showinfo(query);
	int count = mysql_num_rows(res);
	if (count == 0)
	{
		cout << "无预约记录" << endl;
		system("pause");
		system("cls");
		return;
	}
	while ((row = mysql_fetch_row(res)) != NULL)
	{
		string ss = row[6];
		if (ss == "审核中" || ss == "已预约") {
			cout << "ID:" << row[0];
			cout << " 预约日期:周" << row[1];
			cout << "  时段:" << (row[2] == "1" ? "上午" : "下午");
			cout << "  机房:" << row[5];
			// 0 取消的预约   1 审核中   2 已预约 -1 预约失败
			cout << "  状态:" << row[6] << endl;
		}
	}
	cout << "请输入取消的记录,0代表返回" << endl;
	int select = 0;
	while (true)
	{
		cin >> select;
		if (select >= 0)
		{
			if (select == 0)
			{
				break;
			}
			else
			{
				query = "update order_info set status='已取消' where id="+ to_string(select);
				update(query);
				cout << "已取消预约" << endl;
				break;
			}

		}
		cout << "输入有误，请重新输入" << endl;
	}
	system("pause");
	system("cls");
}

老师

#include"teacher.h"

//默认构造
Teacher::Teacher()
{
}

//有参构造 (职工编号，姓名，密码)
Teacher::Teacher(int empId, string name, string pwd)
{
	//初始化属性
	this->m_EmpId = empId;
	this->m_Name = name;
	this->m_Pwd = pwd;
}

//菜单界面
void Teacher::operMenu()
{
	cout << "欢迎教师：" << this->m_Name << "登录！" << endl;
	cout << "\t\t ----------------------------------\n";
	cout << "\t\t|                                  |\n";
	cout << "\t\t|          1.查看所有预约          |\n";
	cout << "\t\t|                                  |\n";
	cout << "\t\t|          2.审核预约              |\n";
	cout << "\t\t|                                  |\n";
	cout << "\t\t|          0.注销登录              |\n";
	cout << "\t\t|                                  |\n";
	cout << "\t\t ----------------------------------\n";
	cout << "请选择您的操作： " << endl;
}

//查看所有预约
void Teacher::showAllorder()
{
	MYSQL_RES* res;
	MYSQL_ROW row;
	string query = "select * from order_info";
	res = showinfo(query);
	int count = mysql_num_rows(res);
	if (count == 0)
	{
		cout << "无预约记录" << endl;
		system("pause");
		system("cls");
		return;
	}

	while ((row = mysql_fetch_row(res)) != NULL)
	{
		cout << "预约日期:周" << row[1];
		cout << " 时段:" << (row[2] == "1" ? "上午" : "下午");
		cout << " 学号：" << row[3];
		cout << " 姓名：" << row[4];
		cout << " 机房:" << row[5];
		string status = " 状态:";  // 0 取消的预约   1 审核中   2 已预约 -1 预约失败
		cout << status << row[6] << endl;
	}

	system("pause");
	system("cls");
}

//审核预约
void Teacher::validorder()
{
	MYSQL_RES* res;
	MYSQL_ROW row;
	string s = "审核中";
	string query = "select * from order_info where status = '" + s+"'";
	res = showinfo(query);
	int count = mysql_num_rows(res);
	if (count == 0)
	{
		cout << "无预约记录" << endl;
		system("pause");
		system("cls");
		return;
	}
	cout << "待审核的预约记录如下：" << endl;
	while ((row = mysql_fetch_row(res)) != NULL)
	{
		string ss = row[6];
		if (ss == "审核中") {
			cout << "ID:" << row[0];
			cout << " 预约日期:周" << row[1];
			cout << "  时段:" << (row[2] == "1" ? "上午" : "下午");
			cout << "  机房:" << row[5];
			// 0 取消的预约   1 审核中   2 已预约 -1 预约失败
			cout << "  状态:" << row[6] << endl;
		}
	}
	cout << "请输入审核的预约记录,0代表返回" << endl;
	int select = 0;
	int ret = 0;
	while (true)
	{
		cin >> select;
		if (select >= 0)
		{
			if (select == 0)
			{
				break;
			}
			else
			{
				cout << "请输入审核结果" << endl;
				cout << "1、通过" << endl;
				cout << "2、不通过" << endl;
				cin >> ret;

				if (ret == 1)
				{
					query = "update order_info set status='通过' where id=" + to_string(select);
					update(query);
					break;
				}
				else
				{
					query = "update order_info set status='不通过' where id=" + to_string(select);
					update(query);
					break;
				}
				cout << "审核完毕！" << endl;
				break;
			}
		}
		cout << "输入有误，请重新输入" << endl;
	}

	system("pause");
	system("cls");

}

4、数据库代码

头文件

#pragma once
//mysql_option.h
#include<iostream>
#include<mysql.h>
#include<string>
using namespace std;
void getConnection(MYSQL& mysql, string host, string userName, string passWord, string dbName);
bool select(string op);
bool insert(string op);
MYSQL_RES* showinfo(string op);
void update(string op);
void delete_(string op);
void close();
int mysql_option();

.cpp

//mysql_option.cpp
#include"mysql_option.h"
MYSQL mysql;//数据库结构体

//与MySql数据库建立连接
void getConnection(MYSQL& mysql, string host, string userName, string passWord, string dbName) {
	mysql_init(&mysql);// 初始化一个MYSQL 连接的实例对象
	//连接MySql
	if (mysql_real_connect(&mysql, host.c_str(), userName.c_str(), passWord.c_str(), dbName.c_str(), 3306, 0, 0) == NULL)
	{
		printf("连接失败！\\n");
	}
	mysql_query(&mysql, "SET NAMES GB2312");//修改mysql编码方式
	//mysql_query(&mysql, "SET NAMES UTF8");
}
bool select(string op) {//查询
	mysql_query(&mysql, op.c_str());
	MYSQL_RES* res = mysql_store_result(&mysql);//返回结果集
	int count1 = mysql_num_rows(res);//获取记录个数(参数为结果集行数,仅select可用)
	if (count1 == 1)
		return true;
}
bool insert(string op) {//插入
	int count = 0;
	mysql_query(&mysql, op.c_str());
	if (mysql_affected_rows(&mysql) == -1)
		return false;
	return true;
}
MYSQL_RES* showinfo(string op)
{
	mysql_query(&mysql, op.c_str());
	MYSQL_RES* res = mysql_store_result(&mysql);//返回结果集
	return res;
}
void update(string op) {//更新
	int count = 0;
	mysql_query(&mysql, op.c_str());
	count = mysql_affected_rows(&mysql);
}
void delete_(string op) {//删除
	mysql_query(&mysql, op.c_str());
	if (mysql_affected_rows(&mysql) != 0)
		cout << "清空成功！" << endl;
}
void close()
{
	//释放一个MYSQL连接
	mysql_close(&mysql);
	printf("结束");
}
int mysql_option() {

	string host = "localhost";
	string userName = "root";//mysql账户
	string passWord = "123456";///mysql账户密码
	string dbName = "com_management";//mysql数据库名
	getConnection(mysql, host, userName, passWord, dbName);
	return 0;
}

部分功能还不是很完善。