sqlite简单使用_sqlitem标签-CSDN博客

本文链接：https://blog.csdn.net/big_sea_m/article/details/81750964

1 sqlite数据库数据类型
Integer varchar(18) float double char(10) text

2 sql语句
2.1 创建表的语句
create table 表名 (字段名称数据类型约束,字段名称数据类型约束,… )
create table person(_id Integer primary key,name varchar(10),age Integer not null)

2.2 删除表的语句
drop table 表名
drop table person

2.3 插入数据
insert into 表名[字段，字段] values(值1，值2)
insert into person(_id,age) values(1,20)
insert into person values(1,”张三”,20)

2.4 修改数据
update 表名 set 字段=新值 where 修改的条件
update person set name=”lisi”,age=20 where _id=1

2.5 删除数据
delete from 表名 where删除条件
delete from person where _id=2

2.6 查询语句
select 字段名 from 表名 where 查询条件 group by 分组的字段名 having 筛选条件 order by 排序字段
select * from person;
select _id,name from person
select * from person where _id=1
select * from person where _id<>=1 // 查询条件是_id不等于1
select * from person where _id=1 and age>18
select * from person where name is null
select * from person where age between 10 and 20 // age在10到20之间
select * from person where age>18 order by _id
select * from person limit 0,15 // 查询第0到15条共15条 0-14

public class MainActivity extends AppCompatActivity {

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        findViewById(R.id.btCreateDB).setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                createDb();
            }
        });
        findViewById(R.id.btInsert).setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                insertData();
            }
        });

    }

    private void insertData() {
        SQLiteDatabase database = DbManager.getHelper(this).getReadableDatabase();
        DbManager.execSql(database,"insert into person values(1,'张三',20)");
        DbManager.execSql(database,"insert into person values(2,'李四',21)");
        database.close();
    }

    private void insertDataForApi() {
        SQLiteDatabase database = DbManager.getHelper(this).getReadableDatabase();
        ContentValues values = new ContentValues();
        values.put("_id",3);
        values.put("name","麻六");
        values.put("age",16);
        long l = database.insert("person",null, values);
        if (l>-1) {
            Log.e("HHH", "insertDataForApi: " + "成功" );
        }
        database.close();
    }

    private void updateData() {
        SQLiteDatabase database = DbManager.getHelper(this).getReadableDatabase();
        DbManager.execSql(database,"update person set name='王五' where _id=1");
        database.close();
    }

    private void updateDataForApi() {
        SQLiteDatabase database = DbManager.getHelper(this).getReadableDatabase();
        ContentValues values = new ContentValues();
        values.put("name","麻六");
        long l = database.update("person",values,"_id=3", null);
//        long l = database.update("person",values,"_id=?", new String[]{"3"});
        if (l>-1) {
            Log.e("HHH", "updateDataForApi: " + "成功" );
        }
        database.close();
    }

    private void deleteData() {
        SQLiteDatabase database = DbManager.getHelper(this).getReadableDatabase();
        DbManager.execSql(database,"delete from person where _id=2");
        database.close();
    }

    private void deleteDataForApi() {
        SQLiteDatabase database = DbManager.getHelper(this).getReadableDatabase();
        long l = database.delete("person","_id=3", null);
//        long l = database.delete("person","_id=?", new String[]{"3"});
        if (l>-1) {
            Log.e("HHH", "updateDataForApi: " + "成功" );
        }
        database.close();
    }

    // 查询
    private void selectDataBySql() {
        SQLiteDatabase database = DbManager.getHelper(this).getReadableDatabase();
//        Cursor cursor = DbManager.selectDataBySql(database,"select * from person",null);
        Cursor cursor = DbManager.selectDataBySql(database,"select * from person where _id=>",new String[]{"1"});

    }

    private void selectDataByApi() {
        SQLiteDatabase database = DbManager.getHelper(this).getReadableDatabase();
        /**
         *
         table: 表名
         columns：查询的字段名称 null查询全部
         selection：查询条件字句 不要where 如 _id=1 没有条件null
         selectionArgs：查询条件的值得String数组 没有null
         groupBy：分组条件 group by 字句 没有null
         having：筛选条件 having字句 没有null
         orderBy：排序 order by 字句 没有null

         */
        Cursor person = database.query("person", new String[]{}, "", new String[]{}, null, null, null);
        person.close();
        database.close();
    }

    // 事务
    private void insertDataBySW() {
        SQLiteDatabase database = DbManager.getHelper(this).getReadableDatabase();
        // 开启事务
        database.beginTransaction();
        DbManager.execSql(database,"insert into person values(1,'张三',20)");
        DbManager.execSql(database,"insert into person values(2,'李四',21)");
        // 提交事务
        database.setTransactionSuccessful();
        // 完成
        database.endTransaction();
        database.close();
    }

    /**
     * 创建数据库
     */
    private void createDb() {
        /**
         * getReadableDatabase和getWritableDatabase方法都是创建或打开可读可写的数据库
         *
         */
        SQLiteDatabase database = DbManager.getHelper(this).getWritableDatabase();

    }
}

public class MySQLiteOpenHelper extends SQLiteOpenHelper {

    /**
     * 构造方法
     * @param context 上下文
     * @param name 数据库名称
     * @param factory 游标工厂 可以null
     * @param version 必须版本>=1
     */
    public MySQLiteOpenHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
        super(context, name, factory, version);
    }

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

    /**
     * 创建数据库时回调
     * 只要数据库被创建了就不会再回调该方法
     * @param db 数据库对象
     */
    @Override
    public void onCreate(SQLiteDatabase db) {

        Log.e("HHH", "onCreate: " + "创建数据库" );
        String sql = "create table person(_id Integer primary key,name varchar(10),age Integer)";
        db.execSQL(sql);  // 执行sql语句
    }

    /**
     * 当版本更新时回调
     * @param db 数据库对象
     * @param oldVersion 旧版本号
     * @param newVersion 新版本号
     */
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

        Log.e("HHH", "onCreate: " + "更新数据库" );
    }
}

public class DbManager {

    private static MySQLiteOpenHelper helper;
    public static MySQLiteOpenHelper getHelper(Context context) {
        if (helper==null) {
            synchronized (DbManager.class) {
                if (helper==null){
                    helper = new MySQLiteOpenHelper(context);
                }
            }
        }
        return helper;
    }

    /**
     * 执行sql语句
     * @param db 数据库对象
     * @param sql sql语句
     */
    public static void execSql(SQLiteDatabase db,String sql) {
        if (db!=null && !TextUtils.isEmpty(sql)) {
            db.execSQL(sql);
        }
    }

    /**
     * 查询
     * @param db db
     * @param sql sql语句
     * @param selectionArgs 查询条件
     * @return 返回cursor
     */
    public static Cursor selectDataBySql(SQLiteDatabase db,String sql,String[] selectionArgs) {
        Cursor cursor = null;
        if (db!=null && !TextUtils.isEmpty(sql)) {
            cursor = db.rawQuery(sql,selectionArgs);
        }
        return cursor;
    }
}