MySQL---ATM

--创建数据库
CREATE DATABASE bankdb;
--客户信息表
CREATE TABLE userInfo (
customerID INT NOT NULL PRIMARY KEY,
customerName CHAR NOT null,
PID CHAR not null ,
telephone CHAR(20) not null,
address VARCHAR(50)
)
--银行卡信息表
CREATE TABLE cardInfo (
cardID CHAR(20) NOT null,
curID VARCHAR(10) NOT null,
savingID INT NOT null,
openDate DATETIME not null,
openMoney DECIMAL not NULL,
balance DECIMAL NOT NULL,
passWord CHAR(6) NOT null,
IsReportLoss bit NOT null,
customer INT NOT NULL
)

--交易信息表
CREATE TABLE cardInfo (
cardID CHAR(20) NOT null,
curID VARCHAR(10) NOT null,
savingID INT NOT null,
openDate DATETIME not null,
openMoney DECIMAL not NULL,
balance DECIMAL NOT NULL,
passWord CHAR(6) NOT null,
IsReportLoss bit NOT null,
customer INT NOT NULL
)

--存款类型表
CREATE TABLE deposit(
savingID int NOT NULL,
savingName VARCHAR(255) NOT NULL,
descrip VARCHAR(255)
)
--约束
--身份证号，只能是18位或15位，唯一约束
ALTER TABLE userinfo
ADD CONSTRAINT id_PID CHECK (len(PID)=18 OR len(PID)=15),
ADD CONSTRAINT UQ_PID UNIQUE(PID);
--手机号11位
ALTER TABLE userinfo
ADD CONSTRAINT id_telephone CHECK (len(telephone)=11);
--密码6位数字，开户时默认为6个“8”
ALTER TABLE cardinfo
ALTER COLUMN `passWord` SET DEFAULT'888888';
--默认RMB
ALTER TABLE cardinfo
ALTER COLUMN `curID` SET DEFAULT"RMB";
--默认为当前日期
ALTER TABLE cardinfo
ADD COLUMN openDate timestamp NULL DEFAULT CURRENT_TIMESTAMP;
--不低于一元
ALTER TABLE cardinfo
ADD CONSTRAINT ck_openMoney CHECK(openMoney>=1)
ALTER TABLE cardinfo
ADD CONSTRAINT ck_openMoney CHECK(openMoney>=1)
--是/否值，默认为否
ALTER TABLE cardInfo
MODIFY IsReportLoss BIT(1) NOT NULL DEFAULT 0;
--添加外键
ALTER TABLE transinfo
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);
--插入数据
客户类型
INSERT INTO userInfo(customerName,PID,telephone,address )
VALUES('张三','123456789012345','010-67898978','北京海淀');
INSERT INTO userInfo(customerName,PID,telephone)
VALUES('李四','321245678912345678','0478-44443333');
INSERT INTO cardInfo(cardID,savingID,openMoney,balance,customerID)
VALUES('1010357612345678',1,1000,1000,1);
INSERT INTO cardInfo(cardID,savingID,openMoney,balance,customerID)
VALUES('1010357612121134',2,1,1,2);
存款类型
INSERT INTO `deposit` (`savingName`,`descrip`) VALUES ('活期','按存款日结算利息');
INSERT INTO `deposit` (`savingName`,`descrip`) VALUES ('定期一年','存款期是1年');
INSERT INTO `deposit` (`savingName`,`descrip`) VALUES ('定期二年','存款期是2年');
INSERT INTO `deposit` (`savingName`,`descrip`) VALUES ('定期三年','存款期是3年');
INSERT INTO `deposit` (`savingName`) VALUES ('定活两便');
INSERT INTO `deposit` (`savingName`) VALUES ('通知');
INSERT INTO `deposit` (`savingName`,`descrip`) VALUES ('零存整取一年','存款期是1年');
INSERT INTO `deposit` (`savingName`,`descrip`) VALUES ('零存整取二年','存款期是2年');
INSERT INTO `deposit` (`savingName`,`descrip`) VALUES ('零存整取三年','存款期是3年');
INSERT INTO `deposit` (`savingName`,`descrip`) VALUES ('存本取息五年','按月支取利息');
SELECT * FROM `deposit`;
存入支取
SELECT * FROM cardinfo;
SELECT * FROM transinfo;
INSERT INTO `transinfo`(`transType`,`cardID`,`transMoney`) VALUES('支取','1010357612345678',900);
INSERT INTO `tradnsinfo`(`transType`,`cardID`,`transMoney`) VALUES('存入','1010357612121134',5000);
更新现有余额
UPDATE `cardinfo` SET `balance`=`balance`-900 WHERE `cardID`='1010357612345678';
UPDATE `cardinfo` SET `balance`=`balance`+5000 WHERE `cardID`='1010357612121134';
修改密码
UPDATE `cardinfo` SET `passWord`='123321' WHERE `cardID`='1010357612345678';
UPDATE `cardinfo` SET `passWord`='123456' WHERE `cardID`='1010357612121134';
办理卡挂失
update cardinfo set IsReportLoss=1
where cardID='1010357612128284';
修改密码和办理银行卡的结果
select cardID as卡号, curID as 货币,de.savingName as 储蓄种类,openDate as 开户日期,
openMoney as 开户金额,balance 余额,passWord AS 密码,IsReportLoss AS是否挂失,us.customerName as 客户姓名
from cardinfo ca
inner join deposit de on ca.savingID=de.savingID
inner join userinfo us on ca.customerID=us.customerID
本周开户信息
select cardID as 卡号,curID as 货币,savingID as 存款类型,openDate as 开户日期,
openMoney as 开户金额,balance as 余额,isLost as 是否挂失
from cardinfo
WHERE WEEK(NOW())=WEEK(openDate);
本月交易金额最高的卡号
SELECT DISTINCT cardID
FROM transinfo
WHERE transMoney=(
SELECT MAX(transMoney)
FROM transinfo
WHERE MONTH(transDate)=MONTH(NOW()));
-挂失客户--------------------
SELECT customerName AS 客户名,telephone AS 联系电话
FROM userinfo
INNER JOIN cardinfo
ON userinfo.customerID=cardinfo.customerID
WHERE IsReportLoss=1
催款业务，余额少于200催款
SELECT customerName AS 客户姓名,telephone AS 联系电话,balance AS 余额
FROM userinfo
INNER JOIN cardinfo
ON userinfo.customerId=cardinfo.customerID
WHERE balance<200
--创建、使用客户友好信息视图
DROP VIEW IF EXISTS view_userinfo;
CREATE VIEW view_userinfo
AS
SELECT `customerID` AS 客户编号,`customerName` AS 开户名, `PID` AS 身份证号,`telephone` AS 电话号码,`address` AS 居住地址
FROM userinfo;

SELECT * FROM view_userinfo;

DROP VIEW IF EXISTS view_cardinfo;
CREATE VIEW view_cardinfo #银行卡信息表视图
AS
SELECT c.cardID AS 卡号,u.customerName AS 客户,c.curID AS 货币种类, d.savingName AS 存款类型,c.openDate AS 开户日期,c.balance AS 余额,c.passWord AS 密码,IsReportLoss AS 是否挂失
FROM `cardinfo` c, `deposit` d,`userinfo` u
WHERE c.`savingID`=d.`savingID` AND c.`customerID`=u.`customerID`;
SELECT * FROM view_cardinfo;

DROP VIEW IF EXISTS view_transinfo;
CREATE VIEW view_transinfo
AS
SELECT `transDate` AS 交易日期,`transType` AS 交易类型, `cardID` AS 卡号,`transMoney` AS 交易金额,`remark` AS 备注
FROM `transinfo`;
SELECT * FROM view_transinfo;

--使用事务完成存款或取款业务
INSERT INTO transinfo(transType,cardID,transMoney)
VALUES('支取','1010357612121134',300);
UPDATE cardinfo SET balance=balance-300 WHERE cardID='1010357612121134';
INSERT INTO transinfo(transType,cardID,transMoney)
VALUES('存入','1010357612345678',300);
UPDATE cardinfo SET balance=balance+300 WHERE cardID='1010357612345678';
COMMIT; #提交事务
#rollback; #回滚事务

/*--------检查测试数据是否正确---------*/
SELECT * FROM cardinfo;
SELECT * FROM tradeinfo;