SQLite Comprehensive Learning (i)

PS One sentence: Eventually choose Csdn to organize the publication of the knowledge points of these years, the article parallel migration to CSDN. Because CSDN also support markdown grammar, Ah!

"Artisan Joshui Http://blog.csdn.net/yanbober"

SQLite is a lightweight database, a relational database (RDBMS) management system, which is included in a relatively small C library. Currently used in many embedded products, it occupies a very low resource, in the embedded device, may only need hundreds of K of memory is enough. It can support Windows/linux/unix/android/ios and so on mainstream operating system, and can be combined with many programming languages, such as TCL, C #, PHP, Java, and ODBC interface, also compared to MySQL, PostgreSQL, the two open source world-renowned database management system, is processing faster than they do.

sqlite Command Categories:

(DDL) Data definition language:

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

(DML) Data manipulation language:

CMD	Description
INSERT	Create a record.
UPDATE	Modify the record.
DELETE	Deletes a record.

(DQL) Data Query Language:

CMD	Description
SELECT	Retrieve some records from one or more tables.

SQLite Point Command

Let's look at one of the following windows:

This is the run sqlite3 command under Windows cmd (how to install and configure please Google | Baidu), and then run the. Help print (only part) as prompted.

You can find that SQLite help lists all SQLite-supported point commands, and you can also find that the point command does not need to have ";" End.

We translate the above. Help command roughly as follows:

CMD	Description
.backup ?DB? FILE	Back up the DB database (the default is "main") to the file.
.bail ON/OFF	Stop after an error occurs. The default is off.
.databases	Lists the name and file of the attached database.
.dump ?TABLE?	Dumps the database in SQL text format. If table tables are specified, only table tables matching the like pattern are dumped.
.echo ON/OFF	Turns the echo command on or off.
.exit	Exit the SQLite prompt.
.explain ON/OFF	Turn on or off the output mode that is appropriate for the explain. If there is no parameter, then explain on, and explain on.
.header(s) ON/OFF	Turns the head display on or off.
.help	Displays the message.
.import FILE TABLE	Import data from file files into table tables.
.indices ?TABLE?	Displays the names of all indexes. If table tables are specified, only the indexes of table tables that match the like pattern are displayed.
.load FILE ?ENTRY?	Load an extension library.
.log FILE/off	Turn the log on or off. File files can be stderr (standard error)/stdout (standard output).
.mode MODE	Set the output mode, mode can be one of the following: CSV comma separated values, column left-aligned columns, HTML HTML <table> code, Insert Table table SQL Insert (INSERT) statement, line one value per row, list by. Separator A string-delimited value; tabs a tab-delimited value; Tcl tcl list element.
.nullvalue STRING	Outputs a string string in place of a null value.
.output FILENAME	Send output to filename file.
.output stdout	Send output to screen.
.print STRING...	Outputs a string literal, literally.
.prompt MAIN CONTINUE	Replace the standard prompt.
.quit	Exit the SQLite prompt.
.read FILENAME	Executes the SQL in the filename file.
.schema ?TABLE?	Displays the CREATE statement. If table tables are specified, only table tables matching the like pattern are displayed.
.separator STRING	Change the delimiter used by the output mode and. Import.
.show	Displays the current values for the various settings.
.stats ON/OFF	Turn statistics on or off.
.tables ?PATTERN?	Lists the names of the tables that match the like pattern.
.timeout MS	Try to open the locked table for Ms microseconds.
.width NUM NUM	Sets the column width for the "column" mode.
.timer ON/OFF	Turn on or off the CPU timer measurement.

sqlite_master table for SQLite database

The primary table holds the key information for the database table and names it as Sqlite_master. To view a table summary, you can do the following:

sqlite>.schema sqlite_master

SQLite Grammar Rules

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

SQLite is case-insensitive, but some commands are case-sensitive, such as Glob and glob have different meanings in SQLite's statements.

SQLite annotations are additional annotations that you can add to your SQLite code to make them more readable, and they can appear in any space, including in the middle of an expression and other SQL statements, but they cannot be nested.

The SQL comment starts with two consecutive "-" characters and expands to the next line break or until the input ends, whichever is first. You can also /*" start with and extend to the next */ character pair or until the input ends, whichever is first. SQLite annotations can span multiple lines.

SQLite statements begin with any keyword, with ";" End.

SQLite data Types

The SQLite data type is a property that specifies the data type of any object. Each column in SQLite has an associated data type for each variable and expression. You can use these data types while creating a table. SQLite uses a more general dynamic type System. In SQLite, the data type of a value is related to the value itself, not to its container.

Storage Class

SQLite has 5 primitive data types, called storage classes. The term storage class indicates that a value is stored on disk in a format that is actually a synonym for a type or data type. The following is the storage class:

Storage Class	Description
Null	Value is a null value.
INTEGER	The value is a signed integer that is stored in 1, 2, 3, 4, 6, or 8 bytes, depending on the size of the value.
REAL	The value is a floating-point value that is stored as a 8-byte IEEE floating-point number.
TEXT	The value is a text string that is stored using the database encoding (UTF-8, utf-16be, or Utf-16le).
Blob	The value is a blob of data that is stored entirely according to its input.

SQLite uses the notation of value to determine its type, and here is the method of SQLite's reasoning:

• text in an SQL statement enclosed in single or double quotation marks is assigned text.
• If the text is data that is not enclosed in quotation marks and has no decimal point and exponent, it is assigned an integer.
• If the text is data that is not enclosed in quotation marks and has a decimal point or exponent, it is assigned to real.
• A null-stated value is assigned as a null storage class.
• If the format of a value is X ' ABCD ', where ABCD is a 16 binary number, then the value is assigned as a blob. The x prefix is case-sensitive.

The following is the verification result:

SQLite a single field may contain values for different storage classes.

The following is the verification result:

Like to be in the dead of this time to specify the beginning BB, temp This column data type is different, how to compare size? How about sorting, etc.?

The lookup data found that values with different storage classes can be stored in the same field. Can be sorted, because these values can be compared to each other. There are well-defined rules to do this thing. The values of different storage classes can be sorted by their class values in their respective classes, defined as follows:

• A null storage class has the lowest class value. A value with a null storage class is smaller than all other values (including other values that have a null storage class). There are no special sortable values between null values.
• Integer or real storage class values are higher than null, and their class values are equal. The integer value and the real value are compared by their values.
• The value of the text storage class is higher than integer and real. Values are always lower than the value of the string. When two text values are compared, the value size is determined by the sort method.
• Blob storage classes have the highest class values. The value with the Blob class is greater than the value of all other classes. The BLOB values are compared using the C function memcmp ().

So, when SQLite sorts a field, it first sorts by storage class, then sorts within the class (the values inside the null class do not have 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 3 fields of x, Y, and z Here are stored in integer, text, and real types.

Take another look at the following example:

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

The 3 fields of x, Y, and Z are stored in the text, text, and text types.

Take another look at the following example:

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

The 3 fields of x, Y, and z Here are stored in integer, real, and blob types.

You will find that you can define types for sqlite fields, but this is not required and you can violate the type definition. This is because, in any case, SQLite can accept a value and infer its type.

In summary, the weak type of sqlite can be expressed as:

1. Fields can have types.
2. Types can be inferred by value.

Type affinity describes how these two rules relate to one another. The so-called type affinity is the art of balancing between strongly typed (strict typing) and dynamic types (typing).

type affinity (type Affinity)

In SQLite, a field has no type or field. When a type is declared for a field, the field actually has only the affinity of that type. declaring type and type affinity are two different things. Type affinity book What storage classes SQLite uses to store values in a field. The combination of the storage class and the field affinity that SQLite will use in this field when storing a given value is determined by the storage class of the value.

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

Affinity	Description
TEXT	The column stores all data using the storage class null, text, or BLOB.
NUMERIC	The column can contain values that use all five storage classes.
INTEGER	Same as the column with numeric affinity, with an exception in the cast expression.
REAL	Similar to a column with numeric affinity, the difference is that it forces an integer value to be converted to a floating point representation.
NONE	Columns with affinity none, which storage class is not preferred, nor does it attempt to cast data from one storage class to another.

The following table lists the various data type names that can be used when creating the SQLite3 table, and also shows the appropriate 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, and 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 storing dates and/or times, but SQLite can store dates and times as text, real, or integer values. You can store dates and times in any of the above formats, and you can use the built-in date and time functions to freely convert different formats.

Storage Class	Date Format
TEXT	The format is "Yyyy-mm-dd HH:MM:SS." SSS "date.
REAL	The number of days starting from midday in November 24, 4714 GMT.
INTEGER	The number of seconds from 1970-01-01 00:00:00 UTC.

field type and affinity

First of all, each field has a kind of affinity. There are five types of affinity: NUMERIC, INTEGER, REAL, text, and none. The affinity of a field is determined by its pre-declared type. So, when you declare a type for a field, you are essentially specifying affinity for the field. SQLite assigns affinity to fields according to the following rules:

• By default, the affinity of a field by default is numeric. If a field is not integer, TEXT, Real, or none, it is automatically assigned as numeric affinity.
• If the type declared for the field contains ' INT ' (regardless of case), the field is assigned integer affinity.
• If the type declared for a field contains ' CHAR ', ' CLOB ', or ' text ' (regardless of case), the field is assigned TEXT affinity. If ' VARCHAR ' contains ' CHAR ', it is assigned the text affinity.
• If the type declared for a field contains a ' BLOB ' (regardless of case), or if no type is declared for the field, the field is assigned the none affinity.

Note: If no type is declared for a field, the affinity of the field is none, in which case all values will be stored in their own (or inferred from their notation) storage class. If you are not sure what to put in a field, or if you are ready to change it in the future, it is a good choice to use none affinity. However, the default affinity of SQLite is numeric. For example, if type Jujyfruit is declared for a certain field, the affinity of the field is not none, because SQLite does not recognize this type and assigns it the default numeric affinity. So, instead of numeric affinity with a type you don't know, it's better not to specify a type for it, so that it gets none affinity.

Affinity and Storage

Affinity affects how values are stored in fields, and the rules are as follows:

• A numeric field may include all 5 storage classes. A numeric field has a preference for digital storage classes (integer and real). When a text value is inserted into an numeric field, it will attempt to convert it into an integer storage class, and if the conversion fails, it will attempt to convert it into a real storage class, and if it fails, it will be stored with the text storage class.
• An integer field is handled much like the numeric field. An integer field stores the real value as a real storage class. That is, if the real value does not have a decimal part, it is converted to an integer storage class. The integer field will try to store the text value as real, and if the conversion fails, it will attempt to convert it to an integer storage class, and if it fails, it will be stored with the text storage class.
• A text field will convert all of the integer or real values to text.
• A none field does not attempt to do any type conversions. All values are stored by their own storage class.
• No fields attempt to convert to null or BLOB values-for example, whatever affinity is used. Null and BLOB values are always stored in all fields as expected.

These rules look more complex at first, but the overall design goal is simple, and if you want, SQLite will try to imitate other relational databases. That is, if you consider SQLite as a traditional database, type affinity will store values as you expect. If you declare an integer field and place a whole number inside it, it is stored as an integer. If you declare a field with a text, char, or varchar type and place an integer in it, the integer will be converted to text. However, if you do not follow these rules, SQLite will find a way to store your values.

The following examples show how affinity works:

Storage classes and type conversions

Another thing to focus on about storage classes is that storage classes sometimes affect how values are compared. In particular, SQLite sometimes converts a value between a numeric storage class (integer and Real) and text before it is compared. To make a binary comparison, follow these rules:

• When a field value is compared to the result of an expression, the affinity of the field is applied to the result of the expression before the comparison.
• When two field values are compared, if one field has integer or numeric affinity and the other does not, numeric affinity is applied to the text value of the non-numeric field.
• When two expressions are compared, SQLite does not make any conversions. If two expressions have similar storage classes, they are compared directly by their values, otherwise compared by class values.

Summary

This article mainly introduces some basic concepts of sqlite and the characteristics of data types. For other content on SQLite the next article continues with the introduction.

"Artisan Joshui Http://blog.csdn.net/yanbober"

SQLite Comprehensive Learning (i)