SQLite summary, sqlite

SQLite summary, sqlite
Zookeeper

Generally, the static data type is fixed, while SQLite uses the dynamic data type, which is automatically determined based on the stored value. SQLite has the following five data types:

1. NULL: NULL.
2. INTEGER: A signed INTEGER, depending on the size of the number to be saved.
3. REAL: floating point number, which is stored as an 8-byte IEEE floating point number.
4. TEXT: String TEXT.
5. BLOB: binary object.

To maximize the compatibility between SQLite and other database engines, SQLite proposes the concept of "Type Affinity. We can understand "type affinity" in this way. After a table field is declared, SQLite selects a kinship type based on the type declared for this field. When data is inserted, the data of this field will take the kinship type as the storage method of this value first, unless the kinship type does not match or the current data cannot be converted to this kinship type, in this way, SQLite will consider other types that are more suitable for storing this value. Currently, SQLite supports the following five kinship types:

1. rules for determining field affinity:
The affinity of a field is determined by the type defined when the field is declared. For specific rules, refer to the following list. Note that the order of the following list is that if a field type meets both affinity, the rule that comes first takes effect.
1). If the type string contains "INT", the kinship type of this field is INTEGER.
2) If the type string contains "CHAR", "CLOB", or "TEXT", the kinship type of this field is TEXT, such as VARCHAR.
3) if the type string contains "BLOB", the kinship type of this field is NONE.
4). If the type string contains "REAL", "FLOA", or "DOUB", the kinship type of this field is REAL.
5) in other cases, the kinship type of the field is NUMERIC.

2. Example:

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.

Datetime contains the date and time format. It must be written as '2017-08-05 'and cannot be written as '2017-8-5'. Otherwise, an error will occur during reading!

SQLite contains the following time/date functions:
Datetime ()...
Date () ......
Time () ......
Strftime () ...... format the date and time generated by the preceding three functions.

Datetime (): datetime (date/time, modifier, modifier ...)
The syntax of date () and time () is the same as that of datetime.

You can use strings in the following format as parameters in the time/date function:
YYYY-MM-DD
YYYY-MM-DD HH: MM.
YYYY-MM-DD HH: MM: SS.
YYYY-MM-DD HH: MM: SS. SSS.
HH: MM
HH: MM: SS
HH: MM: SS. SSS
Now
Now indicates the current generation time.

The strftime () function converts a date string in the YYYY-MM-DD HH: MM: SS format to another form of string.
Strftime () syntax is strftime (format, date/time, modifier, modifier ,...)

It can be formatted with the following symbols:
% D month, 01-31
% F seconds in decimal form, SS. SSS
% H hour, 00-23
% J calculates the day of the year, 001-366
% M month, 00-12
% M minutes, 00-59
% S seconds from January 1, January 1, 1970 to the present
% S seconds, 00-59
% W weeks, 0-6 (0 is Sunday)
% W calculate the week of the year for a day, 01-53
% Y, YYYY
% Percent sign

The usage of strftime () is as follows:


Example 11. select strftime ('% Y. % m. % d % H: % M: % s', 'Now', 'localtime ');
Result: 2006.10.17 21:41:09


Sqlite functions:
Arithmetic functions
Abs (X) returns the absolute value of a given numeric expression.
Max (X, Y [,...]) returns the maximum value of the expression.
Min (X, Y [,...]) returns the minimum value of the expression.
Random (*) returns a random number.
Round (X [, Y]) returns a numeric expression rounded to the specified length or precision.
Character Processing functions
Length (X) returns the number of characters in a given string expression.
Lower (X) converts uppercase data to lowercase data and returns a character expression.
Upper (X) returns a character expression that converts lowercase data to uppercase.
Substr (X, Y, Z) returns part of the expression.
Randstr ()
Quote ()
Like (A, B) determines whether the specified string matches the specified pattern.
Glob (A, B)
Conditional judgment Function
Coalesce (X, Y [,...])
Ifnull (X, Y)
Nullif (X, Y)
Set Functions
Avg (X) returns the average value of the group's values.
Count (X) returns the number of items in the group.
Max (X) returns the maximum value of the group value.
Min (X) returns the minimum value of the group value.
Sum (X) returns the sum of all values in the expression.
Other functions
Type of data returned by typeof (X.
Last_insert_rowid () returns the ID of the last inserted data.
Sqlite_version (*) returns the SQLite version.
Change_count () returns the number of rows affected by the previous statement.
Last_statement_change_count ()