mysql多表查询实训,mysql-08mysql多表查询

实验表创建

建表

#建表

create table dep(

id int,

name varchar(20)

);

create table emp(

id int primary key auto_increment,

name varchar(20),

sex enum('male','female') not null default 'male',

age int,

dep_id int

);

插入数据

#插入数据

insert into dep values

(200,'技术'),

(201,'人力资源'),

(202,'销售'),

(203,'运营');

insert into emp(name,sex,age,dep_id) values

('jason','male',18,200),

('egon','female',48,201),

('kevin','male',38,201),

('nick','female',28,202),

('owen','male',18,200),

('jerry','female',18,204)

;

# 当初为什么我们要分表，就是为了方便管理，在硬盘上确实是多张表，但是到了内存中我们应该把他们再拼成一张表进行查询才合理

笛卡尔积

select * from emp,dep; # 左表一条记录与右表所有记录都对应一遍>>>笛卡尔积

# 将所有的数据都对应了一遍，虽然不合理但是其中有合理的数据，现在我们需要做的就是找出合理的数据

# 查询员工及所在部门的信息

select * from emp,dep where emp.dep_id = dep.id;

# 查询部门为技术部的员工及部门信息

select * from emp,dep where emp.dep_id = dep.id and dep.name = '技术';

内连接

# 1、内连接：只取两张表有对应关系的记录

select * from emp inner join dep on emp.dep_id = dep.id;

select * from emp inner join dep on emp.dep_id = dep.id

where dep.name = "技术";

左连接

# 2、左连接: 在内连接的基础上保留左表没有对应关系的记录

select * from emp left join dep on emp.dep_id = dep.id;

右连接

# 3、右连接: 在内连接的基础上保留右表没有对应关系的记录

select * from emp right join dep on emp.dep_id = dep.id;

全连接

# 4、全连接：在内连接的基础上保留左、右面表没有对应关系的的记录

select * from emp left join dep on emp.dep_id = dep.id

union

select * from emp right join dep on emp.dep_id = dep.id;

子查询

# 就是将一个查询语句的结果用括号括起来当作另外一个查询语句的条件去用

# 1.查询部门是技术或者人力资源的员工信息

"""

先获取技术部和人力资源部的id号，再去员工表里面根据前面的id筛选出符合要求的员工信息

"""

select * from emp where dep_id in (select id from dep where name = "技术" or name = "人力资源");

# 2.每个部门最新入职的员工 思路：先查每个部门最新入职的员工，再按部门对应上联表查询

select t1.id,t1.name,t1.hire_date,t1.post,t2.* from emp as t1

inner join

(select post,max(hire_date) as max_date from emp group by post) as t2

on t1.post = t2.post

where t1.hire_date = t2.max_date

;

"""

记住一个规律，表的查询结果可以作为其他表的查询条件，也可以通过其别名的方式把它作为一张虚拟表去跟其他表做关联查询

"""

select * from emp inner join dep on emp.dep_id = dep.id;