PostgreSQL基础操作指南：修改表结构、数据清理、视图管理、事务处理与锁机制
============== 在PostgreSQL中，用于修改现有表结构的指令集包含列增删、类型变更及约束管理功能。
语法结构
向现有表追加字段的指令格式：
ALTER TABLE employees ADD COLUMN join_date DATE;
移除表字段的指令格式：
ALTER TABLE employees DROP COLUMN birth_year;
修改字段数据类型的指令：
ALTER TABLE employees ALTER COLUMN salary TYPE NUMERIC(10,2);
添加非空约束的指令：
ALTER TABLE employees MODIFY email TEXT NOT NULL;
创建唯一性约束的指令：
ALTER TABLE orders
ADD CONSTRAINT unique_order_id UNIQUE(order_number);
添加检查约束的指令：
ALTER TABLE products
ADD CONSTRAINT price_check CHECK (unit_price > 0);
设置主键约束的指令：
ALTER TABLE customers
ADD CONSTRAINT pk_customer_id PRIMARY KEY (customer_id);
移除约束的指令：
ALTER TABLE orders
DROP CONSTRAINT unique_order_id;
MySQL环境下的等效指令：
ALTER TABLE orders
DROP INDEX unique_order_id;
移除主键的指令：
ALTER TABLE customers
DROP CONSTRAINT pk_customer_id;
MySQL环境下的等效指令：
ALTER TABLE customers
DROP PRIMARY KEY;
============== PostgreSQL的TRUNCATE命令用于清除表内数据但保留结构定义。
相比DROP TABLE命令，该操作保留表结构，避免了重建表的繁琐流程。与DELETE语句相比，其执行效率更高且直接释放存储空间，无需后续VACUUM处理。
基础语法
TRUNCATE TABLE sales_data;
======== 视图是基于SQL查询的虚拟表结构，实际存储的是查询定义而非物理数据。
特性包括：
- 提供定制化的数据访问视图
- 实现数据访问权限控制
- 支持多表数据聚合
- 仅支持只读操作（可通过触发器扩展）
创建视图
CREATE TEMP VIEW employee_summary AS
SELECT name, department, salary
FROM staff
WHERE salary > 50000;
删除视图
DROP VIEW employee_summary;
============== 事务是数据库操作的逻辑单元，确保数据一致性与并发控制。
遵循ACID特性：
- 原子性：操作全成功或全失败
- 一致性：保持数据完整性约束
- 隔离性：并发操作互不干扰
- 持久性：提交后数据永久保存
控制指令
BEGIN;
-- 数据操作语句
COMMIT; -- 或 END TRANSACTION
ROLLBACK;
示例场景
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;
COMMIT;
====== 锁机制用于保障并发操作下的数据一致性。
支持的锁模式：
- ACCESS SHARE：最小权限锁
- ROW EXCLUSIVE：行级排他锁
- SHARE UPDATE EXCLUSIVE：表级共享锁
- EXCLUSIVE：完全排他锁
- ACCESS EXCLUSIVE：最高权限锁
锁定语法
LOCK TABLE inventory IN EXCLUSIVE MODE;
死锁预防
建议按统一顺序获取锁资源，避免循环依赖。
咨询锁应用
SELECT pg_advisory_lock(12345);
-- 操作代码
SELECT pg_advisory_unlock(12345);
