DML Data Management Language summary

Insert

One, mode one
Grammar:
Insert into table name (field name,...) VALUES (value,...);
Characteristics:
1, the type of the required value and the type of the field to be consistent or compatible
2. The number and order of fields are not necessarily the same as the number and order of fields in the original table
However, you must guarantee that the value and field one by one correspond
3. If there are nullable fields in the table, note that null values can be inserted in the following two ways
① fields and values are omitted
The ② field is written with a value of NULL
4. The number of fields and values must be the same
5, the field name can be omitted, the default all columns

Two, mode two
Grammar:
Insert into table name set field = value, field = value,...;

The difference between the two ways:
1. Mode one supports inserting multiple lines at once, the syntax is as follows:
Insert into table name (field name,..) "Values (value,.), (value, ...),...;
2. Mode one supports subqueries, the syntax is as follows:
Insert INTO table name
Query statement;

Modify

First, modify the record of the single table ★
Syntax: Update table name Set field = value, field = value "where filter condition";

Second, modify the records of multiple Tables "supplement"
Grammar:
Update Table 1 aliases
Left|right|inner Join table 2 aliases
On connection condition
Set field = value, field = value
"Where filter condition";

Delete

Method One: Use delete
First, delete the record of the single table ★
Syntax: Delete from table name "where filter Condition" "Limit entry number"
Ii. cascading deletions [supplemental]
Grammar:
Delete Alias 1, alias 2 from table 1 alias
Inner|left|right Join table 2 aliases
On connection condition
"Where filter Condition"

Mode two: Use truncate
Syntax: TRUNCATE TABLE name

The difference between the two ways "Face question" ★

1.truncate after deletion, if re-inserted, the identity column starts from 1
After delete is removed, if you insert again, the identity column starts at the breakpoint
2.delete You can add filter criteria
Truncate can not add filter criteria
3.truncate High Efficiency
4.truncate no return value
Delete can return the number of rows affected
5.truncate cannot be rolled back
Delete can be rolled back

DML Data Management Language summary