SQLITE Tips
来自Jack's Lab
(版本间的差异)
(→Index) |
(→Drop) |
||
(未显示1个用户的2个中间版本) | |||
第158行: | 第158行: | ||
<source lang=bash> | <source lang=bash> | ||
− | sqlite> drop index if | + | sqlite> drop index if exists idx_id; |
sqlite> drop index idx_type; | 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 | ||
+ | |||
</source> | </source> | ||
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