Use of IOS SQLite3

first, what is SQLiteSQLite is a lightweight embedded database, it occupies a very low resource, in the embedded device, may only need hundreds of K of memory is sufficient. Its processing speed is faster than the two famous databases of MySQL and PostgreSQL .Ii. steps for storing data in a database1. Create a new database2, create a new table (table)3. Add multiple fields (column, columns, properties)4, add multiple rows of records (row, each row holds the values of multiple fieldsIII. Types of SQL statements1. Data definition statement (ddl:data definition Language)includes operations such as Create and dropCreate a new table or delete a table in the database (CREATE table or drop tables)
2. Data operation statement (Dml:data manipulation Language)include INSERT, UPDATE, delete, and morethe above 3 actions are used to add, modify, and delete data in a table, respectively
3. Data query Statement (dql:data query Language)can be used to query for data in a tablekeyword Select is the most used operation for DQL (and all SQL)other dql commonly used keywords are where,order by,group by and having
Third, the field type of SQLiteinteger: Integer valueReal: Floating-point valuetext: literal stringblob: Binary data (such as files)In fact, SQLite is untyped, but in order to maintain good programming specifications, facilitate the communication between programmers, it is best to write a table statement when the specific type of each field
iv. use of SQLite1. When using SQLite3 in iOS, you first need to add the library file Libsqlite3.dylib and import the main header file#import<sqlite3.h>
Create a database

Splicing database address NSString *path = [Nssearchpathfordirectoriesindomains (nsdocumentdirectory, Nsuserdomainmask, YES)    Lastobject];    NSString *sqlfile = [path stringbyappendingpathcomponent:@ "Student.sqlite"];    Open Data int result = Sqlite3_open (sqlfile.utf8string, &_db); Opening the database returns a return value of type int, which can be used to determine whether the creation of the database succeeded//Whether it was opened successfully        if (result = = Sqlite_ok) {NSLog (@ "open successfully");         CREATE TABLE/* The first parameter: the database object that needs to execute the SQL statement the second parameter: The SQL statement that needs to be executed the third parameter: callback function fourth parameter: parameter of the third parameter Fifth 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, NULL, NULL);        if (result = = Sqlite_ok) {NSLog (@ "CREATE table succeeded");        }else {NSLog (@ "Failed to create TABLE");    }}else {NSLog (@ "Open failed"); }

When the above code is executed, a database is created in the app's sandbox

the database can be opened with a software called Navicat Premium, as follows

table fields have been created, and now it 's time to have a good database operation.2. Inserting Data

NSString *sql = @ "INSERT into t_student (age, score, name) VALUES (' + ', ' + ', ' Jonathan ');";    int result =  sqlite3_exec (_db, SQL. Utf8string, NULL, NULL, NULL);    if (result = = SQLITE_OK) {        NSLog (@ "Insert succeeded");    }

3. Modify the Data

NSString *sql = @ "UPDATE t_student SET name = ' Lnj ';";    int result =  sqlite3_exec (_db, SQL. Utf8string, NULL, NULL, 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, NULL, NULL);    if (result = = SQLITE_OK) {        NSLog (@ "Delete succeeded");    }

5. Query DataSqlite3in Action,AllDMLstatements are all using thesqlite3_execfunction ExecutionSQLstatement can, However, if you need to query the database , you cannot use sqlite3_exec because it does not return a query to the results sent to US

NSString *sql = @ "SELECT * from T_student;";    sqlite3_stmt *stemt = NULL;     /* First parameter: The database that needs to execute the SQL statement the     second parameter: The SQL statement that needs to be executed the     third argument: tells the system the length of the SQL statement, if passed in a number less than 0, the system will automatically calculate the     fourth parameter: result set, It contains all the data that is queried (not rigorous)     *    /SQLITE3_PREPARE_V2 (_db, SQL. Utf8string,-1, &STEMT, NULL);    Determine if there is a query result while    (Sqlite3_step (stemt) = = Sqlite_row) {        //Check out the first field for the result        const unsigned char *name = sqlit E3_column_text (STEMT, 1);        The query for the first field is fetched with the result        int age = Sqlite3_column_int (STEMT, 2);        Take out the query for the first field results        double score = sqlite3_column_double (STEMT, 3);        NSLog (@ "%s%d%f", name, age, score);    }

Use of IOS SQLite3