Oracle用户登录和连接查询、特殊排序、over()、rank()、decode()、 case when、UNION/UNION ALL
一、登录问题
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';
