sqlite数据库使用

1. 基本操作

SQL教程（W3school）
SQL教程（菜鸟）
Qt中建立Sqlite使用环境的两种方式）
QT中读写Sqlite数据库的三种方式
Qt中操作SQLite数据库

1.1 字符串不区分大小写

（sqlite在进行字符串查询的时候，默认是区分大小写）
不区分大小写的三种解决方案:

一：使用大小写转换函数LOWER,UPPER
select * from test where LOWER(name) = 'abcde'；
二：在进行比较时强制声明不区分大小写
select * from test where name = 'abcde' collate nocase;
三：创建表时声明该字段不区分大小写
create table test (id integer, name text collate nocase)
注：
（1）不推荐使用方案一，有额外的性能消耗
（2）如果只是少量查询不需要区分大小写，建议使用方案二
（3）在任何情况下都不需要区分大小写，方案三完美献身

1.2 清空表

delete from TableName;  //清空数据
update sqlite_sequence SET seq = 0 where name ='TableName';//自增

1.3 可视化工具

【SQLiteStudio】的下载及安装
SQLite可视化工具GUI/SQLiteExpert/SQLiteStudio/SQLiteBrowser

2、优缺点

2.1 优点

• 轻量级
  SQLite和C/S模式的数据库软件不同，它是进程内的数据库引擎，因此不存在数据库的客户端和服务器。使用SQLite一般只需要带上它的一个动态库，就可以享受它的全部功能。
• 单一文件
  所谓的“单一文件”，就是数据库中所有的信息（比如表、视图、触发器、等）都包含在一个文件内。并且这个文件可以copy到其它目录或其它机器上，也同样可以使用。通过这个单一文件性，我们便可以将本身需要离散存储的文件进行统一管理了。
• 跨平台/可移植性
  除了主流操作系统，SQLite还支持了很多冷门的操作系统，比如Android、Windows Mobile、Symbin、Palm、VxWorks等的支持。一次存储，多点使用。
• 内存数据库（in-memory database）
  目前内存越来越廉价， SQLite的内存数据库特性就越发显得好用。SQLite 的API不区分当前操作的数据库是在内存还是在文件（对于存储介质是透明的）。所以如果你觉得磁盘I/O有可能成为瓶颈的话，可以考虑切换 为内存方式。切换的时候，操作SQLite的代码基本不用大改，只要在开始时把文件Load到内存，结束时把内存的数据库Dump回文件就可以了。
  在存储瓦片时，如果瓦片数据不算多，内存足够用，可以切换为内存方式，进一步提高瓦片读取效率。

2.2 缺点

• 并发访问的锁机制
  SQLite在并发（包括多进程和多线程）读写方面的性能一直不太理想。数据库可能会被写操作独占，从而导致其它读写操作阻塞或出错。
• SQL标准支持不全
  在它的官方网站上，具体列举了不支持哪些SQL92标准。

3. 存储瓦片

3.1 MBTiles规范

　　MBTiles 是一种地图瓦片存储的数据规范，可大大提高海量地图瓦片的读取速度，比通过瓦片文件方式的读取要快很多，适用于Android、IPhone等智能手机的离线地图存储。
　　MBTiles通用方法是将瓦片表分成两张:一个用来存储原始图像和一个存储瓦片坐标对应那些图片。具体设计如下：map表存储瓦片行列号以及对应的瓦片ID；images表对瓦片进行存储；视图tiles是基于map和images生成的。

3.2 写入图片

#include "mainwindow.h"
#include <QApplication>
#include <QtSql>
#include <QtWidgets>
int main(int argc, char *argv[])
{
    QApplication a(argc, argv);
    // Set up database
    QString dbName("myDatabase.db3"); //默认路径在文件夹build-sqldemo-Desktop_Qt_5_12_9_MSVC2017_64bit-Debug
    QFile::remove(dbName);            // delete sqlite file if it exists from a previous run
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName(dbName);
    db.open();
    QSqlQuery query = QSqlQuery(db);
    query.exec("CREATE TABLE IF NOT EXISTS imgTable ( filename TEXT, imagedata BLOB )");
    // Generate an image (in this case a screenshot) and store it in a QByteArray
    QScreen *screen = a.primaryScreen();
    QPixmap inPixmap = screen->grabWindow(0);
    QByteArray inByteArray;
    QBuffer inBuffer(&inByteArray);
    inBuffer.open(QIODevice::WriteOnly);
    inPixmap.save(&inBuffer, "PNG"); // write inPixmap into inByteArray in PNG format
    // Alternatively, load an image file directly into a QByteArray
    // QFile file("screenshot.png");
    // if (!file.open(QIODevice::ReadOnly)) return;
    // QByteArray inByteArray = file.readAll();
    // Insert image bytes into the database
    // Note: make sure to wrap the :placeholder in parenthesis
    query.prepare("INSERT INTO imgTable (filename, imagedata) VALUES ('screenshot.png', :imageData)");
    query.bindValue(":imageData", inByteArray);
    if (!query.exec())
        qDebug() << "Error inserting image into table:\n"
                 << query.lastError();
    // Get image data back from database
    if (!query.exec("SELECT imagedata from imgTable"))
        qDebug() << "Error getting image from table:\n"
                 << query.lastError();
    query.first();
    QByteArray outByteArray = query.value(0).toByteArray();
    QPixmap outPixmap = QPixmap();
    outPixmap.loadFromData(outByteArray);
    db.close();
    // Display image
    QLabel myLabel;
    myLabel.setPixmap(outPixmap);
    myLabel.show();
    //MainWindow w;
    //w.show();
    return a.exec();
}

	QFile file(fileName);
	if (!file.open(QIODevice::ReadOnly)) 
		return;
	QByteArray byteArray = file.readAll();
	QSqlQuery query;
	query.prepare("INSERT INTO imgtable (imgdata) VALUES (?)");
	query.addBindValue(byteArray);
	query.exec();

从JPG文件写入BLOB 代码如下：

with open(“xxxx.jpg","rb") as f:
    img = f.read()

ba = QByteArray()
bf = QBuffer(ba)
bf.open(QIODevice.WriteOnly)
bf.write(img)
//这里 data 是BLOB 类型，type 数据类型，我想以后加入PDF，或其它文件
sql = "insert into voucher (type,data) values('JPG',:pic)"
self.query.prepare(sql)
self.query.bindValue(":pic", ba )
self.query.exec()

BLOB到 Qpixmap：

sql = "select data from voucher where ID= ? "
// key 是关键字 
query = QSqlQuery()
query.prepare(sql)
query.bindValue(0, key)
query.exec_()
query.first()
ba = QByteArray()
ba.append(query.value(0))
bf = QBuffer(ba)
bf.open(QIODevice.ReadOnly)
imgreader = QImageReader(bf)
img = QImage(imgreader.read())
pix = QPixmap(img) 

4. Qt使用sqlite

void CDBMgr::openDb()
{
    //建立数据库
    if(QSqlDatabase::contains("qt_sql_default_connection"))
    {
        m_db = QSqlDatabase::database(m_strConnectName);
    }
    else
    {
        if(!QSqlDatabase::contains("QSQLITE"))
        {
            m_db = QSqlDatabase::addDatabase("QSQLITE", m_strConnectName);
            m_db.setDatabaseName(m_strDbPath);

        }


    }
    //打开数据库
    if(m_db.open())
    {
        qDebug()<< "successed to open db !";
    }
    else
    {
        qDebug()<< "failed to open db ...";
    }
    //查询表是否存在，没有则创建表STREERMAP(注意：字段名''，类型小写，最后加个;)---
    QSqlQuery query = m_db.exec("SELECT count(*) FROM sqlite_master WHERE type='table' AND name = 'STREETMAP'");
    int iCount = 0;
    while(query.next())
    {
        iCount = query.value(0).toInt();
    }
    if(iCount == 0)
    {
        m_db.exec("CREATE TABLE IF NOT EXISTS STREETMAP("
                  "'X'    integer   NOT NULL,"
                  "'Y'    integer   NOT NULL,"
                  "'Z'    integer   NOT NULL,"
                  "'TILE' blob      NOT NULL"
                  ");");
    }
}

void CDBMgr::closeDb()
{
    m_db.close();
    QSqlDatabase::removeDatabase(m_strConnectName);
}

5. 多线程调用

QSqlDatabase只能在创建它的线程中使用
qt多线程操作数据库(创建不同连接)

6、Qt数据库连接线程池

Qt数据库之数据库连接池
QT实现sqlite数据库连接池