目录
引入
为什么要使用多表查询?
例如,实际开发中,我需要一个商品的数据,但是这个商品的数据不会再一张表进行存储,可能会存在几张表里。单表查询是不能查到完整数据的,所有需要多表查询。
正题
要进行多表查询,首先要对两个表或两个以上表(最多3个,多了会影响查询效率)进行连接
多表查询的分类:
1.等值连接 vs 非等值连接
等值连接与非等值连接的划分是根据连接条件,连接条件 a.id=b.id(是等号=)就是等值连接
否则就是非等值连接
非等值连接例子:
select last_name,salary,grade_level
from employ,job where employ >= job.lowest_sal
2.自连接与非自连接
自连接与非自连接是根据连接的表是否是自己本身,如果是自己与自己连接就是自连接
自连接例子
select id, name,salary from employ e1,employ e2
3。内连接与外连接
内连接,内连接就是默认的连接,
说明:结果集中只包含满足条件的,不包含两个表之间不匹配的行
select id,name from employee e,departments d where e.id=d.id;
外连接:结果集包含左表或右表不满足条件的行
左外连接:包含左表不满足条件的行记录
右外连接:包含右表不满足条件的行记录
外连接练习例子
练习:查询所有的员工的name,depart信息
员工和部门是两个表,所有的包含没有部门的(其不满足条件的),是外连接
SQL语法 实现多表连接:
1.SQL92使用+(mySql不支持,oracle支持)
2.SQL99使用join ...on方式
cross join(交叉连接,笛卡儿积)
cross join 相当于 ,on条件可以直接写在where后
内连接
select name ,depart from employee e join depart d on e.id=d.id
join 要连接的表名 on 连接条件
注意: 内连接 是 inner join ,只不过inner 可以省略,join 就代表了内连接
natural join 自然连接,是特殊的自然连接特点,去重,要求俩表有相同属性
外连接
全外连接 full join
左外连接和右外连接的并集
知识小贴士:在MySQL中是不允许full join, 可以使用union是全外连接mysql 不支持
左外连接(left join)
左外连接会保留左表的全部记录,相当于在左表的基础上加入右表满足 on 条件的记录,空位以NULL填充
select name ,depart from employee e left join depart d on e.id=d.id
右外连接(right join)
右外连接会保留右表的全部记录,相当于在右表的基础上加入左表满足 on 条件的记录,空位以NULL填充
select name ,depart from employee e right join depart d on e.id=d.id
7.种JOIN
UNION关键字
把多条select结果组合成单个结果集
条件:select对应的表的列数和数据类型必须相同,且相互对应。
union操作符 查询结果的并集,去除重复数据
union all操作符 并集,不去重
union all推荐使用效率高
左中图实现:
select id ,name,depart from employee e left join depart d on
e.id=d.id where d.id is null;
左下图实现:
左上图union all 右中图
新特性
自然连接
natural join : 自动查询两张表中所有相同字段进行等值连接
select id,name,depart from employ e natural join depart
using连接:在{}放入同名字段
select id,name,depart from employ e join depart using {id}
注意:要控制多表连接的数量,多表连接相当于嵌套for循环,会使性能下降,推荐不超过三个。
SQL题
表中三个字段 id ,日期,销售额,要求找出比前一天销售额更高的数据?
题解
解题必备小知识:
-
计算时间函数
-
datediff(日期1,日期2),返回结果日期1,与日期2相差天数(日期1-日期2)
-
timestampdiff(时间类型,日期1,日期2),返回对应类型的差值(日期2-日期1)
-
cross join 进行交叉连接(笛卡尔积,然后根据on的条件进行过滤,最总就是结果集),cross join 相当于 ,on条件可以直接写在where后
解题逻辑
将表进行自连接,然后根据昨天(天数差值为1),销售额大于,两个条件进行过滤,然后就是所求的结果集。现在有个疑问是否要进行去重?比较简单的是举个例子自己试一下,还有不管去不去重我加上总归没毛病。这个题是不用去重的(有时间差值这个条件)。
-