多表操作

表与表的关系

一对一

不再介绍，因为一对一可以创建成一张表。

一对多

与上一节外键约束中描述内容相同。

多对多

在创建中间表时添加对两张表的主键约束
-- 创建复合主键
primary key(rid,uid),
foreign key (rid) references tab_route(rid),
foreign key(uid) references tab_user(uid)

数据库设计

数据规范化

• 什么是范式：
  好的数据库设计对数据的存储性能和后期的程序开发，都会产生重要的影响。建立科学的，规范的数据库就需
  要满足一些规则来优化数据的设计和存储，这些规则就称为范式。

• 三大范式：
  目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF） 、
  第四范式(4NF）和第五范式（5NF，又称完美范式）。
  满足最低要求的范式是第一范式（1NF）。在第一范式的基础上进一步满足更多规范要求的称为第二范式（2NF） ，
  其余范式以次类推。一般说来，数据库只需满足第三范式(3NF）就行了。

• 1NF 第一范式每一列不可再拆分，称为原子性.

• 2NF 第一范式的基础上所有列完全依赖于主键列,不能依赖于其他列。(类似于一次函数，只能有一个变量)

• 3NF在满足第二范式的前提下，表中的每一列都直接依赖于主键（y = f(a)），而不是通过其它的列来间接依赖于主键 (y = f(a,b))。

多表查询

• 作用：一条 SQL 语句查询多张表，因为查询结果在多张不同的表中。每张表取 1 列或多列。
• 笛卡尔积现象
  • 左表每条数据与右表每条数据的组合效果
  • 清除笛卡尔积：
    我们发现不是所有的数据组合都是有用的，所以需要通过条件过滤掉没用的数据。
• 分类
  • 内连接
    • 隐式内连接
    • 显式内连接
  • 外连接
    • 左外连接
    • 右外连接

内连接

• 使用条件：显示内连接比隐式内连接的扫描速度快，当需要 3 张表以上的连接时推荐使用显式内连接。

• 总结内连接查询步骤：

1. 确定查询哪些表
2. 确定表连接的条件
3. 确定查询的条件
4. 确定查询的字段

隐式内连接

 select 字段名 from 表1,表2 [ ,表3... ] where 连接条件 [and 查询/连接条件 ... ];
	select * from emp,dept where emp.`dept_id` = dept.`id`;

显示内连接

select 字段名 from 表1 join 表2 on 连接条件 [ join 表3 on 连接条件 ... ] [ where 查询条件 ... ];
	-- 查询唐僧的信息，显示员工 id，姓名，性别，工资和所在的部门名称
	select 
		e.`id`,e.`name`,e.`gender`,e.`salary`,d.`name` 
	from emp e inner join dept d 
	on 
		e.`dept_id` = d.`id` where e.`name`='唐僧';
	需要查询的表：emp表 dept 表
	连接条件：e.`dept_id` = d.`id` 时，表有效
	查询条件：e.`name`='唐僧'，查询唐僧的信息

左外连接

• 右外连接与左外连接同属于外连接，但右外连接与左外连接基本相同，就不再叙述，使用时仅需要
  将左外连接中的left转换成right即可
• 在内连接的基础上保证左表的数据全部显示

SELECT  字段名 FROM  左表 LEFT [OUTER] JOIN  右表 ON  条件
	-- 使用内连接查询
	select * from dept d inner join emp e on d.`id` = e.`dept_id`;
	
	-- 使用左外连接查询
	select * from dept d left join emp e on d.`id` = e.`dept_id`;

子查询(select 语句的嵌套)

• 子查询的概念：

1. 一个查询的结果做为另一个查询的条件
2. 有查询的嵌套，内部的查询称为子查询
3. 子查询要使用括号

• 子查询的分类
  • 查询结果单行单列（一个返回值）
  • 查询结果单行多列（返回一个一维数组）
  • 查询结果多行多列 （返回一个多维数组）

子查询结果单行单列

• 在 WHERE 后面作为条件，父查询使用：比较运算符，如：> 、<、<>、= 等

SELECT  查询字段 FROM  表 WHERE  字段= （子查询）;
 -- 查询工资最高的员工是谁？
 select * from emp where salary = (select max(salary) from emp);

子查询结果多行单列

• 结果集类似于一个数组，父查询使用 IN运算符

SELECT  查询字段 FROM  表 WHERE  字段 IN  （子查询）;
	-- 查询工资大于 5000 的员工，来自于哪些部门的名字
	-- 先查询大于 5000 的员工所在的部门 id
	select dept_id from emp where salary > 5000;
	-- 再查询在这些部门 id 中部门的名字 Subquery returns more than 1 row
	select name from dept where id = (select dept_id from emp where salary > 5000);
	select name from dept where id in (select dept_id from emp where salary > 5000);

子查询结果多行多列

• 在 FROM 后面作为表

SELECT  查询字段 FROM  （子查询）  表别名 WHERE  条件;
	-- 查询出 2011 年以后入职的员工信息，包括部门名称
	-- 在员工表中查询 2011-1-1 以后入职的员工
	select * from emp where join_date >='2011-1-1';
	-- 查询所有的部门信息，与上面的虚拟表中的信息组合，找出所有部门 id 等于的 dept_id
	select * from dept d, (select * from emp where join_date >='2011-1-1') e where
	d.`id`= e.dept_id ;