MySQL database -- add, delete and change

Hello, everyone, welcome to meet you. After the last two query summary posts, today I will sort out the addition, modification and deletion of the data table.

Last two Hyperlinks:
Basic chapter portal
Advanced level chapter portal
Add today's finishing this article, together with the addition, deletion, change and check the four kings of heaven!

In fact, the content of this article is quite abstract, because you haven't seen the original table, so the reading process can only be familiar with or run through the code in your mind. If you need the original form, you can chat with me privately. We have provided QR code before, and then we can't review the article.

INSERT statement

General grammar

Insert write record. It can be one record or multiple records

  • Insert into table name (field 1, field 2,...) VALUES (value 1, value 2 ).
  • INSERT INTO table name (field 1, field 2,...) VALUES (value 1, value 2 ), (value 1, value 2 );
-- INSERT INTO
INSERT INTO t_dept (deptno, dname, loc)
VALUES (50,'Technology Department','Beijing'),(60,'Logistics Department','Beijing'),(70,'Security Department','Beijing');

-- Add an employee record to the Technology Department, I just know he's the technology department,I don't know the number of Technology Department
INSERT INTO t_emp
(empno,ename, job,mgr,hiredate,sal,comm,deptno)
VALUES (8001,'Liu Na','SALESMAN',8000,'1988-12-20',2000,NULL,
(SELECT deptno FROM t_dept WHERE dname='Technology Department'));
-- Subquery can only be a single row and single column.

INSERT dialect grammar

Not universal [red]
mysql Dialect: INSERT INTO table name SET field 1 = value 1, field 2 = value 2

IGNORE statement

ignore keyword allows insert to insert only records that do not exist in the database

  • INSERT [IGNORE] INTO table name ;
    Case: 70 already exists (inserted above), now insert another 70 and 80, and remember to delete them after running
-- 70 Already existing,80 No,
INSERT IGNORE INTO t_dept ( deptno,dname,loc) 
values (70,'A','Beijing'),(80,'B','Shanghai');

UPDATE statement

The syntax for modifying records is as follows:
UPDATE [IGNORE] table name
SET field 1 = value 1, field 2 = value 2
[WHERE condition 1 ]- it's a condition modification
[ORDER BY... ]– change by order when written
[LIMIT... ]; – only the first paging record can be modified, such as limit 10, which represents the first ten entries

Cases & Exercises:

-- Case 1: Add one to each employee number and supervisor number
UPDATE t_emp SET empno=empno+1 , mgr=mgr+1
ORDER BY empno DESC;

-- Case 2:Reduce the base salary of the top three monthly employees by 100,use LIMIT complete
UPDATE t_emp
SET sal = sal-100
ORDER BY sal+IFNULL(comm,0) DESC
LIMIT 3;

update table link

  • Syntax: * * UPDATE table 1 JOIN table 2 on condition SET field 1 = value 1, field 2 = value 2 * *
  • Table linked UPDATE statement can modify records of multiple tables
  • Syntax: UPDATE table 1, table 2 set field 1 = value 1, field 2 = value 2 WHERE connection conditions
    Case study:
-- hold ALLEN Transfer to RESEARCH department,Position adjustment to ANALYST
-- e.deptno=d.deptno This sentence is to research Department number,Number assigned to Allen
UPDATE t_emp e  JOIN t_dept d
SET  e.deptno=d.deptno ,e.job='ANALYST' , d.loc="Beijing"
WHERE e.ename="ALLEN" AND d.dname="RESEARCH";


-- Employees whose base salary is lower than the average base salary of the company,Base salary increased by 150 yuan
-- Table link update
UPDATE  t_emp e JOIN (SELECT AVG(sal) AS avg FROM t_emp)t
ON e.sal <t.avg
SET e.sal =e.sal+150;
  • The table link of the update statement can be either internal or external
    UPDATE table 1 [LEFT | RIGHT] JOIN table 2 ON condition SET field 1 = value 1, field 2 = value 2 ;
    Case study:
-- The employee who has no department, or sales The employees whose department salary is less than 2000 yuan are all transferred to 20 departments
UPDATE t_emp e LEFT JOIN t_dept d ON e.deptno=d.deptno
SET  e.deptno = 20
WHERE e.deptno IS NULL  OR ( d.dname="SALES" AND e.sal<2000);

DELETE delete

Delete [ignore] from table name
**[WHERE condition 1, condition 2 * *
[ ORDER BY … ]Delete data after sorting
[LIMIT... ]Pagination

Internal connection

Case study:

-- Delete 10 departments,Employees with more than 20 years of service
DELETE 
FROM t_emp
WHERE deptno=10 AND DATEDIFF(now(),hiredate)/365>=20;

-- Delete 20 departments,Record of highest paid employees
DELETE FROM t_emp
WHERE deptno=20
ORDER BY sal+IFNULL(comm,0)DESC
LIMIT 1;

-- delete SALES Department and all employee records of the Department,One delete Statement deletes records from two tables 
DELETE e,d
FROM t_emp e JOIN t_dept d ON e.deptno=d.deptno
WHERE d.dname='SALES';

-- Delete employee record of average base salary of each department
DELETE e
FROM t_emp e JOIN 
(SELECT deptno,AVG(sal) avgg
FROM t_emp GROUP BY deptno) t
ON e.deptno=t.deptno AND e.sal < t.avgg;

-- Delete staff KING Employee records with his direct reports,Implement with table link.
DELETE e
FROM t_emp  e JOIN
(SELECT  empno FROM t_emp WHERE ename='king') t
ON e.mgr=t.empno  OR e.empno=t.empno;

External connection

DELETE Table 1 FROM table 1 [left | right] join table 2 ON condition

Case study:

-- delete SALES Departmental staff,And employees without departments.
DELETE e
FROM t_emp e LEFT join t_dept d ON e.deptno=d.deptno
WHERE d.dname='SALES' OR e.deptno IS NULL;

TRUNCATE TABLE

Quickly delete all records in data table

  • DELETE statement is to DELETE records under the transaction mechanism. Before deleting records, first save the records to be deleted to the log file, and then DELETE the records
  • Truncate table table name;
-- TRUNCATE  TABLE Employee list
TRUNCATE  TABLE t_emp;

This article learned:

  • INSERT syntax
  • UPDATE syntax
  • DELETE syntax
  • TRUNCATE TABLE syntax
  • Master the table link syntax in data update and deletion

Although the road is endless and faraway, I still want to pursue the truth in the world.

Published 40 original articles, won praise 5, visited 1024
Private letter follow

Tags: MySQL Database supervisor less

Posted on Tue, 14 Jan 2020 04:59:49 -0500 by azeem123456