Android-sqlite3 basic command operation, androidsqlite3

Android-sqlite3 basic command operation, androidsqlite3

There are not many databases used at ordinary times. Here we record the basic operations on the database directly under the shell terminal!

Not easy to write, reproduced please indicate the source: http://blog.csdn.net/jscese/article/details/40016701

I. concept:

Sqlite3 is a lightweight database used by android, which is small and convenient. It is used to manage various db files in the android system and can be installed in ubuntu.SqlitemanTo view the db file in the android system. The interface location in the Framework is/frameworks/base/core/java/android/database/sqlite/SQLiteDatabase. java.

Ii. shell usage:

I am using a shell terminal under ubuntu minicom. Taking the system setting database as an example, the directory is/data/com. android. providers. settings/databases/setting. db

Cd to the databases directory to 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 and a simple prompt.;"SemicolonEnd!

Sqlite3 *. dbOpen the database. Open the database if it exists. If it does not exist, create the database. save and create the database after modification.

Use. HelpView 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 offsqlite> .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 databasessqlite> 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 supported commands, which are commonly used:

. DatabaseDisplays the database information, including the location of the current database
. TablesOr, if the table name is not displayed
. SchemaCommand to view the SQL command used to create a data object;

. Mode csv|Column|Insert|Line|List|Tabs|TclChange output format

By defaultSelect * from systemView All data in the system table:

sqlite> select * from system;1|volume_music|152|volume_ring|53|volume_system|74|volume_voice|45|volume_alarm|66|volume_notification|57|volume_bluetooth_sco|7... 

The list is displayed, and the ID name values are separated by "| ". . Separator "X". X is the delimiter!

After using. mode culumn:

sqlite> .mode columnsqlite> 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  

Others are similar, just to change the output format.

1. CREATE command:

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

Parameters and attributes are specified during creation. There are three parameters and auto-increment parameters. For details about the data type:

NULL: The value is null.
INTEGER: The value is identified as an integer and can be stored as 1, 2, 3, 4, 5, 6, or 8 bytes in sequence based on the value size.
REAL: All values are floating values and are stored as 8-byte IEEE floating mark numbers.
TEXT: Text. value is a text string, stored using database encoding (TUTF-8, UTF-16BE or UTF-16-LE ).
BLOB: The value is BLOB data, and the input is stored without changing the format.

2. Insert data:

sqlite> .mode insertsqlite> 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');

For example, to insert a data entry into the system table:

insert into system values('45','sqlite','jscese');

Here, the data to be inserted must correspond to the number at the time of creation, otherwise it will report:

Error: table table_name has * 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 value. The table attributes include _ Id, name, value, You can see in the CREATE command!

4. delete data:

delete from system where value='jscese';

Delete the entire table: Drop table table_name

5. Update table data:

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

6. Operation Problems:

Enter without adding a semicolon after using the SQL command, and enter the input mode. Then add another; semicolon:

sqlite> select * from system   ...> ;

I directly use sqlite under minicom to identify the up and down direction keys and the back key!

Garbled characters such as ^ [A ^ H,

I have never tried what others give online ~ Http://ljhzzyx.blog.163.com/blog/static/3838031220102595026789/

The local terminal adb shell can recognize the rollback key.

Generally, it is used out of sqlite3.. Quit

Actually cannot exit...> the mode uses ctrl + D!

Android basic file operation commands

ADB (Android Debug Bridge)
Note: The following commands must have the root permission to be successfully executed.
Quickly start the dos window and execute adb:
1. Add the path of adb.exe to the system environment variable
2. Configure the shortcut key to start dos
Go to the C: \ WINDOWS \ system32directory and find cmd.exe.
Right-click the menu "send to"-> desktop shortcut.
Right-click "shortcut to cmd.exe"-> "properties"-> "shortcut" on the desktop.
-> Highlight the cursor "shortcut key"-> press the custom shortcut key (for example, Ctrl + Alt + Z)

In any case, press Ctrl + Alt + Z to start the dos window and run the adb command.

----------- View device connection status series -----------
Adb get-serialno get the device ID and serial number serialNumber
Adb devices queries the devices connected to the current computer (including simulators and mobile phones). The output format is [serialNumber] [state].
Adb get-state to view the current status of the simulator/facility.

Note:
Serial number [serialNumber] -- a string created by adb, which uses its own control port <type>-<leleport>
Uniquely identifies a simulator/device instance. Example of a serial number: emulator-5554

----------- Send command to device series -----------
Adb [-d |-e |-s <serialNumber>] <command>
-D. Send the command to the usb-connected device.
-E. Send the command to the simulator device.
-S <serialNumber> sends the command to the specified device

For example, start the mobile phone device shell: adb-d shell.

Adb forward <local> <remote> Publish port. You can set any port number,
The request port from the host to the simulator or device. For example, adb forward tcp: 5555 tcp: 8000

Adb reboot restart mobile phone
Adb remount remounts system partitions as read/write partitions
Adb kill-server terminates the adb service process
Adb start-server restart adb service process
Adb root has root permission to restart adb Service
Adb wait-for-device: the command is reprinted in the adb CLI before the simulator/device connection.
Adb jdwp view available JDWP information of the specified facility.
You can use forward jdwp: <pid> port ing information to connect to the specified JDWP process. For example:
Adb forward tcp: 8000 jdwp: 472
Jdb-attach local host: 8000

The adb shell am command can start the application.

Adb shell input text <string> input text to the device (text box where the cursor is located)
Adb shell input keyevent <event_code> sends a key event to the device
For example:
When editing the text message, enter adb shell input text "hello" in the text box"
Send the key value back to Home: adb sh ...... the remaining full text>

How does android sqlite3 go to the console through the dos command for query?

Sqlite3 xxxxx. db
. Dump