-- 查询各地区的员工数
-- 员工表是主表
-- 要优先保证员工数量的完整性
select count(empno),dept.deptno,loc
from emp left join dept on emp.deptno=dept.deptno
group by loc;
use test;
select * from dept;
-- 查询员工数多于5人的地区
select count(empno)员工数,dept.deptno,loc
from emp left join dept on emp.deptno=dept.deptno
group by emp.deptno
having count(empno)>5;
-- 查询平均工资最高的部门：deptno,dname,平均工资
select emp.deptno,dname,avg(sal)
from emp left join dept on emp.deptno=dept.deptno
group by emp.deptno
order by avg(sal) desc
limit 1;
select t.dname,deptno,max(平均工资)
from
(select emp.deptno,dname,avg(sal)平均工资
from emp left join dept on emp.deptno=dept.deptno
group by emp.deptno) t;
-- 合并查询
create table class1(
s_id varchar(2),
s_name varchar(5),
score int);
create table class2(
s_id varchar(2),
s_name varchar(5),
fenshu int);
insert into class1 values(\'s1\',\'林一\',95),
(\'s2\',\'刘二\',55),
(\'s3\',\'张三\',80),
(\'s4\',\'李四\',69);
insert into class2 values(\'s5\',\'赵大\',87),
(\'s2\',\'刘二\',55),
(\'s6\',\'张三\',76),
(\'s7\',\'王五\',92);
select * from class1;
select * from class2;
-- union去重
select * from class1
union
select * from class2;
-- union all不去重
select * from class1
union all
select * from class2;
-- 子查询
-- 子查询
-- 标量子查询：
-- 查询基本工资高于公司平均工资的员工信息
-- 比较运算符后面不能直接跟计算函数
SELECT
AVG(sal)
FROM
emp;
SELECT
*
FROM
emp
WHERE
sal > (SELECT
AVG(sal)
FROM
emp);
-- 练习：查询和allen同一个领导的员工：empno,ename,job,mgr
SELECT
mgr
FROM
emp
WHERE
ename = \'allen\';
SELECT
empno, 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
empno, ename, job, deptno
FROM
emp
WHERE
(deptno , job) = (SELECT
deptno, job
FROM
emp
WHERE
ename = \'smith\')
AND ename <> \'smith\';
-- 列子查询：
-- 查询员工数不少于5人的部门的所有员工：empno,ename,deptno
SELECT
deptno, COUNT(empno)
FROM
emp
GROUP BY deptno
HAVING COUNT(empno) >= 5;
SELECT
empno, ename, deptno
FROM
emp
WHERE
deptno IN (SELECT
deptno
FROM
emp
GROUP BY deptno
HAVING COUNT(empno) >= 5)
;
-- !!!练习：查询普通员工的工资等级：empno,ename,sal,grade
SELECT
*
FROM
emp;
SELECT DISTINCT
mgr
FROM
emp
WHERE
mgr IS NOT NULL;
SELECT
empno, ename, sal, grade
FROM
emp
LEFT JOIN
salgrade ON sal BETWEEN minimum AND maximum;
SELECT
empno, ename, sal, grade
FROM
emp
LEFT JOIN
salgrade ON sal BETWEEN minimum AND maximum
WHERE
empno NOT IN (SELECT DISTINCT
mgr
FROM
emp
WHERE
mgr IS NOT NULL)
;
-- 查询基本工资高于30号部门任意员工的员工信息
-- any/min
-- 30部门的工资
select sal
from emp
where deptno=30;
select *
from emp
where sal>any(select sal
from emp where deptno=30) and deptno<>30;
select min(sal)
from emp
where deptno=30;
select *
from emp
where sal>(select min(sal) from emp where deptno=30);
-- 查询基本工资高于30号部门所有员工的员工信息
select max(sal) from emp where deptno=30;
select *
from emp
where sal>(select max(sal) from emp where deptno=30) and deptno<>30;
select *
from emp
where sal>all(select sal
from emp where deptno=30);
-- 练习：查询基本工资高于chicago地区所有员工的员工姓名及其所在地区
select sal
from emp left join dept on emp.deptno=dept.deptno
where loc=\'chicago\';
select ename,loc,sal
from emp left join dept on emp.deptno=dept.deptno
where sal>all(select sal from emp left join dept on emp.deptno=dept.deptno where loc=\'chicago\');
-- from子查询
-- 查询各部门最高工资的员工：empno,ename,sal,deptno
select deptno,max(sal)最高工资 from emp group by deptno;
select *
from emp left join (select deptno,max(sal) 最高工资 from emp group by deptno)t
on emp.deptno=t.deptno
where sal=最高工资;
-- 练习：查询平均工资最高的部门
select avg(sal),deptno from emp group by deptno;
select deptno,max(平均工资)
from (select avg(sal)平均工资,deptno from emp group by deptno)t;