惠州外贸网站建设推广,网站开发后所有权,广东网站建设企业,共享的网站备案一、单行子查询
/*一、单行子查询格式#xff1a;select 列明表 from 表名(查询select 语句)where 列或表达式 比较运算符(SELECT 列名 FROM 表名 WHERE 条件)-- 子查询#xff0c;必须要使用小括号括起来---最大值函数#xff1a;max()最小值函数: min()二、 from…一、单行子查询
/*一、单行子查询格式select 列明表 from 表名(查询select 语句)where 列或表达式 比较运算符(SELECT 列名 FROM 表名 WHERE 条件)-- 子查询必须要使用小括号括起来---最大值函数max()最小值函数: min()二、 from 子查询from 后面的表不是一个具体的表而是一个查询或者多层查询S果从1开始可以是 或者 between and
*/
例题
-- 单行子查询是指只返回一行或者说是一个记录的子查询
-- 例如查找出与‘SMITH在同一个部门deptno工作的所有职工姓名以及工资
-- 1.1、找出SMITH所在的部门号
select deptno
from scott.emp
where ename SMITH; -- 20
-- 1.2、根据条件查询
select ename,sal
from scott.emp
where deptno 20;-- 1.3、使用子查询实现
select ename,sal
from scott.emp
where deptno (select deptno from scott.emp where ename SMITH
);-- 2、查找出表中在CHICAGO地点工作的职工的姓名工种、工资
-- dept - 部门表
-- deptno - 部门编号 number
-- dname - 部门名字 varcha2
-- loc - 部门送在地 varchar2-- 2.1 在部门表dept中找到CHICAGO对应的部门号
select deptno from scott.dept where loc CHICAGO;-- 2.2 根据2.1获得的部门号查询到数据
select ename,job,sal from scott.emp where deptno 30;-- 2.3 子查询替换2.1和2.2
select ename,job,sal from
scott.emp
where
deptno (select deptno from scott.dept where loc CHICAGO);-- 3、查找出工资比SCOTT高并且在NEW YORK工作的职工的有关情况。-- 3.1 先找出SCOTT的工资
select sal from scott.emp where ename SCOTT;
-- 3.2 在部门表dept中找到new YORK 的部门号
select deptno from scott.dept where loc NEW YORK; -- 10
-- 3.3 根据条件查询
select * from scott.emp
where
sal 3000 and deptno 10;
-- 3.4 合并为子查询
select * from scott.emp
where
sal (select sal from scott.emp where ename SCOTT)
and
deptno (select deptno from scott.dept where loc NEW YORK)
;-- 4、查找出工资比SCOTT工资高的职工的名字工种工资和所在的部门号并按工资升序排序。-- 4.1 找出scott的工资
select sal from scott.emp where ename SCOTT;
-- 4.2 根据条件查询
select ename,job,sal,deptno
from scott.emp
where
sal 3000
order by sal
;
-- 4.3 子查询替换
select ename,job,sal,deptno
from scott.emp
where
sal (select sal from scott.empwhereename SCOTT
)
order by sal;-- 5、查找出具有最高月工资的雇员的姓名、工种和工资。
-- 5.1 先找出最高的工资是多少 max() 最大值、min()最小值
select max(sal) from scott.emp;
-- 5.2 根据条件查询
select ename,job,sal
from scott.emp
where
sal 5000
;
-- 5.3 子查询合并
select ename,job,sal
from scott.emp
where
sal (select max(sal) from scott.emp
)-- 6、列出与SCOTT从事相同工作的雇员
select ename
from scott.emp
where job (select jobfrom scott.empwhereename SCOTT
)
;
-- 7、列出某些雇员的姓名和佣金条件是他们的薪资等于部门30中任何一个雇员的薪资
select ename,sal
from scott.emp
where
sal in(select sal from scott.emp where deptno 30
)
and
deptno ! 30
;-- 8、列出某些雇员的姓名和佣金条件是他们的薪资高于部门30中所有雇员的薪资
select ename,sal
from scott.emp
where
sal (select max(sal) from scott.empwhere deptno 30
)
;-- 9、列出薪资水平处于第四位的雇员
/*这里涉及到一个函数rownum伪列 取序号 从1开始from子查询
*/
-- 9.1 对薪资表进行排序
select * from scott.emp order by sal desc;-- 9.2 使用form子查询9.1sql并且使用rownum获取行号
select e.*,rownum r
from(select * from scott.emporder by sal desc
) e
;-- 9.2 合并子查询
select * from(select e.*,rownum rfrom (select * from scott.emporder by sal desc) e
) where r 4
;-- 11、不用分组函数求出薪水的最大值
select max(sal) from scott.emp;-- 子查询替换
select * from scott.emp
where sal (select max(sal) from scott.emp
)
;-- 12、查询所有工资高于平均工资平均工资包括所有员工的销售人员‘SALESMAN’
-- avg() 获取到平均工资
-- 12.1 找出平均薪资
select avg(sal) from scott.emp;-- 12.2 合并子查询
select * from scott.emp
where job SALESMAN
and
sal (select avg(sal) from scott.emp)
;-- 13、查询工资最高的3名员工信息(from子查询)
-- rownum 取序列号
-- 子查询工资降序
-- 子查询的表是降序表
select e.*,rownum r
from (select * from scott.emporder by sal desc
) e
where rownum 4 二、with as 语句
/*三、with as 语句语法with 别名 as (select 语句)别名 as (select 语句)...select 查询-------语句作用可以创建一个自定义别名作为表名的sql临时表在后续操作可以方便从sql中直接提取临时表进行使用
*/
-- 1、 举例找出emp表中工资排名为6-10的记录
-- 1.1 from子查询
with a as (select * from scott.emp order by nvl(sal,0) desc),b as (select a.*,rownum r from a)select * from b where r between 6 and 10;-- 2、列出薪资水平处于第四位的员工
with a as(select * from scott.emp order by sal desc),b as(select a.*,rownum r from a)select * from b where r 4
;-- 3、查询工资最高的3名员工信息
with a as(select * from scott.emp order by sal desc),b as(select a.*,rownum from a where rownum 4)select * from b
;
-- 用bteween and
with a as(select * from scott.emp order by sal desc),b as(select a.*,rownum from a where rownum between 1 and 3)select * from b
;-- 4、查询工资高于编号为7782的员工工资并且和7369员工从事相同工作的员工编号
select sal from scott.emp where empno 7782;
select job from scott.emp where empno 7369;
--合并子查询 -- 典型的单行子查询
select * from scott.emp where sal (select sal from scott.emp where empno 7782
)and job (select job from scott.emp where empno 7369
)
;-- 5、显示出和员工姓名中包含W的员工相同部门的员工姓名
-- 5.1 找出部门编号
select deptno from scott.emp where ename like %W%;
-- 5.2 用部门号去查询信息合并子查询
select ename,deptno,empno from scott.emp
where deptno (select deptno from scott.emp where ename like %W%
)
;-- 6、显示比工资最高的员工参加工作时间晚的员工姓名参加工作时间
-- 6.1 找出最高工资
select max(sal) from scott.emp;-- 6.2 通过工资找人
select hiredate from scott.emp where sal (select max(sal) from scott.emp
)
;-- 6.3 通过找到的人的时间去获取到最晚的那个人
select ename,empno,hiredate,sal from scott.emp
where hiredate (select hiredate from scott.empwhere sal (select max(sal) from scott.emp)
)
;-- 7、查询入职日期最早的前五名员工姓名入职日期并显示序号
-- 7.1、时间升序
select * from scott.emp order by hiredate;-- 7.2、通过时间升序和rownum查询
select ename,hiredate,rownum
from (select * from scott.emp order by hiredate)
--where rownum 6
where rownum between 1 and 5
;-- 8、查询工作在HICAGO并且入职日期最早的两名员工姓名入职日期
-- 8.1 要获取到dept表的部门号
select deptno from scott.dept where loc CHICAGO;-- 8.2 从dept的部门号去查询emp表中的部门号获取到员工信息
select ename,hiredate from scott.emp
where deptno (select deptno from scott.dept where loc CHICAGO
)
;-- 8.3 获取到行号和表信息
select e.*,rownum from (select ename,hiredate from scott.empwhere deptno (select deptno from scott.dept where loc CHICAGO)
) e where rownum 3
;-- 8.4 使用 with as 编写
witha as (select * from scott.emp where deptno (select deptno from scott.dept where loc CHICAGO) order by hiredate)select a.*,rownum from a where rownum between 1 and 2
; 三、聚合函数 /*----------------四、分组/聚合函数 ------------------聚合/统计函数在某个结果集中计算count(字段名或者列名)计算指定字段或列为 非空 的行数count(*)计算表中的全部行数包含 重复行 和 空行avg(字段名)计算指定字段非空的平均值min(字段名)计算指定字段的最小值max(字段名)计算指定字段的最大值sum(字段名)计算指定字段非空的总和------count()统计数据去除重复数据关键字distinctcount(distinct 列)
*/
-- 1、相关的函数举例
select
count(*),
count(empno),
count(comm)
from scott.emp
; -- 返回回来的是统计出来的数量num-- 2、计算emp表中公司职工的最低工资、最高工资、平均工资、有奖金的人奖金不为空、总工资的综合
select
min(sal), -- 最低工资
max(sal), -- 最高工资
avg(sal), -- 平均工资
count(*), -- 表中的行总数
count(comm), -- 有奖金的人
sum(sal) -- 总工资
from scott.emp;-- 3、计算emp表中公司职工的总人数及工种数
select count(*) zs,count(empno) zs1,count(job) from scott.emp;
-- job 去重
select count(*) zs,count(empno) zs1,count(distinct job) from scott.emp;-- 4、计算全部销售员的年平均报酬平均工资 * 12 平均奖金 *12
-- 4.1 找出销售员
select * from emp where job SALESMAN; -- 4.2 销售员的平均工资和平均奖金
select avg(sal),avg(comm) from scott.emp where job SALESMAN; -- 4.3 对平均的值进行计算
select avg(sal*12),avg(comm*12) from scott.emp where job SALESMAN; -- 5、统计20号部门人数和工资总和
-- 5.1 查到部门为20的员工
select * from scott.emp where deptno 20;
-- 5.2 form查询去统计人数和工资总和
select count(*),count(empno),sum(sal) from scott.emp where deptno 20;-- 6、统计每个部门人数和工资总和
select count(*),count(empno),sum(sal) from scott.emp where deptno 20;select count(*),count(empno),sum(sal) from scott.emp where deptno 30;select deptno,count(*),count(empno),sum(sal) from scott.emp group by deptno;
