MySQL DML (data manipulation language)

The notebook

    SQL Language is divided into 4 Categories: ： DQL（ Data query language ）、 DDL（ Data definition language ）、 DML（ Data manipulation language ）、 DCL （ Data control language ）      This article introduces one of them DML（Data Manipulation Language）, Data manipulation language , The most commonly used addition, deletion, modification and query belong to DML, The operation object is the record in the data table .

One 、 Insert statement

Grammar 1 ：

INSERT INTO  Table name ( Name ,...) VALUES( value 1,...);

There are certain requirements for using this statement ：
     The type of the inserted value should be consistent with or compatible with the type of the column ;
The number and order of fields are not necessarily the same as those in the original table , However, you must ensure that the values and fields correspond one by one ;
Not for NULL The column of must have a value inserted ; The order of the columns can be changed ;
The number of columns and the number of values must be the same ;
You can omit column names , The default is all columns , And the order of the columns is the same as that of the tables .
eg：

INSERT INTO beauty(id,NAME,sex,borndate,phone,photo,boyfriend_id)
VALUES(1,' Liu Yifei ',' Woman ','1987-8-25','12345678910',NULL,2);

Grammar II ：

INSERT INTO  Table name 
SET  Name = value , Name = value ,...

Their differences ：
① Syntax 1 supports inserting multiple lines , But syntax 2 is not supported

INSERT INTO beauty(id,NAME,sex,borndate,phone,photo,boyfriend_id)
VALUES(1,' Liu Yifei ',' Woman ','1987-8-25','12345678910',NULL,2),
(1,' Liu Yifei ',' Woman ','1987-8-25','12345678910',NULL,2),
(1,' Liu Yifei ',' Woman ','1987-8-25','12345678910',NULL,2);

② Syntax 1 supports subqueries , Syntax 2 does not support

INSERT INTO beauty(id,NAME,phone)
SELECT id,boyname,'123456' FROM boys WHERE id<3;

Two 、 Modify the statement

UPDATE  Table name 
SET  Column = The new value , Column = The new value ,...
WHERE  filter 

eg：

UPDATE beauty SET phone='12345678' WHERE NAME LIKE ' The tang dynasty %';

3、 ... and 、 Delete statements

Mode one ：DELETE
1、 Delete single table
DELETE FROM Table name WHERE filter

2、 Delete multiple tables
sql92 grammar ：

DELETE  surface 1 Another name for , surface 2 Another name for 
FROM  surface 1  Alias , surface 2  Alias 
WHERE  Connection condition  AND  filter ; 

sql99 grammar ：

DELETE   surface 1 Another name for , surface 2 Another name for 
FROM  surface 1  Alias 
INNER|LEFT|RIGHT JOIN surface 2  Alias  ON Connection condition 
WHERE  filter ;

Case study ： Delete Zhang Wuji's girlfriend's information

DELETE b
FROM beauty b INNER JOIN boys bo
ON b.`boyfriend_id`=bo.`id`
WHERE bo.`boyName`=' zhang wuji ';

Mode two ：TRUNCATE
grammar ：

TRUNCATE TABLE  Table name ;

The difference between the two ways ：
①DELETE You can add WHERE Conditions ,TRUNCATE Cannot add
②TRUNCATE More efficient
③ Suppose the table to be deleted has a self growing column ,
If you use delect After deleting , Then insert the data , Starting from the value breakpoint of the growth column ,
and TRUNCATE After deleting , Then insert the data , The value of the self growing column starts from 1 Start .
④TRUNCATE Delete has no return value ,delect Delete with return value
⑤TRUNCATE Deletion cannot be rolled back ,delect Delete can be rolled back

notebook