首先: 下载驱动
下载自:https://mariadb.org/download/?t=mariadb&p=mariadb&r=10.6.7&os=windows&cpu=x86_64&pkg=zip&m=xtom_hk
解压到E:\CPP\lib\05mariadb-10.9.4-winx64后 引用 - 库目录:E:\CPP\lib\05mariadb-10.9.4-winx64\lib
E:\CPP\lib\05mariadb-10.9.4-winx64\lib\libmariadb.dll 放到 exe目录下
QT +=sql
#include <qsqldatabase.h> #include <qsqlerror.h>
链接:
//连接数据库 void jjsoft::btn_db_connect() { qDebug().noquote() << "调试"; QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL"); db.setHostName(ui.db_host->text()); //连接本地主机 db.setPort(ui.db_port->text().toInt()); db.setDatabaseName(ui.db_name->text()); db.setUserName(ui.db_user->text()); db.setPassword(ui.db_pwd->text()); bool ok = db.open(); if (ok) { QMessageBox::information(this, "infor", "link success"); } else { QMessageBox::information(this, "infor", "link failed"); qDebug() << "error open database because" << db.lastError().text(); } }
数据插入
QSqlQuery query; bool ok = query.exec("insert into student(id, name, age, score) values(1, '张三', 18, 80)"); if(!ok) { qDebug() << query.lastError(); }
数据修改
QSqlQuery query; bool ok = query.exec("update student set score=100 where name='张三' "); if(!ok) { qDebug() << query.lastError(); }
数据查询
QSqlQuery query; bool ok = query.exec("select id from student where name='张三' "); if(!ok) { qDebug() << query.lastError(); } else { query.next(); qDebug() << query.value(0).toInt(); } //遍历整个表 ok = query.exec("select *from student"); if (!ok) { qDebug()<< query.lastError(); } else { while(query.next()) //遍历完为false { //方式1:以下标 qDebug()<< query.value(0).toInt()<< query.value(1).toString() << query.value(2).toInt() << query.value(3).toInt(); //方式2:以字段 //qDebug()<< query.value("id").toInt()<< query.value("name").toString() //<< query.value("age").toInt()<< query.value("score").toInt(); } }
数据删除
QSqlQuery query; bool ok = query.exec("delete from student where name='张三' "); if(!ok) { qDebug() << query.lastError(); }
创建数据表
QSqlQuery query; bool ok = query.exec("create table student(id int primary key auto_increment, name varchar(255), age int, score int)ENGINE=INNODB;"); if(!ok) { qDebug() << query.lastError(); }
删除整个表
QSqlQuery query; bool ok = query.exec("drop table student"); if(!ok) { qDebug() << query.lastError(); }
//快捷链接代码
#include "jjsoft.h" //链接数据库 bool jjsoft::dbOpen(QSqlDatabase& db) { QString dbName = QString::number((quint64)QThread::currentThreadId()); { db = QSqlDatabase::addDatabase("QMYSQL", dbName); //连接的MYSQL的数据库驱动 } db.setHostName(ui.db_host->text()); //连接主机 db.setPort(ui.db_port->text().toInt()); db.setDatabaseName(ui.db_name->text()); db.setUserName(ui.db_user->text()); db.setPassword(ui.db_pwd->text()); if (db.open()) { qDebug().noquote() << ("dbOpen OK:" + dbName); return true; } else { qDebug().noquote() << ("dbOpen err:" + dbName + " err:" + db.lastError().text()); return false; } } //断开数据库 void jjsoft::dbClose(QSqlDatabase& db) { QString dbName = QString::number((quint64)QThread::currentThreadId()); if (db.isValid()) { db.close(); } db = QSqlDatabase();// 通过分配一个默认构造进行重置 QSqlDatabase: db.removeDatabase(dbName);//移除数据库 qDebug().noquote() << "dbClose" << dbName; }