The most basic requirement for proficiency in any database is an understanding of the SQL language, a skill that every programmer should have.
Reprint please indicate the source: blog.csdn.net/guolin_blog…
preface
The most basic requirement for proficiency in any database is an understanding of the SQL language, a skill that every programmer should have. Although SQL is extensive and profound, it is very difficult to master, but some of the most basic table building commands, adding, deleting, modifying and checking, we must learn.
Understanding SQL
The Structured Query Language (SQL) is a standard database Query Language that is supported by all relational databases, although each database supports the Language in slightly different ways. We don’t have to worry about other databases’ support for the SQL language; we’ll just focus on SQLite. I’ll use the emulator to demonstrate the various commands that SQLite supports, or if you want to use your phone, but make sure your phone is already Root and includes the sqlite3 command file.
Into the SQL
First make sure the emulator is connected to the computer, then type ADB Shell on the command line to go to the console, as shown below:
Note that the # sign indicates that we are currently a superuser, and the $sign indicates that we are currently a regular user. In this case, you need to enter the su command to switch the user identity.
With superuser access, there are many things we can do, so let’s take a look at the system’s own contact list. Go to the /data /data directory, as shown in the figure below:
All applications’ local storage files are stored in this directory. In order to make data easily distinguishable between different applications, Android uses the application package name to separate the data. This means that each application’s local storage file is stored in the same directory as its own application package name. Let’s take a look at how many subdirectories there are:
OK, that’s a lot, since 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:
You can see that 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 library, and shared_prefs is used to store shared files. These are some of the options for Data persistence on Android. For those who are not familiar with this topic, refer to Chapter 6 of First Line of Code — Android.
Enter databases directory and ls:
Db and profile.db are the actual database files. You can use sqlite3 to open the database, as shown below:
Ok, the database is open, so how do we know which tables are currently in the database? The simple.table command does this:
Wow, there are so many lists! Yes, the data structure of contacts is very complex, and a lot of data is stored in separate tables. So let’s pick a random table here, let’s say the table Accounts, what if I want to know which columns are in that table? The desc accounts command can be used in MySQL, but SQLite does not recognize it, because they are differentiated. SQLite can use pragma table_info(TABLE_NAME) command to view the data structure of the table, as shown in the figure below:
As you can see, three results are displayed, indicating a total of three columns in the Table Accounts. However, all the fields of the shrinkage in a row, with “|” symbols, that it is hard to see how the meaning of each field. Simply 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 in the figure below:
Well, that’s a lot clearer, right? The column names of these three columns are account_NAME, account_TYPE, and data_set, and the data type is TEXT(a string), null is allowed, and none of them are primary keys. Ok, so what I want to do now is look at the data in the accounts? This is too simple, just use the select statement, as shown below:
Boon? There’s only one empty piece of data. This seems to be the default on the emulator, but if you have a phone, you should be able to see the actual data here. However, we can manually add an email account in our Settings, as shown below:
Now query the Accounts table again as follows:
OK, the new account added has been successfully detected.
In addition to the query command, there are other add, delete, and change commands that share the same syntax as standard SQL, namely INSERT, DELETE, and UPDATE, which I won’t go into because of their simplicity. Select * from sqlite_master select * from sqlite_master select * from sqlite_master
That’s too much, isn’t it? It’s not gonna fit on one screen. Don’t worry, remember that we are using the select command. You can use the WHERE statement to filter out the part of the query you want, as shown below:
CREATE TABLE Accounts (account_name TEXT, account_type TEXT, data_set TEXT) In this way, we can query the construction sentence of any table, which is helpful for us to learn and analyze the structure of database table.
Some of you may find it too cumbersome to enter the select command every time you want to query the data in the table. Yes, and make sure your phone is connected to your computer, which is really inconvenient. Fortunately, there are some mobile apps that allow you to query tables in your database, so you can easily see the data in your 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 the Contacts2.db database, select the built-in database viewer, and then click on any table to view the data in it, as shown in the figure below:
Using this method, we can always view the latest data in the database table, intuitive and convenient, when the program development can play a very big help.
Well, that’s enough for today, and in the next article I’ll take you through more of the mysteries of Android databases.