1、 insert data

1.1、 Inserts data in the default order for all fields of the table

INSERT INTO  Table name 
VALUES (value1,value2,....);
 In the value list, you need to specify a value for each field of the table , And the order of values must be the same as that of fields in the data table 
INSERT INTO departments
VALUES (70, 'Pub', 100, 1700);

1.2、 Insert data for the specified fields of the table

INSERT INTO  Table name (column1 [, column2, …, columnn])
VALUES (value1 [,value2, …, valuen]);
 Insert data for the specified fields of the table , Is in the INSERT Statement inserts values into only some fields , The values of other fields are the values of the table definition 
 The default value is .
 stay  INSERT  Column names are arbitrarily listed in the clause , But once listed ,VALUES To insert value1,....valuen Need and 
column1,...columnn The columns correspond one by one . If the type is different , Will not be able to insert , also MySQL There will be mistakes 

1.3、 Insert multiple records at the same time

INSERT Statement can insert multiple records into the data table at the same time , Specify multiple value lists when inserting , Each value list is separated by commas 
 open , The basic syntax is as follows ：
INSERT INTO table_name(column1 [, column2, …, columnn])
VALUES
(value1 [,value2, …, valuen]),
(value1 [,value2, …, valuen]),
……
(value1 [,value2, …, valuen]);
 Use INSERT When inserting multiple records at the same time ,MySQL It will return some additional information that is not available when performing single line insertion , The content of this information 
 The meaning is as follows ： ●　Records： Indicates the number of records inserted . ●　Duplicates： Indicates the records that were ignored during insertion , The reason may be that 
 Some records contain duplicate primary key values . ●　Warnings： Data values indicating a problem , For example, data type conversion occurs .

1.4、 Insert the query results into the table

INSERT Can also be SELECT Insert the result of the statement query into the table , At this time, it is not necessary to input the values of each record one by one , just To use a INSERT Statement and a line SELECT Statements can be quickly inserted from one or more tables into a table Multiple lines .

INSERT INTO emp2
SELECT *
FROM employees
WHERE department_id = 90;
INSERT INTO sales_reps(id, name, salary, commission_pct)
SELECT employee_id, last_name, salary, commission_pct
FROM employees
WHERE job_id LIKE '%REP%';

2、 Delete data

DELETE FROM table_name [WHERE ];

table_name Specify the table to delete ;“[WHERE ]” Is an optional parameter , Specify deletion criteria , without WHERE Clause , DELETE Statement will delete all records in the table .

3、 Modifying data

UPDATE table_name
SET column1=value1, column2=value2, … , column=valuen
[WHERE condition]

 You can update more than one piece of data at a time .
 If you need to roll back data , It needs to be guaranteed in DML front , Set it up ：SET AUTOCOMMIT = FALSE;


 Use  WHERE  Clause specifies the data to be updated 
UPDATE employees
SET department_id = 70
WHERE employee_id = 113;
 If omitted  WHERE  Clause , Then all data in the table will be updated .

4、 MySQL8 New characteristics ： Calculated column

What is a calculated column ？ Simply put, the value of a column is calculated from other columns . for example ,a The column value is 1、b The column value is 2,c Column Manual insertion is not required , Definition a+b As the result of the c Value , that c Is the calculation column , It is calculated from other columns . stay MySQL 8.0 in ,CREATE TABLE and ALTER TABLE Both support adding calculation Columns . Let's say CREATE TABLE Take as an example Explain . give an example ： Define data table tb1, Then define the fields id、 Field a、 Field b And field c, Which field c Is a calculated column , Used to calculate a+b Of value . First create a test table tb1, The statement is as follows ：

CREATE TABLE tb1(
id INT,
a INT,
b INT,
c INT GENERATED ALWAYS AS (a + b) VIRTUAL
);