MySQL learning database operation DML detailed explanation for beginners

1. Insert statement

1.1 Insert a row

There are two types:
1. In the form of parentheses

Note: If there is no parenthesis after the table name, all columns will be added by default.

2. In set form

insert into + table name
set A field in the table = the value to be inserted, A field in the table = the value to be inserted, A field in the table = the value to be inserted...

1.2 Insert multiple rows

insert into + table name (column name) + values ​​(value 1, value 2…), (value 1, value 2…)…

Note that multi-row insertion and join query statements are not supported here, and the primary key cannot be empty or repeated.

1.3 Insert query statement

insert into + table name (column name) + select statement

Note that the query statement is run first and then the query result set is inserted into the table, but it must correspond to the column after the table name. The primary key cannot be repeated and cannot be empty, otherwise the insertion will fail.

2. Modify the statement

2.1 Modify single table records

update table name
set + column=newvalue, column=newvalue…
where + filter condition

2.2 Modify multi-table records

1. Use sql92 syntax

update+table1(alias),table2(alias)
set + column = value, column = value...
where+join conditions and filter conditions

2. Use sql99 syntax

update+table1+(alias)
inner|left|right join Table 2 Alias
on+connection condition
set + column = value, column = value...
where+filter condition

3. Delete statement

3.1 Method 1: Use delete to delete

1. Single table deletion

delete from + table name + where + filter condition

Will delete all the corresponding data in a whole row

2. Multiple table deletion

delete table
from Table 1
inner|left|right| join Table 2
on connection condition
where + filter condition

This is sql99 syntax, and sql92 syntax is also supported. The table after delete is the table whose contents are to be deleted.

3.2 Method 2: Delete using truncate

truncate table + table name

Directly delete the data of the entire table

3.3 The difference between using truncate and delete

1. delete can add where conditions but truncat cannot
2.truncat is less efficient
3. If the deleted table has a self-increasing sequence, then delete and then add will start from the breakpoint, while truncat starts from 1
4. truncat deletes no return value, but delete does.
5. truncat can not be rolled back, delete can not be rolled back

The above is the detailed content of MySQL learning: a detailed explanation of database operation DML for beginners. For more information about MySQL database operation DML, please pay attention to other related articles on 123WORDPRESS.COM!