SQLite database common statements and Mac SQLite visualization tool Meassqllite use method _sqlite

First, the introduction

In mobile development, some small databases are often used for data management. SQLite is a very small and convenient database, in the development of iOS, the original framework also has a good support.

Second, sqlite commonly used statements

The significance of the database is its integration and management of data, so the core operation of the database is to increase the data, delete, change, check the operation.

1. Set up a data table statement

A database file can consist of tables that create a table in the database file by using the following statement:

Copy Code code as follows:

CREATE table Class (num integer PRIMARY key,name text not NULL DEFAULT "Class 1", Count integer CHECK (count>10))

The above statement code can be simplified into the following format:

CREATE table table name (parameter name 1 type modifier condition, parameter name 2, type modifier parameter, ...)

The following types are supported in SQLite:
smallint Short Integral type
Integer integral type
Real number Type
Float single precision floating-point
Double Double-precision Floating-point
Currency Long integral type
varchar character type

Text string

Binary binary data
Blob binary Large Object
Boolean Boolean type
Date Date Type
Time Type
Timestamp time stamp Type

For cosmetic conditions, the following are commonly used:

PRIMARY key: This argument, the primary key, must have a unique value that can be used as the index of the data, such as numbering.
Not NULL: Tag This argument is a Non-empty property.
Unique: Marks the key value unique for this parameter, similar to the primary key.

Default: Set defaults for this parameter
Check: A parameter check condition, such as the code above, is valid if the write data is that count must be greater than.

2. Add Data

Use the following statement to add operations to a data row:

Copy Code code as follows:

INSERT into Class (Num,name,count) VALUES (2, "three years, 2 classes", 58)

The above statement code can be simplified into the following format:

Copy Code code 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, to add a new key:

Copy Code code as follows:

ALTER TABLE class Add new text
ALTER TABLE name add key Name key type

3. Modify Data

Use the following statement to change the operation:

Copy Code code 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

Where to add the conditions for modifying the data, such as the first name and Mun value of the class with Num 1 modified above.

4. Delete data

Copy Code code as follows:

Delete from class where num=1
Delete from table name where condition

The above code deletes a data of Num 1.

Deleting a table applies the following statement:

Copy Code code as follows:

drop TABLE Class
drop TABLE Table name

5. Query operation

Query operation is the core function of database, many query commands of SQLite can quickly complete complex query function.

Some key values in the query table:

Copy Code code as follows:

Select num from class
Select Key Name, Key name ... From table name

Query all key value data:

Copy Code code as follows:

SELECT * FROM class
SELECT * FROM table name

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

Query sort:

Copy Code code as follows:

SELECT * from Class ORDER by count ASC
Select Key Name, Key name, ... From table name order by key Name ordering method

The key name to be sorted, followed by the order by, has ASC ascending desc Descending

To find the number of data bars and find location restrictions:

Copy Code code 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 Code code as follows:

SELECT * FROM class where num>2
Select Key name from table name where condition

Query the number of data bars:

Copy Code code as follows:

Select COUNT (*) from class
Select COUNT (key name) from table name

To re-query:

Copy Code code as follows:

SELECT DISTINCT num from class
Select DISTINCT key name from table name

Three, the simple use method of Mesasqlite

Mesasqlite is a visual SQLite database editing software, very convenient to use. The following address is the download link: http://www.jb51.net/softs/419734.html.

1. Create a database file

Open the Mesasqlite software, select File in the navigation bar, select New Database in the pop-up menu, create a newer file, or open a database.
Note: The database file created by default is RDB format and can be manually changed to DB format.

2. Create a table

Mesasqlite There are two ways to operate a database, one is through an SQL statement and one is through a visual interface. In the SQL Query tool window, you can manipulate the database through an SQL statement, as shown in the following figure:

or create visual visualization in the Structure tool window:

3. Query operation

For data query operations, you can also query by using the SQL query tool through statements or by filling out query criteria in the Content window as follows: