Android sqlite 简单操作

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.db

SQLite version 3.5.9

Enter ".help" for instructions

sqlite> 

2.查看有那些表

sqlite> .schema

CREATE 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 video

3.查看表中的数据，以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|ui

2|/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|ui

3|/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|ui

select语句后别忘了加分号;

4.能输入SQL语句就能干很多很多事了~

5.帮助

sqlite>.help

6.退出 Ctrl+d

原文出处：http://forest606.blog.163.com/blog/static/134450089201062263432860/