DML language

Data operation language:

Inserting: insert ing
Modify: update
Delete: delete

1, Insert statement

Mode 1:
Syntax:
insert into table name (column name...)
Values (value 1,...);
Features: it supports inserting multiple rows and querying subsets

SELECT * FROM beauty; 

1. The type of the inserted value should be consistent or compatible with the type of the column

INSERT INTO beauty (id, NAME, sex, borndate, phone , photo, boyfriend_id)
VALUES(13, 'Tang Yixin', 'female', '1990-4-23', '1898888888' , NULL,2) ;

2. Columns that cannot be nul1 must be inserted with values. How can I insert values for nul1 columns?

INSERT INTO beauty(id, NAME, sex, borndate, phone, photo, boyfriend_id)
VALUES(13, 'Tang Yixin', 'female', '1990-4-23', '1898888888' ,NULL,2) ;

INSERT INTO beauty (id, NAME, sex, borndate, phone, boyfriend_id)
VALUES(14, 'Venus', 'female', '1990-4-23', '13888888881',9) ;

3. The sequence of columns can be changed and must correspond one by one

INSERT INTO beauty (NAME, sex, id, phone)
VALUES('Jiang Xin','female',16, '110') ;

4. The number of columns and values must be the same. If they are inconsistent, an error will be reported

INSERT INTO beauty(NAME, sex, id, phone)
VALUES('Guan Xiaotong', 'female',17, '110') ;

5. You can omit the column name, default to all columns, and the column order is the same as that in the table

INSERT INTO beauty
VALUES(18, 'Fei Zhang', 'male',NULL, '119' , NULL, NULL) ;

Mode 2:

Syntax:
insert into table name
Value = column name

INSERT INTO beauty
SET id=19, NAME='Liu Tao' , phone='999';

2, Modify statement

1. Record of modification sheet
Syntax:
update table name
set column = new value, column = new value
where screening conditions;

2. Modify multi table records [supplement]

Syntax:
sq192 syntax:
update table 1 alias, table 2 alias
set column = value
where connection conditions
and screening conditions;

sq199 syntax:
update table 1 alias
Inner / left / right join table 2 aliases
on connection condition
set column = value
where screening conditions;

*/

1. Record of modification sheet
#Case 1: modify the telephone number of the goddess surnamed Liu in the beauty table to 18666668888

UPDATE beauty SET phone = '18666668888'
WHERE NAME like 'Liu%';

#Case 2: modify the name with id 2 in the boys table as Zhang Fei, and the charm value is 10

UPDATE boys SET boyname='Fei Zhang' ,usercp=10
WHERE id=2;

2. Modify multi table records

#Case 1: modify the userCP of Tang Yixin's boyfriend to 100

UPDATE beauty 
inner join boys
on beauty.boyfriend_id = boys.id
SET userCP = 100
where name = 'Tang Yixin';

#Case 2: modify the boyfriend number of Goddess without boyfriend to be No. 2

UPDATE boys bo
RIGHT JOIN beauty b ON bo.id=b.boyfriend_id
SET b. boyfriend_id=2
WHERE bo.id IS NULL;
SELECT * FROM boys;

3, Delete statement

/*
Method 1: delete
Syntax:
1. Deletion of single table [★]
delete from table name where filter criteria
2. Deletion of multiple tables [supplement]
sq192 syntax:
delete alias of Table 1 / alias of Table 2
from table 1 aliases, table 2 aliases
where connection conditions
and screening conditions;
sq199 syntax:
delete alias of Table 1 / alias of Table 2
from table 1 alias
inner/left/right join table 2 alias on connection conditions
where screening conditions;

Method 2: truncate deletes all contents of the entire table, which is equivalent to emptying
Syntax: truncate table name;

Comparison between delete and truncate

1. where condition can be added to delete but not truncate
2. truncate deletion, high efficiency, one loss
3. If there is a self growing column in the table to be deleted,
If you use delete to delete and then insert data, the value of the growing column starts from the breakpoint (which can be understood as sequence number, where to delete and where to add)
After truncate is deleted, the data is inserted again, and the value of the growth column starts from 1. (add from scratch)
4. There is no return value for truncare deletion, and there is a return value for delete deletion (return the number of deleted rows)
5. truncate deletion cannot be rolled back. delete deletion can be rolled back

*/

Method 1: delete
1. Delete single table
#Delete the goddess information with id=17

DELETE FROM beauty 
WHERE
    id = 17;

2. Delete multiple tables
#Delete the goddess information with id=19 in Zhang Fei's girlfriend

delete beauty from boys inner join beauty on beauty.boyfriend_id = boys.id
where beauty.id = 19;

3.TEST

#1. Run the following script to create the table my_ employees

CREATE TABLE my_employees (
			Id INT(10) ,
			First_name VARCHAR(10) ,
			Last_name VARCHAR(10),
			Userid VARCHAR(10),
			Salary DOUBLE (10,2)
);

CREATE TABLE users (
			id INT,
			userid VARCHAR(10) ,
			department_id INT
);

#2. Display table_ Structure of employees

DESC my_employees;

#3. Report to my_ Insert the following data into the employees table
/*
ID FIRST NAME LAST NAME USERID SALARY
1 patel Ralph Rpatel 895
2 Dancs Betty Bdancs 860
3 BirI Ben Bbiri 1100
4 Newman Chad Cnewman 750
5 Ropeburn Audrey Aropebur 1550
*/

insert into my_employees
value(1,'patel','Ralph','Rpatel','895'),
	(2,'Dancs','Betty','Bdancs','860'),
	(3,'BirI','Ben','Bbiri','1100'),
	(4,'Newman','Chad','Cnewman','750'),
	(5,'Ropeburn','Audrey','Aropebur','1550');

#4. Insert data into the users table
/*
1 Rpatel 10
2 Bdancs 10
3 Bbiri 20
4 Cnewman 30
5 Aropebur 40
*/

insert into users
value(1,'Rpatel',10),
	(2,'Bdancs',10),
	(3,'Bbiri',20),
	(4,'Cnewman',30),
	(5,'Aropebur',40);

#5. Put the last of employee 3_ Change the name to "drelxer"“

UPDATE my_employees SET last_name='drelxer' WHERE id = 3;

#6. Revise the salary of all employees whose salary is less than 900 to 1000 I

UPDATE my_employees  SET SALARY = 1000
WHERE SALARY <900; 

#7. The users table with userid as Bbiri and my_ All records in the employees table are deleted

DELETE users,my_employees from users inner join my_employees on users.userid = my_employees.Userid
where users.userid = 'Bbiri';

#8. Delete all data

DELETE from  users;
DELETE from  my_employees;

#9. Check the corrections made

select * from users;
select * from my_employees;

#10. Empty the table_ employees

TRUNCATE  TABLE my_employees;