使用mysql内连接查询年龄_Mysql的连表查询

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

准备表

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

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 )