oracle中怎么實現select操作

這篇文章給大家介紹oracle中怎么實現select操作，內容非常詳細，感興趣的小伙伴們可以參考借鑒，希望對大家能有所幫助。

? 查看表結構 
DESC emp; 
? 查詢所有列 
SELECT * FROM dept; 
切忌動不動就用 select * 
SET TIMING ON; 打開顯示操作時間的開關，在下面顯示查詢時間。 
CREATE TABLE users(userId VARCHAR2(10), uName VARCHAR2 (20), uPassw VARCHAR2(30)); 
INSERT INTO users VALUES('a0001', '啊啊啊啊', 'aaaaaaaaaaaaaaaaaaaaaaa'); 
--從自己復制，加大數據量 大概幾萬行就可以了   可以用來測試 sql 語句執行效率 
INSERT INTO users (userId,UNAME,UPASSW) SELECT * FROM users; 
SELECT COUNT (*) FROM users;統計行數
? 查詢指定列 
SELECT ename, sal, job, deptno FROM emp; 
? 如何取消重復行 DISTINCT 
SELECT DISTINCT deptno, job FROM emp; 
?查詢 SMITH 所在部門，工作，薪水 
SELECT deptno,job,sal FROM emp WHERE ename = 'SMITH'; 
注意：oracle 對內容的大小寫是區分的，所以 ename='SMITH'和 ename='smith'是不同的  
Oracle  筆記
8
? 使用算術表達式   nvl   null 
問題：如何顯示每個雇員的年工資？ 
SELECT sal*13+nvl(comm, 0)*13 "年薪" , ename, comm FROM emp; 
? 使用列的別名 
SELECT ename "姓名", sal*12 AS "年收入" FROM emp; 
? 如何處理 null 值 
使用 nvl 函數來處理 
? 如何連接字符串（||） 
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); 
? 使用 is null 的操作符 
問題：如何顯示沒有上級的雇員的情況？ 
錯誤寫法：select * from emp where mgr = ''; 
正確寫法：SELECT * FROM emp WHERE mgr is null; 
6.oracle 表查詢(2)
? 使用邏輯操作符號 
問題：查詢工資高于 500 或者是崗位為 MANAGER 的雇員，同時還要滿足他們的姓名首字母為大寫的 J？ 
SELECT * FROM emp WHERE (sal >500 or job = 'MANAGER') and ename LIKE 'J%'; 
? 使用 order by 字句     默認 asc 
問題：如何按照工資的從低到高的順序顯示雇員的信息？ 
SELECT * FROM emp ORDER by sal; 
問題：按照部門號升序而雇員的工資降序排列 
SELECT * FROM emp ORDER by deptno, sal DESC; 
? 使用列的別名排序 
問題：按年薪排序 
select ename, (sal+nvl(comm,0))*12 "年薪" from emp order by "年薪" asc; 
別名需要使用“”號圈中,英文不需要“”號  
Oracle  筆記
9
? 分頁查詢 
等學了子查詢再說吧。。。。。。。。 
Clear 清屏命令 
oracle 表復雜查詢 
? 說明 
在實際應用中經常需要執行復雜的數據統計，經常需要顯示多張表的數據，現在我們給大家介紹較為復雜的 select 語句 
數據分組 ——max，min， avg， sum， count 
問題：如何顯示所有員工中最高工資和最低工資？ 
SELECT MAX(sal),min(sal) FROM emp e; 
最高工資那個人是誰？ 
錯誤寫法：select ename, sal from emp where sal=max(sal); 
正確寫法：select ename, sal from emp where sal=(select max(sal) from emp); 
注意：select ename, max(sal) from emp;這語句執行的時候會報錯，說 ORA-00937：非單組分組函數。因為 max 是分組函數，
而 ename 不是分組函數....... 
但是 select min(sal), max(sal) from emp;這句是可以執行的。因為 min 和 max 都是分組函數，就是說：如果列里面有一個
分組函數，其它的都必須是分組函數，否則就出錯。這是語法規定的 
問題：如何顯示所有員工的平均工資和工資總和？ 
問題：如何計算總共有多少員工問題：如何 
擴展要求： 
查詢最高工資員工的名字，工作崗位 
SELECT ename, job, sal FROM emp e where sal = (SELECT MAX(sal) FROM emp); 
顯示工資高于平均工資的員工信息 
SELECT * FROM emp e where sal > (SELECT AVG(sal) FROM emp); 
? group by 和 having 子句 
group by 用于對查詢的結果分組統計， 
having 子句用于限制分組顯示結果。
問題：如何顯示每個部門的平均工資和最高工資？ 
SELECT AVG(sal), MAX(sal), deptno FROM emp GROUP by deptno; 
（注意：這里暗藏了一點，如果你要分組查詢的話，分組的字段 deptno 一定要出現在查詢的列表里面，否則會報錯。因為分組
的字段都不出現的話，就沒辦法分組了） 
問題：顯示每個部門的每種崗位的平均工資和最低工資？ 
SELECT min(sal), AVG(sal), deptno, job FROM emp GROUP by deptno, job; 
問題：顯示平均工資低于 2000 的部門號和它的平均工資？ 
SELECT AVG(sal), MAX(sal), deptno FROM emp GROUP by deptno having AVG(sal) < 2000; 
? 對數據分組的總結 
1 分組函數只能出現在選擇列表、having、order by 子句中(不能出現在 where 中) 
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; 
這里 deptno 就一定要出現在 group by 中 
多表查詢 
? 說明 
多表查詢是指基于兩個和兩個以上的表或是視圖的查詢。在實際應用中，查詢單個表可能不能滿足你的需求，（如顯示 sales
部門位置和其員工的姓名），這種情況下需要使用到（dept 表和 emp 表）  
Oracle  筆記
10
問題：顯示雇員名，雇員工資及所在部門的名字【笛卡爾集】？ 
規定：多表查詢的條件是 至少不能少于 表的個數-1 才能排除笛卡爾集 
（如果有 N 張表聯合查詢，必須得有 N-1 個條件，才能避免笛卡爾集合） 
SELECT e.ename, e.sal, d.dname FROM emp e, dept d WHERE e.deptno = d.deptno; 
問題：顯示部門號為 10 的部門名、員工名和工資？ 
SELECT d.dname, e.ename, e.sal FROM emp e, dept d WHERE e.deptno = d.deptno and e.deptno = 10; 
問題：顯示各個員工的姓名，工資及工資的級別？ 
先看 salgrade 的表結構和記錄 
SQL>select * from salgrade; 
GRADE                 LOSAL                   HISAL 
-------------     -------------     ------------1                   700                     1200 
2                   1201                   1400 
3                   1401                   2000 
4                   2001                   3000 
5                   3001                   9999 
SELECT e.ename, e.sal, s.grade FROM emp e, salgrade s WHERE e.sal BETWEEN s.losal AND s.hisal; 
擴展要求： 
問題：顯示雇員名，雇員工資及所在部門的名字，并按部門排序？ 
SELECT e.ename, e.sal, d.dname FROM emp e, dept d WHERE e.deptno = d.deptno ORDER by e.deptno; 
（注意：如果用 group by，一定要把 e.deptno 放到查詢列里面） 
? 自連接 
自連接是指在同一張表的連接查詢 
問題：顯示某個員工的上級領導的姓名？ 
比如顯示員工‘FORD’的上級 
SELECT worker.ename, boss.ename FROM emp worker,emp boss WHERE worker.mgr = boss.empno AND worker.ename = 'FORD'; 
子查詢 
? 什么是子查詢 
子查詢是指嵌入在其他 sql 語句中的 select 語句，也叫嵌套查詢。 
? 單行子查詢 
單行子查詢是指只返回一行數據的子查詢語句 
請思考：顯示與 SMITH 同部門的所有員工？ 
思路： 
1 查詢出 SMITH 的部門號 
select deptno from emp WHERE ename = 'SMITH'; 
2 顯示 
SELECT * FROM emp WHERE deptno = (select deptno from emp WHERE ename = 'SMITH'); 
數據庫在執行 sql 是從左到右掃描的， 如果有括號的話，括號里面的先被優先執行。 
? 多行子查詢 
多行子查詢指返回多行數據的子查詢 
請思考：如何查詢和部門 10 的工作相同的雇員的名字、崗位、工資、部門號 
SELECT DISTINCT job FROM emp WHERE deptno = 10; 
SELECT * FROM emp WHERE job IN (SELECT DISTINCT job FROM emp WHERE deptno = 10); 
（注意：不能用 job=..，因為等號=是一對一的） 
? 在多行子查詢中使用 all 操作符  
Oracle  筆記
11
問題：如何顯示工資比部門 30 的所有員工的工資高的員工的姓名、工資和部門號？ 
SELECT ename, sal, deptno FROM emp WHERE sal > all (SELECT sal FROM emp WHERE deptno = 30); 
擴展要求： 
大家想想還有沒有別的查詢方法。 
SELECT ename, sal, deptno FROM emp WHERE sal > (SELECT MAX(sal) FROM emp WHERE deptno = 30); 
執行效率上， 函數高得多 
? 在多行子查詢中使用 any 操作符 
問題：如何顯示工資比部門 30 的任意一個員工的工資高的員工姓名、工資和部門號？ 
SELECT ename, sal, deptno FROM emp WHERE sal > ANY (SELECT sal FROM emp WHERE deptno = 30); 
擴展要求： 
大家想想還有沒有別的查詢方法。 
SELECT ename, sal, deptno FROM emp WHERE sal > (SELECT min(sal) FROM emp WHERE deptno = 30); 
? 多列子查詢 
單行子查詢是指子查詢只返回單列、單行數據，多行子查詢是指返回單列多行數據，都是針對單列而言的，而多列子查詢是指
查詢返回多個列數據的子查詢語句。 
請思考如何查詢與 SMITH 的部門和崗位完全相同的所有雇員。 
SELECT deptno, job FROM emp WHERE ename = 'SMITH'; 
SELECT * FROM emp WHERE (deptno, job) = (SELECT deptno, job FROM emp WHERE ename = 'SMITH'); 
? 在 from 子句中使用子查詢 
請思考：如何顯示高于自己部門平均工資的員工的信息 
思路： 
1. 查出各個部門的平均工資和部門號 
SELECT deptno, AVG(sal) mysal FROM emp GROUP by deptno; 
2. 把上面的查詢結果看做是一張子表 
SELECT e.ename, e.deptno, e.sal, ds.mysal FROM emp e, (SELECT deptno, AVG(sal) mysal FROM emp GROUP by deptno) ds 
WHERE e.deptno = ds.deptno AND e.sal > ds.mysal; 
如何衡量一個程序員的水平？ 
網絡處理能力， 數據庫， 程序代碼的優化程序的效率要很高 
小總結： 
在這里需要說明的當在 from 子句中使用子查詢時，該子查詢會被作為一個視圖來對待，因此叫做內嵌視圖，當在 from 子句中
使用子查詢時，必須給子查詢指定別名。 
注意：別名不能用 as，如：SELECT e.ename, e.deptno, e.sal, ds.mysal FROM emp e, (SELECT deptno, AVG(sal) mysal FROM 
emp GROUP by deptno) as ds WHERE e.deptno = ds.deptno AND e.sal > ds.mysal; 
在 ds 前不能加 as，否則會報錯   （給表取別名的時候，不能加 as；但是給列取別名，是可以加 as 的） 
? 分頁查詢 
按雇員的 id 號升序取出 
oracle 的分頁一共有三種方式 
1.根據 rowid 來分
select * from t_xiaoxi where rowid in (select rid from (select rownum rn, rid from(select rowid rid, cid from 
t_xiaoxi order by cid desc) where rownum<10000) where rn>9980) order by cid desc; 
執行時間 0.03 秒 
2.按分析函數來分
select * from (select t.*, row_number() over(order by cid desc) rk from t_xiaoxi t) where rk<10000 and rk>9980; 
執行時間 1.01 秒 
3.按 rownum 來分  
Oracle  筆記
12
select * from (select t.*,rownum rn from(select * from t_xiaoxi order by cid desc)t where rownum<10000) where 
rn>9980; 
執行時間 0.1 秒 
其中 t_xiaoxi 為表名稱，cid 為表的關鍵字段，取按 cid 降序排序后的第 9981-9999 條記錄，t_xiaoxi 表有 70000 多條記錄。  
個人感覺 1 的效率最好，3 次之，2 最差。 
//測試通過的分頁查詢 okokok 
select * from (select a1.*, rownum rn from(select ename,job from emp) a1 where rownum<=10)where rn>=5; 
下面最主要介紹第三種：按 rownum 來分 
1. rownum 分頁
SELECT * FROM emp;       
2. 顯示 rownum[oracle 分配的] 
SELECT e.*, ROWNUM rn FROM (SELECT * FROM emp) e; 
rn 相當于 Oracle 分配的行的 ID 號 
3.挑選出 6—10 條記錄 
先查出 1-10 條記錄 
SELECT e.*, ROWNUM rn FROM (SELECT * FROM emp) e WHERE ROWNUM <= 10; 
如果后面加上 rownum>=6 是不行的， 
4. 然后查出 6-10 條記錄 
SELECT * FROM (SELECT e.*, ROWNUM rn FROM (SELECT * FROM emp) e WHERE ROWNUM <= 10) WHERE rn >= 6; 
5. 幾個查詢變化 
a. 指定查詢列，只需要修改最里層的子查詢 
只查詢雇員的編號和工資 
SELECT * FROM (SELECT e.*, ROWNUM rn FROM (SELECT ename, sal FROM emp) e WHERE ROWNUM <= 10) WHERE rn >= 6; 
b. 排序查詢，只需要修改最里層的子查詢 
工資排序后查詢 6-10 條數據 
SELECT * FROM (SELECT e.*, ROWNUM rn FROM (SELECT ename, sal FROM emp ORDER by sal) e WHERE ROWNUM <= 10) WHERE 
rn >= 6; 
? 用查詢結果創建新表 
這個命令是一種快捷的建表方式 
CREATE TABLE mytable (id, name, sal, job, deptno) as SELECT empno, ename, sal, job, deptno FROM emp; 
創建好之后，desc mytable；和 select * from mytable;看看結果如何？ 
合并查詢 
? 合并查詢 
有時在實際應用中，為了合并多個 select 語句的結果，可以使用集合操作符號 union，union all，intersect，minus 
多用于數據量比較大的數據局庫，運行速度快。 
1). union 
該操作符用于取得兩個結果集的并集。當使用該操作符時，會自動去掉結果集中重復行。 
SELECT ename, sal, job FROM emp WHERE sal >2500 
UNION 
SELECT ename, sal, job FROM emp WHERE job = 'MANAGER'; 
2).union all 
該操作符與 union 相似，但是它不會取消重復行，而且不會排序。 
SELECT ename, sal, job FROM emp WHERE sal >2500 
UNION ALL 
SELECT ename, sal, job FROM emp WHERE job = 'MANAGER';  
Oracle  筆記
13
該操作符用于取得兩個結果集的并集。當使用該操作符時，會自動去掉結果集中重復行。 
3). intersect 
使用該操作符用于取得兩個結果集的交集。 
SELECT ename, sal, job FROM emp WHERE sal >2500 
INTERSECT 
SELECT ename, sal, job FROM emp WHERE job = 'MANAGER'; 
4). minus 
使用改操作符用于取得兩個結果集的差集，他只會顯示存在第一個集合中，而不存在第二個集合中的數據。 
SELECT ename, sal, job FROM emp WHERE sal >2500 
MINUS 
SELECT ename, sal, job FROM emp WHERE job = 'MANAGER'; 
（MINUS 就是減法的意思） 

關于oracle中怎么實現select操作就分享到這里了，希望以上內容可以對大家有一定的幫助，可以學到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到。