基于ERD创建含全约束的SQL表及ERD图SQL查询编写需求

Alright, let's tackle these two ERD-related SQL tasks head-on. I'll use a common e-commerce ERD as an example (since you didn't share the exact diagram), but you can easily adapt this to your specific entity relationships. Let's dive in!

First, let's make sure we cover all key constraints typically defined in an ERD:

• Primary Keys: Uniquely identify each record in a table
• Foreign Keys: Enforce relationships between entities
• NOT NULL: Mark fields that can't be empty
• UNIQUE: Ensure no duplicate values for specific fields
• CHECK Constraints: Enforce business rules (like valid statuses or positive prices)

Here's how you'd translate a standard e-commerce ERD into SQL tables with all these constraints:

-- Users entity: Stores customer details
CREATE TABLE Users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    full_name VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CHECK (email LIKE '%@%.%') -- Basic validation for email format
);

-- Products entity: Stores inventory items
CREATE TABLE Products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL CHECK (price > 0),
    stock_quantity INT NOT NULL DEFAULT 0 CHECK (stock_quantity >= 0),
    category VARCHAR(50) NOT NULL
);

-- Orders entity: Links to Users (each order belongs to one user)
CREATE TABLE Orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    total_amount DECIMAL(12,2) NOT NULL CHECK (total_amount > 0),
    status VARCHAR(20) NOT NULL CHECK (status IN ('Pending', 'Shipped', 'Delivered', 'Cancelled')),
    -- Foreign key constraint to enforce user validity
    FOREIGN KEY (user_id) REFERENCES Users(user_id)
        ON DELETE RESTRICT -- Prevent deleting users with existing orders
        ON UPDATE CASCADE -- Sync user_id changes to Orders
);

-- OrderItems junction entity: Links Orders and Products (many-to-many relationship)
CREATE TABLE OrderItems (
    order_item_id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL CHECK (quantity > 0),
    unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price > 0),
    -- Foreign keys for order and product relationships
    FOREIGN KEY (order_id) REFERENCES Orders(order_id)
        ON DELETE CASCADE -- Delete items if the parent order is removed
        ON UPDATE CASCADE,
    FOREIGN KEY (product_id) REFERENCES Products(product_id)
        ON DELETE RESTRICT -- Prevent deleting products in active orders
        ON UPDATE CASCADE,
    UNIQUE (order_id, product_id) -- Ensure a product isn't added twice to the same order
);

A quick note: AUTO_INCREMENT is MySQL-specific. If you're using PostgreSQL, use SERIAL or GENERATED AS IDENTITY; for SQL Server, use IDENTITY(1,1).

Now let's write practical queries that leverage the relationships in our example ERD. These are common use cases you might encounter:

Query 1: Get full order details for a specific user #

SELECT 
    o.order_id,
    o.order_date,
    o.status,
    p.product_name,
    oi.quantity,
    oi.unit_price,
    (oi.quantity * oi.unit_price) AS item_subtotal
FROM Orders o
JOIN OrderItems oi ON o.order_id = oi.order_id
JOIN Products p ON oi.product_id = p.product_id
JOIN Users u ON o.user_id = u.user_id
WHERE u.username = 'emily_rose'
ORDER BY o.order_date DESC;

Query 2: Calculate total sales per product category (only for delivered orders) #

SELECT 
    p.category,
    SUM(oi.quantity * oi.unit_price) AS total_category_sales,
    COUNT(DISTINCT o.order_id) AS total_orders
FROM Products p
JOIN OrderItems oi ON p.product_id = oi.product_id
JOIN Orders o ON oi.order_id = o.order_id
WHERE o.status = 'Delivered'
GROUP BY p.category
ORDER BY total_category_sales DESC;

Query 3: Find users who haven't placed any orders yet #

SELECT u.user_id, u.username, u.email
FROM Users u
LEFT JOIN Orders o ON u.user_id = o.user_id
WHERE o.order_id IS NULL;

Query 4: Update product stock after an order is shipped #

-- This uses a transaction to ensure stock updates are atomic
START TRANSACTION;

UPDATE Products p
SET p.stock_quantity = p.stock_quantity - oi.quantity
FROM Products p
JOIN OrderItems oi ON p.product_id = oi.product_id
JOIN Orders o ON oi.order_id = o.order_id
WHERE o.order_id = 123;

UPDATE Orders
SET status = 'Shipped'
WHERE order_id = 123;

COMMIT;

These queries directly use the relationships defined in the ERD—using JOIN to connect related tables, filtering with WHERE, and aggregating data across entities. Adjust them to match your specific ERD's entities and relationships!

内容的提问来源于stack exchange，提问作者Fuse Koda