2017年8月25日 (五) 12:50的版本

1 Show database

[root@mcuzone /app]#sqlite3 ./gatherdb.db
SQLite version 3.10.0 2016-01-06 11:01:07
Enter ".help" for usage hints.
sqlite>
sqlite> .database
seq  name             file
---  ---------------  ----------------------------------------------------------
0    main             /app/gatherdb.db-test
sqlite>

2 Show tables

sqlite> .tables
t_air_data      t_elect_data    t_meter_info    t_time_node
t_base_define   t_heat_data     t_request_data  t_water_data
sqlite>

3 Exec SQL

sqlite> .head on
sqlite> insert into t_meter_info values(10,40,1,00000000000055,7,0,"2#79");
sqlite> update t_meter_info set f_meter_address='00000000000055' where f_id=10;

4 Import csv file

Import csv file into table:

sqlite> .mode csv
sqlite> .import test.csv t_meter_info

5 Export csv file

Export table into csv file:

sqlite> .head on
sqlite> .mode csv
sqlite> .once test.csv
sqlite> select * from t_meter_info;

6 Execute cmd.sql

Execute SQL in cmd.sql:

sqlite> .read cmd.sql

7 Dump database

sqlite> .output /tmp/sta2.sql
sqlite> .dump
sqlite> .exit

8 Recover Database

$ sqlite3 /tmp/new-sta2.db
sqlite> .read /tmp/sta2.sql
sqlite> .tables

or:

$ sqlite3 /tmp/new-sta2.db < /tmp/sta2.sql

9 Dump table

sqlite> .output /tmp/sta2-heat.sql
sqlite> .dump t_heat_data
sqlite> .quit

10 Clean up DB file size

sqlite> delete from t_action;
sqlite> vacuum

When an object (table, index, trigger, or view) is dropped from the database, it leaves behind empty space. This empty space will be reused the next time new information is added to the database. But in the meantime, the database file might be larger than strictly necessary. Also, frequent inserts, updates, and deletes can cause the information in the database to become fragmented - scrattered out all across the database file rather than clustered together in one place.

The VACUUM command cleans the main database by copying its contents to a temporary database file and reloading the original database file from the copy. This eliminates free pages, aligns table data to be contiguous, and otherwise cleans up the database file structure.

Reference: http://www.sqlite.org/pragma.html#pragma_auto_vacuum