一、说明
Qt使用MySQL,进行增删改查等操作
二、.pro
#-------------------------------------------------
#
# Project created by QtCreator 2022-04-15T10:51:50
#
#-------------------------------------------------
QT += core gui
QT += sql
greaterThan(QT_MAJOR_VERSION, 4): QT += widgets
TARGET = MySqlDemo
TEMPLATE = app
# The following define makes your compiler emit warnings if you use
# any feature of Qt which has been marked as 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 you use 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
CONFIG += c++11
SOURCES += \
main.cpp \
MainWindow.cpp
HEADERS += \
MainWindow.h
FORMS += \
MainWindow.ui
# Default rules for deployment.
qnx: target.path = /tmp/$${TARGET}/bin
else: unix:!android: target.path = /opt/$${TARGET}/bin
!isEmpty(target.path): INSTALLS += target
三、main.cpp
#include "MainWindow.h"
#include <QApplication>
int main(int argc, char *argv[])
{
QApplication a(argc, argv);
MainWindow w;
w.show();
return a.exec();
}
四、MainWindow.h
#ifndef MAINWINDOW_H
#define MAINWINDOW_H
#include <QMainWindow>
#include <QSqlTableModel>
namespace Ui {
class MainWindow;
}
class MainWindow : public QMainWindow
{
Q_OBJECT
public:
explicit MainWindow(QWidget *parent = nullptr);
~MainWindow();
private slots:
void connectDB();
void on_showDBButton_clicked();
void on_addDBButton_clicked();
void on_deleteNameButton_clicked();
void on_deleteAgeButton_clicked();
void on_deleteScoreButton_clicked();
void on_findNameButton_clicked();
void on_findAgeButton_clicked();
void on_findScoreButton_clicked();
void on_changeNameButton_clicked();
void on_changeAgeButton_clicked();
void on_changeScoreButton_clicked();
void textEditShow(QString str);
private:
Ui::MainWindow *ui;
};
#endif // MAINWINDOW_H
五、MainWindow.cpp.cpp
#include "MainWindow.h"
#include "ui_MainWindow.h"
#include <QSqlDatabase>
#include <QSqlError>
#include <QSqlQuery>
#include <QDebug>
#include <QMessageBox>
MainWindow::MainWindow(QWidget *parent) :
QMainWindow(parent),
ui(new Ui::MainWindow)
{
ui->setupUi(this);
connectDB();
}
MainWindow::~MainWindow()
{
delete ui;
}
//连接数据库
void MainWindow::connectDB()
{
QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");
db.setHostName("127.0.0.1"); //数据库服务器IP,本地服务器
db.setPort(3306); //端口号
db.setDatabaseName("test"); //数据库名
db.setUserName("root"); //用户名
db.setPassword(""); //密码
bool ok = db.open();
if (!ok)
{
qDebug()<<"open sql error:" << db.lastError();
}
//创建表
QSqlQuery query;
query.exec("create table student(id int primary key auto_increment, name varchar(255), age int, score int)ENGINE=INNODB;");
//删除整个表
//query.exec("drop table student");
}
//显示所有
void MainWindow::on_showDBButton_clicked()
{
//遍历
QSqlQuery query;
bool ok = query.exec("select *from student");
if (!ok)
{
QString str = QString("show error:%1").arg(query.lastError().text());
textEditShow(str);
return;
}
ui->textEdit->append("******************************");
while(query.next()) //遍历完为false
{
//方式1:以下标
ui->textEdit->append(QString("%1 姓名:%2 年龄:%3 成绩:%4")
.arg(query.value(0).toInt())
.arg(query.value(1).toString())
.arg(query.value(2).toInt())
.arg(query.value(3).toInt()));
//方式2:以字段
//qDebug()<< query.value("id").toInt()<< query.value("name").toString()
//<< query.value("age").toInt()<< query.value("score").toInt();
}
}
//添加
void MainWindow::on_addDBButton_clicked()
{
QSqlQuery query;
//单行插入
// 在创建表时id设置了auto_increment,所以插入时加不加id都可以
//bool ok = query.exec("insert into student(id, name, age, score) values(1, '张三', 18, 80)");
QString str = QString("insert into student(name, age, score) values('%1', %2, %3)")
.arg(ui->nameLineEdit->text())
.arg(ui->ageLineEdit->text())
.arg(ui->scoreLineEdit->text());
bool ok = query.exec(str);
if (!ok)
{
QString str = QString("add error:%1").arg(query.lastError().text());
textEditShow(str);
}
else
{
on_showDBButton_clicked();
}
//多行插入
// query.prepare("insert into student(name, age, score) values(?,?,?)"); //?是占位符
// QVariantList name;
// QVariantList age;
// QVariantList score;
// name << "李四" << "王五" << "赵六";
// age << 19 << 20 << 21;
// score << 85 << 90 << 95;
// //按顺序给字段绑定相应的值
// query.addBindValue(name);
// query.addBindValue(age);
// query.addBindValue(score);
// //执行预处理命令
// ok = query.execBatch();
// if (!ok)
// {
// qDebug()<<"execBatch error:" << query.lastError();
// }
}
//根据姓名删除
void MainWindow::on_deleteNameButton_clicked()
{
QSqlQuery query;
//删除某一行
QString str = QString("delete from student where name='%1'").arg(ui->nameLineEdit->text());
bool ok = query.exec(str);
if (!ok)
{
QString str = QString("delete error:%1").arg(query.lastError().text());
textEditShow(str);
}
else
{
on_showDBButton_clicked();
}
}
//根据年龄删除
void MainWindow::on_deleteAgeButton_clicked()
{
QSqlQuery query;
//删除某一行
QString str = QString("delete from student where age='%1'").arg(ui->ageLineEdit->text());
bool ok = query.exec(str);
if (!ok)
{
QString str = QString("delete error:%1").arg(query.lastError().text());
textEditShow(str);
}
else
{
on_showDBButton_clicked();
}
}
//根据成绩删除
void MainWindow::on_deleteScoreButton_clicked()
{
QSqlQuery query;
//删除某一行
QString str = QString("delete from student where score='%1'").arg(ui->scoreLineEdit->text());
bool ok = query.exec(str);
if (!ok)
{
QString str = QString("delete error:%1").arg(query.lastError().text());
textEditShow(str);
}
else
{
on_showDBButton_clicked();
}
}
//根据姓名查找
void MainWindow::on_findNameButton_clicked()
{
QSqlQuery query;
//查询
QString str = QString("select id,age,score from student where name='%1'")
.arg(ui->nameLineEdit->text());
bool ok = query.exec(str);
if (!ok)
{
QString str = QString("find error:%1").arg(query.lastError().text());
textEditShow(str);
}
else
{
if(query.size() == 0)
{
textEditShow("没有找到");
return;
}
ui->textEdit->append("******************************");
while(query.next()) //遍历完为false
{
ui->textEdit->append(QString("%1 id:%2 年龄:%3 成绩:%4")
.arg(ui->nameLineEdit->text())
.arg(query.value(0).toInt())
.arg(query.value(1).toInt())
.arg(query.value(2).toInt()));
}
}
}
//根据年龄查找
void MainWindow::on_findAgeButton_clicked()
{
QSqlQuery query;
//查询
QString str = QString("select id,name,score from student where age='%1'")
.arg(ui->ageLineEdit->text());
bool ok = query.exec(str);
if (!ok)
{
QString str = QString("find error:%1").arg(query.lastError().text());
textEditShow(str);
}
else
{
if(query.size() == 0)
{
textEditShow("没有找到");
return;
}
ui->textEdit->append("******************************");
while(query.next()) //遍历完为false
{
ui->textEdit->append(QString("%1 id:%2 姓名:%3 成绩:%4")
.arg(ui->ageLineEdit->text())
.arg(query.value(0).toInt())
.arg(query.value(1).toString())
.arg(query.value(2).toInt()));
}
}
}
//根据成绩查找
void MainWindow::on_findScoreButton_clicked()
{
QSqlQuery query;
//查询
QString str = QString("select id,name,age from student where score='%1'")
.arg(ui->scoreLineEdit->text());
bool ok = query.exec(str);
if (!ok)
{
QString str = QString("find error:%1").arg(query.lastError().text());
textEditShow(str);
}
else
{
if(query.size() == 0)
{
textEditShow("没有找到");
return;
}
ui->textEdit->append("******************************");
while(query.next()) //遍历完为false
{
ui->textEdit->append(QString("%1 id:%2 姓名:%3 年龄:%4")
.arg(ui->scoreLineEdit->text())
.arg(query.value(0).toInt())
.arg(query.value(1).toString())
.arg(query.value(2).toInt()));
}
}
}
//根据年龄和成绩,修改姓名
void MainWindow::on_changeNameButton_clicked()
{
//修改
QSqlQuery query;
if(!ui->ageLineEdit->text().isEmpty() && !ui->scoreLineEdit->text().isEmpty())
{
QString str = QString("update student set name='%1' where age=%2 and score=%3")
.arg(ui->nameLineEdit->text())
.arg(ui->ageLineEdit->text())
.arg(ui->scoreLineEdit->text());
bool ok = query.exec(str);
if (!ok)
{
QString str = QString("update error:%1").arg(query.lastError().text());
textEditShow(str);
}
else
{
on_showDBButton_clicked();
}
}
else
{
QString str = QString("年龄和成绩不能为空");
textEditShow(str);
}
}
//根据姓名和成绩,修改年龄
void MainWindow::on_changeAgeButton_clicked()
{
QSqlQuery query;
if(!ui->nameLineEdit->text().isEmpty() && !ui->scoreLineEdit->text().isEmpty())
{
QString str = QString("update student set age=%1 where name='%2' and score=%3")
.arg(ui->ageLineEdit->text())
.arg(ui->nameLineEdit->text())
.arg(ui->scoreLineEdit->text());
bool ok = query.exec(str);
if (!ok)
{
QString str = QString("update error:%1").arg(query.lastError().text());
textEditShow(str);
}
else
{
on_showDBButton_clicked();
}
}
else
{
QString str = QString("姓名和年龄不能为空");
textEditShow(str);
}
}
//根据姓名和年龄,修改成绩
void MainWindow::on_changeScoreButton_clicked()
{
QSqlQuery query;
if(!ui->nameLineEdit->text().isEmpty() && !ui->ageLineEdit->text().isEmpty())
{
QString str = QString("update student set score=%1 where name='%2' and age=%3")
.arg(ui->scoreLineEdit->text())
.arg(ui->nameLineEdit->text())
.arg(ui->ageLineEdit->text());
bool ok = query.exec(str);
if (!ok)
{
QString str = QString("update error:%1").arg(query.lastError().text());
textEditShow(str);
}
else
{
on_showDBButton_clicked();
}
}
else
{
QString str = QString("姓名和年龄不能为空");
textEditShow(str);
}
}
//显示提示信息
void MainWindow::textEditShow(QString str)
{
ui->textEdit->append("******************************");
ui->textEdit->append(str);
}
六、MainWindow.ui
七、从两个表中读取数据
在做项目中用到了如下代码,在此记录一下,方便下次查找
QString sql = QString("SELECT "
"d. id ,"
"d. event ,"
"d. code ,"
"s. name "
"FROM %1 . alarm d, %1 . reg"
"WHERE d. site = '%2' "
"AND d. id IN(%3) "
"AND s. id IN(%3) "
"AND d. code = s. seq "
"AND event BETWEEN '%4' AND '%5' limit %6, %7;")
.arg(name).arg(site).arg(id).arg(startTime).arg(endTime).arg(startRow).arg(rowCount);