Master MySQL DML: Insert, Update, Delete, and Truncate Explained

Explanation

DML (Data Manipulation Language) refers to commands that modify data in a database, primarily INSERT, UPDATE, and DELETE, which are essential knowledge for learning MySQL.

As in previous sections, content inside [] can be omitted.

INSERT operation

Row‑by‑row insertion

Syntax:

<code>insert into t_name[(column_name1,column_name2,...)] values (val1,val2);
or
insert into t_name set column_name1 = val1, column_name2 = val2;</code>

1. The number, type, and order of columns and values must match, otherwise errors occur.

2. NOT NULL columns must receive a value; nullable columns can be omitted or use NULL.

3. Numeric values do not need quotes; character or date values require single quotes.

4. Omitting the column list means all columns are targeted; the order of values must follow the table definition.

5. The second syntax form is now rarely used.

Example:

<code>mysql> desc `user1`;
+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| id      | bigint(20)   | NO   | PRI | NULL    | auto_increment |
| name    | varchar(20)  | NO   |     | NULL    |                |
| age     | int(11)      | NO   |     | 0       |                |
| address | varchar(255) | YES  |     | NULL    |                |
+---------+--------------+------+-----+---------+----------------+
4 rows in set

mysql> insert into `user1`(name,age,address) values('brand',20,'fuzhou');
Query OK, 1 row affected

mysql> insert into `user1`(age,address) values(20,'fuzhou');
1364 - Field 'name' doesn't have a default value

mysql> insert into `user1` values('sol',21,'xiamen');
1136 - Column count doesn't match value count at row 1

mysql> insert into `user1` values(null,'sol',21,'xiamen');
Query OK, 1 row affected

mysql> select * from `user1`;
+----+-------+-----+---------+
| id | name  | age | address |
+----+-------+-----+---------+
|  3 | brand |  20 | fuzhou  |
|  4 | sol   |  21 | xiamen |
+----+-------+-----+---------+
2 rows in set</code>

Batch insertion

Syntax:

<code>insert into t_name [(column_name1,column_name2)] values (val1_1,val1_2),(val2_1,val2_2)...);
or
insert into t_name [(column_name1,column_name2)] select o_name1,o_name2 from o_t_name [where condition];</code>

1. In the first form, each parenthesized value set must match the column count; multiple rows are separated by commas to improve insertion efficiency.

2. In the second form, the selected columns must have the same number, order, and type as the target columns; column list can be omitted to insert into all columns.

Example:

<code>mysql> insert into `user1`(name,age,address) values('brand',20,'fuzhou'),('sol',21,'xiamen');
Query OK, 2 rows affected

mysql> select * from `user1`;
+----+-------+-----+---------+
| id | name  | age | address |
+----+-------+-----+---------+
|  5 | brand |  20 | fuzhou  |
|  6 | sol   |  21 | xiamen |
+----+-------+-----+---------+
2 rows in set

mysql> desc `user2`;
+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| id      | bigint(20)   | NO   | PRI | NULL    | auto_increment |
| name    | varchar(20)  | NO   |     | NULL    |                |
| age     | int(11)      | NO   |     | 0       |                |
| address | varchar(255) | YES  |     | NULL    |                |
| sex     | int(11)      | NO   |     | 1       |                |
+---------+--------------+------+-----+---------+----------------+
5 rows in set

mysql> insert into `user2` (name,age,address,sex) select name,age,address,null from `user1`;
Query OK, 2 rows affected

mysql> select * from `user2`;
+----+-------+-----+---------+------+
| id | name  | age | address | sex  |
+----+-------+-----+---------+------+
|  7 | brand |  20 | fuzhou  | 1    |
|  8 | sol   |  21 | xiamen  | 1    |
+----+-------+-----+---------+------+
2 rows in set</code>

UPDATE operation

Data update

Syntax:

<code>update t_name [[as] alias] set [alias.]column_name1 = val1, [alias.]column_name2 = val2 [where condition];</code>

1. An alias is optional; without it the table name serves as the identifier.

2. The AS keyword and the WHERE clause are optional; users can update rows that meet specific conditions.

Example:

<code>mysql> select * from `user2`;
+----+-------+-----+---------+------+
| id | name  | age | address | sex  |
+----+-------+-----+---------+------+
|  7 | brand |  20 | fuzhou  | NULL |
|  8 | sol   |  21 | xiamen  | NULL |
+----+-------+-----+---------+------+

mysql> update `user2` as u2 set u2.name = 'hero', u2.age = 23, u2.sex = 1 where id = 7;
Query OK, 1 row affected

mysql> select * from `user2`;
+----+------+-----+---------+------+
| id | name | age | address | sex  |
+----+------+-----+---------+------+
|  7 | hero |  23 | fuzhou  | 1    |
|  8 | sol  |  21 | xiamen  | NULL |
+----+------+-----+---------+------+</code>

Updating multiple tables simultaneously with different aliases is possible but not recommended due to complexity and maintenance concerns.

DELETE operation

Delete with alias

Syntax:

<code>delete [alias] from t_name [[as] alias] [where condition];</code>

1. Alias works like a table identifier; without it the table name is used.

2. When an alias is defined, it must be referenced after DELETE.

Example:

<code>mysql> select * from `user2`;
+----+------+-----+---------+------+
| id | name | age | address | sex  |
+----+------+-----+---------+------+
|  7 | hero |  23 | fuzhou  | 1    |
|  8 | sol  |  21 | xiamen  | NULL |
+----+------+-----+---------+------+

mysql> delete alias from `user2` as alias where sex = 1;
Query OK, 1 row affected

mysql> select * from `user2`;
+----+------+-----+---------+------+
| id | name | age | address | sex  |
+----+------+-----+---------+------+
|  8 | sol  |  21 | xiamen  | NULL |
+----+------+-----+---------+------+</code>

3. Omitting the WHERE clause deletes all rows, which should be used with caution.

Truncate method

Syntax:

<code>truncate t_name;</code>

Example:

<code>mysql> select * from `user2`;
+----+-------+-----+----------+-----+
| id | name  | age | address  | sex |
+----+-------+-----+----------+-----+
| 12 | brand | 21  | fuzhou   | 1   |
| 13 | helen | 20  | quanzhou | 0   |
| 14 | sol   | 21  | xiamen   | 0   |
+----+-------+-----+----------+-----+

mysql> truncate `user2`;
Query OK, 0 rows affected

mysql> select * from `user2`;
Empty set</code>

Truncate clears the table and resets auto‑increment counters, unlike DELETE which retains the current counter value.

Truncate vs. Delete comparison

1. TRUNCATE removes all rows and releases space but keeps the table definition; it cannot include a WHERE clause.

2. DELETE removes rows optionally filtered by a WHERE clause; each row deletion is logged for possible rollback.

3. Both commands preserve the table structure, columns, constraints, and indexes.

4. If a foreign‑key constraint exists, TRUNCATE is prohibited; DELETE without WHERE must be used.

5. DELETE operations are recorded in the rollback segment and fire triggers; TRUNCATE does not fire triggers.

6. When a table has an auto‑increment column, TRUNCATE resets the counter to 1, whereas DELETE may continue incrementing from the previous maximum unless the server restarts.

7. The DROP statement, covered earlier, removes the table definition, data, constraints, triggers, and indexes.