SQLite3 of data storage for iOS development (including Fmdb)

1 , overview

SQLite3 is an open-source, embedded relational database with good portability, ease of use, and low memory overhead.

SQLite3 is untyped, meaning that you can save any type of data to any field in any table. For example, the following statement of the creation is legal:

CREATE table T_person (name, age);

To ensure readability, it is recommended that you add the field type:

CREATE table T_person (name text, age integer);

SQLite divides the data into the following types of storage:

Integer: Integer value

Real: floating-point value

Text: literal string

BLOB: Binary data (such as files)

In order to maintain good programming specifications and facilitate communication between programmers, it is best to add the specific type of each field when writing a table statement.

in the IOS used in SQLite3 , you first add the library file Libsqlite3.dylib and import the primary header file.

2 , Increase (gen) Table

Format:

(1) Create TABLE table name (Field name 1 field type 1, Field Name 2 field type 2, ...);

(2) Create table if not EXISTS name (field name 1 field type 1, Field Name 2 field type 2, ...);

For example:

CREATE table t_student (ID integer, name text, age Inetger, score Real)

3 , delete table

Format:

(1) drop table name;

(2) The name of the drop table if exists;

For example:

drop table t_student;

4 , insert data ( Insert )

Format:

Insert into table name (Field 1, Field 2, ...) VALUES (Value of field 1, Value of field 2, ...);

For example:

Insert into T_student (name, age) VALUES (' MJ ', 10);

The string contents in the memo database should be enclosed in single quotes

5 , update data ( Update )

Format:

Update table name set field 1 = value of field 1, field 2 = value of field 2, ...;

For example:

Update t_student Set name = ' Jack ', age = 20;

The preceding example changes the name of all records in the T_student table to Jack,age to 20.

6 , delete data ( Delete )

Format:

Delete from table name;

For example:

Delete from t_student;

"Remarks" The example above will delete all the records in the T_student table

7 , conditional statements

If you want to update or delete only some of the fixed records, you must add some conditions to the statement.

Common formats for conditional statements:

where field = a value; cannot be used for two x =

The Where field is a value; is equals =

where field! = a value;

The Where field is not a value; is isn't equivalent to! =

Where field > a value;

where field 1 = a value and field 2 > a value; and equivalent to the && in C language

where field 1 = a value or field 2 = a value; or equivalent to the C language | |

For example:

Delete from T_student where is age <= or age > 30;

8 , database Queries ( Select )

Format:

(1) Select field 1, Field 2, ... from table name;

(2) SELECT * from table name; Query all fields

For example:

Select name, age from T_student;

SELECT * from T_student;

SELECT * from T_student where age > 10; Conditional query

9 , alias

Format (fields and tables can be aliases):

(1) Select field 1 alias, Field 2 alias, ... from table name alias;

(2) Select field 1 Alias, Field 2 as Alias, ... from table name as alias;

(3) Select Alias. Field 1, alias. Field 2, ... from table name alias;

For example:

Select name MyName, age myage from T_student;

Give name a nickname called MyName, an alias called Myage for Age.

Select S.name, s.age from T_student s;

Give T_student an individual name called S and use s to refer to the fields in the table

Ten , calculate the number of records

Format:

(1) Select count (field) from table name;

(2) Select COUNT (*) from table name;

For example:

Select count (age) from T_student;

Select COUNT (*) from T_student where score >= 60;

One , sort

The results of the query can be sorted with order by:

(1) SELECT * FROM T_student the order by field;

For example:

SELECT * from T_student order by age;

The default is to sort in ascending order (from small to large), or to descending (from large to small)

(1) SELECT * FROM T_student ORDER by age desc ; Descending

(2) SELECT * FROM T_student ORDER BY age ASC ; Ascending (default)

You can also sort by multiple fields:

(1) SELECT * FROM T_student ORDER BY age ASC, height desc;

Sort by age (ascending), and age equal by height (descending)

A , Limit

Use limit to precisely control the number of query results, such as querying only 10 data at a time.

Format:

SELECT * from table name limit value 1, value 2;

For example:

SELECT * from T_student limit 4, 8;

Can be understood as: Skip the first 4 statements, then fetch 8 records

The limit is often used for paging queries, such as 5 data per page fixed, so you should take the data:

1th page: Limit 0, 5

2nd page: Limit 5, 5

3rd page: Limit 10, 5

...

Page N: Limit 5* (n-1), 5

For example:

SELECT * from t_student limit 7;

Equivalent to select * from T_student limit 0, 7;

Indicates the first 7 records are taken

- , simple constraints

When you build a table, you can set some constraints on specific fields, and the common constraints are:

Not NULL: The value of the specified field cannot be null

Unique: The value of the specified field must be unique

Default: Specify a value for the field

(recommendation: As far as possible to set strict constraints on the field to ensure the normative data)

For example:

CREATE table t_student (ID integer, name text NOT null-unique, age integer NOT null default 1);

The name field cannot be null, and the unique

The age field cannot be null, and the default is 1

- , PRIMARY KEY constraints

If the T_student table is in the name and age two fields, and some records have the same name as the value of the Date field, then the data cannot be distinguished, causing the database records to be unique, which makes it inconvenient to manage the data.

A good database programming specification should ensure the uniqueness of each record, adding a PRIMARY key constraint.

In other words, each table must have a primary key to identify the uniqueness of the record.

Introduction to the PRIMARY key:

A primary key (Primary key, referred to as PK) is used to uniquely identify a record.

For example, t_student can add an ID field as the primary key, which is equivalent to a person's ID.

The primary key can be a field or multiple fields.

Primary KEY design principles:

The primary key should not be meaningful to the user

Never update the primary key

The primary key should not contain dynamically changing data

The primary key should be automatically generated by the computer

Declaration of the primary key:

When creating a table, declare a primary key with primary key:

CREATE table t_student (ID integer primary key, Name text, age integer);

The ID of the integer type as the primary key for the T_student table

Primary key fields:

As long as primary key is declared, the description is a primary key field.

The primary key field contains not NULL and a unique two constraint by default.

If you want the primary key to grow automatically (must be an integer type), you should increase the AutoIncrement:

CREATE table t_student (ID integer primary key autoincrement, name text, age integer);

the , FOREIGN KEY constraints

The use of FOREIGN key constraints can be used to establish a table-to-table connection. The general case of a foreign key is a field in a table that refers to the primary key field of another table.

Create a new foreign key:

CREATE table t_student (ID integer primary key autoincrement, name text, age integer, class_id integer, constraint fk_t_st udent_class_id_t_class_id foreign Key (CLASS_ID) (ID)); References T_class

There is a foreign key called fk_t_student_class_id_t_class_id in the T_student table.

The role of this foreign key is to refer to the ID field of the T_class table using the class_id field in the T_student table

- , table join query

What is a table join query:

You need to federate multiple tables to find the data you want.

Type of table connection (not required):

(1) Inner connection: INNER JOIN or join (shows a record that both the left and right tables have a full field value)

(2) Left outer connection: the LEFT OUTER join (guarantees the integrity of the data in the table)

For example:

All students in the 0316iOS class are queried:

Select S.name,s.age from T_student s, t_class c where s.class_id = c.id and c.name = ' 0316iOS ';

- , creating, opening, and closing databases (creating databases)

Create or open a database

Path is: ~/documents/person.db

sqlite3 *db;

int result = Sqlite3_open([path utf8string], &db); Note that the DB address is passed in

Code parsing:

(1) Sqlite3_open () will open the database based on the file path, and if it does not, a new database will be created. If result equals constant SQLITE_OK, the database is opened successfully.

(2) Sqlite3 *db: An open DB instance

(3) The path to the database file must be passed in the C string (not nsstring),[path utf8string] is to convert the nsstring string to C a Char -type string for the language . equivalent to: path. Utf8string

To close the database:

Sqlite3_close (DB);

- , execution does not return data. SQL statement (creating a table in the database / Delete a table / Update table)

To execute a statement of Genesis:

Char *errormsg; Used to store error messages

Char *sql = "CREATE table if not exists T_person (ID integer primary key autoincrement, name text, age integer);";

int result = sqlite3_exec(db, SQL, NULL, NULL, &ERRORMSG);

Code parsing:

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. If result equals constant SQLITE_OK, the creation of the table succeeds.

sqlite3_exec () statements that can also be executed:

① open transaction: BEGIN TRANSACTION;

② ROLLBACK TRANSACTION: rollback;

③ COMMIT TRANSACTION: Commit;

+ , with placeholders to insert data (in case SQL injection)

Char *sql = "INSERT into T_person (name, age) VALUES (?,?);";

sqlite3_stmt *stmt;

if (sqlite3_prepare_v2(DB, SQL,-1, &stmt, NULL) = = Sqlite_ok) {

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 parsing:

The SQLITE3_PREPARE_V2 () 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 are only 3 a parameter:

(1) The 1th parameter is a sqlite3_stmt * type

(2) The 2nd parameter refers to the position of the placeholder, the position of the first placeholder is 1, not 0

(3) The 3rd parameter refers to the value to which the placeholder is bound

(4) The 4th parameter refers to the length of the data passed in the 3rd parameter, and for the C string, it can be passed-1 instead of the length of the string

(5) The 5th parameter is an optional function callback that is typically used to complete the memory cleanup work after the statement is executed

Sqlite_step (): Execute SQL statement, return Sqlite_done representative successful execution completed

Sqlite_finalize (): Destroy Sqlite3_stmt * Object

- , querying data (querying table data)

Char *sql = "Select Id,name,age from T_person;";

sqlite3_stmt *stmt;//Storing result sets

if (sqlite3_prepare_v2(DB, SQL,-1, &stmt, NULL) = = Sqlite_ok) {//return SQLITE_OK No error on behalf of SQL statement

while (sqlite3_step(stmt) = = Sqlite_row) {//return sqlite_row description query to Data

int _id = sqlite3_column_int(stmt, 0);//id

Char *_name = (char *)sqlite3_column_text(stmt, 1); Name

NSString *name = [NSString stringwithutf8string: _name];

int _age = sqlite3_column_int(stmt, 2); Age

NSLog (@ "id=%i, name=%@, age=%i", _id, Name, _age);

}

}

sqlite3_finalize (stmt);//Close result set

Code parsing:

(1) Sqlite3_step () returns the Sqlite_row representative to traverse to a new record

(2) sqlite3_column_* () to get the corresponding value for each field, the 2nd parameter is the index of the field, starting from 0

+ , FMDB

( 1 ) Overview

Fmdb is the SQLite database framework for the iOS platform.

Fmdb encapsulates the C language API for SQLite in OC mode.

FMDB The advantages of:

It is more object-oriented and eliminates a lot of cumbersome, redundant C-language code.

Compared to Apple's own core data frame, it is more lightweight and flexible.

Provides a multi-threaded secure database operation method, effectively prevent data confusion.

FMDB of the GitHub Address:

Https://github.com/ccgus/fmdb

Click to view the locally saved Fmdb framework

( 2 ) FMDB There are three main classes

Fmdatabase

A Fmdatabase object represents a single SQLite database.

Used to execute SQL statements.

Fmresultset

Use Fmdatabase to execute the result set after the query.

Fmdatabasequeue

Used to execute multiple queries or updates in multiple threads, which is thread-safe.

( 3 ) Open the database

by specifying SQLite database file path to create Fmdatabase Object

Fmdatabase *db = [Fmdatabase Databasewithpath:path];

if (![ DB Open]) {

NSLog (@ "Database open failed! ");

}

There are three types of file paths:

(a) specific file path.

It is created automatically if it does not exist.

(b) Empty string @ ""

An empty database is created in the temp directory.

When the Fmdatabase connection is closed, the database file is also deleted.

(c) Nil

An in-memory staging database is created and the database is destroyed when the Fmdatabase connection is closed.

( 4 ) to perform the update

In Fmdb, all operations except queries are called "Updates":

Create, DROP, insert, UPDATE, Delete, and so on.

Use the Executeupdate: method to perform the update:

-(BOOL) Executeupdate: (nsstring*) SQL, ...

-(BOOL) Executeupdatewithformat: (nsstring*) format, ...

-(BOOL) Executeupdate: (nsstring*) SQL Withargumentsinarray: (Nsarray *) arguments

For example:

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

For example, open the database and create a new table:

@property (nonatomic, strong) Fmdatabase *db;

Implementation code:

-(void) viewdidload

{

[Super Viewdidload];

0. Get the database file name in the sandbox

NSString *filename

= [[Nssearchpathfordirectoriesindomains (NSDocumentDirectory, Nsuserdomainmask, YES) Lastobject] stringByAppendingPathComponent:

@ "Student.sqlite"];

1. Creating a DB Instance Object

self.db = [Fmdatabase databasewithpath: filename];

2. Open the Database

if ([self.db Open]) {

NSLog (@ "Database open successfully");

Create a Watch

BOOL result = [self.db executeupdate: @ "CREATE table if not exists t_student (ID integer PRIMARY key autoincrement , name text, age integer); "];

if (result) {

NSLog (@ "Genesis success");

} else {

NSLog (@ "Failure to create a table");

}

} else {

NSLog (@ "Database open failed");

}

}

( 5 ) Execute the query

Query method:

-(Fmresultset *) ExecuteQuery: (nsstring*) SQL, ...

-(Fmresultset *) Executequerywithformat: (nsstring*) format, ...

-(Fmresultset *) ExecuteQuery: (NSString *) SQL Withargumentsinarray:

(Nsarray *) arguments

For example:

Querying data

Fmresultset *rs = [db executeQuery: @ "SELECT * from T_student"];

Traversing result Sets

while ([Rs next]) {

NSString *name = [rs stringforcolumn:@ "name"];

int age = [rs intforcolumn:@ ' age '];

Double score = [Rs doubleforcolumn:@ "score"];

}

"Remarks" close the database [db close];

( 6 ) Fmdatabasequeue

Fmdatabase This class is thread insecure, and if you use a Fmdatabase instance in multiple threads at the same time, it can cause data confusion and other problems.

To ensure thread safety, Fmdb provides a quick and easy Fmdatabasequeue class.

Fmdatabasequeue creation of:

Fmdatabasequeue *queue =

[Fmdatabasequeue Databasequeuewithpath:path];

[Queue indatabase:^ (Fmdatabase *db) {//Get an open DB instance directly from this block

[DB executeupdate:@ INSERT into t_student (name) VALUES (?) ", @" 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]) {

...

}

}];

( 7 ) Fmdatabasequeue Using Transactions

[Queue intransaction:^ (Fmdatabase *db, BOOL *rollback) {

[DB executeupdate:@ INSERT into t_student (name) VALUES (?) ", @" Jack "];

[DB executeupdate:@ INSERT into T_student VALUES (?) ", @" Rose "];

[DB executeupdate:@ INSERT into t_student (name) VALUES (?) ", @" Jim "];

if (the condition is not found)

{

*rollback = yes//ROLLBACK TRANSACTION

}

Fmresultset *rs = [db executequery:@ "select * from T_student"];

while ([Rs next]) {

...

}

}];

Of course, you can also use the following methods:

[Self.queue indatabase:^ (Fmdatabase *db) {

Open transaction

[DB executeupdate:@ "BEGIN Transaction;"];/ /equivalent to [DB BeginTransaction];

[DB executeupdate:@ "Update t_student set age =? where name =?;", @20, @ "Jack"];

[DB executeupdate:@ "Update t_student set age =? where name =?;", @20, @ "Jack"];

if (the condition is not found) {

Rolling back a transaction

[DB executeupdate:@ "ROLLBACK transaction;"];/ /equivalent to [db rollback];

}

[DB executeupdate:@ "Update t_student set age =? where name =?;", @20, @ "Jack"];

Commit a transaction

[DB executeupdate:@ "Commit transaction;"];/ /equivalent to [DB commit];

}];

SQLite3 of data storage for iOS development (including Fmdb)