Sqlite数据库基础

1.Sqlite基本介绍

SQLite是一个Acid的数据库管理系统，不是一般的C/S结构，而是被连接到程序中，成为应用的一部分，减少了跨进程通信的开销，采用写入数据时加锁，但同时也支持多任务操作。

SQLite只支持部分触发器，不支持对Alter table的删除和修改列，不进行类型检查。

主要优点：

1.零配置，无需安装。
2.跨平台，
3.最大支持2T
4.足够小，3万行代码
5.开源

主要有两部分构成：前端解析系统，后端引擎。

前端包括：词法分析器，将SQL分割成标识符，传递给解析器。

语法分析器：在指定上下文赋予标识符具体含义。

代码生成器：将标识符组装成sQL之后，生成虚拟机能够执行的代码。

后端引擎：

虚拟机：将前端生成的代码进行执行，

B/B+树：将数据的元组集合排序，帮助vm进行搜索，删除等操作。

页面调度程序：在原始程序的上层实现了面向页面的数据库文件抽象。管理缓存，文件锁定，和事务的ACID等。

2.SQLite的SQL

SQLite 的SQL复合SQL92标准，但是有些功能没有实现。

1.创建数据库

使用：sqlite3+ 数据库名，例如sqlite3 school.db

创建表

将实体数据拆分到各个表中，可以减小单表的体积，提升操作速度。

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

每条sql都以分号结尾，temp 关键字表示该表是临时表，临时表会在这次连接会话结束时自动销毁。

SQLite的内置数据类型只有5种,但是创建表时不仅仅能使用这些类型：

数据类型	说明
Null	数值为空
INTEGER	整型
REAL	浮点型
TEXT	字符型，使用数据库编码utf-8或者utf-16.utf-16le
BLOB	只是数据块，完全按照输入存放。

SQLite除了整型主列外，其他任何列的类型都是未定的，可以存任何类型的值，引擎会在查询过程中对其进行转换。

SQLite没有boolean类型，和日期类型。

类型相关的内容可以参考这篇文章：
http://www.cnblogs.com/bpasser/archive/2011/11/02/2233455.html

类型近似

类型近似是指，存储在该列的数据的推荐类型。任何列都可以存储任何类型，只是会优先 选择一些存储类型，优先选择的类型称为该数据的类型近似。

当创建表时，某一列的数据类型与内置的5种数据类型近似时会自动转换为近似的类型。
类型近似的转换规则表：

数据类型	Affinity类型
Interger INT TINYINT SMALLINT MEDIUMINT BIGINT UNSIGNED BIG INT INT2 INT8	INTEGER
CHARACTER(20) VARCHAR(255) VARYING CHARACTER(255) NCHAR(55) NATIVECHAR(70) NVARCHAR(100) TEXT CLOB	TEXT
BLOB NO DATATYPE	NONE
REAL DOUBLE DOUBLE PRECISION FLOAT	REAL
NUMERIC DECIMAL BOOLEAN DATE DATETIME	NUMERIC

约束

约束是在表的数据列上强制执行的规则。这些是用来限制可以插入到表中的数据类型。这确保了数据库中数据的准确性和可靠性。

约束可以是列级或表级。

列级约束仅适用于列，表级约束被应用到整个表。

以下是在 SQLite 中常用的约束。

NOT NULL 约束：确保某列不能有 NULL 值。

DEFAULT 约束：当某列没有指定值时，为该列提供默认值。

UNIQUE 约束：确保某列中的所有值是不同的，或者对表约束时列的组合是唯一的。

PRIMARY Key 约束：唯一标识数据库表中的各行/记录。

CHECK 约束：CHECK 约束确保某列中的所有值满足一定条件。

以创建classes表为例

CREATE TABLE classes （

id INTEGER PRIMARY KEY AUTOINCREMENT,

mayjor varchar（20）CHECK (length(name)>3)，

year integer not null ,

);

主键约束

PRIMARY KEY 约束唯一标识数据库表中的每个记录。在一个表中可以有多个 UNIQUE 列，但只能有一个主键。在设计数据库表时，主键是很重要的。主键是唯一的 ID。

一个表必须有一个主键，默认情况有一个默认的主键rowid，64位整形，自动增长。

外键约束

关系完整性概念，也称为外键，外键确保表中的关键值必须从里一个表中引用，且该数据必须在另一个表中存在，否则将操作失败或者引发其他连锁反应，用户可以定制该反应。

外键引用的表称为父表，含有外键的表称为子表。Android 开启外键功能，执行以下语句：

PRAGMA foreign_keys = ON；

外键约束的格式
FOREIGN KEY (子表的列) REFERENCES 父表（父表的列）

[ON {DELETE | UPDATE } 对应的操作

[NOT] DEFERRABLE INITIALLY {DEFERRED | IMMEDIATE}]

第二行表示，删除或者更新父表时子表对应的操作，

第三行表示，操作的时机，是立即执行还是等待事务结束执行，默认为立即执行，deferable 意为可延缓的。

例如：CREATE TABLE students （

id INTEGER PRIMARY KEY AUTOINCREMENT,

name varchar（20）CHECK (length(name)>3)，

tel_no varchar(11) not null unique,

cls_id integer not null,

FORENIGN KEY (cls_id) PREFERENCES classes(id),

UNIQUE(name,tel_no)

);

在students中插入数据时，cls_id必须要在classes表中存在，可以指定父表删除和更新对应id行时子表students的操作

操作可以有以下几种：

NO ACTION :不做操作

RESTRICT：禁止删除或更新父键

SET NULL：子键设为null

SET DEFAULT：设为默认值

CASCADE:：父表操作时，同时更新或删除子表，有点类似触发器

插入数据

语法：insert into 表名 （字段列表）values (字段对应的值)

如：insert into classes (id , majyor,year) values( 1, “chuanbo “,”2015”);
当字段列表包含表中全部列时可以省略字段列表。

批量插入

insert into 表名(字段列表) 数据集；

创建表stu ：create table stu (id integer primary key ,name varchar(20),tel_no varchar(11) not null,cls_id integer not null);

将students的数据复制到stu：insert into stu select * from students;也可以指定某些字段进行插入

快速的复制表：create table stu as select * from stdents; as关键字

此时，创建的表stu是没有约束的表，这种方式可以用来进行数据库升级，将原始表复制到临时表，使用临时表创建新表，删除原始表。

查询数据 Select 语句

数据库的重要功能就是保存数据然后进行查询检索。

SQLite支持除了右外连接和全外连接的所有操作。

操作的输出又可以作为另一个操作的输入，形成嵌套关系。

==select 相当于一个输出结果的关键字==,select的通用格式：

select [distinct] 字段列表 from 表列表 where 条件 group by 字段列表 order by 字段列表 limit 数量，offset 开始位置

从from开始将数据集进行条件筛选，最终得到结果。

from 有多张表时，会将各个表的数据输出。select 后的字段列表的字段前可以加上表名，如：

select classes.mayjor , students.name from classes ,students where classes.id =2;

字段和表的别名

在多表查询时，可能有相同的字段，在表明较长时可能会使SQL较复杂，使用别名来替代表名和字段名可以简化我们的SQL。

如：

select classes.major ,students.name from classes ,students where classes.id = 2 and classes.year > 2011;

别名简化：

select classes.major as clsmj ,students.name as stunm from classes as cls,students as stu where cls.id = 2 and cls.year >2011;

where 子句

where 用于确定筛选条件,可以包含算术运算操作和逻辑操作

+ ，-，*，/，=，！=，>,<,<=,>=

逻辑操作包括：or not，and

还有like 关键字，用于模糊匹配，如

select * from students where name like “t%”;输出包含所有包含t开头的名字。

group by 分组

分组是对聚合查询结果进行操作的，按照字段进行分组输出，如：

select count（*） ,cls_id from students group by cls_id;

得到学生表中的class.id 相同的人数和cls_id两列

having 过滤 groupby的结果

group by的结果 也可以进行筛选，使用having

如：

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

以cls_id分组输出cls_id 大于2 的students的人数

排序order by

asc 升序，desc降序

对select 的结果进行排序，如： select * from students order by name asc,year desc;

数量限定 limit

通常由于数据量较大只显示一部分数据，如分页显示

select * from students limit 10,offset 2;

显示students的前10条，并且从rowid 为2 开始，即从第三条开始检索。

distinct 去重

distinct 放在select后将查询结果的重复行去除，如：

select distinct cls_id from students;

查询cls_id同时去除重复的cls_id

聚合函数

聚合函数可以对字段、结果集做处理。有：

函数名	作用
count（）	计算数据集的数量
Avg（）	统计某个字段的平均值
Min（）	数据集的最小值
Max（）	数据集的最大值
Length（）	计算字段长度
Upper（）	字符串类型字段值转为大写
Lower（）	转为小写
Abs（）	计算数值的绝对值

聚合函数放在select后，

例如统计students表中各个班级的人数

==select 相当于一个输出结果的关键字==

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

students 名字的平均长度：

select avg(length(name)) from students;输出一个整数；

多表连接，根据where查多表

通过students的cls_id，可以查询classes中对应id的行每个字段信息，如：

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

得到的结果是每个student的字段和具有相同cls_id的classes里的字段，students的每一行都要在classes所有行中查找对应的id；

内连接，A inner join B on 条件

根据关系代数的交叉操作，对两个表进行筛选，。两个表中都符合条件的数据才会被输出。

同样是上面的功能：

内连接实现：select * from students inner join classes on students.cls_id = classes.id;

左外连接 left outer join on

与inner join 的区别是如果条件不匹配，那么结果集会返回左表中的数据，右表中的数据由空值填充。

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

如果students有cls_id在classes中不存在则数据结果集会返回students的行，在该结果集中classes的字段用空值填充。

右外连接和全外连接

右外连接和左外连接相反，左表又不匹配的行在结果集中用空值填充，全外连接，左表或者右表又不匹配的都用空值填充。SQLite不支持这两种查询。

子查询

SQLite 子查询
子查询或内部查询或嵌套查询是在另一个 SQLite 查询内嵌入在 WHERE 子句中的查询。
使用子查询返回的数据将被用在主查询中作为条件，以进一步限制要检索的数据。
子查询可以与 SELECT、INSERT、UPDATE 和 DELETE 语句一起使用，可伴随着使用运算符如 =、<、>、>=、<=、IN、BETWEEN 等。

以下是子查询必须遵循的几个规则：

1 子查询必须用括号括起来。

2 子查询在 SELECT 子句中只能有一个列，除非在主查询中有多列，与子查询的所选列进行比较。

3 ORDER BY 不能用在子查询中，虽然主查询可以使用 ORDER BY。可以在子查询中使用 GROUP BY，功能与 GROUP BY 相同。

4 子查询返回多于一行，只能与多值运算符一起使用，如 IN 运算符。

5 BETWEEN 运算符不能与子查询一起使用，但是，BETWEEN 可在子查询内使用。

update

update用于更新表的字段值，其实是个字段赋值语句。

update table students set name =”houson” , tel_no = “119” where id=0;

update 如果主键重复了可能会失败。

delete

delete from .. where ..

用于删除表的数据，delete from classes ，将删除classes中所有数据

修改表alter

Sqlite 的修改表包括：重命名和增加字段，不包括删除字段等。

alter table students rename to stu_table;

alter table students add column age default >0;

要删除字段需要创建，临时表进行数据复制。drop掉原始表。

实现如下

create table stu_tmp (id integer primary key autoincrement ,name varchar(20) not null,tel_no varchar(11) not null ,cls_id integer not null) ;

insert into stu_tmp id,name,tel_no select id,name,tel_no from students;

drop table students;

alter table stu_tmp rename to students

创建索引

当表比较大，而查询频率较高时，可以使用索引表为某些字段进行查询加速，索引就像书的目录存储了每条记录的地址。

create index [unique] 索引名 on 表名 （字段列表）

创建索引表会使数据库体积增大，减慢insert ，delete，insert 的操作，需要权衡使用。

SQLite根据查询条件判断是否使用索引。

当条件与索引字段判等时，使用索引，当多个字段时，从第一个不等的字段开始，后续字段不使用索引。

如：

create index student_table_index on students (id ,name,cls_id);

select * from students where id = 1 and cls_id = 2;

由于name没有出现在查询条件中，因此b开始和索引字段不判等，只有id 字段会使用索引。

select * from students where id >1 and name =”houson” and cls_id =4;

此时，虽然字段的名字都有但是，id是>,不是= ，所以不判等。不使用索引

创建视图 view

视图是动态生成的虚拟表，存储的是一条select 语句的结果集。操作视图就是相当于执行这条select语句，view不会存储到数据库中，方便简化查询。

create [TEMP | TEMPORARY] view 视图名 as select语句；

select * from view_name ;

视图（View）是一种虚表，允许用户实现以下几点：
用户或用户组查找结构数据的方式更自然或直观。
限制数据访问，用户只能看到有限的数据，而不是完整的表。
汇总各种表中的数据，用于生成报告。
SQLite 视图是只读的，因此可能无法在视图上执行 DELETE、INSERT 或 UPDATE 语句。但是可以在视图上创建一个触发器，当尝试 DELETE、INSERT 或 UPDATE 视图时触发，需要做的动作在触发器内容中定义。

删除视图

要删除视图，只需使用带有 view_name 的 DROP VIEW 语句。DROP VIEW 的基本语法如下：
sqlite> DROP VIEW view_name;

创建 触发器

触发器用于当特定表的操作操作时的回调函数，

create [temp | temporary] trigger 触发器名 [before | after ] [insert | update | delete | select of 字段列表 ] on 表名

begin

操作

end；

三大要素：触发器名，表名，操作。before|after表示操作在事件发生之前还是之后

可以用于生成日志。

例如：create table delete_log (stu_id ,name );

create triggle delete_trig after delete on students

begin

insert into delete_log (stu_id , name) values(old.id,old.name);

end；

还可以添加时间字段datetime(‘now’);

删除时被删除的数据为old，插入时为new

drop命令

用于删除物理存储介质，表、视图、索引、触发器等。

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

数据库事务

事务是数据库操作的执行单元，定义了一条或多条SQL语句，要么不被执行，要么全被执行。begin、commit、roolback三条命令。

begin

delete from students where id = 1;

rollback;

rollback回滚表示delete没有被执行。

事务保证了原子性，提升SQL执行效率。

SQLite资料：http://www.runoob.com/sqlite/sqlite-data-types.html