Sakila——MySQL样例数据库解析

建库建表sql语句

一、Introduction

Sakila可以作为数据库设计的参考,也可作为实验数据。我是用作数据仓库和ODI学习的实验数据。


The Sakila sample database was developed by Mike Hillyer, a former member of the MySQL AB documentation team, andis intended to provide a standard schema that can be used for examples in books, tutorials, articles, samples, and so forth.Sakila sample database also serves to highlight the latest features of MySQL such as Views, Stored Procedures, and Triggers.

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

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


clip_image002clip_image004

方法二：下载后导入

The Sakila sample database is available fromhttp://dev.mysql.com/doc/index-other.html. A downloadable archive is available in compressed file or Zip format. The archive contains three files:sakila-schema.sql,sakila-data.sql, andsakila.mwb.


Thesakila-schema.sqlfile contains all theCREATEstatements required to create the structure of the Sakila database including tables, views, stored procedures, and triggers.


Thesakila-data.sqlfile contains theINSERTstatements required to populate the structure created by thesakila-schema.sqlfile, along with definitions for triggers that must be created after the initial data load.


Thesakila.mwbfile is a MySQL Workbench data model that you can open within MySQL Workbench to examine the database structure. For more information, seeMySQL Workbench.


下载地址：


ZIP格式：http://downloads.mysql.com/docs/sakila-db.zip


tar格式http://downloads.mysql.com/docs/sakila-db.tar.gz


官方文档http://dev.mysql.com/doc/sakila/en/index.html


解压后得到三个文件：


1. sakila-schema.sql 文件包含创建Sakila数据库的结构：表、视图、存储过程和触发器


2. sakila-data.sql文件包含：使用 INSERT语句填充数据及在初始数据加载后，必须创建的触发器的定义


3. sakila.mwb文件是一个MySQL Workbench数据模型，可以在MySQL的工作台打开查看数据库结构。


导入参考：http://dev.mysql.com/doc/sakila/en/sakila-installation.html


shell>mysql -u root -p

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

查看导入结果


sakila

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


image


全图：




Customer Data 和 Business Data





Inventory Data




3.1 Tables

3.1.1. The actor Table演员表


3.1.2. The address Table地址表


3.1.3. The category Table电影类别表


3.1.4. The city Table城市表


3.1.5. The country Table国家表


3.1.6. The customer Table客户表


3.1.7. The film Table电影表


3.1.8. The film_actor Table电影和演员中间对应表


3.1.9. The film_category Table电影和类别的中间对应表


3.1.10. The film_text Table只包含title和description两列的电影表子表，用以快速检索


3.1.11. The inventory Table库存表


3.1.12. The language Table语言表


3.1.13. The payment Table付款表


3.1.14. The rental Table租金表


3.1.15. The staff Table工作人员表


3.1.16. The store Table商店表


【01演员表actor】


演员表列出了所有演员的信息。演员表和电影表之间是多对多的关系，通过film_actor表建立关系



1

2

3

4


actor_id: 代理主键用于唯一标识表中的每个演员

first_name: 演员的名字

last_name: 演员的姓氏

last_update: 该行已创建或最近更新的时间



【02地址表address】


地址表包含客户、员工和商店的地址信息。地址表的主键出现在顾客、 员工、和存储表的外键 。



1

2

3

4

5

6

7

8


address_id: 代理主键用于唯一标识表中的每个地址

address: 地址的第一行

address2: 一个可选的第二行地址

district: 该地区的所属地区，这可以是国家，省，县等

city_id: 指向城市表的外键

postal_code: 邮政编码

phone: 地址的电话号码

last_update: 该行已创建或最近更新的时间



【03分类表category】


类别表列出了可以分配到一个电影类别。分类和电影是多对多的关系，通过表film_category建立关系



1

2

3


category_id: 代理主键用于唯一标识表中的每个类别

name: 类别名称

last_update: 该行已创建或最近更新的时间



【04城市表city】


城市表包含的城市名单。城市表使用外键来标示国家；在地址表中被作为外键来使用。



1

2

3

4


city_id: 代理主键用于唯一标识表中的每个城市

city: 城市的名字

country_id: 外键，用于标示城市所属的国家

last_update: 该行已创建或最近更新的时间



【05国家表country】


国家表中包含的国家名单。国家表是指在城市表的外键 。



1

2

3


country_id: 代理主键用于唯一标识表中的每个国家

country: 国家的名称

last_update: 该行已创建或最近更新的时间



【06客户表customer】


客户表包含了所有客户的列表 。 客户表在支付表和租金表被作为外键使用；客户表使用外键来表示地址和存储。



1

2

3

4

5

6

7

8

9


customer_id: 代理主键用于唯一标识表中的每个客户

store_id: 一个外键，确定客户所属的store。

first_name: 客户的名字

last_name: 客户的姓氏

email: 客户的电子邮件地址

address_id: 使用在地址 表的外键来确定客户的地址

active: 表示客户是否是活跃的客户

create_date: 顾客被添加到系统中的日期。使用 INSERT 触发器自动设置。

last_update: 该行已创建或最近更新的时间



说明： active: 此设置为“ FALSE“作为替代客户彻底删除。大多数查询应该有一个 WHERE active = TRUE 字句。 store_id: 此处的客户不仅限于只由这家商店出租，而是包括客户常常去逛的商店


【07电影表film】


电影表是一个可能在商店库存的所有影片名单。每部影片的拷贝的实际库存信息保存在库存表。电影表指使用外键来标示语言表；在film_category、film_actor和库存表中作为外键使用。



1

2

3

4

5

6

7

8

9

10

11

12

13


film_id: 代理主键用于唯一标识表中的每个电影

title: 影片的标题

description: 一个简短的描述或电影的情节摘要

release_year: 电影发行的年份

language_id: 使用外键来标示语言

original_language_id: 电影的原始语音。使用外键来标示语言

rental_duration: 租赁期限的长短，以天作为单位

rental_rate: 指定的期限内电影的租金

length: 影片的长度，以分钟为单位。

replacement_cost: 如果电影未被归还或损坏状态向客户收取的款项

rating: 分配给电影评级。可以是 G， PG，PG - 13 ， R 或NC - 17

special_features: 包括DVD上常见的特殊功能的列表

last_update: 该行已创建或最近更新的时间



特殊功能包括零个或多个拖车、评论、删剪片段、幕后。


【08 film_actor表】


film_actor表是用来支持许多电影和演员之间的多对多关系 。对于每一个给定的电影演员，将有film_actor表中列出的演员和电影中的一个行 。


film_actor表指的是使用外键的电影和演员表。



1

2

3


actor_id: 用于识别演员的外键

film_id: 用于识别电影的外键

last_update: 该行已创建或最近更新的时间



【09 film_category表】


film_category表是用来支持许多电影和类别之间的多对多关系 。应用于电影的每个类别中，将有film_category表中列出的类别和电影中的一个行。


film_category表是指使用外键 的 电影 和类别表 。



1

2

3


film_id: 用于识别电影的外键

category_id: 用于识别类别的外键

last_update: 该行已创建或最近更新的时间



【10 film_text表】


film_text表是Sakila样例数据库唯一使用MyISAM存储引擎的表。MyISAM类型不支持事务处理等高级处理，而InnoDB类型支持。MyISAM类型的表强调的是性能，其执行数度比InnoDB类型更快。此表提供允许全文搜索电影表中列出的影片的标题和描述。film_text表包含的film_id，标题和描述的列电影表，保存的内容与电影表上的内容同步（指电影表的插入、更新和删除操作）



1

2

3


film_id: 代理主键用于唯一标识表中的每个电影

title: 影片的标题

description: 一个简短的描述或电影的情节摘要



注意：film_text表的内容不应该直接修改。所有的变更来自于电影表 。


【11库存表inventory】


库存表的一行为存放在一个给定的商店里的一个给定的电影的copy副本。库存表是使用外键来识别电影和存储；在出租表中使用外键来识别库存。



1

2

3

4


inventory_id: 理主键用于唯一标识每个项目在库存

film_id: 使用外键来识别电影

store_id: 使用外键来识别物品所在的商店

last_update: 该行已创建或最近更新的时间



【12语言表language】


语言表是一个查找表，列出可能使用的语言，电影可以有自己的语言和原始语言值 。


语言表在电压表中被作为外键来使用。



1

2

3


language_id: 代理主键用于唯一标识每一种语言

name: 语言的英文名称

last_update: 该行已创建或最近更新的时间



【13付款表payment】


付款表记录每个客户的付款，如支付的金额和租金的资料。


付款表使用外键来表示客户、出租、和工作人员。



1

2

3

4

5

6

7


payment_id: 代理主键用于唯一标识每个付款

customer_id: 使用外键来标识付款的客户

staff_id: 工作人员，负责处理支付 。使用外键来标识

rental_id: 租借ID, 外键，参照rental表

amount: 付款金额

payment_date: 处理付款的日期

last_update: 该行已创建或最近更新的时间



【14租金表rental】


租借表的一行表示每个inventory的租借客户、租借时间、归还时间


租借表是使用外键来标识库存 ，顾客 和工作人员；在支付表中使用了外键来标识租金 。



1

2

3

4

5

6

7


rental_id: 代理主键唯一标识的租金

rental_date: 该项目租用的日期和时间

inventory_id: 该项目被租用

customer_id: 租用该项目的客户

return_date: 归还日期

staff_id: 处理该项业务的工作人员

last_update: 该行已创建或最近更新的时间



【15工作人员表staff】


工作人员表列出了所有的工作人员，包括电子邮件地址，登录信息和图片信息 。


工作人员表是指使用外键来标识存储和地址表；在出租、支付和商店表中作为外键。



1

2

3

4

5

6

7

8

9

10

11

12


staff_id: 代理主键唯一标识的工作人员

first_name: 工作人员的名字

last_name: 工作人员的姓氏

address_id: 工作人员的地址在地址表的外键

picture: 工作人员的照片，使用了 BLOB属性

email: 工作人员的电子邮件地址

store_id: 工作人员所在的商店，用外键标识

active: 是否是活跃的工作人员。

username: 用户名，由工作人员用来访问租赁系统

password: 工作人员访问租赁系统所使用的密码。使用了 SHA1 函数

last_update: 该行已创建或最近更新的时间

active: 是否有效，删除时设置为False



【16商店表store】


store表列出了系统中的所有商店 。


store使用外键来标识工作人员和地址；在员工、客户、库存表被作为外键使用。



1

2

3

4


store_id: 代理主键唯一标识的商店

manager_staff_id: 使用外键来标识这家商店的经理

address_id: 使用外键来确定这家店的地址

last_update: 该行已创建或最近更新的时间


3.2 Views

3.2.1. The actor_info View


actor_info视图提供了所有演员的列表及所演的电影, 电影按category分组.


actor →film_actor →film→film_category →category


image

SELECT
a.actor_id,
a.first_name,
a.last_name,
GROUP_CONCAT(DISTINCT CONCAT(c.name, ': ',
(SELECT GROUP_CONCAT(f.title ORDER BY f.title SEPARATOR ', ')
FROM sakila.film f
INNER JOIN sakila.film_category fc
ON f.film_id = fc.film_id
INNER JOIN sakila.film_actor fa
ON f.film_id = fa.film_id
WHERE fc.category_id = c.category_id
AND fa.actor_id = a.actor_id
)
)
ORDER BY c.name SEPARATOR '; ')
AS film_info
FROM sakila.actor a
LEFT JOIN sakila.film_actor fa
ON a.actor_id = fa.actor_id
LEFT JOIN sakila.film_category fc
ON fa.film_id = fc.film_id
LEFT JOIN sakila.category c
ON fc.category_id = c.category_id
GROUP BY a.actor_id, a.first_name, a.last_name

3.2.2. The customer_list View


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


customer→address→city →country


image

SELECT
cu.customer_id AS ID,
CONCAT(
cu.first_name,
_utf8 ' ',
cu.last_name
) AS NAME,
a.address AS address,
a.postal_code AS `zip code`,
a.phone AS phone,
city.city AS city,
country.country AS country,
IF (
cu.active,
_utf8 'active',
_utf8 ''
) AS notes,
cu.store_id AS SID
FROM
customer AS cu
JOIN address AS a ON cu.address_id = a.address_id
JOIN city ON a.city_id = city.city_id
JOIN country ON city.country_id = country.country_id

3.2.3. The film_list View


电影列表视图，包含了每一部电影的信息及电影所对应的演员。电影对应的演员以逗号作为分隔符。连接了film,film_category,category,film_actorandactor表的数据

SELECT
film.film_id AS FID,
film.title AS title,
film.description AS description,
category. NAME AS category,
film.rental_rate AS price,
film.length AS length,
film.rating AS rating,
GROUP_CONCAT(
CONCAT(
actor.first_name,
_utf8 ' ',
actor.last_name
) SEPARATOR ', '
) AS actors
FROM
category
LEFT JOIN film_category ON category.category_id = film_category.category_id
LEFT JOIN film ON film_category.film_id = film.film_id
JOIN film_actor ON film.film_id = film_actor.film_id
JOIN actor ON film_actor.actor_id = actor.actor_id
GROUP BY
film.film_id

image


3.2.4. The nicer_but_slower_film_list View


电影列表视图，包含了每一部电影的信息及电影所对应的演员。电影对应的演员以逗号作为分隔符。连接了film,film_category,category,film_actorandactor表的数据。和The film_list View不同，演员名字只有单词首字母大写了。


image

SELECT
film.film_id AS FID,
film.title AS title,
film.description AS description,
category. NAME AS category,
film.rental_rate AS price,
film.length AS length,
film.rating AS rating,
GROUP_CONCAT(
CONCAT(
CONCAT(
UCASE(
SUBSTR(actor.first_name, 1, 1)
),
LCASE(
SUBSTR(
actor.first_name,
2,
LENGTH(actor.first_name)
)
),
_utf8 ' ',
CONCAT(
UCASE(
SUBSTR(actor.last_name, 1, 1)
),
LCASE(
SUBSTR(
actor.last_name,
2,
LENGTH(actor.last_name)
)
)
)
)
) SEPARATOR ', '
) AS actors
FROM
category
LEFT JOIN film_category ON category.category_id = film_category.category_id
LEFT JOIN film ON film_category.film_id = film.film_id
JOIN film_actor ON film.film_id = film_actor.film_id
JOIN actor ON film_actor.actor_id = actor.actor_id
GROUP BY
film.film_id

3.2.5. The sales_by_film_category View


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


image

SELECT
c.name AS category
, SUM(p.amount) AS total_sales
FROM payment AS p
INNER JOIN rental AS r ON p.rental_id = r.rental_id
INNER JOIN inventory AS i ON r.inventory_id = i.inventory_id
INNER JOIN film AS f ON i.film_id = f.film_id
INNER JOIN film_category AS fc ON f.film_id = fc.film_id
INNER JOIN category AS c ON fc.category_id = c.category_id
GROUP BY c.name
ORDER BY total_sales DESC

3.2.6. The sales_by_store View


每个商店的manager及销售额。


payment →rental→inventory→store→staff


image

SELECT
CONCAT(c.city, _utf8',', cy.country) AS store
, CONCAT(m.first_name, _utf8' ', m.last_name) AS manager
, SUM(p.amount) AS total_sales
FROM payment AS p
INNER JOIN rental AS r ON p.rental_id = r.rental_id
INNER JOIN inventory AS i ON r.inventory_id = i.inventory_id
INNER JOIN store AS s ON i.store_id = s.store_id
INNER JOIN address AS a ON s.address_id = a.address_id
INNER JOIN city AS c ON a.city_id = c.city_id
INNER JOIN country AS cy ON c.country_id = cy.country_id
INNER JOIN staff AS m ON s.manager_staff_id = m.staff_id
GROUP BY s.store_id
ORDER BY cy.country, c.city

3.2.7. The staff_list View


工作人员的列表


image

SELECT
s.staff_id AS ID,
CONCAT(
s.first_name,
_utf8 ' ',
s.last_name
) AS NAME,
a.address AS address,
a.postal_code AS `zip code`,
a.phone AS phone,
city.city AS city,
country.country AS country,
s.store_id AS SID
FROM
staff AS s
JOIN address AS a ON s.address_id = a.address_id
JOIN city ON a.city_id = city.city_id
JOIN country ON city.country_id = country.country_id
3.3 Stored Procedures

3.3.1. The film_in_stock Stored Procedure




Description


Thefilm_in_stockstored procedure is used to determine whether any copies of a given film are in stock at a given store.


判断在一个给定的商店里是否有一个给定电影的copy.


Parameters

p_film_id: The ID of the film to be checked, from thefilm_idcolumn of thefilmtable.


p_store_id: The ID of the store to check for, from thestore_idcolumn of thestoretable.


p_film_count: AnOUTparameter that returns a count of the copies of the film in stock.

Return Values


This procedure produces a table of inventory ID numbers for the copies of the film in stock, and returns (in thep_film_countparameter) a count that indicates the number of rows in that table.


BEGIN
SELECT inventory_id
FROM inventory
WHERE film_id = p_film_id
AND store_id = p_store_id
AND inventory_in_stock(inventory_id);
SELECT FOUND_ROWS() INTO p_film_count;
END

image


3.3.2. The film_not_in_stock Stored Procedure


和The film_in_stock Stored Procedure相反，判断给定的电影，是否有copy，不在一个给定的商店




Description


Thefilm_not_in_stockstored procedure is used to determine whether there are any copies of a given film not in stock (rented out) at a given store.


Parameters

p_film_id: The ID of the film to be checked, from thefilm_idcolumn of thefilmtable.


p_store_id: The ID of the store to check for, from thestore_idcolumn of thestoretable.


p_film_count: AnOUTparameter that returns a count of the copies of the film not in stock.

Return Values


This procedure produces a table of inventory ID numbers for the copies of the film not in stock, and returns (in thep_film_countparameter) a count that indicates the number of rows in that table.


BEGIN
SELECT inventory_id
FROM inventory
WHERE film_id = p_film_id
AND store_id = p_store_id
AND NOT inventory_in_stock(inventory_id);
SELECT FOUND_ROWS() INTO p_film_count;
END

image


3.3.3. The rewards_report Stored Procedure


过去一个月，数量大于输入参数min_monthly_purchases ，消费金额大于输入参数min_dollar_amount_purchased的客户




Description


Therewards_reportstored procedure generates a customizable list of the top customers for the previous month.


Parameters

min_monthly_purchases: The minimum number of purchases or rentals a customer needed to make in the last month to qualify.


min_dollar_amount_purchased: The minimum dollar amount a customer needed to spend in the last month to qualify.


count_rewardees: AnOUTparameter that returns a count of the customers who met the qualifications specified.

Return Values


This function returns a table of customers who met the qualifications specified. The table has the same structure as thecustomertable. The procedure also returns (in thecount_rewardeesparameter) a count that indicates the number of rows in that table.


Stop Service net stop uxsms


Start Service net start uxsms


Disable Service sc config uxsms start= disabled


Enable Service sc config uxsms start= auto


proc: BEGIN
DECLARE last_month_start DATE;
DECLARE last_month_end DATE;
/* Some sanity checks... */
IF min_monthly_purchases = 0 THEN
SELECT 'Minimum monthly purchases parameter must be > 0';
LEAVE proc;
END IF;
IF min_dollar_amount_purchased = 0.00 THEN
SELECT 'Minimum monthly dollar amount purchased parameter must be > $0.00';
LEAVE proc;
END IF;
/* Determine start and end time periods */
SET last_month_start = DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH);
SET last_month_start = STR_TO_DATE(CONCAT(YEAR(last_month_start),'-',MONTH(last_month_start),'-01'),'%Y-%m-%d');
SET last_month_end = LAST_DAY(last_month_start);
/*
Create a temporary storage area for
Customer IDs.
*/
CREATE TEMPORARY TABLE tmpCustomer (customer_id SMALLINT UNSIGNED NOT NULL PRIMARY KEY);
/*
Find all customers meeting the
monthly purchase requirements
*/
INSERT INTO tmpCustomer (customer_id)
SELECT p.customer_id
FROM payment AS p
WHERE DATE(p.payment_date) BETWEEN last_month_start AND last_month_end
GROUP BY customer_id
HAVING SUM(p.amount) > min_dollar_amount_purchased
AND COUNT(customer_id) > min_monthly_purchases;
/* Populate OUT parameter with count of found customers */
SELECT COUNT(*) FROM tmpCustomer INTO count_rewardees;
/*
Output ALL customer information of matching rewardees.
Customize output as needed.
*/
SELECT c.*
FROM tmpCustomer AS t
INNER JOIN customer AS c ON t.customer_id = c.customer_id;
/* Clean up */
DROP TABLE tmpCustomer;
END

image


因为不满足系统时间，所以结果为0

3.4 Stored Functions

3.4.1. The get_customer_balance Function


返回指定客户到某一个日期欠的钱。 所有的rental_rate+超期费用-所有的payment




Description


Theget_customer_balancefunction returns the current amount owing on a specified customer's account.


Parameters

p_customer_id: The ID of the customer to check, from thecustomer_idcolumn of thecustomertable.


p_effective_date: The cutoff date for items that will be applied to the balance. Any rentals, payments, and so forth after this date are not counted.

Return Values


This function returns the amount owing on the customer's account.


BEGIN
#OK, WE NEED TO CALCULATE THE CURRENT BALANCE GIVEN A CUSTOMER_ID AND A DATE
#THAT WE WANT THE BALANCE TO BE EFFECTIVE FOR. THE BALANCE IS:
# 1) RENTAL FEES FOR ALL PREVIOUS RENTALS
# 2) ONE DOLLAR FOR EVERY DAY THE PREVIOUS RENTALS ARE OVERDUE
# 3) IF A FILM IS MORE THAN RENTAL_DURATION * 2 OVERDUE, CHARGE THE REPLACEMENT_COST
# 4) SUBTRACT ALL PAYMENTS MADE BEFORE THE DATE SPECIFIED
DECLARE v_rentfees DECIMAL(5,2); #FEES PAID TO RENT THE VIDEOS INITIALLY
DECLARE v_overfees INTEGER;#LATE FEES FOR PRIOR RENTALS
DECLARE v_payments DECIMAL(5,2); #SUM OF PAYMENTS MADE PREVIOUSLY
SELECT IFNULL(SUM(film.rental_rate),0) INTO v_rentfees
FROM film, inventory, rental
WHERE film.film_id = inventory.film_id
AND inventory.inventory_id = rental.inventory_id
AND rental.rental_date <= p_effective_date
AND rental.customer_id = p_customer_id;
SELECT IFNULL(SUM(IF((TO_DAYS(rental.return_date) - TO_DAYS(rental.rental_date)) > film.rental_duration,
((TO_DAYS(rental.return_date) - TO_DAYS(rental.rental_date)) - film.rental_duration),0)),0) INTO v_overfees
FROM rental, inventory, film
WHERE film.film_id = inventory.film_id
AND inventory.inventory_id = rental.inventory_id
AND rental.rental_date <= p_effective_date
AND rental.customer_id = p_customer_id;SELECT IFNULL(SUM(payment.amount),0) INTO v_payments
FROM payment
WHERE payment.payment_date <= p_effective_date
AND payment.customer_id = p_customer_id;
RETURN v_rentfees + v_overfees - v_payments;
END

3.4.2. The inventory_held_by_customer Function


返回指定的inventory现在在那个客户的customer_id




Theinventory_held_by_customerfunction returns thecustomer_idof the customer who has rented out the specified inventory item.


Parameters

p_inventory_id: The ID of the inventory item to be checked.

Return Values


This function returns thecustomer_idof the customer who is currently renting the item, orNULLif the item is in stock.


BEGIN
DECLARE v_customer_id INT;
DECLARE EXIT HANDLER FOR NOT FOUND RETURN NULL;
SELECT customer_id INTO v_customer_id
FROM rental
WHERE return_date IS NULL
AND inventory_id = p_inventory_id;
RETURN v_customer_id;
END

3.4.3. The inventory_in_stock Function


返回指定的inventory是否在商店里




Theinventory_in stock functionreturns a boolean value indicating whether the inventory item specified is in stock.


Parameters

p_inventory_id: The ID of the inventory item to be checked.

Return Values


This function returnsTRUEorFALSEto indicate whether the item specified is in stock.


BEGIN
DECLARE v_rentals INT;
DECLARE v_out INT;
#AN ITEM IS IN-STOCK IF THERE ARE EITHER NO ROWS IN THE rental TABLE
#FOR THE ITEM OR ALL ROWS HAVE return_date POPULATED
SELECT COUNT(*) INTO v_rentals
FROM rental
WHERE inventory_id = p_inventory_id;
IF v_rentals = 0 THEN
RETURN TRUE;
END IF;
SELECT COUNT(rental_id) INTO v_out
FROM inventory LEFT JOIN rental USING(inventory_id)
WHERE inventory.inventory_id = p_inventory_id
AND rental.return_date IS NULL;
IF v_out > 0 THEN
RETURN FALSE;
ELSE
RETURN TRUE;
END IF;
END
3.5 Triggers

3.5.1. The customer_create_date Trigger


插入新客户时，设置create_date为当前日期

-- Trigger to enforce create dates on INSERT
CREATE TRIGGER customer_create_date BEFORE INSERT ON customer
FOR EACH ROW SET NEW.create_date = NOW();

3.5.2. The payment_date Trigger


payment新增记录时，payment_date设置为当前日期

-- Trigger to enforce payment_date during INSERT
CREATE TRIGGER payment_date BEFORE INSERT ON payment
FOR EACH ROW SET NEW.payment_date = NOW();

3.5.3. The rental_date Trigger


rental表新增记录时，rental_date设置为当前日期

-- Trigger to enforce rental_date on INSERT
CREATE TRIGGER rental_date BEFORE INSERT ON rental
FOR EACH ROW SET NEW.rental_date = NOW();

3.5.4. The ins_film Trigger


确保 film_text 和 film同步插入

CREATE TRIGGER `ins_film` AFTER INSERT ON `film` FOR EACH ROW BEGIN
INSERT INTO film_text (film_id, title, description)
VALUES (new.film_id, new.title, new.description);
END;;

3.5.5. The upd_film Trigger


确保 film_text 和 film同步更新

CREATE TRIGGER `upd_film` AFTER UPDATE ON `film` FOR EACH ROW BEGIN
IF (old.title != new.title) OR (old.description != new.description) OR (old.film_id != new.film_id)
THEN
UPDATE film_text
SET title=new.title,
description=new.description,
film_id=new.film_id
WHERE film_id=old.film_id;
END IF;
END;;

3.5.6. The del_film Trigger


确保 film_text 和 film同步删除

CREATE TRIGGER `del_film` AFTER DELETE ON `film` FOR EACH ROW BEGIN
DELETE FROM film_text WHERE film_id = old.film_id;
END;;



.热点聚合:解析 数据库 Sakila MySQL
.

相关文章
MySQL的Innodb中的事务隔离级别和锁的关系
使用django向mysql中存入emoji表情
win7 64下安装mysql-python报错的解决办法
MySQL学习笔记02_数据库和表的基本操作
如何在PHP7中安装mysql的扩展
常用mysql 语句整理
mysql通过“延迟关联”进行limit分页查询优化的一个实
bat命令获取mysql路径
mysql触发器 当记录的指定字段发生变化时，跟新表中的另
将mysql非分区表转换为分区表