MySQL 员工信息查询

单表查询 

目录

单表查询 

 多表查询

---

SELECT * FROM emp;

-- 需求：查询出工资高于500或者岗位为MANAGER的员工，同时还要满足他们的姓名的首字母为大写的J的数据.
SELECT * FROM emp WHERE (SAL >= 500 or JOB='MANAGER') and ENAME like 'J%';


-- 需求：按照工资的从低到高的顺序显示员工信息。
SELECT * FROM emp order by SAL asc;

-- 需求：按照部门号升序，员工的工资按降序排序。
SELECT * FROM emp ORDER BY DEPTNO ASC,SAL desc;
 
-- 需求：查询员工的年薪，并以年薪进行升序排序。
select empno,ename,sal,comm,(sal+ifnull(comm,0))*12 as nx from emp ORDER BY nx;


-- 显示工资最高的员工的姓名，工作，工资。
select max(sal) from emp;
select ename,job,sal from emp WHERE sal=(select max(sal) from emp);

-- 显示工资高于平均工资的员工信息
select ename,job,sal from emp WHERE sal>(select avg(sal) from emp);

-- 每个部门的平均工资和最高工资。
select deptno,avg(sal),max(sal) from emp GROUP BY DEPTNO;
 
-- 每个部门的每种岗位的平均工资和最低工资。
select deptno,job,avg(sal),min(sal) from emp GROUP BY DEPTNO,job;

-- 按部门编号进行分组，平均工资低于2000的部门号和他的平均工资。
select deptno,avg(sal) from emp GROUP BY DEPTNO having avg(sal)<2000;

-- 如何显示工资比部门编号30的所有员工的工资高的员工的姓名，工资，部门号
select max(sal) from emp where deptno=30

select * from emp where sal > (select max(sal) from emp where deptno=30)

														-- > all(集合) 你查询的值是比集合中所有的值都大的。
select * from emp where sal > all(select sal from emp where deptno=30)

-- 
-- 
-- 如何显示工资比部门编号30的任意员工的工资高的员工的姓名，工资，部门号

select * from emp where sal > (select min(sal) from emp where deptno=30)
-- 
-- 
	                           -- > any(集合) 你查询的值是比集合中任意一个值都大的。

select * from emp where sal > any(select sal from emp where deptno=30)

 多表查询

-- 两个表 查询

/*
	select 列名1,列名2.......
  from 表1
	[cross left right ]join 表2
  on 连接的条件  表1.字段名=表2.字段名
  and 查询条件
	join 表3
  on 连接的条件  表1.字段名=表3.字段名
  where 查询条件
  group by ....
  having .....
  order by .....
  .......
*/

-- 交叉连接  两种表进行连接  一张表的每一条数据去跟另一张的表每一条数据进行连接  结果：笛卡尔乘积   5*4=20
select e.*,d.* from emp e
cross join dept  d

-- 多表查询  
-- 查询的字段来源于多张表  emp：员工编号、姓名、工种、工资、部门编号  dept：部门名称
-- 查询员工信息（员工编号、姓名、工种、工资、部门编号、部门名称）
-- 部门表和员工表： 一个部门下有多个员工  一个员工只能属于一个部门  部门：员工：1：n

-- select ...... from 表名 表别名  [inner] join  表名  表别名  on 连接条件  ...... 内连接 返回的数据必须满足on后面的连接条件 
select e.*,d.dname from emp e
join dept  d
on e.deptno = d.deptno
order by e.empno

-- 我要查询所有员工 即使没有部门编号也要把数据显示出来  
-- 外连接  左[外]连接  右[外]连接  全[外]连接
-- select ...... from 表名 表别名   left/right/full  [outer] join  表名  表别名  on 连接条件  ...... 
-- 外连接结果  主表  left join 前面表为主表    副表  left join后面的表为副表
-- 外连接结果：内连接结果+主表中不满足on条件的数据显示出来， 查询的字段 只要不是主表的字段都显示null值。

-- 左连接
select e.*,d.dname from emp e
left join dept  d
on e.deptno = d.deptno
order by e.empno

-- 右连接 主表  right join后面的表为表表    副表   right join 前面表为副表
-- 外连接结果：内连接结果+主表中不满足on条件的数据显示出来， 查询的字段 只要不是主表的字段都显示null值。
select e.*,d.dname from emp e
right join dept  d
on e.deptno = d.deptno
order by e.empno

-- 全连接  full outer join  mysql不支持该语法
-- 外连接结果：内连接结果+两种表中不满足on条件的数据显示出来， 查询的字段 只要不是主表的字段都显示null值。
-- UNION 查询结果的并集 去重复合并数据
-- UNION  all 不去重复合并数据
select * from (select e.*,d.dname from emp e
left join dept  d
on e.deptno = d.deptno
UNION  all
select e.*,d.dname from emp e
right join dept  d
on e.deptno = d.deptno) tab 
order by tab.sal


select e.*,d.dname from emp e
full outer join dept  d
on e.deptno = d.deptno



-- 查询所有员工，并显示员工工资的等级
SELECT e.*,sg.GRADE from emp e
join salgrade sg
ON e.sal>=sg.LOSAL and sg.hisal>e.sal
ORDER BY sg.GRADE

-- 返回工资处于第四级别的员工的姓名。

-- 方法一 子查询
SELECT tab.* FROM (SELECT e.*,sg.GRADE from emp e
join salgrade sg
ON e.sal>=sg.LOSAL and sg.hisal>e.sal) tab
WHERE tab.grade=4

-- 方法二 推荐
SELECT e.*,sg.GRADE from emp e
join salgrade sg
ON e.sal>=sg.LOSAL and sg.hisal>e.sal AND sg.grade=4



-- 返回工资为二等级的职员名字、部门所在地、和二等级的最低工资和最高工资
SELECT e.ename,sg.grade,d.loc,MAX(sal),MIN(sal) from emp e
-- 返回工资为二等级的职员名字
JOIN salgrade sg
ON e.sal>=sg.LOSAL and sg.hisal>e.sal AND sg.grade=2
-- 返回部门所在地
left JOIN dept d
on e.deptno=d.deptno
-- 二等级的最低工资和最高工资
GROUP BY sg.grade


-- 工资等级多于smith的员工信息。
SELECT e.*,sg.grade FROM emp e 
JOIN salgrade sg
ON e.sal>=sg.losal and sg.hisal >= e.sal and sg.grade>(
SELECT sg.grade FROM emp e
JOIN salgrade sg
ON e.sal>=sg.losal and sg.hisal >= e.sal
WHERE e.ename ='SMITH'
)
ORDER BY sg.grade