1、创建或删除数据表:
如:
SQLiteDatabase test = SQLiteDatabase.openDatabase(LOG_DBPATH, null, SQLiteDatabase.OPEN_READWRITE | SQLiteDatabase.CREATE_IF_NECESSARY); String strSql = "create table if not exists " + LOG_DBTAB_NAME + "(id integer primary key AUTOINCREMENT, time text)"; test.execSQL(strSql);
基本流程是:打开数据库,执行SQL语句进行各项操作,关闭数据库
SQLite数据库打开标识 标识类型 说明
SQLiteDatabase. CREATE_IF_NECESSARY 按需创建
SQLiteDatabase. NO_LOCALIZED_COLLATORS 不使用本地化校验
SQLiteDatabase. OPEN_READONLY 只读方式
SQLiteDatabase. OPEN_READWRITE 读写方式
2、往数据库表中 添加列
/** *添加列 */ public void addColumnByBySQL(String tableName, String columnName) { try { boolean checkColumnExists = checkColumnExists(tableName, columnName); if (!checkColumnExists) { String addColumnSQL = "alter table " + tableName + " add column " + columnName + " varchar"; db.execSQL(addColumnSQL); Log.d("addColumnByBySQL", addColumnSQL); } } catch (Exception e) { e.printStackTrace(); } } /** *判断表中是否存在列 * @param tableName 表名 * @param columnName 列名 * @return */ public boolean checkColumnExists(String tableName, String columnName) { boolean result = false; Cursor cursor = null; try { cursor = db.rawQuery("select * from sqlite_master where name = ? and sql like ?", new String[]{tableName, "%" + columnName + "%"}); result = null != cursor && cursor.moveToFirst(); } catch (Exception e) { Log.e("tag", "checkColumnExists2..." + e.getMessage()); } finally { if (null != cursor && !cursor.isClosed()) { cursor.close(); } } return result; }
3、创建数据库索引
/** * 通过sql创建索引 * @param indexName 索引名称 * @param tableName 表名 * @param columnNameStr 表中需要添加索引的列表,可以是 多个列 * @return */ private String addIndexBySQL(String indexName, String tableName, String columnNameStr) { String sqlStr = ""; sqlStr = " create index if not exists " + indexName + " on " + tableName + "(" + columnNameStr + ")"; return sqlStr; }
例子:
/** * 添加索引 */ public void addIndexOnTable() { try { List<String> sqlStrs = new ArrayList<>(); // JTYS_KHQYB 添加索引cardNum sqlStrs.add(addIndexBySQL("index_khqyb_creater", "JTYS_KHQYB", "cardNum")); // JTYS_FWMYD 添加索引(creater,satisfiedState) sqlStrs.add(addIndexBySQL("index_fwmyd_creater", "JTYS_FWMYD", "creater,satisfiedState")); for (int i = 0, size = sqlStrs.size(); i < size; i++) { db.execSQL(sqlStrs.get(i)); Log.e("addIndexOnTable", sqlStrs.get(i)); } } catch (Exception e) { e.printStackTrace(); } }
4、对于数据库中某个列名含有多个,拼接并需要查询单个值的数量时 SQL这样写
(','||POPULATION_TYPE||',') 为 默认为 列 POPULATION_TYPE 默认左右都添加 ,,
匹配的时候 ,type, 匹配
例子 :
POPULATION_TYPE 结果值中有 2 2,3 1,2 三个结果值时,默认所有结果值都被加上,,
如 2 变成 ,2,
2,3 变成 ,2,3,
1,2 变成 ,1,2,
/**
* 查询类别 1:一般人群,2:儿童,3:孕产妇,4:老年人,5:重精神病,6:高血压,7:糖尿病,8:冠心病,9:脑卒中 10 残疾人默认值1
*/
public int getCountByJDRType(String str_jdrno, String str_type) {
int count = 0;
try {
String strCountByJDRTypeSql = "select count(ID) from ARCHIVE_BASEINFO where CREATER='" + str_jdrno
+ "' and CREATED_BY='" + str_jdrno + "' and (','||POPULATION_TYPE||',') like '%," + str_type
+ ",%'";
Cursor cursor = db.rawQuery(strCountByJDRTypeSql, null);
Log.w("sql---查询疾病类型种类" + str_type, strCountByJDRTypeSql);
cursor.moveToFirst();
long cout_long = cursor.getLong(0);
count = (int) cout_long;
cursor.close();
} catch (Exception e) {
e.printStackTrace();
count = 0;
}
return count;
}
这篇文章不错:
sqlite查看所有表名及字段名,及判断是否存在的方法