当前位置: 首页 > news >正文

MySQL多表查询案例

多表查询

本文介绍了多表查询中的表关系概念和操作方法。主要内容包括:1.三种表关系类型(一对多、多对多、一对一)及其实现方式;2.多表查询的四种连接方式(内连接、左外连接、右外连接、自连接)及语法;3.子查询的使用方法;4.通过员工-部门-薪资等级表的实际案例,演示了9种典型查询场景的实现方式,包括基础查询、条件筛选、分组统计、子查询应用等。案例涵盖了从简单到复杂的多表查询操作,展示了SQL在数据关联分析中的强大功能。

多表关系

概念

一对多(多对一)

一张表中的一列可以和另外一张表中多条数据关联,拿学生表和成绩表举例,一个学生有多个成绩。

案例:部门与员工

多对多

拿学生表和科目表举例,一个学生可以选择多门课程,一个课程也可以被很多学生选择;多对多关系实现需要借助第三张中间表。中间表至少包含两个字段,将多对多的关系,拆成一对多的关系

案例:学生与课程

建立一个中间表

一对一

一张表中的一条数据对应另外一张表中的一列数据,比如一个人只有一张身份证,一张身份证对应一个人。一对一关系比较少见,因为一对一这种关系的表可以合并成一张表

多表查询概述

内连接

两张表交集的部分

隐式

select 字段列表 from 表一,表二 where 条件;

显式

 select 字段列表 from 表一 inner join 表二 on 连接条件;

外连接

左外:左表所有数据包含交集部分

select 字段列表 from 表一 left join 表二 on 条件;

右外:右表所有数据包含交集部分

select 字段列表 from 表一 right join 表二 on 条件;

自连接

自连接查询,顾名思义,就是自己连接自己,也就是把一张表连接查询多次。

自连接的查询语法:

 SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ... ;

子查询

SQL语句中嵌套SELECT语句,称为嵌套查询,又称子查询。

SELECT * FROM t1 WHERE column1 = ( SELECT column1 FROM t2 ); 1

多表查询案例

数据准备

emp员工表

create table emp(
id int auto_increment comment 'ID' primary key,
name varchar(50) not null comment '姓名',
age int comment '年龄',
job varchar(20) comment '职位',
salary int comment '薪资',
entrydate date comment '入职时间',
managerid int comment '直属领导ID',
dept_id int comment '部门ID'
)comment '员工表';
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references
dept(id);
INSERT INTO emp (id, name, age, job,salary, entrydate, managerid, dept_id)
VALUES
(1, '金庸', 66, '总裁',20000, '2000-01-01', null,5),
(2, '张无忌', 20, '项目经理',12500, '2005-12-05', 1,1),
(3, '杨逍', 33, '开发', 8400,'2000-11-03', 2,1),
(4, '韦一笑', 48, '开发',11000, '2002-02-05', 2,1),
(5, '常遇春', 43, '开发',10500, '2004-09-07', 3,1),
(6, '小昭', 19, '程序员鼓励师',6600, '2004-10-12', 2,1),
(7, '灭绝', 60, '财务总监',8500, '2002-09-12', 1,3),
(8, '周芷若', 19, '会计',48000, '2006-06-02', 7,3),
(9, '丁敏君', 23, '出纳',5250, '2009-05-13', 7,3),
(10, '赵敏', 20, '市场部总监',12500, '2004-10-12', 1,2),
(11, '鹿杖客', 56, '职员',3750, '2006-10-03', 10,2),
(12, '鹤笔翁', 19, '职员',3750, '2007-05-09', 10,2),
(13, '方东白', 19, '职员',5500, '2009-02-12', 10,2),
(14, '张三丰', 88, '销售总监',14000, '2004-10-12', 1,4),
(15, '俞莲舟', 38, '销售',4600, '2004-10-12', 14,4),
(16, '宋远桥', 40, '销售',4600, '2004-10-12', 14,4),
(17, '陈友谅', 42, null,2000, '2011-10-12', 1,null);

dept部门表

salgrade薪资等级表

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、查询员工的姓名、年龄、职位、部门信息
: emp , dept
连接条件: emp.dept_id = dept.id
select emp.name,age,job,dept.name from db5.emp,dept where emp.dept_id=dept.id;

2、查询年龄小于30岁的员工的姓名、年龄、职位、部门信息(显式内连接)
: emp , dept
连接条件: emp.dept_id = dept.id
select emp.name,age,job,d.name from db5.emp inner join dept d on emp.dept_id = d.id where age<30;

3、查询拥有员工的部门ID、部门名称
: emp , dept
连接条件: emp.dept_id = dept.id
select distinct d.id,d.name from dept d,db5.emp e where d.id=e.dept_id;

4、查询所有年龄大于40岁的员工, 及其归属的部门名称; 如果员工没有分配部门, 也需要展示出
(外连接)
: emp , dept
连接条件: emp.dept_id = dept.id
select e.name,e.age,d.name from dept d left join emp e on d.id = e.dept_id where e.age>40;

5、查询所有员工的工资等级
: emp , salgrade
连接条件 : emp.salary >= salgrade.losal and emp.salary <= salgrade.hisal
select e.*,s.* from db5.emp e,salgrade s where e.salary >= s.losal and e.salary <= s.hisal;
#或者
select e.*,s.* from db5.emp e,salgrade s where e.salary between s.losal and s.hisal;

6、查询 "研发部" 所有员工的信息及 工资等级
: emp , salgrade , dept
连接条件 : emp.salary between salgrade.losal and salgrade.hisal ,
emp.dept_id = dept.id
查询条件 : dept.name = '研发部'
select e.*,d.name,s.grade from emp e , dept d, salgrade s where e.dept_id=d.id and (e.salary between s.losal and s.hisal) and d.name='研发部';

7、查询 "研发部" 员工的平均工资
: emp , dept
连接条件 : emp.dept_id = dept.id
select avg(salary) '平均工资',d.name from dept d left join emp e on d.id = e.dept_id where d.name='研发部';

8、查询工资比 "灭绝" 高的员工信息。
. 查询 "灭绝" 的薪资
select salary from emp where name='灭绝';
. 查询比她工资高的员工数据
select * from emp where salary>(select salary from emp where name='灭绝');

9、查询比平均薪资高的员工信息
. 查询员工的平均薪资
. 查询比平均薪资高的员工信息
select avg(salary) from emp;
select * from emp where salary>(select avg(salary) from emp);
http://www.xdnf.cn/news/1305469.html

相关文章:

  • 关系型数据库从入门到精通:MySQL 核心知识全解析
  • 单片机常用通信协议(一)
  • ubuntu 24.04 通过部署ollama提供大模型api接口
  • Storage.AI解读:构建AI数据基础设施的开放标准
  • 【万字精讲】 左枝清减·右枝丰盈:C++构筑的二叉搜索森林
  • Java 中使用阿里云日志服务(SLS)完整指南
  • nifi 增量处理组件
  • 区块链:用数学重构信任的数字文明基石
  • 【0基础3ds Max】学习计划
  • 007TG洞察:特斯拉Robotaxi成本降低84%?技术驱动的效率革命对营销自动化的启示
  • 以下是对智能电梯控制系统功能及系统云端平台设计要点的详细分析,结合用户提供的梯控系统网络架构设计和系统软硬件组成,分点论述并补充关键要点:
  • 深度解读 Browser-Use:让 AI 驱动浏览器自动化成为可能
  • 初识CNN02——认识CNN2
  • 数据结构初阶:排序算法(二)交换排序
  • Boost库中boost::function函数使用详解
  • Redis面试精讲 Day 22:Redis布隆过滤器应用场景
  • 测控一体化闸门驱动灌区信息化升级的核心引擎
  • 波浪模型SWAN学习(1)——模型编译与波浪折射模拟(Test of the refraction formulation)
  • yolo安装
  • es7.x中分片和节点关系以及查看节点数
  • WEB安全--Java安全--Servlet内存马
  • 前端基础知识版本控制系列 - 01( 对版本管理的理解)
  • pyqt5无法显示opencv绘制文本和掩码信息
  • Map、Dictionary、Hash Table:到底该用哪一个?
  • 机械学习---- PCA 降维深度解析
  • 朗空量子与 Anolis OS 完成适配,龙蜥获得抗量子安全能力
  • redis-保姆级配置详解
  • 焊接机器人保护气体效率优化
  • 18- 网络编程
  • NAS播放器的新星,一站式全平台媒体库管理工具『Cinemore』体验