Related articles
Click to view the MySQL series

1, Insert (insert)

Mode 1

Syntax:

insert into table name (field name,...) values (value,...);

characteristic:

1. The type of the value and the type of the field are required to be consistent or compatible

2. The number and order of the fields are not necessarily the same as the number and order of the fields in the original table, but the values and fields must correspond one by one

3. If there are nullable fields in the table, please note that you can insert the null value in the following two ways: ① omit the field and the value; ② write the field, and use null for the value

4. The number of fields and values must be the same

5. Field names can be omitted. All columns are default

Case:

#1. The type of inserted value should be consistent with or compatible with the type of column
INSERT INTO beauty(id,NAME,sex,borndate,phone,photo,boyfriend_id)
VALUES(13,'Tang Yixin','female','1990-4-23','1898888888',NULL,2);

#2. Non nullable columns must have values inserted. How can nullable columns insert values?
#Mode 1:
INSERT INTO beauty(id,NAME,sex,borndate,phone,photo,boyfriend_id)
VALUES(13,'Tang Yixin','female','1990-4-23','1898888888',NULL,2);

#Mode 2:

INSERT INTO beauty(id,NAME,sex,phone)
VALUES(15,'Nazar','female','1388888888');


#3. Whether the order of columns can be changed
INSERT INTO beauty(NAME,sex,id,phone)
VALUES('Jiang Xin','female',16,'110');


#4. The number of columns and the number of values must be the same
INSERT INTO beauty(NAME,sex,id,phone)
VALUES('Guan Xiaotong','female',17,'110');

#5. You can omit the column name. By default, all columns are in the same order as the columns in the table
INSERT INTO beauty
VALUES(18,'Zhang Fei','male',NULL,'119',NULL,NULL);

Mode 2

Syntax:

insert into table name set field = value, field = value;

The difference between the two methods:

1. Method 1 supports inserting multiple rows at a time. The syntax is as follows: insert into table name [(field name,...) )]Values (values ), (value ),… ;

INSERT INTO beauty
VALUES(23,'Tang Yixin 1','female','1990-4-23','1898888888',NULL,2)
,(24,'Tang Yixin 2','female','1990-4-23','1898888888',NULL,2)
,(25,'Tang Yixin 3','female','1990-4-23','1898888888',NULL,2);

1. Method 1 supports sub query, the syntax is as follows: insert into table name query statement;

INSERT INTO beauty(id,NAME,phone)
SELECT 26,'Song Qian','11809866';

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

2, Modify (update)

Modify the record of single table ★

Syntax: update table name set field = value, field = value [where filter criteria];

Modify multi table records [supplement]

Syntax:

update table 1 alias 
left|right|inner join table 2 alias 
on connection conditions  
set field = value, field = value 
[where screening criteria];

Case:
Modification list record

#Case 1: modify the phone number of the goddess named Tang in the beauty table to 1389988899
UPDATE beauty SET phone = '13899888899'
WHERE NAME LIKE 'Tang Dynasty%';

Modify multi table record

#Case 1: modify Zhang Wuji's girlfriend's mobile number to 114
UPDATE boys bo #4
INNER JOIN beauty b ON bo.`id`=b.`boyfriend_id`#1
SET b.`phone`='119',bo.`userCP`=1000 #3
WHERE bo.`boyName`='zhang wuji';#2

3, Delete

Method 1: use delete

1) Delete single table record ★

Syntax: delete from table name [where filter criteria] and [limit entries]

2) Cascade deletion [supplement]

Syntax:

delete alias 1, alias 2 from table 1 
Inner left right join table 2 alias 
on connection conditions
 [where screening criteria]

3) Cases

• Delete single table

#Case: delete the goddess information whose mobile number ends with 9
DELETE FROM beauty WHERE phone LIKE '%9';
SELECT * FROM beauty;

• Deletion of multiple tables

#Case: 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 2: use truncate

Syntax: truncate table table name

The difference between the two ways