MySQL建表练习

练习题目：通过所提供的E-R图和数据库模型图完成库表的创建，并插入适量的数据.要求必须使用SQL命令进行构建。

已知如下：





1、创建客户信息表：

代码：

CREATE DATABASE Bank;  //建库

CREATE TABLE  Userinfo(
	CustomerID INT AUTO_INCREMENT COMMENT '客户编号',
	CustomerName CHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '开户名',
	PID CHAR(18) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '身份证号',
	Telephone CHAR(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '联系电话',
	Address VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '居住地址',
	PRIMARY KEY (customerID) USING BTREE,
	UNIQUE INDEX PID(PID) USING BTREE
)ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci;

结果截图：

Userinfo表结构如下：

2、创建银行卡信息表：

代码：

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表后输入

Cardinfo表结构：

3、建立交易信息表：

代码：

CREATE TABLE Tradeinfo (
	TransDate TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE
CURRENT_TIMESTAMP COMMENT '交易日期',
	CardID CHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '卡号',
	TransType CHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '交易类型',
	TransMoney DECIMAL(18,2) NOT NULL COMMENT '交易金额',
	Remark TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '居住地址'
)ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci;

结果：

Tradeinfo表结构：

4、建立储存信息表：

代码：

CREATE TABLE DEPOSIT (
	savingID VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci  COMMENT '存款类型',
	savingName VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '存款类型名称',
	descrip VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '描述',
	PRIMARY KEY (savingID)
)ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci;

结果：

DEPOSIT表结构：

5、添加信息：

insert Userinfo values(1001,'张三',5105222026737618,133307407,'相爱苏带带速度'),(1002,'李四',51052220017627,13330218639,'阿披实丢啊UI');