掌握sqlite操作
最近用Ruby写了一个七牛的demo参赛作品，使用了sqlite3，用到很多操作，利用假期的时间，简单做一个快速掌握sqlite命令的小入门。
sqlite是一个开放源代码的数据库引擎，具有独立，无服务器依赖，零配置，支持事务等特点。sqlite一直以轻量级为特点，在移动和嵌入式设备上使用广泛，官方称其是世界上部署最广泛的数据库引擎。
强大的命令集
首先我们看一下sqlite提供了哪些强大的命令。
fileos:false以”.“开始的命令规则
看到了上面的全部命令，可以观察到，所有的命令都是以”.“开始的。而常用的sql语句是格式自由的，并且可以跨越多行，空白字符（whitespace）和注释可以出现在任何地方。而sqlite中以.开始的命令有更多的限制，具体如下
- 所有命令以.开始，并且.的左侧不包含任何空白字符
- 所有命令必须全部包含在一行输入行中
- 所有命令不能出现在sql语句之中
- 命令不识别注释
常用操作
创建一个数据库文件
fileos:false#找一个不存在的文件 09:35:16-androidyue/tmp$ cat test.db cat: test.db: No such file or directory #使用sqlite3 想要创建的数据库文件 09:35:28-androidyue/tmp$ sqlite3 test.db #进入sqlite，执行建表语句 sqlite> CREATE TABLE qn_uploaded(filePath VARCHAR(255),bucket VARCHAR(63),lastModified FLOAT); #退出sqlite sqlite> .exit #查看指定的文件，创建成功 09:42:26-androidyue/tmp$ cat test.db 09:44:45-androidyue/tmp$ dedqn_uploadedCREATE TABLE qn_uploaded(filePath VARCHAR(255),lastModified FLOAT)
打开已存在的数据库文件
fileos:false22:56:15-androidyue~ $ sqlite3 database_file.db 查看数据库
fileos:falsesqlite> .databases seq name file --- --------------- ---------------------------------------------------------- 0 main /home/androidyue/qiniu/.qiniu.db 1 temp
查看数据表
fileos:falsesqlite> .tables qn_uploaded
查看建表语句
fileos:false显示字段名称
fileos:false#没有开启 sqlite> select * from qn_uploaded; /home/androidyue/Documents/octopress/public//images/email.png|droidyue|1410096518.43964 #开启之后 sqlite> .header on sqlite> select * from qn_uploaded; filePath|bucket|lastModified /home/androidyue/Documents/octopress/public//images/email.png|droidyue|1410096518.43964
导出数据表结构和数据(文本形式)
fileos:falsesqlite> .dump qn_uploaded PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; CREATE TABLE qn_uploaded(filePath VARCHAR(255),lastModified FLOAT); INSERT INTO "qn_uploaded" VALUES('/home/androidyue/Documents/octopress/public/images/dotted-border.png','droidyue',1410096552.54864); COMMIT;
调整输出
sqlite3程序可以使用八种不同的格式显示结果。 这些格式是”csv”,“column”,“html”,“insert”,“line”,“list”,“tabs”,and “tcl”. 你可以使用.mode命令来进行切换输出格式
默认的输出模式list，使用了list模式，每条查询结果记录都会输出到一行，每一列使用一个分割符分割，默认的分割符是 “|“，list模式有一个常用的使用情况，就是当你想对查询结果记性额外处理（比如AWK处理）时，会事半功倍。
列表模式输出
fileos:false修改列表模式分割符
fileos:falsesqlite> .separator "," sqlite> select * from qn_uploaded; /home/androidyue/Documents/octopress/public//images/email.png,droidyue,1410096518.43964
使用Line模式
每行的输出格式为字段名 = 字段值
使用列模式
fileos:falsesqlite> .mode column sqlite> select * from qn_uploaded; /home/androidyue/Documents/octopress/public//images/email.png droidyue 1410096518.43964 /home/androidyue/Documents/octopress/public/images/RSS.png droidyue 1410096552.54764
输出内容
输出结果
默认情况下，所有的查询结果都是都是作为标准的输出展示。使用.output可以将输出结果定向到文件中。
fileos:falsesqlite> .output /tmp/test.txt sqlite> select * from qn_uploaded; sqlite> .exit 17:48:54-androidyue~/Documents/octopress/qiniu (master)$ cat /tmp/test.txt file bucket last ---- ------------- ---- /home/androidyue/Documents/octopress/public//images/email.png droidyue 1410096518.43964 /home/androidyue/Documents/octopress/public/images/RSS.png droidyue 1410096552.54764
备份和恢复
备份
fileos:false恢复
fileos:false数据库是应用开发中常用的技术，在Android应用中也不例外。Android默认使用了sqlite数据库，在应用程序开发中，我们使用最多的无外乎增删改查。纵使操作简单，也有可能出现查找数据缓慢，插入数据耗时等情况，如果出现了这种问题，我们就需要考虑对数据库操作进行优化了。本文将介绍一些实用的数据库优化操作，希望可以帮助大家更好地在开发过程中使用数据库。
建立索引
很多时候，我们都听说，想要查找快速就建立索引。这句话没错，数据表的索引类似于字典中的拼音索引或者部首索引。
建立索引
创建索引的基本语法如下
创建单列索引
索引真的好么
毋庸置疑，索引加速了我们检索数据表的速度。然而正如西方谚语 “There are two sides of a coin”，索引亦有缺点：对于增加，更新和删除来说，使用了索引会变慢，比如你想要删除字典中的一个字，那么你同时也需要删除这个字在拼音索引和部首索引中的信息。建立索引会增加数据库的大小，比如字典中的拼音索引和部首索引实际上是会增加字典的页数，让字典变厚的。为数据量比较小的表建立索引，往往会事倍功半。所以使用索引需要考虑实际情况进行利弊权衡，对于查询操作量级较大，业务对要求查询要求较高的，还是推荐使用索引的。
编译sql语句
sqlite想要执行操作，需要将程序中的sql语句编译成对应的sqliteStatement，比如select * from record这一句，被执行100次就需要编译100次。对于批量处理插入或者更新的操作，我们可以使用显式编译来做到重用sqliteStatement。
想要做到重用sqliteStatement也比较简单，基本如下：
请参考如下简单的使用代码
private void insertWithPreCompiledStatement(sqliteDatabase db) { String sql = "INSERT INTO " + TableDefine.TABLE_RECORD + "( " + COLUMN_INSERT_TIME + ") VALUES(?)"; sqliteStatement statement = db.compileStatement(sql); int count = 0; while (count < 100) { count++; statement.clearBindings(); bindLong(1, System.currentTimeMillis()); executeInsert(); } }
显式使用事务
在Android中，无论是使用sqliteDatabase的insert,delete等方法还是execsql都开启了事务，来确保每一次操作都具有原子性，使得结果要么是操作之后的正确结果，要么是操作之前的结果。
然而事务的实现是依赖于名为rollback journal文件，借助这个临时文件来完成原子操作和回滚功能。既然属于文件，就符合Unix的文件范型(Open-Read/Write-Close)，因而对于批量的修改操作会出现反复打开文件读写再关闭的操作。然而好在，我们可以显式使用事务，将批量的数据库更新带来的journal文件打开关闭降低到1次。
具体的实现代码如下：
insertWithTransaction(db) { 0; ContentValues values = new ContentValues(); try { beginTransaction(); count++ < values.put(COLUMN_INSERT_TIME,210)!important">insert(TABLE_RECORD, null,210)!important">values); } setTransactionSuccessful(); } catch (Exception e) { e.printStackTrace(); } finally { endTransaction(); } }
上面的代码中，如果没有异常抛出，我们则认为事务成功，调用db.setTransactionSuccessful();确保操作真实生效。如果在此过程中出现异常，则批量数据一条也不会插入现有的表中。
查询数据优化
对于查询的优化，除了建立索引以外，有以下几点微优化的建议
按需获取数据列信息
通常情况下，我们处于自己省时省力的目的，对于查找使用类似这样的代码
其中上面方法的第二个参数类型为String[]，意思是返回结果参考的colum信息，传递null表明需要获取全部的column数据。这里建议大家传递真实需要的字符串数据对象表明需要的列信息，这样做效率会有所提升。
提前获取列索引
当我们需要遍历cursor时，我们通常的做法是这样
badQueryWithLoop(Cursor cursor = String[]{COLUMN_INSERT_TIME},0)!important">null) ; cursor.moveToNext()) { long insertTime = getLong(getColumnIndex(COLUMN_INSERT_TIME)); } }
但是如果我们将获取ColumnIndex的操作提到循环之外，效果会更好一些，修改后的代码如下：
goodQueryWithLoop(null) ; insertTimeColumnIndex = COLUMN_INSERT_TIME); insertTimeColumnIndex); } close(); }
ContentValues的容量调整
sqliteDatabase提供了方便的ContentValues简化了我们处理列名与值的映射，ContentValues内部采用了HashMap来存储Key-Value数据，ContentValues的初始容量是8，如果当添加的数据超过8之前，则会进行双倍扩容操作，因此建议对ContentValues填入的内容进行估量，设置合理的初始化容量，减少不必要的内部扩容操作。
及时关闭Cursor
使用数据库，比较常见的就是忘记关闭Cursor。关于如何发现未关闭的Cursor，我们可以使用StrictMode，详细请戳这里Android性能调优利器StrictMode
耗时异步化
数据库的操作，属于本地IO，通常比较耗时，如果处理不好，很容易导致ANR,因此建议将这些耗时操作放入异步线程中处理，这里推荐一个单线程 + 任务队列形式处理的HandlerThread实现异步化。
