| |
SQLite is an open source embedded relational database, which is very small, simple and rapid characteristics readily applied to mobile application data management.
One. Introduction
In mobile development, it is common to use some small database for data management. SQLite is a very compact and convenient database in iOS development, its original framework also have very good support.
Two, SQLite common statement
Database meaning of existence lies in its integration and data management, the core operation of the database is nothing more than increase the data, delete, change, Richard operation.
1. Establish a data table statement
A database file may consist of a number of tables, create a table in a database file with the following statement:
create table class (num integer PRIMARY KEY, name text NOT NULL DEFAULT "1 class", count integer CHECK (count> 10))
The above statement code can be simplified into the following formats:
create table table name (the name of a type parameter modification conditions, parameter names 2, Type parameter modification, ...)
sqlite support the following types:
Short Integer smallint
integer integer
real real type
float single-precision floating point
double double-precision floating-point
currency long integer
char varchar
text string
binary binary data
blob blob
boolean boolean
date Date type
time time type
timestamp timestamp type
About modification of the condition, commonly used are the following:
PRIMARY KEY: When this parameter is the primary key value of the primary key must be unique, it can be used as index data, such as numbers.
NOT NULL: This parameter is non-empty tag attributes.
UNIQUE: The only key values of this parameter markers, similar to the primary key.
DEFAULT: the default setting of the parameter
CHECK: Check the condition of parameters, such as the above code, the data is written to count must be greater than when active.
2. Add data
Use the following statement to add rows of data operations:
insert into class (num, name, count) values (2, "three-year 2 classes," 58)
The above statement code can be simplified into the following formats:
insert into table (key 1, key 2, ...) values (value 1, value 2, ...)
Use the following statement to add a column of data, namely, to add a new key:
alter table class add new text
alter table table name add keys Key Type
3. Modify the data
Use the following statement to change the following:
update class set num = 3, name = "new class" where num = 1
update table set key value 1 = 1, 2 = value 2 where key conditions
Modify the data added to the end where conditions such as described above for the modified code num mun names and values of class 1.
4. Delete Data
delete from class where num = 1
delete from table where conditions
The above code deletes num is a 1 data.
To delete a table applicable to the following statement:
drop table class
drop table table name
5. query operation
Query operation is a core function of the database, sqlite many query can quickly perform complex queries.
Some key lookup table:
select num from class
select keys, key ... from table
Discover all key data:
select * from class
select * from table
* Is a full wildcard for any number of characters is not limited
Sort query:
select * from class order by count asc
select keys, key, ... from table name order by sort keys
order by writing back to sort of the key name, there is sort asc desc descending ascending
Find the number of data and find the location restrictions:
select * from class limit 2 offset 0
select keys from table limit the maximum number of queries starting position offset
Criteria query:
select * from class where num> 2
select keys from table where conditions
Query data number:
select count (*) from class
select count (key name) from table
To re-query:
select distinct num from class
select distinct keys from the table name
Three, MesaSQLite simple to use
MesaSQLite is a visual SQLite database editing software, easy to use. Download link following address: http: //pan.baidu.com/s/1skunNOx. |
|
|
|