SQLite tutorial (4): built-in functions, sqlite tutorial built-in functions

SQLite tutorial (4): built-in functions, sqlite tutorial built-in functions

1. Aggregate functions:

The aggregate functions supported by SQLite are also supported in many other relational databases. Therefore, here we will just give a brief description of each aggregate function, rather than giving more examples. It should be further noted that for all Aggregate functions, the distinct keyword can be used as a pre-attribute of the function parameter field to ignore all repeated field values During computation, for example, count (distinct x ).

 

Function	Description
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.

Ii. core functions:

The following functions are built-in functions provided by SQLite by default. The descriptions and descriptions are listed as follows:

 

Function	Description
Abs (X)	This function returns the absolute value of the numeric parameter X. If X is NULL, NULL is returned. If X is a string that cannot be converted to a numeric value, 0 is returned. If the X value exceeds the Integer upper limit, the system throws an "Integer Overflow" exception.
Changes ()	This function returns the number of data rows affected by the most recent INSERT, UPDATE, and DELETE statements. You can also run the C/C ++ function sqlite3_changes () to get the same result.
Coalesce (X, Y ,...)	Returns the first non-NULL parameter in the function parameter. If all the parameters are NULL, NULL is returned. This function has at least two parameters.
Ifnull (X, Y)	This function is equivalent to the coalesce () function of two parameters. That is, the first function parameter not NULL is returned. If both are NULL, NULL is returned.
Length (X)	If X is a string, the number of characters is returned. If it is a numerical value, the length of the string representation of this parameter is returned. If it is NULL, NULL is returned.
Lower (X)	Returns the lowercase form of the function parameter X. By default, this function can only be applied to ASCII characters.
Ltrim (X [, Y])	If no optional parameter Y is available, this function removes all space characters on the left side of parameter X. If the parameter Y exists, remove any characters in Y on the left side of X. Returns the removed string.
Max (X, Y ,...)	Returns the maximum value of a function parameter. If any of the input values is NULL, NULL is returned.
Min (X, Y ,...)	Returns the minimum value of a function parameter. If any of the input values is NULL, NULL is returned.
Nullif (X, Y)	If the function parameters are the same, NULL is returned. Otherwise, the first parameter is returned.
Random ()	Returns an integer pseudo-random number.
Replace (X, Y, Z)	Replace all substring Y in string-type function parameter X with string Z, and return the replaced string. The source string X remains unchanged.
Round (X [, Y])	Returns the value of X rounded to Y scale. If Y does not exist, the default value is 0.
Rtrim (X [, Y])	If Y is not available, this function removes all space characters on the Right of X. If the parameter Y exists, remove any characters that appear in Y on the right side of X. Returns the removed string.
Substr (X, Y [, Z])	Returns the sub-string of function parameter X. The Z-length character is truncated from the Y-th (the first character in X is 1). If the Z parameter is ignored, take all characters after the Y character. 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.
Total_changes ()	This function returns the total number of rows affected by the INSERT, UPDATE, and DELETE statements since the connection is opened. We can also use the C/C ++ interface function sqlite3_total_changes () to get the same result.
Trim (x [, y])	If Y is not available, this function removes all space characters on both sides of X. If the parameter Y exists, remove any characters on both sides of X that appear in Y. Returns the removed string.
Upper (X)	Returns the upper case of the function parameter X. By default, this function can only be applied to ASCII characters.
Typeof (X)	Returns a string representation of the Data Type of function parameters, such as Integer, text, real, and null.

Iii. Date and time functions:

SQLite mainly supports the following four date-and time-related functions, such:
1). date (timestring, modifier, modifier ,...)
2). time (timestring, modifier, modifier ,...)
3). datetime (timestring, modifier, modifier ,...)
4). strftime (format, timestring, modifier, modifier ,...)
All the above four functions accept a time string as the parameter, followed by 0 or more modifiers. The strftime () function also accepts a format string as its first parameter. Strftime () and C Runtime libraries have the same name function. For the other three functions, the default format of the date function is: "YYYY-MM-DD", the default format of the time function is: "HH: MM: SS", the default format of the datetime function is: "YYYY-MM-DD HH: MM: SS ".
1. format information of the strftime function:

Format	Description
% D	Day of month: 00
% F	Fractional seconds: SS. SSS
% H	Hour: 00-24
% J	Day of year: 001-366
% J	Julian day number
% M	Month: 01-12
% M	Minute: 00-59
% S	Seconds 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
%	%

Note that the other three time functions can be expressed using strftime, for example:
 Copy codeThe Code is as follows:
Date (...) strftime ('% Y-% m-% d ',...)
Time (...) strftime ('% H: % M: % s ',...)
Datetime (...) strftime ('% Y-% m-% d % H: % M: % s ',...)

2. Time string format:

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) only contains the time section. SQLite assumes that the date is 2000-01-01. 8) indicates 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
10). weekday N

1) to 6) simply add or subtract a specified number of date or time values. If the NNN value is negative, subtract. Otherwise, Add. 7) to 9), set the specified date in the time string to the start of the current month, year, or day. 10) the date is moved forward to N of the next week, where Sunday is 0. Note: The order of modifier is extremely important. SQLite will execute the modifier in sequence from left to right.

4. Example:
 Copy codeThe Code is 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 from 00:00:00 to the current time.
Sqlite> SELECT strftime ('% s', 'Now ');
1326641166
-- Returns the date of the first Tuesday in July of the current year.
Sqlite> SELECT date ('now ', 'start of year',' + 9 months', 'weekday 2 ');
2012-10-02