-- 工资等级
CREATE TABLE salgrade
( sal INT,
losal DOUBLE,
hisal DOUBLE );
INSERT INTO salgrade VALUES (1,700,1200);
INSERT INTO salgrade VALUES (2,1201,1400);
INSERT INTO salgrade VALUES (3,1401,2000);
INSERT INTO salgrade VALUES (4,2001,3000);
INSERT INTO salgrade VALUES (5,3001,9999);
alter table salgrade change sal grade int;
select * from salgrade;
select * from emp;
-- 笛卡尔积连接
use test;
select ename, dname, job, hiredate
from emp, dept
where emp.deptno = dept.deptno and job = 'manager';
select ename, dname, job, hiredate
from emp inner join dept on emp.deptno = dept.deptno and job = 'manager';
-- 查询每位员工的工资等级;empno,ename,sal.grade (不等值连接)
select empno, ename, sal, grade
from emp left join salgrade on sal
between losal and hisal;
select * from emp;
-- 查询每个工资等级的员工数
select grade, count(empno)
from salgrade left join emp on sal between losal and hisal
group by grade;
-- 查询所有员工姓名及其直属领导姓名
select 员工表.ename 员工姓名, 领导表.ename 领导姓名 from emp as 员工表 left join emp as 领导表 on
员工表.mgr = 领导表.empno;
-- 查询入职日期早于其直属领导的员工:empno,ename,dname
select * from emp;
select empno, ename, dname from
(select * from
(select 员工表.empno, 员工表.ename, 员工表.hiredate 员工入职日期, 领导表.hiredate 领导入职日期, 员工表.deptno
from emp as 员工表 left join emp as 领导表 on
员工表.mgr = 领导表.empno) as vo
where 员工入职日期 < 领导入职日期) as vt
left join dept on vt.deptno = dept.deptno;
select 员工表.empno, 员工表.ename, dept.dname
from emp as 员工表
left join emp as 领导表 on 员工表.mgr = 领导表.empno
left join dept on 领导表.deptno = dept.deptno
where 员工表.hiredate < 领导表.hiredate;
-- 子查询
-- 标量子查询
-- 查询基本工资高于公司平均工资的员工信息
select * from emp
where
sal > (select avg(sal) from emp);
-- where字句中不能使用聚合函数
-- 练习:查询和allen同一个领导的员工:empno,ename,job,mgr
-- 查询allen的直属领导工号
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 * from emp;
select job, deptno
from emp
where ename = 'smitz';
select empno, ename, job, deptno
from emp
where (deptno,job)=(20, 'clerk') and ename<>'smitz';
select empno, ename, job, deptno
from emp
where (deptno, job) =
(select deptno, job from emp where ename = 'smitz');
-- 列子查询
-- 查询普通员工的工资等级:ename, ename, sal, grade
select distinct mgr from emp where mgr is not null;
select ename, ename, sal, grade
from emp left join salgrade on sal between losal and hisal
where empno not in (select distinct mgr from emp where mgr is not null);
-- 练习,查询员工数不少于5人的部门的所有员工:ename, ename, deptno
select * from emp;
select deptno, count(empno) from emp group by deptno
having count(empno) >= 5;
select ename, ename, deptno from emp
where deptno = (select deptno from emp group by deptno
having count(empno) >= 5);
-- 查询基本工资高于30号部门任意员工的员工信息
select sal from emp where deptno = 30;
select * from
emp where sal > any(select sal from emp where deptno = 30) and deptno<>30;
# 查询30号部门员工的基本工资
select sal from emp where
deptno = 30;
-- 查询基本工资高于30号部门所有员工的员工信息
select * from
emp where sal > (select max(sal) from emp where deptno = 30) and deptno<>30;
-- from子查询
-- 查询各部门最高工资的员工:empno, ename, sal, deptno
select deptno, max(sal) from
emp group by deptno;
select empno, ename, sal ,deptno
from emp where sal in (select max(sal) from
emp group by deptno);
select empno, ename, sal ,emp.deptno
from emp
left join (select deptno, max(sal) max_sal from
emp group by deptno) as t
on emp.deptno = t.deptno
where sal = max_sal;
行子查询:=
列子查询:in/not in