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 handy for managing various DB files on Android, and able to install Sqliteman in Ubuntu to view DB files on Android Interface location in the framework:/frameworks/base/core/java/android/database/sqlite/sqlitedatabase.java

Two. Shell uses:

I'm using the shell terminal under Ubuntu minicom. Take the system setting database for example, the folder is:/data/data/com.android.providers.settings/databases/setting.db

CD to the Databases folder. Open 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> 

Can see the SQL version number, as well as a simple hint, the use of SQL statements with the ";" semicolon end!

sqlite3 *.db Open the database, assuming that there is an open operation, if it does not exist, it is created, and can be saved after the change.

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



	

Be able to see the commands that are supported, which are often used:

. database displays information about databases, including the location of the current database
The . Tables or. table Display table name does not appear without a table
The . Schema command is able to 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 :

Can see the form of the list is displayed, and the ID name value is separated by "|", the cut symbol is . Separator "X"To define. X is the cutting character!

After using the. Mode culumn:

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

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

1. Create the directive:

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  

When creating a specified number of parameters and attributes, here are three, and self-increment, about the data type:

NULL: This value is a null value
Integer: The value is identified as an integer and 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 the BLOB data, how the input is stored, does not change the format.

2. Insert Data:

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);

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

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');

Insert the data here to follow the creation of the amount of time, otherwise it will be reported:

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

3. Query the specified data:

sqlite> select * from system where name='sqlite';
INSERT INTO table VALUES(45,'sqlite','jscese'); 

Filter queries by 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 make up one more time. A semicolon can:

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 this garbled.

people on the Internet. I haven't tried ~ 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