42.QT-QSQLQUERY class Operation SQLite database (creation, query, delete, modification)

August 22, 2021By Simo Sqlite

Qt provides the QtSql module to provide platform-independent SQL-based database operations. What we mean by “platform
independent” includes not only operating system platforms, but also various database platforms. Qt supports the following types of databases:

share picture

QT comes with SQLITE database , No need to install again
QTDS has been moved since Qt4.7 Except

1.QtSql

To use the QtSql module, you need to add this sentence to the .pro file:

QT += sql

2.QSqlDatabase

QSqlDatabase class provides an interface for To access data through a connection. An instance of QSqlDatabase represents a connection. The connection provides access to the database through one of the supported database drivers, which is derived from QSqlDriver.

2.1 An example of creating a database is as follows

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

db.setDatabaseName("scooters.dat"); //If this directory If there is no such file, it will be generated in this directory, otherwise link to the file

 if (!db.open()) {

 QMessageBox::warning(0, QObject::tr("< /span>Database Error" ),

 db.lastError().text());

 return false;

 }

After compiling and running, you can see that the file has been created :

share pictures

After successful creation, the file the default is empty, and then you can use the QSqlQuery class to operate the database, the QSqlQuery class uses SQL statements, if you only need to use high-level data
library interface (do not care about SQL syntax), we can choose QSqlTableModel and
QSqlRelationalTableModel(introduced in subsequent chapters). In this chapter, we introduce QSqlQuery class, how to use SQL syntax.

3.QSqlQueryClass introduction

through the exec() member function to execute DML (data manipulation language) statements, such as SELECT, INSERT, UPDATE and DELETE, and DDL (data definition language) statements, etc..

For example:

QSqlQuery query;

query.exec("DROP TABLE students"); //Delete the name Students’ table

4. Next , Let’s talk about how to import data

Create a table:

query.exec("CREATE TABLE students ("

 "id INTEGER PRIMARY KEY AUTOINCREMENT, "

 "name VARCHAR(40) NOT NULL, "

 " score INTEGER NOT NULL, "

 "class VARCHAR(40) NOT NULL)");

 //Create a students table with the titles id, name, score, class

” PRIMARY KEY AUTOINCREMENT ,”: means that the column is an integer increment, if it is empty, it will automatically fill in 1, and then each row below will automatically +1, PRIMARY KEY means that this column is used as the primary key of the list, It can easily get a row of data

” INTEGER “: means the column is a signed integer

” VARCHAR(40) “: means that the column is a variable-length character string, by default it can only store English and numbers or utf-8, up to Store 40 bytes.

“NOT NULL “: means that the content of the column is not empty

Import data:

query.exec( "INSERT INTO students (name, score,class) "

 "VALUES ('小张', 85,'Early 2-1 class') ");

 //to (name, score,class) in the students table Insert a piece of data under the heading "小张", 85, "Early 2-1 class"

after adding As shown in the figure below:

share picture

5. Import libraries in bulk

If we need a large string of data When importing, you can also use prepare() to bind the value, and then use bindValue()Add data to the bound value

The sample code is as follows:

QStringList names;

names<<"Small A"<<"Little B "<<"Small C "<<"Little D"<<"Little E"<<"Little F"<<" span>Little G"

 <<"Little H"<<"Little I"<<"Little G< span style="color: #800000;">"<<"小K< /span>"<<"Small L"<<"Little M"<<"Little N";



QStringList clases;

clases<<"First 1-1 class< span style="color: #800000;">"<<"初1- Class 2"<<"Early 1-3 classes"<<"< span style="color: #800000;">First 2-1 class"

 <<"Early 2-2 class"<<"Early 2-3 classes"<<"Early Class 3-1"<<"Beginning Class 3-2"<<"Beginning Class 3-3";



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

db.setDatabaseName("students.dat"); //In this directory Generate below

QSqlQuery query;

query.exec("DROP TABLE students"); //Clear it first Table

query.exec("CREATE TABLE students (< span style="color: #800000;">"

 "id INTEGER PRIMARY KEY AUTOINCREMENT, "

 "name VARCHAR(40) NOT NULL, "

 " score INTEGER NOT NULL, "

 "class VARCHAR(40) NOT NULL)");

 //Create a students table



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

 "VALUES (:name, :score, :class)");

 //Add binding values for each column header



 foreach (QString name, names) //Get each name from the names table

 {

 query.bindValue(":name", name); //to tie Add the name to the fixed value

 query.bindValue(":score", (qrand()% 101)); //Achievements

 query.bindValue(":class", clases[qrand()%clases.length()] ); //Class

 query.exec(); //Add to the library

 }

After running, open students.dat through SQLite tool, as shown below:

share picture

6. Inquiry form content

We are right The students.dat file generated in the above figure is used to query the content, you need to use WHERE strong> Keyword implementation.

Example -Query students whose score value is between 60 and 80:

query.exec("SELECT * FROM students WHERE score >= 60 AND score <= 80;");
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();

qDebug()<classs;
}

Run printing:

Of course there are other sentences, such as: p>

"SELECT * FROM students WHERE score >= 80 OR class =='Early Class 3-3';"
//The judgement score is greater than or equal to 80, or the class is junior 3-3 class

Print as shown below:

"SELECT * FROM students WHERE class GLOB'*3-3*';"
// GLOB stands for wildcard, matching class names with "3-3"

Print as shown below:

7. Delete table content

There are 3 sentences to delete table content:

DROP: It is used to delete the entire table, and the table structure will also be deleted. After deletion, you can only use CREATE TABLE to recreate Table

TRUNCATE: There is no such statement in SQLite, but in MySQL, it is used to clarify the data in the table, but the table structure will not be deleted.

DELETE: Delete some records, and the table structure will not be deleted, the deletion speed is slower than the above two statements, you can cooperate WHERE to delete a specified row

Example 1

query.exec("DELETE FROM students"); //Delete all in the students table Content

After deleting, it will look like the picture below:

Example 2-Delete a line with id=3

< div class="code">

query.exec("DELETE FROM students WHERE id = 3 ");

Before deleting:

After deleting:





8. Change the content of the table

To change the content of the table, the following two sentences are generally used:

UPDATE: Used to modify the contents of the table, you can specify the modification through the WHERE statement

ALTER TABLE: Used to rename Table, or add a new column to an existing table

8.1 ALTER Example

Example 1

query.exec(" span>ALTER TABLE students RENAME TO new_students"); //Rename students to new_students

As shown in the figure below after running:

Example 2

query .exec("ALTER TABLE new_students ADD COLUMN result VARCHAR(10)");
//Add a new column to the new_students table with the title as results , The content format is VARCHAR

After running, it is as shown in the figure below:

8.2 UPDATE example

Example 1-Modify a column directly without using WHERE

query.exec("UPDATE new_students SET score = 100, name ='小A'");
//Edit the content of the column where the score and name are located

After modification, it looks like the following figure:

p>

Example 2-Use WHERE to determine which is less than 60 Set as unqualified, otherwise set as qualified

query.exec("UPDATE new_students SET result='unqualified' WHERE score<60 ");
query.exec("UPDATE new_students SET result='qualified' WHERE score>=60 ");

Before modification, as shown in the figure below:

After modification:

QT += sql

QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
db.setDatabaseName("scooters.dat"); //If this directory If there is no such file, it will be generated in this directory, otherwise link to the file
if (!db.open()) {
QMessageBox::warning(0, QObject::tr("Database Error" ),
db.lastError().text());
return false;
}

QSqlQuery query;
query.exec("DROP TABLE students"); //Delete the name students' table

query.exec("CREATE TABLE students ("
"id INTEGER PRIMARY KEY AUTOINCREMENT, "
"name VARCHAR(40) NOT NULL, "
" score INTEGER NOT NULL, "
"class VARCHAR(40) NOT NULL)");
//Create a students table with the titles id, name, score, class

query.exec("INSERT INTO students (name, score,class) "
"VALUES ('小张', 85,'Early 2-1 class') ");
//to (name, score,class) in the students table Insert an item of data under the heading "Xiao Zhang", 85, "Early 2-1 Class"

QStringList names;
names<<"Small A"<<"Little B "<<"Small C "<<"Little D"<<"Little E"<<"Little F"<<" span>Little G"
<<"Little H"<<"Little I"<<"Little G"<<"小K"<<"Small L"<<"Little M"<<"Little N";

QStringList clases;
clases<<"First 1-1 class"<<"初1- Class 2"<<"Early 1-3 classes"<<"First 2-1 class"
<<"Early 2-2 class"<<"Early 2-3 classes"<<"Early Class 3-1"<<"Beginning Class 3-2"<<"Beginning Class 3-3";

QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE ");
db.setDatabaseName("students.dat"); //在本目录下生成
QSqlQuery query;
query.exec("DROP TABLE students"); //先清空一下表
query.exec("CREATE TABLE students ("
"id INTEGER PRIMARY KEY AUTOINCREMENT, "
"name VARCHAR(40) NOT NULL, "
" score INTEGER NOT NULL, "
"class VARCHAR(40) NOT NULL)");
//创建一个students表

query.prepare("INSERT INTO students (name, score,class) "
"VALUES (:name, :score, :class)");
//为每一列标题添加绑定值

foreach (QString name, names) //从names表里获取每个名字
{
query.bindValue(":name", name); //向绑定值里加入名字
query.bindValue(":score", (qrand() % 101)); //成绩
query.bindValue(":class", clases[qrand()%clases.length()] ); //班级
query.exec(); //加入库中
}

query.exec("SELECT * FROM students WHERE score >= 60 AND score <= 80;");
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();

qDebug()<classs;
}

"SELECT * FROM students WHERE score >= 80 OR class == ‘初3-3班‘;"
//判断成绩大于等于80,或者班级为初3-3班的

"SELECT * FROM students WHERE class GLOB ‘*3-3*‘;"
// GLOB表示通配符,匹配班级带有"3-3"的名字

query.exec("DELETE FROM students"); //删除students表里所有内容

query.exec("DELETE FROM students WHERE id = 3");

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

Leave a Comment Cancel reply