【H2O2|全栈】MySQL的基本操作（三）

目录

前言

开篇语

准备工作

案例准备

多表查询

笛卡尔积

等值连接

外连接

内连接

自连接

子查询

存在和所有

含于

分页查询

建表语句

结束语

前言

开篇语

本篇继续讲解MySQL的一些基础的操作——数据字段的查询中的多表查询和分页查询，与单表查询相比，这些在面试时会更加常用。

准备工作

软件：【参考版本】Navicat Premium 16 / SQLyog - 32 bit

语言：MySQL

版本信息：MySQL5.7

案例准备

本次的案例为四张表，创建的语句比较长，放在本章的最后，请自行跳转至对应位置复制创建。

参考库结构——

dept部门表结构（4行3列）——

emp员工表结构（14行8列）——

多表查询

笛卡尔积

概念

笛卡尔积查询的结果是两张被查询的表的列数相加，行数相乘。

比如说一张三列四行的表和一张四列六行的表查询，在不进行过滤去重的条件下，查询的结果就是一张七列二十四行的新表，相同的列不会合并。

案例

1.员工表emp和部门表dept的笛卡尔积

select emp.*,dept.*

from emp,dept

可以看到，多表查询的from语句里不止有一张表。查询的结果是11*56，相同的列deptno（部门编号并未被合并）。

等值连接

概念

当过滤条件中涉及到两张表的某一条件对应相同（=）时，就可以使用等值连接查询。

而其余的情况就是非等值连接，比如使用>=，<=，<>，betwen and等作为过滤条件。

关键字

=

案例

1.使用等值连接，显示员工的编号，姓名，部门编号，部门名

select empno, ename, e.deptno, dname
from emp e, dept d where e.deptno = d.deptno 
order by e.deptno

由于部门编号实际上在两张表中都有，所以我们可以将该列作为等值连接的过滤条件。

注意，由于这里有两张表，所以要指定使用哪一张表的部门编号，否则会提醒你不知道要找的是哪一张表的这一列——

外连接

概念

外连接实际上分为左外连接和右外连接两种，一般来说，以左表作为基准就是左外连接，反之就是右外连接。 

那么，什么是以左表为基准呢？实际上就是左表中有的条件，右表中可能是没有的，但是我们需要把右表强行对应拼到左表中，如果拼接位置找不到对应的数据，则结果里的该位置处为NULL。

即以谁为基准，就应当保证结果中有谁的全部数据，而不保证另一侧。

关键字

LEFT JOIN 

ON

RIGHT JOIN

ON

案例

1.外连接 查询 员工编号 员工姓名 部门编号  部门名称

这里以左外连接为例——

select empno, ename, e.deptno, d.dname
from emp e left join dept d

on e.deptno = d.deptno order by e.deptno

可以看到，外连接和笛卡尔积查询的结果非常相似，只是把并列的两张表变成了外连接，然后把过滤条件放到ON中了。

实质上，笛卡尔积查询和外连接查询可以相互转化。

内连接

概念

当数据项既满足左外连接，又满足右外连接，即为两表有关联（不会补NULL）的数据项时，就是内连接了。

在数学上，我们也可以把内连接的结果看做是两表的交集。

关键字

INNER JOIN 

ON

案例

1.内连接 查询 员工编号 员工姓名 部门编号  部门名称

select empno,ename,e.deptno, d.dname
from emp e inner join dept d on e.deptno = d.deptno order by e.deptno

自连接

概念

自连接实际上就是相当于将自身复制一份，然后让自身和复制表之间实现连接。

案例

1.使用自连接，显示"XXX的上级是XXX"这种格式，对于没有上级的，显示为“无”

select  concat(u.ename, '的上级是', ifnull(boss.ename, "无")) 附属关系
from emp u 
left join emp boss 
on u.mgr = boss.empno;

实际上，这里就是将一张表作为员工表，取的是员工姓名；

而部分员工是有下属的，所以员工表也可以作为上级表使用，两表的连接条件就是员工表的mgr编号与上级表的empno编号一致。

而部分员工可能没有上级，比如说KING的mgr为NULL，说明员工表实际上是外连接中的基准表。

使用IFNULL()来处理NULL的问题，否则整个varchar的值在拼接后会变为NULL。

子查询

概念

子查询的作用是，查询条件未知的事物，将查询的结果作为父查询的条件使用。

一般来说，子查询有下面的要点——

1) 子查询与父查询可以针对不同的表，也可以针对同一张表
2) 子查询与父查询在传参时，参数数量、类型和含义要相同

说白了，子查询可以看做一个（组）参数，或代表一张表。

但是子查询的效率是很低的，应当尽量少用。 

案例

1.查询出高于10号部门的平均工资的员工信息

select *

from emp

where sal > (select avg(sal) from emp where  deptno = 10)

实际上，我们需要做两次查表的操作，第一次是查询所有员工的信息（父查询），第二次是查询10号部门的平均工资（子查询）。

然后，将子查询的结果（avg(sal)）作为父查询的过滤条件，完成查询需求。

2.查询出有哪些部门的平均工资高于30号部门的平均工资，部门编号  部门名称 平均工资

select e.deptno, dname, avg(sal)

from emp e, dept d

where e.deptno <> 30 and e.deptno = d.deptno

group by e.deptno, dname

having avg(sal) > (select avg(sal) from emp where deptno = 30)

对于多行函数AVG()，该过滤条件需要添加到HAVING分组过滤关键字中。

不要忘记两表公有的列一定要指定是哪一张表的该列。

3.查询各部门中工资比本部门平均工资高的员工的员工号 姓名 工资 

select empno, ename, sal
from emp e, (select avg(sal) avsa, deptno from emp group by deptno) f
where e.deptno = f.deptno
and sal > avsa

子查询的结果实际上也可以看成一张表，为了使这张新表 f 能够与员工表 e 有关联，我们可以为新表添加一个公共列deptno。 

存在和所有

概念

存在是指，在结果集中至少有一个结果符合条件就符合；

所有是指，结果集的所有结果都符合条件才符合。

关键字

ANY()

ALL()

案例

1.查询出比20号部门任一员工薪资高的员工信息

select *

from emp

where sal > any(select sal from emp where  deptno = 30)

这里涉及到员工表和只有30号部门员工的子表，所以用到的是子查询。

而过滤条件里要求查询的工资比任一结果高，即至少有一个符合（最低薪资）即可，所以可以使用ANY关键字。 

2.查询出比30号部门任何员工薪资高的员工信息

select *

from emp

where sal > all(select sal from emp where  deptno = 30)

这里要求比任何30号部门员工工资都高，所以使用ALL关键字。

实际上，在部分情况下，ANY和ALL与MIN()和MAX()函数的作用是相同。

含于

概念

含于是指某个（些）筛选内容包含在结果集中，筛选内容需要与结果集中内容对应。

关键字

IN()

案例

1.查询工作和工资与MARTIN完全相同的员工信息

select *

from emp

where (job, sal) in(select job, sal from emp where ename = "MARTIN")

我们的子查询查询出了两列的内容，而筛选条件job和sal与这两列一一对应。 

分页查询

概念

分页查询是指查询指定表中的某一条至某一条的行。

关键字

LIMIT start, number

其中start为开始的索引值（从0开始），number为查询的条数。 

案例

1.查询工资排名第4到8名的员工的信息

select *

from emp

order by sal

limit 3, 5

建表语句

员工表

CREATE TABLE `emp` (`empno` INT(4) NOT NULL COMMENT '员工编号',`ename` VARCHAR(10) DEFAULT NULL COMMENT '员工姓名',`job` VARCHAR(9) DEFAULT NULL COMMENT '员工岗位',`mgr` INT(4) DEFAULT NULL COMMENT '领导编号',`hiredate` DATE DEFAULT NULL COMMENT '入职时间',`sal` DECIMAL(7,2) DEFAULT NULL COMMENT '工资',`comm` DECIMAL(7,2) DEFAULT NULL COMMENT '奖金',`deptno` INT(2) DEFAULT NULL COMMENT '部门编号',PRIMARY KEY (`empno`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

员工表数据

INSERT INTO emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
VALUES ('7369', 'SMITH', 'CLERK', '7902','1980-12-17', '800', NULL, '20');INSERT INTO emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
VALUES ('7499', 'ALLEN', 'SALESMAN', '7698', '1981-02-20', '1600', '300', '30');INSERT INTO emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
VALUES ('7521', 'WARD', 'SALESMAN', '7698', '1981-02-22', '1250', '500', '30');INSERT INTO emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
VALUES ('7566', 'JONES', 'MANAGER', '7839', '1981-04-02', '2975', NULL, '20');INSERT INTO emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
VALUES ('7654', 'MARTIN', 'SALESMAN', '7698', '1981-09-28', '1250', '1400', '30');INSERT INTO emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
VALUES ('7698', 'BLAKE', 'MANAGER', '7839', '1981-05-01', '2850', NULL, '30');INSERT INTO emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
VALUES ('7782', 'CLARK', 'MANAGER', '7839', '1981-06-09', '2450', NULL, '10');INSERT INTO emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
VALUES ('7788', 'SCOTT', 'ANALYST', '7566', '1987-06-13', '3000', NULL, '20');INSERT INTO emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
VALUES ('7839', 'KING', 'PRESIDENT', NULL, '1981-11-17', '5000', NULL, '10');INSERT INTO emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
VALUES ('7844', 'TURNER', 'SALESMAN', '7698', '1981-09-08', '1500', '0', '30');INSERT INTO emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
VALUES ('7876', 'ADAMS', 'CLERK', '7788', '1987-06-13', '1100', NULL, '20');INSERT INTO emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
VALUES ('7900', 'JAMES', 'CLERK', '7698', '1981-12-03', '950', NULL, '30');INSERT INTO emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
VALUES ('7902', 'FORD', 'ANALYST', '7566', '1981-12-03', '3000', NULL, '20');INSERT INTO emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
VALUES ('7934', 'MILLER', 'CLERK', '7782', '1982-01-23', '1300', NULL, '10');

部门表

CREATE TABLE dept(deptno INT(2) PRIMARY KEY NOT NULL,dname  VARCHAR(14),loc    VARCHAR(13)
);

部门表数据

INSERT INTO dept (DEPTNO, DNAME, LOC)
VALUES ('10', 'ACCOUNTING', 'NEW YORK');INSERT INTO dept (DEPTNO, DNAME, LOC)
VALUES ('20', 'RESEARCH', 'DALLAS');INSERT INTO dept (DEPTNO, DNAME, LOC)
VALUES ('30', 'SALES', 'CHICAGO');INSERT INTO dept (DEPTNO, DNAME, LOC)
VALUES ('40', 'OPERATIONS', 'BOSTON');

结束语

本期内容到此结束。关于本系列的其他博客，可以查看我的MySQL专栏。

本系列的博客主要是记录学习经历，并总结阶段的知识点。全篇的操作过程由笔者完成并核验，在部分定义上有参考其他的内容。本身也是新手，多多包涵。

==期待与你在下一期博客中再次相遇==

——放了挺久的【H2O2】

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

如若内容造成侵权/违法违规/事实不符，请联系长河编程网进行投诉反馈email:809451989@qq.com，一经查实，立即删除！

相关文章

springboot vue 会员收银系统 (12)购物车关联服务人员 订单计算提成 开源

springboot vue 会员收银系统 (12)购物车关联服务人员 订单计算提成 开源

前言 完整版演示 http://120.26.95.195/ 开发版演示 http://120.26.95.195:8889/ 在之前的开发进程中&#xff0c;我们完成订单的挂单和取单功能&#xff0c;今天我们完成购物车关联服务人员&#xff0c;用户计算门店服务人员的提成。 1.商品关联服务人员 服务人员可以选择 一…
阅读更多...
leetcode 1853 转换日期格式(postgresql)

leetcode 1853 转换日期格式(postgresql)

需求 表: Days ----------------- | Column Name | Type | ----------------- | day | date | ----------------- day 是这个表的主键。 给定一个Days表&#xff0c;请你编写SQL查询语句&#xff0c;将Days表中的每一个日期转化为"day_name, month_name day, year"…
阅读更多...
java操作doc(二)——java利用Aspose.Words动态创建自定义doc文档

java操作doc(二)——java利用Aspose.Words动态创建自定义doc文档

有关java动态操作word文档&#xff0c;上一篇写了如何使用模板动态设置对于内容以及相关单元格的动态合并问题&#xff0c;详细请参看如下文档&#xff1a; java利用Aspose.Words操作Word动态模板文档并动态设置单元格合并 这篇文档说说&#xff0c;如何利用Aspose.Words动态…
阅读更多...
仿蝠鲼软体机器人实现高速多模态游动

仿蝠鲼软体机器人实现高速多模态游动

近期&#xff0c;华南理工大学周奕彤老师研究团队最新成果"Manta Ray-Inspired Soft Robotic Swimmer for High-speed and Multi-modal Swimming"被机器人领域会议 IEEE/RSJ International Conference on Intelligent Robots and Systems&#xff08;IROS 2024&#…
阅读更多...
【网络原理】网络地址转换----NAT技术详解

【网络原理】网络地址转换----NAT技术详解

&#x1f490;个人主页&#xff1a;初晴~ &#x1f4da;相关专栏&#xff1a;计算机网络那些事 我们在 IP协议 一文中介绍过&#xff0c;由于IPv4协议中 IP地址只有32位&#xff0c;导致最多只能表示 42亿9千万个IP地址。但我们需要通过IP地址来标识网络上的每一个设备&#x…
阅读更多...
D86【python 接口自动化学习】- pytest基础用法

D86【python 接口自动化学习】- pytest基础用法

day86 pytest配置testpaths 学习日期&#xff1a;20241202 学习目标&#xff1a;pytest基础用法 -- pytest配置testpaths 学习笔记&#xff1a; pytest配置项 主目录创建pytest.ini文件 [pytest] testpaths./testRule 然后Terminal里直接命令&#xff1a;pytest&#xff…
阅读更多...
电机瞬态分析基础（15）：电机的电磁转矩（三相同步电机和三相感应电机）

电机瞬态分析基础（15）：电机的电磁转矩（三相同步电机和三相感应电机）

1. 三相同步电机电磁转矩 1.1 隐极同步电机 图1. 三相隐极同步电机基本结构 三相隐极同步电机的基本结构可用图1来简单表示&#xff0c;图中&#xff0c;定子分布绕组可等效为三相对称绕组A-X、B-Y和C-Z&#xff1b;转子分布绕组为励磁绕组。若在定子三相对称绕组中通入三相…
阅读更多...
人工智能与机器学习在智能扭矩系统中的应用

人工智能与机器学习在智能扭矩系统中的应用

【大家好&#xff0c;我是唐Sun&#xff0c;唐Sun的唐&#xff0c;唐Sun的Sun。】 在当今科技飞速发展的时代&#xff0c;智能扭矩系统正经历着一场深刻的变革&#xff0c;而人工智能&#xff08;AI&#xff09;和机器学习算法的应用成为了推动这一变革的关键力量。 传统的扭矩…
阅读更多...
Android hid 数据传输（device 端 ）

Android hid 数据传输（device 端 ）

最近一直在处理hid 数据需求&#xff0c;简而言之就是两台设备直接可以通过usb 线互相传递数据。 项目架构 为什么Device 端要采用HID&#xff08;人机接口设备&#xff09;的方式发送和接收数据呢&#xff1f; 主要是速度快&#xff0c;举个例子&#xff0c;就是鼠标移动&am…
阅读更多...
K8S离线部署Nacos集群【Oracle作外部数据源】

K8S离线部署Nacos集群【Oracle作外部数据源】

一、前言 由于公司的要求下要使Nacos集群以Oracle作为外部数据源&#xff0c;前期咱们已经阐述了如何在本地搭建&#xff08;Nacos集群搭建【Oracle作外部数据源】&#xff09;&#xff0c;本次将带领大家在k8s上部署Nacos集群并以Oracle作为外部数据源。 二、软件包 nacos-f…
阅读更多...
【Java开发】Springboot集成mybatis-plus

【Java开发】Springboot集成mybatis-plus

1、引入 mybatis-plus 依赖 <dependency> <groupId>com.baomidou</groupId> <artifactId>mybatis-plus-boot-starter</artifactId> <version>3.5.0</version> </dependency> <!--mysql依赖--> <dependen…
阅读更多...
技术成长战略是什么？

技术成长战略是什么？

文章目录 技术成长战略是什么&#xff1f;1. 前言2. 跟技术大牛学成长战略2.1 系统性能专家案例2.2 从开源到企业案例2.3 技术媒体大V案例2.4 案例小结 3. 学习金字塔和刻意训练4. 战略思维的诞生5. 建议 技术成长战略是什么&#xff1f; 1. 前言 在波波的微信技术交流群里头…
阅读更多...
【开源免费】基于Vue和SpringBoot的课程答疑系统（附论文）

【开源免费】基于Vue和SpringBoot的课程答疑系统（附论文）

博主说明&#xff1a;本文项目编号 T 070 &#xff0c;文末自助获取源码 \color{red}{T070&#xff0c;文末自助获取源码} T070&#xff0c;文末自助获取源码 目录 一、系统介绍二、演示录屏三、启动教程四、功能截图五、文案资料5.1 选题背景5.2 国内外研究现状5.3 可行性分析…
阅读更多...
【二分查找】力扣 275. H 指数 II

【二分查找】力扣 275. H 指数 II

一、题目 二、思路 h 指数是高引用引用次数&#xff0c;而 citations 数组中存储的就是不同论文被引用的次数&#xff0c;并且是按照升序排列的。也就是说 h 指数将整个 citations 数组分成了两部分&#xff0c;左半部分是不够引用 h 次 的论文&#xff0c;右半部分论文的引用…
阅读更多...
【LeetCode】169.多数元素

【LeetCode】169.多数元素

题目连接&#xff1a; https://leetcode.cn/problems/majority-element/solutions/2362000/169-duo-shu-yuan-su-mo-er-tou-piao-qing-ledrh/?envTypestudy-plan-v2&envIdtop-interview-150 题目描述&#xff1a; 思路一&#xff1a; 使用哈希表unordered_map记录每个元…
阅读更多...
SpringBoot整合knife4j，以及会遇到的一些bug

SpringBoot整合knife4j，以及会遇到的一些bug

这篇文章主要讲解了“Spring Boot集成接口管理工具Knife4j怎么用”&#xff0c;文中的讲解内容简单清晰&#xff0c;易于学习与理解&#xff0c;下面请大家跟着小编的思路慢慢深入&#xff0c;一起来研究和学习“Spring Boot集成接口管理工具Knife4j怎么用”吧&#xff01; 一…
阅读更多...
️️耗时一周，肝了一个超丝滑的卡盒小程序

️️耗时一周，肝了一个超丝滑的卡盒小程序

前言 先看看成品效果&#xff1a; 在上个月&#xff0c;我出于提升自己的英语造句能力的目的&#xff0c;想要找一个阅读或者练习造句类的英语学习 APP&#xff0c;但是最终找了几个 APP 不是不太好用就是要付费。于是我转换思路&#xff0c;找到了一本书&#xff0c;叫《36…
阅读更多...
aardio - 汉字笔顺处理 - json转sqlite转png

aardio - 汉字笔顺处理 - json转sqlite转png

本代码需要最新版 godking.conn 库&#xff0c;请自行下载&#xff01; 如果没有安装 odbc for sqlite 驱动&#xff0c;可以使用 godking.conn.driver.sqlite3.install() 安装。 也可以在此下载自行安装&#xff1a;http://www.chengxu.online/show.asp?softid267 1、将js…
阅读更多...
Pick：一款安全易用的密码管理器

Pick：一款安全易用的密码管理器

Pick&#xff1a;一款安全易用的密码管理器 pick A secure and easy-to-use CLI password manager for macOS and Linux 项目地址: https://gitcode.com/gh_mirrors/pick/pick 在当今数字化时代&#xff0c;密码管理已成为每个人不可或缺的一部分。为了保护您的在线账户…
阅读更多...
C/C++当中的内存对齐

C/C++当中的内存对齐

一&#xff1a;为什么要存在内存对齐 对与计算机而言&#xff0c;一次性可以取出处理的单元大小为字&#xff0c;在32位系统下&#xff0c;一次性可以取出4个字节&#xff0c;而在64位系统下&#xff0c;一次性可以取出8个字节&#xff0c;而一个地址对应一个内存单元&#xff…
阅读更多...
最新文章
推荐文章

Copyright @ 2022~2023