MySQL learning (four)

Article Directory

One, INSERT statement

The INSER statement can write records to the data table, which can be one record or multiple records.

INSERT INTO 表名(字段1,字段2,...)
VALUES(值1, 值2,……);

Write multiple records:
a parenthesis is a record

INSERT INTO 表名(字段1,字段2,...)
VALUES(值1, 值2,……),(值1, 值2,……);

For example:

Insert picture description here


View the content of the data table:

Insert picture description here


Example: add an employee record to the technical department

INSERT INTO t_emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
VALUES(8001,"小明","SALESMAN",8000,"1988-12-20",2000,null,
(SELECT deptno FROM t_dept WHERE dname="技术部"))

The field returned by the subquery of this SQL statement must be one, the value must also be one, and the subquery must have a single row and single column.
There is also a dialect syntax: (sentences that can only be executed in a certain database, not universal)

INSERT INTO 表名 SET 字段1=值1,字段2=值2,......;

IGNORE keywords

The IGNORE keyword will make INSERT only insert records that did not exist in the database before

INSERT [IGNORE] INTO 表名 ......;

Example: deptno=40 is the primary key, which is already occupied. If IGNORE is not added, an error will be reported as follows: SQL 错误 [1062] [23000]: Duplicate entry '40' for key 'PRIMARY';After IGNORE is added, no error will be reported:

INSERT IGNORE INTO t_dept(deptno,dname,loc)
VALUES(40,"技术部","北京")
Insert picture description here

Subquery in INSERT statement

The INSERT statement can contain a sub-query statement, which means that the content of the sub-query is written to the data table.
For example: copy department records with more than 5 people to the new department table

CREATE TABLE t_dept_new LIKE t_dept;
INSERT INTO t_dept_new(deptno,dname,loc)
(SELECT d.deptno,d.dname,d.loc FROM t_dept d JOIN
(SELECT deptno FROM t_emp GROUP BY deptno HAVING COUNT(*)>=5) temp
ON d.deptno = temp.deptno);

Two, UPDATE statement

The UPDATE statement is used to modify the records in the data table.

UPDATE [IGNORE] 表名 SET 字段1=值1,字段2=值2,......
[WHERE 条件1,条件2......]
[ORDER BY......]
[LIMIT ......];

Example: +1 the number of each employee and the number of the boss, and use the ORDER BY clause to complete.

UPDATE t_emp SET empno=empno+1,mgr=mgr+1
ORDER BY empno DESC;

Example: Reduce the basic salary of the top three employees with monthly income by 100 yuan, complete with the LIMIT clause

UPDATE t_emp
SET sal=sal-100
ORDER BY sal+IFNULL(comm,0) DESC
LIMIT 3;

Table join of UPDATE statement

The efficiency of subquery with WHERE clause is very low, and UPDATE statement can be modified by way of table join.

UPDATE 表1 JOIN 表2 ON 条件
SET 字段1=值1,字段2=值2,......;

The UPDATE statement of the table connection can modify the records of multiple tables.
There are also the following forms:

UPDATE 表1,表2
SET 字段1=值1,字段2=值2,......
WHERE 连接条件;

Example: Transfer ALLEN to the RESEARCH department, and adjust the position to ANALYST.

UPDATE t_emp e JOIN t_dept d
SET e.deptno=d.deptno,e.job="ANALYST",d.loc="北京"
WHERE e.name="ALLEN" AND d.name="RESEARCH"

You cannot write connection conditions when connecting. If you write connection conditions, you are looking for ALLEN, and the department is an employee of RESEARCH, which is different from our purpose.
Example: Increase the basic salary of an employee whose basic salary is lower than the company's average basic salary by 150 yuan.

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 UPDATE table connection can be either an inner connection or an outer connection:

UPDATE 表1 [LEFT|RIGHT] JOIN 表2 ON 条件
SET 字段1=值1,字段2=值2,......;

Example: Transfer employees who do not have a department, or employees who have a basic salary of less than RMB 2,000 in the SALES department, to department 20.

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);

Three, DELETE statement

DELETE statement is used to delete records

DELETE [IGNORE] FROM 表名
[WHERE 条件1, 条件2, ......]
[ORDER BY ......]
[LIMIT ......];

Example: Delete the records of employees with more than 20 years of service in 10 departments.

DELETE FROM t_emp
WHERE deptno=10 AND DATEDIFF(NOW(),hiredate)/365>=20;

Example: Delete the record of the employee with the highest salary in the 20 departments.

DELETE FROM t_emp
WHERE deptno=20
ORDER BY sal+IFNULL(comm,0) DESC
LIMIT 1;

Table join of DELETE statement

Because the efficiency of correlated sub-queries is very low, the DELETE statement is modified by means of table joins.

DELETE 表1,......FROM 表1 JOIN 表2 ON 条件
[WHERE 条件1, 条件2, ......]
[ORDER BY ......]
[LIMIT ......];

Example: Delete the SALES department and all employee records in the department.

DELETE e,d
FROM t_emp e JOIN t_dept d ON e.deptno=d.deptno
WHERE d.dname="SALES";

Example: Delete each employee record whose salary is lower than the average base salary of the department.
First, group by department, query the number of each department and the average basic salary corresponding to that department, and use this result set as a table to join the employee table.

DELETE e
FROM t_emp e JOIN (SELECT deptno.AVG(sal) AS sal FROM t_emp GROUP BY deptno) t
ON e.deptno=t.deptno AND e.sal<t.sal;

Example: Deleting the employee records of employee KING and his direct subordinates, realized by table connection.

DELETE e,t
FROM t_emp e JOIN (SELECT empno FROM t_emp WHERE ename="KING") t
ON e.mgr=t.empno OR e.empno=t.empno;

The DELETE table connection can be either an inner connection or an outer connection:

DELETE 表1,...... FROM 表1 [LEFT|RIGHT] JOIN 表2 
ON 条件......;

Example: Delete employees in the SALES department and employees without a department.

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;

Quickly delete all records in the data table

The DELETE statement deletes a record under the transaction mechanism. Before deleting a record, save the deleted record in the log file and then delete it.

TRUNCATE statement

The TRUNCATE statement deletes records outside of the transaction mechanism, much faster than the DELETE statement.

TRUNCATE TABLE 表名;

Four, aggregate function

Aggregate functions can sum data, find maximum and minimum values, find average values, and so on.

1. SUM function

The SUM function is used for summation and can only be used for numeric types. The statistical result of character type is 0, and the statistical result of date type is the addition of milliseconds.

SELECT SUM(ename) FROM t_emp;
SELECT SUM(sal) FROM t_emp
WHERE deptno IN (10,20);

SUM function summation will exclude NULL values

2.MAX function

The MAX function is used to obtain the maximum value of a non-empty value.

SELECT MAX(comm) FROM t_emp;

Example 1: Query the employees with the highest monthly income in departments 10 and 20.

SELECT MAX(sal+IFNULL(comm,0)) FROM t_emp
WHERE deptno IN(10,20);

Example 2: Query the longest number of characters in the employee's name

SELECT MAX(LENGTH(ename)) FROM t_emp;

3.MIN function

The MIN function is used to obtain the minimum value of a non-empty value

SELECT MIN(empno) FROM t_emp;
SELECT MIN(hiredate) FROM t_emp;

4.AVG function

The AVG function is used to obtain the average value of non-empty values, and the result of non-digital statistics is 0

SELECT AVG(sal+IFNULL(comm,0)) FROM t_emp;

5. COUNT function

COUNT (*) is used to obtain the number of records containing null values, and COUNT (column name) is used to obtain the number of records containing non-null values

SELECT COUNT(*) FROM t_emp;
SELECT COUNT(comm) FROM t_emp;

Example 1: Query the number of employees in departments 10 and 20 whose basic salary exceeds RMB 2,000 and who have worked for more than 15 years.

SELECT COUNT(*) FROM t_emp
WHERE deptno IN(10,20) AND sal >= 2000
AND DETEDIFF(NOW(),hiredate)/365>15;

Example 2: Query the number of employees who joined the company after 1985, and the number of employees whose basic salary exceeds the company's average basic salary.

SELECT COUNT(*) FROM t_emp
WHERE hiredate>="1985-01-01" 
AND sal > AVG(sal);

The problem with this SQL statement: The WHERE clause is used for filtering. After the WHERE clause is executed, determine which data is retained for summary statistics. If the WHERE clause is not executed, then the COUNT in the SELECT clause is not counted. significance. The AVG aggregate function is used in the WHERE clause, because the WHERE clause does not delimit the data range, and then the condition is used to judge sal> AVG(sal), and AVG is not sure which range is the average value.
Note: Aggregate functions cannot appear in the WHERE clause.

6. Group query

The GROUP BY clause divides a data set into several small areas through certain rules, and then summarizes each small area.
Calculate the average salary of each department:

SELECT deptno,AVG(sal) FROM t_emp
GROUP BY deptno;

Group by level

The database supports multi-column grouping conditions, which are grouped level by level during execution.
Example: Query the number and average basic salary of each position in each department

SELECT deptno,job,COUNT(*),AVG(sal)
FROM t_emp GROUP BY deptno,job
ORDER BY deptno;

If the query statement contains a GROUP BY clause, the content of the SELECT clause must comply with the regulations: the SELECT clause can include aggregate functions or grouping columns of the GROUP BY clause, and the rest cannot appear in the SELECT clause.

SELECT deptno,COUNT(*),AVG(sal)
FROM t_emp GROUP BY deptno;

There will be a problem with the following statement:

SELECT deptno,COUNT(*),AVG(sal),sal
FROM t_emp GROUP BY deptno;

Do the summary calculation again on the grouped result set

SELECT deptno,COUNT(*),AVG(sal),MAX(sal),MIN(sal)
FROM t_emp GROUP BY deptno WITH ROLLUP;

7. GROUP_CONCAT function

You can concatenate a field in the group query into a string.
Example: Query the number of employees whose basic salary exceeds RMB 2,000 and the names of employees in each department.

SELECT deptno,GROUP_CONCAT(ename),COUNT(*)
FROM t_emp WHERE sal>=2000
GROUP BY deptno;

8. HAVING statement

Example: Query the department numbers of more than 2 employees who have joined the company after 1982 in each department.

SELECT deptno FROM t_emp 
WHERE hiredate>="1982-01-01"
GROUP BY deptno HAVING COUNT(*)>=2;
ORDER BY deptno ASC;

If the GROUP BY deptno HAVING COUNT(*)>=2 AND sal>=AVG(sal);syntax is wrong in the above code .
But if it is written in GROUP BY deptno HAVING COUNT(*)>=2 AND AVG(sal)>=2000;grammatically correct.
The special usage of the HAVING clause: group
according to the number 1, MySQL will group according to the columns in the SELECT clause, and the HAVING clause can also be used normally.

SELECT deptno,COUNT(*)
FROM t_emp GROUP BY 1;
SELECT deptno,COUNT(*)
FROM t_emp GROUP BY 1 HAVING deptno IN (10,20);

Here HAVING can also be replaced by WHERE.
Generally use the WHERE clause, because the execution order of the WHERE clause precedes the GROUP BY, and the execution order of HAVING is after the GROUP BY, so the efficiency of using the WHERE clause is faster.

Five, table join query

To extract data from multiple tables, the associated conditions must be specified. If the association conditions are not defined, an unconditional connection will occur, and the data of the two tables will be cross-connected, resulting in a Cartesian product. The following command:

SELECT empno,ename,dname
FROM t_emp JOIN t_dept;

As a result, 56 records will be found.
There will be no Cartesian product in the table join statement that specifies the join condition.

SELECT e.empno,e.ename,d.name
FROM t_emp e JOIN t_dept d
ON e.deptno=d.deptno;

The result is 15.

1. Internal connection

Only the records that meet the connection conditions are kept in the result set

SELECT ...... FROM 表1
[INNER] JOIN 表2 ON 条件
[INNER] JOIN 表3 ON 条件
......;

Multiple grammatical forms of internal connection:
1) SELECT ...... FROM 表1 [INNER] JOIN 表2 ON 连接条件;
2) SELECT ...... FROM 表1 [INNER] JOIN 表2 WHERE 连接条件;
3) SELECT ...... FROM 表1,表2 WHERE 连接条件;
Example: Query the department information of each employee.

SELECT e.empno,e.ename,d.dname
FROM t_emp e JOIN t_dept d ON e.deptno=d.deptno;
SELECT e.empno,e.ename,d.dname
FROM t_emp e JOIN t_dept d WHERE e.deptno=d.deptno;
SELECT e.empno,e.ename,d.dname
FROM t_emp e, t_dept d WHERE e.deptno=d.deptno;

The operation results of the above three statements are the same.
Example: Query the job number, name, department name, basic salary, position, and salary grade of each employee.
Analysis: Extract field information from 3 data tables. The department name comes from the department table, the salary grade comes from the salary grade table, and the other fields come from the employee table. Looking at the data table, it is found that there is no same field in the employee table and the salary grade table

SELECT e.empno,e.ename,d.dname,e.sal,e.job,s.grade
FROM t_emp e JOIN t_dept d ON e.deptno=d.deptno
JOIN t_salgrade s ON e.sal BETWEEN s.losal AND s.hisal;

Note: The data table of the inner connection does not have to have the same-named fields, as long as the fields conform to the logical relationship.
Example: Query who has the employees in the same department as SCOTT.

SELECT ename
FROM t_emp
WHERE deptno=(SELECT deptno FROM t_emp WHERE ename="SCOTT")
AND ename!="SCOTT"; 

The efficiency of the subquery is very low, and it is replaced with a table join.

SELECT ename
FROM t_emp e1 JOIN t_emp e2 ON e1.deptno=e2.deptno
WHERE e1.ename="SCOTT" AND e2.ename!="SCOTT"; 

Note: The same data table can also be connected to the table.
Example: Query information about employees whose basic salary exceeds the company's average basic salary.

SELECT e2.empno,e2.ename,e2.sal
FROM t_emp e1 JOIN t_emp e2 ON e2.sal>=AVG(e1.sal);

The SQL statement has a syntax error. The ON clause has the same function as the WHERE clause, and the AVG aggregate function appears in the ON clause.

SELECT e.empno,e.ename,e.sal
FROM t_emp e JOIN (SELECT AVG(sal) avg FROM t_emp) t 
ON e.sal>=t.avg;

Example: Query the number of people in the RESEARCH department, the highest base salary, the lowest base salary, the average base salary, and the average length of service.

SELECT COUNT(*),MAX(e.sal),MIN(e.sal),AVG(e.sal),AVG(DATEDIFF(NOW(),e.hiredate)/365)
FROM t_emp e JOIN t_dept d ON e.deptno=d.deptno
WHERE d.dname="RESEARCH";

Example: Query the maximum wage, minimum wage, average wage, maximum wage level, and minimum wage level of a certain occupation.

SELECT e.job,MAX(e.sal+IFNULL(e.comm,0)),MIN(e.sal+IFNULL(e.comm,0)),AVG(e.sal+IFNULL(e.comm,0)),MAX(e.grade),MIN(e.grade)
FROM t_emp e JOIN t_salgrade s
ON (e.sal+IFNULL(comm,0)) BETWEEN s.losal AND s.hisal
GROUP BY e.job;

Example: Query the information of each employee whose basic salary exceeds the average basic salary of the department.

SELECT e.empno,e.ename,e.sal
FROM t_emp JOIN (SELECT deptno,AVG(sal) AS avg FROM t_emp GROUP BY deptno) t
ON e.deptno=t.deptno AND e.sal>=t.avg;

2. External connection

Why use outer joins?
If Chen Hao is a temporary employee and does not have a fixed department establishment, then if you query each employee and his department name, Chen Hao will be missed if you use the inner join, so the grammar of the outer join must be introduced to solve this problem.
Regardless of whether the outer join does not meet the join conditions, the record must be kept in the result set.

SELECT e.empno,e.ename,d.dname
FROM t_emp e
LEFT JOIN t_dept d ON e.deptno=d.deptno;

Left connection and right connection

The left outer join is to keep all the records of the left table and connect with the right table. If the right table has records that meet the conditions, it is connected with the left table. If the right table does not have a record that meets the conditions, use NULL to join the left table. The same is true for the right outer join.

SELECT e.empno,e.ename,d.dname
FROM t_dept d
RIGHT JOIN t_emp e ON e.deptno=d.deptno;

Example 1: Query the name of each department and the number of people in the department.

SELECT d.dname,COUNT(e.deptno)
FROM t_dept d LEFT JOIN t_emp e
ON d.deptno=e.deptno
GROUP BY d.deptno;

Example 2: Query the name of each department and the number of people in the department. If there are no employees in the department, replace the department name with NULL.

(SELECT d.dname,COUNT(e.deptno)
FROM t_dept d LEFT JOIN t_emp e
ON d.deptno=e.deptno
GROUP BY d.deptno)
UNION
(SELECT d.dname,COUNT(*)
FROM t_dept d RIGHT JOIN t_emp e
ON d.deptno=e.deptno
GROUP BY d.deptno);

UNION keyword

The result sets of multiple query statements can be combined.

(查询语句) UNION (查询语句) UNION (查询语句) ……

Example 3: Query the number, name, department, monthly salary, salary grade, length of service, boss number, boss name, and boss department of each employee.

SELECT e.empno,e.ename,d.dname,e.sal+IFNULL(e.comm,0),s.grade,FLOOR(DATEDIFF(NOW(),e.hiredate)/365),t.empno AS mgrno,t.ename AS mname,t.dname AS mdname
FROM t_emp e LEFT JOIN t_dept d ON e.deptno=d.deptno
LEFT JOIN t_salgrade s
ON e.sal BETWEEN s.losal AND s.hisal
LEFT JOIN (SELECT e1.empno,e1.ename,d1.dname
FROM t_emp e1 JOIN t_dept d1
ON e1.deptno=d1.deptno) t ON e.mgr=t.empno;

Note: The inner join only keeps the records that meet the conditions, so the effect of the query conditions written in the ON and WHERE clauses is the same. However, in the outer join, the conditions are written in the WHERE clause, and records that do not meet the conditions will be filtered out instead of being retained.

SELECT e.ename,d.dname,d.deptno
FROM t_emp e
LEFT JOIN t_dept d ON e.deptno=d.deptno
AND e.deptno=10;
SELECT e.ename,d.dname,d.deptno
FROM t_emp e
LEFT JOIN t_dept d ON e.deptno=d.deptno
WHERE e.deptno=10;

Six, subqueries

A subquery is a statement that is nested within a query.
Example: Query information about employees whose basic salary exceeds the company's average basic salary.

SELECT empno,ename,sal FROM t_emp
WHERE sal>=(SELECT AVG(sal) FROM t_emp);

Classification of subqueries

Sub-query can be written in three places: WHERE clause, FROM clause, SELECT clause, but only the FROM sub-sentence query is the most desirable.

WHERE subquery

The easiest to understand and the least efficient.
Example: Query information about employees whose basic salary exceeds the company's average basic salary.

SELECT empno,ename,sal FROM t_emp
WHERE sal>=(SELECT AVG(sal) FROM t_emp);

Comparing each record requires re-executing the subquery.

FROM subquery

This kind of subquery will only be executed once, so the query efficiency is very high.

SELECT e.empno,e.ename,e.sal,t.avg
FROM t_emp e JOIN (SELECT deptno,AVG(sal) AS avg FROM t_emp GROUP BY deptno) t
ON e.deptno=t.deptno AND e.sal>=t.avg;

SELECT subquery

This kind of subquery must be executed every time a record is output, and the query efficiency is very low.
The result set of a single-row subquery has only one record, and the result set of a multi-row subquery has multiple rows.
Multi-line subqueries can only appear in the WHERE clause and FROM clause.
Example: Find the colleagues of FORD and MARTIN.

SELECT ename
FROM t_emp
WHERE deptno IN(SELECT deptno FROM t_emp WHERE ename IN("FORD","MARTIN"))
AND ename NOT IN("FORD","MARTIN");

Multi-line subquery in WHERE clause

In the WHERE clause, you can use the IN, ALL, ANY, and EXISTS keywords to process the conditional judgment of the multi-row expression result set.
Example: Query information about employees whose base salary is higher than that of FORD and MARTIN.

SELECT ename FROM t_emp
WHERE sal>ALL
(SELECT sal FROM t_emp WHERE ename IN("FORD","MARTIN"));

EXISTS keyword

The EXISTS keyword puts the original conditional judgment outside the subquery in the subquery.

SELECT …… FROM 表名 WHERE [NOT] EXISTS (子查询)

Example: Query the information of employees whose salary grade is 3 or 4.

SELECT empno,ename FROM t_emp
WHERE EXISTS(SELECT * FROM t_salgrade WHERE sal BETWEEN losal AND hisal
AND grade IN(3,4));