MySQL data operation-use of DML statements

MySQL data operation-use of DML statements

illustrate

DML (Data Manipulation Language) refers to the operation instructions for adding, deleting and modifying the database. There are three main types: INSERT, UPDATE and DELETE, which stand for insert, update and delete. This is the basic knowledge that must be mastered when learning MySQL.

The contents in [] in the syntax can be omitted.

INSERT Operation

Insert row by row

The syntax format is as follows:

 insert into t_name[(column_name1,columnname_2,...)] values ​​(val1,val2);
 Or insert into t_name set column_name1 = val1, column_name2 = val2;

1. The field names and values ​​must be consistent in number, type, and position, otherwise exceptions may occur.

2. The not null field must have an inserted value, otherwise a non-null exception message will be reported. If you do not want to enter data in a field that allows null, neither the field nor the value will appear, or the value will be replaced by null.

3. For numeric types, the value does not need to be enclosed in single quotes. For other types such as character types or date types, the value needs to be enclosed in single quotes;

4. If the column_name after the table name is omitted, it means covering all fields of the table. The order of values ​​must be consistent with the order of fields in the table.

5. The second grammar mentioned above is more complicated to write and is rarely used now.

Test it out:

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

Batch Insert

The syntax format is as follows:

 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];

1. In the first syntax above, the number of values ​​after values ​​needs to be equal to the number of matching columns. You can set multiple values ​​separated by commas to improve data insertion efficiency.

2. In the second syntax, the number, order, and type of the fields in the select query and the fields in the inserted data must be consistent. The insert field can be omitted, which means inserting all fields of the t_name table. Condition optional.

Test it out:

mysql> insert into `user1`(name,age,address) values('brand',20,'fuzhou'),('sol',21,'xiamen');
Query OK, 2 rows affected
Records: 2 Duplicates: 0 Warnings: 0

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
Records: 2 Duplicates: 0 Warnings: 0

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

UPDATE Operation

Data Update

The syntax format is as follows:

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

1. Alias ​​means alias. The simpler the alias, the better the recognition. It is easy to identify and convenient to operate. If there is no alias, the table name is the alias.

2. The as in as alias is also optional, and the where condition is also optional, so users can select the required data that meets specific conditions for update.

Test it out:

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

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

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

Another way is to update multiple tables at the same time, using different aliases and some conditions to restrict them. However, this is not recommended because it is error-prone and difficult to maintain.

DELETE Operation

Delete method

The syntax format is as follows:

delete [alias] from t_name [[as] alias] [where condition];

1. As above, alias stands for alias. If there is no alias, the table name is the alias.

2. If the table has an alias, the delete command must be followed by the alias, otherwise the database will report an exception.

Test it out:

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

mysql> delete from `user2` as alias where sex=1;
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'as alias where sex=1' at line 1

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 |
+----+------+-----+---------+------+
1 row in set

3. If you want to delete all the data in the table, you don't need to include the where condition at the end, but use it with caution.

mysql> select * from `user2`;
+----+-------+-----+----------+-----+
| id | name | age | address | sex |
+----+-------+-----+----------+-----+
| 8 | sol | 21 | xiamen | 0 |
| 10 | brand | 21 | fuzhou | 1 |
| 11 | helen | 20 | quanzhou | 0 |
+----+-------+-----+----------+-----+
3 rows in set

mysql> delete from `user2`;
Query OK, 3 rows affected

mysql> select * from `user2`;
Empty set

Delete by truncate

The syntax format is as follows:

truncate t_name;
mysql> select * from `user2`;
+----+-------+-----+----------+-----+
| id | name | age | address | sex |
+----+-------+-----+----------+-----+
| 12 | brand | 21 | fuzhou | 1 |
| 13 | helen | 20 | quanzhou | 0 |
| 14 | sol | 21 | xiamen | 0 |
+----+-------+-----+----------+-----+
3 rows in set

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

mysql> select * from `user2`;
Empty set

It looks very similar to delete, but when you reinsert the data, you will find that its auto-increment primary key will start again from 1, but delete directly adds the original auto-increment value. See the id field below.

mysql> insert into `user2` (name,age,address,sex) values('brand',21,'fuzhou',1),('helen',20,'quanzhou',0),('sol',21,'xiamen',0);
Query OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from `user2`;
+----+-------+-----+----------+-----+
| id | name | age | address | sex |
+----+-------+-----+----------+-----+
| 1 | brand | 21 | fuzhou | 1 |
| 2 | helen | 20 | quanzhou | 0 |
| 3 | sol | 21 | xiamen | 0 |
+----+-------+-----+----------+-----+
3 rows in set

So what is the difference between truncate and delete? Let’s sort it out.

Comparison between truncate and delete

1. Truncate means clearing the data in the table and freeing up the space in the table, but does not delete the table's schema definition (table structure). Because there is no Where condition, the entire table is cleared instead of deleting specific rows.

2. The delete statement is used to delete data rows in the table. It can be followed by conditions to control the dimension and range of deletion. Each time a row is deleted from the table, the deletion operation of the row will be saved in the log as a transaction for possible rollback operations.

3. The similarities between truncate and delete are that they only delete data, and the table structure and its columns, constraints, indexes, etc. involved will not change.

4. If constrained by a foreign key, truncate cannot be used. Only delete statements without a where clause can be used.

5. The truncate operation will be recorded in the log, and the delete operation will be placed in the rollback segment. When executed, it will take effect only after the transaction is committed; therefore, delete will trigger the delete trigger (if any), but truncate will not.

6. If the auto-increment field is included as we tested above, the value of the auto-increment column will be initialized to 1 after the truncate method is cleared.

The delete method should be judged according to different situations (if all data is deleted and the database is not restarted, the previous max+1 will be used; if the database is restarted, the initial value of the auto-increment column will be recalculated).

7. There is also drop. The drop statement will delete the table including its structure, data, constraints that depend on the table, triggers, indexes, etc.

The above is the detailed content of MySQL data operation-DML statement. For more information about MySQL dml statements, please pay attention to other related articles on 123WORDPRESS.COM!

You may also be interested in:
  • Detailed explanation of the use of MySQL DML statements
  • MySQL DML statement summary
  • MySQL Basic Tutorial: Detailed Explanation of DML Statements

<<:  React's transition from Class to Hooks

>>:  Docker automated build Automated Build implementation process diagram

Recommend

A brief analysis of the event delegation mechanism and deep and shallow copying in JavaScript

Table of contents 1. Event delegation Event Bubbl...

Use CSS variables to achieve cool and amazing floating effects

Recently, I found a fun hover animation from the ...

How to solve the problem of Chinese garbled characters when inserting table data into MySQL

1. Problem During development, when inserting dat...

HTML table_Powernode Java Academy

To draw a table in HTML, use the table tag tr me...

MySQL 8.0.20 Window10 free installation version configuration and Navicat management tutorial graphic detailed explanation

1. Download and decompress MySQL 8.0.20 Download ...

5 Commands to Use the Calculator in Linux Command Line

Hello everyone, I am Liang Xu. When using Linux, ...

Overview of time configuration under Linux system

1. Time types are divided into: 1. Network time (...

Use Typescript configuration steps in Vue

Table of contents 1. TypeScript is introduced int...

Summary of MySQL string interception related functions

This article introduces MySQL string interception...

Move MySQL database to another disk under Windows

Preface Today I installed MySQL and found that th...

JavaScript countdown to close ads

Using Javascript to implement countdown to close ...

Discussion on the way to open website hyperlinks

A new window opens. Advantages: When the user cli...

Detailed steps for configuring virtual hosts in nginx

Virtual hosts use special software and hardware t...

Problems with installing mysql and mysql.sock under linux

Recently, I encountered many problems when instal...

Detailed explanation of the lock structure in MySQL

Mysql supports 3 types of lock structures Table-l...