MySQL创建表练习

        通过提供的E-R图和数据库模型图完成银行数据库数据表的创建

E-R图

数据库模型图和数据表的创建

客户信息表结构

客户信息表创建

CREATE TABLE UserInfo (
CustomerID INT PRIMARY KEY AUTO_INCREMENT COMMENT '客户编号',
CustormrName CHAR CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '开户名',
PID CHAR CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci UNIQUE NOT NULL CHECK (LENGTH(PID) = 18 OR LENGTH(PID) = 15) COMMENT '身份证号',
Telephone CHAR CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL CHECK (LENGTH(Telephone) = 11) COMMENT '联系电话',
Address VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '居住地址，可选输入'
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci;

存款类型表结构

存款类型表创建

CREATE TABLE Deposit (
SavingID INT PRIMARY KEY AUTO_INCREMENT COMMENT '存款类型号',
SavingName VARCHAR(255)  CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '存款类型名称',
Descrip VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '描述，可空'
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci;

银行卡信息表结构

银行信息表创建

CREATE TABLE CardInfo (
CardID CHAR CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci PRIMARY KEY COMMENT '卡号',
SavingID INT NOT NULL COMMENT '存款类型号',
OpenDate TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '开户日期',
OpenMoney DECIMAL NOT NULL CHECK (OpenMoney > 1) COMMENT '开户金额',
Balance DECIMAL NOT NULL CHECK (Balance > 1) COMMENT '余额',
Password VARCHAR(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL CHECK (Password LIKE '[0-9][0-9][0-9][0-9][0-9][0-9]') COMMENT '密码',
IsReportLoss 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;

交易信息表结构

交易信息表创建

CREATE TABLE TardeInfo (
TransDate TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '交易日期',
CardID CHAR CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '卡号',
TransType CHAR CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '交易类型',
TransMoney DECIMAL NOT NULL COMMENT '交易金额',
Remark TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT '可选输入，其他说明',
FOREIGN KEY(CardID) REFERENCES CardInfo(CardID)
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci;

         按成创建后，我们在数据库中查看一下创建的是否符合要求

客户信息表结构

银行卡信息表结构 

交易信息表结构

 

存款类型表结构

        当然，如果直接输入数据进行测试也可以。