数据库实验

一、建数据表

CREATE TABLE deposit(
   savingID INT NOT NULL AUTO_INCREMENT COMMENT'存款类型号', 
   savingName VARCHAR(30) NOT NULL COMMENT'存款类型名称', 
   descrip VARCHAR(60) NULL COMMENT'描述', PRIMARY KEY (savingID) 
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci;

CREATE TABLE tradeInfo( 
transDate TIMESTAMP NOT NULL COMMENT'交易日期', cardID CHAR(20) NOT NULL COMMENT'卡号', 
transType CHAR(20) NOT NULL COMMENT'交易类型', transMoney DECIMAL(10,2) NOT NULL COMMENT'交易金额', remark TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT'备注,可选输入,其他说明' 
)ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci;

//添加外键约束
alter table tradeInfo add constraint FK_ID foreign key(cardID) references cardInfo(cardID);

CREATE TABLE userInfo(
customerID INT NOT NULL AUTO_INCREMENT COMMENT'客户编号',
customerName char(10) NOT NULL COMMENT'开户名', 
PID char(18) NOT NULL UNIQUE COMMENT'身份证号', telephone char(11) NOT NULL COMMENT'联系电话',
address varchar(30) NULL COMMENT'居住地址',PRIMARY KEY (customerID) 
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci;

create table cardInfo(
     cardID char(20) NOT NULL COMMENT'卡号',
     savingID int NOT NULL COMMENT'存款类型号',
     openDate timestamp NOT NULL COMMENT'开户日期',
     openMoney decimal(5,2) NOT NULL COMMENT'开户金额',
     balance decimal(3,2) NOT NULL COMMENT'余额',
     password varchar(6) DEFAULT'888888' COMMENT'密码',
     IsReportLoss bit(8) COMMENT'是否挂失',
     customerID int NOT NULL COMMENT'客户编号',
     PRIMARY KEY(cardID)
     )ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci;

//添加外键约束
alter table cardInfo add foreign key(savingID) references deposit(savingID);
alter table cardInfo add foreign key(customerID) references userInfo(customerID);