多表联查
多个有关系的表可以同时查询,语法和单表查询基本一致
select
from 表1,表2,...
where 拼接条件 and 其它条件
group by
having
order by
limit;
注意:
1.from 表1,表2,...:笛卡尔积的结果
如果一张表不需要筛选,两张表以上,要做筛选,
n张表:n-1个筛选条件,关联的列值相等
2.如果查询多个表同名的列,需要表名来区分
3.建议多表联查时,每个列都加上表名做区分
4.可以给表取别名：FROM 表 别名,表 别名...一旦起别名,只能使用别名
笛卡尔积:
对应到表:
from 表1: 这张表的全部数据
from 表1,表2:
列:表1+表2
行:表1每一行和表2每一行拼接, 乘关系
from 多张表: 列相加 行相乘
多表联查--表连接方式
内连接
查询出符合条件的结果
语法1:简单方便
select 字段
from 表1,...,表n
where 拼接条件 and 其它条件;
语法2:
select 字段
from 表1 [inner] join 表2
on 拼接条件
inner join 表3
on 拼接条件
...
where 其它条件;
例:查询41号部门员工名字,职位,部门名
语法1:
select e.first_name,e.title,d.name
from s_emp e,s_dept d
where e.dept_id=d.id and e.dept_id=41;
语法2:
select e.first_name,e.title,d.name
from s_emp e inner join s_dept d
on e.dept_id=d.id
where e.dept_id=41;
外连接
基于内连接的结果
左外连接(掌握)
查询两张表,分为左表,右表
基于内连接的结果,同时包含左表未匹配的结果,右表对应的列值为null
语法:
select 字段
from 左表 left [outer] join 右表
on 拼接条件
where 其它条件;
例:查询"每个"客户的名字,对应的销售名字
select c.name,e.first_name
from s_customer c left outer join s_emp e
on c.sales_rep_id=e.id;
右外连接(可以用左外来替换,只要掌握左外即可)
查询两张表,分为左表,右表
基于内连接的结果,同时包含右表未匹配的结果,左表对应的列值为null
语法:
select 字段
from 左表 right [outer] join 右表
on 拼接条件
where 其它条件;
例:查询"每个"客户的名字,对应的销售名字
select c.name,e.first_name
from s_emp e right outer join s_customer c
on c.sales_rep_id=e.id;
全(外)连接(mysql不支持)
基于内连接的结果,同时包含左表未匹配的结果,右表未匹配的结果,另外一侧值是null
特殊的连接:自连接
自己和自己连接，如果列引用的是自己表的列,就要自己和自己连接查询
结果集集合运算符:
union union all
前提:两个结果集,列一致,顺序一致,类型相似
union:对两个结果集求并集,去重
union all:求并集,不去重, 叠加
查询的嵌套
查询中使用查询,内部的查询称为子查询
注意:查询中的查询要用()括起来通过查询的嵌套,可以是查询变得很复杂,但是实际开发中,不是越复杂越好,而是越简单越好,可以把嵌套查询拆分成多步来完成,由程序一步一步调用完成.
where中嵌套子查询
例：查询和‘Ben’同区域的员工
select *
from s_emp
where dept_id in(
select id
from s_dept
where region_id=(
select id
from s_region
where id=(
select region_id
from s_dept
where id=(
select dept_id
from s_emp
where first_name like 'Ben'
)
)
)
);
注意：
如果子查询返回多行 如果子查询返回一行相等用in 相等用=
大于 大于>
1）所有 >all(子查询)
>最大值 max
2）一个即可 >any(子查询)
>最小值 min
小于 小于<
1）所有 <all(子查询)
<最小值
2）一个即可<any(子查询)
<最大值
all,any效率不高
having中嵌套子查询
例：查询部门平均薪资低于公司平均薪资的部门
select dept_id
from s_emp
group by dept_id
having avg(salary)<(
select avg(salary)
from s_emp
);
from中嵌套子查询
结果集：行和列组成，可以把结果集当成表，再做查询
在mysql中,把一个结果集当做表,需要取别名
例：查询查询部门平均薪资低于公司平均薪资的部门编号，部门名
select d.id,d.name
from (
select dept_id
from s_emp
group by dept_id
having avg(salary)<(
select avg(salary)
from s_emp
)
) t,s_dept d
where t.dept_id=d.id;
未完待续......
