数据库是存数据的仓库。通讯录、短信、日历、邮箱都靠本地库。Android 内置的是轻量关系库 SQLite，资源占用很小，不必像 PC 那样先装一套数据库软件。下面从命令行、可视化客户端，再到 SQLiteOpenHelper 增删改查。
一、命令行操作 SQLite
SDK 的 tools 目录有 sqlite3.exe。程序生成的 *.db 一般在 /data/data/包名/databases/*.db。
-
cmd 里输入
adb shell，进入 shell
-
cd /data/data/包名/databases/，进入应用库目录
-
sqlite3 *.db，打开库，用熟悉的 SQL 增删改查
-
-
select * from tablename 查数据
二、可视化操作 SQLite
命令行不熟可以用客户端，例如 sqlitebrowser：从 IDE 文件管理导出 *.db，用软件打开即可。
三、代码操作：SQLiteDatabase 与 OpenHelper
核心类 SQLiteDatabase
拿到 SQLiteDatabase 实例就能执行 SQL。最简写法：
SQLiteDatabase db = openOrCreateDatabase("scxh1502.db", MODE_PRIVATE, null);
db.execSQL("create table student (id INTEGER NOT NULL,name TEXT,PRIMARY KEY(id))");
db.execSQL("insert into student (id,name) values (1001,'张三')");
工程里更常见的是继承 SQLiteOpenHelper，用它管理创建和版本升级，实现 onCreate 和 onUpgrade。
数据库辅助类 SQLiteOpenHelper
public class DataBaseHelper extends SQLiteOpenHelper {
private final static String DATABASE_NAME = "android1601.db"; //数据库名
private final static int DATABASE_VERSION = 3; //数据库版本号
private static String createUserTabelSQL = "create table " + DataColumn.UserTable.TABLE_NAME_USER
+ "("
+ DataColumn.UserTable.COLUMN_NAME_ID + " Integer NOT NULL PRIMARY KEY AUTOINCREMENT,"
+ DataColumn.UserTable.COLUMN_NAME_NAME + " varchar(20),"
+ DataColumn.UserTable.COLUMN_NAME_PASSWORD + " varchar(6) "
+ ")";
private static String alterUserAddSexSQL = "Alter table " + DataColumn.UserTable.TABLE_NAME_USER
+ " add column " + DataColumn.UserTable.COLUMN_NAME_SEX + " varchar(2)";
private static String alterUserAddNumberSQL = "Alter table " + DataColumn.UserTable.TABLE_NAME_USER
+ " add column " + DataColumn.UserTable.COLUMN_NAME_NUMBER + " Integer";
private static String createStudentSQL = "CREATE TABLE "+ DataColumn.StudentTable.TABLE_NAME_SUTDENT
+ "("
+ DataColumn.StudentTable.COLUMN_NAME_ID + " Integer NOT NULL PRIMARY KEY AUTOINCREMENT,"
+ DataColumn.StudentTable.COLUMN_NAME_NAME + " varchar(50), "
+ DataColumn.StudentTable.COLUMN_NAME_AGE + " varchar(3) "
+")";
public DataBaseHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
Logs.e("DataBaseHelper 构造方法");
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL(createUserTabelSQL);
Logs.e("onCreate 方法");
}
/**
* 版本号变化时执行。newVersion 必须大于 oldVersion，执行完后 oldVersion = newVersion
*/
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
Logs.e("onUpgrade 方法 oldVersion: " + oldVersion + " ,newVersion: " + newVersion);
switch (oldVersion) {
case 1: //旧版本 1：加 sex 字段
db.execSQL(alterUserAddSexSQL);
case 2: //旧版本 2：加 number，并新建学生表（注意 case 故意不 break，便于连升）
db.execSQL(alterUserAddNumberSQL);
db.execSQL(createStudentSQL);
}
}
}
public class DataColumn {
public static class UserTable implements BaseColumns{
public static final String TABLE_NAME_USER = "user";
public static final String COLUMN_NAME_NAME = "name";
public static final String COLUMN_NAME_PASSWORD = "password";
public static final String COLUMN_NAME_SEX = "sex";
public static final String COLUMN_NAME_NUMBER = "number";
}
public static class StudentTable{
public static final String TABLE_NAME_SUTDENT = "student";
public static final String COLUMN_NAME_ID = "_id";
public static final String COLUMN_NAME_NAME = "name";
public static final String COLUMN_NAME_AGE = "age";
}
}
getWritableDatabase() 与 getReadableDatabase() 都能拿到 SQLiteDatabase。后者不是一上来就只读：它先走可写打开，失败（例如磁盘满）再尝试只读。磁盘满时 getWritableDatabase() 会直接出错。
四、创建库与增删改查
1. 创建 / 打开数据库
调用 Helper 的 getReadableDatabase()，首次会回调 onCreate()。
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
DatabaseHelper dbHelper = new DatabaseHelper(this); // Activity 里才用 this
SQLiteDatabase db = dbHelper.getReadableDatabase();
}
常用 API：
(int) delete(String table, String whereClause, String[] whereArgs)
(long) insert(String table, String nullColumnHack, ContentValues values)
(int) update(String table, ContentValues values, String whereClause, String[] whereArgs)
(void) execSQL(String sql)
(void) close()
(Cursor) query(String table, String[] columns, String selection, String[] selectionArgs,
String groupBy, String having, String orderBy, String limit)
(Cursor) rawQuery(String sql, String[] selectionArgs) // 预置 SQL，利于防注入
增删改查各有两条路：API 便捷方法，或 execSQL / rawQuery。
2. 添加
insert：
ContentValues cv = new ContentValues();
cv.put("username","Jack Johnson");
cv.put("password","iLovePopMusic");
db.insert("user",null,cv);
execSQL：
String sql = "insert into user(username,password) values ('Jack Johnson','iLovePopMusic')";
db.execSQL(sql);
3. 删除
String whereClause = "username=? and sex=?";
String[] whereArgs = {"Jack Johnson","男"};
db.delete("user",whereClause,whereArgs);
String sql = "delete from user where username='Jack Johnson'";
db.execSQL(sql);
4. 修改
ContentValues cv = new ContentValues();
cv.put("password","iHatePopMusic");
String whereClause = "username=?";
String[] whereArgs = {"Jack Johnson"};
db.update("user",cv,whereClause,whereArgs);
String sql = "update [user] set password = 'iHatePopMusic' where username='Jack Johnson'";
db.execSQL(sql);
5. 查询
query 参数：table 表名，columns 列数组，selection 相当于 where，selectionArgs 条件参数，groupBy / having / orderBy / limit，返回 Cursor（类似 ResultSet）。
| Cursor 方法 |
作用 |
getCount() |
总记录条数 |
isFirst() / isLast() |
是否第一条 / 最后一条 |
moveToFirst() / moveToLast() |
移到首 / 末 |
move(int offset) |
相对移动 offset |
moveToNext() / moveToPrevious() |
下一条 / 上一条 |
getColumnIndex(String) |
列名 → 列下标 |
方法一（按条数循环 + move）：
Cursor c = db.query("user",null,null,null,null,null,null);
if(c.moveToFirst()){
for(int i=0;i<c.getCount();i++){
c.move(i);
String username = c.getString(c.getColumnIndex("username"));
String password = c.getString(c.getColumnIndex("password"));
}
}
方法二（while moveToNext，更常用）：
Cursor c = db.query("user",null,null,null,null,null,null);
while(c.moveToNext()){
int id = c.getInt(c.getColumnIndex("id"));
String name = c.getString(c.getColumnIndex("name"));
String number = c.getString(c.getColumnIndex("number"));
Logs.v("id :"+id + " name :"+name+ " number :"+number);
}
rawQuery 带参数：
Cursor c = db.rawQuery("select * from user where username=?", new String[]{"Jack Johnson"});
if(c.moveToFirst()) {
String password = c.getString(c.getColumnIndex("password"));
}
一句话总结：Android 用 SQLite；日常走 SQLiteOpenHelper 管创建和升级，增删改用 ContentValues API 或 execSQL，查询用 query / rawQuery 拿 Cursor。
