查询与过滤

-- 创建练习数据库
CREATE DATABASE practice;
\c practice

-- 创建员工表
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    department VARCHAR(30),
    salary NUMERIC(10,2),
    hire_date DATE,
    is_active BOOLEAN DEFAULT true
);

-- 插入测试数据
INSERT INTO employees (name, department, salary, hire_date, is_active) VALUES
    ('张三', '技术部', 15000, '2023-01-15', true),
    ('李四', '技术部', 18000, '2022-06-01', true),
    ('王五', '市场部', 12000, '2023-03-10', true),
    ('赵六', '市场部', 11000, '2024-01-20', true),
    ('钱七', '技术部', 22000, '2021-09-01', true),
    ('孙八', '人事部', 9000, '2024-07-15', true),
    ('周九', '人事部', 9500, '2023-11-01', true),
    ('吴十', '技术部', 16000, '2023-06-20', false),
    ('郑一', '市场部', 10000, '2024-09-01', true),
    ('陈二', '财务部', 13000, '2022-12-01', true);

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

-- 查询指定列
SELECT name, salary FROM employees;

-- 列别名
SELECT name AS 姓名, salary AS 工资 FROM employees;

-- 列计算
SELECT name, salary, salary * 1.1 AS 涨薪10后 FROM employees;

-- 查询有哪些部门（去重）
SELECT DISTINCT department FROM employees;

-- 多列去重
SELECT DISTINCT department, is_active FROM employees;

-- 精确匹配
SELECT * FROM employees WHERE department = '技术部';

-- 范围比较
SELECT * FROM employees WHERE salary > 15000;
SELECT * FROM employees WHERE salary >= 15000 AND salary <= 20000;
SELECT * FROM employees WHERE salary BETWEEN 10000 AND 15000;  -- 包含边界

-- AND：所有条件都满足
SELECT * FROM employees 
WHERE department = '技术部' AND salary > 15000;

-- OR：任一条件满足
SELECT * FROM employees 
WHERE department = '技术部' OR department = '市场部';

-- NOT：取反
SELECT * FROM employees 
WHERE NOT department = '财务部';

-- 组合括号
SELECT * FROM employees 
WHERE (department = '技术部' OR department = '市场部')
  AND salary > 12000;

-- 等价于多个 OR
SELECT * FROM employees 
WHERE department IN ('技术部', '市场部', '财务部');

-- 也可以 NOT IN
SELECT * FROM employees 
WHERE department NOT IN ('技术部', '市场部');

-- % 匹配任意字符
SELECT * FROM employees WHERE name LIKE '张%';   -- 以"张"开头
SELECT * FROM employees WHERE name LIKE '%部';   -- 以"部"结尾
SELECT * FROM employees WHERE name LIKE '%五%';  -- 包含"五"

-- _ 匹配单个字符
SELECT * FROM employees WHERE name LIKE '张_';    -- "张"加一个字符

-- ILIKE 忽略大小写
SELECT * FROM employees WHERE name ILIKE 'zhang%';

-- IS NULL / IS NOT NULL
SELECT * FROM employees WHERE salary IS NULL;
SELECT * FROM employees WHERE salary IS NOT NULL;

-- 注意：NULL = 0 或 NULL = '' 都不对
-- NULL 和任何值比较都返回 FALSE（包括 NULL = NULL）

-- COALESCE：NULL 替换为默认值
SELECT name, COALESCE(salary, 0) AS 工资 FROM employees;

-- NULLIF：如果两个值相等则返回 NULL
SELECT NULLIF(salary, 0) FROM employees;

-- WHERE 中使用函数结果
SELECT * FROM employees 
WHERE EXTRACT(YEAR FROM hire_date) = 2023;

SELECT * FROM employees 
WHERE hire_date >= '2023-01-01' AND hire_date < '2024-01-01';

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

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

-- 多列排序：先按部门升序，同部门按工资降序
SELECT * FROM employees 
ORDER BY department ASC, salary DESC;

-- 按别名排序
SELECT name, salary * 12 AS 年收入 
FROM employees 
ORDER BY 年收入 DESC;

-- NULL 排序控制
SELECT * FROM employees 
ORDER BY salary NULLS LAST;   -- NULL 排最后（默认）

-- 取前 3 条
SELECT * FROM employees ORDER BY salary DESC LIMIT 3;

-- 跳过前 3 条，取 3 条（第 4-6 条）
SELECT * FROM employees ORDER BY salary DESC LIMIT 3 OFFSET 3;

-- 简洁写法（MySQL/PostgreSQL 兼容语法糖）
SELECT * FROM employees ORDER BY salary DESC LIMIT 3 OFFSET 3;

-- 键集分页（高效，适合大表）
SELECT * FROM employees
ORDER BY salary DESC, id
WHERE (salary, id) < (上一个salary, 上一个id)
ORDER BY salary DESC, id
LIMIT 3;

-- 1. 技术部工资 > 15000 的活跃员工
SELECT name, salary, hire_date 
FROM employees 
WHERE department = '技术部' 
  AND salary > 15000 
  AND is_active = true
ORDER BY salary DESC;

-- 2. 2023 年入职的员工
SELECT name, department, hire_date 
FROM employees 
WHERE hire_date BETWEEN '2023-01-01' AND '2023-12-31'
ORDER BY hire_date;

-- 3. 除了技术部和市场部的员工
SELECT name, department, salary 
FROM employees 
WHERE department NOT IN ('技术部', '市场部')
ORDER BY salary DESC;

-- 4. 姓名中含"五"且工资超过 10000
SELECT * FROM employees 
WHERE name LIKE '%五%' AND salary > 10000;

-- 5. 各部门工资最高的员工
SELECT DISTINCT ON (department) 
    department, name, salary
FROM employees
ORDER BY department, salary DESC;