oracle中怎么實現select操作

137次閱讀

共計 8058 個字符，預計需要花費 21 分鐘才能閱讀完成。

這篇文章給大家介紹 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
下面最主要介紹第三種：按 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
5. 幾個查詢變化
a. 指定查詢列，只需要修改最里層的子查詢
只查詢雇員的編號和工資
SELECT * FROM (SELECT e.*, ROWNUM rn FROM (SELECT ename, sal FROM emp) e WHERE ROWNUM = 10) WHERE rn
b. 排序查詢，只需要修改最里層的子查詢
工資排序后查詢 6-10 條數據
SELECT * FROM (SELECT e.*, ROWNUM rn FROM (SELECT ename, sal FROM emp ORDER by sal) e WHERE ROWNUM = 10) WHERE
rn
? 用查詢結果創建新表
這個命令是一種快捷的建表方式
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 就是減法的意思）