第五章 SQLite 数据库

第五章 SQLite 数据库

1.AIDL:原子性,一致性,隔离性,持久性

2.SQLite 对于写操作是进行了加锁，不过读操作是可以多任务同时操作的。

3.SQlite 对于Alert Table有所限制，只能进行添加和重命名列，不能修改删除列，如果修改删除列时，只能通过创建新表删除旧表来迂回实现。

4.在Android 2.2以后都支持外键，在其之前都不支持外键。默认是不开启外键的，需要手动开启。代码如下：

在SQLiteOpenHlper类中的onOpen方法中启用该功能if(!db.isOnlyRead()){    db.execSQL("PRAGMA foreign_key=ON;");}

5.check用于条件检查，例如:

create table students id primary key aotoincrement name vachar(20) check(length(name)>3),//在创建表时就限定了name字段长度应该大于3  

6.关键字unique组合唯一性，例如：

//手机号码与name应该保持唯一unique(name,mobile)

7.insert 操作时，当插入一条完整数据时，tablename后的字段面名称可以省略。

8.批量插入操作（将查询结果当作源数据集插入：

insert into students select * from student;//表名一样的情况insert into students (nameNew,gradeNew) select name,grade from student;//新表旧表字段名字不一致的情况

9.快速将旧表数据导入新表中(缺点：不可以将限制条件一并copy过来)：

create table stu as select * from students;  ／／它通常的用途时用做表升级时使用，将旧表数据导入临时表中，然后将旧表删除，然后创建新表，最后将临时表中的数据导入到新表中，这样就完成了一次表结构的升级。

10.Sqlite 中select 不支持全外连接，右外连接

11.Select 通用格式：

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

12.as 关键字，可以用作别名简化，使长SQL语句看起来整洁,更易读。示例：

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

13.select 常用的逻辑操作符：and,or,not

14.like 关键词可用于模糊查询，例如查找以t开头的学生信息

select * from students where name like "t%";  

15.Group By 与 Having

Group By 用于分组使用，例如统计每班级人数：

select count(*) from classes group by classID;    

Having 相当于Group By的‘where’关键字用于将分组后的结果进行过滤。示例（统计3班以后的各班级人数）:

select count(*) from classes group by classID having classID > 2;  

16.order by排序

asc 生序 ，desc 降序

select name from student order by syudentID;  

17.数量限定 limit,偏移量 offset

select * form students limit 1 offset 2;  //可以简写为 select * from students limit 1,2;  

18.distinct 去重(查询学生表中所有的class_id):

select distinct class_id * from students;    

19.聚合函数

• Count(*) 计算数量
• Avg() 平均值
• Min() 最小值
• Max() 最大值
• Length() 计算某个字断的长度
• Upper() 大写
• Lower() 小写
• Abs() 计算数值的绝对值

示例（计算学生名称的平均长度：

select Avg(Length(name)) from students;  

20.多表链接

 inner join xxx on 其实可以使用select from where 来替代  

21.Update 语句

* update table set update_list where predicates; *

update students set tel_no=123,cla_id=2 where name='xiaoming';  

22.Delete 语句

* delete from tables where predicates;*

delete form students where name = "xiaoming";  

23.修改表

SQLite 对与表的操作只有两种操作：重命名表与为表新增加字段
对于删除表中某些字段的操作只能通过创建新表的形式迂回进行。
alter table tableName {rename to newName | add column 新的字段}示例:

alter table student rename to studentNew;  alter table student add column age integer default 0;  

删除表中的某些字段的实现方式：
1.创建临时表
2.将旧表数据导入临时表中
3.删除旧表
4.将临时表重命名

24.索引

索引是用于加速查询的结构。默认情况下，当我们按照指定的条件查询表中的数据时会扫描所有的行来吵到匹配的数据。如果我们的表比较大时，且这个查询被使用的频率比较高，就可以为这个查询条件创建一个索引。索引类比与一本书前面的目录，它能够为指定的列创建一个索引表，每个索引指向特定的记录，这样就能加快数据库的查询效率。
语法: inser

create index [unique] index_name on students (column_list);  

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

25.创建视图

动态生成的虚拟表，在视图上只能进行select操作，进行其它操作都是无效的

create view view_name on select-stmt;  

26. 触发器

触发器的作用是在当特定的表发生特定的操作时进行预定义的操作。创建触发器的通用命令：
create [temp|temporary] trigger name [before | after] [insert|select|delete|update of column] on table begin action end;
删除时添加触发器的示例：

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

插入时创建触发器的示例:

create trigger insert_trid after insert on studentsbegin insert into new_log(stu_Id,stu_name,time) values(new.id,new.name,datetime('now'));end;  

需要注意的是，插入时新记录使用new,儿删除时则使用old。

27.drop命令

drop 可用于删除无力存储介质。例如删除表，视图，索引，触发器等。语法如下：

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

28.数据库事务

事务是一个数据库操作的执行单元。它定义了一条或多条SQL语句，这些语句要么被全部执行，要不全部不执行。它保证了多条SQL语句的原子性。事务有begin,commit,rollback 三个命令。示例：

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

在Android中使用事务，会提升SQL的执行效率

29.操作数据库时使用getApplicationContext()

mDatabase = new SQLiteDBHelper(getApplicationContext().getWritableDatabase());  

不使用具体的Activity作为参数传入的原因是：防止Activity不能释放，造成内存泄漏。

30.查询数据库数据时使用getColumnIndex方法，防止直接传入index引入认为的错误。

31.一种优雅的方式操作数据库

在单一线程池中执行数据库相关操作,执行完毕将数据通过主线程创建的Handler返回到主线程.其中泛型T为数据库的操作后结果的数据类型

public abstract class DbCommand<T> {private static ExecutorService executorService = Executors.newSingleThreadExecutor();//单线程的线程池private final static Handler sUIhandler = new Handler(Looper.getMainLooper());//主线程上的Handlerpublic void execute() {    executorService.execute(new Runnable() {        @Override        public void run() {            try{                postResult(doInBackground());            }catch (Exception e){                e.printStackTrace();            }        }    });}//在线程池中所要执行的操作public abstract T doInBackground();public void postResult(final T result) {    sUIhandler.post(new Runnable() {        @Override        public void run() {            dealResult(result);        }    });}//处理结果,运行在主线程(子类可去复写此方法,实现对数据库操作结果具体操作)public void dealResult(T result){}; }