1. 数据库简介
2. Sqlite数据库
2.1 Sqlite工作原理
2.2 Sqlite主要特性
- ACID: 原子性,一致性, 合理性,持久性
2.3 在ubuntu上安装Sqlite数据库
$ sudo apt-get install sqlite3
3. Sqlite命令行程序(两种)
3.1 sqlite自身配置和格式控制相关指令
$ sqlite3
SQLite version 3.11.0 2016-02-15 17:29:24
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
# 对数据库的增删改查都会被记录到testDB.db这个文件里
sqlite> .open testDB.db
# 如果不加.open testDB.db, 那么所有的操作都是基于内存的, 退出以后就啥也没有了。
# 可以一开始就 sqlite3 testDB.db
.mode, .nullvale 可以直接写到配置文件 ~/.sqliterc
3.2 SQL语句
3.2.1 创建表(如果存在不会重复创建)
3.2.2 删除表
3.2.3 插入数据
3.2.4 删除数据
3.2.5 修改数据
3.2.6 查询数据
- ORDER BY 和 WHERE 配合使用时,放在其后。ASC升序, DASC降序
3.2.7 模糊查询
$ sqlite3 testDB.db
SQLite version 3.11.0 2016-02-15 17:29:24
Enter ".help" for usage hints.
sqlite> CREATE TABLE student
...> (id INT PRIMARY KEY NOT NULL,
...> name TEXT NOT NULL,
...> score REAL NOT NULL);
# 查看所有存在的数据表的名字
sqlite> .table
student
# 查看student数据表创建时信息
sqlite> .schema student
CREATE TABLE student
(id INT PAIMARY KEY NOT NULL,
name TEXT NOT NULL,
score REAL NOT NULL);
# 向表中插入数据
sqlite>
5. 在Qt中使用Sqlite数据库
- 建立和数据库连接以后才能,QSqlQuery query; 然后操作都是针对这个已经连接的数据库来的。
- 对数据库的 增删改都得通过query这个对象来完成。
- 查询用 QSqlQueryModel 获取结果集
5.1 案例
- 记得在工程文件.pro中加sql模块
- 最上面的ID, 升序控件叫ComboBox, 中间显示表格的控件叫tableViewe,用来显示数据库内容
5.1.1 构建框架
- 然后右键按钮控件转到槽,会在头文件和源文件中生成槽函数, 并且会自动connect信号和槽,不用再写connect
5.1.2 代码示例
studentwidget.h
#ifndef STUDENTWIDGET_H
#define STUDENTWIDGET_H
#include <QWidget>
#include <QSqlDatabase> // connect sql database
#include <QSqlQuery> // exec sql
#include <QSqlQueryModel> // get query result set
#include <QSqlError> // get reason of failed
#include <QDebug>
#include <QMessageBox>
namespace Ui {
class StudentWidget;
}
class StudentWidget : public QWidget
{
Q_OBJECT
public:
explicit StudentWidget(QWidget *parent = 0);
~StudentWidget();
private:
// create database
void createDb();
// create table
void createTable();
// query and display table
void queryTable();
private slots:
void on_insertButton_clicked();
void on_deleteButton_clicked();
void on_updateButton_clicked();
void on_sortButton_clicked();
private:
Ui::StudentWidget *ui;
QSqlDatabase db; //establish the connection between Qt and database
QSqlQueryModel model; //save query result set
};
#endif // STUDENTWIDGET_H
studentwidget.cpp
#include "studentwidget.h"
#include "ui_studentwidget.h"
StudentWidget::StudentWidget(QWidget *parent) :
QWidget(parent),
ui(new Ui::StudentWidget)
{
ui->setupUi(this);
createDb();
createTable();
queryTable();
}
StudentWidget::~StudentWidget()
{
delete ui;
}
// create database
void StudentWidget::createDb(){
// add database driver of sqlite
db = QSqlDatabase::addDatabase("QSQLITE");
// set up name of database
db.setDatabaseName("student.db");
// open db
if (db.open() == true){
qDebug() << "create or open database successfully!";
} else {
qDebug() << "failed to create or open database...";
}
}
// create table
void StudentWidget::createTable(){
QSqlQuery query;
QString str = QString("CREATE TABLE student("
"id INT PRIMARY KEY NOT NULL, "
"name TEXT NOT NULL, "
"score REAL NOT NULL);");
if (query.exec(str) == false){
qDebug() << str;
} else {
qDebug() << "create tabel successfully!";
}
}
// query and dispaly result set
void StudentWidget::queryTable(){
QString str = QString("SELECT * FROM student");
model.setQuery(str);
ui->tableView->setModel(&model);
}
// insert
void StudentWidget::on_insertButton_clicked()
{
QSqlQuery query;
int id = ui->idEdit->text().toInt();
QString name = ui->nameEdit->text();
double score = ui->scoreEdit->text().toDouble();
// check the inserted id
if(id==0){
QMessageBox::critical(this, "Error", "illegal id!");
return;
}
// check the inserted name
if(name == ""){
QMessageBox::critical(this, "Error", "illegal name!");
return;
}
// check the inserted score
if(score < 0 || score > 100){
QMessageBox::critical(this, "Error", "illegal score!");
return;
}
QString str = QString("INSERT INTO student VALUES(%1, '%2', %3)").arg(id).arg(name).arg(score);
if(query.exec(str) == false){
qDebug() << str;
} else {
qDebug() << "insert data successfully!";
queryTable();
}
}
// delete (delte data according to id)
void StudentWidget::on_deleteButton_clicked()
{
// are you sure to delte?
if(QMessageBox::question(this, "delte", "are you sure to delte?",
QMessageBox::Yes|QMessageBox::No) == QMessageBox::No){
return;
}
QSqlQuery query;
int id = ui->idEdit->text().toInt();
QString str = QString("DELETE FROM student WHERE id = %1").arg(id);
if(query.exec(str) == false){
qDebug()<<str;
}else{
qDebug()<<"delete data sucessfully!";
queryTable();
}
}
// update (update score according to id)
void StudentWidget::on_updateButton_clicked()
{
QSqlQuery query;
int id = ui->idEdit->text().toInt();
double score = ui->scoreEdit->text().toDouble();
QString str = QString("UPDATE student SET score='%1' WHERE id=%2").arg(score).arg(id);
if(query.exec(str) == false){
qDebug()<<str;
} else{
qDebug()<<"update data sucessfully!";
queryTable();
}
}
// sort (according to id or score)
void StudentWidget::on_sortButton_clicked()
{
// huo qu pai xu lie ming
QString value = ui->valueComboBox->currentText();
// huo qu pai xu fang shi
QString condition;
// same: if (ui->condComboBox->currentText() == "asc")
if(ui->condComboBox->currentIndex() == 0){
condition = "ASC";
} else {
condition = "DESC";
}
QString str = QString("SELECT * FROM student ORDER BY %1 %2").arg(value).arg(condition);
model.setQuery(str);
ui->tableView->setModel(&model);
}