亲宝软件园·资讯

展开

MySQL中的多表联合查询功能操作

阳862 人气:0

一.介绍

多表查询就是同时查询两个或两个以上的表,因为有的时候用户在查看数据的时候,需要显示的数据来自多张表.多表查询有以下分类:

数据准备

-- 创建部门表
create table if not exists dept3(
  deptno varchar(20) primary key ,  -- 部门号
  name varchar(20) -- 部门名字
);

-- 创建员工表
create table if not exists emp3(
  eid varchar(20) primary key , -- 员工编号
  ename varchar(20), -- 员工名字
  age int,  -- 员工年龄
  dept_id varchar(20)  -- 员工所属部门
);

-- 给dept3表添加数据
insert into dept3 values('1001','研发部');
insert into dept3 values('1002','销售部');
insert into dept3 values('1003','财务部');
insert into dept3 values('1004','人事部');

-- 给emp3表添加数据
insert into emp3 values('1','乔峰',20, '1001');
insert into emp3 values('2','段誉',21, '1001');
insert into emp3 values('3','虚竹',23, '1001');
insert into emp3 values('4','阿紫',18, '1001');
insert into emp3 values('5','扫地僧',85, '1002');
insert into emp3 values('6','李秋水',33, '1002');
insert into emp3 values('7','鸠摩智',50, '1002'); 
insert into emp3 values('8','天山童姥',60, '1003');
insert into emp3 values('9','慕容博',58, '1003');
insert into emp3 values('10','丁春秋',71, '1005');

交叉连接查询

格式

实现

结果

 内连接查询

内连接查询求多张表的交集

 格式

操作

 inner可以省略

操作

-- 查询研发部门的所属员工
-- 隐式内连接
select * from emp3 e ,dept3 d where e.dept_id =d.deptno and name ='研发部';
-- 显式内连接
select * from dept3 d join emp3 e on d.deptno =e.dept_id and name ='研发部'; 
-- 查询研发部和销售部的所属员工
select * from dept3 a join emp3 b on a.deptno = b.dept_id and (name = '研发部' or name = '销售部') ; 
select * from dept3 a join emp3 b on a.deptno = b.dept_id and name in('研发部' ,'销售部') ; 
 
-- 查询每个部门的员工数,并升序排序
 
select 
	a.name,a.deptno,count(1) 
from dept3 a 
   join emp3 b on a.deptno = b.dept_id 
group by 
  a.deptno,name;
 
 
-- 查询人数大于等于3的部门,并按照人数降序排序
 
select
  a.deptno,
  a.name,
  count(1) as total_cnt
from dept3 a
	join emp3 b on a.deptno = b.dept_id
group by 
  a.deptno,a.name
having 
  total_cnt >= 3
order by 
  total_cnt desc;

外连接

外连接分为左外连接(left outer join)、右外连接(right outer join),满外连接(full outer join)。注意: oracle里面有full join,可是在mysql对full join支持的不好。我们可以使用unjion来达到目的。

 格式

左外连接: left outer join
        select* from A left outer join B on条件;
右外连接: right outer join
        select* from A right outer join B on条件;
满外连接: full outer join
        select * from A full outer join B on 条件;

 操作

 直接用fulljion会报错

-- 外连接查询
-- 查询哪些部门有员工,哪些部门没有员工
select * from dept3 left outer join emp3 on dept3.deptno = emp3.dept_id;
-- 查询员工有对应的部门,哪些没有
select * from dept3 right outer join emp3 on dept3.deptno = emp3.dept_id;
-- 使用union关键字实现左外连接和右外连接的并集
select * from dept3 left outer join emp3 on dept3. deptno = emp3.dept_id
union
select * from dept3 right outer join emp3 on dept3.deptno = emp3.dept_id;

子查询

介绍

子查询就是指的在一个完整的查询语句之中,嵌套若干个不同功能的小查询,从而一起完成复杂查询的一种编写形式,通俗一点就是包含select嵌套的查询。

特点

子查询可以返回的数据类型一共分为四种:

单行单列:返回的是一个具体列的内容,可以理解为一个单值数据;单行多列:返回一行数据中多个列的内容;多行单列:返回多行记录之中同一列的内容,相当于给出了一个操作范围;多行多列:查询返回的结果是一张临时表

操作

-- 查询年龄最大的员工信息,显示信息包含员工号、员工名字,员工年龄
select * from emp3 where age=(select max(age) from emp3 );
 
select eid,ename ,age from emp3 where age = (select max (age) from emp3);
-- 查询年研发部和销售部的员工信息,包含员工号、员工名字
select  * from emp3 where emp3.dept_id in (select deptno from dept3 d where d.name='研发部' or d.name='销售部');
 
select eid,ename , t.name from emp3 where dept_id in (select deptno, name from dept3where name ='研发部'or name ='销售部');
-- 查询研发部20岁以下的员工信息,包括员工号、员工名字,部门名字
 
-- 方式一:关联查询
select * from dept3 d join emp3 e on d.deptno =e.dept_id and (d.name='研发部' and e.age<20); 
-- 方式二:子查询
select eid,age ,ename , name from (select * from dept3 where name = '研发部')t1 , (select * from emp3 where age <20) t2 where  t1.deptno =t2.dept_id ;
 
select eid,age ,ename , name from (select * from dept3 where name = '研发部')t1 join  (select * from emp3 where age <20) t2 on t1.deptno =t2.dept_id ;

子查询关键字

在子查询中,有一些常用的逻辑关键字,这些关键字可以给我们提供更丰富的查询功能,主要关键字如下:

1.ALL关键字
2.ANY关键字
3.SOME关键字
4.IN关键字
5.EXISTS关键字

all关键字

格式

特点

操作

any关键字和some关键字

格式

特点

操作

in关键字

格式

 特点

操作

exists关键字

格式

特点

操作

 自关联查询

概念

MySQL有时在信息查询时需要进行对表自身进行关联查询,即一张表自己和自己关联,一张表当成多张表来用。注意自关联时表必须给表起别名。

格式

操作

数据准备

-- 创建表,并建立自关联约束
create table t_sanguo (
    eid int primary key ,
    ename varchar (20) ,
    manager_id int,
    foreign key (manager_id) references t_sanguo (eid) -- 添加自关联约束
);

--添加数据
insert into t_sanguo values (1,'刘协',NULL) ,
                             (2,'刘备',1),
                             (3,'关羽',2),
                            (4,'张飞',2),
                             (5,'曹操',1),
                            (6,'许褚',5),
                             (7,'典韦',5),
                             (8,'孙权',1) ,
                             (9,'周瑜',8),
                            (10,'鲁肃',8) ;
-- 进行关联查询
-- 1.查询每个三国人物及他的上级信息,如:关羽刘备
select * from t_sanguo ts ,t_sanguo ts2 where ts .manager_id  =ts2.eid  ;						
select ts.ename ,ts2.ename  from t_sanguo ts ,t_sanguo ts2 where ts .manager_id  =ts2.eid  ;						
-- 2.查询所有人物及上级(刘协(没有上级)也输出)
select a.ename,b.ename from t_sanguo a left join t_sanguo b on a.manager_id = b.eid;
-- 3.查询所有人物、上级,上上级比如:张飞刘备刘协
select
	a.ename,b.ename,c.ename
from t_sanguo a
left join t_sanguo b on a.manager_id = b.eid
left join t_sanguo c on b.manager_id = c.eid;

总结

加载全部内容

相关教程
猜你喜欢
用户评论