I. How the data is stored in the database!
{
1. Create a table. (according to the stored data characteristics, create the corresponding table!)
2. Storing data in the table
}
Two. Database usage
{
1. Database (db) file: The path to the DB files! All the data in the database is saved in this file!
Database (SQL) statements are case-insensitive! If it's the system's keyword capitalization! Use your own lowercase!
2. "Table name" Specification: Start with T_/t_, followed by the table name!
3. Insert Data!
}
DDL statements: "Build table" and "Delete table"
{
"Build table": CREATE table IF not EXISTS t_class (ID integer, numbers text);
If the table does not exist, it will be created! Table exists, no error!
"Delete": "DROP table IF EXISTS t_class;"
If the table exists, it will be deleted! The table does not exist, will not error!
Attention:
Primary key: The primary key is used to mark only one piece of data in a database table! In general, when defining the primary key, the primary key value is automatically generated by the system!
The type of the primary key Integter type, automatically grows!
Final statement of "Build table":
"CREATE TABLE IF not EXISTS t_student (ID integer PRIMARY KEY autoincrement, name text, score integer, age integer);"
PRIMARY key: Specifies the primary key. The default primary KEY constraint cannot be empty.
AutoIncrement: Automatic growth.
}
Database operations: CRUD operations! "Additions and deletions to check"! All----operations are "tables"
DML statement: "Add" "Delete" "Change"
{
"Add": Insert data into the table:
"INSERT into T_student (name,score,age) VALUES (' Jsak ', 80,21);"
Print: Affects one row of data!
"Delete": Deletes data from the table:
DELETE from T_student;
By default, all data in the table is deleted and the table is not deleted;
Conditional Delete:
"DELETE from T_student WHERE score > 90;"
DELETE from t_student WHERE name = ' Jsak ';
"Change": Update the data!
"UPDATE t_student SET age =-WHERE Age < 18;"
Change age to 18 for children younger than 18 years;
}
DQL statement: Query statement: The result of the query is returned as a table/query to a result set.
{
SELECT * from T_student;
Remove all data from the table T_student!
SELECT Name,score from T_student;
Remove all data from the table T_student fields name and score!
SELECT Name,score from T_student LIMIT 5, 5;
Limit 5, 5: Remove the data constraints, over the first 5 data, take the back of the 5!
Limit: Limits the amount of data that is removed from a single time!
SELECT Name,score from t_student WHERE score > Bayi LIMIT 2;
Query statement with conditional statement!
"Select Name,score from t_student WHERE score > Bayi ORDER by score DESC LIMIT 10;"
Take the score > 81 data out and sort in descending order! One-time query to take out 10 data!
}
Common statements for databases in IOS development
