iOS Advanced (Database SQLite)

1.SQLite statements

CREATE table table name in existing database (ID integer primary key, name text notNULL, sex textdefault"male") inserting data into a table insert into table name (name, sex) the values ("stream", "male") fields can be no order, but the value must be guaranteed to correspond, and the value of the sex field is not set at this time because we have set the gender to The default. Insert into table name values ( One, "Ba Da", "demon") must ensure that the value set is the same as the field order in the table modify the information for one record in the table Update table nameSetname = "hehe", sex = "female"whereID =1Query Statement Select Id,name,sex The result of the From table name query is the same as the field order of your query. Select*The From table name queries all the field information in the table, and the result of the query is the same as the order of the fields in your table select* FROM table namewhereID =1The following can be queried for specific desired information with the Select* FROM table namewhereID >1and sex ="Female" can be connected with multiple conditions and multiple conditions, and the same up all records are found at this time. If more than one condition is connected with or, the results are only required to satisfy one of them. Select* FROM table namewhereName like "% Source%""As long as the name contains the source, can be queried to delete the record deleted from the table namewherename = "Stream"

2. Operational database steps

Create a class to manipulate the database (note to import Libsqlite3.dylib first)

StaticSqlite3 *db =Nil;//Open Database+ (Sqlite3 *) open{//The main function of this method is to open the database, to be exact, to connect the database//Square Drawing is a database pointer//because this database pointer is used in many SQLite APIs (functions), we declare a class method to get it, which is more convenient        if(db! =Nil) {        returnDB; }        //Get documents FileNSString *docpath =[Nssearchpathfordirectoriesindomains (NSDocumentDirectory, Nsuserdomainmask, YES) lastobject]; //build the location of the database file in the sandboxNSString *sqlpath = [DocPath stringbyappendingpathcomponent:@"Studb.sqlite"]; //Get File Management ObjectsNsfilemanager *filemanager =[Nsfilemanager Defaultmanager]; //determine if there is a database file in the sandbox path and if no copy operation is performed    if([filemanager fileexistsatpath:sqlpath] = =NO) {        //get the file for the database file in the packageNSString *filepath = [[NSBundle mainbundle] Pathforresource:@"Studb"OfType:@"SQLite"]; //using a file management object to complete a management object[FileManager Copyitematpath:filepath topath:sqlpath Error:nil]; }    //    //Create a database pointer//sqlite3 *db = nil; //The following functions are required to open the database//The first parameter is a database path//The second argument is a pointer to the database pointerSqlite3_open ([SQLPath utf8string], &db); returnDB;}//Close the database+ (void) close{//Close the databasesqlite3_close (DB); //Empty Database pointerdb =Nil; }

Open Database- - Create statement Object ( save associated Database, execute SQL statement,length of SQL statement, etc.)- - line fetch data, and will read out C to OC language- - save data into the model and store it in an array- - Don't forget to release the statement object at the end

//Find a student+ (Student *) Findstudentbyid: (int) id{//Open DatabaseSqlite3 *db =[DB Open]; Sqlite3_stmt*STMT =Nil; Student*student =Nil; intresult = SQLITE3_PREPARE_V2 (db,"SELECT * from Students where ID =?", -1, &stmt, nil); if(Result = =SQLITE_OK) {        //if a query statement or other SQL statement is conditional, inside the function that prepares the statement object, SQL uses the? To replace the condition, it must be bound before the statement executes.Sqlite3_bind_int (stmt,1, ID);//The second parameter represents the first question mark, and the subscript of the question mark starts at 1 .        if(Sqlite3_step (stmt) = =Sqlite_row) {            //get field information in a record            ConstUnsignedChar*cname = Sqlite3_column_text (stmt,1); ConstUnsignedChar*cgender = Sqlite3_column_text (stmt,2); //converting a C string into an OC stringNSString *name = [NSString stringwithutf8string: (Const Char*) CName]; NSString*gender = [NSString stringwithutf8string: (Const Char*) Cgender]; Student=[Student studentwithid:id name:name Gender:gender]; }    }    //Release the statement object firstsqlite3_finalize (stmt); returnstudent;}

iOS Advanced (Database SQLite)