E）
表是关系型数据库中最基本的对象，PostgreSQL支持丰富的数据类型与约束机制。
常用数据类型示例：
| 类型分类 | 常用数据类型 | 存储范围/特性 | 示例 |
| 数值型 | INT/BIGINT | 4/8字节整数 | 100,500 |
| 数值型 | DECIMAL(p,s) | 高精度十进制数 | DECIMAL(10,2)→1234.56 |
| 字符型 | VARCHAR(n) | 可变长字符串 | '数据分析' |
| 字符型 | TEXT | 无长度限制字符串 | 长文本内容 |
| 日期时间 | DATE | 年月日 | '20231231' |
| 日期时间 | TIMESTAMPWITHTIMEZONE | 带时区的时间戳 | '2024010110:00:00+08' |
| 布尔型 | BOOLEAN | 真/假 | TRUE/FALSE |
| 二进制 | BYTEA | 二进制数据 | \\xDEADBEEF |
创建表示例：
```sql
部门表
CREATETABLEdepartments(
dept_idINTEGERPRIMARYKEY,
dept_nameVARCHAR(50)
);
员工信息表（含多种约束）
CREATETABLEemployees(
emp_idSERIALPRIMARYKEY,自增主键
emp_nameVARCHAR(50)NOTNULL,非空约束
emailVARCHAR(100)UNIQUE,唯一约束
hire_dateDATEDEFAULTCURRENT_DATE,默认值约束
salaryNUMERIC(10,2)CHECK(salary0),检查约束
department_idINTEGERREFERENCESdepartments(dept_id)外键约束
);
```
约束类型对比：
| 约束类型 | 关键字 | 作用 | 性能影响 |
| 主键 | PRIMARYKEY | 唯一标识记录 | 读优化，写轻微影响 |
| 唯一 | UNIQUE | 确保字段值唯一 | 类似主键，允许NULL |
| 非空 | NOTNULL | 禁止字段为空 | 无索引性能影响 |
| 检查 | CHECK | 自定义逻辑约束 | 每次写入时触发检查 |
| 外键 | REFERENCES | 建立表间关联 | 级联操作需额外开销 |
修改表结构（ALTERTABLE）
`ALTERTABLE`功能丰富，用于表创建后修改其定义。
```sql
添加字段（带默认值）
ALTERTABLEemployeesADDCOLUMNphoneVARCHAR(20)DEFAULT'未提供';
修改数据类型（需重建表）
ALTERTABLEemployeesALTERCOLUMNsalaryTYPENUMERIC(12,2);
删除字段（生产环境慎用）
ALTERTABLEemployeesDROPCOLUMNfax;
添加外键（延迟约束检查）
ALTERTABLEemployees
ADDCONSTRAINTfk_dept
FOREIGNKEY(department_id)REFERENCESdepartments(dept_id)
DEFERRABLEINITIALLYDEFERRED;
``
关键选项解析：
`CASCADE`：自动删除依赖该列的对象（如视图、外键）。
`RESTRICT`（默认）：存在依赖对象时拒绝删除。
`USING`子句：指定从旧类型到新类型的转换规则。
删除表（DROPTABLE）
```sql
级联删除依赖对象
DROPTABLEIFEXISTSemployeesCASCADE;
仅删除表结构，保留数据（PostgreSQL12+）
TRUNCATETABLEemployees;
```
重要提示：`TRUNCATE`比`DELETEFROM`效率高10–100倍，适合清空大表。
2.3索引创建与管理
索引是提升查询性能的关键机制，PostgreSQL支持多种索引类型。
基本索引创建：
```sql
创建Btree索引（默认）
CREATEINDEXidx_employee_nameONemployees(emp_name);
创建唯一索引
CREATEUNIQUEINDEXidx_unique_emailONemployees(email);
创建多列复合索引
CREATEINDEXidx_dept_salaryONemployees(department_id,salaryDESC);
```
并发创建索引：
适用于在线业务，避免阻塞其他会话的DML操作。
```sql
CREATEINDEXCONCURRENTLYidx_employee_nameONemployees(emp_name);
```
部分索引（PartialIndex）：
仅对满足条件的行创建索引，减少索引大小。
```sql
CREATEINDEXidx_active_employeesONemployees(department_id)WHEREactive=true;
```
表达式索引：
在表达式上创建索引。
```sql
CREATEINDEXidx_lower_nameONemployees(LOWER(emp_name));
```
2.4视图创建与管理
视图是基于一个或多个表的查询结果集定义的虚拟表，不存储实际数据。
基本视图创建：
```sql
基于多表连接创建视图
CREATEVIEWemployee_detailsAS
SELECTe.emp_id,e.emp_name,e.salary,d.dept_name
FROMemployeese
JOINdepartmentsdONe.department_id=d.dept_id;
查询视图
SELECTFROMemployee_detailsWHEREsalary5000;
```
可更新视图与检查选项：
```sql
创建带检查选项的视图
CREATEVIEWhigh_paid_employeesAS
SELECTFROMemployeesWHEREsalary10000
WITHCHECKOPTION;
以下插入会失败（5000<10000）
INSERTINTOhigh_paid_employees(emp_name,salary)VALUES('John',5000);
```
物化视图（MaterializedView）：
将查询结果实际存储，需手动刷新。
```sql
创建物化视图
CREATEMATERIALIZEDVIEWmonthly_sales_summaryAS
SELECTdate_trunc('month',sale_date)ASmonth,
product_id,
SUM(amount)AStotal_sales
FROMsales
GROUPBYdate_trunc('month',sale_date),product_id;
刷新物化视图
REFRESHMATERIALIZEDVIEWmonthly_sales_summary;
```
2.5外键与参照完整性深度解析
外键是实现参照完整性（ReferentialIntegrity）的核心机制。
外键约束创建：
```sql
在创建表时定义外键
CREATETABLEorders(
order_idSERIALPRIMARYKEY,
product_idINTEGERREFERENCESproducts(product_id)ONDELETECASCADE,
quantityINTEGER,
order_dateDATEDEFAULTCURRENT_DATE
);
通过ALTERTABLE添加外键
ALTERTABLEorders
ADDCONSTRAINTfk_product
FOREIGNKEY(product_id)REFERENCESproducts(product_id);
```
引用动作（ReferentialActions）：
定义父表记录更新或删除时子表记录的响应方式。
| 动作 | 描述 | 示例 |
| NOACTION/RESTRICT | 禁止操作（默认） | 父表记录有引用时禁止删除 |
| CASCADE | 级联删除或更新 | 删除父表记录时同时删除子表相关记录 |
| SETNULL | 将子表中外键列设置为NULL | 删除父表记录时将子表外键设为NULL |
| SETDEFAULT | 将子表中外键列设置为默认值 | 删除父表记录时将子表外键设为默认值 |
示例：
```sql
CREATETABLEorder_items(
item_idSERIALPRIMARYKEY,
order_idINTEGERREFERENCESorders(order_id)ONDELETECASCADE,
product_idINTEGERREFERENCESproducts(product_id)ONDELETESETNULL,
quantityINTEGER
);
```
外键性能优化：
若PostgreSQL版本未自动建立外键索引，需手动创建以避免影响更新或删除性能。
```sql
为外键列创建索引
CREATEINDEXidx_order_items_order_idONorder_items(order_id);
CREATEINDEXidx_order_items_product_idONorder_items(product_id);
```
2.6对象重命名（RENAME）
重命名操作用于修改数据库对象的名称。
```sql
重命名表
ALTERTABLEemployeesRENAMETOstaff;
重命名列
ALTERTABLEemployeesRENAMECOLUMNemp_nameTOfull_name
重命名索引
ALTERINDEXidx_old_nameRENAMETOidx_new_name;
重命名视图
ALTERVIEWold_view_nameRENAMETOnew_view_name;
重命名序列
ALTERSEQUENCEold_sequence_nameRENAMETOnew_sequence_name;
```
注意事项：
重命名操作立即生效，无法回滚。
重命名后，所有引用该对象的地方均需更新。
建议在低峰期执行重命名操作。
2.7注释管理（COMMENT）
注释是数据库文档化的重要组成部分。
基本语法：
```sql
COMMENTON{对象类型}对象名称IS'注释文本'|NULL;
```
为各类对象添加注释：
```sql
为表添加注释
COMMENTONTABLEemployeesIS'员工信息表，存储公司所有员工的基本信息';
为列添加注释
COMMENTONCOLUMNemployees.salaryIS'员工月薪，单位为人民币元，税前金额';
为索引添加注释
COMMENTONINDEXidx_employee_nameIS'员工姓名索引，用于加速按姓名查询';
为视图添加注释
COMMENTONVIEWemployee_detailsIS'员工详细信息视图，包含员工及其部门信息';
为函数添加注释
COMMENTONFUNCTIONcalculate_bonus(decimal,integer)IS'计算员工年终奖金，参数1：基本工资，参数2：绩效评分';
```
修改与删除注释：
```sql
修改注释（直接覆盖）
COMMENTONTABLEemployeesIS'员工信息主表，包含员工基本信息、薪资和部门信息';
删除注释（设置为NULL）
COMMENTONTABLEemployeesISNULL;
```
查看注释：
```sql
使用系统函数查看
SELECTobj_description('employees'::regclass,'pg_class');
SELECTcol_description('employees'::regclass,列序号);
```
注释最佳实践：
为所有重要对象（表、列、索引、视图、函数等）添加注释。
保持注释简洁明了，统一团队内部注释风格。
在注释中说明重要的业务规则与约束。
记录重要的表结构变更原因与时间
权限与安全提示：
大多数对象只有所有者可设置或修改注释。
注释无安全机制，所有连接用户均可查看，请勿存放敏感信息。
2.8NULL值的处理与最佳实践
NULL表示“未知”或“不存在”的值，不同于空字符串或零。
NULL比较与计算：
```sql
正确：使用ISNULL判断
SELECTFROMemployeesWHEREsalaryISNULL;
错误：与NULL的比较结果总是NULL（视为FALSE）
SELECTFROMemployeesWHEREsalary=NULL;不会返回任何行
包含NULL的计算结果通常也是NULL
SELECT10+NULL;返回NULL
SELECT'Hello'||NULL;返回NULL
```
NULL与约束：
`NOTNULL`约束：确保列不允许NULL值。
`UNIQUE`约束：PostgreSQL视多个NULL值为互不相同（允许存在多个NULL）。
外键中的NULL：外键列允许为NULL，表示该记录未关联到父表。
处理NULL的函数：
```sql
COALESCE：返回第一个非NULL值
SELECTCOALESCE(salary,0)ASeffective_salaryFROMemployees;
NULLIF：如果两个表达式相等则返回NULL
SELECTNULLIF(salary,0)FROMemployees;若salary为0则返回NULL
ISDISTINCTFROM：比较时考虑NULL
SELECTFROMt1WHEREaISDISTINCTFROMb;
```
来源：小程序app开发|ui设计|软件外包|IT技术服务公司-木风未来科技-成都木风未来科技有限公司
