Oracle基础

oracle:

    

    oracle 中 char 、varchar 、 varchar2 的区别？     

         1.CHAR的长度是固定的，而VARCHAR2的长度是可以变化的， 比如，存储字符串“abc"，

           对于CHAR (20)，表示你存储的字符将占20个字节(包括17个空字符)，而同样的VARCHAR2 (20)则只占用3个字节的长度，20只是最大值，

            当你存储的字符小于20时，按实际长度存储。
         2.CHAR的效率比VARCHAR2的效率稍高。

       3.varchar2把所有字符都占两字节处理(一般情况下)，varchar只对汉字和全角等字符占两字节，数字，英文字符等都是一个字节；

       4.VARCHAR2把空串等同于null处理，而varchar仍按照空串处理；

       5.大部分情况下建议使用varchar2类型，可以保证更好的兼容性。

【mysql中有varchar但没有varchar2 ：mysql中 varchar(20)表示 20个字符；而orcale varchar2(20)代表20个字节】

 

    函数：

     !!1.substr(ename,1,1) 

            l 查询姓名以M 开头所有的员工

            select * from emp where substr(ename, 1,1)='M';

 

mysql:

substring(’abcdefg’,2,3);

结果：bcd

     2.length

           length(ename)

    3.    trim会去首尾空格，不会取出中间的空格 

           trim(ename)

     !!4.to_date 

          l 查询1981-02-20入职的员工（第二种方法，将字符串转换成date类型）

           select* from emp where hiredate=to_date('1981-02-20 00:00:00', 'YYYY-MM-DDHH24:MI:SS');

    !!5.to_char

           查询1981-02-20以后入职的员工，将入职日期格式化成yyyy-mm-ddhh:mm:ss

           selectempno, ename, to_char(hiredate, 'yyyy-mm-dd hh24:mi:ss') from emp wherehiredate>to_date('1981-02-20 00:00:00', 'YYYY-MM-DD HH24:MI:SS');

           查询员工薪水加入千分位和保留两位小数

           selectempno, ename, to_char(sal, '$999,999.00') from emp;

 

mysql:

select date_format(now(),’%Y-%m-%d’);

select time_format(now(),’%H-%i-%S’);

    !!6.to_number 

           将字符串转换成数值

           select* from emp where sal>to_number('1,500', '999,999');

    !!7.nvl 

           用Oracle提供的nvl，该函数的语法格式为：nvl(表达式1，表达式2)，表达式1：指的是字段名称；表达式2：指的是将该字段的null转换成的值

           采用nvl函数，取得员工的全部薪水，薪水+津贴

           selectempno, ename, sal, comm, sal+nvl(comm,0) from emp;

    8.case … when … then …end 

           如果job为MANAGERG薪水上涨10%,如果job为SALESMAN工资上涨50%

           selectempno, ename, job, sal, (case job when 'MANAGER' then sal*1.1 when 'SALESMAN'then sal*1.5 end) as newsal from emp;

    9.decode 

           同case …when …then … end

        l 如果job为MANAGERG薪水上涨10%,如果job为SALESMAN工资上涨50%

           selectempno, ename, job, sal, decode(job, 'MANAGER', SAL*1.1, 'SALESMAN', sal*1.5) asnewsal from emp;

  10.round 四舍五入

           selectround(1234567.4567, 2) from dual;

 

 聚合函数：

      count sum min max avg

      count(*) 取所有记录 忽略null  (依旧算一条记录)

           当一行数据所有字段全部为空的场合，count方法是否有效 ？

            count方法有效！（依旧取）

       count(某字段) 不取该字段为null的记录 （不算一条记录）

       count(distinct 字段)

 

 分组函数

      group by

          如果使用了order by，orderby必须放到group by后面group by .... order by...

          采用group by，非聚合函数使用的字段必须参与分组eg:select job , sum(sal) from ..group by job ;(job必须参与分组)

      having 过滤分组的

 

 select ...

 from ...

 where ...

 group by ...

 having ...

 order by ...

 

 !!!rownum:

  Oracle提供了rownum，rownum是一个隐含的字段，rownum是一个行号，从1开始

  注意：

  1.取得大与第5条的所有数据

  select * from emp where rownum >5;

  !!!以上语句，oracle不支持，oracle只支持rownum小于或小于等于的运算, 【不支持】大于或大于等于的运算。

  2.取得薪水最好的前5名

    select rownum, empno, ename, sal fromemp where rownum<=5 order by sal desc;

  !!!以上语句，oracle不支持，因为采用orderby不会改变rownum，rownum的值在数据插入到表中时已经形成，正确使用的方式，将排序好的数据作为一张表来使用，这样这个表的rownum是新形成的，所以可以保证它的顺序是正确的，如下

select empno, ename, sal from (select empno, ename, sal from emp order by sal desc) where rownum <=5

3.采用rownum进行分页:

    因为rownum存在问题，所以需要采用三层的select嵌套完成分页，嵌套的目的将rownum转换成我们自己的字段

select empno, ename, sal from (  select rownum r, empno, ename, sal  from   ( select empno, ename, sal from emp )    where rownum <=4 )where r>2

 通用的分页方法，使用的时候可以直接拷贝。只需要修改红色字体的部分就可以了

select * from (  select rownum r, t.*  from   (     任意的SQL语句 )  t  where rownum <=结尾的行号 )where r>开始的行号

 

增删改：

    insert :

        表内容的复制

             create table emp_bak asselect * from emp;