IOS SQLite syntax basics and iossqlite syntax

IOS SQLite syntax basics and iossqlite syntax

Sharing the basic knowledge of SQLite statements is a basic part. It only involves four syntaxes: "add", "delete", "modify", and "query. table links and other content are not involved. I will update the table link later.

Github has an SQL Demo, including add, delete, modify, query. UI: url: ---> https://github.com/huyp/SQLite3_Demo.git

1 # import <sqlite3.h> 2 3 @ interface ViewController () 4 5 @ property (assign, nonatomic) sqlite3 * database; 6 7 @ end 8 9 @ implementation ViewController10 11 @ synthesize database;

 

Create a student table with three attributes: ID, name, and age. ID is the primary key.

Create table: create table if not exists t_student (id integer primary key autoincrement, name text, age integer )"

Create table: create a table. if not exists: if it has not been created. t_student: Table name. id integer: ID integer type (integer ). primary key: primary key. autoincreament: auto + 1. name text: name (string) age integer (integer ).

1 // convert the text box to the C language 2 const char * name = [_ text1.text UTF8String]; // name 3 const char * age = [_ text2.text UTF8String]; // age 4 // Add a row of Statement 5 char * SQL = "insert into t_student (name, age) values (?,?) "; 6/** 7 sqlite binary data requires an auxiliary data type: sqlite3_stmt *. 8. This data type records an "SQL statement ". Why do I use double quotation marks for "SQL statements? Because you can regard the content represented by sqlite3_stmt * as an SQL statement, but in fact it is not a well-known SQL statement. It is an internal data structure that has resolved SQL statements and marked records by sqlite itself. 9 */10 sqlite3_stmt * stmt; 11 12 // The sqlite statement is executed here (Database, SQL statement,-1, & stmt, NULL ); this code is used for addition, deletion, modification, and query. 13 // The difference is that the SQL statement is different, and the values in sqlite3_bind_text () are different. 14 int result = sqlite3_prepare_v2 (database, SQL,-1, & stmt, NULL); 15 if (result = SQLITE_ OK) {16 sqlite3_bind_text (stmt, 1, name,-1, NULL); 17 sqlite3_bind_text (stmt, 2, age,-1, NULL); 18} 19 else {20 NSLog (@ "failed to prepare "); 21} 22 // check whether the operation is completed 23 if (sqlite3_step (stmt) = SQLITE_DONE) {24 NSLog (@ "Operation completed "); 25} 26 else {27 NSLog (@ "operation failed"); 28} 29 // each time you call the sqlite3_prepare function sqlite, The sqlite3_stmt space will be re-opened, 30 // Therefore, before calling sqlite3_stmt again using the same sqlite3_prepare pointer 31 // you need to call sqlite3_finalize to first release space 32 sqlite3_finalize (stmt );

 

Insert an object to the database: insert into t_student (name, age) values (?,?)

Insert into t_student: insert data to the t_student table. (name, age): Data name. values (?,?) : Value (value 1, value 2 ).

1 // convert the text box to C language 2 const char * name = "zhangsan"; // name 3 const char * age = "20 "; // age 4 // Add a row of Statement 5 char * SQL = "insert into t_student (name, age) values (?,?) "; 6 sqlite3_stmt * stmt; 7 int result = sqlite3_prepare_v2 (database, SQL,-1, & stmt, NULL); 8 if (result = SQLITE_ OK) {9 sqlite3_bind_text (stmt, 1, name,-1, NULL); 10 sqlite3_bind_text (stmt, 2, age,-1, NULL); 11} 12 else {13 NSLog (@ "failed to prepare "); 14} 15 // check whether the operation is completed 16 if (sqlite3_step (stmt) = SQLITE_DONE) {17 NSLog (@ "Operation completed "); 18} 19 else {20 NSLog (@ "operation failed"); 21} 22 // each time you call the sqlite3_prepare function sqlite, The sqlite3_stmt space will be re-opened, 23 // Therefore, before calling sqlite3_stmt again using the same sqlite3_prepare pointer 24 // you need to call sqlite3_finalize to first release space 25 sqlite3_finalize (stmt );

 

Delete an object in the database: delete from t_student where id =?

Delete from t_sudent: delete the delete id =? In the t_student table? .

1 int a = [_ text1.text intValue]; 2 sqlite3_stmt * stmt; 3 char * SQL = "delete from t_student where id =? "; 4 int result = sqlite3_prepare_v2 (database, SQL,-1, & stmt, NULL); 5 if (result = SQLITE_ OK) {6 result = sqlite3_bind_int (stmt, 1, a); 7 if (result = SQLITE_ OK) {8 result = sqlite3_step (stmt); 9 if (result = SQLITE_DONE) {10 NSLog (@ "deleted successfully "); 11} 12 else {13 NSLog (@ "failed to delete"); 14} 15} 16} 17 else {18 NSLog (@ "failed to delete "); 19} 20 21 sqlite3_finalize (stmt );

　　

Change Data: update t_student set name =? Where id =?

Update t_student: update table. set name =: set the number of data whose name is. id =.

Is to modify id =? .

1 const char * ID = [_ text1.text UTF8String]; 2 const char * newname = [_ text2.text UTF8String]; 3 sqlite3_stmt * stmt; 4 char * SQL = "update t_student set name =? Where id =? "; 5 int result = sqlite3_prepare_v2 (database, SQL,-1, & stmt, NULL); 6 if (result = SQLITE_ OK) {7 result = sqlite3_bind_text (stmt, 1, newname,-1, NULL); 8 result = sqlite3_bind_text (stmt, 2, ID,-1, NULL); 9 if (result! = SQLITE_ OK) {10 NSLog (@ "data update error"); 11} 12 if (sqlite3_step (stmt )! = SQLITE_DONE) {13 NSLog (@ "failed to update data"); 14} 15} 16 else {17 NSLog (@ "failed to modify data "); 18} 19 sqlite3_finalize (stmt );

 

Query all data in the database: select id, name, age from t_student

Select: Query. id, name, age: ID, name, and age attributes. from t_student: from the t_student table

1 char * SQL = "select id, name, age from t_student"; 2 sqlite3_stmt * stmt; 3 int result = sqlite3_prepare_v2 (database, SQL,-1, & stmt, NULL ); 4 if (result = SQLITE_ OK) {5 // sqlite3_step (stmt) = SQLITE_ROW: 6 while (sqlite3_step (stmt) = SQLITE_ROW) {7 int ID = sqlite3_column_int (stmt, 0); 8 char * name = (char *) sqlite3_column_text (stmt, 1); 9 NSString * strName = [NSString stringwithuf8string: name]; 10 int age = sqlite3_column_int (stmt, 2); 11 NSLog (@ "% d, % @, % d", ID, strName, age ); 12} 13} 14 sqlite3_finalize (stmt );