Oracle通過scott使用者中的emp練習單表操作

scott使用者中的表很適合練習查詢操作
要想操作scott使用者,先將這個使用者密碼設定了,並解鎖

-- 1. 暴力破解某個使用者的密碼
ALTER USER scott IDENTIFIED BY orcl;
-- 2. 解鎖使用者
ALTER USER scott ACCOUNT UNLOCK;

單表查詢的話我們主要操縱僱員表emp

一.基礎查詢

-- 單行註釋方式

/*
|| 多行註釋1
|| 為啥前面加兩個豎線？
|| 因為這樣好看。。。
*/

/*
多行註釋2
不加豎線的效果
是不是不好看？因為是斜體，所以比較亂，其實tap鍵也可以。。。
*/

-- 任務1 : 基礎查詢結構
-- SELECT * 或 列名 FROM 表名;

-- 查詢 EMP 表所有列資料 
-- ( SQL語句要大寫 , 這樣可以提高執行效能 )
-- ( SQL語句要以分號結尾 )
SELECT * FROM EMP;

-- 查詢指定列的資料 ( 查詢了員工表的編號,姓名,職位三列 )
SELECT EMPNO,ENAME,JOB FROM EMP;

-- 給列起個別名 ( 笑 :-D )
SELECT EMPNO 編號,ENAME 姓名,JOB 職位 FROM EMP;
-- 除了列可以起別名,表也可以起別名( 笑 :-D )
SELECT E.EMPNO 編號,E.ENAME 姓名 FROM EMP E;

-- 消除重複的資料 ( 初級操作 )
-- 實際意義 : 想看一個欄位有幾種資料 , 除了 group by, 呵呵 ...
SELECT DISTINCT JOB 職位種類 FROM EMP;

-- 四則運算 ( 加減乘除啥的都可以 )
-- 計算員工年薪
SELECT E.ENAME 姓名,E.SAL*12 年薪 FROM EMP E;

-- 空值 NULL 的理解 需要注意
-- 比如福利 , 計算年福利
SELECT COMM*12 FROM EMP;
-- 會發現沒有值的地方 並不是 0 或者 空格 
-- 是程式中我們理解的 null 值 不管你怎麼計算 都是 NULL

-- 連線符 || 剛才我麼見過的兩個豎線 當然此豎線非彼豎線
-- 正 + 常 + 理 + 解  但是 萬萬沒想到 ...
SELECT '員工的姓名是:'||ENAME FROM EMP;

二.條件查詢和排序

-- 知識點 1 : 查詢某列資料是否有 NULL 值
-- 為空 ( IS NULL )
SELECT * FROM EMP E WHERE E.COMM IS NULL;
-- 不為空 ( IS NOT NULL )
SELECT * FROM EMP E WHERE E.COMM IS NOT NULL;

-- 查詢工資大於1500 並且有獎金的員工
-- 注意 AND 連線兩個查詢條件 兩個條件必須同時滿足
SELECT * FROM EMP E WHERE E.SAL > 1500 AND E.COMM IS NOT NULL;

-- 查詢工資大於1500 或者 沒有獎金的員工
-- 注意 OR 連線兩個查詢條件 兩個查詢條件滿足一個即可
SELECT * FROM EMP E WHERE E.SAL > 1500 OR E.COMM IS NULL;

-- 查詢工資小於1500 並且 沒有獎金的員工
SELECT * FROM EMP E WHERE NOT(E.SAL > 1500) AND E.COMM IS NULL;
-- 其實上面只是想說下 NOT 的用法 其實 可以變成這樣
SELECT * FROM EMP E WHERE E.SAL < 1500 AND E.COMM IS NULL;

-- 知識點 2:範圍限制
-- 基本工資大於 1500 但是小於 3000
SELECT * FROM EMP E WHERE E.SAL > 1500 AND E.SAL < 3000;

-- 如果包含1500 和 3000 呢 ?
SELECT * FROM EMP E WHERE E.SAL >= 1500 AND E.SAL <= 3000;
-- 是不是太麻煩呢 ? 簡化一下吧 使用 between ... and 
SELECT * FROM EMP E WHERE E.SAL BETWEEN 1500 AND 3000;

-- between ... and 還可以查詢時間日期
-- 查詢1981年入職的員工
SELECT * FROM EMP E WHERE E.HIREDATE BETWEEN '1-1月-1981' AND '31-12月-1981';
-- 時間為什麼要這麼寫 ? 這是預設格式啦 ,能改麼? 當然可以 !
SELECT * FROM EMP E WHERE E.HIREDATE BETWEEN DATE'1981-01-01' AND DATE'1981-12-31';
-- 是不是很神奇 ? 
-- 屁啦 Sql Server MySQL 連 DATE 都不用加 ! 哎 !

-- 查詢條件中 值 是區分大小寫的 ! 
-- 查詢叫 scott 的員工
SELECT * FROM EMP E WHERE E.ENAME = 'scott';
-- 查不到 為啥 ? 因為 scott 是區分大小寫的 !
SELECT * FROM EMP E WHERE E.ENAME = 'SCOTT';

-- 關鍵字 IN 的用法
-- 查詢員工編號為 7369 7499 7521 三個員工的姓名
SELECT * FROM EMP E WHERE E.EMPNO = 7369 OR E.EMPNO = 7499 OR E.EMPNO = 7521;
-- 是不是有點麻煩 ? 同一列的 不同值而已嘛 改一下
SELECT * FROM EMP E WHERE E.EMPNO IN (7369,7499,7521);
-- 簡單吧 但是要注意用法 同一列 不同值 可以用 IN 圈查
-- 不同值中間要以逗號隔開
-- 例如 , 查詢姓名為 SMITH WARD SCOTT 三個員工資訊
SELECT * FROM EMP E WHERE E.ENAME IN ('SMITH','WARD','SCOTT');

-- 模糊查詢
-- 記不清了 只能根據大概查詢 
-- 以 X 開頭 ( 例如 找個人叫 張XX 的 )
-- 以 X 結尾 ( 例如 找個人叫 XX明 的 )
-- 中間包含X 的 ( 例如 找下名字中包含 文 的 )
-- LIKE 關鍵字 % 匹配任意字元 _匹配一個字元
-- 查詢員工姓名第二個字母包含 M 的員工
SELECT * FROM EMP E WHERE E.ENAME LIKE '_M%';

-- LIKE 中沒有指定關鍵字 表示查詢全部
SELECT * FROM EMP E WHERE E.ENAME LIKE '%%';
-- 恕我直言 , 這麼寫簡直是腦殘寫法 不管是一個 % 還是兩個 %

-- 查詢名字中帶 M 的員工資訊
SELECT * FROM EMP E WHERE E.ENAME LIKE '%M%';
-- 經驗 : LIKE 使用不好很有可能導致查詢全部資料找你所需要的
-- 這樣查詢效率會很低噠 比如 LIKE '%X' LIKE '%X%' 都是不可以
-- 噠 這樣不行 所以一定要避免使用這樣的樣式 非要用 ? 不存在噠
-- 如果真的非要用 滾去了解索引去 !

-- Oracle 非等於 可以使用 <> 或者 !=
-- 員工資訊不是7369的員工資訊
SELECT * FROM EMP E WHERE E.EMPNO <> 7369;
-- 或者
SELECT * FROM EMP E WHERE E.EMPNO != 7369;


-- Oracle 對資料的排序可以使用 ORDER BY 的形式
-- 根據員工的工資高低展示資料 ( 從高到低展示 )
SELECT * FROM EMP E ORDER BY E.SAL DESC;
-- 從低到高呢 ?
SELECT * FROM EMP E ORDER BY E.SAL ASC;
-- ASC 從低到高排序是預設的 你可以不寫哈
SELECT * FROM EMP E ORDER BY E.SAL;

-- 那可以一起排序多個欄位麼 ? 當然可以呀
-- 根據工資和入職時間排序
SELECT * FROM EMP E ORDER BY E.SAL DESC,E.HIREDATE DESC;
-- 根據查詢結果可知 先按工資排序 如果工資相同再按時間排序

-- 排序中的 NULL 值問題
-- 比如按照獎金排序
SELECT * FROM EMP E ORDER BY E.COMM DESC;
-- 會出現一堆 NULL 排在前面 什麼鬼 ? 怎麼辦呢 ?
-- 可以使用 NULLS FIRST 和 NULLS LAST 
-- NULL 值放前面 一般不會這麼幹吧 ?
SELECT * FROM EMP E ORDER BY E.COMM DESC NULLS FIRST;
-- NULL 值放在後面 這樣可以噠
SELECT * FROM EMP E ORDER BY E.COMM DESC NULLS LAST;
-- 但是還是一堆 NULL 哦 能去掉不 ? 完全沒問題
SELECT * FROM EMP E WHERE E.COMM IS NOT NULL ORDER BY E.COMM DESC;
-- 呵呵呵呵 還有個 0 ? 不存在的
SELECT * FROM EMP E WHERE E.COMM IS NOT NULL AND E.COMM > 0 ORDER BY E.COMM DESC;

三.單行函式

-- 知識點 1 : 字串函式
-- 字串全轉成大寫
SELECT UPPER('hello,Oracle') FROM DUAL;
-- 逗號也大寫了 你能看出來麼? 屁啦 騙人噠
-- 字串全轉成小寫
SELECT LOWER('HELLO,ORACLE') FROM DUAL;
-- 逗號也小..... 啪 .. 已打死

-- 字串拼接
SELECT CONCAT('Hello','Oracle') FROM DUAL;
-- 拼接多個怎麼辦
SELECT 'Hello'||','||'Oracle' FROM DUAL;
-- 神 + 奇 + 不 + ? + 
-- 但 + 是 + 我 + 還 + 是 + 喜 + 歡 + 加 + 號 (可惜用不了)

-- 字串擷取
SELECT SUBSTR('Hello,Oracle!',1,3) FROM DUAL;
-- 索引 1 和 0 都一樣 Oracle 還真是照顧我們呢 !
SELECT SUBSTR('Hello,Oracle!',0,3) FROM DUAL;

-- 獲取字串的長度
SELECT LENGTH('Hello,Oracle') FROM DUAL;
-- 是不是很簡單 但是 SQL Server 是 len() 函式 笑著面對 挺萌的 !

-- 字元替換
SELECT REPLACE('hello,Oracle','l','x') FROM DUAL;



-- 知識點 2 : 數值函式
-- 可以四捨五入的 , 很實用呀 
SELECT ROUND(46.578,2) FROM DUAL;
-- 直接幹掉 多少也不要的 土豪的做法
SELECT TRUNC(46.578,2) FROM DUAL;
-- 取餘數的 , 很實用呀 , 分頁儲存過程用這個可以
SELECT MOD(5,2) FROM DUAL;


-- 知識點 3 : 日期函式
-- 計算員工入職的週數
SELECT E.ENAME,ROUND((SYSDATE - HIREDATE)/7) FROM EMP E;

-- 獲得兩個時間段中的月數
-- 計算員工入職的月數
SELECT E.ENAME,ROUND(MONTHS_BETWEEN(SYSDATE,HIREDATE)) FROM EMP E;

-- 獲得幾個月後的日期
-- 獲得一個月後的日期
SELECT ADD_MONTHS(SYSDATE,1) FROM EMP E; 


-- 知識點 4 : 轉換函式
-- 日期轉字串
-- 年月日 時分秒
SELECT TO_CHAR(SYSDATE,'yyyy-MM-dd hh24:mm:ss') FROM DUAL;

-- 獲得星期幾
SELECT TO_CHAR(SYSDATE,'DAY') FROM DUAL;

-- 數字轉字串
-- 9 屬於數字佔位符 一定要長 省得位數不夠 
SELECT TO_CHAR(10000.00,'L999,999,999,999.00') FROM DUAL;

-- 字串轉時間
SELECT TO_DATE('2017-12-12','yyyy-MM-dd') FROM DUAL;

-- 字串轉數字
SELECT TO_NUMBER('100.00') FROM DUAL;


-- 知識點 5: 通用函式
-- 處理 NULL
-- NVL 如果目標為 NULL 取第二個數值
SELECT NVL(E.COMM,0) FROM EMP E;
-- NVL2 如果目標不為 NULL 取第一個 為 NULL 取第二個
SELECT NVL2(E.COMM,E.COMM,0) FROM EMP E;
-- NULLIF 如果兩個值相等 返回一個 NULL 我的天 什麼鬼 ?
SELECT NULLIF(E.COMM,E.COMM) FROM EMP E;
-- COALESCE 配置多個表示式 返回一個為空的 如果都為空 則返回空
SELECT COALESCE(NULL,1,NULL,2) FROM DUAL;
-- 計算年薪 基本工資*12 + 獎金
SELECT E.ENAME 員工名稱,E.SAL*12+NVL(E.COMM,0) 年薪 FROM EMP E;

-- 知識點6 : 條件表示式
-- 計算員工的工資等級
-- 條件匹配版
SELECT E.ENAME 姓名,E.SAL 薪資,
CASE 
WHEN E.SAL >= 500 AND E.SAL < 1500 THEN 1 
WHEN E.SAL >= 1500 AND E.SAL < 2000 THEN 2 
WHEN E.SAL >= 2000 AND E.SAL < 2500 THEN 3 
WHEN E.SAL >= 3000 AND E.SAL < 3500 THEN 4 
ELSE 5 
END 薪資等級 
FROM EMP E;
-- CASE WHEN THEN END 結構 跟 Java switch 一樣
-- 結果匹配版
SELECT E.ENAME 姓名,E.SAL 薪資,
DECODE(
ROUND(E.SAL/1000),
0,0.00,
1,0.09,
2,0.20,
3,0.30,
4,0.40,
5,0.42,
6,0.44,
0.45
) 手續費
FROM EMP E;

四.多行函式

-- 多行函式

-- 計數器 : 統計個數
SELECT COUNT(EMPNO) FROM EMP;
-- COUNT(*) 包含所有列再計數
-- COUNT(1) 包含 N 多行1再計數 相當於佔位符
-- COUNT(主鍵) 包含 N 多行主鍵ID 再計數 因為主鍵上有索引,查詢是
-- 最快的

-- 最小值 : 最低工資
SELECT MIN(SAL) FROM EMP;

-- 最大值 : 最高工資
SELECT MAX(SAL) FROM EMP;

-- 平均值 : 平均工資
SELECT AVG(SAL) FROM EMP;
--有可能除不開 所以四捨五入
SELECT ROUND(AVG(SAL)) FROM EMP E;

-- 求和 : 看看一個月薪資的支出
SELECT SUM(SAL) FROM EMP;

-- 分組查詢 : 看看每個部門有多少個人
SELECT DEPTNO,COUNT(EMPNO) FROM EMP E GROUP BY DEPTNO;
-- 一旦分組 只能查詢分組欄位和聚合函式 不能查其他欄位
-- 不信 ?  不信你試試 select * 

-- 過濾分組 : 查詢平均工資大於 2000的部門編號和人員個數
SELECT E.DEPTNO,COUNT(E.EMPNO) FROM EMP E GROUP BY E.DEPTNO HAVING AVG(E.SAL) > 2000;

-- HAVING 最好不用 因為分組都已經很累啦 你居然還要過濾
-- 是不是*  是不是 *