数据库作业

最新推荐文章于 2024-09-29 08:44:52 发布

Zombie_QP

最新推荐文章于 2024-09-29 08:44:52 发布

阅读量464

点赞数 10

文章标签：数据库

本文链接：https://blog.csdn.net/Zombie_QP/article/details/135833002

版权

本文介绍了如何在数据库中创建UserInfo、CardInfo、TradeInfo和Deposit表，包括字段定义、主键和外键约束，以支持客户信息管理、卡片交易和存款类型的数据存储。

摘要由CSDN通过智能技术生成

UserInfo表

**建表**
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;

CardInfo

**设置外键**
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表后输入

**建表**
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;

TradeInfo

**外键**
ALTER TABLE TRADEINFO ADD CONSTRAINT card_id FOREIGN KEY (cardID) REFERENCES CARDINFO (cardID);

**建表**
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;

Deposit

**建表**
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;