IOS development-SQLite database (CRUD)

IOS development-SQLite database (CRUD)
Data storage in iOS

Plist (NSArrayNSDictionary)
Preference (Preference NSUserDefaults)
NSCoding (NSKeyedArchiverNSkeyedUnarchiver)
SQLite3
Core Data

SQLite Introduction

What is SQLite?
SQLite is a lightweight embedded database.
It occupies very low resources. In embedded devices, it may only need several hundred KB of memory.
It processes faster than Mysql and PostgreSQL, both famous databases.

What is a database?
A Database is a warehouse that organizes, stores, and manages data according to the data structure.
Databases can be divided into two types
Relational Database Service (mainstream)
Object-based database

Common relational databases
PC: Oracle, MySQL, SQL Server, Access, DB2, Sybase

How to store data

How does a database store data?
The storage structure of the database is similar to that of excel, in the unit of table

Steps for database data storage
Create a table)
Add multiple fields (column, column, attribute)
Add multi-row records (row stores the values of multiple fields in each row)

SQL statement

How to operate data in the database while the program is running
You must first learn to use SQL statements

What is SQL
Structured query language: structured query language
SQL is a language that defines and operates data in a relational database.
The SQL language is concise, simple syntax, and easy to use

What is an SQL statement?
The sentence code written in the SQL language is an SQL statement.
When running the program, you must use SQL statements to operate data in the database (add, delete, modify, query, and CRUD ).

Features of SQL statements
Case-insensitive (for example, the database considers that the user and UsEr are the same)
Each statement must end with a semicolon.

Common keywords in SQL include:
Select, insert, update, delete, from, create, where, desc, order, by, group, table, alter, view, index, etc.

Keywords cannot be used to name tables and fields in the database.

Types of SQL statements

Data Definition Language)
Including create and drop operations
Create or delete a table in the database (create table or drop table)

Data operation statement (DML: Data Manipulation Language)
Including insert, update, delete, and other operations
The preceding three operations are used to add, modify, and delete table data respectively.

Data Query Statement (DQL: Data Query Language)
It can be used to query and obtain table data.
The keyword select is the most commonly used operation of DQL (and all SQL statements ).
Other common DQL keywords include where, order by, group by, and having.

Create a table

Format: create table Name (field name 1 Field Type 1, field name 2 Field Type 2 ,...) ; Create table if not exists table name (field name 1 Field Type 1, field name 2 Field Type 2 ,...) ; Example create table t_student (id integer, name text, age inetger, score real)

Field Type

SQLite divides data into the following Storage types:
Integer: integer
Real: floating point value
Text: text string
Blob: binary data (such as files)

In fact, SQLite is non-typed.
String text can be stored even if declared as integer (except for primary keys)
When creating a table, you can declare any type or do not declare any type, which means that the table creation statement can be written as follows:
Create table t_student (name, age );

To maintain good programming specifications and facilitate communication between programmers, it is best to add the specific types of each field when writing table creation statements.

Delete table

Format: drop table name; drop table if exists table name; example: drop table t_student;

Insert)

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 ('mj ', 10); note that the string content in the database should be enclosed by single quotation marks

Update)

Format update table name set field 1 = value of Field 1, Field 2 = value of Field 2 ,... ; Example update t_student set name = 'jack', age = 20; note that the above example will change the name of all records in the t_student table to jack and age to 20

Delete)

Format: delete from table name. Example: delete from t_student. Note that all records in the t_student table will be deleted in the preceding example.

Condition Statement

If you only want to update or delete some fixed records, you must add a common format where field = a value for some conditional statements after the DML statement; // two = where fields are not allowed; // is equivalent to = where field! = A value; where field is not a value; // is not equivalent! = Where field> A value; where field 1 = a value and Field 2> A value; // and is equivalent to the & where field 1 = a value in C language or field 2 = a value; // or is equivalent to the C language |

For example, change the age of the t_student table to 5 update t_student set age = 5 where age> 10 and name for the records whose age is greater than 10 and whose name is not equal to jack! = 'Jack'; delete records whose ages are less than or equal to 10 in the t_student table or whose ages are greater than 30 from t_student where age <= 10 or age> 30; guess the role of the following statement: update t_student set score = age where name = 'jack'; change the value of the height field to the value of the age field for the record whose name is equal to haha in the t_student table.

DQL statement

Format: select Field 1, Field 2 ,... From table name; select * from table name; // select name, age from t_student; select * from t_student where age> 10; // conditional Query

Alias

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 alias. Field 1, alias. Field 2 ,... From table name alias; example: select name myname, age myage from t_student; give name an alias named myname, and give age an alias named myage select s. name, s. age from t_student s; give the t_student table an alias called s, and use s to reference fields in the table

Calculate the number of records

Format: select count (field) from table name; select count (*) from table name; example: select count (age) from t_student; select count (*) from t_student where score> = 60;

Sort

The query results can be sorted using order by: select * from t_student order by field; select * from t_student order by age; by default, the results are sorted in ascending order (from small to large ), it can also be changed to descending order (from large to small) select * from t_student order by age desc; // descending select * from t_student order by age asc; // ascending (default) you can also use multiple fields for sorting select * from t_student order by age asc, height desc; sort by age first (ascending), and sort by height (descending) when the age is equal)

You can use limit to precisely control the number of query results. For example, you can query only 10 data records at a time. For example, select * from table name limit value 1, value 2. For example, select * from t_student limit 4, 8. It can be understood that the first four statements are skipped and eight records are obtained.

Limit is often used for paging query. For example, if five data entries are displayed on each page, you should retrieve page 1st: limit 0, page 5 2nd: limit 5, page 5 3rd: limit 10, 5... Page n: limit 5 * (n-1), 5 guess the role of the following statement select * from t_student limit 7; equivalent to select * from t_student limit 0, 7; indicates taking the first seven records

Simple Constraints

When creating a table, you can set certain constraints for specific fields. Common constraints include not null: the specified field value cannot be nullunique: the specified field value must be unique default: create table t_student (id integer, name text not null unique, age integer not null default 1); The name field cannot be null, and the unique age field cannot be null, and the default value is 1.

Primary key constraint

If the name and age fields in the t_student table are the same and the values of the name and age fields in some records are the same, the data cannot be distinguished and the database records are not unique, in this way, it is inconvenient to manage data. Database Programming specifications should ensure the uniqueness of each record. Therefore, a primary key constraint is added, that is, each table must have a primary key, used to identify the uniqueness of a record. What is the Primary Key (PK) used to uniquely identify a record? For example, t_student can add an id field as the Primary Key, the primary key of a person's ID card can be one or more fields.

When creating a table, use primary key to declare a primary key create table t_student (id integer primary key, name text, age integer ); as long as the id of the integer type is declared as the primary key of the t_student table, it indicates that the primary key field of a primary key field contains the not null and unique constraints by default. If you want to make the primary key grow automatically (must be of the integer type ), autoincrementcreate table t_student (id integer primary key autoincrement, name text, age integer) should be added );

A foreign key constraint can be used to establish a link between a table and a table. Generally, a field in a table is used, 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); in the references t_class t_student table, a foreign key named fk_t_student_class_id_t_class_id is used to reference the id field of the t_class table using the class_id field in the t_student table.

Table connection Query

What is table join query? You need to join multiple tables to find the type of inner join or join of the desired data table connection. The results show that both the left and right tables have full field values) left outer join: left outer join (to ensure the integrity of the left table data) Example query all students in the 0316iOS class select s. name, s. age from t_student s, t_class c where s. class_id = c. id and c. name = '0316ios ';

SQLite database (CRUD) instance

# Import ViewController. h # import
  
   
@ Interface ViewController () {sqlite3 * _ db; // create a database instance}-(IBAction) insertData;-(IBAction) deleteData;-(IBAction) updateData;-(IBAction) selectData; @ end @ implementation ViewController-(void) viewDidLoad {[super viewDidLoad]; // 0. obtain the database file name NSString * fileName = [[NSSearchPathForDirectoriesInDomains (NSDocumentDirectory, NSUserDomainMask, YES) lastObject] stringByAppendingPathComponent: @ student. sqlite]; NSLog (@ fileName = % @, fileName); // 1. create (Open Database, automatically created if the database file does not exist) int result = sqlite3_open (fileName. UTF8String, & _ db); if (result = SQLITE_ OK) {NSLog (@ successfully opened the database); // 2. create table const char * SQL = create table if not exists t_student (id integer primary key autoincrement, name text, age integer); char * errorMessage = NULL; int result = sqlite3_exec (_ db, SQL, NULL, NULL, & errorMessage); if (result = SQLITE_ OK) {NSLo G (@ successfully created table);} else {NSLog (@ failed to create table: % s, errorMessage);} else {NSLog (@ failed to open database );}} -(void) didReceiveMemoryWarning {[super didreceivemorywarning]; // Dispose of any resources that can be recreated .} /*** insert data */-(IBAction) insertData {for (int I = 0; I <20; I ++) {NSString * name = [NSString stringWithFormat: @ student-% d, arc4random () % 100]; int age = arc4random () % 100; NSString * SQL = [NSString stringWithFormat :@ Insert into t_student (name, age) values ('% @', % d);, name, age]; char * errorMessage = NULL; int result = sqlite3_exec (_ db, SQL. UTF8String, NULL, NULL, & errorMessage); if (result = SQLITE_ OK) {NSLog (@ added data successfully);} else {NSLog (@ failed to add data: % s, errorMessage) ;}}/ *** delete data */-(IBAction) deleteData {// 1. define the SQL statement const char * SQL = delete from t_student where age = 9; // 2. define a stmt to store the result set sqlite3_stmt * stmt = NULL; // 3. check Test SQL statement validity int result = sqlite3_prepare_v2 (_ db, SQL,-1, & stmt, NULL); if (result = SQLITE_ OK) {// 4. execute the SQL statement and retrieve the data from the result set while (sqlite3_step (stmt) = SQLITE_ROW) {// obtain the data corresponding to this row // obtain the id int sid of The 0th column = sqlite3_column_int (stmt, 0 ); // obtain the name const unsigned char * sname = sqlite3_column_text (stmt, 1) of the first column; // obtain the age int sage = sqlite3_column_int (stmt, 1) of the 2nd column ); NSLog (@ % d % s % d, sid, sname, sage) ;}nslog (@ deleted successfully !);} Else {NSLog (@ deletion failed !); }/*** Update data */-(IBAction) updateData {// 1. define the SQL statement const char * SQL = update t_student set name = 'jack', age = 20; // 2. define a stmt to store the result set sqlite3_stmt * stmt = NULL; // 3. check SQL statement validity int result = sqlite3_prepare_v2 (_ db, SQL,-1, & stmt, NULL); if (result = SQLITE_ OK) {// 4. execute the SQL statement and retrieve the data from the result set while (sqlite3_step (stmt) = SQLITE_ROW) {// obtain the data corresponding to this row // obtain the id int sid of The 0th column = sqlite3_column_int (stmt, 0); // obtain the name c of the first column Onst unsigned char * sname = sqlite3_column_text (stmt, 1); // get the age int sage of the 2nd Column = sqlite3_column_int (stmt, 1); NSLog (@ % d % s % d, sid, sname, sage) ;}nslog (@ update successful !);} Else {NSLog (@ update failed !); }/*** Query data */-(IBAction) selectData {// 1. define the SQL statement const char * SQL = select id, name, age from t_student where name = ?;; // 2. define a stmt to store the result set sqlite3_stmt * stmt = NULL; // 3. check SQL statement validity int result = sqlite3_prepare_v2 (_ db, SQL,-1, & stmt, NULL); if (result = SQLITE_ OK) {NSLog (@ query statement valid ); // 4. execute the SQL statement and retrieve the data from the result set while (sqlite3_step (stmt) = SQLITE_ROW) {// obtain the data corresponding to this row // obtain the id int sid of The 0th column = sqlite3_column_int (stmt, 0 ); // obtain the name const unsigned char * sname = sqlite3_column_text (stmt, 1) of the first column; // obtain the age int sage = sqlite3_column_int (stmt, 1) of the 2nd column ); NSLog (@ % d % s % d, sid, sname, sage) ;}} else {NSLog (@ query statement invalid );}}