! Description: In the last 5 days, I have read the basic MySQL chapters of Master Kang of Shangxi Valley (forcing JVM) and shared some notes, mark, mark!

1. Subqueries

• A subquery is a query whose statement is nested within another query statement
• Use Attention
  • Subqueries need to be enclosed in parentheses
  • Place subqueries to the right of the comparison criteria (readability)
  • Single row operation corresponds to single row subquery, and multiple row operation corresponds to multiple row subquery

1. Classification of subqueries

1) Single row subquery vs multi row subquery

• Divide subqueries into single-row subqueries and multiple-row subqueries by whether the records returned from the results of the internal query are one or more records

2) Related subqueries vs irrelevant subqueries

• Is inline query executed multiple times

2. Single row subquery

1) Single-line comparison operator

• =,>,<,>=,<=
• If the result of a subquery is multiple rows of data (in this case, multiple rows of subqueries), the query will fail

2) Attention

• When the result of a subquery is NULL, the parent query is not recorded

3. Multi-line subqueries

1) Multi-line comparison operator

• IN: equal to any one of the records
• ANY: Need to be used with a single-line comparison operator to compare with a value returned by a subquery
• ALL: Need to be used with a single-line comparison operator to compare all values returned by a subquery
• SOME: Actually, it is an alias of ANY. It works the same as ANY. Use ANY more often.

4. Related Subqueries

1) Definition

• External queries since the execution of a subquery, usually because the tables of the subquery use external tables and conditionally associate them, need to be refreshed every time an external query is executed. Such subqueries are called associated subqueries (related subqueries)
• Each execution process: Get candidate columns from the main query --> Subquery uses data from the main query --> Return the number of rows changed if the conditions of the subquery are met

# Query last_of employees whose wages are higher than the average in their department Name, salary, department_ ID
SELECT 
    last_name, salary, department_id
FROM
    employees e1
WHERE
    salary > (
		SELECT 
            AVG(salary)
        FROM
            employees e2
        WHERE
            department_id = e1.department_id);

2) Scope of using subqueries

• In SELECT, subqueries can be declared anywhere except GROUP BY and LIMIT

SELECT ..., ..., ...(Aggregation function exists)
FROM ..., ..., ...
WHERE Multi-table join condition AND Join condition without aggregate function
GROUP BY ..., ..., ...
HAVING Joining Conditions Including Aggregate Functions
ORDER BY ..., ..., ...(ASC, DESC)
LIMIT ..., ...

3)EXIST / NOT EXIST

• Use EXIST to check for qualified rows in subqueries
• EXIST If there are no qualified rows in the subquery
  • Conditional return FALSE
  • Continue searching in subqueries
• EXIST If there are qualified rows in the subquery
  • Do not continue search in subqueries
  • Conditional Return TRUE

2. Subquery Related Exercises

use atguigudb;

# 1 Query last_of employees in the same department as Zlotkey Name, salary
SELECT 
    last_name, salary
FROM
    employees
WHERE
    department_id = (
		SELECT 
            department_id
        FROM
            employees
        WHERE
            last_name = 'Zlotkey');

# 2 Query employee_of employees whose salary is higher than the company's average salary Id, last_ Name, salary
SELECT 
    employee_id, last_name, salary
FROM
    employees
WHERE
    salary > (
		SELECT 
            AVG(salary)
        FROM
            employees);

# 3 Select salary greater than all jobs_ Id ='SA_ Last_of salary employee of MAN's employee Name, job_ Id, salary
SELECT 
    last_name, job_id, salary
FROM
    employees
WHERE
    salary > ALL (
		SELECT 
            salary
        FROM
            employees
        WHERE
            job_id = 'SA_MAN');

# 4 Query and last_ Employee_of an employee in the same department whose name contains the letter u Id, last_name
# But this should exclude these employees themselves
SELECT 
    employee_id, last_name
FROM
    employees
WHERE
    department_id IN (
		SELECT DISTINCT
            department_id
        FROM
            employees
        WHERE
            last_name LIKE '%u%')
        AND last_name NOT LIKE '%u%';

# 5Query location in department_ Id = employee_of the employee working in the 1700 Department ID
SELECT 
    employee_id
FROM
    employees e,
    departments d
WHERE
    e.department_id = d.department_id
        AND d.location_id = 1700;

# 6 Query last_of employees whose manager is King Name, salary
SELECT 
    last_name, salary
FROM
    employees
WHERE
    manager_id IN (SELECT 
            employee_id
        FROM
            employees
        WHERE
            last_name = 'King');

# 7 Query last_of the lowest salary employee Name, salary
SELECT 
    last_name, salary
FROM
    employees
WHERE
    salary = (
      	SELECT 
            MIN(salary)
        FROM
            employees);

# 8 Query information about the Department with the lowest average salary
SELECT 
    d.*
FROM
    employees e,
    departments d
WHERE
    e.department_id = d.department_id
GROUP BY department_id
HAVING AVG(salary) <= ALL (
  	SELECT 
        AVG(salary)
    FROM
        employees
    GROUP BY department_id);

# 9 Query the lowest average salary department information and the average salary for that department (using related subqueries)
SELECT 
    d.*,
    (		SELECT 
            AVG(salary)
        FROM
            employees
        WHERE
            department_id = d.department_id) salary
FROM
    employees e,
    departments d
WHERE
    e.department_id = d.department_id
GROUP BY department_id
HAVING AVG(salary) <= ALL (
		SELECT 
        AVG(salary)
    FROM
        employees
    GROUP BY department_id);

SELECT AVG(salary)
    FROM
        employees
    GROUP BY department_id;

# 10 Query job information with the highest average salary