Oracle- 表查詢

標籤：des   style   blog   http   color   os   io   使用   strong   

這些都比較基礎，還是重溫一下。

一、簡單的查詢語句
1、查詢表結構
sql>desc dept; (使用PL/SQL Developer查詢時，必需在命令視窗才能使用該語句)

2、查詢所有列
sql>select * from dept;

3、查詢指定列
sql>select ename,sal,job,deptno from emp;

4、如何取消重複行
sql>select distinct deptno,job from emp;

5、使用算術運算式
執行個體：顯示每個僱員的年工資
sql>select ename, sal * 12 "年工資" from emp;

6、使用列的別名
sql>select ename "姓名", sal*12 as "年度營收" from emp;

7、如何處理null值
說明：對於null值，我們可以使用nvl函數來處理。
執行個體：顯示每個僱員的年工資（包括獎金）
sql>select ename, sal*12+nvl(comm,0)*12 "年度營收" from emp;

8、如何連接字串
sql>select ename || ‘is a ‘ || job from emp;

9、使用where子句
執行個體：如何顯示工資高於3000的員工
sql>select ename,sal from emp where sal>3000;
執行個體：如何尋找1982.1.1後入職的員工
sql>select ename,hiredate from emp where hiredate>‘1-1月-1982‘;
執行個體：如何顯示工資在2000到2500的員工情況
sql>select ename,sal from emp where sal between 2000 and 2500;

10、使用like操作符
（1）%：表示任意0到多個字元。
（2）_：表示任意單個字元。
執行個體：如何顯示首個字元為S的員工姓名和工資
sql>select ename,sal from emp where ename like ‘S%‘;
執行個體：如何顯示第三個字元為大寫O的所有員工的姓名和工資
sql>select ename,sal from emp where ename like ‘__O%‘;

11、在where條件中使用in
執行個體：如何顯示empno為123，345，800的僱員情況
sql>select * from emp where empno in (123,345,800);

12、使用is null的操作符
執行個體：如何顯示沒有上級的僱員的情況
sql>select * from emp where mgr is null;

13、使用邏輯操作符號
執行個體：查詢工資高於500或是崗位為MANAGER的僱員，同時還要滿足他們的姓名首先字母為大寫的J
sql>select * from emp where (sal>500 or job=‘MANAGER‘) and ename like ‘J%‘;

14、使用order by子句
執行個體：如何按照工資中低到高的順序顯示僱員的資訊
sql>select * from emp order by sal;
執行個體：按照部分號升序而僱員的工資降序排列
sql>select * from emp order by deptno,sal desc;

15、使用列的別名排序
sql>select ename,sal*12 "年薪" from emp order by "年薪" desc;
註：別名需要使用雙引號括起來。

 

二、複雜的查詢語句
說明：在實際應用中經常需要執行複雜的資料統計，經常需要顯示多張表的資料，現在我們給大家介紹較為複雜的select語句。
1、資料分組——max,min,avg,sum,count
執行個體：如何顯示所有員工中最高工資和最低工資
sql>select max(sal),min(sal) from emp;
執行個體：顯示所有員工的平均工資和工資總和
sql>select avg(sal),sum(sal) from emp;
執行個體：計算共有多少員工
sql>select count(*) from emp;
執行個體：請顯示工資最高的員工的名字，工作崗位
sql>select ename,job from emp where sal=(select max(sal) from emp);
執行個體：顯示工資高於平均工資的員工
sql>select * from emp where sal>(select avg(sal) from emp);

2、group by和having子句
group by用於對查詢的結果分組統計。
having用於限制分組顯示結果。
執行個體：如何顯示每個部門的平均工資和最高工資
sql>select avg(sal),max(sal),deptno from emp group by deptno;
執行個體：顯示每個部門的每種崗位的平均工資和最低工資
sql>select avg(sal),min(sal),deptno,job from emp group by deptno,job;
執行個體：顯示平均工資低於2000的部門號和他的平均工資
sql>select avg(sal),deptno from emp group by deptno having avg(sal)>2000;

對資料分組的總結
1、分組函數只能出現在挑選清單、having、order by子句中。
2、如果在select語句中同時包含group by、having、order by，那麼他們的順序是group by，having，order by。
3、在選擇列中如果有列、運算式和分組函數，那麼這些列和運算式必須有一個出現在group by子句中，否則就會出錯。
如：select deptno,avg(sal),max(sal) from emp group by deptno having avg(sal)>2000 order by avg(sal);
這裡deptno就一定要出現在group by中。

 

三、多表查詢
說明：多表查詢是指基於兩個或兩個以上的表或視圖的查詢，在實際應用中，查詢單個表可能不能滿足需求（如顯示sales部門位置和其員工的姓名），這種情況下需要使用到（dept表和emp表）。
1、顯示僱員名，僱員工資及所在部門的名字
sql>select a1.ename,a1.sal,a2.dname from emp a1 inner join dept a2 on a1.deptno=a2.deptno;

2、如何顯示部門號為10號的部門名、員工和工資
sql>select a2.dname,a1.ename,a1.sal from emp a1,dept a2 where a1.deptno=a2.deptno and a2.deptno=10;

3、顯示各個員工的姓名、工資及其工資的層級
sql>select a1.ename,a1.sal,a2.grade from emp a1,salgrade a2 where a1.sal between a2.losal and a2.hisal;

4、顯示僱員名、僱員工資及其所在部門的名稱，並按部門排序
sql>select a1.ename,a1.sal,a2.dname from emp a1 inner join dept a2 on a1.deptno=a2.deptno order by a1.deptno;

5、自串連
說明：自串連是指在同一張表的串連查詢。
案例：顯示FORD的上級領導的姓名
sql>select worker.ename "員工",boss.ename "上級領導" from emp worker,emp boss where worker.mgr=boss.empno and worker.ename=‘FORD‘;

四、子查詢
概述：子查詢是指嵌入在其他sql語句中的select語句，也叫巢狀查詢。
1、單行子查詢
說明：單行子查詢是指只返回一行資料的子查詢語句。
案例：如何顯示與SMITH同一部門的所有員工
sql>select * from emp where deptno=(select deptno from emp where ename=‘SMITH‘);

2、多行子查詢
說明：多行子查詢指返回多行資料的子查詢。
案例：如何查詢和部門10的工作相同的僱員的名字、崗位、工資、部門號
sql>select ename,job,sal,deptno from emp where job in (select distinct job from emp where deptno=10);

3、在多行子查詢中使用all操作符
案例：如何顯示工資比部門30的所有員工的工資高的員工的姓名、工資和部門號
sql>select ename,sal,deptno from emp where sal > all(select sal from emp where deptno=30);

4、在多行子查詢中使用any操作符
案例：如何顯示工資比部門30的任意一個員工的工資高的員工的姓名、工資和部門號
sql>
sql>select ename,sal,deptno from emp where sal > any(select sal from emp where deptno=30);

5、多列子查詢
說明：單行子查詢是指子查詢只返回單列、單行資料。多行子查詢是指返回單列多行資料，都是針對單列而言的，而多列子查詢則是指查詢返回多個列資料的子查詢語句。
案例：如何查詢與SMITH的部門和崗位完全相同的所有僱員
sql>select * from emp where (deptno,job)=(select deptno,job from emp where ename=‘SMITH‘);

6、在from子句中使用子查詢
案例：如何顯示高於自己部門平均工資的員工的資訊
sql>select a1.ename,a1.sal,a1.deptno,a2.avgSal from emp a1,(select deptno,avg(sal) from emp group by deptno) a2 where a1.deptno=a2.deptno and sal>avgSal;
總結：這裡需要說明的是當在from子句中使用子查詢時，該子查詢會被作為一個視圖來對待，因此叫做內嵌視圖，當在from子句中使用子查詢時，必須給子查詢指定別名。

7、用查詢結果建立新表
說明：這個命令是一種快捷的建表方法。
案例：create table myemp(id,ename,sal) as (select empno,ename,sal from emp);

8、使用子查詢插入資料
說明：當使用values子句時，一次只能插入一行資料，當使用子查詢插入資料時，一條insert語句可以插入大量的資料。當處理行遷移或者裝載外部表格的資料到資料庫時，可以使用子查詢來插入資料。
案例：sql>insert into Users select * from emp where deptno=10;

9、使用子查詢更新資料
說明：使用Update語句更新資料時，既可以使用運算式或者數值直接修改資料，也可以使用子查詢修改資料。
案例：希望更新員工scott的崗位、工資、補助與Smith員工一樣
sql>update emp set (job,sal,comm)=(select job,sal,comm from emp where ename=‘SMITH‘) where ename=‘SCOTT‘;

五、分頁查詢
1、根據ROWID來分,效率最高
sql>select * from emp where rowid in (select rid from (select rownum rn,rid from (select rowid rid,empno from emp order by empno desc) where rownum<10) where rn>6) order by empno desc;

2、按分析函數來分，效率最差
sql>select * from (select t.*,row_number() over (order by empno desc) rk from emp t) where rk between 6 and 10;

3、按ROWNUM來分,效率次之
sql>select * from (select a1.*,rownum rn from (select * from emp) a1 where rownum<=10) where rn>=6;

六、合并查詢
概述：有時在實際應用中，為了合并多個select語句的結果，可以使用集合操作符號union，union all，intersect，minus。
1、union
說明：該操作符用於取得兩個結果集的並集。當使用該操作符時，會自動去掉結果集中重複行。
案例：sql>select ename,sal,job from emp where sal>2500 union select ename,sal,job from emp where ename=‘MANAGER‘;

2、union all
說明：該操作符用於取得兩個結果集的並集。當使用該操作符時，不會自動去掉結果集中重複行。
案例：sql>select ename,sal,job from emp where sal>2500 union all select ename,sal,job from emp where ename=‘MANAGER‘;

3、intersect
說明：使用該操作符用於取得兩個結果集的交集。
案例：sql>select ename,sal,job from emp where sal>2500 intersect ename,sal,job,from emp where job=‘MANAGER‘;

4、minus
說明：使用該操作符用於取得兩個結果集的差集，它只會顯示存在第一個集合中，而不存在第二個集合中的資料。
案例：sql>select ename,sal,job from emp where sal>2500 minusename,sal,job,from emp where job=‘MANAGER‘;

 

轉載自：http://www.cnblogs.com/BeautyOfCode/archive/2010/09/15/1826576.html

 

Oracle- 表查詢