Use of database Fmdb for iOS development

This article mainly introduces the common software of iOS database and common database operation methods. 1. Introduction of basic knowledge of database

Small data volume, simple structure, data storage can be archived, plist (XML format), the efficiency is very low;

The data is complex, the format is many, the quantity is large, with the database access efficiency is high, for example the data storage of the test item;

Local Data---> Network data---> local (fetching data once, fetching data multiple times)

1.1 Common databases

(1). MicroSoft SQL Server ... 2000/2008 Small and medium enterprises use more

(2). Oracle, more complex, large enterprise use more

(3). Mysql database, Web site use more

(4). SQLite: Above 3 is a network database, send network request to get data, SQLite is a local database, not through the network, direct access to files, fast.

Simple enough to occupy small space, suitable for use on the moving segment. But the function is not complete enough compared with other large databases. In, QQ is widely used. (Synchronous push software)

2, Mesasqlite software use

Can learn more about database principles, database operations knowledge.

Example: Using data to store information about a class student

Number ID user name username password PassWord Score score

1 Zhangsan 123 100

2 Lilei 321 200

(1). Create a database

(2). Create a data table for storage

(3). Design data table, add multiple fields/columns

(4). Database commonly used operations, additions and deletions to check

3. Structured query statements for SQL

SQL: Refers to the structure query language, structured querying language, used to manipulate the database (create tables, data and other additions and deletions to change).

3.1 Creating a Table

Create Table integer varchar (varchar()

Inserting data into a table

Insert   intoStudentinfomation (Sid,username,password,score)Values(1501,'Zhangsan','123',' -') Insert   intoStudentinfomation (Sid,username,password,score)Values(1502,'Lisi','1234',' the'); Insert   intoStudentinfomation (Sid,username,password,score)Values(1503,'Wangwu','321',' -') ;Insert   intoStudentinfomation (Sid,username,password,score)Values(1504,'xiaoming','123456',' -') ;

3.2 Queries

Select  from Studentinfomation

3.3 Query for specified conditions

Select   *  from where = ' Zhangsan '

3.4 Query based on multiple criteria

Select *  from  where = ' Zhangsan '  and password='123'

3.5 in ascending or descending order

Descending

Select *  from Order  by desc

Ascending

Select *  from Order  by

3.6 Number of data queries

Select Count (* from Studentinfomation

3.7 Modifying data

Set = '  - ' where = ' Zhangsan ';

3.8 Deleting data

Delete  from where = ' 1503 '

3.9 Query Table Data count

Select Count (* from Studentinfomation

4.FMDB Operational Database 4.1 configuration

(1)/import file, iOS security mechanism-sandbox.

(2). Each app is placed under a sandbox, and each app can only modify files in its own sandbox directory, and other files cannot be modified.

(3). Default folder: Documents,library,tmp. After development, the files you create will be placed under documents.

Create a database

    [Self createandinitdatabase];-(void) createandinitdatabase{NSString*Path=[nsstring stringwithformat:@ "%@/documents/stuinfo.sqlite", Nshomedirectory ()]; _DataBase =[[Fmdatabase alloc]Initwithpath:path]; if(!_DataBase.Open{NSLog (@ "Open failed"); return;    } NSLog (@ "open successfully"); }

Create a table

    [Self createtable];-(void) createtable{NSString*Sql=@"Create Table if  not exists"" Studentinfo (SIDinteger, "" Usernamevarchar( -), "" Passwordvarchar( -), "" Scorevarchar( -));";//ExecuteQuery used to execute a SELECT statement//other statements using execut BOOL b= [_database Executeupdate:sql]; NSLog (@ "CreateB=%d ", b); }

Inserting data

 [Self insertdata];-(void) insertdata{intSid=1501; NSString*Username=@ "Zhangsan"; NSString*Password=@"123"; NSString*Score=@" -"; NSString*Sql=@"Insert  intoStudentinfo (Sid,username,password,score)Values(?,?,?,?)";    //The question mark is passed to the string type for each parameter, and other types need to be converted to string BOOL b=[_database executeupdate:sql,[nsstring stringwithformat:@ "%d", Sid], Username,password,score]; NSLog (@ "InsertData b==%d ", b); }

Querying data

    [Self querydata];-(void) querydata{NSString*Sql=@"Select * fromStudentinfo "; //Next method gets one record at a time, gets no return nil,next just beginning to point to-1 Row Fmresultset*ResultSet=[_database Executequery:sql];  while([ResultSet Next]) {NSLog (@ "Sid= %@,name=%@,pw=%@,score=%@",[resultSet stringforcolumn:@ "Sid"],[resultSet stringforcolumn:@ "username"],[resultSet stringforcolumn:@ "password"],[resultSet stringforcolumn:@ "Score"]); }}

5. Database + single case design mode.

Create a singleton, store the data obtained from the database, and then call the data from the agent.

#import "DatabaseManager.h"#import "FMDatabase.h"@interfaceDatabasemanager () {fmdatabase*_database;}@end@implementationDatabasemanager+(ID) sharedinstance{StaticDatabasemanager *DC =Nil; if(dc==Nil) {DC=[[[selfclass] [alloc] init]; }    returnDC;}-(ID) init{if(self =[Super Init])    {[Self opendatabase]; }    returnSelf ;}-(void) opendatabase{NSString*path =[[nsbundle Mainbundle] Pathforresource:@"Data.sqlite"Oftype:nil]; _database=[[Fmdatabase alloc] initwithpath:path]; if(!_database.open) {NSLog (@"Open Failed"); }}//get the first level of data-(Nsarray *) firstlevels{nsstring*sql =@"SELECT * from Firstlevel"; Fmresultset*resultset =[_database Executequery:sql]; Nsmutablearray*marr =[[Nsmutablearray alloc] init];  while([ResultSet next]) {//Firstlevelmodel *model =[[Firstlevelmodel alloc] init]; Model.pid=[resultset Stringforcolumn:@"PID"]; Model.pname=[resultset Stringforcolumn:@"pname"]; Model.pcount=[resultset Stringforcolumn:@"Pcount"];         [Marr Addobject:model]; }    returnMarr;}

Use of database Fmdb for iOS development