1.pro中加入
QT += sql
2.加入头文件
#include <QSqlDatabase>
#include <QSqlError>
#include <QSqlQuery>
3.检查连接、添加数据库驱动、设置数据库名称、数据库登录用户名、密码。
QSqlDatabase database;
if (QSqlDatabase::contains("qt_sql_default_connection"))
{
database = QSqlDatabase::database("qt_sql_default_connection"); //qt_sql_default_connection为qt中默认连接名称,可以自己修改
}
else
{
database = QSqlDatabase::addDatabase("QSQLITE");
database.setDatabaseName("MyDataBase.db");
database.setUserName("XingYeZhiXia");
database.setPassword("123456");
}
如果上面修改了连接名称,则addDatatbase函数需要添加参数
database = QSqlDatabase::addDatabase("QSQLITE", "my_sql_connection);
4.打开数据库
语句:查看database.open()泛函函数是否为TRUE
示例:
if (!database.open())
{
qDebug() << "Error: Failed to connect database." << database.lastError();
}
else
{
// do something
}
5.关闭数据库
database.close();
数据库操作完成后,最好关闭。
6.操作数据库
6.1创建表格
语句:也可以直接执行sql_query.exec("create table student (id int primary key, name varchar(30), age int)");
示例:
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();
}
else
{
qDebug() << "Table created!";
}
6.2插入数据
语句:INSERT INTO student VALUES(3, \"Li\", 23)
可以用addBindValue函数按表格顺序插入
也可以用语句INSERT INTO语句插入
示例:
QString insert_sql = "insert into student values (?, ?, ?)";
sql_query.prepare(insert_sql);
sql_query.addBindValue(max_id+1);
sql_query.addBindValue("Wang");
sql_query.addBindValue(25);
if(!sql_query.exec())
{
qDebug() << sql_query.lastError();
}
else
{
qDebug() << "inserted Wang!";
}
if(!sql_query.exec("INSERT INTO student VALUES(3, \"Li\", 23)"))
{
qDebug() << sql_query.lastError();
}
else
{
qDebug() << "inserted Li!";
}
6.3更新数据、修改数据
语句:update <table_name> set <f1=value1>, <f2=value2>… where <expression>;
也可以用bindValue(" “,” ")函数进行添加数据
示例:
QString update_sql = "update student set name = :name where id = :id";
sql_query.prepare(update_sql);
sql_query.bindValue(":name", "Qt");
sql_query.bindValue(":id", 1);
if(!sql_query.exec())
{
qDebug() << sql_query.lastError();
}
else
{
qDebug() << "updated!";
}
6.4查询数据
6.4.1查询部分数据
语句:select <f1>, <f2>, ... from <table_name>;
示例:
QString select_sql = "select id, name from student";
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);
}
}
6.4.2查询全部数据
语句:select * from <table_name>;
示例:
QString select_all_sql = "select * from student";
sql_query.prepare(select_all_sql);
if(!sql_query.exec())
{
qDebug()<<sql_query.lastError();
}
else
{
while(sql_query.next())
{
int id = sql_query.value(0).toInt();
QString name = sql_query.value(1).toString();
int age = sql_query.value(2).toInt();
qDebug()<<QString("id:%1 name:%2 age:%3").arg(id).arg(name).arg(age);
}
}
6.4.3查询最大id
示例:
QString select_max_sql = "select max(id) from student";
int max_id = 0;
sql_query.prepare(select_max_sql);
if(!sql_query.exec())
{
qDebug() << sql_query.lastError();
}
else
{
while(sql_query.next())
{
max_id = sql_query.value(0).toInt();
qDebug() << QString("max id:%1").arg(max_id);
}
}
6.5.删除与清空
6.5.1删除一条数据
语句:delete from <table_name> where <f1> = <value>
示例:
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!";
}
6.5.3清空表格(删除所有)
语句:delete from student
(没有加where就会删除全部)`
示例:
QString clear_sql = "delete from student";
sql_query.prepare(clear_sql);
if(!sql_query.exec())
{
qDebug() << sql_query.lastError();
}
else
{
qDebug() << "table cleared";
}