iOS database common SQL statements

SQLite Common Statements

Because SQL statements in the program code in the form of a string, there is no code hint, careless is easy to error, and it is not easy to detect. The SQL statement string is single quotation marks. You must be careful when writing SQL statements. If you can't write well, you can find the company backstage to do the database of colleagues to help you.

Note: SQLite statements are case insensitive

1. Create a Table statement
CREATE TABLE Student (Student is the table name)
If not EXISTS table does not exist before creating
Gender text denotes its type string

Storage type:
Null value is a null value
Integer value is integral type
REAL value is a floating-point number
Text value is a literal string
BLOB value is a binary type

Number integer primary key not NULL primary key value if self-increment is not manipulated

CREATE TABLE IF not EXISTS lanoustudent (number integer primary key is not NULL, name text is not NULL, gender text is not NULL, age Integer not NULL)


2. Insert statement
INSERT into Lanoustudent table name
Note: Single quotation marks correspond to order

Insert into lanoustudent (name, gender, age, number) values ('%@ ', '%@ ', '%ld ', '%ld ')


3. Delete a statement
Delete from lanoustudent table name
Where is deleted by condition

Delete from lanoustudent where > '%ld '

4. UPDATE statements
Update lanoustudent table name
Where updates are based on conditions
Set age-Updated fields

Update lanoustudent Set age = '%ld ' WHERE name = '%@ '

5. Query statements
Where to query multiple conditions by criteria with an and connection
* Indicates all fields are queried

SELECT * from lanoustudent where name = '%@ ' and "age = '%ld '

SELECT * FROM lanoustudent query all


Important function Parameters:

Sqlite3_exec (Sqlite3 *, const char *sql, int (*callback) (void *, int, char * *, char * *), void *, char **errmsg)

The 1th parameter is a pointer to the previous open function.
The 2nd parameter is an SQL statement.
The 3rd parameter is a callback, and when the statement executes, SQLITE3 will call the function you provided.
The 4th parameter is the pointer you provide, you can pass any pointer parameter here, this parameter will eventually be passed to the callback function, if you do not need to pass the pointer to the callback function, you can fill null. Let's see how the callback function is written, and how this parameter is used.
The 5th parameter is an error message.


SQLITE3_PREPARE_V2 (sqlite3 *db, const char *zsql, int nbyte, sqlite3_stmt **ppstmt, const char **pztail)

int nByte-1 means that the length of the SQL statement can be infinitely long
SQLITE3_STMT Follow the pointer address
const CHAR **pztail Intercept SQL statement not used partially

Binding query values
The second parameter refers to the first question mark of the query starting with 1
Sqlite3_bind_text (stmt, 1, name. Utf8string,-1, NULL);

Reading data
The second parameter refers to the number of columns in the table starting from 0
Char *name = (char *) Sqlite3_column_text (stmt, 1);

iOS database common SQL statements