MySQL数据库——多表查询(4)-实例练习、多表查询总结

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群版 2核4GB 100GB
推荐场景:
搭建个人博客
云数据库 RDS MySQL,高可用版 2核4GB 50GB
简介: MySQL数据库——多表查询(4)-实例练习、多表查询总结

进行案例练习之前,需要先增加一个表格

create table salgrade(
    grade int,
    losal int,  -- 对应等级的最低薪资
    hisal int   -- 对应等级的最高薪资
) comment '薪资等级表';
 
insert into salgrade values (1,0,3000);
insert into salgrade values (2,3001,5000);
insert into salgrade values (3,5001,8000);
insert into salgrade values (4,8001,10000);
insert into salgrade values (5,10001,15000);
insert into salgrade values (6,15001,20000);
insert into salgrade values (7,20001,25000);
insert into salgrade values (8,25001,30000);

练习之前,我们把三张表都拿出来:

练习1

  1. 查询员工的姓名、年龄、职位、部门信息 (隐式内连接)
  2. 查询年龄小于30岁的员工的姓名、年龄、职位、部门信息(显式内连接)
  3. 查询拥有员工的部门ID、部门名称
  4. 查询所有年龄大于40岁的员工, 及其归属的部门名称; 如果员工没有分配部门, 也需要展示出来
  5. 查询所有员工的工资等级

1-1

-- 查询员工的姓名、年龄、职位、部门信息 (隐式内连接

select e.name '姓名',e.age '年龄',e.job '职位',d.name  '部门信息'
    from emp e,dept d where e.dept_id = d.id;

查询结果:

1-2

-- 查询年龄小于30岁的员工的姓名、年龄、职位、部门信息(显式内连接)

select e.name '姓名',e.age '年龄',e.job '职位',d.name  '部门信息'
    from emp e inner join dept d on e.dept_id = d.id where e.age < 30;

查询结果:

1-3

-- 查询拥有员工的部门ID、部门名称

-- 要点:自连接,去重关键字

select distinct d.id,d.name from emp e,dept d where e.dept_id = d.id;

查询结果:

1-4

-- 查询所有年龄大于40岁的员工, 及其归属的部门名称; 如果员工没有分配部门, 也需要展示出来

-- 要点:左外连接

select e.name '姓名',d.name '部门名称' 
    from emp e left join dept d on e.dept_id = d.id where age > 40;

查询结果:

1-5

-- 查询所有员工的工资等级

-- 要点:表结构为emp和salgrade,搞清楚两张表的连接条件

select e.name '姓名',s.grade '工资等级' 
    from emp e left join salgrade s on e.salary >= s.losal and e.salary <= s.hisal;
 
-- 另一种写法
select e.name '姓名',s.grade '工资等级'
    from emp e left join salgrade s on e.salary between s.losal and s.hisal;

查询结果:

练习2

  1. 查询 "研发部" 所有员工的信息及 工资等级
  2. 查询 "研发部" 员工的平均工资
  3. 查询工资比 "灭绝" 高的员工信息。
  4. 查询比平均薪资高的员工信息
  5. 查询低于本部门平均工资的员工信息
  6. 查询所有的部门信息, 并统计部门的员工人数
  7. 查询所有学生的选课情况, 展示出学生名称, 学号, 课程名称

2-1

-- 查询 "研发部" 所有员工的信息及 工资等级


-- 要点:搞清楚连接条件和查询条件


-- 连接条件:

(e.dept_id = d.id)

(e.salary between losal and hisal)

-- 查询条件

(d.name = '研发部')

select e.*,s.grade
from emp e,
     dept d,
     salgrade s
where (e.dept_id = d.id)
  and (e.salary between losal and hisal)
  and (d.name = '研发部');

查询结果:

2-2

-- 查询 "研发部" 员工的平均工资

-- 要点:函数avg()

select avg(e.salary)
from emp e,
     dept d
where e.dept_id = d.id
  and d.name = '研发部';

查询结果:

2-3

-- 查询工资比 "灭绝" 高的员工信息。

select *
from emp
where salary > (select salary from emp where name = '灭绝');

查询结果:

2-4

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

select *
from emp
where salary > (select avg(salary) from emp);

查询结果:

2-5

-- 查询低于本部门平均工资的员工信息

-- 要点:查询出每个部门的平均工资

select *, (select avg(e1.salary) from emp e1 where e1.dept_id = e2.dept_id) '所在部门平均工资'
from emp e2
where e2.salary < (select avg(e1.salary) from emp e1 where e1.dept_id = e2.dept_id);

查询结果:

2-6

-- 查询所有的部门信息, 并统计部门的员工人数

先查询所有部门的部门信息:

select * from dept;

再统计单个部门的员工人数:

select count(*) from emp where dept_id = 1;

整合起来:

select d.*, (select count(*) from emp e where e.dept_id = d.id) '员工人数'
from dept d;

查询结果:

2-7

-- 查询所有学生的选课情况, 展示出学生名称, 学号, 课程名称

涉及另外的三个表,是多对多的关系

理清楚三个表的连接关系就可以查询出来

select s.name '学生名称', s.no '学号', c.name '课程名称'
from student s,
     course c,
     student_course sc
where (s.id = sc.studentid)
  and (c.id = sc.courseid);

查询结果:

总结

1.多表关系

一对多:在多的一方设置外键,关联一的一方的主键

多对多:建立中间表,中间表包含两个外键,关联两张表的主键

一对一:用于表结构拆分,在其中任何一方设置外键(UNIQUE),关联另一方的主键

2.多表查询

自连接

       隐式:SELECT...FROM 表A,表B WHERE 条件...

 显式:SELECT...FROM 表A INNER JOIN 表B ON 条件...


外连接:


       左外:SELECT...FROM 表A LEFT JOIN 表B ON 条件...


       右外:SELECT...FROM 表A RIGHT JOIN 表B ON 条件...


自连接:SELECT ... FROM 表A 别名1,表A 别名2 WHERE 条件...


子查询:标量子查询、列子查询、行子查询、表子查询


end


相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
7天前
|
分布式计算 大数据 关系型数据库
MaxCompute产品使用合集之如何实现类似mysql实例中的数据库功能
MaxCompute作为一款全面的大数据处理平台,广泛应用于各类大数据分析、数据挖掘、BI及机器学习场景。掌握其核心功能、熟练操作流程、遵循最佳实践,可以帮助用户高效、安全地管理和利用海量数据。以下是一个关于MaxCompute产品使用的合集,涵盖了其核心功能、应用场景、操作流程以及最佳实践等内容。
|
26天前
|
运维 安全 数据管理
数据管理DMS产品使用合集之是否可以为同一个实例下的不同数据库设置不同的审批规则
阿里云数据管理DMS提供了全面的数据管理、数据库运维、数据安全、数据迁移与同步等功能,助力企业高效、安全地进行数据库管理和运维工作。以下是DMS产品使用合集的详细介绍。
33 5
|
26天前
|
SQL 弹性计算 数据管理
数据管理DMS产品使用合集之sql server实例,已经创建了数据库,登录时提示实例已存在,该怎么处理
阿里云数据管理DMS提供了全面的数据管理、数据库运维、数据安全、数据迁移与同步等功能,助力企业高效、安全地进行数据库管理和运维工作。以下是DMS产品使用合集的详细介绍。
36 1
|
28天前
|
关系型数据库 MySQL 分布式数据库
PolarDB产品使用问题之如何将实例关联到本地的数据库
PolarDB产品使用合集涵盖了从创建与管理、数据管理、性能优化与诊断、安全与合规到生态与集成、运维与支持等全方位的功能和服务,旨在帮助企业轻松构建高可用、高性能且易于管理的数据库环境,满足不同业务场景的需求。用户可以通过阿里云控制台、API、SDK等方式便捷地使用这些功能,实现数据库的高效运维与持续优化。
|
7天前
|
分布式计算 DataWorks Java
DataWorks操作报错合集之无法连接到指定的数据库实例,该如何解决
DataWorks是阿里云提供的一站式大数据开发与治理平台,支持数据集成、数据开发、数据服务、数据质量管理、数据安全管理等全流程数据处理。在使用DataWorks过程中,可能会遇到各种操作报错。以下是一些常见的报错情况及其可能的原因和解决方法。
|
1月前
|
关系型数据库 MySQL 数据库
关系型数据库MySQL开发要点之多表查询2024详解
关系型数据库MySQL开发要点之多表查询2024详解
22 2
|
1月前
|
弹性计算 安全 数据库
云上攻防-云服务篇&弹性计算&云数据库&实例元数据&控制角色&AK控制台接管
云上攻防-云服务篇&弹性计算&云数据库&实例元数据&控制角色&AK控制台接管
|
1月前
|
关系型数据库 MySQL 数据库
MySQL数据库开发之多表查询数据准备及案例实操
MySQL数据库开发之多表查询数据准备及案例实操
27 1
|
1月前
|
关系型数据库 MySQL 数据挖掘
MySQL窗口函数详解(概念+练习+实战)
MySQL窗口函数详解(概念+练习+实战)
199 1
|
1月前
|
关系型数据库 MySQL 数据库
MySQL数据库基础第四篇(多表查询与事务)
MySQL数据库基础第四篇(多表查询与事务)