首页 > 数据库 > 数据库其它

数据库数据库实验大作业实验报告(QT编写学生选课系统)

admin 数据库其它 2021-05-25 09:25:47
后台-系统设置-扩展变量-手机广告位-内容正文底部

本实验用到的表如下:
在这里插入图片描述

QT编写学生选课系统与老师管理系统

  • 登录与注册系统
    • 登录的判定
    • 注册与找回密码
      • 相关触发器
      • 注册系统的实现
      • 找回密码的实现
      • 图片验证码与邮箱验证码
      • 账号密码进行MD5加密
  • 老师管理系统
    • 相关触发器
    • 学生信息修改
      • 读取学生表的实现
      • 学生表的增删改查
      • 使用数据库的valbinary类型来存取图片
    • 学生成绩修改
    • 课程信息修改
  • 学生选课系统
    • 查看个人成绩
    • 查看不及格成绩
    • 查看所有成绩
    • 选/退课

登录与注册系统

登录的判定

登录界面如下:
在这里插入图片描述登录结果的判定过程:先读取输入框中的两个字符串, 将字符串与数据库中的进行比对。由于数据库中的数据读取出来末尾会有空格,所以要先去掉为空格再判断。
失败会在底下的statusbar显示5s错误信息,然后消失.
在这里插入图片描述核心代码:

bool Database::find(QString ID, QString PassWord, QString Type){
    QSqlQuery query(db);
    query.exec("Select ID, PassWord, Type From U");
    while(query.next())
    {   qDebug() << query.value(0).toString() << endl;
        if(query.value(0).toString().trimmed() == ID && query.value(1).toString().trimmed() == PassWord && query.value(2).toString() == Type) return 1;}
    return 0;
}
void MainWindow::on_pushButton_clicked()
{   Database *db = new Database();
    db -> OpenDatabase();
    QString ID = ui -> lineEdit ->text();
    QString PassWord = ui -> lineEdit_2 -> text();
    if(!ui -> radioButton -> isChecked() && !ui -> radioButton_2 -> isChecked()) {//QMessageBox::information(NULL, "请选择登录方式", "请选择登录方式");
        ui -> statusbar -> showMessage("请选择登录方式", 5000);}
    else{
    QString flag = ui -> radioButton -> isChecked()? "1" : "0";
    qDebug() << ID << ' ' << PassWord << endl;
    if(db->find(ID, PassWord, flag)){
    if(flag == "0"){
        Student * st = new Student();
        st -> id = ID;
        qDebug() << "to student: " << ID << endl;
        st -> show();
    }
    else{
        Teacher *tc = new Teacher();
        tc -> id = ID;
        tc -> show();
    }
    this -> close();
    }
    else
        {//QMessageBox::information(NULL, "账号或密码错误!", "账号或密码错误!");
        ui -> statusbar -> showMessage("账号或密码错误!", 5000);
        }
    }
}

注册与找回密码

相关触发器

student表里有下面这个触发器,一旦student表被更改或者插入,就更新U表.

USE [1]
GO
/****** Object:  Trigger [dbo].[Monitor_Dic_date_Up1]    Script Date: 2021/5/21 星期五 15:23:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER  trigger [dbo].[Monitor_Dic_date_Up1]
  on [dbo].[student]--触发器对象表
  for update ,insert--插入或更新时触发.
  as --
  begin
  if EXISTS(select 1 from u  A ,inserted B where A.id=B.Sno)--检查插入或更新的数据在DIC_DATAupdate表中是否存在,有则更新,无则添加
  update u  set [Name]=B.[Sname], [Type]=0, [password]=B.[Spassword],
  [email]=B.[Semail]   from inserted B
  where  u.[id]=B.[Sno] --为记录数据插入或更新时间,增加时间(lasttime)字段.
  else
  insert into u(id, name, PassWord, email,Type) select [Sno],[Sname],[Spassword],[Semail],0 from inserted
  end

注册系统的实现

注册系统界面:
在这里插入图片描述
主要过程:
在程序中,通过设置正则表达式,让密码只能输入字母和数字.
通过读取输入框信息,先判断账号和密码的长度是否符合条件,再判断邮箱中@和.的相对位置与个数来辨别邮箱格式是否符合条件。在所有格式正确的条件下,通过读取数据库中U表的信息来判断是否有重复学号或邮箱,若没有,就把信息存到U表中。其中U表既存教师账号又存学生账号,U表中有一个type的变量来区别二者,0是学生,1是老师,如果是学生账号,就再往student表里面插入学生信息。
关键代码如下:

Register::Register(QWidget *parent) :
    QWidget(parent),
    ui(new Ui::Register)
{
    ui->setupUi(this);
    setWindowFlags(Qt::FramelessWindowHint);
    m_pCodeArea = new CodeArea(this);
    QGridLayout *lay = new QGridLayout();
    lay ->addWidget(m_pCodeArea);
    ui -> widget -> setLayout(lay);
    QRegExp regx("[a-zA-Z0-9]+$");
    QValidator *validator = new QRegExpValidator(regx, ui -> le2 );
    ui -> le2->setValidator( validator );
    QValidator *validator1 = new QRegExpValidator(regx, ui -> le3 );
    ui -> le3->setValidator(validator1);
}
void Register::on_pushButton_clicked()
{
    Database *db = new Database();
    db -> OpenDatabase();
    if(!(ui -> le1 -> text().size()>= 1 && ui -> le1 -> text().size() <= 20)) QMessageBox::information(NULL, "请输入长度为1~20之间的学号", "请输入长度为1~10之间的学号");
    else if(!(ui -> le2 -> text().size() >= 6 && ui -> le2 -> text().size() <= 20)) QMessageBox::information(NULL, "请输入长度为6~20之间的密码", "请输入长度为6~20之间的密码");
    else if(ui -> le2 -> text() != ui -> le3 -> text()) QMessageBox::information(NULL, "两次输入的密码不一致", "两次输入的密码不一致");
    else if(!isemail(ui -> le4 -> text())) QMessageBox::information(NULL, "邮箱格式不正确", "邮箱格式不正确");
    else if(!m_pCodeArea->checkCode(ui -> le5 -> text())) QMessageBox::information(NULL, "验证码错误", "验证码错误");
    else if(!ui -> rb1 -> isChecked() && !ui -> rb2 -> isChecked()) QMessageBox::information(NULL, "请选择要注册的用户", "请选择要注册的用户");
    else if(db -> find(ui -> le1 -> text(), ui -> le4 -> text())){
         QMessageBox::information(NULL, "学号或邮箱重复", "已存在该学号或邮箱,请更换!");
    }
    else{
        QString flag;
        if(ui -> rb1 -> isChecked()) flag = "0";
        else flag = "1";
        db -> OpenDatabase();
        db -> InsertUser(ui -> le1 -> text(), ui -> le5_2 -> text(), ui -> le2 -> text(), flag, ui -> le4 -> text());
        if(flag == "0")
            {db -> InsertStu(ui -> le1 -> text(), ui -> le5_2 -> text(), ui -> le2 -> text(), ui -> le4 -> text());
            db -> InsertAll(ui -> le1 -> text(), ui -> le5_2 -> text(), ui -> le2 -> text(), ui -> le4 -> text());
            db -> InsertMD5(QCryptographicHash::hash((ui -> le1 -> text() + ui -> le2 -> text()).toLatin1(),QCryptographicHash::Md5).toHex());
        }
        QMessageBox::information(NULL, "注册成功", "注册成功");
    }
}

找回密码的实现

界面如图:在这里插入图片描述

找回密码只通过邮箱找回,判断方式很简单,从U表中查找是否有该邮箱,若有,就向该邮箱发送验证码,最后判断信息即可。
核心代码:

void Find_Password::on_pushButton_3_clicked()
{
    Register r;
    char c[] = {'0', '1', '2', '3', '4', '5', '6', '7', '8', '9'};
    std::random_shuffle(c, c + 10);
    s.clear();
    for(int i = 0;i < 4; i++) s += c[i];
    if(!r.isemail(ui -> lineEdit -> text())) QMessageBox::information(NULL, "邮箱格式不正确", "邮箱格式不正确");
    else{
    Database db = Database();
       db.OpenDatabase();
       if(!db.find("", ui ->lineEdit -> text()))   QMessageBox::information(NULL, "查无此邮箱", "查无此邮箱");
       else{
    QByteArray qb = "";
    for(auto c : ui -> lineEdit -> text()) qb.append(c);
    Send_Email se("liuzhexuanlove@163.com", "NPXARVXNZXGIMPVE");
    se.send(qb, "程序验证码:", s);}
    ui -> pushButton_3 -> setEnabled(false);
    timer->start(1000);
    ui -> pushButton_3 -> setText("发送验证码");
    ui -> pushButton_3 -> setEnabled(true);
    }
}

图片验证码与邮箱验证码

图片验证码:通过初始化随机种子,然后随机颜色和字符即可。重写paintEvent画一张图片,然后将其载入到一个QWidget中,在QWidget下面藏一个和图片大小一样的按钮,这样的话,在点击图片的时候就可以刷新验证码了。效果如下:在这里插入图片描述
核心代码:

void CodeArea::paintEvent(QPaintEvent *event)
{
    QPainter painter(this);
    painter.setRenderHint(QPainter::Antialiasing);
    painter.fillRect(event->rect(), QBrush(Qt::white));

    painter.translate(0, 0);
    painter.save();
    painter.translate(event->rect().center().x() - m_lCodePic.size() / 2 * 6, event->rect().center().y());
    for (int i = 0; i < m_lCodePic.size(); i++)
    {
        drawConversion(painter);
        drawCode(painter, i);
        painter.translate(10, 0);
    }
    painter.restore();
    drawOutline(painter);
    drawNoisyPoint(painter);
}
void CodeArea::drawCode(QPainter &painter, int nCodeIndex)
{
    painter.fillPath(*m_lCodePic[nCodeIndex], QBrush(m_lCodeColor[qrand() % 7]));
}
void CodeArea::drawConversion(QPainter &painter)
{
    if (qrand() % 2)
        painter.rotate(qrand() % m_nConverseRotate);
    else
        painter.rotate(-(qrand() % m_nConverseRotate));

    painter.scale((qrand() % m_nConverseScale + (100 - m_nConverseScale)) / 100.0 ,
        (qrand() % m_nConverseScale + (100 - m_nConverseScale)) / 100.0);
}

邮箱验证码:某位热心同学给我的代码.发送验证码后有60s的倒计时,这是通过Qtimer实现的.

#include "send_email.h"
#include<qDebug>
Send_Email::Send_Email(QByteArray username,QByteArray password)
{
    if(username.contains("@163"))
    {
        this->username = username;
        this->password = password;
    }
    else
        qDebug()<<"NOT 163";
}
void Send_Email::send(QByteArray recvaddr,QString subject,QString content)
{
    this->recvaddr = recvaddr;
    this->subject = subject;
    this->content = content;
    QByteArray usernametmp = this->username;
    QByteArray recvaddrtmp = this->recvaddr;

    clientsocket=new QTcpSocket();
    this->clientsocket->connectToHost("smtp.163.com",25,QTcpSocket::ReadWrite);
    this->clientsocket->waitForConnected(1000);
    this->clientsocket->waitForReadyRead(1000);
    recvdata = clientsocket->readAll();
    qDebug()<<recvdata;


    this->clientsocket->write("HELO smtp.163.com\r\n");
    this->clientsocket->waitForReadyRead(1000);
    recvdata = clientsocket->readAll();
    qDebug()<<recvdata;

    this->clientsocket->write("AUTH LOGIN\r\n");
    this->clientsocket->waitForReadyRead(1000);
    recvdata = clientsocket->readAll();
    qDebug()<<recvdata;

    this->clientsocket->write(username.toBase64().append("\r\n"));
    this->clientsocket->waitForReadyRead(1000);
    recvdata = clientsocket->readAll();
    qDebug()<<recvdata;

    this->clientsocket->write(password.toBase64().append("\r\n"));
    this->clientsocket->waitForReadyRead(1000);
    recvdata = clientsocket->readAll();
    qDebug()<<recvdata;

    this->clientsocket->write(mailfrom.append(usernametmp.append(">\r\n")));
    this->clientsocket->waitForReadyRead(1000);
    recvdata = clientsocket->readAll();
    qDebug()<<recvdata;

    this->clientsocket->write(rcptto.append(recvaddrtmp.append(">\r\n")));
    this->clientsocket->waitForReadyRead(1000);
    recvdata = clientsocket->readAll();
    qDebug()<<recvdata;

    this->clientsocket->write("data\r\n");
    this->clientsocket->waitForReadyRead(1000);
    recvdata = clientsocket->readAll();
    qDebug()<<recvdata;


    usernametmp = this->username;
    recvaddrtmp = this->recvaddr;

    this->clientsocket->write(prefrom.append(usernametmp.append("\r\n")));
    this->clientsocket->write(preto.append(recvaddrtmp.append("\r\n")));
    this->clientsocket->write(presubject.append(subject.toLocal8Bit().append("\r\n")));
    this->clientsocket->write("\r\n");
    this->clientsocket->write(content.toLocal8Bit().append("\r\n"));
    this->clientsocket->write(".\r\n");
    this->clientsocket->waitForReadyRead(1000);
    recvdata = clientsocket->readAll();
    qDebug()<<recvdata;

    this->clientsocket->write("quit\r\n");
    this->clientsocket->waitForReadyRead(1000);
    recvdata = clientsocket->readAll();
    qDebug()<<recvdata;
}

Send_Email::~Send_Email()
{
    delete this->clientsocket;
}

成功收到在这里插入图片描述

账号密码进行MD5加密

QT中有一个QCryptographicHash类可以进行MD5加密,注册后,会将账号和密码的拼接字符串加密成MD5格式存到Table_MD5表中,效果如下:
在这里插入图片描述
核心代码:

db -> InsertMD5(QCryptographicHash::hash((ui -> le1 -> text() + ui -> le2 -> text()).toLatin1(),QCryptographicHash::Md5).toHex());

老师管理系统

相关触发器

老师因为可以更改学生信息,所以学生表更改后要同步更新到表中。

USE [1]
GO
/****** Object:  Trigger [dbo].[Monitor_Dic_date_Up1]    Script Date: 2021/5/21 星期五 15:36:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER  trigger [dbo].[Monitor_Dic_date_Up1]
  on [dbo].[student]--触发器对象表
  for update ,insert--插入或更新时触发.
  as --
  begin
  if EXISTS(select 1 from u  A ,inserted B where A.id=B.Sno)--检查插入或更新的数据在DIC_DATAupdate表中是否存在,有则更新,无则添加
  update u  set [Name]=B.[Sname], [Type]=0, [password]=B.[Spassword],
  [email]=B.[Semail]   from inserted B
  where  u.[id]=B.[Sno] --为记录数据插入或更新时间,增加时间(lasttime)字段.
  else
  insert into u(id, name, PassWord, email,Type) select [Sno],[Sname],[Spassword],[Semail],0 from inserted
  end

All1表存了所有的信息,所以更改的时候需要变动student表。

USE [1]
GO
/****** Object:  Trigger [dbo].[Monitor_Dic_date_Up]    Script Date: 2021/5/21 星期五 15:38:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER  trigger [dbo].[Monitor_Dic_date_Up]
  on [dbo].[All1]--触发器对象表
  for update ,insert--插入或更新时触发.
  as --
  begin
  if EXISTS(select 1 from student  A ,inserted B where A.Sno=B.Sno)--检查插入或更新的数据在DIC_DATAupdate表中是否存在,有则更新,无则添加
  update student  set [sName]=B.[Sname], [sdept]=b.[sdept], [spassword]=B.[Spassword],[ssex]=b.[ssex],[sage]=b.[sage],
  [semail]=B.[Semail]   from inserted B
  where  student.[Sno]=B.[Sno] 
  else
  insert into student(Sno, sname, sPassWord, semail,sdept,ssex,sage) select [Sno],[Sname],[Spassword],[Semail],[sdept],[ssex],[sage] from inserted
  end

学生信息修改

读取学生表的实现

表的界面如下:
在这里插入图片描述使用QTableView来显示学生信息,设置QSqlQueryModel来读取数据库信息并显示在表格中,QItemSelectionModel可以读取当前选中行的信息。
核心代码如下:

void editstudentinf::opentable(){
     qryModel=new QSqlQueryModel(this);
    theSelection=new QItemSelectionModel(qryModel);
     qryModel->setQuery("SELECT Sno, Sname, Ssex, Sage, Sdept, Semail, Spassword FROM All1 order by Sno");
     if (qryModel->lastError().isValid())
     {
        QMessageBox::information(this, "错误", "数据表查询错误,错误信息\n"
     +qryModel->lastError().text(), QMessageBox::Ok,QMessageBox::NoButton);
        return;
     }
     qryModel->setHeaderData(0,Qt::Horizontal,"学号");
     qryModel->setHeaderData(1,Qt::Horizontal,"姓名");
     qryModel->setHeaderData(2,Qt::Horizontal,"性别");
     qryModel->setHeaderData(3,Qt::Horizontal,"年龄");
     qryModel->setHeaderData(4,Qt::Horizontal,"系别");
     qryModel->setHeaderData(5,Qt::Horizontal,"邮箱");
     qryModel->setHeaderData(6,Qt::Horizontal,"密码");
        ui->tableView->setModel(qryModel);
        ui->tableView->setSelectionModel(theSelection);
        }

学生表的增删改查

在本程序中,双击表格的某一行就是修改信息,通过重写QTable的doubleclick函数可以实现该功能,为了避免更改学号产生的一系列问题,所以学号不可改.
核心代码:

void editstudentinf::on_tableView_doubleClicked(const QModelIndex &index)
{ //tableView上双击,编辑当前记录
   int curRecNo=index.row();
   updateRecord(curRecNo);
}

void editstudentinf::on_action_7_triggered()
{   Teacher *t = new Teacher();
    this -> close();
    t -> show();
}
void editstudentinf::updateRecord(int recNo){
    qDebug() << recNo << endl;
    QSqlRecord  curRec= qryModel -> record(recNo); //获取当前记录
       QString Sno=curRec.value("Sno").toString();
       QSqlQuery query; //查询出当前记录的所有字段
       query.prepare("select * from All1 where Sno = :ID");
       query.bindValue(":ID",Sno);
       query.exec();
       query.first();
       if (!query.isValid()) //是否为有效记录
          return;
       curRec=query.record();
       WDialogData   *dataDialog=new WDialogData(this);
       Qt::WindowFlags   flags=dataDialog->windowFlags();
       dataDialog->setWindowFlags(flags | Qt::MSWindowsFixedSizeDialogHint);
       dataDialog->setUpdateRecord(curRec);//调用对话框函数更新数据和界面
       int ret=dataDialog->exec();// 以模态方式显示对话框
       if (ret==QDialog::Accepted) //OK键被按下
       {
        QSqlRecord  recData=dataDialog->getRecordData(); //获得对话框返回的记录
        query.prepare(QString("update All1 set Sname=:Sname, Ssex=:Ssex,  Sdept=:Sdept, Sage=:Sage, Semail=:Semail, Spassword=:Spassword,Photo=:Photo  where Sno = :Sno"));
        query.bindValue(":Sname",recData.value("Sname"));
             query.bindValue(":Ssex",recData.value("Ssex"));
             query.bindValue(":Sdept",recData.value("Sdept"));
             query.bindValue(":Sage",recData.value("Sage"));
             query.bindValue(":Semail",recData.value("Semail"));
             query.bindValue(":Sno",recData.value("Sno"));
             query.bindValue(":Spassword",recData.value("Spassword"));
             query.bindValue(":Photo",recData.value("Photo"));
        qDebug() << QString("update Student set Sname='%1', Ssex='%2',"
                      " Sdept='%3', Sage='%4', Semail='%5'"
                            " where Sno ='%6' ").arg(recData.value("Sname").toString()).arg(recData.value("Ssex").toString()).arg(recData.value("Sdept").toString()).arg(recData.value("Sage").toString()).arg(recData.value("Semail").toString()).arg(recData.value("Sno").toString()) << endl;
         if (!query.exec())
             QMessageBox::critical(this, "错误",
                      "记录更新错误\n"+query.lastError().text(),
                      QMessageBox::Ok,QMessageBox::NoButton);
         else//重新执行SQL语句查询
         {       qryModel->setQuery("SELECT Sno, Sname, Ssex, Sage, Sdept, Semail, Spassword FROM All1 order by Sno");
             ui->tableView->setModel(qryModel);
             ui->tableView->setSelectionModel(theSelection);
            }
       }
       delete dataDialog;
}

界面如下:在这里插入图片描述
选中某一行或者某一列然后点击工具栏的删除或者添加或者查找即可实现增删查。
或者使用快捷键insert、delete、ctrl + f.这是通过QT中的Qaction实现的
核心代码:

void editstudentinf::on_action_2_triggered()
{
    qryModel->setQuery("SELECT Sno, Sname, Ssex, Sage, Sdept, Semail, Spassword FROM All1 order by Sno");
    ui->tableView->setModel(qryModel);
    ui->tableView->setSelectionModel(theSelection);
}
void editstudentinf::on_currentChanged(const QModelIndex &current, const QModelIndex &previous)
{//更新actSubmit和actRevert的状态
   Q_UNUSED(current);
   Q_UNUSED(previous);
   ui->action_2->setEnabled(tabModel->isDirty()); //有未保存修改时可用
}
void editstudentinf::on_currentRowChanged(const QModelIndex &current, const QModelIndex &previous)
{

 /*   Q_UNUSED(previous);
    if (!current.isValid())
    {  ui->dbLabPhoto->clear();
       return;
    }
    dataMapper->setCurrentModelIndex(current); //更新数据映射的行号
    int curRecNo=theSelection->currentIndex().row();
    QSqlRecord  curRec=qryModel->record(curRecNo); //获取当前记录
    auto empNo=curRec.value("Sno").toString();
    QSqlQuery query; //查询当前EmpNo的Memo和Photo字段的数据
    query.prepare("select Sno, Photo from student where Sno = :ID");
    query.bindValue(":ID",empNo);
    query.exec();
    query.first();

    QVariant   va=query.value("Photo");
    if (!va.isValid())  //图片字段内容为空
      ui->dbLabPhoto->clear();
    else
    {//显示照片
      QByteArray data=va.toByteArray();
      QPixmap pic;
      pic.loadFromData(data);
      ui->dbLabPhoto->setPixmap(pic.scaledToWidth(
               ui->dbLabPhoto->size().width()));
 }*/
 }

void editstudentinf::on_action_triggered()
{
    //插入记录
       QSqlQuery query;
       query.exec("select * from all1 where sno =-1"); //只查询字段信息
       QSqlRecord curRec=query.record();//获取当前记录,实际为空记录
      // curRec.setValue("sno",qryModel->rowCount()+20191102000);
       WDialogData   *dataDialog=new WDialogData(this);
       Qt::WindowFlags   flags=dataDialog->windowFlags();
       dataDialog->setWindowFlags(flags | Qt::MSWindowsFixedSizeDialogHint);
       dataDialog->setInsertRecord(curRec); //插入记录
       int ret=dataDialog->exec();// 以模态方式显示对话框
       if (ret==QDialog::Accepted) //OK键被按下
       {
         QSqlRecord  recData=dataDialog->getRecordData();
         query.exec(QString("select * from all1 where sno = %1").arg(recData.value("Sno").toString()));
         bool flag = 0;
         while(query.next())
             flag = 1;
         query.exec(QString("select * from all1 where semail = %1").arg(recData.value("Semail").toString()));
         bool flag1 = 0;
         while(query.next())
             flag1 = 1;
        // qDebug() << QString("insert into All1(sname,ssex,sdept,sage,semail,sno,spassword, photo) values('%1', '%2',  '%3', '%4', '%5','%6','%7',CAST(cast('%8' as varchar(max)) as varbinary(max)))").arg(recData.value("Sname").toString()).arg(recData.value("Ssex").toString()).arg(recData.value("Sdept").toString()).arg(recData.value("Sage").toString()).arg(recData.value("Semail").toString()).arg(recData.value("Sno").toString()).arg(recData.value("Spassword").toString()).arg(recData.value("Photo").toString()) << endl;
         query.prepare(QString("insert into All1(sname,ssex,sdept,sage,semail,sno,spassword, photo) values(:sname, :ssex, :sdept, :sage, :semail,:sno,:spassword,:photo)"));
         query.bindValue(":sname",recData.value("Sname"));
              query.bindValue(":ssex",recData.value("Ssex"));
              query.bindValue(":sdept",recData.value("Sdept"));
              query.bindValue(":sage",recData.value("Sage"));
              query.bindValue(":semail",recData.value("Semail"));
              query.bindValue(":sno",recData.value("Sno"));
              query.bindValue(":spassword",recData.value("Spassword"));
              query.bindValue(":photo",recData.value("Photo"));
         if(flag1 || flag){
             QMessageBox::critical(this, "错误",
                       "邮箱或学号重复",
                       QMessageBox::Ok,QMessageBox::NoButton);
         }
         else if (!query.exec())
             QMessageBox::critical(this, "错误",
                       "插入记录错误\n"+query.lastError().text(),
                       QMessageBox::Ok,QMessageBox::NoButton);
         else //重新执行SQL语句查询
         {
             qryModel->setQuery("SELECT Sno, Sname, Ssex, Sage, Sdept, Semail, Spassword FROM All1 order by Sno");
             ui->tableView->setModel(qryModel);
             ui->tableView->setSelectionModel(theSelection);}
         }
       delete dataDialog;
}
void editstudentinf::on_action_5_triggered()
{
    int curRecNo=theSelection->currentIndex().row();
    QSqlRecord  curRec=qryModel->record(curRecNo); //获取当前记录
    if (curRec.isEmpty()) //当前为空记录
          return;
       QString empNo=curRec.value("Sno").toString();//获取员工编号
       QSqlQuery query;
       query.prepare("delete  from all1 where sno = :ID");
       query.bindValue(":ID",empNo);
       query.exec();
       query.prepare("delete  from student where sno = :ID");
       query.bindValue(":ID",empNo);
       query.exec();
       query.prepare("delete  from U where id = :ID");
       query.bindValue(":ID",empNo);
       query.exec();
       if (!query.exec())
          QMessageBox::critical(this, "错误",
                "删除记录出现错误\n"+query.lastError().text(),
                 QMessageBox::Ok,QMessageBox::NoButton);
       else //重新执行SQL语句查询
       {
           qryModel->setQuery("SELECT Sno, Sname, Ssex, Sage, Sdept, Semail, Spassword FROM All1 order by Sno");
           ui->tableView->setModel(qryModel);
           ui->tableView->setSelectionModel(theSelection);
       }
}

void editstudentinf::on_action_6_triggered()
{
    //插入记录
       QSqlQuery query;
       query.exec("select * from all1 where sno =-1"); //只查询字段信息
       QSqlRecord curRec=query.record();//获取当前记录,实际为空记录
      // curRec.setValue("sno",qryModel->rowCount()+20191102000);
       find_dialog   *dataDialog=new find_dialog(this);
       Qt::WindowFlags   flags=dataDialog->windowFlags();
       dataDialog->setWindowFlags(flags | Qt::MSWindowsFixedSizeDialogHint);
    //   dataDialog->setInsertRecord(curRec); //插入记录
       int ret=dataDialog->exec();// 以模态方式显示对话框
       if(ret){
       qryModel->setQuery(QString("SELECT Sno, Sname, Ssex, Sage, Sdept, Semail, Spassword FROM All1 where sno = %1").arg(dataDialog->getText()));
       ui->tableView->setModel(qryModel);
       ui->tableView->setSelectionModel(theSelection);}
       delete dataDialog;
}

使用数据库的valbinary类型来存取图片

用QByteArray把图片转换成二进制数据存到数据库中,使用的时候再转回来即可。
实现如下:

   QVariant  va=recData.value("Photo");
   if (!va.isValid())  //图片字段内容为空
      ui->LabPhoto->clear();
   else
   {
      QByteArray data=va.toByteArray();
      QPixmap pic;
      pic.loadFromData(data);
      ui->LabPhoto->setPixmap(pic.scaledToWidth(
                  ui->LabPhoto->size().width()));
   }

效果:
在这里插入图片描述

老师修改学生照片,不仅老师看到的学生照片发生变化,而且学生自己看到的照片也变换了。

学生成绩修改

和上面代码类似,将student表改成sc表即可。

课程信息修改

和上面代码类似,将student表改成course表即可。

学生选课系统

查看个人成绩

在界面的头文件中加一个变量Sno,将上一个界面的账号的Sno传给该界面,然后用select语句选出学生信息。

查看不及格成绩

select 中加入 where grade < 60即可。

查看所有成绩

将上述约束去除.

选/退课

将course表里的所有信息和sc表里的信息都select出来,然后将course表的数据读入Qcombobox里,即可实现如下的选课框:
在这里插入图片描述

#include "editsc.h"
#include "find_dialog.h"
#include "sdialogdata.h"
#include "teacher.h"
#include "ui_editsc.h"
#include <QSqlError>
#include <QMessageBox>
#include <QSqlQuery>
#include <qdebug.h>
editsc::editsc(QWidget *parent) :
    QMainWindow(parent),
    ui(new Ui::editsc)
{
    ui->setupUi(this);
    ui->tableView->setSelectionBehavior(QAbstractItemView::SelectItems);
    ui->tableView->setSelectionMode(QAbstractItemView::SingleSelection);
      ui->tableView->setAlternatingRowColors(true);
  db = new Database();
  db -> OpenDatabase();
  opentable();
  qryModel->setQuery("SELECT Cno, Cname, Sno, Sname, Grade FROM sc order by Cno");
  ui->tableView->setModel(qryModel);
  ui->tableView->setSelectionModel(theSelection);
}
void editsc::on_tableView_doubleClicked(const QModelIndex &index)
{ //tableView上双击,编辑当前记录
   int curRecNo=index.row();
   updateRecord(curRecNo);
}
void editsc::on_action_7_triggered()
{
    Teacher *t = new Teacher();
        this -> close();
        t -> show();
}
void editsc::updateRecord(int recNo){
    qDebug() << recNo << endl;
    QSqlRecord  curRec= qryModel -> record(recNo); //获取当前记录
       QString Cno=curRec.value("Cno").toString();//获取EmpNo
       QSqlQuery query; //查询出当前记录的所有字段
       query.prepare("select * from sc");
       query.exec();
       query.first();
       if (!query.isValid()) //是否为有效记录
          return;
       curRec=query.record();
       SDialogData   *dataDialog=new SDialogData(this);
       Qt::WindowFlags   flags=dataDialog->windowFlags();
       dataDialog->setWindowFlags(flags | Qt::MSWindowsFixedSizeDialogHint);
       dataDialog->setUpdateRecord(curRec);//调用对话框函数更新数据和界面
       int ret=dataDialog->exec();// 以模态方式显示对话框
       if (ret==QDialog::Accepted) //OK键被按下
       {
        QSqlRecord  recData=dataDialog->getRecordData(); //获得对话框返回的记录
        query.prepare(QString("update sc set Grade=:Grade  where Cno = :Cno and Sno = :Sno"));
        auto p = recData.value("Grade");
        query.bindValue(":Grade",p);
        query.bindValue(":Sno",recData.value("Sno"));
        query.bindValue(":Cno",recData.value("Cno"));
        qDebug() << QString("update Student set Sname='%1', Ssex='%2',"
                      " Sdept='%3', Sage='%4', Semail='%5'"
                            " where Sno ='%6' ").arg(recData.value("Sname").toString()).arg(recData.value("Ssex").toString()).arg(recData.value("Sdept").toString()).arg(recData.value("Sage").toString()).arg(recData.value("Semail").toString()).arg(recData.value("Sno").toString()) << endl;
        if(p.toInt() > 100 || p.toInt() < 0){
            QMessageBox::critical(this, "错误",
                      "分数要介于0~100之间",
                      QMessageBox::Ok,QMessageBox::NoButton);
        }
        else if (!query.exec())
             QMessageBox::critical(this, "错误",
                      "记录更新错误\n"+query.lastError().text(),
                      QMessageBox::Ok,QMessageBox::NoButton);
         else//重新执行SQL语句查询
         {  qryModel->setQuery("SELECT Cno, Cname, Sno, Sname, Grade FROM sc order by Cno");
             ui->tableView->setModel(qryModel);
             ui->tableView->setSelectionModel(theSelection);
            }
       }
       delete dataDialog;
}
void editsc::opentable(){
     qryModel=new QSqlQueryModel(this);
    theSelection=new QItemSelectionModel(qryModel);
     qryModel->setQuery("SELECT Cno, Cname, Sno, Sname, Grade FROM SC order by Cno");
     if (qryModel->lastError().isValid())
     {
        QMessageBox::information(this, "错误", "数据表查询错误,错误信息\n"
     +qryModel->lastError().text(), QMessageBox::Ok,QMessageBox::NoButton);
        return;
     }
     qryModel->setHeaderData(0,Qt::Horizontal,"课程号");
     qryModel->setHeaderData(1,Qt::Horizontal,"课程名");
     qryModel->setHeaderData(2,Qt::Horizontal,"学号");
     qryModel->setHeaderData(3,Qt::Horizontal,"姓名");
     qryModel->setHeaderData(4,Qt::Horizontal,"成绩");
        ui->tableView->setModel(qryModel);
        ui->tableView->setSelectionModel(theSelection);
      //  ui->actOpenDB->setEnabled(false);
}
void editsc::on_action_2_triggered()
{
    qryModel->setQuery("SELECT Cno, Cname, Sno, Sname, Grade FROM sc order by Cno");
    ui->tableView->setModel(qryModel);
    ui->tableView->setSelectionModel(theSelection);
}
void editsc::on_currentChanged(const QModelIndex &current, const QModelIndex &previous)
{//更新actSubmit和actRevert的状态
   Q_UNUSED(current);
   Q_UNUSED(previous);
   ui->action_2->setEnabled(tabModel->isDirty()); //有未保存修改时可用
}
void editsc::on_currentRowChanged(const QModelIndex &current, const QModelIndex &previous)
{

}
void editsc::on_action_triggered()
{
    //插入记录
       QSqlQuery query;
       query.exec("select * from sc where cno = -1 and sno = -1"); //只查询字段信息
       QSqlRecord curRec=query.record();//获取当前记录,实际为空记录
      // curRec.setValue("cno",qryModel->rowCount()+20191102000);
       SDialogData   *dataDialog=new SDialogData(this);
       Qt::WindowFlags   flags=dataDialog->windowFlags();
       dataDialog->setWindowFlags(flags | Qt::MSWindowsFixedSizeDialogHint);
       dataDialog->setInsertRecord(curRec); //插入记录
       int ret=dataDialog->exec();// 以模态方式显示对话框
       if (ret==QDialog::Accepted) //OK键被按下
       {
         QSqlRecord  recData=dataDialog->getRecordData();
         query.exec(QString("select * from sc where cno = %1 and sno = %2").arg(recData.value("Cno").toString()).arg(recData.value("Sno").toString()));
         qDebug() << QString("select * from sc where cno = %1 and sno = %2").arg(recData.value("Cno").toString()).arg(recData.value("Sno").toString()) << endl;
         bool flag = 0;
         while(query.next())
             flag = 1;
        // qDebug() << QString("insert into All1(sname,ssex,sdept,sage,semail,Cno,spassword, photo) values('%1', '%2',  '%3', '%4', '%5','%6','%7',CAST(cast('%8' as varchar(max)) as varbinary(max)))").arg(recData.value("Sname").toString()).arg(recData.value("Ssex").toString()).arg(recData.value("Sdept").toString()).arg(recData.value("Sage").toString()).arg(recData.value("Semail").toString()).arg(recData.value("Sno").toString()).arg(recData.value("Spassword").toString()).arg(recData.value("Photo").toString()) << endl;
         query.prepare(QString("insert into sc(cno,cname,sno, sname, grade) values(:cno,:cname, :sno,:sname,:grade)"));
         auto p = recData.value("Grade");
         query.bindValue(":cname",recData.value("Cname"));
         query.bindValue(":cno",recData.value("Cno"));
              query.bindValue(":sno",recData.value("Sno"));
              query.bindValue(":sname",recData.value("Sname"));
              query.bindValue(":grade",p);
              qDebug() << p.toInt() << endl;
         if(flag){
             QMessageBox::critical(this, "错误",
                       "课程号与学号重复",
                       QMessageBox::Ok,QMessageBox::NoButton);
         }
         else if(p.toInt() > 100 || p.toInt() < 0){
             QMessageBox::critical(this, "错误",
                       "分数要介于0~100之间",
                       QMessageBox::Ok,QMessageBox::NoButton);
         }
         else if (!query.exec())
             QMessageBox::critical(this, "错误",
                       "插入记录错误\n"+query.lastError().text(),
                       QMessageBox::Ok,QMessageBox::NoButton);
         else //重新执行SQL语句查询
         {
             qryModel->setQuery("SELECT Cno, Cname, Sno, Sname, Grade FROM sc order by Cno");
             ui->tableView->setModel(qryModel);
             ui->tableView->setSelectionModel(theSelection);}
         }
       delete dataDialog;
}
void editsc::on_action_5_triggered()
{
    int curRecNo=theSelection->currentIndex().row();
    QSqlRecord  curRec=qryModel->record(curRecNo); //获取当前记录
    if (curRec.isEmpty()) //当前为空记录
          return;
       QString empNo=curRec.value("Sno").toString();//获取员工编号
       QString empN1o=curRec.value("Cno").toString();//获取员工编号
       QSqlQuery query;
       query.prepare("delete  from sc where Sno = :ID and Cno = :ID1");
       query.bindValue(":ID",empNo);
       query.bindValue(":ID1",empN1o);
       query.exec();
       if (!query.exec())
          QMessageBox::critical(this, "错误",
                "删除记录出现错误\n"+query.lastError().text(),
                 QMessageBox::Ok,QMessageBox::NoButton);
       else //重新执行SQL语句查询
       {
           qryModel->setQuery("SELECT Cno, Cname, Sno, Sname, Grade FROM sc order by Cno");
           ui->tableView->setModel(qryModel);
           ui->tableView->setSelectionModel(theSelection);
       }
}
void editsc::on_action_6_triggered()
{
    //插入记录
       QSqlQuery query;
       query.exec("select * from sc where cno =-1"); //只查询字段信息
       QSqlRecord curRec=query.record();//获取当前记录,实际为空记录
      // curRec.setValue("cno",qryModel->rowCount()+20191102000);
       find_dialog   *dataDialog=new find_dialog(this);
       dataDialog ->setText("学号");
       Qt::WindowFlags flags=dataDialog->windowFlags();
       dataDialog->setWindowFlags(flags | Qt::MSWindowsFixedSizeDialogHint);
    //   dataDialog->setInsertRecord(curRec); //插入记录
       int ret=dataDialog->exec();// 以模态方式显示对话框
       if(ret){
       qryModel->setQuery(QString("SELECT Cno, Cname, Sno, Sname, Grade FROM sc where sno = %1").arg(dataDialog->getText()));
       ui->tableView->setModel(qryModel);
       ui->tableView->setSelectionModel(theSelection);}
       delete dataDialog;
}
editsc::~editsc()
{
    delete ui;
}

文章来源:https://blog.csdn.net/qq_45834992/article/details/117120813

后台-系统设置-扩展变量-手机广告位-内容正文底部
版权声明

本文仅代表作者观点,不代表本站立场。
本文系作者授权发表,未经许可,不得转载。
本文地址:https://www.jcdi.cn/shujukuqita/30763.html

留言与评论(共有 0 条评论)
   
验证码:
后台-系统设置-扩展变量-手机广告位-评论底部广告位

教程弟

https://www.jcdi.cn/

统计代码 | 京ICP1234567-2号

Powered By 教程弟 教程弟

使用手机软件扫描微信二维码