MySQL笔记(四)[多表设计、一对多、多对多、多表查询内、外链接以及子查询]

一、多表的设计

多表的设计存在3种方式
1. 一对多
- 在多方案中，创建一个新的字段，作为当前表的外键，指向一方表的主键。
1. 多对多
- 先创建一张中间表，中间表中至少包含2个字段作为当前中间表的外键，指向原来多对多表的主键。
1. 一对一
- 一对一，一般不处理，放在同一张表中就可以。
学习：理论，怎么样来创建表结构(建表原则)

二、表的设计(一对多、多对多、一对一) 一对多(重要)

建表原则：分清楚一方和多方。在多方的表中添加一个字段，作为该表的外键，指向一方表的主键。

三、多对多(重要)

如果是多对多的关系，创建中间表。把表的关系拆成两个一对多。在中间表至少包含两个字段，作为该表的外键指向一方表的主键。

四、多表查询之内链接

前提条件：两个表有联系，通过外键关联。
普通内链接
1. 语法：关键字 ...inner join ... on 条件;
2. 注意：
- 在inner join关键字之前写表1
- 在inner join关键字之后写表2
- on的后面写条件：(表1是dept,表2是emp) dept.did = emp.dno
1. 语句： select * from dept inner join emp on dept.did = emp.dno;
隐式内链接(用的最多的)
- 语法：select ... from 表1,表2 where 表1.字段 = 表2.字段;
- 语句：select * from dept,emp where dept.did = emp.dno;
- 别名：select * from dept d,emp e where d.did = e.dno;
- 指定字段：select d.dname,e.ename,e.sal from dept d,emp e where d.did = e.dno;

五、多表查询之外链接

外链接又分成左外链接和右外链接
(1) 左外链接
- 使用关键字 select * from 表1 left outer join 表2 on 条件
- outer关键字省略不写
- select * from dept left outer join emp on dept.did = emp.dno;
- select * from dept left join emp on dept.did = emp.dno;
  (2)右外链接
- 使用关键字 select * from 表1 right outer join 表2 on 条件
- outer 关键字省略不写
- select * from dept right outer join emp on dept.did = emp.dno;
内链接和外链接的数据区别

六、子查询

图片说明
6.1 什么是子查询？

-- 需求：查询开发部中有哪些员工
select * from emp; 

-- 通过两条语句查询 select id from dept where name='开发部' ; 
select * from emp where dept_id = 1; 
-- 使用子查询 
select * from emp where dept_id = (select id from dept where name='市场部');

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

6.2 子查询结果的三种情况：
图片说明
6.3 子查询的结果是一个值的时候

子查询结果只要是单行单列，肯定在 WHERE 后面作为条件，父查询使用：比较运算符，如：> 、<、<>、 = 等
```
SELECT 查询字段 FROM 表 WHERE 字段=（子查询）; 
```

6.3.1 案例：查询工资最高的员工是谁？

-- 1) 查询最高工资是多少 select max(salary) from emp; 
-- 2) 根据最高工资到员工表查询到对应的员工信息 
select * from emp where salary = (select max(salary) from emp);

6.3.2 查询工资小于平均工资的员工有哪些？

-- 1) 查询平均工资是多少 select avg(salary) from emp; 
-- 2) 到员工表查询小于平均的员工信息 
select * from emp where salary < (select avg(salary) from emp);

6.4 子查询结果是多行单列的时候

子查询结果是单例多行，结果集类似于一个数组，父查询使用 IN 运算符
```
SELECT 查询字段 FROM 表 WHERE 字段 IN （子查询）; 
```

6.4.1 查询工资大于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);

6.4.2 查询开发部与财务部所有的员工信息

-- 先查询开发部与财务部的id 
select id from dept where name in('开发部','财务部'); 
-- 再查询在这些部门id中有哪些员工 
select * from emp where dept_id in (select id from dept where name in('开发部','财务 部'));

6.5 子查询的结果是多行多列子查询结果只要是多列，肯定在 FROM 后面作为表

SELECT 查询字段 FROM （子查询） 表别名 WHERE 条件; 
子查询作为表需要取别名，否则这张表没有名称则无法访问表中的字段

6.5.1 查询出2011年以后入职的员工信息，包括部门名称

-- 查询出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 ; 
-- 也可以使用表连接： 
select * from emp inner join dept on emp.`dept_id` = dept.`id` where join_date >='2011-1-1'; select * from emp inner join dept on emp.`dept_id` = dept.`id` and join_date >='2011-1-1';

6.6 子查询小结