SQLite tutorial (6): expression explanation, sqlite expression

SQLite tutorial (6): expression explanation, sqlite expression

I. common expressions:

Like most relational databases, SQLite can well support expressions provided in the SQL standard, and its functions are consistent with the SQL standard, such:
Copy codeThe Code is as follows:
|
*/%
+-
<> & |
<=> =
===! = <> IS NOT IN LIKE
AND
OR
~ NOT

In the above expression, the only thing to note is "|". This expression is mainly used for the connection between two strings. Its return value is the connected string, even if the operands on both sides of the operator are non-string type, they must be converted to the string type before the expression is executed, and then connected.

Ii. conditional expressions:

The syntax of this expression is as follows:
 Copy codeThe Code is as follows:
1). CASE x WHEN w1 THEN r1 WHEN w2 THEN r2 ELSE r3 END
2). case when x = w1 THEN r1 WHEN x = w2 THEN r2 ELSE r3 END
 
In the first case, condition expression x only needs to be calculated once and then compared with the condition after the WHEN keyword one by one until an equal condition is found. The comparison rule is equivalent to equal sign (=) expression. If a matching condition is found, the value pointed to by the subsequent THEN keyword is returned. If no matching is found, the value after the ELSE keyword is returned. If no ELSE branch exists, NULL is returned. In the second case, the only difference from the first case is that expression x may be executed multiple times. For example, if the first WHEN condition does not match, the following WHEN condition is calculated, other rules are identical to the first rule. It should be noted that the above two forms of CASE expressions follow the short circuit principle, that is, once the conditions of the first expression match, all the subsequent WHEN expressions will not be executed or compared.

Iii. Conversion expression:

The syntax of this expression is as follows:
 Copy codeThe Code is as follows:
CAST (expr AS target_type)
 
This expression converts the expr parameter to the target_type type. For specific conversion rules, see the following list:

Target type	Conversion Rule Description
TEXT	If you want to convert an INTEGER or REAL value to the TEXT type, you can directly convert it, just like the work completed by the C/C ++ interface function sqlite3_snprintf.
REAL	If the value of the TEXT type is converted to the REAL type, the TEXT that can be converted to the REAL number is converted to the corresponding REAL number at the beginning of the TEXT, and the rest is ignored. The leading zeros of the text value are also ignored. If the text value does not contain any character, it can be converted to a real number. The CAST expression is converted to 0.0.
INTEGER	If you convert the TEXT value to the INTEGER type, the TEXT that can be converted to an INTEGER is converted to the corresponding INTEGER at the beginning of the TEXT, and the rest is ignored. The leading zeros of the text value are also ignored. If the text value does not contain any characters, it can be converted to an integer. The CAST expression is converted to 0. If a real value is converted to the INTEGER type, the fractional part of the real number is truncated. If the real number is too large, the maximum negative integer-9223372036854775808 is returned.
NUMERIC	If the text value is converted to the NUMERIC type, the value is first forcibly converted to the REAL type. Only when the REAL type is converted to an INTEGER will not cause data loss or completely reversible data, SQLite will be further converted to the INTEGER type.

Note that if expr is NULL, the conversion result is also NULL.