确保你的数据库已经在运行了,才能用C语言连接。
如果没有运行,则会报错:
数据库连接失败: Can't connect to MySQL server on 'localhost:3306' (10061)
一、数据库连接和准备
#include<bits/stdc++.h>
#include <mysql.h>
using namespace std;
MYSQL *conn;
MYSQL_RES *res;
MYSQL_ROW row;
int main(void){
/*连接数据库部分*/
conn = mysql_init(NULL);
mysql_set_character_set(conn, "utf8");/*设置数据库信息的编码*/
const char *server = "localhost";
const char *user = "root";
const char *password = "yorelee1125";
const char *database = "design";//填写数据库名称
/*检查数据库连接*/
if (!mysql_real_connect(conn, server, user, password, database, 0, NULL, 0)) {
std::cerr << "数据库连接失败: " << mysql_error(conn) << std::endl;
//为防止cerr和cout输出顺序非预期,可以使用线程停止等待操作,保证cerr输出完毕。
//std::this_thread::sleep_for(std::chrono::milliseconds(50));
return 1;
}
/*进行你的数据库操作*/
/*关闭数据库*/
mysql_free_result(res);
mysql_close(conn);
}
二、数据库操作
更新和删除只需要执行操作,执行成功则更新或删除成功。查询操作不仅需要执行操作,还可能需要根据查询结果是否为空,进行进一步处理。最后可能需要输出查询结果。
如果急着用,可以直接跳过a.基础
,直接看具体的语句,涂涂改改就好了。
a.基础
- 执行SQL语句 :
mysql_query(conn,query)
- 该语句这个函数在执行成功时返回 0,如果有错误发生(比如语法错误、连接问题等)则返回非 0 值。值得注意的是这个操作包含了查询、更新、插入、删除等MySQL数据库操作,用这一个语句就能执行数据库操作。
sprintf(query, "SELECT Course.ID, Course.title, Course.credit FROM Teaches JOIN Course ON Teaches.Course_ID = Course.ID WHERE Teaches.Instructor_ID = %d", id);
// 执行SQL操作
if (mysql_query(conn, query)) {
cerr << "查询失败:" << mysql_error(conn) << endl;
//std::this_thread::sleep_for(std::chrono::milliseconds(50));
return;
}
- 如果是查询操作,需要判断查询是否成功:
mysql_store_result(conn)
mysql_store_result(conn)
返回NULL
, 通常意味着查询执行失败或没有结果集(例如执行了一个 INSERT、UPDATE 或 DELETE 语句)。没有结果集并不代表查询结果为空,空集并不等于没有集合结果。执行了非查询操作并未返回结果集,则返回NULL
,可以用来进一步判断是否想进行查询,但未按预期进行查询操作。
MYSQL_RES *result = mysql_store_result(conn);
if (result == NULL) {
cerr << "查询失败:" << mysql_error(conn) << endl;
return;
}
result=mysql_store_result(conn)
- 如果是查询操作,当结果集为空,可能需要进行处理:
mysql_num_rows(result)
- 当查询结果集行数是0时,
mysql_num_rows(result)
返回0
- 如果是查询操作,当结果集为空,可能需要进行处理:
//查看查询结果是否为空
if (mysql_num_rows(result) == 0) {
mysql_free_result(result);
cout<<"查无此人..."<<endl;
return false;
}
- 如果是查询操作,可能需要输出结果集:
row = mysql_fetch_row(result)
- 执行
mysql_fetch_row(result)
,每次取出一行数据,返回类型是:MYSQL_ROW
row
被定义为MYSQL_ROW
类型row[i]
表示当前行的第i列的数据,是一个const char*
类型。
- 执行
//输出结果
MYSQL_ROW row;
bool hasCourses = false;
while ((row = mysql_fetch_row(result))) {
hasCourses = true;
cout << "课程ID: " << row[0] << ", 课程名称: " << row[1] << ", 学分: " << row[2] << endl;
}
if(hasCourses==false) {//和刚刚查询结果为空实际上是一样的。}
- 释放结果:
mysql_free_result()
- 参数是之前
mysql_store_result(conn)
返回的指针。
- 参数是之前
mysql_free_result(result);//释放结果
b.查询
- 定义查询操作
- 执行SQL查询
- 检查查询是否成功
- 输出结果
只需要修改查询操作 和 输出操作就可以了。
char query[1024];
/*输入你的查询操作,里面是MySQL的语句,其中可以用 格式控制符 来规定值*/
sprintf(query, "SELECT Course.ID, Course.title, Course.credit FROM Teaches JOIN Course ON Teaches.Course_ID = Course.ID WHERE Teaches.Instructor_ID = %d", id);
// 执行SQL操作
if (mysql_query(conn, query)) {
cerr << "查询失败:" << mysql_error(conn) << endl;
//std::this_thread::sleep_for(std::chrono::milliseconds(50));
return;
}
//查看查询是否成功
MYSQL_RES *result = mysql_store_result(conn);
if (result == NULL) {
cerr << "查询失败:" << mysql_error(conn) << endl;
//std::this_thread::sleep_for(std::chrono::milliseconds(50));
return;
}
//查看查询结果是否为空
if (mysql_num_rows(result) == 0) {
mysql_free_result(result);
cout<<"没有可以授课的老师..."<<endl;
return false;
}
//输出结果
MYSQL_ROW row;
bool hasCourses = false;
while ((row = mysql_fetch_row(result))) {
hasCourses = true;
cout << "课程ID: " << row[0] << ", 课程名称: " << row[1] << ", 学分: " << row[2] << endl;
}
mysql_free_result(result);//释放结果
c.删除
- 定义删除操作
- 执行删除
char query2[1024];
sprintf(query2, "DELETE FROM Instructor WHERE ID = %d", id);
if (mysql_query(conn, query2)) {
cerr << "删除教师失败: " << mysql_error(conn) << endl;
//std::this_thread::sleep_for(std::chrono::milliseconds(50));
} else {
cout << "教师删除成功!" << endl;
}
d.更新
- 定义更新操作
- 执行更新
char query[256];
sprintf(query, "UPDATE Instructor SET Is_Formal = 1 WHERE ID = %d", id);
if (mysql_query(conn, query)) {
cerr << "转正失败: " << mysql_error(conn) << endl;
//std::this_thread::sleep_for(std::chrono::milliseconds(50));
} else {
cout << "老师已成功转正!" << endl;
}
三、教学管理系统
值得注意的是,MySQL删除数据的时候,并不会因为数据不存在而报错。如果需要不存在提示,则需要写一个查询判断是否存在来进行提示。
这里给出的交互代码只是示例,功能大部分是完善的,由于是纯手打,难免存在问题。
一、表和触发器
create database design;
use design;
create table Student (
ID int auto_increment primary key,
name varchar(100) not null,
total_gain_credit int default 0,
School varchar(20) not null,
student_ID int not null, -- 所在学校对应的学号信息,只用于存储防止相同
unique (School,student_ID)
);
create table Instructor (
ID int auto_increment primary key,
name varchar(100) not null,
ID_card int not null,
salary int default 0,
Is_Formal boolean default false,
unique(ID_card) -- 防止同一个老师创建多个
);
create table Course (
ID int auto_increment primary key,
title varchar(255) not null,
credit decimal(3, 1) not null
);
create table Classroom (
Room_Number varchar(50) primary key,
Capacity int not null
);
create table Time_Slot (
Time_Slot_ID int auto_increment primary key,
Day varchar(10) not null,
Start_Time time not null,
End_Time time not null
);
create table Takes (
Student_ID int,
Course_ID int,
Grade decimal(5, 2),
primary key (Student_ID, Course_ID),
foreign key (Student_ID) references Student(ID),
foreign key (Course_ID) references Course(ID)
);
create table Teaches (
Instructor_ID int,
Course_ID int,
primary key (Instructor_ID, Course_ID),
foreign key (Instructor_ID) references Instructor(ID),
foreign key (Course_ID) references Course(ID)
);
create table Course_Timing (
Course_ID int,
Time_Slot_ID int,
Room_Number varchar(50),
primary key (Course_ID, Time_Slot_ID, Room_Number),
foreign key (Course_ID) references Course(ID),
foreign key (Time_Slot_ID) references Time_Slot(Time_Slot_ID),
foreign key (Room_Number) references Classroom(Room_Number)
);
ALTER TABLE Course_Timing
ADD UNIQUE (Course_ID);
delimiter $$
create trigger CheckTeacherBeforeInsert -- 检查课设是否已经有老师授课
before insert on Takes
for each row
begin
declare teacher_count int;
select count(*) into teacher_count
from Teaches
where Course_ID = new.Course_ID;
if teacher_count = 0 then
signal sqlstate '45000'
set message_text = '没有老师授课,不能被选择';
end if;
end$$
delimiter ;
delimiter $$
-- 触发器:在更新成绩后检查并更新学生的累计学分
create trigger UpdateCreditsAfterGradeChange
after update on Takes
for each row
begin
-- 当成绩从低于80分提高到80分及以上时,增加学分
if old.Grade < 80 and new.Grade >= 80 then
update Student
set total_gain_credit = total_gain_credit + (select credit from Course where ID = new.Course_ID)
where ID = new.Student_ID;
end if;
-- 当成绩从80分及以上降低到低于80分时,减少学分
if old.Grade >= 80 and new.Grade < 80 then
update Student
set total_gain_credit = total_gain_credit - (select credit from Course where ID = old.Course_ID)
where ID = old.Student_ID;
end if;
end$$
delimiter ;
delimiter $$
create trigger PreventMultipleInstructors -- 管理员添加授课教师 防止一门课程被多个教师教授
before insert on Teaches
for each row
begin
declare instructor_count int;
select count(*) into instructor_count
from Teaches
where Course_ID = new.Course_ID;
if instructor_count > 0 then
signal sqlstate '45000'
set message_text = 'There are already teachers teaching the course.';
end if;
end$$
delimiter $$
create trigger CheckClassroomConflict -- 管理员分配课程时间和教师,检查课程时间和教室的冲突
before insert on Course_Timing
for each row
begin
declare conflict_count int;
select count(*) into conflict_count
from Course_Timing
where Room_Number = new.Room_Number and Time_Slot_ID = new.Time_Slot_ID;
if conflict_count > 0 then
signal sqlstate '45000'
set message_text = '同一教室和时间不能分配给不同的课程';
end if;
end$$
delimiter $$
-- 删除学生时删除其选课记录
delimiter $$
create trigger DeleteStudentTakes
before delete on Student
for each row
begin
delete from Takes where Student_ID = old.ID;
end$$
-- 删除教师时删除其教课记录
create trigger DeleteInstructorTeaches
before delete on Instructor
for each row
begin
delete from Teaches where Instructor_ID = old.ID;
end$$
delimiter $$
-- 课程学分变动时更新学生总学分
delimiter $$
create trigger UpdateCreditsOnCourseChange
after update on Course
for each row
begin
if old.Credit <> new.Credit then
-- 先找出所有受影响的学生
update Student
set Total_Gain_Credit = Total_Gain_Credit + (new.Credit - old.Credit)
where ID in (
select Student_ID
from Takes
where Course_ID = old.ID and Grade >= 80
);
end if;
end$$
delimiter ;
DELIMITER $$
CREATE TRIGGER BeforeInstructorDelete
BEFORE DELETE ON Instructor
FOR EACH ROW
BEGIN
-- 删除所有该教师授课的课程对应的学生选课信息
DELETE FROM Takes
WHERE Course_ID IN (
SELECT Course_ID FROM Teaches
WHERE Instructor_ID = OLD.ID
);
-- 如果需要,也可以在这里添加删除该教师授课记录的代码
DELETE FROM Teaches
WHERE Instructor_ID = OLD.ID;
END$$
DELIMITER ;
DELIMITER $$ -- 查看techeas里面
CREATE TRIGGER CheckCourseBeforeAssign
BEFORE INSERT ON Teaches
FOR EACH ROW
BEGIN
DECLARE course_exists INT;
SELECT COUNT(*) INTO course_exists FROM Course_Timing WHERE Course_ID = NEW.Course_ID;
IF course_exists = 0 THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'The course is not scheduled in Course_Timing';
END IF;
END$$
DELIMITER ;
DELIMITER $$
CREATE TRIGGER check_room_availability BEFORE INSERT ON Course_Timing
FOR EACH ROW
BEGIN
DECLARE conflict_count INT;
-- 检查新插入的课程时间是否与同一教室的其他课程时间冲突
SELECT COUNT(*) INTO conflict_count
FROM Course_Timing AS existing
JOIN Time_Slot AS new_slot ON new_slot.Time_Slot_ID = NEW.Time_Slot_ID
JOIN Time_Slot AS existing_slot ON existing_slot.Time_Slot_ID = existing.Time_Slot_ID
WHERE existing.Room_Number = NEW.Room_Number
AND existing.Course_ID != NEW.Course_ID
AND existing_slot.Day = new_slot.Day
AND (
(new_slot.Start_Time < existing_slot.End_Time AND new_slot.End_Time > existing_slot.Start_Time)
);
-- 如果存在冲突,中止插入操作
IF conflict_count > 0 THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Time slot conflict in the same room.';
END IF;
END$$
DELIMITER ;
二、C++交互代码
#include<bits/stdc++.h>
#include <mysql.h>
using namespace std;
MYSQL *conn;
MYSQL_RES *res;
MYSQL_ROW row;
string admin_account="admin";
string admin_password="yorelee0328";
bool check(string & s){//查看是否是数字
for(auto i :s){
if(i<'0'||i>'9') return true;
}
return false;
}
int transfer(string & s){
int temp=0;
for(auto i:s){
temp*=10;
temp+=i-'0';
}
return temp;
}
class Admin{
public:
Admin(){
cout<<">>> 进入管理员登陆页面"<<endl;
string id;
string password;
int num=3;
while(num) {
cout << "请依次输入管理员账号和密码:" << endl;
cout << "账户:";
cin >> id;
cout << "密码:";
cin >> password;
if (id == admin_account && admin_password == password) {
cout<<">>> 登陆成功! <<<"<<endl;
init();
break;
}else{
cout<<"账号或密码错误,请重新输入。"<<"还剩"<<num-1<<"次机会。"<<endl;
num--;
}
}
}
private:
void init(){
std::this_thread::sleep_for(std::chrono::milliseconds(1000));
std::cout <<">>> 进入管理员操作选择界面 <<<"<<endl;
std::this_thread::sleep_for(std::chrono::milliseconds(1000));
cout<<">> 请输入你想进行的操作:<<"<<endl;
cout<<"A:查看教师信息"<<endl;//
cout<<"B:查看学生信息"<<endl;
cout<<"C:查看课程时间安排信息"<<endl;//
cout<<"D:查看教室信息"<<endl;//
cout<<"E:查看时间片信息"<<endl;//
cout<<"------------"<<endl;
cout<<"0:添加课程时间安排信息"<<endl;//
cout<<"1:导入教师信息"<<endl;//
cout<<"2:分配教师授课"<<endl;//
cout<<"3:修改教师的薪资"<<endl;//
cout<<"4:转正教师"<<endl;//
cout<<"------------"<<endl;
cout<<"5:删除教师信息"<<endl;//
cout<<"6:删除课程时间安排信息"<<endl;//
cout<<"7:删除教师授课信息"<<endl;//
cout<<"------------"<<endl;
cout<<"*:退出"<<endl;
std::cout.flush(); // 确保所有挂起的输出都已经被写出
string s;cin>>s;
if(s.size()==1){
switch(s[0]){
case 'A':search_instructor();break;case 'a':search_instructor();break;
case 'B':search_student();break;case 'b':search_student();break;
case 'C':displayCourseTimings();break;case 'c':displayCourseTimings();break;
case 'D':displayClassRoom();break;case 'd':displayClassRoom();break;
case 'E':displayTime_slot();break;case 'e':displayTime_slot();break;
case '0':add_course_info(3);break;
case '1':add_instructor_info(3);break;
case '2':assign_instructor(3);break;
case '3':modify_salary(3);break;
case '4':To_Formal(3);break;
case '5':delete_instructor(3);break;
case '6':delete_course_timing(3);break;
case '7':delete_teaches(3);break;
case '*': return;
}
}
init();
return;
}
private:
//删除教师信息
void delete_instructor(int num){//
displayInstructors();
/******************************************/
cout << "请输入要删除的教师ID: (0:退出)\n";
string instructor_id;
cin >> instructor_id;
if(check(instructor_id)||instructor_id.size()>9){
cout<<"请输入正确的instructor_id"<<endl;
cout<<"还剩"<<num-1<<"次机会。"<<endl;
delete_instructor(num-1);
return;
}
int id=transfer(instructor_id);
if(id==0) return;
if(!find_instructor(id)){
cout << "查无此人。\n";
cout<<"还剩"<<num-1<<"次机会。\n";
delete_instructor(num-1);
return;
}
// 删除该教师所有授课的课程对应的学生选课信息
char query2[1024];
sprintf(query2, "DELETE FROM Instructor WHERE ID = %d", id);
if (mysql_query(conn, query2)) {
cerr << "删除教师失败: " << mysql_error(conn) << endl;
std::this_thread::sleep_for(std::chrono::milliseconds(50));
} else {
cout << "教师删除成功!" << endl;
}
}//添加了查无此人
//添加课程信息
void add_course_info(int num) {
if(num==0) return;
// 展示当前课程信息
if(num==3) {
displayOptions("SELECT ID, title, credit FROM Course", "可以添加的课程信息:");
// 展示教室信息
displayOptions("SELECT Room_Number, Capacity FROM Classroom", "可以添加的教室信息:");
// 展示时间片信息
displayOptions("SELECT Time_Slot_ID, Day, Start_Time, End_Time FROM Time_Slot", "时间信息:");
}
cout<<">> 请输入course_id,Room_Number和Time_slot_id 以便于添加课程信息:"<<endl;
// 输入信息
string course, time_slot;
string room_number;
cout <<"输入course_id:";
cin >> course;
if (course.size() > 9 || check(course) ) {
cout << "请输入正确的课程ID,还剩"<<num-1<<"次机会。" << endl;
add_course_info(num-1);
return;
}
cout <<"输入Room_Number:";
cin >> room_number;
cout <<"输入Time_slot_id:";
cin >> time_slot;
if (time_slot.size() > 9 || check(time_slot) ) {
cout << "请输入正确的Time_slot_ID,还剩"<<num-1<<"次机会。" << endl;
add_course_info(num-1);
return;
}
int course_id, time_slot_id;
course_id=transfer(course);
time_slot_id=transfer(time_slot);
// Insert into Course_Timing
char query[256];
sprintf(query, "INSERT INTO Course_Timing (Course_ID, Time_Slot_ID, Room_Number) VALUES (%d, %d, '%s')",
course_id, time_slot_id, room_number.c_str());
std::cout.flush(); // 确保所有挂起的输出都已经被写出
if (mysql_query(conn, query)) {
cerr << "添加课程信息失败:" << mysql_error(conn) << endl;
std::this_thread::sleep_for(std::chrono::milliseconds(50));
cout<<"还剩"<<num-1<<"次机会。" << endl;
std::cout.flush();
add_course_info(num-1);
return;
} else {
cout << "添加课程信息成功!" << endl;
}
return;
}
//导入老师信息,这必然是正式老师
void add_instructor_info(int num){
if (num == 0) return;
if(num!=3){
cout<<"是否退出?(0:退出)"<<endl;
string s;
cin>>s;
if(s.size()==1&&s[0]=='0') return;
}
cout << ">> 导入正式教师账号 <<" << endl;
cout << "请依次输入姓名、身份证和instructor_id 以及密码,以注册账号" << endl;
string name;
int ID_card, instructor_id;
string id1;
string id_card;
cout << "姓名:"; cin >> name;
cout << "身份证:"; cin >> id_card;
cout << "instructor_id:"; cin >> id1;
// 检查instructor_id是否合法,例如是否为非负数(通常ID不应为负)
if (id1.size()>9||id_card.size()>9||check(id1)||check(id_card)) {
cout << "注册失败,id必须为不大于9位的整数。还剩" << num - 1 << "次机会。" << endl;
add_instructor_info(num - 1);
return;
}
string password;
cout << "密码:"; cin >> password;
ID_card=transfer(id_card);
instructor_id=transfer(id1);
// 创建SQL插入语句
char query[1024];
sprintf(query, "INSERT INTO Instructor(ID, name, ID_card,salary,Is_Formal) VALUES (%d, '%s', %d,%d,1)", instructor_id, name.c_str(), ID_card,4500);
// 执行SQL查询
if (mysql_query(conn, query)) {
cout << "注册失败:" << mysql_error(conn) << "。还剩" << num - 1 << "次机会。" << endl;
add_instructor_info(num - 1);
} else {
cout << ">> 注册成功" << endl;
// 将密码存储到教师信息映射中
sprintf(query, "INSERT INTO InstructorCredentials(instructor_id, username, password) VALUES (%d, '%s', '%s')", instructor_id, name.c_str(), password.c_str());
mysql_query(conn, query);
}
}
//转正老师
void To_Formal(int num){
if(num==0) return;
if(num==3)
displayNonFormalInstructors();
cout << "请输入要转正的老师的 ID(输入0退出):" << endl;
string in_id;
cin >> in_id;
if(check(in_id)||in_id.size()>9){
cout<<"请输入正确的instructor_id"<<endl;
cout<<"还剩"<<num-1<<"次机会。"<<endl;
To_Formal(num-1);
return;
}
int id=transfer(in_id);
if (id == 0) return;
if(!find_instructor(id)){
cout << "查无此人。\n";
cout<<"还剩"<<num-1<<"次机会。\n";
To_Formal(num-1);
return;
}
char query[256];
sprintf(query, "UPDATE Instructor SET Is_Formal = 1 WHERE ID = %d", id);
if (mysql_query(conn, query)) {
cerr << "转正失败: " << mysql_error(conn) << endl;
std::this_thread::sleep_for(std::chrono::milliseconds(50));
} else {
cout << "老师已成功转正!" << endl;
}
return;
}//添加了查无此人
//修改老师薪资
void modify_salary(int num){
if(num==0) return;
if(num==3) {
cout << "老师薪资列表:" << endl;
displayInstructors_all();
}
string i_id, i_salary;
cout << "请输入要修改薪资的教师ID:(0:退出)";
cin >> i_id;
if(i_id.size()==1&&i_id[0]=='0') return;
// 检查是否输入有效的ID
if (i_id.size()>9||check(i_id)) {
cout << "输入的ID无效,请输入一个正确的instructor_ID。\n";
cout<<"还剩"<<num-1<<"次机会。"<<endl;
modify_salary(num-1);
return;
}
int id=transfer(i_id);
if(!find_instructor(id)){
cout << "查无此人。\n";
cout<<"还剩"<<num-1<<"次机会。\n";
modify_salary(num-1);
return;
}
cout << "请输入新的薪资:";
cin >> i_salary;
// 检查是否输入有效的薪资
if (i_salary.size()>9||check(i_salary)) {
cout << "请重新输入有效的薪资。\n";
cout<<"还剩"<<num-1<<"次机会。"<<endl;
modify_salary(num-1);
return;
}
int new_salary;
id=transfer(i_id);
new_salary=transfer(i_salary);
char query[256];
sprintf(query, "UPDATE Instructor SET salary = %d WHERE ID = %d", new_salary, id);
// 执行SQL更新
if (mysql_query(conn, query)) {
cerr << "更新薪资失败: " << mysql_error(conn) << endl;
std::this_thread::sleep_for(std::chrono::milliseconds(50));
} else {
cout << "薪资更新成功!" << endl;
}
return;
}//添加了查无此人
//删除课程信息
void delete_course_timing(int num){
if (num == 0) {
return;
}
if(num==3)
displayCourseTimings();
cout << "请输入要删除的课程ID(0:退出):\n";
string id;
cin >> id;
if(check(id)||id.size()>9){
cout<<"请输入正确的course_id格式"<<endl;
cout<<"还剩"<<num-1<<"次机会。"<<endl;
delete_course_timing(num-1);
return;
}
int course_id=transfer(id);
if (course_id == 0) return;
// 构建并执行删除语句
char query[256];
sprintf(query, "DELETE FROM Course_Timing WHERE Course_ID = %d", course_id);
if (mysql_query(conn, query)) {
cerr << "删除课程信息失败: " << mysql_error(conn) << endl;
std::this_thread::sleep_for(std::chrono::milliseconds(50));
cout << "还剩" << num - 1 << "次机会。\n";
delete_course_timing(num - 1); // 递归调用以重试
} else {
cout << "课程信息删除成功!\n";
}
}
//删除教师授课信息
void delete_teaches(int num) {
if (num <= 0) {
return;
}
if(num==3)
if(!displayTeaches()) return;
string instructor_id, course_id;
cout << "请输入要删除的教师ID和课程ID(输入0退出):\n";
cout << "教师ID:";
cin >> instructor_id;
if (instructor_id.size()==1&&instructor_id[0] == '0') return;
cout << "课程ID:";
cin >> course_id;
if (course_id.size()==1&&course_id[0]=='0') return;
if(check(instructor_id)||check(course_id)||instructor_id.size()>9||course_id.size()>9){
cout<<"请输入正确的ID"<<endl;
cout<<"还剩"<<num-1<<"次机会。"<<endl;
delete_teaches(num-1);
return;
}
int i_id=transfer(instructor_id);
int c_id=transfer(course_id);
// 首先删除所有相关的学生选课信息
char query[256];
sprintf(query, "DELETE FROM Takes WHERE Course_ID = %d", c_id);
if (mysql_query(conn, query)) {
cerr << "删除学生选课信息失败: " << mysql_error(conn) << endl;
delete_teaches(num - 1); // 递归调用以重试
return;
}
// 删除授课记录
sprintf(query, "DELETE FROM Teaches WHERE Instructor_ID = %d AND Course_ID = %d", i_id, c_id);
if (mysql_query(conn, query)) {
cerr << "删除授课信息失败: " << mysql_error(conn) << endl;
delete_teaches(num - 1); // 递归调用以重试
} else {
cout << "授课信息删除成功!\n";
}
}//等待测试
//分配教师授课
void assign_instructor(int num){
if(num==0) return;
if(num==3){
if(!displayFomalInstructors()) return;
displayUnTeachesCourseTimings();
}
string instructor_id, course_id;
cout << "请输入教师ID:";
cin >> instructor_id;
cout << "请输入课程ID:";
cin >> course_id;
if(instructor_id.size()>9||course_id.size()>9||check(instructor_id)||check(course_id)){
cout<<"请输入正确的id格式"<<endl;
cout<<"还剩"<<num-1<<"次机会。"<<endl;
assign_instructor(num-1);
return;
}
int i_id=transfer(instructor_id);
int c_id=transfer(course_id);
// 构建并执行插入语句
char query[256];
sprintf(query, "INSERT INTO Teaches (Instructor_ID, Course_ID) VALUES (%d, %d)", i_id, c_id);
if (mysql_query(conn, query)) {
cerr << "分配课程失败: " << mysql_error(conn) << endl;
std::this_thread::sleep_for(std::chrono::milliseconds(50));
} else {
cout << "课程成功分配给教师!" << endl;
}
}//
//查看教师信息(instructor表,授课信息)
void search_student(){
cout<<">> 请输入您想进行的查询:(0:查看学生基本信息,1:查看学生选课信息,*:退出"<<endl;
string s;cin>>s;
if(s.size()==1){
switch(s[0]){
case '0':displayStudent();break;
case '1':displayTakes();break;
case '*':return;
}
}
search_student();
return;
}
//查看学生信息(student表,学生选课信息)
void search_instructor(){
cout<<">> 请输入您想进行的查询:(0:查看教师基本信息,1:查看教师授课信息,*:退出"<<endl;
string s;cin>>s;
if(s.size()==1){
switch(s[0]){
case '0':displayInstructors_all();break;
case '1':displayTeaches();break;
case '*':return;
}
}
search_instructor();
return;
}
private:
void displayOptions(const char* query, const char* description) {
cout << description << endl;
if (mysql_query(conn, query)) {
cerr << "查询失败: " << mysql_error(conn) << endl;
return;
}
MYSQL_RES* result = mysql_store_result(conn);
if (result == NULL) {
cerr << "检索结果失败: " << mysql_error(conn) << endl;
return;
}
MYSQL_ROW row;
while ((row = mysql_fetch_row(result))) {
cout << "ID: " << row[0] << ", 详情: ";
for (int i = 1; i < mysql_num_fields(result); ++i) {
cout << row[i] << " ";
}
cout << endl;
}
mysql_free_result(result);
}
bool displayFomalInstructors(){
const char* query = "SELECT s.ID, s.name FROM Instructor AS s WHERE s.Is_Formal = 1 AND s.ID NOT IN (SELECT t.Instructor_ID FROM Teaches AS t)";
if (mysql_query(conn, query)) {
cerr << "查询失败: " << mysql_error(conn) << endl;
std::this_thread::sleep_for(std::chrono::milliseconds(50));
return false;
}
MYSQL_RES* result = mysql_store_result(conn);
if (!result) {
cerr << "获取结果集失败: " << mysql_error(conn) << endl;
std::this_thread::sleep_for(std::chrono::milliseconds(50));
return false;
}
if (result == NULL || mysql_num_rows(result) == 0) {
mysql_free_result(result);
cout<<"没有可以授课的老师..."<<endl;
return false;
}
MYSQL_ROW row;
cout << "目前还可授课的老师列表:" << endl;
while ((row = mysql_fetch_row(result))) {
cout << "ID: " << row[0] << ", 姓名: " << row[1] << endl;
}
mysql_free_result(result);
return true;
}
void displayNonFormalInstructors() {
cout << "未转正的老师列表:" << endl;
const char* query = "SELECT ID, name FROM Instructor WHERE Is_Formal = 0";
if (mysql_query(conn, query)) {
cerr << "查询失败: " << mysql_error(conn) << endl;
std::this_thread::sleep_for(std::chrono::milliseconds(50));
return;
}
MYSQL_RES* result = mysql_store_result(conn);
if (!result) {
cerr << "获取结果集失败: " << mysql_error(conn) << endl;
std::this_thread::sleep_for(std::chrono::milliseconds(50));
return;
}
MYSQL_ROW row;
while ((row = mysql_fetch_row(result))) {
cout << "ID: " << row[0] << ", 姓名: " << row[1] << endl;
}
mysql_free_result(result);
}
void displayInstructors(){
cout << "老师列表:" << endl;
const char* query = "SELECT ID, name,Is_Formal FROM Instructor";
if (mysql_query(conn, query)) {
cerr << "查询失败: " << mysql_error(conn) << endl;
std::this_thread::sleep_for(std::chrono::milliseconds(50));
return;
}
MYSQL_RES* result = mysql_store_result(conn);
if (!result) {
cerr << "获取结果集失败: " << mysql_error(conn) << endl;
std::this_thread::sleep_for(std::chrono::milliseconds(50));
return;
}
MYSQL_ROW row;
while ((row = mysql_fetch_row(result))) {
cout << "ID: " << row[0] << "\t姓名: " << row[1] << "\t\tIs_Formal:" << row[2] << endl;
}
mysql_free_result(result);
}//只带is_formal的
void displayInstructors_all(){
const char* query = "SELECT ID, name,salary,Is_Formal FROM Instructor";
if (mysql_query(conn, query)) {
cerr << "查询失败: " << mysql_error(conn) << endl;
std::this_thread::sleep_for(std::chrono::milliseconds(50));
return;
}
MYSQL_RES* result = mysql_store_result(conn);
if (!result) {
cerr << "获取结果集失败: " << mysql_error(conn) << endl;
std::this_thread::sleep_for(std::chrono::milliseconds(50));
return;
}
MYSQL_ROW row;
while ((row = mysql_fetch_row(result))) {
cout << "ID: "<< row[0] << " ,姓名: "<< row[1] << " ,薪资: " << row[2]<<" ,Is_Formal: "<<row[3]<< endl;
}
mysql_free_result(result);
}//输出所有老师,函数之前的输出信息需要自定义。
bool find_instructor(int id){
char query2[256];
// 构建查询语句
sprintf(query2, "SELECT * FROM Instructor WHERE ID = %d", id);
// 执行查询
if (mysql_query(conn, query2)) {
cerr << "查询失败: " << mysql_error(conn) << endl;
std::this_thread::sleep_for(std::chrono::milliseconds(50));
return false;
}
MYSQL_RES* result = mysql_store_result(conn);
if (result == NULL || mysql_num_rows(result) == 0) {
mysql_free_result(result);
return false;
}
mysql_free_result(result); // 清理结果集
return true;
}
void displayUnTeachesCourseTimings(){
cout << "当前未被授课的课程时间信息:" << endl;
const char* query = "SELECT ct.Course_ID, c.title, ct.Time_Slot_ID, ct.Room_Number FROM Course_Timing AS ct JOIN Course AS c ON ct.Course_ID = c.ID where ct.Course_ID not in (select cs.Course_ID from teaches as cs)";
if (mysql_query(conn, query)) {
cerr << "查询失败: " << mysql_error(conn) << endl;
return;
}
MYSQL_RES* result = mysql_store_result(conn);
if (!result) {
cerr << "获取结果集失败: " << mysql_error(conn) << endl;
return;
}
MYSQL_ROW row;
while ((row = mysql_fetch_row(result))) {
cout << "课程ID: " << row[0] <<", 课程名: "<<row[1]<<", 时间槽ID: " << row[2] << ", 教室号: " << row[3] << endl;
}
mysql_free_result(result);
}
void displayCourseTimings() {
cout << "当前课程时间信息:" << endl;
const char* query = "SELECT ct.Course_ID, c.title, ct.Time_Slot_ID, ct.Room_Number FROM Course_Timing AS ct JOIN Course AS c ON ct.Course_ID = c.ID";
if (mysql_query(conn, query)) {
cerr << "查询失败: " << mysql_error(conn) << endl;
return;
}
MYSQL_RES* result = mysql_store_result(conn);
if (!result) {
cerr << "获取结果集失败: " << mysql_error(conn) << endl;
return;
}
MYSQL_ROW row;
while ((row = mysql_fetch_row(result))) {
cout << "课程ID: " << row[0] <<", 课程名: "<<row[1]<<", 时间槽ID: " << row[2] << ", 教室号: " << row[3] << endl;
}
mysql_free_result(result);
}
bool displayTeaches() {
const char* query = "SELECT t.Instructor_ID, t.Course_ID, i.name, c.title FROM Teaches t "
"JOIN Instructor i ON t.Instructor_ID = i.ID "
"JOIN Course c ON t.Course_ID = c.ID";
if (mysql_query(conn, query)) {
cerr << "查询失败: " << mysql_error(conn) << endl;
std::this_thread::sleep_for(std::chrono::milliseconds(50));
return false;
}
MYSQL_RES* result = mysql_store_result(conn);
if (!result) {
cerr << "获取结果集失败: " << mysql_error(conn) << endl;
std::this_thread::sleep_for(std::chrono::milliseconds(50));
return false;
}
if (result == NULL || mysql_num_rows(result) == 0) {
mysql_free_result(result);
cout<<"没有授课信息..."<<endl;
return false;
}
MYSQL_ROW row;
cout << "当前授课信息:" << endl;
while ((row = mysql_fetch_row(result))) {
cout << "教师ID: " << row[0] << ", 课程ID: " << row[1] << ", 教师名: " << row[2] << ", 课程名: " << row[3] << endl;
}
mysql_free_result(result);
return true;
}
void displayClassRoom() {
// SQL查询获取所有教室信息
const char* query = "SELECT Room_Number, Capacity FROM Classroom";
// 执行查询
if (mysql_query(conn, query)) {
cerr << "查询失败: " << mysql_error(conn) << endl;
std::this_thread::sleep_for(std::chrono::milliseconds(50));
return;
}
// 存储查询结果
MYSQL_RES* result = mysql_store_result(conn);
if (result == NULL) {
cerr << "获取结果集失败: " << mysql_error(conn) << endl;
std::this_thread::sleep_for(std::chrono::milliseconds(50));
return;
}
if (result == NULL || mysql_num_rows(result) == 0) {
mysql_free_result(result);
cout<<"没有教室信息..."<<endl;
return;
}
// 检查结果集中是否有数据
if (mysql_num_rows(result) == 0) {
cout << "没有教室信息。" << endl;
} else {
cout << "教室信息:" << endl;
MYSQL_ROW row;
while ((row = mysql_fetch_row(result))) {
cout << "会议号: " << row[0] << ", 容量: " << row[1] << endl;
}
}
// 释放结果集
mysql_free_result(result);
}
void displayTime_slot() {
// SQL查询获取所有时间槽信息
const char* query = "SELECT Time_Slot_ID, Day, Start_Time, End_Time FROM Time_Slot";
// 执行查询
if (mysql_query(conn, query)) {
cerr << "查询失败: " << mysql_error(conn) << endl;
std::this_thread::sleep_for(std::chrono::milliseconds(50));
return;
}
// 存储查询结果
MYSQL_RES* result = mysql_store_result(conn);
if (result == NULL) {
cerr << "获取结果集失败: " << mysql_error(conn) << endl;
std::this_thread::sleep_for(std::chrono::milliseconds(50));
return;
}
// 检查结果集中是否有数据
if (mysql_num_rows(result) == 0) {
cout << "没有时间槽信息。" << endl;
} else {
cout << "时间槽信息:" << endl;
MYSQL_ROW row;
while ((row = mysql_fetch_row(result))) {
cout << "时间槽ID: " << row[0]<< "\t星期: " << row[1]<< "\t开始时间: " << row[2]<< "\t结束时间: " << row[3] << endl;
}
}
// 释放结果集
mysql_free_result(result);
}
void displayStudent() {
// SQL查询获取所有学生信息
const char* query = "SELECT ID, name, total_gain_credit, School, student_ID FROM Student";
// 执行查询
if (mysql_query(conn, query)) {
cerr << "查询失败: " << mysql_error(conn) << endl;
std::this_thread::sleep_for(std::chrono::milliseconds(50));
return;
}
// 存储查询结果
MYSQL_RES* result = mysql_store_result(conn);
if (result == NULL) {
cerr << "获取结果集失败: " << mysql_error(conn) << endl;
std::this_thread::sleep_for(std::chrono::milliseconds(50));
return;
}
// 检查结果集中是否有数据
if (mysql_num_rows(result) == 0) {
cout << "没有学生信息。" << endl;
} else {
cout << "学生信息:" << endl;
MYSQL_ROW row;
while ((row = mysql_fetch_row(result))) {
cout << "ID: " << row[0] << "\t姓名: " << row[1] << "\t总学分: " << row[2]
<< "\t学校: " << row[3] << "\t学校学号: " << row[4] << endl;
}
}
// 释放结果集
mysql_free_result(result);
}
void displayTakes() {
// SQL查询获取所有学生选课信息
const char* query = "SELECT Student_ID, Course_ID, Grade FROM Takes";
// 执行查询
if (mysql_query(conn, query)) {
cerr << "查询失败: " << mysql_error(conn) << endl;
std::this_thread::sleep_for(std::chrono::milliseconds(50));
return;
}
// 存储查询结果
MYSQL_RES* result = mysql_store_result(conn);
if (result == NULL) {
cerr << "获取结果集失败: " << mysql_error(conn) << endl;
std::this_thread::sleep_for(std::chrono::milliseconds(50));
return;
}
// 检查结果集中是否有数据
if (mysql_num_rows(result) == 0) {
cout << "没有学生选课信息。" << endl;
} else {
cout << "学生选课信息:" << endl;
MYSQL_ROW row;
while ((row = mysql_fetch_row(result))) {
const char* grade = (row[2] == NULL) ? "NULL" : row[2];
cout << "学生ID: "<<row[0] <<"\t课程ID:"<< row[1] << "\t成绩:" << grade << endl;
}
}
// 释放结果集
mysql_free_result(result);
}
};
class Student{//学生控制面板
public:
Student() {init();}
private:
void init(){
cout<<">>> 进入学生登陆页面"<<endl;
cout<<">>> 0:注册号码,1:登录号码,2:退出 <<<"<<endl;
string s;
cin>>s;
if(s.size()==1){
switch(s[0]){
case '0':Register(3);break;
case '1':Login(3);break;
case '2':return;
}
}
return;
}
void Register(int num) {
if (num == 0) return;
if(num!=3){
cout<<"是否退出?(0:退出)"<<endl;
string s;
cin>>s;
if(s.size()==1&&s[0]=='0') return;
}
cout << ">> 注册学生账号 <<" << endl;
cout << "请依次输入姓名、学校、学校学号和student_id以及密码,以注册账号" << endl;
string name, school, id_card, id1, password;
cout << "姓名:"; cin >> name;
cout << "学校:"; cin >> school;
cout << "学校学号(学生ID):"; cin >> id_card;
cout << "student_id:"; cin >> id1;
// 检查student_id是否合法
if (id1.size() > 9 || id_card.size() > 9 || check(id1) || check(id_card)) {
cout << "注册失败,id必须为不大于9位的整数。还剩" << num - 1 << "次机会。" << endl;
Register(num - 1);
return;
}
cout << "密码:"; cin >> password;
int school_id = transfer(id_card);//在学校里的id号
int student_id = transfer(id1);//在系统中的id号
// 创建SQL插入语句,插入到Student表
char query[1024];
sprintf(query, "INSERT INTO Student(ID,name, School, student_ID) VALUES (%d,'%s', '%s', %d)", student_id,name.c_str(), school.c_str(), school_id);
// 执行SQL查询
if (mysql_query(conn, query)) {
cout << "注册失败:" << mysql_error(conn) << "。还剩" << num - 1 << "次机会。" << endl;
Register(num - 1);
return;
}
cout << ">> 注册成功" << endl;
// 插入登录凭证到StudentCredentials表
sprintf(query, "INSERT INTO StudentCredentials(student_id, username, password) VALUES (%d, '%s', '%s')", student_id, name.c_str(), password.c_str());
if (mysql_query(conn, query)) {
cout << "账号密码存储失败:" << mysql_error(conn) << endl;
} else {
}
}
void Login(int num){
if(num==0) return;
string id;
string password;
cout << ">> 登陆学生号码 <<" << endl;
cout << "请依次输入student_id和密码" << endl;
cout << "student_id:"; cin >> id;
cout << "密码:"; cin >> password;
if(check(id)){
cout << "账号输入格式错误,请重试" << endl;
cout << "还剩" << num - 1 << "次机会。" << endl;
Login(num - 1);
return;
}
int student_id = transfer(id);
// 创建SQL查询语句,调整为学生凭证表
char query[1024];
sprintf(query, "SELECT password FROM StudentCredentials WHERE student_id = %d", student_id);
if (mysql_query(conn, query)) {
cout << "查询失败:" << mysql_error(conn) << endl;
cout << "还剩" << num - 1 << "次机会。" << endl;
Login(num - 1);
return;
}
MYSQL_RES *result = mysql_store_result(conn);
if (result == NULL) {
cout << "查询失败:" << mysql_error(conn) << endl;
mysql_free_result(result); // 修正:确保在此处释放result
cout << "还剩" << num - 1 << "次机会。" << endl;
Login(num - 1);
return;
}
MYSQL_ROW row = mysql_fetch_row(result);
if (row == NULL || strcmp(row[0], password.c_str()) != 0) {
cout << "账号或密码错误,请重试" << endl;
cout << "还剩" << num - 1 << "次机会。" << endl;
mysql_free_result(result);
Login(num - 1);
return;
}
mysql_free_result(result);
cout << ">> 登陆成功!" << endl;
cout << "学生" << student_id << "您好!" << endl;
// 假设有study函数处理学生登录后的学习会话
study(student_id);
return;
}
void study(int id){ /*在数据库中找出教师id的信息*/
cout<<">> 请输入你想进行的操作:(0:选课,1:查看成绩,2:查看已选课程,3:退课,4:退出) <<"<<endl;
string s;cin>>s;
if(s.size()==1){
switch(s[0]){
case '0':select_course(id);break;
case '1':my_grade(id);break;
case '2':my_course(id);break;
case '3':delete_course(id);break;
case '4':return;
}
}
study(id);
return;
}
//↓↓↓↓退课功能delete_course↓↓↓↓
void delete_course(int student_id) {
// 创建SQL查询语句,获取学生已选的课程
char query[1024];
sprintf(query, "SELECT c.ID, c.title FROM Course c JOIN Takes t ON c.ID = t.Course_ID WHERE t.Student_ID = %d", student_id);
// 执行SQL查询
if (mysql_query(conn, query)) {
cerr << "查询失败:" << mysql_error(conn) << endl;
return;
}
MYSQL_RES *result = mysql_store_result(conn);
if (result == NULL) {
cerr << "查询结果错误:" << mysql_error(conn) << endl;
return;
}
MYSQL_ROW row;
bool hasCourses = false;
unordered_set<string> st;
while ((row = mysql_fetch_row(result))) {
if(!hasCourses){
cout << "当前选课情况:" << endl;
}
hasCourses = true;
cout << "课程ID: " << row[0] << ", 课程名: " << row[1] << endl;
st.insert(row[0]);
}
if (!hasCourses) {
cout << "没有选课记录,无法退课。" << endl;
} else {
cout << "输入想退的课程ID(0:退出):" << endl;
string course_id;
while(cin >> course_id) {
if (course_id.size() == 1 && course_id == "0") return;
if (st.count(course_id) == 0 || check(course_id)) {
cout << "课程格式错误请重新输入。" << endl << endl;
cout << "输入想退的课程ID(0:退出):" << endl;
}else break;
}
int id=transfer(course_id);
// 创建SQL删除语句
sprintf(query, "DELETE FROM Takes WHERE Student_ID = %d AND Course_ID = %d", student_id, id);
// 执行SQL删除
if (mysql_query(conn, query)) {
cerr << "退课失败:" << mysql_error(conn) << endl;
} else {
cout << "退课成功!" << endl;
}
}
mysql_free_result(result);
}
void select_course(int student_id) {
/*学生选课:能够在已经有老师教授的课程里面选课,并且已选过的课不能再选,并且选过的课时间也不能和已选的课发生冲突。
该功能查询的结果是能选择的课程,包含的信息是course_id,title,学分,授课老师,授课时间,授课地点,不包含以上筛掉的课程。*/
// 创建SQL查询语句
char query[2048];
sprintf(query,
"SELECT DISTINCT c.ID, c.title, c.credit, i.name, ts.Day, ts.Start_Time, ts.End_Time, ct.Room_Number "
"FROM Course c "
"JOIN Teaches t ON c.ID = t.Course_ID "
"JOIN Instructor i ON t.Instructor_ID = i.ID "
"JOIN Course_Timing ct ON c.ID = ct.Course_ID "
"JOIN Time_Slot ts ON ct.Time_Slot_ID = ts.Time_Slot_ID "
"LEFT JOIN Takes tk ON tk.Course_ID = c.ID AND tk.Student_ID = %d "
"WHERE tk.Course_ID IS NULL AND NOT EXISTS ("
" SELECT 1 FROM Takes tk2 "
" JOIN Course_Timing ct2 ON tk2.Course_ID = ct2.Course_ID "
" JOIN Time_Slot ts2 ON ct2.Time_Slot_ID = ts2.Time_Slot_ID "
" WHERE tk2.Student_ID = %d AND ts2.Day = ts.Day AND "
" (ts2.Start_Time < ts.End_Time AND ts2.End_Time > ts.Start_Time)"
")", student_id, student_id);
// 执行SQL查询
if (mysql_query(conn, query)) {
cerr << "查询失败:" << mysql_error(conn) << endl;
return;
}
MYSQL_RES *result = mysql_store_result(conn);
if (result == NULL) {
cerr << "查询结果错误:" << mysql_error(conn) << endl;
return;
}
unordered_set<string> st;
MYSQL_ROW row;
bool hasCourses = false;
while ((row = mysql_fetch_row(result))) {
if(!hasCourses){
cout << "可选课程:" << endl;
}
hasCourses = true;
cout << "课程ID: " << row[0] << ", 标题: " << row[1] << ", 学分: " << row[2]
<< ", 授课老师: " << row[3] << ", 时间: " << row[4] << " " << row[5] << "-" << row[6]
<< ", 教室号: " << row[7] << endl;
st.insert(row[0]);
}
if (!hasCourses) {
cout << "没有可选课程。" << endl;
}else {
cout<<"是否需要选课?:(0表示选择)";
string s;
cin >> s;
if (s.size() == 1 && s[0] == '0') choose(student_id, st);
mysql_free_result(result);
}
return;
}
void choose(int student_id, unordered_set<string>& st) {
/*一门课程全程由一个老师教授且在同一时间教授,因此takes只需要插入course_id即可*/
cout << "请输入你想选择的课程号(0:退出):" << endl;
string s;
cin >> s;
if (s.size() == 1 && s[0] == '0') return;
if (s.size() > 9 || check(s) || st.count(s) == 0) {
cout << "请输入正确的课程ID" << endl;
choose(student_id, st);
return;
}
int course_id = transfer(s);
// 检查该学生是否已经选择了这门课程
char query[256];
sprintf(query, "SELECT * FROM Takes WHERE Student_ID = %d AND Course_ID = %d", student_id, course_id);
if (mysql_query(conn, query) == 0) {
MYSQL_RES* result = mysql_store_result(conn);
if (result != nullptr && mysql_fetch_row(result) != nullptr) {
cout << "你已经选了这门课程,请选择其他课程。" << endl;
mysql_free_result(result);
choose(student_id, st);
return;
}
mysql_free_result(result);
}//这个在select_course里面已经实现了,这里是一个冗余的判断。(但以防万一还是不删了)
// 插入新的课程选择
sprintf(query, "INSERT INTO Takes (Student_ID, Course_ID) VALUES (%d, %d)", student_id, course_id);
if (mysql_query(conn, query)) {
cout << "课程选择失败:" << mysql_error(conn) << endl;
} else {
cout << "课程选择成功!" << endl;
}
//继续
//select_course(student_id);
return;
}
void my_grade(int student_id) {
// 创建SQL查询语句,联接 `Takes` 和 `Course` 表,仅选出非空成绩
char query[1024];
sprintf(query, "SELECT Course.ID, Course.title, Takes.Grade "
"FROM Takes "
"JOIN Course ON Takes.Course_ID = Course.ID "
"WHERE Takes.Student_ID = %d AND Takes.Grade IS NOT NULL", student_id);
// 执行SQL查询
if (mysql_query(conn, query)) {
cerr << "查询失败:" << mysql_error(conn) << endl;
return;
}
MYSQL_RES *result = mysql_store_result(conn);
if (result == NULL) {
cerr << "查询结果错误:" << mysql_error(conn) << endl;
return;
}
MYSQL_ROW row;
bool hasGrades = false;
while ((row = mysql_fetch_row(result))) {
if(!hasGrades){
cout<<"成绩查询结果:"<<endl;
}
const char * grade=(row[2]==NULL?"NULL":row[2]);
hasGrades = true;
cout << "课程ID: " << row[0] << ", 课程名: " << row[1] << ", 成绩: " << grade << endl;
}
if (!hasGrades) {
cout << "没有成绩记录或所有成绩均为空。" << endl;
}
mysql_free_result(result);
cout<<"查询结束..."<<endl;
}
//查找课程
void my_course(int student_id) {
// 创建SQL查询语句
char query[1024];
sprintf(query, "SELECT Course.ID, Course.title, Course.credit "
"FROM Takes "
"JOIN Course ON Takes.Course_ID = Course.ID "
"WHERE Takes.Student_ID = %d", student_id);
// 执行SQL查询
if (mysql_query(conn, query)) {
cerr << "查询失败:" << mysql_error(conn) << endl;
return;
}
MYSQL_RES *result = mysql_store_result(conn);
if (result == NULL) {
cerr << "查询结果错误:" << mysql_error(conn) << endl;
return;
}
MYSQL_ROW row;
bool hasCourses = false;
while ((row = mysql_fetch_row(result))) {
if(!hasCourses){
cout<<"所选课程查询结果"<<endl;
}
hasCourses = true;
cout << "课程ID: " << row[0] << ", 课程名: " << row[1] << ", 学分: " << row[2] << endl;
}
if (!hasCourses) {
cout << "您还未选任何课程。" << endl;
}
mysql_free_result(result);
}
};
class Instructor{//教师操作控制面板
public:
Instructor(){init();}
private:
void init(){
cout<<">>> 进入教师登陆页面"<<endl;
cout<<">>> 0:注册号码,1:登录号码,2:退出 <<<"<<endl;
string s;
cin>>s;
if(s.size()==1){
switch(s[0]){
case '0':Register(3);break;
case '1':Login(3);break;
case '2':return;
}
}
return;
}
//注册
void Register(int num) {
if (num == 0) return;
if(num!=3){
cout<<"是否退出?(0:退出)"<<endl;
string s;
cin>>s;
if(s.size()==1&&s[0]=='0') return;
}
cout << ">> 注册教师账号 <<" << endl;
cout << "请依次输入姓名、身份证和instructor_id 以及密码,以注册账号" << endl;
string name;
int ID_card, instructor_id;
string id1;
string id_card;
cout << "姓名:"; cin >> name;
cout << "身份证:"; cin >> id_card;
cout << "instructor_id:"; cin >> id1;
// 检查instructor_id是否合法,例如是否为非负数(通常ID不应为负)
if (id1.size()>9||id_card.size()>9||check(id1)||check(id_card)) {
cout << "注册失败,id必须为不大于9位的整数。还剩" << num - 1 << "次机会。" << endl;
Register(num - 1);
return;
}
string password;
cout << "密码:"; cin >> password;
ID_card=transfer(id_card);
instructor_id=transfer(id1);
// 创建SQL插入语句
char query[1024];
sprintf(query, "INSERT INTO Instructor(ID, name, ID_card) VALUES (%d, '%s', %d)", instructor_id, name.c_str(), ID_card);
// 执行SQL查询
if (mysql_query(conn, query)) {
cout << "注册失败:" << mysql_error(conn) << "。还剩" << num - 1 << "次机会。" << endl;
Register(num - 1);
} else {
cout << ">> 注册成功" << endl;
// 将密码存储到教师信息映射中
sprintf(query, "INSERT INTO InstructorCredentials(instructor_id, username, password) VALUES (%d, '%s', '%s')", instructor_id, name.c_str(), password.c_str());
mysql_query(conn, query);
}
}
//登陆
void Login(int num){
if(num==0) return;
string id;
string password;
cout<<">> 登陆教师号码 <<"<<endl;
cout<<"请依次输入insturctor_id和密码"<<endl;
cout<<"insturctor_id:";cin>>id;
cout<<"密码:";cin>>password;
if(id.size()>9||check(id)){
cout<<"账号输入格式错误,请重试"<<endl;
cout<<"还剩"<<num-1<<"次机会。"<<endl;
Login(num-1);
return;
}
int instructor_id=transfer(id);
// 创建SQL查询语句
char query[1024];
sprintf(query, "SELECT password FROM instructorCredentials WHERE instructor_id = %d", instructor_id);
if (mysql_query(conn, query)) {
cout << "查询失败:" << mysql_error(conn) << endl;
cout << "还剩" << num - 1 << "次机会。" << endl;
Login(num - 1);
return;
}
MYSQL_RES *result = mysql_store_result(conn);
if (result == NULL) {
cout << "查询失败:" << mysql_error(conn) << endl;
mysql_free_result(result);
cout << "还剩" << num - 1 << "次机会。" << endl;
Login(num - 1);
return;
}
MYSQL_ROW row = mysql_fetch_row(result);
if (row == NULL || strcmp(row[0], password.c_str()) != 0) {
cout << "账号或密码错误,请重试" << endl;
cout << "还剩" << num - 1 << "次机会。" << endl;
mysql_free_result(result);
Login(num - 1);
return;
}
mysql_free_result(result);
cout << ">> 登陆成功!" << endl;
cout<<"教师"<<instructor_id<<"您好!"<<endl;
instruct(instructor_id);
return;
}
//选择教师操作
void instruct(int id){
/******************************************************************************************************************************************/
/*在数据库中找出教师id的信息*/
cout<<">> 请输入你想进行的操作:(0:查询授课,1:修改成绩,2:退出) <<"<<endl;
string s;cin>>s;
if(s.size()==1){
switch(s[0]){
case '0':my_course(id);break;
case '1':grade(id);break;
case '2':return;
}
}
instruct(id);
return;
}
//输出教授课程
void my_course(int id){
// 创建SQL查询语句
char query[1024];
sprintf(query, "SELECT Course.ID, Course.title, Course.credit FROM Teaches JOIN Course ON Teaches.Course_ID = Course.ID WHERE Teaches.Instructor_ID = %d", id);
// 执行SQL查询
if (mysql_query(conn, query)) {
cerr << "查询失败:" << mysql_error(conn) << endl;
std::this_thread::sleep_for(std::chrono::milliseconds(50));
return;
}
MYSQL_RES *result = mysql_store_result(conn);
if (result == NULL) {
cerr << "查询失败:" << mysql_error(conn) << endl;
std::this_thread::sleep_for(std::chrono::milliseconds(50));
return;
}
MYSQL_ROW row;
bool hasCourses = false;
while ((row = mysql_fetch_row(result))) {
if(!hasCourses){
cout << "您所教授的课程:" << endl;
}
hasCourses = true;
cout << "课程ID: " << row[0] << ", 课程名称: " << row[1] << ", 学分: " << row[2] << endl;
}
if (!hasCourses) {
cout << "您目前没有教授任何课程!" << endl;
}
mysql_free_result(result);
cout<<"查询结束..."<<endl;
}
//修改成绩,或查看成绩
void grade(int instructor_id) {
// 第一步:找出该教师教授的所有课程
char query[1024];
sprintf(query, "SELECT Student.ID, Student.name, Course.title, Takes.Grade "
"FROM Student "
"JOIN Takes ON Student.ID = Takes.Student_ID "
"JOIN Course ON Takes.Course_ID = Course.ID "
"JOIN Teaches ON Course.ID = Teaches.Course_ID "
"WHERE Teaches.Instructor_ID = %d", instructor_id);
if (mysql_query(conn, query)) {
cerr << "查询失败:" << mysql_error(conn) << endl;
return;
}
MYSQL_RES *result = mysql_store_result(conn);
if (result == NULL) {
cerr << "查询结果错误:" << mysql_error(conn) << endl;
return;
}
MYSQL_ROW row;
int row_count = 0;
while ((row = mysql_fetch_row(result))) {
if(!row_count){
cout<<"学生信息:"<<endl;
}
const char * grade = row[3]==NULL?"NULL":row[3];
cout << "学生ID: " << row[0] << ", 姓名: " << row[1]
<< ", 课程: " << row[2] << ", 当前成绩: " << grade << endl;
row_count++;
}
if (row_count == 0) {
cout << "没有找到任何记录。" << endl;
} else {
// 修改成绩
bool flag=true;
cout<< endl << "是否需要修改成绩?(1:是,其余:否)"<<endl;
string s;cin>>s;
if(s.size()==1&&s[0]=='1') {
cout << "输入学生ID和课程ID以修改成绩(格式:学生ID 课程ID 新成绩):" << endl;
int student_id, course_id;
double new_grade;
cin >> student_id >> course_id >> new_grade;
sprintf(query, "UPDATE Takes SET Grade = %.2f WHERE Student_ID = %d AND Course_ID = %d",
new_grade, student_id, course_id);
if (mysql_query(conn, query)) {
cerr << "更新失败:" << mysql_error(conn) << endl;
} else {
cout << "成绩更新成功!" << endl;
}
}
}
mysql_free_result(result);
cout<<"操作结束..."<<endl;
}
};
void LogPage(){
std::this_thread::sleep_for(std::chrono::milliseconds(1000));
cout<<">>> 进入初始登陆选择页面"<<endl;
std::this_thread::sleep_for(std::chrono::milliseconds(1000));
cout<<">>>> 请输入你的身份(0:管理员,1:教师,2:学生,*:退出 <<<<"<<endl;
string s;
cin>>s;
if(s.size())
switch(s[0]){
case '0':Admin();break;
case '1':Instructor();break;
case '2':Student();break;
case '*':return;
default:cout<<">> 格式错误,请重新输入 <<" << endl;
}
//未成功重复进行
cout<<endl;
LogPage();//任务完成,再来一次。
return;
}
int main(void){
conn = mysql_init(NULL);
/*连接数据库部分*/
mysql_set_character_set(conn, "utf8");
const char *server = "localhost";
const char *user = "root";
const char *password = "liyao20021125";
const char *database = "design";
if (!mysql_real_connect(conn, server, user, password, database, 0, NULL, 0)) {
std::cerr << "数据库连接失败: " << mysql_error(conn) << std::endl;
return 1;
}
cout<<"------------------欢迎进入教学管理系统------------------"<<endl;
LogPage();
cout<<"-------------------期待我们下次再会--------------------";
/*关闭数据库*/
mysql_free_result(res);
mysql_close(conn);
return 0;
}
运行演示: