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

【MySQL】多表查询全解-【多表关系/内外自连接/子查询/多表查询案例链接】（可cv代码＆案例演示）

news 来源：原创 2024/5/21 16:28:59

前言

大家好吖，欢迎来到 YY 滴MySQL系列 ，热烈欢迎！ 本章主要内容面向接触过C++ Linux的老铁
主要内容含：
在这里插入图片描述

欢迎订阅 YY滴C++专栏！更多干货持续更新！以下是传送门！

  • YY的《C++》专栏
  • YY的《C++11》专栏
  • YY的《Linux》专栏
  • YY的《数据结构》专栏
  • YY的《C语言基础》专栏
  • YY的《初学者易错点》专栏
  • YY的《小小知识点》专栏
  • YY的《单片机期末速过》专栏
  • YY的《C++期末速过》专栏
  • YY的《单片机》专栏
  • YY的《STM32》专栏
  • YY的《数据库》专栏
  • YY的《数据库原理》专栏

目录

  • ※备注：数据准备环境在下文第六点【数据准备环节】
  • ※基本内容概述
  • 一.查询基本语法＆笛卡尔积问题（场景演示讲解）
  • 二.多表关系（案例讲解＆可cv代码）
    • 1.多表关系概述
    • 2.用可视化界面展示多表关系
    • 3.多表关系-一对多/多对一-（多的一方建立外键指向少的一方）
    • 4.多表关系-一对一-（拆分表并设置唯一约束unique）
    • 5.多表关系-多对多-（建立中间表）
  • 三.多表查询—连接查询连接
    • ※多表查询分类概述
    • 1.连接查询-内连接查询(语法＆示意图＆案例演示)
    • ※多表查询过程【起别名】注意事项！！！
    • 2.连接查询-外连接查询(语法＆示意图＆案例演示)
    • 3.连接查询-自连接查询(语法＆示意图＆案例演示)
  • 四.多表查询—联合查询（语法＆示意图＆案例演示）
  • 五.多表查询—子查询(嵌套查询)
    • 1.子查询语法
    • 2.子查询分类（根据查询结果区分＆根据子查询位置分）
    • 3.标量子查询（子查询结果为单个值）
    • 4.列子查询（子查询结果为一列）
    • 5.行子查询（子查询结果为一行）
    • 6.表子查询（子查询结果为多行多列）
  • 六.多表查询练习（案例讲解＆可cv代码演示）
    • 1.案例需求
    • 2.数据准备环节
    • 3.案例演示＆可cv代码

※备注：数据准备环境在下文第六点【数据准备环节】

※基本内容概述

  • 主要内容如下
    在这里插入图片描述

一.查询基本语法＆笛卡尔积问题（场景演示讲解）

  • 基本概念如下所示
    在这里插入图片描述
  • 单表查询语法：select * from emp , dept ;
  • 加入 消除笛卡尔积意识 的查询：
  • 我们举的 是建立好了一对多表关系的例子
  • 一对多原理部分具体可看下文（多的一方建立外键指向少的一方）
  • 我们先设置了一个员工表emp，部门表dept
  • 员工表在这里插入图片描述
  • 部门表
    在这里插入图片描述
  • 我们发现普通查询select * from emp , dept ;出的结果有102条
  • 即笛卡尔积个数： 17*6=102
  • 我们发现其中有很多重合的部分
    在这里插入图片描述
  • 我们改用加入 消除笛卡尔积意识 的查询：select * fron emp , dept where emp.dept_id = dept.id;
  • 发现数目正常，一一对应
    在这里插入图片描述

二.多表关系（案例讲解＆可cv代码）

1.多表关系概述

  • 如下所示
    在这里插入图片描述

2.用可视化界面展示多表关系

  • 这里我们那下文会提到的 多对多 举例
  • 我们按如下图操作，即可用可视化界面展示多表关系
    在这里插入图片描述
    在这里插入图片描述

3.多表关系-一对多/多对一-（多的一方建立外键指向少的一方）

  • 核心思路：在多的一方建立外键，指向一的一方的主键
    在这里插入图片描述
  • 具体操作即普通设置外键操作：
在这里插入代码片

4.多表关系-一对一-（拆分表并设置唯一约束unique）

  • 在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的（UNIQUE)
    在这里插入图片描述
  • 设置用户基本信息表
create table tb_user(id int auto_increment primary key comment'主键ID',name varchar(10)comment'姓名',age int comment'年龄',gender char(1)comment'1:男，2:女',phone char(11)comment'手机号'
)comment'用户基本信息表';
  • 设置用户教育信息表
  • 注意用户id的约束：unique 
create table tb_user_edu(id int auto_increment primary key comment'主键ID',degree varchar(20)comment'学历',major varchar(50)comment'专业',primaryschool varchar(58)comment'小学',middleschool varchar(50)comment'中学',university varchar(50)comment'大学',userid int unique comment'用户ID',constraint fk_userid foreign key (userid) references tb_user(id)
)comment'用户教育信息表';

5.多表关系-多对多-（建立中间表）

  • 核心思路：建立中间表
    在这里插入图片描述
  • 设置学生表，并插入数据
create table student(id int auto_increment primary key comment'主键ID',name varchar(10)comment'姓名',no varchar(10) comment'学号'
)comment'学生表';
insert into student values（nul1,'黛绮丝','2000100101'）,（nul1,'谢逊','2000100102'）,（nul1,'般天正',‘2000100103'）,（nul1,'韦一笑','206
  • 设置课程表，并插入数据
create table course(id int auto_increment primary key comment'主键ID',name varchar(10) comment'课程名称'
)comment'课程表';
insert into course values (null,'Java'),(null,'PHP'), (null,'MySQL') ,(null,'Hadoop');
  • 核心操作，设置“学生课程中间表”，并插入数据 
create table student_course(id int auto_increment comment'主键'primary key,studentid int not null comment'学生ID',courseid int not null comment'课程ID',constraint fk_courseid foreign key (courseid) references course (id),constraint fk_studentid foreign key (studentid) references student (id))comment'学生课程中间表';insert into student_course values (null,1,1),(null,1,2),(null,1,3),(null,2,2),(null,2,3),(null,3,4);
  • 我们可以发现我们最后一步设置了 “两个字段 对应 两个外键”
  • 我们按如下图操作，即可用可视化界面展示多表关系：看清其外键对应关系；
    在这里插入图片描述
    在这里插入图片描述

三.多表查询—连接查询连接

※多表查询分类概述

  • 我们简单了解有哪两种查询方式，具体看下文
    在这里插入图片描述

1.连接查询-内连接查询(语法＆示意图＆案例演示)

  • 注意： inner可省略
  • 我们编写内连接sql语句时： 1.先列出表结构 2.再列出连接条件 在这里插入图片描述
--隐式内连接
SELECT 字段列表 FROM 表1,表2 WHERE 条件.…;

--显式内连接
SELECT 字段列表 FROM 表1 [INNER]JOIN 表2 ON 连接条件…;

--内连接演示--
--1.查询每一个员工的姓名，及关联的部门的名称（隐式内连接实现）
--表结构：emp，dept
--连接条件：emp.dept_id=dept.id
select emp.name , dept.name from emp , dept where emp.dept_id = dept.id ;--2.查询每一个员工的姓名，及关联的部门的名称（显式内连接实现）
---INNERJ0IN...0N...
--表结构：emp,dept
--连接条件：emp.dept_id = dept.id
select e.name, d.name from emp e inner join dept d on e.dept_id = d.id;

※多表查询过程【起别名】注意事项！！！

  • 在多表查询过程中，我们经常对表起别名，简化我们sql语句编写
  • 取别名 直接后面空格后跟即可，as可省略
  • 注意：如果已经起了别名，就不能通过表名限定字段！！！
    在这里插入图片描述

2.连接查询-外连接查询(语法＆示意图＆案例演示)

  • 如下图中示意图所示： 外连接可以查询内连接查不到的数据 ，
  • （左外连接）即图中蓝色部分,完全左表信息
    在这里插入图片描述
  • 我们再举个例子来理解：
  • 我们现在有一个员工表emp，部门表apt
  1. 使用左外连接：我们只能看到员工表，而不能看到部门表
  2. 使用右外连接：我们只能看到部门表，而不能看到员工表
  • 注意：在日常使用中，我们左外连接用的比较多，右外连接通常可以当作左外
--左外连接
SELECT 字段列表 FROM 表1 LEFT [OUTER]JOIN 表2 ON 条件…;

--右外连接
SELECT 字段列表 FROM 表1 RIGHI [OUTER] JOIN2 ON 条件…;

--1.查询dept表的所有数据，和对应的员工信息（左外连接)
--表结构:emp,dept
--连接条件:emp.dept_id =dept.id
select e.*, d.name from emp e left outer join dept d on e.dept_id = d.id;--2.查询dept表的所有数据，和对应的员工信息（右外连接)
--表结构:emp,dept
--连接条件:emp.dept_id =dept.id
select d.*, e.* from emp e right outer join dept d on e.dept_id = d.id;

3.连接查询-自连接查询(语法＆示意图＆案例演示)

  • 注意：自连接查询，可以是内连接查询，也可以是外连接查询。
    在这里插入图片描述
SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件…;

四.多表查询—联合查询（语法＆示意图＆案例演示）

  • 联合查询：即把多次查询的结果合并起来
    在这里插入图片描述
    注意点1：
  • union：会将全部的数据直接合并在一起，不去重
  • union all：会对合并之后的数据去重

注意点2：

  • 对于联合查询的多张表的 列数 必须保持一致， 字段类型 也需要保持一致
    在这里插入图片描述
-- 不去重
select * from emp where salary < 5000
union
select * from emp where age > 58;-- 去重
select * from emp where salary < 5000
union all
select * from emp where age > 50;

五.多表查询—子查询(嵌套查询)

1.子查询语法

  • 如下所示
    在这里插入图片描述
  • 具体做法是：先写出嵌套语句，再写外部语句
  • 下文有更详细演示
SELECT * FROM t1 WHERE column1= (SELECT column1 FROM t2);

2.子查询分类（根据查询结果区分＆根据子查询位置分）

  • 如下所示
    在这里插入图片描述

3.标量子查询（子查询结果为单个值）

  • 注意常用操作符：= <> > >= < <=
    在这里插入图片描述
--标量子查询--1.查询“销售部”的所有员工信息
--a.查询“销售部”部门ID
select id from dept where name ='销售部';
--b.根据销售部部门ID，查询员工信息
select * from emp where dept_id =(select id from dept where name ='销售部');--2.查询在“方东白”入职之后的员工信息
--a.查询方东白的入职日期
select entrydate from emp where name ='方东白';
--b.查询指定入职日期之后入职的员工信息
select * from emp where entrydate >(select entrydate from emp where name ='方东白');

4.列子查询（子查询结果为一列）

  • 注意常用操作符：IN、NOT IN、ANY、SOME、ALL
  • some 和 any 可视作一样的 在这里插入图片描述
--列子查询
--1.查询“销售部”和“市场部”的所有员工信息
--a.查询"销售部"和"市场部”的部门ID
select id from dept where name ='销售部'or name ='市场部;
--b.根据部门ID，查询员工信息
select * from emp where dept_id in(select id from dept where name ='销售部'or name ='市场部');--2.查询比财务部所有人工资都高的员工信息
--a.查询所有财务部人员工资
select id from dept where name ='财务部';
select salary from emp where dept_id =(select id from dept where name ='财务部');
--b.比财务部所有人工资都高的员工信息
select * from emp where salary >all( select salary from emp where dept_id =(select id from dept where name ='财务部'));--3.查询比研发部其中任意一人工资高的员工信息
--a.查询研发部所有人工资
select salary from emp where dept_id = (select id from dept where name ='研发部');
--b.比研发部其中任意一人工资高的员工信息
select * from emp where salary >any(select salary from emp where dept_id=(select id from dept where name='研发部'));

5.行子查询（子查询结果为一行）

  • 注意常用操作符：= <> IN NOT IN
    在这里插入图片描述
--行子查询--
1.查询与“张无忌”的薪资及直属领导相同的员工信息
--a.查询“张无忌”的薪资及直属领导
select salary,managerid from emp where name='张无忌';
--b.查询与“张无忌”的薪资及直属领导相同的员工信息
select * from emp where salary = 12500 and managerid = 1;
//写法2
select * from emp where (salary,managerid) = (12500,1) ;

6.表子查询（子查询结果为多行多列）

  • 表子查询的结果是张 临时表 ，和其他表进行 联查操作
  • 常用操作符： IN（不能用=） 
--表子查询--
--1.查询与“鹿杖客”，“宋远桥”的职位和薪资相同的员工信息
--a.查询“鹿杖客”，“宋远桥”的职位和薪资
select job,salary from emp where name='鹿杖客'or name='宋远桥';
--b.查询与“鹿杖客”，“宋远桥”的职位和薪资相同的员工信息
select *from emp where(job,salary)in(select job,salary from emp where name ='鹿杖客'or name='宋远桥');--2.查询入职日期是“2006-01-01”之后的员工信息，及其部门信息
--a.入职日期是“2006-81-81"之后的员工信息
select * from emp where entrydate > '2006-01-01';
--b.查询这部分员工，对应的部门信息；
select e.*, d.* from (select * from emp where entrydate > '2006-01-01') e left join dept d on e.dept_id = d.id ;

六.多表查询练习（案例讲解＆可cv代码演示）

1.案例需求

  • 案例需求如下
    在这里插入图片描述

文字描述

  1. 查询员工的姓名、年龄、职位、部门信息。
  2. 查询年龄小于30岁的员工姓名、年龄、职位、部门信息。
  3. 查询拥有员工的部门ID、部门名称。
  4. 查询所有年龄大于40岁的员工，及其归属的部门名称；如果员工没有分配部门，也需要展示出来。
  5. 查询所有员工的工资等级。
  6. 查询“研发部”所有员工的信息及工资等级。
  7. 查询“研发部”员工的平均工资。
  8. 查询工资比“灭绝”高的员工信息。
  9. 查询比平均薪资高的员工信息。
  10. 查询低于本部门平均工资的员工信息。
  11. 查询所有的部门信息，并统计部门的员工人数。
  12. 查询所有学生的选课情况，展示出学生名称，学号，课程名称

2.数据准备环节

-- 准备数据
create table dept(id   int auto_increment comment 'ID' primary key,name varchar(50) not null comment '部门名称'
)comment '部门表';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 dept (id, name) VALUES (1, '研发部'), (2, '市场部'),(3, '财务部'), (4, '销售部'), (5, '总经办'), (6, '人事部');
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);
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);

3.案例演示＆可cv代码

--1.查询员工的姓名、年龄、职位、部门信息（隐式内连接）
--表:emp,dept
--连接条件：emp.dept_id=dept.idselect e.name , e.age , e.job , d.name from emp e , dept d where e.dept_id = d.id;--2.查询年龄小于30岁的员工的姓名、年龄、职位、部门信息（显式内连接）
--表:emp，dept
--连接条件：emp.dept_id = dept.idselect 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--3.查询拥有员工的部门I0、部门名称(去重)
--表:emp，dept
连接条件:emp.dept_id = dept.id select distinct d.id, d.name from emp e , dept d where e.dept_id = d.id;--4. 查询所有年龄大于40岁的员工, 及其归属的部门名称; 如果员工没有分配部门, 也需要展示出来
--表: emp , dept
--连接条件: emp.dept_id = dept.id
--外连接select e.*, d.name from emp e left join dept d on e.dept_id = d.id where e.age > 40 ;--5. 查询所有员工的工资等级
--表: emp , salgrade
--连接条件 : emp.salary >= salgrade.losal and emp.salary <= salgrade.hisalselect e.* , s.grade , s.losal, s.hisal from emp e , salgrade s where e.salary >= s.losal and e.salary <= s.hisal;select e.* , s.grade , s.losal, s.hisal from 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.* , 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.idselect avg(e.salary) from emp e, dept d where e.dept_id = d.id and d.name = '研发部';--8. 查询工资比 "灭绝" 高的员工信息。
--a. 查询 "灭绝" 的薪资
select salary from emp where name = '灭绝';--b. 查询比她工资高的员工数据
select * from emp where salary > ( select salary from emp where name = '灭绝' );--9. 查询比平均薪资高的员工信息
--a. 查询员工的平均薪资
select avg(salary) from emp;--b. 查询比平均薪资高的员工信息
select * from emp where salary > ( select avg(salary) from emp );--10. 查询低于本部门平均工资的员工信息
--a. 查询指定部门平均薪资  1
select avg(e1.salary) from emp e1 where e1.dept_id = 1;
select avg(e1.salary) from emp e1 where e1.dept_id = 2;--b. 查询低于本部门平均工资的员工信息
select * from emp e2 where e2.salary < ( select avg(e1.salary) from emp e1 where e1.dept_id = e2.dept_id );--11. 查询所有的部门信息, 并统计部门的员工人数
select d.id, d.name , ( select count(*) from emp e where e.dept_id = d.id ) '人数' from dept d;select count(*) from emp where dept_id = 1;--12. 查询所有学生的选课情况, 展示出学生名称, 学号, 课程名称
--表: student , course , student_course
--连接条件: student.id = student_course.studentid , course.id = student_course.courseidselect s.name , s.no , c.name from student s , student_course sc , course c where s.id = sc.studentid and sc.courseid = c.id ;

相关文章：

  • QT子窗口关闭时自动释放及注意事项
  • VSCode好用插件
  • 手写简易操作系统(十一)--可编程中断控制器8259A
  • Vue-Electron配置及踩坑
  • 每日一题 第六十六期 洛谷 小朋友排队
  • Maven是什么？ Maven的概念+作用
  • 计算机网络-HTTP相关知识-RSA和ECDHE及优化
  • Unity类银河恶魔城学习记录11-15 p117 Ice and Fire item Effect源代码
  • 【详细介绍WebKit的结构】
  • 缓存最佳实践
  • Pointnet++改进即插即用系列：全网首发OREPA在线重新参数化卷积,替代普通卷积 |即插即用，提升特征提取模块性能
  • Fractions Again?!（UVA 10976）
  • linux系统编程 线程 p1
  • C#字典学习笔记
  • 实验：基于Red Hat Enterprise Linux系统的创建磁盘和磁盘分区（二、三）
  • [Vue CLI 3] 配置解析之 css.extract
  • 【编码】-360实习笔试编程题（二）-2016.03.29
  • 【个人向】《HTTP图解》阅后小结
  • Docker容器管理
  • EOS是什么
  • Essential Studio for ASP.NET Web Forms 2017 v2，新增自定义树形网格工具栏
  • gops —— Go 程序诊断分析工具
  • k8s如何管理Pod
  • Linux中的硬链接与软链接
  • Material Design
  • React Transition Group -- Transition 组件
  • React-生命周期杂记
  • session共享问题解决方案
  • spring security oauth2 password授权模式
  • SQLServer之创建显式事务
  • Vue 动态创建 component
  • yii2中session跨域名的问题
  • 力扣(LeetCode)22
  • 一些基于React、Vue、Node.js、MongoDB技术栈的实践项目
  • ​html.parser --- 简单的 HTML 和 XHTML 解析器​
  • ​ssh免密码登录设置及问题总结
  • ​人工智能之父图灵诞辰纪念日，一起来看最受读者欢迎的AI技术好书
  • ​软考-高级-系统架构设计师教程（清华第2版）【第9章 软件可靠性基础知识（P320~344）-思维导图】​
  • %3cli%3e连接html页面,html+canvas实现屏幕截取
  • （1）(1.13) SiK无线电高级配置（六）
  • （51单片机）第五章-A/D和D/A工作原理-A/D
  • （pytorch进阶之路）CLIP模型 实现图像多模态检索任务
  • （学习日记）2024.02.29：UCOSIII第二节
  • （转）nsfocus-绿盟科技笔试题目
  • (转)Windows2003安全设置/维护
  • （转）全文检索技术学习(三)——Lucene支持中文分词
  • (转)重识new
  • .form文件_SSM框架文件上传篇
  • .mysql secret在哪_MYSQL基本操作（上）
  • .net core 6 集成 elasticsearch 并 使用分词器
  • .net6 webapi log4net完整配置使用流程
  • @CacheInvalidate(name = “xxx“, key = “#results.![a+b]“,multi = true)是什么意思
  • @WebService和@WebMethod注解的用法
  • [ C++ ] STL_vector -- 迭代器失效问题
  • [ Linux 长征路第二篇] 基本指令head,tail,date,cal,find,grep,zip,tar,bc,unname