Common SQLite database statements and how to use SQLite visualization tool MeasSQLlite on MAC, sqlitemeassqllite

Common SQLite database statements and how to use SQLite visualization tool MeasSQLlite on MAC, sqlitemeassqllite

I. Introduction

In mobile development, small databases are usually used for data management. SQLite is a very small and convenient database. In iOS development, the native framework also has good support.

Ii. Commonly Used SQLite statements

The significance of a database lies in its integration and management of data. Therefore, the core operation of a database is to add, delete, modify, and query data.

1. Create a data table statement

A database file may consist of tables. You can use the following statement to create a table in the database file:
Copy codeThe Code is as follows:
Create table class (num integer primary key, name text not null default "class 1", count integer CHECK (count> 10 ))

The preceding statement code can be simplified to the following format:

Create table Name (parameter name 1 type modification condition, parameter name 2, type modification parameter ,···)

Sqlite supports the following types:
Smallint short integer
Integer
Real Number Type
Float Single-precision floating point
Double-precision floating point
Currency long integer
Varchar character type

Text string

Binary data
Blob Binary Large Object
Boolean Type
Date type
Time Type
Timestamp

There are several common modifier conditions:

Primary key: this parameter is the primary key. The value of the primary key must be unique and can be used as a data index, such as a number.
Not null: flag this parameter as a non-empty property.
UNIQUE: the key value marking this parameter is UNIQUE, similar to the primary key.

DEFAULT: Set the DEFAULT value of this parameter.
CHECK: The parameter CHECK condition. For example, in the code above, the data written is valid only when the count value must be greater than the preceding value.

2. Add data

Use the following statement to add data rows:
Copy codeThe Code is as follows:
Insert into class (num, name, count) values (2, "three years, two shifts", 58)

The preceding statement code can be simplified to the following format:
Copy codeThe Code is as follows:
Insert into Table Name (key 1, key 2,...) values (value 1, value 2 ,···)

Use the following statement to add a data column, that is, add a new key:
Copy codeThe Code is as follows:
Alter table class add new text
Alter table name add key name key type

3. modify data

Use the following statement to perform the modification:
Copy codeThe Code is as follows:
Update class set num = 3, name = "New class" where num = 1
Update table name set key 1 = value 1, key 2 = value 2 where Condition

Add conditions for data modification after where, for example, the name of the class whose num is 1 and the mun value modified in the above Code.

4. delete data
Copy codeThe Code is as follows:
Delete from class where num = 1
Delete from table name where Condition

The code above deletes a piece of data with num as 1.

The following statements apply to deleting a table:

Copy codeThe Code is as follows:
Drop table class
Drop table Name

5. query operations

Query operations are the core functions of databases. Many query commands of sqlite can quickly complete complex query functions.

Query some key values in the table:
Copy codeThe Code is as follows:
Select num from class
Select key name, key name · from Table Name

Query all key value data:

Copy codeThe Code is as follows:
Select * from class
Select * from Table Name

* Is a full wildcard, representing an unlimited number of arbitrary characters

Query order:

Copy codeThe Code is as follows:
Select * from class order by count asc
Select key name, key name,... from table name order by key name sorting method

Name of the key to be sorted after order by. The sorting method is asc ascending desc descending

The number of data items to be searched and the location limit:

Copy codeThe Code is as follows:
Select * from class limit 2 offset 0
Select key name from table name limit maximum number of offset query start position

Conditional query:
Copy codeThe Code is as follows:
Select * from class where num> 2
Select key name from table name where Condition

Query data entries:
Copy codeThe Code is as follows:
Select count (*) from class
Select count (key name) from Table Name

Deduplication:
Copy codeThe Code is as follows:
Select distinct num from class
Select distinct key name from Table Name

Iii. simple use of MesaSQLite

MesaSQLite is a visual SQLite database editing software that is easy to use. Download link: http://www.bkjia.com/softs/419734.html.

1. Create a database file

Open MesaSQLite, select File in the navigation bar, select New DataBase in the pop-up menu to create a New Database File, or select Open DataBase to Open a Database.
Note: by default, the created database file is in the rdb format. You can manually change it to the db format.

2. Create a table

MesaSQLite can be used to operate databases in two ways: SQL statements and visual interfaces. In the SQL Query tool window, you can use SQL statements to operate databases, such:

You can also create a visual Structure in the Structure tool window:

3. query operations

For data Query operations, you can also use the SQL Query tool to Query data using statements or fill in Query conditions in the Content window for Query, as shown below: