SQLite3 学习笔记 以及C#连接SQLite3

数据库结构

在sqlite中，一个文件是一个数据库，一个数据库中可以包含多个表，其中sqlite_master表是数据库的核心表，用于记录其余所有表的基本相关信息。

可以使用.table命令查看数据库中的所有表(不包括核心表)。

查看表结构的方法有两种：

1. 查询sqlite_master表。
2. 使用pragma table_info( );语句，在括号中添上需要查看的表名称

数据类型

虽然sqlite也有数据类型，但是与我们熟悉的mysql和sqlserver完全不同。SQLite 使用了一个特别另类的动态类型系统。在 SQLite 中，值的数据类型与值本身是相关的，而不是与它的容器相关。

要想理解sqlite的数据类型，首先要明白它的存储类。在sqlite中，不管是什么数据类型，最后一定会转换成下面五种类型中的一种。

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

相对于其他使用静态数据类型的数据库，sqlite数据库可以做很多意想不到的操作。

• sqlite没有强制要求数据类型，甚至数据类型可以自己随便写(主键自增列除外)，如下，可以声明一个用abcd数据类型的列。

create table [test_1]
(
	[id] integer primary key autoincrement not null,
	[fint] int not null,
	[fstr] abcd not null,
);
           

• sqlite中，数据类型可以混着放(主键自增的列除外)。不管你这一列用的是integer，int，text还是其他数据类型，丝毫不影响往里面放东西，例如第一行放整形数字，第二列放浮点数，第三列放字符串，都丝毫不受影响，而且不受大小和长度的限制。最后也不影响使用where查询语句。

insert into [test_1] values (1, 'str4', 8.354);
insert into [test_1] values (26, '54', '3');
-- 特别长的整数也没有问题
insert into [test_1] values (4, 8.45, 78945318615647315768415);
           

C#连接SQLite数据库

安装依赖包

通过NuGet安装 System.Data.SQLite 包，其他包会被作为该包的依赖自动安装。 同时会在 .config 文件中自动生成相应的配置，这些配置可以直接用，不需要专门去修改。

从官网下载说明文档：http://system.data.sqlite.org/index.html/doc/trunk/www/index.wiki

说明文档里面详细记载了如何使用C#操作SQLite。

读操作(查询)：

static void Main(string[] args)
{
    string conStr = @"Data Source=C:\Users\source\repos\ConsoleApp1\SQLite\DataBase\test.db;Max Pool Size=10";
    SQLiteConnection connection = new SQLiteConnection(conStr);
    connection.Open();
    DataTable table = new DataTable();
    using (SQLiteCommand cmd = new SQLiteCommand(connection))
    {
        cmd.CommandText = "select * from [test_table] where [id][email protected] or [str][email protected];";
        cmd.Parameters.Add("id", DbType.Int32).Value = 1;
        cmd.Parameters.Add("str", DbType.String).Value = "abc";
        //声明适配器，方便接受查询结果(比起直接用数据流接受查询结果方便太多了)
        SQLiteDataAdapter adapter = new SQLiteDataAdapter();
        adapter.SelectCommand = cmd;
        //将查询结果放入DataTable中
        adapter.Fill(table);
        //查看获取到的数据的行数
        Console.WriteLine(table.Rows.Count);
    }
    connection.Close();
    Console.ReadKey(true);
}
           

写操作(增删改)：

Journal Mode=Off

字段可以关闭数据库回滚功能，加快写入效率，但会降低安全性。

static void Main(string[] args)
{
    string conStr = @"Data Source=C:\Users\source\repos\ConsoleApp1\SQLite\DataBase\test.db;Max Pool Size=10;Journal Mode=Off";
    SQLiteConnection connection = new SQLiteConnection(conStr);
    connection.Open();
    int rows;
    using (SQLiteCommand cmd = new SQLiteCommand(connection))
    {
	    cmd.CommandText = "INSERT INTO [test_table] VALUES(@id, @str)";
	    cmd.Parameters.Add("id", DbType.Int32).Value = 1;
	    cmd.Parameters.Add("str", DbType.String).Value = "abc";
	    //获取受影响的行数
	    rows = cmd.ExecuteNonQuery();
    }
    Console.WriteLine(rows);
    connection.Close();
    Console.ReadKey(true);
}
           

如果你的程序需要频繁写入大量数据(就像我的一样)，就建议使用下面一种方式，这种方法可以通过预处理SQL语句来加快写入速度。

频繁写入的操作：

using (SQLiteTransaction mytransaction = myconnection.BeginTransaction())
{
  using (SQLiteCommand mycommand = new SQLiteCommand(myconnection))
  {
    SQLiteParameter myparam = new SQLiteParameter();
    int n;
  
    mycommand.CommandText = "INSERT INTO [MyTable] ([MyId]) VALUES(?)";
    mycommand.Parameters.Add(myparam);
    
    for (n = 0; n < 100000; n ++)
    {
      myparam.Value = n + 1;
      mycommand.ExecuteNonQuery();
    }
  }
  mytransaction.Commit();
} 
           

性能比较

因为我这里对于写操作的性能要求很高，同时对于稳定性要求不高，所以这里都关闭了日志功能和崩溃自动回滚功能。(关掉后效率能翻至少好几倍)

发现对于单机环境下，单线程模式下，sqlite的效率是mySQL的两倍左右。