SQL statements for inserting, deleting, and updating data in MySQL

We use three different commands to update, delete, and save data in mysql. Next we will introduce how to save, delete, and update data in mysql.

Insert statement Definition:

Insert statement insert a data entry at a time

Insert into links (name, address) values ('jerichen', 'gdsz ');

Insert statements insert multiple data records at a time:

1, field value 2, field value 3), (value of another field 1, value of another field 2, value of another field 3 );

# Insert two pieces of data at the same time. Check the syntax description. I have omitted the.
Insert links (name, url) values ('jerichen', 'gdsz'), ('alone', 'gdgz ');

UPDATE tbl_name SET column to be changed

For example, in the pet table, we find that the gender of the pet Whistler is not specified, so we can modify this record as follows:

Mysql> update pet set *** = 'F' where name = "Whistler ";

Delete record

The DELETE statement has the following format:

Delete from tbl_name WHERE record to be deleted

We have read all the basic information. The following is an example.

1. INSERT data:

Method 1: Batch insert

Basic Syntax:

Insert into tb_name (col1, col2,...) VALUES (val1, val2,...) [, (val1, val2,...),...]
Character Type: single quotes
Numeric type: no quotation marks are required.
Datetime type: no quotation marks are required
NULL: NULL, cannot be written''
For example, insert two rows of data to the tutors table:

Mysql> insert into tutors (Tname, Gender, Age) VALUES ('Sam ', 'M', 28), ('barlow', 'M', 27 );
Query OK, 2 rows affected (0.03 sec)
Records: 2 Duplicates: 0 Warnings: 0

Method 2: Insert a row at a time

Basic Syntax:
Insert into tb _
Ame SET col_name = {expr | DEFAULT },...
For example, insert a row of data to the tutors table:

Mysql> insert into tutors SET Tname = 'winne', Gender = 'F', Age = 25;
Query OK, 1 row affected (0.04 sec)

Method 3: insert the query result to the table

Basic Syntax:
INSERT [INTO] tbl_name [(col_name,...)] SELECT...
Insert Age greater than 20 in the students table to the tutors table as follows:

Mysql> SELECT Name, Gender, Age FROM students WHERE Age> 20;
+ ------------- + -------- + ------ +
| Name | Gender | Age |
+ ------------- + -------- + ------ +
| DingDian | M | 25 |
| HuFei | M | 31 |
| Xuzhu | M | 26 |
| LingHuchong | M | 22 |
+ ------------- + -------- + ------ +
4 rows in set (0.19 sec)

Mysql> insert into tutors (Tname, Gender, Age) SELECT Name, Gender, Age FROM students WHERE Age> 20;
Query OK, 4 rows affected (0.09 sec)
Records: 4 Duplicates: 0 Warnings: 0

Ii. insert data into REPLACE

When you use INSERT to INSERT data, if the constraints are violated, insertion will fail. If the students table requires that the Name field be different, INSERT an existing Name using INSERT will fail. If you use REPLACE to INSERT data, you can use new data to REPLACE the original data. If you do not violate the constraints, you can INSERT data normally like INSERT.

The basic syntax of REPLACE is exactly the same as that of INSERT:

Method 1:

REPLACE [LOW_PRIORITY | DELAYED]
[INTO] tbl_name
[PARTITION (partition_name,...)]
[(Col_name,...)]
{VALUES | VALUE} ({expr | DEFAULT },...),(...),...

Method 2:

REPLACE [LOW_PRIORITY | DELAYED]
[INTO] tbl_name
[PARTITION (partition_name,...)]
SET col_name = {expr | DEFAULT },...

Method 3:

REPLACE [LOW_PRIORITY | DELAYED]
[INTO] tbl_name
[PARTITION (partition_name,...)]
[(Col_name,...)]
SELECT...

Iii. DELETE data deletion:

Basic Syntax:
Delete from tb_name WHERE condition;

The usage of the WHERE clause is the same as that of the WHERE clause in the SELECT statement.
Delete the rows whose Age is less than 30 in the tutors table as follows:

Mysql> delete from tutors WHERE Age <30;
Query OK, 6 rows affected (0.07 sec)
Note that the corresponding data in the AUTOINCREMENT counter is not deleted after the data is deleted.

To clear a table and reset the AUTOINCREMENT counter, run the following command:

TRUNCATE tb_name

4. UPDATE Data

Basic Syntax:
UPDATE tb_name SET col1 =..., col2 =... WHERE
Change the Age of xuzhu in the students table to 20 as follows:

Mysql> UPDATE students SET Age = 20 WHERE Name = 'xuzhu ';
Query OK, 1 row affected (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 0