Android database Master cheats: SQLite command

To skillfully operate any database, the most basic requirement is to understand the SQL language, which is each Programmer should have mastered the skills. Although SQL is broad and profound, to be proficient is really difficult, but the most basic form of some orders, additions and deletions to change, we still have to learn.

SQL (Structured query Language) is a standard database query language, which is supported by all relational databases, except that there is a slight difference in the support and standards for SQL language for each type of database. We don't have to worry about the support of other databases for SQL language, we just need to focus on SQLite. I'll use the simulator to demonstrate the various commands supported by SQLite, if you want to use your phone, but make sure your phone is rooted and contains the sqlite3 command file.

First make sure that the emulator is connected to the computer, and then enter the ADB shell into the console at the command line, as shown in:

Note that the # symbol indicates that we are currently a superuser, and if the $ symbol is displayed, it means that the current user is just a normal customer, then you need to enter the SU command to switch user identity.

With Superuser privileges, we can do a lot of things, here we first look at the system comes with the Contact table. Enter into the/data/data directory as shown in:

The local storage files for all applications are stored under this directory. To make the data between different applications easy to distinguish, Android is managed separately using the application package name, which means that each application's local storage file is stored in the directory of its own application package name, here we LS to see how many subdirectories:

OK, indeed there are many, after all, all the apps on the phone are here. Among them, the com.android.providers.contacts is the contact of the relevant data, we enter this directory again LS:

as you can see, there are several subdirectories, databases, files, Lib, and Shared_prefs. Where databases must be used for storing database files, files are used for storing ordinary text files, Lib is used to store so library, Shared_prefs is used for storing shared files. This is a few options for the persistence of Android data, and a friend who is not familiar with this part of the content can refer to the sixth chapter of the first line of code--android .

Then enter into the databases directory, and then LS:

Where the suffix named Journal file is a log file, we do not have to tube, contacts2.db and profile.db is the real database file, you can use the Sqlite3 command to open the database, as shown in:

OK, the database is open, so how do we know which tables are in the current database? Very simple, the. Table command will do the following:

Wow, there are so many sheets! Yes, the data structure of the contacts is very complex, and much of the data is stored in tables. Here we pick a table, for example, accounts, if I want to know which columns in this table should do? In MySQL you can use the DESC accounts command, but SQLite does not know this command, after all, they are differentiated. SQLite can use the pragma table_info (table_name) command to view the data structure of a table, as shown in:

As you can see, there are three results displayed, representing a total of three columns in the Accounts table. However, all the fields are indented in one line, and the "|" Symbols, so it's hard to see what each field means. Very simple, just need to change a display mode, such as line mode is very good. Enter the. Mode command to toggle the display mode and rerun the pragma command, as shown in the following:

How is that so much clearer? The column names of the three columns are account_name, Account_type, and Data_set, each of which is text (string), is allowed to be empty, and is not a primary key. Okay, so I'm going to look at the data in the Accounts table now? This is so simple that using the SELECT statement is possible, as follows:

Well? There's only one empty piece of data. Looks like the default on the simulator is this, if you use a mobile phone, here should be able to find the real data. But it doesn't matter, we can manually add a mailbox account in the settings, as shown in:

Now again, check the accounts table again, as shown below:

OK, the added new account has been successfully detected.

In addition to the query command, there are other additions and deletions to the standard SQL syntax is the same, that is, insert, delete and update, because it is relatively simple, I will not repeat. It is worth mentioning that each SQLite database also has a hidden sqlite_master table, which records all the tables in the current database table statements, you can use the SELECT * from Sqlite_master command to view:

Too many results, huh? A screen is not displayed at all. Don't worry, don't forget that we are using the Select command, and we can use the where statement to filter out the part of the content that we want to query, as shown in:

Ok,create table Accounts (account_name text, account_type text, data_set text) This is the statement of the Accounts table, In this way, we can query the table statement, which is helpful for us to learn and analyze the database table structure.

Some friends may feel that it is too troublesome to enter the Select command every time to query the data in the table. Yes, but also to ensure that the phone is connected to the computer when the query, it is really inconvenient. Fortunately, some mobile software has provided the database table query function, so that we can easily view the data in the database, such as root explorer this software is good.

Still make sure your phone is rooted, then install root Explorer, open the software and follow the path we described earlier, enter/data/data/com.android.providers.contacts/databases, Click the contacts2.db database, select the built-in database Viewer, then click on a table to see the data inside, as shown in:

Using this method, we can always look at the latest data in the database table, intuitive and convenient, in the development of the program can play a very big help.

Android database Master cheats: SQLite command