IOS: Use of local database Sqlite3

First, the concept of the database: 1., what is a databaseSQL Server 2010, Oracle, MySQLrelational databaseNoSQL Database-Non-relational databaseThe database consists mainly of tablestables are made up of fieldsdata is the record in the tableRelationship between tables: a pair of one or one-to-many (order: order header and order details)FOREIGN Keyprimary key: Indexing, cannot be duplicated2. SQL (Structured Query language) statementsA, SQL statement for data manipulation:2.1 Queries 　　　 Select (field name) from (table name); 　　　WHERE clause: Defining query Criteria 　　　Sort: Order By field name (ASC,DESC) 　　　Grouping: Group By field name2.2 DeleteDelete from (table name) WHERE name = @ "Admin";2.3 Inserting insert into (table name) (field list) VALUES (field values corresponding to field lists);2.4 UpdateUpdate (table name) Set field = value WHERE clause;B. SQL statements maintained on the database:To Create a table:CREATE TABLE (table name) (definition field: Field name fields type);sqlite3 Database name//Enter a local database under terminal (automatically create one if it does not exist). Schema table name //under terminal You can quickly see all the fields in a table . Table //under Terminal to view all tables created . Exit/.quit//Exit a database under a terminalSecond, the basic use of SQLiteThe common data types supported by SQLite are shown below. –integer signed integer type–real Floating-point types–text String type, encoded with UTF-8 and UTF-16 characters–blob Binary Large object type, capable of storing any binary data (in C) use steps: 1. When you create a new project, import the system framework (C language) first. (libsqlite3) 2. header File #import<sqlite3.h> 3. Sqlite3_open (filename.utf8string, &_db); Open or create a data

*_db define a member variable of the sqlite3 itself. To use when making a search for additional deletions

 4.sqlite3_exec (_db, SQL, NULL, null,&error);//No statement with result set, only operation on table, no result returned* This function can be used for insert,delete,update operation. 5. Query Operation SELECT.The query statement with the result set will return the result, and the data queried from the table will be placed in the stmt structure*SQLITE3_PREPARE_V2 (_db, SQL,-1, &stmt, NULL);prepare for the query before checking that the SQL statement is correct.*sqlite3_step (stmt) extracts the data from the query and extracts one at a time.//through loops, all data can be retrieved*sqlite3_column_text (stmt, 0) takes out the data in column No. 0. 6. Close the database Sqlite3_close (Sqlite3 *);————————————————————————————————————————————————————————————————————————————————————————————————————————execute an SQL statement with parameters

NSString *sqlstr = @ "INSERT OR REPLACE into note (cdate,content) VALUES (?,?)";

Sqlite3_stmt *statement;
preprocessing process, producing result sets
if (SQLITE3_PREPARE_V2 (DB, [Sqlstr utf8string],-1, &statement,

NULL) = = SQLITE_OK)

{

NSDateFormatter *dateformatter = [[NSDateFormatter alloc] init]; [Dateformatter setdateformat:@ "Yyyy-mm-dd HH:mm:ss"];
nsstring *nsdate = [Dateformatter stringFromDate:model.date];

//Bind parameter start
Sqlite3_bind_text (statement, 1, [NSDate utf8string],-1, NULL); Sqlite3_bind_text (statement, 2, [Model.content utf8string],

-1, NULL);

//Perform insert
if (sqlite3_step (statement)! = Sqlite_done)

{

Nsassert (NO, @ "failed to insert data. "); }

}

}

Clean up result sets to prevent memory leaks

Sqlite3_finalize (statement);

Singleton mode: (this is used primarily to ensure that the initialized database is unique, as long as it is created once, it will not be recreated)

+ (notedao*) Sharedmanager

{

Static dispatch_once_t once;

Dispatch_once (&once, ^{

Sharedmanager = [[Self alloc] init];

[Sharedmanager createeditablecopyofdatabaseifneeded]; }

);

return sharedmanager;

}

IOS: Use of local database Sqlite3