Android sqlite3 adb command Learning

Create a database
Use sqlite3.exe in the region. The data creation command is as follows:
Sqlite3 db_name.xx;
The database name is arbitrary. After creation, the database is directly entered. If the file exists, the database is opened directly;

SQL Instruction format
All SQL commands end with semicolons (;). To improve readability, multiple lines of one command can be edited until a semicolon ends;
Two minus signs (--) in sqlite3 represent annotations, which are ignored by sqlite.

Create a table
Enter the following command to create a tab_name table:
Create table tab_name (field1, field2, field3 ...);
Sqlite3 has no strict requirements on fields. A field can store any type of data, and it will be automatically converted in a timely manner. Of course, you can also define the data type when creating a table.

Data Type
NULL
INTEGER
REAL
TEXT
BLOB
But in fact, sqlite3 also accepts the following data types:
Smallint is an integer of 16 bits.
An integer of 32-bit interger.
Decimal (p, s) p exact value and the decimal integer of s size. Exact value p refers to the number of digits after the decimal point. If not specified

The system is set to p = 5; s = 0.
Float 32-bit real number.
The real number of the double 64-bit element.
Char (n) n length string, n cannot exceed 254.
A string with an unfixed varchar (n) length and a maximum length of n. n cannot exceed 4000.
Graphic (n) is the same as char (n), but it is measured in double-bytes. n cannot exceed 127. This form supports two character-length fonts, such as Chinese characters.
A dual-character string with a variable vargraphic (n) length and a maximum length of n. n cannot exceed 2000
Date contains the year, month, and date.
Time contains hours, minutes, and seconds.
Timestamp includes year, month, day, hour, minute, second, And 1‰ seconds.

View
. Database: displays database information;
. Tables: displays the table name. (It may also be. table)
The. schema command can be used to view the SQL commands used to create a data table;
. Schema table_name: SQL command used to view the table table_name created;

Insert record
Insert into table_name values (field1, field2, field3 ...);

Query
Select * from table_name; view all records in the table_name table;
Select * from table_name where field1 = 'xxxxx'; query records that meet the specified conditions;

Delete
Drop table_name; delete a table;
Drop index_name; delete an index;

Change output format

. Mode list | column | insert | line | tabs | tcl | csv
. Separator "X" change the boundary symbol to X

Change output
. Output file_name | stdout
Output to file or standard output (default Terminal)

. Databases: List database file names
. Tables? PATTERN? List? PATTERN? Matched table name
. Import file table:
. Dump? TABLE? Generate an SQL script to form a database table
. Output FILENAME: import the output to the specified file.
. Output stdout print the output to the screen
. Mode MODE? TABLE? Set the data output mode (csv, html, tcl...
. Nullvalue STRING replaces the output null string with the specified STRING
. Read FILENAME: Execute the SQL statement in the specified file
. Schema? TABLE? Print the SQL statement used to create a database table
. Separator STRING replaces the field separator with the specified STRING
. Show print the settings of all SQLite Environment Variables
. Quit exit command line interface

1. Storage category

The second version stores the values of all columns in the ASCII text format. The third edition stores data as integers and real numbers, and BLOB data.

Each value stored in an SQLite database has an attribute that belongs to one of the classes listed below (controlled by the database engine)

NULL: The value is NULL.
INTEGER: The value is identified as an INTEGER. values can be stored as 1, 2, 3, 4, 5, 6, or 8 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. The value is a TEXT string that is 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.

Like SQLite2.0, in MySQL 3.0, all columns in the database can store any type of data except integer primary key. This rule is also similar to the following:

Description in mode.

Enter all SQLite values, whether embedded in SQL statements or pre-compiled values bound to SQL statements, to be stored as a class before execution of SQL statements.

In this case, the database engine checks and converts the value between the digital storage class (integer and real number) and text class.

The storage category is initially classified as follows:

For example, the text with double quotation marks or single quotation marks in the SQL statement is defined as text. If the text does not contain quotation marks and does not have a decimal point or index, it is defined as an integer. If the text does not contain quotation marks but has

The decimal point or index is defined as a real number. If the value is null, it is defined as a null value. BLOB data is identified by the symbol 'abc.

Values supplied using the input value uses sqlite3_bind _ * APIs to be classified as a storage level, which is basically consistent with the original class. (For example

Qlite3_bind_blob () bind a BLOB value ).

The classification of values is the result of SQL hierarchical operations, which is determined by the farthest operation expression. user-defined functions may return any class value. during compilation, it is basically necessary to determine the storage class of the expression.

Impossible.

2. affinity between columns

In SQLite3.0, the reason why the value type is defined is only related to the value itself is that it has no relationship with the column and the variable. (This is sometimes called a weak type .) all other data we use

The library engine is limited by the static type system. All the values of the class are determined by the attribute of the column to which it belongs, and the value is irrelevant.

To maximize compatibility between SQLite databases and other databases, SQLite supports column "type affinity ".
The column affinity is recommended for the data stored in this column. we should pay attention to suggestions rather than forced. theoretically, any column can still store any type of data. just needle

For certain columns, the database will store the recommended data type. The data type that is preferentially used is called "affinity type ".

In SQLite3.0, each column in the database is defined as one of the following affinity types:

TEXT
NUMERIC
INTEGER
REAL
NONE

A column with type affinity stores all the data according to the untyped, text, or BLOB. If the numeric data is inserted into a column with the text type affinity, the number is converted

Text.

A column with a numeric type affinity may use all five storage types to store values. when text data is inserted into a numeric column, the database will try to convert the text into an integer before it is stored.

Number or real number. if the conversion is successful, the value will be stored based on the type of live real number of the certificate. if the conversion fails, the value can only be stored by text type, instead of being converted to untyped or

BLOB type to store.

A column with integer affinity is the same as a column with numeric affinity in terms of conversion, but there are also some differences, such as the real value without floating volume (the value of text value conversion) inserted with an integer parent

It is converted to an integer and stored as an integer.

A column with no type affinity does not select which type to use first. It does not force data conversion before data is input.

Affinity of 2.1 Columns

The affinity type of a column is determined by the declared type of the column. The following rules are observed:

A. if the data type includes the string "INT", it is defined as an integer affinity.
B. if the data types in the column include any of the following strings: "CHAR", "CLOB", or "TEXT", the column has the TEXT affinity. note that the VARCHAR type includes the string "CHAR"

Therefore, it also has the text type affinity.
C. If the data type of a column includes the string "BLOB" or if the data type is embodied,
This column has no type affinity.
D. Otherwise, the data type is compatible.

If the TABLE is generated using the If "create table as select..." statement, no specific data type exists for all columns, and no type affinity exists.

Example of the affinity of the 2.2 Column

Create table t1 (
T TEXT,
Nu NUMERIC,
I INTEGER,
No BLOB
);

-- Storage classes for the following row:
-- TEXT, REAL, INTEGER, TEXT
Insert into t1 VALUES ('2014. 0', '2014. 0', '2014. 0', '2014. 0 ');

-- Storage classes for the following row:
-- TEXT, REAL, INTEGER, REAL
Insert into t1 VALUES (500.0, 500.0, 500.0, 500.0 );

3. Comparison expression

Like SQLite2.0, one feature of version 3.0 is the binary comparison operator '=', '<', '<=', '> =' and '! = ', One operation 'in' can be used to test the fixed membership, triple comparison operations

Or 'between '.
The comparison result depends on the storage type of the two values to be compared. Follow the following rules:

A value with an empty storage type is considered to be smaller than any value (including another value with an empty storage type ).
An integer or real value is smaller than any text value or BLOB value. When an integer or real number is compared with another integer or real number, it is compared according to the actual value.
A text value is smaller than the BLOB value. When comparing two text values, use the memcmp () function in the C language class library to compare them. However, sometimes this is not the case, as described below

In the case of "user-defined sorting order.
When two BLOB texts are compared, the result depends on the memcmp () function.
Before starting the comparison, SQLite tries to convert values between numbers (integers and real numbers) and texts. The following is an example of how to compare binary values. In the duplicate below

The expression used can represent the SQL scalar expression or text but not a column value.

When a column value is compared to the expression result, the column affinity is applied to the expression result before the comparison starts.
When two column values are compared, if one column has an integer or number affinity, but the other column does not, the number affinity applies to any text storage extracted from non-numeric columns.

Type value. P>
When comparing the results of two expressions, the result is directly compared without any conversion. If a string is compared with a number, the number is always smaller than the string.
In SQLite, the expression "a BETWEEN B AND c" is equal to the expression "a> = B AND a <= c". When comparing expressions, a can have any affinity.

Expression "a IN (SELECT B ....) "The comparison follows the three rules mentioned above. It is a binary comparison. (for example, in a similar style "a = B "). for example, if 'B' is

Column value. 'A' is an expression. Before comparison, the affinity of 'B' is converted to that of 'A.

SQLite treats the expressions "a IN (x, y, z)" and "a = z OR a = y OR a = z" as equal.

3.1 comparison examples

Create table t1 (
A TEXT,
B NUMERIC,
C BLOB
);

-- Storage classes for the following row:
-- TEXT, REAL, TEXT
Insert into t1 VALUES ('20170101', '20160301', '20160301 ');

-- 60 and 40 are converted to '60' and '40' and values are compared as TEX
T.
SELECT a <60, a <40 FROM t1;
1 | 0

-- Comparisons are numeric. No conversions are required.
SELECT B <60, B <600 FROM t1;
0 | 1

-- Both 60 and 600 (storage class NUMERIC) are less than '000000'
-- (Storage class TEXT ).
SELECT c <60, c <600 FROM t1;
0 | 0

4. Operators

All mathematical operators (all operators, rather than the chain operator "|") operator objects have a numerical affinity first. If one or both of them cannot be converted to digits

The result will be a null value.
For Concatenation Operators, all operators will first have text affinity. If any of the operators cannot be converted to text (because it is null or BLOB), it is connected to the operator.

It will be null.

5. Classification, sorting, and hybrid selection

When a value is selected using the ORDER clause, the null value is first selected, then the integer and real number are selected in ORDER, and then the text value is selected in the memcmp () ORDER, finally, the BLOB value is

Memcmp () sequence is selected. Values of no storage type are converted before selection.
When a group by clause is used to GROUP values, values of Different Storage types are considered different, but there are also exceptions, such as an integer and a real

Values are equal from the numerical point of view, so they are equal. After the GROUP by clause is used for comparison, the values do not have any affinity.

The mixed selection operator UNION, INTERSECT and except t performs absolute comparison between values. The same affinity will be applied to all values, and these values will be stored in a separate

In the columns in the result group of the SELECT statement, the affinity granted is the affinity of the column, which is returned by most of the remaining mixed SELECTS.

Column value (rather than other types of expressions). If a given mixed SELECT column does not have the number of SELECTS, the value of this column will not have any affinity before comparison.

6. Other affinity Modes

The above section describes the operations performed by the database engine in the normal affinity mode. SQLite describes the other two affinity modes, as shown below:

Strict affinity mode. In this mode, if you need to convert data storage types between values, the database engine will send an error report and the current statement will run again.
In this mode, the data storage type of values is not converted. Values of Different Storage types cannot be compared, but can be compared between integers and real numbers.
7. User-Defined proofreading Sequence
By default, when SQLite compares two text values, it is set By the system, regardless of the encoding of the string, compared with memcmp (). The third edition of SQLite allows users to provide any

Instead of memcmp (), that is, the User-Defined comparison sequence.
In addition to the BINARY comparison sequence predefined by the system, it is compared using the memcmp () function. SQLite also contains two additional built-in comparison sequence functions, NOCASE and REVERSE:

BINARY-use memcmp () to compare string data, regardless of text encoding.
NOCASE-the same as binary, but before comparison, the 26-digit upper-case letter disk will be converted into the corresponding lower-case letter disk.

7.1 allocation comparison order
Each column in each table has a predefined comparison type. If a comparison type is not required by binary, the comparison clause is embodied in the column definition to define the column.
When we use SQLite to compare two text values, the comparison sequence determines the comparison result according to the following rules. The third and fifth sections of the document describe the situation in which such a comparison occurs.

For binary comparison operators (=, <, >,< = and> =), if each operand is a column, the default comparison type of this column is determined by the comparison sequence used. if both operands are columns

Then the comparison class of the left operand
Type determines the order of comparison to be used. If neither of the two operands is a column, binary comparison is used.

The expressions "x BETWEEN y and z" AND "x> = y and x <= z" are the same. expression "x IN (SELECT y ...) "And expression" x = y "use the same method for operations, which is

Determines the order of comparison to be used. if X is a column or binary, "x IN (y, z ...) "The comparison sequence used by the expression in the form is the default comparison type of X.

Order by clause that is part of a SELECT statement may be assigned a collation sequence to be used for the sort operation

Explicitly. In this case the explicit collation sequence is always used. Otherwise, if the expression sorted by an ORDER

Clause is a column, then the default collation type of the column is used to determine sort order. If the expression is not

Column, then the BINARY collation sequence is used.

7.2 comparison sequence example

The following example describes the examples below identify the collation sequences that wocould be used to determine The results of text

Comparisons that may be saved med by various SQL statements. Note that a text comparison may not be required, and no collation

Sequence used, in the case of numeric, blob or NULL values.

Create table t1 (
A, -- default collation type BINARY
B COLLATE BINARY, -- default collation type BINARY
C collate reverse, -- default collation type REVERSE
D collate nocase -- default collation type NOCASE
);

-- Text comparison is saved med using the BINARY collation sequence.
SELECT (a = B) FROM t1;

-- Text comparison is saved med using the NOCASE collation sequence.
SELECT (d = a) FROM t1;

-- Text comparison is saved med using the BINARY collation sequence.
SELECT (a = d) FROM t1;

-- Text comparison is saved med using the REVERSE collation sequence.
SELECT ('abc' = c) FROM t1;

-- Text comparison is saved med using the REVERSE collation sequence.
SELECT (c = 'abc') FROM t1;

-- Grouping is saved med using the NOCASE collation sequence (I. e. values

-- 'Abc' and 'abc' are placed in the same group ).
SELECT count (*) group by d FROM t1;

-- Grouping is saved med using the BINARY collation sequence.
SELECT count (*) group by (d | '') FROM t1;

-- Sorting is saved med using the REVERSE collation sequence.
SELECT * FROM t1 order by c;

-- Sorting is performed med using the BINARY collation sequence.
SELECT * FROM t1 order by (c | '');

-- Sorting is saved med using the NOCASE collation sequence.
SELECT * FROM t1 order by c collate nocase;