1. Attach database:
The ATTACH DATABASE statement adds another database file to the current connection. If the file name is ":memory:", we can treat it as an in-memory database, which cannot be persisted on the disk file. If you operate on a table in an Attached database, you need to prepend the table name, such as dbname.table_name. Finally, it should be noted that if a transaction contains multiple Attached database operations, the transaction is still atomic. See the following example:
sqlite> CREATE TABLE testtable (first_col integer);
sqlite> INSERT INTO testtable VALUES(1);
sqlite> .backup 'D:/' --Back up the main database in the current connection to the specified file.
sqlite> .exit
--Re-login to the sqlite command line tool:
sqlite> CREATE TABLE testtable (first_col integer);
sqlite> INSERT INTO testtable VALUES(2);
sqlite> INSERT INTO testtable VALUES(1);
sqlite> ATTACH DATABASE 'D:/' AS mydb;
sqlite> .header on --The query result outputs the field name as the title.
sqlite> .mode column --Show each column separately.
sqlite> SELECT t1.first_col FROM testtable t1, t2 WHERE t.first_col = t2.first_col;
first_col
----------
1
2. Detach database:
Uninstall the specified database in the current connection. Note that the main and temp databases cannot be uninstalled. See the following example:
--This example carries the result of the above example, that is, the mydb database has been Attached to the current connection.
sqlite> DETACH DATABASE mydb;
sqlite> SELECT t1.first_col FROM testtable t1, t2 WHERE t.first_col = t2.first_col;
Error: no such table:
3. Affairs:
In SQLite, if there is no specified transaction displayed for the current SQL command (except SELECT), SQLite will automatically add an implicit transaction to the operation to ensure the atomicity and consistency of the operation. Of course, SQLite also supports displayed transactions, and its syntax is basically the same as that of most relational databases. See the following example:
sqlite> BEGIN TRANSACTION;
sqlite> INSERT INTO testtable VALUES(1);
sqlite> INSERT INTO testtable VALUES(2);
sqlite> COMMIT TRANSACTION; --Show transactions are 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; --Show transactions are rolled back and the data in the data table has not changed.
sqlite> SELECT COUNT(*) FROM testtable;
COUNT(*)
----------
2