Ios-sqlite3 database: Create TABLE database tables and additions and deletions to database tables (Create/insert/delete)

1. The steps are:

First join the SQLite development Library Libsqlite3.dylib,

Create a new or open database,

Create a data table,

Insert Data,

Querying data and printing

2, the method of SQLite

Sqlite3 *db, database handle, similar to file handle files

Sqlite3_stmt *stmt, an ODBC-equivalent command object that holds compiled SQL statements

Sqlite3_open (), open the database when it is created without a database.
Sqlite3_exec (), execute non-query SQL statement
Sqlite3_step (), use this function to move through the recordset after calling Sqlite3_prepare.
Sqlite3_close (), close the database file
There is also a series of functions that are used to fetch data from a recordset field, such as
Sqlite3_column_text (), take the text type of data.
Sqlite3_column_blob (), fetching blob-type data
Sqlite3_column_int (), take int type of data

3. Operation of database tables: Create and Update/insert/delete/select

Creating a database table (CREATE TABLE)

+ (BOOL) createtable {

Determine if the database is open

if ([sharedatabase Open]) {

If the table Inner_ac_info does not exist, it is created, contains the field column name _id (data type is integer, primary key, auto increment), ac_name (data type varchar), iu_no (data type varchar), ou_ No (data type varchar), GW_IP (data type varchar).

nsstring *sql = @ "CREATE TABLE IF not EXISTS inner_ac_info (_id Integer primary key autoincrement, Ac_name Varcha R, Iu_no varchar, ou_no varchar, gw_ip varchar) ";

[sharedatabase executeupdate:sql];

nsstring *sql1 = @ "CREATE TABLE IF not EXISTS ac_mode_info (_id Integer primary key autoincrement, Ac_mode_name V Archar, On_off Integer, Speed Integer, work_mode integer, temp integer) ";

[sharedatabase EXECUTEUPDATE:SQL1];

nsstring *sql2 = @ "CREATE TABLE IF not EXISTS ac_mode_inner_ac (_id Integer primary key AutoIncrement, ac_mode_id Long, ac_id long) ";

[sharedatabase executeupdate:sql2];        

[Sharedatabase Close];

}

SQLite's autoincrement is a keyword that is used to automatically increment the value of a field in a table.

The keyword autoincrement can only be used for integer fields.

/**

Querying database tables

+ (Nsarray *) getaclist{

Nsmutablearray *acarray = [[Nsmutablearray alloc] init];

[self init];

if ([sharedatabase Open]) {

Query database table The following sentence

fmresultset *s = [sharedatabase executeQuery: [nsstring stringwithformat: @ " SELECT * from Inner_ac_info "]];

< span class= "s2" >         //equivalent to  nsstring *string = [nsstring < Span class= "S3" >stringwithformat:@ "select * from Inner_ac_info" ]&NBSP;

+ fmresultset *s = [sharedatabase executeQuery: string];

While ([s next]) {

airconditioner *ac = [[airconditioner alloc] init];

AC.acId = [nsnumber numberwithint:[s intforcolumn:@ "_id"];

AC.rmname = [s stringforcolumn:@ "Ac_name"];

AC.Aciuno = [s stringforcolumn:@ "Iu_no"];

AC.Acouno = [s stringforcolumn:@ "Ou_no"];

AC.GWIP = [s stringforcolumn:@ "Gw_ip"];

[Acarray Addobject:ac];

}

Close the database

[sharedatabase Close];

}

return acarray;

}

Updating database table Modifications (update)

+ (void) Updateac: (airconditioner*) ac{

[self init];

NSLog(@ "Acid =========%@", AC.acid);

if ([sharedatabase Open]) {

Database tables Perform update operations: Modify the nkname of the ID corresponding

[Sharedatabase executeupdate:

@ "UPDATE inner_ac_info set ac_name =?" where _id =? " , AC.nkname, AC.acId];

[Sharedatabase close];

}

}

Delete a database table

Delete part

+ (void) Deleteac: (nsstring*) Iuno with: (nsstring*) ouno{

[self init];

if ([sharedatabase Open]) {

[Sharedatabase executeupdate:

@ "Delete from inner_ac_info where iu_no =?" and Ou_no =? " , Iuno, Ouno];

[Sharedatabase close];

}

}

Delete all

+ (void) deleteallac{

[self init];

if ([sharedatabase Open]) {

[Sharedatabase executeupdate:

@ "Delete from Inner_ac_info"];

[Sharedatabase close];

}

}

Ios-sqlite3 database: Create TABLE database tables and additions and deletions to database tables (Create/insert/delete)