2018-02-06

Android数据库（2）、SQLite数据库常用的数据结构

我们都知道通过DDL语言来描述数据库中包含的数据结构，在RDBMS中，定义了标准的几种数据结构，为四种类型，分别是表（table）、视图（View）、触发器（trigger）、索引（Index），这篇博客主要讲以上这四种数据结构的基本用法。

一、表（Table）

我们使用数据库最开始接触到的就是表，同样的也是最为常用的数据结构，最简单的数据结构，且其他数据结构都是建立在它之上的。

1、关系模型

表来自于数学模型，在数学模型上面称为关系模型，基于一阶谓词逻辑的分支而诞生。该模型重在描述关系，并不强调顺序所以也是无序元组集合，其类型主要由关系的特性来定义。直观上面有点类似于我们日常使用的电子表格。关系模型不仅仅只是描述关系，同时它还描述关系的若干操作，最重要的几个关系是我们日常开发当中可以感知的，比如：约束（主键约束，外键约束，列约束等）、投影（查询）、连接（多表通过主外键进行连接查询，左外连接、右外连接、全外连接等，在数学中也被成为叉积），

2、关系模型的操作

2.1、投影和约束

这两个操作非常的相似，除了它们分别影响关系的行和列之外，约束一个关系产生一个新关系，其行是原有关系中行的子集。投影几乎和约束一样，除了新关系是原关系列的子集以外。

二、触发器（Trigger）

1、触发器的定义：

我们定义的触发器最后都是会被SQLite引擎所回调的，相当于Java中我们写了一个回调函数，SQLite引擎会自动根据我们定义的事件去触发我们定义的触发器，然后执行我们所指定动作，该动作可能是该表其他值的改变，可能就是该表所引用其他表的数据的改变。这便是触发器。

2、触发器的分类：

在SQL server当中将触发器主要分为了两种，第一种就是“Instead of”触发器，第二种叫做“After”触发器。

2.1、Instead of触发器，

这种类型的触发器在执行真正的“插入之前”会被SQL引擎调用，除了表之外，还可以将该触发器运用于视图当中，用来让视图可以支持更新操作。

这种类型的触发器会替代所要执行的SQL语句，意思就是说本来要执行的SQL语句斌不会真正的执行。下面可以看一个例子。

create trigger tigger_users_delete
on users
instead of Delete
as
begin
    select * from deleted
end

delete from users where userNo= 4;

上述触发器定义了，当我们去删除表users当中的数据时，进行触发，所要做的动作就是去查询user表，

但是当我们最后执行了delete语句之后，发现userNo=4的数据还在，并没有被删除，原因就是触发器代替了所有执行的delete，而在触发器当中并未真正意义上的删除。

2.2、After 触发器

After触发器是在DML语句执行之后被触发的。并且After触发器只能修饰表，不能修饰视图。
After触发器主要用途就是在表进行DML语句之后，用来修改其他表的值，可以通过这种触发器来判断外键约束，和引用完整性。

3、触发器的原理

SQL引擎为每一个触发器都会额外的去创建两张表，这两张表都是由系统来进行维护的，它们存在于数据库引擎的内存当中，而不是在数据库当中，可以理解为是两张虚拟的表。并且这两张表都是由SQL引擎进行维护。且这两张表的结构总是和触发该触发器的表结构相同，在触发器工作结束以后，这两张表第一时间被删除，
对表的操作

4、触发器的执行过程

4.1、如果一个DML因为语句违反了约束，那么这条SQL语句就没有执行成功，因此“After”触发器也不会激活
4.2、“Instead Of”触发器可以取代激发它的操作来执行，它在Inserted表和Delete表刚刚建立，其他任何操作还没有发生的时候被执行。因为“Instead Of”触发器在约束之前执行，所以它可以对约束进行一些预处理。

5、触发器的基础语法

5.1、创建触发器，

CREATE TRIGGER trigger_name [BEFORE|AFTER] event_name 
ON table_name
BEGIN
 -- Trigger logic goes here....
END;

上面提到的event_name就是相对的插入操作还是删除操作，以及更新操作，我们可以在表名字后面定义是什么触发器，是行触发器还是语句触发器（FOR EACH ROW），最后我们可以在BEGIN和END中间填入，相应的动作语句就OK了。下面就是一个创建相应更新操作的例子。

create trigger name 
after update of _id
on users
begin
    -----
end;

创建的触发器的功能为每当更新了_id这个字段以后,执行相应的动作。

5.2、删除触发器，

通过DML当中的drop关键字来删除相应的触发器，但是相对的必须事前知道触发器的名字

1	sqlite> DROP TRIGGER trigger_name;

5.3、查看表中所存在的触发器

SQLite引擎会把当前数据库中所有的触发器存储在一个名叫sqlite_master的表中，如果我们想要查找当前数据库当中所有的触发器，那么我们只需要查询着一张表即可

1 2	sqlite> SELECT name FROM sqlite_master WHERE type = 'trigger’;

如果我们想看一个表上面到底被添加了多少张触发器，我们可以使用如下的查询语句进行查询

1 2	sqlite> SELECT name FROM sqlite_master WHERE type = 'trigger' AND tbl_name = 'COMPANY';

6、触发器的例子

下面我们通过一个例子来了解触发器应该如何被应用，

--创建班级表
create table class(
    id integer primary key autoincrement, --班级编号   
    className nvarchar(50) --班级名称
);

--创建学生表
create table student(
    id integer primary key autoincrement, --编号   
    stuName nvarchar(20),    --学生名称
    stuSex bit,    --性别
    stuAge integer ,     --年龄
    classId   --班级编号
);

--创建插入触发器 （创建学生时要触发插入触发器去判断是否存在该班级，存在插入成功，反之插入失败）
create trigger fk_Insert
before insert on student
for each row 
begin
    select raise(rollback,'还没有该班级') 
    where (select id from class where id = new.classId ) is null;    
end;

--创建更新触发器 （更新学生时要触发更新触发器去判断是否存在更新班级，存在更新成功，反之更新失败）
create trigger fk_Update
before update on student
for each row 
begin
    select raise(rollback,'还没有该班级')
    where (select id from class where id = new.classId)is null; 
end;

--创建删除触发器 （删除班级时，首先根据班级编号删除该班级学生）
create trigger fk_Delete
before delete on class
for each row
begin
     delete from student where classId = old.classId;
end ;

insert into class(className) values('s1t64');
insert into student(stuName,stuSex,stuAge,classId)values('zhangsan',1,23,1);
update student set stuName='lishi',classId=1 where id = 1;
select * from class ;
select * from student limit 0,100 ; -- 分页查询从索引0开始查找，100条数据

一、表（table）

1、表是什么
2、表的使用场景
3、使用表的相关优缺点
4、相关语法
5、简单示例

二、触发器（trigger）

1、触发器是什么
2、使用场景
3、使用触发器的优缺点
4、相关语法
5、简单示例

三、视图（View）

1、视图是什么
2、使用场景
3、使用视图的优缺点
4、相关语法
5、简单的例子

四、索引（Index）

1、索引是什么
2、索引的分类
3、索引的原理
4、使用场景
5、使用索引的优缺点
6、简单的例子

五、事物和锁特性

1、事务
2、事务的范围
3、数据库的锁
4、数据库的死锁
5、事务的种类
6、使用注意点

备注：未完成，由于查看资料时，发现很大一部分内容是两种说法，所以本人以列出大纲，自行研究中，下面网址，可以供读者学习这一方面内容

http://blog.csdn.net/hustyangju/article/details/17488309
http://blog.csdn.net/lzq_it/article/details/6960176
http://www.runoob.com/sqlite/sqlite-trigger.html
http://www.cnblogs.com/rainman/p/3675834.html
https://www.cnblogs.com/richzhang/archive/2013/04/24/3041280.html
https://www.cnblogs.com/envoy/p/4751140.html
https://stackoverflow.com/questions/172303/is-there-a-regular-expression-to-detect-a-valid-regular-expression