数据库设计、三大范式、关联查询

一、数据库设计：
1、概述：
需求分析 - 需求分析师 -》 原始需求- > 抽取业务模型
图书模型：图书名称，版本号，作者
学生模型： 学号，学生姓名 手机号码
……
角色：学生，老师，图书管理员
《需求说明书》
需求设计 -
概要设计：
抽取实体：业务模型 -> 实体模型（java 类 c++类）内存
————–class Book{ name, bookNo,author }
数据库设计：业务模型/实体模型 - > 数据模型 (硬盘)
数据库表设计：问题： 如何设计？
详细设计：类详细，属性和方法
2、实例：
1）需求

2）分析：
a）一个工程可以由多个职工负责
b）一个职工可以负责多个工程
c）职工的工资率由职务决定
3）设计：

工程职工工时表
       工程号  工程名称  职工号  姓名  职务  薪水 工时

工程表：
    工程ID  工程号  工程名称 
      1            花园酒店   
      2            立交桥      

职工表：
    职工ID 职工号  姓名  职务ID 
     1      1     张三                
     2      2     李四

职务表：
    职务ID  职务名称  薪水

中间表（工程职工表）
     职工ID  工程ID   工时 
     1       1        13
     1       2        20
     2       1
     2       2

数据库想要设计好，只有通过多练习！！！

二、三大范式：
1）设计原则： 建议设计的表尽量遵守三大范式。
2）第一范式： 要求表的每个字段必须是不可分割的独立单元。

student:name              -- 违反第一范式
        张小名|狗娃                  
sutdent:name    old_name    --符合第一范式
        张小名    狗娃

3）第二范式： 在第一范式的基础上，要求每张表只表达一个意思。表的每个字段都和表的主键有依赖。

employee（员工）: 员工编号  员工姓名 部门名称   订单名称  
--违反第二范式

员工表：员工编号  员工姓名 部门名称   
订单表：  订单编号  订单名称            
-- 符合第二范式

4）第三范式： 在第二范式基础，要求每张表的主键之外的其他字段都只能和主键有直接依赖（绝对）关系。

员工表： 员工编号（主键） 员工姓名  部门编号  部门名 
--符合第二范式，违反第三范式（数据冗余高）                                                  

员工表：员工编号（主键） 员工姓名  部门编号  
部门表：部门编号  部门名  
--符合第三范式（降低数据冗余）        

5、注意：在实际建表的时候，可能因为一定原因（比如：为了方便）而使表的数据冗余提高，这也是可以的，所以一切要以实际情况以及需求而定，三大范式的规范可以适当的不遵守。

三、关联查询（多表查询）：
1、准备条件：

2、交叉连接查询（不推荐。产生笛卡尔乘积现象：4 * 4=16，有些是重复记录）
1）需求：查询员工及其所在部门(显示员工姓名，部门名称)
2）代码：

SELECT empName,deptName FROM employee,dept;

3）结果：

3、内连接查询：只有满足条件的结果才会显示(使用最频繁)
1）需求：查询员工及其所在部门(显示员工姓名，部门名称)
2）多表查询规则：a）确定查询哪些表 b）确定显示哪些字段 c）表与表之间连接条件 (规律：连接条件数量是表数量-1)
3）代码1：

SELECT empName,deptName       -- 2）确定显示哪些字段
    FROM employee,dept    -- 1）确定查询哪些表
    WHERE employee.deptId=dept.id  -- 3）表与表之间连接条件

代码2：

SELECT empName,deptName
    FROM employee
    INNER JOIN dept
    ON employee.deptId=dept.id;

代码3：

-- 使用别名（employee e属性名与别名间的AS可以省略）
SELECT e.empName,d.deptName
    FROM employee e
    INNER JOIN dept d
    ON e.deptId=d.id;

4）结果：

4、左[外]连接查询： 使用左边表的数据去匹配右边表的数据，如果符合连接条件的结果则显示，如果不符合连接条件则显示null。
1）需求：查询每个部门的员工
2）预期结果：

deptName    empName
软件开发部    张三
软件开发部    李四
应用维护部    王五
秘书部        null

3）代码：

SELECT d.deptName,e.empName
    FROM dept d
    LEFT OUTER JOIN employee e
    ON d.id=e.deptId;

4）结果：

5）注意： 左外连接：左表的数据一定会完全显示！注意OUTER可以省略。

5、右[外]连接查询：使用右边表的数据去匹配左边表的数据，如果符合连接条件的结果则显示，如果不符合连接条件则显示null。
1）代码：

SELECT d.deptName,e.empName
    FROM employee e
    RIGHT OUTER JOIN dept d
    ON d.id=e.deptId;

2）注意： 右外连接：右表的数据一定会完成显示！
3）提醒：左外连接与右外连接可以相互转换，掌握一个，另一个自然迎刃而解。

6、自连接查询
1）需求：查询员工及其上司
2）准备条件：

2）预期结果：

empName   empName
  张三     null
  李四     张三
  王五     李四

3）代码：

--使用到了自连接和左外连接
SELECT e.empName,b.empName
    FROM employee e 
    LEFT OUTER JOIN employee b
    ON e.bossId=b.id;

4）结果：