编辑代码

DROP DATABASE IF EXISTS mall-db;
CREATE DATABASE mall-db;
use mall-db;
DROP TABLE IF EXISTS customers;
CREATE TABLE customers (
	customer-id INT PRIMARY KEY AUTO_INCREMENT,
	name VARCHAR(30),
	phone CHAR(11) UNIQUE NOT NULL,
    reg_date DATE NOT NULL,
    level ENUM('普通','白银','黄金','铂金') DEFAULT'普通'
);
DROP TABLE IF EXISTS products;
CREATE TABLE products(
product_id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
category VARCHAR(20) NOT NULL,
price DECIMAL(10,2) NOT NULL CHECk(price>0),
stock INT DEFAULT 0 CHECk(stock>=0)
);
DROP TABLE IF EXISTS orders;
CREATE TABLE orders(
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL FOREING KEY,
    order_date DATETIME NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL,
    status ENUM('待付款','已付款','已发货','未发货') DEFAULT '待付款'
) ;
DROP TABLE IF EXISTS order_details;
CREATE TABLE order_details(
    detail_id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT  NOT NULL FOREING KEY,
    product_id INT NOT NUll FOREING KEY,
    quanity INT NOT NULL CHECk(quanity>0),
    subtotal DECIMAL(10,2) NOT NUll 
);
INSERT INTO customers VALUES
 ('张三','13800138001','2023-01-15','黄金会员'),
 ('李四','139001139002','2023-02-20','普通会员'),
 ('王芳','13700137003','2023-03-10','铂金会员'),
 ('赵明','13600136004','2023-04-05','白银会员'),
 ('刘婷','13500135005','2023-05-12','普通会员');
INSERT INTO products VALUES
('iPhone14','手机类','5999.00','100'),
('华为P60','手机类','4999.00','80'),
 ('小米电视','家电类','3299.00','50'),
 ('联想笔记本','电脑类','6999.00','30'),
 ('索尼耳机','数码类','899.0','200'),
 ('耐克运动鞋','服饰类','699.00','150'),
 ('《三体》全集','图书类','128.00','300');
 INSERT INTO orders VALUES
 ('张三','2023-06-01','10：30：25','11998.00','状态已完成'),
SELECT * FROM student;