DML language

DML (Data Manipulation Language) refers to the Data Manipulation Language, which is used to update the records of tables in the database.

As the name suggests, data is manipulated, so it is for table records, not for table structures.

insert statement

To realize data insertion, you need to provide table name, column name and value, so these parts need to be included in the insert statement.

Mode 1

Its syntax is as follows

insert into Table name (column name) values (Corresponding row and column value), (Corresponding row and column value), (Corresponding row and column value)

matters needing attention

• The following values are plural

• The inserted value type should be consistent or compatible with the column type, l both of which should correspond to each other (position and quantity), but not affected by the order.

• The column name can be omitted, and all fields are listed in the order of the table by default. According to the previous article, the value part needs to write all.

• Non nullable values must be inserted into data. There are two ways to insert nullable columns

  #Omit column names and values that can be empty columns
  insert into boy(id,boyname) values(123,'Zhang San')
  #Use null as the value of the nullable column
  insert into bot(id,boyname,userCp) values('23','Li Si'，null)
  

Mode 2

Its syntax is as follows

insert into Table name
set Listing=Value, column name=value...

In this way, you can explicitly insert columns with values without having to deal with null values.

contrast

• Method 1 supports batch insertion of multiple rows of data, while method 2 does not

• Method 1 supports sub query, while method 2 does not. This leads to the mainstream of mode I.

  insert into shoplist(orderID,flowerID,email,num) 
  SELECT max(orderID),1,'a@163.com',1 from myorder  //Note that the sub query is directly after the insert, and there is no value keyword. The query list of the subquery is the value of the column to be inserted.
  

update statement

As the name suggests, update statements generally modify several records rather than the whole table, so they are mostly used in conjunction with the where clause. It can be divided into updating single table records and multi table records.

Update sheet

Basic grammar

update Table name
set Listing=Value, column name=value
[where clause]   //If there is no where clause, the full table is updated.

Update multiple tables

Updating multiple tables refers to connecting multiple tables as a large table. Update the fields of the large table. Because the connection mode is divided into sql92 and sql99, there are two syntax for updating multiple tables

#sql92
update Table 1 alias 1, table 2 alias 2
set Listing=Value, column name=value
where Alias 1.key = Alias 2.key  //Connection conditions must be
and  [Screening conditions]  

#sql99
update Table 1 alias 1
inner join | left outer join |right outer join  Table 2 alias 2
on Alias 1.key = Alias 2.key  //Connect first
set Listing=Value, column name=value
[where clause]

The mobile phone number of Zhang Wuji's girlfriend is 1114

#sql99 mode
UPDATE beauty b
INNER JOIN 
boys bo
ON b.boyfriend_id = bo.id
SET  b.phone='1114'
WHERE bo.boyName = 'zhang wuji'

#Error: where and set cannot change order
UPDATE beauty b
INNER JOIN 
boys bo
ON b.boyfriend_id = bo.id
WHERE bo.boyName = 'zhang wuji' 
SET  b.phone='1114'

Instance modification: the number of the boyfriend of the goddess without boyfriend is 2

#Use subquery
UPDATE beauty b
SET boyfriend_id = 2
WHERE boyfriend_id NOT IN(
	SELECT DISTINCT id
	FROM boys
)
#Use connection
UPDATE boys bo
RIGHT JOIN beauty b
ON b.boyfriend_id = bo.id
SET b.boyfriend_id = 2
WHERE bo.id IS NULL

delete statement

The smallest unit of deletion is row, so the deletion operation needs to find the location of the deleted row, which requires the table name and filter criteria in the sql statement. Deletion statements are divided into single table deletion and multi table deletion

Single table delete

The basic syntax is as follows. If there is no where clause, the data of the whole table will be deleted.

delete from Table name [where clause]

Single table delete (truncate)

The basic syntax is as follows. Note that truncate cannot use the where clause.

truncate table Table name

Compare truncate and delete

• delete can use the where clause, but truncate cannot.
• truncate deletion is more efficient.
• If there is a self growing column in the table, the self growing column starts from 1 after true deletion, and the self growing column starts from the breakpoint after delete. This indicates that mysql saves the delete field information.
• truncate does not return a value. delete returns the number of deleted rows.
• truncate cannot roll back, delete can. This echoes the third point, that is, mysql saves the delete scene.

Multi table deletion (cascade deletion)

Multi table deletion refers to connecting multiple tables to form an intermediate table and deleting the data of the intermediate table. It is divided into sql92 and sql99, and its syntax is as follows

#sql92
delete Alias 1, alias 2      //If the data of a table is deleted, only one table alias will be written. Otherwise, write two
from Table 1 aliases, table 2 aliases
where Connection conditions
and  Screening conditions

#sql99
delete Alias 1, alias 2  //If the data of a table is deleted, only one table alias will be written. Otherwise, write two
from Table 1 alias 1
inner|left |right join Table name 2 alias 2
on Connection conditions
where Screening conditions