使用mysql制作库表（关于银行）_银行账户表mysql-CSDN博客

本文详细描述了如何在MySQL数据库中设计和实现一个银行系统，包括创建用户信息、银行卡信息和交易信息表，设置了默认值、外键约束，并进行了数据插入和查询操作。

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

查看银行卡信息表数据