SQLite Database Related Syntax knowledge

1, SQLite3 is an open-source embedded relational database, portability is good, easy to use, memory overhead is relatively smallSQLite3 is untyped, meaning that you can save any type of data to any table of anyFields 2, SQLite3 commonly used 5 types of data: text (text), Integer (integer), real (floating point value), blob (binary) 3, use SQLite3 in iOS, First, you add the library file Libsqlite3.dylib and import the master header file 4, create or open the database

//path: ~/documents/person.db

Sqlite3 *db;

int result = Sqlite3_open ([path utf8string], &db);

Code parsing: Sqlite3_open () will open the database based on the file path, and if it does not, a new database will be created. If result equals constant SQLITE_OK, the database is opened successfully. The path to the database file must be passed in the C string (not NSString)

To close the database:sqlite3_close (db);5. Execute Statement of Creation

Char *errormsg; Used to store error messages

Char *sql = "CREATE table if not exists T_person (ID integer primary key autoincrement, name text, age integer);";

int result = SQLITE3_EXEC (db, SQL, NULL, NULL, &ERRORMSG);

Code parsing: sqlite3_exec () can execute any SQL statement, such as CREATE TABLE, update, insert, and delete operations. However, it is generally not necessary to execute a query statement because it does not return the queried data

Sqlite3_exec () can also execute the statement: ① open transaction: BEGIN TRANSACTION;              ② ROLLBACK TRANSACTION: rollback; ③ COMMIT TRANSACTION: Commit;        6, the basic Operation ① CREATE TABLE: (1) Table if not EXISTS tables name (field name 1 field type 1, Field Name 2 field type 2, ...); (2) Create TABLE table name (Field name 1 field type 1, Field Name 2 field type 2, ...);Special Note: (1) The ability to store string literals (except primary keys), even if declared as an integer type(2) It is possible to declare what type or not to declare a type when building a table, which means that the statement can be written like this: Create TABLE t_student (name, age); (to maintain good programming practices and facilitate communication between programmers , it is best to add the specific type of each field when writing a table statement)② Delete Table:(1) drop table name;(2) The name of the drop table if exists;③ Inserting data:insert into table name (Field 1, Field 2, ...) VALUES (Value of field 1, Value of field 2, ...);Note: The string contents in the database should be enclosed in single quotes④ Update Data:Update table name set field 1 = value of field 1, field 2 = value of field 2, ...;⑤ Delete data: Delete from table name; ⑥Common formats for conditional statements:where field = a value; cannot be used for two x =The where field is a value; is equals =where field! = a value;The where field is not a value; is isn't equivalent to! =where field > a value;where field 1 = a value and field 2 > a value; and equivalent to the && in C languagewhere field 1 = a value or field 2 = a value; or equivalent to the C language | |⑦ Query statement:Select field 1, Field 2, ... from table name;select * from table name; Query all fields⑧ Aliases:Format (fields and tables can be aliases)Select field 1 alias, Field 2 alias, ... from table name alias;Select field 1 alias, Field 2 as Alias, ... from table name as alias;Select Alias. Field 1, alias. Field 2, ... from table name alias;

⑨ Calculate the number of records: format

Select count (field) from table name;Select COUNT (*) from table name;⑩ Sort by:The results of the query can be sorted with order bySELECT * from T_student the order by field;SELECT * from T_student order by age;The default is to sort in ascending order (from small to large), or to descending (from large to small)SELECT * from T_student order BY age Desc; DescendingSELECT * from T_student order by age ASC; Ascending (default)You can also sort by multiple fieldsSELECT * from T_student ORDER BY age ASC, height desc;Sort by age first (ascending), equal to 7, limit: Use limit to precisely control the number of query results, such as querying only 10 data formats at a time: SELECT * from table name limit value 1, value 2; Example: SELECT * from T_student limit 4, 8; (Can be understood as: Skip the first 4 statements, and then take 8 records) 8, the constraint of the table: you can set some constraints on a particular field when you build a table, and a common constraint has not NULL: the specified field The value cannot be null unique: The value of the specified field must be unique to default: Specifies the value of the field (recommended: Try to set strict constraints on the field to ensure the normative data)