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),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),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,dept d where e.deptno=d.deptno=10;

select e.ename,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,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,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,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,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,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,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,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.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.*,sal from emp) e where xm<4;

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

*

ERROR at line 1:

ORA-00904: "XM": invalid identifier

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

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

ENAME SAL XM

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

SMITH 800 1

ALLEN 1600 2

WARD 1250 3

SQL> SQL> select e.*,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.*,sal from emp) e where rownum<4) where xm>2;

ENAME SAL XM

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

WARD 1250 3

或者如下第二种：

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

SQL> select e.*,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.*,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,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,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,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,job from emp where sal>2500 union

2 select ename,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,job from emp where sal>2500 union all select ename,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,job from emp where sal>2500 intersect select ename,job from emp where job='MANAGER';

ENAME SAL JOB

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

BLAKE 2850.00 MANAGER

JONES 2975.00 MANAGER

minus取差集，大的集合减去小的集合（谁的查询在前就是减去后面查询的集合）

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

集合操作速度快

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!