SQL语法：浅析select之七大子句

Mysql版本：8.0.26
可视化客户端：sql yog

---

---

提示：以下是本篇文章正文内容，下面案例可供参考

一、七大子句顺序

(1) from: 从哪些表中筛选。

(2) inner l left | right … on: 关联多表查询时，去除笛卡尔积

(3) where: 从表中筛选的条件

(4) group by: 分组依据

(5) having: 在分组统计结果中再次筛选 (with rollup)

(6) order by: 排序

(7) limit: 分页

注意：必须按照 (1) - (7) 的顺序编写子句。

二、演示

测试数据准备如下：

①创建数据表 t_department:

CREATE TABLE `t_department` (`did` int NOT NULL AUTO_INCREMENT COMMENT '部门编号',`dname` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '员工名称',`description` varchar(200) DEFAULT NULL COMMENT '员工简介',PRIMARY KEY (`did`),UNIQUE KEY `dname` (`dname`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

②创建数据表t_employee

CREATE TABLE `t_employee` (`eid` int NOT NULL AUTO_INCREMENT COMMENT '员工编号',`ename` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '员工姓名',`salary` double NOT NULL COMMENT '薪资',`commission_pct` decimal(3,2) DEFAULT NULL COMMENT '奖金比例',`birthday` date NOT NULL COMMENT '出生日期',`gender` enum('男','女') CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '男' COMMENT '性别',`tel` char(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '手机号码',`email` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '邮箱',`address` varchar(150) DEFAULT NULL COMMENT '地址',`work_place` set('北京','深圳','上海','武汉') CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '北京' COMMENT '工作地点',`hiredate` date NOT NULL COMMENT '入职日期',`job_id` int DEFAULT NULL COMMENT '职位编号',`mid` int DEFAULT NULL COMMENT '领导编号',`did` int DEFAULT NULL COMMENT '部门编号',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

③创建数据表t_job

CREATE TABLE `t_job` (`jid` int NOT NULL AUTO_INCREMENT COMMENT '职位编号',`jname` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '职位名称',`description` varchar(200) DEFAULT NULL COMMENT '职位简介',PRIMARY KEY (`jid`),UNIQUE KEY `jname` (`jname`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

2.1 from语句

👉功能：

表示从某个表中筛选数据

案例：查询t_department表的所有欣喜

代码演示如下：

select * 
from t_department  #表示从某个表中筛选数据

2.2 on子句

👉特点：

（1）on必须配合join使用

（2）on后面只写关联条件
所谓关联条件是两个表的关联字段的关系

例如：t_employee表和t_department表关联,t_employee.did=t_department.did

（3）有n张表关联，就有n-1个关联条件

两张表关联，就有1个关联条件
三张表关联，就有2个关联条件

案例：#查询员工的编号，姓名，职位编号，职位名称，部门编号，部门名称，需要t_employee员工表，t_department部门表，t_job职位表。

代码如下（示例）：

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子句

👉功能：

在查询结果中筛选

案例：#查询所有男员工的姓名和部门名称

代码演示如下：

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

2.4 group by子句

👉功能：

分组依据

案例：#查询每一个部门的平均薪资

代码演示如下：

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

案例：#查询每一个部门所有男员工的平均薪资

SELECT t_employee.did,dname,AVG(salary) AS 平均薪资
FROM t_employee RIGHT JOIN t_department ON t_department.`did`=t_employee.`did`
WHERE gender='男'
GROUP BY t_employee.`did`;

2.4.1 WITHROLLUP，加在group by后面

WITHROLLUP：在group分组字段的基础上再进行统计数据。

案例：#按照部门统计人数，并合计总数

代码演示如下：

SELECT IFNULL(did,'合计') AS "部门编号" , COUNT(*)  AS "人数" FROM t_employee GROUP BY did WITH ROLLUP;

2.4.2 是否可以按照多个字段分组统计？

案例：#分别统计查询每一个部门男、女员工的平均薪资

代码演示如下：

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

2.4.3 分组统计时，select后面字段列表的问题

案例：统计每个部门的人数

代码演示如下：

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

❌

分析：不符合案例需求，案例需求只要统计各部门的总人数，而不是统计总人数，况且加上count(*)【count()，它的功能是统计记录数,，又是分组函数，即函数执行完后，得到结果的行数可能会变少，有可能是1行，也可能是几行】，如果不加分组条件，此查询语句会查询所有的人数+带返回第一个员工的员工编号和姓名以及部门编号。

代码改善如下：

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

❌

分析：虽然加了group by did，即按部门编号分组，但整个查询语句返回的”eid“和”ename“等字段下的记录有歧义，如下所示：

红圈勾出的记录是在表达，部门编号为1的部门下有14个员工编号为1，名为"孙洪亮"的员工吗？，显然不对，不符合逻辑，古往今来，没有十四胞胎，且一模一样的人。

正确代码如下：

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

分组统计时，select后面只写和分组统计有关的字段，其他无关字段不要出现，否则会引起歧义

2.5 having子句

👉功能：

在分组统计结果中再次筛选

案例：#分别统计查询每一个部门男、女员工的平均薪资，只显示平均薪资在10000元以上的记录

代码演示如下：

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

where和having的区别别?

• where是针对原表的原始数据筛选，后面不能接分组函数(avg,sum,count,max,min）等

• having是针对分组统计结果的再次筛选，后面可以接分组函数，还可以使用统计结果的别名

2.6 order by子句

👉功能：

升序和降序，默认是升序
asc代表升序【从小到大】
desc 代表降序【从大到小】

案例：#查询所有员工的姓名和薪资，按照薪资从高到低排序

代码演示如下：

SELECT ename,salary
FROM t_employee
ORDER BY salary DESC; #默认是升序，降序要加desc，升序可以加asc

案例：#查询所有员工的姓名和薪资、出生日期，按照薪资从高到低排序 。如果薪资相同的，按照出生日期从小到大。

代码演示如下：

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

2.7 limit子句

👉功能：分页显示结果。

注意：

limit m,n
n：表示最多该页显示几行
m：表示从第几行开始取记录，第一个行的索引是0
m = (page-1)×n
page表示第几页

每页最多显示5条，n=5
第1页，page=1，m = (1-1)*5 = 0; limit 0,5
第2页，page=2，m = (2-1)*5 = 5; limit 5,5
第3页，page=3，m = (3-1)*5 = 10; limit 10,5

每页显示20条，n=20
第6页，page=6，m = (6-1)*20；limit 100,20

案例：#查询员工信息，按照每页显示5条的规则，查询第1页

代码演示如下：

SELECT * 
FROM t_employee
LIMIT 0,5;

案例：#查询员工信息，按照每页显示5条的规则，查询第2页

代码演示如下：

SELECT * 
FROM t_employee
LIMIT 10,5;

---