1. 创建数据库
数据库的创建并非通过 SQL 语言来创建。比如你想创建一个名为 school.db 的数据库，在命令行下输入：
sqlite3 school.db;
即可，此时会进入该数据库的操作命令页面中。
不过，我们不需要手动创建数据库，因为构造 SQLiteOpenHelper 时可以传入数据库的名字，若当前程序检测到不存在该数据库，则会自动帮我们创建。
2. 创建表
create [temp | temporary] table 表名 (字段1 数据类型约束, ......, 字段n 数据类型约束);
注意要加末尾的分号；
table 前面加上 temp 或 temporary 表示这是一个临时表，临时表会在这次连接会话结束时自动销毁。
SQLite 中的数据类型只有 5 种：
| 数据类型 | 说明 |
|---|---|
| Null | 数据值为空 |
| Integer | 整型 |
| Real | 浮点型 |
| Text | 字符类型，使用数据库编码（UTF-8、UTF-16BE 或者 UTF-16E）存放 |
| Blob | 只是一个数据块，完全按照输入存放 |
- SQLite 没有布尔存储类型，可以使用 Integer 作为存储类型，0 为 false，1 为 true。
- SQLite 没有代表日期和时间的数据类型，可以将日期或时间转换为字符串或者整型，然后通过 Text 或 Integer 类型存储在数据库中。
2.1 类型近似
为了使 SQLite 与其他数据库兼容性最大化，SQLite 支持列上“类型近似”。类型近似指的是存储在列上数据的推荐类型。当创建表时某个列的数据类型与内置的 5 种类型相似时，会被转换为与之相似的内置类型。比如说，varchar(255) 会选择使用 Text 类型来存储。
看完列的数据类型，我们来看看列的约束。
常用的约束条件如下：
| 约束条件 | 作用 |
|---|---|
| not null | 非空 |
| unique | 唯一 |
| primary key | 主键 |
| foreign key | 外键 |
| check | 条件检查 |
| default | 字段的默认值 |
create table students (id integer primary key autoincrement,
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)
);
如果要在一个表中将多个字段组合起来使之成为唯一，可以在 SQL 语句最后使用 unique 声明，如上面：
unique(name, tel_no)
2.2 主键约束
在 SQLite 中，一个表必须有一个主键。SQLite 表中都默认含有一个内置主键，称为 rowid，它是一个 64 位的整型字段。用户可以使用 primary key 将表中的一个字段定义为主键，如果该字段为整型，还可以添加 autoincreament 使之自动增长。
2.3 外键约束
定义外键约束的格式如下：
foreign key (子表中的列) references 父表(父表中的字段)
[on {delete | update} action
[not] deferrable initially {deferred | immediate} ]
第一行必选，二三行可选。第二行表示父表的记录删除或更新时子表中对应的操作，默认为 no action；第三行表示父表的记录删除或更新时，子表对应操作的执行时机，是立即执行还是等到整个事务结束。
立即执行：
not deferrable initially deferred
not deferrable initially immediate
not deferrable
deferrable initially immediate
deferrable
延迟执行：
deferrable initially deferred
on delete 和 on update 的 action 可以有如下几种：
| 行为 | 作用 |
|---|---|
| no action | 默认为 no action，父键被删除或修改时，没有特别的行为发生。 |
| restrict | 应用禁止程序删除（on delete restrict）或修改（on update restrict）父键。 |
| set null | 父键被删除或修改，将字段设置为 null。 |
| set default | 父键被删除或修改，将字段设置为默认值。 |
| cascade（串联） | 将父键上的删除（on delete cascade）或更新（on update cascade）操作，传递给与之相关的子键，删除则一并删除，更新则一并更新。 |
create table students (id integer primary key autoincrement,
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)
on delete cascade
on update cascade
);
3.6.19 版本之后的 SQLite 支持外键，但为了兼容以前的程序，默认没有启用该功能。如果要启用该功能需要执行 PRAGMA foreign_keys = ON 命令，可以在 SQLOpenHelper 类中的 onOpen 函数中启用该功能。
@Override
public void onOpen(SQLiteDatabase db) {
super.onOpen(db);
if (!db.isReadOnly()) {
// 启动外键
db.execSQL("PRAGMA foreign_keys = 1;");
// 或者这样写
String query = String.format("PRAGMA foreign_keys = %s", "ON");
db.execSQL(query);
}
}
3. 插入数据 — insert
// 插入单条数据
insert into 表名 (字段列表) values(与字段列表中对应的字段值)
// values 中包括表中所有字段值时，字段列表可以忽略
insert into classes values(1, 'software', 2010);
// 指定了字段列表
insert into classes (major, year) values('markting', 2008);
// 批量插入数据
insert into 表名 数据集
insert into stu select * from students;
insert into stu (name, tel_no, cls_id) select name, tel_no, cls_id, from students;
可以在建表的同时，将另一个表的数据导入正在建的表中：
// 表名后面添加一个 as 关键字，后面接数据
create table stu as select * from students;
注意：这样创建出来的表不会导入原始表的字段约束，因此，这种方式不常用。它的常用场景是做表升级，首先通过这种形式，创建一个临时表，第二步删除原始表，然后重新创建一个新表，再将临时表的数据导入到新创建的表中，这样就完成表结构的升级。
4. 选择数据 — select
Select 语句通用格式：
select [distinct] columns from tables
where predictate
group by columns
having predictate
order by columns
limit count, offset ;
4.1 字段与表的别名
使用别名来替代字段或表的原名，简化 sql 语句。
// as 可以省略
origin_name [as] new_name
// classes 变为 cls
select cls.major as cmj, students.name
from classes as cls, students
where cls.id = 2 and cls.year > 2008 ;
4.2 where 条件过滤
where 语句设置获取目标对象的条件。
select name
from students
where name = 'Jerry' ;
where 语句有个关系操作符叫 like，它的作用是模糊匹配，而 = 是精确匹配。
select * from students where name like "J%" ;
4.3 Group by 分组
聚合有一个重要功能就是分组。
// 按照 cls_id 分组，并且只取 cls_id > 2 的数据
select count(*), cls_id from students group by cls_id having cls_id > 2 ;
4.4 排序
select 结果集的排序使用 order by 字句，排序有升序和降序两种，分别对应 asc 与 desc，默认为升序排序。order by 后接要排序的字段，可以有多个，用逗号隔开。
select * from students order by cls_id desc ;
select * from students order by cls_id, name asc ;
4.5 数量限定
返回特定数量的记录。
limit 返回的数量 offset 偏移量
// 从索引为 3 的记录开始索引，并只返回 1 条数据
select * from students limit 1, offset 2 ;
// 简写，offset 的值放在 limit 后面，然后跟着一个逗号和 limit 值
select * from students limit 2, 1 ;
limit 与 offset 通常用于分页检索数据，即数量较大时，首先加载一部分数据，当用户下拉时，再修改 offset 数量，从而检索到其他数据。
4.6 distinct 去重
去除 select 语句返回数据中重复的行。
select distinct cls_id from students ;
4.7 聚合函数
| 函数名 | 作用 |
|---|---|
| Count() | 计算数据集的数量 |
| Avg() | 计算数据集中某个字段的平均值 |
| Min() | 计算数据集中的最小值 |
| Max() | 计算数据集中的最大值 |
| Length() | 计算某个字段的长度 |
| Upper() | 将字符串类型的字段值转换为大写 |
| Lower() | 将字符串类型的字段值转换为小写 |
| Abs() | 计算数值的绝对值 |
// 返回结果集的数量
select count(*) from students ;
4.8 多表连接
下面以 students 的 cls_id 字段与 classes 表的 id 相等为条件进行查询：
select * from students, classes where students.cls_id = classes.id ;
对于 students 表中每一行都需要查找 classes 表中的所有行，以查找符合条件的数据。结果包括 students 表和 classes 表的所有字段。
4.9 内连接
内连接使用关系代数交叉操作，根据指定的条件筛选两个表的子集。例如：
select * from students inner join classes on students.cls_id = classes.id ;
结果与多表连接的结果一样，语法上有差别，内连接使用 inner join 关键字，并且用 on 关键字取代 where 语句。
4.10 左外连接
左外连接使用 left outer join 而不是 inner join，它与内连接的区别是如果条件不匹配，那么它的结果集中会返回左表中的数据，右表中的数据由字段类型的空值填充。
select * from students left outer join classes
on students.cls_id = classes.id ;
标准的 SQL 还有右外连接、全外连接。右外连接的特点是左表有不匹配的则会用空值填充，全外连接则是任何一方存在不匹配数据则用空值填充。但是对于 SQLite，它只支持内连接和左外链接。
