Android sqlite 簡單操作
2010-07-22 18:34:32| 分類：
android |字型大小 訂閱
sqlite3能夠直接查看資料庫的內容，有時方便調試，首先使用adb或者串口串連手機
1.開啟資料庫1)如果不知道資料庫的名稱，可以先尋找/ # find / -name *.db/data/data/com.cooliris.media/databases/picasa.db/data/data/com.android.phone/databases/num_addr.db/data/data/com.android.providers.media/databases/external-c4a1ebc0.db/data/data/com.android.providers.media/databases/internal.db/data/system/accounts.db......2)開啟，就以internal.db為例/ # sqlite3 /data/data/com.android.providers.media/databases/internal.dbSQLite version 3.5.9Enter ".help" for instructionssqlite> 2.查看有那些表sqlite> .schemaCREATE TABLE album_art (album_id INTEGER PRIMARY KEY,_data TEXT);CREATE TABLE albums (album_id INTEGER PRIMARY KEY,album_key TEXT NOT NULL UNIQUE,album TEXT NOT NULL);CREATE TABLE android_metadata (locale TEXT);CREATE TABLE artists (artist_id INTEGER PRIMARY KEY,artist_key TEXT NOT NULL UNIQUE,artist TEXT NOT NULL);CREATE TABLE audio_meta (_id INTEGER PRIMARY KEY,_data TEXT NOT NULL,_display_name TEXT,_size INTEGER,mime_type TEXT,date_added INTEGER,date_modified INTEGER,drm_type INTEGER,title TEXT NOT NULL,title_key TEXT NOT NULL,duration INTEGER,artist_id INTEGER,composer
TEXT,album_id INTEGER,track INTEGER,year INTEGER CHECK(year!=0),is_ringtone INTEGER,is_music INTEGER,is_alarm INTEGER,is_notification INTEGER, is_podcast INTEGER, bookmark INTEGER);CREATE TABLE images (_id INTEGER PRIMARY KEY,_data TEXT,_size INTEGER,_display_name TEXT,mime_type TEXT,title TEXT,date_added INTEGER,date_modified INTEGER,drm_type INTEGER,description TEXT,picasa_id TEXT,isprivate INTEGER,latitude DOUBLE,longitude DOUBLE,datetaken
INTEGER,orientation INTEGER,mini_thumb_magic INTEGER,bucket_id TEXT,bucket_display_name TEXT);CREATE TABLE thumbnails (_id INTEGER PRIMARY KEY,_data TEXT,image_id INTEGER,kind INTEGER,width INTEGER,height INTEGER);CREATE TABLE video (_id INTEGER PRIMARY KEY,_data TEXT NOT NULL,_display_name TEXT,_size INTEGER,mime_type TEXT,date_added INTEGER,date_modified INTEGER,drm_type INTEGER,title TEXT,duration INTEGER,artist TEXT,album TEXT,resolution TEXT,description TEXT,isprivate
INTEGER,tags TEXT,category TEXT,language TEXT,mini_thumb_data TEXT,latitude DOUBLE,longitude DOUBLE,datetaken INTEGER,mini_thumb_magic INTEGER, bucket_id TEXT, bucket_display_name TEXT, bookmark INTEGER);CREATE TABLE videothumbnails (_id INTEGER PRIMARY KEY,_data TEXT,video_id INTEGER,kind INTEGER,width INTEGER,height INTEGER);CREATE VIEW album_info AS SELECT audio.album_id AS _id, album, album_key, MIN(year) AS minyear, MAX(year) AS maxyear, artist, artist_id, artist_key, count(*) AS numsongs,album_art._data AS album_art FROM audio LEFT OUTER JOIN album_art ON audio.album_id=album_art.album_id
WHERE is_music=1 GROUP BY audio.album_id;CREATE VIEW artist_info AS SELECT artist_id AS _id, artist, artist_key, COUNT(DISTINCT album) AS number_of_albums, COUNT(*) AS number_of_tracks FROM audio WHERE is_music=1 GROUP BY artist_key;CREATE VIEW artists_albums_map AS SELECT DISTINCT artist_id, album_id FROM audio_meta;CREATE VIEW audio as SELECT * FROM audio_meta LEFT OUTER JOIN artists ON audio_meta.artist_id=artists.artist_id LEFT OUTER JOIN albums ON audio_meta.album_id=albums.album_id;CREATE VIEW search AS SELECT _id,'artist' AS mime_type,artist,NULL AS album,NULL AS title,artist AS text1,NULL AS text2,number_of_albums AS data1,number_of_tracks AS data2,artist_key AS match,'content://media/external/audio/artists/'||_id AS suggest_intent_data,1
AS grouporder FROM artist_info WHERE (artist!='<unknown>') UNION ALL SELECT _id,'album' AS mime_type,artist,album,NULL AS title,album AS text1,artist AS text2,NULL AS data1,NULL AS data2,artist_key||' '||album_key AS match,'content://media/external/audio/albums/'||_id
AS suggest_intent_data,2 AS grouporder FROM album_info WHERE (album!='<unknown>') UNION ALL SELECT searchhelpertitle._id AS _id,mime_type,artist,album,title,title AS text1,artist AS text2,NULL AS data1,NULL AS data2,artist_key||' '||album_key||' '||title_key
AS match,'content://media/external/audio/media/'||searchhelpertitle._id AS suggest_intent_data,3 AS grouporder FROM searchhelpertitle WHERE (title != '');CREATE VIEW searchhelpertitle AS SELECT * FROM audio ORDER BY title_key;CREATE INDEX albumkey_index on albums(album_key);CREATE INDEX artistkey_index on artists(artist_key);CREATE INDEX image_id_index on thumbnails(image_id);CREATE INDEX mini_thumb_magic_index on images(mini_thumb_magic);CREATE INDEX sort_index on images(datetaken ASC, _id ASC);CREATE INDEX titlekey_index on audio_meta(title_key);CREATE INDEX video_id_index on videothumbnails(video_id);CREATE TRIGGER audio_delete INSTEAD OF DELETE ON audio BEGIN DELETE from audio_meta where _id=old._id;DELETE from audio_playlists_map where audio_id=old._id;DELETE from audio_genres_map where audio_id=old._id;END;CREATE TRIGGER images_cleanup DELETE ON images BEGIN DELETE FROM thumbnails WHERE image_id = old._id;SELECT _DELETE_FILE(old._data);END;CREATE TRIGGER thumbnails_cleanup DELETE ON thumbnails BEGIN SELECT _DELETE_FILE(old._data);END;CREATE TRIGGER video_cleanup DELETE ON video BEGIN SELECT _DELETE_FILE(old._data);END;CREATE TRIGGER videothumbnails_cleanup DELETE ON videothumbnails BEGIN SELECT _DELETE_FILE(old._data);END;sqlite> 以上以CREATE TABLE開頭的都是表了，例如 CREATE TABLE video3.查看錶中的資料，以audio為例sqlite> select * from audio;1|/system/media/audio/ui/KeypressSpacebar.ogg|KeypressSpacebar.ogg|7392|application/ogg|1279692229|1279624670|0|KeypressSpacebar|279|1||1|0||0|1|0|0|0||1&&&&?|Copyright 2009 Android Open Source Project|178638153|ui2|/system/media/audio/ui/KeypressReturn.wav|KeypressReturn.wav|64320|audio/wav|1279692230|1279624670|0|KeypressReturn|363|2||1|0||0|1|0|0|0||2||<unknown>|178638153|ui3|/system/media/audio/ui/camera_click.wav|camera_click.wav|13030|audio/wav|1279692230|1279624670|0|camera_click?|139|2||1|0||0|1|0|0|0||2||<unknown>|178638153|uiselect語句後別忘了加分號;4.能輸入SQL語句就能幹很多很多事了~5.協助sqlite>.help6.退出 Ctrl+d
原文出處：http://forest606.blog.163.com/blog/static/134450089201062263432860/
