Docker Desktop | 本地化挂载 Postgresql挂载夸克网盘链接PowerShell创建postgre1 简单2想自定义的UTCCST在Docker Desktop中查看信息StateEnv查看信息版本目录差别测试-- -- seed.sql —— myapp 测试数据可重复执行会先清空重建 -- DROP TABLE IF EXISTS order_items CASCADE; DROP TABLE IF EXISTS orders CASCADE; DROP TABLE IF EXISTS products CASCADE; DROP TABLE IF EXISTS users CASCADE; -- 用户表顺便演示 PG 特色JSONB 和数组类型 CREATE TABLE users ( id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, username VARCHAR(50) NOT NULL UNIQUE, email VARCHAR(100) NOT NULL UNIQUE, password_hash TEXT NOT NULL, profile JSONB NOT NULL DEFAULT {}, tags TEXT[] NOT NULL DEFAULT {}, is_active BOOLEAN NOT NULL DEFAULT TRUE, created_at timestamp NOT NULL DEFAULT now() ); -- 商品表 CREATE TABLE products ( id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name VARCHAR(100) NOT NULL, price NUMERIC(10,2) NOT NULL CHECK (price 0), stock INT NOT NULL DEFAULT 0 CHECK (stock 0), description TEXT, created_at TIMESTAMPTZ NOT NULL DEFAULT now() ); -- 订单表 CREATE TABLE orders ( id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, user_id BIGINT NOT NULL REFERENCES users(id), status VARCHAR(20) NOT NULL DEFAULT pending CHECK (status IN (pending,paid,shipped,completed,cancelled)), total NUMERIC(12,2) NOT NULL DEFAULT 0, created_at TIMESTAMPTZ NOT NULL DEFAULT now() ); -- 订单明细 CREATE TABLE order_items ( id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, order_id BIGINT NOT NULL REFERENCES orders(id) ON DELETE CASCADE, product_id BIGINT NOT NULL REFERENCES products(id), quantity INT NOT NULL CHECK (quantity 0), unit_price NUMERIC(10,2) NOT NULL ); -- 索引 CREATE INDEX idx_orders_user ON orders(user_id); CREATE INDEX idx_orders_status ON orders(status); CREATE INDEX idx_items_order ON order_items(order_id); CREATE INDEX idx_users_tags ON users USING GIN (tags); -- 数组字段的 GIN 索引MySQL 没有 -- 测试数据 INSERT INTO users (username, email, password_hash, profile, tags) VALUES (alice, aliceexample.com, fakehash1, {age: 28, city: 北京}, ARRAY[vip,early-adopter]), (bob, bobexample.com, fakehash2, {age: 35, city: 上海}, ARRAY[vip]), (charlie, charlieexample.com, fakehash3, {age: 22, city: 深圳}, ARRAY[]::TEXT[]), (diana, dianaexample.com, fakehash4, {age: 31, city: 广州}, ARRAY[beta-tester]), (eve, eveexample.com, fakehash5, {age: 26, city: 杭州}, ARRAY[vip]); INSERT INTO products (name, price, stock, description) VALUES (机械键盘 K870, 399.00, 120, 红轴 87 键), (无线鼠标 M220, 129.00, 300, 2.4G 蓝牙双模), (4K 显示器 27寸, 1899.00, 45, IPS 面板), (USB-C 扩展坞, 249.00, 80, 8 合 1), (降噪耳机 HC45, 799.00, 60, 主动降噪); INSERT INTO orders (user_id, status, total) VALUES (1, completed, 528.00), -- alice: 键盘 鼠标 (1, pending, 799.00), -- alice: 耳机 (2, paid, 1899.00), -- bob: 显示器 (3, shipped, 258.00), -- charlie: 鼠标 x2 (4, cancelled, 129.00), -- diana: 鼠标已取消 (5, paid, 1047.00); -- eve: 键盘x2 扩展坞 INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES (1, 1, 1, 399.00), (1, 2, 1, 129.00), (2, 5, 1, 799.00), (3, 3, 1, 1899.00), (4, 2, 2, 129.00), (5, 2, 1, 129.00), (6, 1, 2, 399.00), (6, 4, 1, 249.00); -- 验证查询 -- 每个用户的订单数和实际消费排除已取消 SELECT u.username, COUNT(o.id) AS order_count, COALESCE(SUM(o.total), 0) AS total_spent FROM users u LEFT JOIN orders o ON o.user_id u.id AND o.status cancelled GROUP BY u.id, u.username ORDER BY total_spent DESC; -- 卖得最好的商品 SELECT p.name, SUM(i.quantity) AS sold FROM order_items i JOIN products p ON p.id i.product_id GROUP BY p.id, p.name ORDER BY sold DESC;由于UTC所以我们在进入客户端查看的时候也需要配置时区客户端配置时区√ 有设置× 无设置
