一、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
在.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[rand()%8]);//随机获取班级,并传入绑定值中
//5、将对应数据加入数据库
query.exec();//执行绑定的操作
}
}
}
程序运行结果:
使用SQLite Expert Personal 3软件查看数据库学生信息表如下:
三、QT上使用控件QTableWidget进行显示
1、拖动QTableWidget控件到ui界面、拖动一个QPushButton控件来实现查询操作
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[rand()%8]);//随机获取班级,并传入绑定值中
//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++;
}
}
}
(2)、查询特定对象
//查询符合条件的项目
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();
}
}
(5)、插入对象
//插入数据
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)、更新特定对象
//更新数据
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[rand()%8]);//随机获取班级,并传入绑定值中
//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_NAMESPACE
namespace Ui {
class Widget;
}
QT_END_NAMESPACE
class Widget : public QWidget
{
Q_OBJECT
public:
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