Analysis of the usage of process control functions/statistical functions/grouping queries in MySql

#5. Process control function #1. if function: the effect of if elseIF (conditional expression, return 1 if true, return 2 if false)
#Same as Java ternary operation SELECT IF(10>5,'big','small'); 

SELECT last_name,commission_pct,IF(commission_pct IS NULL,'No bonus','Yes bonus') AS remarks FROM employees; 

#2. Use of CASE function 1: Effect of swirch case [equal value judgment]
Recap: switch(variable or expression) {
case constant 1: statement 1; break;
...
default: statement n; break;
}
In MySQL, case is the field, expression or variable to be judged when constant 1 then the value to be displayed 1 or statement 1; [semicolons are required for statements, but not for values]
when constant 2 then value 2 to be displayed or statement 2;
...
slse value n or statement n to be displayed; [default value]
end
Case is equivalent to an expression after SELECT. No statement can be placed after it, only values.
In subsequent studies, stored procedures and functions can be used independently, without SLECT, and with statements.
First, let's look at an example of how to use the expression: Query employee salaries. If department number = 30, the displayed salary is 1.1 times. If department number = 40, the displayed salary is 1.2 times. If department number = 50, the displayed salary is 1.3 times. For other departments, the displayed salary is the original salary.

SELECT salary original salary, department_id,
CASE department_id
WHEN 30 THEN salary*1.1
WHEN 40 THEN salary*1.2
WHEN 50 THEN salary*1.3
ELSE salary
END AS newSalary FROM employees; 

#2.Use of CASE function 2: Multiple IF [interval judgment]
Review Java: if (condition 1) {
Statement 1;
}else if(condition 2){
Statement 2;
}...
else{
statement n;
}

In mysql:
case
when condition 1 then value 1 to be displayed or statement [a semicolon should be added after the statement;]
when condition 2 then value 2 to be displayed or statement [a semicolon should be added after the statement;]
...
else value n or statement n to be displayed
end
Example: Query the salary of an employee. If the salary is > 20,000, display level A. If the salary is > 15,000, display level B. If the salary is > 10,000, display level C. Otherwise, display level D. 

#Calculate several types of wages.
SELECT COUNT(DISTINCT salary),
COUNT(salary)
FROM employees; 

#5. Detailed introduction of count function SELECT COUNT(salary) FROM employees; 

#COUNT (count all columns)
SELECT COUNT(*) FROM employees; 

#Generate a column from the rows of the table and each column is 1. Count the total number of 1s. Any constant value can be used in count.
SELECT COUNT(1) FROM employees; 

# Considering efficiency issues:
#Before 5.5, COUNT(*) was the highest in MYISAM, and the counter returned directly. #After 5.5, by default, COUNT() and COUNT(1) in INNODB are almost the same.
 More efficient than COUNT(field). If it is a field, it is necessary to determine whether the field is NULL.

#6. The fields queried together with the grouping function are restricted SELECT AVG(salary),employee_id FROM employees; 

#1. Query the maximum, minimum, average and total salary of company employees.
SELECT MAX(salary),MIN(salary),AVG(salary),SUM(salary)
FROM employees; 

SELECT MAX(salary) AS maximum value, MIN(salary) AS minimum value,
ROUND(AVG(salary)) AS minimum value, SUM(salary) AS total FROM employees; 

#2. Query the difference in days between the maximum and minimum joining time in the employee table.
#DATEDIFF Calculates the number of days. DATEIFF(parameter 1 - parameter 2)
SELECT DATEDIFF(NOW(),('1995-1-1')); 

SELECT DATEDIFF(MAX(hiredate),MIN(hiredate)) AS DIFFRENCE
FROM employees; 

#3. Query the number of employees in department number 90.
SELECT COUNT(*) AS number FROM employees
WHERE department_id=90; 

#Advanced 5. Group query GROUP BY clause syntax.
You can use the GROUP BY clause to divide the data in a table into several groups.
grammar:
SELECT grouping function, column (required to appear after GROUP BY)
FROM table [WHERE filter condition]
GROP BY grouped list [ORDER BY clause]
Notice:
  The query list must be special, requiring the grouping function and the field characteristics that appear after GROUP BY:
  1. The filtering conditions in group query are divided into two categories 

2. The GROUP BY clause supports single field grouping and multiple field grouping (multiple fields are separated by commas without order requirements)
Also supports expression or function grouping (less commonly used)
3. You can also add sorting (sorting is placed at the end of the entire grouping query) 

#Introduction: Query the average salary of each department and keep two decimal places.
SELECT ROUND(AVG(salary),2) AS average salary FROM employees; 

#Simple group query, add filtering before grouping WHERE
#Case 1: Query the highest salary for each type of work.
SELECT MAX(salary) AS maximum salary, job_id AS job number FROM employees
GROUP BY job_id; 

#Case 2: Query the number of departments at each location.
SELECT COUNT(*) AS total,location_id
FROM departments
GROUP BY location_id; 

#Add filter conditions#Query emails containing the character a, average salary with two decimal places, average salary of each department SELECT email,ROUND(AVG(salary),2),department_id
FROM employees
WHERE email LIKE '%a%'
GROUP BY department_id; 

#Case 2: Query the highest salary of each leader's employees with bonuses SELECT MAX(salary), manager_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY manager_id; 

Add complex filtering, add filtering after grouping HAVING
#Case 1: Query which department has the number of employees > 2
#①Query the number of employees in each departmentSELECT COUNT(*),department_id
FROM employees
GROUP BY department_id; 

#②Based on the result of ①, check which department has more employees than 2
SELECT COUNT(*) AS total, department_id AS department number FROM employees
GROUP BY department_id
HAVING COUNT(*)>2; 

#Case 2. Query the job number and maximum salary of employees with bonuses in each job type whose maximum salary is > 12000.
#①Query the highest salary of employees who receive bonuses in each job type.
#Put the original table that can be filtered after FROM and use WHERE.

SELECT job_id AS employee number, MAX(salary) AS maximum salary FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id; 

#②Continue screening based on the result of ①, the highest salary is >12000.
SELECT job_id AS employee number, MAX(salary) AS maximum salary FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING MAX(salary)>12000; 

#Case 3. Query the leader number of each leader with a minimum salary > 5000 and their minimum salary for leaders with a leader number > 102.
#①Query the minimum salary of each leader with leader number > 102 SELECT MIN(salary) AS minimum salary, manager_id AS leader number FROM employees
WHERE manager_id > 102
GROUP BY manager_id; 

#②Minimum wage is greater than 5000.
SELECT MIN(salary) AS minimum salary, manager_id AS leader number FROM employees
WHERE manager_id > 102
GROUP BY manager_id
HAVING MINIMUM SALARY > 5000; 

#Case: Group by the length of employee names, query the number of employees in each group, and filter out which employees have more than 5 #In MYSQL, aliases are supported after GROUP BY and HAVING, but not WHERE. However, ORACLE database GROUP BY and HAVING do not support aliases.
SELECT LENGTH(CONCAT(last_name,first_name)) AS name length,
COUNT(*) AS number FROM employees
GROUP BY name_length HAVING name_length > 5; 

#Group by multiple fields# Example: Query the average salary of employees in each department (keep two decimal places).
SELECT ROUND(AVG(salary),2) AS average salary,department_id AS department,job_id AS job type FROM employees
GROUP BY department, job type; 

#Add sorting#Example: Query the average salary of employees in each department and each job type, and display them by average salary.
SELECT ROUND(AVG(salary),2) AS average salary,department_id AS department,job_id AS job type FROM employees
GROUP BY job type, department ORDER BY average salary DESC; 

#Case: The query department cannot be empty, and the average salary of employees of each job type in each department, and display them according to the average salary.
SELECT ROUND(AVG(salary),2) AS average salary,department_id AS department,job_id AS job type FROM employees
WHERE department_id IS NOT NULL
GROUP BY job type, department ORDER BY average salary DESC; 

#Case: The query department cannot be empty, the average salary of employees in each department and each type of work is higher than 10,000, and it is displayed according to the average salary.
SELECT ROUND(AVG(salary),2) AS average salary,department_id AS department,job_id AS job type FROM employees
WHERE department_id IS NOT NULL
GROUP BY job type, department HAVING average salary > 10000
ORDER BY averagesalary DESC; 

#1. Query the maximum, minimum, average, and total salary of employees for each job_id, and sort them in ascending order by job_id.
SELECT job_id,MAX(salary),MIN(salary),AVG(salary),SUM(salary)
FROM employees
GROUP BY job_id
ORDER BY job_id ASC; 

#2. Query the difference between the highest and lowest wages of employees (DIFFERENCE).
SELECT MAX(salary) - MIN(salary) AS DIFFERENCE
FROM employees;
=======================================================
SELECT MAX(salary) AS highest,MIN(salary) AS lowest,MAX(salary)-MIN(salary) AS DIFFERENCE
FROM employees; 

#3. Query the minimum salary of employees under each manager. The minimum salary cannot be lower than 6,000. Employees without managers cannot be included.
SELECT MIN(salary),CONCAT(last_name,first_name),manager_id
FROM employees
WHERE manager_id IS NOT NULL
GROUP BY manager_id
HAVING MIN(salary)>6000;