编辑代码

-- 创建数据库
CREATE DATABASE IF NOT EXISTS pet_shop;
USE pet_shop;

-- 1. 店员表
CREATE TABLE staff (
    staff_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    phone VARCHAR(20) UNIQUE,
    age INT CHECK (age >= 18),
    address VARCHAR(100)
);

-- 2. 顾客表
CREATE TABLE customer (
    customer_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    phone VARCHAR(20) UNIQUE,
    age INT
);

-- 3. 宠物表
CREATE TABLE pet (
    pet_id INT PRIMARY KEY AUTO_INCREMENT,
    gender ENUM('Male', 'Female') NOT NULL,
    price DECIMAL(10, 2) CHECK (price >= 0),
    weight DECIMAL(5, 2) CHECK (weight > 0),
    species VARCHAR(50) NOT NULL,
    age INT CHECK (age >= 0),
    customer_id INT NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customer(customer_id)
);

-- 4. 订单表 
CREATE TABLE order_info (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    staff_id INT NOT NULL,
    amount DECIMAL(10, 2) CHECK (amount >= 0),
    pet_id INT UNIQUE NOT NULL, -- 一对一关系
    FOREIGN KEY (customer_id) REFERENCES customer(customer_id),
    FOREIGN KEY (staff_id) REFERENCES staff(staff_id),
    FOREIGN KEY (pet_id) REFERENCES pet(pet_id)
);

-- 5. 店员-顾客关系表
CREATE TABLE staff_customer (
    staff_id INT NOT NULL,
    customer_id INT NOT NULL,
    PRIMARY KEY (staff_id, customer_id),
    FOREIGN KEY (staff_id) REFERENCES staff(staff_id),
    FOREIGN KEY (customer_id) REFERENCES customer(customer_id)
);

-- 插入5名店员数据
INSERT INTO staff (name, phone, age, address) VALUES
('张店长', '13800138001', 35, '陕西省西安市雁塔区科技路1号'),
('李美容师', '13800138002', 28, '陕西省西安市碑林区长安北路3号'),
('王兽医', '13800138003', 42, '陕西省西安市新城区长乐西路8号'),
('赵训犬师', '13800138004', 38, '陕西省西安市莲湖区西大街15号'),
('钱助理', '13800138005', 25, '陕西省西安市未央区未央路20号');

-- 插入6名顾客数据
INSERT INTO customer (name, phone, age) VALUES
('王先生', '13900139001', 32),
('李女士', '13900139002', 29),
('张同学', '13900139003', 22),
('刘太太', '13900139004', 45),
('陈先生', '13900139005', 38),
('杨女士', '13900139006', 27);

 -- 插入20条宠物数据
INSERT INTO pet (gender, price, weight, species, age, customer_id) VALUES
('Male', 2200.00, 5.5, '柯基犬', 2, 1),
('Female', 3800.00, 3.2, '布偶猫', 1, 2),
('Male', 1900.00, 6.8, '金毛犬', 3, 3),
('Female', 2600.00, 2.8, '英短猫', 2, 4),
('Male', 4100.00, 4.2, '柴犬', 1, 5),
('Female', 1600.00, 1.0, '仓鼠', 0, 6),
('Male', 3500.00, 7.5, '哈士奇', 4, 1),
('Female', 3900.00, 3.5, '暹罗猫', 2, 2),
('Male', 2700.00, 8.0, '拉布拉多', 3, 3),
('Female', 2000.00, 1.8, '龙猫', 1, 4),
('Male', 4200.00, 4.8, '萨摩耶', 2, 5),
('Female', 2800.00, 3.0, '美短猫', 1, 6),
('Male', 2400.00, 9.5, '阿拉斯加', 5, 1),
('Female', 3700.00, 3.8, '波斯猫', 3, 2),
('Male', 3100.00, 5.0, '边牧', 2, 3),
('Female', 1800.00, 0.9, '兔子', 0, 4),
('Male', 3400.00, 7.2, '德牧', 4, 5),
('Female', 4000.00, 3.2, '缅因猫', 2, 6),
('Male', 2300.00, 7.8, '比熊犬', 3, 1),
('Female', 2500.00, 2.0, '荷兰猪', 1, 2);

-- 插入10条订单数据
INSERT INTO order_info (customer_id, order_date, staff_id, amount, pet_id) VALUES
(1, '2025-01-05', 1, 2300.00, 1),
(2, '2025-01-12', 1, 3900.00, 2),
(3, '2025-01-18', 2, 2000.00, 3),
(4, '2025-02-03', 2, 2700.00, 4),
(5, '2025-02-15', 3, 4200.00, 5),
(6, '2025-03-02', 3, 1700.00, 6),
(1, '2025-03-10', 4, 3600.00, 7),
(2, '2025-04-05', 4, 4000.00, 8),
(3, '2025-04-18', 5, 2800.00, 9),
(4, '2025-04-25', 5, 2100.00, 10);

-- 建立店员与顾客的服务关系（每个店员服务1-2个顾客）
INSERT INTO staff_customer (staff_id, customer_id) VALUES
(1, 1), (1, 2),
(2, 3), (2, 4),
(3, 5), (3, 6),
(4, 1), (4, 3),
(5, 2), (5, 4);

-- 修改顾客电话
UPDATE customer SET phone = '13912345678' WHERE customer_id = 1;

-- 调整宠物价格
UPDATE pet SET price = 1800.00 WHERE pet_id = 1;

-- 删除宠物
DELETE FROM pet WHERE pet_id = 20;


-- 查询所有金毛犬
SELECT * FROM pet WHERE species = '金毛犬';

-- 查询10月订单
SELECT o.order_id, c.name AS customer, s.name AS staff, o.amount 
FROM order_info o
JOIN customer c ON o.customer_id = c.customer_id
JOIN staff s ON o.staff_id = s.staff_id
WHERE o.order_date BETWEEN '2023-10-01' AND '2023-10-31';

-- 创建视图存储顾客订单统计
CREATE VIEW customer_order_summary AS
SELECT 
    c.customer_id,
    c.name,
    c.phone,
    COUNT(o.order_id) AS total_orders
FROM customer c
LEFT JOIN order_info o ON c.customer_id = o.customer_id
GROUP BY c.customer_id;

-- 查询单个顾客订单总数
SELECT total_orders 
FROM customer_order_summary 
WHERE customer_id = 1;

-- 查询所有顾客订单统计
SELECT * FROM customer_order_summary ORDER BY total_orders DESC;

-- 添加宠物颜色字段
ALTER TABLE pet ADD color VARCHAR(20) AFTER species;


-- 删除表
DROP TABLE IF EXISTS order_info;