cascade sqlite 数据库_Android SQLite 数据库探索（二）— SQLite 中的 SQL 语句（上）...

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，它只支持内连接和左外链接。