Android--sqlite3 Basic Command operation

Usually use the database where not many, here to record the shell terminal directly to the basic operation of the DB!

Writing is not easy, reproduced please specify the source: http://blog.csdn.net/jscese/article/details/40016701

I. Concept:

Sqlite3 is a lightweight database for Android, small and easy to manage the various DB files on Android, and can be installed Sqliteman in Ubuntu to view DB files in Android system. Interface location in the framework:/frameworks/base/core/java/android/database/sqlite/sqlitedatabase.java

Two. Shell uses:

I am using Ubuntu minicom Shell Terminal, the system setting database for example, the directory is:/data/data/com.android.providers.settings/databases/ Setting.db

CD to databases directory, open the database file:sqlite3 setting.db

SQLite version 3.7.11 2012-03-20 11:35:50                                                                                                
Enter ".help" for instructions                                                                                                           
Enter SQL statements terminated with a ";"                                                                                               
sqlite> 

You can see the SQL version as well as a simple hint that you need to end with a ";" semicolon when using SQL statements!

sqlite3 *.db Open the database, open the operation if it exists, create it if it does not exist, and save the creation after the modification.

Use the . Help to view assistance:

.backup ?DB? FILE      Backup DB (default "main") to FILE
.bail ON|OFF           Stop after hitting an error.  Default OFF
.databases             List names and files of attached databases
.dump ?TABLE? ...      Dump the database in an SQL text format
                         If TABLE specified, only dump tables matching
                         LIKE pattern TABLE.
.echo ON|OFF           Turn command echo on or off
.exit                  Exit this program
.explain ?ON|OFF?      Turn output mode suitable for EXPLAIN on or off.
                         With no args, it turns EXPLAIN on.
.header(s) ON|OFF      Turn display of headers on or off
.help                  Show this message
.import FILE TABLE     Import data from FILE into TABLE
.indices ?TABLE?       Show names of all indices
                         If TABLE specified, only show indices for tables
                         matching LIKE pattern TABLE.
.log FILE|off          Turn logging on or off.  FILE can be stderr/stdout
.mode MODE ?TABLE?     Set output mode where MODE is one of:
                         csv      Comma-separated values
                         column   Left-aligned columns.  (See .width)
                         html     HTML <table> code
                         insert   SQL insert statements for TABLE
                         line     One value per line
                         list     Values delimited by .separator string
                         tabs     Tab-separated values
                         tcl      TCL list elements
.nullvalue STRING      Print STRING in place of NULL values
.output FILENAME       Send output to FILENAME
.output stdout         Send output to the screen
.prompt MAIN CONTINUE  Replace the standard prompts
.quit                  Exit this program
.read FILENAME         Execute SQL in FILENAME
.restore ?DB? FILE     Restore content of DB (default "main") from FILE
.schema ?TABLE?        Show the CREATE statements
                         If TABLE specified, only show tables matching
                         LIKE pattern TABLE.
.separator STRING      Change separator used by output mode and .import
.show                  Show the current values for various settings
.stats ON|OFF          Turn stats on or off
.tables ?TABLE?        List names of tables
                         If TABLE specified, only list tables matching
                         LIKE pattern TABLE.
.timeout MS            Try opening locked tables for MS milliseconds
.vfsname ?AUX?         Print the name of the VFS stack
.width NUM1 NUM2 ...   Set column widths for "column" mode
.timer ON|OFF          Turn the CPU timer measurement on or off
sqlite> .help
.backup ?DB? FILE      Backup DB (default "main") to FILE
.bail ON|OFF           Stop after hitting an error.  Default OFF
.databases             List names and files of attached databases

sqlite> 
sqlite> 
sqlite> .help
.backup ?DB? FILE      Backup DB (default "main") to FILE
.bail ON|OFF           Stop after hitting an error.  Default OFF
.databases             List names and files of attached databases
.dump ?TABLE? ...      Dump the database in an SQL text format
                         If TABLE specified, only dump tables matching
                         LIKE pattern TABLE.
.echo ON|OFF           Turn command echo on or off
.exit                  Exit this program
.explain ?ON|OFF?      Turn output mode suitable for EXPLAIN on or off.
                         With no args, it turns EXPLAIN on.
.header(s) ON|OFF      Turn display of headers on or off
.help                  Show this message
.import FILE TABLE     Import data from FILE into TABLE
.indices ?TABLE?       Show names of all indices
                         If TABLE specified, only show indices for tables
                         matching LIKE pattern TABLE.
.log FILE|off          Turn logging on or off.  FILE can be stderr/stdout
.mode MODE ?TABLE?     Set output mode where MODE is one of:
                         csv      Comma-separated values
                         column   Left-aligned columns.  (See .width)
                         html     HTML <table> code
                         insert   SQL insert statements for TABLE
                         line     One value per line
                         list     Values delimited by .separator string
                         tabs     Tab-separated values
                         tcl      TCL list elements
.nullvalue STRING      Print STRING in place of NULL values
.output FILENAME       Send output to FILENAME
.output stdout         Send output to the screen
.prompt MAIN CONTINUE  Replace the standard prompts
.quit                  Exit this program
.read FILENAME         Execute SQL in FILENAME
.restore ?DB? FILE     Restore content of DB (default "main") from FILE
.schema ?TABLE?        Show the CREATE statements
                         If TABLE specified, only show tables matching
                         LIKE pattern TABLE.
.separator STRING      Change separator used by output mode and .import
.show                  Show the current values for various settings
.stats ON|OFF          Turn stats on or off
.tables ?TABLE?        List names of tables
                         If TABLE specified, only list tables matching
                         LIKE pattern TABLE.
.timeout MS            Try opening locked tables for MS milliseconds
.vfsname ?AUX?         Print the name of the VFS stack
.width NUM1 NUM2 ...   Set column widths for "column" mode
.timer ON|OFF          Turn the CPU timer measurement on or off

You can see the commands that are supported, which are commonly used:

. database displays information about the databases, and contains the location of the current database
The . Tables or. table Display table name does not appear without a table
The . Schema command can view SQL commands when creating data objects;

. Mode csv| Column| Insert| Line | List| Tabs| tcl Change output format

By default, all data for the system table is viewed using the select * from System :

sqlite> select * from system;
1|volume_music|15
2|volume_ring|5
3|volume_system|7
4|volume_voice|4
5|volume_alarm|6
6|volume_notification|5
7|volume_bluetooth_sco|7
... 

You can see that it is displayed in the form of a list, and the ID name value is separated by "|", and the split symbol is . Separator "X"To define, X is the separator!

After using the. Mode culumn:

sqlite> .mode column
sqlite> select * from system;
1           volume_music  15        
2           volume_ring   5         
3           volume_syste  7         
4           volume_voice  4         
5           volume_alarm  6         
6           volume_notif  5         
7           volume_bluet  7  

Other similar, just to change the format of the output.

1. Create the directive:

sqlite> .schema system
CREATE TABLE system (_id INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT UNIQUE ON CONFLICT REPLACE,value TEXT);
CREATE INDEX systemIndex1 ON system (name);

When creating, specify parameters and attributes, there are three, and self-increment, about data types:

NULL: This value is a null value
Integer: The value is identified as an integer, which can be stored sequentially as 1,2,3,4,5,6,7,8 bytes, depending on the size of the value
REAL: All values are floating numeric values that are stored as 8-byte IEEE floating tag ordinals.
text: Literal. The value is a text string that is stored using the database encoding (TUTF-8, utf-16be, or Utf-16-le).
blob: The value is BLOB data, how to enter it on how to store it, without changing the format.

2. Insert Data:

sqlite> .mode insert
sqlite> select * from system;
INSERT INTO table VALUES(1,'volume_music','15');
INSERT INTO table VALUES(2,'volume_ring','5');
INSERT INTO table VALUES(3,'volume_system','7');
INSERT INTO table VALUES(4,'volume_voice','4');
INSERT INTO table VALUES(5,'volume_alarm','6');
INSERT INTO table VALUES(6,'volume_notification','5');
INSERT INTO table VALUES(7,'volume_bluetooth_sco','7');

If I want to insert a piece of data into the system table:

Insert into system values (' n ', ' SQLite ', ' Jscese ');

The data inserted here should correspond to the number created, otherwise it will be reported:

Error:table table_name have * columns but * values were supplied

3. Query the specified data:

Sqlite> SELECT * from the system where name= ' SQLite '; INSERT into table VALUES (' SQLite ', ' Jscese ');

Filter queries based on table type values, where the properties of the table are _id, Name,value, you can see it in the Create command!

4. Delete data:

Delete from system where value= ' Jscese ';

To delete an entire table: DROP TABLE table_name

5. Update the table data:

45|sqlite|jscese
sqlite> update system set name='sqlite3' where value='jscese';
sqlite> select * from system where value='jscese';
45|sqlite3|jscese

6. Operational issues:

After using the SQL instruction without a semicolon, enter the input mode, and then fill in one;

Sqlite> SELECT * from System   ...>;

I directly under the minicom use SQLite does not recognize the upper and lower left and right arrow keys, as well as the fallback key!

Appear ^[[a ^h such garbled,

I didn't try it on the Internet.http://ljhzzyx.blog.163.com/blog/static/3838031220102595026789/

The native terminal adb shell is the one that can recognize the fallback key

General exit Sqlite3 use . Quit

Really can not get out of ...> mode to use Ctrl+d strong retreat!

Android--sqlite3 Basic Command operation