不管是使用开源的数据库还是原生自带的,学会一些SQL命令是非常有必要的. 

文章接下来是自己在使用Room的过程中使用的sql语句.关于Room的使用说明见官网 https://developer.android.com/training/data-storage/room

在操作数据库以及数据库升级的过程中,会频繁的用到sql语句,下面来分别说明

1 使用 Room DAO 访问数据

    @Entity
    public class User {
        @PrimaryKey
        public int id;

        public String firstName;
        public String lastName;
        public int age;
    }
idfirstNamelastNameage
1xm噫23
2df兄55
3er弟40

查询数据

    @Dao
    public interface MyDao {
        @Query("SELECT * FROM user")
        public User[] loadAllUsers();
    }
    
    @Dao
    public interface MyDao {
        @Query("SELECT * FROM user WHERE age BETWEEN :minAge AND :maxAge")
        public User[] loadAllUsersBetweenAges(int minAge, int maxAge);

        @Query("SELECT * FROM user WHERE first_name LIKE :search " +
               "OR last_name LIKE :search")
        public List<User> findUserWithName(String search);
    }

注意: 查询语句的时候,可以选择性的返回列.例如如下代码.

    @Dao
    public interface MyDao {
        @Query("SELECT first_name, last_name FROM user")
        public List<NameTuple> loadFullName();
    }

Room 知道该查询会返回 first_name 和 last_name 列的值，并且这些值会映射到 NameTuple 类的字段中。因此，Room 可以生成正确的代码。如果查询返回的列过多，或者返回 NameTuple 类中不存在的列，则 Room 会显示一条警告。

 

删除语句

注意@Delete不能添加sql语句,所以要用@Query来支持条件删除

@Query("DELETE FROM user WHERE age=:age")
int deleteByAge(int age);

2 数据库升级

 public static AdvertDatabase getInstance(Context context) {
        if (sInstance == null) {
            synchronized (AdvertDatabase.class) {
                if (sInstance == null) {
                    sInstance = Room.databaseBuilder(context.getApplicationContext(),
                            AdvertDatabase.class, "user.db")
                            .allowMainThreadQueries()
                            .addMigrations(MIGRATION_1_2, MIGRATION_2_3, MIGRATION_3_4, MIGRATION_4_5, MIGRATION_5_6,MIGRATION_6_7)
                            .addCallback(CALLBACK)
                            .fallbackToDestructiveMigration()
                            .build();
                }
            }
        }
        return sInstance;
    }
 @VisibleForTesting
    static final Migration MIGRATION_3_4 = new Migration(3, 4) {
        @Override
        public void migrate(@NonNull SupportSQLiteDatabase database) {
            Log.i(TAG, "migrate 3==>4: ");
            database.execSQL("ALTER TABLE `User` ADD COLUMN weight INTEGER NOT NULL  DEFAULT 0 ");
          
            database.execSQL("DROP INDEX IF EXISTS `id`");
            database.execSQL("CREATE UNIQUE INDEX `user_id` ON `User` (`id`)");
        }
    };
 @VisibleForTesting
    static final Migration MIGRATION_5_6 = new Migration(5, 6) {
        @Override
        public void migrate(@NonNull SupportSQLiteDatabase database) {
            Log.i(TAG, "migrate 5==>6: ");
            database.execSQL("DROP TABLE IF EXISTS `User`");
            database.execSQL("CREATE TABLE IF NOT EXISTS `User` (`id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,`age` INTEGER ,`lastName` TEXT  NOT NULL,`firstName` TEXT NOT NULL)");
            database.execSQL("ALTER TABLE `AdvertBean` DROP COLUMN weight INTEGER ");
        }
    };

以下是详细说明

DROP TABLE 语句用于删除表。
DROP TABLE table_name

DROP INDEX 语句
DROP INDEX 语句用于删除表中的索引

CREATE TABLE 语句用于创建数据库中的表。
表由行和列组成，每个表都必须有个表名。

创建表的时候注意数据类型,目前android支持的数据类型包括:

见参考： https://www.runoob.com/sqlite/sqlite-data-types.html 

SQL ALTER TABLE 语法
如需在表中添加列，请使用下面的语法:
ALTER TABLE table_name
ADD column_name datatype

如需删除表中的列，请使用下面的语法（请注意，某些数据库系统不允许这种在数据库表中删除列的方式）：
ALTER TABLE table_name
DROP COLUMN column_name

要改变表中列的数据类型，请使用下面的语法：
ALTER TABLE table_name
ALTER COLUMN column_name datatype

常用SQL语句参考

SQL 语句语法
AND / ORSELECT column_name(s)
FROM table_name
WHERE condition
AND|OR condition
ALTER TABLEALTER TABLE table_name
ADD column_name datatype
or
ALTER TABLE table_name
DROP COLUMN column_name
AS (alias)SELECT column_name AS column_alias
FROM table_name
or
SELECT column_name
FROM table_name AS table_alias
BETWEENSELECT column_name(s)
FROM table_name
WHERE column_name
BETWEEN value1 AND value2
CREATE DATABASECREATE DATABASE database_name
CREATE TABLECREATE TABLE table_name
(
column_name1 data_type,
column_name2 data_type,
column_name2 data_type,
...
)
CREATE INDEXCREATE INDEX index_name
ON table_name (column_name)
or
CREATE UNIQUE INDEX index_name
ON table_name (column_name)
CREATE VIEWCREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition
DELETEDELETE FROM table_name
WHERE some_column=some_value
or
DELETE FROM table_name
(Note: Deletes the entire table!!)
DELETE * FROM table_name
(Note: Deletes the entire table!!)
DROP DATABASEDROP DATABASE database_name
DROP INDEXDROP INDEX table_name.index_name (SQL Server)
DROP INDEX index_name ON table_name (MS Access)
DROP INDEX index_name (DB2/Oracle)
ALTER TABLE table_name
DROP INDEX index_name (MySQL)
DROP TABLEDROP TABLE table_name
GROUP BYSELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVINGSELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value
INSELECT column_name(s)
FROM table_name
WHERE column_name
IN (value1,value2,..)
INSERT INTOINSERT INTO table_name
VALUES (value1, value2, value3,....)
or
INSERT INTO table_name
(column1, column2, column3,...)
VALUES (value1, value2, value3,....)
INNER JOINSELECT column_name(s)
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name
LEFT JOINSELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name=table_name2.column_name
RIGHT JOINSELECT column_name(s)
FROM table_name1
RIGHT JOIN table_name2
ON table_name1.column_name=table_name2.column_name
FULL JOINSELECT column_name(s)
FROM table_name1
FULL JOIN table_name2
ON table_name1.column_name=table_name2.column_name
LIKESELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern
ORDER BYSELECT column_name(s)
FROM table_name
ORDER BY column_name [ASC|DESC]
SELECTSELECT column_name(s)
FROM table_name
SELECT *SELECT *
FROM table_name
SELECT DISTINCTSELECT DISTINCT column_name(s)
FROM table_name
SELECT INTOSELECT *
INTO new_table_name [IN externaldatabase]
FROM old_table_name
or
SELECT column_name(s)
INTO new_table_name [IN externaldatabase]
FROM old_table_name
SELECT TOPSELECT TOP number|percent column_name(s)
FROM table_name
TRUNCATE TABLETRUNCATE TABLE table_name
UNIONSELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2
UNION ALLSELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2
UPDATEUPDATE table_name
SET column1=value, column2=value,...
WHERE some_column=some_value
WHERESELECT column_name(s)
FROM table_name
WHERE column_name operator value

SQL 函数

  • AVG() - 返回平均值
  • COUNT() - 返回行数
  • FIRST() - 返回第一个记录的值
  • LAST() - 返回最后一个记录的值
  • MAX() - 返回最大值
  • MIN() - 返回最小值
  • SUM() - 返回总和

参考: 

https://www.runoob.com/sql/sql-tutorial.html

https://developer.android.com/training/data-storage/room

 

Logo
华为云开发者联盟

为开发者提供学习成长、分享交流、生态实践、资源工具等服务，帮助开发者快速成长。

更多推荐

cover

通过HPA+CronHPA组合应对业务复杂弹性伸缩场景

avatar 华为云开发者联盟
cover

FT-FMEA融合混沌演练，零售运营系统韧性架构在线验证实践

avatar 华为云开发者联盟
cover

如何使用Python和Plotly绘制3D图形

avatar 华为云开发者联盟