SQLite Simple operation

2016-11-05

Database Introduction The role of the database

Used to do offline data caching

Data caching policies

• plist, 归档 , 偏好设置 , 沙盒文件 ,SQLite数据库

The drawbacks of system-provided data storage methods

• Inconvenient to manipulate large amounts of data
  • If you want to add new data, you must first load the old data into memory.
  • The system provides a way 覆盖存储 to store the data, and the new data will overwrite the old data.

• Inconvenient to find large amounts of data
  • When the amount of data is very large, it is very difficult to query some of the data.

• For example, to store the array data in the Plist file, when the amount of data is large, whether the storage or lookup data is quite inconvenient.
  • When the amount of data in a plist file is very large, a one-time load into memory can cause memory spikes
  • When the amount of data in a plist file is very large, it is difficult to query one of these data

About SQLite

• SQLite is a lightweight database

  • The design goal is embedded (can be placed in the cell phone sandbox)
  • Small footprint, less resources
  • Fast processing speed
  • Cross-platform

• Official website: http://www.sqlite.org/

• Client's database:SQLite

• Server-side database: MySQL , SQLServer ,Oracle

Here's a walkthrough of the Xcode Native SQLite demo

Creating database and Table usage preparation

• Import Header File"#import <sqlite3.h>"
• New Class Librarylibsqlite3.0.tbd

Use steps

1.准备数据库存储的路径2.创建数据库文件并打开3.如果创建数据库文件成功,就创建数据库表4.创建数据库表成功就可以操作数据库 (增删改查)

Tips

• sqlite3The functions in are all sqlite3 beginning with the

• To create and open a database function:sqlite3_open

• Build tables and data manipulation functions (except query functions):sqlite3_exec

• Query function:sqlite3_prepare_v2

• When querying data, you get a result set, the data to be queried is in the result set, and the while loop iterates through the result sets, a record is taken out

function declaration

Create a database file

/*参数1 : 数据库的存储路径参数2 : 数据库实例返回值 : int*/sqlite3_open(<#const char *filename#>, <#sqlite3 **ppDb#>);

Functions for creating database tables

/*参数1 : 数据库实例参数2 : 要执行的SQL语句(建表)参数3 : 建表成功的回调,传入NULL参数4 : 回调的参数,传入NULL参数5 : 错误信息*/sqlite3_exec(<#sqlite3 *#>, <#const char *sql#>, <#int (*callback)(void *, int, char **, char **)#>, <#void *#>, <#char **errmsg#>);

Code Demo

Creating databases and Tables

全局的数据库实例

{    sqlite3 *_db;}

创建数据库函数sqlite3_open

1.这个函数会自动监测数据库是否已经存在，只有数据库不存在的时候才会去新建数据库并打开
2.创建和打开数据库成功之后，就可以创建数据库表

Create Database-(void) createdb{/* Create and open a database this function will automatically monitor whether the database is already present, and only when the database is not saved to the new database and open the database parameter 1: Database path Parameter 2: DB instance return value int */1. Database pathNSString *sqlpath = [[Nssearchpathfordirectoriesindomains (NSDocumentDirectory, Nsuserdomainmask,YES) Lastobject] stringByAppendingPathComponent:@ "Sql.db"];2. Functions to create and open a databaseint result = Sqlite3_open (SQLPath. Utf8string, &_db);Determine if the database is created open successfullyif (result = = SQLITE_OK) {nslog (@ "Database Creation opened successfully"); /* table parameter 1: db instance Parameter 2: SQL statement to build table Parameter 3: function executes a successful callback, does not need to write null parameter 4: Callback parameter, no write NULL parameter 5: Save error message returned Value int *///3. Build table SQL statement nsstring *createsql = @" CREATE table if not exists T_person (ID integer primary key,name text not null,age integer); "; //Save error message char *errmsg = NULL; Sqlite3_exec (_db, Createsqlnull, null, &errmsg); if (errmsg = nil) {NSLog (@ "Build Table Success"); }} //4. Action table}           

Database of data additions and deletions check the operation of new records

- (void)insert{    // 新增SQL语句    NSString *insertSQL = @"insert into t_person(name,age) values(‘老王‘,21);";    // 保存错误信息 char *errmsg = NULL; sqlite3_exec(_db, insertSQL.UTF8String, NULL, NULL, &errmsg); if (errmsg == nil) { // 获取影响的行数 int changes = sqlite3_changes(_db); NSLog(@"insert影响的行数 %d",changes); NSLog(@"新增成功"); }}

Deleting records

• When a record is deleted, it does not return an error if the data does not exist

- (void)delete{    // 删除SQL语句    NSString *deleteSQL = @"delete from t_person where id = 4;";    // 保存错误信息 char *errmsg = NULL; sqlite3_exec(_db, deleteSQL.UTF8String, NULL, NULL, &errmsg); if (errmsg == nil) { // 获取影响的行数 int changes = sqlite3_changes(_db); NSLog(@"delete影响的行数 %d",changes); NSLog(@"删除成功"); }}

Modify a record

• If the specified ID does not exist when the record is updated, no error is returned

 -(void) update{//Modify SQL statements Span class= "hljs-built_in" >nsstring *updatesql = @ "Update T_person set name = ' Li lei ' where id = 2;    "; //Save error message char *errmsg = NULL; Sqlite3_exec (_db, Updatesqlnull, null, &errmsg); if (errmsg = nil) {//gets the number of rows affected Span class= "Hljs-keyword" >int changes = Sqlite3_changes (_db); nslog (@ "number of rows affected by update%d", changes); nslog (@ "modified successfully");}}        

 

Query precompilation: Check the validity of SQL syntax

- (void) query{/* Execute Query statement parameter 1: db instance Parameter 2: query statement parameter 3: Length of query statement, incoming-1, let function calculate parameter 4: Result set, traverse result set, fetch data parameter 5: General pass in null return value int *///query SQL statement nsstring *querysql = @ " Select Name,age from T_person; "; //result set sqlite3_stmt *ppstmt = null; //Execute query statement int result = Sqlite3_prepare_v2 (_db, Querysql1, &ppstmt, null); //precompilation: Check the validity of the syntax if (result = = SQLITE_OK) {nslog (@" query data Failed ");} else {nslog (@ "query data Failed");} //release result set: otherwise memory leaks sqlite3_finalize (ppstmt);}        

Note here: Releasing the result set would otherwise be a memory leak sqlite_finalize (PPSTMT);

- (void) query{/* Execute Query statement parameter 1: db instance Parameter 2: query statement parameter 3: Length of query statement, incoming-1, let function calculate parameter 4: Result set, traverse result set, fetch data parameter 5: General pass in null return value int */Querying SQL statementsNSString *querysql =@ "Select Name,age from T_person;";Result set sqlite3_stmt *ppstmt =NULL;Execute Query statementint result = SQLITE3_PREPARE_V2 (_db, Querysql. Utf8string,-1, &ppstmt,NULL);Precompilation: Checking the validity of the syntaxif (result = = SQLITE_OK) {Iterate through the result set, fetch the data, until you find the data.while (Sqlite3_step (ppstmt) = = Sqlite_row) {/* Parameter 1: Result set parameter 2: Index of the field, starting counting from the primary key *///take name const unsigned char *cname = Sqlite3_column_text (ppstmt, 0); nsstring *ocname = [[nsstring Alloc] initwithcstring: (const char *) CName encoding:nsutf8stringencoding]; //take age int age = Sqlite3_column_int (ppstmt, 1); nslog (@ "%@--%d", ocname,age);}} else {nslog (@ "query data Failed");} //release result set: otherwise memory leaks sqlite3_finalize (ppstmt);}        

SQLite Simple operation