Android SQLite Memento1 —— Basic SQL

1.数据类型&约束条件
数据类型：
SQLite中的数据类型只有5种，如下所示：
Null，数据值为空；
INTEGER，整型；
REAL，浮点型；
TEXT，字符类型，使用数据库编码存放；
BLOB，只是一个数据块，完全按照输入存放。

约束条件：
NOT NULL，非空；
UNIQUE，唯一；
PRIMARY KEY，主键；
FOREIGN KEY，外键；
CHECK，条件检查；
DEFAULT，字段的默认值；
AUTOINCREMENT，自动增长；

2.create语句
1）配置好安卓开发环境后，在命令行下输入”sqlite3 数据库名.db;”即可创建一个数据库文件。
2）创建表的SQL语法：
create [temp|temporary] table 表名 (字段1 数据类型约束, …字段n 数据类型约束);
注意，每一条SQL语句都以分号结尾；
temp或temporary为可选字段，表示这是一个临时表，连接会话结束就自动销毁；
数据类型和约束之间通过空格分开；
3）外键约束
FOREIGN KEY (子表中的字段名) REFERENCES 父表名 (父表中的字段名)
[ON {DELETE|UPDATE} action
[NOT] DEFERRABLE INITIALLY {DEFERRED|IMMEDIATE}]
第二行表示父表记录删除或更新时，子表对应的操作，默认no action，操作列表如下：
NO ACTION，什么也不做；
RESTRICT，存在一个或多个子键外键引用了相应的父键，应用程序禁止删除或者修改父键；
SET NULL，父键被删除或修改时，将字段设置为NULL；
SET DEFAULT，父键被删除或修改时，将字段设置为默认值；
CASCADE，父键被删除时，子键对应记录也被删除；父键更新时，子键对应记录也被一起更新。
4）示例：
create table classes (id integer primary key autoincrement, major text not null, year integer not null);
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.insert语句
1）语法
insert into 表名 (字段1, … 字段n) values (字段值1, … 字段值n);
values包含了所有字段值时，字段列表可以省略；
id为自动生成时，不需要指定id的字段和字段值；
2）扩展用法
a. 用已有的表创建一个临时表
create table stu as select * from students;
注意这个临时表只包含所有数据而没有约束，一般用于数据库升级。
b. insert into stu select * from students;
c. insert into stu (name, tel_no, cls_id) select name, tel_no, cls_id from students;
3）示例：
insert into classes (major, year) values (‘marketing’, 2008);
insert into classes (major, year) values (‘software’, 2010);
insert into classes (major, year) values (‘english’, 2009);
insert into students (name, tel_no, cls_id) values (‘Lucas’, ‘1234’, 1);
insert into students (name, tel_no, cls_id) values (‘mr.simple’, ‘5678’, 2);
insert into students (name, tel_no, cls_id) values (‘sophie’, ‘2222’, 3);
insert into students (name, tel_no, cls_id) values (‘lily’, ‘3333’, 3);
insert into students (name, tel_no, cls_id) values (‘tiiime’, ‘444444’, 2);
insert into students (name, tel_no, cls_id) values (‘chaossss’, ‘12345’, 2);
insert into students (name, tel_no, cls_id) values (‘Jake’, ‘1111111’, 2);

4.select语句
1）语法
select [distinct] columns from tables
where predicate
group by columns
having predicate
order by columns
limit count, offset;
2）别名
original_name as new_name
示例：
select cls.major as cmj, students.name from classes as cls, students where cls.id=2 and cls.year > 2008;
3）where子句
常用操作：+, -, *, /, =, !=, <=, >=；
模糊匹配：like;
示例：
下面一些实例演示了 带有 ‘%’ 和 ‘_’ 运算符的 LIKE 子句不同的地方：
WHERE SALARY LIKE ‘200%’ 查找以 200 开头的任意值
WHERE SALARY LIKE ‘%200%’ 查找任意位置包含 200 的任意值
WHERE SALARY LIKE ‘_00%’ 查找第二位和第三位为 00 的任意值
WHERE SALARY LIKE ‘2_%_%’ 查找以 2 开头，且长度至少为 3 个字符的任意值
WHERE SALARY LIKE ‘%2’ 查找以 2 结尾的任意值
WHERE SALARY LIKE ‘_2%3’ 查找第二位为 2，且以 3 结尾的任意值
WHERE SALARY LIKE ‘2___3’ 查找长度为 5 位数，且以 2 开头以 3 结尾的任意值
4）group by和having子句
select count(*), cls_id from students group by cls_id;
将得到的结果按照一定的规则分为多个组；
select count(*), cls_id from students group by cls_id having cls_id > 2;
为分组设置过滤条件（这里不能使用where）
5）order by子句
asc和desc分别对应升序和降序，默认升序;
select * from students order by cls_id desc, name asc;
6）数量限定
limit 返回的数量 offset 偏移量
select * from students limit 1 offset 2;
主要用于分页检索。
6）distinct去重
可以保证返回数据的唯一性。
select distinct cls_id from students;
7）聚合函数
count(), avg(), min(), max(), length(), upper(), lower(), abs()。
select avg(length(name)) from students;
8）多表连接
select * from students, classes where students.cls_id = classes.id;
9）内连接
select * from students inner join classes on students.cls_id = classes.id;
10）左外连接
select * from students left outer join classes on students.cls_id = classes.id;
与内连接的区别是如果条件不匹配，它的结果集中会返回左表中的数据，而右表中的数据将由字段类型的空值填充。
sqlite只支持内连接和左外连接，不支持右外连接和全外连接。

5.update语句
update 表名 set 字段1 = value, … 字段n = value where predicate;
update students set tel_no = 4321, cls_id = 3 where name = ‘Jake’;
更新可能失败。

6.delete语句
delete from 表名 where predicate;
如果没有where限制条件将删除表中所有的数据。

7.alert语句
alert table 表名 {rename to newName | add column 新的字段}
sqlite中的alert只能修改表名和添加字段，不能删除字段。
重命名：
alert table students rename to stu_table;
添加字段：
alert table students add column age integer default 0;
删除字段：
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;
alert table stu_temp rename to students;

8.索引
create index [unique] 索引名 on 表名 (column_list);
使用索引能够加速查询，但是会增加数据库的体积，并且会减慢insert，update，delete操作。
create index stu_name_index on students (name);

9.视图
create view 视图名 as select-stmt；
视图是动态生成的虚拟表，它不会被存储到数据库文件中。它常用于将某些查询结果简化为一个视图，以便下次运用时简化SQL语句。
create view student_view as select * from students, classes where students.cls_id=classes.id and cls_id > 2;
select * from student_view;

10.触发器
create [temp|temporary] trigger name [before|after] [insert|select|delete|update of columns] on table
begin
action;
end;
名称、表名、行为是触发器的三大要素，当特定的表发生特定的操作时进行预定义的操作。
示例：
1）删除触发器
create table delete_log (stu_id integer 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;
delete from students where name = ‘Jake’;
select * from delete_log;
2）插入触发器
create table insert_log (stu_id integer not null, stu_name text not null, time text);
create trigger insert_trig after insert on students
begin
insert into insert_log (stu_id, stu_name, time) values (new.id, new.name, datetime(‘now’));
end;
insert into students (name, tel_no, cls_id) values (‘char’, ‘15901234567’, 3);
select * from insert_log;

11.drop语句
drop [table|view|index|trigger] name;
drop命令会将对象从物理存储介质上删除……

12.事务
begin;
sql语句;
[commit|rollback];
事务是一个数据库操作的执行单元，它定义了一条或多条SQL语句，这些语句要么被全部执行，要么全部不执行，保证了多条sql语句的原子性。