数据库表创建

#存款类型表结构deposit表
CREATE TABLE deposit(
    savingID INT PRIMARY KEY AUTO_INCREMENT COMMENT '存款类型号',
    savingName VARCHAR(50) NOT NULL COMMENT '存款类型名称',
    descrip VARCHAR(100) NULL DEFAULT NULL COMMENT '存款类型名称'
)ENGINE = InnoDB AUTO_INCREMENT = 1  CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci;

#存款类型表结构deposit表
CREATE TABLE IF NOT EXISTS userInfo(
    customerID INT(8) PRIMARY KEY AUTO_INCREMENT COMMENT'客户编号',
    customerName VARCHAR(10) NOT NULL COMMENT'开户姓名',
    PID CHAR(18) NOT NULL COMMENT'身份证号',
    telephone CHAR(11) NOT NULL COMMENT'联系电话',
    address VARCHAR(50) COMMENT'居住地址'
)ENGINE=INNODB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci;

#银行卡信息表结构cardInfo表
CREATE TABLE cardInfo(
    cardID CHAR(8) PRIMARY KEY NOT NULL COMMENT'卡号', 
    savingID INT NOT NULL COMMENT '存款类型号',
    openDate TIMESTAMP DEFAULT NOW() NOT NULL COMMENT '开户日期',
    openMoney DECIMAL NOT NULL CHECK(openMoney >= 1) COMMENT '开户金额',
    balance DECIMAL NOT NULL CHECK(balance >= 1) COMMENT '余额',
    password VARCHAR(6) NOT NULL DEFAULT '888888' CHECK(password BETWEEN '000000' AND '999999') COMMENT '密码',
    lsReportLoss BIT NOT NULL DEFAULT 0 COMMENT '是否挂失',
    customerID INT NOT NULL COMMENT '客户编号',
    FOREIGN KEY(savingID) REFERENCES deposit(savingID),
    FOREIGN KEY(customerID) REFERENCES userInfo(customerID)
)ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci;

#交易信息表结构tradeInfo表
CREATE TABLE tradeInfo(
    transDate TIMESTAMP DEFAULT NOW() NOT NULL COMMENT '交易时间',
    cardID CHAR(8) NOT NULL COMMENT '卡号',
    transType CHAR(2) NOT NULL COMMENT '交易类型',
    transMoney DECIMAL NOT NULL COMMENT '交易金额',
    remark TEXT COMMENT '备注',
    FOREIGN KEY(cardID) REFERENCES cardInfo(cardID)
)ENGINE = InnoDB   CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci;