How data is stored in iOS

• Plist (Nsarry\nsdictionary)
• Preference (Preference Settings \nsuserdefaults
• Nscoding (Nskeyedarchiver\nskeyedunarchiver)
• SQLite3
• Core Data

SQLite3

• 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
  Its processing speed is faster than the two famous databases of MySQL and PostgreSQL.

• What is a database
  A database is a warehouse that organizes, stores, and manages data in accordance with its data structure.
  The database can be divided into 2 major categories
  relational database (Mainstream)
  Object Type Database

• Common relational database
  PC-side: Oracle, MySQL, SQL Server, Access, DB2, Sybase
  Embedded \ Mobile Client: SQLite

How to store data

• How a database stores data

  • The storage structure of the database is similar to Excel, in table (table) units

• Steps for storing data in a database

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

Navicat

• Navicat is a well-known database management software that supports most major databases (including SQLite
• Use NAVICAT to establish a database connection?

Build table

?
?

Command+s Save the name of the fill-in table

?

View DDL

?

Execute SQL statement

?

SQL statements

• How to manipulate data in a database while the program is running
  You have to learn to use SQL statements first.

• What is SQL

  • SQL (Structured Query Language): Structured Query Language
  • SQL is a language for defining and manipulating data in a relational database
  • SQL language concise, simple syntax, studious and useful

• What is a SQL statement

  • The sentence \ code written in the SQL language is the SQL statement
  • In the process of running the program, you must use SQL statements to manipulate (add and remove, CRUD) data in the database

• Features of SQL statements

  • case insensitive (e.g. the database thinks the user and user are the same)
  • Each statement must have a semicolon; End

• Commonly used keywords in SQL are

select、insert、update、delete、from、create、where、desc、order、by、group、table、alter、view、index等等

• You can not use keywords to name tables, fields in a database

Types of SQL statements

• 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 drop tables)

• 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

• 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

Field type

• SQLite divides the data into the following types of storage:

integer : 整型值real : 浮点值text : 文本字符串blob : 二进制数据（比如文件）

• In fact, SQLite is untyped.

  • The ability to store string literals (except primary keys), even if declared as an integer type
  • It is possible to declare what type or not to declare a type when building a table, which means that the statement can be written like this:
  • create table t_student(name, age);

• In order to maintain good programming specifications and facilitate communication between programmers, it is best to add the specific type of each field when writing a table statement.

Create a Watch

• Format

  create table 表名 (字段名1 字段类型1, 字段名2 字段类型2, …) ;create table if not exists 表名 (字段名1 字段类型1, 字段名2 字段类型2, …) ;

• Example

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

Delete a table

• Format

drop table 表名 ;drop table if exists 表名 ;

• Exampledrop table t_student ;

Inserting data (insert)

• Format

insert into 表名 (字段1, 字段2, …) values (字段1的值, 字段2的值, …) ;

• Example

insert into t_student (name, age) values (‘zhangsan’, 10) ;

• Attention
  • The string contents in the database should be enclosed in single quotes

Updating data (update)

• Format

set 字段1 = 字段1的值, 字段2 = 字段2的值, … ; 

• Example

update t_student set name = ‘jack’, age = 20 ; 

• Attention
  • The previous example changes the name of all records in the T_student table to Jack,age to 20

Deleting data (delete)

• Format

delete from 表名 ;

• Example

delete from t_student ;

• Attention
  • The above example deletes all the records in the T_student table

Conditional statements

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

• Conditional Statements Common formatting

where field = some value; //cannot use two =where field is a value; //is equivalent = where field! = a value; where field is not a value; //is not equivalent to! = where field > a value; where field 1 = some value and field 2 > A value; //and corresponds to the C language &&where field  1 = a value or field 2 = a value; //or equivalent in C language | |            

Conditional Statement Practice

• 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’ ;

• Delete records with age less than or equal to 10 or older than 30 in the T_student table

delete from t_student where age <= 10 or age > 30 ;

• Guess the effect of the following statement

update t_student set score = age where name = ‘jack’ ;

• Change the name in the T_student table to the Jack record, and the value of the score field to the value of the age field

DQL statements

• Format

select 字段1, 字段2, … from 表名 ;select * from 表名; // 查询所有的字段

• Example

select name, age from t_student ;select * from t_student ;select * from t_student where age > 10 ; // 条件查询

Alias from

• Format (fields and tables can be aliases)

select 字段1 别名 , 字段2 别名 , … from 表名 别名 ; select 字段1 别名, 字段2 as 别名, … from 表名 as 别名 ;select 别名.字段1, 别名.字段2, … from 表名 别名 ;

• Example

select name myname, age myage from t_student ;

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

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

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

Calculate the number of records

• Format

select count (字段) from 表名 ;select count ( * ) from 表名 ;

• Example

select count (age) from t_student ;select count ( * ) from t_student where score >= 60;

Sort

• The results of the query can be sorted with order by

select * from t_student order by 字段 ;select * from t_student order by age ;

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 asc ; // 升序（默认）

• You can also sort by multiple fields

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

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

Limit

• Use limit to precisely control the number of query results, such as querying only 10 data at a time

• Format

select * from 表名 limit 数值1, 数值2 ;

• Example

select * from t_student limit 4, 8 ;

Can be understood as: Skip the first 4 statements, then fetch 8 records

The limit is often used for paging queries, such as 5 data per page, so you should take the data

1th page: Limit 0, 5
2nd page: Limit 5, 5
3rd page: Limit 10, 5
...
Page N: Limit 5* (n-1), 5

• Guess the effect of the following statement

select * from t_student limit 7 ;相当于select * from t_student limit 0, 7 ;

Indicates the first 7 records are taken

Simple constraints

• 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: Specifies the value of the field (recommended: Try to set strict constraints on the field to ensure the normative data)

• Example

create table t_student (id integer, name text not null unique, age integer not null default 1) ;name字段不能为null，并且唯一age字段不能为null，并且默认为1

PRIMARY KEY constraint

• If the T_student table is in the name and age two fields, and some records have the same name and the value of the Date field, then the data cannot be distinguished, resulting in a database that is not unique, which makes it inconvenient to manage the data

• A good database programming specification should ensure uniqueness of each record, adding a PRIMARY KEY constraint

  • In other words, each table must have a primary key to identify the uniqueness of the record

• What is a primary key

  • Primary KEY (Primary key, referred to as PK) uniquely identifies a record
  • For example, t_student can add an ID field as the primary key, which is equivalent to a person's ID
  • The primary key can be a field or multiple fields

Principle of primary key design

• The primary key should not be meaningful to the user

• Never update the primary key

• The primary key should not contain dynamically changing data

• The primary key should be automatically generated by the computer

Declaration of the primary key

• Declare a primary key with primary key when creating a table

create table t_student (id integer primary key, name text, age integer) ;integer类型的id作为t_student表的主键

• Primary key field

  • As long as primary key is declared, it is a primary key field
  • The primary key field contains not NULL and a unique two constraint by default

• If you want the primary key to grow automatically (must be an integer type), you should increaseautoincrement

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

FOREIGN KEY constraints

• Using FOREIGN KEY constraints can be used to establish a table-to-table connection

  • The general case of a foreign key is a field in a table that refers to the primary key field of another table

• Create a new foreign key

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) references t_class (id)); t_student表中有一个叫做fk_t_student_class_id_t_class_id的外键

• The role of this foreign key is to refer to the ID field of the T_class table using the class_id field in the T_student table

Table Join Query

• What is a table join query

  • You need to federate multiple tables to find the data you want.

• 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

  • Check all students in 6 (1) classes

select s.name,s.age from t_student s, t_class c where s.class_id = c.id and c.name = ‘6(1)’;

How data is stored in iOS