2. 首页 > PostgreSQL教程 > 正文

PostgreSQL教程FG022-PG数据定义语言（DDL）：CREATE/ALTER/DROP核心用法

教程整理：风哥教程  |  更新时间：2026-01-22  |  教程分类：PostgreSQL教程  |  文档学习：17

本文档风哥主要介绍PostgreSQL教程022相关内容。风哥教程参考PostgreSQL官方文档Server Administration, SQL Language内容编写，适合DBA人员在学习和测试中使用，如果要应用于生产环境则需要自行确认。

内容简介：本文介绍PostgreSQL数据库的数据定义语言（DDL），包括CREATE、ALTER和DROP命令的核心用法。风哥教程参考PostgreSQL官方文档Data Definition内容，详细讲解如何使用DDL命令创建、修改和删除数据库对象，帮助读者掌握PostgreSQL数据定义的核心技能。

Part01-基础概念与理论知识

1.1 DDL概述

数据定义语言（DDL，Data Definition Language）是SQL语言的一部分，用于定义和管理数据库对象的结构。PostgreSQL支持的DDL命令主要包括：

  • CREATE：创建数据库对象
  • ALTER：修改数据库对象
  • DROP：删除数据库对象
  • TRUNCATE：清空表中的数据
  • COMMENT：添加注释

DDL命令操作的数据库对象包括：

  • 数据库（Database）
  • 表（Table）
  • 视图（View）
  • 索引（Index）
  • 序列（Sequence）
  • 约束（Constraint）
  • 函数（Function）
  • 触发器（Trigger）
  • 类型（Type）

1.2 CREATE命令

CREATE命令用于创建新的数据库对象，其基本语法为：

CREATE OBJECT_TYPE object_name [WITH properties];

其中，OBJECT_TYPE是要创建的对象类型，如DATABASE、TABLE、INDEX等，object_name是对象的名称，properties是对象的属性。

更多视频教程www.fgedu.net.cn

1.3 ALTER命令

ALTER命令用于修改现有数据库对象的结构，其基本语法为：

ALTER OBJECT_TYPE object_name action;

其中，OBJECT_TYPE是要修改的对象类型，object_name是对象的名称，action是要执行的修改操作，如添加列、修改数据类型、添加约束等。

1.4 DROP命令

DROP命令用于删除数据库对象，其基本语法为：

DROP OBJECT_TYPE [IF EXISTS] object_name [CASCADE | RESTRICT];

其中，OBJECT_TYPE是要删除的对象类型，object_name是对象的名称，IF EXISTS是可选的，如果对象不存在则不报错，CASCADE表示级联删除相关对象，RESTRICT表示如果有依赖对象则不允许删除。

from oracle:www.itpux.com

Part02-生产环境规划与建议

2.1 DDL最佳实践

风哥教程针对风哥教程针对风哥教程针对生产环境建议：
– 在非高峰期执行DDL操作，减少对业务的影响
– 执行DDL操作前进行充分测试，确保操作的正确性
– 对重要对象进行备份，以便在出现问题时进行恢复
– 使用IF EXISTS选项，避免因对象不存在而导致的错误
– 谨慎使用CASCADE选项，避免意外删除重要对象
– 为数据库对象添加注释，提高可维护性
– 遵循命名规范，保持对象名称的一致性

2.2 事务处理考虑

风哥教程针对风哥教程针对风哥教程针对生产环境建议：
– PostgreSQL支持在事务中执行DDL操作
– 可以使用BEGIN、COMMIT和ROLLBACK命令控制DDL事务
– 长时间运行的DDL事务会锁定相关对象，影响系统性能
– 对于大型表的DDL操作，建议使用CONCURRENTLY选项（如果支持）
– 执行DDL操作前，检查是否有未提交的事务

2.3 性能影响因素

风哥教程针对风哥教程针对风哥教程针对生产环境建议：
– DDL操作会导致表锁定，影响并发访问
– 添加索引会增加数据插入、更新和删除的开销
– 修改表结构可能需要重写整个表，影响性能
– 对于大型表，DDL操作可能需要较长时间才能完成
– 监控DDL操作的执行时间和资源使用情况

Part03-生产环境项目实施方案

3.1 CREATE命令使用示例

以下是PostgreSQL中CREATE命令的使用示例。学习交流加群风哥微信: itpux-com

$ psql -d fgedudb -U fgedu
Password for fgedu fgedu:
psql (18.3)
Type “help” for help.

fgedudb=>

— 创建数据库
CREATE DATABASE fgedu_test_db;

CREATE DATABASE

— 创建表
CREATE TABLE fgedu_employees (
employee_id SERIAL PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
hire_date DATE NOT NULL,
salary NUMERIC(10, 2) NOT NULL,
department_id INTEGER
);

CREATE TABLE fgedu_– 创建索引
CREATE INDEX idx_employees_last_name ON fgedu_employees(last_name);
CREATE INDEX idx_employees_department_id ON fgedu_employees(department_id);

CREATE INDEX
CREATE INDEX

— 创建视图
CREATE VIEW v_active_employees AS
SELECT employee_id, first_name, last_name, email, hire_date, salary, department_id
FROM fgedu_employees
WHERE salary > 5000.00;

CREATE VIEW

— 创建序列
CREATE SEQUENCE seq_order_id
START WITH 1000
INCREMENT BY 1
NO MAXVALUE
CACHE 10;

CREATE SEQUENCE

— 创建函数
CREATE FUNCTION get_employee_count()
RETURNS INTEGER AS $$
BEGIN
RETURN (SELECT COUNT(*) FROM fgedu_employees);
END;
$$ LANGUAGE plpgsql;

CREATE FUNCTION

3.2 ALTER命令使用示例

以下是PostgreSQL中ALTER命令的使用示例。

— 修改表：添加列
ALTER TABLE fgedu_employees ADD COLUMN phone VARCHAR(20);

ALTER TABLE

— 修改表：修改列的数据类型
ALTER TABLE fgedu_employees ALTER COLUMN phone TYPE VARCHAR(30);

ALTER TABLE

— 修改表：添加约束
ALTER TABLE fgedu_employees ADD CONSTRAINT chk_salary CHECK (salary > 0);

ALTER TABLE

— 修改表：添加外键约束
CREATE TABLE fgedu_departments (
department_id SERIAL PRIMARY KEY,
department_name VARCHAR(50) NOT NULL
);

CREATE TABLE fgedu_ALTER TABLE fgedu_employees
ADD CONSTRAINT fk_department_id
FOREIGN KEY (department_id)
REFERENCES fgedu_departments(department_id);

ALTER TABLE

— 修改表：重命名列
ALTER TABLE fgedu_employees RENAME COLUMN phone TO phone_number;

ALTER TABLE

— 修改表：重命名表
ALTER TABLE fgedu_employees RENAME TO fgedu_staff;

ALTER TABLE

— 修改索引：重命名索引
ALTER INDEX idx_employees_last_name RENAME TO idx_staff_last_name;

ALTER INDEX

— 修改视图：更新视图定义
CREATE OR REPLACE VIEW v_active_employees AS
SELECT employee_id, first_name, last_name, email, hire_date, salary, department_id
FROM fgedu_staff
WHERE salary > 6000.00;

CREATE VIEW

— 修改序列：修改序列的起始值
ALTER SEQUENCE seq_order_id RESTART WITH 2000;

ALTER SEQUENCE

3.3 DROP命令使用示例

以下是PostgreSQL中DROP命令的使用示例。

— 删除函数
DROP FUNCTION IF EXISTS get_employee_count();

DROP FUNCTION

— 删除视图
DROP VIEW IF EXISTS v_active_employees;

DROP VIEW

— 删除索引
DROP INDEX IF EXISTS idx_staff_last_name;
DROP INDEX IF EXISTS idx_employees_department_id;

DROP INDEX
DROP INDEX

— 删除表
DROP TABLE IF EXISTS fgedu_staff CASCADE;
DROP TABLE IF EXISTS fgedu_departments CASCADE;

DROP TABLE
DROP TABLE

— 删除序列
DROP SEQUENCE IF EXISTS seq_order_id;

DROP SEQUENCE

— 删除数据库
DROP DATABASE IF EXISTS fgedu_test_db;

DROP DATABASE

Part04-生产案例与实战讲解

4.1 表的DDL操作实战

以下是表的DDL操作实战示例。学习交流加群风哥QQ113257174

— 创建产品表
CREATE TABLE fgedu_products (
product_id SERIAL PRIMARY KEY,
product_name VARCHAR(100) NOT NULL,
category VARCHAR(50) NOT NULL,
price NUMERIC(10, 2) NOT NULL,
stock INTEGER NOT NULL DEFAULT 0,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE fgedu_– 查看表结构
\d fgedu_products
Table “public.fgedu_products”
Column | Type | Collation | Nullable | Default
————-+————————–+———–+———-+————————————–
product_id | integer | | not null | nextval(‘fgedu_products_product_id_seq’::regclass)
product_name | character varying(100) | | not null |
category | character varying(50) | | not null |
price | numeric(10,2) | | not null |
stock | integer | | not null | 0
created_at | timestamp with time zone | | | CURRENT_TIMESTAMP
updated_at | timestamp with time zone | | | CURRENT_TIMESTAMP
Indexes:
“fgedu_products_pkey” PRIMARY KEY, btree (product_id)

— 修改表：添加新列
ALTER TABLE fgedu_products ADD COLUMN description TEXT;
ALTER TABLE fgedu_products ADD COLUMN is_active BOOLEAN DEFAULT TRUE;

ALTER TABLE
ALTER TABLE

— 修改表：修改列的默认值
ALTER TABLE fgedu_products ALTER COLUMN stock SET DEFAULT 10;

ALTER TABLE

— 修改表：添加唯一约束
ALTER TABLE fgedu_products ADD CONSTRAINT uq_product_name UNIQUE (product_name);

ALTER TABLE

— 插入测试数据
INSERT INTO fgedu_products (product_name, category, price, stock, description)
VALUES (‘PostgreSQL教程’, ‘书籍’, 99.99, 50, ‘PostgreSQL数据库入门教程’),
(‘Python编程’, ‘书籍’, 89.99, 30, ‘Python编程语言入门教程’),
(‘Java开发’, ‘书籍’, 109.99, 20, ‘Java企业级开发教程’);

INSERT 0 3

— 查询数据
SELECT * FROM fgedu_products;
product_id | product_name | category | price | stock | created_at | updated_at | description | is_active
————+————–+———-+——-+——-+——————————-+——————————-+————————-+———–+
1 | PostgreSQL教程 | 书籍 | 99.99 | 50 | 2026-04-02 15:45:30.123456+08 | 2026-04-02 15:45:30.123456+08 | PostgreSQL数据库入门教程 | t
2 | Python编程 | 书籍 | 89.99 | 30 | 2026-04-02 15:45:30.123456+08 | 2026-04-02 15:45:30.123456+08 | Python编程语言入门教程 | t
3 | Java开发 | 书籍 | 109.99 | 20 | 2026-04-02 15:45:30.123456+08 | 2026-04-02 15:45:30.123456+08 | Java企业级开发教程 | t
(3 rows)

— 修改表：删除列
ALTER TABLE fgedu_products DROP COLUMN description;

ALTER TABLE

— 修改表：重命名表
ALTER TABLE fgedu_products RENAME TO fgedu_book_products;

ALTER TABLE

— 查看修改后的表结构
\d fgedu_book_products
Table “public.fgedu_book_products”
Column | Type | Collation | Nullable | Default
————-+————————–+———–+———-+————————————–
product_id | integer | | not null | nextval(‘fgedu_products_product_id_seq’::regclass)
product_name | character varying(100) | | not null |
category | character varying(50) | | not null |
price | numeric(10,2) | | not null |
stock | integer | | not null | 10
created_at | timestamp with time zone | | | CURRENT_TIMESTAMP
updated_at | timestamp with time zone | | | CURRENT_TIMESTAMP
is_active | boolean | | | true
Indexes:
“fgedu_products_pkey” PRIMARY KEY, btree (product_id)
“uq_product_name” UNIQUE CONSTRAINT, btree (product_name)

— 删除表
DROP TABLE IF EXISTS fgedu_book_products;

DROP TABLE

4.2 索引的DDL操作实战

以下是索引的DDL操作实战示例。

— 创建订单表
CREATE TABLE fgedu_orders (
order_id SERIAL PRIMARY KEY,
customer_id INTEGER NOT NULL,
order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
total_amount NUMERIC(10, 2) NOT NULL,
order_status VARCHAR(20) DEFAULT ‘pending’
);

CREATE TABLE fgedu_– 创建普通索引
CREATE INDEX idx_orders_customer_id ON fgedu_orders(customer_id);

CREATE INDEX

— 创建复合索引
CREATE INDEX idx_orders_customer_date ON fgedu_orders(customer_id, order_date DESC);

CREATE INDEX

— 创建唯一索引
CREATE UNIQUE INDEX idx_orders_unique ON fgedu_orders(order_id);

CREATE INDEX

— 查看索引
\di
List of relations
Schema | Name | Type | Owner | Table
——–+——————————–+——-+——-+————–
public | fgedu_orders_pkey | index | fgedu | fgedu_orders
public | idx_orders_customer_date | index | fgedu | fgedu_orders
public | idx_orders_customer_id | index | fgedu | fgedu_orders
public | idx_orders_unique | index | fgedu | fgedu_orders
(4 rows)

— 重命名索引
ALTER INDEX idx_orders_unique RENAME TO idx_orders_order_id;

ALTER INDEX

— 删除索引
DROP INDEX IF EXISTS idx_orders_customer_id;

DROP INDEX

— 查看修改后的索引
\di
List of relations
Schema | Name | Type | Owner | Table
——–+——————————–+——-+——-+————–
public | fgedu_orders_pkey | index | fgedu | fgedu_orders
public | idx_orders_customer_date | index | fgedu | fgedu_orders
public | idx_orders_order_id | index | fgedu | fgedu_orders
(3 rows)

— 删除表（同时删除相关索引）
DROP TABLE IF EXISTS fgedu_orders;

DROP TABLE

4.3 约束的DDL操作实战

以下是约束的DDL操作实战示例。

— 创建用户表
CREATE TABLE fgedu_fgedus (
fgedu_id SERIAL PRIMARY KEY,
fgeduname VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL,
password_hash VARCHAR(255) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE fgedu_– 添加唯一约束
ALTER TABLE fgedu_fgedus ADD CONSTRAINT uq_fgeduname UNIQUE (fgeduname);
ALTER TABLE fgedu_fgedus ADD CONSTRAINT uq_email UNIQUE (email);

ALTER TABLE
ALTER TABLE

— 添加检查约束
ALTER TABLE fgedu_fgedus ADD CONSTRAINT chk_email_format CHECK (email ~* ‘^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Z|a-z]{2,}$’);

ALTER TABLE

— 创建地址表
CREATE TABLE fgedu_addresses (
address_id SERIAL PRIMARY KEY,
fgedu_id INTEGER NOT NULL,
address_line1 VARCHAR(100) NOT NULL,
address_line2 VARCHAR(100),
city VARCHAR(50) NOT NULL,
state VARCHAR(50) NOT NULL,
zip_code VARCHAR(20) NOT NULL,
is_default BOOLEAN DEFAULT FALSE
);

CREATE TABLE fgedu_– 添加外键约束
ALTER TABLE fgedu_addresses
ADD CONSTRAINT fk_fgedu_id
FOREIGN KEY (fgedu_id)
REFERENCES fgedu_fgedus(fgedu_id)
ON DELETE CASCADE;

ALTER TABLE

— 查看表的约束
\d fgedu_fgedus
Table “public.fgedu_fgedus”
Column | Type | Collation | Nullable | Default
—————+————————–+———–+———-+————————————–
fgedu_id | integer | | not null | nextval(‘fgedu_fgedus_fgedu_id_seq’::regclass)
fgeduname | character varying(50) | | not null |
email | character varying(100) | | not null |
password_hash | character varying(255) | | not null |
created_at | timestamp with time zone | | | CURRENT_TIMESTAMP
updated_at | timestamp with time zone | | | CURRENT_TIMESTAMP
Indexes:
“fgedu_fgedus_pkey” PRIMARY KEY, btree (fgedu_id)
“uq_email” UNIQUE CONSTRAINT, btree (email)
“uq_fgeduname” UNIQUE CONSTRAINT, btree (fgeduname)
Check constraints:
“chk_email_format” CHECK (email ~* ‘^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Z|a-z]{2,}$’)

\d fgedu_addresses
Table “public.fgedu_addresses”
Column | Type | Collation | Nullable | Default
—————+————————–+———–+———-+————————————–
address_id | integer | | not null | nextval(‘fgedu_addresses_address_id_seq’::regclass)
fgedu_id | integer | | not null |
address_line1 | character varying(100) | | not null |
address_line2 | character varying(100) | | |
city | character varying(50) | | not null |
state | character varying(50) | | not null |
zip_code | character varying(20) | | not null |
is_default | boolean | | | false
Indexes:
“fgedu_addresses_pkey” PRIMARY KEY, btree (address_id)
Foreign-key constraints:
“fk_fgedu_id” FOREIGN KEY (fgedu_id) REFERENCES fgedu_fgedus(fgedu_id) ON DELETE CASCADE

— 删除约束
ALTER TABLE fgedu_fgedus DROP CONSTRAINT chk_email_format;

ALTER TABLE

— 删除表
DROP TABLE IF EXISTS fgedu_addresses CASCADE;
DROP TABLE IF EXISTS fgedu_fgedus CASCADE;

DROP TABLE
DROP TABLE

Part05-风哥经验总结与分享

风哥提示：数据定义语言（DDL）是数据库管理的重要组成部分，用于定义和管理数据库对象的结构。掌握DDL命令的核心用法是进行数据库设计和维护的基础。

1. CREATE命令用于创建新的数据库对象，包括数据库、表、索引、视图、序列、函数等。

2. ALTER命令用于修改现有数据库对象的结构，如添加列、修改数据类型、添加约束等。

3. DROP命令用于删除数据库对象，使用时需要谨慎，避免意外删除重要数据。

4. 在生产环境中执行DDL操作时，应该在非高峰期进行，并进行充分测试和备份。

5. PostgreSQL支持在事务中执行DDL操作，可以使用BEGIN、COMMIT和ROLLBACK命令控制事务。

6. DDL操作会影响数据库性能，特别是对于大型表，需要考虑操作的执行时间和资源使用情况。

7. 使用IF EXISTS选项可以避免因对象不存在而导致的错误，提高脚本的健壮性。

8. 谨慎使用CASCADE选项，避免意外删除相关对象，造成数据丢失。

9. 为数据库对象添加注释，提高代码的可维护性和可读性。

10. 遵循命名规范和最佳实践，保持数据库结构的一致性和可维护性。更多学习教程公众号风哥教程itpux_com

通过掌握DDL命令的核心用法，可以有效地进行数据库设计和维护，创建和管理符合业务需求的数据库结构。from PostgreSQL:www.itpux.com

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html

相关推荐

联系我们

在线咨询：点击这里给我发消息

微信号：itpux-com

工作日：9:30-18:30，节假日休息