admin管理员组文章数量:1034398
Mysql复合查询
1. 基本查询简单回顾
条件筛选 | 使用 AND、OR、LIKE 等条件操作符进行多条件筛选。 |
|---|---|
排序 | 使用 ORDER BY 对查询结果进行排序，支持多个字段排序（如按部门号升序，工资降序）。 |
聚合函数与分组 | 使用 GROUP BY 对数据进行分组，并使用聚合函数（如 AVG(), MAX(), COUNT()）进行汇总。 |
子查询 | 用于动态选择数据，如查询工资高于平均工资的员工。 |
内嵌查询 | 查询工资最高的员工、显示员工的年薪等。 |
- 查询工资高于500或岗位为MANAGER的雇员，同时还要满足他们的姓名首字母为大写的
select *from emp where ename like 'j%' and (sal>500 or job='MANAGER');- 使用年薪进行降序排序：
select ename ,job,sal*12+ifnull(comm,0) sarn from emp order by sarn desc;- 显示工资最高的员工的名字和工作岗位：
方案一：
代码语言：javascript代码运行次数：0运行复制select ename ,job ,sal from emp where sal in(select max(sal) from emp );方案二：
代码语言：javascript代码运行次数：0运行复制select ename ,job ,sal from emp order by sal desc limit 1;- 显示工资高于平均工资的员工信息：
select ename ,job ,sal from emp where sal>(select avg(sal) from emp);- 显示每个部门的平均工资和最高工资：
select deptno,format(avg(sal),2),max(sal) from emp group by deptno;- 显示平均工资低于2000的部门号和它的平均工资：
select deptno ,avg(sal) avgsal from emp group by deptno having avgsal<2000;- 显示每种岗位的雇员总数，平均工资：
select job ,count(*) from emp group by job;2. 多表查询
联接查询 | 通过表之间的关系字段（如 deptno）进行联合查询，常用的连接方式包括内连接（INNER JOIN）等。 |
|---|---|
跨表查询 | 通过连接多个表（如 EMP 和 DEPT）来获取联合结果。 |
使用 GROUP BY 和聚合函数 | 计算每个部门的平均工资和最高工资等。 |
连接条件 | 可以通过多个条件进行查询，显示特定条件下的数据。 |
将第一张表的每一条数据和第二章表的每一条数据相结合，不加过滤条件得到的结果被称为笛卡尔积
笛卡尔积：
- 显示部门为10的员工和部门的全部信息
select * from emp,dept where emp.deptno=dept.deptno and emp.deptno=10;- 显示部门为10的员工姓名，部门名和工资
select ename,sal,dname from emp,dept
where emp.deptno=dept.deptno and emp.deptno=10;- 显示员工的姓名，工资，及工资级别
select ename ,sal,grade from emp,salgrade where sal between losal and hisal;3. 自连接（Self-Join）
表的别名 | 自连接时需要使用别名来区分同一表的不同实例。 |
|---|---|
示例 | 查找员工的上级（使用 mgr 字段指向经理的 empno）。 |
应用场景 | 员工与其上级、领导层次的关系查询。 |
将两张相同的表做笛卡尔积
- 显示员工及员工领导的个人信息
select * from emp e1, emp e2 where e1.empno=e2.mgr;4. 子查询
单行子查询 | 子查询只返回一行结果，通常用于 =、< 等条件。 |
|---|---|
多行子查询 | 子查询返回多行结果，常配合 IN、ALL、ANY 等关键字使用。 |
多列子查询 | 返回多个列的数据，适用于多条件的筛选。 |
FROM 子查询 | 将子查询作为临时表使用，来简化复杂查询。 |
嵌套查询 | 通过将查询嵌套在其他查询语句中，增加查询灵活性。 |
4.1单行子查询
- 显示SMITH同一部门的员工
select *from emp where
deptno =(select deptno from emp where ename='SMITH');4.2多行子查询
返回多行记录的子查询
- in关键字；查询和10号部门的工作岗位相同的雇员的名字，岗位，工资，部门号，但是不包含10自己的
select ename ,job,sal,deptno from emp where
job in(select job from emp where deptno =10)and deptno<>10;- all关键字；显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号
select ename,sal,deptno from emp
where sal > all(select sal from emp where deptno =30);- any关键字；显示工资比部门30的任意员工的工资高的员工的姓名、工资和部门号（包含自己部门的员工）
select ename,sal,deptno from emp
where sal>any(select sal from emp where deptno=30);4.3多列子查询
单行子查询是指子查询只返回单列，单行数据；多行子查询是指返回单列多行数据，都是针对单列而言的，而多列子查询则是指查询返回多个列数据的子查询语句
- 查询和SMITH的部门和岗位完全相同的所有雇员，不含SMITH本人
select * from emp where
(deptno,job)=(select deptno,job from emp where ename='SMITH')
and ename<>'SMITH';4.4在from中使用子查询
在mysql中每一次的查询结果都是一个子表。
- 显示每个高于自己部门平均工资的员工的姓名、部门、工资、平均工资
select ename,deptno,sal,asal from emp t1,(select deptno dt,avg(sal) asal
from emp group by dt)t2 where t1.sal>t2.asal and t1.deptno=t2.dt;- 查找每个部门工资最高的人的姓名、工资、部门、最高工资
select ename,sal,emp.deptno,msal
from emp ,(select deptno,max(sal)msal from emp group by deptno)
emp2 where sal=msal and emp.deptno=emp2.deptno ;- 显示每个部门的信息（部门名，编号，地址）和人员数量
select *from dept,
(select deptno,count(deptno) from emp group by deptno)dept2
where dept.deptno=dept2.deptno;5. 合并查询
UNION | 将多个 SELECT 查询结果合并，去除重复记录。 |
|---|---|
UNION ALL | 将多个 SELECT 查询结果合并，不去除重复记录。 |
应用场景 | 如查询职位为 MANAGER 或工资高于某数值的员工。 |
5.1UNION
该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中的重复行。
- 将工资大于2500或职位是MANAGER的人找出来
5.2UNION ALL
该操作符用于取得两个结果集的并集。当使用该操作符时，不会去掉结果集中的重复行。
Mysql复合查询
1. 基本查询简单回顾
条件筛选 | 使用 AND、OR、LIKE 等条件操作符进行多条件筛选。 |
|---|---|
排序 | 使用 ORDER BY 对查询结果进行排序，支持多个字段排序（如按部门号升序，工资降序）。 |
聚合函数与分组 | 使用 GROUP BY 对数据进行分组，并使用聚合函数（如 AVG(), MAX(), COUNT()）进行汇总。 |
子查询 | 用于动态选择数据，如查询工资高于平均工资的员工。 |
内嵌查询 | 查询工资最高的员工、显示员工的年薪等。 |
- 查询工资高于500或岗位为MANAGER的雇员，同时还要满足他们的姓名首字母为大写的
select *from emp where ename like 'j%' and (sal>500 or job='MANAGER');- 使用年薪进行降序排序：
select ename ,job,sal*12+ifnull(comm,0) sarn from emp order by sarn desc;- 显示工资最高的员工的名字和工作岗位：
方案一：
代码语言：javascript代码运行次数：0运行复制select ename ,job ,sal from emp where sal in(select max(sal) from emp );方案二：
代码语言：javascript代码运行次数：0运行复制select ename ,job ,sal from emp order by sal desc limit 1;- 显示工资高于平均工资的员工信息：
select ename ,job ,sal from emp where sal>(select avg(sal) from emp);- 显示每个部门的平均工资和最高工资：
select deptno,format(avg(sal),2),max(sal) from emp group by deptno;- 显示平均工资低于2000的部门号和它的平均工资：
select deptno ,avg(sal) avgsal from emp group by deptno having avgsal<2000;- 显示每种岗位的雇员总数，平均工资：
select job ,count(*) from emp group by job;2. 多表查询
联接查询 | 通过表之间的关系字段（如 deptno）进行联合查询，常用的连接方式包括内连接（INNER JOIN）等。 |
|---|---|
跨表查询 | 通过连接多个表（如 EMP 和 DEPT）来获取联合结果。 |
使用 GROUP BY 和聚合函数 | 计算每个部门的平均工资和最高工资等。 |
连接条件 | 可以通过多个条件进行查询，显示特定条件下的数据。 |
将第一张表的每一条数据和第二章表的每一条数据相结合，不加过滤条件得到的结果被称为笛卡尔积
笛卡尔积：
- 显示部门为10的员工和部门的全部信息
select * from emp,dept where emp.deptno=dept.deptno and emp.deptno=10;- 显示部门为10的员工姓名，部门名和工资
select ename,sal,dname from emp,dept
where emp.deptno=dept.deptno and emp.deptno=10;- 显示员工的姓名，工资，及工资级别
select ename ,sal,grade from emp,salgrade where sal between losal and hisal;3. 自连接（Self-Join）
表的别名 | 自连接时需要使用别名来区分同一表的不同实例。 |
|---|---|
示例 | 查找员工的上级（使用 mgr 字段指向经理的 empno）。 |
应用场景 | 员工与其上级、领导层次的关系查询。 |
将两张相同的表做笛卡尔积
- 显示员工及员工领导的个人信息
select * from emp e1, emp e2 where e1.empno=e2.mgr;4. 子查询
单行子查询 | 子查询只返回一行结果，通常用于 =、< 等条件。 |
|---|---|
多行子查询 | 子查询返回多行结果，常配合 IN、ALL、ANY 等关键字使用。 |
多列子查询 | 返回多个列的数据，适用于多条件的筛选。 |
FROM 子查询 | 将子查询作为临时表使用，来简化复杂查询。 |
嵌套查询 | 通过将查询嵌套在其他查询语句中，增加查询灵活性。 |
4.1单行子查询
- 显示SMITH同一部门的员工
select *from emp where
deptno =(select deptno from emp where ename='SMITH');4.2多行子查询
返回多行记录的子查询
- in关键字；查询和10号部门的工作岗位相同的雇员的名字，岗位，工资，部门号，但是不包含10自己的
select ename ,job,sal,deptno from emp where
job in(select job from emp where deptno =10)and deptno<>10;- all关键字；显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号
select ename,sal,deptno from emp
where sal > all(select sal from emp where deptno =30);- any关键字；显示工资比部门30的任意员工的工资高的员工的姓名、工资和部门号（包含自己部门的员工）
select ename,sal,deptno from emp
where sal>any(select sal from emp where deptno=30);4.3多列子查询
单行子查询是指子查询只返回单列，单行数据；多行子查询是指返回单列多行数据，都是针对单列而言的，而多列子查询则是指查询返回多个列数据的子查询语句
- 查询和SMITH的部门和岗位完全相同的所有雇员，不含SMITH本人
select * from emp where
(deptno,job)=(select deptno,job from emp where ename='SMITH')
and ename<>'SMITH';4.4在from中使用子查询
在mysql中每一次的查询结果都是一个子表。
- 显示每个高于自己部门平均工资的员工的姓名、部门、工资、平均工资
select ename,deptno,sal,asal from emp t1,(select deptno dt,avg(sal) asal
from emp group by dt)t2 where t1.sal>t2.asal and t1.deptno=t2.dt;- 查找每个部门工资最高的人的姓名、工资、部门、最高工资
select ename,sal,emp.deptno,msal
from emp ,(select deptno,max(sal)msal from emp group by deptno)
emp2 where sal=msal and emp.deptno=emp2.deptno ;- 显示每个部门的信息（部门名，编号，地址）和人员数量
select *from dept,
(select deptno,count(deptno) from emp group by deptno)dept2
where dept.deptno=dept2.deptno;5. 合并查询
UNION | 将多个 SELECT 查询结果合并，去除重复记录。 |
|---|---|
UNION ALL | 将多个 SELECT 查询结果合并，不去除重复记录。 |
应用场景 | 如查询职位为 MANAGER 或工资高于某数值的员工。 |
5.1UNION
该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中的重复行。
- 将工资大于2500或职位是MANAGER的人找出来
5.2UNION ALL
该操作符用于取得两个结果集的并集。当使用该操作符时，不会去掉结果集中的重复行。
本文标签： Mysql复合查询
版权声明：本文标题：Mysql复合查询 内容由热心网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://it.en369.cn/jiaocheng/1748144811a2260089.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。
发表评论