根据以上信息
建库
CREATE TABLE deposit (
savingID INT AUTO_INCREMENT PRIMARY KEY COMMENT '存款类型号',
savingName VARCHAR(255) NOT NULL COMMENT '存款类型名称',
descrip VARCHAR(255) COMMENT '描述'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE tradelnfo (
transDate TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '交易日期',
cardID CHAR(16) NOT NULL COMMENT '卡号',
transType CHAR(10) NOT NULL COMMENT '交易类型',
transMoney DECIMAL(10, 2) NOT NULL COMMENT '交易金额',
remark TEXT COMMENT '备注'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE cardInfo (
cardID CHAR(16) NOT NULL PRIMARY KEY COMMENT '卡号',
savinglD INT NOT NULL COMMENT '存款类型',
openDate TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '开户日期',
openMoney DECIMAL(15, 2) NOT NULL CHECK (openMoney >= 1) COMMENT '开户金额',
balance DECIMAL(15, 2) NOT NULL DEFAULT 1 CHECK (balance >= 1) COMMENT '余额',
password VARCHAR(6) NOT NULL DEFAULT '888888' CHECK (password REGEXP '^[0-9]{6}$') COMMENT '密码',
IsReportLoss BIT NOT NULL DEFAULT b'0' COMMENT '是否挂失',
customerlD INT NOT NULL COMMENT '客户编号',
FOREIGN KEY (savinglD) REFERENCES deposit(savingID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
ALTER TABLE cardInfo
ADD CONSTRAINT fk_cardInfo_userlnfo
FOREIGN KEY (customerlD)
REFERENCES userlnfo(customerlD);
CREATE TABLE userlnfo (
customerlD INT NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '客户编号,自动编号从1开始',
customerName CHAR(255) NOT NULL COMMENT '开户名',
PID CHAR(18) NOT NULL UNIQUE CHECK (LENGTH(PID) = 18 OR LENGTH(PID) = 15) COMMENT '身份证号,必须是18位或15位',
telephone CHAR(11) NOT NULL COMMENT '联系电话,手机号11位',
address VARCHAR(255) COMMENT '居住地址'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;