Oracle读书笔记--基础（二）

单行函数

常用单行函数：

--================单行函数：
/*
dual : 它是一张伪表，不存在的，只是为了补全语法
*/
--=====================================字符串
-- 大小写控制
-- lower(v1) :把字符串变成小写
-- upper(v1) ：把字符串变成大写
-- initcap(v1) :把每个单词首字母大写
-- trim('H' from 'HHelloHH'):去掉首尾字符---》ello
-- ltrim('HelloHH','H')：去掉首H字符-->elloHH
-- rtrim('HelloHH','H')：去掉尾H字符-->Hello
select upper('abc') from dual;
select lower('ABC') from dual;
 
--计算字符串的长度
select length('123456') from dual;
-- 补充
select lpad(1000,7,'*') ,rpad(1000,7,'*') from dual;  ==>***1000  1000***
--查找字符串位置 
 instr('abc','b')  ==》2 
--截取字符串： substr(v1,p1,p2)  v1：源字符串，p1:开始索引【0和1都是从1开始】,p2:截取长度
select substr('hello',0,2) from dual; -- he
select substr('hello',1,2) from dual; -- he
select substr('hello',2,2) from dual; -- el
 
--替换字符串： replace(v1,p1,p2)  v1：源字符串 , p1:要替换的字符串 ，p2:替换后的字符串
select replace('hello','l','x') from dual;

--================================数字
--四舍五入：round(v1,p1) ：v1:源数字 ，p1:省略，返回的是整数，四舍五入；不省略，返回的是p1指定的小数位
select round(2002.56) from dual; -- 2003
select round(2002.56,1) from dual; -- 2002.6
--截断: trunc(v1,p1) ：v1:源数字 ，p1:省略，返回的是整数，不会进行四舍五入；不省略，返回的是p1指定的小数位
select trunc(2002.56) from dual; -- 2002
select trunc(2002.56,1) from dual; -- 2002.5

--====================================日期函数：
--系统时间 ： sysdate
select sysdate from dual;
 
--计算员工入职的天数： 时间减去时间得到的是天数
select empno,ename,hiredate, trunc(sysdate-hiredate) as 入职天数
from emp;

--计算入职周数
select empno,ename,hiredate, trunc(sysdate-hiredate) as 入职天数,trunc((sysdate-hiredate)/7) as 入职周数
from emp;

--月数： months_between(v1,v2) 得到两个时间的 月数
select empno,ename,hiredate, trunc(sysdate-hiredate) as 入职天数,
       trunc((sysdate-hiredate)/7) as 入职周数,
       trunc(months_between(sysdate,hiredate)) as 入职月数
from emp;

--================================转换函数：

--空值转换函数：nvl ，nvl2 ， nullif
select ename ， com ， nvl（com，0） from emp；  ==》将com为null的值变为0。

--to_char: 可以数字转成字符串；可以日期转成字符串
#L显示本地货币符号，$:直接显示 ，9表示数值 0：同9，强制占位。
select to_char(123.5,'L9,999,999.00') from dual;    ==》123.50
select 123,to_char(123) from dual;
格式：#年：year , YYYY,yy, 月：month ,non , nn 日：dd 星期几 ：DAY（Monday） ，DY（mon） ,D（1）
select sysdate,to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
 
--to_date：把字符串转成日期
select '2018-08-11 12:15:23' ,to_date('2018-08-11 12:15:23','yyyy-mm-dd hh24:mi:ss') from dual;
 
--to_number: 把字符串转成数字
select '123',to_number('123') from dual;

--================================条件表达式
/*
CASE 表达式：SQL99 的语法，类似 Basic，比较繁琐【推荐】
DECODE 函数：Oracle 自己的语法，类似 Java，比较简洁
*/
 
--案例： 查询员工的部门编号，当前等于10显示java部门；当等于20显示UI部门;其他情况就显示 “前台”
select empno,ename,deptno,
       case deptno
           when 10 then 'java部门'
           when 20 then 'UI部门'
           else '前台'
       end as "部门名称",
      
       decode(deptno,10,'java部门',20,'UI部门','前台') as 部门名称2
      
from emp;

多行函数

--=======================多行函数
/*
处理的是多行数据，返回一条
 
count、avg、max、min、sum
*/
--统计数据：几条
select count(*) from emp;
 
--计算公司的平均工资
select avg(sal) from emp;
 
--计算公司的每个部门的平均工资
select deptno,round(avg(sal) ,2)
from emp
group by deptno;
 
 
--计算公司的每个部门的平均工资 ,要求：平均工资大于2000才显示
select deptno,round(avg(sal) ,2)
from emp
group by deptno
having round(avg(sal) ,2)>2000;
 
 
--having 和 where 区别：   where 后面不能加上 组函数
--多行子查询：
-- 用到关系运算符：any ， all ，in  
 
-- 判断数值在子查询是否存在：exists

子查询

Oracle里面，子查询的效率较高。

/*
子查询：将一个查询的结果作为另外一个查询的 条件/表
*/

--谁的工资比 SCOTT 高？
--先查scott的工资
select * from emp where ename = 'SCOTT';
--将上面的查询作为下面查询的条件
select * from emp where sal > (select sal from emp where ename = 'SCOTT');


--查询出比雇员 7654 的工资高，同时从事和 7788 的工作一样的员工
--1）查询7654的工资，7788的工作
select sal from emp where empno=7654;
select job from emp where empno=7788;
--2) 将上述查询作为条件
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) dsal
from emp
group by deptno;
--2）把上面查询当成一张表
select e.empno,e.ename,e.deptno,d.dname,t.dsal 
from emp e,
     (select deptno ,min(sal) dsal
      from emp
      group by deptno
     )t,
     dept d
where e.sal = t.dsal
      and e.deptno = t.deptno
      and e.deptno = d.deptno;


--细节：
--1、单行子查询与多行子查询的空值问题
--1）单行子查询 : 子查询中有空值，数据不会显示，但是不会报错
--案例：查询比Mike工资高的员工
select * from emp where sal > (select sal from emp where ename = 'Mike');
--2）多行子查询: 使用not in 的时候要去空
--案例：查询不是老板的员工
select *
from emp
where empno not in(select distinct mgr from emp where mgr is not null);  

-- in   相当于; empno = 7369 or empno = 7788 or empno = null
-- not in 相当于在上面的基础上取反; empno = 7369 and empno = 7788 and empno = null  


--非法使用子查询：子查询中返回的结果必须跟条件相对应
select * 
from emp 
where sal > (select sal from emp);

---=========================exists的用法：
/*
exists:存在，返回的布尔值  : true | false
   
        用法:
          exists(子查询)： 
                  1）当子查询有返回结果的时候，返回true
                  2）当子查询没有返回结果的时候，返回false
       
*/
select * from emp where 1=1;
select * from emp where exists(select * from dept where deptno=10);



select * from emp where 1=2;
select * from emp where exists(select * from dept where deptno=100);


--查询有员工的部门
select *
from dept
where deptno in (select distinct deptno from emp);

--换成exists
select *
from dept d
where exists(select * from emp e where d.deptno=e.deptno);

--in 与 exists的区别：in 会发生全表扫描；exists不会，找到就返回，但数据量很大【百万级】的时候建议用exists

-- Oracle特有的外连接法：+ 的使用。
/*
oracle中特有的外链接:使用+号
		口决:+号在那边,对面的那一边就是基准表
*/
--查询员工与他领导的信息
select*
from emp e, emp m
where e.mgr=m.empno (+);
(使用内连接后再加+号后变为外连接)

Oracle伪列

--1）介绍oracle中的伪列
/*
rownum: 行号，每查询到一条数据就分配一个行号
rowid: 行id，它指向的是物理存储的id
*/
select rownum,e.*,rowid from emp e;

--2）使用rowunm
select rownum,e.* 
from emp e
order by e.sal desc;

--3）先排序，把排序好的数据当成一张表
select rownum,t.empno, t.ename,t.sal
from (
      select rownum,e.* 
      from emp e
      order by e.sal desc
     ) t
where rownum < 4;

Oracle分页

--=======================Oracle中的分页
/*
mysql:  select * from tab1 limit 0[startIndex],2[pageSize];

Oracle： oracle中不是靠limit去分页，靠的是" rownum "
rownum ；每查询一条就分配一个行号
          使用小于号，说明数据已经查询出来
          使用大于号，数据还没有查询出来，不能使用大于号
          
          for(int i=1;i<10;i++){
                  System.out.println(i);
          }
          1
          2
          3   当是3的时候，1与2可以知道，但是后面的值它不知道
          4
          .
          .
          9
*/
--案例： 查询emp表数据，pageSize = 3；
--1）求第一页
select * 
from emp
where rownum <=3;

--2）求第二页。。。。。:rownum不能使用大于号 ： 先查询小于等于6，再把这个查询当成一张表
select * 
from (
      select rownum as rnum,e.* 
      from emp e
      where rownum <=6
     ) t
where t.rnum >3;
--案例:对工资降序排序，取出第二页，pageSize=3
--1）先排序，把排序的结果当成一张表
select *
from (
      select * 
      from emp
      order by sal desc
     ) t
where rownum <=6 ;
--2)把上面的查询当成一张表
select * 
from (
      select rownum rn,t.*
      from (
            select * 
            from emp
            order by sal desc
           ) t
      where rownum <=6 
     )t2
where t2.rn>3;

Oracle的集合运算

--=====================集合运算
/*
两个集合返回的字段数量、字段数据类型要一致
*/
--并集：把两个集合加起来
--案例： 查询工资大于1500 或者 他是20号部门的员工
select * from emp where sal> 1500 or deptno=20;--or的关键字会引起全表扫描，他效率很低

--集合运算：并集
select * from emp where sal> 1500
union all  -- 把两个集合联合起来，有可能有重复的数据 效率高
select * from emp where deptno=20;

select * from emp where sal> 1500
union -- 把两个集合联合起来，去除重复的数据 效率较低
select * from emp where deptno=20;

--交集
--案例： 查询工资大于1500 并且 他是20号部门的员工
select * from emp where sal> 1500
intersect
select * from emp where deptno=20;

--差集
--1981 年入职的普通员工（不包括总裁和经理）（差集）
--1)A集合：1981年入职的员工
select * from emp where to_char(hiredate,'yyyy')='1981';

--2)B集合：总裁与经理查询出来
select * from emp where job = 'PRESIDENT' or job='MANAGER';

--3）A差B  注意顺序
select * from emp where to_char(hiredate,'yyyy')='1981'
minus
select * from emp where job = 'PRESIDENT' or job='MANAGER';

转载请注明出处！！！本文为个人所学的一些笔记以及小结。

未完待续