QT数据库SQLITE数据库操作
一、Qt SQl模块1、在项目配置文件xxx.pro中的“QT += core gui”反面添加：
QT += sql
2、输入数据库访问的类
（1）：QSQL （包罗整个QtSQL模块中使用的各种标识符）
（2）：QSqlDriverCreatorBase (SQL驱动步伐工厂的基类)
（3）：QSqlDriverCreator （模板类，为特定的驱动步伐类型提供SQL驱动步伐工厂）
（4）：QSqlDatabase （表现与数据库的毗连）
（5）：QSqlDriver (用于访问特定SQL数据库的抽象基类)
（6）：QSqlError (数据库错误信息)
（7）：QSqlField (处理SQL数据库表和视图中的字段)
（8）：QSqlIndex (操作和描述数据库索引的函数)
（9）：QSqlQuery (执行和操作SQL语句的方法)
（10）：QSqlRecord （封装数据库记录)
(11) : QSqlResult (用于从特定的SQL数据库访问数据的抽象接口)
（12）：QSqlQueryModel （SQL效果集的只读数据模型）
（13）：QSqlRelationalTableModel （具有外键支持的单个数据库表的可编辑数据模型）
（14）：QSqlTableModel （单个数据库表的可编程数据模型）
二、访问SQLite数据库
下载SQLite数据库应用步伐（方便Windows直接查看和编辑）（可不下载，QT可直接编辑）
下载链接：SQLite administration | SQLite Expert
数据库操作语句：
1、创建SQLITE数据库毗连
（1） QSqlDatabase db_student = QSqlDatabase::addDatabase("QSQLITE");使用SQLITE数据库, 数据库毗连缺省的名字为 "qt_sql_default_connection"
（2） QSqlDatabase db_student = QSqlDatabase::addDatabase("QSQLITE", "my_db_connection");//使用SQLITE数据库, 指定数据库毗连的名字为"my_db_connection"
2、设置数据库的文件名
db_student.setDatabaseName(db_file_name);
3、打开数据库
if (!db_student.open())
{
qDebug() << "打开数据库失败1";
}
4、关闭数据库
db_student.close();
5、使用当前的数据库毗连 执行SQL操作
a、指定数据库毗连
（1）QSqlQuery query(db_student); //指定一个数据库的毗连
（2）QSqlQuery query; //不指定数据库毗连, 它会使用缺省的数据库毗连 "qt_sql_default_connection"
b、创建表
QString queryStr = "create table Student (id INTEGER PRIMARY KEY AUTOINCREMENT, name VARCHAR(40) NOT NULL, score INTEGER NOT NULL, class VARCHAR(40) NOT NULL)";//AUTOINCREMENT表现该列为整数递增，如果为空则主动填入1，然后在下面的每一行都会主动加1；PRIMARY KEY表现该列为列表的主键；VARCHAR(40)表现该列为可变长字符串（40字节），默认只能存储英文、数字、大概UTF-8编码的字符，最多40个字节；INTEGER表现该列为带符号的整数：NOT NULL表现该列的内容不能为空。
if (!query.exec(queryStr)) //执行创建表的操作（query.exec(queryStr)）
{
qDebug() << "创建数据库表失败";
qDebug() << query.lastError().text();
}
c、插入数据
//数据插入方式1
queryStr = "insert into Student values('1'， '小A'，87)";
if (!query.exec(queryStr))
{
qDebug() << "插入数据失败";
qDebug() << query.lastError().text();
}
//数据插入方式2
queryStr = "insert into Student values(?,?,?)";
query.prepare(queryStr);
query.addBindValue("2309250002");
query.addBindValue("李四");
query.addBindValue(82);
if (!query.exec(queryStr))
{
qDebug() << "插入数据失败2";
qDebug() << query.lastError().text();
}
//数据插入方式3
QString queryStr = "insert into Student values(:id， :name，:score)";
query.prepare(queryStr);
query.bindValue(":id", ui->le_id->text());
query.bindValue(":name", ui->le_name->text());
query.bindValue(":score", ui->le_score->text().toInt());
if (!query.exec(queryStr))
{
qDebug() << queryStr << " failed";
qDebug() << query.lastError().text();
}
d、查询数据
//查询特定表中所有数据
QString queryStr = "select * from Student";
if (!query.exec(queryStr))
{
qDebug() << queryStr << " failed";
qDebug() << query.lastError().text();
}
//查询特定表中符合某一条内容的数据
QString queryStr = "select * from Student where id = :id";
query.prepare(queryStr);
query.bindValue(":id", ui->le_id->text());
if (!query.exec(queryStr))
{
qDebug() << queryStr << " failed";
qDebug() << query.lastError().text();
}
while (query.next())
{
ui->le_name->setText(query.value(1).toString());
ui->le_score->setText(query.value(2).toString());
}
e、修改数据
QString queryStr = "update Student set name=?, score=? where id=?";
query.prepare(queryStr);
query.addBindValue(ui->le_name->text());
query.addBindValue(ui->le_score->text());
query.addBindValue(ui->le_id->text());
if (!query.exec(queryStr))
{
qDebug() << queryStr << " failed";
qDebug() << query.lastError().text();
}
f、删除数据
//删除数据库特定表中特定数据
QString queryStr = "delete from Student where id = :id";
query.prepare(queryStr);
query.bindValue(":id", ui->le_id->text());
if (!query.exec())
{
qDebug() << queryStr << " failed";
qDebug() << query.lastError().text();
}
//删除数据库中特定表（清空数据表）
QString queryStr = "DROP TABLE Student";
三、新建一个SQLite数据库操作
一、添加头文件
项目文件添加QT += sql
https://i-blog.csdnimg.cn/direct/68736b7209f445fc85f13a09660cec87.png
在.h文件中添加头文件如下
#include <QtSql/QSqlDatabase>
#include <QtSql/QSqlError>
#include <QtSql/QSqlQuery>
#include <QDebug> 二、编辑.cpp文件实现学生信息表的创建和数据插入
新建学生信息表并插入数据信息
#include "widget.h"
#include "ui_widget.h"
Widget::Widget(QWidget *parent)
: QWidget(parent)
, ui(new Ui::Widget)
{
ui->setupUi(this);
QStringList names;//创建学生名字信息表
names <<"小A" << "小B"<< "小C"<< "大D"<< "大E"<< "大F"<< "中G"<< "中H"<< "中I"<< "王J"<< "王K"<< "毛L"<< "毛M"<< "黄N"<< "黄O"<< "思P"<< "HQ"<< "GYUR";
QStringList clases;//创建学生班级信息表
clases <<"高一、一班"<<"高一、二班"<<"高一、三班"<<"高二、一班"<<"高二、二班"<<"高二、三班"<<"高三、一班"<<"高三、二班"<<"高三、三班";
//创建数据库
QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");//创建SQLITE数据库，使用缺省连接
//设置数据库文件名
db.setDatabaseName("MySqlit_db");
//打开数据库
if(!db.open())//打开失败
qDebug()<< db.lastError().text();//输出错误信息
else //打开成功
{
qDebug()<< "数据库打开成功";
//数据库操作
//1、指定数据库连接
QSqlQuery query(db);//连接数据库db
query.exec("DROP TABLE students");//清空一下表students，防止数据库中有students表从而创建失败
//2、创建一个学生信息表
query.exec("CREATE TABLE students("
"id INTEGER PRIMARY KEY AUTOINCREMENT,"
"name VARCHAR(40) NOT NULL,"
"score INTEGER NOT NULL,"
"class VARCHAR(40) NOT NULL)");
qDebug()<< "创建学生信息表成功（id，name，score，class）";
//3、为每一列标题添加绑定值
query.prepare("INSERT INTO students (name, score, class)"
"VALUES (:name, :score, :class)");
//4、插入对应数据
foreach (QString name, names) {
query.bindValue(":name", name);//向绑定值中加入名字
query.bindValue(":score", rand()%101);//随机获取0~101的值传入成绩中
query.bindValue(":class", clases);//随机获取班级，并传入绑定值中
//5、将对应数据加入数据库
query.exec();//执行绑定的操作
}
}
} 步伐运行效果：
https://i-blog.csdnimg.cn/direct/6ca40ecf22394ebfb41654cf6dd2d8a2.png
使用SQLite Expert Personal 3软件查看数据库学生信息表如下：
https://i-blog.csdnimg.cn/direct/13ba392b268a4d74bc528502f023ff0b.png
三、QT上使用控件QTableWidget举行表现
1、拖动QTableWidget控件到ui界面、拖动一个QPushButton控件来实现查询操作
https://i-blog.csdnimg.cn/direct/b897f7f1fa0f4ea69c194fdc303b67dc.png
2、点击按键转到槽编写函数实现查询功能
因为此处需要使用数据库db，以是将数据库界说为全局变量方便一些；直接在.h文件里面界说：QSqlDatabase db;//数据库对象，之后.cpp文件直接使用db就行；
（1）、查询所有对象
#include "widget.h"
#include "ui_widget.h"
Widget::Widget(QWidget *parent)
: QWidget(parent)
, ui(new Ui::Widget)
{
ui->setupUi(this);
ui->tabWidget->setTabText(0, "Select");
ui->tabWidget->setTabText(1, "Delete");
ui->pushButton->setText("查询数据库");
QStringList names;//创建学生名字信息表
names <<"小A" << "小B"<< "小C"<< "大D"<< "大E"<< "大F"<< "中G"<< "中H"<< "中I"<< "王J"<< "王K"<< "毛L"<< "毛M"<< "黄N"<< "黄O"<< "思P"<< "HQ"<< "GYUR";
QStringList clases;//创建学生班级信息表
clases <<"高一、一班"<<"高一、二班"<<"高一、三班"<<"高二、一班"<<"高二、二班"<<"高二、三班"<<"高三、一班"<<"高三、二班"<<"高三、三班";
//创建数据库
db = QSqlDatabase::addDatabase("QSQLITE");//创建SQLITE数据库，使用缺省连接
//设置数据库文件名
db.setDatabaseName("MySqlit_db");
//打开数据库
if(!db.open())//打开失败
qDebug()<< db.lastError().text();//输出错误信息
else //打开成功
{
qDebug()<< "数据库打开成功";
//数据库操作
//1、指定数据库连接
QSqlQuery query(db);//连接数据库db
query.exec("DROP TABLE students");//清空一下表students，防止数据库中有students表从而创建失败
//2、创建一个学生信息表
query.exec("CREATE TABLE students("
"id INTEGER PRIMARY KEY AUTOINCREMENT,"
"name VARCHAR(40) NOT NULL,"
"score INTEGER NOT NULL,"
"class VARCHAR(40) NOT NULL)");
qDebug()<< "创建学生信息表成功（id，name，score，class）";
//3、为每一列标题添加绑定值
query.prepare("INSERT INTO students (name, score, class)"
"VALUES (:name, :score, :class)");
//4、插入对应数据
foreach (QString name, names) {
query.bindValue(":name", name);//向绑定值中加入名字
query.bindValue(":score", rand()%101);//随机获取0~101的值传入成绩中
query.bindValue(":class", clases);//随机获取班级，并传入绑定值中
//5、将对应数据加入数据库
query.exec();//执行绑定的操作
}
}
//设置控件QTableWidget属性
ui->tableWidget->setEditTriggers(QAbstractItemView::NoEditTriggers); // 禁止编辑单元格内容
}
Widget::~Widget()
{
delete ui;
}
//点击按钮查询数据库并显示查询到的所有信息
void Widget::on_pushButton_clicked()
{
QSqlQuery query(db);
if(!query.exec("SELECT * FROM students"))//如果查询失败
{
qDebug()<<"查询学生信息表失败"<<db.lastError().text();
}
else//查询成功
{
int num = 0;
//初始化tableWidget控件
ui->tableWidget->setColumnCount(4);//设置控件为4列
ui->tableWidget->setHorizontalHeaderLabels(QStringList() << "id" << "name" << "score" << "class");
while(query.next())
{
//存储数据
QString id = query.value(0).toString();
QString name = query.value(1).toString();
QString score = query.value(2).toString();
QString class1 = query.value(3).toString();
QTableWidgetItem *item0 = new QTableWidgetItem(id);
QTableWidgetItem *item1 = new QTableWidgetItem(name);
QTableWidgetItem *item2 = new QTableWidgetItem(score);
QTableWidgetItem *item3 = new QTableWidgetItem(class1);
// 设置数据内容居中对齐
item0->setTextAlignment(Qt::AlignCenter);
item1->setTextAlignment(Qt::AlignCenter);
item2->setTextAlignment(Qt::AlignCenter);
item3->setTextAlignment(Qt::AlignCenter);
ui->tableWidget->setRowCount(num+1);//设置对应行数
//加载数据到控件中显示
ui->tableWidget->setItem(num, 0, item0);
ui->tableWidget->setItem(num, 1, item1);
ui->tableWidget->setItem(num, 2, item2);
ui->tableWidget->setItem(num, 3, item3);
num++;
}
}
}
https://i-blog.csdnimg.cn/direct/0205027ed561474bb6ac45a2ded7ac4d.gif
（2）、查询特定对象
https://i-blog.csdnimg.cn/direct/e8b6ace080af49e2a31b76df8040b657.gif
//查询符合条件的项目
void Widget::on_pushButton_2_clicked()
{
if(ui->lineEdit->text().isEmpty())
{
QMessageBox mesbox;
mesbox.setText("请输入内容！！！");
mesbox.exec();
}
else
{
QString info = ui->lineEdit->text();
QSqlQuery query;
if(ui->comboBox->currentIndex() == 0)
{
query.prepare("select * from students where name=:info");
query.bindValue(":info", info);
if(query.exec())
{
int num = 0;
//初始化tableWidget控件
ui->tableWidget->setColumnCount(4);//设置控件为4列
ui->tableWidget->setHorizontalHeaderLabels(QStringList() << "id" << "name" << "score" << "class");
while(query.next())
{
//存储数据
QString id = query.value(0).toString();
QString name = query.value(1).toString();
QString score = query.value(2).toString();
QString class1 = query.value(3).toString();
QTableWidgetItem *item0 = new QTableWidgetItem(id);
QTableWidgetItem *item1 = new QTableWidgetItem(name);
QTableWidgetItem *item2 = new QTableWidgetItem(score);
QTableWidgetItem *item3 = new QTableWidgetItem(class1);
// 设置数据内容居中对齐
item0->setTextAlignment(Qt::AlignCenter);
item1->setTextAlignment(Qt::AlignCenter);
item2->setTextAlignment(Qt::AlignCenter);
item3->setTextAlignment(Qt::AlignCenter);
ui->tableWidget->setRowCount(num+1);//设置对应行数
//加载数据到控件中显示
ui->tableWidget->setItem(num, 0, item0);
ui->tableWidget->setItem(num, 1, item1);
ui->tableWidget->setItem(num, 2, item2);
ui->tableWidget->setItem(num, 3, item3);
num++;
}
}
else
{
qDebug()<<query.lastError().text();
}
}
if(ui->comboBox->currentIndex() == 1)
{
query.prepare("select * from students where class=:info");
query.bindValue(":info", info);
if(query.exec())
{
int num = 0;
//初始化tableWidget控件
ui->tableWidget->setColumnCount(4);//设置控件为4列
ui->tableWidget->setHorizontalHeaderLabels(QStringList() << "id" << "name" << "score" << "class");
while(query.next())
{
//存储数据
QString id = query.value(0).toString();
QString name = query.value(1).toString();
QString score = query.value(2).toString();
QString class1 = query.value(3).toString();
QTableWidgetItem *item0 = new QTableWidgetItem(id);
QTableWidgetItem *item1 = new QTableWidgetItem(name);
QTableWidgetItem *item2 = new QTableWidgetItem(score);
QTableWidgetItem *item3 = new QTableWidgetItem(class1);
// 设置数据内容居中对齐
item0->setTextAlignment(Qt::AlignCenter);
item1->setTextAlignment(Qt::AlignCenter);
item2->setTextAlignment(Qt::AlignCenter);
item3->setTextAlignment(Qt::AlignCenter);
ui->tableWidget->setRowCount(num+1);//设置对应行数
//加载数据到控件中显示
ui->tableWidget->setItem(num, 0, item0);
ui->tableWidget->setItem(num, 1, item1);
ui->tableWidget->setItem(num, 2, item2);
ui->tableWidget->setItem(num, 3, item3);
num++;
}
}
else
{
qDebug()<<query.lastError().text();
}
}
if(ui->comboBox->currentIndex() == 2)
{
query.prepare("select * from students where score=:info");
query.bindValue(":info", info);
if(query.exec())
{
int num = 0;
//初始化tableWidget控件
ui->tableWidget->setColumnCount(4);//设置控件为4列
ui->tableWidget->setHorizontalHeaderLabels(QStringList() << "id" << "name" << "score" << "class");
while(query.next())
{
//存储数据
QString id = query.value(0).toString();
QString name = query.value(1).toString();
QString score = query.value(2).toString();
QString class1 = query.value(3).toString();
QTableWidgetItem *item0 = new QTableWidgetItem(id);
QTableWidgetItem *item1 = new QTableWidgetItem(name);
QTableWidgetItem *item2 = new QTableWidgetItem(score);
QTableWidgetItem *item3 = new QTableWidgetItem(class1);
// 设置数据内容居中对齐
item0->setTextAlignment(Qt::AlignCenter);
item1->setTextAlignment(Qt::AlignCenter);
item2->setTextAlignment(Qt::AlignCenter);
item3->setTextAlignment(Qt::AlignCenter);
ui->tableWidget->setRowCount(num+1);//设置对应行数
//加载数据到控件中显示
ui->tableWidget->setItem(num, 0, item0);
ui->tableWidget->setItem(num, 1, item1);
ui->tableWidget->setItem(num, 2, item2);
ui->tableWidget->setItem(num, 3, item3);
num++;
}
}
else
{
qDebug()<<query.lastError().text();
}
}
}
} （3）、删除所有数据
//删除所有数据
void Widget::on_pushButton_4_clicked()
{
QSqlQuery query(db);
if(!query.exec("delete FROM students"))//如果删除失败
qDebug()<<"删除所有学生信息失败"<<db.lastError().text();
else//删除成功
{
QMessageBox mybox;
mybox.setText("删除成功，请重新查询数据库查看");
mybox.exec();
}
} （4）、删除特定对象
//删除特定项void Widget::on_pushButton_3_clicked(){ if(ui->lineEdit->text().isEmpty()) { QMessageBox mesbox; mesbox.setText("请输入内容！！！"); mesbox.exec(); } else { QString info = ui->lineEdit->text(); QSqlQuery query; if(ui->comboBox->currentIndex() == 0) { query.prepare("delete from students where name=:info"); query.bindValue(":info", info); if(query.exec()) { qDebug()<<"删除乐成项目name = "<<info; } else { qDebug()<<query.lastError().text(); } } if(ui->comboBox->currentIndex() == 1) { query.prepare("delete from students where class=:info"); query.bindValue(":info", info); if(query.exec()) { qDebug()<<"删除乐成项目 class = "<<info; } else { qDebug()<<query.lastError().text(); } } if(ui->comboBox->currentIndex() == 2) { query.prepare("delete from students where score=:info"); query.bindValue(":info", info); if(query.exec()) { qDebug()<<"删除乐成项目 score = "<<info; } else { qDebug()<<query.lastError().text(); } } }}//删除所有数据
void Widget::on_pushButton_4_clicked()
{
QSqlQuery query(db);
if(!query.exec("delete FROM students"))//如果删除失败
qDebug()<<"删除所有学生信息失败"<<db.lastError().text();
else//删除成功
{
QMessageBox mybox;
mybox.setText("删除成功，请重新查询数据库查看");
mybox.exec();
}
} https://i-blog.csdnimg.cn/direct/70f0c4aad5514630b53d5d19e6625101.gif
（5）、插入对象
https://i-blog.csdnimg.cn/direct/fefccf2dbda94711b9ca763affcb9bf1.gif
//插入数据
void Widget::on_pushButton_5_clicked()
{
QSqlQuery query(db);
QString str = "INSERT INTO students VALUES(:id, :name, :score,:class)";
query.prepare(str);
query.bindValue(":id", ui->le_id->text().toInt());
query.bindValue(":name", ui->le_name->text());
query.bindValue(":score", ui->le_score->text().toInt());
query.bindValue(":class", ui->le_class->text());
if(query.exec())
qDebug()<<"数据插入成功";
else
qDebug()<<query.lastError().text();
}
（6）、更新特定对象
https://i-blog.csdnimg.cn/direct/35216bff03874835819daaa18d37a676.gif
//更新数据
void Widget::on_pushButton_6_clicked()
{
QString info1 = ui->le_updata_1->text();
QString info2 = ui->le_updata_2->text();
QSqlQuery query(db);
//根据id去更新
if(ui->comboBox_2->currentIndex() == 0)//id
{
if(ui->comboBox_3->currentIndex() == 0)//name
{
query.prepare("update students set name=:name where id=:id1");
query.bindValue(":name", info2);
query.bindValue(":id1", info1.toInt());
if(query.exec())
qDebug()<<info1<<" name更新成功";
else
qDebug()<<query.lastError().text();
}
if(ui->comboBox_3->currentIndex() == 1)//score
{
query.prepare("update students set score=:score where id=:id1");
query.bindValue(":score", info2.toInt());
query.bindValue(":id1", info1.toInt());
if(query.exec())
qDebug()<<info1<<" score更新成功";
else
qDebug()<<query.lastError().text();
}
if(ui->comboBox_3->currentIndex() == 2)//class
{
query.prepare("update students set class=:class where id=:id1");
query.bindValue(":class", info2);
query.bindValue(":id1", info1.toInt());
if(query.exec())
qDebug()<<info1<<" class更新成功";
else
qDebug()<<query.lastError().text();
}
}
//根据name去更新
if(ui->comboBox_2->currentIndex() == 1)//name
{
if(ui->comboBox_3->currentIndex() == 0)//name
{
query.prepare("update students set name=:name where name=:name1");
query.bindValue(":name", info2);
query.bindValue(":name1", info1);
if(query.exec())
qDebug()<<info1<<" name更新成功";
else
qDebug()<<query.lastError().text();
}
if(ui->comboBox_3->currentIndex() == 1)//score
{
query.prepare("update students set score=:score where name=:name");
query.bindValue(":score", info2.toInt());
query.bindValue(":name", info1);
if(query.exec())
qDebug()<<info1<<" score更新成功";
else
qDebug()<<query.lastError().text();
}
if(ui->comboBox_3->currentIndex() == 2)//class
{
query.prepare("update students set class=:class where name=:name");
query.bindValue(":class", info2);
query.bindValue(":name", info1);
if(query.exec())
qDebug()<<info1<<" class更新成功";
else
qDebug()<<query.lastError().text();
}
}
//根据score去更新
if(ui->comboBox_2->currentIndex() == 2)//score
{
if(ui->comboBox_3->currentIndex() == 0)//name
{
query.prepare("update students set name=:name where score=:score");
query.bindValue(":name", info2);
query.bindValue(":score", info1.toInt());
if(query.exec())
qDebug()<<info1<<" name更新成功";
else
qDebug()<<query.lastError().text();
}
if(ui->comboBox_3->currentIndex() == 1)//score
{
query.prepare("update students set score=:score where score=:score1");
query.bindValue(":score", info2.toInt());
query.bindValue(":score1", info1.toInt());
if(query.exec())
qDebug()<<info1<<" score更新成功";
else
qDebug()<<query.lastError().text();
}
if(ui->comboBox_3->currentIndex() == 2)//class
{
query.prepare("update students set class=:class where score=:score");
query.bindValue(":class", info2);
query.bindValue(":score", info1.toInt());
if(query.exec())
qDebug()<<info1<<" class更新成功";
else
qDebug()<<query.lastError().text();
}
}
//根据class去更新
if(ui->comboBox_2->currentIndex() == 3)//class
{
if(ui->comboBox_3->currentIndex() == 0)//name
{
query.prepare("update students set name=:name where calss=:class");
query.bindValue(":name", info2);
query.bindValue(":class", info1);
if(query.exec())
qDebug()<<info1<<" class更新成功";
else
qDebug()<<query.lastError().text();
}
if(ui->comboBox_3->currentIndex() == 1)//score
{
query.prepare("update students set score=:score where class=:class");
query.bindValue(":score", info2.toInt());
query.bindValue(":class", info1);
if(query.exec())
qDebug()<<info1<<" class更新成功";
else
qDebug()<<query.lastError().text();
}
if(ui->comboBox_3->currentIndex() == 2)//class
{
query.prepare("update students set class=:class where class=:class1");
query.bindValue(":class", info2);
query.bindValue(":class1", info1);
if(query.exec())
qDebug()<<info1<<" class更新成功";
else
qDebug()<<query.lastError().text();
}
}
} 四、总体代码
1、widget.cpp文件
#include "widget.h"#include "ui_widget.h"Widget::Widget(QWidget *parent) : QWidget(parent) , ui(new Ui::Widget){ ui->setupUi(this); ui->tabWidget->setTabText(0, "Select"); ui->tabWidget->setTabText(1, "Delete"); ui->pushButton->setText("查询数据库"); QStringList names;//创建学生名字信息表 names <<"小A" << "小B"<< "小C"<< "大D"<< "大E"<< "大F"<< "中G"<< "中H"<< "中I"<< "王J"<< "王K"<< "毛L"<< "毛M"<< "黄N"<< "黄O"<< "思P"<< "HQ"<< "GYUR"; QStringList clases;//创建学生班级信息表 clases <<"高一、一班"<<"高一、二班"<<"高一、三班"<<"高二、一班"<<"高二、二班"<<"高二、三班"<<"高三、一班"<<"高三、二班"<<"高三、三班"; //创建数据库 db = QSqlDatabase::addDatabase("QSQLITE");//创建SQLITE数据库，使用缺省毗连 //设置数据库文件名 db.setDatabaseName("MySqlit_db"); //打开数据库 if(!db.open())//打开失败 qDebug()<< db.lastError().text();//输出错误信息 else //打开乐成 { qDebug()<< "数据库打开乐成"; //数据库操作 //1、指定数据库毗连 QSqlQuery query(db);//毗连数据库db query.exec("DROP TABLE students");//清空一下表students，防止数据库中有students表从而创建失败 //2、创建一个学生信息表 query.exec("CREATE TABLE students(" "id INTEGER PRIMARY KEY AUTOINCREMENT," "name VARCHAR(40) NOT NULL," "score INTEGER NOT NULL," "class VARCHAR(40) NOT NULL)"); qDebug()<< "创建学生信息表乐成（id，name，score，class）"; //3、为每一列标题添加绑定值 query.prepare("INSERT INTO students (name, score, class)" "VALUES (:name, :score, :class)"); //4、插入对应数据 foreach (QString name, names) { query.bindValue(":name", name);//向绑定值中到场名字 query.bindValue(":score", rand()%101);//随机获取0~101的值传入结果中 query.bindValue(":class", clases);//随机获取班级，并传入绑定值中 //5、将对应数据到场数据库 query.exec();//执行绑定的操作 } } //设置控件QTableWidget属性 ui->tableWidget->setEditTriggers(QAbstractItemView::NoEditTriggers); // 禁止编辑单位格内容}Widget::~Widget(){ delete ui;}//点击按钮查询数据库并表现查询到的所有信息void Widget::on_pushButton_clicked(){ QSqlQuery query(db); if(!query.exec("SELECT * FROM students"))//如果查询失败 { qDebug()<<"查询学生信息表失败"<<db.lastError().text(); } else//查询乐成 { if(query.next()) { int num = 0; //初始化tableWidget控件 ui->tableWidget->setColumnCount(4);//设置控件为4列 ui->tableWidget->setHorizontalHeaderLabels(QStringList() << "id" << "name" << "score" << "class"); do { //存储数据 QString id = query.value(0).toString(); QString name = query.value(1).toString(); QString score = query.value(2).toString(); QString class1 = query.value(3).toString(); QTableWidgetItem *item0 = new QTableWidgetItem(id); QTableWidgetItem *item1 = new QTableWidgetItem(name); QTableWidgetItem *item2 = new QTableWidgetItem(score); QTableWidgetItem *item3 = new QTableWidgetItem(class1); // 设置数据内容居中对齐 item0->setTextAlignment(Qt::AlignCenter); item1->setTextAlignment(Qt::AlignCenter); item2->setTextAlignment(Qt::AlignCenter); item3->setTextAlignment(Qt::AlignCenter); ui->tableWidget->setRowCount(num+1);//设置对应行数 //加载数据到控件中表现 ui->tableWidget->setItem(num, 0, item0); ui->tableWidget->setItem(num, 1, item1); ui->tableWidget->setItem(num, 2, item2); ui->tableWidget->setItem(num, 3, item3); num++; }while(query.next()); } else//未查询到数据 { ui->tableWidget->clear(); ui->tableWidget->setColumnCount(4);//设置控件为4列 ui->tableWidget->setHorizontalHeaderLabels(QStringList() << "id" << "name" << "score" << "class"); QMessageBox mesbox; mesbox.setText("数据库内无内容"); mesbox.exec(); } }}//查询符合条件的项目
void Widget::on_pushButton_2_clicked()
{
if(ui->lineEdit->text().isEmpty())
{
QMessageBox mesbox;
mesbox.setText("请输入内容！！！");
mesbox.exec();
}
else
{
QString info = ui->lineEdit->text();
QSqlQuery query;
if(ui->comboBox->currentIndex() == 0)
{
query.prepare("select * from students where name=:info");
query.bindValue(":info", info);
if(query.exec())
{
int num = 0;
//初始化tableWidget控件
ui->tableWidget->setColumnCount(4);//设置控件为4列
ui->tableWidget->setHorizontalHeaderLabels(QStringList() << "id" << "name" << "score" << "class");
while(query.next())
{
//存储数据
QString id = query.value(0).toString();
QString name = query.value(1).toString();
QString score = query.value(2).toString();
QString class1 = query.value(3).toString();
QTableWidgetItem *item0 = new QTableWidgetItem(id);
QTableWidgetItem *item1 = new QTableWidgetItem(name);
QTableWidgetItem *item2 = new QTableWidgetItem(score);
QTableWidgetItem *item3 = new QTableWidgetItem(class1);
// 设置数据内容居中对齐
item0->setTextAlignment(Qt::AlignCenter);
item1->setTextAlignment(Qt::AlignCenter);
item2->setTextAlignment(Qt::AlignCenter);
item3->setTextAlignment(Qt::AlignCenter);
ui->tableWidget->setRowCount(num+1);//设置对应行数
//加载数据到控件中显示
ui->tableWidget->setItem(num, 0, item0);
ui->tableWidget->setItem(num, 1, item1);
ui->tableWidget->setItem(num, 2, item2);
ui->tableWidget->setItem(num, 3, item3);
num++;
}
}
else
{
qDebug()<<query.lastError().text();
}
}
if(ui->comboBox->currentIndex() == 1)
{
query.prepare("select * from students where class=:info");
query.bindValue(":info", info);
if(query.exec())
{
int num = 0;
//初始化tableWidget控件
ui->tableWidget->setColumnCount(4);//设置控件为4列
ui->tableWidget->setHorizontalHeaderLabels(QStringList() << "id" << "name" << "score" << "class");
while(query.next())
{
//存储数据
QString id = query.value(0).toString();
QString name = query.value(1).toString();
QString score = query.value(2).toString();
QString class1 = query.value(3).toString();
QTableWidgetItem *item0 = new QTableWidgetItem(id);
QTableWidgetItem *item1 = new QTableWidgetItem(name);
QTableWidgetItem *item2 = new QTableWidgetItem(score);
QTableWidgetItem *item3 = new QTableWidgetItem(class1);
// 设置数据内容居中对齐
item0->setTextAlignment(Qt::AlignCenter);
item1->setTextAlignment(Qt::AlignCenter);
item2->setTextAlignment(Qt::AlignCenter);
item3->setTextAlignment(Qt::AlignCenter);
ui->tableWidget->setRowCount(num+1);//设置对应行数
//加载数据到控件中显示
ui->tableWidget->setItem(num, 0, item0);
ui->tableWidget->setItem(num, 1, item1);
ui->tableWidget->setItem(num, 2, item2);
ui->tableWidget->setItem(num, 3, item3);
num++;
}
}
else
{
qDebug()<<query.lastError().text();
}
}
if(ui->comboBox->currentIndex() == 2)
{
query.prepare("select * from students where score=:info");
query.bindValue(":info", info);
if(query.exec())
{
int num = 0;
//初始化tableWidget控件
ui->tableWidget->setColumnCount(4);//设置控件为4列
ui->tableWidget->setHorizontalHeaderLabels(QStringList() << "id" << "name" << "score" << "class");
while(query.next())
{
//存储数据
QString id = query.value(0).toString();
QString name = query.value(1).toString();
QString score = query.value(2).toString();
QString class1 = query.value(3).toString();
QTableWidgetItem *item0 = new QTableWidgetItem(id);
QTableWidgetItem *item1 = new QTableWidgetItem(name);
QTableWidgetItem *item2 = new QTableWidgetItem(score);
QTableWidgetItem *item3 = new QTableWidgetItem(class1);
// 设置数据内容居中对齐
item0->setTextAlignment(Qt::AlignCenter);
item1->setTextAlignment(Qt::AlignCenter);
item2->setTextAlignment(Qt::AlignCenter);
item3->setTextAlignment(Qt::AlignCenter);
ui->tableWidget->setRowCount(num+1);//设置对应行数
//加载数据到控件中显示
ui->tableWidget->setItem(num, 0, item0);
ui->tableWidget->setItem(num, 1, item1);
ui->tableWidget->setItem(num, 2, item2);
ui->tableWidget->setItem(num, 3, item3);
num++;
}
}
else
{
qDebug()<<query.lastError().text();
}
}
}
}//删除特定项void Widget::on_pushButton_3_clicked(){ if(ui->lineEdit->text().isEmpty()) { QMessageBox mesbox; mesbox.setText("请输入内容！！！"); mesbox.exec(); } else { QString info = ui->lineEdit->text(); QSqlQuery query; if(ui->comboBox->currentIndex() == 0) { query.prepare("delete from students where name=:info"); query.bindValue(":info", info); if(query.exec()) { qDebug()<<"删除乐成项目name = "<<info; } else { qDebug()<<query.lastError().text(); } } if(ui->comboBox->currentIndex() == 1) { query.prepare("delete from students where class=:info"); query.bindValue(":info", info); if(query.exec()) { qDebug()<<"删除乐成项目 class = "<<info; } else { qDebug()<<query.lastError().text(); } } if(ui->comboBox->currentIndex() == 2) { query.prepare("delete from students where score=:info"); query.bindValue(":info", info); if(query.exec()) { qDebug()<<"删除乐成项目 score = "<<info; } else { qDebug()<<query.lastError().text(); } } }}//删除所有数据
void Widget::on_pushButton_4_clicked()
{
QSqlQuery query(db);
if(!query.exec("delete FROM students"))//如果删除失败
qDebug()<<"删除所有学生信息失败"<<db.lastError().text();
else//删除成功
{
QMessageBox mybox;
mybox.setText("删除成功，请重新查询数据库查看");
mybox.exec();
}
}//插入数据
void Widget::on_pushButton_5_clicked()
{
QSqlQuery query(db);
QString str = "INSERT INTO students VALUES(:id, :name, :score,:class)";
query.prepare(str);
query.bindValue(":id", ui->le_id->text().toInt());
query.bindValue(":name", ui->le_name->text());
query.bindValue(":score", ui->le_score->text().toInt());
query.bindValue(":class", ui->le_class->text());
if(query.exec())
qDebug()<<"数据插入成功";
else
qDebug()<<query.lastError().text();
}
//更新数据
void Widget::on_pushButton_6_clicked()
{
QString info1 = ui->le_updata_1->text();
QString info2 = ui->le_updata_2->text();
QSqlQuery query(db);
//根据id去更新
if(ui->comboBox_2->currentIndex() == 0)//id
{
if(ui->comboBox_3->currentIndex() == 0)//name
{
query.prepare("update students set name=:name where id=:id1");
query.bindValue(":name", info2);
query.bindValue(":id1", info1.toInt());
if(query.exec())
qDebug()<<info1<<" name更新成功";
else
qDebug()<<query.lastError().text();
}
if(ui->comboBox_3->currentIndex() == 1)//score
{
query.prepare("update students set score=:score where id=:id1");
query.bindValue(":score", info2.toInt());
query.bindValue(":id1", info1.toInt());
if(query.exec())
qDebug()<<info1<<" score更新成功";
else
qDebug()<<query.lastError().text();
}
if(ui->comboBox_3->currentIndex() == 2)//class
{
query.prepare("update students set class=:class where id=:id1");
query.bindValue(":class", info2);
query.bindValue(":id1", info1.toInt());
if(query.exec())
qDebug()<<info1<<" class更新成功";
else
qDebug()<<query.lastError().text();
}
}
//根据name去更新
if(ui->comboBox_2->currentIndex() == 1)//name
{
if(ui->comboBox_3->currentIndex() == 0)//name
{
query.prepare("update students set name=:name where name=:name1");
query.bindValue(":name", info2);
query.bindValue(":name1", info1);
if(query.exec())
qDebug()<<info1<<" name更新成功";
else
qDebug()<<query.lastError().text();
}
if(ui->comboBox_3->currentIndex() == 1)//score
{
query.prepare("update students set score=:score where name=:name");
query.bindValue(":score", info2.toInt());
query.bindValue(":name", info1);
if(query.exec())
qDebug()<<info1<<" score更新成功";
else
qDebug()<<query.lastError().text();
}
if(ui->comboBox_3->currentIndex() == 2)//class
{
query.prepare("update students set class=:class where name=:name");
query.bindValue(":class", info2);
query.bindValue(":name", info1);
if(query.exec())
qDebug()<<info1<<" class更新成功";
else
qDebug()<<query.lastError().text();
}
}
//根据score去更新
if(ui->comboBox_2->currentIndex() == 2)//score
{
if(ui->comboBox_3->currentIndex() == 0)//name
{
query.prepare("update students set name=:name where score=:score");
query.bindValue(":name", info2);
query.bindValue(":score", info1.toInt());
if(query.exec())
qDebug()<<info1<<" name更新成功";
else
qDebug()<<query.lastError().text();
}
if(ui->comboBox_3->currentIndex() == 1)//score
{
query.prepare("update students set score=:score where score=:score1");
query.bindValue(":score", info2.toInt());
query.bindValue(":score1", info1.toInt());
if(query.exec())
qDebug()<<info1<<" score更新成功";
else
qDebug()<<query.lastError().text();
}
if(ui->comboBox_3->currentIndex() == 2)//class
{
query.prepare("update students set class=:class where score=:score");
query.bindValue(":class", info2);
query.bindValue(":score", info1.toInt());
if(query.exec())
qDebug()<<info1<<" class更新成功";
else
qDebug()<<query.lastError().text();
}
}
//根据class去更新
if(ui->comboBox_2->currentIndex() == 3)//class
{
if(ui->comboBox_3->currentIndex() == 0)//name
{
query.prepare("update students set name=:name where calss=:class");
query.bindValue(":name", info2);
query.bindValue(":class", info1);
if(query.exec())
qDebug()<<info1<<" class更新成功";
else
qDebug()<<query.lastError().text();
}
if(ui->comboBox_3->currentIndex() == 1)//score
{
query.prepare("update students set score=:score where class=:class");
query.bindValue(":score", info2.toInt());
query.bindValue(":class", info1);
if(query.exec())
qDebug()<<info1<<" class更新成功";
else
qDebug()<<query.lastError().text();
}
if(ui->comboBox_3->currentIndex() == 2)//class
{
query.prepare("update students set class=:class where class=:class1");
query.bindValue(":class", info2);
query.bindValue(":class1", info1);
if(query.exec())
qDebug()<<info1<<" class更新成功";
else
qDebug()<<query.lastError().text();
}
}
} 2、widget.h文件
#ifndef WIDGET_H#define WIDGET_H#include <QWidget>#include <QtSql/QSqlDatabase>
#include <QtSql/QSqlError>
#include <QtSql/QSqlQuery>
#include <QDebug>#include <QMessageBox>QT_BEGIN_NAMESPACEnamespace Ui {class Widget;}QT_END_NAMESPACEclass Widget : public QWidget{ Q_OBJECTpublic: Widget(QWidget *parent = nullptr); ~Widget();private slots: void on_pushButton_clicked(); void on_pushButton_2_clicked(); void on_pushButton_3_clicked(); void on_pushButton_4_clicked(); void on_pushButton_5_clicked(); void on_pushButton_6_clicked();private: Ui::Widget *ui; QSqlDatabase db;//数据库对象};#endif // WIDGET_H 3、main.cpp文件
#include "widget.h"
#include <QApplication>
int main(int argc, char *argv[])
{
QApplication a(argc, argv);
Widget w;
w.show();
return a.exec();
}
4、.pro文件
QT += core gui
QT += sql
greaterThan(QT_MAJOR_VERSION, 4): QT += widgets
CONFIG += c++17
# You can make your code fail to compile if it uses deprecated APIs.
# In order to do so, uncomment the following line.
#DEFINES += QT_DISABLE_DEPRECATED_BEFORE=0x060000 # disables all the APIs deprecated before Qt 6.0.0
SOURCES += \
main.cpp \
widget.cpp
HEADERS += \
widget.h
FORMS += \
widget.ui
# Default rules for deployment.
qnx: target.path = /tmp/$${TARGET}/bin
else: unix:!android: target.path = /opt/$${TARGET}/bin
!isEmpty(target.path): INSTALLS += target
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]