QT sqlite的简单用法

1、相关头文件

#include <QSqlDatabase>
#include <QSqlError>
#include <QSqlQuery>
#include <QSqlRecord>
#include <QSqlIndex>
#include  <QSqlField>
#include <QFile>
#include <QDebug>

2、数据库对象

QSqlDatabase database;

3、创建数据库并打开数据库

bool initDataBase()
{if(QSqlDatabase::contains("qt_sql_default_connect")){database = QSqlDatabase::database("qt_sql_default_connect");}else{database = QSqlDatabase::addDatabase("QSQLITE");database.setDatabaseName("myDatabase.db");database.setUserName("hs");database.setPassword("hs");if(database.isValid()){qDebug() << "valid." ;}else{qDebug() << "not valid!"<< database.lastError();return false;}if (!database.open()){qDebug() << "Error: Failed to connect database." << database.lastError();return false;}}return true;
}

4、创建表

bool testCreateTable()
{QSqlQuery sql_query;QString create_sql = "create table student (id int primary key, name varchar(30), age int)";sql_query.prepare(create_sql);if(!sql_query.exec()){qDebug() << "Error: Fail to create table." << sql_query.lastError();return false;}else{qDebug() << "Table created!";}return true;
}

5、向表中插入数据

bool testInsert(int id,const QString& name,int age)
{QSqlQuery query;query.prepare("INSERT INTO student(id,name,age)""VALUES (:id,:name,:age)");query.bindValue(":id",id);query.bindValue(":name",name);query.bindValue(":age",age);return query.exec();
}

6、查询表中的数据

bool testQuery()
{QSqlQuery sql_query;QString select_sql = "select id, name,age from student";if(!sql_query.exec(select_sql)){qDebug()<<sql_query.lastError();return false;}else{while(sql_query.next()){int id = sql_query.value("id").toInt();QString name = sql_query.value("name").toString();int age = sql_query.value("age").toInt();qDebug()<<QString("id:%1 name:%2 age:%3").arg(id).arg(name).arg(age);}}QString lastQuery = sql_query.lastQuery();qDebug() << "lastQuery: " << lastQuery;return true;
}

7、修改表中的数据

bool testModify(const QString& name,int age,int id)
{QString sql = "update student set name=:name,age=:age where id=:id";QSqlQuery query;query.prepare(sql);// 设置参数query.bindValue(":name",name);query.bindValue(":age",age);query.bindValue(":id",id);return query.exec();
}

8、删除表中的数据

bool testDelete(int id)
{QString sql = "delete from student where id=:id";QSqlQuery query;query.prepare(sql);// 设置参数query.bindValue(":id",id);return query.exec();
}

9、查询数据库中的表

bool testQueryTable()
{// 查询sqlite_master表QString querySql = "SELECT name, type FROM sqlite_master WHERE type IN ('table', 'view')";QSqlQuery query(querySql);// 遍历查询结果while (query.next()) {QString name = query.value(0).toString();QString type = query.value(1).toString();qDebug() << "------------------";qDebug() << "Object name:" << name;qDebug() << "Object type:" << type;qDebug() << "------------------";}return true;
}

10、删除指定的表

bool testDeleteTable()
{QSqlQuery sql_query;QString drop_sql = "DROP TABLE student";sql_query.prepare(drop_sql);if(!sql_query.exec()){qDebug() << sql_query.lastError();return false;}else{qDebug() << "drop table success";}return true;
}

11、查询表是否存在

bool isTableExist()
{QSqlQuery sql_query;// count(*) 表示查询数量，因为student只有一个，因此只查询了一行结果sql_query.exec("SELECT COUNT(*) FROM sqlite_master  WHERE type='table' AND name='student'");int count = 0;if (sql_query.next()) {count = sql_query.value(0).toInt();}return count;
}

12、查询表的字段等

void testRecord()
{QSqlQuery query("select * from student");//QString sql = "select * from student";//query.prepare(sql);QSqlRecord rec = query.record();qDebug() << "Number of columns: " << rec.count();for(int i = 0; i < rec.count();++i){qDebug() << "field: " << rec.fieldName(i);}int nameCol = rec.indexOf("name"); // index of the field "name"while (query.next())qDebug() << query.value(nameCol).toString(); // output all names
}

13、查询数据库的一些其他输出信息

void testDatabaseOutput()
{QString connectionOptions = database.connectOptions();qDebug() <<"option:" << connectionOptions;QStringList connectionName = database.connectionNames();qDebug() << "connectionNames:"<<connectionName;qDebug() << "database Name: " << database.databaseName();qDebug() << "dirver Name: " << database.driverName();//QSqlDriver* driver = database.driver();qDebug() << "dirvers: " << database.drivers();qDebug() << "host Name: " << database.hostName();qDebug() << "password: " << database.password();qDebug() << "port: " << database.port();qDebug() << "tables: " << database.tables(QSql::TableType::AllTables);QSqlIndex rlt = database.primaryIndex("student");qDebug() << "primary key: "<<rlt.name();QSqlRecord rec = database.record("student");qDebug() << "record count: "<<rec.count();for(int i = 0; i < rec.count();++i){qDebug() << "rec: " << rec.fieldName(i);qDebug() << "rec Value:" << rec.value(i).toString();QSqlField field = rec.field(i);qDebug() <<"field: "<< field.name();qDebug() << "field value: " << field.value().toString();qDebug() << "field type: " << field.type();}}