----------------------------------------------------------------------------------------
源码下载地址:
http://download.csdn.net/download/qq78442761/9971770
----------------------------------------------------------------------------------------
用SQLyog查看如下图所示:
数据库涉及3个表的构建下面是相关代码。
ER图如下所示:
下面是上课和下课打卡的流程图:
左图为上课,右图为下课
具体代码如下:
DROP TABLE IF EXISTS `subject`;
CREATE TABLE `subject` (
`id` int(11) NOT NULL,
`name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
`time_from` time NOT NULL,
`time_to` time NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE `student` (
`CardNum` int(11) NOT NULL,
`id` int(11) NOT NULL,
`class` int(11) NOT NULL,
`name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE `chose` (
`subject_id` int(11) NOT NULL,
`student_id` int(11) NOT NULL,
`in_num` int(11) NOT NULL,
`out_num` int(11) NOT NULL,
KEY `Stu_id` (`student_id`),
KEY `Sub_id` (`subject_id`),
CONSTRAINT `Stu_id` FOREIGN KEY (`student_id`) REFERENCES `student` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `Sub_id` FOREIGN KEY (`subject_id`) REFERENCES `subject` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
下面是一些简单的SQL语句,都插入到Qt程序里面了:
//上课打卡时:查询选课表中是否有此人
select * from student where id in (select student_id from chose
where student_id =(select id from Student where CardNum=0110 and subject_id=(select id from subject where now()>=time_from and now()<=time_to)));
如果有就:
update chose set in_num=in_num+1 where student_id=XXXXXXXX;
//下课打卡时:查询选课表中是否有此人
select * from student where id in (select student_id from chose
where student_id =(select id from Student where CardNum=0110 and subject_id=(select id from subject where now()>time_to)));
如果有就
update chose set out_num=in_num+1 where student_id=XXXXXXXX;
//根据学号查询对应学号所有课程的出勤信息
select student.id,subject.name,chose.in_num from student,chose,subject where chose.student_id=14220307 and student.id=14220307
//根据姓名查询对应学号所有课程的出勤信息
SELECT student.name,subject.name,chose.in_num FROM student,chose,SUBJECT WHERE chose.student_id = (SELECT id FROM student WHERE NAME='张三') AND student.id=(SELECT id FROM student WHERE NAME='张三');
//查询选取了此课表的所有出勤人信息,和出勤分数:
select subject.id,subject.name,student.id,student.name,chose.in_num,chose.in_num*10 as '出勤分数'from subject,student,chose where subject.id=chose.subject_id and chose.student_id=student.id;
//更具学号删除学生数据
delete from student where id=14220310;
//添加学生
insert into student values(9999,14220388,142203,'小白');
QStringAndBool ConnMysql::LinkMySQL()
{
db=QSqlDatabase::addDatabase("QMYSQL");
db.setHostName("localhost");
db.setDatabaseName(m_DataBase);
db.setUserName(m_UserName);
db.setPassword(m_PassWd);
QStringAndBool returnValues;
if(!db.open()){
QMessageBox::information(NULL,"提示","数据库连接失败!");
returnValues.MesQString="数据库连接失败!";
returnValues.MesBool=0;
return returnValues;
}
else{
QMessageBox::information(NULL,"提示","数据库连接成功!",QMessageBox::Ok);
returnValues.MesQString="数据库连接成功!";
returnValues.MesBool=1;
return returnValues;
}
}
void ConnMysql::DisLinkMysql()
{
QString SqlQuery="exit";
model->setQuery(SqlQuery);
}
注意:具体代码在本博文开头,此处只有关键代码;
上一篇博文:
大四课程设计之基于RFID技术的考勤管理系统(二)读取COM口数据
http://blog.csdn.net/qq78442761/article/details/77950585
下一篇博文: