Sqlite

1.Sqlite 简介

SQLite，是一款轻型的数据库，是遵守ACID的关系型数据库管理系统，它包含在一个相对小的C库中。
它的设计目标是嵌入式的，而且已经在很多嵌入式产品中使用了它，它占用资源非常的低，在嵌入式设备中，可能只需要几百K的内存就够了。
它能够支持Windows/Linux/Unix等等主流的操作系统，同时能够跟很多程序语言相结合，比如 Tcl、C#、PHP、Java等，还有ODBC接口

2.Sqlite 功能特性

ACID事务
零配置 – 无需安装和管理配置
储存在单一磁盘文件中的一个完整的数据库
数据库文件可以在不同字节顺序的机器间自由的共享
支持数据库大小至2TB
简单, 轻松的API
独立: 没有额外依赖
支持事务处理功能

4.客户端管理工具 SqliteStudio

https://sqlitestudio.pl/

5.四种数据类型

存储类	描述
NULL	值是一个 NULL 值。
INTEGER	值是一个带符号的整数，根据值的大小存储在 1、2、3、4、6 或 8 字节中。
REAL	值是一个浮点值，存储为 8 字节的 IEEE 浮点数字。
TEXT	值是一个文本字符串，使用数据库编码（UTF-8、UTF-16BE 或 UTF-16LE）存储。
BLOB	值是一个 blob 数据，完全根据它的输入存储。

6. Sqlite 使用

Nuget 中 搜索 System.data.sqlite.core 选择版本 安装
连接字符串 Data Source=c:\mydb.db  最简单一种

/// <summary>
/// SQLite 连接
/// </summary>
/// <returns></returns>
private IDbConnection OpenConnForSQLite()
{
    // 需要安装 nuget中 "System.Data.SQLite" 包
    IDbConnection connection = new SQLiteConnection(_connectionStr);
    try
    {
        connection.Open();
    }
    catch (Exception ex)
    {
    }
    return connection;
}
若不在此数据库，会直接创建一个数据库

7. 第一查询比较慢,随后再点击查询就相当快

缓存命中

8. where 条件比较时 需要将字符串转成 数字类型进行比较

1.where cast(Capacity as demical) 
2.where Start_Time <= datetime('{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}')

9. SQLite在进行字符串比较的时候，默认对大小写是敏感的，解决方式：

方案一：使用大小写转换函数LOWER、UPPER
SELECT * FROM [User] WHERE LOWER(UserName) = 'user1'
方案二：在进行比较时强制声明不区分大小写
SELECT * FROM [User] WHERE UserName = 'user1' COLLATE NOCASE
方案三：创建表时声明该字段不区分大小写
CREATE TABLE [User] ( [UserName] NVARCHAR(20) COLLATE NOCASE );
如果在任何情况下都不需要对大小写敏感，方案三是最好的解决方案；如果只是少量查询对大小写不敏感，可以用方案二。方案一由于用到了函数，可能有额外的性能损失，不推荐使用。

10. SQLite 中日期时间格式存储 这样就可以转换成 DateTime 判断

DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")

11. SQLite 代码创建数据库方式

/// <summary>
/// 新建数据库文件
/// </summary>
/// <param name="dbPath">数据库文件路径及名称</param>
/// <returns>新建成功，返回true，否则返回false</returns>
static public Boolean NewDbFile(string dbPath)
{
    try
    {
        SQLiteConnection.CreateFile(dbPath);
        return true;
    }
    catch (Exception ex)
    {
        throw new Exception("新建数据库文件" + dbPath + "失败：" + ex.Message);
    }
}

12. SQLite 代码创建表 SQL

"CREATE TABLE " + tableName + "(时间 Text,数据 Text, 状态 Text)"

13. SQLite 索引

单列索引
单列索引是一个只基于表的一个列上创建的索引。基本语法如下：
CREATE INDEX index_name ON table_name (column_name);

唯一索引
使用唯一索引不仅是为了性能，同时也为了数据的完整性。唯一索引不允许任何重复的值插入到表中。基本语法如下：
CREATE UNIQUE INDEX index_name on table_name (column_name);

组合索引
组合索引是基于一个表的两个或多个列上创建的索引。基本语法如下：
CREATE INDEX index_name on table_name (column1, column2);

是否要创建一个单列索引还是组合索引，要考虑到您在作为查询过滤条件的 WHERE 子句中使用非常频繁的列。
如果值使用到一个列，则选择使用单列索引。如果在作为过滤的 WHERE 子句中有两个或多个列经常使用，则选择使用组合索引。

14. SQLite PRAGMA

1. $"PRAGMA main.table_info({tableName})" 查询表中所有信息
2. "PRAGMA Synchronous = Off"
3. $"PRAGMA Cache_Size = {size}"
4. "PRAGMA Journal_Mode = WAL"

15. SQLite HAVING

在一个查询中，HAVING 子句必须放在 GROUP BY 子句之后，必须放在 ORDER BY 子句之前。
sqlite > SELECT * FROM COMPANY GROUP BY name HAVING count(name) < 2;

16. SQLite Like

SQLite 的 LIKE 运算符是用来匹配通配符指定模式的文本值。如果搜索表达式与模式表达式匹配，LIKE 运算符将返回真（true），也就是 1。这里有两个通配符与 LIKE 运算符一起使用：

百分号 （%） 下划线 （_）

百分号（%）代表零个、一个或多个数字或字符。
下划线（_）代表一个单一的数字或字符。这些符号可以被组合使用。
1. SELECT column_list FROM table_name WHERE column LIKE 'XXXX%'
2.SELECT column_list FROM table_name WHERE column LIKE '%XXXX%'
3.SELECT column_list FROM table_name WHERE column LIKE 'XXXX_'
4.SELECT column_list FROM table_name WHERE column LIKE '_XXXX'
5.SELECT column_list FROM table_name WHERE column LIKE '_XXXX_'

17. SQLite 并发问题

https://www.cnblogs.com/xichji/p/11630252.html
多线程用同一个连接 就不会出现并发问题

18. SQLite 执行效率优化问题

1. SQLiteConnection对象初始化、打开及关闭，其花费时间约为109ms，因此，最好不要频繁地将该对象初始化、打开与关闭，这与SQL Server不一样，在这里建议使用单例模式来初始化SQLiteConnection对象；
2. 使用ExecuteReader方式比使用Adapter Fill Table方式快一点点，但这不是绝对的，这取决于编写的代码；
3. 无论是执行插入或查询操作，使用事务比不使用事务快，尤其是在批量插入操作时，减少得时间非常明显；
 比如在不使用事务的情况下插入3000条记录，执行所花费的时间为17.252s，而使用事务，执行时间只用了0.057s，效果非常明显，而SQL Server不存在这样的问题。
4. 不能每次执行一条SQL语句前开始事务并在SQL语句执行之后提交事务，这样的执行效率同样是很慢，最好的情况下，是在开始事务后批量执行SQL语句，再提交事务，这样的效率是最高的。
5.
6.

19. SQLite数据库中删除数据后，文件大小没有变化

全部都是这样子的，数据库原理（磁盘块如何保存数据、如何维系和使用空闲空间）的基本常识。

20. SQLite database is locked解决办法

https://blog.csdn.net/qq_42675313/article/details/88013243?utm_medium=distribute.pc_relevant_bbs_down.none-task-blog-baidujs-2.nonecase&depth_1-utm_source=distribute.pc_relevant_bbs_down.none-task-blog-baidujs-2.nonecase

21. SQLite 查询优化性能要点

https://www.cnblogs.com/dongweiq/p/5486433.html

/// <summary>
/// 优化数据库性能
/// </summary>
/// <param name="db"></param>
private static bool AdjustPerformance(Db db, int size = 8000)
{
    try
    {
        string sql = $"PRAGMA Synchronous = Off;PRAGMA Journal_Mode = WAL;PRAGMA default_Cache_Size = {size}"; // default_Cache_Size
        int result = db.ExecSQL(sql);
        Basic._logger.Info($"PRAGMA Db Result={result}");
        return result > 0;
    }
    catch (Exception ex)
    {
        Basic._logger.Error(ex.Message + ex.StackTrace, nameof(AdjustPerformance));
    }
    return false;
}