# 创建用户表
ecommerce=# CREATE TABLE fgedu_shop.fgedus (
fgedu_id SERIAL PRIMARY KEY,
fgeduname VARCHAR(50) UNIQUE NOT NULL,
password VARCHAR(100) NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
first_name VARCHAR(50),
last_name VARCHAR(50),
phone VARCHAR(20),
address TEXT,
created_at TIMESTAMP DEFAULT NOW(),
updated_at TIMESTAMP DEFAULT NOW()
);
CREATE TABLE fgedu_# 查看表结构
ecommerce=# \d shop.fgedus
Table “shop.fgedus”
Column | Type | Collation | Nullable | Default
————+—————————–+———–+———-+——————————————–
fgedu_id | integer | | not null | nextval(‘shop.fgedus_fgedu_id_seq’::regclass)
fgeduname | character varying(50) | | not null |
password | character varying(100) | | not null |
email | character varying(100) | | not null |
first_name | character varying(50) | | |
last_name | character varying(50) | | |
phone | character varying(20) | | |
address | text | | |
created_at | timestamp without time zone | | | now()
updated_at | timestamp without time zone | | | now()
Indexes:
“fgedus_pkey” PRIMARY KEY, btree (fgedu_id)
“fgedus_email_key” UNIQUE CONSTRAINT, btree (email)
“fgedus_fgeduname_key” UNIQUE CONSTRAINT, btree (fgeduname)

# 创建产品表
ecommerce=# CREATE TABLE fgedu_shop.products (
product_id SERIAL PRIMARY KEY,
product_name VARCHAR(100) NOT NULL,
category VARCHAR(50) NOT NULL,
description TEXT,
price DECIMAL(10,2) NOT NULL CHECK (price > 0),
stock_quantity INTEGER DEFAULT 0 CHECK (stock_quantity >= 0),
created_at TIMESTAMP DEFAULT NOW(),
updated_at TIMESTAMP DEFAULT NOW()
);
CREATE TABLE fgedu_# 查看表结构
ecommerce=# \d shop.products
Table “shop.products”
Column | Type | Collation | Nullable | Default
—————–+—————————–+———–+———-+———————————————–
product_id | integer | | not null | nextval(‘shop.products_product_id_seq’::regclass)
product_name | character varying(100) | | not null |
category | character varying(50) | | not null |
description | text | | |
price | numeric(10,2) | | not null |
stock_quantity | integer | | | 0
created_at | timestamp without time zone | | | now()
updated_at | timestamp without time zone | | | now()
Indexes:
“products_pkey” PRIMARY KEY, btree (product_id)
Check constraints:
“products_price_check” CHECK (price > 0)
“products_stock_quantity_check” CHECK (stock_quantity >= 0)

# 创建订单表
ecommerce=# CREATE TABLE fgedu_shop.orders (
order_id SERIAL PRIMARY KEY,
fgedu_id INTEGER NOT NULL REFERENCES shop.fgedus(fgedu_id),
order_date DATE DEFAULT NOW(),
total_amount DECIMAL(10,2) NOT NULL CHECK (total_amount >= 0),
status VARCHAR(20) DEFAULT ‘pending’ CHECK (status IN (‘pending’, ‘processing’, ‘shipped’, ‘delivered’, ‘cancelled’)),
shipping_address TEXT NOT NULL,
created_at TIMESTAMP DEFAULT NOW(),
updated_at TIMESTAMP DEFAULT NOW()
);
CREATE TABLE fgedu_# 查看表结构
ecommerce=# \d shop.orders
Table “shop.orders”
Column | Type | Collation | Nullable | Default
——————-+—————————–+———–+———-+———————————————–
order_id | integer | | not null | nextval(‘shop.orders_order_id_seq’::regclass)
fgedu_id | integer | | not null |
order_date | date | | | now()
total_amount | numeric(10,2) | | not null |
status | character varying(20) | | | ‘pending’
shipping_address | text | | not null |
created_at | timestamp without time zone | | | now()
updated_at | timestamp without time zone | | | now()
Indexes:
“orders_pkey” PRIMARY KEY, btree (order_id)
Foreign-key constraints:
“orders_fgedu_id_fkey” FOREIGN KEY (fgedu_id) REFERENCES shop.fgedus(fgedu_id)
Check constraints:
“orders_status_check” CHECK (status = ANY (ARRAY[‘pending’::character varying, ‘processing’::character varying, ‘shipped’::character varying, ‘delivered’::character varying, ‘cancelled’::character varying]))
“orders_total_amount_check” CHECK (total_amount >= 0)

# 创建订单项表
ecommerce=# CREATE TABLE fgedu_shop.order_items (
order_item_id SERIAL PRIMARY KEY,
order_id INTEGER NOT NULL REFERENCES shop.orders(order_id),
product_id INTEGER NOT NULL REFERENCES shop.products(product_id),
quantity INTEGER NOT NULL CHECK (quantity > 0),
unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price > 0),
created_at TIMESTAMP DEFAULT NOW()
);
CREATE TABLE fgedu_# 查看表结构
ecommerce=# \d shop.order_items
Table “shop.order_items”
Column | Type | Collation | Nullable | Default
—————–+—————————–+———–+———-+—————————————————–
order_item_id | integer | | not null | nextval(‘shop.order_items_order_item_id_seq’::regclass)
order_id | integer | | not null |
product_id | integer | | not null |
quantity | integer | | not null |
unit_price | numeric(10,2) | | not null |
created_at | timestamp without time zone | | | now()
Indexes:
“order_items_pkey” PRIMARY KEY, btree (order_item_id)
Foreign-key constraints:
“order_items_order_id_fkey” FOREIGN KEY (order_id) REFERENCES shop.orders(order_id)
“order_items_product_id_fkey” FOREIGN KEY (product_id) REFERENCES shop.products(product_id)
Check constraints:
“order_items_quantity_check” CHECK (quantity > 0)
“order_items_unit_price_check” CHECK (unit_price > 0)

# 插入用户数据
ecommerce=# INSERT INTO shop.fgedus (fgeduname, password, email, first_name, last_name, phone, address) VALUES
(‘zhangsan’, ‘123456’, ‘zhangsan@fgedu.net.cn’, ‘张’, ‘三’, ‘13800138001’, ‘北京市朝阳区’),
(‘lisi’, ‘123456’, ‘lisi@fgedu.net.cn’, ‘李’, ‘四’, ‘13800138002’, ‘上海市浦东新区’),
(‘wangwu’, ‘123456’, ‘wangwu@fgedu.net.cn’, ‘王’, ‘五’, ‘13800138003’, ‘广州市天河区’);
INSERT 0 3

# 插入产品数据
ecommerce=# INSERT INTO shop.products (product_name, category, description, price, stock_quantity) VALUES
(‘PostgreSQL数据库实战’, ‘计算机图书’, ‘PostgreSQL数据库从入门到精通’, 89.90, 100),
(‘Python编程基础’, ‘计算机图书’, ‘Python语言入门教程’, 59.90, 200),
(‘Java核心技术’, ‘计算机图书’, ‘Java语言高级编程’, 129.90, 150),
(‘数据结构与算法’, ‘计算机图书’, ‘数据结构与算法分析’, 79.90, 80),
(‘机器学习实战’, ‘人工智能’, ‘机器学习算法与应用’, 99.90, 60);
INSERT 0 5

# 插入订单数据
ecommerce=# INSERT INTO shop.orders (fgedu_id, order_date, total_amount, status, shipping_address) VALUES
(1, ‘2026-04-01’, 244.38, ‘delivered’, ‘北京市朝阳区’),
(2, ‘2026-04-02’, 142.89, ‘processing’, ‘上海市浦东新区’),
(3, ‘2026-04-03’, 179.80, ‘shipped’, ‘广州市天河区’);
INSERT 0 3

# 插入订单项数据
ecommerce=# INSERT INTO shop.order_items (order_id, product_id, quantity, unit_price) VALUES
(1, 1, 1, 89.90),
(1, 2, 2, 59.90),
(2, 3, 1, 129.90),
(2, 4, 1, 79.90),
(3, 5, 2, 99.90);
INSERT 0 5

# 查看插入的数据
ecommerce=# SELECT * FROM shop.fgedus;
ecommerce=# SELECT * FROM shop.products;
ecommerce=# SELECT * FROM shop.orders;
ecommerce=# SELECT * FROM shop.order_items;

# 查询所有用户信息
ecommerce=# SELECT fgedu_id, fgeduname, email, first_name || ‘ ‘ || last_name AS full_name FROM shop.fgedus;
fgedu_id | fgeduname | email | full_name
———+———-+————————+———–
1 | zhangsan | zhangsan@fgedu.net.cn | 张 三
2 | lisi | lisi@fgedu.net.cn | 李 四
3 | wangwu | wangwu@fgedu.net.cn | 王 五
(3 rows)

# 查询价格大于100的产品
ecommerce=# SELECT product_id, product_name, category, price FROM shop.products WHERE price > 100;
product_id | product_name | category | price
————+————–+————+———
3 | Java核心技术 | 计算机图书 | 129.90
(1 row)

# 查询所有已发货的订单
ecommerce=# SELECT order_id, fgedu_id, order_date, total_amount, status FROM shop.orders WHERE status = ‘shipped’;
order_id | fgedu_id | order_date | total_amount | status
———-+———+————+————–+——–
3 | 3 | 2026-04-03 | 179.80 | shipped
(1 row)

# 查询订单详情（连接查询）
ecommerce=# SELECT
o.order_id,
u.fgeduname,
o.order_date,
o.total_amount,
o.status,
p.product_name,
i.quantity,
i.unit_price
FROM shop.orders o
JOIN shop.fgedus u ON o.fgedu_id = u.fgedu_id
JOIN shop.order_items i ON o.order_id = i.order_id
JOIN shop.products p ON i.product_id = p.product_id
ORDER BY o.order_id, i.order_item_id;
order_id | fgeduname | order_date | total_amount | status | product_name | quantity | unit_price
———-+———-+————+————–+————+——————–+———-+————
1 | zhangsan | 2026-04-01 | 244.38 | delivered | PostgreSQL数据库实战 | 1 | 89.90
1 | zhangsan | 2026-04-01 | 244.38 | delivered | Python编程基础 | 2 | 59.90
2 | lisi | 2026-04-02 | 142.89 | processing | Java核心技术 | 1 | 129.90
2 | lisi | 2026-04-02 | 142.89 | processing | 数据结构与算法 | 1 | 79.90
3 | wangwu | 2026-04-03 | 179.80 | shipped | 机器学习实战 | 2 | 99.90
(5 rows)

# 统计每个用户的订单数量和总消费金额
ecommerce=# SELECT
u.fgeduname,
COUNT(o.order_id) AS order_count,
SUM(o.total_amount) AS total_spent
FROM shop.fgedus u
LEFT JOIN shop.orders o ON u.fgedu_id = o.fgedu_id
GROUP BY u.fgeduname
ORDER BY total_spent DESC;
fgeduname | order_count | total_spent
———-+————-+————-
zhangsan | 1 | 244.38
lisi | 1 | 142.89
wangwu | 1 | 179.80
(3 rows)

# 查询库存不足50的产品
ecommerce=# SELECT product_id, product_name, category, stock_quantity FROM shop.products WHERE stock_quantity < 50; product_id | product_name | category | stock_quantity ------------+--------------+----------+---------------- (0 rows)

# 更新产品价格
ecommerce=# UPDATE shop.products SET price = 139.90, updated_at = NOW() WHERE product_id = 3;
UPDATE 1

# 更新订单状态
ecommerce=# UPDATE shop.orders SET status = ‘shipped’, updated_at = NOW() WHERE order_id = 2;
UPDATE 1

# 查看更新结果
ecommerce=# SELECT product_id, product_name, price FROM shop.products WHERE product_id = 3;
product_id | product_name | price
————+————–+——–
3 | Java核心技术 | 139.90
(1 row)

ecommerce=# SELECT order_id, status FROM shop.orders WHERE order_id = 2;
order_id | status
———-+——–
2 | shipped
(1 row)

# 删除订单项
ecommerce=# DELETE FROM shop.order_items WHERE order_item_id = 5;
DELETE 1

# 删除相关订单
ecommerce=# DELETE FROM shop.orders WHERE order_id = 3;
DELETE 1

# 查看删除结果
ecommerce=# SELECT * FROM shop.orders;
order_id | fgedu_id | order_date | total_amount | status | shipping_address | created_at | updated_at
———-+———+————+————–+————+——————+—————————-+—————————-
1 | 1 | 2026-04-01 | 244.38 | delivered | 北京市朝阳区 | 2026-04-02 15:00:00.123456 | 2026-04-02 15:00:00.123456
2 | 2 | 2026-04-02 | 142.89 | shipped | 上海市浦东新区 | 2026-04-02 15:00:00.123456 | 2026-04-02 15:05:00.123456
(2 rows)

# 为用户表创建索引
ecommerce=# CREATE INDEX idx_fgedus_email ON shop.fgedus(email);
CREATE INDEX

# 为产品表创建索引
ecommerce=# CREATE INDEX idx_products_category ON shop.products(category);
ecommerce=# CREATE INDEX idx_products_price ON shop.products(price);
CREATE INDEX

# 为订单表创建索引
ecommerce=# CREATE INDEX idx_orders_fgedu_id ON shop.orders(fgedu_id);
ecommerce=# CREATE INDEX idx_orders_status ON shop.orders(status);
CREATE INDEX

# 为订单项表创建索引
ecommerce=# CREATE INDEX idx_order_items_order_id ON shop.order_items(order_id);
ecommerce=# CREATE INDEX idx_order_items_product_id ON shop.order_items(product_id);
CREATE INDEX

# 查看所有索引
ecommerce=# \di shop.*;
List of relations
Schema | Name | Type | Owner | Table
——–+——————————+——-+———-+——————
shop | idx_order_items_order_id | index | pgsql | order_items
shop | idx_order_items_product_id | index | pgsql | order_items
shop | idx_orders_status | index | pgsql | orders
shop | idx_orders_fgedu_id | index | pgsql | orders
shop | idx_products_category | index | pgsql | products
shop | idx_products_price | index | pgsql | products
shop | idx_fgedus_email | index | pgsql | fgedus
shop | order_items_pkey | index | pgsql | order_items
shop | orders_pkey | index | pgsql | orders
shop | products_pkey | index | pgsql | products
shop | fgedus_pkey | index | pgsql | fgedus
shop | fgedus_email_key | index | pgsql | fgedus
shop | fgedus_fgeduname_key | index | pgsql | fgedus
(13 rows)

# 退出PostgreSQL命令行
# \q

# 使用pg_dump备份数据库
$ pg_dump -U pgsql ecommerce > ecommerce_backup.sql

# 查看备份文件
$ ls -l ecommerce_backup.sql
-rw-r–r–. 1 pgsql pgsql 123456 Apr 2 15:10 ecommerce_backup.sql

# 创建测试数据库用于恢复
$ psql -U pgsql -c “CREATE DATABASE ecommerce_test;”

# 恢复数据库
$ psql -U pgsql ecommerce_test < ecommerce_backup.sql # 验证恢复结果 $ psql -U pgsql -d ecommerce_test -c "SELECT COUNT(*) FROM shop.fgedus;" count ------- 3 (1 row)

# 登录PostgreSQL并连接到数据库
$ psql -U pgsql -d ecommerce

# 查看数据库大小
ecommerce=# SELECT pg_size_pretty(pg_fgedudb_size(‘ecommerce’));
pg_size_pretty
—————-
8751 kB
(1 row)

# 查看表大小
ecommerce=# SELECT relname AS table_name, pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_stat_fgedu_tables
WHERE schemaname = ‘shop’
ORDER BY pg_total_relation_size(relid) DESC;
table_name | total_size
————–+————
fgedus | 16 kB
products | 16 kB
orders | 16 kB
order_items | 16 kB
(4 rows)

# 查看索引使用情况
ecommerce=# SELECT
relname AS table_name,
indexrelname AS index_name,
idx_scan,
idx_tup_read,
idx_tup_fetch
FROM pg_stat_fgedu_indexes
WHERE schemaname = ‘shop’
ORDER BY idx_scan DESC;
table_name | index_name | idx_scan | idx_tup_read | idx_tup_fetch
————–+————————–+———-+————–+—————
orders | idx_orders_status | 1 | 1 | 1
products | idx_products_price | 1 | 1 | 1
fgedus | fgedus_pkey | 1 | 1 | 1
products | products_pkey | 1 | 3 | 3
order_items | idx_order_items_order_id | 1 | 5 | 5
order_items | idx_order_items_product_id | 0 | 0 | 0
orders | idx_orders_fgedu_id | 0 | 0 | 0
products | idx_products_category | 0 | 0 | 0
fgedus | idx_fgedus_email | 0 | 0 | 0
fgedus | fgedus_email_key | 0 | 0 | 0
fgedus | fgedus_fgeduname_key | 0 | 0 | 0
order_items | order_items_pkey | 0 | 0 | 0
(12 rows)

# 查看当前连接
ecommerce=# SELECT datname, usename, fgapplication_name, client_addr, state FROM pg_stat_activity;
datname | usename | fgapplication_name | client_addr | state
———-+———-+——————+————-+——–
ecommerce | pgsql | psql | ::1 | active
(1 row)