[Android base]011 Storage data (medium)--sqlite syntax Introduction

Storage data (Medium)--sqlite syntax Introduction

What this article has learned:
1, sqlite data storage

The database used by Android is SQLite, which is presented here as an example of sqlite3.

1 Sqlite3 supported data types

NULL  INTEGER  REAL  TEXT

2 but in fact, Sqlite3 also accepts the following data types:

smallint 16-bit integer.
interger 32-bit integer.
decimal (p, s) p is an exact value and a decimal integer in the size of s. The exact value p refers to all digits and s refers to the number of digits after the decimal point. If not specified, the system will be set to p = 5; s = 0.
float 32-bit real number.
double 64-bit real number.
char (n) A string of length n, where n cannot exceed 254.
varchar (n) A string with a fixed length and a maximum length of n. n cannot exceed 4000.
graphic (n) is the same as char (n), but its unit is two characters double-bytes, n cannot exceed 127. This form is to support two-character fonts, such as Chinese characters.
vargraphic (n) A variable-length double-character string with a maximum length of n, where n cannot exceed 2000
date contains the year, month, and date.
time contains hours, minutes, and seconds.
timestamp contains year, month, day, hour, minute, second, thousandth of a second.

3 SQLite contains the following time/date functions:

datetime () .............. producing date and time
date () ...........
time () ..............
strftime () .............. Format the date and time generated by the above three functions

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

You can use a string of the following format as a parameter in a 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 where now is the time of the present. 

Example 1.

Select DateTime (' Now ');
Results :

2015-08-31 13:17:41

Example 2.

Select DateTime (' 2015-08-31 ');
Results :

2015-08-31 00:00:00

Example 5.

Select DateTime (' Now ', ' start of Year ');
result : 2015-01-01 00:00:00

Example 6.

Select DateTime (' Now ', ' start of Month ');
result : 2015-08-01 00:00:00

Example 7.

Select DateTime (' Now ', ' start of Day ');
result : 2015-08-31 00:00:00

Example 8.

Select DateTime (' Now ', ' +10 hour ', ' start of day ', ' +10 Hour ');
result : 2015-08-31 10:00:00

Example 9.

Select DateTime (' Now ', ' localtime ');
result : 2015-08-31 21:21:34

Example 10.

Select DateTime (' Now ', ' +8 Hour ');
Results :

2015-08-31 21:22:19

The +1 hour and -12 minute in example 3 indicate that a certain amount of time can be increased or decreased at the base time (the first parameter of the DateTime function).

The start of year in Example 5 represents the time of the beginning of the first day.

As can be seen from Example 8, although the 2nd parameter added 10 hours, but was the 3rd parameter "StartOf Day" to zero the time to 00:00:00, followed by the 4th parameter at 00:00:00
On the basis of the time increased by 10 hours into the 10:00:00.

Example 9 converts the Greenwich time zone to the cost of the time zone.

Example 10 converts the Greenwich time zone to the East eight zone.

The strftime () function converts date strings in YYYY-MM-DD HH:MM:SS format to other forms of strings.
The syntax of Strftime () is strftime (format, date/time, modifier, modifier, ...)

It can be formatted with the following symbols for dates and times:
%d month, 01-31
%f decimal form of the second, SS. Sss
%H hours, 00-23
%j figure out the day of the year, 001-366
%m month, 00-12
%M min, 00-59
%s number of seconds from January 1, 1970 to present
%s seconds, 00-59
%w Week, 0-6 (0 is Sunday)
%W calculates the day of the week that falls within that year, 01-53
%Y years, YYYY
Percent hundred percent semicolon

An example of the use of strftime () is as follows:

Example 11.

Select Strftime ('%y.%m.%d%h:%m:%s ', ' Now ', ' localtime ');
Results :

2015.08.31 21:22:54

4 function Chapters: arithmetic functions

ABS (X) returns the absolute value of the given numeric expression.
Max (x,y[,...]) Returns the maximum value of an expression.
min (x,y[,...]) Returns the minimum value of an expression.
Random (*) returns the number.
round (x[,y]) Returns a numeric expression that is rounded to the specified length or precision.

Character processing functions

Length (X) returns the number of characters for the given string expression.
Lower (X) returns a character expression after converting the uppercase character data to lowercase characters.
Upper (X) returns the character expression that converts the lowercase character data to uppercase.
substr (x, Y, z) returns part of an expression.
randstr ()
quote (A)
A like (A, b) determines whether the given string matches the specified pattern.
Glob (A, B)

Conditional Judgment function

COALESCE (x,y[,...])
ifnull (x, y)
Nullif (x, y)

Aggregate functions

avg (X) returns the average of the values in the group.
count (X) returns the number of items in the group.
Max (X) returns the maximum value of the value in the group.
min (X) returns the minimum value of the value in the group.
sum (X) returns the sum of all values in an expression.

Other functions

typeof (X) returns the type of data.
Last_insert_rowid () returns the ID of the last inserted data.
sqlite_version (*) returns the version of SQLite.
change_count () returns the number of rows affected by the previous statement.
Last_statement_change_count ()

This site article is for baby bus SD. Team Original, reproduced must be clearly noted: (the author's official website: Baby bus )

Reprinted from "Baby bus Superdo Team" original link: http://www.cnblogs.com/superdo/p/4774116.html

[Android base]011 Storage data (medium)--sqlite syntax Introduction