“This is the fifth day of my participation in the Gwen Challenge in November. Check out the details: The Last Gwen Challenge in 2021.”

Table record management

Prepare the test sheet first

create table test(
    id int PRIMARY key auto_increment,
    uname VARCHAR(20),
    sex TINYINT default 1
)
Copy the code

insert

Specifies a single field insertion

insert into test(uname) values('b');
Copy the code

We can choose to assign to fields that we care about and to assign to fields that are not required to be empty. The default values are used for other fields that are not specified in the table. If the default is not declared, the value is null

Do not specify single field insertion

Building on the previous insert method, we can omit the fields inserted in the SQL statement

insert into test values(default.'a'.default)
Copy the code

As shown in the figure, which value corresponds to which column in the specified inserted data is not shown, mysql will parse the table according to the current column order, so we need to assign a value for all fields in order to occupy the space. We can set it to null if the field allows, or we can use default to indicate that we want to use the default value for the column definition

Bulk insert

Similar to single insert, you can choose to specify a field, or not specify and then enter the data sequentially

Mysql has a special insert operation that supports multiple records to be inserted at a time. The format is as follows

insert into table (c1, c2, ..) values(a1, b1, ..) , (a2, b2, ...) .Copy the code

If multiple records need to be inserted, separate them

delete

Conventional delete

We routinely use ids as a basis for deletion

DELETE from test where id=1
Copy the code

Batch delete

In our application, it is sometimes necessary to support deleting multiple records at once. If you traverse a collection of ids and then execute a single delete SQL, the performance of 1000 operations will be significantly delayed

The recommended optimization is to change the deletion condition to IN

DELETE
FROM
 test
WHERE
 id IN (2.3.4);
Copy the code

Or, depending on our actual business, we can modify the condition that follows where in the delete statement. Here we can understand that mysql will delete all records that meet the condition for us