oracle学习笔记（1）

一、创建用户并授权（12c）

 select * from dba_users;  /*查询所有用户名*/
 create user C##scott identified by tiger;/*创建tiger用户*/
 grant dba to c##scott container = all;/*授权*/

二、单行函数

 1、大小写控制函数
 （1）upper  查询结果转大写   select upper(t.prodescription) from pts_re_prod t;
 （2）lower  查询结果转小写   select lower(t.prodescription) from pts_re_prod t;
 （3）initcap 查询结果首字母大写  select initcap(t.prodescription) from pts_re_prod t;
 
 2、字符控制函数
  （1）concat 将字段拼接起来
   select concat(prodCode,prodName) from pts_re_prod t;
   
  （2）sustr 字符串截取
    ①substr(a,b)  从a中b位开始取（包含b位），取右边所有
     
    ②substr(a,b,c) 从a中b位开始取（包含b位）取到C位（包含C位）
  
  （3）length\lengthb  求字符数、字节数
    select lengthb(t.prodname) from pts_re_prod t;
  
  （4）instr(a,b)  在a里面找b 找到返回位置
  
  （5）lpad\rpad  左\右填充
    lpad(abcd,10,'*') 左填充abcd到10个字节，填充*
    
  （6）trim  去掉前后指定的字符
    select trim('h' from 'hello worldh') from...;
    
  （7）replace 替换
    select replace('hello world','e','*')from...;
    
 3、数字函数
  （1）ROUND 四舍五入    round(45.658,2)  45.66  四舍五入保留两位小数，第2个参数可为负和0(0:整数，-1到十位),同时可操作日期;
  
  （2）TRUNC 截断   trunc(45.658,2) 45.65 截断同上
  
  （3）MOD 求余  MOD(1600,300)   100
  
 4、日期函数
  （1）数据类型
   mysql: date日期 datetime 日期时间
   oracle:date 日期时间
 
  （2）格式化日期
   select sysdate from dual;
   select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')from dual;
         fmyyyy-mm-dd  月份10以下去掉前边的0;
         
  （3）日期的运算
   日期±天数 日期-日期
   
  （4）日期函数
   MONTHS_BETWEEN 两个日期相差的月数
   ADD_MONTHS 在指定日期上加若干月数
   NEXT_DAY 指定日期的下一个日期
    next_day(sysdate,'星期五') 下一个星期五
   LAST_DAY 本月的最后一天
   ROUND  日期四舍五入
    ROUND(SYSDATE,'MONTH') 按月四舍五入
   TRANC  日期截断
 
 5、转换函数
  分隐式和显式转换
  显式转换： TO_CHAR,TO_NUMBER,TO_DATE
   TO_DATE(SYSDATE,'yyyy-mm-dd hh24:mi:ss"今天是"day');
  
  日期格式：
   YYYY  2018
   YEAR  Twenty Eighteen
   MM   04
   MONTH  4月
   DY   星期一
   DAY   星期一
  TO_CHAR格式
   9   数字
   0   零
   $   美元符号
   L   本地货币符号
   .   小数点
   ,   千位符
    TO_CHAR(MONEY,'L 9,999.99')
 
 6、通用函数：适用于任何函数，包括空值
  NVL(expr1,expr2) 如果expr1为空，则返回expr2,否则返回本身
  NVL2(expr1,expr2,expr3)  如果expr1为空，则返回expr3，否则expr2
  NULLIF(expr1,expr2)   如果两个表达式相等，则返回空，不等则返回expr1
  COALESCE(expr1,expr2,...,exprn)   从左到右找到第一个不为空的值，都为空，返回空
  
 7、条件表达式
  1、case表达式（oracle、mysql均可用）
    CASE expr1(要判断的字段) WHEN comparsion_expr1 THEN  return_expr1(返回结果)
           WHEN comparsion_expr1 THEN  return_expr1
           WHEN comparsion_expr1 THEN  return_expr1
           ELSE else_expr 
   END 
   
  2、DECODE(oracle独有的)
    slect DECODE(job,'teacher',sal+100,
       'manager',sal+200,
         sal+50)
    from 表;

二、分组函数（多行函数）    
  1、AVG、COUNT、SUM、MAX、MIN  
  多行函数会自动滤空，可嵌套滤空函数
   count(nvl(comm,0))
   
  2、DISTINCT 去重
   select count(distinct deptNo)from ...;
   
  3、GROUP BY 分组 
    在select列表中所有未包含在多行函数的列都应包含在group by子句中
     select a,b,c,avg(d) from user_table group by a,b,c
   
    having 过滤（先分组在过滤）
    select deptNo,avg(sal) from emp group deptNo having avg(sal)>1000;
    where和having的区别：where 后面不能使用多行行数
    where和having同时可以用的时候，用where 效率高点。原因：where先过滤.
   
    group by的增强
   GROUP BY ROLLUP(a，b)= GROUP BY a，b   +  GROUP BY a +没有分组
     select deptno,job,sum(sal) from emp group by deptno,job
     +
     select deptno,sum(sal) from emp group by deptno
     +
     select sum(sal) from emp
     ==
     select deptno,job,sum(sal) from emp group by rollup(deptno,job);

三、多表查询
  1、等值连接
   select e.empno,e.ename,e.sal,d.dname
   from emp e,dept d
   where e.deptno=d.deptno;

  2、不等值连接
   select e.empno,e.ename,e.sal,s.grade
   from emp e,salgrade s
   where e.sal between s.losal and s.hisal;

  3、自连接（不适用大表）
  
  4、层次查询（单表查询）
   select level,empno,ename,mgr
   from emp
   connect by prior empno=mgr
   start with mgr is null
  
四、子查询
  注意事项：
   1、合理的书写风格
   
   2、用“（）”括起来
   
   3、可在select 、from、having、where后使用子查询
     select后的子查询只能是单行子查询（返回一条记录）
       select empno,ename,sal,(select job from emp where empno=7839) from emp;  此句子查询代表第4列
       
   4、不可以在group by后使用子查询
   
   5、强调from后的子查询
    
   6、主查询和子查询可以不是同一张表，只要子查询返回的结果主查询可以使用即可
     子查询和多表查询都可以使用时，多表查询好于子查询。
   7、一般不在子查询中排序。但Top-n分析问题（排序取前几名），必须对子查询排序
   
   8、一般子查询先于主查询执行。但相关查询除外。
   
   9、单行查询只能使用单行操作符（>,<,=等），多行查询只能使用多行操作符（in 列表中的一个、any 和子查询返回的任意一个数值比较、all 和子查询返回的所有数值比较）
   
   10、子查询中的空值问题
    当行查询空值，即无记录
    多行查询，如果集合里有空值不能使用not in ,可是使用in

五、集合运算
 ①参与运算的各个集合必须列数相同 且类型一致，不一致不可补（如：to_char(null)...)）
 ②采用第一个集合作为最后的表头
 ③order by永远在最后
 ④可以用括号改变执行顺序
 ⑤尽量不要使用集合运算（SQL优化）
  1、并集
   UNION UNION ALL
   无交集效率差不多，有交集尽量使用union all
  2、交集
   INTERSECT
  3、差集
   MINUS