基礎查詢語法
-- 查詢所有欄位
SELECT * FROM tablename;
-- 範例
SELECT * FROM employees;
-- 指定欄位查詢
SELECT select_list FROM tablename;
SELECT employee_id, first_name FROM employees;
-- 去重查詢
SELECT DISTINCT column FROM tablename;
SELECT DISTINCT manager_id FROM employees;
字串連接與算術運算
-- 連接符 ||、四則運算、括號
SELECT employee_id, first_name || '.' || last_name, salary * (1 + 0.1) / 100, manager_id * 2 FROM employees;
-- 正負號
SELECT -salary FROM employees;
比較與邏輯運算符
-- 比較運算符
SELECT * FROM employees WHERE salary > 13000;
SELECT * FROM employees WHERE salary < 13000;
SELECT * FROM employees WHERE salary <> 13000; -- 不等於
SELECT * FROM employees WHERE salary = 13000;
-- IN / NOT IN
SELECT -salary FROM employees WHERE employee_id IN (100, 101, 102);
SELECT -salary FROM employees WHERE employee_id IN (SELECT employee_id FROM employees);
SELECT -salary FROM employees WHERE employee_id NOT IN (100, 101, 102);
-- ANY / SOME / ALL
SELECT * FROM employees WHERE employee_id > ANY (100, 101, 102); -- 大於任意一個
SELECT * FROM employees WHERE employee_id > ALL (100, 101, 102); -- 大於所有
-- BETWEEN / NOT BETWEEN
SELECT * FROM employees WHERE employee_id BETWEEN 100 AND 102;
SELECT * FROM employees WHERE employee_id NOT BETWEEN 100 AND 102;
-- 邏輯運算
SELECT * FROM employees WHERE employee_id > 100 AND employee_id < 1000;
SELECT * FROM employees WHERE employee_id > 100 OR employee_id < 1000;
排序與虛擬表
-- ORDER BY
SELECT * FROM employees WHERE employee_id BETWEEN 100 AND 102 ORDER BY employee_id DESC;
SELECT * FROM employees WHERE employee_id BETWEEN 100 AND 102 ORDER BY employee_id ASC;
-- DUAL 虛擬表（無需實表時使用）
SELECT SYSDATE FROM DUAL;
SELECT 1*2*3*4*5 FROM DUAL;
