2.1 介绍

  sqlite为关系型数据库，是一款轻型的数据库，是遵守ACID的关系型数据库管理系统，它包含在一个相对小的C库中。它的设计目标是嵌入式的，而且已经在很多嵌入式产品中使用了它，它占用资源非常的低，在嵌入式设备中，可能只需要几百K的内存就够了.

  SQLite 是一个软件库，实现了自给自足的、无服务器的、零配置的、事务性的 SQL 数据库引擎。SQLite 是在世界上最广泛部署的 SQL 数据库引擎。SQLite 源代码不受版权限制。

2.2 优缺点

优点：

    1>sqlite占用的内存和cpu资源较少
    2>源代码开源，完全免费
    3>检索速度上十几兆、几十兆的数据库sqlite很快，但是上G的时候最慢
    4>管理简单，几乎无需管理。灵巧、快速和可靠性高
    5>功能简约，小型化，追求最大磁盘效率

缺点：

    1>不支持多用户多线程同时读写数据库
    2>数据库同一时间仅仅同意一个写操作。因此吞吐量有限。

2.3 在windows安装

    1>下载 sqlite-tools-win32-.zip 和 sqlite-dll-win32-.zip 压缩文件
    2>创建文件夹 C:\sqlite，并在此文件夹下解压上面两个压缩文件，将得到 sqlite3.def、sqlite3.dll 和 sqlite3.exe 文件
    3>添加 C:\sqlite 到 PATH 环境变量，最后在命令提示符下，使用 sqlite3 命令

2.4 在qt上的使用

database.h

#ifndef DATABASE_H
#define DATABASE_H#include 
#include 
#include 
#include 
#include 
#include 
#include typedef struct db_user
{QString sUsername;QString sPassword;int nAuth;
} DB_USER_t;typedef void (*db_callback)(void*pUser, void*pData);
typedef void (*db_callback2)(void*pUser1, void*pUser2, void*pData);class Database : public QObject
{Q_OBJECT
public:virtual ~Database();static Database& getInstance();int db_init(const QString& sDbPath);int db_run_cmd(QString& sql);int db_query_cmd(QString& sql);//User 增删改查int db_user_delete(QString sUsername);int db_user_get_all(int* pUserNum ,DB_USER_t* user);int db_user_get_byname(QString &username, DB_USER_t* user);int db_user_add(DB_USER_t* user);int db_user_modify_pwd(DB_USER_t* user);private:Database(QObject *parent = NULL);QMutex      *m_dbLock;QSqlDatabase m_db;QString      m_dbcon;QSqlQuery   *m_dbQuery;bool         m_bLock;signals:void sig_databaseLock();
};#endif // DATABASE_H

database.cpp

#include "database.h"
#include 
#include 
#include 
#include 
#include 
#include 
#include "commondef.h"static int g_nCon = 0;#define DB_NAME         "db/mydb.db"
#define DB_TYPE         "QSQLITE"
#define DB_TBL_USEr     "User"#define DB_CREATE_TBL_USER   \"CREATE TABLE [User](\[username] VARCHAR(32) NOT NULL, \[password] VARCHAR(32) NOT NULL, \[auth] INTEGER NOT NULL DEFAULT 0);"Database::Database(QObject *parent): QObject(parent)
{this->setObjectName("MyDB");QString sDbPath = QString("%1/%2").arg(QCoreApplication::applicationDirPath()).arg(DB_NAME);MY_DEBUG << "sDbPath:" << sDbPath;db_init(sDbPath);
}Database &Database::getInstance()
{static Database s_db;return s_db;
}int Database::db_init(const QString& sDbPath)
{m_dbLock = new QMutex(QMutex::Recursive);QMutexLocker mutexlock(m_dbLock);m_bLock = false;// 1. create connect.m_dbcon = QString("DatabaseCon%1").arg(g_nCon++);m_db = QSqlDatabase::addDatabase(DB_TYPE, m_dbcon);m_db.setDatabaseName(sDbPath);if(!m_db.open()){MY_DEBUG << "Db open failed!!!";return -1;}// 2. check if exsist table.m_dbQuery = new QSqlQuery(m_db);m_dbQuery->clear();bool isTableExist = m_dbQuery->exec(QString("select count(*) from sqlite_master where type='table' and name='%1'").\arg(DB_TBL_USEr));m_dbQuery->next();if(!isTableExist || m_dbQuery->value(0).toInt() == 0){m_dbQuery->exec(DB_CREATE_TBL_USER);// 1. add default users.DB_USER_t stUser = {"admin", "123456", 0};return db_user_add(&stUser);}return 0;
}Database::~Database()
{m_dbLock->lock();if(m_db.isOpen()){m_db.close();}QSqlDatabase::removeDatabase(m_dbcon);delete m_dbQuery;m_dbLock->unlock();delete m_dbLock;
}int Database::db_run_cmd(QString &sql)
{QMutexLocker mutexlock(m_dbLock);// 1. check valid.if(!m_db.isOpen() || !m_db.isValid()){MY_DEBUG << "db is not valid";return -1;}// 2. clear old query.m_dbQuery->clear();// 3. run command.if(!m_dbQuery->exec(sql)){MY_DEBUG << "run sql:" << sql;MY_DEBUG << "m_dbQuery->exec fail: " << m_dbQuery->lastError();if(m_bLock == false && m_dbQuery->lastError().text().contains("locked")){emit sig_databaseLock();m_bLock = true;}return -1;}return 0;
}int Database::db_query_cmd(QString &sql)
{// 1. check valid.if(!m_db.isOpen() || !m_db.isValid()){MY_DEBUG << "db is not valid";return -1;}// 2. clear old query.m_dbQuery->clear();// 3. run command.if(!m_dbQuery->exec(sql)){MY_DEBUG << "run sql:" << sql;MY_DEBUG << "m_dbQuery->exec fail: " << m_dbQuery->lastError();return -1;}return 0;
}//User
int Database::db_user_delete(QString sUsername)
{QString sql = QString("delete from User where username = '%1'").arg(sUsername);return db_run_cmd(sql);
}int Database::db_user_get_all(int* pUserNum ,DB_USER_t* user)
{QMutexLocker mutexlock(m_dbLock);// 1. query exec.QString sql = QString("select username,password,auth from User");if(db_query_cmd(sql)){MY_DEBUG << "DBERR: " << __FUNCTION__ << __LINE__;return -1;}// 2. get all data.int i = 0;while(m_dbQuery->next() && i < MAX_USER_NUM){user[i].sUsername = m_dbQuery->value(0).toString();user[i].sPassword = m_dbQuery->value(1).toString();user[i].nAuth = m_dbQuery->value(2).toInt();i++;}if(i == 0){MY_DEBUG << "DBERR: " << __FUNCTION__ << __LINE__;return -1;}*pUserNum = i;return 0;
}int Database::db_user_get_byname(QString &username, DB_USER_t *user)
{QMutexLocker mutexlock(m_dbLock);// 1. query exec.QString sql = QString("select username,password,auth from User where username='%1'").arg(username);if(db_query_cmd(sql)){MY_DEBUG << " db_query_cmd failed";return -1;}// 2. get user data.if(m_dbQuery->next()){user->sUsername = username;user->sPassword = m_dbQuery->value(1).toString();user->nAuth = m_dbQuery->value(2).toInt();return 0;}return -1;
}int Database::db_user_add(DB_USER_t *user)
{QString sql = QString("insert into User(username, password, auth) values('%1', '%2', %3)").\arg(user->sUsername).arg(user->sPassword).arg(user->nAuth);return db_run_cmd(sql);
}int Database::db_user_modify_pwd(DB_USER_t *user)
{QString sql = QString("UPDATE User set password = '%1', auth = %2 where username='%3'").\arg(user->sPassword).arg(user->nAuth).arg(user->sUsername);return db_run_cmd(sql);
}

main.cpp

#include 
#include "database.h"
#include "commondef.h"int main(int argc, char *argv[])
{QCoreApplication a(argc, argv);//增DB_USER_t stUser1 = {"test1", "test1_123456", 0};Database::getInstance().db_user_add(&stUser1);//查MY_DEBUG << "##############1##################";int nUserNum;DB_USER_t db_user[MAX_USER_NUM];Database::getInstance().db_user_get_all(&nUserNum, db_user);MY_DEBUG << "nUserNum:" << nUserNum;for(int i = 0; i < nUserNum; i++){MY_DEBUG << "i:" << i << ", db_user->sUsername:" << db_user[i].sUsername << ", db_user->sPassword:" << db_user[i].sPassword << ", db_user->nAuth:" << db_user[i].nAuth;}//改DB_USER_t stUser2 = {"test1", "test1_654321", 0};Database::getInstance().db_user_modify_pwd(&stUser2);//查MY_DEBUG << "##############2##################";DB_USER_t stUser3;QString sUsername = "test1";Database::getInstance().db_user_get_byname(sUsername, &stUser3);MY_DEBUG << "stUser3.sUsername:" << stUser3.sUsername << ", stUser3.sPassword:" << stUser3.sPassword << ", stUser3.nAuth:" << stUser3.nAuth;//删Database::getInstance().db_user_delete("test1");//查MY_DEBUG << "##############3##################";int nUserNum2;DB_USER_t db_user2[MAX_USER_NUM];Database::getInstance().db_user_get_all(&nUserNum2, db_user2);for(int i = 0; i < nUserNum2; i++){MY_DEBUG << "i:" << i << ", db_user2->sUsername:" << db_user2[i].sUsername << ", db_user2->sPassword:" << db_user2[i].sPassword << ", db_user2->nAuth:" << db_user2[i].nAuth;}return a.exec();

2.5 qt-demo下载

下载链接：https://download.csdn.net/download/linyibin_123/86341593

2.6 sqlite的具体学习

菜鸟教程：https://www.runoob.com/sqlite/sqlite-intro.html