🏷️ The current chapter is [MySQL basics] 05: DML data operation language 🤖

🏷️ More: MySQL basic tutorial , take you to start MySQL database on a zero basis! 💪

🏷️ Welcome to praise 👍 Collection 🌟 Attention ❤️ Leaving a message. ✍️

DML (Data Manipulation Language)

DML is used to add, delete and modify data in the data table.

INSERT: INSERT data (add data)

Basic grammar

1. INSERT... VALUES statement

   insert into <tableName> [<columnName_1>,...,<columnName_n>] values(value_1,...,value_n)
   

   • < tablename >: Specifies the name of the operated table.
   • <columnName_ x> : Specifies the column name to insert data into. If you insert data into all columns in the table, all column names can be omitted. You can directly use insert < table name > values (...).
   • VALUES or VALUE clause: this clause contains a list of data to insert. The order of data in the data list should correspond to the order of columns.

2. INSERT... SET statement

   insert into <tableName> set <columnName_1> = <value_1> , ... , <columnName_n> = <value_n>;
   -- This statement is used to directly specify the corresponding column values for some columns in the table, that is, the column names of the data to be inserted are SET Clause, col_name For the specified column name, the equal sign is followed by the specified data, while for unspecified columns, the column value is specified as the default value of the column.
   

• Use the INSERT... VALUES statement to INSERT a row of data or multiple rows of data into the table;
• Use the INSERT... SET statement to specify the value of each column in the inserted row or the value of some columns;
• INSERT... SELECT statement inserts data from another table into a table.
• The INSERT... SET statement can be used to INSERT the values of some columns into the table, which is more flexible;
• The INSERT... VALUES statement can INSERT multiple pieces of data at a time.
• In MySQL, processing multiple inserts with a single INSERT statement is faster than using multiple INSERT statements. When using a single INSERT statement to INSERT multiple rows of data, you only need to enclose each row of data with ().

Add values to all fields in the table

insert into <tableName> values(value_1 , ... , value_n);
insert into <tableName>(<columnName_1> , ... , <columnName_n>) values(value_1 , ... , value_n);

-- sample sql
insert into job(job) values("Writer");
insert into job(job) values("Writer") , ("Test") , ("Engineer");

Adds a value to the specified field in the table

Inserting data into the specified field of the table is to INSERT values into only some fields in the INSERT statement, while the values of other fields are the default values when the table is defined.

insert into <tableName> (<columnName>) values(value_1);

-- sample sql
insert into worker(name,job) values("Holeo","Test");

INSERT INTO... FROM statementcopy table data

The insert into... SELECT... FROM statement is used to quickly fetch data FROM one or more tables and insert the data into another table as row data.

The SELECT clause returns a query result set. The INSERT statement inserts the result set into the specified table. The number of fields and data types of each row of data in the result set must be exactly the same as the operated table.

insert into <tableName_1>(<columnName_1> , ... , <columnName_n>) select columnName_1 , ... , columnName_n from <table_2>;

-- sample sql
create table job_2(
	id int primary key auto_increment,
    job varchar(32) not null
);
insert into job_2 select * from job;

UPDATE: modify data (UPDATE data)

Basic syntax of UPDATE statement:

update <tableName> set <columnName_1> = <value_1> [, ... , <columnName_n> = <value_n>] [where ...] [order by ...][limit n,m];

• < tablename >: used to specify the name of the table to be updated.
• Set clause: used to specify the column name and its value to be modified in the table. Each specified column value can be an expression or the corresponding DEFAULT value of the column. If you specify a DEFAULT value, you can use the keyword DEFAULT to represent the column value. When modifying multiple column values of a row of data, each value of the set clause can be separated by commas.
• where clause: optional. Used to limit the rows in the table to be modified. If not specified, all rows in the table are modified.
• order by clause: optional. Used to limit the order in which rows in a table are modified.
• Limit clause: optional. Used to limit the number of rows modified.

-- sample sql
update worker set salary = 20000 where job = 'Go' and salary < 20000 order by salary limit 2;

DELETE: DELETE data

Basic syntax of DELETE statement:

delete from <tableName> [where ...][order by][limit n,m];

• < tablename >: Specifies the table name to delete data.
• order by clause: optional. Indicates that when deleting, each row in the table is deleted in the order specified in the clause.
• where clause: optional. Indicates that the deletion condition is limited for the deletion operation. If this clause is omitted, it means that all rows in the table are deleted.
• limit clause: optional. Used to tell the server the maximum value of the deleted line before the control command is returned to the client.

When the WHERE condition is not used, all data will be deleted.

-- sample sql
delete from worker where age > 30 order by age DESC limit 1;

TRUNCATE: clear table records

TRUNCATE keyword is used to completely empty a table.

-- table Keywords can be omitted
truncate table <tableName>;

The difference between TRUNCATE and DELETE:

• DELETE is a DML type statement; TRUNCATE is a DDL type statement. They are used to empty the data in the table.
• Delete is to delete records line by line; TRUNCATE is as like as two peas, which is to delete the original table directly, and then recreate a new table exactly the same, instead of deleting data in the table by line, and executing data faster than DELETE. Therefore, when you need to delete all data rows in the table, try to use truncate statements to shorten the execution time.
• After deleting data, the data can be retrieved in conjunction with event rollback; TRUNCATE does not support transaction rollback, and the data cannot be retrieved after deletion.
• DELETE after deleting data, the system will not reset the counter of the auto increment field; After TRUNCATE clears the table record, the system will reset the counter of the auto increment field.
• DELETE is more widely used because it can DELETE some data by specifying conditions through the WHERE clause; TRUNCATE does not support the WHERE clause and can only DELETE the whole.
• DELETE will return the number of rows of deleted data, but TRUNCATE will only return 0, which has no meaning.

When the table is not needed, use DROP; When you still want to keep the table but want to DELETE all records, use TRUNCATE; When you want to DELETE some records, use DELETE.