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;
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/c30ea79e513a2633839d6f9cd3a49638.png)
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;
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/b994ff9f371c83e42b96f81a112a184b.png)
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");
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/c52a2fa10f2eafc37f9b371b1dca2c8e.png)
多行子查询
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 );
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/a00f2438404c7a764a7b7d4ef71521ce.png)
多列子查询
单行子查询是指子查询只返回单列,单行数据;多行子查询是指返回单列多行数据,都是针对单列而言的,而多列子查询则是指查询返回多个列数据的子查询语句
查询和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 ;
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/fb305b7e3a261bcfed807fcbb38e3498.png)
合并查询
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";**
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/f0e1327cbdd5588ed5662c7648743d50.png)
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';
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/24f16e81d75286401f346a9f1c40d477.png)
外连接
左外连接
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;