Java之路-MySql6范式和多表查询

一、范式

范式是指设计数据库时，需要遵循的一些规范。目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。我们日常需要遵循到第三范式

第一范式（1NF）：第一范式每一列不可再拆分，称为原子性

第二范式（2NF）：，第二范式就是在第一范式的基础上所有列完全 26 / 26 依赖于主键列

第三范式（3NF）：任何非主列不得传递 依赖于主键，所谓传递依赖，指的是如果存在"A → B → C"的决定关系，则 C 传递依赖于 A。

二、多表查询

多表查询指多个表的数据整合成一张表展示。比如我们的员工表和部门表组成一个既可以看到员工信息又能看到部门信息的员工部门表。

常见的多表查询的分类如上图所示

1.内连接

用左边表的记录去匹配右边表的记录，如果符合条件的则显示。如：从表.外键=主表.主键

1.1.隐式内连接

隐式内连接：看不到 JOIN 关键字，条件使用 WHERE 指定

格式：SELECT 字段名 FROM 左表, 右表 WHERE 条件

select * from emp,dept where emp.`dept_id` = dept.`id`;

1.2.显式内连接

隐式内连接：使用 INNER JOIN ... ON 语句, 可以省略 INNER

格式：SELECT 字段名 FROM 左表 [INNER] JOIN 右表 ON 条件

select * from emp e inner join dept d on e.`dept_id` = d.`id` where e.`name`='唐僧 ';

2.外连接

外联接可以是左外联接、右外联接。

2.1.左外连接

用左边表的记录去匹配右边表的记录，如果符合条件的则显示；否则，显示 NULL，使用 LEFT OUTER JOIN ... ON，OUTER 可以省略

格式：SELECT 字段名 FROM 左表 LEFT [OUTER] JOIN 右表 ON 条件

select * from dept d left join emp e on d.`id` = e.`dept_id`;

2.2.右外联接

用右边表的记录去匹配左边表的记录，如果符合条件的则显示；否则，显示 NULL，使用 RIGHT OUTER JOIN ... ON，OUTER 可以省略

格式：SELECT 字段名 FROM 左表 RIGHT [OUTER ]JOIN 右表 ON 条件

select * from dept right join emp on dept.`id` = emp.`dept_id`;

3.子查询

一个查询的结果做为另一个查询的条件，有查询的嵌套，内部的查询称为子查询，比如查询工资最高的员工信息

-- 1 查询最高的工资是多少 9000

SELECT MAX(salary) FROM emp;

- 2 查询员工信息，并且工资等于9000的

SELECT * FROM emp WHERE emp.`salary` = 9000;

-- 一条sql就完成这个操作。子查询

SELECT * FROM emp WHERE emp.`salary` = (SELECT MAX(salary) FROM emp);

3.1.子查询的结果是单行单列时

子查询可以作为条件，使用运算符去判断。 运算符： > >= < <= =

SELECT * FROM emp WHERE emp.salary < (SELECT AVG(salary) FROM emp);

3.2.子查询的结果是多行单列时

SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE NAME = '财务部' OR NAME = '市场部');

3.3. 子查询的结果是多行多列时

SELECT * FROM dept t1 ,(SELECT * FROM emp

WHERE emp.`join_date` > '2011-11-11') t2 WHERE t1.id = t2.dept_id;