银行系统基础查询

#创建数据库

CREATE DATABASE bankDB

use bankDB

#创建用户，密码为123
CREATE USER `bankMaster`IDENTIFIED BY '123'
#修改权限
GRANT ALL  ON bankDB.* TO `bankMaster`
#创建用户信息表
CREATE TABLE userInfo(
customerID INT(4) PRIMARY KEY AUTO_INCREMENT COMMENT '用户编号',
customerName CHAR(8) NOT NULL COMMENT '用户编号',
pID CHAR(18) UNIQUE NOT NULL COMMENT '身份证号',
telephone CHAR(20) NOT NULL COMMENT '手机号码',
address VARCHAR(50) COMMENT '居住地址'
)ENGINE =INNODB,COMMENT ='用户表'
#创建银行卡表
CREATE TABLE cardInfo(
cardID CHAR(16) PRIMARY KEY NOT NULL COMMENT'银行卡号',
curID VARCHAR(50) NOT NULL DEFAULT 'RMB',
savingID VARCHAR(50) NOT NULL COMMENT'存款类型',
openDate TIMESTAMP NOT NULL DEFAULT NOW() COMMENT'开户日期',
openMoney DECIMAL(9,2) NOT NULL COMMENT '开户金额',
balance DECIMAL(9,2) NOT NULL COMMENT '余额',
PASSWORD CHAR(6) NOT NULL DEFAULT '888888',
IsReportLoss BIT NOT NULL DEFAULT 0,
customerID INT NOT NULL COMMENT'客户编号'
)
#创建
CREATE TABLE tradeInfo(
transDate TIMESTAMP NOT NULL DEFAULT NOW() COMMENT'交易日期',
cardID CHAR(16) NOT NULL,
transType CHAR(6) NOT NULL COMMENT'交易类型',
transMoney DECIMAL(9,2) NOT NULL COMMENT'交易金额',
remark TEXT COMMENT'备注'
)
#创建
CREATE TABLE deposit(
savingID INT PRIMARY KEY AUTO_INCREMENT COMMENT '存款类型号',
savingName VARCHAR(50) NOT NULL COMMENT'存款类型名称',
descrip VARCHAR(225) COMMENT'描述'
)
#银行卡号信息到用户信息的外键约束
ALTER TABLE cardinfo ADD CONSTRAINT fk_user_card FOREIGN KEY (customerID) REFERENCES userInfo(cardID)


ALTER TABLE tradeinfo ADD CONSTRAINT fk_trand_card FOREIGN KEY (cardID) REFERENCES  cardInfo(cardID)


SELECT a.cardID  FROM cardinfo AS a INNER JOIN userinfo AS b ON a.customerID=b.customerID WHERE b.customerName='张三'


INSERT INTO tradeinfo(cardID,tradeDate,tradeMoney,tradeType) VALUES ('1010357612345678',NOW(),900,'支取') 


UPDATE cardinfo SET balance=balance-1000 WHERE cardID=1010357612345678


#修改密码
UPDATE cardinfo SET PASSWORD='123456' WHERE cardID='1010357612345678'

#更新数据1
UPDATE cardinfo SET balance = balance +5000 WHERE cardID = 1010357612121134
INSERT INTO tradeinfo(cardID,tradeDate,tradeMoney,tradeType) VALUES ('1010357612121134',NOW(),5000,'存入') 

#更新数据2
UPDATE cardinfo SET balance = balance +1000 WHERE cardID = 1010357612121134
INSERT INTO tradeinfo(cardID,tradeDate,tradeMoney,tradeType) VALUES ('1010357612121134',NOW(),1000,'存入') 

#一个月内查询银行卡号记录次数最多得卡号
SELECT cardID FROM tradeinfo WHERE tradeMoney = (SELECT MAX(tradeMoney) FROM tradeinfo WHERE MONTH(tradeDate)=NOW() AND YEAR(tradeDate)=YEAR(NOW()))