多表操作

第一章 外键

在实际开发项目中，一个健壮的数据表一定有很好的参照完整性，为保证数据的完整性，需将两表建立关系。这时可通过外键约束来实现

1.1、介绍

什么是外键约束？

在另一张表中引用另一张表的主键约束或唯一约束。

例如：如下操作创建表

create table grade(
	id int primary key,
    name varchar(36)
);

create table student(
	sid int primary key,
    sname varchar(36),
    gid int not null,
    foreign key(gid) references grade(id)
);

从上述创建表可知，学生表中引用班级表中的主键，从而两表有了联系。两表有了主从关系。

主表：被引用的表，如 grade 表

从表：引用表，如 student 表

注意：引用外键后，外键列只能插入参照列存在的值，且删除数据时，只能先删除 student 表中的数据，再删除 grade 表的数据

1.2、外键约束

还可以在表创建后来创建外键约束。

语法：

alter table 表名 add constraint 外键约束名 foreign key(外键字段) references 外表(主键字段)
[on delete [cascade|set null | no action | restrict]]
[on update [cascade|set null | no action | restrict]]

需要注意的是建立外键的表必须是 innoDB 型，不能为临时表。

定义外键约束名时，不能加引号。

[]:可选项，当删除主表时，从表中的数据也应该删除，各参数如下：

参数	说明
cascade	删除包含有参照关系的记录
set null	使用null替换删除含有参照关系的字段数据，前提 字段约束不为 not null
no action	不进行任何操作
restrict	默认配置，拒绝删除主表或修改外键关联列

1.3、删除外键

当有业务需求需要删除表间的关联关系时，需要删除外键约束

语法：

alter table 表名 drop foreign key 外键约束名;

第二章 关联表

2.1、关联关系

• 多对一
  • 是常见的一种关系，在多对一的表关系中，外键会建在多的一方，否则会造成数据的冗余。
• 多对多
  • 会常见中间表，该表会存有 两个外键。
• 一对一
  • 常见于保存临时的数据表中

注意:在两个具有关联表中删除数据时，一定要先删除从表数据，再删除主表数据。

第三章 连接查询

3.1、交叉连接

也是进行笛卡尔积运算。

语法：

select * from 表1 cross join 表2;

结果是两表列的乘积。

不常见。

3.2、内连接

又称简单连接或自然连接，是常见的一种连接查询

通过表中共有字段进行连接运算

语法：

select 查询字段 from 表1 [inner] join 表2
on 表1.字段 = 表2.字段

还有一种连接为自连接，是内连接特殊的存在，是在同一张表上进行的连接运算。

语法：

select 查询字段 from 表1 join 表2
on 表1.字段 = 表2.字段

例如：

select * from emp e1 join emp e2
on e1.字段 = e2.字段

3.3、外连接

查询两张表中另一张表的数据加上本身的数据。也就是说查询结果中以一张表为主表，在另一张表为从表中进行匹配，

语法：

select 查询字段 from 表1 left|right [outer] join 表2
on 表1.字段 = 表2.字段 
where
.....

1、左外连接

语法：

select 查询字段 from 表1 left join 表2
on 表1.字段 = 表2.字段 
where
.....

左边的表为主表，右边为从表，以主表为主，从表为辅

如果主表在从表中没有匹配，则以null代替

2、右外连接

语法：

select 查询字段 from 表1 right join 表2
on 表1.字段 = 表2.字段 
where
.....

右边的表为主表，左边为从表，以主表为主，从表为辅

如果主表在从表中没有匹配，则以null代替

3.4、复合连接

在连接查询中，添加过滤条件来限制查询结果，使查询结果更加精确。例如 在链家查询中添加 where、group by、order by等操作。

第四章 子查询

4.1、带 in 查询

内层查询语句返回集合，集合中的元素供外层查询进行比较

例如：查询存在年龄为20 岁的员工部门

select * 
from dept 
where did in (
	select did
   	from emp where age=20
);

其中 in 前还可以添加 not ，表示不再其中，与 in 相反

4.2、带 exists 查询

true 、false

当为true 时，外层查询才会执行

例如：查询 emp 表中是否存在年龄大于 21 岁的员工

select *
from dept
where exists(
	select did
    from emp where age > 21
);

exists 比 in 关键字的运行效率高，在实际开发中，尤其是数据量大时，推荐使用

4.3、带 any 查询

表示满足其一即可

例如：

select *
from dept
where did > any(
	select did from emp
);

4.4、带 all 查询

全都满足

例如：

select *
from dept
where did > all(
	select did from emp
);

4.5、比较运算符查询

'<','>','=','<=','>=','!='