SQLite Tutorial (vi): expressions detailed _sqlite

One, commonly used expressions:

Like most relational databases, SQLite is well able to support the expressions provided in the SQL Standard, and its functions are consistent with the SQL standard, such as:

Copy Code code as follows:

||
*    /    %
+    -
<< >> & |
< <= > >=
= = = = =!= <> is isn't in
and
OR
~ Not

In the expression above, the only thing to say is "| |", this expression is used primarily for a connection between two strings, whose return value is the concatenated string, even if the operands on either side of the operator are not of a string type, before executing the expression needs to be converted to a string type before the connection is made.

Second, the conditional expression:

The syntax rules for this expression are as follows:

Copy Code code as follows:

1. Case x when W1 THEN R1 if W2 THEN R2 ELSE R3 End
2. Case when X=W1 THEN R1 when x=w2 THEN ELSE R2 End

For the first case, the conditional expression x only needs to be evaluated once and then compared to the condition after the When keyword, until an equal condition is found, and its comparison rule is equivalent to an equal sign (=) expression. If a matching condition is found, the value pointed to by the then keyword is returned, and if no match is found, the value after the ELSE keyword is returned, or null if no else branch exists. For the second case, the only difference is that the expression x may be executed multiple times, such as the first when condition does not match, continue to calculate the later when condition, the other rules are identical to the first one. Finally, it should be explained that both of these forms of case expressions follow the short-circuit principle, that is, once the condition of the first expression is matched, all subsequent when expressions are no longer executed or compared.

Iii. Conversion Expressions:

The syntax rules for this expression are as follows:

Copy Code code as follows:

CAST (expr as Target_type)

The expression converts the parameter expr to the Target_type type, and the specific conversion rules are listed as follows:

Target type	Conversion Rule Description
TEXT	If you convert the value of an integer or real type to a direct conversion of text type, you can do the work that is done by sqlite3_snprintf the C + + interface function.
Real	If you convert the value of the text type to the real type, at the very front of the text, the text that can be converted to a real number is converted to the corresponding actual number, and the remainder is ignored. The leading 0 of the text value will also be ignored. If the literal value does not have any characters that can be converted to real, the cast expression converts the result to 0.0.
INTEGER	If you convert the value of the text type to the integer type, in the front of the text, the text that can be converted to an integer is converted to the appropriate integer, and the remainder is ignored. The leading 0 of the text value will also be ignored. If the literal value does not have any characters that can be converted to an integer, the cast expression converts the result to 0. If the conversion converts a real value to an integer type, the decimal part of the real number is truncated directly. If the real number is too large, the largest negative integer is returned:-9223372036854775808.
NUMERIC	If you convert a literal value to a numeric type, the value is cast first to the real type, and SQLite is further converted to an integer type only if the conversion of real to an integer does not result in loss of data information and full reversibility.

The last thing to note is that if expr is null, the result of the conversion is also null.