Sakila MySQL 样例数据库解析

0.前言

Sakila 可以作为数据库设计的参考，也可作为实验数据。本例是用作数据分析学习的实验数据。

1.实验环境

方法一：安装 MySQL 时选择 FULL 完全安装

安装 MySQL 时选择 FULL 完全安装，默认安装了 Sakila sample database

方法二：下载后导入

下载地址：

• ZIP 格式
• tar 格式
• 官方文档

解压后得到三个文件：

1. sakila-schema.sql 文件包含创建 Sakila 数据库的结构：表、视图、存储过程和触发器
2. sakila-data.sql 文件包含：使用 INSERT 语句填充数据及在初始数据加载后，必须创建的触发器的定义
3. sakila.mwb 文件是一个 MySQL Workbench 数据模型，可以在 MySQL 的工作台打开查看数据库结构。

shell> mysql -u root -p
mysql> SOURCE C:/temp/sakila-db/sakila-schema.sql;
mysql> SOURCE C:/temp/sakila-db/sakila-data.sql;

2.数据结构

Sakila 样例数据库包括 16 张表格，7 个视图，3 个 Stored Procedures，3 个 Functions，6 个 Triggers。英文描述点开相应连接即可查看。
总览：

关系图:

3.视图

actor_info 视图提供了所有演员的列表及所演的电影，电影按 category 分组.
actor → film_actor → film → film_category → category

1. actor_id: 代理主键用于唯一标识表中的每个演员
2. first_name: 演员的名字
3. last_name: 演员的姓氏
4. last_update: 该行已创建或最近更新的时间

客户列表，firstname和lastname连接成fullname，将 address, city, country 集成在一个视图里
customer → address → city → country

1. stomer_id: 代理主键用于唯一标识表中的每个客户
2. store_id: 一个外键，确定客户所属的 store。
3. first_name: 客户的名字
4. last_name: 客户的姓氏
5. email: 客户的电子邮件地址
6. address_id: 使用在地址 表的外键来确定客户的地址
7. active: 表示客户是否是活跃的客户
8. create_date: 顾客被添加到系统中的日期。使用 INSERT 触发器自动设置。
9. last_update: 该行已创建或最近更新的时间

每个电影种类的销售额
payment → rental → inventory → film → film_category → category

每个商店的 manager 及销售额。
payment → rental → inventory → store → staff

store 表列出了系统中的所有商店 。
store 使用外键来标识工作人员和地址；在员工、客户、库存表被作为外键使用。

1. store_id: 代理主键唯一标识的商店
2. manager_staff_id: 使用外键来标识这家商店的经理
3. address_id: 使用外键来确定这家店的地址
4. last_update: 该行已创建或最近更新的时间

工作人员的列表

工作人员表列出了所有的工作人员，包括电子邮件地址，登录信息和图片信息 。
工作人员表是指使用外键来标识存储和地址表；在出租、支付和商店表中作为外键。

1. staff_id: 代理主键唯一标识的工作人员
2. first_name: 工作人员的名字
3. last_name: 工作人员的姓氏
4. address_id: 工作人员的地址在地址表的外键
5. picture: 工作人员的照片，使用了 BLOB 属性
6. email: 工作人员的电子邮件地址
7. store_id: 工作人员所在的商店，用外键标识
8. active: 是否是活跃的工作人员。
9. username: 用户名，由工作人员用来访问租赁系统
10. password: 工作人员访问租赁系统所使用的密码。使用了 SHA1 函数
11. last_update: 该行已创建或最近更新的时间
12. active: 是否有效，删除时设置为 False

4.数据分析

最具有购买潜力的顾客：

从600位顾客中去发现和找出最具有购买潜力的顾客，找出他们的邮箱地址和联系方法，并给他们发送邮件进行感谢，以保持该部分用户的持续购买欲望，增加用户的满意度，以此来保持商品的销售量。

0.数据获取

根据customer、payment两个表进行数据筛选，获得顾客编号：customer.customer_id 顾客编号；计算总消费金额;最后根据顾客消费总金额降序排列。

具体代码：

SELECT
  customer1.customer_id 顾客编号,
    CONCAT( customer1.`first_name`, CONCAT( " ", customer1.`last_name` ) ) AS '顾客名',
    FORMAT(sum(payment.amount),2) AS '消费金额'
FROM
    customer1,
    payment
WHERE
    customer1.customer_id = payment.customer_id
GROUP BY
    CONCAT( customer1.`first_name`, CONCAT( " ", customer1.`last_name` ) )
ORDER BY
    FORMAT(sum(payment.amount),2)+0 DESC

1.连接顾客姓名

通过筛选address_id连接customer和address两表得到顾客的编号、地址、电话，然后通过顾客的编号升序排列。

具体代码：

SELECT
CONCAT( customer1.`first_name`, CONCAT( " ", customer1.`last_name` ) ) AS 顾客姓名,
customer1.customer_id 顾客编号,
address.address 顾客地址,
address.phone 顾客电话,
customer1.email
FROM
customer1 ,
address
WHERE
    customer1.address_id=address.address_id
GROUP BY
    CONCAT( customer1.`first_name`, CONCAT( " ", customer1.`last_name` ) )
ORDER BY
  customer1.customer_id+0 ASC

2.结果分析

通过表一得到散点图：

根据数据分析我们可以看到顾客KARL SEAL的消费金额221.55是600位顾客中消费总金额最高的一位，他便是最有潜力的顾客，然后我们可以通过表二去得到他的联系信息。
通过散点图我们可以分析到大多数的顾客消费总金额分布在120元上下，所以我们以120元为界，将消费总金额在120元以上顾客列为有购买潜力的顾客，从表二寻找他们的联系方式，批量对他们发送邮件进行感谢。