AndroidStudy之SQLite了解与基本运用

LZ-Says：给大家推荐一个网站，有兴趣可以查阅，想为大家贡献一点自己的力量也可以投稿，老大审核通过会发表，更好的帮助有需要的人_{欢迎大家踊跃投稿}地址如下：
http://www.123si.org/android

当年 豪放 爱自由，而今 阔步 向前(钱)看~谁还没个 年少轻狂 ！

1. SQLite 简介

1-1 SQLite 介绍

SQLite，是一款轻型的数据库，是遵守ACID的关系型数据库管理系统，它包含在一个相对小的C库中。

它设计目标是嵌入式，力求占用资源较低，处理速度较快；它支持Windows/Linux/Unix等等主流的操作系统，同时也支持很多语言(Java，php，.Net等)。

总的来说，SQLite是一个软件库，实现了自给自足的、无服务器的、零配置的、事务性的 SQL 数据库引擎。

1-2 SQLite 历史

1. 2000 &＃8212; D. Richard Hipp 设计 SQLite 是为了不需要管理即可操作程序；
2. 2000 &＃8212; 在八月，SQLite1.0 发布 GNU 数据库管理器（GNU Database Manager）；
3. 2011 &＃8212; Hipp 宣布，向 SQLite DB 添加 UNQl 接口，开发 UNQLite（面向文档的数据库）

1-3 SQLite 局限性

在 SQLite 中，SQL92 不支持的特性如下所示：

这里写图片描述

2. SQLite 优点及缺点

2-1 SQLite 优点

• 动态数据类型存储：采用无数据类型，所以可以保存任何类型的数据，会根据存入值自动判断（一般推荐指定类型为好）；

• 轻量级

• 绿色软件：核心引擎本身不依赖第三方的软件，使用它也不需要“安装”，无需各种琐碎配置 ；

• 单一文件：数据库中所有的信息（比如表、视图、触发器、等）都包含在一个文件内，方便移植；

• 跨平台/可移植性

• 支持多语言编程接口

• 开源，免费

• 。。。 。。。

2-2 SQLite 缺点

• 并发访问的锁机制：数据库可能会被写操作独占，从而导致其他读写操作阻塞或出错；

• SQL标准支持不全；

• 网络文件系统（NFS）并发读写可能会出问题： SQLite文件放置于NFS时，在并发读写的情况下可能会出问题（比如数据损坏）。原因据说是由于某些NFS的文件锁实现上有Bug。

3. SQLite 数据类型

我们在上面曾说过，SQLite采用的是动态数据类型存储，它拥有基本数据类型，同时也关联亲和(Affinity)类型，具体说明如下：

3-1 基本数据类型

这里写图片描述

3-2 亲和(Affinity)类型

SQLite支持列的亲和类型概念。任何列仍然可以存储任何类型的数据，当数据插入时，该字段的数据将会优先采用亲缘类型作为该值的存储方式。

SQLite目前的版本支持以下五种亲缘类型：

这里写图片描述

3-3 数据类型名称以及相应的亲和类型：

这里写图片描述

这里写图片描述

这里写图片描述

3-4 存储时间类型技巧

这里写图片描述

4. Android 官方提供常用方法

Android提供了创建和使用 SQLite 数据库的 API 。SQLiteDatabase 代表一个数据库对象，提供了操作数据库的一些方法。

下面我们分别去了解下Android为我们提供这些API的常用内容。

4-1 SQLiteOpenHelper 常用方法简述

• 1. void onCreate(SQLiteDatabase db)： 在首次生成数据库时才会被调用，在onCreate()方法里可以生成数据库表结构等相关初始化操作。
• 2. void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)： 在数据库的版本发生变化时会被调用，一般在软件升级时才需改变版本号，或者说由于需求的变化导致不得不对数据库相关属性(字段)，那么这时候可以在这里面做相关操作。

4-2 SQLiteDatabase 常用方法简述

1. 获取操作数据库的SQLiteDatabase实例

• getReadableDatabase()
  先以读写方式打开数据库，如果数据库的磁盘空间满了，就会打开失败，当打开失败后会继续尝试以只读方式打开数据库。如果该问题成功解决，则只读数据库对象就会关闭，然后返回一个可读写的数据库对象。
• getWritableDatabase()
  以读写方式打开数据库，一旦数据库的磁盘空间满了，数据库就只能读而不能写

继续深入了解，我们发现他们内部调用同一个方法，同样是获取数据库的SQLiteDatabase实例，只不过二者之间对异常处理的方式不一样。

这里写图片描述

2. 新增

• long insert(String table, String nullColumnHack, ContentValues values)
• long insertOrThrow(String table, String nullColumnHack, ContentValues values)
• long insertWithOnConflict(String table, String nullColumnHack, ContentValues initialValues, int conflictAlgorithm)

官方为我们提供了以上三种方式去实现新增操作，频繁使用的也就是第一种方式，通过查阅源码得知，insert()和insertOrThrow()最终都会调用insertWithOnConflict()。

为了避免大家说我扯犊子，把找到的证据摆上来：

这里写图片描述

这里写图片描述

也就是说最后都会通过调用insertWithOnConflict()去做处理，也就是进行新增操作。接下来我们再简单聊聊参数相关含义，如下：

• table：要插入数据的表的名称；

• values：一个ContentValues对象，类似一个map.通过键值对的形式存储值；

• conflictAlgon：冲突解决方案。例如当数据表主键的唯一性检测出错的时候，就会按照该值设定的值进行处理；

• nullColumnHack：当values参数为空或者里面没有内容的时候，我们insert是会失败的（底层数据库不允许插入一个空行），为了防止这种情况，我们要在这里指定一个 列名，到时候如果发现将要插入的行为空行时，就会将你指定的这个列名的值设为null，然后再向数据库中插入

方法返回值含义： 方法返回当前插入的索引。

3. 删除

• int delete(String table, String whereClause, String[] whereArgs)

参数解释如下：
whereClause：条件，为一个字符串；如果多个条件中间使用 and 隔开；
whereArgs：字符串数组，和whereClause配合使用。与条件匹配的值。

4. 修改

• int update(String table, ContentValues values, String whereClause, String[] whereArgs)
• int updateWithOnConflict(String table, ContentValues values,String whereClause, String[] whereArgs, int conflictAlgorithm)
  我们普遍使用的是update()方法，但是它内部会调用updateWithOnConflict()去实现更新操作，有兴趣的可以了解了解

5. 查询

• Cursor query(String table, String[] columns, String selection,String[] selectionArgs, String groupBy, String having,String orderBy)；
• Cursor query(boolean distinct, String table, String[] columns,String selection, String[] selectionArgs, String groupBy,String having, String orderBy, String limit)；
• Cursor query(boolean distinct, String table, String[] columns,String selection, String[] selectionArgs, String groupBy,String having, String orderBy, String limit, CancellationSignal cancellationSignal)；
• Cursor query(String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having,String orderBy, String limit)；
• Cursor rawQuery(String sql, String[] selectionArgs)；
• Cursor rawQuery(String sql, String[] selectionArgs, CancellationSignal cancellationSignal)；
• Cursor rawQueryWithFactory( CursorFactory cursorFactory, String sql, String[] selectionArgs,String editTable)；
  查询最终依然调用了rawQueryWithFactory()，主要的操作还是在这里面实现。其中相关的参数大家可以从字面上理解，这点不得不说谷歌编码还是很6的

以上的方法都是基于实用官方封装好的方法，那么有的兄弟问了，我想直接使用SQL语句怎么弄，比较封装好的内部也是通过拼接SQL去实现功能的，别急，官方在查询中同样也提供了SQL语句方式，大家注意看上面，下面简单介绍下execSQL()~

execSQL(String sql, Object[] bindArgs)：方法的第一个参数为SQL语句，第二个参数为SQL语句中占位符参数的值，参数值在数组中的顺序要和占位符的位置对应。

通过查看源码，我们可以得知此方法是void类型，也就是说无返回！大家注意。

这里写图片描述

下面为大家举个小例子，如下：

//省略初始化操作
方法一：指定列名
db.execSQL("insert into stu(stu_name,stu_age,stu_address) values(?,?,?)", new Object[]{"贺大宝",21,"目前在廊坊~"});
方法二：不指定列名
db.execSQL("insert into stu values(?,?,?)", new Object[]{"贺大宝",21,"目前在廊坊~"});
//省略关闭操作


5. 常用 SQL 语句

注意：SQL 语句对大小写不敏感

5-1 基本 SQL 语句

• insert &＃8212; 插入

写法一： insert into table_name values (值1, 值2,&＃8230;.)

写法二： insert into table_name values (列1, 列2,&＃8230;) values(值1, 值2,&＃8230;.)（需注意的是，值需要和列一一对应）

• delete &＃8212; 删除

删除符合条件数据：delete from table_name where 列名称=值

删除表中所有数据：delete from table_name

• update &＃8212; 修改

修改一列：update table_name set 列名称 = 新值 where 列名称 = 某值

修改多列：update table_name set 列名称1 = 新值1，列名称2 = 新值2 where 列名称 = 某值

• select &＃8212; 查询

查询某表中所有数据：select * from table_name

查询某表中指定列数据：select 列名称 from table_name

查询去除重复数据：select distinct 列名称 from table_name

• order by &＃8212; 排序

order by 语句用于根据指定的列对结果集进行排序；

order by 语句默认按照升序对记录进行排序，如果希望按照降序对记录进行排序，可以使用 desc 关键字。
附上一张例子图：

这里写图片描述

5-2 常用 SQL 函数

• avg() &＃8212; 返回数值列的平均值（null 值不包括在计算中）

select avg(column_name) from table_name

• count() &＃8212; 返回指定列的值的数目（null 不计入）

表中的记录数：select count(*) from table_name

返回指定列的值的数目：select count(column_name) from table_name

• max() &＃8212; 返回一列中的最大值（null 值不包括在计算中）

select max(column_name) from table_name

• min() &＃8212; 返回一列中的最小值（null 值不包括在计算中）

select min(column_name) from table_name

• sum() &＃8212; 返回数值列的总数

select sum(column_name) from table_name

• first() &＃8212; 返回指定的字段中第一个记录的值

select first(column_name) from table_name

• last() &＃8212; 返回指定的字段中最后一个记录的值

select last(column_name) from table_name

6. 让我们愉快的开始撸码之旅吧~

我们在上面说过，官方为我们提供了 SQLiteOpenHelper 去方便我们简单，快速创建数据库以及基本表，那么具体实现又是怎么样的呢？看下面高能热量~

6-1 创建DBHelper帮助类

package cn.hlq.sqlitestudy.db;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
/**
* Created by HLQ on 2017/4/7
* 创建数据库(初始化)帮助类
* 现在创建一个学生表，表中含有编号，姓名，年龄，地址
*/
public class DBHelper extends SQLiteOpenHelper {
/**
* 数据库名称
*/
private static final String DATABASE_NAME="sqlite_study.db";
/**
* 数据库版本号
*/
private static final int DATABASE_VERSION=1;
/**
* 初始化设置数据库名称，数据库版本号
* @param context
*/
public DBHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
/**
* 键明其意，在创建的时候会调用，而且只会调用一次
* @param db
*/
@Override
public void onCreate(SQLiteDatabase db) {
// 创建学生表
db.execSQL(SQLManager.SQL_CREATE_TABLE_STU);
}
/**
* 数据库有更新时调用
* @param db
* @param oldVersion
* @param newVersion
*/
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
}
}


6-2 创建SQLManager管理类

为了后期方便拓展，我们可以将SQL语句归为一个类，单独管理

package cn.hlq.sqlitestudy.db;
/**
* Created by HLQ on 2017/4/7
* 这里为了避免累赘，简单附上创建学生表的SQL语句，具体详情大家可直接查看Demo源码
*/
public class SQLManager {
/**
* 创建学生表
*/
public static final String SQL_CREATE_TABLE_STU="create table if not exists stu (stu_id integer primary key autoincrement,stu_name varchar(15),stu_age integer,stu_address varchar(50))";
}


6-3 创建DBManager管理类，实现CRUD

package cn.hlq.sqlitestudy.db;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;
import java.util.ArrayList;
import java.util.List;
import cn.hlq.sqlitestudy.entity.Student;
/**
* Created by HLQ on 2017/4/7
*/
public class DBManager {
private Context context;
private DBHelper helper;
private SQLiteDatabase db;
/**
* 构造方法初始化
*
* @param context
*/
public DBManager(Context context) {
this.cOntext= context;
this.helper = new DBHelper(context);
this.db = helper.getWritableDatabase();
}
/**
* 新增一条数据
*
* @param stu
* @return
*/
public long insertDB(Student stu) {
ContentValues values = new ContentValues();
values.put("stu_name", stu.getStuName());
values.put("stu_age", stu.getStuAge());
values.put("stu_address", stu.getStuAddress());
long rowsNum = 0;
try {
rowsNum = db.insert("stu", null, values);
} catch (Exception e) {
e.printStackTrace();
Log.e("HLQ_Struggle", "insert error:" + e.getMessage());
} finally {
if (db != null) {
db.close();
}
}
return rowsNum;
}
/**
* 删除单条数据
*
* @param stuName
* @return
*/
public int deleteDB(String stuName) {
int rowsNum = 0;
try {
rowsNum = db.delete("stu", "stu_name=?", new String[]{stuName});
} catch (Exception e) {
e.printStackTrace();
Log.e("HLQ_Struggle", "delete error:" + e.getMessage());
} finally {
if (db != null) {
db.close();
}
}
return rowsNum;
}
/**
* 删除多条数据
*
* @param stuName
* @param stuAge
* @return
*/
public int deleteDBForWhere(String stuName, int stuAge) {
int rowsNum = 0;
try {
rowsNum = db.delete("stu", "stu_name=? and stu_age=?", new String[]{stuName, stuAge + ""});
} catch (Exception e) {
e.printStackTrace();
Log.e("HLQ_Struggle", "delete more where error:" + e.getMessage());
} finally {
if (db != null) {
db.close();
}
}
return rowsNum;
}
/**
* 根据姓名修改
*
* @param stuName
* @return
*/
public int updateDB(String stuName) {
ContentValues values = new ContentValues();
values.put("stu_name", "Test");
int rowsNum = 0;
try {
rowsNum = db.update("stu", values, "stu_name=?", new String[]{stuName});
} catch (Exception e) {
e.printStackTrace();
Log.e("HLQ_Struggle", "update error:" + e.getMessage());
} finally {
if (db != null) {
db.close();
}
}
return rowsNum;
}
/**
* 查询所有数据
*
* @return
*/
public List queryStu() {
List stuList = new ArrayList<>();
Cursor cursor = null;
try {
cursor = db.query("stu", null, null, null, null, null, null);
if (cursor != null) {
while (cursor.moveToNext()) {
Student stu = new Student();
stu.setStuName(cursor.getString(cursor.getColumnIndex("stu_name")));
stu.setStuAge(cursor.getInt(cursor.getColumnIndex("stu_age")));
stu.setStuAddress(cursor.getString(cursor.getColumnIndex("stu_address")));
stuList.add(stu);
}
}
} catch (Exception e) {
e.printStackTrace();
Log.e("HLQ_Struggle", "query error:" + e.getMessage());
} finally {
if (cursor != null) {
cursor.close();
}
if (db != null) {
db.close();
}
}
return stuList;
}
}


都说眼见为实，那么让我们一起看看运行结果。

运行结果大展示

• 新增一条数据

这里写图片描述

新增成功，看看数据库中数据是否正确。

这里写图片描述

• 根据一个条件删除数据

这里写图片描述

• 根据多个条件删除数据

首先查看下数据库目前存储数据内容

这里写图片描述

接下来要对姓名为“张三”，年龄为“18”的数据进行删除，结果如下：

这里写图片描述

再次查看数据库中数据

这里写图片描述

• 根据条件修改数据

这里写图片描述

• 查询结果展示

这里写图片描述

7. 使用注意项以及相关技巧

7-1 创建表以及属性(字段)需注意避免使用 关键字

官方给出关键字如下：

这里写图片描述

大家使用过程中可以取第一位前缀或者单词，例如，Stu表中id可以写成 s_id or stu_id，这样感觉一下子就明确多了。

7-2 创建表使用关键字 if not exists

使用普通创建表可能会返回创建失败，原因可能是表已存在，而是用if not exists，如果不存在才会创建，保证程序健壮性。

7-3 读取或者查询完之后 记得关闭游标或者数据库，防止内存泄漏

8. 代码查看及下载

GitHub 查看地址：https://github.com/HLQ-Struggle/SQLiteStudy

Demo 下载地址：http://download.csdn.net/detail/u012400885/9807179

9. 参考资料

感谢如下各位同仁奉献（前人栽树，后人乘凉），Thanks：

1. http://baike.baidu.com/link?url=k3hYvLxllkitqVHdjF-phxvOzaeGtM6YBLwjRYrFeRJXJIiY8LGrcvYxpfBX4ooFGWUlPDbHhNifVp_Zn30LTK ；

2. http://www.runoob.com/sqlite/sqlite-tutorial.html ;

3. http://blog.sina.com.cn/s/blog_8cfbb9920100zetj.html；

4. http://blog.knowsky.com/185331.htm；

5. http://www.w3school.com.cn/sql/index.asp ;

6. http://blog.csdn.net/imxilife/article/details/45620009；

7. http://blog.csdn.net/primer_programer/article/details/28513919；

8. http://www.educity.cn/wenda/586383.html；