MySQL基础(四)

数据库设计三范式

第一范式：任何一张表都应该有主键，并且每一个字段原子性不可再分

第二范式：建立在第一范式的基础上，非主键字段完全依赖主键，不能产生部分依赖 多对多，三张表，关系表两个外键

第三范式：建立在第二范式的基础上，所有非主键字段不能传递依赖于主键字段 一对多，两张表，多的表加外键

在实际开发中，以满足客户需求为主，有时候会拿冗余换执行速度

一对一表的设计：主键共享或者外键唯一

练习题：查询各部门工资最高的人

select
    e.name, t.*
from 
    emp e
join
    (select deptno, max(sal) as maxsal from emp group by deptno) t
on 
    t.deptno = e.deptno and t.maxsal = e.sal;

练习题：不用max选出收入最高的人的两种方法

第一种：自连接  第二种：order+limit

select 
    sal
from
    emp
where
    sal not in(select distinct a.sal from emp a join emp b on a.sal < b.sal)

练习题： 列出薪酬高于公司平均薪酬的所有员工，所在部门，上级领导，雇员的薪资等级

select 
    e.name'员工', d.dname, l.ename'领导', s.grade
from 
    emp e
join 
    dept d
on
    e.deptno = d.deptno
left join
    emp l
on
    e.mgr = l.empo
join
    salgrade s
on
    e.sal between s.losal and s.hisal
where
    e.sal > (select avg(sal) from emp); 

练习题：列出薪酬等于部门30员工的薪酬的其他员工的姓名和薪酬

select 
    ename,sal
from
    emp
where 
    sal in (select distinct sal from emp where deptno = 30)
and
    deptno <> 30;

练习题：列出每个部门工作的员工数量，平均工资和平均服务期限

select 
    d.deptno, count(e.name), ifnull(avg(e.sal),0), ifnull(avg(timestampdiff(YEAR,hiredate,now())), 0)
from 
    emp e
right join 
    dept d
on
    e.deptno = d.deptno
group by
    d.deptno