若一个查询同时涉及到两个以上的表,称为连表查询

准备表

create table department(
id int auto_increment PRIMARY KEY,
name varchar(20) 
);

department

 

 create table employee(
id int primary key auto_increment,
name varchar(20),
sex enum(\'male\',\'female\') not null default \'male\',
age int,
dep_id int,
FOREIGN key (dep_id) references department(id)
on DELETE cascade 
on update cascade
);

employee 表

 

插入数据

insert into department values
(200,\'技术\'),
(201,\'人力资源\'),
(202,\'销售\'),
(203,\'运营\');

insert into employee(name,sex,age,dep_id) values
(\'egon\',\'male\',18,200),
(\'alex\',\'female\',48,201),
(\'wupeiqi\',\'male\',38,201),
(\'yuanhao\',\'female\',28,202),
(\'liwenzhou\',\'male\',18,200),
(\'jingliyang\',\'female\',18,203)
;

数据

 

join连表操作

employee 表

department 表

 

1.把这两张表有关联的部分显示出来

内连接(inner join ) :按照on后边的条件只去两张表有关联的部分,没关联就不显示

内连接有两种方法:

# 等值连接方法
select * from employee,department  where department.id =employee.dep_id  特点: 这种方法这把这两张表发生关系的记录显示出来,没关系的记录则没有显示

#执行过程
在emplyee表中找到一条记录,然后开始从头扫码department表,找到其中的dep_id和department中的id一样的记录,和employee中的那条记录连接起来,形成结果表中的一条记录。重复以上操作,
直到employee表中的记录都处理完毕,这就是嵌套循环连接算法的基本思想
# 缺点
需要一边边的扫码employee表,查询效率低
# 解决方法:在emplyee上创建索引

 

 



这个可以用内连接来查询一样的效果, 
select * from employee inner join departmen on department.id =employee.dep_id 推荐使用这种方法,这种效率更高

注意:如果多表联合查询不加条件则会出现(笛卡尔乘积)
注意:在使用多表联合查询时,一定要加条件
结果:符合两个表条件的结果

 

2.把这两张表有关联和没关联的记录显示出来
左连接查询(left join):也就是保留左表记录,右表能与左表发生关系则显示,没发生关系则不显示.

select* from employee LEFT JOIN department on department.id=employee.dep_id

注意:on 表示条件 专门配置 left join 来使用
特点:也就是保留左表记录,右表能与左表发生关系则显示,没发生关系则不显示,右表的数据与左表数据相匹配则显示,不匹配则以NULL填充

 结果:

右连接查询(right join):

select* from employee right JOIN department on department.id=employee.dep_id

具体内容和左连接差不多.

全连接查询(left join union right join ):把这两张表有关联和没关联的记录都显示出来

select* from employee LEFT JOIN department on department.id=employee.dep_id
union
select* from employee right JOIN department on department.id=employee.dep_id;

结果:

三表联合查询:A lelf join B on 条件 lefl join C on 条件

自然连接(nature_join):这个是在数据库系统概论中看到的,概念:自然连接是一种特殊的等值连接,他要求两个表中进行比较的属性必须是同名属性,并且在结果中把重复的属性列去掉。很抽象和懵逼,看大神的解释

 https://blog.csdn.net/Call_Coder/article/details/79483708?depth_1-utm_source=distribute.pc_relevant.none-task-blog-BlogCommendFromBaidu-4&utm_source=distribute.pc_relevant.none-task-blog-BlogCommendFromBaidu-4

 

 

 

 

select * from tbl_ name1 natural join tbl_name2;

 

 

 

in 子查询

子查询又叫嵌套查询

注意:内查询的select语句不能使用order_by子句,order_by只能对最终的查询结果排序

连表查询要比子查询的效率高,因为子查询还要建立一个临时表 ,而连表查询不用

1.查询技术部门和销售部门的人员信息

select * from employee where dep_id in(select id from department where name in(\'技术\',\'销售\'))

结果:

2.查询平均年龄大于25岁的部门?

 


select id,name from department where id in(select dep_id from employee GROUP BY dep_id having avg(age)>25)

结果:

 

3.提取空部门名

select name from department where id not in (select distinct dep_id from employee )

 

版权声明:本文为sticker0726原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://www.cnblogs.com/sticker0726/articles/9982484.html