Secret of Android database experts (1) -- SQLite command

Secret of Android database experts (1) -- SQLite command

 

To be proficient in operating any database, the most basic requirement is to understand the SQL language, which is also a skill that every programmer should master. Although SQL is profound and profound, it is really difficult to be proficient, but you still have to learn the most basic table creation commands, addition, deletion, modification, and query.

Structured Query Language (Structured Query Language) is a standard database Query Language, that is, all relational databases support it, but each database has slightly different support for SQL languages than the standard. We don't need to care about the SQL language support of other databases. Here we only need to focus on SQLite. Next I will use a simulator to demonstrate the various commands supported by SQLite. If you want to use a mobile phone, make sure that your mobile phone is already Root and contains the sqlite3 command file.

First, make sure that the simulator is connected to the computer, and then enter adb shell in the command line to enter the console, as shown in:

Note # The symbol indicates that we are already superusers. If the $ symbol is displayed, it indicates that we are only common users. In this case, you need to enter the su command to switch the user identity.

With the Super User permission, we can do a lot of things. Here, let's first check the contact table that comes with the system. Go to the/data directory, as shown in:

The local storage files of all applications are stored in this directory. To distinguish data between different applications, Android uses the application package name for separate management, that is to say, the local storage file of each application is stored in the directory of the application package name. Here we will look at the number of subdirectories:

OK, there are indeed many. After all, all the applications on the mobile phone are here. Among them, the data stored in com. android. providers. contacts is related to the contact. Let's go to this directory and then ls it:

As you can see, there are currently several sub-directories databases, files, lib, and shared_prefs. Databases must be used to store database files. files are used to store common text files, lib is used to store so files, and shared_prefs is used to store shared files. This is an optional method for Android data persistence. If you are not familiar with this part of content, referThe first line of code-AndroidChapter 6.

Go to the databases directory, and then ls:

The file suffixed with journal is a log file. We don't need to worry about it. contacts2.db and profile. db are the real database files. You can use the sqlite3 command to open the database, as shown in:

Okay, the database has been opened. How can we know which tables are in the current database? The. table command is simple:

Wow, there are so many tables! Yes, the data structure of contacts is very complex, and a lot of data is stored in sub-tables. Here we randomly select a table, such as the accounts table. What should I do if I want to know which columns are in this table? You can use the desc accounts command in MySQL, but SQLite does not know the command. After all, they are differentiated. In SQLite, you can use the pragma table_info (TABLE_NAME) command to view the table's data structure, as shown in:

Three results are displayed, indicating that there are three columns in the accounts table. However, all fields are saved in a row and separated by the "|" symbol, which makes it difficult to see the meaning of each field. It's easy. You only need to change the display mode. For example, the line mode is quite good. Enter the. mode line command to switch the display mode and run the pragma command again. The result is as follows:

How can this be clearer? The names of the three columns are account_name, account_type, and data_set. The data types are both TEXT (string), which can be empty and are not primary keys. Okay. Now I want to check the data in the accounts table? This is too simple. You can use the select statement as follows:

Well? Why is there only one blank data. It seems that this is the case on the simulator by default. If you are using a mobile phone, you should be able to find the real data here. But it doesn't matter. You can manually add an email account in the settings, as shown in:

Now query the accounts table again, as shown below:

OK. The new account has been successfully found.

In addition to the query command, other addition, deletion, modification, and deletion commands are the same as the standard SQL syntax, namely, insert, delete, and update. I will not repeat them here because of their simplicity. It is worth mentioning that each SQLite database also has a hidden sqlite_master table, which records the table creation statements for all the tables in the current database, you can use the select * from sqlite_master command to View Details:

Too many results, right? A screen cannot be displayed. Don't worry. Don't forget that we use the select command. You can use the where statement to filter out the part of the content we want to query, as shown in:

OK, CREATE TABLE accounts (account_name TEXT, account_type TEXT, data_set TEXT). This is the TABLE creation statement of the accounts TABLE. In this way, we can query the TABLE creation statement of any TABLE, this helps us to learn and analyze the database table structure.

Some may think that it is too troublesome to input the select command every time to query the data in the table. That's right. It's really inconvenient to make sure your phone is connected to your computer. Fortunately, some mobile phone software has provided the Database Table query function, so that we can conveniently view the data in the database anytime and anywhere. For example, the Root Explorer software is good.

Still make sure that your mobile phone is already Root, then install Root Explorer, open the software and follow the path we described earlier to enter/data/com. android. providers. contacts/databases, click contacts2.db database, select the built-in database viewer, and click a table to view the data, as shown in:

Using this method, we can view the latest data in the database table at any time, which is intuitive and convenient, and can be of great help during program development.

Now, I will explain more about the mysteries of the Android database in the next article.