Oracle笔记 三、function 、select

Scott表下有这么几个常用的表，而且还带有数据。分别是emp、dept、salgrade；

1、查看表结构用desc

    desc emp;

 

2、空表dual，最常用的空表，如：

   select 2 * 4 from dual;

    select sysdatefrom dual;

 

3、双引号能保持格式

   如：select sysdate “toDay日期”from dual;

 

4、||字符串连接

    如：select 2*3 || 8from dual;

   select ename || salfrom scott.emp;

    select ename || ‘ORACLE’from scott.emp;

 

5、单引号，如：select 2 * 2 ||'abc''efg'from dual;

   用两个单引号表示一个单引号

 

6、去掉重复数据distinct

    selectdistinct deptnofrom scott.emp;

   去掉重复组合：selectdistinct deptno,jobfrom scott.emp;

 

7、where查询

    A、=查询，select *from scott.emp where sal = 1500;

 

    B、比较<、>、>=、<=

       select * from scott.emp where sal > 1500;

    C、andor

       select * from scott.emp where sal > 1500and sal <= 5000 or deptno = 10;

    D、in、notin

       select * from scott.emp where salin (1500, 800) and deptno notin (10, 20)

 

   E、like模糊escape转义

        Select * from scott.empwhere ename like ‘%in%’;

       Select * from scott.emp where enamelike ‘%in\%k%’;

        Select * from scott.empwhere ename like ‘%in#%k%’escape ‘#’;

       表示like中的#号是转义字符，相当于\

    F、isnull、isnotnull

   K、    orderby

        select sal, ename from scott.emp orderby sal;

       select sal, enamefrom scott.emp orderby salasc;

        select sal, ename from scott.emp orderby saldesc;

       select sal, enamefrom scott.emp where sal > 2000 orderby saldesc;

        select sal, deptno, enamefrom scott.emp orderby sal,deptnodesc;

   

8、function

   A、lower、upper、substr

        selectlower(‘abcABC’)from dual;

       selectupper(‘abcABC’)from dual;

        substr(target, startIndex, length)

       select substr(‘abcABC’, 1, 3)from dual;

 

   B、chr、ascii

        将数字安装ascii值转换成字符：selectchar(65)from dual;

       将字符转换成ascii值：select ascii(‘Z’)from dual;

   

   C、round、to_char

        精确小数

       select round(22.456)from dual;

        保留2位小数：select round(22.456, 2)from dual;

       精确到个位：select round(22.456, -1)from dual;

   

       货币

        设置货币格式，000前面不足就用0代替

       select to_char(sal,'$000,000.00')from scott.emp;

        999就不会替换不足的地方，只会安装格式输出

       select to_char(sal,'$999,999.99')from scott.emp;

        本地货币格式

       select to_char(sal,'L999,999.99')from scott.emp;

 

       日期

        日期格式

       格式控制描述

        YYYY、YYY、YY分别代表4位、3位、2位的数字年

       YEAR年的拼写

        MM 数字月

       MONTH月的全拼

        MON 月的缩写

       DD 数字日

        DAY星期的全拼

       DY 星期的缩写

        AM 表示上午或者下午

       HH24、HH12 12小时制或24小时制

        MI 分钟

       SS 秒钟

        SP 数字的拼写

       TH 数字的序数词

 

       “特殊字符” 假如特殊字符

        HH24:MI:SS AM 15:43:20 PM

       select to_char(sysdate,'YYYY-MM-DD HH:MI:SS')from dual;

        select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS')from dual;

 

     D、to_date、to_number、nvl

       to_date(target, current_format)

        select to_date('2011-4-2 17:55:55','YYYY-MM-DD HH:MI:SS')from dual;

       select to_number('$12,322.56','$999,999.99') + 10from dual;

        select to_number('$12,322.56','$00,000.00') + 10from dual;

       select to_number('22.56') + 10from dual;

        nvl可以将某个字段的空值转换成指定的值

       select ename, sal, nvl(comm, 1.00)from scott.emp;

 

9、groupfunction组函数：min、max、avg、sum、count

        selectmax(sal)from scott.emp;

       selectmin(sal)from scott.emp;

        selectavg(sal)from emp;

       select round(avg(sal), 2)from emp;

        select to_char(avg(sal),'L999,999.99')from emp;

       selectsum(sal)from emp;

        selectcount(comm)from emp;

       selectcount(distinct deptno)from emp;

 

10、groupby分组

    select deptno,avg(sal) from emp groupby deptno;

   select deptno, job,avg(sal) from emp groupby deptno, job;

    求部门最高工资的所在部门的员工信息：

   select deptno, ename, salfrom emp where sal in (selectmax(sal)from emp groupby deptno);

 

11、having对分组数据进行过滤

    求部门评价工资：

   select * from (selectavg(sal) sal, deptnofrom emp groupby deptno)where sal > 2000;

    selectavg(sal) sal, deptnofrom emp groupby deptnohavingavg(sal) > 2000;

 

12、子查询

   求部门分组后工资最高的员工信息

    select emp.ename, emp.sal, emp.deptnofrom emp, (selectmax(sal) max_sal, deptnofrom emp groupby deptno) twhere emp.sal = t.max_saland emp.deptno = t.deptno;

   求部门平均工资等级

    select s.grade, t.deptno, t.avg_salfrom scott.salgrade s, (select deptno,avg(sal) avg_salfrom emp groupby deptno) twhere t.avg_sal >s.losaland t.avg_sal < s.hisal;（between）

 

13、自连接

   select a.ename, b.ename mgr_namefrom emp a, emp bwhere a.empno = b.mgr;

 

14、连接查询

    select dname, enamefrom dept, emp where dept.deptno = emp.deptno;

   select dname, enamefrom dept join emp on dept.deptno =emp.deptno;

    select dname, enamefrom dept join emp using(deptno);

   select dname, enamefrom dept leftjoin empon dept.deptno = emp.deptno;

    select dname, enamefrom dept rightjoin empon dept.deptno = emp.deptno;

   select dname, enamefrom dept fulljoin empon dept.deptno = emp.deptno;

    select a.ename, b.ename mgr_namefrom emp a join emp b on a.mgr = b.empno;

   select a.ename, b.ename mgr_namefrom emp a leftjoin emp bon a.mgr = b.empno;

 

15、 Rownum

    select rounum, deptno, dnamefrom dept;

   select * from (

         select rownum r, dept.*from dept

   ) t where  t.r > 2;

 

16、树状结构查询

    selectlevel, empno, ename, mgrfrom emp

   connectbyprior mgr = empno;

 

17、排序函数

    --按部门分组，给出分组后的序号

   select row_number()over(partition by deptno orderby sal), emp.*from emp;

   

   --rank排序，空出相同部分

    select rank()over(partition by deptno orderby sal), emp.*from emp;

   select rank() over(orderby deptno), emp.*from emp;

    select rank()over(orderby sal), emp.*from emp;

   

    --dense_rank排序给出相同序号,不空留序号

   select rank() over(orderby sal), emp.*from emp;

    select dense_rank()over(orderby sal), emp.*from emp;

 

18、交集、并集、割集查询

   --并集：不带重复数据

    select *from emp

   union

    select *from emp2;

   

    --并集：带重复数据

   select * from emp

    unionall

   select * from emp2;       

   

   --割集,显示不同部分

    select *from emp

   minus

    select *from emp2;

 

19、查询系统表、视图

   select owner, object_name, object_type, status, dba_objects.*from dba_objectswhere object_type ='view'and status ='invalid';

 

   select * from user_objects where object_type like'PROCEDURE';

 

20、练习题

    --部门最高薪资员工信息

   select ename, sal, deptnofrom emp

    where salin (selectmax(sal)from emp groupby deptno);

   

    --部门最高薪资员工信息

   select ename, sal, emp.deptnofrom emp

    join (selectmax(sal) max_sal, deptno from empgroupby deptno) t

   on emp.deptno = t.deptnoand emp.sal =t.max_sal;

   

   --部门平均薪资等级

    select grade, losal, hisal, t.avg_salfrom salgrade

   join (selectavg(sal) avg_sal, deptnofrom emp groupby deptno) t

    on t.avg_salbetween losal and hisal;

   

    --经理人

   select ename, jobfrom emp where empno in (select mgrfrom emp);

   

   --不用分组函数，查询薪水最高值

    select *from (select sal, enamefrom emp orderby saldesc) where rownum = 1;

   selectdistinct a.salfrom emp a join emp b on a.sal > b.salwhere rownum = 1;

    select salfrom emp where sal notin (selectdistinct a.sal from emp ajoin emp b on a.sal < b.sal);

   

    --部门平均薪水最高的部门编号

   select deptno, t.avg_salfrom (selectavg(sal) avg_sal, deptnofrom emp groupby deptno) t

    where avg_sal = (

       selectmax(avg_sal) max_salfrom (selectavg(sal) avg_sal, deptnofrom emp groupby deptno)

    );

   

    select deptno, t.avg_salfrom (selectavg(sal) avg_sal, deptnofrom emp groupby deptno) t

   where avg_sal = (

        selectmax(avg(sal)) max_salfrom emp groupby deptno

   );

   

   --部门平均薪水最高的部门名称

    select dnamefrom dept where deptno = (

    select deptno from (selectavg(sal) avg_sal, deptnofrom emp groupby deptno) t

     where avg_sal = (

           selectmax(avg_sal) max_salfrom (selectavg(sal) avg_sal, deptnofrom emp groupby deptno)

     )

   );

   

   select dname from dept where deptno = (

        select deptno from (selectavg(sal) avg_sal, deptno from empgroupby deptno) t

       where avg_sal = (

              selectmax(avg(sal))from emp groupby deptno

       )

    );

   

    --平均薪水最低的部门的部门名称

   select dname from dept where deptno = (

      select deptnofrom (selectavg(sal) avg_sal, deptnofrom emp groupby deptno)

     where avg_sal = (

        selectmin(avg_sal) min_salfrom (

              selectavg(sal) avg_salfrom emp groupby deptno

        )

     )

    );

   

    select dnamefrom dept where deptno = (

       select deptno from (selectavg(sal) avg_sal, deptnofrom emp groupby deptno)

        where avg_sal = (   

         selectmin(avg(sal)) avg_salfrom emp groupby deptno

        )

   );

   

   --平均薪水等级最低的部门的部门名称

    select dnamefrom dept where deptno = (

   select deptno from (

         select grade, t.deptno from salgrade s join (

           selectavg(sal) avg_sal, deptnofrom emp groupby deptno

         ) t

        on t.avg_sal between s.losal and s.hisal

      )

     where grade = (

        selectmin(grade)from salgrade s join (

           selectavg(sal) avg_sal, deptnofrom emp groupby deptno

        ) t

       on t.avg_sal between s.losal and s.hisal

      )

   );

   

   --部门经理人中，平均薪水最低的部门名称

    select t.deptno, dnamefrom (

       select sal, deptnofrom emp where empno in (selectdistinct mgrfrom emp)

    ) tjoin dept

   on t.deptno = dept.deptno

    where sal = (

       selectmin(sal)from emp where empno in (selectdistinct mgrfrom emp)

    );

   

    --比普通员工的最高薪水还要高的经理人名称

   select * from (

        select empno, ename, salfrom emp where empno in (selectdistinct mgrfrom emp where mgr isnotnull)

   ) t

    where t.sal > (

       selectmax(sal) max_salfrom emp where empno notin (

         selectdistinct mgrfrom emp where mgr isnotnull

       )

    );