Android/ophone development handout (10): SQLite Database

This article is original, if you need to repost, please indicate the author and source. Thank you!

it's time to explain the database. Databases are also the core of the android storage solution. The SQLite database is used in the Android system. SQLite is a very lightweight database. From the perspective of SQLite, we can see that the goal of SQLite is to show itself as a lightweight database in the past, present, and future. SQLite is lightweight, but it is faster than MySQL and PostgreSQL when executing some simple SQL statements. Since many readers have been familiar with SQLite databases for the first time, before introducing how to use SQLite in Android, this section briefly introduces how to create SQLite databases on a PC, and some special aspects of the SQLite database (because this book is not intended to introduce the SQLite database, this book will not introduce similar parts of other databases (such as insert and update. Readers who do not have this knowledge can refer to other books on databases.
6.4.1 SQLite database management tool
before learning a new technology, the first thing to do is install a tool that can operate on this technology on your computer. Of course, this is also very consistent with an idiom: to do good deeds, you must first sharpen your skills. Although good tools do not allow ourselves to better master this technology, they can greatly improve our work efficiency.
let's get down to the truth. Now let's take a look at the tools provided by the official team to operate the SQLite database. Go to the official download page, URL:
http://www.sqlite.org/download.html
Find the Binary Download package for windows on the download page. When writing this book, the latest version of SQLite is sqlite3.6.2. For this reason, the file to be downloaded is sqlite-3_6_20.zip. Decompress the ZIP file and find that there is only one file in the extracted Directory: sqlite3.exe. Yes, this file is a tool for operating the SQLite database (isn't it light? Only one tool is available ). It is a command line Program . Run this program and enter the operation interface, as shown in Figure 6.15.

You can enter SQL statements or console commands in the console. All SQL statements must end with a semicolon. Console Commands must start with a solid vertex (.), such as. Help (display help information),. Quit (exit the console), and. Tables (display all table names in the current database ).
Although you can enter SQL statements in the SQLite console to operate databases, inputting a large number of commands will greatly increase the workload. Therefore, we must replace this console program with the so-called "weapon.
Since SQLite provides various types of program interfaces, there are many tools for managing the SQLite database. Below are some commonly used SQLite management tools.
SQLite Database Browser
Http://sourceforge.net/projects/sqlitebrowser
SQLite expert professional
Http://www.sqliteexpert.com
SQLite developer
Http://www.sqlitedeveloper.com
Sqlitespy
Http://www.softpedia.com/progDownload/SQLiteSpy-Download-107386.html
The author used sqliteexpert professional when writing this book, which is also a recommended SQLite management tool. This tool has a large number of visualization functions, such as creating databases, creating tables, and SQL builder. Figure 6.16 shows the main interface of SQLite expertprofessional.

6.4.2Create databases and tables

Using sqliteconsole tool (sqlite3.exe) to create a database is very simple, you only need to enter the following command to create or open the database.
Sqlite3.exe test. DB
If the database (test. if the database does not exist, test is pre-created. DB file (test is not generated at this time. DB file. test is not created until you execute commands or SQL statements related to database components (tables, views, triggers, and so on) on the SQLite console. DB file.
If you want to use the sqlite.exe command to create a database and a table at the same time, you can first create an SQL. script file (or another file name) and enter the following SQL statement:
Create Table Table1 (

Id integerprimary key,

Age int,

Name text
);
Create Table Table2 (

Idinteger primary key,

Type_idinteger,

Name text
);
Run the following command to create two tables, Table1 and Table2, while creating the test. DB file.
Sqlite3.exe test. DB <SQL. Script
When using the createtable statement to create a table, you can also specify the default value for each field, as shown in the following SQL statement:
Create Table Table1 (

Id integerprimary key,

Age int default 20,

Name text
);
Create Table Table2 (

Id integerprimary key,

Type_idinteger,

Name text default 'name1'
);
6.4.3Fuzzy search

The fuzzy search of SQLite is similar to that of other databases. The like keyword and the % wildcard are used. However, SQLite may encounter some problems when processing Chinese characters. For example, insert a record to table 2 using the following SQL statement.
Insert into Table2 (ID, type_id, name) values (1, 20, 'mobile OS ');
Using the following SQL query in the SQLite console is correct.
Select * From Table2 where name =
'Mobile OS ';
However, if the following fuzzy query statement is used, records cannot be queried.
Select * From Table2 where name like
'Cell phone % ';
This happens because the SQLite console uses gb2312 as the encoding format when saving Chinese, while the like operation uses a UTF-8. You can use the following command to view the current encoding format of the SQLite console.
Pragma encoding;
To enable like fuzzy search for Chinese characters, we recommend that you use SQLite expert professional introduced in section 6.4.1 to execute SQL statements such as insert and update to edit data. In this tool, the UTF-8 is used directly to save Chinese.
6.4.4Display records by PAGE

paging is a concept that is often mentioned in Web applications. The basic principle is to obtain some data of the query result from the database and display it on the page. Although this book does not introduce the development of web programs, it is still very important to obtain some data about the query results.
SQLite and MySQL both use the limit keyword to limit the number of records returned by the SELECT statement. Two parameters are required for limit. The first parameter indicates that the returned sub-record set is at the beginning of the parent record set (starting from 0). The second parameter indicates the number of records in the returned sub-record set. The 2nd parameters are optional. If this parameter is not specified, all records starting from the starting position will be obtained. For example, the following SELECT statement returns 11th records starting from 100 records in table 2.
select * From Table2 limit 10,100
6.4.5 transaction

if multiple SQL statements with modification records (such as insert and update) are executed at a time, when an SQL statement fails to be executed, you need to cancel the modification of records by other SQL statements. Otherwise, data inconsistency may occur. Transactions are the best way to solve this problem.
In SQLite, you can use begin to start an event. For example, the following Code executes two SQL statements, if the execution of 2nd SQL statements fails, the results of the execution of the 1st SQL statements will be rolled back, which is equivalent to failing to execute this SQL statement.
begin;
insert into Table1 (ID, name) values (50, 'android ');
insert into Table2 (ID, name) values (1, 'test');
to explicitly roll back the modification results of a record, you can use the rollback statement, the Code is as follows:
begin;
Delete from Table2;
rollback;
to explicitly submit the Modification result of a record, use the commit statement. The Code is as follows:
begin;
Delete from Table2;
commit;