Oracle基础查询 #

-- SELECT语句基本语法
SELECT [DISTINCT] column_list
FROM table_name
[WHERE condition]
[GROUP BY column_list]
[HAVING group_condition]
[ORDER BY column_list];

-- 创建示例表
CREATE TABLE employees (
    employee_id NUMBER(6) PRIMARY KEY,
    first_name VARCHAR2(50),
    last_name VARCHAR2(50),
    email VARCHAR2(100),
    phone_number VARCHAR2(20),
    hire_date DATE,
    job_id VARCHAR2(10),
    salary NUMBER(8,2),
    commission_pct NUMBER(2,2),
    manager_id NUMBER(6),
    department_id NUMBER(4)
);

-- 插入测试数据
INSERT INTO employees VALUES (1, 'John', 'Doe', 'john@example.com', '123-456-7890', 
    TO_DATE('2020-01-15', 'YYYY-MM-DD'), 'IT_PROG', 8000, NULL, 100, 10);
INSERT INTO employees VALUES (2, 'Jane', 'Smith', 'jane@example.com', '123-456-7891', 
    TO_DATE('2019-03-20', 'YYYY-MM-DD'), 'SA_REP', 9000, 0.1, 101, 20);
INSERT INTO employees VALUES (3, 'Bob', 'Johnson', 'bob@example.com', '123-456-7892', 
    TO_DATE('2021-06-10', 'YYYY-MM-DD'), 'IT_PROG', 7000, NULL, 100, 10);
INSERT INTO employees VALUES (4, 'Alice', 'Williams', 'alice@example.com', '123-456-7893', 
    TO_DATE('2018-11-05', 'YYYY-MM-DD'), 'SA_MAN', 12000, 0.2, 102, 30);
INSERT INTO employees VALUES (5, 'Charlie', 'Brown', 'charlie@example.com', '123-456-7894', 
    TO_DATE('2022-02-28', 'YYYY-MM-DD'), 'SA_REP', 6500, 0.15, 101, 20);

-- 查询所有列
SELECT * FROM employees;

-- 结果
EMPLOYEE_ID FIRST_NAME LAST_NAME  EMAIL              PHONE_NUMBER  HIRE_DATE  JOB_ID   SALARY
----------- ---------- ---------- ------------------ ------------- ---------- -------- ------
          1 John       Doe        john@example.com   123-456-7890  2020-01-15 IT_PROG   8000
          2 Jane       Smith      jane@example.com   123-456-7891  2019-03-20 SA_REP    9000
          3 Bob        Johnson    bob@example.com    123-456-7892  2021-06-10 IT_PROG   7000
          4 Alice      Williams   alice@example.com  123-456-7893  2018-11-05 SA_MAN   12000
          5 Charlie    Brown      charlie@example.com 123-456-7894  2022-02-28 SA_REP    6500

-- 查询指定列
SELECT employee_id, first_name, last_name, salary
FROM employees;

-- 使用列别名
SELECT 
    employee_id AS emp_id,
    first_name AS "名",
    last_name AS "姓",
    salary AS "薪资"
FROM employees;

-- 省略AS关键字
SELECT employee_id emp_id, first_name name FROM employees;

-- 使用表达式
SELECT 
    employee_id,
    first_name || ' ' || last_name AS full_name,
    salary,
    salary * 12 AS annual_salary
FROM employees;

-- 查询所有部门ID（包含重复）
SELECT department_id FROM employees;

-- 查询不重复的部门ID
SELECT DISTINCT department_id FROM employees;

-- 多列去重
SELECT DISTINCT department_id, job_id FROM employees;

-- 统计去重数量
SELECT COUNT(DISTINCT department_id) AS dept_count FROM employees;

-- 等于
SELECT * FROM employees WHERE department_id = 10;

-- 不等于
SELECT * FROM employees WHERE department_id != 10;
SELECT * FROM employees WHERE department_id <> 10;

-- 大于、小于
SELECT * FROM employees WHERE salary > 8000;
SELECT * FROM employees WHERE salary < 8000;
SELECT * FROM employees WHERE salary >= 8000;
SELECT * FROM employees WHERE salary <= 8000;

-- AND：同时满足多个条件
SELECT * FROM employees 
WHERE department_id = 10 AND salary > 7000;

-- OR：满足任一条件
SELECT * FROM employees 
WHERE department_id = 10 OR department_id = 20;

-- NOT：取反
SELECT * FROM employees WHERE NOT department_id = 10;

-- 组合使用（注意优先级）
SELECT * FROM employees 
WHERE (department_id = 10 OR department_id = 20) AND salary > 7000;

-- 范围查询（包含边界）
SELECT * FROM employees 
WHERE salary BETWEEN 7000 AND 10000;

-- 等价于
SELECT * FROM employees 
WHERE salary >= 7000 AND salary <= 10000;

-- NOT BETWEEN
SELECT * FROM employees 
WHERE salary NOT BETWEEN 7000 AND 10000;

-- 日期范围
SELECT * FROM employees 
WHERE hire_date BETWEEN TO_DATE('2020-01-01', 'YYYY-MM-DD') 
                    AND TO_DATE('2021-12-31', 'YYYY-MM-DD');

-- 列表匹配
SELECT * FROM employees 
WHERE department_id IN (10, 20, 30);

-- 等价于
SELECT * FROM employees 
WHERE department_id = 10 OR department_id = 20 OR department_id = 30;

-- NOT IN
SELECT * FROM employees 
WHERE department_id NOT IN (10, 20);

-- 子查询
SELECT * FROM employees 
WHERE department_id IN (
    SELECT department_id FROM departments WHERE location_id = 1700
);

-- 注意：NOT IN与NULL
-- 如果子查询返回NULL，NOT IN不会返回任何结果
SELECT * FROM employees 
WHERE department_id NOT IN (SELECT department_id FROM temp WHERE department_id IS NOT NULL);

-- % 匹配任意多个字符
SELECT * FROM employees WHERE first_name LIKE 'J%';    -- 以J开头
SELECT * FROM employees WHERE first_name LIKE '%n';    -- 以n结尾
SELECT * FROM employees WHERE first_name LIKE '%oh%';  -- 包含oh

-- _ 匹配单个字符
SELECT * FROM employees WHERE first_name LIKE 'J_hn';  -- John

-- 组合使用
SELECT * FROM employees WHERE email LIKE '%@example.com';

-- NOT LIKE
SELECT * FROM employees WHERE first_name NOT LIKE 'J%';

-- 转义特殊字符
SELECT * FROM employees WHERE email LIKE '%\_%' ESCAPE '\';  -- 匹配包含下划线

-- 大小写敏感
SELECT * FROM employees WHERE first_name LIKE 'JOHN';       -- 大小写敏感
SELECT * FROM employees WHERE UPPER(first_name) LIKE 'JOHN'; -- 大小写不敏感

-- IS NULL
SELECT * FROM employees WHERE commission_pct IS NULL;

-- IS NOT NULL
SELECT * FROM employees WHERE commission_pct IS NOT NULL;

-- 错误写法
SELECT * FROM employees WHERE commission_pct = NULL;    -- 不会返回结果
SELECT * FROM employees WHERE commission_pct != NULL;   -- 不会返回结果

-- NVL函数处理NULL
SELECT 
    employee_id,
    salary,
    NVL(commission_pct, 0) AS commission,
    salary + salary * NVL(commission_pct, 0) AS total_income
FROM employees;

-- NVL2函数
SELECT 
    employee_id,
    NVL2(commission_pct, '有提成', '无提成') AS has_commission
FROM employees;

-- COALESCE函数
SELECT 
    employee_id,
    COALESCE(commission_pct, 0) AS commission
FROM employees;

-- 升序排序（默认ASC）
SELECT * FROM employees ORDER BY salary;
SELECT * FROM employees ORDER BY salary ASC;

-- 降序排序
SELECT * FROM employees ORDER BY salary DESC;

-- 多列排序
SELECT * FROM employees 
ORDER BY department_id ASC, salary DESC;

-- 按列别名排序
SELECT employee_id, salary * 12 AS annual_salary
FROM employees
ORDER BY annual_salary DESC;

-- 按列位置排序
SELECT employee_id, first_name, salary
FROM employees
ORDER BY 3 DESC;  -- 按第3列（salary）排序

-- NULL值默认排在最后（升序）或最前（降序）
SELECT * FROM employees ORDER BY commission_pct;      -- NULL在最后
SELECT * FROM employees ORDER BY commission_pct DESC; -- NULL在最前

-- NULLS FIRST：NULL值排在最前
SELECT * FROM employees ORDER BY commission_pct NULLS FIRST;

-- NULLS LAST：NULL值排在最后
SELECT * FROM employees ORDER BY commission_pct DESC NULLS LAST;

-- 按表达式排序
SELECT employee_id, salary, commission_pct
FROM employees
ORDER BY salary + NVL(salary * commission_pct, 0) DESC;

-- 按函数结果排序
SELECT * FROM employees ORDER BY LENGTH(first_name);

-- 按CASE表达式排序
SELECT * FROM employees
ORDER BY CASE job_id
    WHEN 'SA_MAN' THEN 1
    WHEN 'SA_REP' THEN 2
    WHEN 'IT_PROG' THEN 3
    ELSE 4
END;

-- ROWNUM是Oracle的伪列，表示行号
SELECT ROWNUM, employee_id, first_name, salary
FROM employees;

-- ROWNUM从1开始
-- 注意：ROWNUM在ORDER BY之前分配

-- 错误：获取薪资最高的3人
SELECT ROWNUM, employee_id, salary
FROM employees
WHERE ROWNUM <= 3
ORDER BY salary DESC;  -- 先取前3行再排序，结果错误

-- 正确：使用子查询
SELECT *
FROM (
    SELECT employee_id, salary
    FROM employees
    ORDER BY salary DESC
)
WHERE ROWNUM <= 3;

-- 方法1：使用子查询
-- 第1页（每页3条）
SELECT *
FROM (
    SELECT a.*, ROWNUM rn
    FROM (
        SELECT * FROM employees ORDER BY employee_id
    ) a
    WHERE ROWNUM <= 3
)
WHERE rn > 0;

-- 第2页
SELECT *
FROM (
    SELECT a.*, ROWNUM rn
    FROM (
        SELECT * FROM employees ORDER BY employee_id
    ) a
    WHERE ROWNUM <= 6
)
WHERE rn > 3;

-- 通用分页公式
-- 第n页，每页m条
-- ROWNUM <= n * m
-- rn > (n - 1) * m

-- 方法2：使用ROW_NUMBER()（推荐）
SELECT *
FROM (
    SELECT 
        a.*,
        ROW_NUMBER() OVER (ORDER BY employee_id) AS rn
    FROM employees a
)
WHERE rn BETWEEN 4 AND 6;  -- 第2页，每页3条

-- 方法3：12c+使用OFFSET FETCH
SELECT * FROM employees
ORDER BY employee_id
OFFSET 3 ROWS FETCH NEXT 3 ROWS ONLY;  -- 第2页，每页3条

-- OFFSET...FETCH语法（Oracle 12c+）

-- 跳过前3条，获取接下来的3条
SELECT * FROM employees
ORDER BY employee_id
OFFSET 3 ROWS FETCH NEXT 3 ROWS ONLY;

-- 获取前3条
SELECT * FROM employees
ORDER BY employee_id
FETCH FIRST 3 ROWS ONLY;

-- 获取前30%
SELECT * FROM employees
ORDER BY employee_id
FETCH FIRST 30 PERCENT ROWS ONLY;

-- 使用WITH TIES包含并列行
SELECT * FROM employees
ORDER BY salary DESC
FETCH FIRST 3 ROWS WITH TIES;  -- 如果第4行薪资相同，也会返回

-- COUNT：计数
SELECT COUNT(*) FROM employees;                    -- 总行数
SELECT COUNT(employee_id) FROM employees;          -- 非NULL行数
SELECT COUNT(DISTINCT department_id) FROM employees; -- 去重计数
SELECT COUNT(commission_pct) FROM employees;       -- 非NULL计数

-- SUM：求和
SELECT SUM(salary) FROM employees;
SELECT SUM(salary * 12) AS annual_total FROM employees;

-- AVG：平均值
SELECT AVG(salary) FROM employees;
SELECT ROUND(AVG(salary), 2) FROM employees;  -- 保留2位小数

-- MAX/MIN：最大/最小值
SELECT MAX(salary), MIN(salary) FROM employees;
SELECT MAX(hire_date), MIN(hire_date) FROM employees;

-- 组合使用
SELECT 
    COUNT(*) AS total_count,
    SUM(salary) AS total_salary,
    ROUND(AVG(salary), 2) AS avg_salary,
    MAX(salary) AS max_salary,
    MIN(salary) AS min_salary
FROM employees;

-- 使用CASE进行条件聚合
SELECT 
    COUNT(*) AS total,
    SUM(CASE WHEN department_id = 10 THEN 1 ELSE 0 END) AS dept_10_count,
    SUM(CASE WHEN department_id = 20 THEN 1 ELSE 0 END) AS dept_20_count,
    SUM(CASE WHEN department_id = 30 THEN 1 ELSE 0 END) AS dept_30_count
FROM employees;

-- 条件求和
SELECT 
    SUM(salary) AS total_salary,
    SUM(CASE WHEN department_id = 10 THEN salary ELSE 0 END) AS dept_10_salary,
    SUM(CASE WHEN department_id = 20 THEN salary ELSE 0 END) AS dept_20_salary
FROM employees;

-- 条件平均
SELECT 
    AVG(CASE WHEN department_id = 10 THEN salary END) AS dept_10_avg,
    AVG(CASE WHEN department_id = 20 THEN salary END) AS dept_20_avg
FROM employees;

-- 按部门分组统计
SELECT 
    department_id,
    COUNT(*) AS employee_count,
    AVG(salary) AS avg_salary,
    MAX(salary) AS max_salary,
    MIN(salary) AS min_salary
FROM employees
GROUP BY department_id
ORDER BY department_id;

-- 多列分组
SELECT 
    department_id,
    job_id,
    COUNT(*) AS count,
    AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id, job_id
ORDER BY department_id, job_id;

-- 规则：SELECT中的非聚合列必须出现在GROUP BY中

-- 正确
SELECT department_id, COUNT(*)
FROM employees
GROUP BY department_id;

-- 错误：first_name不在GROUP BY中
-- SELECT department_id, first_name, COUNT(*)
-- FROM employees
-- GROUP BY department_id;

-- 正确：使用聚合函数
SELECT 
    department_id,
    MAX(first_name) AS first_name,  -- 取最大值
    COUNT(*) AS count
FROM employees
GROUP BY department_id;

-- HAVING：过滤分组后的结果
SELECT 
    department_id,
    COUNT(*) AS employee_count,
    AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 1;

-- HAVING与WHERE区别
-- WHERE：过滤行，在分组前执行
-- HAVING：过滤分组，在分组后执行

SELECT 
    department_id,
    COUNT(*) AS count,
    AVG(salary) AS avg_salary
FROM employees
WHERE salary > 5000  -- 先过滤薪资>5000的员工
GROUP BY department_id
HAVING AVG(salary) > 7000  -- 再过滤平均薪资>7000的部门
ORDER BY avg_salary DESC;

-- ROLLUP：生成分组小计和总计
SELECT 
    department_id,
    job_id,
    COUNT(*) AS count,
    SUM(salary) AS total_salary
FROM employees
GROUP BY ROLLUP(department_id, job_id);

-- 结果包含：
-- 1. 每个部门每个职位的统计
-- 2. 每个部门的小计
-- 3. 总计

-- CUBE：生成所有组合的小计
SELECT 
    department_id,
    job_id,
    COUNT(*) AS count
FROM employees
GROUP BY CUBE(department_id, job_id);

-- GROUPING SETS：指定分组组合
SELECT 
    department_id,
    job_id,
    COUNT(*) AS count
FROM employees
GROUP BY GROUPING SETS(
    (department_id),
    (job_id),
    (department_id, job_id),
    ()
);

-- GROUPING函数：标识是否为聚合行
SELECT 
    GROUPING(department_id) AS is_dept_total,
    department_id,
    GROUPING(job_id) AS is_job_total,
    job_id,
    COUNT(*) AS count
FROM employees
GROUP BY ROLLUP(department_id, job_id);

SELECT DISTINCT column_list
FROM table_name
[JOIN another_table ON condition]
[WHERE row_condition]
[GROUP BY column_list]
[HAVING group_condition]
[ORDER BY column_list];

1. FROM        → 确定数据来源
2. JOIN        → 连接表
3. ON          → 连接条件
4. WHERE       → 过滤行
5. GROUP BY    → 分组
6. HAVING      → 过滤分组
7. SELECT      → 选择列
8. DISTINCT    → 去重
9. ORDER BY    → 排序

SELECT department_id, COUNT(*) AS count, AVG(salary) AS avg_salary
FROM employees
WHERE salary >= 5000
GROUP BY department_id
HAVING COUNT(*) > 1
ORDER BY avg_salary DESC;

-- 执行顺序：
-- 1. FROM employees          → 获取employees表数据
-- 2. WHERE salary >= 5000    → 过滤薪资>=5000的行
-- 3. GROUP BY department_id  → 按department_id分组
-- 4. HAVING COUNT(*) > 1     → 过滤分组
-- 5. SELECT ...              → 选择列并计算聚合
-- 6. ORDER BY avg_salary     → 排序

-- DUAL是Oracle提供的单行单列表
SELECT * FROM DUAL;

-- 常用于计算、获取系统信息
SELECT 1 + 1 FROM DUAL;
SELECT SYSDATE FROM DUAL;
SELECT USER FROM DUAL;

-- 计算表达式
SELECT 10 * 5 FROM DUAL;

-- 获取系统日期
SELECT SYSDATE, SYSTIMESTAMP FROM DUAL;

-- 获取当前用户
SELECT USER FROM DUAL;

-- 类型转换测试
SELECT TO_DATE('2024-01-15', 'YYYY-MM-DD') FROM DUAL;

-- 获取序列值
SELECT seq_employee_id.NEXTVAL FROM DUAL;

-- 调用函数
SELECT 
    UPPER('hello'),
    LOWER('HELLO'),
    LENGTH('Oracle'),
    SUBSTR('Oracle', 1, 3)
FROM DUAL;