Android 开发之 Sqlite数据库操作

Sqlite 数据库

---

一、Sqlite 基本知识及SQL基本语句

1.Sqlite 所支持的数据类型

Interger; varchar(10) ; float; double; char(10); text

2.sql语句

2.1 创建表

//create table table_name(字段名 数据类型 约束,字段名 数据类型 约束......)

create table person(pid Integer primary key,name varchar(10), age Integer, score Integer, groupid Integer not null)

2.2 删除表

// drop table 表名
drop table person

2.3 插入数据

//insert into 表名[字段，字段] values(值1， 值2......)
insert into person(age, groupid) values(20,1)
insert into person values(13, "xiaoli", 25, 1)

2.4 修改数据

//update 表名 set 字段 = 新值 where 修改的条件
update person set age = 17 where pid = 13

2.5 删除数据

//delete from 表名 where 删除的条件
delete from person where pid = 13

2.6 查询语句

//select 字段名 from 表名 where 查询条件 group by 分组的字段 having 筛选条件 order by 排序字段

select * from person
select pid, name from person
select * from person where pid = 13
select * from person where pid <> 13
select * from person where pid = 13 and age > 15
select * from person where name like "%肖%"
select * from person where name like "_肖%"
select * from person where name is null
select * from person where age between 10 and 20
select * from person where age > 16 order by pid

二、Android创建Sqlite数据库

1.创建MySqlite类继承自SQLiteOpenHelper

class MySqlite(context:Context?, name:String?, cursor:SQLiteDatabase.CursorFactory?, version:Int) : SQLiteOpenHelper(context, name, cursor, version){
	//重写构造方法， 此处可不重写
	constructor(context:Context?):this(context, "mydb.db", null, 1)

	//重写onCreate 和 onUpgrade方法
	override fun onCreate(db:SQLiteDatabase?){
	}

	override fun onUpgrade(db:SQLiteDatabase?, oldVersion:Int, newVersion:Int){
	}
}

2.生成数据库

	//Activity 类中调用
	val	helper = MySqlite(this)    //返回的是SQLiteOpenHelper的对象
	//创建只读数据库
	val db_read = helper.readableDatabase

	//创建读写数据库
	val db_RW = helper.writeableDatabase

3. 数据库执行

//execSQL 可用于创建表，增加数据，删除数据，修改数据等。
  db_RW.execSQL("delete from person where pid = 13")

//使用api 函数操作数据库
  /* Insert  */
  var values = contentValuesOf()
  values.put("name", "zhangsan")
  values.put("age", 16)
  values.put("groupid", 2)
  val rowID = db_RW.insert("person", values)//返回新插入的数据的row ID,如果返回 -1表示插入失败

  /*  Update  下面两种方式效果相同*/
 //count  表示更新的数据的个数
  val count = db.update("person", values, "pid = 3", null)

  val count = db.update("person", values, "pid = ?", arrayOf("3"))

/*  Delete */
  val c1 = db.delete("person", "pid = 13", null)
  val c2 = db.delete("person", "pid = ?", arrayOf("1"))  //



/* select */
	/*
	 public Cursor query(String table, String[] columns, String selection,
	            String[] selectionArgs, String groupBy, String having,
	            String orderBy, String limit) 
	
	*/
  val cursor = db.query("person", null,null,null,null,null,null,null )

  while(cursor.moveToNext() != null)
  {
      val pid = cursor.getInt(0)
      val name = cursor.getString(2)
  }
  cursor.close()

  db_RW.close()