多表联查

多个有关系的表可以同时查询,语法和单表查询基本一致

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;

未完待续......