一、创建新数据库
create database bank_xxx
use bank_xxx
二、创建表
客户信息(customerInfo)
create table customerInfo
(
customerID int not null primary key , #客户编号,非空,主键
customerName varchar(8) not null , #客户姓名,非空
cardType varchar(10) not null default('身份证'), #证件类型,非空,默认"身份证"
cardID varchar(18) not null unique , #证件号码,非空,惟一约束
phone varchar(11) not null , #电话,非空
address varchar(100) #地址邮编
);
账户信息表(accountInfo)
create table accountInfo
(
accountID char(19) not null primary key ,
savingType varchar(8) not null default('活期') check(savingType in ('活期','定期','定活两便')),
moneyType varchar(10) not null default('人民币'),
openDate datetime not null default(now()),
openMoney decimal(20,2) not null check(openMoney>=1),
accountMoney decimal(20,2) not null check(accountMoney>=1),
password char(6) not null default(666666),
accountState char(4) not null default('正常') check(accountState in ('正常','挂失','销户')),
customerID int not null references customerInfo(customerID)
);
交易信息表(transInfo)
create table transInfo
(
transID int not null , #交易流水号,非空,主键,标识列,初值,增量
transDate datetime not null default(now()), #交易日期,非空,默认系统日期
accountID char(19) not null key references accountInfo(accountID), #账号,非空,外键
transType char(4) not null check(transType in ('存入','支取')), #交易类型,非空,存入/支取
transMoney decimal(20,2) not null check(transMoney>0) #交易金额,非空,大于
);
三、在用户信息表里插入信息
insert into customerInfo(customerID,customerName,cardType,cardID,phone,address)
values(1,'你的名字','身份证','111111',111111,'安徽芜湖湾沚区')
insert into customerInfo(customerID,customerName,cardType,cardID,phone,address)
values(2,'同学1','身份证','22222',221111,'安徽合肥'),
(3,'同学2','身份证','33333',331111,'安徽蚌埠'),
(4,'张亮亮','护照','43434',431111,'安徽芜湖'),
(5,'亮晶晶','军官证','56789',551111,null)
(1)插入信息要与定义名称对应,
(2)可以添加信息格式要满足约束条件。
四、添加开户人账户信息
# --每个人开一个账户
insert into accountInfo(accountID , openMoney , accountMoney , customerID)
values(1001,500,500,1), (1002,300,300,2), (1003,400,400,3) ,(1004,30,30,4), (1005,40,40,5)
#开第二个账户
insert into accountInfo(accountID , openMoney , accountMoney , customerID)
values(3001,1000,1000,4)
insert into transInfo( transID,accountID , transType , transMoney)
values(6,3001,'存入',1000)
五、存款操作
#删除表transInfo里的主键
alter table transinfo
drop primary key;
#存款
insert into transInfo(transID,accountID , transType , transMoney)
values(7,1001,'存入',10)
update accountInfo set accountMoney = accountMoney + 10
where accountID = 1001
insert into transInfo(transID,accountID , transType , transMoney)
values(8,1001,'存入',2000)
update accountInfo set accountMoney = accountMoney + 2000
where accountID = 1001
由于刚开始在transinfo表中创建了唯一索引在进行存款操作时出现了错误,所以要删除transinfo中的唯一索引才能操作。
六、修改数据
--修改数据
update accountInfo set moneyType = '港币' where accountID = 1003
update transInfo set transDate = '2023-5-6' where transID <=9
update accountInfo set savingType='定期' where accountID = 3001
七、查询个表所有信息
select * from accountInfo
select * from transInfo
select * from customerInfo