Comprehensive Sqlite Learning (1), sqlite Learning

Comprehensive Sqlite Learning (1), sqlite Learning

Finally, CSDN was chosen to sort out the knowledge points published over the past few years. This article was migrated to CSDN in parallel. Because CSDN also supports the MarkDown syntax, it's awesome!

[Craftsman's water: http://blog.csdn.net/yanbober]

SQLite is a lightweight database and a relational database management system (RDBMS). It is included in a relatively small C library. Currently, it is used in many embedded products, and it occupies very low resources. In embedded devices, it may only need several hundred KB of memory. It supports mainstream operating systems such as Windows, Linux, Unix, Android, and IOS, and can be combined with many programming languages, such as Tcl, C #, PHP, and Java, there are also ODBC interfaces, which are faster than Mysql and PostgreSQL, the two world-renowned open-source database management systems.

Sqlite command classification:

(DDL) data definition language:

CMD	Description
CREATE	Create a new table, a table view, or other objects in the database.
ALTER	Modify an existing database object in the database, such as a table.
DROP	Delete the entire table, view of the table, or other objects in the database.

(DML) data operation language:

CMD	Description
INSERT	Create a record.
UPDATE	Modify records.
DELETE	Delete a record.

(DQL) Data Query Language:

CMD	Description
SELECT	Retrieves certain records from one or more tables.

Sqlite point command

First, let's look at a piece of Windows:

This is to run the sqlite3 command in Windows cmd (google | baidu is required for installation and configuration), and then run the print of. help (only part) as prompted ).

You can find that the help column of sqlite lists all the point commands supported by sqlite. You can also find that the point command does not need to end.

The. help Command is translated as follows:

CMD	Description
.backup ?DB? FILE	Back up the database (main by default) to the FILE.
.bail ON/OFF	Stop when an error occurs. The default value is OFF.
.databases	Lists the names and files of the attached database.
.dump ?TABLE?	Dump the database in SQL text format. If a TABLE is specified, only the LIKE-mode TABLE is dumped.
.echo ON/OFF	Enable or disable the echo command.
.exit	Exit the SQLite prompt.
.explain ON/OFF	Enable or disable the output mode suitable for EXPLAIN. If no parameter is provided, it is "EXPLAIN on" and "enable EXPLAIN.
.header(s) ON/OFF	Enable or disable the header display.
.help	Displays messages.
.import FILE TABLE	Import data from the FILE to the TABLE.
.indices ?TABLE?	Displays the names of all indexes. If a TABLE is specified, only the indexes of the TABLE that matches the LIKE pattern are displayed.
.load FILE ?ENTRY?	Load an extension library.
.log FILE/off	Enable or disable logging. The FILE can be stderr (standard error)/stdout (standard output ).
.mode MODE	Set the output MODE. The MODE can be one of the following: values separated by commas (,); left-aligned column of column; html<table>Code; SQL insert statement in the insert TABLE; line has one value per line; list is the value separated by the. separator string; tabs is the value separated by the Tab; tcl list element.
.nullvalue STRING	Output a string in the NULL value.
.output FILENAME	Send the output to the FILENAME file.
.output stdout	Send the output to the screen.
.print STRING...	Returns a STRING.
.prompt MAIN CONTINUE	Replace the standard prompt.
.quit	Exit the SQLite prompt.
.read FILENAME	Run the SQL statement in the FILENAME file.
.schema ?TABLE?	Displays the CREATE statement. If a TABLE is specified, only tables matching the LIKE mode are displayed.
.separator STRING	Change the delimiter used by the output mode and. import.
.show	Displays the current values of various settings.
.stats ON/OFF	Enable or disable statistics.
.tables ?PATTERN?	Lists the names of tables that match the LIKE pattern.
.timeout MS	Try to open the locked table in milliseconds.
.width NUM NUM	Set the column width for "column" mode.
.timer ON/OFF	Enable or disable the CPU timer measurement.

Sqlite_master table of Sqlite Database

The master table stores the key information of the database table and name it sqlite_master. To view the table Summary, perform the following operations:

sqlite>.schema sqlite_master

Sqlite syntax rules

SQLite follows a unique set of rules and guidelines called syntax.

SQLite is case-insensitive, but some commands are case-sensitive. For example, GLOB and glob have different meanings in SQLite statements.

SQLite comments are additional comments that can be added to the SQLite code for readability. They can appear in any blank space, including in the expression and in the middle of other SQL statements, but they cannot be nested.

The SQL comment starts with two consecutive "-" characters and extends to the next line break or ends until the input ends, whichever comes first. You can also use"/*"Start and extend to the next"*/"Character pair or until the input ends, whichever comes first. SQLite annotations can span multiple rows.

The SQLite statement starts with any keyword and ends.

Sqlite Data Type

SQLite data type is an attribute used to specify the Data Type of any object. Each column in SQLite has a data type related to each variable and expression. You can use these data types when creating tables. SQLite uses a more general dynamic type system. In SQLite, the Data Type of a value is related to the value itself, rather than its container.

Storage Class

SQLite has five original data types, called storage classes. The storage vocabulary defines the format of a value stored on a disk. It is actually a synonym of the type or data type. The storage class is as follows:

Storage Class	Description
NULL	The value is a NULL value.
INTEGER	A value is a signed integer stored in 1, 2, 3, 4, 6, or 8 bytes based on the value size.
REAL	The value is a floating point value, which is an 8-byte IEEE floating point number.
TEXT	A value is a text string that is stored using database encoding (UTF-8, UTF-16BE, or UTF-16LE.
BLOB	A value is a blob of data, which is stored completely according to its input.

SQLite uses the value representation to determine its type. The following is the reasoning method of SQLite:

• TEXT enclosed in single or double quotation marks in SQL statements is assigned as TEXT.
• If the text is enclosed by no quotation marks and there is no decimal point or index, it is assigned as an INTEGER.
• If the text is data enclosed with no quotation marks and has a decimal point or index, it is assigned as REAL.
• The value specified with NULL is assigned as a NULL storage class.
• If the format of a value is X 'abcd', where ABCD is a hexadecimal number, the value is assigned as BLOB. The X prefix is case sensitive.

The verification result is as follows:

A separate field of SQLite may contain values of different storage classes.

The verification result is as follows:

When I like to drill the horns, I specify the starting BB. The data type of the temp column is different. How can I compare the size? What sort?

After reading the data, we found that values with different storage classes can be stored in the same field. Values can be sorted because they can be compared with each other. There are well-defined rules to do this. The values of different storage classes can be sorted by the "class values" of their respective classes, which are defined as follows:

• The NULL storage class has the lowest class value. A value with a NULL storage class is smaller than all other values (including other values with a NULL storage class ). There is no special sortedvalue between NULL values.
• INTEGER or REAL storage class values are greater than NULL, and their class values are equal. The INTEGER value and the REAL value are compared by their numerical values.
• The value of the TEXT storage class is higher than that of INTEGER and REAL. The value is always lower than the string value. When two TEXT values are compared, the value size is determined by the "sort method.
• BLOB storage class has the highest class value. The values of BLOB classes are greater than those of all other classes. Use the C function memcmp () when comparing BLOB values ().

Therefore, when SQLite sorts a field, it first sorts the field by storage class, and then sorts the values in the class (the values in the NULL class do not need to be sorted ).

Weak type (manifest typing)

First, there are the following SQL statements:

CREATE TABLE table_yanbo( x integer, y text, z real );INSERT INTO table_yanbo VALUES ('1', '1', '1');

The x, y, and z fields store the INTEGER, TEXT, and REAL types.

Let's look at the following example:

CREATE TABLE table_yanbo(x, y, z);INSERT INTO table_yanbo VALUES ('1', '1', '1');

The three fields x, y, and z Here store the TEXT, TEXT, and TEXT types.

Let's look at the following example:

CREATE TABLE table_yanbo(x, y, z);INSERT INTO table_yanbo VALUES (1, 1.0, x'10');

The x, y, and z fields store the INTEGER, REAL, And BLOB types.

You can use the preceding methods to define the type for SQLite fields. However, this is not necessary. You can violate the type definition. This is because SQLite can accept a value and infer its type in any case.

In short, the weak type of SQLite can be expressed:

Type affinity describes how these two rules are correlated. The so-called type affinity is the balance between strict typing and dynamic typing.

Type Affinity)

In SQLite, the field has no type or field. When a field is declared as a type, the field actually only has the affinity of this type. Declaring type and type affinity are two different things. Type affinity predefines the storage class used by SQLite to store values in fields. When a given value is stored, What storage class will SQLite use in this field determines the combination of the storage class of the value and the field affinity.

Any column can store any type of data, but its preferred storage class is its affinity. In the SQLite3 database, columns in each table are assigned one of the following types of affinity:

Affinity	Description
TEXT	This column uses the storage class NULL, TEXT, or BLOB to store all data.
NUMERIC	This column can contain values of all five storage classes.
INTEGER	It is the same as a column with NUMERIC affinity and has an exception in the CAST expression.
REAL	Similar to a column with NUMERIC affinity, it forcibly converts an integer to a floating point.
NONE	Columns with affinity NONE do not give priority to the storage class or forcibly convert data from one storage class to another.

The following table lists the names of various data types that can be used to create a SQLite3 table, and displays the corresponding application Affinity:

Data Type	Affinity
INT, NTEGER, TINYINT, SMALLINT, MEDIUMINT, BIGINT, unsigned big int, INT2, INT8	INTEGER
CHARACTER (20), VARCHAR (255), varying character (255), NCHAR (55), native character (70), NVARCHAR (100), TEXT, CLOB	TEXT
BLOB, no datatype specified	NONE
REAL, DOUBLE, double precision, FLOAT	REAL
NUMERIC, DECIMAL (10, 5), BOOLEAN, DATE, DATETIME	NUMERIC

Boolean data type

SQLite does not have a separate Boolean storage class. Boolean values are stored as integers 0 (false) and 1 (true ).

Date and Time Data Types

SQLite does not have a separate storage class for date and/or time, But SQLite can store the date and time as TEXT, REAL, or INTEGER values. You can store the date and time in any of the above formats, and use the built-in date and time functions to convert different formats freely.

Storage Class	Date Format
TEXT	The date in the format of "YYYY-MM-DD HH: MM: SS. SSS.
REAL	The number of days counted from noon on January 1, November 24, 4714 BC.
INTEGER	The number of seconds from 00:00:00 UTC.

Field Type and affinity

Each field has an affinity. There are five affinity types: NUMERIC, INTEGER, REAL, TEXT, and NONE. The affinity of a field is determined by its pre-declared type. Therefore, when you declare a type for a field, it basically specifies the affinity for the field. SQLite:

• By default, the default affinity of a field is NUMERIC. If a field is not INTEGER, TEXT, REAL, or NONE, it is automatically assigned as a NUMERIC affinity.
• If the type declared for the field contains 'int' (case-insensitive), this field is assigned an INTEGER affinity.
• If the declared type of a field contains 'Char ', 'clob', or 'text' (case-insensitive), the field is assigned a TEXT affinity. For example, 'varchar 'contains 'Char', so it is assigned as the TEXT affinity.
• If the declared field type contains 'blob '(case-insensitive) or the field is not declared as a type, the field is assigned a NONE affinity.

Note: If the field is not declared as a type, the affinity of the field is NONE. In this case, all values will be expressed as their own (or inferred from their representation) storage Class storage. If you are not sure what you want to add to a field, or you want to modify it in the future, using the NONE affinity is a good choice. However, the default affinity of SQLite is NUMERIC. For example, if JUJYFRUIT is declared for a certain field, the affinity of this field is not NONE, because SQLite does not know this type, it will be assigned a default NUMERIC affinity. Therefore, it is better not to specify a type for it to get the NUMERIC affinity when using an unknown type, so that it can get the NONE affinity.

Affinity and storage

The rules for how to store affinity values in fields are as follows:

• A NUMERIC field may include all five storage classes. A NUMERIC field has the preference of the Digital Storage Class (INTEGER and REAL ). When a TEXT value is inserted into a NUMERIC field, it will try to convert it into an INTEGER Storage Class; if the conversion fails, it will try to convert it into a REAL storage class; if it still fails, the TEXT storage class is used for storage.
• Processing an INTEGER field is similar to a NUMERIC field. An INTEGER field stores the REAL value according to the REAL storage class. That is to say, if the REAL value has no decimal part, it will be converted into an INTEGER storage class. The INTEGER field will try to store the TEXT value by REAL; if the conversion fails, it will try to convert it into an INTEGER Storage Class; if the conversion still fails, it will be stored using the TEXT storage class.
• A text field converts all INTEGER or REAL values to TEXT.
• A none field does not attempt to convert any type. All values are stored based on their own storage class.
• No field attempts to convert to NULL or BLOB value-for example, whatever affinity is used. NULL and BLOB values are always stored in all fields in the original way.

These rules seem complicated at the beginning, but the overall design goal is very simple. If you need them, SQLite will try its best to imitate other relational databases. That is to say, if you think of SQLite as a traditional database, type affinity will store values as expected. If you declare an INTEGER field and place an INTEGER in it, it is stored as an INTEGER. If you declare a field of the TEXT, CHAR, or VARCHAR type and place an integer in it, the integer is converted to TEXT. However, if you do not comply with these rules, SQLite will also find a way to store your values.

The following example shows how affinity works:

Storage Class and type conversion

Another thing to note about storage classes is that storage classes sometimes affect how values are compared. In particular, SQLite sometimes converts values between the numeric Storage Class (INTEGER and REAL) and TEXT before comparison. For binary comparison, follow the following rules:

• When a field value is compared with the result of an expression, the affinity of the field will be applied to the result of the expression before comparison.
• When two field values are compared, if one field has an INTEGER or NUMERIC affinity and the other does not, the NUMERIC affinity is applied to the TEXT value of a non-NUMERIC field.
• When two expressions are compared, SQLite does not perform any conversion. If two expressions have similar storage classes, compare them directly based on their values; otherwise, compare them based on class values.

Summary

This section describes some basic concepts and features of Sqlite. For more information about Sqlite, see the following article. Continue to read the next article Sqlite comprehensive learning (II).

[Craftsman's water: http://blog.csdn.net/yanbober]