Most of the previous discussions are about MYSQL queries. Now we talk about adding, deleting and modifying. After all, these are several operations commonly used in DML.

Now create a table

CREATE TABLE test(
a INT,
b VARCHAR(10),
c DATE
)

Add data

General insertion

Adding data is very simple, that is, adding data to the database. This is not to directly import sql files into the database as mentioned above.

Format:

--value It can also be implemented, but it is generally used by default values
-- Single line
INSERT INTO tablename [(Field name,Field name....) ]values (value,value...);

-- Multiline
INSERT INTO tablename [(Field name,Field name....)] values (value,value...),(value,value...),.....;

One thing to note about the inserted data is that when inserting data with fields, the subsequent data should be consistent with the corresponding field position in turn.

INSERT INTO test.test (a, b, c) VALUES('1', 'Zhang San', '2021-01-01');
INSERT INTO test.test (a, c, b) VALUES('2' ,'2021-01-02', 'Li Si');

INSERT INTO test.test (a, c, b) VALUES ('3' ,'2021-01-03', 'Wang Wu'),('4' ,'2021-01-04', 'Zhao Liu');

The order of the following data is consistent according to the fields in front of VALUES. It is not necessary to be consistent with the order in the table. Sometimes, for example, if the B and C types in the instance are consistent, the data will not report an error before and after putting in, but the data is the wrong data for the original intention.

If there are no fields, data can only be placed in parentheses in the order in which the table is created.

INSERT INTO test.test  VALUES('1', 'Zhang San', '2021-01-01');

Another place is the length when inserting data. It should be within the length of the defined type or the length range of the type. Different versions may have different effects. Some will intercept part of the length and save it, and some will report an error and cannot be inserted. Therefore, this should be considered clearly when designing table fields and data.

INSERT INTO test.test  VALUES('1', 'Zhang San, Zhang San, Zhang San, Zhang San 1', '2021-01-01');

Insert this data and it will be automatically intercepted in version 5.0 as follows:

In 8.0:

When inserting a large amount of data, it is recommended to use the multi row insertion mode, because the efficiency of multi row insertion will be higher.

Another problem is that when inserting data, it not only corresponds to the field variables one by one, but also the inserted data must have a value with the number of fields in the table, otherwise an error may be reported. Why don't you say you will report an error? We'll talk about this later in a separate article.

Insert query data into table

The format is as follows:

INSERT INTO Table name
[(Field name,............ )]
SELECT .... FROM Table name

Because there is only one test table, the existing data is directly inserted into the table again;

INSERT INTO test.test  SELECT 	a, b, c FROM test.test 

Now look at the results:

Update data

Because when inserting data, you sometimes need to modify the data. After all, some data changes dynamically. This requires the following format:

UPDATE Table name SET Field 1=Modified value [,Field 2=Modified value]  [WHERE condition]
-- The following conditions can be seen when using query WHERE Almost all conditional expressions can be used.

If there are no conditions for modifying the data, all the data will be modified. Of course, you can also limit it by conditions, and then modify the data to be modified.

UPDATE test.test  SET b='Wang Dongfang',c='2020-01-01' WHERE  a='1'

However, in addition, sometimes even if the SQL statement itself is correct (excluding field types and length limiting elements), it still fails to update successfully, which may be caused by external constraints. We'll talk about this later.

Delete data

You can also delete some data in specific formats

DELETE FROM  Table name  [WHERE condition]
-- The following conditions can be seen when using query WHERE Almost all conditional expressions can be used.

If there are no conditions, all data will be deleted, and the conditional statement will delete the data that meets the conditions.

Then demonstrate:

DELETE FROM  test.test  WHERE  a='1'

In fact, if the deletion is unsuccessful, the SQL statement is correct, but it is still unsuccessful. It may be caused by external constraints. TRUNCATE has been compared before, so I won't explain it anymore.

DML statements automatically commit transactions by default, or SET autocommit=FALSE

expansion

What's new in MYSQL8: calculated columns

What is a calculated column? Simply put, the value of a column is calculated from other columns. For example: column A is 1, column b is 2, and column c does not need to be inserted manually. Define a+b as c.

demonstration:

CREATE TABLE test1(
a INT ,
b INT,
c INT generated always AS (a+b) VIRTUAL
);

INSERT INTO test1 (a,b) VALUES (1,2);
INSERT INTO test1 (a,b) VALUES (2,3);

What if you change the value of a or b?

UPDATE test1 SET  a='4' WHERE a='1';

It can be seen that modifying the value of a or b will affect the value of c, but what if you modify the value of c?

UPDATE test1 SET  c='10' WHERE c='5';

An error will be reported if found. After all, the value of c depends on a and b, so the value of c cannot be modified directly.

Although it is convenient to query some values to be calculated, it will naturally occupy some resources. So it depends on the situation.