[数据库]数据科学OLTP实验

---

实验要求

在MySQL上(必选)或者Spark SQL上(可选)完成实验，包括数据导入和查询实验， 设计销户。（本人采用的是MySQL）

---

实验准备

1. OLTP（on-line transaction processing）为联机事务处理，主要是对数据的增删改。

2. 安装Mysql（Macbook Mysql8.0.20）。

3. 复习数据库和SQL语句相关内容。

---

实验过程

模型

本实例中，我们需要对客户、账户、交易明细进行管理，所以需要三张表，对这些信息进行管理，这些表格的字段及其主外键关系，请参考下图。

• 客户表，包括id、姓名、性别、出生日期、居住地等字段。Id为主键，一般用身份证号即可。

• 账户表是活期账户，字段包括客户id、开户日期、当前余额等字段。客户id是账户表的主键，同时它指向客户表，是一个外键。

• 交易明细表记录历次交易，包括开户、销户、存钱、取钱、转账等。包括客户id、操作日期时间、操作类型、金额、上次余额、本次余额等。其中客户id、操作日期时间为主键，客户id指向客户表，是一个外键。

---

建表

首先在终端登录Mysql：

mysql -u root -p （后面加上自己的密码）

创建数据库：

create database OLTP;

选择使用的数据库：（这一步很容易忘记）

use OLTP;
切换数据库后会显示：Database changed

1. 客户表

客户表包含如下字段。表名为 customer。

字段	数据类型	备注
id	Char(18)	主键，唯一标识一个客户。
name	Char(16)	姓名
gender	Char(1)	性别，M 表示男，F 表示女
birth_day	Char(8)	用字符串表示日期，比如“19900101” 表示 1990 年 1 月 1 日，月和日都用两个字符表示
residence_place	Char(16)	居住地，具体到省份

建表的Sql语句如下：

Create table customer ( 
	id char(18), 
	name char(16), 
	gender char(1),
	birth_day char(8), 
	residence_place char(16) 
);

创建完表后才发现忘记添加主键信息：

alter table customer add primary key (id);

2. 账户表

账户表是活期账户，其字段如下，表名为 savings。

由于一个客户只有一个账户，所以 cust_id 为外键指向 customer 表，同时也作为账户表的主键，唯一标识这个账户是谁的。

字段	数据类型	备注
cust_id	Char(18)	这是外键，也是主键。
open_date	Char(8)	开户日期
cur_balance	Numeric(16,3)	当前余额，小数点后到厘

建表的Sql语句如下：

Create table savings (
	cust_id char(18) primary key ,
	open_date char(8),
	cur_balance decimal(16,3),
	foreign key(cust_id) references customer(id)
);

我们用 numeric 来表示金额，定义这种字段类型的时候，第一个参数表示精度，即包括小数点的十进制数字的个数，第二个参数表示小数点后的数字位数。

比如 salary 定义为 decimal(5,2)，那么它的范围为-99.99 到 99.99，注意包括小数点。 NUMERIC 和 DECIMAL 类型被 MySQL 以同样的类型实现，在 SQL92 标准中是允许的。

3. 交易明细表

这个表记录用户的交易明细，包括存钱、取钱、转账等。

表名为 transaction_history。

字段	数据类型	备注
cust_id	char(18)	这是外键 和 operation_datetime、Operation_type 一起构成主键
operation_datetime	Char(12)	用字符串表示日期时间，比如 “19900101151515”表示 1990 年 1 月 1 日 15 点 15 分 15 秒
Operation_type	Char(1)	O 表示 open 开户, C 表示 close 销户, D 表示 deposit 存钱,W 表示 withdraw 取钱, T 表示 transfer 转账
amount	操作金额	正数表示增加 负数表示减少
last_balance	余额	上一个余额
cur_balance	余额	当前余额

建表的 sql 语句如下：

Create table transaction_history (
	cust_id char(18), 
	operation_datetime char(12), 
	operation_type char(1), 
	amount decimal(16,3), 
	last_balance decimal(16,3), 
	cur_balance decimal(16,3)
	foreign key(cust_id) references customer(id)
);

添加主键约束：

alter table transaction_history add primary key(cust_id,operation_datetime,operation_type);

---

初始数据

插入 3 个用户

insert into customer values ('110108197012190014', 'wang tao', 'M', '19701219', 'beijing'); 
insert into customer values ('110108197108290016', 'li ming', 'M', '19710829', 'tianjin'); 
insert into customer values ('110108197509050018', 'li li', 'F', '19750905', 'beijing');

备注：M/F 表示 male 和 female。注意身份证里面的日期和出生日期的对应关系。

插入 3 个开户记录

insert into savings values ('110108197012190014', '20180301', 13000.00); 
insert into savings values ('110108197108290016', '20180101', 35000.00); 
insert into savings values ('110108197509050018', '20180101', 150000.00);

备注：3 个人开户的日期不一样，开户时存入的金额不一样。

插入一些交易记录

具体如下：

开户、开户、开户：三个人的开户应该在交易历史记录里有一笔记录。

insert into transaction_history values('110108197012190014', '20180301093030', 'O', 13000.00, 0, 13000.00); 
insert into transaction_history values('110108197108290016', '20180101103030', 'O', 35000.00, 0, 35000.00);
insert into transaction_history values('110108197509050018', '20180101111515', 'O', 150000.00, 0, 150000.00);

备注：开户的时候，交易金额就是开户存入的钱数，原来的余额是 0，现在的余额就是刚刚存进去的钱。

此时出现了报错，ERROR 1406 (22001): Data too long for column 'operation_datetime' at row 1，原因是原先设置的operation_datetime是12位Char，其实需要14位，现修改数据类型：

alter table transaction_history modify operation_datetime char(14);

重新插入数据，显示成功。

接下来，存钱、取钱、存钱、取钱、存钱、取钱：每个账户存钱 500，取钱 200。

insert into transaction_history values('110108197012190014', '20180302093030', 'D', 500.00, 13000.00, 13500.00); 
update savings set cur_balance=13500.00 where cust_id='110108197012190014'; 
insert into transaction_history values('110108197012190014', '20180302093030', 'W', -200.00, 13500.00, 13300.00); 
update savings set cur_balance=13300.00 where cust_id='110108197012190014';

insert into transaction_history values('110108197108290016', '20180102103030', 'D', 500.00, 35000.00, 35500.00); 
update savings set cur_balance=35500.00 where cust_id='110108197108290016'; 
insert into transaction_history values('110108197108290016', '20180102103030', 'W', -200.00, 35500.00, 35300.00); 
update savings set cur_balance=35300.00 where cust_id='110108197108290016';

insert into transaction_history values('110108197509050018', '20180102111515', 'D',500.00, 150000.00, 150500.00); 
update savings set cur_balance=150500.00 where cust_id='110108197509050018'; 
insert into transaction_history values('110108197509050018', '20180102111515', 'W', -200.00, 150500.00, 150300.00); 
update savings set cur_balance=150300.00 where cust_id='110108197509050018';

备注：开户的第二天做了存钱和取钱。

转账

从’110108197012190014’转账 200 到’110108197509050018’，注意两者的交易记录和账户余额。

insert into transaction_history values('110108197012190014', '20180503093030', 'T', -200.00, 13300.00, 13100.00); 

update savings set cur_balance=13100.00 where cust_id='110108197012190014';

insert into transaction_history values('110108197509050018', '20180503093030', 'T', 200.00, 150300.00, 150500.00);

update savings set cur_balance=150500.00 where cust_id='110108197509050018';

备注：上述数据修改，应该组织成一个事务。

备注 1：一般来讲，转账的交易明细，应该有一个摘要字段，记录对方账号等信息。

备注 2：这里为了准备种子数据，数据的变动并没有按照事务来进行组织。比如存钱的时候，应该把余额的变动（savings）和交易历史的记录(transaction_history)，组织成一个事务，保证 all or nothing 的事务特性。

---

事务处理

存款

开始事务，修改余额，增加流水帐，提交事务。

'110108197012190014’存了 100 进去。

set @@autocommit=0;

start transaction;

--SELECT INTO 语句从一个表中选取数据，然后把数据插入另一个表中。
--SELECT INTO 语句常用于创建表的备份复件或者用于对记录进行存档。
select cur_balance into @cur_balance from savings where cust_id='110108197012190014'; 

--@作为变量定义的标识符
set @old_balance = @cur_balance; 

set @cur_balance = @cur_balance +100; 

update savings set cur_balance=@cur_balance where cust_id='110108197012190014';

set @cur_datetime = date_format(now(),'%Y%m%d%H%i%s');

select @cur_datetime; 

insert into transaction_history values('110108197012190014', @cur_datetime, 'D', +100.00, @old_balance, @cur_balance);

commit;

set @@autocommit=1;

对上述代码的解释如下：首先启动事务，然后读取当前的账户余额。接着更新账户余额， 同时增加交易历史记录。最后，提交事务。

备注 1：@打头的标识符是变量，比如@cur_datetime 是我们自己用的一个变量，目的是记录当前日期时间，为了记录存钱是什么时候发生的。

备注 2：set @@autocommit=0;表示由用户掌控事务提交，而不是每个 sql 单独作为一 个事务提交，set @@autocommit=1;则恢复单个 sql 语句作为事务提交的系统缺省配置。

---

取款

开始事务，修改余额，增加流水帐，提交事务 '110108197108290016’账户取款 100 。

set @@autocommit=0;

start transaction;

select cur_balance into @cur_balance from savings where cust_id='110108197012190014'; 

set @old_balance = @cur_balance; 

set @cur_balance = @cur_balance -100;

update savings set cur_balance=@cur_balance where cust_id='110108197012190014';

set @cur_datetime = date_format(now(),'%Y%m%d%H%i%s');

select @cur_datetime; 

insert into transaction_history values('110108197012190014', @cur_datetime, 'W', -100.00, @old_balance, @cur_balance);

commit;

set @@autocommit=1;

特别注意，余额不足的时候，不能取款，用语言编写这个交易的时候，注意这点。现在余额是足的。

---

开户

开始事务，新增用户表，修改余额表，增加流水帐，提交事务为身份证号为’110108197808080020’叫’wang tao’的一位男用户开户，存钱 80000 元。

set @@autocommit=0; 

start transaction;

insert into customer values ('110108197808080020', 'wang tao', 'M', '19780808', 'beijing'); 

insert into savings values ('110108197808080020', '20180510', 80000.00); 

insert into transaction_history values('110108197808080020', '20180510093030', 'O', 80000.00, 0, 80000.00);

commit; 

set @@autocommit=1;

销户

开始事务，删除用户表，删除余额表，删除所有的流水帐，提交事务。 如果这么做，历史记录就都没有了。 可以在用户表里面增加一个字段，为“账户激活状态”，销户的时候，把这个字段的值置为“N”即可。

set @@autocommit=0; 

start transaction;

delete from customer where id=…;

delete from savings where cust_id=…; 

delete from transaction_history where cust_id=…; 

commit;

set @@autocommit=1;

--在用户表添加字段，默认为'Y'
alter table customer add activate_state char(1) default 'Y';

update customer set activate_state='N' where id='110108197808080020';

---

查询交易明细

查询’110108197808080020’的’20180101’到’20180510’的所有交易明细。

select * from transaction_history where cust_id='110108197108290016' and operation_datetime >= '20180101' and operation_datetime <='20180510';

输出结果：

备注：由于把日期时间表示为字符串，我们可以利用字符串的字典序，查询一定时间范 围里面的交易记录，即 operation_datetime >= '20180101' and operation_datetime <='20180510' 所表达的意思，就是限定查找’20180101’到’20180510’之间的所有交易明细，由于是字符串比较， 没有时间部分信息也没有关系， 如果一定要加上， 可以写成 operation_datetime >= '20180101000000' and operation_datetime <='20180510235959'。

备注：由于只有一个 sql 语句，所以它自己就作为一个事务运行。无需我们指定事务开始和结束。

---

查询余额

查询’110108197808080020’的余额。

select cur_balance from savings where cust_id = '110108197808080020';

输出结果：

---

转账

开始事务，修改余额 1，修改余额 2，增加流水帐 1，增加流水帐 2，提交事务。

从’110108197012190014’到’110108197509050018’转 100 元钱。

为了确认转账成功， 需要在下面转账事务之前、之后， 通过“select cur_balance from savings where cust_id=‘110108197012190014’ or cust_id=‘110108197509050018’”来查询余额。

set @@autocommit=0;

start transction;

select cur_balance into @cur_balance_A from savings where cust_id='110108197012190014'; 

set @old_balance_A = @cur_balance_A; 

set @cur_balance_A = @cur_balance_A -100; 

update savings set cur_balance=@cur_balance_A where cust_id='110108197012190014';

set @cur_datetime = date_format(now(),'%Y%m%d%H%i%s');

select @cur_datetime; 

insert into transaction_history values('110108197012190014', @cur_datetime, 'T', -100.00, @old_balance_A, @cur_balance_A);

select cur_balance into @cur_balance_B from savings where cust_id='110108197509050018'; 

set @old_balance_B = @cur_balance_B; 

set @cur_balance_B = @cur_balance_B +100; 

update savings set cur_balance=@cur_balance_B where cust_id='110108197509050018';

set @cur_datetime = date_format(now(),'%Y%m%d%H%i%s');

select @cur_datetime; 

insert into transaction_history values('110108197509050018', @cur_datetime, 'T', +100.00, @old_balance_B, @cur_balance_B);

commit;

set @@autocommit=1;

在进行编程的时候，一定要注意，账户 A 的钱不够，就不能进行转账，需要严格做检查。在这里，我们简单地执行若干 sql，在保证余额够的情况下，做转账。

转账和回滚

只要把转账事务中的 commit 改成 rollback，就可以实现回滚。转账就没有发生，两个账户仍然是原来的钱。可以通过 select cur_balance from savings;语句来确认。

---

实验结果

实验最终三个表的内容如下：

• Customer表：
  

• Savings表：

• Transaction_history表：

（上述图片采用的Navicat进行数据库的可视化）

---

参考资料

OLTP与OLAP的关系是什么？
MySQL 数据库事务详解