MySQL query syntax

Time:2022-5-11

1 grouping function and single line function

Difference between grouping function and single line function:
Single line function: process a data and return a value
Grouping function: treat the virtual table as a group, process a group of data and return a value
Common single line functions:
Mathematical function
String function

concat
substr
instr
trim

And others

Common grouping functions:

Sum (field): sum all the values of the field
AVG (field): find the average value of this field
Max (field): find the maximum value
Min (field): find the minimum value
Count (field): calculate the number of non null values in this field

There is usually no performance difference between count (*) and count (1).
The count (field) does not count null rows

2 group query

Syntax:

Select query list
From table
[where screening criteria]
Group by grouped fields
Having grouped queries
[order by sorted fields]

Note that the non grouping function field in the query list must appear after group by.

For example:

SELECT MAX(salary),department_id
FROM employees
WHERE email LIKE '%a%'
GROUP BY department_id;

Max (salary) is a grouping function field, but Department_ ID is not. It must appear after group by.
At the same time, if other query fields appear in the aggregation function, but grouping is not used, it is not allowed (the values are equal and can not appear):

#Wrong
SELECT last_name,department_name, count(*)
FROM departments d
INNER JOIN  employees e;

3 multi table query

For multi table query, you can use commas to connect multiple tables or join to query. Comma join multiple tables

select * from a,b

It is equivalent to inner join and cross join. Join is the SQL syntax of version 99, which is not supported by version 96 SQL syntax.

#Inner connection
SELECT last_name,department_name
FROM departments d
INNER JOIN  employees e
ON e.`department_id` = d.`department_id`;

#Implementation of comma old style inner connection
SELECT last_name,department_name
FROM departments d, employees e

Join join queries can be divided into the following types:

3.1 internal connection classification

Inner connection is not equivalent to self connection.
The word inner can be omitted

Internal connections are also subdivided into the following categories:

Equivalent connection

In fact, the statement in on uses equal judgment

SELECT last_name,department_name
FROM departments d
INNER JOIN  employees e
ON e.`department_id` = d.`department_id`;

Non equivalent connection

In fact, the equal sign is not used

SELECT salary,grade_level
FROM employees e
 JOIN job_grades g
 ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`;

Self connection

Statistics of data in the same table by alias

 SELECT e.last_name,m.last_name
 FROM employees e
 JOIN employees m
 ON e.`manager_id`= m.`employee_id`;
MySQL query syntax

image.png

3.2 external connection

For external connection query, the data found is all records in the master table. If the master table does not find the slave table, it will be null filled
Remember: external connection query result = internal connection query result + records in the main table but not in the slave table
reference resources:https://www.jianshu.com/p/e7e6ce1200a4
So what is the master and slave table? In the left (outer) connection, the table on the left of the left join is the master table. The main table is on the right of right join.

Left outer join is equivalent to left join

Total external connection = results of internal connection + table 1 has table 2 does not + table 1 does not, but Table 2 does

Cross connection: the Cartesian product of two tables, which is equivalent to the comma connection of 96 syntax.

MySQL query syntax

image.png

4 sub query

Multiple keywords can appear in sub query, which is a complex part of SQL statement

Select statements that appear in other statements are called sub queries or internal queries, and external query statements are called main queries or external queries

#Advanced 7: subquery
/*
meaning:
Select statements that appear in other statements are called subqueries or intra queries
External query statements are called primary query or external query

Classification:
Location of sub query:
    After select:
        Only scalar subqueries are supported
    
    After from:
        Support table sub query
    After where or having: ★
        Scalar subquery (single line) √
        Column sub query (multiple lines) √
        
        Row subquery
        
    After exists (related sub query)
        Table subquery
According to the number of rows and columns in the result set:
    Scalar subquery (result set has only one row and one column)
    Query result set (only one column)
    Row subquery(结果集有一行多列)
    Table subquery(结果集一般为多行多列)



*/


#1、 After where or having
/*
1、标量子查询(单Row subquery)
2、列子查询(多Row subquery)

3、Row subquery(多列多行)

characteristic:
① Subqueries are enclosed in parentheses
② Subqueries are generally placed on the right side of conditions
③ Scalar subqueries are generally used with single line operators
> < >= <= = <>

Column subqueries are generally used with multi row operators
in、any/some、all

④ The execution of the sub query takes precedence over the main query, and the conditions of the main query use the results of the sub query

*/
#1. Scalar subquery ★

#Case 1: whose salary is higher than Abel?

#① Query Abel's salary
SELECT salary
FROM employees
WHERE last_name = 'Abel'

#② Query the employee's information and meet the salary > ① result
SELECT *
FROM employees
WHERE salary>(

    SELECT salary
    FROM employees
    WHERE last_name = 'Abel'

);

#Case 2: return job_ The ID is the same as that of employee 141. Salary is more than that of employee 143_ ID and salary

#① Query the job of employee 141_ id
SELECT job_id
FROM employees
WHERE employee_id = 141

#② Query salary of employee 143
SELECT salary
FROM employees
WHERE employee_id = 143

#③ Query employee's name, job_ ID and salary, job required_ Id = ① and salary > ②

SELECT last_name,job_id,salary
FROM employees
WHERE job_id = (
    SELECT job_id
    FROM employees
    WHERE employee_id = 141
) AND salary>(
    SELECT salary
    FROM employees
    WHERE employee_id = 143

);


#Case 3: return the last of the employee with the lowest salary in the company_ name,job_ ID and salary

#① Query the company's minimum wage
SELECT MIN(salary)
FROM employees

#② Query last_ name,job_ ID and salary, salary = ① is required
SELECT last_name,job_id,salary
FROM employees
WHERE salary=(
    SELECT MIN(salary)
    FROM employees
);


#Case 4: query the Department ID and the minimum wage of the Department whose minimum wage is greater than the minimum wage of No. 50 department

#① Check the minimum wage of department No. 50
SELECT  MIN(salary)
FROM employees
WHERE department_id = 50

#② Query the minimum wage of each department

SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id

#③ Screening on the basis of ②, meeting the requirements of min (salary) > ①
SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
HAVING MIN(salary)>(
    SELECT  MIN(salary)
    FROM employees
    WHERE department_id = 50


);

#Illegal use of scalar subquery

SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
HAVING MIN(salary)>(
    SELECT  salary
    FROM employees
    WHERE department_id = 250


);



#2.列子查询(多Row subquery)★
#Case 1: return location_ The ID is the name of all employees in the 1400 or 1700 department

#① Query location_ ID is the department number of 1400 or 1700
SELECT DISTINCT department_id
FROM departments
WHERE location_id IN(1400,1700)

#② Query the employee's name. The department number is required to be one in ① list

SELECT last_name
FROM employees
WHERE department_id  <>ALL(
    SELECT DISTINCT department_id
    FROM departments
    WHERE location_id IN(1400,1700)


);


#Case 2: return to a job in another type of work_ ID is' it '_ 'prog' employee number, name and job of any employee with low salary_ ID and salary

#① Query job_ ID is' it '_ Prog 'any salary of the Department

SELECT DISTINCT salary
FROM employees
WHERE job_id = 'IT_PROG'

#② Query employee number, name and job_ ID and any one of salary, salary < (①)
SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary<ANY(
    SELECT DISTINCT salary
    FROM employees
    WHERE job_id = 'IT_PROG'

) AND job_id<>'IT_PROG';

#Or
SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary<(
    SELECT MAX(salary)
    FROM employees
    WHERE job_id = 'IT_PROG'

) AND job_id<>'IT_PROG';


#Case 3: return to a job in another department_ ID is' it '_ Prog 'employee number, name and job of all employees with low salary in the Department_ ID and salary

SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary<ALL(
    SELECT DISTINCT salary
    FROM employees
    WHERE job_id = 'IT_PROG'

) AND job_id<>'IT_PROG';

#Or

SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary<(
    SELECT MIN( salary)
    FROM employees
    WHERE job_id = 'IT_PROG'

) AND job_id<>'IT_PROG';



#3、Row subquery(结果集一行多列或多行多列)

#Case: query the information of the employee with the smallest employee number and the highest salary



SELECT * 
FROM employees
WHERE (employee_id,salary)=(
    SELECT MIN(employee_id),MAX(salary)
    FROM employees
);

#① Query the minimum employee number
SELECT MIN(employee_id)
FROM employees


#② Query maximum wage
SELECT MAX(salary)
FROM employees


#③ Query employee information
SELECT *
FROM employees
WHERE employee_id=(
    SELECT MIN(employee_id)
    FROM employees


)AND salary=(
    SELECT MAX(salary)
    FROM employees

);


#2、 After select
/*
Only scalar subqueries are supported
*/

#Case: query the number of employees in each department


SELECT d.*,(

    SELECT COUNT(*)
    FROM employees e
    WHERE e.department_id = d.`department_id`
 )Number
 FROM departments d;
 
 
 #Case 2: query the Department name with employee No. = 102
 
SELECT (
    SELECT department_name,e.department_id
    FROM departments d
    INNER JOIN employees e
    ON d.department_id=e.department_id
    WHERE e.employee_id=102
    
)Department name;



#3、 From back
/*
It is required to alias the sub query results as a table
*/

#Case: query the salary grade of the average salary of each department
#① Query the average salary of each department
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id


SELECT * FROM job_grades;


#② Connect the result set and job of ①_ Grades table, filter criteria, average salary between lowest_ sal and highest_ sal

SELECT  ag_dep.*,g.`grade_level`
FROM (
    SELECT AVG(salary) ag,department_id
    FROM employees
    GROUP BY department_id
) ag_dep
INNER JOIN job_grades g
ON ag_dep.ag BETWEEN lowest_sal AND highest_sal;



#四、After exists (related sub query)

/*
Syntax:
Exists (complete query statement)
result:
1 or 0



*/

SELECT EXISTS(SELECT employee_id FROM employees WHERE salary=300000);

#Case 1: query the Department name with employees

#in
SELECT department_name
FROM departments d
WHERE d.`department_id` IN(
    SELECT department_id
    FROM employees

)

#exists

SELECT department_name
FROM departments d
WHERE EXISTS(
    SELECT *
    FROM employees e
    WHERE d.`department_id`=e.`department_id`


);


#Case 2: query the information of boyfriend without girlfriend

#in

SELECT bo.*
FROM boys bo
WHERE bo.id NOT IN(
    SELECT boyfriend_id
    FROM beauty
)

#exists
SELECT bo.*
FROM boys bo
WHERE NOT EXISTS(
    SELECT boyfriend_id
    FROM beauty b
    WHERE bo.`id`=b.`boyfriend_id`

);

5 joint query

Union Union merge: merge the results of multiple query statements into one result

Syntax:
Query statement 1
union
Query statement 2
union
...


Application scenario:
When the results to be queried come from multiple tables, and multiple tables have no direct connection relationship, but the query information is the same

Features: ★
1. The number of query columns of multiple query statements is required to be consistent!
2. The type and order of each column of the query requiring multiple query statements should be consistent
3. Union keyword is de duplicated by default. If union all is used, duplicate items can be included