sqlite 实用教程

一些SQLite操作命令

1 adb shell 进入命令模式

2 cd 文件名 进入文件

3 ls或ls -l 查看目录下的文件

4 sqlite3 数据库名 进入数据库

5 .schema 查看数据库下的信息

6 ctrl+d 退出sqlite模式

首先是创建db数据库， android有一个 SQLiteOpenHelper 类可以使用

package com.example.testcustomerlistsql;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DataBaseHelper extends SQLiteOpenHelper {

	public DataBaseHelper(Context context,String name,CursorFactory factory,int version){
		super(context,name,factory,version);
	}

	public void onCreate(SQLiteDatabase db){
		Log.d("LL", "Create a table Customer_List _start");
		
		db.execSQL("CREATE TABLE IF NOT EXISTS customerlist(_id integer primary key autoincrement," +
		"user_id varchar(30),user_mobile varchar(30),user_name varchar(30),id_type varchar(30),identification varchar(30), status varchar(1))");
	}

	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		Log.d("LL", "更新表结构");		
		
		db.execSQL("DROP TABLE IF EXISTS customerlist"); 
		onCreate(db); 
	}
}

增：

		  //使用execSQL方法向表中插入数据
		  db.execSQL("insert into hero_info(name,level) values('bb',1)");
		  //使用insert方法向表中插入数据
		  ContentValues values = new ContentValues();
		  values.put("name", "xh");
		  values.put("level", 2);
		  //调用方法插入数据
		  db.insert("hero_info", "id", values);

删

sqld.delete(dbc,"user_id=?" ,new String[]{UserID});

查

		DataBaseHelper db = new DataBaseHelper(CustomerList.this, "user", null,2);
		
		SQLiteDatabase sqld = db.getWritableDatabase();
		Cursor cursor = sqld.query(dbc, new String[] { "_id", "user_id","user_mobile", "user_name", "id_type", "identification", "status"}, null, null, null, null, null);
		
		int i = 0;
		while (cursor.moveToNext()) {
			String _id = cursor.getString(cursor.getColumnIndex("_id"));
			String user_id = cursor.getString(cursor.getColumnIndex("user_id"));
			String user_mobile = cursor.getString(cursor.getColumnIndex("user_mobile"));
			String user_name = cursor.getString(cursor.getColumnIndex("user_name"));
			String id_type = cursor.getString(cursor.getColumnIndex("id_type"));
			String identification = cursor.getString(cursor.getColumnIndex("identification"));;
			String status = cursor.getString(cursor.getColumnIndex("status"));
			
			Log.d("LL", "_id——>" + _id + "   user_id——>" + user_id +"   status——>" + status
					+ "  user_mobile——>" + user_mobile + "  user_name——>" + user_name
					+ "  id_type——>" + id_type + "  identification——>" +identification);

			strUserId[i] = user_id;
			strMobile[i] = user_mobile;
			strName[i] = user_name;
			strIdType[i] = id_type;
			strIdentification[i]= identification;
			strStatus[i]= status;
			i++;
		}
		cursor.close();
		sqld.close();
	

改

sqd.update(dbc, values,"user_id=?", new String[]{strUserId[i]});

直接添加 ——列

db.execSQL("ALTER TABLE hero_info ADD COLUMN asd INTEGER DEFAULT 0");