SQLite

一、SQLite介绍

SQLite，是一款轻型的数据库，是遵守ACID的关联式数据库管理系统，它的设计目标是嵌入式的，而且目前已经在很多嵌入式产品中使用了它，它占用资 源非常的低，在嵌入式设备中，可能只需要几百K的内存就够了。它能够支持Windows/Linux/Unix等等主流的操作系统，同时能够跟很多程序语 言相结合，比如Tcl、C#、PHP、Java等，还有ODBC接口，同样比起Mysql、PostgreSQL这两款开源世界著名的数据库管理系统来讲，它的处理速度 比他们都快。

二、SQLiteOpenHelper使用方法

JAVA中使用JDBC来访问数据库，而Android则设计使用一套自己的API来访问数据库，必须要是用到SQLiteOpenHelper方法来访 问数据库，不使用也可以，但会很麻烦。SQLiteOpenHelper是一个管理数据库的创建、版本等操作的帮助类。可以创建一个子类去实现一些方法。SQLiteOpenHelper是一个抽象类，用户需要继承这个类，并实现该类中的一些方法。

使用SQLiteOpenHelper来访问数据库必须要使用一些方法：

方法名	描述
getReadableDatabase()：	创建或者打开一个可读的数据库，会返回一个SQLiteDatabase对象，基于这个对象可以进行增删改查操作。
getWritableDatabase()：	创建或者打开一个（可读）可写的数据库，同样也会返回一个SQLiteDatabase对象，基于这个对象可以进行增删改查操作。
onCreate(SQLiteDatabase db)：	回调函数，当数据库第一次被创建的时候就会调用。
onOpen(SQLiteDatabase db)：	回调函数，当数据库被打开的时候就会被调用。
onUpgrade(SQLiteDatabase db,int oldVersion,int newVersion)：	回调函数，当数据库被更新的时候就会被调用。
close()：	关闭数据库。

三、             使用adb访问SQLite

adb的全称为Android Debug Bridge，就是起到调试桥的作用。通过adb我们可以在Eclipse中方面通过DDMS来调试Android程序，说白了就是debug工具。 adb的工作方式比较特殊，采用监听Socket TCP 5554等端口的方式让IDE和Qemu通讯，默认情况下adb会daemon相关的网络端口，所以当我们运行Eclipse时adb进程就会自动运行。

adb是android sdk里的一个工具, 用这个工具可以直接操作管理android模拟器或者真实的andriod设备(如G1手机). 它的主要功能有:　　

1)      运行设备的shell(命令行)　　

2)      管理模拟器或设备的端口映射　　

3)      计算机和设备之间上传/下载文件　　

4)      将本地apk软件安装至模拟器或android设备　　

ADB是一个 客户端-服务器端 程序, 其中客户端是你用来操作的电脑, 服务器端是android设备.

打开命令行，输入adb，就会看到adb的相关命令。如果看到的是adb不是内部或外部命令这个提示，说明环境变量没有配置好，以前path只配置了 sdk目录下的tools，对于旧版本中adb.exe就在该目录下，可以正常启动。但是对于新版本sdk，adb.exe被移动到了platform- tools目录下，这一点需要注意。

如果此时提示device not found:

则是因为模拟器没有启动，需要先启动模拟器，启动模拟器可以从Eclipse上的Opens Android SDK and AVD Manageer上选择需要启动的模拟器进行启动，也可以从命令行进行启动：

SQLite数据库放在./data/data/当前虚拟机运行的应用程序的包名/databases/目录里。当没有创建数据库的时候，是没有databases目录的，只有当数据库创建成功时才有。(我的程序包名均为com.android.activity)

    此时只有lib目录，没有databases目录。当运行创建程序之后，就会创建databases目录，且该目录下有数据库。

 

四、             SQLite增删改查

DatabaseHelper.java

//DatabaseHelper作为一个访问SQLite的助手累，提供两个方面的功能

//第一，getReadableDatabase()和getWriteableDatabase()可以获得SQLiteDatabase对象，通过这个对象对数据库进行操作

//第二，提供了onCreate和onUpdate两个回调函数，允许我们在创建和升级数据库时，进行自己的操作

public class DatabaseHelper extends SQLiteOpenHelper {

   

    public static final int VERSION=3;

    //在SQLiteOpenHelper的子类中，必须有下面的第一个构造函数

    //第一个参数：一个Activity对象，第二个参数：数据库的名字,第三个参数：第四个参数：当前数据库的版本

    public DatabaseHelper(Context context, String name, CursorFactory factory,

           int version) {

       //必须通过super调用父类当中的构造函数

       super(context, name, factory, version);

       // TODO Auto-generated constructor stub

    }

    public DatabaseHelper(Context context,String name,int version){

       this(context,name,null,version);

    }

    public DatabaseHelper(Context context,String name){

       this(context,name,VERSION);

    }

    //onCreate函数是第一次创建数据库的时候执行，实际上是在第一次得到SQLiteDatabase对象的时候，才会调用这个方法

    //也就是在DatabaseHelper调用getReadableDabase或者getWriteableDatabase时，如果是第一次创建数据库才执行

    @Override

    public void onCreate(SQLiteDatabase db) {

       // TODO Auto-generated method stub

       System.out.println("创建数据库");

       db.execSQL("create table user(id int,name varchar(20))");//千万别写错了

      

    }

   

    //数据库版本更新的时候，调用这个函数

    //比方说，创建数据库的版本是1，更新后的版本是2，就表示数据库更新了

    @Override

    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

       // TODO Auto-generated method stub

        System.out.println("更改数据库");    

    }

   

      

}

 

MySQLiteActivity.java

public class MySQLiteActivity extends Activity {

   

    private Button createButton = null;

    private Button updataButton = null;

    private Button insertButton = null;

    private Button unButton = null;

    private Button selectButton = null;

    @Override

    public void onCreate(Bundle savedInstanceState) {

        super.onCreate(savedInstanceState);

        setContentView(R.layout.main);

       

        createButton = (Button)this.findViewById(R.id.createButton);

        createButton.setOnClickListener(new CreateListener());

       

        updataButton =(Button)this.findViewById(R.id.updateButton);

       

    }

    class CreateListener implements OnClickListener{

 

       @Override

       public void onClick(View v) {

           // TODO Auto-generated method stub

           //创建一个DatabaseHelper对象

           DatabaseHelper dbHelper =newDatabaseHelper(MySQLiteActivity.this,"bjldb");

            //只有调用了DatabaseHelper对象的getReadableDatabase()方法，或者是getWritableDatabase()方法之后，才会创建，或打开一个数据库

           SQLiteDatabase db = dbHelper.getReadableDatabase();

       }      

    }

    class InsertListener implements OnClickListener{

 

       @Override

       public void onClick(View v) {

           // TODO Auto-generated method stub

           //生成ContentValues对象,和map对象差不多，存放的都是键值对

           ContentValues values= new ContentValues();

           //向该对象当中插入键值对，其中键是列名，值是希望插入到这一列的值

           values.put("id",1);

           values.put("name", "张三丰");

           DatabaseHelper dbHelper =newDatabaseHelper(MySQLiteActivity.this,"bjldb");

           SQLiteDatabase db = dbHelper.getWritableDatabase();

           //调用insert方法，就可以将数据插入到数据库当中，三个参数：表名、默认空值，ContentValues对象

           db.insert("user",null,values);

       }      

    }

    class UpdateRecordListener implementsOnClickListener{

 

       @Override

       public void onClick(View v) {

           // TODO Auto-generated method stub

           DatabaseHelper dbHelper =newDatabaseHelper(MySQLiteActivity.this,"bjldb");

           SQLiteDatabase db = dbHelper.getWritableDatabase();

           ContentValues values = new ContentValues();

           values.put("name", "hello");

           ///第一个参数是要更新的表名

            //第二个参数是一个ContentValeus对象

            //第三个参数是where子句

           //第四个参数，对占位符进行赋值，第一个元素赋值给第一个展位副

           db.update("users",values,"id=?",new String[]{"1"});

          

       }

   

    }

   class SelectListener implements OnClickListener{

 

    @Override

    public void onClick(View v) {

       // TODO Auto-generated method stub

       DatabaseHelper dbHelper = newDatabaseHelper(MySQLiteActivity.this,"bjldb");

       SQLiteDatabase db = dbHelper.getWritableDatabase();

        Cursor cursor = db.query("user",new String[]{"id","name"},"id=?",newString[]{"1"},null,null,null);

        while(cursor.moveToNext()){

        //getString必须用索引

        String name= cursor.getString(cursor.getColumnIndex("name"));

        System.out.println("姓名是"+name);

        }

    }

      

   }

}

说明：

1)      在CreateListener这个监听器中是用来创建数据库的，创建数据库必须要先创建一个DatabaseHelper对象，同时调用的是 DatabaseHelper两个参数的构造方法，分别为当前Activity的对象，以及要创建的数据库的名字。并且只有调用了 DatabaseHelper对象的getReadableDatabase()方法，或者是getWritableDatabase()方法之后，才会 创建，或打开一个数据库。

2)      UpdateListener是用来更新数据库的，不是更新数据的。由于默认数据库版本为1，此处设置为2，则android会自动调用onUpgrade方法。

3)      插入数据必须使用ContentValues对象进行数据的存储、传递。contentValue对象和Map差不多，存放的都是键值对。想在该对象当中 插入键值对，其中键是列名，值是希望插入到这一列的值，值必须和数据库当中的数据类型一致。由于插入数据，相对于修改，所以版本号为2。最后调用 insert方法就可以将数据插入到数据库当中，三个参数：表名、如果ContentValues对象为空时的默认值，ContentValues对象。

4)      更新数据操作需要使用ContentValues对象存储需要更新的列名以及更新后的值。最后调用DatabaseHelper的update方法，参数 分别为表名、contentValues对象，where子句，此时相当于JDBC的PrepareStatement的方法使用？作为占位符，在后面的 String数组中设置占位符的值。

5)      数据查询是调用query方法，该方法参数众多，一般的参数为：表名、存储要查询列的String型数组、where子句、占位符数组、groupby、 having、orderby。返回值为Cursor类型，循环调用cursor的moveToNext方法即可。

6)      不要过于依赖SQLite，不太稳定。

----------------------------------------------------------------------------

sqlite仅支持直接修改表名，以及增加列，不能直接删除列，修改列。

注意：Android对数据库表有一个约定。就是每张表都应该至少有_id这列。
listview在适配cursor的时候，会默认的获取 _id 这列的值，
如果建的表没有 _id这列的话，自然也就报错了

解决方法是在查询数据时，加入规则 select  自定义主键名 AS _id， 再传递该cursor给adapter 就应该没有问题了

表名为read(_id,news_id,timestamp)

1、插入数据  insert into read values(1,'1',123);

2、更新表名： alter table 旧表名 rename to 新表名

      旧表中的数据保留。

     eg：alter table read rename to reads;

3、增加一列： alter table 表名 add column 列名 数据类型 限定

     旧表中的数据保留

    eg：alter table reads add column mynewcolumn text;

  

4、删除列

     需要用间接方法

     1）create table read as select news_id,timestamp from reads;   
     2）drop table reads;   
     3）alter table read rename to reads;

5、更改列名

    需要用间接方法

   1）create table read(news_id text primary key,timestamp text,mynewcolumn text);
   2） insert into read select news_id,timestamp,'' from reads; 
   3）drop table reads;
   4） alter table read rename to reads; 

6、更改列类型，类似 更改列名的方法

查询表是否存在

SELECT count(*) FROM sqlite_master WHERE type='table' AND name='tableName'

--------------------------------------------------------------------------------------

ADB使用SQLITE

C:\Users\heq>adb shell
shell@android:/ $ cd data/data/com.sina.news/databases
cd data/data/com.sina.news/databases
shell@android:/data/data/com.sina.news/databases $ sqlite3 news.db
sqlite3 news.db
SQLite version 3.7.4
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>

注意： 

1）可使用 .tables 查看当前数据库所有表

2）查看整个数据库的表结构

sqlite> .schema
CREATE TABLE a (time integer not null default current_timestamp,name text);
CREATE TABLE ab (time integer,name text);
CREATE TABLE abc (name text,time integer not null default current_timestamp);
CREATE TABLE abcd (name text,time integer not null default current_timestamp);
CREATE TABLE abcde (name text,time integer );

3）显示表头：

sqlite> .head on
sqlite> select * from abcde;
name|time
aaa|
sqlite>

4）.exit  退出sqlite3

adb 中 ctrl+c 退出adb

-----------------------------------------------------------------

联合查询

联合查询效率较高.以下例子来说明联合查询的好处

t1表结构(用户名,密码)    userid int         username   varchar(20)     password       varchar(20)

                                                  1                    jack                           jackpwd

                                                   2                    owen                        owenpwd

t3表结构(用户积分,等级)    userid int         jf   int                   dj           int

                                                1                     20                       3

                                                 3                     50                       6

第一:内联(inner join)

如果想把用户信息,积分,等级都列出来.那么一般会这样写

select * from t1 ,t3 where t1.userid = t3.userid   其实这样的结果等同于select * from t1 inner join t3 on t1.userid=t3.userid

就是把两个表中都存在userid的行拼成一行.这是内联.但后者的效率会比前者高很多.建议用后者的写法.

运行结果:userid    username password     userid   jf    dj

                   1             jack        jacjpwd      1          20   3

第二:左联（left outer join）显示左表中的所有行

select * from t1 left outer join t3 on t1.userid=t3.userid

运行结果：userid    username password     userid   jf     dj

　　　　　　1        jack          jackpwd       1         20   3

                      2      owen         owenpwd     NULL NULL   NULL

第三:右联（right outer join）显示右表中的所有行

select * from t1 right outer join t3 on t1.userid=t3.userid

运行结果：userid    username password     userid   jf     dj

　　　　　　1        jack          jackpwd       1         20   3

                    Null      Null           Null              3        50     6

第四:全联（full outer join）显示两边表中所有行

select * from t1 full outer join t3 on t1.userid=t3.userid

运行结果：userid    username password     userid   jf     dj

　　　　　   1        jack          jackpwd       1         20   3

                     2       owen         owenpwd     NULL NULL   NULL

                     Null    Null           Null              3        50     6

--------------------------------------

事务操作

ACID就是：原子性(Atomicity )、一致性( Consistency )、隔离性( Isolation)和持久性(Durabilily)。

1. 原子性
原子性属性用于标识事务是否完全地完成，一个事务的任何更新要在系统上完全完成，如果由于某种原因出错，事务不能完成它的全部任务，系统将返回到事务开始前的状态。
让我们再看一下银行转帐的例子。如果在转帐的过程中出现错误，整个事务将会回滚。只有当事务中的所有部分都成功执行了，才将事务写入磁盘并使变化永久化。
为了提供回滚或者撤消未提交的变化的能力，许多数据源采用日志机制。例如，SQL Server使用一个预写事务日志，在将数据应用于(或提交到)实际数据页面前，先写在事务日志上。但是，其他一些数据源不是关系型数据库管理系统(RDBMS)，它们管理未提交事务的方式完全不同。只要事务回滚时，数据源可以撤消所有未提交的改变，那么这种技术应该可用于管理事务。

2. 一致性
事务在系统完整性中实施一致性，这通过保证系统的任何事务最后都处于有效状态来实现。如果事务成功地完成，那么系统中所有变化将正确地应用，系统处于有效状态。如果在事务中出现错误，那么系统中的所有变化将自动地回滚，系统返回到原始状态。因为事务开
始时系统处于一致状态，所以现在系统仍然处于一致状态。
再让我们回头看一下银行转帐的例子，在帐户转换和资金转移前，帐户处于有效状态。如果事务成功地完成，并且提交事务，则帐户处于新的有效的状态。如果事务出错，终止后，帐户返回到原先的有效状态。
记住，事务不负责实施数据完整性，而仅仅负责在事务提交或终止以后确保数据返回到一致状态。理解数据完整性规则并写代码实现完整性的重任通常落在开发者肩上，他们根据业务要求进行设计。
当许多用户同时使用和修改同样的数据时，事务必须保持其数据的完整性和一致性。因此我们进一步研究A C I D特性中的下一个特性：隔离性。

3. 隔离性
在隔离状态执行事务，使它们好像是系统在给定时间内执行的唯一操作。如果有两个事务，运行在相同的时间内，执行相同的功能，事务的隔离性将确保每一事务在系统中认为只有该事务在使用系统。
这种属性有时称为串行化，为了防止事务操作间的混淆，必须串行化或序列化请求，使得在同一时间仅有一个请求用于同一数据。
重要的是，在隔离状态执行事务，系统的状态有可能是不一致的，在结束事务前，应确保系统处于一致状态。但是在每个单独的事务中，系统的状态可能会发生变化。如果事务不是在隔离状态运行，它就可能从系统中访问数据，而系统可能处于不一致状态。通过提供事
务隔离，可以阻止这类事件的发生。
在银行的示例中，这意味着在这个系统内，其他过程和事务在我们的事务完成前看不到我们的事务引起的任何变化，这对于终止的情况非常重要。如果有另一个过程根据帐户余额进行相应处理，而它在我们的事务完成前就能看到它造成的变化，那么这个过程的决策可能
建立在错误的数据之上，因为我们的事务可能终止。这就是说明了为什么事务产生的变化，直到事务完成，才对系统的其他部分可见。
隔离性不仅仅保证多个事务不能同时修改相同数据，而且能够保证事务操作产生的变化直到变化被提交或终止时才能对另一个事务可见，并发的事务彼此之间毫无影响。这就意味着所有要求修改或读取的数据已经被锁定在事务中，直到事务完成才能释放。大多数数据库，例如SQL Server以及其他的RDBMS，通过使用锁定来实现隔离，事务中涉及的各个数据项或数据集使用锁定来防止并发访问。

4. 持久性
持久性意味着一旦事务执行成功，在系统中产生的所有变化将是永久的。应该存在一些检查点防止在系统失败时丢失信息。甚至硬件本身失败，系统的状态仍能通过在日志中记录事务完成的任务进行重建。持久性的概念允许开发者认为不管系统以后发生了什么变化，完
成的事务是系统永久的部分。

-----------------------------------------------------

范式

在实际开发中最为常见的设计范式有三个：

1．第一范式(确保每列保持原子性)

第一范式（1NF）是指数据库表的每一列都是不可分割的基本数据项，同一列中不能有多个值，即实体中的某个属性不能有多个值或者不能有重复的属性

第一范式是最基本的范式。如果数据库表中的所有字段值都是不可分解的原子值，就说明该数据库表满足了第一范式。

第一范式的合理遵循需要根据系统的实际需求来定。比如某些数据库系统中需要用到“地址”这个属性，本来直接将“地址”属性设计成一个数据库表的字段就行。但是如果系统经常会访问“地址”属性中的“城市”部分，那么就非要将“地址”这个属性重新拆分为省份、城市、详细地址等多个部分进行存储，这样在对地址中某一部分操作的时候将非常方便。这样设计才算满足了数据库的第一范式，如下表所示。

上表所示的用户信息遵循了第一范式的要求，这样在对用户使用城市进行分类的时候就非常方便，也提高了数据库的性能。

                

2．第二范式(确保表中的每列都和主键相关)

如果关系模式R为第一范式，并且R中每一个非主属性完全函数依赖于R的某个候选键， 则称为第二范式模式。

第二范式在第一范式的基础之上更进一层。第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。

比如要设计一个订单信息表，因为订单中可能会有多种商品，所以要将订单编号和商品编号作为数据库表的联合主键，如下表所示。

 订单信息表

这样就产生一个问题：这个表中是以订单编号和商品编号作为联合主键。这样在该表中商品名称、单位、商品价格等信息不与该表的主键相关，而仅仅是与商品编号相关。所以在这里违反了第二范式的设计原则。

而如果把这个订单信息表进行拆分，把商品信息分离到另一个表中，把订单项目表也分离到另一个表中，就非常完美了。如下所示。

这样设计，在很大程度上减小了数据库的冗余。如果要获取订单的商品信息，使用商品编号到商品信息表中查询即可。

                 

3．第三范式(确保每列都和主键列直接相关,而不是间接相关)

如果关系模式R是第二范式，且每个非主属性都不传递依赖于R的候选键，则称R为第三范式模式。

第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。

比如在设计一个订单数据表的时候，可以将客户编号作为一个外键和订单表建立相应的关系。而不可以在订单表中添加关于客户其它信息（比如姓名、所属公司等）的字段。如下面这两个表所示的设计就是一个满足第三范式的数据库表。

这样在查询订单信息的时候，就可以使用客户编号来引用客户信息表中的记录，也不必在订单信息表中多次输入客户信息的内容，减小了数据冗余。

满足第三范式的数据库表应该不存在如下依赖关系：

 

关键字段 → 非关键字段x → 非关键字段y

 

假定学生关系表为Student(学号, 姓名, 年龄, 所在学院, 学院地点, 学院电话)，关键字为单一关键字"学号"，因为存在如下决定关系：

 

(学号) → (姓名, 年龄, 所在学院, 学院地点, 学院电话)

 

这个数据库是符合2NF的，但是不符合3NF，因为存在如下决定关系：

 

(学号) → (所在学院) → (学院地点, 学院电话)

 

即存在非关键字段"学院地点"、"学院电话"对关键字段"学号"的传递函数依赖。

 

它也会存在数据冗余、更新异常、插入异常和删除异常的情况，读者可自行分析得知。

 

把学生关系表分为如下两个表：

 

学生：(学号, 姓名, 年龄, 所在学院)；

 

学院：(学院, 地点, 电话)。

 

这样的数据库表是符合第三范式的，消除了数据冗余、更新异常、插入异常和删除异常。

鲍依斯-科得范式（BCNF是3NF的改进形式）

 

若关系模式R是第一范式，且每个属性都不传递依赖于R的候选键。这种关系模式就是BCNF模式。即在第三范式的基础上，数据库表中如果不存在任何字段对任一候选关键字段的传递函数依赖则符合鲍依斯-科得范式。

 

假设仓库管理关系表为StorehouseManage(仓库ID, 存储物品ID, 管理员ID, 数量)，且有一个管理员只在一个仓库工作；一个仓库可以存储多种物品。这个数据库表中存在如下决定关系：

 

(仓库ID, 存储物品ID) →(管理员ID, 数量)

 

(管理员ID, 存储物品ID) → (仓库ID, 数量)

 

所以，(仓库ID, 存储物品ID)和(管理员ID, 存储物品ID)都是StorehouseManage的候选关键字，表中的唯一非关键字段为数量，它是符合第三范式的。但是，由于存在如下决定关系：

 

(仓库ID) → (管理员ID)

 

(管理员ID) → (仓库ID)

 

即存在关键字段决定关键字段的情况，所以其不符合BCNF范式。它会出现如下异常情况：

 

(1) 删除异常：

 

当仓库被清空后，所有"存储物品ID"和"数量"信息被删除的同时，"仓库ID"和"管理员ID"信息也被删除了。

 

(2) 插入异常：

 

当仓库没有存储任何物品时，无法给仓库分配管理员。

 

(3) 更新异常：

 

如果仓库换了管理员，则表中所有行的管理员ID都要修改。

 

把仓库管理关系表分解为二个关系表：

 

仓库管理：StorehouseManage(仓库ID, 管理员ID)；

 

仓库：Storehouse(仓库ID, 存储物品ID, 数量)。

 

这样的数据库表是符合BCNF范式的，消除了删除异常、插入异常和更新异常。

 

四种范式之间存在如下关系：