Learning + translation of SQL as understood by SQLite -- insert

Insert

SQL-Statement: =

Insert[OrConflict-Algorithm]Into[Database-name .] Table-name [(Column-list)]Values(Value-list) | Insert[OrConflict-Algorithm]Into[Database-name .] Table-name [(Column-list)] Select-Statement

 

The insert statement comes in two basic forms. the first form (with the "values" keyword) creates a single new row in an existing table. if no column-list is specified then the number of values must be the same as the number of columns in the table. if a column-list is specified, then the number of values must match the number of specified columns. columns of the table that do not appear in the column list are filled with the default value, or with null if not default value is specified.

The second form of the insert statement takes it data from a select statement. the number of columns in the result of the select must exactly match the number of columns in the table if no column list is specified, or it must match the number of columns name in the column list. A new entry is made in the table for every row of the select result. the Select may be simple or compound. if the SELECT statement has an order by clause, the order by is ignored.

The optional conflict-clause allows the specification of an alternative constraint conflict resolution algorithm to use during this one command. see the section titled on conflict for additional information. for compatibility with MySQL, the parser allows the use of the single keyword replace as an alias for "insert or replace ".

The insert function has two basic forms:

1. keyword "values"

If no column is restricted, the number of values = the number of columns in the table; if the column is restricted, the number of values = the number of columns, in this case, columns not filled in the table are the default values (null if no default value is available ).

2. obtain data from the SELECT statement. The data count limit is the same as that of the SELECT statement. Each row in the table stores a select result. Ignore the "Order by" statement in the SELECT statement at the same time (which expert will give you some advice on what the statement is ).

 

Optional [Conflict-Algorithm]: Allows the definition of an alternative constraint conflict resolution algorithm to use during this one command (who will translate ?). You can get more information in on conflict. To be compatible with MySQL, "replace" is equivalent to "insert or replace ".

In addition, I saw the following comments on the Internet:

Unlike other databases, SQLite starts transactions much faster than when transactions are not started.
The reason is as follows:
Because SQLite's transaction processing mechanism is implemented through file locks and temporary files, it does not add checkpoints like other databases for secondary commit and multi-step rollback, therefore, if you do not open a notice, every time you operate on the database, SQLite releases the file write lock to get support for concurrency. After you enable the transaction, it does not need to consider concurrency, until the end of the transaction, it will always use the Write File lock to maintain the occupation of the database file, so this time the performance will be far greater than that of the unknown, I did a test, for pieces of data, unused transactions, 7 ~ 8 s is similar to access. It enables transactions, 0.1 ~ 0.3 s

However, when reading data (select), you don't need to consider the above issues, so the performance is almost the same

Therefore, SQLite is an alternative database, which will change our understanding of the database many times, such as transaction processing and physical file design.

How can I enable "transaction processing "?
Example: insert into existword values ("W") (Insert "W" to the exsitword table ")