SQL语句创建一个简单的银行数据库

目录

一、银行业务E-R图

二、数据库模型图

转换关系模型后：

三、创建数据库

3.1 创建银行业务数据库

四、创建表

4.1 创建客户信息表

4.2 创建银行卡信息表

4.3 创建交易信息表

4.4 创建存款类型表

结果如下：

​编辑

五、插入适量数据

5.1 客户信息表数据

5.2 银行卡信息表数据

5.3 交易信息表数据

5.4 存款类型数据

结果如下：

---

一、银行业务E-R图

二、数据库模型图

转换关系模型后：

三、创建数据库

3.1 创建银行业务数据库

mysql> DROP DATABASE IF EXISTS Bank;//如果存在先删除表
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE DATABASE IF NOT EXISTS Bank;
Query OK, 1 row affected (0.00 sec)

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| Bank               |
| MySchool_db        |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.01 sec)

四、创建表

4.1 创建客户信息表

CREATE TABLE IF NOT EXISTS userInfo(
    customerID INT(8) PRIMARY KEY AUTO_INCREMENT COMMENT'客户编号',
    customerName VARCHAR(10) NOT NULL COMMENT'开户姓名',
    PID CHAR(18) NOT NULL COMMENT'身份证号',
    telephone CHAR(11) NOT NULL COMMENT'联系电话',
    address VARCHAR(50) COMMENT'居住地址'
)ENGINE=INNODB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci;

4.2 创建银行卡信息表

CREATE TABLE IF NOT EXISTS cardInfo(
    cardID VARCHAR(19) NOT NULL PRIMARY KEY COMMENT'卡号',
    curID VARCHAR(10) NOT NULL DEFAULT'RMB' COMMENT'货币种类',
    savingID INT(2) NOT NULL COMMENT'存款类型',
    openDate TIMESTAMP NOT NULL COMMENT'开户日期',
    openMoney DECIMAL NOT NULL COMMENT'开户金额',
    balance DECIMAL NOT NULL COMMENT'余额',
    password VARCHAR(6) NOT NULL DEFAULT'888888' COMMENT'密码',
    IsReportLoss BIT NOT NULL DEFAULT 0 COMMENT'是否挂失'.
    customerID INT(8) NOT NULL COMMENT'客户编号'
)ENGINE=INNODB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci;

ALTER TABLE cardInfo MODIFY openMoney DECIMAL(9,2);
ALTER TABLE cardInfo MODIFY balance DECIMAL(9,2);#确保账户有余额

ALTER TABLE cardInfo
ADD CONSTRAINT FK_cardID FOREIGN KEY(cardID) REFERENCES cardInfo(cardID);
ALTER TABLE cardInfo
ADD CONSTRAINT FK_customerID FOREIGN KEY(customerID) REFERENCES userInfo(customerID);
ALTER TABLE cardInfo
ADD CONSTRAINT FK_savingID FOREIGN KEY(savingID) REFERENCES deposit(savingID);
#设立外键

4.3 创建交易信息表

CREATE TABLE IF NOT EXISTS tradeInfo(
    transDate TIMESTAMP NOT NULL COMMENT'交易日期',
    cardID CHAR(19) NOT NULL COMMENT'卡号',
    transType CHAR(10) NOT NULL COMMENT'交易类型',
    transMoney DECIMAL NOT NULL COMMENT'交易金额',
    remark TEXT COMMENT'备注',
    CONSTRAINT FK_cardID FOREIGN KEY(cardID) REFERENCES cardInfo(cardID)
)ENGINE=INNODB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci;

4.4 创建存款类型表

CREATE TABLE IF NOT EXISTS deposit(
    savingID INT(2) PRIMARY KEY COMMENT'存款类型号',
    savingName VARCHAR(20) NOT NULL COMMENT'存款类型名称',
    descrip VARCHAR(20) COMMENT'描述'
)ENGINE=INNODB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci;

结果如下：

五、插入适量数据

5.1 客户信息表数据

INSERT INTO userInfo(customerID,customerName,PID,telephone,address)
VALUES
(1,'王令','320123199001021111','19599931111','江苏淮安'),
(2,'老王','320123199102032222','19599932222','四川成都'),
(3,'嘉文','320123199203043333','19599933333','福建福州'),
(4,'文静','320123199304054444','19599934444','江苏南京'),
(5,'高宇','320123199405065555','19599935555','重庆渝北');

5.2 银行卡信息表数据

INSERT INTO cardInfo(cardID,curID,savingID,openDate,openMoney,balance,password,isReportLoss,customerID)
VALUES
('9999123400000102','RMB',1,NOW(),100,100,'888888',0,1),
('9999123400000203','RMB',1,NOW(),100,1,'888888',0,2),
('9999123400000304','RMB',2,NOW(),10,10,'888888',0,3),
('9999123400000405','RMB',2,NOW(),100,1000,'888888',0,4),
('9999123400000506','RMB',1,NOW(),100,100000,'888888',0,5);
UPADTE cardInfo SET cardID='9999123400000901' WHERE cardID='9999123400000506';

5.3 交易信息表数据

INSERT INTO tradeInfo(transDate,cardID,transType,transMoney,remark)
VALUES
(NOW(),'9999123400000102','取出',222,NOW()),
(NOW(),'9999123400000304','存入',1111,NOW()),
(NOW(),'9999123400000506','取出',1002,NOW()),
(NOW(),'9999123400000102','存入',100,NOW()),
(NOW(),'9999123400000102','存入',2002,NOW()),
(NOW(),'9999123400000304','取出',2000,NOW()),
(NOW(),'9999123400000203','存入',1000,NOW());

5.4 存款类型数据

INSERT INTO deposit(savingID,savingName,descrip)
VALUES(1,'定期'，‘ ’),(2,'活期'，‘ ’);

结果如下：