An explanation of the use of the iOS SQLite

Characteristics of the database:

Stored together in a certain way
Can be shared for multiple users
With as few redundant code as possible
Datasets that are independent of each other from the program
Sqlite

SQLite is a lightweight relational database, originally designed to be used in embedded systems, and it consumes very little resources. In iOS, you only need to include the Li ' blibsqlite3.0 dependency and the introduction of the Sqlite3.h header file.
SQLite is an untyped database that can hold any type of data, and it is completely valid for SQLite to not specify a type for a field
SQLite approximate similar rules

If the type string contains "INT", then the relationship of the field is an integer
If the type string contains "CHAR", "CLOB" or "text", then the relative type of the field is TEXT, such as varchar
If the type string contains "BLOB", then the affinity type of the field is None
If the type string contains "real", "Floa" or "Doub", then the affinity type of the field is real
In other cases, the affinity type of the field is numeric
Constraints for SQLite fields

Not null-– non-empty
unique--Unique
Primary key--PRIMARY Key
FOREIGN key ——— foreign key
Check ——— condition checks to ensure that all values in a column meet certain conditions
default--Default
autoincrement-self-increment variable, this field data if the integer type can automatically add 1
SQLite field constraints: PRIMARY key-primary key

First, each record in the data table has a primary key, which is like our identity card number, and in turn each primary key corresponds to a data record, so the primary key must be unique
Second, the primary key is also an index, so the primary key to find records faster
Thirdly, in a relational type library, a table's primary key can act as a foreign key to another table, so that the relationship between the two tables is established by this key
Finally, the primary key is generally an integer or a string, as long as it is guaranteed to be unique, in SQLite, if the primary key is an integer type, the value of the column can automatically grow
SQLite statements

Build table commands (CREATE TABLE)
Write a picture description here

Data Insertion Command (insert)
Write a picture description here

Database Update command (UPDATA)
Write a picture description here

Database Delete command (delete)
Write a picture description here

Database Retrieval command (SELECT)
Write a picture description here

The implementation of iOS database technology:

Code:

#pragma mark-1. Introduction of <sqlite3.h> header files
Add Libsqlite3.0.tbd

#import <sqlite3.h>

Static Sqlite3 *db;//is a pointer to the database, and all of our other operations are done with this pointer.

#pragma mark-2. Open the Database

-(void) Opensqlite {
Determine if the database is empty, if it is not empty the description is already open
if (db! = nil) {
NSLog (@ "Database already open");
Return
}

Get file path
NSString *str = [Nssearchpathfordirectoriesindomains (nsdocumentdirectory, Nsuserdomainmask, YES) firstObject];
NSString *strpath = [str stringbyappendingpathcomponent:@ "My.sqlite"];
NSLog (@ "%@", strpath);
Open Database
If the database exists, open it, and if it does not, create one and then open it.
int result = Sqlite3_open ([strpath utf8string], &db);
Judge
if (result = = SQLITE_OK) {
NSLog (@ "Database open successfully");
} else {
NSLog (@ "Database open failed");
}
}

#pragma mark-3. Adding and deleting changes
Create a table
-(void) CreateTable {
1. Prepare SQLite statements
NSString *sqlite = [nsstring stringwithformat:@ "CREATE table if not EXISTS ' student ' (' number ' integer primary key autoinc Rement not NULL, ' name ' text, ' sex ' text, ' age ' integer '];
2. Execute SQLite statements
Char *error = null;//fails when performing sqlite statements, it stores the reason for failure
int result = SQLITE3_EXEC (db, [SQLite utf8string], nil, nil, &error);
Whether the 3.sqlite statement executes successfully

if (result = = SQLITE_OK) {
NSLog (@ "CREATE table succeeded");
} else {
NSLog (@ "Failed to create TABLE");
}
}

Add data
-(void) Addstudent: (Student *) Stu {
1. Prepare SQLite statements
NSString *sqlite = [NSString stringwithformat:@ "INSERT into student (Number,name,age,sex) VALUES ('%ld ', '%@ ', '%@ ', '%ld ') ) ", Stu.number,stu.name,stu.sex,stu.age];
2. Execute SQLite statements
Char *error = null;//fails when performing sqlite statements, it stores the reason for failure
int result = SQLITE3_EXEC (db, [SQLite utf8string], nil, nil, &error);
if (result = = SQLITE_OK) {
NSLog (@ "Add data success");
} else {
NSLog (@ "Add data failed");
}
}

Delete data
-(void) Delete: (student*) Stu {
1. Prepare SQLite statements
NSString *sqlite = [NSString stringwithformat:@ "Delete from student where number = '%ld '", Stu.number];
2. Execute SQLite statements
Char *error = null;//fails when performing sqlite statements, it stores the reason for failure
int result = SQLITE3_EXEC (db, [SQLite utf8string], nil, nil, &error);
if (result = = SQLITE_OK) {
NSLog (@ "Delete data succeeded");
} else {
NSLog (@ "Delete data failed%s", error);
}
}

modifying data
-(void) Updatawithstu: (Student *) Stu {
1.sqlite statements
NSString *sqlite = [nsstring stringwithformat:@ "update student set name = '%@ ', sex = '%@ ', age = '%ld ' where number = '%ld ' ", Stu.name,stu.sex,stu.age,stu.number];
2. Execute SQLite statements
Char *error = null;//fails when performing sqlite statements, it stores the reason for failure
int result = SQLITE3_EXEC (db, [SQLite utf8string], nil, nil, &error);
if (result = = SQLITE_OK) {
NSLog (@ "Modify data success");
} else {
NSLog (@ "Failed to modify data");
}
}

Querying all data
-(nsmutablearray*) Selectwithstu {
Nsmutablearray *array = [[Nsmutablearray alloc] init];
1. Prepare SQLite statements
NSString *sqlite = [NSString stringwithformat:@ "SELECT * from student"];
2. Companion pointers
sqlite3_stmt *stmt = NULL;
3. Pre-Execute SQLite statements
int result = Sqlite3_prepare (db, SQLite. Utf8string, 1, &stmt, NULL);//The 4th parameter is a one-time return of all parameters, 1
if (result = = SQLITE_OK) {
NSLog (@ "Query success");
4. Execute n times
while (Sqlite3_step (stmt) = = Sqlite_row) {
Student *stu = [[Student alloc] init];
Get data from the companion pointer, column NO. 0
Stu.number = Sqlite3_column_int (stmt, 0);
Get data from the companion pointer, column 1th
Stu.name = [NSString stringwithutf8string: (const char *) Sqlite3_column_text (stmt, 1)];
Get data from the companion pointer, column 2nd
Stu.sex = [NSString stringwithutf8string: (const char *) Sqlite3_column_text (stmt, 2)];
Get data from the companion pointer, column 3rd
Stu.age = Sqlite3_column_int (stmt, 3);
[Array Addobject:stu];
}
} else {
NSLog (@ "Query failed");
}
5. Close the companion pointer
Sqlite3_finalize (stmt);
return array;
}

#pragma mark-4. Close the database
-(void) Closesqlite {

int result = Sqlite3_close (db);
if (result = = SQLITE_OK) {
NSLog (@ "database shutdown succeeded");
} else {
NSLog (@ "Database shutdown failed");
}
}

An explanation of the use of the iOS SQLite