iOS development data Cache-Database

How data is stored in iOS

• Plist (Nsarray\nsdictionary)
• Preference (Preferences \nsuserdefaults)
• Nscoding (Nskeyedarchiver\nskeyedunarchiver)
• SQlite3
• Core Date

Plist, Preference, nscoding storage methods

See how iOS Development files are stored

How the database is stored

Core Date: Core data is a framework that appears after IOS5, which provides an object-relational mapping (ORM) function that enables the conversion of OC objects into data, stored in SQLite database files, It is also possible to restore data saved in the database to OC objects. During this data operation, we do not need to write any SQL statements. Because the function is too strong, so the performance of the loss is also relatively large, in this first not to introduce the core data processing method, about core data and SQLite choice, refer to the use of SQLite and FMDB without Core data.

SQLite:

1. What is SQLite

   • SQLite is a lightweight, embedded database
   • It occupies very low resources, in the embedded device, may only need hundreds of K of memory is enough
   • It's faster than the two well-known databases of MySQL and PostgreSQL.

2. What is a database

   • A database is a warehouse that organizes, stores, and manages data in accordance with its data structure.
   • Databases can be divided into two main categories: relational databases (mainstream) and object-based databases

3. Common relational database

   • Sqlite
   • Mysql
   • Oracle

4. How a database stores data : The database's storage structure is similar to Excel, in tables (table).

5. Steps for storing data in a database

   • Create a new form (table)
   • Add multiple fields (column, columns, properties)
   • Add multiple rows of records (row, each row holds values for multiple fields)

Introduction to SQL Statements

features of the 1.SQL statement :

• Case insensitive

• Each statement must end with a semicolon

Common keywords in 2.SQL

• Select, insert, UPDATE, Delete, from, create, where, desc, order, by, Group, table, Alter, view, index, and so on

3. You cannot use keywords to name tables, fields in a database

Types of SQL statements

1. Data definition statements (ddl:data definition Language)

   • Includes operations such as create and drop
   • Create a new table or delete a table in the database (CREATE TABLE or droptables)

1. Data Manipulation statements (Dml:data manipulation Language)

   • Include insert,update,delete , and more
   • The above 3 actions are used to add, modify, and delete data in a table, respectively

2. Data Query statement (dql:data query Language)

   • can be used to query for data in a table
   • Keyword Select is the most used operation for DQL (and all SQL)
   • Other dql commonly used keywords are where,order by,group by and having

DDL data definition

1. Create a Watch

   • Format (generic table name prefixed with t_)

     • CREATE TABLE table name (Field name 1 field type 1, Field Name 2 field type 2, ...);
     • The CREATE table if not EXISTS table name (field name 1 field type 1, Field Name 2 field type 2, ...);

   • Field type

     • Integer: Integer value
     • Real: floating-point value
     • Text: literal string
     • BLOB: Binary data (such as files)

   • Example

       create table t_student (id integer, name text, age inetger, score real) ;

2. Delete Table

   • Format

     • drop table name;
     • drop table if exists name;

   • Example

     • drop table t_student;

DML Data manipulation

1. Inserting data (insert)

   • Format:数据库中的字符串内容应该用单引号 ’ 括住

     • Insert into table name (Field 1, Field 2, ...) VALUES (Value of field 1, Value of field 2, ...);

   • Example

       insert into t_student (name, age) values (‘kingsly‘, 20) ;

2. Updating data (update)

   • Format
     • Update table name set field 1 = value of field 1, field 2 = value of field 2, ...;

   • Example

       update t_student set name = ‘Roger’, age = 34 ;

   • Results
     • Change the name of all records in the T_student table to Roger,age to 34

3. Deleting data (delete)

   • Format
     • Delete from table name;

   • Example

        delete from t_student;

   • Results
     • Delete all records for all fields in the T_student table table

DQL data Query

2. Format

   • Select field 1, Field 2,... from table name;

   • SELECT * from table name; Querying all fields

4. Example

    select name,age frome t_student; select * frome t_student;

6. Rename Field name

   • Format

     • Select field 1 alias, Field 2 alias, ... from table name alias;
     • Select field 1 alias, Field 2 as Alias, ... from table name as alias;
     • Select field 1 alias, Field 2 as Alias, ... from table name as alias;

   • Example

     • Give name a nickname called MyName, an alias called Myage for Age.

         select name myname, age myage from t_student ;

     • Give T_student an individual name called S and use s to refer to the fields in the table

         select s.name, s.age from t_student s ;

8. Statistics
   • Format
     • Select count [field] Frome table name;
     • Select Count [*] Frome table name
   • Example
     • Select count (age) from T_student;

Conditional restrictions in SQL statements

1. If you only want to update or delete some fixed records, you must add some conditions after the DML statement

2. Common formats for conditional statements

   • where field = a value; cannot be used for two x =
   • where field! = a value;
   • Where field > a value;
   • where field 1 = a value and field 2 > a value; and equivalent to the && in C language
   • where field 1 = a value or field 2 = a value; or equivalent to the C language | |

3. Example

   • Change the age of the T_student table to more than 10 and the name does not equal Jack's record to 5

       update t_student set age = 5 where age > 10 and name != ‘jack’ ;

Sort

1. SELECT * from T_student the order by field;
2. The results of the query can be sorted with order by

    select * from t_student order by age ;

1. The default is to sort in ascending order (from small to large), or to descending (from large to small)

    select * from t_student order by age desc ;  //降序 select * from t_student order by age desc ;  //降序

2. You can also sort by multiple fields

   Sort by age (ascending), and age equal by height (descending)

    select * from t_student order by age asc, height desc ;

Limit Query Quantity

1. Use limit to precisely control the number of query results

2. Format

   • SELECT * from table name limit value 1, value 2;

3. Example

   • After the 5th data, then take 10 records

        select * from t_student limit 5, 10 ;

Building table Constraints

2. When you build a table, you can set some constraints on specific fields, and the common constraints are
   • Not null : The value of the specified field cannot be null
   • Unique : The value of the specified field must be unique
   • Default: Specify a value for the field

4. Example

   • The name field cannot be null and unique, the age field cannot be null, and the default is 1

       create table t_student (id integer, name text not null unique, age integer not null default 1) ;

6. Primary key

   • Primary Key , used to uniquely identify a record

   • In the T_student table, if a field is not set as the primary key, it is unavoidable that the name and age are exactly equal in several records, so an identity is required to differentiate, such as the IDof the person's ID, To distinguish between people of the same name and age.

   • The primary key can be one or more fields

   • The primary key should not affect the data recorded by the user, preferably by the computer automatically generated primary key

8. Declaration of the primary key

   • when creating a table, declare a primary key with primary key , eg: Use an integer ID field as the primary key for the T_student table

       create table t_student (id integer primary key, name text, age integer) ;

   • The primary key field contains not null and a unique two constraint by default

   • Let the integer type of the primary key automatically grow, need to add autoincrement, generally let the primary key automatically increase ease of management

       create table t_student (id integer primary key autoincrement, name text, age integer) ;

10. FOREIGN KEY constraints

    • A FOREIGN KEY constraint can be used to establish a table-to-table connection: One of the fields in a table comes from a field in another table.

    • Class field for student tables, from the ID field in the class table

        create table t_student (id integer primary key autoincrement, name text, age integer, class_id integer, constraint fk_t_student_class_id_t_class_id foreign key (class_id) (id)) ; references t_class

12. Table Join Query

    • If you need to federate multiple tables to find the data you want, you will typically need to use a table connection

    • Types of table joins

      • Inner joins: INNER JOIN or join (displays records with a full field value in the left and right tables)

      • Left outer connection: Left OUTER join (guarantees the integrity of data on the table)

    • Example: Querying network Engineering all students in Class 2

        select s.name,s.age from t_student s, t_class c where s.class_id = c.id and c.

iOS development data Cache-Database