MySQL Getting started inserting, deleting, and updating data SQL statements

Definition OF INSERT statement:

Insert statement inserts one piece of data at a time

Insert into links (name,address) VALUES (' Jerichen ', ' Gdsz ');

Insert statement inserts more than one data at a time:

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

# Insert two data at the same time, look at the syntax description, that into was omitted by me
Insert links (Name,url) VALUES (' Jerichen ', ' Gdsz '), (' Alone ', ' gdgz ');

UPDATE Tbl_name Set the column to change

For example, in the pet table, we found that the sex of the pet Whistler was not specified, so we can modify this record as follows:

Mysql> Update pet Set ***= ' F ' where name= ' Whistler ';

Delete Deletes records

The DELETE statement has the following format:

Delete from Tbl_name WHERE to delete records

We've all seen the basics, look at the example below

Insert data:

Method One: Bulk INSERT

Basic syntax:

INSERT into Tb_name (col1, col2, ...) VALUES (Val1, Val2, ...) [, (Val1, Val2, ...),...]
Character type: Single quotation mark
Numeric type: no quotes required
Date-Time Type: no quotes required
Nulls: null, cannot be written as '
If you insert two rows of data into the tutors table:

Mysql> INSERT into Tutors (tname,gender,age) VALUES (' Sam ', ' m ', '), (' Barlow ', ' m ', 27);
Query OK, 2 rows affected (0.03 sec)
Records:2 duplicates:0 warnings:0

Method Two: Insert one row at a time

Basic syntax:
INSERT into Tb_
Ame SET col_name={expr | DEFAULT}, ...
If you insert a row of data into the tutors table:

Mysql> INSERT into tutors SET tname= ' Winne ', gender= ' F ', age=25;
Query OK, 1 row affected (0.04 sec)

Method Three: Insert query results into a table

Basic syntax:
INSERT [into] tbl_name [(Col_name,...)] SELECT ...
Insert the age greater than 20 in the students table into 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

Second, replace insert data

Inserting data with insert causes the insertion to fail if the constraint is violated. If the students table requires that the name field not be the same, you will fail if you insert the name that already exists by using insert. Insert data using Replace, in which case you can replace the original data with the new data, and insert the data as normal if the constraint relationship is not violated.

Replace basic syntax is exactly the same as insert:

Method One:

REPLACE [Low_priority | Delayed]
[Into] Tbl_name
[PARTITION (Partition_name,...)]
[(Col_name,...)]
{VALUES | VALUE} ({expr | DEFAULT},...), (...),...

Method Two:

REPLACE [Low_priority | Delayed]
[Into] Tbl_name
[PARTITION (Partition_name,...)]
SET col_name={expr | DEFAULT}, ...

Method Three:

REPLACE [Low_priority | Delayed]
[Into] Tbl_name
[PARTITION (Partition_name,...)]
[(Col_name,...)]
SELECT ...

Delete data:

Basic syntax:
DELETE from Tb_name WHERE condition;

Where clause usage same as where in select
The following deletes a row in the tutors table that is less than 30:

Mysql> DELETE from Tutors WHERE age < 30;
Query OK, 6 rows affected (0.07 sec)
Note that when you delete data, the corresponding data in the AutoIncrement counter is not deleted.

If you need to clear a table and reset the AutoIncrement counter, you can use the following command:

TRUNCATE Tb_name

Iv. Update Data

Basic syntax:
UPDATE tb_name SET col1= ..., col2= ... WHERE
The following changes the age of Xuzhu in the students table to 20:

mysql> UPDATE students SET age=20 WHERE name= ' Xuzhu ';
Query OK, 1 row affected (0.04 sec)
Rows matched:1 changed:1 warnings:0