iOS Development Note--SQLITE3 Statement summary

1. Sqlite3 the type of data stored
Null: Identifies a null value
Interger: Integer type
REAL: Floating point
TEXT: String
BLOB: Binary number

2, sqlite3 storage data constraint conditions
The usual constraints for SQLite are as follows:
PRIMARY Key-PRIMARY key:
1) The value of the primary key must be unique to identify each record, such as student's number
2) Primary key is also an index, the primary key to find records faster
3) If the primary key is an integer type, the value of the column can grow automatically
Not null-non-null:
Constraint column record cannot be empty, otherwise an error
Unique-Single:
The value of the data that constrains the other columns is unique except for the primary key
Check-condition checking:
The value that constrains the column must meet the criteria before it can be deposited
Default-Defaults:
The values in the column data are basically the same, so the field column can be set to the default value

3, Sqlite3 Common instructions
1) Set up a data sheet
CREATE TABLE table_name (field1 type1, Field2 type1, ...);
TABLE_NAME is to create a data table name, FIELDX is the field name in the datasheet, and Typex is the field type.
example, create a simple student information table that contains student information such as number and name:
CREATE TABLE Student_info (stu_no Interger primary key, name text);
The CREATE table if not EXISTS table name (field name 1, field name 2 ...);

2) adding data records
INSERT INTO table_name (field1, field2, ...) VALUES (Val1, val2, ...);
Valx is the value that needs to be stored in the field.
example, add data to the Student information table:
Insert into Student_info (stu_no, name) VALUES (0001, Alex);

3) Modify the data record
UPDATE table_name set FIELD1=VAL1, field2=val2 where expression;
Where is the command used in the SQL statement for conditional judgment, and expression is the judge-
example, modify the data record for student information table number 0001:
Update Student_info set stu_no=0001, name=hence where stu_no=0001;

4) Delete data records
Delete from table_name [where expression];
Clears all data records of the table without judging the condition.
example, delete the data record of Student information table number 0001:
Delete from Student_info where stu_no=0001;

5) query data record
Select instruction basic format:
Select columns from table_name  [where expression];
A query outputs all data records
Select * from table_name;
b limit the number of output data records
Select * from table_name limit val;
C Ascending Output data record
Select * from table_name order by field asc;
D Descending output data record
Select * from table_name order by field desc;
E conditional query
Select * from table_name where expression;
select * from table_name where field in  (' val1 ',  ' val2 ',  ' val3 ');
select * from table_name where field between val1 and val2;
F Query Record number
select count  (*)  from table_name;
G distinguish column data
Select distinct field from table_name;
Some of the values of the fields may recur, distinct remove duplicates, and each field value in the column is listed individually.

6) Building the index
When you say that a data table has a large number of records, indexing helps speed up finding data tables.
CREATE INDEX index_name on table_name (field);
example, for the Student Table Stu_no field, create an index:
Create INDEX Student_index on student_table (STU_NO);
When Setup is complete, Sqlite3 automatically uses the index when it queries the field.

7) Delete data table or index
DROP TABLE table_name;
Drop index index_name;

iOS Development Note--SQLITE3 Statement summary