PostgreSQL基础知识——DDL深度解析

E）

表是关系型数据库中最基本的对象，PostgreSQL支持丰富的数据类型与约束机制。

常用数据类型示例：

创建表示例：

```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)外键约束

);

```

约束类型对比：

修改表结构（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）：

定义父表记录更新或删除时子表记录的响应方式。

示例：

```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技术服务公司-木风未来科技-成都木风未来科技有限公司