第29讲：多表查询综合案例

多表查询综合案例

文章目录

1.准备多表查询使用的表数据

部门信息表

createtable bmxxb (
    id intprimarykeyauto_incrementcomment'部门ID',
    bmmc varchar(10)notnullcomment'部门名称')comment'部门信息表';INSERTINTO bmxxb (id, bmmc)VALUES(1,'平台研发部'),(2,'技术运维部'),(3,'技术测试部'),(4,'销售部'),(5,'市场部'),(6,'人事部'),(7,'实施部');

人员信息表

createtable ygxxb (
    id intprimarykeyauto_incrementcomment'id',
    xm varchar(10)notnullcomment'姓名',
    nl intcomment'年龄',
    zw varchar(20)comment'职务',
    xz intcomment'薪资',
    rzsj datecomment'入职时间',
    zsld_id intcomment'直属领导ID',
    bm_id intcomment'部门ID',constraint fk_ygxxb_bmid foreignkey(bm_id)references bmxxb(id))comment'人员信息表';INSERTINTO ryxxb ( id, xm, nl, zw, xz, rzsj, zsld_id, bm_id )VALUES(1,'余伟',38,'技术总监',100000,'2001-01-01',1,1),(2,'江睿基',31,'运维负责人',33000,'2017-01-01',1,2),(3,'张希',28,'高级运维工程师',15000,'2020-12-05',2,2),(4,'张亚男',29,'运维工程师',9000,'2021-11-03',2,2),(5,'吴昊泽',33,'网络工程师',11000,'2019-02-05',2,2),(6,'乔亮',32,'研发经理',29000,'2015-09-07',1,1),(7,'张子云',34,'JAVA开发工程师',13000,'2017-10-12',6,1),(8,'陈国明',33,'JAVA开发工程师',15000,'2016-09-15',6,1),(9,'张茜茜',26,'JAVA开发工程师',16000,'2020-06-02',6,1),(10,'赵晓燕',23,'安卓开发工程师',18000,'2021-05-13',6,1),(11,'王蕊蕊',25,'测试主管',21000,'2018-10-23',1,3),(12,'牛泽阳',42,'测试工程师',11000,'2018-10-31',11,3),(13,'吴刚',30,'测试工程师',10000,'2019-05-09',11,3),(14,'高宇',23,'销售主管',25000,'2013-02-12',1,4),(15,'刘洋',24,'销售',19000,'2019-7-12',14,4),(16,'王文良',30,'销售',20000,'2021-6-20',14,4),(17,'陈锋',21,'市场专员',7000,'2021-5-11',14,5),(18,'白琳',32,'HR',6000,'2018-10-29',1,6),(19'李金',33,'网络管理员',6000,'2022-05-20',NULL,null);

薪资等级表

createtable xzdjb (
    xzdj varchar(2)comment'薪资等级',
    xzfwzxqj intcomment'薪资范围最小区间',
    xzfwzdqj intcomment'薪资范围最大区间')comment'薪资等级表';insertinto xzdjb values('p1','0','3000'),('p2','3001','5000'),('p3','5001','8000'),('p4','8001','15000'),('p5','15000','25000'),('p6','25001','40000'),('p7','40001','60000'),('p8','60001','100000');

2.查询人员姓名、年龄、职位、部门的信息

查询人员的姓名、年龄、职位、部门的信息，要求通过隐式内连接实现。

需要关联的表名：ryxxb、bmxxb

多表关联条件：ryxxb.bm_id = bmxxb.id

1）编写对应的SQL语句。

SELECT
    ry.xm AS 姓名,
    ry.nl AS 年龄,
    ry.zw AS 职位,
    bm.bmmc AS 部门名称 
FROM
    ryxxb AS ry,
    bmxxb AS bm 
WHERE
    ry.bm_id = bm.id

2）查询结果如下，每个人员的姓名、年龄、职位、所属的部门都被查询出来了。

3.查询年龄小于30岁的人员姓名、年龄、职位、部门的信息

查询年龄小于30岁的人员的信息，打印出人员的姓名、年龄、职位和所在部门的信息，要求使用显式内连接来实现。

需要关联的表名：ryxxb、bmxxb

多表关联条件：ryxxb.bm_id = bmxxb.id

1）编写对应的SQL语句。

SELECT
    ry.xm AS 姓名,
    ry.nl AS 年龄,
    ry.zw AS 职位,
    bm.bmmc AS 部门名称 
FROM
    ryxxb AS ry
    INNERJOIN bmxxb AS bm ON ry.bm_id = bm.id 
WHERE
    ry.nl <'30';

2）SQL分析：将人员表中的部门ID字段与部门表的ID字段关联，查询出来的数据时全部人员的信息，然后添加一个条件，如果年龄大于30岁则输出。

3）查询结果如下，年龄小于30岁的每个人员的姓名、年龄、职位、所属的部门都被查询出来了。

4.查询部门中有员工的部门ID和名称

需要查询出那些部门中员工，无员工的部门不需要打印。

在部门信息表中一共有7个部门，其中实施部是没有任何员工的，下面我们通过SQL来查询。

需要关联的表名：ryxxb、bmxxb

多表关联条件：ryxxb.bm_id = bmxxb.id

1）编写对应的SQL语句。

SELECTDISTINCT
    bm.id AS 部门 ID,
    bm.bmmc AS 部门名称 
FROM
    ryxxb AS ry,
    bmxxb AS bm 
WHERE
    ry.bm_id = bm.id;

2）SQL分析：部门ID在人员信息表有数据关联，就表示该部门下有员工，那么就可以当做连接条件，最后查询出每个人对应的部门名称后，只打印出部门的ID号和部门的名称，然后去重即可。

3）查询结果如下，有员工的部门列表如下。

5.查询所有年龄大于30岁的员工信息

要求查询出所有年龄大于30岁的员工信息，以及所在部门的信息，即使没有分配部门，也需要查询出来。

要求查询全部的人员，即使没有关联的数据也要查询，这里是有左外连接最合适。

需要关联的表名：ryxxb、bmxxb

多表关联条件：ryxxb.bm_id = bmxxb.id

1）编写对应的SQL语句。

SELECT
    ry.*,
    bm.bmmc 
FROM
    ryxxb AS ry
    LEFTJOIN bmxxb AS bm 
ON ry.bm_id = bm.id 
WHERE
    ry.nl >'30';

2）SQL分析：需求是所有人员都要查询，包括没有部门的，也就是说不管有没有关联的数据，人员表都要被全部查询，那么这里使用左外连接查询最方便，只要人员表部门ID等于部门表的ID，都是交集数据，都会被查询出来，最后加一个条件，年龄大于30。

3）查询结果如下，30岁以上所有的员工信息都被查询出来了。

6.查询所有员工的工资等级

需要查询出每个人员的工资等级范围。

需要关联的表名：ryxxb、xzdjb

多表关联条件：ryxxb.xz between xzdjb.xzfwzxqj and xzdjb.xzfwzdqj

分析：这个需求相对来说比较复杂，首先人员信息表并没有明确的字段说明每个人的薪资等级是多少，接下来就需要通过复杂的关联条件来实现。

人员信息表中提供了薪资的字段，而薪资等级表中则提供薪资范围的2个字段，一个最小值，一个最大值，薪资介于这个范围内的都会划分到一个等级。

根据这些线索，我们就已经知道该如何获取每个人员的薪资等级了。

我们可以增加一个条件，如果薪资是在xxx范围到xxx范围的，就打印出对应等级字段的值。

1）编写对应的SQL语句。

SELECT
    ry.xm AS 姓名,
    ry.zw AS 职位,
    ry.xz AS 薪资,
    xz.xzdj AS 薪资等级,
    xz.xzfwzxqj AS 薪资范围最小区间,
    xz.xzfwzxqj AS 薪资范围最大区间 
FROM
    ryxxb AS ry,
    xzdjb AS xz 
WHERE
    ry.xz BETWEEN xz.xzfwzxqj AND xz.xzfwzdqj;

2）SQL分析：首先使用内连接将人员表和薪资表关联起来，然后查询薪资在薪资等级表中最大值和最小值的范围，查到范围后取出等级值。

3）查询结果如下，所有人员的薪资以及薪资等级都被查询出来了。

7.查询平台研发部所有员工的信息和工资等级

查询平台研发部所有员工的信息和工资等级。

需要关联的表名：ryxxb、bmxxb、xzdjb

多表关联条件：ryxxb.xz between xzdjb.xzfwzxqj and xzdjb.xzfwzdqj 和 ryxxb.bm_id = bmxxb.id

分析：完成6中的需求后，这个需求就变得比较简单了，在前面的SQL语句基础上，增加一个查询条件就可以完成，但是本次需求还需要打印出所在的部门信息，那么就多增加一个关联条件，关联上部门信息表即可。

1）编写对应的SQL语句。

SELECT
    ry.xm AS 姓名,
    ry.zw AS 职位,
    bm.bmmc AS 部门名称,
    ry.xz AS 薪资,
    xz.xzdj AS 薪资等级,
    xz.xzfwzxqj AS 薪资范围最小区间,
    xz.xzfwzxqj AS 薪资范围最大区间 
FROM
    ryxxb AS ry,
    xzdjb AS xz,
    bmxxb AS bm 
WHERE( ry.xz BETWEEN xz.xzfwzxqj AND xz.xzfwzdqj )AND( ry.bm_id = bm.id )AND( bm.bmmc ='平台研发部');

2）SQL分析：本次共需要关联三张表，通过内连接实现即可，然后通过连接条件，将三张表连接在一起，ry.bm_id = bm.id可以查询出人员和部门的对应关系，ry.xz BETWEEN xz.xzfwzxqj AND xz.xzfwzdqj可以查询出人员对应的薪资等级。

3）查询结果如下，研发部每个人员的信息以及部门信息和薪资等级都被查询出来了。

8.查询平台研发部每个员工的平均薪资

需要查询出研发部每个人员的平均工资。

需要关联的表名：ryxxb、bmxxb

多表关联条件：ryxxb.bm_id = bmxxb.id

分析：要求是查询出平台研发部每个人的平均薪资，首先通过关联查询，查询出谁是研发部的成员，然后通过AVG函数统计出每个人工资的平均值。

1）编写对应的SQL语句。

SELECTavg( ry.xz )FROM
    ryxxb AS ry,
    bmxxb AS bm 
WHERE( ry.bm_id = bm.id )AND( bm.bmmc ='平台研发部');

2）SQL分析：由于要统计某个部门下的人员的平均薪资，因此需要将人员表和部门表关联起来，通过查询条件查询出研发部的人员，最后使用avg函数求出平均值。

3）查询结果如下，研发部的平均薪资就查询出来了。

9.查询出比王文良薪资高的其他人员信息

查询出比王文良薪资都高的其他人员信息。

单表查询即可，通过子查询来实现。

SELECT*FROM
    ryxxb 
WHERE
    xz >(SELECT xz FROM ryxxb WHERE xm ='王文良');

查询结果如下，薪资都是比王文良高的人员。

10.查询比平均薪资高的员工信息

查询出公司的平均工资，然后统计出比平均工资还高的人员信息。

单标查询即可，通过子查询来实现。

SELECT*FROM
    ryxxb 
WHERE
    xz >(SELECTavg( xz )FROM ryxxb );

查询结果如下，所有比平均薪资高的人员信息都被查询出来了。

11.查询各个部门低于本部门平均信息的人员信息

查询出每个部门的平均薪资，然后查询出每个部门低于平均薪资的人员信息。

多表查询结合通过子查询来实现。

分析：首先编写一个能够查询指定一个部门的平均薪资的SQL语句。

通过内连接方式统计出部门的平均薪资，通过自连接查询出低于部门平均薪资的人员信息。

内连接要关联人员信息表和部门信息表，统计出人员对应的部门名称，和部门的平均薪资，如果我们想统计指定部门的平均薪资，则指定一个查询条件即可。

部门平均薪资的字段可以通过查询平均薪资的语句，嵌套进去，从而生成一个新的字段，叫做部门平均薪资。

能够查询出部门的平均薪资之后，就可以通过自连接的方式，来进行匹配和关联，内连接语法中用到了人员信息表，内连接语法中的人员信息表的别名可以叫做ry2。

自连接语法中的表也是人员信息表，表的别名叫做ry1，用于展示最终输出的每个字段的结果，将获取部门平均工资的内连接语句嵌套进自连接的SQL中，需要对嵌套的内连接语法稍加调整，其中ry2表有部门的平均薪资的字段，ry1表就是正常的人员信息表，此时查询条件就可以是ry1表的bm_id等于ry2表中的bm_id，这样一对应之后，拿着ry1表中的部门id，就可以获取到这条数据对应在ry2表中的部门平均薪资，最后再来一个条件查询，如果数据中人员的薪资小于所在部门的平均薪资时，就被查询出来。

这种查询语句也可以叫做内连接嵌套内连接，整体语法可以看做是一个大的内连接，而每个条件对应的结果都是另外一个内连接查询出来的值，相对来说比较复杂。

最终希望打印的列有：姓名、职位、薪资、部门名称、部门平均薪资。

关联的表：ryxxb、bmxxb

连接条件：ryxxb.bm_id = bmxxb.id

1）求出技术运维部的平均薪资，内连接语法，此时通过内连接处理的新结果集，就增加了一个平均薪资字段和一个部门名称的字段，稍后把它叫做为ry2表。

select bm.bmmc,avg(xz)from ryxxb as ry , bmxxb as bm where(ry.bm_id = bm.id)and(bm.bmmc ='技术运维部');

2）统计各部门下低于平均薪资的员工信息，自连接语法。

SELECT
    ry1.xm AS 姓名,
    ry1.zw AS 职位,
    ry1.xz AS 薪资,
    bm.bmmc AS 部门名称,(SELECTavg(ry2.xz)FROM ryxxb AS ry2 , bmxxb AS bm WHERE( ry1.bm_id = ry2.bm_id ))AS 部门平均薪资 
FROM
    ryxxb AS ry1,
    bmxxb AS bm 
WHERE( ry1.bm_id = bm.id )AND( ry1.xz <(SELECTavg(ry2.xz)FROM ryxxb AS ry2 , bmxxb AS bm WHERE( ry1.bm_id = ry2.bm_id )));
    
ry1.bm_id = ry2.bm_id 这个语句的妙处就在于可以根据每个人员的部门ID去匹配有平均工资结果集中的部门ID，如果是一样的，就可以去比较人员的薪资与平均薪资了。

数据查询结果如下，列出了每个部门的平均薪资，地域部门平均薪资的人员信息都被查询出来了。

12.统计各个部门的员工人数

需要查询各个部门中的员工人数。

相较于前一个案例，简单了许多，查询部门表中的所有字段，然后增加一个字段，这个字段就是部门的人数，通过查询人员表中的bm_id字段与部门表的id字段关联的数据，然后统计出数量，这个数量就是部门的人数。

SELECT
    bm.id AS 部门 ID,
    bm.bmmc AS 部门名称,(SELECTcount(*)FROM ryxxb AS ry WHERE ry.bm_id = bm.id )AS 部门人数 
FROM
    bmxxb AS bm;

查询结果如下，查询出了每个部门的人数。