SQLite Tutorial (iv): built-in functions _sqlite

One, aggregate functions:

The aggregate functions supported in SQLite are also supported in many other relational databases, so we'll just give a brief description of each aggregate function, rather than giving more examples. It is also important to note that for all aggregate functions, the DISTINCT keyword can be used as the predecessor of a function parameter field to ignore all duplicate field values, such as COUNT (Distinct x), when calculations are made.

Function	Description
AVG (x)	This function returns the average of the number of internal reference in the same group. field values for string and blob types that cannot be converted to numeric values, such as ' HELLO ', are treated as 0 by SQLite. The result of the AVG function is always a floating-point type, with the exception that all field values are null and the result of the function is null.
Count (x|*)	The count (x) function returns the number of rows in the X field that are not equal to NULL in the same group. The count (*) function returns the number of rows of data in the same group.
Group_concat (X[,y])	The function returns a string that will concatenate all non-null x values. The y-parameter of the function will act as a separator between each x value, and if this argument is ignored at the time of the call, the default separator "," will be used when connecting. Then there is the connection order between the strings is indeterminate.
Max (x)	This function returns the maximum value of the X field within the same group, and returns null if all the values for that field are null.
MIN (x)	This function returns the minimum value of the X field within the same group, and returns null if all the values for that field are null.
SUM (x)	This function returns the sum of X field values within the same group, and returns null if the field value is null. If all x field values are integral or NULL, the function returns an integer value, otherwise the floating-point value is returned. Finally, it should be noted that if all data values are integral types, an "integer Overflow" exception will be thrown if the result exceeds the upper limit.
Total (x)	This function does not belong to standard SQL, and the function is basically the same as sum, but the result is more reasonable than sum. For example, when all field values are NULL, the function returns 0.0, unlike sum. And then there's that. The function always returns a floating-point value. The function does not throw an exception at all times.

Second, the core function:

The following functions are the built-in functions provided by the SQLite default, and their declarations and descriptions are shown in the following list:

Function	Description
ABS (X)	The function returns the absolute value of the numeric parameter x, or null if x is NULL, and returns 0 if X is a string that cannot be converted to a numeric value, and throws an "integer Overflow" exception if the x value exceeds the upper limit of the integer.
Changes ()	This function returns the number of data rows affected by the most recently executed insert, UPDATE, and DELETE statements. We can also get the same result by performing sqlite3_changes () of the C + + function.
COALESCE (X,y,...)	Returns the first non-null parameter in the function argument, or null if the argument is null. The function has at least 2 parameters.
Ifnull (X,y)	The function is equivalent to the COALESCE () function of two parameters, which returns the first null function argument, or null if two are null.
Length (X)	If the argument x is a string, returns the number of characters and, if it is a numeric value, returns the length of the string representation of the parameter, or null if NULL.
Lower (X)	Returns the lowercase form of the function parameter x, which, by default, can only be applied to ASCII characters.
LTrim (X[,y])	If you do not have an optional argument y, the function removes all spaces on the left side of the parameter x. If you have a parameter Y, remove any characters that appear in Y on the left side of X. Finally, the string returned after the removal.
Max (X,y,...)	Returns the maximum value in a function argument, or null if any one of the arguments is null.
Min (x,y,...)	Returns the minimum value in a function argument, or null if any one of the arguments is null.
Nullif (X,y)	Returns null if the function argument is the same, otherwise the first argument is returned.
Random ()	Returns the pseudo-random number of integers.
Replace (X,Y,Z)	Replaces all substring y in the function argument x of the string type with the string Z, finally returns the replacement string, and the source string x remains unchanged.
Round (X[,y])	Returns the value for which the numeric parameter x is rounded to the Y scale, and if the argument y does not exist, the default argument value is 0.
RTrim (X[,y])	If you do not have an optional argument y, the function removes all spaces from the right of the parameter x. If there is a parameter Y, remove any characters from the right side of x that appear in Y. Finally, the string returned after the removal.
SUBSTR (X,y[,z])	Returns a substring of the function parameter x, starting at the Y-bit (the first character position in X is 1) to intercept the z-length character, and if the z-parameter is omitted, all characters after the Y-character. If the value of Z is negative, the ABS (Z) character is truncated to the left, starting at the Y-bit. If the Y-value is negative, start at the end of the X string to the position of ABS (Y).
Total_changes ()	This function returns the total number of rows affected by the INSERT, update, and DELETE statements since the connection was opened. We can also get the same result by Sqlite3_total_changes () of the C + + interface function.
Trim (X[,y])	If you do not have an optional argument y, the function removes all spaces on both sides of the parameter x. If you have a parameter Y, remove any characters that appear in Y on both sides of the X. Finally, the string returned after the removal.
Upper (X)	Returns the uppercase form of the function argument x, which can only be applied to ASCII characters by default.
typeof (X)	Returns a string representation of the function parameter data type, such as "Integer, text, real, null", and so on.

Three, date and time functions:

SQLite mainly supports the following four types of date and time related functions, such as:
1). Date (timestring, modifier, modifier, ...)
2). Time (timestring, modifier, modifier, ...)
3. DateTime (timestring, modifier, modifier, ...)
4). strftime (format, timestring, modifier, modifier, ...)
All four of these functions accept a time string as an argument followed by 0 or more modifiers. where the strftime () function also accepts a format string as its first argument. Strftime () and the C Run-time library have identical functions in the same name. For the other three functions, the default format for the date function is: "YYYY-MM-DD", the default format for the time function is: "HH:MM:SS", and the default format for the DateTime function is: "Yyyy-mm-dd HH:MM:SS."
1. strftime function's format information:

month:01-12

format	Description
%d	day of month:00
%f	Fractional Secon Ds:SS.SSS
% H	hour:00-24
%j	Day of year:001-366
% J	Julian Day number
%m
% M	minute:00-59
%s	s Econds since 1970-01-01
% S	seconds:00-59
%w	Day of week 0-6 with sunday==0
% W	Week of year:00-53
% Y	year:0000-9999
%	%

It is additional to note that the remaining three time functions can be represented by strftime, such as:

Copy Code code as follows:

Date (...) Strftime ('%y-%m-%d ', ...)
Time (...) Strftime ('%h:%m:%s ', ...)
DateTime (...) Strftime ('%y-%m-%d%h:%m:%s ', ...)

2. Format of time string:

See the following list:
1). yyyy-mm-dd
2). Yyyy-mm-dd hh:mm
3). Yyyy-mm-dd HH:MM:SS
4). Yyyy-mm-dd HH:MM:SS. Sss
5). hh:mm
6). HH:MM:SS
7). HH:MM:SS. Sss
8). Now
5 to 7) contains only the time part, SQLite will assume the date is 2000-01-01. 8) represents the current time.

3. Modifier:

See the following list:

1). NNN days
2). NNN hours
3). NNN minutes
4). NNN. NNNN seconds
5). NNN months
6). NNN years
7). Start of month
8). Start of year
9). Start of day
). Weekday N

1) to 6 will simply add and subtract a specified number of date or time values, if the value of the NNN is negative, minus, otherwise added. 7) to 9 sets the specified date portion of the time series to the beginning of the current month, year, or day. 10) forward the date to the next one weeks N, of which Sunday is 0. Note: The order of the modifiers is extremely important, and SQLite will execute the modifiers sequentially from left to right.

4. Example:

Copy Code code as follows:

--Returns the current date.
sqlite> SELECT Date (' Now ');
2012-01-15
--Returns the last day of the current month.
sqlite> SELECT Date (' Now ", ' start of Month ', ' 1 month ', '-1 day ');
2012-01-31
--Returns the number of seconds that flow from 1970-01-01 00:00:00 to the current time.
sqlite> SELECT strftime ('%s ', ' Now ');
1326641166
--Returns the first Tuesday of the current year October is the date.
sqlite> SELECT Date (' Now ', ' start of Year ', ' +9 months ', ' weekday 2 ');
2012-10-02