-- 创建数据库
CREATE DATABASE mydb
    WITH
    OWNER = myuser
    ENCODING = 'UTF8'
    LC_COLLATE = 'en_US.UTF-8'
    LC_CTYPE = 'en_US.UTF-8'
    TEMPLATE = template0
    CONNECTION LIMIT = 100;

-- 查看所有数据库
\l

-- 修改数据库
ALTER DATABASE mydb SET timezone = 'Asia/Shanghai';

-- 删除数据库（危险操作！）
DROP DATABASE IF EXISTS mydb;

-- 重命名数据库
ALTER DATABASE mydb RENAME TO newdb;

-- 创建表
CREATE TABLE employees (
    id          BIGSERIAL       PRIMARY KEY,
    name        VARCHAR(100)    NOT NULL,
    email       VARCHAR(255)    UNIQUE NOT NULL,
    age         SMALLINT        CHECK (age >= 0 AND age <= 150),
    salary      NUMERIC(12, 2)  DEFAULT 0.00,
    department  VARCHAR(50),
    hire_date   DATE            NOT NULL DEFAULT CURRENT_DATE,
    bio         TEXT,
    is_active   BOOLEAN         DEFAULT TRUE,
    created_at  TIMESTAMPTZ     DEFAULT NOW(),
    updated_at  TIMESTAMPTZ     DEFAULT NOW()
);

-- 添加列
ALTER TABLE employees ADD COLUMN phone VARCHAR(20);

-- 修改列类型
ALTER TABLE employees ALTER COLUMN phone TYPE VARCHAR(30);

-- 添加 NOT NULL 约束
ALTER TABLE employees ALTER COLUMN name SET NOT NULL;

-- 删除列
ALTER TABLE employees DROP COLUMN IF EXISTS phone;

-- 重命名列
ALTER TABLE employees RENAME COLUMN name TO full_name;

-- 添加约束
ALTER TABLE employees ADD CONSTRAINT chk_salary
    CHECK (salary >= 0);

-- 删除约束
ALTER TABLE employees DROP CONSTRAINT IF EXISTS chk_salary;

-- 创建索引
CREATE INDEX idx_emp_email ON employees (email);
CREATE INDEX idx_emp_dept ON employees (department) WHERE is_active = TRUE;

-- 删除表
DROP TABLE IF EXISTS employees CASCADE;

-- 截断表（清空数据，保留结构）
TRUNCATE TABLE employees RESTART IDENTITY;

-- IF EXISTS / IF NOT EXISTS（防止报错）
CREATE TABLE IF NOT EXISTS t1 (id INT);
DROP TABLE IF EXISTS t1;

-- CREATE TABLE AS（从查询结果建表）
CREATE TABLE emp_backup AS SELECT * FROM employees WHERE hire_date < '2024-01-01';

-- CREATE TABLE ... LIKE（复制表结构）
CREATE TABLE emp_like (LIKE employees INCLUDING ALL);

-- 自增主键的三种方式
-- 方式 1：BIGSERIAL（传统方式）
CREATE TABLE t1 (id BIGSERIAL PRIMARY KEY, name TEXT);

-- 方式 2：GENERATED ALWAYS AS IDENTITY（SQL 标准，推荐）
CREATE TABLE t2 (id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name TEXT);

-- 方式 3：GENERATED BY DEFAULT AS IDENTITY（允许手动指定值）
CREATE TABLE t3 (id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, name TEXT);

-- 单行插入
INSERT INTO employees (name, email, age, salary, department, hire_date)
VALUES ('张三', 'zhangsan@example.com', 30, 15000.00, '工程部', '2024-01-15');

-- 多行插入
INSERT INTO employees (name, email, age, salary, department, hire_date) VALUES
    ('李四', 'lisi@example.com', 28, 12000.00, '产品部', '2024-02-01'),
    ('王五', 'wangwu@example.com', 35, 20000.00, '工程部', '2023-06-15'),
    ('赵六', 'zhaoliu@example.com', 26, 10000.00, '设计部', '2024-03-10');

-- 从查询结果插入
INSERT INTO emp_backup (name, email, salary)
SELECT name, email, salary FROM employees WHERE department = '工程部';

-- UPSERT（INSERT ... ON CONFLICT）— PG 独有亮点
INSERT INTO employees (name, email, age, salary, department, hire_date)
VALUES ('张三', 'zhangsan@example.com', 31, 16000.00, '工程部', '2024-01-15')
ON CONFLICT (email)
DO UPDATE SET
    age = EXCLUDED.age,
    salary = EXCLUDED.salary,
    updated_at = NOW();

-- ON CONFLICT DO NOTHING（忽略冲突）
INSERT INTO employees (name, email, age, salary, department, hire_date)
VALUES ('张三', 'zhangsan@example.com', 31, 16000.00, '工程部', '2024-01-15')
ON CONFLICT DO NOTHING;

-- RETURNING 子句（返回受影响的行）
INSERT INTO employees (name, email, age, salary, department, hire_date)
VALUES ('钱七', 'qianqi@example.com', 29, 13000.00, '市场部', '2024-04-01')
RETURNING id, name;

-- 基本更新
UPDATE employees
SET salary = salary * 1.10
WHERE department = '工程部';

-- 多列更新
UPDATE employees
SET salary = 25000.00, department = '技术管理部', updated_at = NOW()
WHERE name = '王五';

-- 基于其他表的更新（使用 FROM）
UPDATE employees e
SET salary = e.salary * (1 + r.raise_rate)
FROM salary_raises r
WHERE e.id = r.emp_id AND r.effective_date = CURRENT_DATE;

-- RETURNING
UPDATE employees
SET is_active = FALSE
WHERE hire_date < '2020-01-01'
RETURNING id, name, is_active;

-- 基本删除
DELETE FROM employees WHERE id = 42;

-- 使用子查询删除
DELETE FROM employees
WHERE id IN (
    SELECT e.id FROM employees e
    LEFT JOIN departments d ON e.department = d.name
    WHERE d.name IS NULL
);

-- 使用 USING（多表关联删除）
DELETE FROM employees e
USING departments d
WHERE e.department = d.name AND d.is_archived = TRUE;

-- TRUNCATE（清空整表，比 DELETE 快得多）
TRUNCATE TABLE temp_data RESTART IDENTITY CASCADE;

-- MERGE：根据条件执行 INSERT/UPDATE/DELETE
MERGE INTO target_table t
USING source_table s ON t.id = s.id
WHEN MATCHED AND s.is_deleted THEN
    DELETE
WHEN MATCHED THEN
    UPDATE SET t.name = s.name, t.updated_at = NOW()
WHEN NOT MATCHED THEN
    INSERT (id, name, created_at)
    VALUES (s.id, s.name, NOW());

-- 基本查询
SELECT id, name, salary FROM employees;

-- 去重
SELECT DISTINCT department FROM employees;

-- 别名
SELECT
    name AS 姓名,
    salary AS 月薪,
    salary * 12 AS 年薪
FROM employees;

-- 条件过滤
SELECT * FROM employees
WHERE department = '工程部'
  AND salary > 10000
  AND is_active = TRUE;

-- IN / NOT IN
SELECT * FROM employees WHERE department IN ('工程部', '产品部');

-- BETWEEN
SELECT * FROM employees WHERE salary BETWEEN 10000 AND 20000;

-- LIKE / ILIKE（PG 的 ILIKE 大小写不敏感）
SELECT * FROM employees WHERE name LIKE '张%';
SELECT * FROM employees WHERE email ILIKE '%@example.com';

-- NULL 判断
SELECT * FROM employees WHERE bio IS NULL;
SELECT * FROM employees WHERE bio IS NOT NULL;

-- COALESCE（空值替换）
SELECT name, COALESCE(bio, '暂无简介') AS bio FROM employees;

-- CASE WHEN（条件表达式）
SELECT name, salary,
    CASE
        WHEN salary >= 20000 THEN '高薪'
        WHEN salary >= 10000 THEN '中等'
        ELSE '初级'
    END AS salary_level
FROM employees;

-- 排序
SELECT * FROM employees ORDER BY salary DESC, name ASC;

-- 分页
SELECT * FROM employees ORDER BY id LIMIT 20 OFFSET 40;
-- 第 3 页（每页 20 条）

-- LIMIT ... WITH TIES（PG 语法，包含并列行）
SELECT name, salary FROM employees
ORDER BY salary DESC
LIMIT 5 WITH TIES;

-- 常用聚合函数
SELECT
    COUNT(*) AS total_count,
    COUNT(DISTINCT department) AS dept_count,
    AVG(salary)::NUMERIC(12,2) AS avg_salary,
    MIN(salary) AS min_salary,
    MAX(salary) AS max_salary,
    SUM(salary) AS total_salary,
    STRING_AGG(name, ', ') AS all_names
FROM employees;

-- GROUP BY
SELECT
    department,
    COUNT(*) AS emp_count,
    AVG(salary)::NUMERIC(12,2) AS avg_salary
FROM employees
GROUP BY department;

-- HAVING（过滤分组结果）
SELECT
    department,
    AVG(salary)::NUMERIC(12,2) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 15000;

-- 创建示例表
CREATE TABLE departments (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) UNIQUE NOT NULL,
    budget NUMERIC(15,2)
);

INSERT INTO departments (name, budget) VALUES
    ('工程部', 500000),
    ('产品部', 300000),
    ('设计部', 200000),
    ('市场部', 250000);

-- INNER JOIN（内连接）
SELECT e.name, e.salary, d.budget
FROM employees e
INNER JOIN departments d ON e.department = d.name;

-- LEFT JOIN（左连接，保留左表全部行）
SELECT e.name, e.salary, d.budget
FROM employees e
LEFT JOIN departments d ON e.department = d.name;

-- RIGHT JOIN（右连接）
SELECT e.name, d.name AS dept, d.budget
FROM employees e
RIGHT JOIN departments d ON e.department = d.name;

-- FULL OUTER JOIN（全外连接）
SELECT e.name, d.name AS dept
FROM employees e
FULL OUTER JOIN departments d ON e.department = d.name;

-- CROSS JOIN（笛卡尔积）
SELECT e.name, d.name FROM employees e CROSS JOIN departments d;

-- SELF JOIN（自连接）
SELECT e1.name AS employee, e2.name AS colleague
FROM employees e1
INNER JOIN employees e2 ON e1.department = e2.department AND e1.id <> e2.id;

-- 标量子查询
SELECT name, salary, (SELECT AVG(salary) FROM employees)::NUMERIC(12,2) AS avg_salary
FROM employees;

-- IN 子查询
SELECT * FROM employees
WHERE department IN (SELECT name FROM departments WHERE budget > 300000);

-- EXISTS 子查询
SELECT * FROM departments d
WHERE EXISTS (SELECT 1 FROM employees e WHERE e.department = d.name);

-- FROM 子句中的子查询（派生表）
SELECT dept, avg_sal FROM (
    SELECT department AS dept, AVG(salary) AS avg_sal
    FROM employees
    GROUP BY department
) sub
WHERE avg_sal > 10000;

-- RETURNING 子句（INSERT/UPDATE/DELETE 后返回结果）
DELETE FROM employees WHERE id = 42 RETURNING *;

-- ILIKE（大小写不敏感 LIKE）
SELECT * FROM employees WHERE name ILIKE '%张%';

-- 类型转换（双冒号语法）
SELECT '123'::INTEGER, '2024-01-15'::DATE, '{"a":1}'::JSONB;

-- ARRAY 操作
SELECT ARRAY[1,2,3] || ARRAY[4,5];  -- 数组拼接：{1,2,3,4,5}
SELECT ARRAY[1,2,3,4] @> ARRAY[2,3]; -- 包含判断：true

-- 任意值比较
SELECT * FROM employees WHERE salary > ALL (SELECT AVG(salary) FROM employees GROUP BY department);

-- 值构造器（VALUES）
VALUES (1, 'a'), (2, 'b'), (3, 'c');

-- GROUPING SETS / ROLLUP / CUBE
SELECT department, is_active, COUNT(*)
FROM employees
GROUP BY ROLLUP(department, is_active);