What you need to know about the Sqlite gameplay in Qt operation, next door Java, Python and a series of uncles who looked at them directly called the experts! ! !

Article Directory

Preface

If you use the sqlite in Qt, you must know how to use the Qt library, so this time is the sorting out of the Qt operation database, and it is also the use of Sqlite to sort out the skills, everyone, I will definitely let you enjoy a different visual feast! ! !

Qt provides the QtSql module to provide platform-independent SQL-based database operations. What we mean by "platform
independence" here includes both operating system platforms and various database platforms. Qt supports the following types of databases:

The document is as follows:

Insert picture description here


The following is the Chinese description of the organized table:

driverdatabase
QDB2IBM DB2 version 7.1 and later versions
QIBASEBorland InterBase
QMYSQLMySQL
QOCIOracle Call Interface
QODBCODBC (including Microsoft's SQL server)
QPSQLPostgreSQL version 7.3 and higher
QSQLITESQLite version 3
QSQLITE2SQLite version 2
QTDSSybase Adaptive Server
  • QT comes with SQLITE database, no need to install
  • QTDS has been removed since Qt4.7

QtSql

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

QT += sql 

QSqlDatabase

The QSqlDatabase class provides an interface for accessing 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 .

An example of creating a database is as follows

Check the connection, add the database driver, set the database name, database login user name, and password.

QSqlDatabase database;
if (QSqlDatabase::contains("qt_sql_default_connection"))
{
    database = QSqlDatabase::database("qt_sql_default_connection");
}
else
{
    database = QSqlDatabase::addDatabase("QSQLITE");
    database.setDatabaseName("MyDataBase.db");
    database.setUserName("BoFengShuiMeng");
    database.setPassword("123456");
}

Explanation of the above code:
(1) In the first line, a QSqlDatabase object is created, and subsequent operations will use this object.

(2) The if statement is used to check whether the specified connection (connection) exists. The connection name specified here is qt_sql_default_connection, which is the default connection name of Qt. In actual use, this name can be chosen arbitrarily. If it is judged that this connection already exists, the QSqlDatabase::contains() function returns true. At this point, enter the first branch, and QSqlDatabase::database() returns this connection.

(3) If this connection does not exist, enter the else branch, you need to create a connection and add a database. In the first line of the else branch, the parameter QSQLITE of addDatabase() is the driver name corresponding to SQLite and cannot be changed. And it should be noted that the second parameter of addDatabase() is omitted, and the default parameter of the second parameter is the Qt default connection name qt_sql_default_connection mentioned above. If you need to use a custom connection name (if the program needs to handle multiple database files), you should add a second parameter, such as

database = QSqlDatabase::addDatabase("QSQLITE", "my_sql_connection);

At this time, if you need to determine whether the my_sql_connection connection exists in another place , you should use

if (QSqlDatabase::contains("my_sql_connection"))。

(4) In the second line of the else branch, the parameter of setDatabaseName() is the database file name. If this database does not exist, it will be automatically created during subsequent operations; if it already exists, subsequent operations will be performed on the existing database.

(5) Two lines after the else branch, set the user name and password. User name and password can be taken at will, or can be omitted.

QSqlQuery class introduction

Execute DML (Data Manipulation Language) statements, such as SELECT, INSERT, UPDATE and DELETE , and DDL (Data Definition Language) statements, through the exec() member function .

such as:

QSqlQuery query;
query.exec("DROP TABLE students");    //删除名为students的表

Open the database

Use **open()** to open the database and judge whether it is successful. Note that when checking whether the connection exists in the first step, if the connection exists, when the connection is returned, the database will be opened by default

if (!database.open())
{
    qWarning() << "Error: Failed to connect database." << database.lastError();
}
else
{
    // do something
}

If the opening is successful, enter the else branch. Operations on the database need to be carried out in the else branch.

Close the database

After the database operation is complete, it is best to close it.

database.close();

How to operate the database

The QSqlQuery class is required to operate the database, and an object must be defined before the operation. The following examples illustrate the method of operation. The operation requires the use of SQLite statements. Several examples in this article will use several commonly used statements. For specific information about SQLite statements, please refer to SQLite related materials.

Create table

Create a table named students. The table contains three columns. The first column is the id, the second column is the name, the third column is the score, and the fourth column is the class.

QSqlQuery sql_query(database);
sql_query.exec("create table if not exists students ("
                   "id INTEGER PRIMARY KEY AUTOINCREMENT, "
                   "name VARCHAR(40) NOT NULL, "
                   "score INTEGER NOT NULL, "
                   "class VARCHAR(40) NOT NULL)");
                //创建一个students表,标题分别为id、name、score、class
///或者这样写
QString create_sql = "create table if not exists students ("
                   "id INTEGER PRIMARY KEY AUTOINCREMENT, "
                   "name VARCHAR(40) NOT NULL, "
                   "score INTEGER NOT NULL, "
                   "class VARCHAR(40) NOT NULL)";
sql_query.prepare(create_sql);
if(!sql_query.exec())
{
    qWarning()<< "Error: Fail to create table." << sql_query.lastError();
}
else
{
    qDebug() << "Table created!";
}

create table if not exists: Indicates that if the table exists, it will not be created, otherwise the table will be created

"PRIMARY KEY AUTOINCREMENT": indicates 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 the column is used as the primary key of the list, through which it can be easily Get a row of data

"INTEGER": indicates that the column is a signed integer

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

"NOT NULL": Indicates that the content of the column is not empty

Insert data

In the table just created, insert a row of data.

Insert directly:

query.exec("INSERT INTO students (name, score,class) "
               "VALUES ('小张', 85, '初2-1班')");  
              //向students表里的(name, score,class)标题下插入一项数据'小张', 85, '初2-1班'

Use placeholders? Insert

QString insert_sql = "INSERT INTO students (name, score,class) values (?, ?, ?)";
sql_query.prepare(insert_sql);
sql_query.addBindValue('小张');
sql_query.addBindValue(85);
sql_query.addBindValue('初2-1班');
if(!sql_query.exec())
{
    qWarning() << sql_query.lastError();
}
else
{
    qDebug() << "inserted finish!";
}

Use placeholder: XXX insert


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

sql_query.bindValue(":name", '小张');                      //向绑定值里加入名字
sql_query.bindValue(":score", 85);      //成绩
sql_query.bindValue(":class",'初2-1班');    //班级
if(!sql_query.exec())
{
    qWarning() << sql_query.lastError();
}
else
{
    qDebug() << "inserted finish!";
}

Large data batch insert data method:

If we have a large string of data that needs to be imported, we can also use prepare() to bind the value, and then add data to the bound value through bindValue()

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

QStringList clases;
clases<<"初1-1班"<<"初1-2班"<<"初1-3班"<<"初2-1班"
         <<"初2-2班"<<"初2-3班"<<"初3-1班"<<"初3-2班"<<"初3-3班";

QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
db.setDatabaseName(QApplication::applicationDirPath()+"/MyDataBase.db");//在本目录下生成

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();               //加入库中
}

Statement:

insert into <table_name> values (value1, value2,…);

insert into is an insert statement, students is the name of the table, and values() is the data to be inserted. Earlier we used different methods to insert data.

  • When inserting data in method 1 , write out the complete sentence directly.
  • When inserting data in method 2 , use addBindValue to replace? In the statement, and the order of replacement is the same as that of addBindValue.
  • When using method three to insert data, use the bindValue(" ", "") function to change the pending variable in the statement to a certain value.

Update data (modify data)

  1. Do not use WHERE, directly modify a column
query.exec("UPDATE students  SET score = 100 , name = '小A'");       
                  //修改score和name所在的列内容

Using bindValue method

QString update_sql = "UPDATE students SET score = :score , name = :name ";
sql_query.prepare(update_sql);
sql_query.bindValue(":score",100);
sql_query.bindValue(":name", "小A");
if(!sql_query.exec())
{
    qDebug() << sql_query.lastError();
}
else
{
    qDebug() << "updated!";
}
  1. Use WHERE, judge that less than 60 is set as unqualified, otherwise set as qualified
query.exec("UPDATE  students  SET 结果='不合格'  WHERE  score<60 ");
query.exec("UPDATE  students  SET 结果='合格'  WHERE  score>=60 ");

Statement:

update <table_name> set <f1=value1>, <f2=value2>… where <expression>;

Update (modification) statement is Update ... ... SET , wherein the student is a table name, name is the name of the header (i.e., the second column), : name is determined variables, WHERE for determining which group of data, : Score is Variable to be determined.
The bindValue(" ", "") function is used to change the pending variable in the statement to a certain value.

Query data

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()<<id<<name<<score<<classs;
}

Judging that the score is greater than or equal to 80, or the class is the sentence of 3-3

"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 part of the data

QString select_sql = "select id, name from students ";
if(!sql_query.exec(select_sql))
{
    qDebug()<<sql_query.lastError();
}
else
{
    while(sql_query.next())
    {
        int id = sql_query.value(0).toInt();
        QString name = sql_query.value(1).toString();
        qDebug()<<QString("id:%1    name:%2").arg(id).arg(name);
    }
}

Statement

select <f1>, <f2>, ... from <table_name>;

select is a query command; <f1> and so on are the variables to be queried (that is, the header), separated by a comma ; **from …** specifies the table.

The above statement is to query the id and name in the students table . After executing the query, use sql_query.value(int) to get the data. Similarly, value(0) represents the first data, which is id , and value(1) represents name . Note: The return value type of the value() function is QVariant , so use toInt() and other functions to convert it to a specific type. In other words , you can use toString() , depending on what specific type you need.

Delete and empty

There are 3 statements to delete the contents of the table:

DROP: Used to delete the entire table, and even the table structure will be deleted. After deletion, you can only use CREATE TABLE to re-create the table.
TRUNCATE: There is no such statement in SQLite. There is this statement in MySQL to clear 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 with WHERE to delete a specified row

Delete a piece of data

QString delete_sql = "delete from student where id = ?";
sql_query.prepare(delete_sql);
sql_query.addBindValue(0);
if(!sql_query.exec())
{
    qDebug()<<sql_query.lastError();
}
else
{
    qDebug()<<"deleted!";
}

Statement

delete from <table_name> where <f1> = <value>

delete is used to delete entries, and use where to give the qualification. For example, here is to delete the entry with id=0 .

Empty table (delete all)

QString clear_sql = "delete from student";
sql_query.prepare(clear_sql);
if(!sql_query.exec())
{
    qDebug() << sql_query.lastError();
}
else
{
    qDebug() << "table cleared";
}

If there is no restriction given by where, all content will be deleted.

Delete database

QString clear_sql = "DROP TABLE students";  //删除名为students的表
sql_query.prepare(clear_sql);
if(!sql_query.exec())
{
    qDebug() << sql_query.lastError();
}
else
{
    qDebug() << "table cleared";
}

Statement

DROP from <table_name>

DROP is used to delete the database

Change the contents of the table

The following two statements are generally used to change the content of the table:

UPDATE: used to modify the contents of the table, you can specify the modification through the WHERE statement
ALTER TABLE: used to rename the table, or add a new column to an existing table

ALTER example

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

UPDATE has been explained above, well, the operation method of database sqlit has been explained! ! !