Ios-sqlite3 and Fmdb use

1 sqlite31> The syntax of SQLite

Management tools: Navicat

Data type
{
Text field
Integer number
Real floating point
Bolb binary allows complete data storage, such as photos
}

DDL (structure definition CREATE ALTER DROP)
{

CREATE
The name of the CREATE table table (the field name data type of each field defined ...) ）
CREATE TABLE T_CJB (xiaoming text not NULL,
Yuwen Integer not NULL)


}
DML (Data manipulation INSERT DELETE UPDATE SELECT)
{
INSERT
INSERT to Table name (field list) VALUES (list of values)
INSERT into Studnet (id,name) VALUES (3, ' Linf ') integer can be used without '

UPDATE
UPDATE table name SET field name = value ... WHERE condition
UPDATE studnet SET telnum = ' 13100000000 ' WHERE name = ' Xiaoming '

DELETE
DELETE from table name WHERE condition
DELETE from studnet WHERE name = ' Xiaoming '

SELECT
SELECT field list from table name WHERE condition ORDER by field name
SELECT Telnum (as phone number) from studnet WHERE name = ' Xiaoming '
SELECT * from studnet WHERE name = ' xiaoming '//Display all columns
SELECT * from studnet WHERE name like ' Zhang% '//fuzzy query, like
SELECT * from Studnet WHERE age>20 and age<30//Show all columns, *
SELECT * from Studnet WHERE age>20 orders by age//Sort, older than 20 from small to large rows
SELECT * from studnet where age>20 orders by age desc//sorted, older than 20 from large to small rows

Statistical functions (data type Intege real)
SELECT Count (*) from studnet WHERE age>20 and age<30//Seek number
SELECT sum (age) from Studnet//Request Total
SELECT avg (age) from studnet//averaging
SELECT *,yuwen+shuxue+yingyu as sum from student//sum of individual columns

To use SQLite3 in iOS, first add the library file libsqlite3.dylib and import the primary header file

2> creating a database and a creation table

    //  Open Database (connect to database)     nsstring *filename = [[ Nssearchpathfordirectoriesindomains (Nsdocumentdirectory, nsuserdomainmask, yes)  lastObject]  stringbyappendingpathcomponent:@ "Shops.sqlite"];    //  if the database file does not exist,  The system automatically creates a file that automatically initializes the database     int status = sqlite3_open (filename. utf8string, &_db);    if  (STATUS == SQLITE_OK)  { //   Open Success         nslog (@ "Open database Success");                 //  Create a table          const char *sql =  "Create table if not exists t _shop  (id integer primary key, name text not null, price  real); ";         char *errmsg = null;        sqlite3_exec (Self.db, sql,  null, null, &errmsg);        if  (errmsg)  {            nslog (@ "Genesis failure--%s",  errmsg) ;        }    } else { //  Open failed         nslog (@ "Failed to open database");     }

3>cud

Sqlite3_exec () can execute any SQL statements, such as Create tables, update, insert, and delete operations. However, it is generally not necessary to execute a query statement because it does not return the queried data

    //Inserting Data     NSString *sql = [NSString  stringwithformat:@ "Insert into t_shop (name, price)  VALUES  ('%@ ',  %f);",  Self.namefield.text, self.pricefield.text.doublevalue];    sqlite3_exec (Self.db,  sql. Utf8string, null, null, null)         //Insert data with placeholder      char *sql =  "Insert into t_person (name, age)  values (?,  ?); ";     SQLITE3_STMT *STMT = NULL; // STMT is used to remove query results      if  (SQLITE3_PREPARE_V2 (db, sql, -1, &stmt, null)  == sqlite_ OK)  { //-1 is the length of the automatically computed SQL statement         sqlite3_bind_text (stmt,  1,  "hen",  -1, null);         sqlite3_bind_int (stmt,  2, 27);     }    if  (Sqlite3_step (stmt)  != sqlite_done)  {         nslog (@ "Insert data error");    }     sqlite3_finalize (stmt); Code resolution: SQLITE3_PREPARE_V2 () The return value equals SQLITE_OK, stating that the SQL statement is ready for success with no syntax issues     Sqlite3_bind_text (): Most of the binding functions have only 3 parameters the 1th parameter is the position of the sqlite3_stmt * type 2nd parameter refers to the placeholder, the position of the first placeholder is 1, Not 0 The 3rd parameter refers to the value that the placeholder is bound to. The 4th parameter refers to the length of the data passed in the 3rd parameter, and for the C string, you can pass 1 instead of the length of the string 5th parameter is an optional function callback, which is generally used to complete the memory cleanup work after statement execution

4> Querying data

    sqlite3_step () returns the Sqlite_row representative to traverse to a new record     sqlite3_column_* () To get the corresponding value for each field, the 2nd parameter is the index of the field, starting with 0        const char *sql =  " select name,price from t_shop; ";     // stmt is the     sqlite3_stmt *stmt = null to take out the results of the query ;    //  Prepare     INT STATUS = SQLITE3_PREPARE_V2 ( Self.db, sql, -1, &stmt, null);    if  (status ==  SQLITE_OK)  { //  prepare successfully  -- sql statement correctly         while   (Sqlite3_step (stmt)  == sqlite_row)  { //  successfully pulls out a piece of data              const char *name =  (const char *) Sqlite3_column_text (stmt, 0);             const char *price =  (const char *) Sqlite3_column_text (stmt, 1 );                         HMShop *shop = [[HMShop alloc] init];             shop.name = [NSString  Stringwithutf8string:name];            shop.price  = [NSString stringWithUTF8String:price];             [self.shops addObject:shop];        }     }

Fuzzy query of 5> with Uisearchbar

#pragma  mark - UISearchBarDelegate-  (void) Searchbar: (uisearchbar *) searchbar  Textdidchange: (nsstring *) searchtext{    [self.shops removeallobjects];         nsstring *sql = [nsstring stringwithformat:@ " select name,price from t_shop where name like  '%%%@%% '  OR   price like  '%%%@%% '  ; ', searchtext, searchtext];    //  stmt is the     sqlite3_stmt *stmt = null;    //to take out the results of the query   Prepare     INT STATUS = SQLITE3_PREPARE_V2 (self.db, sql. Utf8string, -1, &stmt, null);    if  (Status == SQLITE_OK )  { //  prepare successfully  -- sql statement correctly         while  ( Sqlite3_step (stmt)  == sqlite_row) &nbSp { //  successfully fetched a data             const char  *name =  (const char *) sqlite3_column_text (stmt, 0);             const char *price =  (const char  *) Sqlite3_column_text (stmt, 1);                         hmshop *shop = [[hmshop  alloc] init];            shop.name  = [NSString stringWithUTF8String:name];             shop.price = [NSString stringWithUTF8String:price];             [self.shops addObject:shop];         }&Nbsp;   }        [self.tableview reloaddata];} 

2 fmda1> What is FMDA

Fmdb encapsulates the C language API of SQLite in OC mode

Advantages of Fmdb
Use more object-oriented, save a lot of cumbersome, redundant C language code
More lightweight and flexible compared to Apple's own core data framework
Provides a multi-threaded secure database operation method to effectively prevent data confusion

Fmdb's GitHub Address
Https://github.com/ccgus/fmdb

2>fmdb has three main classes

1) fmdatabase
A Fmdatabase object represents a single SQLite database
Used to execute SQL statements

2) Fmresultset
Result set after query execution with Fmdatabase

3) Fmdatabasequeue
Used to execute multiple queries or updates in multiple threads, which is thread-safe

3> creating or opening a database

Fmdatabase *db = [Fmdatabase databasewithpath:path];if (![ DB Open] {NSLog (@ "Database open failed!) ");}

An empty string @ "" Creates an empty database in the temp directory. The database file is also deleted when the Fmdatabase connection is closed
Nil creates a temporary in-memory database that is destroyed when the Fmdatabase connection is closed

4> Performing Updates

In Fmdb, all operations except queries are called "updates"
Create, DROP, insert, UPDATE, delete, and more

To perform an update using the Executeupdate: method

-(BOOL) Executeupdate: (nsstring*) SQL, ...-(BOOL) Executeupdatewithformat: (nsstring*) format, ...-(BOOL) Executeupdate: (nsstring*) SQL Withargumentsinarray: (Nsarray *) arguments

the SQL statement string in example Fmdb does not need to be added '

[DB executeupdate:@ "UPDATE t_student SET age =? WHERE name =?; ", @20, @" Jack "]

5> executing a query

Query method

-(Fmresultset *) ExecuteQuery: (nsstring*) SQL, ...-(Fmresultset *) Executequerywithformat: (nsstring*) format, ...-( Fmresultset *) ExecuteQuery: (NSString *) SQL Withargumentsinarray: (Nsarray *) arguments

Example. Traversing result set

Fmresultset *rs = [db executequery:@ "select * from T_student"];//traverse result set while ([Rs next]) {NSString *name = [Rs STRINGF    orcolumn:@ "Name"];    int age = [rs intforcolumn:@ ' age ']; Double score = [Rs doubleforcolumn:@ "score"];}

6> multi-threaded asynchronous execution of multiple data operations

Fmdatabase This class is thread insecure, if you use a Fmdatabase instance in multiple threads, it can cause data confusion and other problems
To ensure thread safety, Fmdb provides quick and easy Fmdatabasequeue class

1) Creation of Fmdatabasequeue

Fmdatabasequeue *queue = [Fmdatabasequeue Databasequeuewithpath:path];

2) Simple to use

[Queue indatabase:^ (Fmdatabase *db)    {[DB executeupdate:@ INSERT into t_student (name) ', @ ' Jack '];    [DB executeupdate:@ INSERT into T_student VALUES (?) ", @" Rose "];        [DB executeupdate:@ INSERT into t_student (name) VALUES (?) ", @" Jim "];    Fmresultset *rs = [db executequery:@ "select * from T_student"]; while ([Rs next]) {//...}];

3) Using Transactions

[Queue intransaction:^ (Fmdatabase *db, BOOL *rollback)    {[DB executeupdate:@ INSERT into t_student (name) ', @ ' Jack '];    [DB executeupdate:@ INSERT into T_student VALUES (?) ", @" Rose "];        [DB executeupdate:@ INSERT into t_student (name) VALUES (?) ", @" Jim "];    Fmresultset *rs = [db executequery:@ "select * from T_student"]; while ([Rs next]) {//...}]; Transaction rollback *rollback = YES;

Ios-sqlite3 and Fmdb use