SQLite expert table separates and solves the problem of the size unchanged after SQLite expert deletes the table

Tagged with: Database sqlite Android Attribution number

Finally to use to the number of the site of the query, find a database file on the Internet, the size of more than 12M, Compressed into a zip also has 1.9M, so for an apk size is very unfavorable, later looked at the database content, found that there are a lot of redundancy, especially the Chinese characters occupy a large space, on the internet to find a way to separate a table, divided into two tables, two tables can be associated with a foreign key form. Here are a few table names: Tb_city, Mob_location, Tb_mobile, the final is to separate the table mob_location into Tb_city, tb_mobile in SQLite expert can use SQL statements, Here are just a few of the things I think are critical, such as creating a table, opening a new field database, setting the primary key, foreign keys these do not speak, for SQLite expert do not know a friend can first Baidu under how to use. Inserts the queried data into the specified table, removing the duplicate

INSERT into tb_city (location, areacode) Select location, AreaCode from Mob_location Group by location

Query two tables at a time
SELECT * from Tb_city, mob_location where tb_city. [Location] = mob_location. [Location]

SELECT * from Tb_city, tb_mobile where tb_city. [_id] = Tb_mobile. [foreign_id] and Tb_mobile. [Number] = 1342797
The above statement is also applicable in Java code, as used in code:
String sql = "SELECT * from Tb_city, tb_mobile where tb_city. [_id] = Tb_mobile. [foreign_id] and Tb_mobile. [NUMBER] = "+ num;

Inserting data from two table queries into a table
Insert into Tb_mobile (foreign_id, number) Select Tb_city. [_id], mob_location. [_id] from tb_city, mob_location where tb_city. [Location] = mob_location. [Location]

Here to the table mob_location separated into tb_city, tb_mobile, at this time to look at the database size of 16M, the table mob_location right-click Delete, but found that the size of the database has not changed, and then detected to delete just put it into the cache, And did not empty, need to right-click on the database->vacuum, tap to empty, then to see the size of only 3M more, so the capacity is much less, and then do the zip compression, but the effect of compression is not very ideal.

The database and code can be downloaded to the following link:

Http://pan.baidu.com/s/1sjFWJRv

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

SQLite expert table separates and solves the problem that the size of SQLite expert Delete table is unchanged