1、数据库设计
绘制E-R
绘制数据库模型
数据库模型图我们可以创建好表和约束之后，点击对象
选中需要绘制模型图的表
数据库默认是第一个，我们可以选择第三个
代码段在最下面
可以去看看这三个图案有什么不同，这样比我们一个一个去添加字段设置约束的效率要高很多，一举两得，希望能帮助到大家！
用例2：创建数据库及登录用户并授权
#创建bankDB数据库,如果存在先删除在创建
DROP DATABASE IF EXISTS bankDB;
CREATE DATABASE if not exists bankDB;
#创建普通用户bankMaster并授权
grant all on bankdb
to bankMaster@127.0.0.1 identifed by '1234';
登录用户的权限书本上有提到，可以去看看它的解析，这就不多做讲述了。
用例3：创建表、约束
#使用bankDB数据库
use bankDB;
#客户信息表
DROP TABLE IF EXISTS userInfo;
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 COMMENT='客户表',
CHARSET=utf8;
#银行卡信息表
DROP TABLE IF EXISTS cardInfo;
CREATE TABLE if not exists cardInfo(
cardID VARCHAR(19) PRIMARY KEY NOT NULL 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 COMMENT='银行卡信息表',
CHARSET=utf8;
alter table cardinfo modify openMoney decimal(9,2);
alter table cardinfo modify balance decimal(9,2);
#创建外键
alter table cardinfo
add constraint foreign key(customerID) references userinfo(customerID);
alter table cardinfo
add constraint foreign key(savingID) references deposit(savingID);
#交易信息表
DROP TABLE IF EXISTS tradeInfo;
CREATE TABLE if not exists tradeInfo(
transDate TIMESTAMP NOT NULL COMMENT'交易日期',
cardID char(19) NOT NULL NULL COMMENT'卡号',
transType char(10) NOT NULL COMMENT'交易类型',
transMoney DECIMAL NOT NULL COMMENT'交易金额',
remark TEXT COMMENT'交易备注'
)ENGINE=INNODB COMMENT='交易信息表',
CHARSET=utf8;
#创建外键
alter table tradeinfo
add constraint foreign key(cardID) references cardinfo(cardID);
#存款类型表
DROP TABLE IF EXISTS deposit;
CREATE TABLE if not exists deposit(
savingID INT(2) PRIMARY KEY COMMENT'存款类型',
savingName VARCHAR(20) NOT NULL COMMENT'存款类型名称',
curID VARCHAR(10) NOT NULL default 'RMB' COMMENT'存款币种'
)ENGINE=INNODB COMMENT='存款类型表',
CHARSET=utf8
用例4：插入测试数据库
#添加数据
#银行客户表数据
INSERT INTO userInfo(customerID,customerName,PID,telephone,address)
VALUES(1,'丁六','567891321242345618','13645667783','北京西城区'),
(2,'王五','56789132124234567X','13642345112','河北石家庄市'),
(3,'张三','567891321242345789','13567893246','北京海定区'),
(4,'丁一','123451321242345618','13056434411','河南新乡'),
(5,'李四','678911321242345618','13318877954','山东济南市');
#银行卡信息数据
INSERT INTO cardInfo(cardID,savingID,openDate,openmoney,balance,`password`,IsReportLoss,customerID)
VALUES('1010357612121004',1,NOW(),1,1001,'888888',0,1),
('1010357612121130',1,NOW(),1,1,'888888',0,2),
('1010357612125678',3,NOW(),1000,6100,'123456',0,3),
('1010357612128284',3,NOW(),1000,1001,'888888',0,4),
('1010357612121134',1,NOW(),1,1501,'123123',1,5);
#交易信息表数据
INSERT INTO tradeInfo(transDate,cardID,transType,transMoney,remark)
VALUES(NOW(),'1010357612125678','支取',900,NOW()),
(NOW(),'1010357612121130','存入',300,NOW()),
(NOW(),'1010357612121004','存入',1000,NOW()),
(NOW(),'1010357612121130','存入',1900,NOW()),
(NOW(),'1010357612121134','存入',5000,NOW()),
(NOW(),'1010357612121134','存入',500,NOW()),
(NOW(),'1010357612121134','支取',2000,NOW()),
(NOW(),'1010357612125678','存入',2000,NOW()),
(NOW(),'1010357612121134','支取',2000,NOW()),
(NOW(),'1010357612125678','存入',2000,NOW()),
(NOW(),'1010357612125678','存入',2000,NOW());
#存款类型表数据
INSERT INTO deposit(savingID,savingName)
VALUES(1,"定期一年"),(2,"定期二年"),(3,'活期');
用例5：模拟常规业务
#1.修改客户密码
update cardinfo set `password`='123456' where cardID='1010357612125678';
update cardinfo set `password`='123123' where cardID='1010357612121134';
#2.办理银行卡挂失
update cardinfo set IsReportLoss=1 where cardID='1010357612121134';
select c.cardID 卡号,c.curID 货币,d.savingName 储蓄种类,c.openDate 开户日期,c.openMoney 开户金额,c.balance 余额,
c.`password` 密码,c.IsReportLoss 是否挂失,u.customerName 客户姓名
from cardinfo c
inner join userinfo u
on c.customerID=u.customerID
inner join deposit d
on c.savingID=d.savingID;
#3.统计银行总存入金额和总总支取金额
select transType 资金流向,sum(transMoney) 总金额 from tradeInfo
where transType='支取' or transType='存入'
group by transType;
#4.查询本周开户信息
select c.cardID 卡号,u.customerName 姓名,c.curID 货币,c.savingID 存款类型,c.openDate 开户日期,c.openMoney 开户金额,
c.balance 开户余额,c.IsReportLoss 账户状态
from cardinfo c
inner join userinfo u
on c.customerID=u.customerID
where week(c.openDate)=week(NOW());
#5.查询本月交易金额最高的卡号
select distinct cardID
from tradeinfo
where transMoney=(select MAX(transMoney) from tradeinfo where transType='支出' or transType='存入'
and MONTH(transDate)=MONTH(NOW()));
#6.查询挂失客户
select u.customerName 客户姓名,u.telephone 联系电话,c.cardID 卡号,c.IsReportLoss 是否挂失
from userinfo u
inner join cardinfo c
on u.customerID=c.customerID
where c.IsReportLoss=b'1';
#7.催款提醒业务
select u.customerName 客户姓名,u.telephone 联系电话,c.balance 存款余额
from userinfo u
inner join cardinfo c
on u.customerID=c.customerID
where c.balance<200;
用例6：创建、使用客户友好信息视图
#创建用户视图
DROP VIEW IF EXISTS view_userInfo;
create view view_userInfo
as
select customerID 客户编号,customerName 客户姓名,PID 身份证号,telephone 手机号码,address 地址
from userinfo u;
#创建银行卡视图
DROP VIEW IF EXISTS view_cardinfo;
create view view_cardinfo
as
select cardID 卡号,customerName 客户名,c.curID 货币种类,savingName 存款类型,openDate 开户日期,balance 余额,
`password` 密码,IsReportLoss 是否挂失
from cardinfo c
inner join userinfo u
on c.customerID=u.customerID
inner join deposit d
on c.curID=d.curID;
#创建交易信息视图
DROP VIEW IF EXISTS view_transInfo;
create view view_transInfo
as
SELECT transDate 交易日期,transType 交易类型,cardID 卡号, transMoney 交易金额, remark 备注
FROM tradeinfo;
用例7：使用事务完成转账
#使用事务完成转账
#从卡号为“1010357612121134”的账户中转出300元给卡号为“1010357612345678”的账户，即李四转账300元给张三。
#先关闭自动提交状态
set autocommit=0;
#开启事务
begin;
update cardinfo set balance=balance-300 where cardID='1010357612121134';
update cardinfo set balance=balance+300 where cardID='1010357612125678';
#提交事务
commit;
#遇到问题回滚数据到原来的状态
rollback;
#恢复自动提交
set autocommit=1;
可以根据书本上的提示完成项目……
可以去看看这三个图案有什么不同，这样比我们一个一个去添加字段设置约束的效率要高很多，一举两得，希望能帮助到大家！
用例2：创建数据库及登录用户并授权
#创建bankDB数据库,如果存在先删除在创建
DROP DATABASE IF EXISTS bankDB;
CREATE DATABASE if not exists bankDB;
#创建普通用户bankMaster并授权
grant all on bankdb
to bankMaster@127.0.0.1 identifed by '1234';
登录用户的权限书本上有提到，可以去看看它的解析，这就不多做讲述了。
用例3：创建表、约束
#使用bankDB数据库
use bankDB;
#客户信息表
DROP TABLE IF EXISTS userInfo;
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 COMMENT='客户表',
CHARSET=utf8;
#银行卡信息表
DROP TABLE IF EXISTS cardInfo;
CREATE TABLE if not exists cardInfo(
cardID VARCHAR(19) PRIMARY KEY NOT NULL 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 COMMENT='银行卡信息表',
CHARSET=utf8;
alter table cardinfo modify openMoney decimal(9,2);
alter table cardinfo modify balance decimal(9,2);
#创建外键
alter table cardinfo
add constraint foreign key(customerID) references userinfo(customerID);
alter table cardinfo
add constraint foreign key(savingID) references deposit(savingID);
#交易信息表
DROP TABLE IF EXISTS tradeInfo;
CREATE TABLE if not exists tradeInfo(
transDate TIMESTAMP NOT NULL COMMENT'交易日期',
cardID char(19) NOT NULL NULL COMMENT'卡号',
transType char(10) NOT NULL COMMENT'交易类型',
transMoney DECIMAL NOT NULL COMMENT'交易金额',
remark TEXT COMMENT'交易备注'
)ENGINE=INNODB COMMENT='交易信息表',
CHARSET=utf8;
#创建外键
alter table tradeinfo
add constraint foreign key(cardID) references cardinfo(cardID);
#存款类型表
DROP TABLE IF EXISTS deposit;
CREATE TABLE if not exists deposit(
savingID INT(2) PRIMARY KEY COMMENT'存款类型',
savingName VARCHAR(20) NOT NULL COMMENT'存款类型名称',
curID VARCHAR(10) NOT NULL default 'RMB' COMMENT'存款币种'
)ENGINE=INNODB COMMENT='存款类型表',
CHARSET=utf8
用例4：插入测试数据库
#添加数据
#银行客户表数据
INSERT INTO userInfo(customerID,customerName,PID,telephone,address)
VALUES(1,'丁六','567891321242345618','13645667783','北京西城区'),
(2,'王五','56789132124234567X','13642345112','河北石家庄市'),
(3,'张三','567891321242345789','13567893246','北京海定区'),
(4,'丁一','123451321242345618','13056434411','河南新乡'),
(5,'李四','678911321242345618','13318877954','山东济南市');
#银行卡信息数据
INSERT INTO cardInfo(cardID,savingID,openDate,openmoney,balance,`password`,IsReportLoss,customerID)
VALUES('1010357612121004',1,NOW(),1,1001,'888888',0,1),
('1010357612121130',1,NOW(),1,1,'888888',0,2),
('1010357612125678',3,NOW(),1000,6100,'123456',0,3),
('1010357612128284',3,NOW(),1000,1001,'888888',0,4),
('1010357612121134',1,NOW(),1,1501,'123123',1,5);
#交易信息表数据
INSERT INTO tradeInfo(transDate,cardID,transType,transMoney,remark)
VALUES(NOW(),'1010357612125678','支取',900,NOW()),
(NOW(),'1010357612121130','存入',300,NOW()),
(NOW(),'1010357612121004','存入',1000,NOW()),
(NOW(),'1010357612121130','存入',1900,NOW()),
(NOW(),'1010357612121134','存入',5000,NOW()),
(NOW(),'1010357612121134','存入',500,NOW()),
(NOW(),'1010357612121134','支取',2000,NOW()),
(NOW(),'1010357612125678','存入',2000,NOW()),
(NOW(),'1010357612121134','支取',2000,NOW()),
(NOW(),'1010357612125678','存入',2000,NOW()),
(NOW(),'1010357612125678','存入',2000,NOW());
#存款类型表数据
INSERT INTO deposit(savingID,savingName)
VALUES(1,"定期一年"),(2,"定期二年"),(3,'活期');
用例5：模拟常规业务
#1.修改客户密码
update cardinfo set `password`='123456' where cardID='1010357612125678';
update cardinfo set `password`='123123' where cardID='1010357612121134';
#2.办理银行卡挂失
update cardinfo set IsReportLoss=1 where cardID='1010357612121134';
select c.cardID 卡号,c.curID 货币,d.savingName 储蓄种类,c.openDate 开户日期,c.openMoney 开户金额,c.balance 余额,
c.`password` 密码,c.IsReportLoss 是否挂失,u.customerName 客户姓名
from cardinfo c
inner join userinfo u
on c.customerID=u.customerID
inner join deposit d
on c.savingID=d.savingID;
#3.统计银行总存入金额和总总支取金额
select transType 资金流向,sum(transMoney) 总金额 from tradeInfo
where transType='支取' or transType='存入'
group by transType;
#4.查询本周开户信息
select c.cardID 卡号,u.customerName 姓名,c.curID 货币,c.savingID 存款类型,c.openDate 开户日期,c.openMoney 开户金额,
c.balance 开户余额,c.IsReportLoss 账户状态
from cardinfo c
inner join userinfo u
on c.customerID=u.customerID
where week(c.openDate)=week(NOW());
#5.查询本月交易金额最高的卡号
select distinct cardID
from tradeinfo
where transMoney=(select MAX(transMoney) from tradeinfo where transType='支出' or transType='存入'
and MONTH(transDate)=MONTH(NOW()));
#6.查询挂失客户
select u.customerName 客户姓名,u.telephone 联系电话,c.cardID 卡号,c.IsReportLoss 是否挂失
from userinfo u
inner join cardinfo c
on u.customerID=c.customerID
where c.IsReportLoss=b'1';
#7.催款提醒业务
select u.customerName 客户姓名,u.telephone 联系电话,c.balance 存款余额
from userinfo u
inner join cardinfo c
on u.customerID=c.customerID
where c.balance<200;
用例6：创建、使用客户友好信息视图
#创建用户视图
DROP VIEW IF EXISTS view_userInfo;
create view view_userInfo
as
select customerID 客户编号,customerName 客户姓名,PID 身份证号,telephone 手机号码,address 地址
from userinfo u;
#创建银行卡视图
DROP VIEW IF EXISTS view_cardinfo;
create view view_cardinfo
as
select cardID 卡号,customerName 客户名,c.curID 货币种类,savingName 存款类型,openDate 开户日期,balance 余额,
`password` 密码,IsReportLoss 是否挂失
from cardinfo c
inner join userinfo u
on c.customerID=u.customerID
inner join deposit d
on c.curID=d.curID;
#创建交易信息视图
DROP VIEW IF EXISTS view_transInfo;
create view view_transInfo
as
SELECT transDate 交易日期,transType 交易类型,cardID 卡号, transMoney 交易金额, remark 备注
FROM tradeinfo;
用例7：使用事务完成转账
#使用事务完成转账
#从卡号为“1010357612121134”的账户中转出300元给卡号为“1010357612345678”的账户，即李四转账300元给张三。
#先关闭自动提交状态
set autocommit=0;
#开启事务
begin;
update cardinfo set balance=balance-300 where cardID='1010357612121134';
update cardinfo set balance=balance+300 where cardID='1010357612125678';
#提交事务
commit;
#遇到问题回滚数据到原来的状态
rollback;
#恢复自动提交
set autocommit=1;
可以根据书本上的提示完成项目……
3148
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
