昨日作业
- 4.求挣最高薪水的员工（BOSS除外）的姓名
select ename, job, sal from tb_emp
where sal=(select max(sal) from tb_emp
where mgr is not null);
- 5.查询薪水超过平均薪水的员工的姓名和工资
select ename, sal from tb_emp
where sal>(select avg(sal) from tb_emp);
- 6.查询薪水超过其所在部门平均薪水的员工的姓名和工资
select ename, sal, avgSal from
(select dno, avg(sal) as avgSal from tb_emp group by dno) as t1,
tb_emp as t2
where t1.dno=t2.dno and t2.sal>t1.avgSal;
- 6.1查询平均薪水不低于2500的部门中薪水超过其所在部门平均薪水的员工的姓名和工资
-- 在分组后做筛选是在group by 加 having
select t1.dno, ename, sal, avgSal from tb_emp as t1,
(select dno, avg(sal) as avgSal from tb_emp
group by dno having avgsal>=2500) as t2
where t1.dno=t2.dno and sal>avgSal;
- 7.查询部门中薪水最高的人姓名、工资和所在部门名称
select dname, ename, sal from
(select t1.dno, ename, sal
(select dno, max(sal) as maxSal from tb_emp
group by dno) as t1, tb_emp as t2
where t1.dno=t2.dno and sal=max(sal)) as t3,
tb_dept as t4 where t3.dno=t4.dno;
select dname, ename, sal from
(select t1.dno, ename, sal from
(select dno, max(sal) as maxSal from tb_emp
group by dno) as t1 inner join tb_emp as t2
on t1.dno=t2.dno and sal=maxSal) as t3
inner join tb_dept as t4 on t3.dno=t4.dno;
- 内连接只能查询到满足条件的
select dname, total from
(select dno, count(dno) as total from tb_emp
group by dno) as t1 inner join tb_dept as t2
on t1.dno=t2.dno;
- 外连接
- 右外连接 把右表不满足也查询出来，补空值
select dname as 部门名称, ifnull(total, 0) as 总人数 from
(select dno, count(dno) as total from tb_emp
group by dno) as t1 right join tb_dept as t2
on t1.dno=t2.dno;
全外连接 把所有表不满足条件的也查询出来
8.哪些人是主管
-- distinct 去重
select ename from tb_emp
where eno in (
select distinct mgr from tb_emp
where mgr is not null);
杂项
产品经理/UI/UE
--- 界面原型
Axure RP / Sketch / Briefs / mockups
UML
--- ROSE / StarUML / EA（Enterprise Architect）
数据库设计
--- ER图 PowerDesigner
