Qt学习 第13节：学生成绩查询系统

 sqlite3  student.db 创建数据库

.tables 显示当前数据库中所有的表

create table userinfo(username,password);创建userinfo表

 insert into userinfo(username,password) values('user',123);向表中插入数据

select * from userinfo;查询userinfo中所有的数据

update userinfo set password = 233 where username = 'user';修改数据

delete from userinfo where username = 'user';删除一条记录

StudentDialog.pro

QT       += core gui sql

greaterThan(QT_MAJOR_VERSION, 4): QT += widgets

CONFIG += c++11

# The following define makes your compiler emit warnings if you use
# any Qt feature that has been marked deprecated (the exact warnings
# depend on your compiler). Please consult the documentation of the
# deprecated API in order to know how to port your code away from it.
DEFINES += QT_DEPRECATED_WARNINGS

# You can also make your code fail to compile if it uses deprecated APIs.
# In order to do so, uncomment the following line.
# You can also select to disable deprecated APIs only up to a certain version of Qt.
#DEFINES += QT_DISABLE_DEPRECATED_BEFORE=0x060000    # disables all the APIs deprecated before Qt 6.0.0

SOURCES += \
    main.cpp \
    StudentDialog.cpp

HEADERS += \
    StudentDialog.h

FORMS += \
    StudentDialog.ui

# Default rules for deployment.
qnx: target.path = /tmp/$${TARGET}/bin
else: unix:!android: target.path = /opt/$${TARGET}/bin
!isEmpty(target.path): INSTALLS += target

ui_StudentDialog.h

/********************************************************************************
** Form generated from reading UI file 'StudentDialog.ui'
**
** Created by: Qt User Interface Compiler version 5.12.8
**
** WARNING! All changes made in this file will be lost when recompiling UI file!
********************************************************************************/

#ifndef UI_STUDENTDIALOG_H
#define UI_STUDENTDIALOG_H

#include <QtCore/QVariant>
#include <QtWidgets/QApplication>
#include <QtWidgets/QComboBox>
#include <QtWidgets/QDialog>
#include <QtWidgets/QGridLayout>
#include <QtWidgets/QHBoxLayout>
#include <QtWidgets/QHeaderView>
#include <QtWidgets/QLabel>
#include <QtWidgets/QLineEdit>
#include <QtWidgets/QPushButton>
#include <QtWidgets/QTableView>
#include <QtWidgets/QVBoxLayout>

QT_BEGIN_NAMESPACE

class Ui_StudentDialog
{
public:
    QVBoxLayout *verticalLayout;
    QHBoxLayout *horizontalLayout;
    QComboBox *valuecomboBox;
    QComboBox *condcomboBox;
    QPushButton *sortButton;
    QTableView *tableView;
    QGridLayout *gridLayout;
    QLabel *label;
    QLineEdit *idEdit;
    QLabel *label_2;
    QLineEdit *nameEdit;
    QLabel *label_3;
    QLineEdit *scoreEdit;
    QHBoxLayout *horizontalLayout_2;
    QPushButton *insertpushButton;
    QPushButton *deletepushButton;
    QPushButton *updatepushButton;

    void setupUi(QDialog *StudentDialog)
    {
        if (StudentDialog->objectName().isEmpty())
            StudentDialog->setObjectName(QString::fromUtf8("StudentDialog"));
        StudentDialog->resize(544, 697);
        QFont font;
        font.setPointSize(20);
        font.setBold(true);
        font.setWeight(75);
        StudentDialog->setFont(font);
        verticalLayout = new QVBoxLayout(StudentDialog);
        verticalLayout->setObjectName(QString::fromUtf8("verticalLayout"));
        horizontalLayout = new QHBoxLayout();
        horizontalLayout->setObjectName(QString::fromUtf8("horizontalLayout"));
        valuecomboBox = new QComboBox(StudentDialog);
        valuecomboBox->addItem(QString());
        valuecomboBox->addItem(QString());
        valuecomboBox->setObjectName(QString::fromUtf8("valuecomboBox"));

        horizontalLayout->addWidget(valuecomboBox);

        condcomboBox = new QComboBox(StudentDialog);
        condcomboBox->addItem(QString());
        condcomboBox->addItem(QString());
        condcomboBox->setObjectName(QString::fromUtf8("condcomboBox"));

        horizontalLayout->addWidget(condcomboBox);

        sortButton = new QPushButton(StudentDialog);
        sortButton->setObjectName(QString::fromUtf8("sortButton"));

        horizontalLayout->addWidget(sortButton);


        verticalLayout->addLayout(horizontalLayout);

        tableView = new QTableView(StudentDialog);
        tableView->setObjectName(QString::fromUtf8("tableView"));

        verticalLayout->addWidget(tableView);

        gridLayout = new QGridLayout();
        gridLayout->setObjectName(QString::fromUtf8("gridLayout"));
        label = new QLabel(StudentDialog);
        label->setObjectName(QString::fromUtf8("label"));

        gridLayout->addWidget(label, 0, 0, 1, 1);

        idEdit = new QLineEdit(StudentDialog);
        idEdit->setObjectName(QString::fromUtf8("idEdit"));

        gridLayout->addWidget(idEdit, 0, 1, 1, 1);

        label_2 = new QLabel(StudentDialog);
        label_2->setObjectName(QString::fromUtf8("label_2"));

        gridLayout->addWidget(label_2, 1, 0, 1, 1);

        nameEdit = new QLineEdit(StudentDialog);
        nameEdit->setObjectName(QString::fromUtf8("nameEdit"));

        gridLayout->addWidget(nameEdit, 1, 1, 1, 1);

        label_3 = new QLabel(StudentDialog);
        label_3->setObjectName(QString::fromUtf8("label_3"));

        gridLayout->addWidget(label_3, 2, 0, 1, 1);

        scoreEdit = new QLineEdit(StudentDialog);
        scoreEdit->setObjectName(QString::fromUtf8("scoreEdit"));

        gridLayout->addWidget(scoreEdit, 2, 1, 1, 1);


        verticalLayout->addLayout(gridLayout);

        horizontalLayout_2 = new QHBoxLayout();
        horizontalLayout_2->setObjectName(QString::fromUtf8("horizontalLayout_2"));
        insertpushButton = new QPushButton(StudentDialog);
        insertpushButton->setObjectName(QString::fromUtf8("insertpushButton"));

        horizontalLayout_2->addWidget(insertpushButton);

        deletepushButton = new QPushButton(StudentDialog);
        deletepushButton->setObjectName(QString::fromUtf8("deletepushButton"));

        horizontalLayout_2->addWidget(deletepushButton);

        updatepushButton = new QPushButton(StudentDialog);
        updatepushButton->setObjectName(QString::fromUtf8("updatepushButton"));

        horizontalLayout_2->addWidget(updatepushButton);


        verticalLayout->addLayout(horizontalLayout_2);


        retranslateUi(StudentDialog);

        QMetaObject::connectSlotsByName(StudentDialog);
    } // setupUi

    void retranslateUi(QDialog *StudentDialog)
    {
        StudentDialog->setWindowTitle(QApplication::translate("StudentDialog", "\345\255\246\347\224\237\346\210\220\347\273\251\347\256\241\347\220\206\347\263\273\347\273\237", nullptr));
        valuecomboBox->setItemText(0, QApplication::translate("StudentDialog", "ID", nullptr));
        valuecomboBox->setItemText(1, QApplication::translate("StudentDialog", "Score", nullptr));

        condcomboBox->setItemText(0, QApplication::translate("StudentDialog", "\345\215\207\345\272\217", nullptr));
        condcomboBox->setItemText(1, QApplication::translate("StudentDialog", "\345\260\206\345\272\217", nullptr));

        sortButton->setText(QApplication::translate("StudentDialog", "\346\216\222\345\272\217", nullptr));
        label->setText(QApplication::translate("StudentDialog", "\345\255\246\345\217\267", nullptr));
        label_2->setText(QApplication::translate("StudentDialog", "\345\247\223\345\220\215", nullptr));
        label_3->setText(QApplication::translate("StudentDialog", "\346\210\220\347\273\251", nullptr));
        insertpushButton->setText(QApplication::translate("StudentDialog", "\346\217\222\345\205\245", nullptr));
        deletepushButton->setText(QApplication::translate("StudentDialog", "\345\210\240\351\231\244", nullptr));
        updatepushButton->setText(QApplication::translate("StudentDialog", "\344\277\256\346\224\271", nullptr));
    } // retranslateUi

};

namespace Ui {
    class StudentDialog: public Ui_StudentDialog {};
} // namespace Ui

QT_END_NAMESPACE

#endif // UI_STUDENTDIALOG_H

StudentDialog.h 

#ifndef STUDENTDIALOG_H
#define STUDENTDIALOG_H

#include <QDialog>
#include <QSqlDatabase>
#include <QSqlQuery>
#include <QSqlQueryModel>
#include <QSqlError>
#include <QDebug>

QT_BEGIN_NAMESPACE
namespace Ui { class StudentDialog; }
QT_END_NAMESPACE

class StudentDialog : public QDialog
{
    Q_OBJECT

public:
    StudentDialog(QWidget *parent = nullptr);
    ~StudentDialog();
private:
    void createDB();//创建数据库
    void createTable();//创建数据表
    void queryTable();//查询
private slots:
    //插入按钮对应的槽函数
    void on_insertpushButton_clicked();
    //删除按钮对应的槽函数
    void on_deletepushButton_clicked();
    //修改按钮对应的槽函数
    void on_updatepushButton_clicked();
    //排序按钮对应的槽函数
    void on_sortButton_clicked();

private:
    Ui::StudentDialog *ui;
private:
    QSqlDatabase db;//建立Qt和数据库连接
    QSqlQueryModel model;//保存数据集
};
#endif // STUDENTDIALOG_H

StudentDialog.cpp

#include "StudentDialog.h"
#include "ui_StudentDialog.h"

StudentDialog::StudentDialog(QWidget *parent)
    : QDialog(parent)
    , ui(new Ui::StudentDialog)
{
    ui->setupUi(this);
    createDB();
    createTable();
    queryTable();
}

StudentDialog::~StudentDialog()
{
    delete ui;
}

void StudentDialog::createDB()//创建数据库
{
    //添加数据库驱动库
    db = QSqlDatabase::addDatabase("QSQLITE");
    //设置数据库名字（文件名）
    db.setDatabaseName("student.db");
    if(db.open()==true){
        qDebug()<<"数据库连接成功!";
    }else
    {
        qDebug()<<"数据库连接失败";
    }
}
void StudentDialog::createTable()//创建数据表
{
    QSqlQuery query;
    QString str = QString("CREATE TABLE student ("
                          "id INT PRIMARY KEY NOT NULL,"
                          "name TEXT NOT NULL,"
                          "score REAL NOT NULL)");
    if(query.exec(str) == false){
        qDebug()<<str;
    }else{
        qDebug()<<"数据表创建成功";
    }
}
void StudentDialog::queryTable()//查询
{
    QString str = QString("SELECT * FROM student");
    model.setQuery(str);
    ui->tableView->setModel(&model);
}

//插入按钮对应的槽函数
void StudentDialog::on_insertpushButton_clicked()
{
    QSqlQuery query;
    int id = ui->idEdit->text().toInt();
    QString name = ui->nameEdit->text();
    double score = ui->scoreEdit->text().toFloat();
    QString str = QString("INSERT INTO student VALUES(%1,'%2',%3)"
                          ).arg(id).arg(name).arg(score);
    if(query.exec(str) == false){
        qDebug() << str;
    }else{
        qDebug() << "插入数据成功！";
        queryTable();
    }
}
//删除按钮对应的槽函数：根据ID删除
void StudentDialog::on_deletepushButton_clicked()
{
    QSqlQuery query;
    int id = ui->idEdit->text().toInt();

    QString str = QString("DELETE FROM student WHERE id = %1").arg(id);

    if(query.exec(str) == false){
        qDebug() << str;
    }else{
        qDebug() << "删除成功！";
        queryTable();
    }
}
//修改按钮对应的槽函数：根据ID号，修改成绩
void StudentDialog::on_updatepushButton_clicked()
{
    QSqlQuery query;
    int id = ui->idEdit->text().toInt();
    double score = ui->scoreEdit->text().toFloat();

    QString str = QString("UPDATE student SET score = %1 WHERE id = %2").arg(score).arg(id);

    if(query.exec(str) == false){
        qDebug() << str;
    }else{
        qDebug() << "修改成功！";
        queryTable();
    }
}
//排序按钮对应的槽函数
void StudentDialog::on_sortButton_clicked()
{
    QString value = ui->valuecomboBox->currentText();
    QString condition;

    if( ui->condcomboBox->currentText() == "升序")
    {
        condition = "ASC";
    }else
    {
         condition = "DESC";
    }
    QString str = QString("SELECT * FROM student ORDER BY %1 %2").arg(value).arg(condition);
    model.setQuery(str);
    ui->tableView->setModel(&model);//显示查询结果
}

main.cpp

#include "StudentDialog.h"

#include <QApplication>

int main(int argc, char *argv[])
{
    QApplication a(argc, argv);
    StudentDialog w;
    w.show();
    return a.exec();
}