MySQL官方演示数据库Sakila 使用指南

MySQL官方演示数据库Sakila 使用指南

简介

Sakila数据库是MySQL 官方提供的一个演示数据库，旨在模拟电影租赁业务。它包含了各种数据库表，如影片、顾客、租赁、支付等，以及关联这些表格的外键关系。Sakila数据库的业务背景主要包括以下几个方面：

1. 影片管理：Sakila数据库包含了各种影片的信息，如标题、描述、发行年份、租赁价格等。这些影片可以被顾客租借。

2. 顾客管理：数据库中存储了各个顾客的信息，如姓名、地址、联系方式等。顾客可以通过租赁影片来享受娱乐服务。

3. 租赁管理：租赁表格记录了顾客租借影片的信息，包括租赁日期、归还日期、租赁费用等。租赁管理方便了顾客和影片之间的交互。

4. 支付管理：支付表格记录了顾客对租赁费用的支付情况，包括支付日期、支付金额等。支付信息与租赁信息相关联，用于管理费用的结算。

5. 库存管理：库存表格记录了影片的库存情况，包括影片ID、库存量等信息。库存管理确保了影片的可供租借状态。

Sakila数据库可以用于学习数据库设计、SQL查询和业务逻辑的实践，是一个理想的教学和学习工具，也可用于书籍、教程、文章等的演示示例。Sakila数据库还可用于展现MySQL的功能，如视图、存储过程和触发器。

安装

Sakila样本数据库可从https://dev.mysql.com/doc/index-other.html获得。 可下载的存档有两种格式：tar文件或Zip格式。其中三个文件：sakila-schema.sql、sakila-data.sql和sakila.mwb。

提示

Sakila包含MySQL版本特定的注释，因为sakila模式和数据取决于您的MySQL服务器的版本。 例如，MySQL服务器5.7.5为InnoDB添加了对空间数据索引的支持，因此address地址表将包括MySQL 5.7.5及更高版本的空间感知的location列。

• sakila-schema.sql：该文件包含创建Sakila数据库结构所需的所有CREATE语句，包括表、视图、存储过程和触发器。

• sakila-data.sql: 该文件包含填充数据库所需的INSERT语句，以及初始数据加载后必须创建的触发器的定义。

• sakila.mwb:该文件是一个MySQL工作台数据模型，可以在MySQL工作台中打开该模型来检查数据库结构。 有关更多信息，请参阅MySQL Workbench。

要安装Sakila示例数据库，请按照以下步骤操作：

1. 将下载的存档文件提取到临时位置，如C:\temp\或/tmp/。当解压存档时，它会创建一个名为sakila-db的目录，其中包含sakila-schema.sql和sakila-data.sql文件。

2.使用mysql命令行客户端连接到MySQL服务器：

$> mysql -u root -p

输入密码。也可以使用非root帐户，前提是该帐户具有创建新数据库的权限。

3. 执行sakila-schema.sql脚本创建数据库结构，然后执行sakila-data.sql脚本来填充数据库：

mysql> SOURCE C:/temp/sakila-db/sakila-schema.sql;
mysql> SOURCE C:/temp/sakila-db/sakila-data.sql;

将sakila-schema.sql和sakila-data.sql文件的路径替换为系统上的实际路径。

提示

在Windows上，在执行SOURCE命令时使用斜杠而不是反斜杠。

4. 执行以下语句,确认示例数据库已正确安装。

mysql> USE sakila;
Database changed

mysql> SHOW FULL TABLES;
+----------------------------+------------+
| Tables_in_sakila           | Table_type |
+----------------------------+------------+
| actor                      | BASE TABLE |
| actor_info                 | VIEW       |
| address                    | BASE TABLE |
| category                   | BASE TABLE |
| city                       | BASE TABLE |
| country                    | BASE TABLE |
| customer                   | BASE TABLE |
| customer_list              | VIEW       |
| film                       | BASE TABLE |
| film_actor                 | BASE TABLE |
| film_category              | BASE TABLE |
| film_list                  | VIEW       |
| film_text                  | BASE TABLE |
| inventory                  | BASE TABLE |
| language                   | BASE TABLE |
| nicer_but_slower_film_list | VIEW       |
| payment                    | BASE TABLE |
| rental                     | BASE TABLE |
| sales_by_film_category     | VIEW       |
| sales_by_store             | VIEW       |
| staff                      | BASE TABLE |
| staff_list                 | VIEW       |
| store                      | BASE TABLE |
+----------------------------+------------+
23 rows in set (0.01 sec)

mysql> SELECT COUNT(*) FROM film;
+----------+
| COUNT(*) |
+----------+
|     1000 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(*) FROM film_text;
+----------+
| COUNT(*) |
+----------+
|     1000 |
+----------+
1 row in set (0.00 sec)

数据结构

表

名称	描述
actor	演员表
address	地址表
category	类别表
city	城市表
country	国家表
customer	客户表
film	电影表
film_actor	电影-演员关联表
film_category	电影-类别关联表
film_text	电影文本表
inventory	库存表
language	语言表
payment	支付表
rental	租赁表
staff	员工表
store	店铺表

实体关系图（ER图）

视图

视图	描述
actor_info	包含演员的详细信息的视图。
customer_list	包含客户列表的视图。
film_list	包含电影列表的视图。
nicer_but_slower_film_list	包含更优质但速度较慢的电影列表的视图。

存储过程

名称	描述
film_in_stock	存储过程，用于获取库存中的电影信息。
film_not_in_stock	存储过程，用于获取缺货的电影信息。
rewards_report	存储过程，用于生成奖励报告。

存储函数

函数	描述
get_customer_balance	用于获取客户的余额信息。
inventory_held_by_customer	用于获取客户持有的库存信息。
inventory_in_stock	用于获取库存中的库存信息。

触发器

触发器	描述
customer_create_date	当创建客户时触发，用于记录客户创建日期。
payment_date	当添加支付记录时触发，用于记录支付日期。
rental_date	当添加租赁记录时触发，用于记录租赁日期。
ins_film	当插入电影记录时触发的触发器。
upd_film	当更新电影记录时触发的触发器。
del_film	当删除电影记录时触发的触发器。

使用示例

租借DVD

要租借DVD，首先确认给定的库存项目是否有库存，然后向rental租赁表中插入一行记录。创建rental租赁表后，向payment支付表中插入一行记录。根据业务规则，还可能需要在处理租赁之前检查客户是否有未结清的余额。

SELECT inventory_in_stock(10);
INSERT INTO rental(rental_date, inventory_id, customer_id, staff_id) 
       VALUES(NOW(), 10, 3, 1);
SET @rentID = LAST_INSERT_ID(),
              @balance = get_customer_balance(3, NOW());
SELECT @rentID, @balance;
INSERT INTO payment (customer_id, staff_id, rental_id, amount,  payment_date)
       VALUES(3, 1, @rentID, @balance, NOW());

归还DVD

要归还DVD，更新rental租赁表并设置归还日期。为此，首先根据正在归还的物品的inventory_id确定要更新的rental_id。根据情况，可能需要检查客户余额，并可能通过向payment支付表中插入一行记录来处理逾期费用的付款。

SELECT rental_id
       FROM rental
       WHERE inventory_id = 10
       AND customer_id = 3
       AND return_date IS NULL
       INTO @rentID;
UPDATE rental
       SET return_date = NOW()
       WHERE rental_id = @rentID;
SELECT get_customer_balance(3, NOW());

查找逾期的DVDs

许多DVD商店每天制作一份逾期租金清单，以便联系客户并要求退回逾期DVD。

要创建这样的列表，请在rental租赁表中搜索返回日期为NULL的电影，并且租赁日期比film电影表中指定的租赁期限更远。如果是这样，电影逾期了，应该显示电影名称以及客户姓名和电话号码。

mysql> SELECT CONCAT(customer.last_name, ’, ‘, customer.first_name) AS customer,
           address.phone, film.title
           FROM rental INNER JOIN customer ON rental.customer_id = customer.customer_id
           INNER JOIN address ON customer.address_id = address.address_id
           INNER JOIN inventory ON rental.inventory_id = inventory.inventory_id
           INNER JOIN film ON inventory.film_id = film.film_id
           WHERE rental.return_date IS NULL
           AND rental_date + INTERVAL film.rental_duration DAY < CURRENT_DATE()
           ORDER BY title
           LIMIT 5;
+----------------+--------------+------------------+
| customer       | phone        | title            |
+----------------+--------------+------------------+
| OLVERA, DWAYNE | 62127829280  | ACADEMY DINOSAUR |
| HUEY, BRANDON  | 99883471275  | ACE GOLDFINGER   |
| OWENS, CARMEN  | 272234298332 | AFFAIR PREJUDICE |
| HANNON, SETH   | 864392582257 | AFRICAN EGG      |
| COLE, TRACY    | 371490777743 | ALI FOREVER      |
+----------------+--------------+------------------+
5 rows in set (0.10 sec)