Android Studio创建sqlite数据库及SQL语句的执行

Android中数据库的创建
• 数据库类：SQLite Database
• 数据库帮助类：SQLiteOpenHelper
方法一
• db = SQLiteDatabase.openOrCreateDatabase(DATABASE_NAME, Context.MODE_PRIVATE, null)

方法二
• db = getContext().openOrCreateDatabase (DATABASE NAME, Context.MODE PRIVATE, null)
方法三
• db = SQLiteOpenHelper.getWritableDatabase()
• db = SQLiteOpenHelper.getReableDatabase()

执行SQL语句，这里是直接执行
• String sq| = "insert into tableName values (" 小华", "111");";
• db.execSQL(sql);

Android中封装好的SQL语句执行方法
•增加一条数据 db.insert(TABLE_ NAME, null, values);
•删除一条数据 db.delete(TABLE_ NAME, selection, selectionArgs)
•修改一条数据 db.update(TABLE_ NAME,values, selection,selectionArgs);
•查询一条数据 db.query(
        TABLE_NAME,         // The table to query
        projection,        //The array of columns to return (pass null to get all)
        selection,        // The columns for the WHERE clause
        selectionArgs,        // The values for the WHERE clause
        null,                // don't group the rows
        null,                // don't filter by row groups
        sortOrder       // The sort order

        );

下面是我写的创建数据库及简单的增删改查。

public class MySQLiteOpenHelper extends SQLiteOpenHelper {

    private static final String DB_NAME="mySQLite.db";//数据库名字
    private static final String TABLE_NAME_STUDENT="student";//表的名字
    //创建表
    private static final String CREATE_TABLE_SQL="create table "+TABLE_NAME_STUDENT +"(id integer primary key autoincrement,name text,number text,gender text,score text )";
    
    public MySQLiteOpenHelper(Context context){
        super(context,DB_NAME,null,1);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {  //onCreate是创建数据库方法
        db.execSQL(CREATE_TABLE_SQL);
    }
    
    //这个方法是数据库升级的时候使用到的，因为我没有用到，所以就没有写
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }
    //添加数据
    public  long insertData(Student student){
        SQLiteDatabase db = getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put("name",student.getName());
        values.put("number",student.getNumber());
        values.put("gender",student.getGender());
        values.put("score",student.getScore());
        return  db.insert(TABLE_NAME_STUDENT,null,values);
    }
    //根据学号删除信息 防止有重名的同学
    public int deleteFromDbByNumber(String number){
        SQLiteDatabase db=getWritableDatabase();
        //返回的是删除的条数
        return db.delete(TABLE_NAME_STUDENT,"number like ?",new String[] {number});
    }
    //修改数据
    public int updateData(Student student){
        SQLiteDatabase db=getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put("name",student.getName());
        values.put("number",student.getNumber());
        values.put("gender",student.getGender());
        values.put("score",student.getScore());
        //依旧是根据学号改
        return db.update(TABLE_NAME_STUDENT,values,"number like ?",new String[] {student.getNumber()});
    }
    //查询数据
    public List<Student> queryFromByNumber(String number){
        SQLiteDatabase db=getWritableDatabase();
        List<Student> studentList=new ArrayList<>();
        Cursor cursor=db.query(TABLE_NAME_STUDENT,null,"number like ?",new String[] {number},null,null,null);
        if(cursor!=null){
            while (cursor.moveToNext()){
                int name1=cursor.getColumnIndex("name");
                String name= cursor.getString(name1);
                int number1=cursor.getColumnIndex("number");
                String number2= cursor.getString(number1);
                int gender1=cursor.getColumnIndex("gender");
                String gender= cursor.getString(gender1);
                int score1=cursor.getColumnIndex("score");
                String score= cursor.getString(score1);

                Student student=new Student();
                student.setName(name);
                student.setNumber(number2);
                student.setGender(gender);
                student.setScore(score);

                studentList.add(student);
            }
            cursor.close();
        }
        return studentList;
    }
}