(Android-sqlite 2) Summary of common SQL statement functions supported by Sqlite

Document directory

• 1. Create a table
• 2 common date functions
• 3. common string functions
• 4. Common statistical functions
• 5. extract data by PAGE

1. Create a table

1.1 The primary key is an auto-incrementing integer.

Example: [pk] integer primary key autoincrement. Note: pk is the primary key name.

1.2 common string types

Example [name] text

 

2 common date functions

2.1 Current Database time

Select datetime ('now ', 'localtime ')

2.2 convert time into date functions

Date (time)

2.3 current time yesterday

Datetime ('now ', 'localtime'),'-24 hour ','-0 minute ')

3. common string functions

Replace (X, Y, Z) replaces all substring Y in function parameter X of string type with string Z, and returns the replaced string. The source string X remains unchanged.

Substr (X, Y [, Z]) returns the substring of the function parameter X, which is a Z-length string starting from the Y-th digit (the first character in X is 1, if the Z parameter is ignored, all characters after the Y character are taken. If the value of Z is negative, the abs (Z) character is truncated from the Y-th to the left. If the Y value is negative, the count starts from the end of the X string to the position of abs (Y.

4. Common statistical functions

 

Avg (x)	This function returns the average value of the parameter fields in the same group. For String and BLOB field values that cannot be converted to numeric values, such as 'hello', SQLite regards it as 0. The results of the avg function are always float. The only exception is that all field values are NULL, and the result of this function is NULL.
Count (x | *)	The count (x) function returns the number of rows in the same group where the value of x is not equal to NULL. The count (*) function returns the number of data rows in the same group.
Group_concat (x [, y])	This function returns a string that connects all non-NULL x values. The y parameter of this function serves as the separator between each x value. If this parameter is ignored during calling, the default separator "," will be used during connection ",". In addition, the connection sequence between strings is uncertain.
Max (x)	This function returns the maximum value of the x field in the same group. If all values of this field are NULL, this function also returns NULL.
Min (x)	This function returns the minimum value of the x field in the same group. If all values of this field are NULL, the function returns NULL.
Sum (x)	This function returns the sum of the x Field Values in the same group. If all the field values are NULL, this function also returns NULL. If all x Field Values are integer or NULL, this function returns an integer value. Otherwise, a floating point value is returned. It should be pointed out that if all data values are integer, an "integer overflow" exception will be thrown once the result exceeds the upper limit.
Total (x)	This function is not a standard SQL statement and has the same functions as sum, but the calculation result is more reasonable than sum. For example, if all fields are NULL, the function returns 0.0, which is different from sum. Then, this function always returns a floating point value. This function will never throw an exception.

5. extract data by PAGE

5.1 extract the first 10 data from the dataset

Example: SELECT * FROM worddata limit 1

5.2 extract 10 data records after 50th data entries in the dataset

Example: SELECT * FROM worddata limit 50, 10

 

Appendix:

Android Market of science back words-Level 4 edition: http://static.apk.hiapk.com/html/2012/07/719368.html