SQLite数据库学习笔记

Android开发中可能会用到数据库，这里我打算说些数据库较少用到但是也很重要的一些知识

1：创建数据库，创建表，删除表，修改表，基于表的增，删，改，查等操作由于用得比较多，就掠过了（Android开发基本都知道）

下面的内容均以 一张员工表（Employee）作为示例,员工表的结构为：Employee(id,name,salary,empDate,departmentId)

2：SQLite的索引

create index 索引名 on 表名(列名,列名.......)

create index index_employee on Employee(departmentId,salary) 创建了一个按照 员工的部门 和 薪水 排列的索引

创建索引的命令其实较简单，一看便知，但是有一点还是需要说下：尽管你创建了索引但是还是会发现索引失效了，没什么用，跟没创建索引时没啥差别（特别是你创建的索引中包含的列很多时），其实这跟前导列有关，
所谓前导列，就是在创建复合索引语句的第一列或者连续的多列。比如通过：CREATE INDEX comp_ind ON table1(x, y, z)创建索引，那么x,xy,xyz都是前导列，而yz，y，z这样的就不是。下面讲的这些，对于其他数据库或许会有一些小的差别，这里以sqlite为标准。在where子句中，前导列必须使用等于或者in操作，最右边的列可以使用不等式，这样索引才可以完全生效。同时，where子句中的列不需要全建立了索引，但是必须保证建立索引的列之间没有间隙。举几个例子来看吧：
用如下语句创建索引：
CREATE INDEX idx_ex1 ON ex1(a,b,c,d,e,...,y,z);
这里是一个查询语句：
...WHERE a=5 AND b IN (1,2,3) AND c IS NULL AND d='hello'
这显然对于abcd四列都是有效的，因为只有等于和in操作，并且是前导列。
再看一个查询语句：
... WHERE a=5 AND b IN (1,2,3) AND c>12 AND d='hello'
那这里只有a，b和c的索引会是有效的，d列的索引会失效，因为它在c列的右边，而c列使用了不等式，根据使用不等式的限制，c列已经属于最右边。
最后再看一条：
... WHERE b IN (1,2,3) AND c NOT NULL AND d='hello'
索引将不会被使用，因为没有使用前导列，这个查询会是一个全表查询。
3）对于between，or，like，都无法使用索引。
如 ...WHERE myfield BETWEEN 10 and 20;
这时就应该将其转换成：
    ...WHERE myfield >= 10 AND myfield <= 20;
再如LIKE：...mytable WHERE myfield LIKE 'sql%';;
此时应该将它转换成：
...WHERE myfield >= 'sql' AND myfield < 'sqm';
    再如OR：...WHERE myfield = 'abc' OR myfield = 'xyz';
此时应该将它转换成：
...WHERE myfield IN ('abc', 'xyz');

3：SQLite的视图

视图使用起来也比较简单，把它当作表来使用即可

创建普通的视图：create view v_employee as select * from Employee where salary > 10000

创建临时的视图：create temp view v_employee as select * from Employee where salary > 10000

删除视图：drop view if exists v_employee 

4：SQLite的事物

注意：Android中每条SQL语句都是自动提交的

begin

delete from Employee where id= 100;

rollback

delete from Employee where id= 200;

commit

select * from Employee where id in (100,200);

大家可以去掉rollback语句，再次执行SQL比较下两次的结果

5：SQLite的触发器

引入两张新表（UserOk和userbak表）

1.删除
    Create  Trigger MAIN.[UserOK_DeleteOK] AFTER DELETE On [UserOK] FOR EACH ROW
    begin
        delete from userbak where uid=old.uid ;
    end;

当删除新表中的一条记录时 同时也删除备份表中同ID的数据

2.更新
    Create  Trigger MAIN.[UserOK_Update] AFTER UPDATE On [UserOK] FOR EACH ROW
    begin
        update userbak set username=new.username   where userbak.uid=old.uid ;
    end;

更新用户表时 ，同时更新用户备份表

6：SQLite的锁

SQLite采用粗放型的锁。当一个连接要写数据库，所有其它的连接被锁住，直到写连接结束了它的事务。SQLite有一个加锁表，来帮助不同的写数据库都能够在最后一刻再加锁，以保证最大的并发性。 SQLite使用锁逐步上升机制，为了写数据库，连接需要逐级地获得排它锁。

SQLite有5个不同的锁状态：未加锁(UNLOCKED)、共享 (SHARED)、保留(RESERVED)、未决(PENDING)和排它(EXCLUSIVE)。每个数据库连接在同一时刻只能处于其中一个状态。每 种状态(未加锁状态除外)都有一种锁与之对应。 

最初的状态是未加锁状态，在此状态下，连接还没有存取数据库。当连接到了一个数据库，甚至已经用BEGIN开始了一个事务时，连接都还处于未加锁状态。 
未加锁状态的下一个状态是共享状态。为了能够从数据库中读(不写)数据，连接必须首先进入共享状态，也就是说首先要获得一个共享锁。多个连接可以 同时获得并保持共享锁，也就是说多个连接可以同时从同一个数据库中读数据。但哪怕只有一个共享锁还没有释放，也不允许任何连接写数据库。 
如果一个连接想要写数据库，它必须首先获得一个保留锁。一个数据库上同时只能有一个保留锁。保留锁可以与共享锁共存，保留锁是写数据库的第1阶段。保留锁即不阻止其它拥有共享锁的连接继续读数据库，也不阻止其它连接获得新的共享锁。 
一旦一个连接获得了保留锁，它就可以开始处理数据库修改操作了，尽管这些修改只能在缓冲区中进行，而不是实际地写到磁盘。对读出内容所做的修改保存在内存缓冲区中。 
当连接想要提交修改(或事务)时，需要将保留锁提升为排它锁。为了得到排它锁，还必须首先将保留锁提升为未决锁。获得未决锁之后，其它连接就不能 再获得新的共享锁了，但已经拥有共享锁的连接仍然可以继续正常读数据库。此时，拥有未决锁的连接等待其它拥有共享锁的连接完成工作并释放其共享锁。 
一旦所有其它共享锁都被释放，拥有未决锁的连接就可以将其锁提升至排它锁，此时就可以自由地对数据库进行修改了。所有以前对缓冲区所做的修改都会被写到数据库文件。