首页 > 数据库 >一文掌握MySQL多表查询技巧:告别繁琐操作,轻松搞定数据查询!

一文掌握MySQL多表查询技巧:告别繁琐操作,轻松搞定数据查询!

时间:2023-11-24 15:34:14浏览次数:46  
标签:多表 查询 dept emp MySQL where id select

在数据库的世界里,我们经常需要处理各种各样的数据。有时候,我们需要从多个表中查询数据,这时候就需要用到MySQL的多表查询了。今天,就让我们一起来了解一下MySQL多表查询的魅力吧!

一、表的关系简介

现实生活中,实体与实体之间肯定是有关系的,比如:部门和员工,老师和学生等。在设计表的时候,就应该体现出表与表之间的这种关系。

1.1 一对多

一对多:班级和学生,部门和员工,分类和商品。

一对多(1:n)建表原则:在从表(多方)创建一个字段,字段作为外键指向主表(一方)的主键。

1.2 多对多

多对多:老师和学生,学生和课程,用户和角色。

多对多(m:n)建表原则:需要创建第三张表,中间表中至少两个字段,这两个字段分别作为外键指向各自一方的主键。

SQL实现代码:

1.3 一对一

在实际的开发中应用不多,因为一对一可以创建成一张表。

实现方式: 可以在任意一方添加唯一外键指向另一方的主键(一般情况下,直接合成一张表即可)。

场景选择: 多用于单表的拆分,将一张表的基础字段放在一张表中,其他的详情字段放在另一张表中,从而提升效率,比如说:如果叫我们描述一个人,我们能从它的身体的基本信息来描述(身高,体重,姓名,年龄),也可以使用受教育信息(小学,中学,大学)等等。

1.4 表关系小结

二、笛卡尔积

在表连接查询时,由于没有指定表的连接条件,那么数据库默认情况下会将任意表的全部记录都匹配一边任意表。

这样达到的结果总的记录就是所有表的记录数的乘积就是笛卡尔积;所以在表连接时,一定要指定好表的连接条件,一般情况下是从表的外链 = 主表的主键

2.1 什么是笛卡尔积现象

需求:查询所有的员工和所有的部门。

select * from dept,emp;

结果如下:

如果左表是部门表,右表是员工表,左表中每一行记录与右表中的每一行记录全都匹配一次。

结果的行数=左表中行数x右表中行数

结果分析:

2.2 如何清除笛卡尔积

我们发现不是所有的数据组合都市有用的,只有员工表.dept_id = 部门表.id 的数据才是有用的,所以需要通过条件过滤掉没用的数据。

  • 设置过滤条件

  • 指定过滤条件 主表.主键=从表.外键

select * from dept,emp where dept.id = emp.dept_id;
  • 可以给表起别名
select * from dept d,emp e where d.id = e.dept_id;

注:以上过滤以后的结果称为隐式内连接。

你还在苦恼找不到真正免费的编程学习平台吗?可以试试云端源想!课程视频、在线书籍、在线编程、实验场景模拟、一对一咨询……你想要的全部学习资源这里都有,重点是统统免费!点这里即可查看

三、内连接查询

用左边表的记录去匹配右边表的记录,如果符合条件的则显示。
特点: 查询出来的记录必须符合表关联的条件。

隐式内连接

  • 看不到 join 关键字,条件使用where指定
select 列名 from 左表 [左表别名], 右表 [右表别名] where 主表.主键=从表.外键;

select * from dept,emp where dept.id=emp.dept_id;
  • 相当于查询A,B(员工表和部门表)的交集数据;

  • 使用where条件消除无效数据;

  • 字段要由对应的表名或别名调用。

显示内连接

无论是显示内连接还是隐式内连接查询结果是一样的,只是写法不同。

  • 使用 inner join … on 语句,可以省略 inner;
select 列名 from 左表 inner join 右表 on 主表.主键=从表.外键;
  • 如果是表关联的条件推荐使用on关键字;

  • 如果是其他普通的条件使用where。

案例:查询小龙的信息,显示员工id,姓名,地址,年龄和所在的部门名称,我们发现需要联合2张表同时才能查询出需要的数据,使用内连接。

内连接的流程
1)确定需要查询的表(dept、emp表);

2)确定连接条件(过滤掉不需要的数据,消除笛卡尔积);

3)确定查询条件,我们查询的是小龙的信息,员工表.name=‘小龙’;

4)确定查询的列名(员工id,姓名,地址,年龄,部门名称)。

代码实现如下:

结果如下:

四、外连接

特点: 需要指定一张查询主表,查询主表的记录必须全部显示,即使不符合表关联的条件

左外连接

左外连接:查询的数据以左表为准,即使在其他表中没有匹配的记录也会显示出来。

  • 使用 left outer join on,outer可以省略

  • 相当于查询A表所有数据和交集部分数据

  • select 列名 from 左表 left join 右表 on 表连接条件

需求:在部门表中增加一个行政部,需要查询所有的部门和员工,将部门表设置成左表,员工表设置成右表。

select * from dept;

insert into dept (name) values ('行政部');
-- 使用内连接查询

-- 需要查询所有的部门和员工,无论这个部门下有没有员工
select * from dept inner join emp on dept.id = emp.dept_id;

结果如下:

使用左外连接查询:

select * from dept left join emp on dept.id = emp.dept_id;

右外连接

右外连接:查询的数据以右表为准,即使在其他表中没有匹配的记录也会显示出来。

  • 使用right outer join … on,outer可以省略

  • 相当于查询B表所有数据和交集部分数据

  • select 列名 from 左表 left join 右表 on 表连接条件

需求:在员工表中增加一个员工,但该员工还未分配部门

select * from emp;

-- 不在任何一个部门
INSERT INTO `emp` VALUES (6, '小赵', '吉安', 26, '男', null);

-- 希望员工的信息全部显示出来
-- 使用内连接查询
select * from dept inner join emp on dept.id = emp.dept_id;

结果如下:

使用右外连接查询:

-- 使用右外连接查询
select * from dept right join emp on dept.id = emp.dept_id;

五、全连接

左连接是无论如何左表的数据都能够显示全,右连接是右表的数据无论如何都能够显示全面,那么如果希望左表和右表的数据都能够显示全面呢(在对方表中没有匹配的数据就以null补齐)?

这种连接查询我们称之为全连接(full join),但是很遗憾,MySQL并没有提供全连接,但Oracle支持;

虽然MySQL不支持全连接,但是我们可以利用MySQL提供的其它功能来完成全连接的功能:

left join + right join

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

全连接查询的结果如下:

Tips:

  • union关键字可以将两个或多个SQL语句的结果集拼接成一个结果集,前提是这些SQL语句的结果集列数必须相同;

  • union关键字自带去重功能,即去除重复的数据;

  • 如果需要保留重复的数据则可以使用union all关键字来连接多个SQL语句的结果集。

六、子查询

  • 一个查询语句结果做为另一个查询语句的条件;

  • 查询语句有嵌套,里面的查询称为子查询,外面的查询称为父查询;

  • 子查询要使用括号括起来。

准备数据:

查询的结果

子查询结果是单行单列的:子查询可以作为条件值,使用运算符去判断。

select * from 表名 where 字段名 = (子查询);

需求: 查询年龄最高的员工是谁?

-- 1. 查询年龄大的员工的详细信息
select max(age) from emp;

-- 2. 根据最高年龄到员工表查询到对应的员工信息
select * from emp where age = 26;

-- 使用子查询
select * from emp where age = (select max(age) from emp);

子查询结果是多行单列的:子查询可以作为条件值,使用运算符 in/any/all 去判断:

select * from 表名 where 字段名 IN/ANY/ALL(子查询)

下面看两个案例:

需求1:查询年龄大于23的员工的部门信息

-- 关联查询
SELECT
d.*
FROM
dept d
INNER JOIN emp e ON d.id = e.dept_id
WHERE
e.age > 23
-- 子查询

-- 1. 首先查询年龄大于23岁的员工所在的部门id
select dept_id from emp where age > 23;

-- 2. 再查询在这些部门id中部门的名字

-- ERROR 1242 (21000): Subquery returns more than 1 row 子查询返回了多行数据
select * from dept where id = (select dept_id from emp where age > 23);
-- 采用in 取结果集中的数据 in (1,2,3)
select * from dept where id in (select dept_id from emp where age > 23);

需求2: 查询年龄大于1号部门所有员工的人

-- 1. 查询1号部门所有员工的年龄,得到多行单列
select age from emp where dept_id=1;

-- 2. 当结果集返回多行时不能使用比较运算符
-- ERROR 1242 (21000): Subquery returns more than 1 row
select * from emp where age > (select age from emp where dept_id=1);

select * from emp where age > all (select age from emp where dept_id=1);
等价于:
select * from emp e where e.age > (select max(age) from emp e where e.dept_id=1);
-- 比1号部门任意一个大就行
select * from emp where age > any (select age from emp where dept_id=1);

等价于:
select * from emp e where e.age > (select min(age) from emp e where e.dept_id=1);

子查询结果是多行多列的:子查询可以作为一张虚拟表

select 列名 from 表, (子查询的结果) 别名 where 条件

如果子查询的结果是多行多列,父查询可以将这个查询结果做为一个虚拟表,进行第2次查询。不是放在where后面,而是放在from的后面;子查询作为表需要取别名,否则这张表没有名称则无法访问表中的字段。

需求: 查询出年龄大于23岁的员工信息和部门名称

-- 1. 在员工表中查询年龄大于23岁的员工
select * from emp where age > 23;

-- 2.查询所有的部门信息,与上面的虚拟表中的信息组合,找出所有部门id等于的dept_id
select * from dept d, emp e where d.id = e.dept_id; -- 隐式内连接

select e.*,d.name 部门名称 from dept d, (select * from emp where age > 23) e where d.id = e.dept_id; -- 隐式内连接

-- 也可以使用表连接
select e.*,d.name 部门名称 from dept d, emp e where d.id = e.dept_id and e.age > 23;

子查询小结

  • 单行单列:父查询使用比较运算符(>,<,=)。

  • 多行单列:父查询使用关键字:in/any/all,只要是单列的情况放在where后面。

  • 多行多列:父查询放在from后面做为虚拟表并起别名再次进行查询。

关于多表查询就讲到这里了,文中举了一些例子来说明,大家阅读的时候也可以自己对照例子敲敲代码哦。在实际开发中多表查询是很常用的,通过多表查询,我们可以将多个表中的数据进行关联,从而得到更全面、更精确的数据结果。

标签:多表,查询,dept,emp,MySQL,where,id,select
From: https://www.cnblogs.com/ydyxcode/p/17853847.html

相关文章

  • PYTHON实现EXCEL数据导入MYSQL
    #coding=utf8importpymysqlimportosimportpandasaspdhost='127.0.0.1'port=3308user='root'password='*****'db='impairment_testing'conn=pymysql.connect(host=host,port=port,user=user,password=password,db=db......
  • 5.mysql8.0以上版本,ProxySQL 监控/连接账户,要以 mysql_native_password 形式创建,否则
    CREATEUSER'monitor'@'%'IDENTIFIEDBY'123456';grantallprivilegeson*.*to'monitor'@'%'withgrantoption;flushprivileges; ALTERUSER'root'@'%'IDENTIFIEDWITHmysql_native_pa......
  • 【Mongo】Mongo表结构设计以及查询示例
      MongoSQL://建表语句db.createCollection("pro_alter_info")//插入文档语句db.pro_alter_info.insert({"alterTime":"2022-03-2716:43:09","alterType":1,"proId":22032710210000......
  • 生产实践:Redis与Mysql的数据强一致性方案
    公众号「架构成长指南」,专注于生产实践、云原生、分布式系统、大数据技术分享。数据库和Redis如何保持强一致性,这篇文章告诉你目的Redis和Msql来保持数据同步,并且强一致,以此来提高对应接口的响应速度,刚开始考虑是用mybatis的二级缓存,发现坑不少,于是决定自己搞要关注的问题点......
  • MySQL将'20231124'转换为'yyyy/MM/dd'格式
    可以使用STR_TO_DATE函数将一个字符串转换为日期,并使用DATE_FORMAT函数将日期格式化为指定的格式SELECTDATE_FORMAT(STR_TO_DATE('20231124','%Y%m%d'),'%Y/%m/%d');解释一下上述语句的步骤:STR_TO_DATE('20231124','%Y%m%d')将字符串"20231124"转换为日期......
  • mysql5.0升级8.0完成后,服务器重启引发"#1449 - The user specified as a definer ('m
    遇到的问题:问题一:ERROR1449(HY000):Theuserspecifiedasadefiner('mysql.infoschema'@'localhost')doesnotexist异常原因:未知解决办法:验证指定的用户('mysql.infoschema'@'localhost')是否存在于mysql数据库中SELECTUserFROMmysql.userWH......
  • mysql 一些优化参数
     大批量数据加载优化load数据加载格式:loaddatalocalinfile'文件路径'intotable表名fieldsterminatedby'[分隔符]'lineterminatedby'[换行符]'11、首先,检测全局变量‘local_infile’的状态,如果是off状态则是不可用showglobalvariableslike'local_infile';......
  • mysql数据库物理及逻辑备份
    一、理论知识1.1:物理备份备份数据库操作系统的物理文件(数据文件、日志文件等):冷备份(脱机备份):关闭数据库进行热备份(联机备份):数据库正运行,依赖数据库的日志文件温备份:数据库锁定表格(可读不可写)后进行常见方法:物理冷备:拷贝数据库文件打包备份,需进行锁表-备份-解表。恢复时拷......
  • hutool 使用 TreeUtil 查询树型结构
    之前写过一篇用stream流实现查询树型结构的文章,现在以hutool中的TreeUtil再来实现一次,之前的帖子JavaStream流实现递归查询树型结构查询出所有数据,用父节点递归查询出所有子节点数据/***封装备注分类集合**@paramremarkTypeList备注分类集合*......
  • SQL提高查询性能的几种方式
    ##创建索引,提高性能索引可以极大地提高查询性能,其背后的原理:1.索引是的数据库引擎能够快速的找到表中的数据,它们类似于书籍的目录,使得你不需要逐页查找所需要的信息2.索引能够帮助数据库引擎直接定位到所需的数据,从而大大减少磁盘I/O操作,如果没有索引,SQLSERSER可能需要执行全......