MySQL tutorial DML data manipulation language example detailed explanation

1. Data Manipulation Language (DML)

The full name of data manipulation language is Data Manipulation Language, abbreviated as DML. DML has four main common functions, as shown in the following table. The query function in DML is a commonly used operation as a data analyst. Query knowledge will be interspersed in all subsequent articles because this issue cannot be fully discussed in one go. Today's article mainly talks about the usage of the skills of adding, deleting and modifying.

The following operations are all based on this student table.

# Create database create database if not exists stu;
# Use database use stu;
# Create a table create table student(
    sid int primary key auto_increment,
    sname varchar(20) not null,
    sex varchar(10)
)charset=utf8;

2. Add data (insert)

Case 1: Add data to all fields;
-- There are two ways to add: When inserting data into all fields, you don't need to write the field name.
insert into student(sid,sname,sex) values ​​(1,"张三","男");
insert into student values ​​(2,"李莉","女");

Case 2: Add data to some fields;
insert into student(sname) values ​​("王五");
insert into student(sname,sex) values ​​("赵六","男");

Case 3: Insert multiple data at one time;
insert into student(sname,sex) values 
("Liu Bei","male"),("Diao Chan","female"),("Zhuge Liang","male");

The results are as follows:

3. Copy the existing table and generate a new table

1) Copy the structure and data of an existing table.

"Create a student1 table, the structure and data of which come from the student table."
mysql> create table student1 select * from student;

The operation results are as follows:

2) Only copy the structure of the existing table (the result is an empty structure table).

"Create a student2 table and copy only the structure of the student table, not the data inside."
mysql> create table student2 select * from student where 0;

The operation results are as follows:

3) Based on 2, insert data into the empty structure table.

"Based on 2, insert data into the student2 table. The data comes from the student table."
mysql> insert into student2 select * from student;

The operation results are as follows:

4. Modify data update

The update and delete statements must be used in conjunction with the where filter, otherwise the records of the entire table will be deleted.

"Syntax format: Multiple columns are separated by commas"
update table name set column 1 = value 1 [column 2 = value 2, column 3 = value 3...] where condition;

"The demonstration example is as follows"
-- Change the name of Wang Wu, whose sid is 3, to Wang Ba.
update student set sname="Wang Ba" where sid = 3;
-- Change Zhuge Liang's name, whose sid is 7, to Kongming, and his gender to a tough guy.
update student set sname="Kong Ming",sex="Macho" where sid=7;

The operation results are as follows:

5. Delete data delete: physical deletion (once deleted, it is completely gone).

The update and delete statements must be used in conjunction with the where filter, otherwise the records of the entire table will be deleted.

"Syntax format:"
delete from table name where condition;

"The demonstration example is as follows"
delete from student where sname="张三";

The operation results are as follows:

6. The difference between truncate and delete

Use the following data to illustrate the difference between the two:

1) delete

2) truncate

3) The difference between truncate and delete

① The structure is not modified, only the data is cleared.
② delete does not release resources, truncate releases the space occupied by the table (it will reset the primary key increment)
③ delete is to delete line by line. The deletion record is recorded as a transaction in the log file and can be rolled back. Truncate deletes all data in the table at once. The deleted records are not recorded in the log file and cannot be restored. The deletion efficiency is higher than delete.

The above is the detailed content of the MySQL tutorial DML data manipulation language example detailed explanation. For more information about DML data manipulation language, please pay attention to other related articles on 123WORDPRESS.COM!