Introduction to the SQLite database and the use of SQL statements to increase and revise

This content mainly explains in the Android development process, how to use the SQL statement to carry on the SQLite database operation.

First, the introduction of the SQLite database

The Android system integrates a lightweight embedded database--->sqlite,sqlite is not a professional database like MySQL, SQL Server, and Oracle. SQLite database is just an embedded database engine, specifically for resource-constrained devices, SQLite can store up to 2T of data volume.

Second, the operation of SQLite

The way SQLite operates is, in essence, a more convenient file operation. Creating a SQLite database is like creating a file and then opening a read-write operation, which is much like Microsoft's access operation. However, SQLite is much more powerful than access. Some people say, in that case, what if there is a lot of data to store or manipulate? There is a direct presence in SQLite, although SQLite can store up to 2TB of data, but the mobile phone is a mobile phone, storage capacity, computing power and other performance far from being able to meet the needs of the server.

Third, the SQL statement in SQLite

(i), installation of Navicat Premium

Navicat Premium is a multi-connection database management tool that allows you to connect to MySQL, MariaDB, SQL Server, SQLite, Oracle, PostgreSQL and other databases at the same time, making it easier to manage different databases Fast. (Excerpt from the Navicat Premium website, which is actually an interface database management tool)

Select the version that is appropriate for your system to download, and then install it. The installation process may vary depending on the version, such as:

Step one, step two, (select I agree, before you can proceed to the next step)

Step three, (choose the installation directory, the default C-drive) step four, (create a shortcut directory, the default is the line)

Step five, (whether to create a desktop shortcut, the recommended check) step six,

(ii) using NAVICAT to create a SQLite database

Step one, open Navicat, click on the file, select New Connection, choose SQLite, pop-up interface, enter the connection name in the name of the connection, type eradication needs to choose, if you choose an existing database file, you need to select the database file options, the corresponding SQLite database files, if you choose other types, A new database connection is created, and you need to select a save location in the database Files option (SQLite and SQLite3 two versions). , the following connection is named Sqliteoperate, the type is SQLite3, the database name is Test.db, and the default has a database main. Advanced, HTTP Usage self-research or find data.

Step two, open the connection, right-attach the database, select the database file that you just created, then open, attach successfully a new database test will appear in the Sqliteoperate connection, and then open the database test, to which the database creation is completed.

Step three, create a data table

Select the test database, click the query option in the upper right column, create a new query, 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 is not null). Create table user, determine whether the table exists when created, notice that there is only null, Integer, real (floating point), blob, text string in SQLite. Click the Run button after typing, you will be prompted to enter the name of the query, such as Create_sql, after running, open the test database, found no user table. Don't be afraid, you can run the SQL statement again, and you'll be prompted that the user table already exists. Are you surprised? Do not be afraid, disconnect sqliteoperate, then open the connection again, open the test database, you will find that SQLite is there.

Step four, insert data

Create a new query, in step three, enter the statement that inserts the data: INSERT into User (Name,sex,age) VALUES (' Zhang San, ' female ', 18), where the _id is self-increment, omit, then run, open the data table user, you will find the data inserted successfully, If the user table is open before running, it is recommended to close the user table and open it again.

Step five, query the data

Same as four, input query SQL statement: Query all content: SELECT * from user, query the specified condition all content: For example: SELECT * from user where name= ' Zhang San ', query the specified conditions specified content: For example: select name from U Ser where age=18

Step six, update the data

With four new queries, enter the update SQL statement: Update all values for a column: For example: Update user set age=18, updating the value of the specified condition: For example: Update user set name= ' Zhao Si ' where name= ' Zhang San '

Step Seven, delete data

With four new queries, enter the DELETE SQL statement: For example: Delete from Stu where Name= ' Zhang San '

This concludes the operation of the SQLite database through Navicat, and the following chapter will talk about manipulating the SQLite database via SQL statements in Android.

Introduction to the SQLite database and the use of SQL statements to increase and revise