MySQL（五）——多表查询

上期文章

MySQL（四）——约束

文章目录

  • 上期文章
  • 多表关系
    • 一对多（多对一）
    • 多对多
    • 多表外键关系可视化
    • 一对一
  • 多表查询概述
    • 笛卡尔积
    • 多表查询分类
      • 连接查询
  • 内连接
    • 隐式内连接
    • 显式内连接
  • 外连接
    • 左外连接
    • 右外连接
  • 自连接
  • 联合查询 union，union all
  • 子查询
    • 标量子查询
    • 列子查询
    • 行子查询
    • 表子查询
  • 多表查询案例
  • 总结
    • 多表关系
    • 多表查询

多表关系

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

  • 一对多（多对一）
  • 多对多
  • 一对一

一对多（多对一）

一个部门对应多个员工，一个员工对应一个部门
实现：在多的一方建立外键，指向一的一方的主键
在这里插入图片描述

多对多

一个学生可以选修多门课程，一门课程也可以供多个学生选择
实现：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键
在这里插入图片描述

create table student(
id int auto_increment primary key comment '主键ID', name varchar(10) comment '姓名',
no varchar(10) comment '学号'
) comment '学生表';
insert into student values (null, '黛绮丝', '2000100101'),(null, '谢逊', '2000100102'),(null, '殷天正', '2000100103'),(null, '韦一笑', '2000100104');

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);

多表外键关系可视化

在这里插入图片描述
在这里插入图片描述

一对一

一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率
实现：在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的（UNQUE）
在这里插入图片描述

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 '用户基本信息表';
create table tb_user_edu(
id int auto_increment primary key comment '主键ID', degree varchar(20) comment '学历',
major varchar(50) comment '专业',
primaryschool varchar(50) 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 '用户教育信息表';
insert into tb_user(id, name, age, gender, phone) values (null,'黄渤',45,'1','18800001111'), (null,'冰冰',35,'2','18800002222'), (null,'码云',55,'1','18800008888'), (null,'李彦宏',50,'1','18800009999');
insert into tb_user_edu(id, degree, major, primaryschool, middleschool,
university, userid) values
(null,'本科','舞蹈','静安区第一小学','静安区第一中学','北京舞蹈学院',1), (null,'硕士','表演','朝阳区第一小学','朝阳区第一中学','北京电影学院',2), (null,'本科','英语','杭州市第一小学','杭州市第一中学','杭州师范大学',3), (null,'本科','应用数学','阳泉第一小学','阳泉区第一中学','清华大学',4);

多表查询概述

create table dept(
id int auto_increment comment 'ID' primary key,
name varchar(50) not null comment '部门名称'
)comment '部门表';
INSERT INTO dept (id, name) VALUES (1, '研发部'), (2, '市场部'),(3, '财务部'), (4, '销售部'), (5, '总经办'), (6, '人事部');

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 '员工表';
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);

alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id);

笛卡尔积

select * from emp,dept;

102条结果
在这里插入图片描述
笛卡尔积：在数学中，两个集合A集合和B集合的所有组合情况，在多表查询时，需要消除无效的笛卡尔积
在这里插入图片描述

select * from emp,dept where emp.dept_id = dept.id order by emp.id;

多表查询分类

连接查询

内连接：相当于查询A、B交集部分数据
外连接：

  • 左外连接：查询左表所有数据，以及两张表交集部分数据
  • 右外连接：查询右表所有数据，以及两张表交集部分数据

自连接：当前表与自身的连接查询，自连接必须使用表别名

内连接

隐式内连接

SELECT 字段列表 FROM 表1,表2 WHERE 条件...;

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

/*表起了别名之后只能使用别名*/
select e.name,d.name from emp e,dept d where e.dept_id = d.id;

显式内连接

SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 连接条件...;

select e.name,d.name from emp e inner join dept d on e.dept_id = d.id ;

外连接

左外连接

SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件...;

相当于查询表1（左表）的所有数据包含表1和表2交集部分的数据

select e.*,d.name from emp e left outer join dept d on d.id = e.dept_id;

右外连接

SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件...;

相当于查询表2（右表）的所有数据包含表1和表2交集部分的数据

select d.*,e.* from emp e right join dept d on d.id = e.dept_id;
select d.*,e.* from dept d left join emp e on d.id = e.dept_id;

自连接

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

自连接查询，可以是内连接查询，也可以是外连接查询

select e.name '员工',m.name '领导' from emp e join emp m on e.managerid = m.id;
select e.name '员工',m.name '领导' from emp e,emp m where e.managerid = m.id;

select m.name '员工',e.name '领导' from emp m left join emp e on m.managerid = e.id;

联合查询 union，union all

多次查询的结果合并起来，形成一个新的查询结果集

SELECT 字段列表 FROM 表A...
UNION [ALL]
SELECT 字段列表 FROM 表B...;

/*union all直接合并结果，所以有重复数据*/
select * from emp where salary < 5000
union all
select * from emp where age > 50;

/*union会把重复数据删掉*/
select * from emp where salary < 5000
union
select * from emp where age > 50;

对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致

union all会将全部的数据直接合并在一起，union会对合并之后的数据去重

子查询

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

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

子查询外部的语句可以是INSERT/UPDATE/DELETE/SELECT中的任何一个
根据子查询结果不同，分为：

  • 标量子查询（子查询结果为单个值）
  • 列子查询（子查询结果为一列）
  • 行子查询（子查询结果为一行）
  • 表子查询（子查询结果为多行多列）

根据子查询位置，分为WHERE之后、FROM之后、SELECT之后

标量子查询

子查询返回的结果是单个值（数字、字符串、日期等），最简单的形式

常用的操作符：= <> > >= < <=

select * from emp where dept_id = (
    select id from dept where name = '销售部'
);

select * from emp where entrydate > (
    select entrydate from emp where name = '方东白'
);

列子查询

子查询返回的结果是一列（可以是多行）

常用的操作符：IN、NOT IN、ANY、SOME、ALL

操作符描述
IN在指定的集合范围之内，多选一
NOT IN不在指定的集合范围之内
ANY子查询返回列表中，有任意一个满足即可
SOME与ANY等同，使用SOME的地方都可以使用ANY
ALL子查询返回列表的所有值都必须满足
select * from emp where dept_id in (
    select id from dept where name = '销售部' or name = '市场部'
);

select * from emp where salary > (
    select max(salary) from emp where dept_id = (
        select id from dept where name = '财务部'
        )
    );

select * from emp where salary > all(
    select salary from emp where dept_id = (
        select id from dept where name = '财务部'
        )
    );

select * from emp where salary > any(
    select salary from emp where dept_id = (
        select id from dept where name = '研发部'
        )
    );

行子查询

子查询返回的结果是一行（可以是多列）

常用的操作符：=、<>、IN、NOT IN

select * from emp where salary = (
    select salary
    from emp
    where name = '张无忌')
and managerid = (
    select managerid
    from emp
    where name = '张无忌');

select * from emp where (salary, managerid) = (
    select salary, managerid from emp where name = '张无忌'
    );

表子查询

子查询返回的结果是多行多列

常用的操作符：IN

/*查询与鹿杖客，宋远桥的职位和薪资相同的员工信息*/
select * from emp where (job, salary) in (select job, salary from emp where name in ('鹿杖客', '宋远桥'));

/*查询入职日期是2006-01-01之后的员工信息，及其部门信息*/
select e.*,dept.* from (select * from emp where entrydate > '2006-01-01') e left join dept on e.dept_id = dept.id;

多表查询案例

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);

/*查询员工的姓名、年龄、职位、部门信息*/
select e.name,e.age,e.job,d.name from emp e left join dept d on d.id = e.dept_id;
/*查询年龄小于30岁的员工姓名、年龄、职位、部门信息*/
select e.name,e.age,e.job,d.name from emp e left join dept d on d.id = e.dept_id where e.age < 30;
/*查询拥有员工的部门ID、部门名称*/
select distinct d.id,d.name from emp e,dept d where d.id = e.dept_id;
/*查询所有年龄大于40岁的员工，及其归属的部门名称；如果员工没有分配部门，也需要展示出来*/
select e.*,d.name from emp e left join dept d on d.id = e.dept_id where e.age > 40;
/*查询所有员工的工资等级*/
select e.*,s.grade from emp e left join salgrade s on e.salary >= s.losal and e.salary <= s.hisal;
/*查询研发部所有员工的信息及工资等级*/
select y.*, s.grade from (select e.* from emp e,dept d where e.dept_id = d.id and d.name = '研发部') y left join salgrade s on y.salary between s.losal and s.hisal;
select * from emp e,dept d,salgrade s where e.dept_id = d.id and d.name = '研发部' and (e.salary between s.losal and s.hisal);
/*查询研发部员工的平均薪资*/
select avg(e.salary) from emp e left join dept d on d.id = e.dept_id where d.name = '研发部';
/*查询工资比灭绝高的员工信息*/
select * from emp e where e.salary > (
    select salary from emp e where e.name = '灭绝'
    );
/*查询比平均薪资高的员工信息*/
select * from emp e where e.salary > (
    select avg(e.salary) from emp e
    );
/*查询低于本部门平均工资的员工信息*/
select e1.* from emp e1 where e1.salary < (
    select avg(e2.salary) from emp e2 where e1.dept_id = e2.dept_id
    );

select *,(select avg(e2.salary) from emp e2 where e2.dept_id = e1.dept_id) from emp e1 where e1.salary < (
    select avg(e2.salary) from emp e2 where e1.dept_id = e2.dept_id
    );


/*查询所有的部门信息，并统计部门的员工人数*/
select n.name,count(n.id) from (select e.id,d.name from emp e right join dept d on e.dept_id = d.id) n group by n.name ;

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

/*查询所有学生的选课情况，展示出学生名称、学号、课程名称*/
select c.name, c.no, course.name from (select student.name,student.no,student_course.courseid from student left join student_course on student.id = student_course.studentid) c left join course on c.courseid = course.id;

总结

多表关系

一对多：在多的一方设置外键，关联一的一方的主键
多对多：建立中间表，中间表包含两个外键，关联两张表的主键
一对一：用于表结构拆分，在其中任何一方设置外键(UNIQUE)，关联另一方的主键

多表查询

在这里插入图片描述

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：/a/337045.html

如若内容造成侵权/违法违规/事实不符，请联系我们进行投诉反馈qq邮箱809451989@qq.com，一经查实，立即删除！

相关文章

python-基础篇-变量

python-基础篇-变量

文章目录 变量的基本使用目标01. 变量定义1) 变量演练1 —— iPython2) 变量演练 2 —— PyCharm3) 变量演练 3 —— 超市买苹果思考题 02. 变量的类型2.1 变量类型的演练 —— 个人信息2.2 变量的类型2.3 不同类型变量之间的计算1) **数字型变量** 之间可以直接计算2) **字符串…
阅读更多...
Python基础第四篇（Python函数）

Python基础第四篇（Python函数）

文章目录 一、函数介绍二、函数的定义三、函数的参数与返回值四、函数说明文档五、函数的嵌套六、变量域七、函数案例1.源代码2.读出结果 在程序设计领域&#xff0c;函数成为一个不可或缺的角色&#xff0c;它们为我们提供了精练、高效和易于管理的编程方式。本篇博客将带您深…
阅读更多...
CentOS 7安装Java并配置环境

CentOS 7安装Java并配置环境

一、安装Java环境 1、检查系统是否安装Java [rootlocalhost ~]# java -version 2、更新系统软件包 [rootlocalhost ~]# yum update #遇到[y/n],选择y并回车&#xff0c;耐心等待下载完毕&#xff0c;之后系统会自动检验更新的软件包遇到 /var/run/yum.pid 已被锁定 /var/…
阅读更多...
【动态规划】【数学】【C++算法】805 数组的均值分割

【动态规划】【数学】【C++算法】805 数组的均值分割

作者推荐 【动态规划】【数学】【C算法】18赛车 本文涉及知识点 动态规划 数学 805 数组的均值分割 给定你一个整数数组 nums 我们要将 nums 数组中的每个元素移动到 A 数组 或者 B 数组中&#xff0c;使得 A 数组和 B 数组不为空&#xff0c;并且 average(A) average(B)…
阅读更多...
nuclei安装；linux上 以及使用教程

nuclei安装；linux上 以及使用教程

kali安装go环境_go1.17 kali安装-CSDN博客Ubuntu完美解决Github网站打不开问题 - 一抹烟霞 - 博客园 (cnblogs.com) All releases - The Go Programming Language 然但是上面两个我似乎都没用到网上的教程 也不适用 一个网不好 一个apt没找到包 然后我先试试了版本 结果 我的…
阅读更多...
BGP Origin 属性控制选路试验

BGP Origin 属性控制选路试验

一、拓朴图&#xff1a; 二、配置步骤&#xff1a; 1、配置 IP 2、配置 IGP&#xff0c;我们这里用了静态&#xff0c;互相宣告了对端接口和 Loopback 0 3、配置 BGP 4、在 R1 上通过 BGP 宣告 1.1.1.1&#xff0c;查看 R2 的路由&#xff0c;发现两条 1.1.1.1 的路由&#x…
阅读更多...
Vue中的组件

Vue中的组件

在应用程序的开发中&#xff0c;组件是不可缺少的。在Vue的使用中&#xff0c;同样也会用到组件。 　　vue组件的一般知识点&#xff1a; 　　1、组件的名字唯一&#xff1b; 　　2、组件以Html形式书写&#xff1b; 　　3、组件可以复用&#xff1b; 　　4、组件可以嵌套&…
阅读更多...
postgresql（Windows）初始化数据库教程

postgresql（Windows）初始化数据库教程

省流&#xff1a;本文章内容讲的是如何初始化postgresql数据库环境&#xff0c;前提是已经安装好postgresql数据库&#xff0c;安装步骤参考postgresql&#xff08;Windows&#xff09;安装教程 # 开始&#xff1a;安装postgresql-12.14-2-windows-x64.exe完成后进行初始化数据…
阅读更多...
gin中间件篇

gin中间件篇

1. 全局中间件 所有请求都经过此中间件 package mainimport ("fmt""time""github.com/gin-gonic/gin" )// 定义中间 func MiddleWare() gin.HandlerFunc {return func(c *gin.Context) {t : time.Now()fmt.Println("中间件开始执行了&quo…
阅读更多...
《Linux高性能服务器编程》笔记04

《Linux高性能服务器编程》笔记04

Linux高性能服务器编程 本文是读书笔记&#xff0c;如有侵权&#xff0c;请联系删除。 参考 Linux高性能服务器编程源码: https://github.com/raichen/LinuxServerCodes 豆瓣: Linux高性能服务器编程 文章目录 Linux高性能服务器编程第09章I/O复用9.1 select系统调用9.2 po…
阅读更多...
JVM之java内存区域[1]（程序计数器、栈）

JVM之java内存区域[1]（程序计数器、栈）

文章目录 版权声明零 运行时数据区一 程序计数器1.1 加载阶段1.2 执行阶段1.3 多线程情况 二 栈2.1 java虚拟机栈2.2 java虚拟机栈帧的组成2.2.1 局部变量表2.2.2 操作数栈2.2.3 帧数据 2.3 栈内存溢出2.4 设置帧大小2.5 本地方法栈 版权声明 本博客的内容基于我个人学习黑马程…
阅读更多...
如何快速打开github

如何快速打开github

作为一个资深码农&#xff0c;怎么能不熟悉全球最大的同性交友社区——github呢&#xff0c;但头疼的是github有时能打开&#xff0c;有时打不开&#xff0c;这是怎么回事&#xff1f; 其实问题出在github.com解析DNS上&#xff0c;并不是需要FQ。下面提供一个方法&#xff0c;…
阅读更多...
C++：基于C的语法优化

C++：基于C的语法优化

C&#xff1a;基于C的语法优化 命名空间命名空间域域作用限定符展开命名空间域 输入输出缺省参数全缺省参数半缺省参数 函数重载参数类型不同参数个数不同参数类型的顺序不同 引用基本语法按引用传递返回引用引用与指针的区别 内联函数autoauto与指针和引用结合 范围for循环nul…
阅读更多...
官方版2345加速浏览器（好用的浏览器分享）

官方版2345加速浏览器（好用的浏览器分享）

官方版2345加速浏览器&#xff08;好用的浏览器分享&#xff09; 2345加速浏览器拥有智能拦截骚扰广告&#xff0c;识别欺诈网站&#xff0c;云收藏夹等功能&#xff0c;高速上网、不假死、不卡机&#xff0c;是一款强大的多功能网页浏览器。 使用2345加速浏览器,您可以轻松应对…
阅读更多...
DHCP配置（路由器，交换机）

DHCP配置（路由器，交换机）

DHCP接口地址池配置 拓扑 PC配置DHCP点击应用。 路由器配置命令 <Huawei>sy Enter system view, return user view with CtrlZ. [Huawei]int g0/0/1[Huawei-GigabitEthernet0/0/1]ip address 10.1.1.1 24[Huawei-GigabitEthernet0/0/1]q[Huawei]dhcp enable Info: T…
阅读更多...
【日常聊聊】边缘计算的挑战和机遇

【日常聊聊】边缘计算的挑战和机遇

&#x1f34e;个人博客&#xff1a;个人主页 &#x1f3c6;个人专栏&#xff1a; 日常聊聊 ⛳️ 功不唐捐&#xff0c;玉汝于成 目录 前言 正文 边缘计算的挑战和机遇 一&#xff1a;数据安全与隐私保护 二&#xff1a;网络稳定性与可靠性 三&#xff1a;实时性与性能优…
阅读更多...
电压检测芯片适用于哪些应用领域？

电压检测芯片适用于哪些应用领域？

原文链接&#xff1a; 电压检测芯片适用于哪些应用领域&#xff1f; - 知乎 (zhihu.com) 电压检测基本涉及到电子世界的方方面面。 我上一份工作是做无人机飞控研发&#xff0c;无人机在使用过程中是需要事件监测电压的&#xff0c;还需要针对电压对航行进行预估&#xff0c;…
阅读更多...
推荐新版AI智能聊天系统网站源码ChatGPT NineAi

推荐新版AI智能聊天系统网站源码ChatGPT NineAi

Nine AI.ChatGPT是基于ChatGPT开发的一个人工智能技术驱动的自然语言处理工具&#xff0c;它能够通过学习和理解人类的语言来进行对话&#xff0c;还能根据聊天的上下文进行互动&#xff0c;真正像人类一样来聊天交流&#xff0c;甚至能完成撰写邮件、视频脚本、文案、翻译、代…
阅读更多...
SpringMVC(八）处理AJAX请求

SpringMVC(八）处理AJAX请求

一、处理AJAX之准备工作： 首先我们创建一个新的工程： 我们将pom.xml复制过来: <?xml version="1.0" encoding="UTF-8"?> <project xmlns="http://maven.apache.org/POM/4.0.0"xmlns:xsi="http://www.w3.org/2001/XMLSchema-in…
阅读更多...
两条链表相同位数相加[中等]

两条链表相同位数相加[中等]

优质博文IT-BLOG-CN 一、题目 给你两个非空的链表&#xff0c;表示两个非负的整数。它们每位数字都是按照逆序的方式存储的&#xff0c;并且每个节点只能存储一位数字。请你将两个数相加&#xff0c;并以相同形式返回一个表示和的链表。你可以假设除了数字0之外&#xff0c;这…
阅读更多...
最新文章

Copyright @ 2022~2023