mysql（四）（多表查询、外键约束、多表设计、内连接、外连接、子查询）

多表查询

 

外键约束的概念

 

两个表，部门表和员工信息表

 

一个部门可以有多个员工，一个员工只能属于一个部门，因此希望在两个表之间创建关系，我们可以在员工表后面加上员工所属的部门id

如果此时删除部门表中的004号部门，员工表中004号部门的信息会依然存在，这就会出现问题。因此应该设置外键，使得员工表中的部门id与部门表中的部门id产生联系。

 

外键——可以明确的声明表示表和表之间关系的字段的参照关系，使数据库帮我们维护这种关系。这种键叫做在表和表之间声明了一个外键，

如果在后续的操作中，由于新增修改删除造成破坏了外键约束的状态发生时，数据库会检测到这种状态从而阻止这类操作的进行。

 

设置外键之后，部门表中删除004号部门会被阻止，因为员工表还有相应的数据；同理，在员工表插入员工，使得员工属于005号部门也会被阻止的，因为部门表中没有005号部门。

 

 

 

添加外键约束

 

1、建表时添加

利用关键字 foreign key 来建立外键，保存对方的主键

创建部门表和员工表，使得员工表中的dept_id与部门表的id字段建立外键

建立外键之后，如果先删除dept表，就会报错。要先删除emp表

 

 

2、在表已经存在的情况下，利用alter来创建外键

建立emp与dept表之间的外键连接。外键别名为FK_ID（自己起的）

 

 

 

删除外键约束

 

 

 

---

多表设计

 

1对1的表

对于1对1关系的表，可以在任意一方设计外键，保存对方的主键

 

 

多对多的表

对于多对多的关系，需要新建一张第三方关系表，保存两张表的主键作为外键，存储两张表主键之间的对应关系。

 

 

---

多表查询

 

笛卡尔集

笛卡尔集行数 =  表1的行数 * 表2的行数

              列数 = 表1的列数 + 表2的列数

2张表的连接条件需要一个

N张表的连接条件需要N-1

 

 

内连接查询

查询出左边表有，右边表也有的数据记录，下面两句等效

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

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

 

 

外连接查询

 

左外连接查询——left join

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

在内连接的基础上+左边表有而右边表没有的记录

（适应场景——就是左边表有的部门，右边表中并不存在该部门的员工，但是部门是存在的，因此不能丢弃）

 

右外连接查询——right join

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

在内连接的基础上+右边表有而左边表没有的记录

 

全外连接查询——full join——mysql不支持full join

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

在内连接的基础上+左边表有而右边表没有的记录+右边表有而左边表没有的记录

 

那么如何解决？使用union实现全外链接查询

 

只查询4号部门的员工和姓名的例子

 

 

 

 

子查询

 

in和not in

使用in关键字进行子查询时，内层查询语句仅仅返回一个数据列，这个数据列中的值将供外层查询语句进行比较操作。

使用not in关键字进行查询

首先建立两张表

 

1、查询存在年龄为20岁的员工的部门

 

2、查询不存在年龄为20的员工的部门

 

 

 

exists

exists关键字后面的参数可以是任意一个子查询，这个子查询的作用相当于测试，他不产生任何数据，只返回true或false。当返回值为true时，外层查询才会执行

 

3、查询employee表中是否存在年龄大于21岁的员工，如果存在，则查询department表中的所有记录

 

 

 

any

any关键字表示满足其中任意一个条件，他运行创建一个表达式对子查询的返回值列表进行比较，只要满足内层子查询中的任意一个比较条件，就返回一个结果作为外层查询条件。

上图中，只有网络部的did值无法大于employee.did中的任意一个值，因此不显示网络部。

 

 

 

all

带all关键字的子查询返回的结果需同时满足所有内层查询条件。

上图中只有“啦啦啦”的did值大于employee表中任意的did值。

 

 

 

带比较运算符的子查询

 

使用带比较运算符的子查询，查询赵四是哪个部门的员工。