Zero, preface

[1] Learning MySQL is as easy as learning to eat Hami melon by someone who knows how to eat watermelon.
[2] If you are not familiar with MySQL children's shoes, take a look at my article. SpringBoot-14-MyBatis Preview, MySQL Summary
[3]SQLite: Android built-in lightweight relational database
[4] It is strongly recommended that statements be written well in advance and tested on MySQL. Otherwise, a few semicolons and commas will be enough.
[5] Android has an API to support database operations, but it doesn't feel very flexible. If you are interested in it, you can find out for yourself.

Pit point

[1]: SQLite does not support DEFAULT and NOT NULL (although it is useless to connect)

[3]: INTO of INSERT INTO should be added (MySQL's bad habit, deserves it)

I. Creating a database

1.SQL Constant Class: SQLCon.java

/**
 * Author: Zhang Fengjie's <br/>
 * Time: 2018/8/26 0026:14:48 < br/>
 * E-mail: 1981462002@qq.com<br/>
 * Description: SQL Constant Class
 */
public class SQLCon {
    /**
     * Database name
     */
    public static String DB_NAME = "weapon";

    /**
     * Database version
     */
    public static int DB_VERSION = 1;

    /**
     * TABLE statement
     */
    public static final String CREATE_TABLE = "CREATE TABLE sword (\n" +
            "id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,\n" +
            "name VARCHAR(32) NOT NULL,\n" +
            "atk SMALLINT UNSIGNED NOT NULL,\n" +
            "hit SMALLINT UNSIGNED NOT NULL DEFAULT 20,\n" +
            "crit SMALLINT UNSIGNED NOT NULL DEFAULT 10\n" +
            ");";
    
}

2.SQLiteOpenHelper Use: My Database Auxiliary Class

/**
 * Author: Zhang Fengjie's <br/>
 * Time: 2018/8/26 0026:14:26 < br/>
 * E-mail: 1981462002@qq.com<br/>
 * Description: My database auxiliary class
 */
public class MySQLHelper extends SQLiteOpenHelper {

    private Context mContext;

    /**
     * Constructor
     *
     * @param context context
     */
    public MySQLHelper(Context context) {
        super(context, SQLCon.DB_NAME, null, SQLCon.DB_VERSION);
        mContext = context;
    }

    /**
     * Create a database and it will not execute if it exists
     *
     * @param db SQLite Database object
     */
    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(SQLCon.CREATE_TABLE);//Create table
    }

    /**
     * Database upgrade
     *
     * @param db         SQLite Database object
     * @param oldVersion Old version
     * @param newVersion new version
     */
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        
    }
}

3. Use where necessary:

MySQLHelper mySQLHelper = new MySQLHelper(this);//Creating Auxiliary Objects
mySQLHelper.getWritableDatabase();//Getting Writable Database Objects

//GetReadable Database () and getWritable Database ()
//Both methods can create or open an existing database and return an object that can read and write to the database.
//getWritableDatabase() exception when disk space is full

2. Delete tables when upgrading database

1.SQL Constant Class, change the database version to 2: SQLCon.java

    /**
     * Database version
     */
    public static int DB_VERSION = 2;
    
    /**
     * Delete table statements
     */
    public static final String DROP_TABLE = "DROP TABLE sword";

2.com.toly1994.si_sqlite.MySQLHelper#onUpgrade

db.execSQL(SQLCon.DROP_TABLE);
L.d(oldVersion+":"+newVersion+L.l());//1:2

3. Use where needed

MySQLHelper mySQLHelper2 = new MySQLHelper(this);//Creating Auxiliary Objects
mySQLHelper2.getWritableDatabase();//Getting Writable Database Objects

Common operation of database (CRUD):

1. Insert data (C)

    /**
     * Insert statement
     */
    public static final String INSERT = "INSERT INTO sword(id,name,atk,hit,crit) VALUES" +
            "(1,'Trace',7000,800,999)," +
            "(2,'Twilight',100,1000,10000)," +
            "(3,'Wind jump',9000,10,255);";

mDb = new MySQLHelper(this).getWritableDatabase();
mDb.execSQL(SQLCon.INSERT);

2. Delete data

    /**
     * Delete data
     */
    public static final String DELETE = "DELETE FROM sword WHERE id=1;";

mDb = new MySQLHelper(this).getWritableDatabase();
mDb.execSQL(SQLCon.DELETE);

3. Modifying data

    /**
     * Modifying data
     */
    public static final String UPDATE = "UPDATE sword SET hit=hit+1;";

mDb = new MySQLHelper(this).getWritableDatabase();
mDb.execSQL(SQLCon.UPDATE);

4. Query data

1. Query

Cursor cursor = mDb.rawQuery("SELECT * FROM sword", null);
while (cursor.moveToNext()) {
    String id = cursor.getString(cursor.getColumnIndex("id"));
    String name = cursor.getString(cursor.getColumnIndex("name"));
    String atk = cursor.getString(cursor.getColumnIndex("atk"));
    String hit = cursor.getString(cursor.getColumnIndex("hit"));
    String crit = cursor.getString(cursor.getColumnIndex("crit"));
    System.out.println(id + "---" + name + "---" + atk + "---" + hit + "---" + crit);
}
    //2 - Twilight - 100 - 1001 - 10000
    //3 - Wind leap - 9000 - 11 - 255
cursor.close();//Close cursor

2. Query one:? Is a placeholder, and the latter String array corresponds to the location of the placeholder, which can be more than one.

 Cursor cursor2 = mDb.rawQuery("SELECT * FROM sword WHERE id = ?", new String[]{"2"});
while (cursor2.moveToNext()) {
    String id = cursor2.getString(cursor2.getColumnIndex("id"));
    String name = cursor2.getString(cursor2.getColumnIndex("name"));
    String atk = cursor2.getString(cursor2.getColumnIndex("atk"));
    String hit = cursor2.getString(cursor2.getColumnIndex("hit"));
    String crit = cursor2.getString(cursor2.getColumnIndex("crit"));
    System.out.println(id + "---" + name + "---" + atk + "---" + hit + "---" + crit);
}
//2 - Twilight - 100 - 1001 - 10000
cursor2.close();//Close cursor

IV. Other Knowledge Points

1. Primary key self-growth and field defaults

/**
 * TABLE statement
 */
public static final String CREATE_TABLE = "CREATE TABLE sword (\n" +
        "_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,\n" +
        "name VARCHAR(32) NOT NULL,\n" +
        "atk SMALLINT UNSIGNED DEFAULT 1000,\n" +
        "hit SMALLINT UNSIGNED DEFAULT 20,\n" +
        "crit SMALLINT UNSIGNED DEFAULT 10\n" +
        ");";

2. Common operations can be encapsulated in a dao class

/**
 * Author: Zhang Fengjie's <br/>
 * Time: 2018/8/26 0026:17:50 < br/>
 * E-mail: 1981462002@qq.com<br/>
 * Description: Database Operations Class
 */
public class SwordDao {
    private static SwordDao sSwordDao;
    private SQLiteDatabase db;

    /**
     * Privatization constructor
     */
    private SwordDao() {
    }

    /**
     * Single-case pattern acquisition SwordDao
     *
     * @return SwordDao
     */
    public static SwordDao get() {
        if (sSwordDao == null) {
            synchronized (SwordDao.class) {
                if (sSwordDao == null) {
                    sSwordDao = new SwordDao();
                }
            }
        }
        return sSwordDao;
    }

    public SwordDao attach(SQLiteDatabase db) {
        this.db = db;
        return this;
    }

    /**
     * Query all
     * @return
     */
    public List<Sword> findAll() {
        Cursor cursor = db.rawQuery("SELECT * FROM sword", null);
        List<Sword> swords = new ArrayList<>();
        while (cursor.moveToNext()) {
            String tempId = cursor.getString(cursor.getColumnIndex("_id"));
            String name = cursor.getString(cursor.getColumnIndex("name"));
            String tempAtk = cursor.getString(cursor.getColumnIndex("atk"));
            String tempHit = cursor.getString(cursor.getColumnIndex("hit"));
            String tempCrit = cursor.getString(cursor.getColumnIndex("crit"));

            int id = tempId == null ? -1 : Integer.parseInt(tempId);
            int atk = tempAtk == null ? -1 : Integer.parseInt(tempAtk);
            int hit = tempHit == null ? -1 : Integer.parseInt(tempHit);
            int crit = tempCrit == null ? -1 : Integer.parseInt(tempCrit);

            Sword sword = new Sword(name, atk, hit, crit);
            sword.setId(id);
            swords.add(sword);
        }
        cursor.close();//Close cursor
        return swords;
    }
    
    /**
     * insert
     * @param sword
     */
    public void insert(Sword sword) {
        db.execSQL("INSERT INTO sword(name,atk,hit,crit) VALUES(?,?,?,?)",
                new String[]{
                        sword.getName(), sword.getAtk() + "", sword.getHit() + "", sword.getCrit() + ""});
    }

    /**
     * Insert a sword name, other defaults
     *
     * @param name Name
     */
    public void insert(String name) {
        db.execSQL("INSERT INTO sword(name) VALUES(?)",
                new String[]{name});
    }
}

Postscript: Jasper Specification

1. The Growth Record and Corrigendum List of this Paper

Project source code	date	Remarks
V0.1-- no	2018-8-26	1-SI--Android SQLite Basic Use Guide
V0.2-- no	2018-10-23	Adding other knowledge points

2. More about me

Pen name	QQ	WeChat	hobby
Zhang Fengjie	1981462002	zdl1994328	language
My github	My short book	My CSDN	Personal website

3. statement

1. This article was originally created by Zhang Fengjie and reproduced by him.
2. Welcome all programming enthusiasts to communicate with each other
3. Individual ability is limited. If there is something wrong, you are welcome to criticize and testify. You must correct it modestly.
4 - Seeing this, I would like to thank you for your love and support.