Use of IOS SQLite3

Use of IOS SQLite3
I. What is SQLIte? SQLite is a lightweight embedded database, which occupies very low resources. In embedded devices, it may only need several hundred KB of memory. The processing speed is faster than that of Mysql and PostgreSQL. 2. Step 1. Create a database. 2. Create a table) 3. Add multiple fields (column, column, attribute) 4. Add multi-row records (row, values of multiple fields in each row. III. SQL statement type. 1. Data Definition Language) create or delete a table (create table or drop table) in the database, including create and drop operations)
2. Data operation statements (DML: Data Manipulation Language) include insert, update, delete, and other operations. The preceding three operations are used to add, modify, and delete table Data respectively.
3. The Data Query statement (DQL: Data Query Language) can be used to Query and obtain the Data keyword "select" in the table is DQL (also all SQL statements) the most commonly used DQL operations include where, order by, group by, and having.
Iii. SQLite field type integer: integer value real: floating point value text: text string blob: binary data (such as files) SQLite is actually non-typed, however, to maintain good programming specifications and facilitate communication between programmers, it is best to add the specific type of each field when writing table creation statements.
Iv. Usage of SQLite 1. When using SQLite3 in IOS, you must first add the library file libsqlite3.dylib and import the main header file # import
Create a database

// Concatenate the database address NSString * path = [NSSearchPathForDirectoriesInDomains (NSDocumentDirectory, NSUserDomainMask, YES) lastObject]; NSString * sqlFile = [path stringByAppendingPathComponent: @ student. sqlite]; // open the data int result = sqlite3_open (sqlFile. UTF8String, & _ db); When a database is opened, a return value of the int type is returned. You can use this value to determine whether the database is created successfully. // you can determine whether the database is opened successfully if (result = SQLITE_ OK) {NSLog (@ open successfully); // create a table/* First parameter: database object for which SQL statements are to be executed; second parameter: third parameter of the SQL statement to be executed: the fourth parameter of the callback function: the fifth parameter of the third parameter: receive error message * // create table SQL statement NSString * SQL = @ CREATE TABLE IF NOT EXISTS t_student (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, age INTEGER, score REAL); result = sqlite3_exec (_ db, SQL. UTF8String, NULL); if (result = SQLITE_ OK) {NSLog (@ table created successfully);} else {NSLog (@ table created failed );}} else {NSLog (@ failed to open );}

After the preceding code is executed, a database is created in the sandbox of the app.

The database can be opened with a software called NaviCat Premium, as shown below:

Table fields have been created, and now you can perform database operations happily. 2. Insert data.

NSString * SQL = @ insert into t_student (age, score, name) VALUES ('28', 100, 'jonathan '); int result = sqlite3_exec (_ db, SQL. UTF8String, NULL); if (result = SQLITE_ OK) {NSLog (@ inserted successfully );}

3. modify data

NSString * SQL = @ UPDATE t_student SET name = 'lnj'; int result = sqlite3_exec (_ db, SQL. UTF8String, NULL); if (result = SQLITE_ OK) {NSLog (@ modified successfully );}

4. delete data

NSString * SQL = @ delete from t_student WHERE id = 1; int result = sqlite3_exec (_ db, SQL. UTF8String, NULL); if (result = SQLITE_ OK) {NSLog (@ deleted successfully );}

5. In sqlite3, all DML statements use the sqlite3_exec function to execute SQL statements. However, if you need to query the database, you cannot use sqlite3_exec, because it does not return the query results to us

NSString * SQL = @ SELECT * FROM t_student; sqlite3_stmt * stmep = NULL;/* First parameter: The second parameter of the database for which the SQL statement needs to be executed: the third parameter of the SQL statement to be executed: tells the system the length of the SQL statement. If a number smaller than 0 is input, the system automatically calculates the fourth parameter: result set, it stores all the queried data (not rigorous) */sqlite3_prepare_v2 (_ db, SQL. UTF8String,-1, & stemt, NULL); // you can check whether the query result is correct. while (sqlite3_step (stmep) = SQLITE_ROW) {// obtain the results of the first field query. const unsigned char * name = sqlite3_column_text (stemt, 1 ); // The result of retrieving the first field: int age = sqlite3_column_int (stemt, 2); // The result of retrieving the first field: double score = sqlite3_column_double (stemt, 3 ); NSLog (@ % s % d % f, name, age, score );}