SQLite tutorial (5): database and transaction, sqlite tutorial

SQLite tutorial (5): database and transaction, sqlite tutorial

I. Attach database:

The attach database statement adds another DATABASE file to the current connection. If the file name is ": memory:", we can regard it as a memory DATABASE, and the memory DATABASE cannot be persisted to the disk file. If you operate a table in the Attached database, you must add the database name before the table name, for example, dbname. table_name. It should be noted that if a transaction contains multiple Attached database operations, the transaction is still atomic. See the following example:
Copy codeThe Code is as follows:
Sqlite> create table testtable (first_col integer );
Sqlite> insert into testtable VALUES (1 );
Sqlite>. backup 'd:/mydb. db' -- back up the master database in the current connection to the specified file.
Sqlite>. exit
-- Log on to the sqlite command line tool again:
Sqlite> create table testtable (first_col integer );
Sqlite> insert into testtable VALUES (2 );
Sqlite> insert into testtable VALUES (1 );
Sqlite> attach database 'd:/mydb. db' AS mydb;
Sqlite>. header on -- the query result outputs the field name as the title.
Sqlite>. mode column -- display each column separately.
Sqlite> SELECT t1.first _ col FROM testtable t1, mydb. testtable t2 WHERE t. first_col = t2.first _ col;
First_col
----------
1

Ii. Detach database:

Detaching: Specifies the database in the current connection. Note that the main and temp databases cannot be detached. See the following example:
 Copy codeThe Code is as follows:
-- This example carries the result of the preceding example, that is, the mydb database has been Attach to the current connection.
Sqlite> detach database mydb;
Sqlite> SELECT t1.first _ col FROM testtable t1, mydb. testtable t2 WHERE t. first_col = t2.first _ col;
Error: no such table: mydb. testtable

Iii. transactions:

In SQLite, if no specified transaction is displayed for the current SQL command (except SELECT), SQLite automatically adds an implicit transaction for this operation, to ensure the atomicity and consistency of the operation. Of course, SQLite also supports transactions displayed. Its syntax is basically the same as that of most relational databases. See the following example:
 Copy codeThe Code is as follows:
Sqlite> begin transaction;
Sqlite> insert into testtable VALUES (1 );
Sqlite> insert into testtable VALUES (2 );
Sqlite> commit transaction; -- indicates that the TRANSACTION is committed, and the data in the data table has also changed.
Sqlite> select count (*) FROM testtable;
COUNT (*)
----------
2
Sqlite> begin transaction;
Sqlite> insert into testtable VALUES (1 );
Sqlite> rollback transaction; -- indicates that the TRANSACTION is rolled back and the data in the data table has not changed.
Sqlite> select count (*) FROM testtable;
COUNT (*)
----------
2