Commonly used SQLite database statements and SQLite visualization tool on MAC MeasSQLlite

Commonly used SQLite database statements and SQLite visualization tool on MAC MeasSQLlite

SQLite is an open-source embedded relational database. Its compact, concise, and fast features are very easy to use in mobile application data management.

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:

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:

Insert into class (num, name, count) values (2, "three years, two shifts", 58)

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

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:

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:

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

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:

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:

Select num from class

Select key name, key name · from Table Name

Query all key value data:

Select * from class

Select * from Table Name

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

Query order:

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:

Select * from class limit 2 offset 0

Select key name from table name limit maximum number of offset query start position

Conditional query:

Select * from class where num> 2

Select key name from table name where Condition

Query data entries:

Select count (*) from class

Select count (key name) from Table Name

Deduplication:

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://pan.baidu.com/s/1skunnox.

Or:

Help house resource site download

------------------------------------------ Split line ------------------------------------------

Free in http://linux.bkjia.com/

The username and password are both www.bkjia.com

The specific download directory is available in/June,/June 13,/common SQLite database statements, and SQLite visualization tool MeasSQLlite on MAC/

For the download method, see

------------------------------------------ Split line ------------------------------------------

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:

Analysis on the combination of storage and data types in SQLite3

SQLite3 installation and basic operations

Simple Application of SQLite databases in Ubuntu 12.04

How to install SQLite in Ubuntu 12.04

Basics of SQLite Database

SQLite details: click here
SQLite: click here

This article permanently updates the link address: