SQL基础介绍:
1、
.pro文件中添加:QT += sql 支持数据库操作
2、查询优化当前系统支持中数据库驱动有哪些?
代码如下:
#include <QCoreApplication>
#include <QSqlDatabase>
#include <QDebug>
#include <QStringList>
int main(int argc, char *argv[])
{
QCoreApplication a(argc, argv);
qDebug() << "Available drivers:";
//获取驱动列表
QStringList drivers = QSqlDatabase::drivers();
//遍历输出
foreach(QString driver, drivers)
qDebug() << driver;
return a.exec();
}
3、数据库的创建连接:
为了避免数据库操作时出现如下错误:
(1):由于定义多个数据库使用过程中,又再次调用QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE"); 造成了问题。
QSqlDatabasePrivate::addDatabase:duplicate connection name
'qt_sql_default_connection', old connection removed.
代码修改如下例子所示:
调用数据库时要指明使用的库: 代码如下
QSqlDatabase db = QSqlDatabase::database("connection1");
QSqlQuery query1(db);
(2):执行query.exec时没有指定db
QSqlQuery::exec: database not open
例子:
头文件:connection.h
#ifndef CONNECTION_H
#define CONNECTION_H
#include <QSqlDatabase>
#include <QDebug>
#include <QSqlQuery>
#include <QMessageBox>
#include <QSqlError>
static bool createConnection()
{
/***************(1)的解决方式***************/
//添加数据库驱动
QSqlDatabase db;
if(QSqlDatabase::contains("connection1"))
db = QSqlDatabase::database("connection1");
else
db = QSqlDatabase::addDatabase("QSQLITE", "connection1");
//设置数据库名称
db.setDatabaseName("D:\\hxjwork\\qttest\\017\\sqlq_name.db");
/***************(1)的解决方式***************/
//打开数据库
if(!db.open())
{
QMessageBox::critical(0,"cannot open data","Unable to establish a database connection.",QMessageBox::Cancel);
return false;
}
/***************(2)的解决方式***************/
/***指定db****/
//以下执行相关sql语句
QSqlQuery query(db);
/***************(2)的解决方式***************/
//新建student表,id设置为主键,还有一个name项
query.exec("create table student(id int primary key,name varchar)");
//向表中插入3条记录
query.exec("insert into student values(1,'xiaogang')");
query.exec("insert into student values(2,'xiaoming')");
query.exec("insert into student values(3,'xiaohong')");
query.exec("insert into student values(4,'xiaohao')");
//添加数据库驱动
QSqlDatabase db1;
if(QSqlDatabase::contains("connection2"))
db1 = QSqlDatabase::database("connection2");
else
db1 = QSqlDatabase::addDatabase("QSQLITE", "connection2");
//设置数据库名称
db1.setDatabaseName("D:\\hxjwork\\qttest\\017\\sqlq_name1.db");
//打开数据库
if(!db1.open())
{
QMessageBox::critical(0,"cannot open data","Unable to establish a database connection.",QMessageBox::Cancel);
return false;
}
//以下执行相关sql语句
QSqlQuery query1(db1);
//新建student表,id设置为主键,还有一个name项
query1.exec("create table student(id int primary key,name varchar)");
//向表中插入3条记录
query1.exec("insert into student values(1,'xiaogang1')");
query1.exec("insert into student values(2,'xiaoming1')");
query1.exec("insert into student values(3,'xiaohong1')");
query1.exec("insert into student values(4,'xiaohao1')");
return true;
}
#endif // CONNECTION_H
主函数:
#include <QApplication>
#include <QSqlDatabase>
#include <QDebug>
#include <QStringList>
#include "connection.h"
void test();
int main(int argc, char *argv[])
{
QApplication a(argc, argv);
//创建数据库连接
if(!createConnection())
return 1;
test();
return a.exec();
}
4、
数据库操作:SQL执行语句:QSqlQuery类
void test()
{
#if 1
//if(!createConnection())
//return 1;
QSqlDatabase db = QSqlDatabase::database("connection1");
QSqlQuery query(db);
query.exec("select * from student");
while(query.next())
{
//query.value(0)是id的值,将其转换为int型
int value0 = query.value(0).toInt();
QString value1 = query.value(1).toString();
//输出两个值
qDebug() << value0 << value1 ;
}
int numRows;
//判断数据库驱动是否支持QuerySize特性,若支持,使用size()函数获取总行数
if(db.driver()->hasFeature(QSqlDriver::QuerySize))
{
qDebug()<<"has feature : query size";
numRows = query.size();
}
else
{
qDebug()<<"no feature : query size";
query.last();
numRows = query.at() + 1;
}
qDebug()<<"row number :"<< numRows;
//指向索引为1的记录,即第二条记录
query.seek(1);
//返回当前索引值
qDebug()<<"current index:"<<query.at();
//获取当前行记录
QSqlRecord record = query.record();
//获取记录中“id”和“name”两个字段的值
int id = record.value("id").toInt();
QString name = record.value("name").toString();
qDebug()<<"id:"<<id<<"name:"<<name;
//获取索引为1的字段,即第二个字段
QSqlField field = record.field(1);
//输出字段名和字段值
qDebug()<<"second field : "<< field.name()
<<"field value:"<< field.value().toString();
//query.exec();
#endif
#if 1//数据插入
//1)
query.exec("insert into student (id,name) values(110,'chenUyurr')");
// query.exec();
//2)
//*
query.prepare("INSERT INTO student (id, name) "
"VALUES (:id,:name)");
query.bindValue(":id", 1001);
query.bindValue(":name", "Bart");
// query.exec();
//*/
//3)
//*
query.prepare("INSERT INTO student (id, name) "
"VALUES (?,?)");
QVariantList ids;
ids<<201<<202<<203<<204;
QVariantList names;
names<<"qwe"<<"rty"<<"asd"<<"zxc"<<"jkl";
query.addBindValue(ids);
query.addBindValue(names);
if (!query.execBatch())
qDebug() << query.lastError();
//*/
#endif
}