Common android sqlite3 commands

$ ADB Shell

$ CD/data/COM. Android. providers. Media/databases // enter
Media
Database directory

$ Sqlite3 settings. DB // start sqlite3
SQLite version 3.6.22
Enter ". Help" for instructions // enter. Help to obtain help information
Enter SQL statements terminated with a ";" // SQL command with; as the Terminator

SQLite>. Tables // view the current table

Android_metadata bookmarks System
Bluetooth_devices secure

SQLite>. headers on // display the header information

SQLite> select * from secure; // view the values in the secure table. The command ends;

SQLite> select * From audio_genres;
_ Id | Name // only
. Headers on is displayed on the header.
1 | unknown
2 | Other
3 | rock
4 | soul and R & B
5 | pop
6 | soundtrack
7 | Goa
8 | pop
9 | Chinese pop music]
10 | pop
11 | 148
12 | blues
13 | Genre
14 | Asian
15 | www.ting30.com
16 | General Rock
17 | folk
18 | General soundtrack

SQLite> insert into secure (name, value) values ('device _ provisioned', 1); // insert device_provisioned into the secure table, and set the value to 1

SQLite>. Exit // exit

 

 

Appendix: Common SQL commands

(1) Data Record Filtering:

SQL = "select * from data table where field name = Field Value Order by field name [DESC]"

SQL = "select * from data table where field name like '% Field Value %' order by field name [DESC]"

SQL = "select top 10 * from data table where field name order by field name [DESC]"

SQL = "select * from data table where field name in ('value 1', 'value 2', 'value 3 ')"

SQL = "select * from data table where field name between value 1 and value 2"

 

(2) update data records:

SQL = "update data table set field name = field value where condition expression"

SQL = "update data table set field 1 = value 1, Field 2 = value 2 ...... Field n = value n where condition expression"

 

(3) Delete data records:

SQL = "delete from data table where condition expression"

SQL = "delete from data table" (delete all data table Records)

 

(4) add data records:

SQL = "insert into data table (Field 1, Field 2, Field 3 ...) Valuess (value 1, value 2, value 3 ...) "

SQL = "insert into target data table select * from source data table" (add records of source data table to target data table)

 

(5) statistical functions of data records:

AVG (field name) returns the average value of a table column
Count (* | field name) statistics on the number of data rows or the number of data rows with values in a column
Max (field name) obtains the maximum value of a table column.
Min (field name) obtains the minimum value of a table column.
Sum (field name) adds the values in the data column

The method for referencing the above functions:

SQL = "select sum (field name) as Alias from data table where condition expression"
Set rs = conn. excute (SQL)

Use RS ("alias") to obtain the calculation value. Use the same method for other functions.

 

(6) Create and delete data tables:

Create Table data table name (Field 1 type 1 (length), Field 2 type 2 (length )...... )

Example: Create Table tab01 (name varchar (50), datetime default now ())

Drop table data table name (permanently delete a data table)