SQLite Common bit Summary (turn)

Expression
Expr:: = expr Binary-op Expr |
expr [not] like-op expr
[ESCAPE Expr] |
Unary-op Expr |
(expr) |
Column-name |
Table-name
. Column-name |
Database-name. Table-name. Column-name |
Literal-value
|
parameter |
Function-name (expr-list | *) |
Expr ISNULL |
Expr
Notnull |
expr [not] between expr and expr |
expr [not] in (value-list)
|
expr [not] in (select-statement) |
expr [not] in [Database-name.]
Table-name |
[EXISTS] (select-statement) |
case [expr] (when expr then
expr) + [ELSE expr] END |
CAST (expr as type) |
Expr COLLATE
Collation-name
Like-op:: = like | GLOB | REGEXP |
MATCH

This part is different from other parts. Most of the other parts of this document are about specific SQL
Command. This section is not about a single command, but an "expression", which is often used as part of other commands.

Sqlite
There are the following two-tuple operators, depending on their priority from high to Low:

||
*    /    %
+    -

<< >> & |
< <= > >=

= = = = = <> in
and

OR

The following are the supported unary operators:

-    +    ! ~ Not

COLLATE
An operator can be thought of as a post-unary operator that has the highest precedence. Typically, it binds to parameters more tightly than the preceding unary, and two-dollar operators.

One dollar [Operator
+] do nothing. It can be applied to a string, a number, or a blob, and always returns the same result as the value it acts on.

Note that the Equals operator for both variants is not the same. Wait can be =
or = =. The inequality operator has! = or <>. | | The operator is a "connector," which connects two strings together. %
Outputs the remainder of the number to the left divided by the right number.

except | | , the result of any binary operator is a numeric value. |
Returns a large string of two operands after the connection.

A literal is a value of an integer or floating-point number. Scientific notation is also supported. The decimal point always uses the "." character to indicate that even if the local settings are specified with ","
To indicate that it is no exception. Under this rule, using "," as a decimal point can cause semantic semantics of two. String values should be enclosed by single quotation marks ('). Like in PASCL.
In the language, the single quotation marks in a string should be represented by two single quotation marks. This type of escape is not supported because it is not the standard of the SQL language to escape using backslashes like the C language.
A blob literal is a string value that begins with an "x" or "X" that contains 16 of binary data. For example:


X ' 53514c697465 '

The literal value can also be a "NULL" token.

parameter is used to specify where a literal is stored in an expression. It will be used at run time
Sqlite3_bind API replacement. The parameters are in the following formats:

? NNN A question mark followed by a number NNN for storing the NNN
A parameter. The NNN must be between 1 and 999.
? There is only one question mark, which will be used to store an unused parameter immediately following it.

: AAAA colon followed by a marker for holding the name AAAA
The variable. The named variable will also be numbered, and the number will use the first unused number. To avoid confusion, it is best not to mix names and numbers as arguments.
@AAAA a @
The symbol is equivalent to a colon.
$AAAA dollar sign followed by a marker will also hold the name AAAA
The parameters. In this case, a lot of "::" can appear in the parameter name, and, in the "(...)" The suffix can contain any text. The syntax is by TCL
The same law in the language.

Arguments that do not use Sqlite3_bind to assign values are considered NULL.

Like
The operator makes a pattern matching comparison. The right side is a matching pattern, and the left side contains the matched string. In match mode, percent percent will match any 0 or more characters in a string. An underscore _
The symbol matches only one arbitrary character. In addition, any other word nonspacing only matches themselves (in a case-insensitive environment, it may match the corresponding large and lowercase letters). (a bug:sqlite is case insensitive only to
The 7-bit Latin character is valid, and the UTF8 character for the 8-bit iso8859 character set is case-sensitive. For example: expression ' a ' like ' a ' is TRUE but ' æ '
The like ' Æ ' is FALSE).

If there is an optional escape clause, escape
The expression after the keyword must be a single character (called an escape character). This character can be used for like
The pattern string to reflect the percent sign or underscore. The semicolon or underscore after the escape character retains their original meaning. The LIKE operator of Infix is the use of like (x, y)
function is implemented.
The LIKE operator is case-insensitive, and it matches a string that is lowercase on one side and uppercase on the other. (a bug:sqlite is case insensitive only to 7
The Latin character of the bit is valid and is case-sensitive for the 8-bit iso8859 character Set UTF8 character). For example: expression ' a ' like ' a ' is TRUE but ' æ '
The like ' Æ ' is FALSE).

The infix operator like is implemented by invoking the user function like (x, y). But if there's an ESCAPE back there,
clause, which adds a third parameter to the above function. If the function is overloaded with another like () SQL function, you need to be aware of it.

GLOB operators and like
Similar, but it uses Unix wildcard characters for file matching syntax. Also, unlike like, GLOB is case-sensitive. GLOB and like can all use not
Keyword to reverse the match result. The GLOB operator of Infix is implemented by invoking the user function GLOB (x, y), which can be overloaded by this function.

Regexp
An operator is a special syntax that uses the regexp () user function. By default, the RegExp () user function is undefined, so use regexp
There will be an error. If you add a user-defined function named RegExp at run time, this function will be used to implement the REGEXP function when you use the operator.

MATCH
The operator is a special syntax for using the match () user function. The default match ()
The function implementation throws only one exception, which is not much use. However, you can implement more useful logic by extending the function.

The column name Colum name can be anything in the CREATE
The column name specified in the TABLE statement, or it can be a special marker: "ROWID", "OID", or
"_rowid_". These special flags nonspacing describe the only integer key that is connected to each row of each table. Special markers will only be in the CREATE TABLE
Statement does not really refer to each row until the same column name is specified. The row key value is like a read-only column. A row key value can be used wherever a normal column can be used. Just, you can't use UPDATE or INSERT
To change the key value of the row. "SELECT * ..." does not return a row key value.

The SELECT statement can appear in the
After the operator, or as a single quantity value, or following the EXISTS operator. SELECT must return only a single column when it is a single quantity value or in an in operator. Composite
SELECT queries (queries that use the UNION or EXCEPT keyword) are also allowed. When you use the EXISTS keyword, the columns in the SELECT result are ignored. If
SELECT returns one or more rows, the expression is TRUE, and FALSE. If an item in an inner select expression is independent of the value of the outer layer, the inner select
The value is evaluated first and reused later if needed. If the inner select contains variables related to the outer query, then the inner select
will be re-evaluated every time it is needed.

When a select is the right operand of the in operator, if the operand to the left of the in is present on the right SELECT
In the query result, in returns TRUE. The in operator can also be paired with not to negate the result of an expression.

When a SELECT appears in the except in
, the first row of the SELECT result is used as a single value in the expression. If the Select returns multiple rows, the other rows are ignored. If SELECT
Returns 0 rows, the result will be NULL.

A cast expression converts the data type to the specified type
<type>. Where <type> can be anything in the CREATE TABLE
A valid non-null type name in the statement.

The system supports simple and aggregate functions. A simple function can be used with any expression. The simple function returns the result immediately on the basis of its input. Aggregation functions can only be used for
The SELECT statement. The aggregate function calculates the cross-row on the returned result set and returns the corresponding result.
Core function cores
Functions

The following are the core functions that are available by default. Additional functions can be written using the C language and use the sqlite3_create_function () API
Join the database engine.
ABS (x) returns the absolute value of the parameter x.
COALESCE (x, y,...) Returns the first non-NULL in a parameter
The value. If all parameters are NULL, NULL is returned. The function requires a minimum of two parameters.
Glob (x, y) This function user implements SQLite's "X glob
Y "syntax. You can use the Sqlite3_create_function () interface to overload the function, which alters the way the GLOB operator behaves.
Ifnull (x, y)
Returns the first non-null parameter value, or null if all parameters are null. Same as the above coalesce ().
Hex (X)
Parameters are treated as blobs. The result is a 16-binary representation of the BLOB content.
Last_insert_rowid () returns the last row inserted in the current database connection.
ROWID. The value is the same as the value returned using the Sqlite_last_insert_rowid () API function.
Length (x) returns X
The length of the string. If SQLite is configured to support UTF-8, the UTF-8 is returned
The length of the character, not the number of bytes.
Like (x, y)
Like (x, Y, z) This function is used to implement the SQL "× like Y [ESCAPE
Z] "syntax. If there is an optional ESCAPE statement, a function with three parameters will be used, otherwise data with only two parameters will be used. can use
Sqlite_create_function () interface to overload the function to change the like
The behavior of the operator. If you do that, be sure to pay attention to the function that overloads the version of two (two parameters and three arguments) at the same time. Otherwise, depending on whether the ESCAPE is used
clause, you can invoke a different code.
Load_extension (X)
Load_extension (x, y) loading SQLite
An extension library outside the shared library that has a file name of X and an entry point of Y. The result will be NULL. If Y is omitted, the default entry point is used
Sqlite3_extension_init. This function throws an exception if the load or initialization fails.

If the extension attempts to modify or delete a SQL
function or a control sequence, the function fails. You can use extensions to add new functions or control sequences, but you cannot modify or delete existing. This is because those functions and/or control sequences may be being used by other SQL statements. To load an extension that can modify or delete a function or a control sequence, use the
Sqlite3_load_extension () C language API.
Lower (x) returns the string after the string X is converted to lowercase. The function uses the C
The Language library function ToLower () is converted, so the UTF-8 character may not be converted correctly.
LTrim (X)
LTrim (x, y)
Returns a string that is the remaining string that is removed from the left side of the string X after any characters that exist in the string Y. If you omit the parameter Y
, the left space is removed.
Max (x, y,...) Returns the maximum value of the parameter. In addition to numbers, parameters may be strings. It uses the usual sort order to determine the maximum value. Note If
Max () has two or more parameters, it is a simple function. But if only one argument is supplied, it becomes a clustered function.
Min (x, y,...)
Returns the minimum value of the parameter. In addition to numbers, parameters may be strings. It uses the usual sort order to determine the minimum value. Note If min ()
has two or more parameters, it is a simple function. But if only one argument is supplied, it becomes a clustered function.
Nullif (x, y)
If the arguments are different, the first argument is returned, otherwise null is returned.
QUOTE (X) This function returns a parameter value that is appropriate for inclusion in other SQL
The string in the statement. The strings included in single quotes will be converted to the internal reference format as needed. The BLOB will be encoded as a hexadecimal literal. The current implementation of VACUUM uses this function. In writing triggers to implement
This function is also useful when the Undo/Redo feature is available.
Random (*) returns a pseudo-random number. Results in-9223372036854775808 and
Between +9223372036854775807.
Replace (x, y, z) returns a position that replaces each occurrence of Y in the X string with the Z
After the string. It is compared using binary control sequences.
RANDOMBLOB (n) returns an N-byte-long BLOG containing pseudo-random bytes. N
Should be a positive integer.
Round (X)
Round (x, y) rounds the X circle to the Y-bit after the decimal point. or omit Y, the default y is
0.
RTrim (X)
RTrim (x, Y) returns the string that is removed from the right side of X after all occurrences of the character in Y. If omitted
Y, the space is removed.
SOUNDEX (x) calculates the phonetic encoding of the string x. If the argument is NULL, return "? 000". SQLite By default
Ignore the function. Valid only if-dsqlite_soundex=1 is specified at compile time.
Sqlite_version (*) returns the currently running SQLite
The version number of the library. such as: "2.8.0"
substr (x, Y, z)
substr (x, Y) returns the string X starting with the first Y character and the length Z
The string. If Z is omitted, a string is returned until the end of the string. The first character of X starts with 1. If Y is negative, the number starts from the right. If X is UTF-8
String, then the subscript value will refer to the actual UTF-8 characters character, not the byte. If X is a
BLOB, then the subscript refers to the byte.
Trim (X)
Trim (x, Y) returns the string that is removed from both ends of the string X after all characters that exist in Y. If omitted
Y, then go to the space.
typeof (X) returns the type of an expression X. It can only be "null", "integer", "real", "text", and
"Blob". The type processing of SQLite is described in the datatypes in SQLite Version 3.
Upper (X) returns a string
The X is converted to a uppercase string. It is implemented using the C library function ToUpper (), and for UTF-8 some strings may not be converted correctly.
Zeroblob (N)
Returns a BLOB of N bytes long and all 0x00. SQLite or to organize these zeroblob very effectively. It can be used to reserve space for future use. can be used later
Incremental blob I/O to write BLOB data.
Date and Time functions

Date and time functions in SQLite
On the wiki.
Aggregation functions

There are the following tacit recognition and aggregation functions: You can use the sqlite3_create_function () API
To add other aggregation functions.

Any aggregation function has only one parameter. It can have a DISTINCT before its parameters. If so, the duplicate elements will be filtered out before they are passed to the aggregation function. For example, the function
COUNT (distinct X) will return only the total number of rows that are not duplicated on the X column, not the total number of all non-empty rows on that column
AVG (x) returns X
The average of all non-null values in a set of columns. Non-numeric values such as strings or blobs are considered to be 0. Even if all inputs are integers, avg ()
The result is always a floating-point number.
Count (X)
The first form of COUNT (*) returns
The total number of non-empty rows on the X column. The second (without any parameters) returns the number of rows in a group.
Group_concat (X)
Group_concat (x, y)
The result is a string of all non-null X joins. If there is a parameter Y, it will be the delimiter when you join X. If y is omitted, the default is a comma (,).
Max (X)
Returns the maximum value in a group. Use the usual sort order to determine the maximum value.
Min (X)
Returns the minimum value except for a null value in a set. Use the usual sort order to do the minimum value. Returns only if all values in the entire group are NULL
Null.
SUM (X)
Total (X) returns the sum of all the non-null values in a set. If there is no non-null value in the input, then sum () returns NULL and
Total () returns 0.0. Null can give helpful results not only when sum () does not sum any rows, but it is also required by the SQL standard. and the other SQL
This is how the database engine is implemented. SQLite does this to maintain compatibility. We also provide a non-standard total () function to provide a convenient way to bypass SQL
This design problem of language.

The result of total () is always a floating-point number. If all non-null inputs are integers, the result of sum () is an integer value. If any input is given to sum ()
is a value other than an integer and NULL, and sum () returns the floating-point number. This is probably the closest way to the standard sum ().

If the input is all integers or
NULL, SUM () will produce an "integer overflow" exception when the result overflows. and total () never will.


Limit number of Pens
SELECT * from film limit 10;
Sqlite3 film.db "SELECT * FROM
Film; "

Output HTML table:

sqlite3-html film.db "SELECT * FROM
Film; "

To "pour out" the database:

Sqlite3 film.db ". Dump" >
Output.sql

Using the output data, create an identical database (plus the above directive, which is the standard SQL database backup):

Sqlite3
Film.db <
Output.sql

When inserting a large amount of data, you may need to call this command first:

Begin

After inserting the data, remember to call this command, the data will be written into the database:

Commit


SQLite built-in function table
Arithmetic functions
ABS (X)
Returns the absolute value of the given numeric expression.
Max (x,y[,...]) Returns the maximum value of an expression.
Min (x,y[,...])
Returns the minimum value of an expression.
Random (*) returns the number.
Round (X[,y])
Returns a numeric expression that is rounded to the specified length or precision.
Character processing functions
Length (X) returns the number of characters for the given string expression.
Lower (X)
Returns a character expression after the uppercase character data is converted to lowercase characters.
Upper (X)
Returns a character expression that converts lowercase character data to uppercase.
substr (x, y, z) returns part of an expression.
Randstr ()

QUOTE (A)
A like (A, B) determines whether the given string matches the specified pattern.
Glob (A, B)

Conditional Judgment function
COALESCE (x,y[,...])
Ifnull (x, y)
Nullif (x, y)

Aggregate functions
AVG (X) returns the average of the values in the group.
COUNT (X) returns the number of items in the group.
Max (X)
Returns the maximum value of a value in a group.
MIN (X) returns the minimum value of the value in the group.
SUM (X)
Returns the and of all values in an expression.
Other functions
typeof (X) returns the type of data.
Last_insert_rowid ()
Returns the ID of the last inserted data.
Sqlite_version (*) returns the version of SQLite.
Change_count ()
Returns the number of rows affected by the previous statement.
Last_statement_change_count ()