【MySQL】入门Day3之多表查询及常用函数

文章目录

---

---

一、自连接

在下面表中想要查询所有员工姓名及其直属领导姓名，我们可以发现在所有员工姓名中就包含领导姓名，这是我们要把二者整合为一张表需要用到的方法就是自连接

首先将此表分别命名为员工表和领导表，按照员工表里的领导编号=领导表中的员工编号连接两表，最后仅显示想要的结果即可。

select * 
from emp as 员工表 left join emp as 领导表 on 员工表.mgr = 领导表.empid;
-- 仅显示员工姓名和直属领导姓名
select 员工表.ename as 员工姓名, 领导表.ename as 直属领导姓名
from emp as 员工表 left join emp as 领导表 on 员工表.mgr = 领导表.empid;

例：查询入职日期早于其直属领导的员工：empid,ename,dname

select 员工表.empid,员工表.ename,dname
from emp as 员工表 left join emp as 领导表 on 员工表.mgr = 领导表.empid
left join dept on 员工表.deptno = dept.deptno
where 员工表.hiredate < 领导表.hiredate;

实现逻辑：三个表进行连接，前两个表依据上面方法进行连接，再将部门表与已经连接好的表进行连接（多表连接不分先后顺序）

二、子查询

一个select语句中包含另一个或多个完整的select语句。

所有的多表查询都能用子查询实现，但是子查询不一定能够用多表查询实现。

2.1 子查询出现的位置

• 出现在where子句中：将子查询返回的结果作为主查询的条件
• 出现在from子句中：将子查询返回的结果作为主查询的一个表
  （先执行括号里面的子查询）
  必须要设置别名

2.2 子查询分类

• 标量子查询：返回的结果是一个数据（单行单列）
• 行子查询：返回的结果是一行(单行多列)
• 列子查询：返回的结果是一列(多行单列)
• 表子查询：返回的结果是一张虚拟结果集(多行多列)

标量子查询：

-- 查询基本工资高于公司平均工资的员工信息
# 不运用子查询逻辑
select *
from emp 
where sal > avg(sal);-- 报错：where 子句中不能使用聚合函数
# 查询公司平均工资
select avg(sal) from emp;
-- 将上面作为一个结果放入括号即实现子查询
select *
from emp 
where sal >(select avg(sal) from emp);
-- 练习：查询和allen同一个领导的员工：empno,ename,job,mgr
select empid,ename,job,mgr
from emp
where mgr = (select mgr from emp where ename = 'allen') and ename <> 'allen';

行子查询：

-- 行子查询
-- 查询和smith同部门同职位的员工：empno,ename,job,deptno
select deptno,job from emp where ename = 'smith';

select empid,ename,job,deptno 
from emp 
where (deptno,job) = (20,'clerk') and ename <> 'smith';
-- 两个整合为一个语句
select empid,ename,job,deptno 
from emp 
where (deptno,job) = (select deptno,job from emp where ename = 'smith') and ename <> 'smith';

列子查询：

-- 查询普通员工的工资等级：empno,ename,sal,grade
-- 查询领导的工号(去重)
select distinct mgr from emp where mgr is not null;

select empno,ename,sal,grade
from emp left join salgrade on sal between losal and hisal
where empid not in(select distinct mgr from emp where mgr is not null) ;

表子查询：

逻辑：先查询出各部门最高工资，将结果作为一个新表与原表按照部门编号进行连接，最后筛选出原表中基本工资与新表中最高工资相等的员工信息

-- from子查询
-- 查询各部门最高工资的员工：empno,ename,sal,deptno
# 查询各部门最高工资
select deptno,max(sal) 最高工资 from emp group by deptno;

select empid,ename,sal,emp.deptno
from emp 
left join (select deptno,max(sal) 最高工资 from emp group by deptno) as t 
on emp.deptno = t.deptno
where sal = 最高工资;

关于mysql常用函数在资源里展示

---