使用Qt实现Mysql数据库的增删查改

前言

利用Qt导入Mysql数据库，对数据库内容进行增加，删除，查找，修改。在项目需要创建数据管理系统时经常用到。

工程文件（student.pro）的配置

在.pro文件中添加sql

头文件(student.h)

#ifndef STUDENTDIALOG_H
#define STUDENTDIALOG_H

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

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_insertButton_clicked();
    //删除
    void on_deletButton_clicked();
    //修改
    void on_updateButton_clicked();
    //排序
    void on_sortButton_clicked();

    void on_pushButton_clicked();

private:
    Ui::studentDialog *ui;
    QSqlDatabase db;//建立qt与mysql连接
    QSqlQueryModel model;//保存结果集
    QSqlTableModel* Tmodel;

};
#endif // STUDENTDIALOG_H

源文件main.cpp

#include "studentdialog.h"

#include <QApplication>

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

源文件student.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("QMYSQL");
    //设置数据库名字
    db.setHostName("localhost");             //主机IP
    db.setPort(3306);                        //端口号，默认3306
    db.setDatabaseName("girls");       //连接的数据库名
    db.setUserName("root");                  //登录MySQL数据库的用户名
    db.setPassword("0830");                  //登录密码
    //打开数据库
    if(db.open() == true){
         qDebug() << "创建/打开数据库成功";
    }
    else{
        qDebug() << "创建/打开数据库失败";
    }
}
//创建数据表
void studentDialog::createTable()
{

}
//查询
void studentDialog::queryTable()
{
    QString str = QString("SELECT * FROM boys");
    model.setQuery(str);
    ui->tableView->setModel(&model);
}

//插入
void studentDialog::on_insertButton_clicked()
{
    QSqlQuery query;
    int id = ui->IDEdit->text().toInt();
    if(id == 0){
        QMessageBox::critical(this,"Error","ID输入错误！");
        return;
    }
    QString boyName = ui->NameEdit->text();
    if(boyName==""){
        QMessageBox::critical(this,"Error","姓名输入错误！");
        return;
    }
    double userCP = ui->scoreEdit->text().toDouble();
    if(userCP<0||userCP>1000){
        QMessageBox::critical(this,"Error","成绩输入错误！");
        return;
    }

    QString str = QString("INSERT INTO boys(id,boyName,userCP) VALUES(%1,'%2',%3)"
                          ).arg(id).arg(boyName).arg(userCP);
    if(query.exec(str)==false){
        QMessageBox::critical(this,"Error","已有信息！");
        qDebug() << "插入失败";
    }
    else{
        qDebug() <<"插入成功";
        queryTable();
    }
}
//删除
void studentDialog::on_deletButton_clicked()
{
    QSqlQuery query;
    int id = ui->IDEdit->text().toInt();
    QString str = QString("DELETE FROM boys WHERE id = %1").arg(id);
    if(QMessageBox::question(
                this,"删除","确定要删除么",
                QMessageBox::Yes|QMessageBox::No)==QMessageBox::No){
        return;
    }

    if(query.exec(str)==false){
        qDebug() << "删除失败";
    }
    else{
        qDebug() <<"删除成功";
        queryTable();
    }
}
//修改
void studentDialog::on_updateButton_clicked()
{
    QSqlQuery query;
    int id = ui->IDEdit->text().toInt();
    double userCP = ui->scoreEdit->text().toDouble();
    QString str = QString("UPDATE boys SET userCP=%1 WHERE id=%2"
                          ).arg(userCP).arg(id);
    if(query.exec(str)==false){
        QMessageBox::critical(this,"Error","修改失败！");
    }
    else{
        QMessageBox::information(this,"success","修改成功！");
        queryTable();
    }
}
//排序
void studentDialog::on_sortButton_clicked()
{
    //获取排序列名
    QString value = ui->acomboBox->currentText();
    //获取排序方式
    QString condition;
    if(ui->condcomboBox->currentIndex() == 0){
        condition = "ASC";//升序
    }
    else{
        condition  = "DESC";//降序
    }
    QString str = QString("SELECT * FROM boys ORDER BY %1 %2"
                          ).arg(value).arg(condition);
    //查询和显示
    model.setQuery(str);
    ui->tableView->setModel(&model);


}
//查询单条信息
void studentDialog::on_pushButton_clicked()
{
    QSqlQuery query;
    int id = ui->IDEdit->text().toInt();
    QString boyName = ui->NameEdit->text();
    QString strID = QString("select * from boys where id=%1").arg(id);
    QString strName = QString("select * from boys where boyName='%1'").arg(boyName);

    if(id!=NULL){
        model.setQuery(strID);
        ui->tableView->setModel(&model);
        QMessageBox::information(this,"suceess","查询成功！");
    }
    else if(boyName!=""){
        model.setQuery(strName);
        ui->tableView->setModel(&model);
        QMessageBox::information(this,"suceess","查询成功！");
    }
    else{
        qDebug()<<"查询失败";
    }


}

UI界面设计