oracle之sql查询二

zyh3033

　　此文章为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    >　　---------- ------------------------------ --- --------- ----------
　　1 ??????                         F   09-JUL-16          1
　　2 xiaobai                        F   06-SEP-16          2
　　3 xiaohua                        F                      2
　　然后临时修改当前会话的时间格式（修改的是当前session会话的，关掉后将失去更改，修改之后才能yyyy-mm-dd格式）

　　SQL>>
　　Session>　　修改后再次查询，如下
　　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 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 select e.*,rownum xm from (select ename,sal from emp) e where rownum SQL> select e.*,rownum xm from (select ename,sal from emp) e where rownum2;
　　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 rownum2;
　　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 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取差集，大的集合减去小的集合（谁的查询在前就是减去后面查询的集合）
　　如果小的减去大的就是空集
　　集合操作速度快