Oracle学习笔记(一)

一、基本查询

-- dual :是一张虚表， 为了完善查询的语法结构,只有一行一列
   -- 别名:如果是数字开头或者纯数字必须加双引号
   -- 字符串使用单引号，别名使用双引号
   select 
   length('abc') "长度",
   length('abc') as "长度",
   length('abc') as 长度, 
   length('abc') as '123'   from dual;
   -- 消除重复的记录
   select distinct job from emp;
   -- 四则运算：+ - *  /      -- 连接符号：||
   select concat('a' , 'b') from dual;
   select concat(concat('a' , 'b'),'c') from dual;
   select 'a' || 'b'|| 'c' from dual;
   select '1' + 1 from dual;
   -- 查询员工的年薪: nvl(comm,0)
   select sal * 12 + nvl(comm,0),nvl(comm,0) from emp;

二、条件查询

-- 查询有奖金的员工
   select * from emp where comm > 0;

   select * from emp where comm is not null and comm != 0;
   -- 查询没有奖金的员工
   select * from emp where comm is null or comm = 0;
   -- not 取反
   select * from emp where not(comm is not null and comm != 0);

   -- 查询1981年入职的员工
   select * from emp where to_char(hiredate,'yyyy') = '1981';

   select * from emp where hiredate >= to_date('1981-01-01','yyyy-mm-dd')
                       and hiredate <= to_date('1981-12-31','yyyy-mm-dd');

   select * from emp where hiredate between  to_date('1981-01-01','yyyy-mm-dd')  
                                         and to_date('1981-12-31','yyyy-mm-dd') ; 
                       --- 函数：to_char  to_date
    -- to_char (p1,p2):将日期转换为字符串
                 -- p1:要转换的日期
                 -- p2:转换的格式
      select sysdate,to_char(sysdate,'yyyy-mm-dd hh24:mi:ss day') from dual;

    -- to_date(p1,p2):将字符串转换为日期
                 -- p1:要转换的字符串
                 -- p2:转换的格式        
     select '2018-07-06 11:11:11' , 
     to_date('2018-07-06 11:11:11' ,'yyyy-mm-dd hh24:mi:ss') from dual;
     -- upper: 转换为大写 lower ：转换为小写
     select * from emp where upper(ename) like upper('%M%');
     -- 排序
   -- 查询所有员工按照工资升序
   select * from emp order by sal asc;
   -- 查询所有员工按照工资降序
   select * from emp order by sal desc;
   -- 查询所有员工按照奖金升序排序（null值放到前面）
   select * from emp order by nvl(comm,0) asc;

   select * from emp order by comm asc nulls first; 
   -- 查询所有员工按照奖金降序排序（null值放到后面） 
   select * from emp order by comm desc nulls last; 

三、单行函数

 -- 单行函数
   select length(ename) from emp;
   --字符串函数
      -- concat
      -- length
      -- substr(str, p1 ,p2): str:截取的字符串 ，p1：开始的索引 ，p2：截取的长度
         -- 起始索引用0和1是一样的
      select substr('abcjavadef' , 4, 4 ) from dual;
      select substr('abcjavadef' , 1, 3 ) from dual;
      select substr('abcjavadef' , 0, 3 ) from dual;
      -- replace(str ,p1,p2) : 替换  str：要替换的字符串 p1：被替换的 p2：替换成的
      select replace('abcdefa' , 'a' ,'z') from dual;
      -- trim() 去除两侧的空白
      select trim('   abc    '),ltrim('   abc    '),rtrim('   abc    ') from dual;
      -- upper lower

   --日期函数
      -- 两个日期相减 == 天数
      select sysdate - hiredate from emp;
      -- 周数
      select (sysdate - hiredate) / 7 from emp;
      -- 月数：months_between
      select months_between(sysdate , hiredate) from emp;
      -- 修改月份： add_months
      select add_months(sysdate ,-12) from dual;
   --数值函数
      -- round () 四舍五入
      select round(2.666) from dual;
      -- trunc() 截取
      select trunc(2.666,1) from dual;
      -- mod() 模运算符（求余）
      select mod(3,10) from dual;
   --转换函数
      -- to_char to_date
      -- to_char to_number
      select 1 ,to_char(1),'1',to_number('1') from dual;
      select 1 + '1' from dual;
   --通用函数
      -- nvl

四、多行函数

 -- 多行函数(聚合函数，分组函数)(count, avg, max ,min ,sum)
   -- 分组后能显示的列：分组函数和在group by语句中出现的列
   select 
      count(1),deptno, avg(sal),max(sal) ,min(sal) ,sum(sal)
   from emp group by deptno;

   -- 查询大于4个人的部门
   -- 在where中不能加分组函数,where 必须放到group by前面
   -- having 在分组的基础上进一步的筛选
   select count(1),deptno from emp  group by deptno having count(1) > 4;