多表查询v1

目录

• 一：介绍
  • • 多表连接查询
    • 复合条件查询
    • 子查询
• 二：准备表
• 三：笛卡尔积
• 四：多表连接查询
• 外连接语法
• 四种外连接介绍
  • 上代码
• 五：条件连接查询
• 六：子查询
  • • 一：介绍
    • 二：带in关键字的子查询
    • 三：带比较运算符的子查询
    • 四：带exists关键字的字查询
    • 五：练习：查询每个部门最新入职的那位员工
  • 外连接查询
    • 子查询(难理解）
    • 六：总结
    • 七：查询语句的优先级

一：介绍

多表连接查询

复合条件查询

子查询

二：准备表

# 建表
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,'运营'),
    (205,'爆破');

insert into emp(name,sex,age,dep_id) values
    ('jason','male',18,200),
    ('egon','female',48,201),
    ('kevin','male',18,201),
    ('nick','male',28,202),
    ('owen','male',18,203),
    ('jerry','female',18,204);

drop table emp;drop table dep;

三：笛卡尔积

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

select emp.id,emp.name,emp.sex,emp.age,dep.name from emp,dep where emp.dep_id = dep.id;

四：多表连接查询

1. 外连接语法

   select 字段。。。 from 表1 inner|left|right| join 表2 on 表1.字段 = 表2.字段;
   

2. 四种外连接介绍

   MySQL也知道你在后面查询数据过程中肯定会经常用到拼表操作
   所以特地给你开设了对应的方法:
   inner join    外连接之内连接(只拼接2张表中共有的数据部分)
   1eft  join    外连接之左连接(左表所有的数据都展示出来, 没有对应的项就用NULL)
   right join    外连接之右连接(右表所有的数据都展示出来, 没有对应的项就用NULL)
   union         外连接之全连接(全连接左右两 表所有的数据都展示出来)
   注意：mysql不支持全外连接 full join
   强调：mysql可以使用下面的方式间接实现全外连接.
   

3. 上代码

   1. 内连接

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

   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;
      
      
      select * from emp union dep on emp.dep_id=dep.id;
      

五：条件连接查询

1. 以内连接的方式查询emp和dep表，并且emp表中的age字段值必须大于25,即找出年龄大于25岁的员工以及员工所在的部门以内连接的方式查询emp和dep表，并且emp表中的age字段值必须大于25,即找出年龄大于25岁的员工以及员工所在的部门

   select emp.name,dep.name
       from emp inner join dep on emp.dep_id=dep.id
       where emp.age>25;
   

2. 以内连接的方式查询emp和dep表，并且以age字段的升序方式显示

   select emp.id, emp.name, emp.sex, emp.age, dep.name
       from emp inner join dep on emp.dep_id=dep.id 
       order by age asc;
   

六：子查询

一：介绍

1：子查询是将一个查询语句嵌套在另一个查询语句中。
2：内层查询语句的查询结果，可以为外层查询语句提供查询条件。
3：子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字
4：还可以包含比较运算符：= 、 !=、> 、<等

二：带in关键字的子查询

子查询就是我们平时解决问题的思路
	分步骤解决问题
		第一步
		第二步
		...
将一个查询语句的结果当做另外一个查询语句的条件去用

# 查询部门是技术或者人力资源的员工信息
1. 先获取部门的id号
2. 再去员工表里面筛选出对应的员工

# 第一步:
select id from dep where name in ('技术', '人力资源');
# 第二步:
select * from emp where dep_id in (200, 201);

# 得出结果:
select * from emp where dep_id in (select id from dep where name in ('技术', '人力资源'));

三：带比较运算符的子查询

比较运算符：=、!=、>、>=、<、<=、<>
查询大于所有人平均年龄的员工名与年龄
select avg(age) from emp;
select name,age from emp where age > (select avg(age) from emp);


# 查询大于部门内平均年龄的员工名、年龄
select dep_id,avg(age) from emp group by dep_id;
select emp.name,emp.age 
    from emp inner join (select dep_id,avg(age) as avg_age from emp group by dep_id) as t1 
    on emp.dep_id=t1.dep_id 
    where emp.age > t1.avg_age;

四：带exists关键字的字查询

"""
exists关字键字表示存在。在使用exists关键字时，内层查询语句不返回查询的记录。
而是返回一个真假值。True或False
当返回True时，外层查询语句将进行查询；当返回值为False时，外层查询语句不进行查询
"""
# dep表中存在dept_id=203，Ture
select * from emp
    where exists (select id from dep where id=203);
    
# dep表中不存在dept_id=204，False   
select * from emp
    where exists (select id from dep where id=204);

五：练习：查询每个部门最新入职的那位员工

company.emp
    员工id      id                  int             
    姓名        emp_name            varchar
    性别        sex                 enum
    年龄        age                 int
    入职日期     hire_date           date
    岗位        post                varchar
    职位描述     post_comment        varchar
    薪水        salary              double
    办公室       office              int
    部门编号     depart_id           int



#创建表
create table emp(
id int not null unique auto_increment,
name varchar(20) not null,
sex enum('male','female') not null default 'male', #大部分是男的
age int(3) unsigned not null default 28,
hire_date date not null,
post varchar(50),
post_comment varchar(100),
salary double(15,2),
office int, #一个部门一个屋子
depart_id int
);


#插入记录
#三个部门：教学，销售，运营
insert into emp(name,sex,age,hire_date,post,salary,office,depart_id) values
('egon','male',18,'20170301','老男孩驻沙河办事处外交大使',7300.33,401,1), #以下是教学部
('alex','male',78,'20150302','teacher',1000000.31,401,1),
('wupeiqi','male',81,'20130305','teacher',8300,401,1),
('yuanhao','male',73,'20140701','teacher',3500,401,1),
('liwenzhou','male',28,'20121101','teacher',2100,401,1),
('jingliyang','female',18,'20110211','teacher',9000,401,1),
('jinxin','male',18,'19000301','teacher',30000,401,1),
('成龙','male',48,'20101111','teacher',10000,401,1),

('歪歪','female',48,'20150311','sale',3000.13,402,2),#以下是销售部门
('丫丫','female',38,'20101101','sale',2000.35,402,2),
('丁丁','female',18,'20110312','sale',1000.37,402,2),
('星星','female',18,'20160513','sale',3000.29,402,2),
('格格','female',28,'20170127','sale',4000.33,402,2),

('张野','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门
('程咬金','male',18,'19970312','operation',20000,403,3),
('程咬银','female',18,'20130311','operation',19000,403,3),
('程咬铜','male',18,'20150411','operation',18000,403,3),
('程咬铁','female',18,'20140512','operation',17000,403,3)
;

# ps：如果在windows系统中，插入中文字符，select的结果为空白，可以将所有字符编码统一设置成gbk

1. 外连接查询

   select * from emp order by hire_date;
   select post,min(hire_date) as min_hire_date from emp group by post;
   
   select * 
       from emp inner join (select post,min(hire_date) as min_hire_date from emp group by post) t1
       on emp.hire_date=t1.min_hire_date;
   

2. 子查询(难理解）

   # 错误写法
   select (select * from emp as t2 
           where t1.post=t2.post 
           order by hire_date desc
           limit 1)
       from emp as t1 group by post; 
   

   答案一为正确答案，答案二中的limit 1有问题（每个部门可能有>1个为同一时间入职的新员工），我只是想用该例子来说明可以在select后使用子查询

   可以基于上述方法解决：比如某网站在全国各个市都有站点，每个站点一条数据，想取每个省下最新的那一条市的网站质量信息

六：总结

表的查询结果可以作为其他表的查询条件, 也可以通过起别名的方式把它作为一个张虚拟表根其他表关联.

多表查询就两种方式:
    先拼接表再查询
    子查询 一步一步来

七：查询语句的优先级

from   
笛卡尔积  # 把两张表简单粗暴的连接到一起
on
left join 或者 right join
inner join
where 
group by
having
distinct
order by
limit