DML (DQL) 查询语句 (Oracle) --2

SQL查询语句 (DQL)

在这里插入图片描述

在这里插入图片描述

SELECT [DISTINCT] {*,column alias,…}

FROM table alias

WHERE 条件表达式

ORDER BY 排序字段列表 [asc|desc]

在这里插入图片描述

在这里插入图片描述

查询所有列

--注释
--查询 select *(通配符)|字段名,字段名... from 数据源;
--执行流程: from --> select

--1)  所有列

--查询|检索|获取 所有员工的所有信息 
--查询的数据: 所有的员工信息
--数据的来源: emp
--条件:
select * from emp; --所有数据的所有字段值
select * from dept;
select deptno,dname,loc from dept;  --推荐

查询所部分列

--2)  部分列
--查询所有的员工名字
select ename from emp;

--3)
--查询所有的员工编号和员工名称,员工上级的编号
select empno, ename, mgr from emp;

--4)
--查询所有员工所在的部门编号
select deptno from emp;

--查询公司所有的部门编号
select deptno from dept;

去重

--5)
--查询出所有有员工存在的部门编号,每个部门编号只出现一次 

--去重distinct
--对结果集中多条所有字段都重复(完全重复)的数据做去重
select distinct deptno from emp;

--6)
--查询出所有存在员工的部门的部门编号,以及员工名称
select distinct deptno,empno from emp;

取别名 :用双引号,原封不动显示。 不用双引号,英文小写变大写,而且别名里面不能有空格。

--7)
--查询表达式  计算器
select 1+1 from dept;
select 123*456 from dept;
select empno,ename,sal,deptno,123*456 from emp;

--8)
--取别名  结果集中的字段   表别名  
--""->原封不动显示
--select 字段名 (as) 别名,字段名 别名,... from 数据源 别名;
select empno,ename,sal,deptno,123*456 from emp;
select empno "no",ename 员工名字,sal as 薪资,deptno "部门      编号",123*456 星球编号 from emp e;

字符串 :使用单引号

--9)
--字符串 
--''
select distinct 'abc' from emp;

select ename from emp;

--10)
--字符串拼接  ||
select 'sxt-'||ename 员工姓名 from emp;

伪类:不存在的列,构建虚拟的列

--11)
--伪列 : 表中不存在字段,但是可以查: 表达式  数值  字符串
select 1 from emp;

虚表 : 用于计算表达式,显示单条记录的值

--12)
--虚表 dual
select distinct 123*456 from emp;
select 123*456 from dual;
select sysdate from dual;

null值运算

--null值和数字 参与 运算结果还是null
--null值和字符串运算,结果为原串 
--处理null  -> nvl(字段,值2)  当字段值不为null,nvl函数最终的结果就为字段值,如果字段值null,函数最终的结果为值2

--13)
--给每一个员工在原来的基础上+1块钱奖金
select empno,ename,sal,comm 原奖金,nvl(comm,0)+1 现奖金 from emp;
select empno,ename,sal,comm 原奖金,comm||'1' 现奖金 from emp;
select nvl(comm,0) from emp;


--查询所有员工的名字, 工种, 年薪(不带奖金)
select ename 员工名字,job 工种,sal*12 "年薪(不带奖金)" from emp;

--查询所有员工的名字,工种,年薪(带12月奖金的)
select ename 员工名字,job 工种,sal*12+nvl(comm,0)*12 "年薪(带12个月奖金)" from emp;

--查询所有员工的名字, 工种, 年薪(带一次奖金的)
select ename 员工名字,job 工种,sal*12+nvl(comm,0)*1 "年薪(带一个月奖金)" from emp;

条件查询

​ --条件查询
–select 数据 from 数据源 where 行过滤条件;
–执行流程: from–>where–>select

where 过滤行记录条件 ,条件有

​ a)、= 、 >、 < 、 >=、 <=、 !=、 <> 、 between and (betweend and 取的左右都是闭区间)

--   = 、 >、 < 、 >=、 <=、

--查询员工名称为SMITH的员工信息
select * from emp where ename = 'SMITH';

--查询30部门的员工信息
select * from emp where deptno = 30;

--查询薪资>1500的员工信息
select * from emp where sal>= 1500;





--   !=、 <>

--查询除了30部门以外的员工信息
select * from emp where deptno != 30;

select * from emp where deptno <> 30;

select * from emp where not deptno = 30;





--between and 
select * from emp where sal between 1500 and 3000;

--查询EMP表显示在   1981年2月1日到1981年5月1日   之间雇佣的 雇员名、岗位及雇佣日期, 并以雇佣日期进行排序。
select ename,job,hiredate from emp where hiredate between  
to_date('1981-02-01','yyyy-mm-dd') and
to_date('1981-05-01','yyyy-mm-dd') order by hiredate;

-- 使用函数 to_date('2007-09-07 00:00:00','yyyy-mm-dd hh24:mi:ss')得到日期

​ b)、and 、or、 not、 union、 union all、 intersect 、minus

-- and 、or、 not、
--查询薪资>1500 同时<3000的员工信息

select * from emp where sal>= 1500 and sal<=3000;

--查询10或者20部门的员工信息

select * from emp where deptno = 10 or deptno = 20;




--   union、 union all、 intersect 、minus 
--集合函数: 对结果集的操作
--union 并集(去重)、  union all并集不去重、  intersect交集 、minus 差集
select * from emp where sal>= 1500
intersect
select * from emp where  sal<=3000;

--查询没有员工存在的部门编号
select deptno from dept
minus
select distinct deptno from emp;


​ c)、 is null 、 is not null

-- null   只能使用  is null   或者    is not null
--奖金 为null的员工信息
select * from emp where comm is null;


--奖金 不为null的员工信息
select * from emp where not comm is null;
select * from emp where  comm is not null;

​ d)、like (表示模糊查询) % (表示匹配任意个字符) _ (表示匹配一个字符) escape(‘单个字符’) (表示单个字符为转义字符)

--模糊匹配 like

--%:任意个任意字符    _: 一个任意字符
select * from emp where ename like 'SMITH';
--名字中包含A被查询
select * from emp where ename like '%A%';
--名字中第二个字符为A
select * from emp where ename like '_A%';

​ f)、in 、 exists(难点) 及 子查询

-- in相当于使用or的多个等值

-- 查询emp中800或900工资的所有员工信息
select * from emp where sal in(900,800);

--10或30部门的雇员信息
select * from emp where deptno in(10,30);

--部门名称为 SALES 或 ACCOUNTING 的雇员信息 
select deptno from dept where dname in('SALES','ACCOUNTING');

--子查询(查询中再有查询) in 只能存在一个字段
select * from emp where sal in (select sal
from emp e where deptno=10);

--在emp中,'SALES', 'ACCOUNTING'对应部门号的所有的员工信息
SELECT * FROM emp WHERE deptno IN (SELECT deptno FROM dept WHERE dname IN ('SALES', 'ACCOUNTING'));


--exists 存在  存在即合法,存在就保留
--exists (查询语句) 从from后数据源中拿出一条数据,判断exists(结果集)如果存在数据,当前这条数据就满足条件

-- exists   全部取到  ---1 
-- 因为内层查询存在数据 true,那么外层每一条数据进去比对 都能 返回 exists的 存在 true
select * from emp 
where exists (select deptno,dname from dept 
             where dname in ('SALES', 'ACCOUNTING'));


-- exists     e.deptno = d.deptno   只有 10 和 30 的deptno取到  ---2
--因为内层 第一个查询存在数据 两列 部门号d.deptno (此处得到10和30) 和部门名,然后第二个查询是要求 外层每一行数据的部门号e.deptno 进去比对d.deptno,都是看看e.deptno是否等于10或者30,如果是能和其中一个相等,那么这行就算是exists存在的,返回得到true。所以最后返回的是emp中部门号为10的所有数据还有部门号为30所有数据,一共9行。
select * from emp e
 where exists (select deptno, dname from dept d 
              where dname in ('SALES', 'ACCOUNTING') and e.deptno = d.deptno);
              

-- not exists  e.deptno = d.deptno  ---2 的取反
-- 这个与上面的取反
select * from emp e 
where not exists (select deptno, dname from dept d 
                 where dname in ('SALES', 'ACCOUNTING')and e.deptno = d.deptno);              
              
--exists     全部取到   --- 3 
--因为内层 第一个查询存在数据 两列 部门号d.deptno (此处得到10和30) 和部门名,然后第二个查询是要求 外层每一行数据的部门号e.deptno 进去比对d.deptno,都是看看e.deptno是否不等于10或者30,如果是能和其中一个不相等,那就是true。如 此时的e.deptno是10,和10比较是相等,不符合,和30比较,达到了不相等的条件,那么就返回true。
select * from emp e
where exists (select deptno, dname from dept d 
             where dname in ('SALES', 'ACCOUNTING')and e.deptno != d.deptno);

--不是exists  用in取  
--只返回5行数据
select ename, sal from emp
where sal in (select sal from emp e2 
             where e2.sal >= 2000); 
             
--exists     全部取到   --- 1          
--返回12行数据,内层永远是true,所以外层都能表示exists的true,那么全部都符合(和上面第一个exists情况相同)
select ename, sal from emp 
where exists (select ename, sal from emp e2 
             where e2.sal >= 2000);
         
         
-- exists     ---2          
--内层的第一个查询empno, ename, sal, comm,可以返回四行数据,由于第二个查询e1.empno = e2.empno,那么外层每个 e1.empno 和 第一个查询的4个e2.empno进行相等,所以最后整个得到4行数据。 (和上面第二个exists情况相同)
select empno, ename, sal from emp e1 
       where exists (select empno, ename, sal, comm from emp e2
                    where comm is not null and e1.empno = e2.empno); 
                    
-- exists     ---2  隐含                    
--内层的第一个查询empno, ename, sal, comm,可返回四行数据,由于第二个查询e1.deptno = e2.deptno,可以看到内层第一个查询没有deptno,但是实际上四行数据对应deptno只有30,所以也可以理解为第一个查询隐含了deptno为30,那么外层每个 e1.deptno 和 第一个查询的里面隐含的所有e2.deptno进行相等,最后得到了整个deptno = 30的6行数据。和 select empno, ename, sal from emp where deptno = 30 的结果一样。
select empno, ename, sal from emp e1 
       where exists (select empno, ename, sal, comm from emp e2 
                    where comm is not null and e1.deptno = e2.deptno);

子查询

​ 子查询 对于内层的查询可以使用 in 而不使用 =

--子查询-------

--查询 最高薪水的员工姓名,及薪水
select max(sal) from emp; 
--子查询 薪资与最高薪资相等的员工姓名
select ename from emp where sal == (select max(sal) from emp);
--子查询 对于内层的查询可以使用 in 。因为如果内层查询有两行或者以上的数据,用 = 去取值,会出错,用in不管里面是一行或者多于一行都可以使用。
select ename from emp where sal in (select max(sal) from emp);

--查询 平均薪资
select avg(sal) from emp;
--子查询 查询工资低于总平均工资的员工编号,姓名及工资
select empno,ename,sal from emp where sal < (select avg(sal) from emp);

排序 order by

​ 使用 ORDER BY 排序,排序不是真实改变存储结构的顺序,而是获取的集合的顺序。

​ 从上到下升序 :asc(默认) 从上到下降序 : desc

​ 多字段: 在前面字段相等时,使用后面的字段排序

--select 数据 from 数据源 where 行过滤条件 order by 排序字段1 desc|asc,排序字段2...  默认升序

--执行流程: from --> where  -->select -->排序
--根据薪资降序排序,查询10,30部门
select * from emp where deptno in(10,30) order by sal,comm desc;

注意: null 默认为最大,在升序时在最下面,在降序时在最上面

​ 但可以强制修改null位置: nulls first|last

--nulls first|last
select * from emp order by comm  nulls first ;   -- 本来null在最下面,这样强制把null放最上面

分组

--分组----------------------------------------------------

--分组 把数据根据不同的逻辑分城不同的小组,以组委单位的计算
--如果一旦分组,select后面只能使用 分组字段 或者 组函数
--select 数据 from 数据源 where 行过滤条件 group by 分组字段 having 组过滤信息 order by 排序字段;
--执行流程: from   where  group by   having   select   order by
--注意: where后面不能使用组函数,having后可以
--在where和having的后面都不能使用别名

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


-- 找出20部门和30部门的最高工资 
--先过滤后分组
select max(sal),deptno from emp where deptno in (20,30) group by deptno;
--先分组后过滤
select max(sal),deptno from emp group by deptno having deptno in(20,30);

-- 求出每个部门的平均工资
select avg(sal),deptno from emp group by deptno;

-- 求出每个部门员工工资高于1000的的平均工资
select avg(sal),deptno from emp where sal>1000 group by deptno order by avg(sal) desc;


-- 求出10和20部门的哪些工资高于1000的员工的平均工资
select avg(sal),deptno from emp where sal>1000 and deptno in(20,10) group by deptno order by avg(sal) desc;
select avg(sal),deptno from emp where sal>1000 group by deptno having deptno in(20,10) order by avg(sal) desc;



--所有部门的部门编号和平均工资
select avg(sal),deptno from emp group by deptno; 

--求出平均工资高于2000的部门编号和平均工资
select avg(sal),deptno from emp group by deptno having avg(sal) > 2000;
select * from (select avg(sal) avg_sal,deptno from emp group by deptno) where avg_sal>2000;

--按 部门岗位(job) 查询 平均工资
select avg(sal),job from emp group by job;

--按 岗位查询 平均工资,且平均工资大于2000的岗位
select avg(sal) avg_sal,job from emp group by job having avg(sal)>2000;


--查询 最低平均工资的部门编号
--1)每个部门的平均工资和部门编号
select avg(sal) from emp group by deptno ;
--2)找到最低平均薪资
select min(avg(sal)) from emp group by deptno ;
--3)部门平均薪资与最低平均薪资相等的部门编号
select avg(sal),deptno from emp group by deptno having avg(sal) = (select min(avg(sal)) from emp  group by deptno);

获取所有行的记录

select * from emp; 

--这里使用 1=1是为了写java代码方便。
--如果没有使用1=1,那么在java拼接的时候,需要先判断是否是第一个行过滤语句(即在where右边最近的行过滤语句),如果是的话那么直接拼接  + "行过滤语句"  ,如果不是的话,那么就要拼接 + "and 行过滤语句"  。
--如果使用了1=1 ,那么不需要判断是不是第一个行过滤语句,直接使用  + "and 行过滤语句"  拼接。
select * from emp where 1=1 ;  

--这个 ename like '%' 也是和上面 1=1 一样的效果,不过like查询效率更低。
select * from emp where ename like '%'; 

函数

单行函数

日期函数:

​ add_month(d,x)

​ lastdate(hiredate)

​ months_between(date1,date2)

​ next_day(sysdate, ‘星期六’)

​ to_char(d,m)

​ to_date(c,m)

--当前日期
select sysdate from dual;
select current_date from dual;

--日期可以直接做加减 (加减的是天数)
select current_date+1 from dual;

--日期加减月份 (这里加的是90天,但是 3个月 有 28 30 31等等 不一定是90天)
select hiredate 入职日期,hiredate+90 转正日期 from emp;

-- add_month(d,x)  d是日期,x是几个月,给日期添加月份
select hiredate 入职日期,add_months(hiredate,3) 转正日期 from emp;

--lastdate(hiredate) 本月份的最后一天  每个员工入职 
select last_day(hiredate), last_day(sysdate) from emp;

--months_between(date1,date2) 返回date1和date2之间月的数目  
-- date1 放后面的日期 ,date2放前面的日期

--下一个 星期x  距下一个星期x最近的的日期
select next_day(sysdate, '星期六') from dual;
--结果:2020/11/7 20:37:56


-- 日期和字符串的互相转化
--to_char(d,m)     -> 日期以指定格式转换为字符串
select to_char(hiredate,'yyyy"年"mm"月"dd ') from emp;
select to_char(sysdate,'yyyy"年"mm"月"dd  hh24:mi:ss') from emp;
select to_char(sysdate, 'yyyy') from emp;

--to_date(c,m)     -> 字符串以指定格式转换为日期  可以对日期进行加减
select to_date('2021年02月22日','yyyy"年"mm"月"dd"日"')+1 from dual;

判定函数:

​ nvl(string1,string2)

​ decode(判定字段,值1,结果1,值2,结果2…,默认值)

​ case when then else end

--判定函数 

--nvl(string1,string2) 如果string1为 null,则结果为string2的值 
select ename, nvl(null,0) from emp; select ename, nvl(to_char(comm),'hello') from emp;

--decode(判定字段,值1,结果1,值2,结果2...,默认值)
--查询部门信息,添加一个字段显示部门编号 的中文形式 10,十   20,二十...
select deptno,
       dname,
       loc,
       decode(deptno, 10, '十', 20, '二十', 30, '三十', '四十') 部门中文名称
  from dept;


--case when then  else end
--部门号10涨1.1   20涨1.08   30涨1.15   其他部门1.2
select ename,
       sal,
       deptno,
       (case deptno
         when 10 then
          sal * 1.1
         when 20 then
          sal * 1.08
         when 30 then
          sal * 1.15
         else
          sal * 1.2
       end) raisesal
  from emp;

多行函数

​ 多行函数 只能和其他 多行函数 ,或者 分组字段 一起使用

count()

sum()

max()

min()

avg()

--多行函数---------------------------------------------

--组函数|多行函数|聚合函数
--count(1|*|字段名 )  sum()  max()  min()  avg()
--select 后面可以多个组函数同时使用
--先确定要计算的结果集|组|多条数据,然后再进行组函数计算
--注意:
--在select后面,组函数只能和 其他组函数 ,或者 分组字段 一起使用


-- count()

-- 统计一下一共有多少个员工
select count(*) from emp;
select count(empno) from emp;
select count(deptno) from emp;


-- count(去重 distinct)
-- 统计有员工存在的部门总数
select count(distinct deptno) from emp;


-- count() where 判定条件
-- 统计20部门一共有多少人
select count(*) from emp where deptno = 20;
-- 统计有奖金的员工有几个
select count(1) from emp where comm is not null;


--count() 伪列
select count(1) from emp;
-- count() 不计算null
select count(comm) from emp;


-- sum()

-- 计算本公司每个月一共要在工资上花费多少钱
select sum(sal) from emp;


-- 计算20部门每个月的工资花销
select sum(sal) from emp where deptno = 20;

-- 计算出所有员工的奖金总和
select sum(comm) from emp;


-- max()

-- 查询本公司的最高工资和最低工资
select max(sal),min(sal),sum(sal) from emp;


--查看30部门的最高工资和最低工资
select max(sal),min(sal),sum(sal) from emp where deptno = 30;


-- avg()

--请查询出 20部门的平均工资
select avg(sal) from emp where deptno  = 20;

--分组 group by 分组字段
--请查询出 20部门的平均工资, 部门编号
select avg(sal),deptno from emp where deptno = 20 group by deptno;

两张表

在这里插入图片描述

在这里插入图片描述

行转列

--1、行转列 decode
select name,
       decode(course, '语文', score) 语文,
       decode(course, '数学', score) 数学,
       decode(course, '英语', score) 英语
  from tb_student;
--2、分组 
select name,
       min(decode(course, '语文', score)) 语文,
       min(decode(course, '数学', score)) 数学,
       min(decode(course, '英语', score)) 英语
  from tb_student

在这里插入图片描述

©️2020 CSDN 皮肤主题: 1024 设计师:上身试试 返回首页