MYSQL_练习_5个表_24.3.18

-- 如果数据库t4存在，则删除它
drop database if exists t4;

-- 如果数据库t4不存在，则创建它
create database if not exists t4;

-- 选择数据库t4进行后续操作
use t4;

-- 如果account表存在，则删除它
drop table if exists account;

-- 创建account表，包含用户信息
create table account (
    -- 用户号，长度为6的字符类型，设置为主键，不允许重复
    userid char(6) not null primary key comment '用户号',
    -- 用户名，最大长度为10的可变长字符类型，不允许为空
    fullname varchar(10) not null comment '用户名',
    -- 密码，最大长度为20的可变长字符类型，不允许为空
    password varchar(20) not null comment '密码',
    -- 性别，长度为2的字符类型，不允许为空
    sex char(2) not null comment '性别',
    -- 住址，最大长度为40的可变长字符类型，可以为空
    address varchar(40) null comment '住址',
    -- 邮箱，最大长度为20的可变长字符类型，可以为空
    email varchar(20) null comment '邮箱',
    -- 电话，长度为11的字符类型，不允许为空
    phone varchar(11) not null comment '电话'
);

-- 如果category表存在，则删除它
drop table if exists category;

-- 创建category表，包含商品分类信息
create table category(
    -- 分类号，长度为10的字符类型，设置为主键，不允许重复
    catid CHAR(10) NOT NULL PRIMARY KEY COMMENT '分类号',
    -- 分类名称，最大长度为20的可变长字符类型，不允许为空
    catname VARCHAR(20) NOT NULL COMMENT '分类名称'
);

-- 如果product表存在，则删除它
drop table if exists product;

-- 创建product表，包含商品信息
create table product(
    -- 商品号，长度为10的字符类型，设置为主键，不允许重复
    productid CHAR(10) NOT NULL PRIMARY KEY COMMENT '商品号',
    -- 分类号，长度为10的字符类型，与category表的catid列相关联
    catid CHAR(10) NOT NULL COMMENT '分类号',
    -- 商品名，最大长度为30的可变长字符类型，不允许为空
    name VARCHAR(30) NOT NULL COMMENT '商品名',
    -- 商品介绍，文本类型，可以为空
    descn TEXT NULL COMMENT '商品介绍',
    -- 市场价格，十进制数类型，最多包含10位数字，其中2位小数，可以为空
    listprice DECIMAL(10,2) NULL COMMENT '市场价格',
    -- 当前价格，十进制数类型，最多包含10位数字，其中2位小数，不允许为空
    unitcost DECIMAL(10,2) NULL COMMENT '当前价格',
    -- 商品数量，整数类型，不允许为空
    qty INT(11) NOT NULL COMMENT '数量'
);

-- 为product表添加外键约束，将catid列与category表的catid列相关联
ALTER Table product
    ADD FOREIGN KEY (catid)
        REFERENCES category(catid)
        ON DELETE RESTRICT; -- 如果category表中的相关记录被删除，product表中的记录不会被删除

-- 如果orders表存在，则删除它
drop table if exists orders;

-- 创建orders表，包含订单信息
create table orders(
    -- 订单号，整数类型，自动递增，不允许为空，设置为主键
    orderid INT(11) NOT NULL COMMENT '订单号' PRIMARY KEY auto_increment,
    -- 用户号，长度为6的字符类型，与account表的userid列相关联
    userid CHAR(6) NOT NULL COMMENT '用户号',
    -- 订单日期，日期时间类型，默认为当前时间，不允许为空
    orderdate datetime NOT NULL COMMENT '订单日期' default now(),
    -- 订单总价，十进制数类型，最多包含10位数字，其中2位小数，可以为空
    totalprice DECIMAL(10,2) NULL COMMENT '订单总价',
    -- 订单状态，整数类型，取值范围为0或1，表示订单是否完成，默认为1表示未完成
    status TINYINT(1) NULL COMMENT '订单状态' check(status=0 or status=1) DEFAULT 1
);

-- 为orders表添加外键约束，将userid列与account表的userid列相关联
ALTER Table orders
    ADD FOREIGN KEY(userid)
        REFERENCES `account`(userid)
        ON DELETE RESTRICT -- 如果account表中的相关记录被删除，orders表中的记录不会被删除
        ON UPDATE RESTRICT; -- 如果account表中的相关记录被更新，orders表中的记录不会更新

-- 如果lineitem表存在，则删除它
drop table if exists lineitem;

-- 创建lineitem表，包含订单明细信息
create table lineitem(
    -- 订单号，整数类型，与orders表的orderid列相关联
    orderid INT(11) NOT NULL COMMENT '订单号',
    -- 商品号，长度为10的字符类型，与product表的productid列相关联
    itemid CHAR(10) NOT NULL COMMENT '商品号',
    -- 数量，整数类型，不允许为空
    quantity INT(11) NOT NULL COMMENT '数量',
    -- 单价，十进制数类型，最多包含10位数字，其中2位小数，不允许为空
    unitprice DECIMAL(10,2) NOT NULL COMMENT '单价',
    -- 复合主键，由orderid和itemid组成
    primary key(orderid,itemid)
);

-- 为lineitem表添加外键约束，将itemid列与product表的productid列相关联
ALTER Table lineitem
    ADD FOREIGN KEY (itemid)
    REFERENCES product(productid)
    ON DELETE CASCADE -- 如果product表中的相关记录被删除，lineitem表中的相关记录也会被删除
    ON UPDATE CASCADE; -- 如果product表中的相关记录被更新，lineitem表中的相关记录也会更新

-- 为lineitem表添加外键约束，将orderid列与orders表的orderid列相关联
ALTER Table lineitem
    ADD FOREIGN KEY (orderid)
    REFERENCES orders(orderid)
    ON DELETE CASCADE; -- 如果orders表中的相关记录被删除，lineitem表中的相关记录也会被删除

-- 为account表添加检查约束，确保sex列的值只能是'男'或'女'
ALTER Table `account`
    ADD CHECK(sex in ('男','女'));