【校招 --阶段二 SQL编程】复合查询

1、复合查询

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

select *from emp where (sal>500 or job="MANAGER") and ename like "J%";

在这里插入图片描述

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

select ename ,emp.deptno ,sal from emp  order by empno asc,sal desc;

在这里插入图片描述
使用年薪进行降序排序

select  ename,sal*12+ifnull(comm,0) "nianxing" from emp order by  "nianxing" desc;

在这里插入图片描述
显示工资最低的员工的名字和工作岗位

select  ename,sal,job from emp where sal=(select Max(sal) from emp);

先在where找到sal最高的哪一个,然后再在sal中找到和max(sal)相同的name和job。
在这里插入图片描述
显示平均工资低于2000的部门号和它的平均工资

select avg(sal) from emp  group by deptno having avg(sal)>2000;

在这里插入图片描述
显示每种岗位的雇员总数,平均工资

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

在这里插入图片描述
2、多表查询

多表查询就是一次select联合多个表来查询结果
其实我们只要emp表中的deptno = dept表中的deptno字段的记录

select *from emp,dept where emp.deptno=dept.deptno;

在这里插入图片描述
显示部门号为10的部门名,员工名和工资

 select ename ,job,sal from emp,dept where emp.deptno=dept.deptno and emp.deptno=10;

	```
![在这里插入图片描述](https://img-blog.csdnimg.cn/8fd85b0ffef34a5a8b50e874e9cc7f38.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA6Ieq6aaW55qE5bCP5YG3,size_19,color_FFFFFF,t_70,g_se,x_16)
**显示各个员工的姓名,工资,及工资级别**

```c
select ename,job ,sal,grade from emp,salgrade where emp.sal between losal and hisal;

在这里插入图片描述
3、自连接

显示员工FORD的上级领导的编号和姓名

select mgr,ename from emp where ename=(select ename from emp where ename="FORD");

使用子查询先找到name为ford的员工再根据name找到领导编号
在这里插入图片描述

4、子查询

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

单行子查询

显示SMITH同一部门的员工

select ename,job,sal from emp where job=(select job from emp where ename="SMITH");

在这里插入图片描述
多行子查询

in关键字

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

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

先查到工作和10号部门的iob种类,再接着除去10号部门的。

在这里插入图片描述
all关键字
显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号\

select ename ,job,deptno,sal from emp where sal >all (select sal from emp where deptno = 30 );

在这里插入图片描述

any关键字
显示工资比部门30的任意员工的工资高的员工的姓名、工资和部门号

select ename ,job,deptno,sal from emp where sal >any (select sal from emp where deptno = 30 );

在这里插入图片描述
多列子查询

单行子查询是指子查询只返回单列,单行数据;多行子查询是指返回单列多行数据,都是针对单列而言的,而多列子查询则是指查询返回多个列数据的子查询语句
查询和SMITH的部门和岗位完全相同的所有雇员,不含SMITH本人

单行查询方式

select ename ,job from emp where job = (select job from emp where ename="SMITH" ) and deptno =(select deptno from empwhere ename="SMITH") and ename<> "smith";

在这里插入图片描述
多行查询方式

select ename ,job from emp where (job,deptno) = (select job,deptno from emp where ename="SMITH" )and ename<> "smith";

在这里插入图片描述

在from子句中使用子查询

子查询语句出现在from子句中。这里要用到数据查询的技巧,把一个子查询当做一个临时表使用
//获取各个部门的平均工资,将其看作临时表

select ename,job,sal,emp.deptno from emp,(select avg(sal) al,emp.deptno from emp group by deptno) tmp where emp.sal>tmp.al and emp.deptno=tmp.deptno;

在这里插入图片描述
查找每个部门工资最高的人的姓名、工资、部门、最高工资

select ename,job,sal,emp.deptno,al from emp,(select Max(sal) al,emp.deptno from emp group by deptno) tmp where emp.deptno=tmp.deptno and emp.sal=al;

在这里插入图片描述
显示每个部门的信息(部门名,编号,地址)和人员数量
使用子查询

select job,mgr,emp.deptno,al from emp,(select count(*) al,emp.deptno from emp group by deptno) tmp where emp.deptno=tmp ;

在这里插入图片描述
合并查询

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

select * from emp where sal>2500 union select *from emp where job="MANAGER";

在这里插入图片描述
union all
该操作符用于取得两个结果集的并集。当使用该操作符时,不会去掉结果集中的重复行。

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

**select * from emp where sal>2500 union select *from emp where job="MANAGER";**

在这里插入图片描述
7.表的内外链接

内连接

内连接实际上就是利用where子句对两种表形成的笛卡儿积进行筛选,我们前面学习的查询都是内连接,也是在开发过程中使用的最多的连接查询。
语法:

select 字段 from 表1 inner join 表2 on 连接条件 and 其他条件;

显示SMITH的名字和部门名称

-- 用前面的写法
select ename, dname from EMP, DEPT where EMP.deptno=DEPT.deptno and ename='SMITH';
-- 用标准的内连接写法
select ename, dname from EMP inner join DEPT on EMP.deptno=DEPT.deptno and
ename='SMITH';

在这里插入图片描述
外连接

左外连接

select 字段名 from 表名1 left join 表名2 on 连接条件

select * from dept  left join emp on dept.deptno=emp.deptno;

在这里插入图片描述
右外连接

select * from dept  right join emp on dept.deptno=emp.deptno;

在这里插入图片描述

  • 6
    点赞
  • 13
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 5
    评论
评论 5
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

自首的小偷

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值