基本查詢
-- 查詢所有員工
SELECT * FROM emp;
-- 格式化輸出
SET LINESIZE 120;
COL empno FOR 9999;
COL ename FOR A8;
SET PAGESIZE 30;
-- 算術運算與空值處理
SELECT empno, ename, sal, sal*12, comm, sal*12+NVL(comm,0) FROM emp;
-- 別名三種寫法
SELECT empno AS "員工編號", ename "姓名", sal 月薪, comm, sal*12+NVL(comm,0) 年收入 FROM emp;
-- DISTINCT 去重
SELECT DISTINCT deptno FROM emp;
SELECT DISTINCT deptno, job FROM emp; -- 兩欄同時相同才視為重複
-- 字串串接
SELECT ename || '的薪水是' || sal FROM emp;
過濾與排序
-- 日期格式設定
ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD';
-- 隱含轉換 (影響效能)
SELECT * FROM emp WHERE hiredate = '1981-11-17';
-- 顯式轉換 (建議)
SELECT * FROM emp WHERE hiredate = TO_DATE('1981-11-17','YYYY-MM-DD');
-- BETWEEN 含邊界
SELECT * FROM emp WHERE sal BETWEEN 1000 AND 2000;
-- IN 集合
SELECT * FROM emp WHERE deptno IN (10,20);
-- LIKE 模糊查詢
SELECT * FROM emp WHERE ename LIKE 'S%'; -- S 開頭
SELECT * FROM emp WHERE ename LIKE '____'; -- 四個字元
SELECT * FROM emp WHERE ename LIKE '%_%' ESCAPE '\'; -- 含底線 (需跳脫)
-- 空值判斷
SELECT * FROM emp WHERE comm IS NOT NULL;
-- ORDER BY 排序
SELECT * FROM emp ORDER BY sal; -- 遞增
SELECT * FROM emp ORDER BY sal DESC; -- 遞減
SELECT empno, ename, sal, sal*12 年薪 FROM emp ORDER BY 年薪; -- 別名
SELECT empno, ename, sal, sal*12 FROM emp ORDER BY 4; -- 位置編號
SELECT * FROM emp ORDER BY deptno, sal; -- 多欄:先 deptno 再 sal
SELECT * FROM emp ORDER BY deptno DESC, sal DESC; -- 每欄需個別加 DESC
SELECT * FROM emp ORDER BY comm DESC NULLS LAST; -- 空值排最後
-- 關閉/開啟回饋訊息
SET FEEDBACK OFF / ON
排序規則摘要
- 可用欄位名、別名、SELECT 清單位置編號。
- 多欄排序:依第一欄排序,相同才比第二欄,以此類推。