2017年8月25日 (五) 13:22的最后版本

[编辑] 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

[编辑] 11 Index

[编辑] 11.1 Create

$ sqlite3 /tmp/test.db 
SQLite version 3.16.2 2017-01-06 16:32:41
Enter ".help" for usage hints.
sqlite> .tables
t_action        t_base_define   t_heat_data     t_request_data  t_water_data  
t_air_data      t_elect_data    t_meter_info    t_time_node  

sqlite> create index idx_id on t_meter_info(f_id);

sqlite> explain query plan select * from t_meter_info where f_id=5;
selectid|order|from|detail
0|0|0|SEARCH TABLE t_meter_info USING INTEGER PRIMARY KEY (rowid=?)

sqlite> create index idx_type on t_meter_info(f_meter_proto_type);

sqlite> explain query plan select * from t_meter_info where f_meter_proto_type='0';
selectid|order|from|detail
0|0|0|SEARCH TABLE t_meter_info USING INDEX idx_type (f_meter_proto_type=?)

[编辑] 11.2 Drop

sqlite> drop index if exists idx_id;
sqlite> drop index idx_type;
sqlite> explain query plan select * from t_meter_info where f_meter_proto_type='0';
selectid|order|from|detail
0|0|0|SCAN TABLE t_meter_info