计算机SQL 表的应用

实验5 表的应用
一实验目的1、使用SQL语句创建表
2、使用SQL语句修改表
3、使用SQL语句更新表
二实验内容1、创建城市银行数据库：CITYBANK，使用默认位置和大小即可。

2、创建如下银行业务表
3、写出如下业务的SQL语句：（银行业务需要进行事务处理）
三实验步骤
1、创建城市银行数据库
-----CITYBANK
2、创建如下银行业务表
（1）账户表Bank_Account
字段名类型约束说明AccountID
PASSWORD
ANAME
cardid
OpenDate
Balance
AccountStatus
ATYPE
Char(6)
Varchar(10)
Varchar(20)
Varchar(18)
Datetime
Decimal(20,2)
Char(4)
Char(4)
PK
NOT NULL
NOT NULL
UNIQUE
默认值：0
默认值:’激活’
默认值:’普通’
账户ID
账户密码
姓名
身份证号码
开户日期
帐户余额
账户状态
账户类型
字段名类型约束说明
NoteNo
AccountID
Amount
NoteDate
Int
Char(6)
Decimal(10,2)
datetime
PK
FK->Account.P
K
存款单序号
帐户ID
存款金额
存款日期
(NoteNo Int Primary key,
AccountID char(6),
foreign key (AccountID) references Bank_Account(AccountID),
Amount Decimal(10,2),
NoteDate datetime)
(NoteNo Int Primary key,
AccountNo char(6),
foreign key (AccountNo) references Bank_Account(AccountID), Amount Decimal(10,2),
NoteDate datetime)
（4）转账单表Bank_TransferNote
(NoteNo Int Primary key,
FROMAccountID char(6),
foreign key (FROMAccountID) references Bank_Account(AccountID), TOAccountID char(6),
foreign key (TOAccountID) references Bank_Account(AccountID), Amount Decimal(10,2),
NoteDate datetime)
(NoteNo Int Primary key,
AccountID char(6),
foreign key (AccountID) references Bank_Account(AccountID), NoteDate datetime,
NoteComment varchar(500))
3、写出如下业务的SQL语句：（银行业务需要进行事务处理）
insert into Bank_Account
values (001001,001,'李铭',’210201222222221’,’2010-01-20,10.00’,'激活','金卡') insert into Bank_Account
values (001002,002,'张林',’210201222222222’,’2009-03-01’,200.00,'激活','银') insert into Bank_Account
values (001003,003,'刘鑫',’210201222222223’,’2008-10-10’,300.00,'激活','普') (2)存款业务：
<1>李铭2010-02-20 存入2000元。

➢SQL语句：
begin transaction
insert into Bank_DepositNote values(1,'1001',2000,'2010-02-20')
update Bank_Account set balance=balance+2000 where AccountID='1001' commit transaction
<2>张林2009-05-10 存入6010.34元
➢SQL语句：
begin transaction
insert into Bank_DepositNote values(2,'1002',2000,'2009-05-10')
update Bank_Account set balance=balance+6010.34 where AccountID='1002' commit transaction
<3>刘鑫2009-03-01 存入8010.67元。

➢SQL语句：
begin transaction
insert into Bank_DepositNote values(3,'1003',2000,'2009-03-01')
update Bank_Account set balance=balance+8010.67 where AccountID='1003' commit transaction
(3)取款业务
<1>李铭2010-04-10 取出1670.78元。

➢SQL语句：
begin transaction
insert into Bank_WithdrawNote values(4,'1001',1670.78,'2010-04-10') update Bank_Account set balance=balance-1670.78 where AccountID='1001' commit transaction
<2>张林2009-07-23 取出4210.24元
➢SQL语句：
begin transaction
insert into Bank_WithdrawNote values(5,'1002',4210.24,'2009-07-23') update Bank_Account set balance=balance-4210.24 where AccountID='1002' commit transaction
<3>刘鑫2009-07-14 取出710.67元。

➢SQL语句：
begin transaction
insert into Bank_WithdrawNote values(6,'1003',710.67,'2009-07-14') update Bank_Account set balance=balance-710.67 where AccountID='1003' commit transaction
<4>刘鑫2009-08-23 取出1110.58元。

➢SQL语句：
begin transaction
insert into Bank_WithdrawNote values(7,'1003', 1110.58,' 2009-08-23') update Bank_Account set balance=balance-1110.58 where AccountID='1003' commit transaction
(4)转帐业务
<1>李铭2010-04-10 转帐500到张林。

➢SQL语句：
begin transaction
insert into Bank_TransferNote values(8,'1001','1002',500,' 2010-04-10') update Bank_Account set balance=balance-500 where AccountID='1001' update Bank_Account set balance=balance+500 where AccountID='1002' commit transaction
<2>张林2009-09-17 转帐1720 到刘鑫。

➢SQL语句：
begin transaction
insert into Bank_TransferNote values(9,'1002','1003',1720,' 2009-09-17') update Bank_Account set balance=balance-1720 where AccountID='1002' update Bank_Account set balance=balance+1720 where AccountID='1003' commit transaction
(5)注销业务：（将帐户状态设定为’注销’）
<1>李铭2010-04-10日，因出国定居申请帐户注销。

➢SQL语句：
begin transaction
insert into Bank_DestoryNote values(10,'1001',2010-04-10,'出国定居') update Bank_Account set AccountStatus='注销' where accountID='1001' commit transaction。