Android学习之sqlite基础

sqlite基础知识介绍
1.sqlite数据库数据类型

Integer varchar(10) float double char(10) text

2.数据库语句

2.1创建表语句
create table person(-id Integer primary key, name varchar(10), age Integer not null)
2.2删除表语句
drop table person
2.3插入语句
Insert into person(_id, age) values(1,20)
2.4修改语句
update person set name="ls", age=20 where _id=10
2.5删除数据
delete from person where _id=1
2.6查询语句
select 字段名 from 表名 where 查询条件 group by 分组字段 having 筛选条件 order by 排序字段

select * from person;

select * from person where age between 10 and 20;

sqlite Demo应用

Constant.java:

package com.czhappy.sqlitedemo.utils;

/**
 * Description:
 * User: chenzheng
 * Date: 2016/11/10 0010
 * Time: 10:42
 */
public class Constant {

    public static final String DATABASE_NAME = "info.db";
    public static final int DATABASE_VERSION = 1;
    public static final String TABLE_NAME = "person";
    public static final String _ID = "_id";
    public static final String NAME = "name";
    public static final String AGE = "age";
}

MySqliteHelper.java:

package com.czhappy.sqlitedemo.utils;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

/**
 * Description:
 * User: chenzheng
 * Date: 2016/11/10 0010
 * Time: 10:37
 */
public class MySqliteHelper extends SQLiteOpenHelper{

    public MySqliteHelper(Context context){
        super(context, Constant.DATABASE_NAME, null, Constant.DATABASE_VERSION);
    }

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

    /**
     * 数据库创建调用
     * @param db
     */
    @Override
    public void onCreate(SQLiteDatabase db) {

        String sql = "create table "+Constant.TABLE_NAME+"(" + Constant._ID
                +" Integer primary key,"+Constant.NAME+" varchar(10),"
                +Constant.AGE +" Integer)";
        db.execSQL(sql);

    }

    /**
     *
     * 数据库版本更新回调函数
     * @param db
     * @param oldVersion
     * @param newVersion
     */
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }

    /**
     * 数据库打开回调函数
     * @param db
     */
    @Override
    public void onOpen(SQLiteDatabase db) {
        super.onOpen(db);
    }
}

DbManager.java:

package com.czhappy.sqlitedemo.utils;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;

/**
 * Description:数据库操作工具类
 * User: chenzheng
 * Date: 2016/11/10 0010
 * Time: 10:46
 */
public class DbManager {

    public static MySqliteHelper helper;

    public static MySqliteHelper getInstance(Context context){
        if(helper==null){
            helper = new MySqliteHelper(context);
        }
        return helper;
    }

    public static void execSql(SQLiteDatabase db, String sql){
        if(db!=null){
            if(sql!=null && !"".equals(sql)){
                db.execSQL(sql);
            }
        }
    }


}

MainActivity.java:

package com.czhappy.sqlitedemo;

import android.content.ContentValues;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.support.v7.app.AppCompatActivity;
import android.view.View;
import android.widget.Toast;

import com.czhappy.sqlitedemo.utils.Constant;
import com.czhappy.sqlitedemo.utils.DbManager;
import com.czhappy.sqlitedemo.utils.MySqliteHelper;

public class MainActivity extends AppCompatActivity {

    private MySqliteHelper helper;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        helper = DbManager.getInstance(this);
    }

    public void createDb(View view){
        SQLiteDatabase db = helper.getWritableDatabase();
    }

    public void click(View view){

        switch (view.getId()){
            case R.id.insert_btn:
                SQLiteDatabase db = helper.getWritableDatabase();
                String sql = "insert into "+ Constant.TABLE_NAME+" values(1, 'lisi', 20)";
                DbManager.execSql(db, sql);
                db.close();
                break;
            case R.id.update_btn:
                db = helper.getWritableDatabase();
                sql = "update "+ Constant.TABLE_NAME+" set name='xiaoming' where _id=1";
                DbManager.execSql(db, sql);
                db.close();
                break;
            case R.id.delete_btn:
                db = helper.getWritableDatabase();
                sql = "delete from "+ Constant.TABLE_NAME+" where _id=1";
                DbManager.execSql(db, sql);
                db.close();
                break;
            case R.id.insertApi_btn:
                db = helper.getWritableDatabase();
                ContentValues values = new ContentValues();
                values.put(Constant._ID, 3);
                values.put(Constant.NAME, "zhangsan");
                values.put(Constant.AGE, 30);
                long result = db.insert(Constant.TABLE_NAME, null, values);
                if(result>0){
                    Toast.makeText(MainActivity.this, "插入数据成功", Toast.LENGTH_SHORT).show();
                }else{
                    Toast.makeText(MainActivity.this, "插入数据失败", Toast.LENGTH_SHORT).show();
                }
                db.close();
                break;
            case R.id.updateApi_btn:
                db = helper.getWritableDatabase();
                values  = new ContentValues();
                values.put(Constant.NAME, "qianliu");
                int count = db.update(Constant.TABLE_NAME,values, Constant._ID+"=?", new String[]{"3"});
                if(count>0){
                    Toast.makeText(MainActivity.this, "修改数据成功", Toast.LENGTH_SHORT).show();
                }else{
                    Toast.makeText(MainActivity.this, "修改数据失败", Toast.LENGTH_SHORT).show();
                }
                db.close();
                break;
            case R.id.deleteApi_btn:
                db = helper.getWritableDatabase();
                values  = new ContentValues();
                values.put(Constant._ID, 3);
                int count2 = db.delete(Constant.TABLE_NAME, Constant._ID+"=?", new String[]{"3"});
                if(count2>0){
                    Toast.makeText(MainActivity.this, "删除数据成功", Toast.LENGTH_SHORT).show();
                }else{
                    Toast.makeText(MainActivity.this, "删除数据失败", Toast.LENGTH_SHORT).show();
                }
                db.close();
                break;
        }
    }
}