首页 > 代码库 > 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查询二