PostgreSQL 16 数据表基本操作 —— 语法详解与实战案例一、创建数据表✅ 语法CREATETABLE[IFNOTEXISTS]table_name(column1 datatype[constraint],column2 datatype[constraint],...[table_constraint]);IF NOT EXISTS避免重复创建报错constraint列级约束如NOT NULL,PRIMARY KEYtable_constraint表级约束如外键、复合主键 案例创建员工表含多种约束-- 创建 employees 表CREATETABLEIFNOTEXISTSemployees(emp_idSERIALPRIMARYKEY,-- 主键 自增emp_nameVARCHAR(100)NOTNULL,-- 非空约束emailVARCHAR(150)UNIQUE,-- 唯一性约束hire_dateDATEDEFAULTCURRENT_DATE,-- 默认约束salaryNUMERIC(10,2)CHECK(salary0),-- 检查约束dept_idINTREFERENCESdepartments(dept_id)ONDELETESETNULL-- 外键约束);-- 注释-- SERIAL 自增整数PostgreSQL 特有-- PRIMARY KEY 主键约束自动 NOT NULL UNIQUE-- NOT NULL 非空约束-- UNIQUE 唯一性约束允许 NULL但只能有一个 NULL-- DEFAULT 默认值约束-- CHECK 检查约束salary 0-- REFERENCES 外键约束关联 departments 表-- ON DELETE SET NULL 父表删除时子表设为 NULL避免级联删除⚠️ 注意在创建外键前需先创建被引用的表如departments二、约束详解与使用方法1. 主键约束PRIMARY KEY用于唯一标识表中的每一行。一个表只能有一个主键可以是单列或多列复合主键。✅ 语法-- 列级column_name datatypePRIMARYKEY-- 表级支持复合主键PRIMARYKEY(col1,col2) 案例-- 单列主键CREATETABLEusers(user_idINTPRIMARYKEY,usernameVARCHAR(50));-- 复合主键订单明细CREATETABLEorder_items(order_idINT,product_idINT,quantityINT,PRIMARYKEY(order_id,product_id)-- 同一订单中商品不重复);2. 外键约束FOREIGN KEY用于建立表与表之间的关联确保引用完整性。✅ 语法-- 列级column_name datatypeREFERENCESparent_table(parent_column)-- 表级FOREIGNKEY(child_column)REFERENCESparent_table(parent_column)[ONDELETEaction][ONUPDATEaction]ON DELETE动作CASCADE级联删除、SET NULL、SET DEFAULT、RESTRICT默认、NO ACTION 案例-- 先创建主表 departmentsCREATETABLEdepartments(dept_idSERIALPRIMARYKEY,dept_nameVARCHAR(100)NOTNULL);-- 再创建子表 employees引用 departmentsCREATETABLEemployees(emp_idSERIALPRIMARYKEY,emp_nameVARCHAR(100)NOTNULL,dept_idINT,FOREIGNKEY(dept_id)REFERENCESdepartments(dept_id)ONDELETESETNULL-- 删除部门时员工部门设为 NULLONUPDATECASCADE-- 更新部门ID时自动更新员工表);3. 非空约束NOT NULL确保字段必须有值不能为 NULL。✅ 语法column_name datatypeNOTNULL 案例CREATETABLEproducts(product_idSERIALPRIMARYKEY,product_nameVARCHAR(200)NOTNULL,-- 商品名不能为空priceNUMERIC(10,2));⚠️ 主键列自动具有NOT NULL无需重复声明。4. 唯一性约束UNIQUE确保列或列组合的值在表中唯一允许一个 NULL。✅ 语法-- 列级column_name datatypeUNIQUE-- 表级支持多列唯一UNIQUE(col1,col2) 案例CREATETABLEusers(user_idSERIALPRIMARYKEY,usernameVARCHAR(50)UNIQUE,-- 用户名唯一emailVARCHAR(100),UNIQUE(email)-- 邮箱唯一表级写法);-- 复合唯一约束防止重复订阅CREATETABLEsubscriptions(user_idINT,plan_idINT,start_dateDATE,UNIQUE(user_id,plan_id)-- 同一用户不能重复订阅同一计划);5. 默认约束DEFAULT为列设置默认值插入时若未提供值则使用默认值。✅ 语法column_name datatypeDEFAULTdefault_value 案例CREATETABLElogs(log_idSERIALPRIMARYKEY,messageTEXTNOTNULL,log_timeTIMESTAMPDEFAULTCURRENT_TIMESTAMP,-- 默认当前时间statusVARCHAR(20)DEFAULTpending-- 默认状态);-- 插入时可省略默认字段INSERTINTOlogs(message)VALUES(系统启动);-- 等价于-- INSERT INTO logs (message, log_time, status)-- VALUES (系统启动, 2025-09-12 10:45:00, pending);三、修改数据表ALTER TABLE1. 修改表名✅ 语法ALTERTABLEold_table_nameRENAMETOnew_table_name; 案例-- 将 employees 表重命名为 staffALTERTABLEemployeesRENAMETOstaff;2. 修改字段数据类型✅ 语法ALTERTABLEtable_nameALTERCOLUMNcolumn_nameTYPEnew_datatype[USINGexpression];USING用于转换数据如字符串转数字 案例-- 将 salary 从 NUMERIC 改为 INT需确保数据可转换ALTERTABLEstaffALTERCOLUMNsalaryTYPEINTUSINGsalary::INTEGER;-- 将 VARCHAR 改为 TEXT无损转换无需 USINGALTERTABLEstaffALTERCOLUMNemp_nameTYPETEXT;3. 修改字段名✅ 语法ALTERTABLEtable_nameRENAMECOLUMNold_column_nameTOnew_column_name; 案例-- 将 emp_name 改为 full_nameALTERTABLEstaffRENAMECOLUMNemp_nameTOfull_name;4. 添加字段✅ 语法ALTERTABLEtable_nameADDCOLUMNcolumn_name datatype[constraint][DEFAULTvalue]; 案例-- 添加 phone 字段ALTERTABLEstaffADDCOLUMNphoneVARCHAR(20);-- 添加带默认值和非空约束的字段PostgreSQL 16 优化ALTERTABLEstaffADDCOLUMNcreated_atTIMESTAMPNOTNULLDEFAULTCURRENT_TIMESTAMP;-- ✅ PostgreSQL 16 新特性添加带默认值的字段不再重写表Instant-- 以前版本添加 NOT NULL DEFAULT 会锁表并重写所有行-- 16 版本内部使用“虚拟默认值”性能极大提升5. 删除字段✅ 语法ALTERTABLEtable_nameDROPCOLUMNcolumn_name[CASCADE];CASCADE级联删除依赖该列的对象如视图、约束 案例-- 删除 phone 字段ALTERTABLEstaffDROPCOLUMNphone;-- 如果该列被视图引用使用 CASCADEALTERTABLEstaffDROPCOLUMNemailCASCADE;6. 删除外键约束✅ 语法ALTERTABLEtable_nameDROPCONSTRAINTconstraint_name;可通过\d table_name查看约束名 案例-- 查看 staff 表的约束\d staff-- 输出示例-- Foreign-key constraints:-- staff_dept_id_fkey FOREIGN KEY (dept_id) REFERENCES departments(dept_id)-- 删除外键约束ALTERTABLEstaffDROPCONSTRAINTstaff_dept_id_fkey;四、删除数据表1. 删除没有被关联的表✅ 语法DROPTABLE[IFEXISTS]table_name[CASCADE]; 案例-- 删除 logs 表无外键依赖DROPTABLEIFEXISTSlogs;2. 删除被其他表关联的主表直接删除会报错需使用CASCADE级联删除子表或先删除外键。 案例-- 方法1级联删除危险会删除所有依赖表DROPTABLEdepartmentsCASCADE;-- 方法2先删除外键再删表推荐ALTERTABLEstaffDROPCONSTRAINTstaff_dept_id_fkey;DROPTABLEdepartments;五、PostgreSQL 16 新特性 —— 新增带默认值字段不再重写表在 PostgreSQL 16 之前添加NOT NULL DEFAULT字段会导致全表重写锁表、慢、占用空间。PostgreSQL 16 开始此类操作是“即时”的Instant通过系统表记录默认值读取时动态填充写入时存储真实值。✅ 案例演示-- 创建测试表100万行CREATETABLElarge_tableASSELECTgenerate_series(1,1000000)ASid,dataAScontent;-- PostgreSQL 16 中执行以下语句几乎瞬间完成ALTERTABLElarge_tableADDCOLUMNcreated_atTIMESTAMPNOTNULLDEFAULTNOW();-- ✅ 无锁表、无重写、高性能 这是 PostgreSQL 16 最受开发者欢迎的改进之一六、综合实战案例 —— 学生选课系统 目标创建学生、课程、选课表进行增删改查与结构变更步骤1创建表结构-- 学生表CREATETABLEstudents(student_idSERIALPRIMARYKEY,student_nameVARCHAR(100)NOTNULL,genderCHAR(1)CHECK(genderIN(M,F)),birth_dateDATE,emailVARCHAR(150)UNIQUE);-- 课程表CREATETABLEcourses(course_idSERIALPRIMARYKEY,course_nameVARCHAR(200)NOTNULLUNIQUE,creditINTDEFAULT3CHECK(credit0),teacherVARCHAR(100));-- 选课表关联学生和课程CREATETABLEenrollments(enrollment_idSERIALPRIMARYKEY,student_idINTNOTNULLREFERENCESstudents(student_id)ONDELETECASCADE,course_idINTNOTNULLREFERENCEScourses(course_id)ONDELETECASCADE,enroll_dateDATEDEFAULTCURRENT_DATE,gradeNUMERIC(4,2)CHECK(gradeBETWEEN0AND100),UNIQUE(student_id,course_id)-- 防止重复选课);步骤2插入测试数据-- 插入学生INSERTINTOstudents(student_name,gender,birth_date,email)VALUES(张三,M,2005-03-12,zhangsanschool.com),(李四,F,2004-07-25,lisischool.com);-- 插入课程INSERTINTOcourses(course_name,teacher)VALUES(高等数学,王教授),(数据库原理,李博士);-- 插入选课INSERTINTOenrollments(student_id,course_id,grade)VALUES(1,1,88.5),(1,2,92.0),(2,1,79.0);步骤3修改表结构模拟需求变更-- 1. 为 students 表添加“入学年份”字段PostgreSQL 16 优化ALTERTABLEstudentsADDCOLUMNadmission_yearINTNOTNULLDEFAULT2025;-- 2. 修改 email 字段长度ALTERTABLEstudentsALTERCOLUMNemailTYPEVARCHAR(200);-- 3. 为 courses 表添加“课程简介”ALTERTABLEcoursesADDCOLUMNdescriptionTEXT;-- 4. 删除 enrollments 的 grade 检查约束假设允许 NULLALTERTABLEenrollmentsALTERCOLUMNgradeDROPNOTNULL;-- 5. 重命名字段ALTERTABLEenrollmentsRENAMECOLUMNenroll_dateTOenrolled_on;步骤4查询与验证-- 查询学生选课情况SELECTs.student_name,c.course_name,e.grade,e.enrolled_onFROMenrollments eJOINstudents sONe.student_ids.student_idJOINcourses cONe.course_idc.course_idORDERBYs.student_name;-- 查询每门课的平均分SELECTc.course_name,AVG(e.grade)ASavg_gradeFROMenrollments eJOINcourses cONe.course_idc.course_idGROUPBYc.course_id,c.course_name;步骤5安全删除表-- 先删子表级联删除会自动处理外键DROPTABLEenrollments;-- 再删主表DROPTABLEstudents;DROPTABLEcourses;七、常见问题及解答❓ 疑问1表删除和修改操作时需注意什么问题✅解答备份数据任何结构变更前建议备份表CREATE TABLE backup AS SELECT * FROM original;。外键依赖删除主表前先处理外键删除或级联。锁表风险大表的ALTER操作可能锁表影响线上服务。PostgreSQL 16 对部分操作如加默认值列已优化。事务包裹重要操作用事务包裹失败可回滚BEGIN;ALTERTABLE...;-- 如果出错ROLLBACK;-- 如果成功COMMIT;权限检查确保用户有ALTER、DROP权限。❓ 疑问2每一个表中都要有一个主键吗✅解答强烈建议每个表都有主键但不是强制的。为什么建议唯一标识行避免重复数据。提高查询、更新、删除效率索引优化。支持复制、逻辑解码、ORM 框架等高级功能。外键必须引用主键或唯一键。无主键的场景极少日志表仅追加不更新。临时中间表。使用UUID或业务字段组合唯一但仍建议显式定义主键。生产环境表无主键 技术债务✅ 附常用命令速查表操作SQL 语句示例创建表CREATE TABLE ...添加列ALTER TABLE ... ADD COLUMN ...修改列类型ALTER TABLE ... ALTER COLUMN ... TYPE ...重命名列ALTER TABLE ... RENAME COLUMN ... TO ...删除列ALTER TABLE ... DROP COLUMN ...删除约束ALTER TABLE ... DROP CONSTRAINT ...重命名表ALTER TABLE ... RENAME TO ...删除表无依赖DROP TABLE ...删除表有依赖DROP TABLE ... CASCADE查看表结构\d table_name学习建议所有操作在测试库中先行演练。使用事务保护重要变更。关注 PostgreSQL 16 的新特性提升开发效率。善用\d、\dt、\dv等 psql 元命令。✅ 本章内容覆盖了 PostgreSQL 数据表操作的方方面面是数据库开发与管理的核心技能。建议反复练习熟练掌握