发帖
雷达卡
-- 查询入职日期早于其直属领导的员工：empno,ename,dname
select a1.empno,a1.ename,b1.dname
from(
select a.empno,a.ename,a.deptno,b.hiredate as 领导入职时间
from emp as a
left join emp as b
on a.mgr=b.empno
where b.hiredate>a.hiredate
) as a1
left join dept as b1
on a1.deptno=b1.deptno
;
select * from dept ;
-- 子查询
-- 标量子查询：
-- 查询基本工资高于公司平均工资的员工信息
use test1;
show tables;
select *
from emp
where sal>(select avg(sal) from emp)
;
-- 练习：查询和allen同一个领导的员工：empno,ename,job,mgr
select *
from emp
where mgr in (
select mgr
from emp
where ename=\"allen\")
;
-- 行子查询
-- 查询和smith同部门同职位的员工：empno,ename,job,deptno
select *
from emp
where (deptno,job)=(select deptno,job from emp where ename=\"smith\") and ename!=\"smith\";
-- 列子查询：
-- 查询普通员工的工资等级：empno,ename,sal,grade
select distinct mnager from emp where mnager is not null;
select *
from emp
left join salgrade
on sal between losal and hisal
where empid not in (select distinct mnager from emp where mnager is not null);
-- 练习：查询员工数不少于5人的部门的所有员工：empno,ename,deptno
select empno,ename,deptno
from emp
where deptno in (
select distinct deptno
from emp
group by deptno
having count(empno)>=5
)
order by deptno
;
-- 查询基本工资高于30号部门任意员工的员工信息
select *
from emp
where sal >(select sal from emp
where deptno=30
order by sal asc
limit 1) and deptno!=30
;
select *
from emp
where sal>any(select sal from emp where deptno=30) and deptno!=30
;
-- 查询基本工资高于30号部门所有员工的员工信息
select *
from emp
where sal>(select max(sal) from emp where deptno=30) and deptno!=30
;
select * from dept;
-- 练习：查询员工数比CHICAGO少的部门的员工人数
#查询CHICAGO的员工人数
select count(empno)
from dept
left join emp on emp.deptno=dept.deptno
where loc=\'CHICAGO\';
#查询各部门的员工人数
select deptno,count(empno)
from emp
group by deptno;
select deptno,count(empno)
from emp
group by deptno
having count(empno)<(select count(empno)
from dept
left join emp on emp.deptno=dept.deptno
where loc=\'CHICAGO\');
-- from子查询
-- 查询各部门最高工资的员工：empno,ename,sal,deptno
select deptno,max(sal) as 最高工资
from emp
group by empto;
select *
from emp
left join (select deptno,max(sal) as 最高工资 from emp group by deptno) as a
on emp.deptno=a.deptno
where sal=最高工资
;
select deptno,job,count(empno)
from emp
group by deptno ,job
;
select deptno,concat(round(count(empno)/(select count(empno) from emp)*100,2),\"%\") as 人数占比
from emp
group by deptno
;
select replace(\'13512345678\',mid(\'13512345678\',4,4),repeat(\'*\',4));
select * from emp ;
-- 常用函数
-- 字符串函数
-- 练习：将每位员工的姓名首字母转换为大写
-- 日期函数
-- 练习：查询每位员工的工龄（年）：ename,hiredate,工龄
select * from emp;
use test1;
show tables;
select ename,hiredate,datediff(curdate(),hiredate)/365 from emp;
select ename,hiredate,floor(datediff(curdate(),hiredate)/365) 工龄 from emp;
-- 分组合并函数
-- 练习：查询各部门的员工姓名(姓名也可以进行排序)
select deptno,group_concat(distinct ename order by sal desc separator \"/\")
from emp
group by deptno
;
-- 逻辑函数
-- if函数：查询每位员工的工资级别：3000及以上为高，1500-3000为中，1500及以下为低
select empno,sal,if(sal>=3000,\"高\",if(sal>=1500,\"中\",\"低\")) as 工资级别
from emp
;
-- 逻辑表达式 case when ...then... else ... end
select *, case when sal>=3000 then \"高\" when sal>=1500 then \"中\" else \"低\" end as 工资级别
from emp
;
select *, case when sal>=3000 then \"高\" when sal>=2000 then \"中高\" when sal>1500 then \"中\" else \"低\" end as 工资级别
from emp
;
-- 开窗函数
-- 聚合函数用于开窗函数
##1、查询所有员工的平均工资，当over中没有指定分区（分组）、排序和滑动窗口，将整个表作为一个区，默认计算的是分区（分组）内所有的值
select *,avg(sal) over() from emp;
#2、查询各部门的平均工资,即over()那部门编号进行分区（分组），未进行指定排序和滑动窗口，结果按分区（分组）返回到每一条记录上
select *,avg(sal) as 平均工资 from emp;
select *,avg(sal) over(partition by deptno) as 平均工资 from emp;
#3、查询各部门的累计工资,即over()那部门编号进行分区（分组）和指定排序，未指定滑动窗口，结果按分区（分组）返回到每一条记录上且以每一分区(分组)为一个统计范围
select *,sum(sal) over (partition by deptno order by hiredate) as 累计工资 from emp;
select *,sum(sal) over (order by hiredate) as 累计工资 from emp;#未指定部门,则按每一个人进行累计alter
select *,sum(sal) over () as 累计工资 from emp;#未指定分区/排序/窗口,则只显示汇总值
#4、当over()中指定分区（分组）、排序、窗口时，则计算的是当前分区内当前行（当前窗口）的值
select *,avg(sal) over(partition by deptno order by hiredate rows between 1 preceding and 1 following) as 移动平均工资 from emp;#以当前行计算前一行和后一行的3个行的平均值
京公网安备 11010802022788号
