— 1. 创建表空间
CREATE TABLESPACE fgedutbs LOCATION ‘/postgresql/tablespace’;

— 2. 创建用户表
CREATE TABLE fgedu_users (
id SERIAL PRIMARY KEY,
username VARCHAR(50) UNIQUE NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
password VARCHAR(100) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

— 3. 创建商品表
CREATE TABLE fgedu_products (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
description TEXT,
price DECIMAL(10,2) NOT NULL,
stock INTEGER NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) TABLESPACE fgedutbs;

— 4. 创建订单表
CREATE TABLE fgedu_orders (
id SERIAL PRIMARY KEY,
user_id INTEGER REFERENCES fgedu_users(id),
total_amount DECIMAL(10,2) NOT NULL,
status VARCHAR(20) NOT NULL DEFAULT ‘pending’,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) TABLESPACE fgedutbs;

— 5. 创建订单商品表
CREATE TABLE fgedu_order_items (
id SERIAL PRIMARY KEY,
order_id INTEGER REFERENCES fgedu_orders(id),
product_id INTEGER REFERENCES fgedu_products(id),
quantity INTEGER NOT NULL,
price DECIMAL(10,2) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) TABLESPACE fgedutbs;

— 6. 创建索引
CREATE INDEX idx_fgedu_users_email ON fgedu_users(email);
CREATE INDEX idx_fgedu_products_name ON fgedu_products(name);
CREATE INDEX idx_fgedu_orders_user_id ON fgedu_orders(user_id);
CREATE INDEX idx_fgedu_orders_status ON fgedu_orders(status);
CREATE INDEX idx_fgedu_order_items_order_id ON fgedu_order_items(order_id);
CREATE INDEX idx_fgedu_order_items_product_id ON fgedu_order_items(product_id);

— 1. 插入用户数据
INSERT INTO fgedu_users (username, email, password) VALUES
(‘admin’, ‘admin@fgedu.net.cn’, ‘admin123’),
(‘user1’, ‘user1@fgedu.net.cn’, ‘user123’),
(‘user2’, ‘user2@fgedu.net.cn’, ‘user123’);

— 2. 插入商品数据
INSERT INTO fgedu_products (name, description, price, stock) VALUES
(‘iPhone 15’, ‘Apple iPhone 15’, 5999.99, 100),
(‘MacBook Pro’, ‘Apple MacBook Pro’, 12999.99, 50),
(‘AirPods Pro’, ‘Apple AirPods Pro’, 1999.99, 200);

— 3. 插入订单和订单商品（使用事务）
BEGIN;

— 插入订单
INSERT INTO fgedu_orders (user_id, total_amount, status) VALUES (2, 7999.98, ‘completed’) RETURNING id;

— 假设返回的订单ID为1
— 插入订单商品
INSERT INTO fgedu_order_items (order_id, product_id, quantity, price) VALUES
(1, 1, 1, 5999.99),
(1, 3, 1, 1999.99);

— 更新商品库存
UPDATE fgedu_products SET stock = stock – 1 WHERE id = 1;
UPDATE fgedu_products SET stock = stock – 1 WHERE id = 3;

— 提交事务
COMMIT;

— 4. 更新订单状态
UPDATE fgedu_orders SET status = ‘shipped’ WHERE id = 1;

— 5. 删除过期订单
DELETE FROM fgedu_order_items WHERE order_id IN (
SELECT id FROM fgedu_orders WHERE created_at < NOW() - INTERVAL '30 days' ); DELETE FROM fgedu_orders WHERE created_at < NOW() - INTERVAL '30 days';

— 1. 查询用户订单数和总消费
SELECT
u.id, u.username, u.email,
COUNT(o.id) AS order_count,
SUM(o.total_amount) AS total_spent
FROM fgedu_users u
LEFT JOIN fgedu_orders o ON u.id = o.user_id
GROUP BY u.id, u.username, u.email
ORDER BY total_spent DESC;

— 2. 查询热销商品
SELECT
p.id, p.name, p.price,
SUM(oi.quantity) AS total_quantity,
SUM(oi.quantity * oi.price) AS total_sales
FROM fgedu_products p
JOIN fgedu_order_items oi ON p.id = oi.product_id
JOIN fgedu_orders o ON oi.order_id = o.id
WHERE o.status = ‘completed’
GROUP BY p.id, p.name, p.price
ORDER BY total_sales DESC
LIMIT 10;

— 3. 查询月度销售统计
SELECT
TO_CHAR(o.created_at, ‘YYYY-MM’) AS month,
COUNT(o.id) AS order_count,
SUM(o.total_amount) AS total_sales
FROM fgedu_orders o
WHERE o.status = ‘completed’
GROUP BY TO_CHAR(o.created_at, ‘YYYY-MM’)
ORDER BY month;

— 4. 查询用户最近订单
SELECT
u.username,
o.id AS order_id,
o.total_amount,
o.status,
o.created_at
FROM fgedu_users u
JOIN fgedu_orders o ON u.id = o.user_id
WHERE o.created_at > NOW() – INTERVAL ‘7 days’
ORDER BY o.created_at DESC;

— 1. 创建角色
CREATE ROLE fgedu_admin;
CREATE ROLE fgedu_customer;
CREATE ROLE fgedu_analyst;

— 2. 授予权限
— 管理员权限
GRANT ALL PRIVILEGES ON DATABASE fgedudb TO fgedu_admin;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO fgedu_admin;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO fgedu_admin;

— 客户权限
GRANT CONNECT ON DATABASE fgedudb TO fgedu_customer;
GRANT USAGE ON SCHEMA public TO fgedu_customer;
GRANT SELECT ON fgedu_products TO fgedu_customer;
GRANT INSERT, SELECT ON fgedu_orders TO fgedu_customer;
GRANT INSERT, SELECT ON fgedu_order_items TO fgedu_customer;
GRANT SELECT ON fgedu_users TO fgedu_customer;
GRANT UPDATE ON fgedu_users TO fgedu_customer;

— 分析师权限
GRANT CONNECT ON DATABASE fgedudb TO fgedu_analyst;
GRANT USAGE ON SCHEMA public TO fgedu_analyst;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO fgedu_analyst;

— 3. 创建用户并分配角色
CREATE USER admin_user WITH PASSWORD ‘Admin123@’;
GRANT fgedu_admin TO admin_user;

CREATE USER customer_user WITH PASSWORD ‘Customer123@’;
GRANT fgedu_customer TO customer_user;

CREATE USER analyst_user WITH PASSWORD ‘Analyst123@’;
GRANT fgedu_analyst TO analyst_user;

— 4. 测试权限
— 以customer_user身份登录
— 可以查询商品
SELECT * FROM fgedu_products;
— 可以下单
INSERT INTO fgedu_orders (user_id, total_amount, status) VALUES (2, 100.00, ‘pending’);
— 不能删除商品
DELETE FROM fgedu_products WHERE id = 1; — 会失败

— 1. 死锁
— 问题：事务死锁
— 解决：
— – 保持事务简短
— – 以相同的顺序访问表
— – 使用适当的隔离级别
— – 监控并及时终止长时间运行的事务

— 2. 慢查询
— 问题：查询执行时间过长
— 解决：
— – 使用EXPLAIN分析查询计划
— – 创建合适的索引
— – 优化查询语句
— – 调整配置参数

— 3. 索引失效
— 问题：索引未被使用
— 解决：
— – 检查索引是否存在
— – 检查查询条件是否使用索引列
— – 检查索引统计信息是否最新
— – 重建索引

— 4. 权限错误
— 问题：权限不足
— 解决：
— – 检查用户权限
— – 授予必要的权限
— – 检查角色继承关系
— – 检查对象所有权

— 5. 语法错误
— 问题：SQL语法错误
— 解决：
— – 检查SQL语句语法
— – 检查引号和括号
— – 检查关键字拼写
— – 使用参数化查询

— 1. 查询优化
— – 使用索引覆盖查询
SELECT id, username FROM fgedu_users WHERE email = ‘user@fgedu.net.cn’;
— – 避免使用函数在索引列上
— 不好的例子：SELECT * FROM fgedu_users WHERE UPPER(username) = ‘ADMIN’;
— 好的例子：SELECT * FROM fgedu_users WHERE username = ‘admin’;
— – 避免使用LIKE ‘%xxx’
— 不好的例子：SELECT * FROM fgedu_users WHERE username LIKE ‘%admin%’;
— 好的例子：SELECT * FROM fgedu_users WHERE username LIKE ‘admin%’;

— 2. 插入优化
— – 使用批量插入
INSERT INTO fgedu_users (username, email) VALUES
(‘user1’, ‘user1@fgedu.net.cn’),
(‘user2’, ‘user2@fgedu.net.cn’);
— – 使用COPY命令导入大量数据
COPY fgedu_users (username, email) FROM ‘/data/users.csv’ DELIMITER ‘,’ CSV HEADER;

— 3. 更新优化
— – 只更新必要的列
UPDATE fgedu_users SET email = ‘newemail@fgedu.net.cn’ WHERE id = 1;
— – 使用条件更新，避免全表更新
UPDATE fgedu_users SET status = ‘active’ WHERE last_login > NOW() – INTERVAL ’30 days’;

— 4. 删除优化
— – 使用批量删除，避免全表删除
DELETE FROM fgedu_users WHERE id IN (1, 2, 3);
— – 使用TRUNCATE替代DELETE删除全表
TRUNCATE TABLE fgedu_users;

— 5. 事务优化
— – 保持事务简短
BEGIN;
— 执行少量操作
COMMIT;
— – 使用合适的隔离级别
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;