To skillfully operate any database, the most basic requirement is to understand SQL language, which is also a skill every programmer should master. Although SQL profound, to master is really difficult, but the most basic commands to build a table, add, delete, change, we still must learn.
The Structured Query Language (SQL) is a standard database Query Language that is supported by all relational databases, but each database has slightly different support for SQL. We don’t have to worry about other databases’ support for THE SQL language, so we’ll just focus on SQLite. I’m going to use an emulator to demonstrate the various commands supported by SQLite, if you want to use a mobile phone, but make sure your phone is already Root and contains the sqlite3 command file.
one
First make sure the emulator is connected to the computer, then enter ADB shell on the command line to go to the console, as shown below:
Note that the # sign indicates that we are a super user, and the $sign indicates that we are a normal user. In this case, we need to switch to su.
With super user privileges, we can do a lot of things, here we first look at the system’s own contact table. Enter the /data /data directory, as shown below:
All applications’ local storage files are stored under this directory. In order to make it easy to distinguish data between different applications, Android uses the application package name for separate management, which means that each application’s local storage files are stored in the same directory as its application package name. Here we can see how many subdirectories there are:
OK, that’s a lot, because all the apps on the phone are here. . Among them, the com. Android. Will the contacts in the deposit is to contact the related data, we enter into this directory ls again:
As you can see, there are subdirectories databases, files, lib, and shared_prefs. Databases are used to store database files, files are used to store common text files, lib is used to store so, and shared_prefs is used to store shared files. These are some of the options available for Android data persistence. For those of you who are not familiar with this section, please refer to Chapter 6 of Line 1: Android.
two
Then go to the databases directory and ls:
Contacts2. db and profile.db are the real database files. You can open the database using sqlite3, as shown below:
Ok, the database is open, so how do we know what tables are in the current database? The.table command does just that:
Wow, there are so many lists! Yes, the data structure of contacts is very complex, and much of the data is stored in separate tables. So let’s just pick any table, like the accounts table, and what if I want to know what columns are in that table? The desc accounts command can be used in MySQL, but SQLite does not recognize this command because they are differentiated. SQLite can use pragma table_info(TABLE_NAME) to view the data structure of the table, as shown below:
As you can see, there are three results showing that the table Accounts has three columns. However, all the fields of the shrinkage in a row, with “|” symbols, that it is hard to see how the meaning of each field. Very simple, just need to change the display mode, such as line mode is good. Enter the. Mode line command to switch the display mode, and then re-run the pragma command. The result is as shown below:
How about that? That’s a lot clearer, isn’t it? The column names are account_name, account_type, and data_set. The data types are TEXT(string), null allowed, and are not primary keys. Ok, now what if I look up the data in the table Accounts? This is too simple, just use the SELECT statement, as follows:
Boon? There’s only one piece of empty data. It looks like this is the default on the emulator, so if you’re using your phone, you should be able to look up the real data here. That’s ok, we can manually add an email account to the Settings, as shown below:
three
Now query the accounts table again as follows:
OK, the new account added has been found out successfully.
In addition to query commands, there are other add, delete, and update commands that are identical to the standard SQL syntax, namely INSERT, delete, and update, but I won’t go into them because they are relatively simple. It is worth mentioning that each SQLite database also has a hidden SQlite_master table, which contains the table construction clauses of all the tables in the current database, which can be viewed using the select * from sqlite_master command:
That’s too much, isn’t it? There’s not enough space on one screen. Don’t worry, remember that we are using the SELECT command, and we can use the WHERE statement to filter out the part of the content we want to query, as shown below:
OK, CREATE TABLE accounts (account_name TEXT, account_type TEXT, data_set TEXT) In this way, we can query the construction of any table, which is helpful for us to learn and analyze the structure of the database table.
Some friends might find it too cumbersome to enter the select command every time to query the data in the table. Yes, and it’s really inconvenient to make sure your phone is connected to your computer. Fortunately, some mobile apps already offer the ability to query database tables, making it easy to view data in a database anytime, anywhere, such as Root Explorer.
Still is to make sure your phone has a Root, and then install Root Explorer, open the software according to the path we mentioned earlier, after entering/data/data/com. Android will. Contacts/databases, Click on the Contacts2.db database, select the built-in database viewer, and then click on any table to view the data in it, as shown below:
Using this method, we can view the latest data in the database table at any time, intuitive and convenient, when the program development can play a very big help.
Well, that’s it for today, and in the next article I’ll take you through more of the mysteries of Android databases.
Pay attention to my technical public account “Guo Lin”, there are high-quality technical articles pushed every working day.