Foreword:

In the previous article, we mainly introduced the use of DDL statements, which may have been discovered by careful students.This article will focus on DML statements to explain table data related operations.

1. Insert data

The main purpose of inserting data is insert syntax, and the official documentation also gives many options:

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [(col_name [, col_name] ...)]
    {VALUES | VALUE} (value_list) [, (value_list)] ...
    [ON DUPLICATE KEY UPDATE assignment_list]

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    SET assignment_list
    [ON DUPLICATE KEY UPDATE assignment_list]

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [(col_name [, col_name] ...)]
    SELECT ...
    [ON DUPLICATE KEY UPDATE assignment_list]

value:
    {expr | DEFAULT}

value_list:
    value [, value] ...

assignment:
    col_name = value

assignment_list:
    assignment [, assignment] ...

Interested students can consult and study the above options, oh, below I will classify a few common grammars.

INSERT INTO ... VALUES (...) 

This is probably one of the most common insert statements you write, and the standard usage is:

INSERT INTO <Table Name> [ <Column Name 1> [ , ... <Column Name n>] ]
VALUES (Value 1) [... , (value n) ];

#Insert multiple lines
INSERT INTO table(column1,column2...)
VALUES (value1,value2,...),
       (value1,value2,...),
...;

The syntax is as follows:

• < table name>: Specify the name of the table being operated on.
• < Column name >: Specify the column name that you want to insert data into.If you insert data into all columns in a table, all column names can be omitted and INSERT<table name>VALUES(...) Yes.
• VALUES or VALUE clause: The clause contains the list of data to insert.The order of the data in the data list corresponds to the order of the columns.

INSERT ... SET ... 

The insert... SET statement can only insert one piece of data at a time, and it is more flexible to insert the values of some columns into a table.

INSERT INTO <Table Name>
SET <Column Name 1> = <Value 1>,
    <Column Name 2> = <Value 2>,
    ...
#where INTO can be omitted

INSERT INTO ... SELECT ... 

INSERT INTO...SELECT...The FROM statement is used to quickly pull data from one or more tables and insert it into another table as row data.

The SELECT clause returns a result set that is queried. The INSERT statement inserts the result set into the specified table. The number of fields in each row of data in the result set and the data type of the fields must be exactly the same as the table being manipulated.

For example, if the test table and the test_bak table are structurally identical, and we want to insert the data from the test table into the test_bak table, we can do this:

INSERT INTO test_bak select * from test;

INSERT ... ON DUPLICATE KEY UPDATE 

If a new row to be inserted violates a primary key (PRIMARY KEY) or UNIQUE constraint, MySQL will error, which is the syntax used to resolve the error.Executing this statement updates a record when it exists in the database and inserts it when it does not.

The following example demonstrates the effect:

#Assume the student table structure and the original data are as follows:
CREATE TABLE `student` (
  `xuehao` int(11) primary key,
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

mysql> select * from student;
+--------+------+------+
| xuehao | name | age  |
+--------+------+------+
|   1001 | aaa  |   18 |
|   1002 | bbb  |   19 |
|   1003 | ccc  |   20 |
+--------+------+------+

#For example, if we want to insert this data, MySQL will execute the following UPDATE statement when it finds that the primary key is duplicated
insert into student (xuehao,name,age) 
 values (1003,'ccc',19) on DUPLICATE KEY UPDATE age = 19;

#When executed, the data became this way
mysql> select * from student;
+--------+------+------+
| xuehao | name | age  |
+--------+------+------+
|   1001 | aaa  |   18 |
|   1002 | bbb  |   19 |
|   1003 | ccc  |   19 |
+--------+------+------+

#That is, the previous statement is equivalent to executing update student set age = 19 where xuehao = 1003;

REPLACE INTO ... VALUES ... 

Replinto is similar to insert in that it first tries to insert data into the table. 1. If it finds that the row already exists in the table (based on the primary key or unique index), delete the row first, and then insert new data.2. Otherwise, insert new data directly.

The same example illustrates the following:

#Or the student table above, xuehao is the primary key and the original data is
mysql> select * from student;
+--------+------+------+
| xuehao | name | age  |
+--------+------+------+
|   1001 | aaa  |   18 |
|   1002 | bbb  |   19 |
|   1003 | ccc  |   19 |
+--------+------+------+

#If executed
replace into student values (1003,'ccc',17);

#Then the new table data is
mysql> select * from student;
+--------+------+------+
| xuehao | name | age  |
+--------+------+------+
|   1001 | aaa  |   18 |
|   1002 | bbb  |   19 |
|   1003 | ccc  |   17 |
+--------+------+------+

#The effect is equivalent to deleting rows with xuehao of 103 and inserting new rows

2. Update Data

The UPDATE statement is used to update table data, and the official recommended syntax is:

UPDATE [LOW_PRIORITY] [IGNORE] table_reference
    SET assignment_list
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

value:
    {expr | DEFAULT}

assignment:
    col_name = value

assignment_list:
    assignment [, assignment] ...

Similarly, here is only a description of the commonly used form update syntax:

UPDATE <Table Name> SET Field 1=Value 1[, Field 2=Value 2...] [WHERE clause]
[ORDER BY clause] [LIMIT clause]

The syntax is as follows:

• < table name>: Used to specify the name of the table to be updated.
• SET clause: Used to specify the column name and its value to be modified in a table.Each specified column value can be either an expression or a default value for that column.If the default value is specified, the column value can be represented by the keyword DEFAULT.
• WHERE clause: Optional.Used to qualify rows in a table to be modified.If not specified, all rows in the table are modified.
• ORDER BY clause: Optional.Used to limit the order in which rows in a table are modified.
• LIMIT clause: Optional.Used to limit the number of rows modified.

3. Delete data

The delete statement is used to delete table data. The official document recommends syntax:

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

Delete data from a single table using a DELETE statement in the following syntax:

DELETE FROM <Table Name> [WHERE clause] [ORDER BY clause] [LIMIT clause]

The syntax is as follows:

• < table name>: Specify the name of the table to delete the data.
• ORDER BY clause: Optional.Indicates that when deleted, the rows in the table are deleted in the order specified in the clause.
• WHERE clause: Optional.Represents a deletion operation that qualifies the deletion condition, or deletes all rows in the table if the clause is omitted.
• LIMIT clause: Optional.The maximum value used to tell the server to delete rows before the control command is returned to the client.

Summary:

This paper mainly introduces the grammar of three DML statements, which seems simple. In fact, the various options are still very complex, especially insert statements. There are many options that are often used.Here's also a reminder that you must be careful when executing update or delete statements. Not using where conditions will update or delete all data.