Preparation of database in iOS-simple database

1. Data persistence

Data persistence is the storage of data on the hard disk through files

There are four main ways to persist data in iOS

Property List

Object archiving

SQLite database

CoreData

Comparison of data persistence pairs

1. Attribute list, object archive for storage and query operations for small amounts of data

2, SQLite, coredata for big data query and wrong left

2. Introduction to SQLite

SQLite is a lightweight database, a relational database management system designed to be used in embedded devices

SQLite occupies very low resources and is ideal for use in mobile devices, and it is open source free

It was born in 2000, has nearly 20 years of experience, SQLite3 has been released, the official website is www.sqlite.org

3. Database statements

SQL statements (DDL, DML, DQL)

Ddl

Statement contains Create and drop

Creating tables and deleting table tables

Dml

The statements contain inserts, update, and delete, which are used to field, modify, and delete data in the table, which is what we often say

DQL

Its statements include SELECT, which is used to query the data

4 Specific statements

Ddl

Create a table

CREATE Table Table name (the Type of field 1 field 1, the Type of field 2 field 2);

Delete a table

DROP table table name;

Dml

inserting insert

INSERT into table name (Field 1, Field 2 ....) ) VALUES (value 1, value 2);

Remove Delete

DELETE from cousin name

If you delete the data, all values will be deleted if the condition is not deleted. If you want to delete the data according to certain criteria, you need a field where statement (and the two conditions to be met)

Update modification

The name SET of the UPDATE table modifies the modified field name = the value to be modified where filter condition;

If you need to modify the values of multiple fields at once, select the fields, separated by commas

DQL statements

Querying data in a table

Using the SELECT statement to query the data in the table, you can specify the fields of the query. If you need to query all the fields, use the wildcard character * instead

You can use the conditional statement where to set the filter criteria for the returned data

Sorting query Results

The query results can be specified by the keyword ORDER by to specify the sorting criteria. ASC Ascending desc Descending

Fuzzy query

Use like keywords to be able to perform fuzzy queries, typically using text type data

SELECT *form table name WHERE need to blur query field like ' Fuzzy query condition, use% to replace any character '

Set the amount of data displayed

In the case of many queries, if the query results are more, the query speed will be slower, and the result of the memory may occupy a larger

SELECT * FROM table name ORDER by age ASC LIMIT x, y;

The results of the query are sorted in ascending order, starting with x and showing Y data in the future

Constraints

Ability to add constraints to a field when creating a table and setting a field

Simple constraints

Cannot be empty

Not NULL is used to constrain a field. A field that is not null constrained cannot be set to NULL when it is inserted or modified using an SQL statement.

Only

In the same table, a field sets a unique value. Then in the field multiple data, cannot appear duplicates.

Default value

The default value can be set to a certain field, and if no specific reference is set, the default value

　　

PRIMARY KEY constraint

is used to differentiate between the encodings of different data in a data table. The entire code is unique in none of the tables. The following principles should be adhered to when designing the primary key

1. The primary key should not be meaningful to the user (no specific meaning of the school number, the Social Security number)

2. The value of the primary key cannot be modified, and once the primary key is generated, it cannot be changed.

3. The value of the primary key should be automatically generated by the system, only the primary key is automatically generated by the system, so that will complete the whole to ensure the integrity of the primary key

4. There can be at most one primary key in a data table

Automatic growth of primary keys

AutoIncrement modifier primary Key, can let the primary key automatically grow

1. When inserting data, manually set the value of the primary key to be able to set the primary key

2. If there is no manually set primary key, the primary key of the largest data in the previous table is automatically used

　　

External constraints

There are data links between the two tables. In order to be able to query the corresponding data through a table of data, can be a yo foreign key to associate

Table (ID name age), table (ID person ...)

Associate the ID in a as a foreign key and a person in B

Code

　　　　　　

Note: Using references keywords, you can bind foreign key associations. In general, binding a primary key in another table is the best choice. When inserting data, it is important to note that the value of the foreign key must be able to exist and only the corresponding data in the associated table

　　

Multi-table Joint search

In the case of a foreign key association, the query table needs to be in two tables and get the data at the same time. Need to use to win joint search

SELECT * Form 1 on table 1 field = relative to field in table 2

　　　　

If you want to learn more about database relationships, http://www.w3school.com.cn/sql/index.asp can study on this site.

I wrote this to learn iOS without any database basis to look at.

Preparation of database in iOS-simple database