{"id":882,"date":"2015-07-13T17:47:15","date_gmt":"2015-07-13T09:47:15","guid":{"rendered":"https:\/\/ai.zhousir.top\/?p=882"},"modified":"2026-08-18T12:58:42","modified_gmt":"2026-08-18T04:58:42","slug":"sqlite%e6%95%b0%e6%8d%ae%e5%ba%93","status":"publish","type":"post","link":"https:\/\/ai.zhousir.top\/?p=882","title":{"rendered":"SQLite\u6570\u636e\u5e93"},"content":{"rendered":"
\n
\n

\u6570\u636e\u5e93\u662f\u5b58\u6570\u636e\u7684\u4ed3\u5e93\u3002\u901a\u8baf\u5f55\u3001\u77ed\u4fe1\u3001\u65e5\u5386\u3001\u90ae\u7bb1\u90fd\u9760\u672c\u5730\u5e93\u3002Android \u5185\u7f6e\u7684\u662f\u8f7b\u91cf\u5173\u7cfb\u5e93 SQLite<\/strong>\uff0c\u8d44\u6e90\u5360\u7528\u5f88\u5c0f\uff0c\u4e0d\u5fc5\u50cf PC \u90a3\u6837\u5148\u88c5\u4e00\u5957\u6570\u636e\u5e93\u8f6f\u4ef6\u3002\u4e0b\u9762\u4ece\u547d\u4ee4\u884c\u3001\u53ef\u89c6\u5316\u5ba2\u6237\u7aef\uff0c\u518d\u5230 SQLiteOpenHelper<\/code> \u589e\u5220\u6539\u67e5\u3002<\/p>\n<\/blockquote>\n

<\/p>\n

\n

\u4e00\u3001\u547d\u4ee4\u884c\u64cd\u4f5c SQLite<\/span><\/h2>\n

SDK \u7684 tools \u76ee\u5f55\u6709 sqlite3.exe<\/code>\u3002\u7a0b\u5e8f\u751f\u6210\u7684 *.db<\/code> \u4e00\u822c\u5728 \/data\/data\/\u5305\u540d\/databases\/*.db<\/code>\u3002<\/p>\n

    \n
  1. \n
    cmd \u91cc\u8f93\u5165 adb shell<\/code>\uff0c\u8fdb\u5165 shell<\/section>\n<\/li>\n
  2. \n
    cd \/data\/data\/\u5305\u540d\/databases\/<\/code>\uff0c\u8fdb\u5165\u5e94\u7528\u5e93\u76ee\u5f55<\/section>\n<\/li>\n
  3. \n
    sqlite3 *.db<\/code>\uff0c\u6253\u5f00\u5e93\uff0c\u7528\u719f\u6089\u7684 SQL \u589e\u5220\u6539\u67e5<\/section>\n<\/li>\n
  4. \n
    .tables<\/code> \u663e\u793a\u6240\u6709\u8868<\/section>\n<\/li>\n
  5. \n
    select * from tablename<\/code> \u67e5\u6570\u636e<\/section>\n<\/li>\n<\/ol>\n
    \n

    \u4e8c\u3001\u53ef\u89c6\u5316\u64cd\u4f5c SQLite<\/span><\/h2>\n

    \u547d\u4ee4\u884c\u4e0d\u719f\u53ef\u4ee5\u7528\u5ba2\u6237\u7aef\uff0c\u4f8b\u5982 sqlitebrowser<\/strong>\uff1a\u4ece IDE \u6587\u4ef6\u7ba1\u7406\u5bfc\u51fa *.db<\/code>\uff0c\u7528\u8f6f\u4ef6\u6253\u5f00\u5373\u53ef\u3002<\/p>\n

    \n

    \u4e09\u3001\u4ee3\u7801\u64cd\u4f5c\uff1aSQLiteDatabase \u4e0e OpenHelper<\/span><\/h2>\n

    \u6838\u5fc3\u7c7b SQLiteDatabase<\/h3>\n

    \u62ff\u5230 SQLiteDatabase<\/code> \u5b9e\u4f8b\u5c31\u80fd\u6267\u884c SQL\u3002\u6700\u7b80\u5199\u6cd5\uff1a<\/p>\n

    SQLiteDatabase db = openOrCreateDatabase(\"scxh1502.db\", MODE_PRIVATE, null);\ndb.execSQL(\"create table student (id INTEGER NOT NULL,name TEXT,PRIMARY KEY(id))\");\ndb.execSQL(\"insert into student (id,name) values (1001,'\u5f20\u4e09')\");<\/code><\/pre>\n
    \u5de5\u7a0b\u91cc\u66f4\u5e38\u89c1\u7684\u662f\u7ee7\u627f SQLiteOpenHelper<\/code>\uff0c\u7528\u5b83\u7ba1\u7406\u521b\u5efa\u548c\u7248\u672c\u5347\u7ea7\uff0c\u5b9e\u73b0 onCreate<\/code> \u548c onUpgrade<\/code>\u3002<\/p>\n
    \u6570\u636e\u5e93\u8f85\u52a9\u7c7b SQLiteOpenHelper<\/h3>\n
    public class DataBaseHelper extends SQLiteOpenHelper {\n    private final static String DATABASE_NAME = \"android1601.db\"; \/\/\u6570\u636e\u5e93\u540d\n    private final static int DATABASE_VERSION = 3; \/\/\u6570\u636e\u5e93\u7248\u672c\u53f7\n    private static String createUserTabelSQL = \"create table \" + DataColumn.UserTable.TABLE_NAME_USER\n            + \"(\"\n            + DataColumn.UserTable.COLUMN_NAME_ID + \" Integer NOT NULL PRIMARY KEY AUTOINCREMENT,\"\n            + DataColumn.UserTable.COLUMN_NAME_NAME + \" varchar(20),\"\n            + DataColumn.UserTable.COLUMN_NAME_PASSWORD + \" varchar(6) \"\n            + \")\";\n    private static String alterUserAddSexSQL = \"Alter table \" + DataColumn.UserTable.TABLE_NAME_USER\n            + \" add column \" + DataColumn.UserTable.COLUMN_NAME_SEX + \" varchar(2)\";\n    private static String alterUserAddNumberSQL = \"Alter table \" + DataColumn.UserTable.TABLE_NAME_USER\n            + \" add column \" + DataColumn.UserTable.COLUMN_NAME_NUMBER + \" Integer\";\n\n    private static String createStudentSQL = \"CREATE TABLE \"+ DataColumn.StudentTable.TABLE_NAME_SUTDENT\n            + \"(\"\n            + DataColumn.StudentTable.COLUMN_NAME_ID + \" Integer NOT NULL PRIMARY KEY AUTOINCREMENT,\"\n            + DataColumn.StudentTable.COLUMN_NAME_NAME + \" varchar(50), \"\n            + DataColumn.StudentTable.COLUMN_NAME_AGE + \" varchar(3) \"\n            +\")\";\n\n    public DataBaseHelper(Context context) {\n        super(context, DATABASE_NAME, null, DATABASE_VERSION);\n        Logs.e(\"DataBaseHelper \u6784\u9020\u65b9\u6cd5\");\n    }\n\n    @Override\n    public void onCreate(SQLiteDatabase db) {\n        db.execSQL(createUserTabelSQL);\n        Logs.e(\"onCreate \u65b9\u6cd5\");\n    }\n\n    \/**\n     * \u7248\u672c\u53f7\u53d8\u5316\u65f6\u6267\u884c\u3002newVersion \u5fc5\u987b\u5927\u4e8e oldVersion\uff0c\u6267\u884c\u5b8c\u540e oldVersion = newVersion\n     *\/\n    @Override\n    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {\n        Logs.e(\"onUpgrade \u65b9\u6cd5 oldVersion: \" + oldVersion + \" ,newVersion:  \" + newVersion);\n\n        switch (oldVersion) {\n            case 1: \/\/\u65e7\u7248\u672c 1\uff1a\u52a0 sex \u5b57\u6bb5\n                db.execSQL(alterUserAddSexSQL);\n            case 2: \/\/\u65e7\u7248\u672c 2\uff1a\u52a0 number\uff0c\u5e76\u65b0\u5efa\u5b66\u751f\u8868\uff08\u6ce8\u610f case \u6545\u610f\u4e0d break\uff0c\u4fbf\u4e8e\u8fde\u5347\uff09\n                db.execSQL(alterUserAddNumberSQL);\n                db.execSQL(createStudentSQL);\n        }\n    }\n}<\/code><\/pre>\n
    public class DataColumn {\n    public static class UserTable implements BaseColumns{\n        public static final String TABLE_NAME_USER = \"user\";\n        public static final String COLUMN_NAME_NAME = \"name\";\n        public static final String COLUMN_NAME_PASSWORD = \"password\";\n        public static final String COLUMN_NAME_SEX = \"sex\";\n        public static final String COLUMN_NAME_NUMBER = \"number\";\n    }\n\n    public static class StudentTable{\n        public static final String TABLE_NAME_SUTDENT = \"student\";\n        public static final String COLUMN_NAME_ID = \"_id\";\n        public static final String COLUMN_NAME_NAME = \"name\";\n        public static final String COLUMN_NAME_AGE = \"age\";\n    }\n}<\/code><\/pre>\n
    getWritableDatabase()<\/code> \u4e0e getReadableDatabase()<\/code> \u90fd\u80fd\u62ff\u5230 SQLiteDatabase<\/code>\u3002\u540e\u8005\u4e0d\u662f<\/strong>\u4e00\u4e0a\u6765\u5c31\u53ea\u8bfb\uff1a\u5b83\u5148\u8d70\u53ef\u5199\u6253\u5f00\uff0c\u5931\u8d25\uff08\u4f8b\u5982\u78c1\u76d8\u6ee1\uff09\u518d\u5c1d\u8bd5\u53ea\u8bfb\u3002\u78c1\u76d8\u6ee1\u65f6 getWritableDatabase()<\/code> \u4f1a\u76f4\u63a5\u51fa\u9519\u3002<\/p>\n
    \n
    \u56db\u3001\u521b\u5efa\u5e93\u4e0e\u589e\u5220\u6539\u67e5<\/span><\/h2>\n
    1. \u521b\u5efa \/ \u6253\u5f00\u6570\u636e\u5e93<\/h3>\n
    \u8c03\u7528 Helper \u7684 getReadableDatabase()<\/code>\uff0c\u9996\u6b21\u4f1a\u56de\u8c03 onCreate()<\/code>\u3002<\/p>\n
    @Override\nprotected void onCreate(Bundle savedInstanceState) {\n    super.onCreate(savedInstanceState);\n\n    DatabaseHelper dbHelper = new DatabaseHelper(this); \/\/ Activity \u91cc\u624d\u7528 this\n    SQLiteDatabase db = dbHelper.getReadableDatabase();\n}<\/code><\/pre>\n
    \u5e38\u7528 API\uff1a<\/p>\n
    (int) delete(String table, String whereClause, String[] whereArgs)\n(long) insert(String table, String nullColumnHack, ContentValues values)\n(int) update(String table, ContentValues values, String whereClause, String[] whereArgs)\n(void) execSQL(String sql)\n(void) close()\n(Cursor) query(String table, String[] columns, String selection, String[] selectionArgs,\n               String groupBy, String having, String orderBy, String limit)\n(Cursor) rawQuery(String sql, String[] selectionArgs) \/\/ \u9884\u7f6e SQL\uff0c\u5229\u4e8e\u9632\u6ce8\u5165<\/code><\/pre>\n
    \u589e\u5220\u6539\u67e5\u5404\u6709\u4e24\u6761\u8def\uff1aAPI \u4fbf\u6377\u65b9\u6cd5\uff0c\u6216 execSQL<\/code> \/ rawQuery<\/code>\u3002<\/p>\n
    2. \u6dfb\u52a0<\/h3>\n
    insert\uff1a<\/p>\n
    ContentValues cv = new ContentValues();\ncv.put(\"username\",\"Jack Johnson\");\ncv.put(\"password\",\"iLovePopMusic\");\ndb.insert(\"user\",null,cv);<\/code><\/pre>\n
    execSQL\uff1a<\/p>\n
    String sql = \"insert into user(username,password) values ('Jack Johnson','iLovePopMusic')\";\ndb.execSQL(sql);<\/code><\/pre>\n
    3. \u5220\u9664<\/h3>\n
    String whereClause = \"username=? and sex=?\";\nString[] whereArgs = {\"Jack Johnson\",\"\u7537\"};\ndb.delete(\"user\",whereClause,whereArgs);<\/code><\/pre>\n
    String sql = \"delete from user where username='Jack Johnson'\";\ndb.execSQL(sql);<\/code><\/pre>\n
    4. \u4fee\u6539<\/h3>\n
    ContentValues cv = new ContentValues();\ncv.put(\"password\",\"iHatePopMusic\");\nString whereClause = \"username=?\";\nString[] whereArgs = {\"Jack Johnson\"};\ndb.update(\"user\",cv,whereClause,whereArgs);<\/code><\/pre>\n
    String sql = \"update [user] set password = 'iHatePopMusic' where username='Jack Johnson'\";\ndb.execSQL(sql);<\/code><\/pre>\n
    5. \u67e5\u8be2<\/h3>\n
    query<\/code> \u53c2\u6570\uff1atable \u8868\u540d\uff0ccolumns \u5217\u6570\u7ec4\uff0cselection \u76f8\u5f53\u4e8e where\uff0cselectionArgs \u6761\u4ef6\u53c2\u6570\uff0cgroupBy \/ having \/ orderBy \/ limit\uff0c\u8fd4\u56de Cursor<\/code>\uff08\u7c7b\u4f3c ResultSet\uff09\u3002<\/p>\n\n\n\n\n\n\n\n\n\n\n
    Cursor \u65b9\u6cd5<\/th>\n\u4f5c\u7528<\/th>\n<\/tr>\n<\/thead>\n
    getCount()<\/code><\/td>\n\u603b\u8bb0\u5f55\u6761\u6570<\/td>\n<\/tr>\n
    isFirst()<\/code> \/ isLast()<\/code><\/td>\n\u662f\u5426\u7b2c\u4e00\u6761 \/ \u6700\u540e\u4e00\u6761<\/td>\n<\/tr>\n
    moveToFirst()<\/code> \/ moveToLast()<\/code><\/td>\n\u79fb\u5230\u9996 \/ \u672b<\/td>\n<\/tr>\n
    move(int offset)<\/code><\/td>\n\u76f8\u5bf9\u79fb\u52a8 offset<\/td>\n<\/tr>\n
    moveToNext()<\/code> \/ moveToPrevious()<\/code><\/td>\n\u4e0b\u4e00\u6761 \/ \u4e0a\u4e00\u6761<\/td>\n<\/tr>\n
    getColumnIndex(String)<\/code><\/td>\n\u5217\u540d \u2192 \u5217\u4e0b\u6807<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    \u65b9\u6cd5\u4e00\uff08\u6309\u6761\u6570\u5faa\u73af + move\uff09\uff1a<\/p>\n
    Cursor c = db.query(\"user\",null,null,null,null,null,null);\nif(c.moveToFirst()){\n    for(int i=0;i<c.getCount();i++){\n        c.move(i);\n        String username = c.getString(c.getColumnIndex(\"username\"));\n        String password = c.getString(c.getColumnIndex(\"password\"));\n    }\n}<\/code><\/pre>\n
    \u65b9\u6cd5\u4e8c\uff08while moveToNext\uff0c\u66f4\u5e38\u7528\uff09\uff1a<\/p>\n
    Cursor c = db.query(\"user\",null,null,null,null,null,null);\nwhile(c.moveToNext()){\n  int id = c.getInt(c.getColumnIndex(\"id\"));\n  String name = c.getString(c.getColumnIndex(\"name\"));\n  String number = c.getString(c.getColumnIndex(\"number\"));\n  Logs.v(\"id :\"+id + \" name :\"+name+ \" number :\"+number);\n}<\/code><\/pre>\n
    rawQuery \u5e26\u53c2\u6570\uff1a<\/p>\n
    Cursor c = db.rawQuery(\"select * from user where username=?\", new String[]{\"Jack Johnson\"});\nif(c.moveToFirst()) {\n    String password = c.getString(c.getColumnIndex(\"password\"));\n}<\/code><\/pre>\n
    \n
    \u4e00\u53e5\u8bdd\u603b\u7ed3\uff1aAndroid \u7528 SQLite\uff1b\u65e5\u5e38\u8d70 SQLiteOpenHelper \u7ba1\u521b\u5efa\u548c\u5347\u7ea7\uff0c\u589e\u5220\u6539\u7528 ContentValues API \u6216 execSQL\uff0c\u67e5\u8be2\u7528 query \/ rawQuery \u62ff Cursor\u3002<\/strong><\/p>\n<\/blockquote>\n<\/section>\n","protected":false},"excerpt":{"rendered":"
    \u6570\u636e\u5e93\u662f\u5b58\u6570\u636e\u7684\u4ed3\u5e93\u3002\u901a\u8baf\u5f55\u3001\u77ed\u4fe1\u3001\u65e5\u5386\u3001\u90ae\u7bb1\u90fd\u9760\u672c\u5730\u5e93\u3002Android \u5185\u7f6e\u7684\u662f\u8f7b\u91cf\u5173\u7cfb\u5e93 SQLite\uff0c\u8d44 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[7],"tags":[198],"_links":{"self":[{"href":"https:\/\/ai.zhousir.top\/index.php?rest_route=\/wp\/v2\/posts\/882"}],"collection":[{"href":"https:\/\/ai.zhousir.top\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ai.zhousir.top\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ai.zhousir.top\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/ai.zhousir.top\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=882"}],"version-history":[{"count":3,"href":"https:\/\/ai.zhousir.top\/index.php?rest_route=\/wp\/v2\/posts\/882\/revisions"}],"predecessor-version":[{"id":1632,"href":"https:\/\/ai.zhousir.top\/index.php?rest_route=\/wp\/v2\/posts\/882\/revisions\/1632"}],"wp:attachment":[{"href":"https:\/\/ai.zhousir.top\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=882"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ai.zhousir.top\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=882"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ai.zhousir.top\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=882"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}