涉及到的問題
1、在select語句中使用條件邏輯
2、限制返回的行數
3、從表中隨機返回n條記錄
4、將空值轉換為實際值
5、對字母和數字混合的資料排序
6、處理排序空值
7、根據資料項的鍵排序
–8、從一個表中查詢另一個表沒有的值
–9、在一個表中查詢與其他表不匹配的記錄
–10、向查詢中增加聯接而不影響其他聯接
–11、檢測兩個表中是否有相同的資料
–12、從多個表中返回丟失的資料
–13、在運算和比較時使用null值
–1、在select語句中使用條件邏輯
select ename,
sal,
case when sal<=2000 then 'UNDERPAID'
when sal>=4000 then 'OVERPAID'
else 'OK'
end as status
from emp
–2、限制返回的行數
select * from emp where rownum<=5
–3、從表中隨機返回n條記錄
select * from (
select ename, job from emp order by dbms_random.value()
)
where rownum <= 5
–4、將空值轉換為實際值
–方法一
select comm, nvl(comm, 0) comm from emp
–方法二
select comm, coalesce(comm, 0) from emp
–方法三
select comm,
case when comm is null then 0
else comm
end comm
from emp
–5、對字母和數字混合的資料排序
–問題：現有字母和數字混合的資料，希望按照數字或字母部分來排序。考慮這個檢視:
create view v_tt as
select ename||' '||deptno data from emp;
主要是透過replace和translate來實現
//by deptno(對數字排序)
select * from v_tt order by replace(data, replace(translate(data, '0123456789', '##########'), '#', ''), '');
//by ename(對字母排序)
select * from v_tt order by replace(translate(data, '0123456789', '##########'), '#', '');
補充：translate函式用法
select translate('123abc','2dc','4e') from dual;
因為from_string和to_string的位置是一一對應的，2對應4，d對應e，c沒有對應的值，所以c應該會被刪除。所以例子的字元裡的2會替換為4，d因為字串裡沒有，所以不作替換，c由於沒有對應的替換字元，所以字串裡的c會被刪除，那麼可以得出，結果是143ab
–6、處理排序空值
主要方法是透過使用CASE表示式來“標記”一個值是否為NULL。這裡標記有兩個值，一個表示NULL，一個表示非NULL。這樣，只要在ORDER BY子句中增加標記列，便可以很容易的控制空值是排在前面還是排在後面，而不會被空值所干擾。
//非空值按升序排序，空值排最後
select ename,sal,comm from(
select ename,sal,comm,
case when comm is null then 0 else 1 end as is_null
from emp
) x order by is_null desc,comm
//非空值按降序排序，空值排最後
select ename,sal,comm from(
select ename,sal,comm,
case when comm is null then 0 else 1 end as is_null
from emp
) x order by is_null desc,comm desc
//非空值按升序排序，空值排最前面
select ename,sal,comm from(
select ename,sal,comm,
case when comm is null then 0 else 1 end as is_null
from emp
) x order by is_null,comm
//非空值按降序排序，空值排最前面
select ename,sal,comm from(
select ename,sal,comm,
case when comm is null then 0 else 1 end as is_null
from emp
) x order by is_null,comm desc
在ORACLE中還可以使用NULLS FIRST和NULLS LAST來實現如上功能
//非空值按升序排序，空值排最後
select ename,sal,comm
from emp
order by comm nulls last
//非空值按降序排序，空值排最後
select ename,sal,comm
from emp
order by comm desc nulls last
//非空值按升序排序，空值排最前面
select ename,sal,comm
from emp
order by comm nulls first
//非空值按降序排序，空值排最前面
select ename,sal,comm
from emp
order by comm desc nulls first
–7、根據資料項的鍵排序
要根據某些條件邏輯來排序。例如，如果JOB是SALESMAN，要根據COMM來排序。否則，根據SAL排序。
select ename,sal,job,comm from emp
order by case when job ='SALSEMAN' then comm else sal end
–8、從一個表中查詢另一個表沒有的值
問題：從表dept中查詢在表emp中不存在的資料的所有部門。示例資料中deptno的值在emp中不存在。
方法一:
select deptno from dept
minus
select deptno from emp
方法二：
select deptno from dept where deptno not in (select deptno from emp where deptno is not null)
注意：
1)、oracle中not in如果返回的有null值的話，不會返回記錄。
例如：select deptno from dept where deptno not in (10, 20, null)
2)、在sql中，true or null的結果是true，而false or null的結果是null，所以在使用in和or計算時，值可能是null的情況，這一點要記住。
要解決not in這樣問題，可以使用not exists和相關子查詢(推薦)
select deptno from dept d where not exists(select 'xx' from emp e where e.deptno = d.deptno)
–9、在一個表中查詢與其他表不匹配的記錄
問題：對於具有相同關鍵字的兩個表，要在一個表中查詢與另一個表中不匹配的行。例如，要查詢沒有職員的部門(emp為從表)。
select d.* from emp e, dept d where e.deptno(+)=d.deptno and e.deptno is null
–10、向查詢中增加聯接而不影響其他聯接
例如，要獲得所有的員工資訊、他們的工作部門的地點以及所獲得的獎勵
select e.ename, d.loc, eb.received
from emp e, dept d, emp_bonus eb
where e.deptno = d.deptno and e.empno = eb.empno
這樣的查詢結果，如果員工沒有獎金，則無法顯示該員工的資訊，那麼，無論有無獎金都要顯示員工資訊，就要使用到外連線
select e.ename, d.loc, eb.received
from emp e, dept d, emp_bonus eb
where e.deptno = d.deptno and e.empno = eb.empno(+)
order by 2
–11、檢測兩個表中是否有相同的資料
create view v2
as select * from scott.emp where deptno!=10
union all
select * from scott.emp where ename=upper('ward')
原理:
1)、首先，查詢出表emp中存在而檢視v2中沒有的行。
2)、然後，合併在檢視v2中存在，而在表emp中沒有的行。
(select empno,ename,job,mgr,hiredate,sal,comm,deptno,count(*) as cnt
from v2
group by empno,ename,job,mgr,hiredate,sal,comm,deptno
minus
select empno,ename,job,mgr,hiredate,sal,comm,deptno,count(*) as cnt
from scott.emp
group by empno,ename,job,mgr,hiredate,sal,comm,deptno)
union all
(select empno,ename,job,mgr,hiredate,sal,comm,deptno,count(*) as cnt
from scott.emp
group by empno,ename,job,mgr,hiredate,sal,comm,deptno
minus
select empno,ename,job,mgr,hiredate,sal,comm,deptno,count(*) as cnt
from v2
group by empno,ename,job,mgr,hiredate,sal,comm,deptno)
–12、從多個表中返回丟失的資料
select d.deptno,d.dname,e.ename from scott.dept d,scott.emp e
where d.deptno=e.deptno(+)
union
select d.deptno,d.dname,e.ename from scott.dept d,scott.emp e
where d.deptno(+)=e.deptno
–13、在運算和比較時使用null值
select ename, comm from scott.emp where coalesce(comm,0) < (select comm from scott.emp where ename=upper('ward'));