Learning to use SQLite (version 3.7.4) (medium h )?

Index:

An index is a structure that accelerates queries under certain conditions. Generally, all rows are scanned sequentially. If the table is large and the query frequency is high, the query is very bloated. SQLite uses B-tree for indexing. The index increases the size of the database. When an index is created, the worst case is that the table content doubles and the index maintenance is troublesome. When you modify a table, the index also needs to be modified.

Indexes can be divided into clustered indexes and non-clustered indexes.
The data is stored in the order of physical locations, rather than clustering indexes. Clustering indexes can improve the speed of multi-row search, instead of clustered indexes, a single row is quickly searched. In fact, this is not the case in SQLite. Because SQLite is not perfect in this aspect. This is different from general databases.

Create index [unique] index_name on table_name (columns). The unique constraint applies not only to the index, but also to the fields restricted by the index.

Drop index index_name.

Example:

The Uniqueness constraint can also be stored in the Union field.

 

The same data can be inserted because the index is removed. Here, there is an application of sorting rules.

 

Sorting rules: each field in the index has a corresponding sorting rule. To create a case-insensitive index, follow these steps:

 

List indexes and other information:

 

Use index:

First, you must understand when the index is used and when it is used. The expression that appears in the WHERE clause. SQLite uses a single field index:

Column {= | >|>=|<=| <} expression

Expression {= | >|>=|<=| <} column

Column in (espression _ List)

Column in (subquery );

 

Trigger:

When a specific event occurs in a specific table, the trigger performs the corresponding action.

Create [temp | temporary] trigger trigger_name

[Before | after] [insert | Delete | update of columns] On table_name

Action

Triggers are defined by behaviors, names, and tables. Actions are made up by the SQL language. When some events occur, the trigger is responsible for starting these commands. The key is before or after to determine whether to perform these operations before or after the event occurs. The event contains commands such as insert and delete. Triggers can have custom integrity constraints, Log changes, update tables, and other things. The function is only limited to the SQL commands written.

A problem occurs here. After the trigger is executed, you do not know why the table foods is not changed.

 

Error Handling: it is defined as a trigger before an event has the opportunity to prevent the event and check the event. Before and after triggers can help implement new integrity constraints. SQLite triggers allow you to call the raise () function to generate errors in the triggers.

Raise () is defined as follows: Raise (resolution, error_message );

Resolution can be abort, fail, ignore, and rollback.

 

Updatable View:

A view is created to connect foods and foods_types, connected through a foreign key relationship (an application with an alias ):

When updating the database table, foods and foods_types are displayed here. The trigger is automatically executed:

After the event, pay attention to rollback. If there is no rollback check, check whether no begin is previously entered;

It can be seen that the name has changed after the update table. This is because the table is not submitted. Note that the table cannot be rolled back after the table is submitted. If you want to restore the content of the original table, you can use the rollback command to restore it.

Next, we will learn and understand transactions. Transactions are a good stuff for databases, and there are a lot of advantages if you use them well.