多表联合查询

八角.。

已于 2022-04-26 10:41:09 修改

阅读量3.4k

点赞数

文章标签：数据库

于 2022-04-09 16:22:42 首次发布

本文链接：https://blog.csdn.net/m0_62274923/article/details/124062530

版权

数据库课堂笔记专栏收录该内容

5 篇文章 0 订阅

订阅专栏

1、创建部门表

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

2、创建员工表

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

3、给dept3表添加数据

insert into dept3 values('1001','研发部');
insert into dept3 values('1002','销售部');
insert into dept3 values('1003','财务部');
insert into dept3 values('1004','人事部');

4、给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');

交叉连接查询

隐式内连接（SOL92标准）：select * from A,B where 条件;

显示内连接（SOL99标准）：select * from A [inner] join B on 条件；

区别：，= join where = on

格式
select * from 表1，表2，表3.....

select * from dept3,emp3;

1、查询每个部门的所属员工

a、隐式内连接

select * from dept3,emp3 where deptno = dept_id;

进阶专业版
select * from dept3,emp3 where dept3.deptno = emp3.dept_id;

注：当两个表有重名的字段时，在连接查询的时候需要指定是哪一个表

起别名
select * from dept3 a ,emp3 b where a.deptno = b.dept_id;

b、显示内连接

select * from dept3 inner join emp3 on dept3.deptno = emp3.dept_id;

select * from dept3 a join emp3 b on a.deptno = b.dept_id;

2、查询研发部门的所属员工
a、隐式

select * from dept3 a,emp3 b where a.deptno = b.dept_id and name = '研发部';

b、显示

select * from dept3 a join emp3 b on a.deptno = b.dept_id and name = '研发部';

2、查询研发部和销售部的所属员工
a、隐式

select * from dept3 a , emp3 b where a.deptno = b.dept_id and (name = '研发部' or name = '销售部');

b、简化版本

select * from dept3 a,emp3 b where a.deptno = b.dept_id and name in ('研发部','销售部');

3、查询每个部门的员工数，并升序排列

select
a.deptno,count(1)
from dept3 a
join emp3 b on a.deptno = b.dept_id
group by
a.deptno;

4、查询人数大于等于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;

外连接查询

概述：
a、左外连接：left [outer] join
语法： select*from A left outer join B on 条件;
注意：左表数据全部输出，右表没有对应数据就会补NULL

        b、右外连接： right [outer] join
               语法： select * from A right outer B on 条件;
                注意：右表数据全部输出，左表没有对应数据就会补NULL

c、满外连接: full outer join
语法：select * from A full outer B on 条件;
注意：两表全部输出

1、左外连接left [outer] join
查询哪些部门有员工，哪些部门没员工

select * from dept3 a left outer join emp3 b on a.deptno = b.dept_id;

            -- outer 可以省略
select * from dept3 a left outer join emp3 b on a.deptno = b.dept_id;

多表的左外连接
select * from A
       left join B on 条件1
       left join C on 条件2
       .....

2、右外连接right [outer] join
查询哪些员工有对应的部门，哪些没有

select * from dept3 a right outer join emp3 b on a.deptno = b.dept_id;

多表的右外连接
select * from A
       right join B on 条件1
       right join C on 条件2
       .....

3、满外连接 full join

使用union关键字实现左外连接和右外连接的并集

注：

1、union是去重的

2、union把上面的结果和下面的结果拼在一起

3、join把左面的结果和右面的结果拼在一起

a、select * from dept3 a full join emp3 b on a.deptno = b.dept_id; -- MySQL不支持

b、select * from dept3 a left outer join emp3 b on a.deptno = b.dept_id
        union
        select * from dept3 a right outer join emp3 b on a.deptno = b.dept_id;

c、select * from dept3 a left outer join emp3 b on a.deptno = b.dept_id
        union all -- (unionall是没有去重的结果<把左外和右外拼在一起>)
        select * from dept3 a right outer join emp3 b on a.deptno = b.dept_id;

子查询

1、查询年龄最大的员工信息，显示信息包含员工工号，员工姓名，员工年龄

    a:查询最大年龄
select max(age) from emp3; -- 只能查询出最大年龄，不知道是谁
select * from emp3 where age = 85;
   b:让每一个员工的年龄和最大年龄进行比较，相等则满足条件
select * from emp3 where age = (select max(age) from emp3);

        -- 单行单列，可以作为一个值来用

2、查询研发部和销售部的员工信息，包含员工工号，员工姓名

方式一：关联查询
select * from dept3 a join emp3 b on a.deptno = b.dept_id and (name = '研发部' or name = '销售部');
方式二：子查询
a:先查询研发部和销售部的部门编号：deptno 1001 和 1002
select deptno from dept3 where (name = '研发部' or name = '销售部');
b:查询哪个员工的部门号是 1001 或者 1002
select * from emp3 where dept_id in (select deptno from dept3 where (name = '研发部' or name = '销售部')); -- 多行单列

3、查询研发部30岁以下的员工信息，包括员工号，员工姓名，部门名字
方式一：关联查询

select * from dept3 a join emp3 b on a.deptno = b.dept_id and (name = '研发部' and age<20);
select * from dept3 a join emp3 b on a.deptno = b.dept_id where (name = '研发部' and age<20);

方式二：子查询 (查询结果当作表示用)

        a:在部门表中查询研发部信息
                select * from dept3 where name = '研发部'; -- 一行多列
       b:在员工表中查询年龄小于30岁的员工信息
               select * from emp3 where age < 30;
       c:将以上两个查询的结果进行关联查询 (必须起别名，因为是表)
                select * from (select * from dept3 where name = '研发部') t1 join (select * from emp3 where age < 30) t2 on t1.deptno = t2.dept_id; -- 多行多列

子查询中的关键字

关键字all

select ...from ...where c > all (查询语句)
等价于:
select....from.....where 列 > result1 and C > result2 and C > result3

特点：
        a. ALL: 与子查询返回的所有值比较为true则返回true
        b. ALL可以与=、>、>=、<、<=、<>结合是来使用，分别表示等于、大于、大于等于、小于、小于等于、不等于其中的其中的所有数据。
        c. ALL表示指定列中的值必须要大于子查询集的每一个值，即必须要大于子查询集的最大值;如果是小于号即小于子查询集的最小值。同理可以推出其它的比较运算符的情况。

查询年龄大于、'1003'部门所有年龄的员工信息
select * from emp3 where age > all (select age from emp3 where dept_id = '1003');
查询不属于任何一个部门的员工信息
select * from emp3 where dept_id != all (select deptno from dept3) ;

关键字ANY和SOME

格式
select ..from. ..where 列 > any (查询语句)
-- 等价于 --
select...from...where 列> result1 or 列 > result2 or 列〉result3
特点
a. ANY:与子查询返回的任何值比较为true则返回true
b. ANY可以与=、>、>=、<、<=、<>结合是来使用，分别表示等于、大于、大于等于、小于、小于等于、不等于其中的其中的任何- -个数据。
c. 表示制定列中的值要大于子查询中的任意一一个值，即必须要大于子查询集中的最小值。同理可以推出其它的比较运算符的情况。
●SOME和ANY的作用一样，SOME可以理解为ANY的别名
操作
1、查询年龄大于'1003'部门任意一个员工年龄的员工信息

select * from emp3 where age > any (select age from emp3 where dept_id = '1003') and dept_id != '1003';

select * from emp3 where age > some (select age from emp3 where dept_id = '1003') and dept_id != '1003';

关键字-IN格式

select ...from ...where 列 in(查询语句)

注：只要有任何一个相等都会查出来
等价于:
select ...from ... where 列 = result1 or 列 = result2 or 列 = result3
特点：
a. IN关键字，用于判断某个记录的值,是否在指定的集合中
b. 在IN关键字前边加上not可以将条件反过来
操作
1、查询研发部和销售部的员工信息，包含员工号、员工名字

select eid,ename from emp3 where dept_id in (select deptno from dept3 where name ='研发部'or name ='销售部');

关键字-exists格式

select ...from ...where exists(查询语句)

注：查询语句至少返回一行，及成立
特点
a. 该子查询如果“有数据结果”(至少返回一行数据)，则该EXISTS()的结果为"true"，外层查询执行
b. 该子查询如果“没有数据结果”（没有任何数据返回），则该EXISTS()的结果为"false"，外层查询不执行
c. EXISTS后面的子查询不返回任何实际数据，只返回真或假，当返回真时where条件成立
## 注意EXISTS关键字，比IN关键字的运算效率高，因此，在实际开发中，特别是大数据量时，推荐使用EXISTS关键字
操作

select * from emp3 where exists (select 1); -- 全表输出
select * from emp3 where exists (select * from emp3);-- 全表输出

1、查询公司是否有大于60岁的员工，有则输出
-- 不起别名就是全表输出，由外面的决定里面的

select * from emp3 a where exists(select * from emp3 where a.age > 60);

select * from emp3 a where eid in(select eid from emp3 where a.age > 60);

2、查询有所属部门的员工信息

select * from emp3 a where exists(select * from dept3 b where a.dept_id = b.deptno);

select * from emp3 a where dept_id in (select deptno from dept3 b where a.dept_id = b.deptno);

自关联查询

自关联查询，即一张表自己和自己关联，一张表当成多张表来用。
   注意：自关联时表必须给表起别名。
格式：
        select字段列表from表l a , 表1 b where条件;或者
        select字段列表from 表1 a [left] join表1 b on条件;
操作

数据准备

1、创建表，并建立自关联约束

create table t_sanguo (
   eid int primary key , -- 主键列
   ename varchar (20) ,
   manager_id int, -- 外键列（受主键列约束）
   foreign key (manager_id) references t_sanguo(eid) -- 添加自关联约束
);

2、添加数据

        insert into t_sanguo values (1 ,'刘协',NULL);
        insert into t_sanguo values (2,'刘备',1);
        insert into t_sanguo values ( 3,'关羽',2);
        insert into t_sanguo values (4,'张飞',2);
        insert into t_sanguo values (5,'曹操',1);
        insert into t_sanguo values (6,'许褚',5);
        insert into t_sanguo values (7,'典韦',5);
        insert into t_sanguo values (8,'孙权',1);
        insert into t_sanguo values (9,'周瑜',8) ;
        insert into t_sanguo values (10,'鲁肃',8);

操作：进行关联查询

1.查询每个三国人物及他的上级信息，如:关羽（员工）刘备（领导）