SQL 多表查询

embedded/2025/1/24 7:08:45/

多表查询

多表关系

概念：
项目开发中，在进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间的相互关联，所以各个表结构之间也存在着各个联系，基本分为三种：

  • 一对多（多对一）
  • 多对多
  • 一对一
    一对多（多对一）：
    案例：部门与员工的关系
    关系：一个部门有多个员工，一个员工只在一个部门
    实现：在多的一方建立外键，指向一的一方的主键

多对多：
案例：学生与课程
关系：一个学生可选修多门课程，一个课程可被多个学生选择
实现：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

一对一：
案例：用户和用户的基本信息
关系：一对一关系，用于单表拆分，将一张表的基础字段放在一张表中，其他详字段放在另一张表中，以提升操作效率
实现：在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的unique

多表查询概述

概念：指从多张表中查询数据
重新建立dept和employee表

最终的表结构
在这里插入图片描述

在这里插入图片描述

建立表后进行多表查询：

sql">-- 多表查询  笛卡尔积
select * from employee,dept;

在这里插入图片描述
查询后实际出现了102条信息。
笛卡尔积：笛卡尔积是指在数学中，两个集合A集合和B集合的所以组合情况。（在查询多表时要去除无效的笛卡尔积）

sql">-- 多表查询，去除笛卡尔积
select * from employee,dept where employee.dept_id = dept.id;
select * from employee e,dept d where e.dept_id = d.id;  -- 将表名简写

多表查询的分类

  • 连接查询
    内连接：相当于查询A，B交集部分数据
    外连接：
    左外连接：查询左表所有数据，以及两张表交集部分
    右外连接：查询右表所有数据，以及两张表交集部分
    自连接：当前表与自身的连接查询，自连接必须使用表别名
  • 子查询

内连接

内连接查询语法：
隐式内连接：
select 字段列表 from 表1，表2 where 条件...
显式外连接：
select 字段列表 from 表1 [inner] join 表2 on 连接条件

sql">-- 内连接
-- 1.查询每一个员工姓名，及关联的部门的名称（隐式内连接）
select e.name,d.name from employee as e,dept as d where e.dept_id = d.id;-- 2.查询每一个员工姓名，及关联的部门的名称（显式内连接）
select e.name,d.name from employee as e join dept as d where e.dept_id = d.id;

外连接

外连接查询语法：

  • 左外连接
    select 字段列表 from 表1 left [outer] join 表2 on 条件...;
    相当于查询表1（左表）的所有数据包含表1和表2交集部分的数据
  • 右外连接
    select 字段列表 from 表1 right [outer] join 表2 on 条件 ...
    相当于查询表2（右表）的所有数据包含表1和表2交集部分的数据
sql">-- 外连接
-- 1.查询employee表的所有数据，和对应的部门信息（左外连接）
select e.*,d.name from employee e left join dept d on e.dept_id = d.id;-- 2.查询dept表的所有数据，和对应的员工信息（右连外接）
select d.* ,e.* from employee e right join dept d on e.dept_id = d.id;

自连接

语法：
select 字段列表 from 表A 别名A join 表A 别名B on条件 ...;
自连接查询，可以是内连接，也可以是外连接查询 。

sql">-- 自连接
-- 1.查询员工 及其 所属领导的名字
-- 表结构:employee
select a.name,b.name from employee a , employee b where a.managerid = b.id;-- 2.查询所有员工employee 及其领导的名字 employee ，如果员工没有领导也要查出来
select a.name '员工',b.name '领导'from employee a left join employee b on a.managerid = b.id;

联合查询

对于union查询，就是把多次查询的结果合并起来，形成一个新的查询结果
select 字段列表 from 表A ... union [All] select 字段列表 from 表B ...;

sql">-- 联合查询
-- 将薪资低于5000的员工，和年龄大于55的员工
select * from employee where salary < 5000
union ALL
select * from employee where age > 55; -- 查询出所有的信息，会有重复的select * from employee where salary < 5000
union 
select * from employee where age > 55;  -- 去重作用

子查询

  1. 概念：SQL语句中嵌套select 语句，称为嵌套查询，又称子查询。
    select * from t1 where column1 = (select column1 from t2);
    子查询外部的语句可以是insert / update / delete / select 的任意一个
  2. 根据子查询结果不同，分为：
  • 标量子查询（子查询结果为单个值）
  • 列子查询（子查询结果为一列）
  • 行子查询（子查询结果为一行）
  • 表子查询（子查询结果为多行多列）
  1. 根据子查询的位置，分为：where 之后，from之后，select之后
标量子查询

子查询返回的结果是单个值（数字，字符串，日期等），最简单的形式，这种子查询称为标量子查询。
常用的操作符：= <> > >= < <=

sql">-- 标量子查询
-- 1. 查询“销售部”的所有员工信息
-- a.查询’销售部‘部门id
select id from dept where name = '销售部';-- b. 根据销售部部门id，查询员工信息
select * from employee where dept_id = 4;
-- 最终查询语句为
select * from employee where dept_id = (select id from dept where name = '销售部')-- 2.查询在“周美辰”入职之后的员工信息
-- a 查询’周美辰‘入职日期
select entrydate from employee where name = '周美辰';-- b 查询在“周美辰”入职之后的员工信息
select * from employee where entrydate > '2006-06-06';
-- 最终语句
select * from employee where entrydate > (select entrydate from employee where name = "周美辰");
列子查询

子查询返回地结果是一列（可以是多行），这种子查询称为列子查询。
常用的操作符：IN ，NOT IN , ANY , SOME , ALL

操作符描述
IN在指定范围之内，多选一
NOT IN不在指定范围
ANY子查询返回列表中，有任意一个满足即可
SOME与ANY等同，使用SOME的地方都可以使用ANY
ALL子查询返回
sql">-- 列子查询
-- 1.查询“销售部”和“市场部”的所有员工信息
-- a 查询销售部和市场部的id
select id from dept where name in ("销售部","市场部");
-- b 查询员工信息
select * from employee where dept_id in (2,4);
-- 最终查询
select * from employee where dept_id in (select id from dept where name in ("销售部","市场部"));-- 2.查询比财务部所有人工资都高的员工
-- a 查询财务部的员工工资
select id from dept where name = '财务部';
select salary from employee where dept_id = 3;
select salary from employee where dept_id = (select id from dept where name = '财务部');
-- b 查询比财务部工资高的员工
select * from employee where salary > all(select salary from employee where dept_id = (select id from dept where name = '财务部'));-- 查询比研发部其中任意一人工资高地员工信息
-- a 查询研发部员工工资
select salary from employee where dept_id = (select id from dept where name = "研发部");-- b 查询比研发部高的员工信息
select * from employee where salary > any(select salary from employee where dept_id = (select id from dept where name = "研发部"));
行子查询

子查询返回地结果是一行（可以是多列），这种子查询称为行子查询
常用操作符：=，<> , IN , NOT IN

sql">-- 行子查询
-- 查询与“李阳”的薪资及直属领导相同的员工信息
-- a 查询李阳的薪资和直属领导
select salary,managerid from employee where name = '李阳';
-- b 查询相应员工信息
select * from employee where salary = 12500 and managerid = 1;
select * from employee where (salary,managerid) = (12500,1);
-- 最终查询语句
select * from employee where (salary, managerid) = (select salary,managerid from employee where name = '李阳');
表子查询

子查询返回地结果是多行多列，这种子查询为表子查询
常用操作符：IN

sql">-- 表子查询
-- 1.查询与“梁朝朝”，“小涛”的职位和薪资相同的员工信息
-- a 查询梁朝朝和小涛的职位和薪资
select job,salary from employee where name = "梁朝朝" or name = '小涛';
-- b 查询相应的员工信息
select * from employee where (job,salary) in (select job,salary from employee where name = "梁朝朝" or name = '小涛');-- 2.查询入职日期是’2006-08-03‘之后的员工信息和部门信息
-- a.查询入职日期是’2006-08-03‘之后的员工信息
select * from employee where entrydate > '2006-08-03';
-- b.这部分员工对应的部门信息(将查询的员工信息作为一张新表进行查询)
select e.*,d.* from (select * from employee where entrydate > '2006-08-03') e left join dept d on e.dept_id = d.id;

多表查询应用

sql">-- 1.查询员工的姓名，年龄，职位，部门信息(隐式内连接)
-- 连接条件 employee.dept_id = dept.id
select e.name,e.age,e.job,d.name from employee e , dept d where e.dept_id = d.id;-- 2.查询年龄小于30岁的员工姓名，年龄，职位，部门信息（显式内连接）
select e.name,e.age,e.job,d.name from employee e join dept d on e.dept_id = d.id where e.age < 30; -- 3.查询拥有员工的部门id，部门名称   distinct 可去重
select distinct d.* from dept d,employee e where e.dept_id = d.id;-- 4.查询所有年龄大于40岁的员工，及其所属的部门名称；如果没有部门也需要展示出来
select e.*,d.name from employee e left join dept d on e.dept_id = d.id where e.age > 40;-- 5.查询所有员工的工资等级
select e.name,s.grade from employee e join salgrade s where e.salary between s.losal and s.hisal;-- 6.查询“研发部”所有员工的信息及工资等级
select id from dept where name = "研发部";
select e.*,s.grade from employee e join salgrade s on e.salary between s.losal and s.hisal where e.dept_id = (select id from dept where name = "研发部");
select * from employee e , dept d,salgrade s where e.dept_id = d.id and (e.salary between s.losal and s.hisal) and d.name = '研发部';-- 7.查询“研发部”员工的平均工资
select avg(e.salary) as '平均工资' from employee e join dept d on e.dept_id = d.id where d.name = '研发部';-- 8.查询工资比“周围”高的员工信息
select salary from employee where name = '周围';
select * from employee where salary > (select salary from employee where name = '周围');-- 9.查询比平均薪资高的员工信息
select avg(salary) from employee ; 
select * from employee where salary > (select avg(salary) from employee);-- 10查询低于本部门平均工资的员工信息
-- a 查询指定部门的平均薪资
select avg(salary) from employee e1 where e1.dept_id = 1;
select avg(salary) from employee e1 where e1.dept_id = 2;
......
-- b 查询低于部门薪资的员工
select * from employee e2 where e2.salary < (select avg(salary) from employee e1 where e1.dept_id = e2.dept_id);
select *,(select avg(salary) from employee e1 where e1.dept_id = e2.dept_id) from employee e2 where e2.salary < (select avg(salary) from employee e1 where e1.dept_id = e2.dept_id);-- 11.查询所有的部门信息，并统计部门的员工人数
select count(*) from employee where dept_id = 1; 
select * ,(select count(*) from employee where dept_id = d.id) ' 人数' from dept d;

http://www.ppmy.cn/embedded/156501.html

相关文章

【Unity】 HTFramework框架（五十九）快速开发编辑器工具（Assembly Viewer + ILSpy）

【Unity】 HTFramework框架（五十九）快速开发编辑器工具（Assembly Viewer + ILSpy）

更新日期&#xff1a;2025年1月23日。 Github源码&#xff1a;[点我获取源码] Gitee源码&#xff1a;[点我获取源码] 索引 开发编辑器工具MouseRayTarget焦点视角Collider线框Assembly Viewer搜索程序集ILSpy反编译程序集搜索GizmosElement类找到Gizmos菜单找到Gizmos窗口分析A…
阅读更多...
网工_数据链路层的作用

网工_数据链路层的作用

2025.01.21&#xff1a;网工老姜学习笔记 第7节 数据链路层的作用 7.1 数据链路层的地位7.2 数据链路层信道类型&#xff08;主要2种&#xff09;7.3 数据链路层的作用7.3.1 封装成帧&#xff08;前面加开始&#xff0c;后面加结束&#xff09;7.3.2 透明传输7.3.3 差错控制&am…
阅读更多...
JVM面试题解，垃圾回收之“分代回收理论”剖析

JVM面试题解，垃圾回收之“分代回收理论”剖析

一、什么是分代回收 我们会把堆内存中的对象间隔一段时间做一次GC&#xff08;即垃圾回收&#xff09;&#xff0c;但是堆内存很大一块&#xff0c;内存布局分为新生代和老年代、其对象的特点不一样&#xff0c;所以回收的策略也应该各不相同 对于“刚出生”的新对象&#xf…
阅读更多...
基于单片机的直流电机控制系统（论文+源码）

基于单片机的直流电机控制系统（论文+源码）

1 系统方案设计 本设计基于单片机的直流电机控制系统的总体架构设计如图2.1所示&#xff0c;其采用STM32F103单片机作为控制器&#xff0c;结合ESP8266 WiFi通信模块、L9110电机驱动电路、OLED液晶、按键等构成整个系统。用户在使用时&#xff0c;可以通过按键或者手机APP设定直…
阅读更多...
人声检测原理VAD

人声检测原理VAD

在机器人的研究中&#xff0c;机器人与人语音交互是一个重要的功能&#xff0c;在语音交互中&#xff0c;人声检测至关重要。不论是在手机中&#xff0c;还是在esp32芯片上&#xff0c;都需要一种简单快捷的方式来检测本地语音&#xff0c;滤掉杂音和噪音。 机器人启动后会一直…
阅读更多...
linux+docker+nacos+mysql部署

linux+docker+nacos+mysql部署

一、下载 docker pull mysql:5.7 docker pull nacos/nacos-server:v2.2.2 docker images 二、mysql部署 1、创建目录存储数据信息 mkdir ~/mysql cd ~/mysql 2、运行 MySQL 容器 docker run -id \ -p 3306:3306 \ --name mysql \ -v $PWD/conf:/etc/mysql/conf.d \ -v $PWD/…
阅读更多...
喵语者：猫咪行为学分析

喵语者：猫咪行为学分析

文章目录 肢体语言叫声含义生活习性社交行为异常行为 肢体语言 尾巴直立&#xff1a;友好或自信&#xff1b;尾尖弯曲表示非常开心&#xff1b;当猫竖起尾巴并轻轻摇晃时&#xff0c;可能是它在邀请互动。尾巴快速摇动&#xff1a;不满、即将攻击或兴奋的表现&#xff1b;快速…
阅读更多...
spring boot中实现手动分页

spring boot中实现手动分页

手动分页 UserMapper.xml <?xml version"1.0" encoding"UTF-8"?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" > <mapper namespace"cn.m…
阅读更多...
最新文章

Copyright @ 2022~2023