查看SQLITE Tips的源代码
←
SQLITE Tips
跳转到:
导航
,
搜索
因为以下原因,你没有权限编辑本页:
您刚才请求的操作只有这个用户组中的用户才能使用:
用户
您可以查看并复制此页面的源代码:
== Show database == <source lang=bash> [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> </source> <br> == Show tables == <source lang=bash> 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> </source> <br><br> == Exec SQL == <source lang=bash> 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; </source> <br><br> == Import csv file == Import csv file into table: <source lang=bash> sqlite> .mode csv sqlite> .import test.csv t_meter_info </source> <br><br> == Export csv file == Export table into csv file: <source lang=bash> sqlite> .head on sqlite> .mode csv sqlite> .once test.csv sqlite> select * from t_meter_info; </source> <br><br> == Execute cmd.sql == Execute SQL in cmd.sql: <source lang=bash> sqlite> .read cmd.sql </source> <br><br> == Dump database == <source lang=bash> sqlite> .output /tmp/sta2.sql sqlite> .dump sqlite> .exit </source> <br><br> == Recover Database == <source lang=bash> $ sqlite3 /tmp/new-sta2.db sqlite> .read /tmp/sta2.sql sqlite> .tables </source> or: <source lang=bash> $ sqlite3 /tmp/new-sta2.db < /tmp/sta2.sql </source> <br><br> == Dump table == <source lang=bash> sqlite> .output /tmp/sta2-heat.sql sqlite> .dump t_heat_data sqlite> .quit </source> <br><br> == Clean up DB file size == <source lang=bash> sqlite> delete from t_action; sqlite> vacuum </source> <pre> 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. </pre> Reference: http://www.sqlite.org/pragma.html#pragma_auto_vacuum <br><br> == Index == === Create === <source lang=bash> $ 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=?) </source> <br> === Drop === <source lang=bash> 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 </source> <br><br> <br><br> <br><br> <br><br> <br><br> <br><br>
返回到
SQLITE Tips
。
个人工具
登录
名字空间
页面
讨论
变换
查看
阅读
查看源代码
查看历史
操作
搜索
导航
首页
社区专页
新闻动态
最近更改
随机页面
帮助
工具箱
链入页面
相关更改
特殊页面