oracle 11g 學習筆記 10_26

一、oracle基本查詢

這是對錶的最重要的一個操作。
*查看錶的結構

desc dept;

.查詢所有列

select * from dept;

.查詢指定的一些列

select empno, ename, job, deptno from emp where ename = 'SMITH';

在oracle中，用單引號括起來的內容是區分大小寫。
.取消重複行 

select distinct deptno from emp;

.使用算數運算式
.使用別名

select ename '姓名',sal*2 as "年度營收"from emp;

*如何處理null值
使用nvl函數來處理

select sal*13+nvl(comm,0)*13 "年工資",ename ,comm from emp;

其中nvl(comm,0)表示的是，如果comm為空白，那麼就將comm表示為0，如果不為空白，則不改變。
.如何連接字串

select ename || 'is a ' || job from emp;

.使用where子句?   
顯示工資高於3000的 員工;  

SELECT * FROM emp WHERE sal > 3000;  

尋找1982.1.1後入職的員工;  

SELECT ename,hiredate FROM emp WHERE hiredate >'1-1月-1982'; 

這裡要注意一下日期的格式。 
顯示工資在2000到3000的員工  

SELECT ename,sal FROM emp WHERE sal >=2000 AND sal <= 3000;  

*如何使用like操作符  
%：表示0到多個字元  _：表示任意單個字元  
顯示首字元為S的員工姓名和工資  

SELECT ename,sal FROM emp WHERE ename like 'S%';  

顯示第三個字元為大寫O的所有員工的姓名和工資  

SELECT ename,sal FROM emp WHERE ename like '__O%'; 

.在where條件中使用in   
顯示empno為7844, 7839,123,456 的僱員情況  

SELECT * FROM emp WHERE empno in (7844, 7839,123,456);

其中 in (7844, 7839,123,456);表示的是在empno包含7844，7839，123，456；  
.使用is null的操作符  
顯示沒有上級的僱員的情況  
錯誤寫法：select * from emp where mgr = '';  
正確寫法：SELECT * FROM emp WHERE mgr is null; 
*order by 的使用
顯示emp表工資由高到低排序

select * from emp order by sal desc;

其中order by sal表示的是按sal這一列來排序，desc表示是按降序來排序，asc表示的是按升序來排序（預設）。
顯示按照部門號升序而僱員的工資降序排列。

select * from emp order by deptno, empno desc;

查詢操作時間可以用set timing on;

當要查詢某個欄位中值為空白的資料時，可以這樣寫

select * from student where birthdya is null;

注意：中文內容要加雙引號
二、複雜查詢

.資料分組
-max,min,avg,sum,count
.顯示所有員工中最高工資和最低工資

select max(sal),min(sal) from emp;

顯示最高工資的員工姓名和工資；
開始我也覺得這個題很簡單，但是我先像以下那樣試做了，結果不對：
(錯)SQL> select ename,sal from emp
  2  where sal = max(sal)
  3  group by ename,sal;
ORA-00934: 此處不允許使用分組函數
(錯)SQL> select ename,max(sal) as "最高工資" from emp;
ORA-00937: 不是單組分組函數
如果有一列是分組函數，那麼其他列都要求是分組函數
正確：

select ename, sal from emp where sal =(select max(sal) from emp);

.顯示工資高於平均工資的員工資訊
*group by 和 having子句
group by用於對查詢的結果分組統計
having子句用於限制分組顯示結果
.顯示每個部門的平均工資和最高工資 

select deptno,avg(sal),max(sal) from emp group by deptno;

.顯示每個部門的每種崗位的平均工資和最低工資

select avg(sal),max(sal),deptno,job from emp group by deptno,job;

.顯示平均工資低於2000的部門號和它的平均工資。

 select avg(sal),max(sal),deptno from emp group by deptno having avg(sal)>2500;

*對資料分組的總結
i 分組函數只能出現在挑選清單，having，order by 子句中，不能當成一個條件來使用，比如在select，where中的條件啊。
ii 如果在select 語句中同時包含有group by，having，order by 那麼他們的順序是group by，having，order by
iii 在選擇列中如果有列，運算式，和分組函數，那麼這些列和運算式中必須有一個出現在group by子句中，否則就會出錯。
三、多表查詢

多表查詢是指基於兩個和兩個以上的表或是視圖的查詢。在實際應用中，查詢單個表是不能滿足要求的。
.顯示僱員姓名，僱員工資及所在部門的名字，並按部門排序。

select ename, sal, dname from emp, dept where emp.deptno = dept.deptno order by dname;

四、子查詢

子查詢是指嵌入在其它sql語句中的select語句。也叫巢狀查詢
*單行子查詢
單行子查詢是指子查詢語句只返回一行資料的查詢；
.顯示與SMITH同一部門的所有員工。

select * from emp   where deptno = (select deptno from emp where ename = 'SMITH');

*多行子查詢
多行子查詢指返回多行資料的子查詢
.查詢和部門10的工作相同的僱員的名字、崗位、工資、部門號

select * from emp where job in(select distinct job from emp where deptno = 10);

*多列子查詢
多列子查詢是指查詢返回多個列資料的子查詢語句；
.查詢與smith的部門和崗位完全相同的所有僱員。

select ename from emp where deptno = (select deptno from emp where ename = 'SMITH') and job = (select job from emp where ename = 'SMITH');

或者是

select ename from emp where (deptno, job) = (select deptno, job from emp where ename = 'SMITH');