用于房屋销售的SQL数据模型

-- 创建房源表
CREATE TABLE Houses (
    HouseID INT PRIMARY KEY,
    Address VARCHAR(255),
    Price DECIMAL(10, 2),
    Area INT,
    Bedrooms INT,
    Bathrooms INT,
    Status VARCHAR(50)
);

-- 创建客户表
CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    Name VARCHAR(100),
    Phone VARCHAR(20),
    Email VARCHAR(100)
);

-- 创建订单表
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    HouseID INT,
    CustomerID INT,
    OrderDate DATE,
    TotalAmount DECIMAL(10, 2),
    FOREIGN KEY (HouseID) REFERENCES Houses(HouseID),
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

-- 插入房源数据示例
INSERT INTO Houses (HouseID, Address, Price, Area, Bedrooms, Bathrooms, Status)
VALUES (1, '123 Main St', 500000, 2000, 3, 2, '在售');

-- 插入客户数据示例
INSERT INTO Customers (CustomerID, Name, Phone, Email)
VALUES (1, 'John Doe', '1234567890', 'john.doe@example.com');

-- 插入订单数据示例
INSERT INTO Orders (OrderID, HouseID, CustomerID, OrderDate, TotalAmount)
VALUES (1, 1, 1, '2023-04-01', 500000);

-- 查询在售房源示例
SELECT * FROM Houses WHERE Status = '在售';