Detailed explanation of the use of MySQL DML statements

Preface:

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

Here we explain the classification of DDL and DML statements. Some students may not be clear about it.

DDL (Data Definition Language): Data definition language, used to create, delete, modify, database or table structure, and operate on the structure of database or table. Common ones include create, alter, drop, etc.

DML (Data Manipulation Language): Data manipulation language, mainly used to update (add, delete, modify) table records. Common ones include insert, update, delete, etc.

1. Insert data

The insert syntax is mainly used to insert data. The official documentation also provides 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] ...

Students who are interested can consult and study the above options. Below I will introduce several commonly used syntaxes to you in categories.

INSERT INTO ... VALUES (...)

This is probably the most common way you write insert statements. The standard usage is:

INSERT INTO <table name> [ <column name 1> [ , … <column name n>] ]VALUES (value 1) [… , (value n) ];
INSERT INTO table(column1,column2...)VALUES (value1,value2,...), (value1,value2,...),...;

The syntax is as follows:

• <table name>: specifies the name of the table to be operated.
• <column name>: Specify the column name where data needs to be inserted. If you want to insert data into all columns of a table, all column names can be omitted and you can directly use INSERT <table name> VALUES(…).
  
• VALUES or VALUE clause: This clause contains the list of data to be inserted. The order of data in the data list should correspond to the order of columns.
  

INSERT ... SET ...

The insert ... set statement can only insert one piece of data at a time. It can insert values ​​of some columns into the table, which is more flexible.

INSERT INTO <table name> SET <column name 1> = <value 1>, <column name 2> = <value 2>, …

INSERT INTO ... SELECT ...

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

The SELECT clause returns a query result set, and the INSERT statement inserts this result set into the specified table. The number of fields and the data type of each row of data in the result set must be exactly the same as the table being operated on.

For example, if the test table and test_bak table have the same structure, and we want to insert the data in 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 the new row to be inserted violates the primary key (PRIMARY KEY) or UNIQUE constraint, MySQL will report an error. This syntax is to resolve this error. When a record exists in the database, executing this statement will update it, and when it does not exist, it will insert it.

Here is an example to demonstrate the effect:

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 |+--------+------+------+
insert into student (xuehao,name,age) values ​​(1003,'ccc',19) on DUPLICATE KEY UPDATE age = 19;
mysql> select * from student;+--------+------+------+| xuehao | name | age |+--------+------+------+| 1001 | aaa | 18 || 1002 | bbb | 19 || 1003 | ccc | 19 |+--------+------+------+
update student set age = 19 where xuehao = 1003;

REPLACE INTO ... VALUES ...

replace into is similar to insert, except that replace into first tries to insert data into the table. 1. If it is found that the row of data already exists in the table (based on the primary key or unique index), it deletes the row of data and then inserts the new data. 2. Otherwise, insert the new data directly.

Let's take an example to illustrate:

mysql> select * from student;+--------+------+------+| xuehao | name | age |+--------+------+------+| 1001 | aaa | 18 || 1002 | bbb | 19 || 1003 | ccc | 19 |+--------+------+------+
replace into student values ​​(1003,'ccc',17);
mysql> select * from student;+--------+------+------+| xuehao | name | age |+--------+------+------+| 1001 | aaa | 18 || 1002 | bbb | 19 || 1003 | ccc | 17 |+--------+------+------+

2. Update data

The update statement is used to update table data. 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 we only introduce the commonly used single-table update syntax:

UPDATE <table name> SET field1=value1 [,field2=value2…] [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 column value to be modified in the table. Each specified column value can be an expression or the default value corresponding to the column. If a default value is specified, the keyword DEFAULT can be used to represent the column value.
• WHERE clause: Optional. Used to limit the rows in the 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 to be modified.
  

3. Delete data

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

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

Use the DELETE statement to delete data from a single table. The syntax is:

DELETE FROM <table name> [WHERE clause] [ORDER BY clause] [LIMIT clause]

The syntax is as follows:

• <Table name>: Specify the table name where data is to be deleted.
• ORDER BY clause: Optional. When deleting, the rows in the table will be deleted in the order specified in the clause.
• WHERE clause: Optional. Indicates that the deletion conditions are limited for the deletion operation. If this clause is omitted, it means that all rows in the table will be deleted.
• LIMIT clause: Optional. Used to tell the server the maximum number of rows to be deleted before control is returned to the client.
  

Summarize:

This article mainly introduces the syntax of three DML statements. Although they seem simple, the various options are actually very complex, especially the insert statement, which has many frequently used options. Here we also want to remind everyone that you must be careful when executing update or delete statements. Not using the where condition will update or delete all data.

The above is a detailed explanation of the use of MySQL DML statements. For more information about MySQL DML statements, please pay attention to other related articles on 123WORDPRESS.COM!