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‘;

 

Oracle使用者登入和串連查詢、特殊排序、over()、rank()、decode()、 case when、UNION/UNION ALL