通过所提供的E-R图和数据库模型图完成库表的创建,并插入适量的数据.要求必须使用SQL命令进行构建。
1、
CREATE TABLE Cardinfo(
CardID CHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '卡号',
SavingID VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '存款类型',
OpenDate TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE
CURRENT_TIMESTAMP COMMENT '开户日期',
OpenMoney DECIMAL(18,2) NOT NULL COMMENT '开户金额',
Balance DECIMAL(18,2) NOT NULL COMMENT '余额',
Password VARCHAR(6) NOT NULL DEFAULT('888888') COMMENT '密码',
IsReportLoss BIT NOT NULL DEFAULT(1) COMMENT '是否挂失,是 0 否 1',
CustomerID INT NOT NULL COMMENT '客户编号',
PRIMARY KEY (cardID)
)ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci;
## 建立外键
ALTER TABLE Cardinfo ADD Constraint s_id FOREIGN KEY (CustomerID) REFERENCES Userinfo (CustomerID);
ALTER TABLE Cardinfo ADD Constraint saving_id FOREIGN KEY (SavingID) REFERENCES DEPOSIT (SavingID); //需建立DEPOSIT表后输入
2、
DROP TABLE IF EXISTS `userInfo`;
CREATE TABLE `userInfo` #用户信息表
(
`customerID` INT(4) PRIMARY KEY AUTO_INCREMENT COMMENT '客户编号',
`customerName` CHAR(8) NOT NULL COMMENT '用户名称',
`PID` CHAR(18) UNIQUE NOT NULL COMMENT '身份证号',
`telephone` CHAR(20) NOT NULL COMMENT '手机号码',
`address` VARCHAR(50) COMMENT '居住地址'
)
3、
CREATE TABLE tradeInfo
(
id int not null primary key auto_increment,
tradeDate TIMESTAMP default current_timestamp NOT NULL COMMENT '交易日期',
cardID CHAR(16) NOT NULL COMMENT '卡号',
tradeType CHAR(4) NOT NULL COMMENT '交易类型',
tradeMoney DECIMAL(20,2) NOT NULL COMMENT '交易金额',
remark TEXT COMMENT '备注'
)COMMENT='交易信息表';
约束:
ALTER TABLE tradeInfo
ADD CONSTRAINT FK_cardID FOREIGN KEY(cardID) REFERENCES cardInfo(cardID);
4、
CREATE TABLE deposit
(
savingID INT(4) AUTO_INCREMENT PRIMARY KEY,
savingName VARCHAR(20) NOT NULL COMMENT '存款类型名称',
descrip VARCHAR(50) COMMENT '描述'
)COMMENT='存款类型表';