MySQL复合查询(重点)

前面我们讲解的mysql表的查询都是对一张表进行查询,在实际开发中这远远不够。

基本查询回顾

查询工资高于500或岗位为MANAGER的雇员,同时还要满足他们的姓名首字母为大写的J

mysql> select * from emp where (sal>500 or job='MANAGER') and ename like 'J%';//用where逻辑筛选

 按照部门号升序而雇员的工资降序排序

mysql> select * from emp order by deptno,sal desc;

 使用年薪进行降序排序(月薪*12+奖金)

因为有的雇员的comm(奖金)为NULL,而NULL不参与运算,所以对于comm为NULL的奖金为0用到函数ifnull();

mysql> select ename,sal*12+comm 年薪 from emp where ename='SMITH' order by 年薪 desc;//对于SMITH他的年薪不为NULL,但为什么最后是NULL,因为comm是NULL,任何数与NULL计算最后都是NULL,所以导致年薪最后为NULL
mysql> select ename,sal*12+ifnull(comm,0) 年薪 from emp order by 年薪 desc;//先有数据才能排序,所以别名能用

 显示工资最高的员工的名字和工作岗位(先找到最高工资,然后找到最高工资的人)

mysql> select ename,job from emp where sal=(select max(sal) from emp);

sql中允许在一条sql内部执行对应的select查询,叫做子查询

select max(sal) from emo本身返回数值,让他的值等于where的sal查询即可(里部的select叫做子查询)

 显示工资高于平均工资的员工信息(用到子查询)

mysql> select * from emp where sal>(select avg(sal) from emp);//子查询查到平均工资,然后外部where筛选

 显示每个部门的平均工资和最高工资

每个部门的注定了要分组了,按照部门分组再求平均和最高(就是将emp这一张表逻辑上分成几组子表在进行聚合统计)

mysql> select deptno,avg(sal),max(sal) from emp group by deptno;
如果觉得平均工资显示的不优雅,可以使用format(avg(sal),2)保留两位精度

select deptno, format(avg(sal), 2) , max(sal) from emp group by deptno;

 显示平均工资低于2000的部门号和它的平均工资

首先把每个部门的平均工资算出(注定要分组),再筛选出低于两千(注定用having筛选,用where的话不对)

mysql> select deptno,avg(sal) 平均 from emp group by deptno having avg(sal)<2000;

 显示每种岗位的雇员总数,平均工资

mysql> select job,count(*),format(avg(sal),2) from emp  group by job;

多表查询 

实际开发中往往数据来自不同的表,所以需要多表查询。本节我们用一个简单的公司管理系统,有三张 表EMP,DEPT,SALGRADE来演示如何进行多表查询。

案例

显示雇员名、雇员工资以及所在部门的名字因为上面的数据来自EMP和DEPT表,因此要联合查询

select count(*) from emp,dept=(count(*) from emp)*(select count(*) from dept;)

如果不经过筛选的话得到的信息是多余的且错误的;其实我们只要emp表中的deptno = dept表中的deptno字段的记录

mysql> select * from emp,dept where emp.deptno=dept.deptno;//筛选

如果是唯一的话就不用  表名.列名  直接用列明即可

 显示部门号为10的部门名,员工名和工资

mysql> select ename, sal,dname from emp, dept where dept.deptno=dept.deptno and  dept.deptno = 10;

 显示各个员工的姓名,工资,及工资级别

mysql> select ename, sal,grade from emp, salgrade where sal between losal and hisal;//这个where筛选相当于在进行笛卡尔积的时候进行过滤过滤掉不符合的信息

 笛卡尔积本质就是穷举,如有筛选就筛选;

 自连接

自连接是指在同一张表连接查询

不能将同一张表写两次:mysql> select * from salgrade,salgrade;
ERROR 1066 (42000): Not unique table/alias: 'salgrade'

但是能将重命名后查询:

mysql> select * from salgrade t1,salgrade t2;
mysql> select * from salgrade t1,salgrade;
mysql> select * from salgrade ,salgrade t;//只要重命名任意一个就行,不要在sql中出现一样的表名

 案例

 显示员工FORD的上级领导的编号和姓名(mgr是员工领导的编号--empno)

1.先找到领导的编号:mysql> select mgr from emp where ename='FORD';

2.再根据编号找信息:mysql> select ename,empno from emp where empno=7566;


方法1(子查询):mysql> select ename,empno from emp where empno=(select mgr from emp where ename='FORD');//使用子查询


方法2(多表查询--自查询):mysql> select leader.empno,leader.ename from emp leader, emp worker where leader.empno = worker.mgr and worker.ename='FORD';//这里from重命名where能用并且select也能用因为sql语句先执行from;因为要先做笛卡尔积,所以别名可以先识别

子查询

子查询是指嵌入在其他sql语句中的select语句,也叫嵌套查询;

 单行子查询

返回一行记录的子查询

显示SMITH同一部门的员工

mysql> select * from emp where deptno=(select deptno from emp where ename='SMITH');//内部查询出的结果作为外部查询的条件

多行子查询

返回多行记录的子查询

查询和10号部门的工作岗位相同的雇员的名字,岗位,工资,部门号,但是不包含10自己的;

首先查出10号部门的工作岗位:mysql> select job from emp where deptno=10;//如有重复的加distinct即可

in关键字:只要满足集合的一个条件即可

查找:mysql> select ename,job,sal,deptno from emp where job in (select job from emp where deptno=10);//查出所有的

最终:mysql> select ename,job,sal,deptno from emp where job in (select job from emp where deptno=10) and deptno <> 10;


那如果查询出后再查出部门名称怎么做呢?

mysql> select * from (select ename,job,sal,deptno from emp where job in (select job from emp where deptno=10) and deptno <> 10) t1,dept where t1.deptno=dept.deptno;

一个sql的查询结果本身就是表结构,我们也可以将查出来的表结构进行进行笛卡尔积;

子查询不仅能出现在where后面充当判断条件也可以出现在from后面充当表

最后:mysql> select ename,job,sal,dname from (select ename,job,sal,deptno from emp where job in (select job from emp where deptno=10) and deptno <> 10) t1,deppt where t1.deptno=dept.deptno;

 显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号

首先筛选出部门30的所有员工的最高工资:mysql> select max(sal) from emp where deptno=30;
结果:筛选出工资大于部门30的所有员工的最高工资的人:mysql> select * from emp where sal > (select max(sal) from emp where deptno=30);

all关键字:都大于集合的才满足

结果:mysql> select * from emp where sal > all (select sal from emp where deptno=30);//这里不挑选出最大的,而是列出所有30部门员工的sal,只有员工的薪水都大于all集合里的薪水才满足条件;

用all相当于我比你们班任何一个人的成绩都要高相当于我比你们班的第一名成绩还要高(学霸思想)


最终查到的都比部门30的员工任意sal都要高

 显示工资比部门30的任意员工的工资高的员工的姓名、工资和部门号(包含自己部门 的员工)

all关键字:都大于集合的才满足

结果:mysql> select * from emp where sal > any (select sal from emp where deptno=30);

用any相当于我只要比你们班一个人高就行(学渣思想)


最终查到的最低sal只要比部门30的最低sal高就行

多行子查询也是单列

多列子查询

行子查询是指子查询只返回单列,单行数据;多行子查询是指返回单列多行数据,都是针对单列而言 的,而多列子查询则是指查询返回多个列数据的子查询语句

查询和SMITH的部门和岗位完全相同的所有雇员,不含SMITH本人

首先找到SMITH的部门和岗位:mysql> select deptno,job from emp where ename='SMITH';
最终:mysql> select * from emp where (deptno,job) = (select deptno,job from emp where ename='SMITH') and ename <> 'SMITH';//多列用括号,得一一对应

目前全部的子查询,全部都在where语句中充当判断条件;

任何时刻查询出来的临时结构本质上也是表结构; 

在from子句中使用子查询

子查询语句出现在from子句中。这里要用到数据查询的技巧,把一个子查询当做一个临时表使用。

案例

显示每个高于自己部门平均工资的员工的姓名、部门、工资、平均工资

先找出每个部门的平均工资(分组与聚合):mysql> select deptno,avg(sal) from emp group by deptno;
筛选:mysql> select * from emp,(select deptno,avg(sal) myavg from emp group by deptno) tmp where emp.deptno=tmp.deptno and emp.sal>tmp.myavg;

注意:子查询语句出现在from子句中一定要起别名

 查找每个部门工资最高的人的姓名、工资、部门、最高工资

mysql> select ename,sal,t1.deptno,mymax from emp t1,(select deptno,max(sal) mymax from emp group by deptno) t2 where t1.deptno=t2.deptno and t1.sal=t2.mymax;

 显示每个部门的信息(部门名,编号,地址)和人员数量(聚合出的)

先找到每个组的人数:mysql> select count(1) from emp group by deptno;

使用子查询:mysql> select * from dept,(select deptno,count(1) from emp group by deptno) t1 where dept.deptno=t1.deptno;//几乎所有的笛卡尔积后都要加判断条件

使用多表:select dept.dname, dept.deptno, dept.loc,count(*) '部门人数' from emp, dept where emp.deptno=dept.deptno group by dept.deptno,dept.dname,dept.loc;

//为什么已经按照deptno分过组了还要按照dname和loc分组?

一方面是防止按照deptno分过后结果不对再按照dname分组;另一方面mysql不允许在分组中出现未被分组的列,例如:select dept.dname, dept.deptno, dept.loc,count(*)  from emp, dept where emp.deptno=dept.deptno group by dept.deptno;这样就不行,要求在使用 GROUP BY 时,SELECT 中的列要么是聚合函数,要么是在 GROUP BY 子句中明确列出的列。

解决多表问题的本质:想办法将多表转化为单表,所以在MySQL中,所有的select的问题全部都可以转化成单表问题!--多表查询的指导思想 

合并查询

 在实际应用中,为了合并多个select的执行结果,可以使用集合操作符 union,union all

union

该操作符用于取得两个结果集的并集。当使用该操作符时,会自动去掉结果集中的重复行

将工资大于2500或职位是MANAGER的人找出来

mysql> select * from emp where sal> 2500;
mysql> select * from emp where job ='MANAGER';

mysql> select * from emp where sal> 2500 union select * from emp where job ='MANAGER';//自动去重

 union all

该操作符用于取得两个结果集的并集。当使用该操作符时,不会去掉结果集中的重复行

 将工资大于25000或职位是MANAGER的人找出来

mysql> select * from emp where sal> 2500 union all select * from emp where job ='MANAGER';

两个表拼接合集的话列属性得一样

mysql> select ename,job,sal from emp where sal> 2500 union all select * from emp where job ='MANAGER';
不能左边的表是筛选出三列跟右边的全列信息拼接,这样拼不上

 

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

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

相关文章

数据湖仓一体(一) 编译hudi

目录 一、大数据组件版本信息 二、数据湖仓架构 三、数据湖仓组件部署规划 四、编译hudi 一、大数据组件版本信息 hudi-0.14.1zookeeper-3.5.7seatunnel-2.3.4kafka_2.12-3.5.2hadoop-3.3.5mysql-5.7.28apache-hive-3.1.3spark-3.3.1flink-1.17.2apache-dolphinscheduler-3.1.9…

[Vulnhub] Sedna BuilderEngine-CMS+Kernel权限提升

信息收集 IP AddressOpening Ports192.168.8.104TCP:22, 53, 80, 110, 111, 139, 143, 445, 993, 995, 8080, 55679 $ nmap -p- 192.168.8.104 --min-rate 1000 -sC -sV PORT STATE SERVICE VERSION 22/tcp open ssh OpenSSH 6.6.1p1 Ubuntu 2ubuntu2 …

C++20中的consteval说明符

在C20中&#xff0c;立即函数(immediate function)是指每次调用该函数都会直接或间接产生编译时常量表达式(constant expression)的函数。这些函数在其返回类型前使用consteval关键字进行声明。 立即函数是constexpr函数&#xff0c;具体情况取决于其要求。与constexpr相同&…

半小时获得一张ESG入门证书【详细中英文笔记一】

前些日子&#xff0c;有朋友转发了一则小红书的笔记给我&#xff0c; 标题是《半小时获CFI官方高颜值免费证书 ESG认证》。这对考证狂魔的我来说&#xff0c;必然不能错过啊&#xff0c;有免费的羊毛不薅白不薅。 ESG课程的 CFI 官方网址戳这里&#xff1a;CFI 于是信心满满的…

清华大学孙富春教授团队开发了多模态数字孪生环境,辅助机器人获得复杂的 3C 装配技能

中国是全球3C产品&#xff08;电脑、通信和消费电子&#xff09;的主要生产国&#xff0c;全球70%的3C产品产能集中在中国。3C智能制造装备的突破与产业化&#xff0c;对于提升我国制造产业的全球竞争力意义重大。 机器人在计算机、通信和消费电子 &#xff08;3C&#xff09; …

常用的设计模式和使用案例汇总

常用的设计模式和使用案例汇总 【一】常用的设计模式介绍【1】设计模式分类【2】软件设计七大原则(OOP原则) 【二】单例模式【1】介绍【2】饿汉式单例【3】懒汉式单例【4】静态内部类单例【5】枚举&#xff08;懒汉式&#xff09; 【三】工厂方法模式【1】简单工厂模式&#xf…

springboot 程序运行一段时间后收不到redis订阅的消息

springboot 程序运行一段时间后收不到redis订阅的消息 问题描述 程序启动后redis.user.two主题正常是可以收到消息的&#xff0c;发一条收一条&#xff0c;但是隔一段时间后&#xff1b;就收不到消息了&#xff1b; 此时如果你手动调用发送另外一个消息订阅redis.user.two2&…

vmware workstation 虚拟机安装

vmware workstation 虚拟机安装 VMware Workstation Pro是VMware&#xff08;威睿公司&#xff09;发布的一代虚拟机软件&#xff0c;中文名称一般称 为"VMware 工作站".它的主要功能是可以给用户在单一的桌面上同时运行不同的操作系统&#xff0c;它也是可进 行开发…

c# 容器变换

List<Tuple<int, double, bool>> 变为List<Tuple<int, bool>>集合 如果您有一个List<Tuple<int, double, bool>>并且您想要将其转换为一个List<Tuple<int, bool>>集合&#xff0c;忽略double值&#xff0c;您可以使用LINQ的S…

3U 与 SV630A 伺服实现 CANLINK 通讯

1、打开 AUTOSHOP&#xff0c;点击工具>系统选项&#xff0c;勾选自动生成 canlink 轴 控通讯配置和 canlink 轴控指令增强功能。 2、检查 plc 的拨码是否已经拨上去。 1 代表 485 通讯&#xff0c;2 代表 can 通讯&#xff0c;将 2 打到 ON 状态。还有9&#xff0c;10拨…

Matlab 计算一个平面与一条直线的交点

文章目录 一、简介二、实现代码三、实现效果参考资料一、简介 这里使用一种很有趣的坐标:Plucker线坐标,它的定义如下所示: 这个坐标有个很有趣的性质,将直线 L L L与由其齐次坐标 V = (

IDEA社区版使用Maven archetype 创建Spring boot 项目

1.新建new project 2.选择Maven Archetype 3.命名name 4.选择存储地址 5.选择jdk版本 6.Archetype使用webapp 7.create创建项目 创建好长这样。 检查一下自己的Maven是否是自己的。 没问题的话就开始增添java包。 [有的人连resources包也没有&#xff0c;那就需要自己添…

AI人工智能开源大模型生态体系分析

人工智能开源大模型生态体系研究 "人工智能开源大模型生态体系研究报告v1.0"揭示&#xff0c;AI(A)的飞速发展依赖于三大核心&#xff1a;数据、算法和算力。这一理念已得到业界广泛认同&#xff0c;三者兼备才能推动AI的壮大发展。随着AI大模型的扩大与普及&#xf…

el-table 动态添加删除 -- 鼠标移入移出显隐删除图标

<el-table class"list-box" :data"replaceDataList" border><el-table-column label"原始值" prop"original" align"center" ><template slot-scope"scope"><div mouseenter"showClick…

finalshell替换背景图片

&#x1f4d1;打牌 &#xff1a; da pai ge的个人主页 &#x1f324;️个人专栏 &#xff1a; da pai ge的博客专栏 ☁️宝剑锋从磨砺出&#xff0c;梅花香自苦寒来 ☁️运维工程师的职责&#xff1a;监…

SpringCloud之Nacos集群,让Nacos不再是谜

Nacos集群搭建 集群结构 Nacos的集群环境我们采用这种结构&#xff1a;3个Nacos注册中心1个MySql Nacos集群 我们在windows上安装3个Nacos节点。分配配置相关信息 application.properties: 持久化数据到mysql中 修改 cluster.conf.example为cluster.conf然后在里面写上相关…

stm32h743 NetXduo 实现http server CubeIDE+CubeMX

在这边要设置mpu的大小,要用到http server,mpu得设置的大一些 我是这么设置的,做一个参考 同样,在FLASH.ld里面也要对应修改,SECTIONS里增加.tcp_sec和 .nx_data两个区,我们用ram_d2区域去做网络,这个就是对应每个数据在d2区域的起点。 在CubeMX里,需要用到filex、dhc…

萝卜快跑:未来出行的双刃剑

欢迎来到 破晓的历程的 博客 ⛺️不负时光&#xff0c;不负己✈️ 在这个日新月异的科技时代&#xff0c;无人驾驶技术正以前所未有的速度改变着我们的出行方式。萝卜快跑&#xff0c;作为自动驾驶出租车领域的佼佼者&#xff0c;其出现无疑为城市交通注入了新的活力&#xff…

电容充放电时间计算

电容充电时间的结论&#xff1a;t充电 R * C 时&#xff0c;Ut2*VCC/3&#xff0c;这是一个不能让我释怀的结论。 1、电池充电 U0表示电容C在充电0时刻的电压值; Ut表示电容C在充电t时刻的电压值; U1表示电容C在充电∝时刻的电压值&#xff0c;就是电源电压; Q C * U ---…

Ubuntu18.04安装ROS

1.添加ROS软件源 sudo sh -c echo "deb http://packages.ros.org/ros/ubuntu $(lsb_release -sc) main" > /etc/apt/sources.list.d/ros-latest.listcurl -s https://raw.githubusercontent.com/ros/rosdistro/master/ros.asc输入指令&#xff1a;curl -s https:…