Oracle的sql （一 )-优快云博客

本文详细介绍Oracle SQL的基础查询操作，包括条件查询、排序、函数使用、分组查询等，并讲解了连接查询、子查询及集合运算等内容。

2019独角兽企业重金招聘Python工程师标准>>>

Oracle体系结构:

数据库 ----> 实例(orcl) ---> 表空间(逻辑单位)(用户) ---> 数据文件(物理单位)
地球 ----> 国家 ---> 省份(省长,公民) ---> 中粮,山川河流

Oracle和mysql区别:

收费, 不开源
Oracle特有的方言/特有的语法
安全级别高

假设要做一个项目:
mysql : 新建一个数据库
Oracle : 新建一个表空间,指定一个用户,让这个用去去创建相应的表里

mysql : 多数据库
Oracle : 多用户操作 ,所有的表都是放在用户下面

基本查询操作

SQL分类:
DDL: 数据定义语言,定义的表的结构 , create , alter, drop ,truncate
DML: 数据操纵语言, 操纵表中数据 , insert , update, delete
DCL: 数据控制语言, 控制一些安全级别, 授权,取消授权 grant revoke
DQL: 数据查询语言, 查询数据 , select , from , where

查询语句的基本结构:
select 显示的列名 from 从哪张表查 [where 条件 ]

select * from tab;

select 1+1; -- 崩溃........ mysql可以,Oracle不行
/*
       dual : oracle中虚表/伪表, 主要是用来补齐语法结构的
*/
select 1+1 from dual;
select * from dual;

-- 查询emp所有信息
select * from emp;


/*
     别名查询 : as 关键字, as可以省略
     如果别名中包含特殊字符或者关键字,请用双引号 扩起来
       
     注意: 单引号 ' ' 字符串值 ,  基本上只有在别名的时候才会使用双引号
*/
select ename as 姓名 from emp;

select ename as "姓 from 名" from emp;


/*
     去除重复数据 distinct 
     注意: 如果是多列, 必须每一列数据都相同才算重复
*/
select job,deptno from emp;

select distinct job,deptno from emp;

四则运算: + - * /

null值问题 , null : 未知的,不可预知的内容不能做四则运算

select 8/2 from dual;

-- 查询员工的年薪
select sal*12 from emp; 

-- 查询员工的年薪
select sal*12,sal*12+comm from emp;  -- 错误

-- nvl 函数 : 判断第一个参数是否为null  如果是,则返回第二个参数, 否则返回第一个参数

select sal*12,sal*12+nvl(comm,1) from emp;

字符串连接

通用的方式:
concat(str1,str2) : str1+str2
Oracle特有的方式:
|| : 拼接字符串


-- 查询  姓名:ename 
select concat('姓名:',ename) from emp;

select concat('abc','efg') from dual;

select 'abc' || 'efg' || 'hij' from dual;

条件查询

where后面的写法:
操作符:
比较运算符: > >= = < <= != <>
逻辑运算符: and or not
其它运算符:
between.. and .. : 在区间内 [10,100] [10,50)
in : 在集合内
not in :
like : 模糊查询
exists(查询语句) : 存在的意思
is null : 判断是否为空
is not null : 判断不为空


-- 查询每月能得到奖金的员工信息
select * from emp where comm != null; --错误的演示

select * from emp where comm is not null;

-- 查询工资在1500--3000之间的员工信息
select * from emp where sal between 1500 and 3000;
select * from emp where sal>=1500 and sal <=3000;


-- 查询名字在某个范围的员工信息('JONES','SCOTT','BLAKE')  --区分大小写
select * from emp where ename in('JONES','SCOTT','BLAKE');

-- 查询员工姓名第三个字符是A的员工信息
/*
   like :
        % : 匹配多个字符
        _ :匹配单个字符
*/
select * from emp where ename like '__A%';

-- 查询名称中包含%的员工信息
insert into emp(empno,ename) values(9527,'HUA%AN');

select * from emp where ename like '%%%';  --所有的数据都查询出来

select * from emp where ename like '%/%%'; 

-- escape相当于是告诉数据库  \ 是一个转义字符

select * from emp where ename like '%\%%' escape '\'；

select * from emp where ename like '%#%%' escape '#'；

delete from emp where empno = 9527；

排序操作

order by 列名排序规则 nulls first|last
asc : ascend (默认)
desc : descend

多列排序:
排序条件两个


--按照工资进行排序
select * from emp order by sal desc;

--按照奖金进型排序 -升序
select * from emp order by comm;

--按照奖金进型排序 -降序
select * from emp order by comm desc;

select * from emp order by comm desc nulls last;
-- 按照部门升序进行, 按照工资降序进行排序
select * from emp order by deptno asc,sal desc;

函数


--------------------字符串函数--------------------------------

-- 截取字符串
-- 不管起始索引写0还是写1 都是从第一个字符开始截取
-- 第一个参数字符串, 第二起始索引, 第三个是长度
select substr('abcdefg',0,3) from dual;  --abc
select substr('abcdefg',1,3) from dual;  --abc
select substr('abcdefg',2,3) from dual; --bcd

-- 获取字符串长度
select length('abcdefg') from dual;

-- trim() 默认去除是字符两边的空格
select '   hello   ' from dual;
select trim('   hello   ') from dual;

-- 字符串替换
select replace('hello','ll','xx') from dual;

/*
     数值型的函数:
       四舍五入 : round
       向上取整: ceil
       向下取整: floor
*/
select round(45.926,2) from dual;  --45.93 
select round(45.926,1) from dual;  --45.9 
select round(45.926,0) from dual;  --46
select round(45.926,-1) from dual;  --50
select round(45.926,-2) from dual;  --0

-- trunc()  截断 : 不会进位
select trunc(45.926,2) from dual; --45.92
select trunc(45.926,1) from dual; --45.9
select trunc(45.926,0) from dual; --45
select trunc(45.926,-1) from dual; --40
select trunc(45.926,-2) from dual;  --0

-- 向大的取整
select ceil(-12.5) from dual;  -- -12
-- 向小的取整
select floor(-12.5) from dual;  -- -13

select abs(-12.5) from dual; --12.5
select mod(9,2) from dual; --1


--------------------日期函数--------------------------------

-- 查询当前的日期  -- 服务器中的时间
select sysdate from dual;  -- 2017/6/8 17:46:08 

-- 查询员工入职的天数
select emp.*, ceil(sysdate - hiredate) from emp;

-- 查询员工入职的周数
select emp.*, ceil((sysdate - hiredate)/7) from emp;

-- 查询员工入职的月数
select emp.*, months_between(sysdate,hiredate) from emp;

-- 查询员工入职的年份
select emp.*, months_between(sysdate,hiredate)/12 from emp;

-- 几个月后的日期
select add_months(sysdate,3) from dual;

select sysdate+23 from dual;

select sysdate+1.5 from dual;

/*
       字符串函数, 数值型, 日期型的函数
      
       转换函数:
          to_char
          to_number
          to_date
          
          
to_char(sysdate,'d') 每周第几天 
to_char(sysdate,'dd') 每月第几天 
to_char(sysdate,'ddd') 每年第几天 
to_char(sysdate,'ww') 每年第几周 
to_char(sysdate,'mm') 每年第几月 
to_char(sysdate,'q') 每年第几季 
to_char(sysdate,'yyyy') 年      
*/

-- to_number  鸡肋,  oracle自动转换
select '100'+10 from dual; -- 110
select to_number('100') + 10 from dual; -- 110

-- to_char
select sal from emp;
select to_char(sal,'$999,999.999') from emp;
select to_char(sal,'L999,999.999') from emp;


-- 日期转字符
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

select to_char(sysdate,'yyyy') from dual;

select to_char(sysdate,'mm') from dual;
select to_char(sysdate,'mon') from dual;
select to_char(sysdate,'month') from dual;
-- 一个星期中第几天
select to_char(sysdate,'d') from dual;  -- 5
-- 一个月中第几天
select to_char(sysdate,'dd') from dual;  -- 08
-- 一年中第几天
select to_char(sysdate,'ddd') from dual;  --159

select to_char(sysdate,'dddd') from dual; --1595

-- ----------------字符转成日期格式----------------------------------
select to_date('2017-06-08','yyyy-mm-dd') from dual;


-- 查询1980 -- 1985年入职的员工信息
select * from emp where hiredate between to_date('1980/01/01','yyyy/mm/dd') and to_date('1985/12/31','yyyy/mm/dd');


select nvl(null,10) from dual;  -- 10
select nvl(1,10) from dual;  -- 1
-- 如果第一个参数为null , 则返回第三个参数, 否则返回第2个参数
select nvl2(null,5,6) from dual; -- 6
select nvl2(1,5,6) from dual; -- 5

-- 判断两个数,是否相等, 如果相等,则返回null 否则返回第一个参数
select nullif(5,6) from dual;  -- 5
select nullif(5,5) from dual;  -- null

-- 找第一个不为null 
select coalesce(null,null,3,null,1) from dual;  -- 3

/*
       条件表达式:
            通用的方式:
                case 列名
                     when 值1  then 新内容
                     when 值2  then 新内容
                     when 值3  then 新内容
                     else
                          默认值
                     end
                     
           Orcale特有的方式:
                 decode(ename,if1,then1,if2,then2,if3,then3,默认值)  解码

*/

-- 给emp表中员工的姓名取一个中文名称
select ename from emp;
select 
       case ename 
            when 'SMITH' then '斯密斯'
            when 'ALLEN' then '艾伦'
            when 'WARD'  then '沃德'
            else
                 '隔壁老王'
            end
from emp;

select decode(ename,'SMITH','曹操','ALLEN','关羽','貂蝉') from emp;


------------------------------聚合函数-----------------------------------
/*
     聚合函数:
       count()  max() min  sum avg   聚合函数直接忽略空值
*/
-- 查询员工的奖金综合
select sum(comm) from emp;  --2200

-- 查询员工人数
select count(*) from emp;  --14

select 1 from emp;
select count(2) from emp;   -- 直接写一个常量,稍微高效

-- 查询员工的平均奖金
select avg(comm) from emp;  --550 

select sum(comm)/count(2) from emp; -- 157

分组查询

group by 分组条件

select 分组的条件,分组之后的操作 from 表名 group by 分组的条件 [having 条件过滤]

where | having

where 是在分组之前执行,不能接聚合函数,可以接单行函数
having 分组之后执行, 可以接聚合函数

SQL编写顺序:
select..from..where..group by ..having .. order by ..

SQL执行顺序:
from..where..group by..having..select..order by..


-- 查询部门的平均工资,并且过滤平均工资大于2000的部门
-- 1.统计所有部门的平均工资
select deptno,avg(sal) from emp group by deptno ;
--2 .条件过滤
select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;

连接查询

*/
/*
         笛卡尔积 :没有实际意义,两张表的乘积
*/
select * from emp;
select * from dept;

select * from emp,dept;
/*
     内连接 : 等值内连接/隐式内连接  
              不等值内连接
     显式内连接  :
           .. inner join.. on 连接的条件 
 */
-- 隐式内连接
select * from emp e,dept d where e.deptno = d.deptno;

select * from emp e,dept d where e.deptno != d.deptno;

--显式内连接 , inner 可以省略
select * from emp e inner join dept d on e.deptno = d.deptno;
select * from emp e join dept d on e.deptno = d.deptno;

-- on 执行顺序 比 where 靠前
-- emp , dept 生成的一张中间表,先生成的笛卡尔积,再使用 where 条件过滤
-- on 带着条件生成结果

/*
   自连接: 自己连接自己
*/
-- 查询员工的编号,姓名,经理编号,经理姓名
select e1.empno,e1.ename "员工姓名",e1.mgr,m1.ename "经理姓名" from emp e1,emp m1 where e1.mgr=m1.empno;

-- 查询员工的编号,员工姓名,员工的部门名称,经理编号,经理姓名,经理部门名称
select e1.empno,e1.ename ,d1.dname ,e1.mgr,m1.ename ,d2.dname
from emp e1,emp m1 ,dept d1,dept d2
where e1.mgr=m1.empno and e1.deptno = d1.deptno and m1.deptno = d2.deptno;

-- 查询员工的编号,员工姓名,员工的部门名称,工资等级,经理编号,经理姓名,经理部门名称,经理的工资等级
select e1.empno,e1.ename ,d1.dname ,s1.grade ,e1.mgr,m1.ename ,d2.dname,s2.grade
from emp e1,emp m1 ,dept d1,dept d2,salgrade s1,salgrade s2
where 
e1.mgr=m1.empno 
and e1.deptno = d1.deptno 
and m1.deptno = d2.deptno
and e1.sal between s1.losal and s1.hisal
and m1.sal between s2.losal and s2.hisal
;

-- 查询员工的编号,员工姓名,员工的部门名称,工资等级(中文),经理编号,经理姓名,经理部门名称,经理的工资等级(中文)
select 
e1.empno,
e1.ename "员工姓名",
d1.dname ,
case s1.grade
    when 1 then '一级'
    when 2 then '二级'
    when 3 then '三级'
    when 4 then '四级'
    when 5 then '五级'
    else
         '超级'
        end "员工工资等级",
e1.mgr,
m1.ename ,
d2.dname,
decode(s2.grade,1,'一级',2,'二级',3,'三级',4,'四级',5,'五级','超级') "经理工资等级"
from emp e1,emp m1 ,dept d1,dept d2,salgrade s1,salgrade s2
where 
e1.mgr=m1.empno 
and e1.deptno = d1.deptno 
and m1.deptno = d2.deptno
and e1.sal between s1.losal and s1.hisal
and m1.sal between s2.losal and s2.hisal
;

/*
    外连接查询:
         左外连接: 以左表为基础将,左表中所有的记录都查询出来,如果右表没有对应的记录,用null填充
         右外连接: 以右表为基础将,右表中所有的记录都查询出来,如果左表没有对应的记录,用null填充 
    left outer join .. on
    right outer join ..on 
     outer 可以省略     
    
    Oracle特有的写法 (+)  + 的是null 如果没有对应的记录,就用null填充
    
   
*/
-- 左外连接
select * from emp e left outer join dept d on e.deptno = d.deptno;

select * from emp e,dept d where e.deptno = d.deptno(+);

-- 右外连接
select * from emp e right outer join dept d on e.deptno = d.deptno;

select * from emp e,dept d where e.deptno(+) = d.deptno;

insert into emp(empno,ename) values(9527,'huaan');
delete from emp where empno=9527;

select * from salgrade;


/*
    子查询: 查询语句中嵌套查询语句
         解决一些复杂的查询需求
         
      按照行列划分:
         单行子查询: 查询出来结果只有一行
                操作符:
                     > >= = < <= != <>
                     
         多行子查询:  查询出来的结果有多行
                操作符:
                     in
                     not in
                     any 任何/任意
                     all 所有
                     exists 存在的意思
                     
         
      按照出现的位置:
         select 
         from
         where
         having
*/
-- 查询工资最高的员工信息
-- 1.查询最高工资是多少 5000
select max(sal) from emp;
-- 2. 看谁的工资等于最高工资
select * from emp where sal = 5000;
select * from emp where sal = (select max(sal) from emp);


-- 查询出比雇员7654的工资高,同时 和7788从事相同工作的员工信息
-- 1.雇员7654的工资 1250
select sal from emp where empno=7654;
-- 2. 7788从事的工作 ANALYST
select job from emp where empno = 7788;
-- 3. 结果:
select * from emp where sal > (select sal from emp where empno=7654) and job=(select job from emp where empno = 7788);


-- 查询每个部门最低工资的员工信息和他所在的部门信息
-- 1. 每个部门最低工资
select deptno,min(sal) minsal from emp group by deptno;

select * 
from 
       emp e1,
       (select deptno,min(sal) minsal from emp group by deptno) t1
where e1.deptno = t1.deptno and e1.sal = t1.minsal;

-- 2.连接部门表,查询信息
select * 
from 
       emp e1,
       (select deptno,min(sal) minsal from emp group by deptno) t1,
       dept d1
where 
       e1.deptno = t1.deptno 
       and e1.sal = t1.minsal
       and e1.deptno = d1.deptno;


-- 查询是领导的员工信息
-- 1. 得到所有经理的编号   int i=3  int j =4   i = j
select distinct mgr from emp where mgr is not null;  -- (7839,7782,7698)
-- 2. 结果:
select * from emp where empno in(select distinct mgr from emp);

-- 查询不是领导的员工信息
select * from emp where empno not in(select distinct mgr from emp where mgr is not null );
select * from emp where empno <>all(select distinct mgr from emp where mgr is not null );


/*
      exists(查询语句)  : 存在的意思
              如果查询语句,有结果,则返回true
              否则返回false    
              
      exists 和 in 通常可以替换使用
      建议使用exists 效率高, 数据量很大的时候              
*/
select * from emp where exists(select * from emp where empno = 1234567);
select * from emp where 1=2;

select * from emp where exists(select * from emp where empno=7369);
select * from emp where 1=1;

-- 查询有员工的部门信息
select * from dept d1 where exists(select * from emp e1 where e1.deptno = d1.deptno);


-- Select接子查询
	-- 获取员工的名字和部门的名字
  select ename,deptno from emp;
  
   select ename,(select dname from dept d1 where e1.deptno = d1.deptno) from emp e1;
-- from后面接子查询
	-- 查询emp表中经理信息
  --1. 查询经理的编号
  select distinct mgr from emp where mgr is not null;
  
  -- 2.结果
  select * from ( select distinct mgr from emp where mgr is not null) m1, emp e1 where m1.mgr = e1.empno;
  
  
-- where 接子查询
	-- 薪资高于10号部门平均工资的所有员工信息
  -- 1. 10号部门平均工资 2916
  select avg(sal) from emp where deptno = 10;
  -- 2.结果
  select * from emp where sal > (select avg(sal) from emp where deptno = 10);
-- having后面接子查询
	-- 有哪些部门的平均工资高于30号部门的平均工资
  -- 1.30号部门的平均工资 1566
  select avg(sal) from emp where deptno = 30;
  -- 2. 分组统计每个部门的平均工资
  select deptno,avg(sal) from emp group by deptno;
  --3. 结果:
 select deptno,avg(sal) from emp group by deptno having avg(sal) > (select avg(sal) from emp where deptno = 30);


-- 找到员工表中工资最高的前三名
/*
   rownum : 伪列 , 是Oracle提供一个代表每行记录的序号
            rownum 默认值从1 开始, 每查询出一条记录, 就加 1 
            
       
   SQL执行顺序:
     from..where..group by..having..select.. rownum  ..order by..
*/

select rownum,emp.* from emp;

select rownum,emp.* from emp order by sal;

select rownum,emp.* from emp where rownum >4;  --没有任何记录

select rownum,emp.* from emp where rownum >=1;  --14条记录

select rownum,emp.* from emp where rownum <4; 

--排序 sal
select emp.* from emp order by sal desc;

select rownum,t1.* from (select emp.* from emp order by sal desc) t1;

select rownum,t1.* from (select emp.* from emp order by sal desc) t1 where rownum < 4;



--找到员工表中薪水大于本部门平均薪水的员工
-- 1. 分组统计每个部门的平均工资
select deptno , avg(sal) avgsal from emp group by deptno;

-- 2. 结果:
select * 
from 
       emp e1,
       (select deptno , avg(sal) avgsal from emp group by deptno) t1  
where e1.deptno = t1.deptno and e1.sal > t1.avgsal;

--统计每年入职员工的个数
select hiredate from emp;

select to_char(hiredate,'yyyy') from emp;

select to_char(hiredate,'yyyy'),count(1) from emp group by to_char(hiredate,'yyyy');


select to_char(hiredate,'yyyy') aa,count(1) cc from emp group by to_char(hiredate,'yyyy');

-- 1. 将1987竖起来   case when end
select case aa when '1987' then cc end "1987"
from
(select to_char(hiredate,'yyyy') aa,count(1) cc from emp group by to_char(hiredate,'yyyy')) tt;

-- 2. 去除所有的null
select sum(case aa when '1987' then cc end) "1987"
from
(select to_char(hiredate,'yyyy') aa,count(1) cc from emp group by to_char(hiredate,'yyyy')) tt;


-- 3. 计算total
select sum(cc) "Total"
from 
(select to_char(hiredate,'yyyy') aa,count(1) cc from emp group by to_char(hiredate,'yyyy')) tt;

--4. Total 和 1987 拼接起来
select
      sum(cc) "Total",
      sum(case aa when '1987' then cc end) "1987"
from 
(select to_char(hiredate,'yyyy') aa,count(1) cc from emp group by to_char(hiredate,'yyyy')) tt;

--5. 所有的结果
select
      sum(cc) "Total",
      sum(case aa when '1980' then cc end) "1980",
      sum(case aa when '1981' then cc end) "1981",
      sum(case aa when '1982' then cc end) "1982",
      sum(case aa when '1987' then cc end) "1987"
from 
(select to_char(hiredate,'yyyy') aa,count(1) cc from emp group by to_char(hiredate,'yyyy')) tt;


/*
    rowid : 伪列, 代表的是每条记录在磁盘上所存放的真实的位置/物理地址
          运用在索引查询的时候
    
    rownum : 伪列, 代表的是行号
           主要是在Oracle分页操作的时候使用
*/
select rownum,emp.* from emp;

select rowid,emp.* from emp;  -- 0x1001

select rowid,emp.* from emp order by sal desc;

-- 查询表中第5-10条记录
-- 1.查询前10条记录
select rownum hanghao,e1.* from emp e1 where rownum <=10;

select rownum hanghao,e1.* from emp e1;

-- 2. 查询第5-10的记录
select *
from
       (select rownum hanghao,e1.* from emp e1 where rownum <=10) t1
where hanghao >=5;

-- rowid  -- 华为的笔试题  : 去除表中重复的记录
create table tt(
   tname varchar2(10)
);

insert into tt values('aa');
insert into tt values('bb');
insert into tt values('cc');

select rowid,tt.* from tt;
-- 查询的时候去除重复
select distinct tname from tt;


-- 删除表中重复的记录,保留rowid最小的记录
delete from tt t1 where rowid >(select min(rowid) from tt t2 where t1.tname = t2.tname );

select * from tt;


select * from emp;
select * from dept;


/*
       集合运算: 将多个查询合并,差集, 交集
        数据有可能来自于不同表
        
        员工表,  经理表
        
        姓名,    婚否
        
        查询本公司所有人的婚姻状况
        
        
*/
-- 并集运算:  将两个查询结果合并在一起
-- 工资大于1500,或者20号部门下的员工
select * from emp where sal > 1500 or deptno =20;

-- 工资大于1500
select * from emp where sal > 1500;

-- 20号部门下的员工
select * from emp where deptno = 20;


select * from emp where sal > 1500
union
select * from emp where deptno = 20;

select * from emp where sal > 1500
union all
select * from emp where deptno = 20;
 

-- 交集运算 : 两个集合相交的部分, 共有的部分
-- 工资大于1500,或者20号部门下的员工
select * from emp where sal > 1500
intersect
select * from emp where deptno = 20;

-- 差集运算 : 一个集合减去另外一个集合
-- 工资大于1500,不是20号部门下的员工
select * from emp where sal > 1500
minus
select * from emp where deptno = 20;


/*
       集合运算的注意事项:
         1. 列的出现顺序要一致
         2. 列的数量要一致
         3. 如果列数不够,null来凑或者使用相同类型的数来凑
*/
select ename,sal from emp where sal > 1500
union
select ename,sal from emp where deptno = 20;

-- 出现的顺序要一致 错误
select ename,sal from emp where sal > 1500
union
select sal,ename from emp where deptno = 20;

-- 
select ename,sal from emp where sal > 1500
union
select ename,0 from emp where deptno = 20;

select ename,sal from emp where sal > 1500
union
select ename,null from emp where deptno = 20;




-- 统计 薪资 大于 薪资最高的员工 所在部门 的平均工资 和 薪资最低的员工 所在部门 的平均工资 的平均工资 的员工信息。
--       sal  >                    10       100                          20         50        75    
--  sal > 75 的员工信息  

-- 有两个及以上直接下属的员工信息  
-- 1.统计mgr的出现的次数
select mgr from emp;

select mgr,count(1) from emp where mgr is not null group by mgr;

select mgr,count(1) from emp where mgr is not null group by mgr having count(1)>=2;

select mgr from emp where mgr is not null group by mgr having count(1)>=2;

select * from emp where empno in(select mgr from emp where mgr is not null group by mgr having count(1)>=2)

转载于:https://my.oschina.net/px828261/blog/1555142