SQLite数据库

SQLite3基本介绍

SQLite是一个遵守ACID（原子性、一致性、隔离性和持久性）的关系型数据库。SQLite不像常见的客户端\服务端结构的数据库，它将整个数据库作为一个单独的、可跨平台使用的文件存储在主机中。采用了写入数据将整个数据库文件加锁的简单设计，写操作只能串行进行，但是读操作可以多任务同时进行。

SQLite实现了多数SQL-92标准，但却失了某些特性比如仅部分支持触发器。支持大多数的复杂查询，但ALTER TABLE功能只能添加和重命名列，不能修改或删除列，因此当需要修改、删除表的列名时，只能通过重新创建表的方式进行。SQLite不进行类型检查，可以把字符串插入到整数列中。

SQLite优点：

1. 零配置，无需安装和配置
2. 存储在单一磁盘文件中的一个完整数据库
3. 数据库文件可以在不同字节顺序的机器间自由共享
4. 支持数据库大小至2TB
5. 足够小，全部源代码大致3万行C代码，250KB
6. 比目前流星的大多数数据库对数据的操作要快
7. 开源

SQLite基本结构

SQLite数据库采用模块化设计，主要被分割为两个部分，前端解析系统和后端引擎。

前端解析系统

前端解析系统负责将处理前端预处理应用程序传递过来的SQL语句和SQLite命令。对获取的编码分析、优化，并转换为后端能够执行的SQLite内部字节编码。

前端解析系统可分为3个模块，分别为词法分析器、语法分析器和代码生成器：

1. 词法分析器（Tokenizer）

   当执行一个包含SQL语句的字符串时，接口程序要把这个字符串传递给Tokenizer。Tokenizer的任务是把原有字符串分割成一个个标识符（token），并把这些标识符传递给解析器。

2. 语法分析器（Parser）

   语法分析器的工作是在指定的上下文中赋予标识符具体的含义。SQLite的语法分析器使用Lemon LALR(1)分析程序生成器来产生，Lemon做的工作与YACC/BISON相同，但它使用不同的输入句法，这种句法更不易出错。Lemon还产生可重入的并且线程安全的语法分析器。Lemon定义了非终结析构器的概念，当遇到语法错误时它不会泄露内存。

3. 代码生成器（Code Generator）

   语法分析器在把标识符组装成完整的SQL语句后，就调用代码生成器产生虚拟机代码，以执行SQL语句请求的工作。

后端引擎

后端是用来解释字节编码程序的引擎，该引擎做的才是真正的数据库处理工作。后段部分主要由3个模块组成，虚拟机、B/B+树和页面调度程序

1. 虚拟机（VM）

   VM模块是一个内部字节编码语言的解释器，它通过执行字节编码语句来实现SQL语句的工作。它是数据库中数据的最终操作者。它把数据库看成表和索引的集合。而表和索引则是一系列的元组或者记录。

2. B/B+树

   B/B+树模块把每一个元祖集组织进一个一次排好序的树状数据结构中，表和索引被分别置于单独的B+和B树中。该模块帮助VM进行搜索，插入和删除树中的元祖。它也帮助VM创建新的树和删除旧的树。

3. 页面调度程序（pager）

   页面调度程序模块在原始文件的上层实现了一个面向页面的数据库文件抽象。它管理B/B+树使用的内存内缓存（数据库页的），另外它也管理文件的锁定，并用日志来实现事务的ACID属性。

SQLite中的SQL语句

创建数据库

数据库创建并非通过SQL语句来实现，需要通过如下命令来实现：sqlite3 database.db（database表示数据库名），此时就会进入到该数据库的操作命令界面中。

创建表

创建表的语法为:

create [temp|temporary] table 表名 （字段1 数据类型约束，……，字段n 数据类型约束）;

每一条语句都以分号结尾,temp或temporary表示是临时表,临时表在连接会话结束时自动销毁

SQLite中的数据类型只有5种，如下表所示：

数据类型	说明
Null	数据值为空
INTEGER	整型
REAL	浮点型数据
TEXT	字符类型，使用数据库（UTF-8、UTF-16BE或者UTF-16LE）存放
BLOB	只是一个数据块，完全按照输入存放

SQLite和其他数据库为了保证兼容性最大化，支持类型近似的方式存储：

数据类型	Affinity类型
INT INTEGER TINYINT SMALLINT MEDIUMINT BIGINT UNSIGNED BIT INT INT2 INT8	INTEGER
CHARACTER(20) VARCHAR(20) VARYING CHARACTER(255) NCHAR(55) NATIVE CHARACTER(70) NVARCHAR(100) TEXT CLOB	TEXT
BLOB no datatype specified	NONE
REAL DOUBLE DOUBLE PRECISION FLOAT	REAL
NUMERIC DECIMAL(10,5) BOOLEAN DATE DATETIME	NUMERIC

再来看看约束条件:

约束条件	作用
NOT NULL	非空
UNIQUE	唯一
PRIMARY KEY	主键
FOREIGN KEY	外键(3.6.19支持之后外键)
CHECK	条件检查
DEFAULT	字段的默认值

了解完字段数据类型与约束之后来创建一个具体的表:

create table 'classes'(
    'id' integer primary key autoincreament,
    'major' text not null,
    'year' integer not null
)

也可以不用’'包裹表名和字段名

create table students(
    id integer primary key autoincreament,
    name varchar(20) check(length(name) > 3),
    tel_no varchar(11) not null,
    cls_id integer not null,
    unique(name,tel_no),
    foreign key(cls_id) references classes(id)
)

外键约束需要通过’PRAGMA foreign_key = ON’手动启用

外键约束格式如下:

FOREIGN KEY(字表中的列) REFERENCES 父表(父表中的字段)
[ON {DELETE|UPDATE} ACTION
[NOT] DEFERRABLE INITIALLY {DEFERRED|IMMEDIATE}]

第二行表示父表的记录删除或更新时子表中对应的操作,默认为not action，第三行表示父表的记录被删除或更新时字表对应操作的执行时机是立即强制执行还是等到整个事务结束，默认为立即执行

单个外键约束可以为ON DELETE和ON UPDATE配置不同的行为，外键在很多时候类似于触发器（trigger）。它的行为如下表所示：

行为	作用
NO ACTION	如果没有明确指定行为，那么默认就是NO ACTION，表示当父键被修改或者删除时，没有特别的行为发生
RESTRICT	存在一个或者多个子键外键引用了相应的父键时，应用程序禁止删除（ON DELETE RESTRICT）或者修改（ON UPDATE RESTRICT）父键。RESTRICT与普通的外键约束的区别是，当字段更新时，RESTRICT行为立即发生
SET NULL	父键被删除（ON DELETE SET NULL）或者修改（ON UPDATE SET NULL）将字段设置为null
SET DEFAULT	父键被删除（ON DELETE SET NULL）或者修改（ON UPDATE SET NULL）将字段设置为默认值
CASCADE	将实施在父键上的删除或者更新操作，传播给与之关联的子键。例如，如果设置students表的外键ON DELETE行为为CASCADE，那么当classes表中删除了id为2的记录之后，students中的cls_id为2的记录就会被自动删除。如果是更新操作，那么两个表中关联的数据一并被关联

如果要创建一个students表，当classes中的数据被删除或者更新时，students表中对应cls_id的记录就会发生相应的改变，即被自动删除或者更新值，SQL语句如下：

create table students (
    id integer primary key autoincrement,
    //...
    foreign key(cls_id) references classes(id)
    on delete cascade
    on update cascade
)

插入数据

插入语句语法为：

insert into 表名(字段列表) values(与字段列表中对应的字段值);

字段列表之间用逗号分隔，必须在表中存在。values括号中是要插入的数据，也用逗号分隔

实例：

当values中取值包含了表中所有字段值时字段列表可以省略：

insert into classes values(1,'software',2010);

不包含所有字段时不能省略字段列表：

insert into classes(major,year) values('markting',2008);

将查询结果插入到stu表中，查询字段需要与插入字段相同：

insert into stu select * from students;

查询字段需要与插入字段不相同的情况：

insert into stu(name,tel_no,cls_id) select name,tel_no,cls_id from students;

创建表时直接导入另一个表的数据：

create table stu as select * from students;

select语句：

select语句格式如下：

select [distinct] columns from tables
    where predictate
    group by columns
    having predictate
    order by columns
    limit count,offset;

常用select语句通常由select、from、where组成

字段与表的别名

多表查询时可能会产生相同字段名或表名太长的情况，通过字段别名方式可以修改字段名显示并简化sql语句

字段与表别名设置格式：

origin_name [[as] new_name]

实例：

select cls.major as cmj, students.name from classes as cls, students where cls.id=2 and cls.year > 2008;

where 条件过滤

where语句常用操作符如下：

操作符	功能
+	相加
-	相减
*	相乘
/	相除
=	等于
!=	不等于
<=	小于等于
>=	大于等于

常用逻辑操作符用and、or、not分别代表与、或、非用于组合多个条件

select * from students, classes where students.name='tiiime' and classes.major='software' and year=2010;

like是一个较为重要的关系操作符，作用是模糊匹配。

select * from students where name like "t%"

上面语句作用是查找所有名字以t开头的记录

Group by 分组

分组是将得到的结果集按照一定的规则划分为多个组

select count(*),cls_id from students group by cls_id;

上面sql语句是将students中的数据按照cls_id分组，分组中有类似于select中where字句的条件字句having，它能够为分组设置一些条件，如果不符合条件，那么分组就会被过滤掉，如下：。

select count(*),cls_id from students group by cls_id having cls_id > 2;

上面sql将分组查询条件设置为cls_id大于2

排序

select结果集的排序使用order by字句，排序有升序和降序两种，分别对应asc与desc，默认为升序排序。order by 后面若紧跟一个或多个字段，其间用逗号分隔，数据库就是根据指定的字段进行排序

select * from students order by cls_id desc;

上面sql按照班级id降序排列

select * from students order by cls_id desc,name asc;

上面sql按照cls_id的降序与name的升序排列

数量限定

查询到数据之后可以用limit来对结果进行数量限定，格式为：

limit 返回的数量 offset 偏移量

下面是查询偏移两位限制一条的语句：

select * from students limit 1 offset 2;

下面语句是上面语句的简化写法，offset值放在limit关键字后，后面跟着一个逗号与limit值

select * from students limit 2,1;

distinct去重

distinct用于去除select语句中重复的行，紧跟在select关键字后

使用如下：

select distinct cls_id from students;

由于cls_id会存在重复的情况，使用distinct后就会去除重复结果

聚合函数

函数名	作用
count()	计算数据集的数量
avg()	计算数据集中某个字段的平均值
min()	计算数据集中的最小值
max()	计算数据集中的最大值
length()	计算某个字段的长度
upper()	将字符串类型的字段值转换为大写
lower()	将字符串类型的字段值转换为小写
abs()	计算数值的绝对值

使用如下：

select count(*) from students;

count统计返回结果的数量，最终输出一个整数

select avg(length(name)) from students;

length获取各个name字段值的长度，然后通过avg计算这些长度的平均值

多表连接

数据关系通常需要跨越多个表，因此多表查询尤为重要

select * from students,classes where students.cls_id=classes.id;

使用students表的cls_id与classes表的id来进行关联

内连接

内连接使用关系代数交叉操作，根据指定的条件筛选出两个表的子集，只有两个表中都有符合条件的数据时该条数据才会被检索到

select * from students inner join classes on students.cls_id=classes.id;

结果与多表连接效果一致

左外连接

左外连接与内连接语法类似，inner join替换为了left outer join，区别是如果条件不匹配，那么结果集中会返回左表中的数据，右表中的数据由字段类型的空值填充

select * from students left outer join classes on students.cls_id=classes.id;

如果出现student.cls_id在classes.id中没有的情况的话这条记录classes表内的字段会被它的空值填充

SQLite中只支持内连接和左外连接，标准的SQL还有右外连接和全外连接，右外连接是坐标有不匹配的则会被用空值填充，全外连接则是任何一方存在不匹配数据则用空值填充

update语句

update语句用于更新表中的数据，格式为：

update table set update_list where predicate;

update_list是要修改的字段以及值，其实就是一个字段赋值语句形式为：字段名=新值，每个字段赋值语句通过逗号分隔开。where条件与select语句一致

update students set tel_no=4321,cls_id=3 where name='Jake';

delete语句

delete语句格式为：

delete from table where predicate;

delete 指出了功能，where限制了删除的条件，如果没有where条件，那么表中所有数据都会被清空

修改表

SQLite中alter命令没有实现标准SQL中的全部功能，只有修改表名和添加字段两个功能，删除字段等功能只能通过重新创建新表来实现，alter语法为：

alter table tableName {rename to newName | add column 新的字段}

上述语法表示alter table之后是要先操作表名，然后在rename to和add column命令之间二选一。rename to是将表名重命名，add column是添加字段。

alter table students rename to stu_table;
alter table students add column age integer default 0;

上面演示了重命名和添加age字段

如果要删除age字段就比较麻烦，需要创建一个新表，然后将旧表中的数据导入到新表，最后删除旧表，示例如下：

create table 'stu_temp' (
    'id' integer primary key autoincrement,
    'name' varchar(20) check(length(name)>3),
    'tel_no' varchar(11) not null,
    'cls_id' integer not null
);
insert into stu_temp select id,name,tel_no,cls_id from students;
drop table students;
alter table stu_temp rename to students;

创建索引

索引是用于加速查询的结构，如果表比较大并且查询被使用的频率比较高，就可以为这个查询条件指定的列创建索引。创建索引的语法为：

create index [unique] index_name on table_name (column_list)

index_name 代表索引名,table_name表示要在哪个表上创建索引。如果指定了unique关键字，那么该索引中的所有字段值必须是唯一。下面为student中的name创建索引：

create index stu_name_index on students (name collate nocase);

索引名为stu_name_index，字段为name，collate nocase代表排序规则与大小写无关。使用索引能够加速查询，但也会增加数据库体积，并会减慢insert、update、delete操作。

使用索引时SQLite会根据条件来选择是否使用索引。当查询条件是与索引的字段判等时，数据库则使用索引来加速，如果有多个索引字段，那么出现第一个不是判等的逻辑之后，后续的字段就不会使用索引。

比如test表中有a、b、c、d四个字段，在a、b、c上创建索引：

create index test_index on test(a,b,c)

然后使用如下SQL进行查询：

select * from test where a=1 and c=3;
select * from test where a=1 and b1 and c=3;

第一条语句只有a会使用索引c则不会，因为中间的b索引断了。第二条语句使用a、b会使用索引，因为b的条件为大于，所以后边的字段将不使用索引，当b为等于时，那么a、b、c三个字段才都会使用索引。

创建视图

视图是动态生成的虚拟表，它不会被存储到数据库文件中。它常用于将某些查询结果简化为一个视图，以便下次运用时简化SQL语句。创建视图的语法为：

create view view_name as select-stmt;

view_name即视图名称，select-stmt就是该视图代表的select语句。

为一条查询创建一个视图对应的sql如下：

create view student_view as select * from students,classes where students.cls_id=classes.id and cls_id>2;

通过这个视图来查询数据对应sql如下：

select * from student_view;

创建触发器

触发器的作用是当特定的表发生特定的操作时进行预定义的操作。创建触发器的通用命令为：

create [temp|temporary] trigger name [before|after] [insert|select|delete|update of columns] on table
begin
action;
end;

名称、表名、行为是触发器的3大要素，发生特定行为时要执行的操作定义在最后，也就是action所在的位置。用户可以通过before、after定义action执行在特定的事件之前或者之后。

下面举个例子，当删除学生时向delete_log表中添加一条记录。首先创建delete_log表，sql如下：

create table delete_log{
    stu_id int not null,
    stu_name text not null,
    time text
};

然后创建触发器：

create trigger delete_trig after delete on students
begin
insert into delete_log(stu_id,stu_name,time) values(old.id,old.name,datetime('now'));
end;

意思是当从students表中删除数据时，会将被删除的学生的id、name添加到delete_log表中，额外添加了一个删除时间。

再来看一个实例，当向students表中插入一条数据时，向new_log表中插入一条记录。首先将delete_log重命名为new_log

alter table delete_log rename to new_log;

然后创建插入时的触发器，sql如下：

create trigger new_log after insert on students
begin
insert into new_log(stu_id,stu_name,time) values(new.id,new.name,datetime('now'));
end;

需要注意的是，插入时新纪录使用new，删除时则使用old。通过new.id、new.name获取新插入的学生id与name,然后插入到new_log表中。

drop命令

drop命令用于删除物理存储介质，例如删除表、视图、索引、触发器等。命令格式为：

drop [table|view|index|trigger] name;

例如删除students表则为“drop table students’”，删除stu_view视图则为“drop view stu_view;”

数据库事务

事务是一个数据库操作的执行单元。它定义了一条或多条sql语句，这些语句要么被全部执行，要么不全部执行，它保证了多条sql语句的原子性。事务有begin、commit、rollback 3个命令，begin表示开始一个事务、commit表示整个事务操作成功、rollback表示回滚到begin之前。格式为：

begin;
//SQL 语句
[commit|rollback];

示例如下：

begin;
delete from students where cls_id = 2;
rollback;

上述sql在事务中删除了cls_id为2的数据，然后又进行了回滚，因此不会执行delete语句。当rollback替换为commit时，delete语句将会执行。