书库sql

CREATE DATABASE IF NOT EXISTS BOOKS
CHARACTER SET UTF8MB4;
USE BOOKS;
create table if not exists userinfo
(
    Uno        char(6)     primary key comment '客户编号',
    Uname      varchar(10)          not null comment '客户姓名',
    Utelephone char(11)             not null comment '客户电话',
    Uregdate   date                 null comment '注册日期',
    Urank      char(2) default 'V0' null comment '客户级别',
    Udiscount  float   default 0.98 null comment '客户折扣',
    Upoints    int     default 0    null comment '客户积分'
)comment '客户信息表';
create table if not exists booktype
(
    BTno   char(4) primary key comment '图书分类编号',
    BTname varchar(10) not null comment '图书分类名称'
)comment '图书分类表';
 
create table if not exists booksubtype
(
    BSTno   char(6)  primary key comment '图书子类编号',
    BTno    char(4)     null comment '图书分类编号',
    BSTname varchar(10) not null comment '图书子类名称',
    constraint BookSubType_booktype_BTno_fk
        foreign key (BTno) references booktype (BTno)
)comment '图书子类表';
 
create table if not exists bookinfo
(
    Bno        char(9)   primary key comment '图书编号',
    Bname      varchar(30) not null comment '图书名称',
    ISBN       varchar(20) null comment '国际标准书号',
    Bwriter    varchar(20) null comment '作者',
    Bpublisher varchar(20) null comment '出版社',
    Bpubdate   date        null comment '出版日期',
    BTno       char(4)     null comment '图书分类编号',
    BSTno      char(6)     null comment '图书子类编号',
    Bprice     float       null comment '图书价格',
    Bunit      varchar(4)  null comment '图书单位',
    Bstock     int         null comment '图书库存量',
    constraint BookInfo_booksubtype_BSTno_fk
        foreign key (BSTno) references booksubtype (BSTno),
    constraint BookInfo_booktype_BTno_fk
        foreign key (BTno) references booktype (BTno)
)comment '图书信息表';
create table if not exists employeeinfo
(
    Eno        char(3)   primary key comment '职工编号',
    Ename      varchar(10)               not null comment '职工姓名',
    Egender    char       default '男'   null comment '职工性别',
    Ebirth     date                      null comment '出生日期',
    Ehiredate  date                      null comment '入职日期',
    Etelephone char(11)                  null comment '职工电话',
    Eaddress   varchar(50)               null comment '家庭住址',
    Erole      varchar(8) default '销售' null comment '职工角色'
)comment '职工信息表';
 
create table if not exists booksale
(
    Bsid       BINARY(16) PRIMARY KEY  DEFAULT (UUID_TO_BIN(UUID()))
    COMMENT '数量编号',
    BSdate     date        not null comment '销售日期',
    Uno        char(6)     null comment '客户编号',
    Bno        char(9)     null comment '图书编号',
    Eno        char(3)     null comment '销售员编号',
    BSnum      int         null comment '销售数量',
    BSprice    float       null comment '销售单价',
    BSdiscount float       null comment '销售折扣',
    BSpoints   int         null comment '积分抵扣',
    Bsamount   float       null comment '销售总价',
    BSmemo     varchar(50) null comment '备注',
    constraint BookSale_bookinfo_Bno_fk
        foreign key (Bno) references bookinfo (Bno),
    constraint BookSale_employeeinfo_Eno_fk
        foreign key (Eno) references employeeinfo (Eno),
    constraint BookSale_userinfo_Uno_fk
        foreign key (Uno) references userinfo (Uno)
)comment '图书销售表';
 
 
 
create table if not exists bookpurchase
(
    BPid    int auto_increment primary key comment '序号',
    BPdate  date    null comment '采购日期',
    Bno     char(9) null comment '图书编号',
    BPnum   int     null comment '采购数量',
    BPprice float   null comment '采购单价',
    Eno     char(3) null comment '采购员编号',
    constraint BookPurchase_bookinfo_Bno_fk
        foreign key (Bno) references bookinfo (Bno),
    constraint BookPurchase_employeeinfo_Eno_fk
        foreign key (Eno) references employeeinfo (Eno)
)comment '图书进货表';