E-R图
建库
mysql> CREATE DATABASE Bank_self;
创建客户信息表(userlnfo)
mysql> CREATE TABLE userInfo(
-> customerID INT NOT NULL AUTO_INCREMENT COMMENT '客户编号',
-> customerName CHAR(20) 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) ,
-> UNIQUE INDEX (PID)
-> ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci;
创建银行卡信息表(cardlnfo)
mysql> CREATE TABLE cardInfo (
-> cardID CHAR(50) NOT NULL COMMENT '卡号',
-> savingID VARCHAR(50) NOT NULL COMMENT '存款类型',
-> openDate TIMESTAMP NOT NULL COMMENT '开户日期',
-> openMoney DECIMAL NOT NULL COMMENT '开户金额',
-> balance DECIMAL NOT NULL COMMENT '余额',
-> password VARCHAR(6) NOT NULL COMMENT '密码',
-> IsReportLoss BIT(1) NOT NULL COMMENT '是否挂失',
-> customerlD INT NOT NULL COMMENT '客户编号',
-> PRIMARY KEY (cardID)
-> ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci;
创建交易信息表(tradelnfo)
mysql> CREATE TABLE tradeInfo (
-> transDate TIMESTAMP NOT NULL COMMENT '交易日期',
-> cardID CHAR(50) NOT NULL COMMENT '卡号',
-> transType CHAR(50) NOT NULL COMMENT '交易类型',
-> transMoney DECIMAL NOT NULL COMMENT '交易金额',
-> remake TEXT NULL DEFAULT NULL COMMENT '备注'
-> )ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci;
创建存款类型表结构(deposit)
mysql> CREATE TABLE deposit (
-> savingID VARCHAR(50) NOT NULL AUTO_INCREMENT COMMENT '存款类型号',
-> savingName VARCHAR(50) NOT NULL COMMENT '存款类型名称',
-> descrip VARCHAR(255) NULL DEFAULT NULL COMMENT '描述',
-> PRIMARY KEY(savingID)
-> ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci;
开户日期设置默认为当前系统时间
给password设置一个默认选项为888888
给isReportLoss设置默认选项为0
添加外键
ALTER TABLE cardInfo MODIFY COLUMN openDate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE cardInfo ALTER password SET DEFAULT "888888";
ALTER TABLE cardInfo alter IsReportLoss SET DEFAULT 0;
ALTER TABLE Bank_self.cardInfo
ADD INDEX fk_bank(customerlD) USING BTREE,
ADD CONSTRAINT fk_bank FOREIGN KEY (customerlD) REFERENCES Bank_self.userInfo (customerID);
ALTER TABLE Bank_self.cardInfo
ADD INDEX fk_saving(savingID) USING BTREE,
ADD CONSTRAINT fk_saving FOREIGN KEY (savingID) REFERENCES Bank_self.deposit (savingID)
ALTER TABLE tradeInfo MODIFY COLUMN transDate datetime NOT NULL DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE Bank_self.tradeInfo
ADD INDEX fk_card(cardID) USING BTREE,
ADD CONSTRAINT fk_card FOREIGN KEY (cardID) REFERENCES Bank_self.cardInfo (cardID)
向存款类型表中插入数据
insert into deposit(savingName,descrip) VALUES ('活期','按存款日结算利息');
insert into deposit(savingName,descrip) VALUES ('定期一年','存款期为一年');
insert into deposit(savingName,descrip) VALUES ('定期两年','存款期为两年');
insert into deposit(savingName,descrip) VALUES ('定期三年','存款期为三年');
insert into deposit(savingName,descrip) VALUES ('零存整取一年','存款期为一年');
insert into deposit(savingName,descrip) VALUES ('零存整取两年','存款期为两年');
insert into deposit(savingName,descrip) VALUES ('零存整取三年','存款期为三年');
查看
向客户信息表中插入数据
INSERT into userInfo(customerName,PID,telephone,address) VALUES
('张三','123456789123456789','12345678912','陕西省西安市'),
('李四','111111111111111111','99999999999','陕西省西安市'),
('王五','222222222222222222','88888888888','陕西省西安市'),
('老六','333333333333333333','77777777777','陕西省西安市'),
('周七','555555555555555555','66666666666','陕西省西安市');
查看
向银行卡表中插入数据
INSERT into cardInfo(cardID,savingID,openMoney,balance,customerlD)VALUES
('1111 6666 1234 5678',1,1000,1000,1),
('2222 6666 5678 1234',2,6000,6000,2),
('3333 6666 5678 1234',3,9000,9000,3),
('5555 6666 5678 1234',4,2500,2500,4),
('6666 6666 5678 1234',5,3600,3600,5);
查看
向交易信息表中插入数据
mysql> INSERT into tradeInfo(transType,cardID,transMoney) VALUES ('支出','1111 6666 1234 5678',700);
Query OK, 1 row affected (0.00 sec)
mysql> UPDATE cardInfo SET balance = balance-700 where cardID = '1111 6666 1234 5678';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> INSERT into tradeInfo(transType,cardID,transMoney) VALUES ('存入','2222 6666 5678 1234',50000);
Query OK, 1 row affected (0.00 sec)
mysql> UPDATE cardInfo set balance = balance+50000 where cardID = '2222 6666 5678 1234';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> INSERT into tradeInfo(transType,cardID,transMoney) VALUES ('支出','3333 6666 5678 1234',1000);
Query OK, 1 row affected (0.01 sec)
mysql> UPDATE cardInfo set balance = balance-1000 where cardID = '3333 6666 5678 1234';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> INSERT into tradeInfo(transType,cardID,transMoney) VALUES ('存入','5555 6666 5678 1234',5000);
Query OK, 1 row affected (0.00 sec)
mysql> UPDATE cardInfo set balance = balance+5000 where cardID = '5555 6666 5678 1234';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> INSERT into tradeInfo(transType,cardID,transMoney) VALUES ('存入','6666 6666 5678 1234',1000);
Query OK, 1 row affected (0.00 sec)
mysql> UPDATE cardInfo set balance = balance+1000 where cardID = '6666 6666 5678 1234';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
查看银行卡信息表数据
查看交易表数据