多表查询

多表查询

概要

• 补充知识

• 多表查询理论

• 可视化软件navicat

• 多表查询练习题

---

详情

• 补充知识

额外的常用查询关键字

1.group_concat() 用于分组之后,获得除分组依据外其他字段,其本质类似于拼接
select post,group_concat(name) from emp group by post;
该方法还可以获取多个字段并拼接分隔符
select post,group_concat(name,'￥',salary) from emp group by post;

2.concat() 用于分组之前拼接字段,也可以加分隔符
select post,concat(name,'￥',salary) from emp;

3.concat_ws() 用于分组之前,相同分隔符的情况,首先指定共同分隔符
select post,concat_ws('||',name,age,salary,gender) from emp;

4.as 起别名
(1)字段别名(as可省略,由于会使语法结构不明显,故不推荐)
select id as '编号',name as '姓名',gender as '性别' from emp;
(2)表别名(常用于多表查询)
select * from emp as t1 where t1.id>5;

---

• 多表查询理论

多表查询的查询结果来自多张表数据的组合。

# 测试数据准备
create table dep(
    id int primary key auto_increment,
    name varchar(20) 
);
create table emp(
    id int primary key auto_increment,
    name varchar(20),
    gender enum('male','female') not null default 'male',
    age int,
    dep_id int
);
# 插入数据
insert into dep values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营');

注意：
1、查询SQL语句执行的结果类似于一张表
2、涉及多张表的语句会有相同字段名，需要在字段名前面加表名限制

多表查询的方法分为两种，联表和子查询。有些复杂的查询需要两者相结合。

联表
本质:将多张表拼接成一张表,再套用单表查询。

1.内连接 inner join 
只展示两张表的对应数据
select * from emp inner join dep on emp.dep_id=dep.id;

2.左连接 left join 
以关键字左边的表为基准只展示左表所有数据,不对应的字段显示null
select * from emp left join dep on emp.dep_id=dep.id;

3.右连接 right join
以关键字右边的表为基准只展示右表所有数据,不对应的字段显示null
select * from emp right join dep on emp.dep_id=dep.id;

4.全连接 union
等价于左连接与右连接的结合,不对应的数据显示null
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;

子查询
本质:将一张表的查询结果加括号,再作为条件给别的SQL语句使用。
等同于分步骤操作
select id,name from emp where dep_id in (select id from dep where name in ('运营','销售'));
上述需求如果用联表方式
select emp.id,emp.name from emp inner join dep on emp.dep_id=dep.id where dep.name in ('运营','销售')
 

---

• 可视化软件navicat

在实际工作中有时为了提高效率，需要借助可视化软件操作数据库，其本质是将SQL语句封装并执行。

Navicat软件需要收费
1.有些软件不具有的功能也可以修改生成的SQL语句完成
2.SQL语句可保存在.sql文件中

---

• 作业

查询平均年轻在25岁以上的部门

联表
select dep.name as '部门' 
from emp 
inner join dep on emp.dep_id=dep.id 
group by dep.name 
having avg(emp.age)>25;

子查询
select name
from dep
where dep.id in
(select dep_id
from emp
group by dep_id
having avg(age)>25);