MySql--实战（基于qt的学生管理系统）

红太狼 2022-06-17 01:40 262阅读 0赞

前一段时间一直在看MySql，看完后就想着做点什么来运用一下，感受一下MySql强大的搜索功能。于是，这两天就在做这个学生管理系统，这次的学生管理系统和之前的职工管理系统有很大的不同，之前的职工管理系统只是增删改查，这次的学生管理系统含有多个数据表，学生学籍信息表、学生体检信息表、学生考试成绩表、课程号表和系统管理表。

做完之后，感觉最核心的就是对数据库的访问，然后再加一些界面之间切换，就成了我现在这个成品。

首先是我的访问数据库类：

class My_Sql
    {
        QSqlQuery *query;
    public:
        My_Sql();
        void initsql();                                                         //初始化连接数据库
        void createtable();                                                     //建表、索引、触发器
        bool inituser(QString name, QString identify, QString passward);        //注册用户
        bool loguser(QString name, QString passward);                           //用户登录/忘记密码
        bool addstu(my_stu* stu);                                               //添加学生信息
        bool deletestu(QString account);                                        //删除学生信息
        bool updatestu(my_stu* stu);                                            //修改学生信息
        bool addheal(my_heal* heal);                                            //添加体检信息
        bool updateheal(my_heal* heal);                                         //修改体检信息
        bool deleteheal(QString account);                                       //删除体检信息
        bool addgrade(my_grade* grad);                                          //添加成绩信息
        bool updategrade(my_grade* grad);                                       //修改成绩信息
        bool deletegrade(QString account);                                      //删除成绩信息
        bool addlicen(my_licen* lice);                                          //添加领证信息
        bool updatelicen(my_licen* lice);                                       //修改领证信息
        bool deletelicen(QString account);                                      //删除领证信息
    };

这里主要显示了对数据库和数据表的初始化，以及对三张表的访问及处理。对数据表的处理和以前的用法差不多，最大的差距就是对多表的处理，比如：将多表查询结果整合存放于视图中，学籍和另外的体检表、证书表添加插入触发器，名字更改后就会将另外两个表的数据一起改变。再比如，对这几张表中的数据添加索引，让其自动排序，这样为数据的检索提高了效率。

query = new QSqlQuery;
    /*用户表*/
    query->exec("create table user(name VARCHAR(30) UNIQUE NOT NULL, passward VARCHAR(30), identify VARCHAR(30) PRIMARY KEY)");
    /*学生信息表*/
    query->exec("create table studentInfo(stuaccount VARCHAR(30) PRIMARY KEY UNIQUE NOT NULL, stuname VARCHAR(30), stusex ENUM('男', '女') NOT NULL, stuage INT, identify VARCHAR(30) UNIQUE NOT NULL, tel VARCHAR(20), enroll_time DATE, leave_time DATE, scondition enum('学习', '结业', '退学'), stu_text TEXT)");
    /*体检信息表*/
    query->exec("create table healthInfo(stuaccount VARCHAR(30) PRIMARY KEY UNIQUE NOT NULL, stuname VARCHAR(30) NOT NULL, height FLOAT, weight FLOAT, differentiate ENUM('正常', '色弱', '色盲'), left_sight FLOAT, right_sight FLOAT, left_ear ENUM('正常','偏弱'), right_ear ENUM('正常','偏弱'), legs ENUM('正常','不相等'), pressure ENUM('正常','偏高','偏低'), history VARCHAR(50), h_text TEXT)");
    /*考试课程表*/
    query->exec("create table courseInfo(classid INT PRIMARY KEY UNIQUE NOT NULL AUTO_INCREMENT, classname VARCHAR(30), beforeclassid INT)");
    /*考试成绩表*/
    query->exec("create table gradeInfo(stuaccount VARCHAR(30) PRIMARY KEY UNIQUE NOT NULL, classid INT NOT NULL, lasttime date, times INT default 1, grade float default 0, constraint grade_acc_fk FOREIGN KEY(stuaccount) REFERENCES studentInfo(stuaccount), constraint grade_cid_fk FOREIGN KEY(classid) REFERENCES courseInfo(classid))");
    /*领证时间表*/
    query->exec("create table licenseInfo(stuaccount VARCHAR(30) PRIMARY KEY UNIQUE NOT NULL, stuname VARCHAR(30), recv_time date, recv_name VARCHAR(30), l_text TEXT);");
    
    /*创建root用户*/
    query->exec("insert into user value('root', 'root', 'root')");
    
    /*学生表姓名索引*/
    query->exec("create index int_name ON studentInfo(stuname)");
    /*学生表学习状态索引*/
    query->exec("alter table studentInfo ADD INDEX ind_con(scondition)");
    /*体检表姓名索引*/
    query->exec("create index ind_hname ON healthInfo(stuname)");
    /*领证表姓名索引*/
    query->exec("alter table licenseInfo ADD INDEX ind_lname(stuname)");
    /*领证表领证人索引*/
    query->exec("alter table licenseInfo ADD INDEX ind_recvname(recv_name)");
    
    /*设计视图*/
    query->exec("create VIEW grade_view AS SELECT g.stuaccount, s.stuname, c.classname, g.lasttime, g.times, g.grade FROM studentinfo s, courseinfo c, gradeinfo g WHERE g.stuaccount = s.stuaccount AND g.classid = c.classid");
    
    /*设计insert触发器*/
    query->exec("CREATE TRIGGER license_stu AFTER INSERT ON licenseInfo  FOR EACH ROW BEGIN UPDATE studentInfo SET leave_time=NEW.recv_time, scondition = '结业' WHERE stuaccount = NEW.stuaccount; END");
    
    /*设计update触发器*/
    query->exec("CREATE TRIGGER update_name AFTER UPDATE ON studentinfo FOR EACH ROW BEGIN UPDATE healthinfo SET stuname = NEW.stuname WHERE stuaccount = NEW.stuaccount;END");
    
    /*设计delete触发器*/
    query->exec("CREATE TRIGGER delete_stu AFTER DELETE ON studentinfo FOR EACH ROW BEGIN DELETE FROM gradeinfo WHERE stuaccount = OLD.stuaccount; DELETE FROM healthinfo WHERE stuaccount = OLD.stuaccount; DELETE FROM licenseinfo WHERE stuaccount = OLD.stuaccount;END");

这个项目的源码文件有点多，我将源码上传，有兴趣的朋友可以查看：[源码分享][Link 1]

这里我上传几张截图，显示一下效果：

![SouthEast][]![SouthEast 1][]

![SouthEast 2][]![SouthEast 3][]

![SouthEast 4][]

![SouthEast 5][]![SouthEast 6][]

![SouthEast 7][]

![SouthEast 8][]![SouthEast 9][]

![SouthEast 10][]![SouthEast 11][]

![SouthEast 12][]![SouthEast 13][]![SouthEast 14][]![SouthEast 15][]![SouthEast 16][]

[Link 1]: http://download.csdn.net/detail/hjf161105/9812202
[SouthEast]: https://img-blog.csdn.net/20170412212124156?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvaGpmMTYxMTA1/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/SouthEast
[SouthEast 1]: /images/20220617/de1960c51d944f47b54847e22482e903.png
[SouthEast 2]: https://img-blog.csdn.net/20170412212201443?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvaGpmMTYxMTA1/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/SouthEast
[SouthEast 3]: /images/20220617/fa2facfb936240d49bb3747d2e329e4b.png
[SouthEast 4]: /images/20220617/5318ef3bcc02465ba9b0953c736e1d0b.png
[SouthEast 5]: https://img-blog.csdn.net/20170412212307937?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvaGpmMTYxMTA1/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/SouthEast
[SouthEast 6]: /images/20220617/1d98b3dea0c64712a76ba4428585881a.png
[SouthEast 7]: /images/20220617/c03db8e5c44f4586a8ee6cdcca0e70cd.png
[SouthEast 8]: https://img-blog.csdn.net/20170412212402219?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvaGpmMTYxMTA1/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/SouthEast
[SouthEast 9]: /images/20220617/c9024982e346496892467061f826b312.png
[SouthEast 10]: https://img-blog.csdn.net/20170412212441907?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvaGpmMTYxMTA1/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/SouthEast
[SouthEast 11]: /images/20220617/d332324ee3a74aa2a433524c17a76b27.png
[SouthEast 12]: https://img-blog.csdn.net/20170412212505032?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvaGpmMTYxMTA1/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/SouthEast
[SouthEast 13]: https://img-blog.csdn.net/20170412212516985?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvaGpmMTYxMTA1/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/SouthEast
[SouthEast 14]: https://img-blog.csdn.net/20170412212531292?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvaGpmMTYxMTA1/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/SouthEast
[SouthEast 15]: https://img-blog.csdn.net/20170412212544464?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvaGpmMTYxMTA1/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/SouthEast
[SouthEast 16]: /images/20220617/ead7fb71847f4a5b8a77d12a1520f5e0.png