SQLite特性

SQLite

数据存储

SQLite是一个轻量级的关系型数据库，运算速度快，占用资源少，很适合在移动设备上使用， 不仅支持标准SQL语法，还遵循ACID(数据库事务)原则，是一个嵌入式SQL数据库引擎。与大多数其他SQL数据库不同，SQLite没有单独的服务器进程，直接读写普通磁盘文件，具有多个表，索引，触发器和视图的完整SQL数据库包含在单个磁盘文件中。

数据结构和组织

SQLite支持五种数据类型:NULL,INTEGER,REAL(浮点数),TEXT(字符串文本)和BLOB(二进制对象) 虽然只有五种,但是对于var char,char等其他数据类型都是可以保存的;因为SQLite有个最大的特点: 可以各种数据类型的数据保存到任何字段中而不用关心字段声明的数据类型是什么。SQLite是一种关系数据库，按表和行去组织，底层是红黑树结构，通过文件来保存数据库，一个文件就是一个数据库，数据库中又包含多个表格，表格里又有多条记录，每个记录由多个字段构成，每个字段有对应的值，每个值我们可以指定类型，也可以不指定类型(主键除外)

数据索引（如何找到数据）

索引是一种特殊的查找表，数据库搜索引擎用来加快数据检索。简单地说，索引是一个指向表中数据的指针。索引有助于加快 SELECT 查询和 WHERE 子句，但它会减慢使用 UPDATE 和 INSERT 语句时的数据输入。索引可以创建或删除，但不会影响数据。使用 CREATE INDEX 语句创建索引，它允许命名索引，指定表及要索引的一列或多列，并指示索引是升序排列还是降序排列。索引也可以是唯一的，与 UNIQUE 约束类似，在列上或列组合上防止重复条目

  • 单列索引：只基于表的一个列上创建的索引
1
2
CREATE INDEX index_name
ON table_name (column_name);
  • 唯一索引：不允许任何重复的值插入到表中,为了性能，同时也为了数据的完整性
1
2
CREATE UNIQUE INDEX index_name
on table_name (column_name);
  • 组合索引：基于一个表的两个或多个列上创建的索引
1
2
CREATE INDEX index_name
on table_name (column1, column2);
  • 隐式索引：在创建对象时，由数据库服务器自动创建的索引。索引自动创建为主键约束和唯一约束。

如果值使用到一个列，则选择使用单列索引。如果在作为过滤的 WHERE 子句中有两个或多个列经常使用，则选择使用组合索引。

数据的更新和查询

  • 更新
1
2
3
UPDATE table_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];
  • 查询
1
2
3
SELECT column1, column2, columnN 
FROM table_name
WHERE [condition]

数据的备份和冗余（防止丢失和保持可用）

通过输入以下语句备份及还原：

1
2
sqlite> .backup 'backup.db'
sqlite> .restore 'backup.db'
  • 备份方式：直接以新文件的形式全量备份
  • 备份时间：手动

数据一致性，事务

事务（Transaction）是一个对数据库执行工作单元。事务（Transaction）是以逻辑顺序完成的工作单位或序列，可以是由用户手动操作完成，也可以是由某种数据库程序自动完成。数据一致性是指在事务开始之前和事务结束以后，数据库的完整性没有被破坏。

事务属性(ACID)

  • 原子性（Atomicity）：确保工作单位内的所有操作都成功完成，否则，事务会在出现故障时终止，之前的操作也会回滚到以前的状态。
  • 一致性（Consistency)：确保数据库在成功提交的事务上正确地改变状态。
  • 隔离性（Isolation）：使事务操作相互独立和透明。
  • 持久性（Durability）：确保已提交事务的结果或效果在系统发生故障的情况下仍然存在。

事务控制

  • BEGIN TRANSACTION：开始事务处理。
  • COMMIT：保存更改，或者可以使用 END TRANSACTION 命令。
  • ROLLBACK：回滚所做的更改。
    事务控制命令只与 DML 命令 INSERT、UPDATE 和 DELETE 一起使用。他们不能在创建表或删除表时使用，因为这些操作在数据库中是自动提交的。

事务执行代码示例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SQLiteDatabase db = dbHelper.getReadableDatabase();  
db.beginTransaction();  //开启事务                       
try {                                                
    db.delete("Book", null, null);                   
    ContentValues values = new ContentValues();      
    values.put("name", "sheng");                     
    values.put("author", "xiao");                    
    values.put("pages", 123);                        
    values.put("price", 20.5);                       
    db.insert("Book", null, values);                 
    db.setTransactionSuccessful();  //事务已经执行成功       
} catch (Exception e) {                              
    e.printStackTrace();                             
}finally{
    db.endTransaction();  //结束事务                     
}

首先调用SQLiteDatabase的beginTransaction()方法来开启一个事务，然后在一个异常捕获的代码块中去执行具体的数据库操作，当所有的操作完成之后，调用setTransactionSuccessful()表示事务已经执行成功了，最后在finally代码块中调用endTransaction()来结束事务

引用：https://www.jianshu.com/p/add11b6a8fa4

数据管理

数据分析

SQLite中的ANALYZE命令用于分析数据表和索引中的数据，并将统计结果存放于SQLite的内部系统表中，以便于查询优化器可以根据分析后的统计数据选择最优的查询执行路径，从而提高整个查询的效率。

  • 如果在ANALYZE命令之后没有指定任何参数，则分析当前连接中所有Attached数据库中的表和索引。
1
sqlite> ANALYZE;
  • 如果指定数据库作为ANALYZE的参数，那么该数据库下的所有表和索引都将被分析并生成统计数据。
1
sqlite> ANALYZE main;
  • 如果指定了数据库中的某个表或索引为ANALYZE的参数，那么该表和其所有关联的索引都将被分析。
1
2
sqlite> ANALYZE main.testtable;
sqlite> ANALYZE main.testtable_idx2;

引用：https://www.kancloud.cn/wizardforcel/sqlite-learning-manual/154304

数据管理工具

引用：https://www.oschina.net/news/43608/5-popular-and-free-sqlite-management-tools