首页 > 代码库 > oracle之sql查询二

oracle之sql查询二

此文章为http://huangsir007.blog.51cto.com/6159353/1854818该片的后续

关于数据库语言查询:

SQL> show parameter nls_language;


NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

nls_language                         string      AMERICAN       支持的是AMERICAN

SQL> select * from nls_session_parameters where parameter=‘NLS_LANGUAGE‘;


PARAMETER

--------------------------------------------------------------------------------

VALUE

--------------------------------------------------------------------------------

NLS_LANGUAGE

AMERICAN


还有一种时间语言:

SQL> show parameter nls_date_language;


NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

nls_date_language                    string

SQL> select * from nls_session_parameters where parameter=‘NLS_DATE_LANGUAGE‘;


PARAMETER

--------------------------------------------------------------------------------

VALUE

--------------------------------------------------------------------------------

NLS_DATE_LANGUAGE

AMERICAN             时间语言也是AMERICAN,所以这种1-1月-1982不允许的,月份必须是英文

否则会乱码


关于时间的格式查询:

SQL> select * from nls_session_parameters where parameter=‘NLS_DATE_FORMAT‘;       这是默认的时间格式


PARAMETER

--------------------------------------------------------------------------------

VALUE

--------------------------------------------------------------------------------

NLS_DATE_FORMAT

DD-MON-RR                        这是默认的时间格式DD-MON-RR

SQL> show parameter nls_date_format;        这种方式查不出来就用上面的方式


NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

nls_date_format                      string


查看当前时间格式

SQL> select * from student;


        XH XM                             SEX BIRTHDAY     CLASSID

---------- ------------------------------ --- --------- ----------

         1 ??????                         F   09-JUL-16          1

         2 xiaobai                        F   06-SEP-16          2

         3 xiaohua                        F                      2

然后临时修改当前会话的时间格式(修改的是当前session会话的,关掉后将失去更改,修改之后才能yyyy-mm-dd格式)

SQL> alter session set nls_date_format=‘yyyy-mm-dd‘;


Session altered.

修改后再次查询,如下

SQL> select * from student;


        XH XM                             SEX BIRTHDAY      CLASSID

---------- ------------------------------ --- ---------- ----------

         1 ??????                         F   2016-07-09          1

         2 xiaobai                        F   2016-09-06          2

         3 xiaohua                        F                       2

这样时间格式的错误之后遇到就好解决了


根据hiredate:入职时间; 受雇日期;做判断来查询

SQL> select ename,HIREDATE from emp where HIREDATE>‘1982-1-1‘;  以此时间入职之后的雇员


ENAME      HIREDATE

---------- ----------

SCOTT      1987-04-19

ADAMS      1987-05-23

MILLER     1982-01-23


根据薪水的某个区间做查询


SQL> select ename,sal from emp where sal>2000 and sal<5000;


ENAME             SAL

---------- ----------

JONES            2975

BLAKE            2850

CLARK            2450

SCOTT            3000

FORD             3000


关键字like,做匹配查询

查询手写字母为S的雇员

SQL> select ename from emp where ename like ‘S%‘;


ENAME

----------

SMITH

SCOTT

选出第三个字母为大写O的雇员(下划线为匹配任一个,%匹配任意个)

SQL> select ename from emp where ename like ‘__O%‘;


ENAME

----------

SCOTT


关键字in

查询雇员号在某个区间


SQL> select empno,ename from emp where empno in (7900,7934);


     EMPNO ENAME

---------- ----------

      7900 JAMES

      7934 MILLER



以顺序查询,关键字order by(默认为升序asc,降序为desc)

SQL> select ename,sal from emp order by sal;


ENAME             SAL

---------- ----------

SMITH             800

JAMES             950

ADAMS            1100

WARD             1250

MARTIN           1250

MILLER           1300

TURNER           1500

ALLEN            1600

CLARK            2450

BLAKE            2850

JONES            2975

SCOTT            3000

FORD             3000

KING             5000

按照雇员年薪进行排序

SQL> select ename,sal*12 nianxin from emp order by nianxin(asc|desc);  别名的作用


ENAME         NIANXIN

---------- ----------

SMITH            9600

JAMES           11400

ADAMS           13200

WARD            15000

MARTIN          15000

MILLER          15600

TURNER          18000

ALLEN           19200

CLARK           29400

BLAKE           34200

JONES           35700

SCOTT           36000

FORD            36000

KING            60000

对同一个部门sal进行降序,对部门号进行升序

SQL> select ename,sal,deptno from emp order by sal desc,deptno;


ENAME             SAL     DEPTNO

---------- ---------- ----------

KING             5000         10

FORD             3000         20

SCOTT            3000         20

JONES            2975         20

BLAKE            2850         30

CLARK            2450         10

ALLEN            1600         30

TURNER           1500         30

MILLER           1300         10

WARD             1250         30

MARTIN           1250         30

ADAMS            1100         20

JAMES             950         30

SMITH             800         20


查询最大值,最小值,平均值,关键字为max,min,avg

SQL> select max(sal),min(sal),avg(sal) from emp;


  MAX(SAL)   MIN(SAL)   AVG(SAL)

---------- ---------- ----------

      5000        800 2073.21429

查找出sal最大值的员工,先查询出sal的最大值,然后使sal=sal的最大值做判断查询

SQL> select ename,sal from emp where sal=(select max(sal) from emp);


ENAME             SAL

---------- ----------

KING             5000

查询出高出平均工资得雇员

1、先找出平均工资是多少?

SQL> select avg(sal) from emp;


  AVG(SAL)

----------

2073.21429

2、然后查询sal与平均工资作比较

SQL> select ename,sal from emp where sal>(select avg(sal) from emp);


ENAME             SAL

---------- ----------

JONES            2975

BLAKE            2850

CLARK            2450

SCOTT            3000

KING             5000

FORD             3000


分组查询每个部门的最大sal和最小sal,关键字group by

SQL> select max(sal),min(sal),deptno from emp group by deptno;


  MAX(SAL)   MIN(SAL)     DEPTNO

---------- ---------- ----------

      2850        950         30

      3000        800         20

      5000       1300         10


找出最大sal大于3000的部门号,关键字having 某字段 做判断

SQL> select max(sal),min(sal),deptno from emp group by deptno having max(sal)>3000;


  MAX(SAL)   MIN(SAL)     DEPTNO

---------- ---------- ----------

      5000       1300         10


1、分组函数只能出现在选择列,having、order by字句中

2、如果在select语句中同时包含group by,having,order by那么他们的顺序是group by,having,order by

3、在选择列中如果有列、表达式和分组函数,那么这些列和表达式必须有一个出现在group by字句中,否则就会出错

SQL> select deptno,avg(sal),max(sal) from emp group by deptno having avg(sal)>2000 order by avg(sal);

DEPTNO   AVG(SAL)   MAX(SAL)

------ ---------- ----------

    20       2175       3000

    10 2916.66666       5000


多表结合查询

按照相同字段deptno查询 如不按照这也条件就是14*4=56条记录

SQL> select e.ename,d.dname,d.deptno from emp e,dept d where e.deptno=d.deptno;


ENAME      DNAME              DEPTNO

---------- -------------- ----------

CLARK      ACCOUNTING             10

KING       ACCOUNTING             10

MILLER     ACCOUNTING             10

JONES      RESEARCH               20

FORD       RESEARCH               20

ADAMS      RESEARCH               20

SMITH      RESEARCH               20

SCOTT      RESEARCH               20

WARD       SALES                  30

TURNER     SALES                  30

ALLEN      SALES                  30

JAMES      SALES                  30

BLAKE      SALES                  30

MARTIN     SALES                  30


查询出部门号为10的员工名和薪水,以及所在部门(dname)

SQL> select e.ename,e.ename,d.dname,d.deptno from emp e,dept d where e.deptno=d.deptno=10;

select e.ename,e.ename,d.dname,d.deptno from emp e,dept d where e.deptno=d.deptno=10

                                                                                 *

ERROR at line 1:

ORA-00933: SQL command not properly ended

这样的组合e.deptno=d.deptno=10是不被允许的,正确如下:


SQL> select e.ename,e.sal,d.dname,d.deptno from emp e,dept d where e.deptno=d.deptno and d.deptno=10;


ENAME             SAL DNAME              DEPTNO

---------- ---------- -------------- ----------

CLARK            2450 ACCOUNTING             10

KING             5000 ACCOUNTING             10

MILLER           1300 ACCOUNTING             10


表salgrade是薪水级别,如下:

SQL> select * from salgrade;


     GRADE      LOSAL      HISAL

---------- ---------- ----------

         1        700       1200      区间700-1200之间的薪水等级

         2       1201       1400

         3       1401       2000

         4       2001       3000

         5       3001       9999

查询员工名及工资,以及工资所在的等级,关键字between and

SQL> select e.ename,e.sal,s.grade from emp e,salgrade s where e.sal between s.losal and s.hisal;


ENAME             SAL      GRADE

---------- ---------- ----------

SMITH             800          1

JAMES             950          1

ADAMS            1100          1

WARD             1250          2

MARTIN           1250          2

MILLER           1300          2

TURNER           1500          3

ALLEN            1600          3

CLARK            2450          4

BLAKE            2850          4

JONES            2975          4

SCOTT            3000          4

FORD             3000          4

KING             5000          5

多表查询结合order by关键字

按照部门号排序查询员工名,薪水,部门号以及所在部门

SQL> select e.ename,e.sal,d.dname,d.deptno from emp e,dept d where e.deptno=d.deptno order by d.deptno;


ENAME             SAL DNAME              DEPTNO

---------- ---------- -------------- ----------

CLARK            2450 ACCOUNTING             10

KING             5000 ACCOUNTING             10

MILLER           1300 ACCOUNTING             10

JONES            2975 RESEARCH               20

FORD             3000 RESEARCH               20

ADAMS            1100 RESEARCH               20

SMITH             800 RESEARCH               20

SCOTT            3000 RESEARCH               20

WARD             1250 SALES                  30

TURNER           1500 SALES                  30

ALLEN            1600 SALES                  30

JAMES             950 SALES                  30

BLAKE            2850 SALES                  30

MARTIN           1250 SALES                  30


找出emp表中,员工的各个上级员工名(一个表中的不同查询)

SQL> select e.ename,m.ename from emp e,emp m where e.mgr=m.empno; 判断条件是员工上级号等于员工号,左边是员工,右边是员工对应的上级号


ENAME      ENAME

---------- ----------

FORD       JONES

SCOTT      JONES

TURNER     BLAKE

ALLEN      BLAKE

WARD       BLAKE

JAMES      BLAKE

MARTIN     BLAKE

MILLER     CLARK

ADAMS      SCOTT

BLAKE      KING

JONES      KING

CLARK      KING

SMITH      FORD

查出某一个员工的上级

SQL> select e.ename,m.ename from emp e,emp m where e.mgr=m.empno and e.ename=‘WARD‘;


ENAME      ENAME

---------- ----------

WARD       BLAKE



子查询

查出和Smith相同部门的员工,当查出来的结果是当行字句时,选择等于号=

1、线找出smith员工所在的部门号


SQL> select deptno from emp where ename=‘SMITH‘;


    DEPTNO

----------

        20

2、然后以部门号为判断条件进行查询所在部门号的员工

SQL> select ename,deptno from emp where deptno=(select deptno from emp where ename=‘SMITH‘);


ENAME          DEPTNO

---------- ----------

SMITH              20

JONES              20

SCOTT              20

ADAMS              20

FORD               20


子查询

当查出来的结果是多行字句时,选择in进行匹配其中某些

1、找出部门号为10的所有职位

SQL> select job from emp where deptno=10;   当有重复的行时,选择关键字distinct


JOB

---------

MANAGER

PRESIDENT

CLERK

SQL> select distinct job from emp where deptno=10;

2、然后查出在所有职位的所有字段,满足job

SQL> select * from emp where job in (select distinct job from emp where deptno=10);


     EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM

---------- ---------- --------- ---------- ---------- ---------- ----------

    DEPTNO

----------

      7782 CLARK      MANAGER         7839 1981-06-09       2450

        10


      7698 BLAKE      MANAGER         7839 1981-05-01       2850

        30


      7566 JONES      MANAGER         7839 1981-04-02       2975

        20



     EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM


找出所有员工工资比30号部门员工工资高的员工,关键字all

1、先查询出部门号30得员工工资

SQL> select sal,deptno from emp where deptno=30;


       SAL     DEPTNO

---------- ----------

      1600         30

      1250         30

      1250         30

      2850         30

      1500         30

       950         30

2、然后再找出比这个部门号都高的员工

SQL> select ename,sal,deptno from emp where sal>all (select sal from emp where deptno=30);


ENAME             SAL     DEPTNO

---------- ---------- ----------

JONES            2975         20

SCOTT            3000         20

FORD             3000         20

KING             5000         10

当然也有第二种方式表示,如下:

SQL> select ename,sal,deptno from emp where sal>(select max(sal) from emp where deptno=30);


ENAME             SAL     DEPTNO

---------- ---------- ----------

JONES            2975         20

SCOTT            3000         20

KING             5000         10

FORD             3000         20


找出任何一个员工工资比30号部门员工工资高的员工(比30号员工的最低工高就满足条件)

运用关键字any或者min(sal)


查找出和smith相同部门号相同职位的员工

1、线找出Smith所在的部门号和职位


SQL> select deptno,job from emp where ename=‘SMITH‘;


    DEPTNO JOB

---------- ---------

        20 CLERK

2、然后再按照要求查询

SQL> select * from emp where (job,deptno)=(select deptno,job from emp where ename=‘SMITH‘);

select * from emp where (job,deptno)=(select deptno,job from emp where ename=‘SMITH‘)

                         *

ERROR at line 1:

ORA-01722: invalid number   查询的判断条件必须一一对应job,deptno对应后面的子查询



SQL> select * from emp where (deptno,job)=(select deptno,job from emp where ename=‘SMITH‘);


     EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM

---------- ---------- --------- ---------- ---------- ---------- ----------

    DEPTNO

----------

      7369 SMITH      CLERK           7902 1980-12-17        800

        20


      7876 ADAMS      CLERK           7788 1987-05-23       1100

        20


查出每个部门号的平均工资进行分组,查出平均工资,以部门号进行分组排序

SQL> select deptno,avg(sal) from emp group by deptno;


    DEPTNO   AVG(SAL)

---------- ----------

        30 1566.66667

        20       2175

        10 2916.66667


查找出自己部门,自己的工资比自己部门的平均工资高的员工

上面已经查找出了平均值工资和部门号作为了一张表,然后结合两表进行查询

SQL> select e.ename,e.sal,e.deptno,b.avg from emp e,(select deptno,avg(sal) avg from emp group by deptno) b where e.deptno=b.deptno and e.sal>b.avg;


ENAME             SAL     DEPTNO        AVG

---------- ---------- ---------- ----------

ALLEN            1600         30 1566.66667

JONES            2975         20       2175

BLAKE            2850         30 1566.66667

SCOTT            3000         20       2175

KING             5000         10 2916.66667

FORD             3000         20       2175


where e.deptno=b.deptno and e.sal>b.avg:两张表查询,条件一定得准确,逻辑必须清楚


内嵌视图就是子查询当做一张表来使用,给这个子查询这张表赋予一个别名

给表取别名不能加as,列可以加


oracle的分页查询

1、rownum分页    rownum为oracle分配的独有的

SQL> select e.*,rownum rn from (select * from emp) e;    rownum作为分页,添加一个字段rn作为分页行号,用其他值(xm)取代也可以,就是指分页行号

select e.*,rownum xm from (select * from emp) e;   一样的效果只是由rn变成了xm

SQL> select e.*,rownum rn from (select * from emp) e;


     EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM

---------- ---------- --------- ---------- ---------- ---------- ----------

    DEPTNO         RN

---------- ----------

      7369 SMITH      CLERK           7902 1980-12-17        800

        20          1


SQL> select e.*,rownum xm from (select ename,sal from emp) e;


ENAME             SAL         XM

---------- ---------- ----------

SMITH             800          1

ALLEN            1600          2

WARD             1250          3

JONES            2975          4

rn和xm就是分页的行数,现在就可以基于条件来进行每页分配多少行,进行分页查询

SQL> select e.*,rownum xm from (select ename,sal from emp) e where xm<4;

select e.*,rownum xm from (select ename,sal from emp) e where xm<4

                                                              *

ERROR at line 1:

ORA-00904: "XM": invalid identifier

这样的查询是错误的,必须用rownum来进行判断,如下:

SQL> select e.*,rownum xm from (select ename,sal from emp) e where rownum<4;


ENAME             SAL         XM

---------- ---------- ----------

SMITH             800          1

ALLEN            1600          2

WARD             1250          3

SQL> SQL> select e.*,rownum xm from (select ename,sal from emp) e where rownum<4 and rownum >2;

SP2-0734: unknown command beginning "WARD      ..." - rest of line ignored.

这样的操作是错误的,正确做法如下:

SQL> select * from (select e.*,rownum xm from (select ename,sal from emp) e where rownum<4) where xm>2;


ENAME             SAL         XM

---------- ---------- ----------

WARD             1250          3

或者如下第二种:

将下面的查询结果当做又一张字表

SQL> select e.*,rownum xm from (select ename,sal from emp) e;


ENAME             SAL         XM

---------- ---------- ----------

SMITH             800          1

ALLEN            1600          2

WARD             1250          3

JONES            2975          4

MARTIN           1250          5

SQL> select * from (select e.*,rownum xm from (select ename,sal from emp) e) where xm>2 and xm<4;


ENAME             SAL         XM

---------- ---------- ----------

WARD             1250          3


函数count

一张表总共有多少行

SQL> select count(*) from emp;

  COUNT(*)

----------

        14


用查询结果创建一张新表

SQL> create table myemp(id,ename,sal) as select empno,ename,sal from emp;   后面是查询的结果

Table created


SQL> desc myemp;

Name  Type         Nullable Default Comments 

----- ------------ -------- ------- -------- 

ID    NUMBER(4)    Y                         

ENAME VARCHAR2(10) Y                         

SAL   NUMBER(7,2)  Y        创建的新表和查询出来的结果一模一样


合并查询:

可以使用的操作符号union,union all,intersect,minus(减)

union:取得两个结果的并集,去掉查询相同的交集

SQL> select ename,sal,job from emp where sal>2500;

ENAME            SAL JOB

---------- --------- ---------

JONES        2975.00 MANAGER

BLAKE        2850.00 MANAGER

SCOTT        3000.00 ANALYST

KING         5000.00 PRESIDENT

FORD         3000.00 ANALYST


SQL> select ename,sal,job from emp where job=‘MANAGER‘;

ENAME            SAL JOB

---------- --------- ---------

JONES        2975.00 MANAGER

BLAKE        2850.00 MANAGER

CLARK        2450.00 MANAGER

然后将两者进行union,取并集,去掉交集部分

SQL> select ename,sal,job from emp where sal>2500 union

  2  select ename,sal,job from emp where job=‘MANAGER‘;

ENAME            SAL JOB

---------- --------- ---------

BLAKE        2850.00 MANAGER

CLARK        2450.00 MANAGER

FORD         3000.00 ANALYST

JONES        2975.00 MANAGER

KING         5000.00 PRESIDENT

SCOTT        3000.00 ANALYST

相同的部分取一次


union all不会取消重复行

SQL> select ename,sal,job from emp where sal>2500 union all select ename,sal,job from emp where job=‘MANAGER‘;

ENAME            SAL JOB

---------- --------- ---------

JONES        2975.00 MANAGER

BLAKE        2850.00 MANAGER

SCOTT        3000.00 ANALYST

KING         5000.00 PRESIDENT

FORD         3000.00 ANALYST

JONES        2975.00 MANAGER

BLAKE        2850.00 MANAGER

CLARK        2450.00 MANAGER

直接是两者查询的总和,不去掉重复行



intersect取交集

SQL> select ename,sal,job from emp where sal>2500 intersect select ename,sal,job from emp where job=‘MANAGER‘;

ENAME            SAL JOB

---------- --------- ---------

BLAKE        2850.00 MANAGER

JONES        2975.00 MANAGER




minus取差集,大的集合减去小的集合(谁的查询在前就是减去后面查询的集合)

如果小的减去大的就是空集

集合操作速度快


oracle之sql查询二