昨日作业

• 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