最近在做的项目涉及到了SQLite，大学时没有好好学习数据库，趁这次项目学习总结下。

      SQLite是一款轻量级数据库，它的设计目的是嵌入式，而且它占用的资源非常少，在嵌入式设备中，可能只需要几百KB，这也是 Android 系统采用 SQLite 数据库的原因之一。

      下面给出SQLite的数据类型。

一般数据采用的固定的静态数据类型，而SQLite采用的是动态数据类型，会根据存入值自动判断。SQLite具有以下五种数据类型：

1.NULL：空值。
　　2.INTEGER：带符号的整型，具体取决有存入数字的范围大小。
　　3.REAL：浮点数字，存储为8-byte IEEE浮点数。
　　4.TEXT：字符串文本。
　　5.BLOB：二进制对象。

但实际上，sqlite3也接受如下的数据类型：
 smallint 16 位元的整数。
 interger 32 位元的整数。
 decimal(p,s) p 精确值和 s 大小的十进位整数，精确值p是指全部有几个数(digits)大小值，s是指小数点後有几位数。如果没有特别指定，则系统会设为 p=5; s=0 。
 float  32位元的实数。
 double  64位元的实数。
 char(n)  n 长度的字串，n不能超过 254。
 varchar(n) 长度不固定且其最大长度为 n 的字串，n不能超过 4000。
 graphic(n) 和 char(n) 一样，不过其单位是两个字元 double-bytes， n不能超过127。这个形态是为了支援两个字元长度的字体，例如中文字。
 vargraphic(n) 可变长度且其最大长度为 n 的双字元字串，n不能超过 2000
 date  包含了 年份、月份、日期。
 time  包含了 小时、分钟、秒。
 timestamp 包含了 年、月、日、时、分、秒、千分之一秒。

datetime 包含日期时间格式，必须写成'2010-08-05'不能写为'2010-8-5'，否则在读取时会产生错误！

 

 

      使用SQLite关键是创建一个类，这个类继承自SQLiteOpenHelper，我们自己创建的类必须重写三个方法：构造方法，onCreate()方法和onUpdate()方法。SQLiteDatabase是直接操作数据库的对象。可以通过SQLiteOpenHelper的getWritableDatabase()和getReadableDatabase()这两个方法来或得SQLiteDatabase的对象。根据方法名字可以知道一个是获得可读的，一个是获得可写的。

     

     下面给出一个例子创建一个数据库。

 

public static class DatabaseHelper extends SQLiteOpenHelper{
        public DatabaseHelper(Context context) {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }
        @Override
        public void onCreate(SQLiteDatabase db) {
            // TODO Auto-generated method stub
            Log.i("TAG", "create table start...");
            db.execSQL(TABLECONTACTS);
                       Log.i("TAG", "create table over...");
        }
          @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            // TODO Auto-generated method stub
            Log.i("TAG", "contactsmanager.db Upgrade...");
            db.execSQL("DROP TABLE IF EXISTS "+TABLE_CONTACTS);
            onCreate(db);
            
        }
        
    }

       为了创建表和索引，需要调用 SQLiteDatabase 的 execSQL() 方法来执行 DDL 语句。如果没有异常，这个方法没有返回值。

例如，你可以执行如下代码：

db.execSQL("CREATE TABLE mytable (_id INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT, value REAL);");

 

        这条语句会创建一个名为 mytable 的表，表有一个列名为 _id，并且是主键，这列的值是会自动增长的整数（例如，当你插入一行时，SQLite 会给这列自动赋值），另外还有两列：title( 字符 ) 和 value( 浮点数 )。 SQLite 会自动为主键列创建索引。

       通常情况下，第一次创建数据库时创建了表和索引。如果你不需要改变表的 schema，不需要删除表和索引 . 删除表和索引，需要使用 execSQL() 方法调用 DROP INDEX 和 DROP TABLE 语句。

      


      上面的代码，已经创建了数据库和表，现在需要给表添加数据。有两种方法可以给表添加数据。

       像上面创建表一样，你可以使用 execSQL() 方法执行 INSERT, UPDATE, DELETE 等语句来更新表的数据。execSQL() 方法适用于所有不返回结果的 SQL 语句。例如：

db.execSQL("INSERT INTO widgets (name, inventory)"+ "VALUES ('Sprocket', 5)");

 

         另一种方法是使用 SQLiteDatabase 对象的 insert(), update(), delete() 方法。这些方法把 SQL 语句的一部分作为参数。示例如下：

ContentValues cv=new ContentValues(); cv.put(Constants.TITLE, "example title"); cv.put(Constants.VALUE, SensorManager.GRAVITY_DEATH_STAR_I); db.insert("mytable", getNullColumnHack(), cv);

 

update（）方法有四个参数，分别是表名，表示列名和值的 ContentValues 对象，可选的 WHERE 条件和可选的填充 WHERE 语句的字符串，这些字符串会替换 WHERE 条件中的“？”标记。update() 根据条件，更新指定列的值，所以用 execSQL() 方法可以达到同样的目的。

WHERE 条件和其参数和用过的其他 SQL APIs 类似。例如：

String[] parms=new String[] {"this is a string"}; db.update("widgets", replacements, "name=?", parms);

 

       delete() 方法的使用和 update() 类似，使用表名，可选的 WHERE 条件和相应的填充 WHERE 条件的字符串。

       对于查询来说，主要是有两种方法：rawQuery和query。

      

     

SQLite内建语法表

结构定义

CREATE TABLE

创建新表。

语法：

CREATE VIEW

创建一个视图（虚拟表），该表以另一种方式表示一个或多个表中的数据。

语法：


例子：

CREATE VIEW master_view AS

    SELECT * FROM sqlite_master WHERE type='view';

说明：

创建一个名为master_view的视图，其中包括sqlite_master这个表中的所有视图表。

CREATE TRIGGER

创建触发器，触发器是一种特殊的存储过程，在用户试图对指定的表执行指定的数据修改语句时自动执行。

语法：

例子：
CREATE TRIGGER update_customer_address UPDATE OF address ON customers 
  BEGIN
    UPDATE orders SET address = new.address WHERE customer_name = old.name;
  END;
说明：
创建了一个名为update_customer_address的触发器，当用户更新customers表中的address字段时，将触发并更新orders表中的address字段为新的值。
比如执行如下一条语句：
UPDATE customers SET address = '1 Main St.' WHERE name = 'Jack Jones';
数据库将自动执行如下语句：
UPDATE orders SET address = '1 Main St.' WHERE customer_name = 'Jack Jones';

CREATE INDEX

为给定表或视图创建索引。

语法：

例子：
CREATE INDEX idx_email ON customers (email);
说明：
为customers表中的email创建一个名为idx_email的字段。

结构删除

DROP TABLE

删除表定义及该表的所有索引。

语法：

例子：

DROP TABLE customers;

DROP VIEW

删除一个视图。

语法：

例子：
DROP VIEW master_view;

DROP TRIGGER

删除一个触发器。

语法：

例子：
DROP TRIGGER update_customer_address;

DROP INDEX

删除一个索引。

语法：

例子：
DROP INDEX idx_email;

数据操作

INSERT

将新行插入到表。

语法：

UPDATE

更新表中的现有数据。

语法：

DELETE

从表中删除行。

语法：

SELECT

从表中检索数据。

语法：

REPLACE

类似INSERT

语法：

事务处理

BEGIN TRANSACTION

标记一个事务的起始点。

语法：

END TRANSACTION

标记一个事务的终止。

语法：

COMMIT TRANSACTION

标志一个事务的结束。

语法：

ROLLBACK TRANSACTION

将事务回滚到事务的起点。

语法：

其他操作

COPY

主要用于导入大量的数据。

语法：

例子：

COPY customers FROM customers.csv;

EXPLAIN

语法：

PRAGMA

语法：

VACUUM

语法：

ATTACH DATABASE

附加一个数据库到当前的数据库连接。

语法：

DETTACH DATABASE

从当前的数据库分离一个使用ATTACH DATABASE附加的数据库。

语法：

sql-command ::=

CREATE [TEMP | TEMPORARY] TABLE table-name (

    column-def [, column-def]*

    [, constraint]*

)

sql-command ::=

CREATE [TEMP | TEMPORARY] TABLE [database-name.] table-name AS select-statement

column-def ::=

name [type] [[CONSTRAINT name] column-constraint]*

type ::=

typename |

typename ( number ) |

typename ( number , number )

column-constraint ::=

NOT NULL [ conflict-clause ] |

PRIMARY KEY [sort-order] [ conflict-clause ] |

UNIQUE [ conflict-clause ] |

CHECK ( expr ) [ conflict-clause ] |

DEFAULT value |

COLLATE collation-name

constraint ::=

PRIMARY KEY ( column-list ) [ conflict-clause ] |

UNIQUE ( column-list ) [ conflict-clause ] |

CHECK ( expr ) [ conflict-clause ]

conflict-clause ::=

ON CONFLICT conflict-algorithm

SQLite内建函数表

算术函数

abs(X)

返回给定数字表达式的绝对值。

max(X,Y[,...])

返回表达式的最大值。

min(X,Y[,...])

返回表达式的最小值。

random(*)

返回随机数。

round(X[,Y])

返回数字表达式并四舍五入为指定的长度或精度。

字符处理函数

length(X)

返回给定字符串表达式的字符个数。

lower(X)

将大写字符数据转换为小写字符数据后返回字符表达式。

upper(X)

返回将小写字符数据转换为大写的字符表达式。

substr(X,Y,Z)

返回表达式的一部分。

randstr()

 

quote(A)

 

like(A,B)

确定给定的字符串是否与指定的模式匹配。

glob(A,B)

 

条件判断函数

coalesce(X,Y[,...])

 

ifnull(X,Y)

 

nullif(X,Y)

 

集合函数

avg(X)

返回组中值的平均值。

count(X)

返回组中项目的数量。

max(X)

返回组中值的最大值。

min(X)

返回组中值的最小值。

sum(X)

返回表达式中所有值的和。

其他函数

typeof(X)

返回数据的类型。

last_insert_rowid()

返回最后插入的数据的ID。

sqlite_version(*)

返回SQLite的版本。

change_count()

返回受上一语句影响的行数。

last_statement_change_count()