QT 数据库QSQLITE使用

1. window安装可视化软件SQLite Expert Professional 5 - 64bit

2. 程序

pro文件中添加 QT       += sql

widget.h

#ifndef WIDGET_H
#define WIDGET_H

#include <QWidget>

#include <QtSql/QSqlDatabase>
#include <QtSql/QSqlError>
#include <QtSql/QtSql>

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();

private:
    Ui::Widget *ui;

    QSqlDatabase d;
    QSqlQuery query;
};
#endif // WIDGET_H

widget.cpp

#include "widget.h"
#include "ui_widget.h"

#include <QMessageBox>

Widget::Widget(QWidget *parent)
    : QWidget(parent)
    , ui(new Ui::Widget)
{
    ui->setupUi(this);

    d = QSqlDatabase::addDatabase("QSQLITE");

    d.setDatabaseName(QApplication::applicationDirPath() + "/scooters.db");
    if(!d.open()) {
        QMessageBox::warning(NULL, "database error", d.lastError().text());
    }

    query = QSqlQuery(d);
}

Widget::~Widget()
{
    delete ui;
}
//增
void Widget::on_pushButton_clicked()
{
    //清空表
    query.exec("DROP TABLE students");

    //创建一个students表， 标题分别为id,name,score,class
    query.exec("CREATE TABLE students("
               "id INTEGER PRIMARY KEY AUTOINCREMENT, "
               "name VARCHAR(40) NOT NULL, "
               "score INTEGER NOT NULL, "
               "class VARCHAR(40) NOT NULL)");

    //导入单一数据
    query.exec("INSERT INTO students(name, score, class) "
               "VALUES('张三', 85, '初2-1班')");

    //批量导入数据
    QStringList names;
    names<<"小A"<<"小B"<<"小C"<<"小D"<<"小E"<<"小F"<<"小G"<<"小H"<<"小I"<<"小J"<<"小K"<<"小L"<<"小M"<<"小N";
    QStringList classes;
    classes<<"初2-1班"<<"初2-2班"<<"初2-3班"<<"初2-4班";

    query.prepare("INSERT INTO students(name, score, class) "
               "VALUES(:name, :score, :class)");

    foreach(QString name, names) {
        query.bindValue(":name", name);
        query.bindValue(":score", (qrand() % 101));
        query.bindValue(":class", classes[qrand()%classes.length()]);
        query.exec();  //加入库中
    }

    ui->label->setText("数据库数据添加完成");
}
//查
void Widget::on_pushButton_2_clicked()
{
    QString mm;

    query.exec("SELECT * FROM students WHERE score >= 60 AND score <= 100");

    while (query.next()) {
        QString id = query.value(0).toString();
        QString name = query.value(1).toString();
        QString score = query.value(2).toString();
        QString classs = query.value(3).toString();

        mm += id + " " + name + " " + score + " " + classs + "\n";
    }
    ui->label->setText(mm);
}
//删
void Widget::on_pushButton_3_clicked()
{
    query.exec("DELETE FROM students WHERE id = 3;");

    ui->label->setText("删除id=3的学生");
}
//改
void Widget::on_pushButton_4_clicked()
{
    query.exec("ALTER TABLE students RENAME TO new_students");//将students重命名为new_students

    query.exec("ALTER TABLE new_students ADD COLUMN 结果 VARCHAR(10)"); //向new_students中添加新的一列 标题为结果 内容格式为VARCHAR

//    query.exec("UPDATE new_students SET score = 100, name = '小A"); // 修改score和name所在的列内容

    query.exec("UPDATE new_students SET 结果 = '不合格' WHERE score < 60");
    query.exec("UPDATE new_students SET 结果 = '合格' WHERE score >= 60");

    ui->label->setText("修改数据完成");
}

3.运行截图