SQLITE Tips

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 Dump table

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