Oracle学习笔记第一天

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

 该练习都是oracle自带的表数据,emp,dept等。

一、优化原则

    1.查询语句尽量指定列名，避免使用*;

    2. WHERE 条件从右到左

    3.尽量使用WHERE，少使用HAVING

    4.多表查询优于子查询。

    5.尽量不要使用集合运算

二、IN可以使用NULL，NOT IN 不可以使用NULL

    在ORACLE中，IN是或逻辑(=1 OR =2 OR =NULL)，NOT IN 是与逻辑(=1 AND =2 AND =NULL)。而且ORACLE中任何值AND NULL的值都是为NULL 或者FALSE。所以，IN可以简写成(=1 OR =2),而NOT IN 返回的都是FALSE 或者NULL。

 

三、查询字符串带有'_'值的模糊查询

    例如查询表emp 中ename含有'_'的值。如果写：select * from emp where ename like '%_%';  这样查询到的结果与想象的不同，这个查询出来的是任意名字，因为'%'代表一个或多个字符串，'_'代表了一个字符串；那么如何做呢？

    应该先转义'_',这样写就可以了：select * from emp where ename like '%\_%';

四、ORDER BY 后的条件可以使用SELECT-LIST的列名,序号,别名，而且必须是SELECT-LIST中的。

    select empno,ename,sal,sal*12 年薪 from emp order by sal*12 desc;

    select empno,ename,sal,sal*12 年薪 from emp order by 4 desc;

    select empno,ename,sal,sal*12 年薪 from emp order by 年薪 desc;

    注意：如果ORDER BY 后面不止一列，那么desc只作用于最接近的一列:

        select * from emp  order by deptno ,sal desc

    如果需要都起作用需要都添加desc:

     select * from emp  order by deptno desc,sal desc

    在ORACLE 中，NULL值最大。

五、to_char 将字符串转换为你想要的格式

    to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') sysdate获取的是当前时间。

    SQL> --2014-12-26 15:19:12 今天是星期五
    SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss"今天是"day') from dual;

    SQL> --查询员工薪水：两位小数，千位符，货币代码
    SQL> select to_char(sal,'L9,999.99') from emp;

六、条件表达式

    1.三元运算符nvl2(a,b,c) 

    如果a=null时，返回c；a!=null 时，返回b.

    如果某个数+null时，其值会为null这不是我们想要的结果，可以参考如下例子。
    SQL> --nvl2(a,b,c) 当a=null时候，返回c；否则返回b
    SQL> select sal*12+nvl2(comm,comm,0) from emp;

    2.nullif 返回null或者前一个字符串
    SQL> --nullif(a,b) 当a=b时候，返回null，否则返回a
    SQL> select nullif('abc','abc') 值 from dual;

    3.coalesce

    SQL> --coalesce  从左到右 找到第一个不为null的值
    SQL> select comm,sal,coalesce(comm,sal) "第一个不为null的值" from emp;

    4.case语句与decode语句

    case语句控制不同条件的显示

     select ename,job,sal 涨前,
          case job when 'PRESIDENT' then sal+1000
                   when 'MANAGER' then sal+800
                    else sal+400
           end 涨后
        from emp;

     使用decode函数表示为

    select ename,job,sal 涨前,decode(

        job,'president',sal+1000,

             'manage'，sal+800,

                          sal+400

    )    涨后 from emp

    使用decode函数的一个例子：根据80号部门员工的工资，显示税率

 

  七、分组函数

  定义：分组函数是作用于一组数据的函数，并对一组数据返回一个值。常见的有SUM,COUNT,AVG,MAX,MIN

    注意：where 之后不能跟组函数的条件。

    1.nvl函数：nvl(a,b) 如果a不为null返回a，否则返回b；

    用法：select count(*),count(nvl(comm,0)) from emp; 这样就可以统计包括comm为null的数量了。

    2.rollup函数 通常用做于报表查询，返回的数据格式比较好看。如下图：

      

 

八、多表查询

    笛卡尔积：笛卡尔积是表的行数相乘的积：以下条件会产生笛卡尔积：

    无条件，条件无效，所有行相互连接。

    在oracle中多表查询方式有等值连接，不等值连接，外连接，自连接。

    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.外连接

        --按部门统计员工人数： 部门号  部门名称 人数

     select d.deptno 部门号,d.dname 部门名称,count(e.empno) 人数 from emp e,dept d where         e.deptno=d.deptno group by d.deptno,d.dname;  这条语句是不对的，因为有一行部门的值并没有查询出来。

        外链接：对于某些不成立的记录，任然希望包含在最后的结果中

        左外连接：当where e.deptno=d.deptno不成立的时候，等号左边的表任然被包含

        写法：当希望是包含d表不成立的记录时：where e.deptno(+)=d.deptno

        写法：当希望是包含e表不成立的记录时：where e.deptno=d.deptno(+)

    4. 自连接：通过表的别名，将同一张表视为多张表

        --在一张表中根据关系自连查询。（与层次相比直观）

        select a.ename,b.ename from emp a,emp b where a.mgr=b.empno;

        

        注：自连接不适合查询大表。。因为大表的乘积特别大。

        层次查询：在emp表中，员工的mgr是他顶级上司的员工号 （不会产生笛卡尔积）

        select level(这是一个伪列，如果不指定则不出现),empno,ename,mgr

       connect by prior empno =mgr start with mgr is null order by 1;

        START WITH：指定层级的跟节点行。

        CONNECT BY：指定层级的父行于子行的关系。

             

    

 

  

转载于:https://my.oschina.net/u/3052654/blog/826013