SQLite数据库的基本使用与笔记

SQLite数据库的使用

1.创建数据库

class MyDatabaseHelper(val context : Context, name : String, version : Int) : 
		SQLiteOpenHelper(context, name, null, version) {
	private val createBook = "create table Book("+
			" id integer primary key autoincrement,"+
			"author text,"+
			"price real,"+
			"pages integer,"+
			"name text)"
			//"create table Book( 	id iteger primary key autoincrement, 
			//						author text, //字符串
			//					 	prive real,  //浮点
			//					 	pages integer  //整型
			//					 	name text)"	//字符串-
	override fun onCreate(db: SQLiteDatabase){
		db.execSQL(createBook)
	}

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

}

我们把建表语句定义成一个字符串变量，然后再onCreate()中调用execSQL()去执行这条建表语句。

class MainActivity : APPCompatActivity(){
	override fun onCreate(savedInstanceState : Bundle?){
		...
		val dbHelper = MyDatabaseHelper(this, "BookStore.db", 1)
		btn.setOnClickListener{ 
			dbHelper.writableDatabase // 这里getWritableDatabase 
		}
	}
}

getWritableDatabase() / getReadableDatabase() :
若存在则返回databse
若不存在则执行onCreate()

2.升级数据库（增加表）

若现在要增加这个表Category

String create_str = "create table Category(
	id integer primary key autoincrement,
	category_name text,
	category_code integer )"

但在MyDatabaseHelper类里要加上相应代码

class MyDatabaseHelper(val context : Context, name : String, version : Int) : 
		SQLiteOpenHelper(context, name, null, version) {
	private val createBook = "create table Book("+
			" id integer primary key autoincrement,"+
			"author text,"+
			"price real,"+
			"pages integer,"+
			"name text)"
		private	val createCategory = "create table Category("+
			"id integer primary key autoincrement,"
			"category_name text,"
			"category_code integer )"
			
	override fun onCreate(db: SQLiteDatabase){
		db.execSQL(createBook)
		db.execSQL(createCategory)
	}

	override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int){
		//若存在则直接清除
		db.execSQL("drop table if exists Book")
		db.execSQL("drop table if exists Category")
		//手动显式调用onCreate()重新创建表
		db.onCreate()
	}									 	

}

在MainActivity中指定新的版本号才会回调onUpgrade()

class MainActivity : APPCompatActivity(){
	override fun onCreate(savedInstanceState : Bundle?){
		...
		val dbHelper = MyDatabaseHelper(this, "BookStore.db", 2)  //这里指定为2！！
		btn.setOnClickListener{ 
			dbHelper.writableDatabase // 这里getWritableDatabase 
		}
	}
}

3.添加数据项

val dbHelper = MyDatabaseHelper(this, "BookStore.db",2)
val db = dbHelper.writableDatabase
val values = ContentValues().apply{
	put("name", "The Da Vinci Code")
	put("author", "Dan Brown")
	put("pages", 510)
	put("price", 16.96)
}
db.insert("Book", null, values)
// db.insert("数据库名字"， null, 插入的数据项value)

4.更新数据

val dbHelper = MyDatabaseHelper(this, "BookStore.db",2)
val db = dbHelper.writableDatabase
val values = ContentValues()
values.put("price", 10.99)
db.update("Book",values, "name = ?",arrayOf("The Da Vinci Code"))
// db.insert("数据库名字"， null, 插入的数据项value)

5.删除数据

delete(a,b,c)三个参数，a:表名 ，b、c用于约束删除某一行或几行

val db = dbHelper.writableDatabase
db.delete("Book", "pages > ?", arrayOf("500"))

6.查询数据

val db = dbHelper.writableDatabase
//select name ,price from Book where pages>500
val cursor = db.query("Book", arrayOf("name", "price"),"pages > ?", arrayOf("500"),null ,null, null)
//select * from Book
val cursor = db.query("Book", null, null, null, null ,null, null)

db.query()7个参数