SQLite database introduction and addition, deletion, modification, and query using SQL statements, sqlitesql

SQLite database introduction and addition, deletion, modification, and query using SQL statements, sqlitesql

This article mainly explains how to use SQL statements to operate SQLite databases during Android development.

I. Introduction to SQLite Databases

The Android system integrates a lightweight embedded database ---> SQLite, SQLite is not as professional as Mysql, SQL Server, and Oracle databases. SQLite database is only an embedded database engine. It is applicable to devices with limited resources. SQLite can store up to 2 TB of data.

2. SQLite operations

SQLite is essentially a more convenient file operation. Creating an SQLite database is actually like creating a file and then opening the read/write operation. In this regard, it is like Microsoft's Access operation. However, SQLite has more powerful functions than Access. Some people say that, in this case, what should we do when a large amount of data needs to be stored or operated? SQLite directly exists. Although SQLite can store up to 2 TB of data, the mobile phone is a mobile phone after all, and its storage and computing performance is far from meeting the needs of serving as a server.

Iii. SQL statement operations in SQLite

(1) install Navicat Premium

Navicat Premium is a multi-Connection database management tool that allows you to connect to databases such as MYSQL, MariaDB, SQL Server, SQLite, Oracle, and PostgreSQL at the same time, it makes it easier and quicker to manage different databases. (From the Navicat Premium official website, it is actually a database management tool on the Interface)

Select a version suitable for your system to download and install it. The installation process may vary depending on different versions, such:

Step 1,

Step 2. (select "I agree" Before proceeding to the next step)

Step 3. (select the installation directory, default disk C)

Step 4. (create a shortcut Directory, which is the default)

Step 5. (whether to create a desktop shortcut. Check it)

Step 6,

(2) Using Navicat to create an SQLite Database

Step 1. Open Navicat, click a file, select create connection, and select SQLite. On the displayed page, enter the connection name in the connection name, and select the type root. If you select an existing database file, you need to find the corresponding SQLite database file in the database file option. If you select another type, a new database connection will be created. In the database file option, select the storage location (SQLite and SQLite3 versions ). The following connection name is SQLiteOperate, the type is SQLite3, and the database name is test. db. By default, there is a database main. In advanced and HTTP, you can study or search for information on your own.

Step 2. Open the connection, right-click to attach the database, select the database file you just created, and open it. After the attachment is completed, a new database test will appear in the SQLiteOperate connection, and then open the database test, at this point, the database has been created.

Step 3. Create a data table

Select the test database, click the query option in the upper right column, create a query, and enter the SQL statement: create table if not exists user (_ id integer primary key not null, name text not null, age integer not null, sex text not null ). Create a table user and determine whether the table exists during creation. Note that SQLite only contains null, integer, real (floating point number), blob, and text strings. Enter and click the run button. In the displayed window, enter the name of the query, for example, create_ SQL. After running the command, open the test database and find that there is no user table. Don't be afraid. You can run the SQL statement again and the system will prompt that the user table already exists. Are you surprised? Don't be afraid, disconnect the SQLiteOperate connection, then open the connection again, open the test database, you will find that SQLite is there.

Step 4. insert data

Create a query and perform the following operations in step 3: Enter the insert data statement: insert into user (name, sex, age) values ('Michael, female, 18 ), here, the _ id is auto-increment and omitted. Then, run. Open the data table user and you will find that the data is inserted successfully. If the user table has been opened before running, we recommend that you disable the user table first, open again.

Step 5. query data

Same as 4. input the query SQL statement: Query all content: select * from user, query all content of the specified condition: for example: select * from user where name = 'zhang san ', query the specified content of a specified condition. For example, select name from user where age = 18.

Step 6. Update Data

For the four new queries, enter the update SQL statement to update all values of a column, for example, update user set age = 18. update the value of a specified condition. For example: update user set name = 'zhao si' where name = 'zhang san'

Step 7. delete data

For the four new queries, enter the delete SQL statement, for example, delete from stu where name = 'zhangsan'

At this point, the operation on the SQLite database through Navicat ends. The following chapter describes how to operate the SQLite database through SQL statements in Android.