Android Studio SQLite数据库

相关类

一. SQLiteOpenHolper 用来创建数据库，数据表，更新版本
用法:

• 1.继承 class MyHelper extends SQLiteOpenHelper
• 2.调用父类构造方法 super(上下文对象，“数据名.db",null(工厂），版本号)
• 3.重写onCreate,只调用一次 (建表、添加初始数据）
• 4.重写update()，只有版本更新时会调用

二.SQLiteDatabase

• 用法：
• 1.获得数据库 helper对象.getWritableDatabase();
• 2.增删改：execSQL(“写sql语句”，new String[]{值});
• 查： rawQuery("写查的sql语句”,new String[]{值});
• 3.增 insert(“表名”,“列名”,值（ContentValue))
• 删 delete(“表名”,“列名=？条件”，new String[]{值});
• 改 update("表名”,新值ContentValue对象，“条件”，new String[]{值}）
• 查 query(“表名”,new String[]{列名},“条件”，new String[]{值}，"分组的列名”，“having条件”，“排序的列名”）
• 3.ContentValue
• 用法：1.new对象 2.put(“列名”,值);
• 4.Cursor查询出来的结果集
• 循环使用 while(c.moveToNext()){}
• 方法：
• getColumnIndex("列名”）;列名对应的下标
• getString()/getInt（列下标）/getBlob(列下标)-byte[]
• getColumnName();//获得列名

代码解析

创建类继承SQLiteOpenHelper

public class MyHelper extends SQLiteOpenHelper {
    public MyHelper(Context context) {
        super(context,"day7",null,1);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        //学生表数据
        db.execSQL("create table student(id integer primary key autoincrement,name varchar(20),sex varchar(10),birth integer,department varchar(20),address varchar(20))");
        //成绩表数据
        db.execSQL("create table score(id integer primary key autoincrement,stu_id integer,c_name varchar(20),grade integer)");
        //学生表添加数据
        db.execSQL("insert into student values(901,'张老大','男',1985,'计算机系','北京市海淀区')");
        db.execSQL("insert into student values(902,'张老二','男',1986,'中文系','北京市昌平区')");
        db.execSQL("insert into student values(903,'张三','女',1990,'中文系','湖南省永州市')");
        db.execSQL("insert into student values(904,'李四','男',1990,'英语系','辽宁省阜新市')");
        db.execSQL("insert into student values(905,'王五','女',1991,'英语系','福建省厦门市')");
        db.execSQL("insert into student values(906,'王六','男',1988,'计算机系','湖南省衡阳市')");
        //成绩表添加数据
        db.execSQL("insert into score values(1,901,'计算机',98)");
        db.execSQL("insert into score values(2,901,'英语',80)");
        db.execSQL("insert into score values(3,901,'计算机',65)");
        db.execSQL("insert into score values(4,901,'中文',88)");
        db.execSQL("insert into score values(5,901,'中文',95)");
        db.execSQL("insert into score values(6,901,'计算机',70)");
        db.execSQL("insert into score values(7,901,'英语',92)");
        db.execSQL("insert into score values(8,901,'英语',94)");
        db.execSQL("insert into score values(9,901,'计算机',90)");
        db.execSQL("insert into score values(10,901,'英语',85)");
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }
}

获得对象

MyHelper myHelper = new MyHelper(MainActivity.this);
db = myHelper.getWritableDatabase();

增删改查

/**
         * 查询显示
         */
        show.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                Cursor cursor = db.rawQuery("select * from student", null);
                while (cursor.moveToNext()){
                    int id = cursor.getInt(cursor.getColumnIndex("id"));
                    String name = cursor.getString(cursor.getColumnIndex("name"));
                    String sex = cursor.getString(cursor.getColumnIndex("sex"));
                    int birth = cursor.getInt(cursor.getColumnIndex("birth"));
                    String department = cursor.getString(cursor.getColumnIndex("department"));
                    String address = cursor.getString(cursor.getColumnIndex("address"));
                    Student student = new Student(id, name, sex, birth, department, address);
                    sb.append(student.toString()+"\n");
            }
                tv.setText(sb.toString());
            }
        });
        /**
         * 增加
         */
        add.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                db.execSQL("insert into student values(null,'add','男',1985,'计算机系','北京市海淀区')");
            }
        });
        /**
         * 删除
         */
        del.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                db.execSQL("delete from student where student.name = ?",new String[]{"add"});
            }
        });
        /**
         * 修改
         */
        set.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                db.execSQL("update student set name = '赵六' where student.name = ?",new String[]{"王六"});
            }
        });