Oracle用户登录和连接查询、特殊排序、over()、rank()、decode()、 case when、UNION/UNION ALL

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了Oracle用户登录和连接查询、特殊排序、over()、rank()、decode()、 case when、UNION/UNION ALL，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含5676字，纯文字阅读大概需要9分钟。

内容图文

一、登录问题

1、 忘记用户名密码：

(1)默认应户名密码：

system/manager    sys/change_on_install     scott/tiger

(2)cmd以系统管理员身份登录：

C:\Users\SAMSUNG>sqlplus system/manager as sysdba

查看所有user：

SQL> select username from dba_users;

修改用户密码：

SQL> alter user scott identified by tiger;

删除用户：

SQL> drop user wx cascade;

创建用户：

SQL> conn system/manager as sysdba

SQL> create user wx identified by lovewx;

SQL> grant connect,resource to wx;

更改登录用户：

SQL> conn scott/tiger

二、连接查询

(1)     相等连接(用=指定连接条件)

SQL> select empno,ename,sal,comm,emp.deptno,dname from emp,dept where emp.deptno = dept.deptno;

(2) 不等连接

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

(3)自连接

SQL> select e1.empno,e1.ename,e2.empno mgrno,e2.ename mgrname from emp e1,emp e2 where e1.mgr = e2.empno;

(4)内连接（返回所有满足条件的记录）

SQL> select dname,ename from emp,dept where emp.deptno = dept.deptno and dept.de

ptno = ‘10‘;

 

SQL> select dname,ename from emp inner join dept on emp.deptno = dept.deptno and

 dept.deptno = ‘10‘;

SQL> select dname,ename from dept natural join emp;

(5)外连接

左外连接：

SQL> select dname,ename from dept left join emp on dept.deptno = emp.deptno and

dept.deptno = ‘10‘;

当有员工未分配部门，统计员工所在部门，部门名称。

右外连接：

完全外连接：

SQL> select ename,dname from emp full join dept on emp.deptno = dept.deptno;

 

三、Over()

问题：输出每个部门工资最高的人员信息

(1) SQL> select * from emp e,(select deptno,max(sal) maxsal  from emp group by deptno) t where e.sal = t.maxsal and e.deptno = t.deptno;

(2)SQL> select * from emp where (deptno,sal) in (select deptno,max(sal) from emp gr

• oup by deptno);

(3) SQL> select empno,ename,job,mgr,sal,comm,hiredate,deptno from (select emp.*,max(sal) over(partition by deptno) maxsal from emp) where sal = maxsal;

 

四、Rank函数

(1)每个部门按工资排序：

select empno,deptno,sal,

rank() over(partition by deptno order by sal) rank,

dense_rank() over(partition by deptno order by sal) denserank,

row_number() over(partition by deptno order by sal) row_number

from emp;

NULLS FIRST/LAST用法：

SQL> select * from emp order by sal nulls first;

 

五、特殊情况排序:

问题：查询出所有员工的empno,ename,job ,mgr,mgrname,deptno,dname,sal,comm,再以部门为范围按工资排序（salrank）,以部门为范围按工资加奖金排序（salcommrank）.

（1）select b.dname, a.*,

rank() over(partition by a.deptno order by NVL(a.sal,0) desc) rk1,

rank() over(partition by a.deptno order by (NVL(a.sal,0)+NVL(a.comm,0)) desc) rk2 from

(select t1.*, t2.ename as MGRNAME from emp t1 left join emp

 t2 on t1.mgr = t2. empno) a left join dept b on a.deptno = b. deptno;

（2）select empno,t.ename,mgr,mgrname,t.deptno,dname,sal,comm,

rank() over(partition by t.deptno order by NVL(sal,0) desc) salrank,

rank() over(partition by t.deptno order by (NVL(sal,0)+NVL(comm,0)) desc) salcommrank

from(select t1.*, t2.ename mgrname from emp t1 left join emp t2 on t1.mgr = t2. empno) t

left join dept d on t.deptno = d.deptno;

 

问题：指定记录排最前或最后

1.Case when用法:

(1)转换字段

select emp.*,case when ename = ‘KING‘ then ‘BOSS‘ else ‘EMPLOYEE‘ end as role from emp;

(2)排序

A.将7788排在第一位

select * from emp order by case empno when 7788 then 1 else 2 end;

B.将7788排第一，7839排第二……

select * from emp order by case empno when 7788 then 1 when 7839 then 1 else 2 end;

2.Decode用法：

(1)比较大小：比较员工工资高于平均工资还是低于平均工资

select e.*,decode(sign(sal-avgsal),1,‘high‘,0,‘equal‘,‘low‘) comparetoavg from (select emp.*,avg(sal) over() avgSal from emp) e;

sign()函数根据某个值是0、正数还是负数，分别返回0、1、-1

(2)排序:

A.将7788排在第一位

select * from emp order by decode(empno,7788,1,2);

B.将7788排第一，7839排第二……

select * from emp order by decode(empno,7788,1,7839,2,3);

(3)排序:

A.将7788排在第一位

select * from emp where empno=‘7788‘ union all select * from emp where empno<>‘7788‘;

B.将7788排第一，7839排第二……

select * from emp where empno=‘7788‘ union all select * from emp where empno =‘7839‘union all select * from emp where empno<>‘7788‘ and empno<>‘7839‘;

 

 

六、UNION/UNION ALL用法：

(1)union:去掉重复记录并排序

select * from emp union select * from emp;

select * from emp where empno=‘7788‘ union select * from emp where empno<>‘7788‘;

(2)union all:保留重复记录不排序

select * from emp union all select * from emp;

 

七、ROWNUM/ROWID

1. rownum: 不支持>,>=,=,between...and，只能用以上符号(<、<=、!=) rownum是对结果集加的一个伪列，即先查到结果集之后再加上去的一个列 (强调：先要有结果集)。简单的说 rownum 是对符合条件结果的序列号。它总是从1开始排起的。所以你选出的结果不可能没有1，而有其他大于1的值。

另外还要注意：rownum不能以任何基表的名称作为前缀。

(1)       取前五条记录

select * from emp where rownum <= 5;

(2)       取除前五条记录的所有记录。

select * from (select emp. *,rownum no from emp) where no > 5;

(3)排名（各部门内按sal排名）

select e.*,rownum no from(select * from emp where deptno=‘10‘ order by nvl(sal,0)) e union all

select e.*,rownum no from(select * from emp where deptno=‘20‘ order by nvl(sal,0)) e union all

select e.*,rownum no from(select * from emp where deptno=‘30‘ order by nvl(sal,0)) e union all

select e.*,rownum no from(select * from emp where deptno is null order by nvl(sal,0)) e

 

2.  rowid: rowid 可以说是物理存在的，表示记录在表空间中的唯一位置ID，在DB中是唯一的。只要记录没被搬动过，rowid是不变的。

select rowid,emp.* from emp;

select emp.* from emp where rowid = ‘AAAQ+jAAEAAAAAeAAI‘;

 

Oracle用户登录和连接查询、特殊排序、over()、rank()、decode()、 case when、UNION/UNION ALL

标签：

本文系统来源：http://www.cnblogs.com/wxlovewx/p/5200356.html

内容总结

以上是互联网集市为您收集整理的Oracle用户登录和连接查询、特殊排序、over()、rank()、decode()、 case when、UNION/UNION ALL全部内容，希望文章能够帮你解决Oracle用户登录和连接查询、特殊排序、over()、rank()、decode()、 case when、UNION/UNION ALL所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

---