多表设计和多表查询

一、外键约束

表是用来保存现实生活中的数据的，而现实生活中数据和数据之间往往具有一定的关系，我们在使用表来存储数据时，可以明确的声明表和表之前的依赖关系，命令数据库来帮我们维护这种关系，向这种约束就叫做外键约束。

create table dept(
    id int primary key auto_increment,
    name varchar(20)
);
insert into dept values(null,'财务部'),(null,'人事部'),(null,'销售部'),(null,'行政部');

create table emp(
    id int primary key auto_increment,
    name varchar(20),
    dept_id int,
    foreign key(dept_id) references dept(id)
);
insert into emp values(null,'奥巴马',1),(null,'哈利波特',2),(null,'本拉登',3),(null,'朴乾',3);

二、多表设计

一对多:在多的一方保存一的一方的主键做为外键

一对一:在任意一方保存另一方的主键作为外键

多对多:创建第三方关系表保存两张表的主键作为外键,保存他们对应关系

三、多表查询

笛卡尔积查询:将两张表的记录进行一个相乘的操作查询出来的结果就是笛卡尔积查询,如果左表有n条记录,右表有m条记录,笛卡尔积查询出有n*m条记录,其中往往包含了很多错误的数据,所以这种查询方式并不常用
select * from dept,emp;

内连接查询:查询的是左边表和右边表都能找到对应记录的记录
select * from dept,emp where dept.id = emp.dept_id;
select * from dept inner join emp on dept.id=emp.dept_id;

外连接查询:

左外连接查询:在内连接的基础上增加左边表有而右边表没有的记录
select * from dept left join emp on dept.id=emp.dept_id;

右外连接查询:在内连接的基础上增加右边表有而左边表没有的记录
select * from dept right join emp on dept.id=emp.dept_id;

全外连接查询:在内连接的基础上增加左边表有而右边表没有的记录和右边表有而左表表没有的记录
select * from dept full join emp on dept.id=emp.dept_id; – mysql不支持全外连接

可以使用union关键字模拟全外连接:

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