MySQL项目：银行系统

创建数据库及登录用户并授权

#创建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());
 
 

#创建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());