多表关系建立¶
一对多¶
脏数据¶
外键约束¶
注意外键约束的命名:fk开头_当前表名_键名(是一种规范)
这个标识表示它是外键了.
这样可以查看可视化界面
关联之后,如果违反了外键约束,那么就会报错.如删除某个还有员工的部门,就会报错.只能删除没有员工的部门.
图像化¶
在这里可以删除.同样的还可以添加
缺点与逻辑外键¶
实际上,很多企业都禁止使用物理外键了.
也就是说我们不要在数据里创建外键,而是应该在业务层就进行处理来保证外键约束了.
一对一¶
本质上也是一对多.外键可以任意放在任意表里.
注意,我们还是建议不要使用物理建议.
多对多¶
图形化
主表附表¶
在一对多的情况下,多的那些表可以认为是副表.副表的字段当中create_time和update_time是不需要的.因为其依附于主表.
多表查询¶
查询多张表
这样会进行笛卡尔积.
有效数据¶
这样就在笛卡尔积的基础上,过滤出有效的条目(也就是员工部门Id等于部门id的条目)
注意,如果某个员工的dept_id为null,那么是不会呈现在结果当中的.
分类¶
内连接¶
需求1¶
注意emp.id:因为两个表都有id字段,因此我们需要加个表名.
实际上,在多表查询的时候都建议把字段加上表名.这样更规范.(即使某个字段只在某一个表中出现,我们还是建议如此)
注意,如果某个员工的dept_id为null,那么是不会呈现在结果当中的.
这是因为,内连接是取两表的交集.既然是交集,那么null自然不会出现在结果当中.
注意,inner关键字是可以省略的.
需求2¶
显式处理查询条件:
起别名¶
由于有时候sql语句太过长:
这里as可以省略.这样起别名之后,后面就能直接使用表的别名来进行处理了.(注意,起了别名之后,之前的字段名都不能用了)
这里其别名的方式和之前给字段起别名的方式是类似的.
注意,别名的作用域是整个sql语句,也就是在as之前也有别名的作用的.
外连接¶
注意outer可以省略
表1就是左表,表2就是右表
需求1¶
注意,这里即使是null也会加进来,因为这里不再是交集了.
需求2¶
需求3¶
这个其实也可以变成右链接
注意两个表是可以互换位置的.
左外连接会包含左表的所有数据,右外链接会包含右表的所有数据.左外连接更常用.甚至有时候我们会专门把右连接转化成左连接来处理.
子查询¶
分类¶
- 标量子查询
- 列子查询
- 行子查询
- 表子查询
要领¶
样例¶
标量子查询¶
结果:
第二步:
合并:
- 示例2
列子查询¶
合并:
行子查询¶
合并:
由于嵌套的子查询会降低性能,因此这里两个子查询就很慢了
如何优化?
原本其实还有种写法:
因此可以这样:
本质上:行子查询返回的是一个行.因此可以通过()来接受一个行
表子查询¶
注意需要分组.分组之后,select后面的字段就只有分组字段和聚合函数了.
我们写的时候,可以先写select * from emp
由于要分组处理,求里面的数据:group by dept_id
然后处理select后面,看看究竟需要加什么
把查询结果当成一张表,然后处理.(起个别名)
案例¶
需求拆分1¶
我们首先定位需要哪些表:
由于我们不需要查询整张员工表或者部门表,因此,内连接.(内外连接的判断方式)
这个e.*注意一下.
需求2¶
需求3¶
这个是怎么写出来的?
先看看每个员工在什么部门:(很常见的操作了)
select * from emp e ,dept d where e.dept_id=d.id;
再按照部门分组,那么就很有结果了.
也就是说,实际上是对上面的新表进行操作.所以,使用count(*)即可.
最终:
需求4¶
需求5¶
注意,这里null也会当作一个部门来统计薪资.
接下来可见tlias3