SQL grammar: Analysis of the seven clauses of select

Mysql version: 8.0.26
Visual client: sql yog

1. The order of the seven clauses
2. Demonstration
- 2.1 from statement
- 2.2 on clause
- 2.3 where clause
- 2.4 group by clause
- - 2.4.1 WITHROLLUP, added after group by
  - 2.4.2 Can statistics be grouped by multiple fields?
  - 2.4.3 When grouping statistics, the problem of the field list behind the select
- 2.5 having clause
- 2.6 order by clause
- 2.7 limit clause

Reminder: The following is the text of this article, the following case is for reference

1. The order of the seven clauses

(1) from: Which tables to filter from.

(2) inner l left | right … on: Remove Cartesian product when associating multi-table queries

(3) where: Criteria to filter from the table

(4) group by: group by

(5) having: Filtering again in group statistical results (with rollup)

(6) order by: sort

(7) limit: Paging

Note: Clauses must be written in the order (1) – (7).

2. Demo

The test data is prepared as follows:

① Create a data table t_department:

CREATE TABLE `t_department` (
  `did` int NOT NULL AUTO_INCREMENT COMMENT 'Department number',
  `dname` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT 'employee name',
  `description` varchar(200) DEFAULT NULL COMMENT 'Employee Profile',
  PRIMARY KEY (`did`),
  UNIQUE KEY `dname` (`dname`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

②Create data table t_employee

CREATE TABLE `t_employee` (
  `eid` int NOT NULL AUTO_INCREMENT COMMENT 'employee number',
  `ename` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT 'employee name',
  `salary` double NOT NULL COMMENT 'salary',
  `commission_pct` decimal(3,2) DEFAULT NULL COMMENT 'Bonus ratio',
  `birthday` date NOT NULL COMMENT 'date of birth',
  `gender` enum('male','female') CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT 'male' COMMENT 'gender',
  `tel` char(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT 'mobile number',
  `email` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT 'email',
  `address` varchar(150) DEFAULT NULL COMMENT 'address',
  `work_place` set('Beijing','Shenzhen','Shanghai','Wuhan') CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT 'Beijing' COMMENT 'Workplace',
  `hiredate` date NOT NULL COMMENT 'hire date',
  `job_id` int DEFAULT NULL COMMENT 'Job ID',
  `mid` int DEFAULT NULL COMMENT 'leader number',
  `did` int DEFAULT NULL COMMENT 'department number',
  PRIMARY KEY (`eid`),
  KEY `job_id` (`job_id`),
  KEY `did` (`did`),
  KEY `mid` (`mid`),
  CONSTRAINT `t_employee_ibfk_1` FOREIGN KEY (`job_id`) REFERENCES `t_job` (`jid`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `t_employee_ibfk_2` FOREIGN KEY (`did`) REFERENCES `t_department` (`did`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `t_employee_ibfk_3` FOREIGN KEY (`mid`) REFERENCES `t_employee` (`eid`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `t_employee_chk_1` CHECK ((`salary` > 0)),
  CONSTRAINT `t_employee_chk_2` CHECK ((`hiredate` > `birthday`))
) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

③Create data table t_job

CREATE TABLE `t_job` (
  `jid` int NOT NULL AUTO_INCREMENT COMMENT 'job number',
  `jname` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT 'job title',
  `description` varchar(200) DEFAULT NULL COMMENT 'job profile',
  PRIMARY KEY (`jid`),
  UNIQUE KEY `jname` (`jname`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

2.1 from statement

Function:

Represents filtering data from a table

Case: Query all joys of the t_department table

The code demonstration is as follows:

select *
from t_department #Indicates to filter data from a table

2.2 on clause

Features:

(1) on must be used in conjunction with join

(2) Only write the associated conditions after on
The so-called association condition is the relationship between the associated fields of two tables

For example: t_employee table is associated with t_department table, t_employee.did=t_department.did

(3) If there are n tables associated, there will be n-1 associated conditions

When two tables are associated, there is one associated condition
When three tables are associated, there are 2 association conditions

Case: #Query the employee’s number, name, job number, job title, department number, department name, t_employee employee table, t_department department table, t_job job table are required.

The code is as follows (example):

SELECT eid,ename,t_job.job_id,t_job.job_name, `t_department`.`did`,`t_department`.`dname`
FROM t_employee INNER JOIN t_department INNER JOIN t_job
ON t_employee.did = t_department.did AND t_employee.job_id = t_job.job_id;

2.3 where clause

Function:

Filter in query results

Case: #Query the names and department names of all male employees

The code demonstration is as follows:

SELECT ename,dname
FROM t_department RIGHT JOIN t_employee ON t_employee.`did`=t_department.`did`
WHERE t_employee.`gender`='Male';

2.4 group by clause

Function:

Group by

Case: #Query the average salary of each department

The code demonstration is as follows:

SELECT dname,AVG(salary) AS average salary
FROM t_department RIGHT JOIN t_employee ON t_department.`did`=t_employee.`did`
GROUP BY t_department.`dname`;

Case: #Query the average salary of all male employees in each department

SELECT t_employee.did,dname,AVG(salary) AS average salary
FROM t_employee RIGHT JOIN t_department ON t_department.`did`=t_employee.`did`
WHERE gender='Male'
GROUP BY t_employee.`did`;

2.4.1 WITHROLLUP, added after group by

WITHROLLUP: Perform statistical data based on the group field.

Case: #Calculate the number of people according to the department, and add up the total

The code demonstration is as follows:

SELECT IFNULL(did,'Total') AS "Department Number" , COUNT(*) AS "Number of People" FROM t_employee GROUP BY did WITH ROLLUP;

2.4.2 Is it possible to group statistics by multiple fields?

Case: #Statistically query the average salary of male and female employees in each department separately

The code demonstration is as follows:

SELECT t_employee.did,dname,gender,AVG(salary) AS average salary
FROM t_employee RIGHT JOIN t_department ON t_department.`did`=t_employee.`did`
GROUP BY t_employee. `did`, gender;

2.4.3 When grouping statistics, the problem of the field list behind the select

Case: Count the number of people in each department

The code demonstration is as follows:

SELECT eid,ename, did, COUNT(*) FROM t_employee;

Analysis: It does not meet the case requirements. The case requirements only need to count the total number of people in each department, not the total number of people. Besides, add count(*)【count(), its function is to count the number of records, and to group Function, that is, after the function is executed, the number of rows obtained may be less, it may be 1 row, or it may be several rows], if no grouping condition is added, this query statement will query all the people + with returns the first employee’s employee number and name and department number.

The code is improved as follows:

SELECT eid,ename, did, COUNT(*) FROM t_employee GROUP BY did;

Analysis: Although group by did is added, that is, grouped by department number, the records under the fields “eid” and “ename” returned by the entire query statement are ambiguous, as shown below:

The records circled in red mean, Does the department number 1 have 14 employees named “Sun Hongliang” under the department number 1? , obviously wrong and illogical. Throughout the ages, there have been no quadruplets who are exactly the same.

The correct code is as follows:

SELECT did, COUNT(*) FROM t_employee GROUP BY did;

When grouping statistics, only write fields related to group statistics after select, and other irrelevant fields should not appear, otherwise it will cause ambiguity

2.5 having clause

Function:

Filter again in group statistics

Case: #Statistically query the average salary of male and female employees in each department separately, and only display records with an average salary of more than 10,000 yuan

The code demonstration is as follows:

SELECT t_employee.did,dname,gender,AVG(salary) AS average salary
FROM t_employee RIGHT JOIN t_department ON t_department.`did`=t_employee.`did`
GROUP BY t_employee. `did`, gender
HAVING average salary>10000;

What is the difference between where and having?

where is to filter the raw data of the original table, cannot be followed by grouping functions (avg, sum, count, max, min), etc.
having is to re-filter group statistical results, which can be followed by grouping functions and aliases of statistical results

2.6 order by clause

Function:

Ascending and descending, default is ascending
asc stands for ascending order [from small to large]
desc represents descending order [from largest to smallest]

Case: #Query the names and salaries of all employees, sorted by salary from high to low

The code demonstration is as follows:

SELECT ename,salary
FROM t_employee
ORDER BY salary DESC; #The default is ascending order, desc should be added for descending order, and asc can be added for ascending order

Case: #Query the names, salaries, and dates of birth of all employees, sorted by salary from high to low. If the salary is the same, in ascending order of date of birth.

The code demonstration is as follows:

SELECT ename,salary,birthday
FROM t_employee
ORDER BY salary DESC, birthday ASC;

2.7 limit clause

Function: Display results in pages.

Notice:

limit m,n
n: indicates the maximum number of lines displayed on this page
m: Indicates from which row to fetch records, the index of the first row is 0
m = (page-1)×n
page represents the page number

Up to 5 items can be displayed on each page, n=5
Page 1, page=1, m = (1-1)*5 = 0; limit 0,5
Page 2, page=2, m = (2-1)*5 = 5; limit 5,5
Page 3, page=3, m = (3-1)*5 = 10; limit 10,5

Display 20 items per page, n=20
Page 6, page=6, m = (6-1)*20; limit 100,20

Case: #Query employee information, according to the rule of displaying 5 items per page, query the first page

The code demonstration is as follows:

SELECT *
FROM t_employee
LIMIT 0,5;

Case: #Query employee information, according to the rule of displaying 5 items per page, query the second page

The code demonstration is as follows:

SELECT *
FROM t_employee
LIMIT 10,5;

Table of Contents

1. The order of the seven clauses

2. Demo

2.1 from statement

2.2 on clause

2.3 where clause

2.4 group by clause

2.4.1 WITHROLLUP, added after group by

2.4.2 Is it possible to group statistics by multiple fields?

2.4.3 When grouping statistics, the problem of the field list behind the select

2.5 having clause

2.6 order by clause

2.7 limit clause