SQLite3 Basic Use Method (i)

I. Introduction of SQLITE3
SQLite3 is an open-source, embedded relational-type database that is portable, easy to use, and has low memory overhead.
SQLite3 is untyped, which means that any type of data can be kept to any field in any table.
SQLite3 commonly used in the 5 data types: Text/integer/float/boolean/blob.

Second, add the library
To use SQLite3 in iOS, you need to add a library file: Libsqlite3.bylib and import the primary header file, which is a C-language library.

third, the use of the steps:
1. Create a database (SQLITE3_OPENDB)
2. Single Step operation (sqlite3_exec)
-Create a database table
-Data Manipulation
• Inserting Data
• Update Data
• Delete Data
3. Query Operations
-sqlite3_prepare_v2 Check SQL legitimacy
-Sqlite3_step Get query results row by line
-sqlite3_coloum_xxx get the corresponding type of content
-Sqlite3_finalize Release stmt

Four, Demo

. h

#import <UIKit/UIKit.h>#import <sqlite3.h>@interface  msviewcontroller: uiviewcontroller{    //Sqlite3 database link, based on the link can be database operation    sqlite3 * m_pdb; @end

. m

#import "MSViewController.h"#import<sqlite3.h>@interfaceMsviewcontroller ()@end@implementationMsviewcontroller- (void) viewdidload{[Super Viewdidload]; //1. Create a database[self opendb]; //2. Create a database table[self createtable]; //3. Data Manipulation//Add//[Self adduserwithname:@ "YY" pass:@ "yy135"]; //Enquiry    /*Nsmutablearray * arr = [self selectalluser];    for (int i=0; i<arr.count; i++) {NSLog (arr[i]); }*/        //Modify//[self updateuserid:2 name:@ "wangxin" pass:@ "wangxin000"]; //Delete[Self Deleteuserid:2]; }///users/username/library/application support/iphone simulator/"sandbox path"//Open the database and create it if it does not exist. - (void) opendb{//To generate a database full path that is stored in a sandboxNSString * Strdocdir = Nssearchpathfordirectoriesindomains (NSDocumentDirectory, Nsuserdomainmask, YES) [0]; NSString* strDbName = [Strdocdir stringbyappendingpathcomponent:@"mysqlite3db.db"]; //A link to the sqlite3 database, based on which you can perform database operations    if(Sqlite_ok = = Sqlite3_open (strdbname.utf8string, &m_pdb)) {NSLog (@"Create/Open database successfully! "); }    Else{NSLog (@"failed to create/Open database! "); }}//Create a database table//use Dbmanage to create and assign the generated code to be OK. //table name: Tbl_user//IF not EXISTS//IOS set the ID to self-increment, when adding data, also use a null stance, can not write. - (void) createtable{NSString* strSQL =@"CREATE TABLE IF not EXISTS tbl_user (Id integer not NULL PRIMARY KEY autoincrement unique,username text,userpass text) "; Char*perrormsg; if(Sqlite_ok = = Sqlite3_exec (m_pdb, strsql.utf8string, NULL, NULL, &perrormsg)) {NSLog (@"Create data table successfully!"); }    Else{NSLog (@"failed to create data table!"); } }//Increase- (void) Adduserwithname: (NSString *) StrName Pass: (NSString *) strpass{NSString* strSQL = [NSString stringWithFormat:@"INSERT into Tbl_user VALUES (null, '%@ ', '%@ ')", StrName, Strpass]; Char*perrormsg; if(Sqlite_ok = = Sqlite3_exec (m_pdb, strsql.utf8string, NULL, NULL, &perrormsg)) {NSLog (@"Add success!"); }    Else{NSLog (@"Add failed!"); }}//Check-(Nsmutablearray *) selectalluser{NSString* strSQL =@"SELECT * from Tbl_user"; Nsmutablearray* Arrreturn =[Nsmutablearray array]; //1. Evaluate whether the prepared SQL syntax is correctSQLITE3_STMT * pstmt =NULL; if(Sqlite_ok = = Sqlite3_prepare_v2 (m_pdb, strsql.utf8string,-1, &pstmt, NULL)) {NSLog (@"SQL syntax is correct! "); //2. If you can query normally, call the step execution method, get the query result sequentially//If you get a row of records         while(Sqlite_row = =Sqlite3_step (pstmt)) {            //3. Get/Show Query results            intNId = Sqlite3_column_int (pstmt,0); ConstUnsignedChar* Pusername = Sqlite3_column_text (pstmt,1); NSString* PUserNameUTF8 = [NSString stringwithutf8string: (Const Char*) Pusername]; ConstUnsignedChar* Puserpass = Sqlite3_column_text (pstmt,2); NSString* PUserPassUTF8 = [NSString stringwithutf8string: (Const Char*) Puserpass]; NSString* strtemp = [NSString stringWithFormat:@"%d--%@--%@", Nid,pusernameutf8, pUserPassUTF8];        [Arrreturn addobject:strtemp]; }    }    Else{NSLog (@"SQL syntax error!"); }    //4. Release the handlesqlite3_finalize (PSTMT); returnArrreturn;}//Change- (void) Updateuserid: (intNId Name: (NSString *) strUserName Pass: (NSString *) struserpass{NSString* strSQL = [NSString stringWithFormat:@"UPDATE tbl_user SET username= '%@ ', userpass= '%@ ' where id=%d", strUserName, Struserpass, nId]; Char*perrormsg; if(Sqlite_ok = = Sqlite3_exec (m_pdb, strsql.utf8string, NULL, NULL, &perrormsg)) {NSLog (@"Add success!"); }    Else{NSLog (@"Add failed!"); }}//Delete- (void) Deleteuserid: (int) nid{NSString* strSQL = [NSString stringWithFormat:@"Delete from tbl_user where Id =%d", NId]; Char*perrormsg; if(Sqlite_ok = = Sqlite3_exec (m_pdb, strsql.utf8string, NULL, NULL, &perrormsg)) {NSLog (@"Add success!"); }    Else{NSLog (@"Add failed!"); }}@end

Summarize:

1. Almost the same as MSSQL. SQL is basically the same.

is to create the table ID when the increment, when inserting, to use a null stance, and MSSQL is different.

2. Add, delete, and change the code to refactor. Execution is a function.

3. Question: How do I manage the db files on my real-time computer?