MySQL INSERT, update and delete

Database additions and deletions are to be mastered.

This part will take a look at the previous 3 relatively simple parts, increase, delete, change.

Inserting data

Insert data for all fields of a table

INSERT INTO table_name (column_list) values (value_list);

Attention:

To specify the table name of the inserted table;

column_list Specifies the columns to insert the data into;

The value_list specifies the corresponding inserted data;

The number of field columns and data values must be the same;

Either all of the field names are specified, or they are all unspecified;

The inserted column name order can be different from the order in which the table was created, as long as the value of the inserted data corresponds to the column field;

If the column name is empty, you specify a value for each field in the table, and the order is the same as the field definition.

Insert data for a specified field in a table

Inserts data into only a subset of fields, while other fields use the default values

To ensure that the inserted data matches the field's data type

Inserting more than one data at a time

INSERT INTO table_name (column_list)

Values

(Value_list1),

(Value_lsit2),

...,

(VALUE_LSITN);

More efficient INSERT statements for multiple rows of records

Inserting query results into a table

Insert can also insert the results of a SELECT statement query into a table

Example:

Insert into table_name1 (COLUMN_LSIT1)

Select (Column_list2) from table_name2 where (condition);

The number of fields in the Column1 and the data type of the field must be the same to insert.

When MySQL is inserted, it does not care about the column name, only the location of the column.

Mysql> CREATE TABLE P1
(N1 char (5) not NULL);
Query OK, 0 rows affected (0.08 sec)

mysql> INSERT INTO P1
Values
(' abc ')
,
(' EFG ');
Query OK, 2 rows affected (0.04 sec)
Records:2 duplicates:0 warnings:0

Mysql> CREATE TABLE P2
--(N2 varchar (5) not NULL);
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO P2
Values
(' xxx '),
(' yyy '),
(' zzz ');
Query OK, 3 rows affected (0.01 sec)
Records:3 duplicates:0 warnings:0

mysql> INSERT INTO P1 (n1) Select (N2) from P2;
Query OK, 3 rows affected (0.06 sec)
Records:3 duplicates:0 warnings:0

Mysql> select * from P1;
+-----+
| N1 |
+-----+
| ABC |
| EFG |
| xxx |
| yyy |
| zzz |
+-----+
5 rows in Set (0.00 sec)
This Nima, a char (5), a varchar (5), does not yet insert the results of the select query.

Update data

UPDATE table_name

Set column_name1 = value1, column_name2 = value2, ..., column_namen = Valuen

where (condition);

To ensure that the update ends with a WHERE clause, otherwise, all rows will be updated in MySQL.

Delete data

Delete from table_name [where <condition>];

Or that's the best way to have a where otherwise, all the records will be deleted, good scary yo.

Tip Before you execute the delete, take a moment to select and delete the data.

If you want to delete all the data in the table, you can also use the TRUNCATE TABLE statement

TRUNCATE TABLE will delete the original table directly and recreate a new one faster than the delete.

TRUNCATE TABLE table_name;

MySQL INSERT, update and delete