IOS SQLite uses

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)

• Data Insertion Command (insert)

• Database Update command (UPDATA)

• Database Delete command (delete)

• Database Retrieval command (SELECT)

The implementation of iOS database technology:

Code:

#pragma mark-1. Introduction of <sqlite3.h> header filesAdd 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 Database-(void) Opensqlite {Determine if the database is empty, if it is not empty the description is already openif (db! =Nil) {NSLog (@"Database is already open");Return }Get file pathNSString *str = [Nssearchpathfordirectoriesindomains (NSDocumentDirectory, Nsuserdomainmask,YES) Firstobject];NSString *strpath = [Str stringbyappendingpathcomponent:@"My.sqlite"];NSLog (@"%@", strpath);Open DatabaseIf the database exists, open it, and if it does not, create one and then open it.int result = Sqlite3_open ([strpath utf8string], &db);Judgeif (result = = SQLITE_OK) {NSLog (@"Database open successfully"); }else {NSLog (@"Database open failed"); }}#pragma mark-3. Adding and deleting changesCREATE Table-(void) CreateTable {1. Prepare SQLite statementsNSString *sqlite = [NSString stringwithformat:@"CREATE table if not EXISTS ' student ' (' number ' integer primary key autoincrement not NULL, ' name ' text, ' sex ' text, ' age ' int Eger) "];2. Execute SQLite statementsChar *error =NULL;When the SQLite statement fails, it stores the reason for the failure.int result = SQLITE3_EXEC (db, [SQLite utf8string],NilNil, &error);Whether the 3.sqlite statement executes successfullyif (result = = SQLITE_OK) {NSLog (@"CREATE table Success"); }else {NSLog (@"CREATE TABLE Failed"); }}Add Data-(void) Addstudent: (Student *) Stu {1. Prepare SQLite statementsNSString *sqlite = [NSString stringwithformat:@"INSERT into student (Number,name,age,sex) VALUES ('%ld ', '%@ ', '%@ ', '%ld ')", Stu. number,stu. name,stu. sex,stu. Age];2. Execute SQLite statementsChar *error =NULL;When the SQLite statement fails, it stores the reason for the failure.int result = SQLITE3_EXEC (db, [SQLite utf8string],NilNil, &error);if (result = = SQLITE_OK) {NSLog (@"Add data Success"); }else {NSLog (@"Add data Failed"); }}Delete Data-(void) Delete: (student*) Stu {1. Prepare SQLite statementsNSString *sqlite = [NSString stringwithformat:@"Delete from student where number = '%ld '", Stu. number];2. Execute SQLite statementsChar *error =NULL;When the SQLite statement fails, it stores the reason for the failure.int result = SQLITE3_EXEC (db, [SQLite utf8string],NilNil, &error);if (result = = SQLITE_OK) {NSLog (@"Delete data success"); }else {NSLog (@"Delete data failed%s", error); }}Modify Data-(void) Updatawithstu: (Student *) Stu {1.sqlite statementsNSString *sqlite = [NSString stringwithformat:@"UPDATE student Set name = '%@ ', sex = '%@ ', age = '%ld ' where number = '%ld ', Stu. name,stu. sex,stu. age,stu. number];2. Execute SQLite statementsChar *error =NULL;When the SQLite statement fails, it stores the reason for the failure.int result = SQLITE3_EXEC (db, [SQLite utf8string],NilNil, &error);if (result = = SQLITE_OK) {NSLog (@"Modify data Success"); }else {NSLog (@"Failed to modify data"); }}Query all data-(nsmutablearray*) Selectwithstu {Nsmutablearray *array = [[Nsmutablearray alloc] init];1. Prepare SQLite statementsNSString *sqlite = [NSString stringwithformat:@"SELECT * from student"];2. Companion pointer sqlite3_stmt *stmt =NULL;3. Pre-Execute SQLite statementsint result = Sqlite3_prepare (db, SQLite. Utf8string,-1, &stmt,NULL);The 4th parameter is to return all parameters at once, using-1if (result = = SQLITE_OK) {NSLog (@"Query Success");4. Execute n timeswhile (Sqlite3_step (stmt) = = Sqlite_row) {Student *stu = [[Student alloc] init];Get data from adjoint pointer, No. 0 column Stu. Number = Sqlite3_column_int (stmt,0);Get data from adjoint pointer, 1th column Stu. name = [NSString stringwithutf8string: (Constchar *) Sqlite3_column_text (stmt,1)];Get data from adjoint pointer, 2nd column Stu. sex = [NSString stringwithutf8string: (Constchar *) Sqlite3_column_text (stmt, 2)]; //get data from companion pointers, 3rd column Stu.age = Sqlite3_column_int (stmt, 3); [Array Addobject:stu]; }} else {nslog (@ "query Failed");} //5. Close 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 (@else {nslog (@ "database shutdown failed");}}   

IOS SQLite uses