SQLite相关备忘

刚好用到Android中SQLite，几点基础知识备忘

一. SQLite本身特性

数据类型只有五种：

NULL

INTEGER

REAL

TEXT

BLOB

系统会自动转换为以上五种类型，而且会忽略SQL语句中的数据类型信息，也就是说对字段没有特殊要求

查看数据库或表的相关信息：

.databases               列出数据库文件名
.tables ?PATTERN?        列出?PATTERN?匹配的表名
.import FILE TABLE       将文件中的数据导入的文件中
.dump ?TABLE?            生成形成数据库表的SQL脚本
.output FILENAME         将输出导入到指定的文件中
.output stdout           将输出打印到屏幕
.mode MODE ?TABLE?       设置数据输出模式(csv,html,tcl…
.nullvalue STRING        用指定的串代替输出的NULL串
.read FILENAME           执行指定文件中的SQL语句
.schema ?TABLE?          打印创建数据库表的SQL语句
.separator STRING        用指定的字符串代替字段分隔符
.show                    打印所有SQLite环境变量的设置
.quit                    退出命令行接口

插入、查询、删除、修改都差不多，写个插入放在这儿，老忘

插入：
insert into table_name values (v1, v2, v3)

或者

insert into table_name(field1,field3) values (v1,v3)

二. Android中的SQLite

1. 两个必须注意的类： 

android.database.sqlite.SQLiteDatabase;

android.database.sqlite.SQLiteOpenHelper;

SQLiteOpenHelper是一个抽象类，需要先继承这个类得到这两个方法：

    getReadableDatabase() 创建或者打开一个查询数据库

    getWritableDatabase() 创建或者打开一个可写数据库

这两个方法均返回SQLiteDatabase对象，通过得到的这个对象，就可以继续进行操作了。SQLiteOpenHelper中还有一些其它的回调函数，如open，upgrade之类的，需要用的时候可以采用。

2.SQLiteDatabase中的现成方法： 

SQLiteDatabase对象方法目前看到三类： insert、update、query，使用都非常方便，如query：

public Cursor query (String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy, String limit)

Since: API Level 1
Query the given table, returning a Cursor over the result set.
Parameters

table	The table name to compile the query against.
columns	A list of which columns to return. Passing null will return all columns, which is discouraged to prevent reading data from storage that isn't going to be used.
selection	A filter declaring which rows to return, formatted as an SQL WHERE clause (excluding the WHERE itself). Passing null will return all rows for the given table.
selectionArgs	You may include ?s in selection, which will be replaced by the values from selectionArgs, in order that they appear in the selection. The values will be bound as Strings.
groupBy	A filter declaring how to group rows, formatted as an SQL GROUP BY clause (excluding the GROUP BY itself). Passing null will cause the rows to not be grouped.
having	A filter declare which row groups to include in the cursor, if row grouping is being used, formatted as an SQL HAVING clause (excluding the HAVING itself). Passing null will cause all row groups to be included, and is required when row grouping is not being used.
orderBy	How to order the rows, formatted as an SQL ORDER BY clause (excluding the ORDER BY itself). Passing null will use the default sort order, which may be unordered.
limit	Limits the number of rows returned by the query, formatted as LIMIT clause. Passing null denotes no LIMIT clause.
Returns

A Cursor object, which is positioned before the first entry. Note that Cursors are not synchronized, see the documentation for more details.

如：封装起来这么调用

public Cursor findList(String tableName,String[] columns,String selection,String[] selectionArgs,String groupBy,String having,String orderBy) {
	 return mDb.query(tableName, columns, selection, selectionArgs, groupBy, having, orderBy);
}

dbHelper.findList("user",new String[] {"id","username", "password"}, "username = 'test'", null,null, null, "id desc");

查文档即可，目前没有看到join，难道要自己实现？

3. 数据库位置 

Android中数据库存储的位置在data/data/<项目文件夹>/databases/