Introduction to SQLite

Common relational databases include SQLite, MySQL, SQL Server, etc. Usually, SQLite is not used when learning relational databases, but SQLite is the most widely used database engine in the world. SQLite is built into all phones and most computers, and bundled with countless other applications that people use every day. SQLite is an embedded library developed by C voice, which is small, fast, self-contained, highly reliable, and fully functional.

The article takes the 3.32.2 version as an example to describe some basic knowledge of SQLite

SQLite common commands and examples

To use the SQLite command on the command line, you need to execute the sqlite3 command and enter SQLite提示符 .

Create or open a database

There are two ways to create or open a database, one is to add the database path after the sqlite3 command, and the other is to use the dot command .open

sqlite3/.../xxx.db method

When entering SQLite提示符 by executing this command, if the database file already exists, the corresponding database will be opened directly, otherwise the xxx.db file will not be created in the corresponding path immediately. Wait until the command to add database objects such as data tables, views, etc. is executed.

Example
Execute the following command first, the comms_ease.db file is not created at this time

 sqlite3 comms_ease.db

Then execute the following command to create a table, which appears in the current directory comms_ease.db file

.open/.../xxx.db method

Use .open is a dot command, to use it, you need to execute the sqlite3 command to enter SQLite提示符 . .open The way to use the command is to append the database path after the command, but sqlite3 /.../xxx.db法 6c53a982e1aaccb2b605279e90e3ac3b---, the database file will be created directly after executing the .open command , no need to create database objects again.

create table

The SQLite creation statement is CREATE TABLE . The complete table creation statement is rich in content. In addition to creating ordinary tables, it can also have the ability to determine whether the table already exists and create temporary tables. The common syntax for creating a normal table is.

 CREATE TABLE 表名 (
        列1名称 列类型 以空格隔开的一个或多个列约束, 
        列2名称 列类型 以空格隔开的一个或多个列约束,
        ...
);

By default, the maximum number of columns in a table is 2000, and the maximum number of bytes that can be stored in each row is one billion, which can meet the vast majority of needs. An example of creating a common table is as follows

 CREATE TABLE table_comms_ease (
    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    value VARCHAR
);

In the example, a data table named table_comms_ease is created, containing two columns, the first column is id , the type is integer, cannot be empty, and is the primary key of the table, and Can be automatically generated; the second column is value , the type is string

In the case that the table name already exists, calling the CREATE TABLE 表名 statement will report an error. To avoid it, you can use the CREATE TABLE IF NOT EXISTS 表名 statement. Create the table if it doesn't exist, and do nothing if it exists. Examples are as follows

 CREATE TABLE IF NOT EXISTS table_comms_ease (
    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    value VARCHAR
);

If you want to temporarily store some data, and only valid for the current connection, you can try a temporary table. The creation statement of the temporary table is CREATE TEMP TABLE . Temporary tables are only valid for the current database connection, and cannot be accessed by re-establishing a connection or other concurrent connections. Examples are as follows

 CREATE TEMP TABLE temp_table_comms_ease (
    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    value VARCHAR
);

Other common point commands

The dot command is SQLite a unique command form of the database. They are usually relatively simple and do not need to end with a semicolon. Common dot commands are shown in the following table

Order	describe	Example of use
.backup	Backup database to file	.backup comms_ease_backup
.databases	List the name of the database and the files it is attached to	.databases
.exit	Exit `SQLite提示符`	.exit
.help	help	.help
.quit	Exit `SQLite提示符`	.quit
.show	Values for various settings	.show
.schema	Check out the create command. Match parameters in the pattern of LIKE`, if there are no parameters, see all table creation commands	.schema .schema table%
.tables	Search table name. Match parameters in the pattern of LIKE`, if there are no parameters, search all table names (temp tables will not appear in the results)	.tables .tables table%

increase

Adding a row to the table constructs a value for each column and populates a new row. The command to add a row is INSERT , there are three ways to add, one is to add a specified value; the other is to add the result of the Select statement; the third is to add by default.

Adding a specified value is to specify the value of some or all columns, and the default value is used for the remaining columns. For columns that do not pass DEFAULTE , AUTOINCREMENT NOT NULL other methods to declare the default value, if not specified- NOT NULL Then fill in NULL , otherwise an error will be reported. At least one column of values needs to be specified . If you need to fill in all the default values, you can use the 默认添加 method. It should be noted that the order of the values must be consistent with the order of the columns. An example is as follows:
```
 INSERT INTO table_comms_ease (value) VALUES ('value of 1');
INSERT INTO table_comms_ease (value, id) VALUES ('value of 2', 2);
```
If you need to specify the values to be filled in all the columns, you can also not list the column names, but list the values in the order of the columns when the table was created. An example is as follows:
```
 INSERT INTO table_comms_ease VALUES (3, 'value of 3');
```
The format of adding a row by adding the result of the Select statement is INSERT INTO 表名 SELECT ... When adding a row by this method, the default value will not be automatically filled, and the amount of data detected by the SELECT statement must be consistent with the number of columns in the table. Suppose table already exists operator containing columns key and description and a row key is '.backup', description 备份数据库到文件 , the example is given as follows:
```
 INSERT INTO table_comms_ease SELECT  4, description FROM operator WHERE key IS '.backup';
```
The default added format is INSERT INTO 表名 DEFAULT VALUES; fill in the default value for each column, if there is no special declaration of the default value, fill in NULL. An example is as follows:
```
 INSERT INTO table_comms_ease DEFAULT VALUES;
```

Create a data table through the example statement in the Create Table chapter, and then call the above example statements in turn, there will be 5 rows of data in the data table, as follows

id	value
1	value of 1
2	value of 2
3	value of 3
4	Backup database to file
5

delete

Delete a row in the table, the command is DELETE , the common format is

 DELETE FROM 表名 WHERE 过滤语句

The delete command itself is relatively simple. You can delete a column by specifying the table name and delete conditions. The following example indicates that if the value in the value column starts with value of , it will be deleted.

 DELETE FROM table_comms_ease WHERE value LIKE 'value of %';

After the above command operation, table_comms_ease the remaining data in the table is

id	value
4	Backup database to file
5

change

The command to modify the data in the table is UPDATE , and its common format is

 UPDATE 表名 SET 一个或多个列的赋值 WHERE 过滤语句

When modifying the contents of multiple columns, you can write the column names first, and then assign values in order, or you can modify the contents one column at a time. If you want to update the id value of row 4 where, set id to 123 , value to new value , two kinds of modifications are as follows

 UPDATE table_comms_ease SET (id, value)= (123, 'new value') WHERE id = 4;

 UPDATE table_comms_ease SET id=123, value='new value' WHERE id = 4;

After modification, table_comms_ease the data in the table is

id	value
5
123	new value

The method of modifying a single column is similar to the method of modifying multiple columns. For example, changing id to ---3cbe4bf5bcf9c0d056eb0b993774c878 value in the row of 5 is also modified to new value , you can do as follows

 UPDATE table_comms_ease SET (value)=('new value') WHERE id = 5;

 UPDATE table_comms_ease SET value='new value' WHERE id = 5;

After modification, table_comms_ease the data in the table is

id	value
5	new value
123	new value

check

The command of the query statement is SELECT , which will not modify the database. The number of rows of the result is in the range of natural numbers, and each row represents a query result. SELECT The common format of the command is

 SELECT 去重策略 列名列表 FROM 表名或者子查询语句 WHERE 过滤语句 ORDER BY 排序策略 LIMIT 数量限制

There are many configurations available in the query statement, but most of them are not necessary. To query table_comms_ease all the contents of the table only need the following command

 SELECT * FROM table_comms_ease;

The * in the above command represents all columns, and the command is equivalent to

 SELECT id,value FROM table_comms_ease;

The result is

id	value
5	new value
123	new value

In addition, you can VALUES construct a statement query results, a column called the results column1 , column2 , column3 and so on. for example

 VALUES (1,2,3),('a','b','c');

The result is

column1	column2	Column3
1	2	3
a	b	c

deduplication strategy

There are two deduplication strategies, one is the default strategy ALL , which means no deduplication; the other is DISTINCT , which means deduplication. table_comms_ease the table value columns have the same value, when using the ALL and DISTINCT command to query the value column, respectively, the result is as follows:

ALL command

 SELECT ALL * FROM table_comms_ease;

The result is

value
new value
new value

DISTINCTCommand

 SELECT DISTINCT value FROM table_comms_ease;

The result is

value
new value

It can be seen that when there are duplicate results, the ALL strategy retains all results, while the DISTINCT strategy retains only one of them

table name or subquery

The FROM keyword of the query statement can be followed by a table name or a subquery statement to limit the scope of the query. When filling in the table name, you can fill in multiple table names, separated by commas or concatenation operators. When filling in a query statement, it can be regarded as querying a table first, and then querying data from this table.

Suppose there is also a table table_comms_ease_1 , the column information is the same as the table_comms_ease table, and the value is

id	value
1	value of 1 in table_comms_ease_1
2	value of 2 in table_comms_ease_1

Then fill in this field table_comms_ease,table_comms_ease_1 and get the following command

 SELECT * FROM table_comms_ease, table_comms_ease_1;

The result is

table_comms_ease.id	table_comms_ease.value	table_comms_ease_1.id	table_comms_ease_id.value
5	new value	1	value of 1 in table_comms_ease_1
5	new value	2	value of 2 in table_comms_ease_1
123	new value	1	value of 1 in table_comms_ease_1
123	new value	2	value of 2 in table_comms_ease_1

When this field is filled with two sub-query statements, such as one with id 5 and the other with id 123, the following command will be obtained

 SELECT * FROM (SELECT * FROM table_comms_ease WHERE iD=5), (SELECT * FROM table_comms_ease WHERE iD=123)；

The result is

table_comms_ease.id	table_comms_ease.value	table_comms_ease.id	table_comms_ease.value
5	new value	123	new value

Considering the result of the subquery statement as a table, the understanding of the two filling formats can be unified. In addition, the query command also supports mixed filling of table names and query statements.

Sort condition

The sorting condition determines the sorting order of the results. The common format is as follows

 ORDER BY 列名 COLLATE 比较方式 排序方式 NULL值的排序方式

There are three comparison methods, namely BINARY , NOCASE and RTRIM

BINARY: Compare byte by byte using the memcmp() function from the standard C library
NOCASE: First convert the uppercase letters in the ASCII code to lowercase letters, and then compare them according to the BINARY method
RTRIM: Compare in BINARY mode after removing the trailing spaces

Add a few pieces of data to the table table_comms_ease through the following command,

 INSERT INTO table_comms_ease VALUES (6, 'A'), (7, 'new value '), (8, 'Z');

The data in the table becomes

id	value
5	new value
6	A
7	new value
8	Z
123	new value

Note : There is a space at the end of the value corresponding to the line with id 7

The following example shows the difference between the three different comparison methods

BINARYCommand

 SELECT * FROM table_comms_ease ORDER BY table_comms_ease.value COLLATE BINARY;

result:

id	value
6	A
8	Z
5	new value
123	new value
7	new value

NOCASE command

 SELECT * FROM table_comms_ease ORDER BY table_comms_ease.value COLLATE NOCASE;

result:

id	value
6	A
5	new value
123	new value
7	new value
8	Z

RTRIM command

 SELECT * FROM table_comms_ease ORDER BY table_comms_ease.value COLLATE RTRIM;

result:

id	value
6	A
8	Z
5	new value
7	new value
123	new value

Quantitative restrictions

The quantity limit statement can limit the number of rows in the query result. The common format is as follows

 LIMIT 数量 OFFET 偏移量

If the number is n and the offset is o, the meaning of the above format is to start from the first o+1 , and take the most n data, if there is no eligible data, the result Is empty.

Limiting the number to 3, we get the following expression

 SELECT * FROM table_comms_ease LIMIT 3;

The result is

id	value
5	new value
6	A
7	new value

Since the total number of rows in the table is 5, if the limit is greater than or equal to 5, the entire table will be found.

If the limit number is 3, and the offset is specified as 1, the following expression is obtained

 SELECT * FROM table_comms_ease LIMIT 3 OFFSET 1;

As a result, the first piece of data (5, newvalue) is filtered out, and 3 pieces are taken backward to get

id	value
6	A
7	new value
8	Z

If the limit number is 3, and the offset is specified as 3, the following expression is obtained

 SELECT * FROM table_comms_ease LIMIT 3 OFFSET 3;

As a result, the first three pieces of data are filtered out, and 3 pieces of data are taken backward, but there are only 2 pieces in the back, so we get

id	value
8	Z
123	new value

If the offset is ≥ 5, nothing is found

SQLite common limitations

category	limit	Remark
string length	100000000	Defined by the macro SQLITE_MAX_LENGTH, the limit can be raised or lowered, up to 231-1
Maximum number of bytes in a single line	100000000	Defined by the macro SQLITE_MAX_LENGTH
Maximum number of columns	2000	Defined by the macro SQLITE_MAX_COLUMN, the limit can be raised or lowered, up to 32767
maximum length of statement	1000000000	Defined by the macro SQLITE_MAX_SQL_LENGTH, which can lower the limit
Maximum number of tables in a join	64	immutable
Maximum depth of expression tree	1000	Defined by the macro SQLITE_MAX_EXPR_DEPTH, which can lower or remove the limit
maximum number of arguments for a function	100	Defined by the macro SQLITE_MAX_FUNCTION_ARG, it can be increased up to 127
The number of SELECTs in a compound SELECT statement	500	Defined by the macro SQLITE_MAX_COMPOUND_SELECT, which can reduce
Library file maximum number of pages	1073741823	Defined by the macro SQLITE_MAX_PAGE_COUNT, the limit can be raised or lowered, up to 4294967294.
Maximum database size	281TB	Combining the maximum number of pages 4294967294 and the maximum page size 65536, the maximum database size is 281TB, but this is a theoretical value and has not been officially verified.
Maximum number of rows in a table	2^64	If it cannot be reached, the database size limit of 281TB will be reached first.

B-tree in database

Introduction to B-tree and B+ tree

B-tree is a balanced multi-way search tree, each node contains three parts: key, value, pointer to child nodes. Assuming that a B-tree node has n keys, it also has n values. If this is a leaf node, it has no pointers to children, otherwise there are n+1 pointers to children. The following figure shows the node situation at n==2 .

三路B树结点.jpg
In the figure 键1 and 键2 two values need to satisfy 键1<键2 . 值1 and 值2 respectively 键1 and 键2 correspond; three 指向子结点的指针 , each having a different range of point Child node of 键 . 子结点1 in 键 less than 键1 ; 子结点2 in 键 greater than 键1 , and less than 键2 ; 子结点3 in 键 are greater than 键2 . The following figure provides an example of a 3-way B-tree.

Each node in the above figure has three rows, the first row is 键 ; the second row is 值 ; the third row is 指向子结点的指针 . There root 50 and 100 two 键 , so its left subtree node 键 less than 50 ; subtree of node 键 greater than 50 and less 100 ; the right subtree node 键 are greater than 100 .

B-tree and B + Tree is similar, but the internal nodes of the B + tree only 键 and 指向子结点的指针 , and leaf nodes having 键 , 值 指向下一组值的指针 , that is, only store data on leaf nodes. Therefore, the key in the parent node will also appear on the child node. And the leaf nodes of the B+ tree 指向下一组值的指针 , string all 值 into a linked list. Since internal nodes do not need to store 值 , the B+ tree can store more 键 . The following figure uses a 5-way B+ tree to store the contents of the 3-way B-tree above.

五路B+树.jpg

B-tree page

A database file consists of one or more pages. In the same database, the size of each page is the same, which is between 512 and 65536, and is an integer power of 2. The pages of the database are divided into lock-byte pages, freelist pages, B-tree pages, load overflow pages, and pointer-mapped pages.

The B-tree algorithm provides a key-value storage mode for SQLite, and it ensures the ordering and uniqueness of keys. The node of the B-tree in the SQLite database is a page, so the pointer to the child node is actually the page number of the corresponding page. The SQLite database uses two B-tree variants, ---483f1260ec5703bf1554ec2dc572893d--- which stores data at leaf nodes and --- 索引B树 表B树 which does not store data. A complete tree can only be complete 表B树 or complete 索引B树 .

Table B-tree and Index B-tree

The table B tree is similar to the B+ tree, and only stores the values in the leaf nodes; the index B tree is a tree without values, and the key is the data itself, so the index B tree is actually similar to the B tree. The following table lists some of the differences between them.

Contrast	table B tree	Index B-tree
key length (byte)	8	Random value up to 2147483647
value storage point	leaf node	No value, key is data
value length	Longest 2147483647	No value, key is data
leaf node structure	key	key and value
Main usage scenarios	rowid table	Index, WITHOUT_ROWID table

Introduction to SQLite