95-sakila说明
1. 下载
解压后得到三个文件：
sakila-schema.sql：创建Sakila数据库的结构、表、视图、存储过程和触发器sakila-data.sql：使用insert语句填充数据及在初始数据加载后，必须创建的触发器的定义sakila.mwb：文件是一个MySQL Workbench数据模型，可以在MySQL的工作台打开查看数据库结构
# 登录mysql
mysql -uroot -p123456
# 导入表的结构数据
source ~/tmp/sakila-schema.sql
# 导入表的数据
source ~/tmp/sakila-data.sql
2. 各表名称, 字段名称
- actor：演员表，列出了所有演员的信息。演员表和电影表之间是多对多的关系，通过film_actor表建立关系
create table actor
(
actor_id smallint unsigned auto_increment comment '代理主键，用于唯一标识表中的每个演员'
primary key,
first_name varchar(45) not null comment '演员的名字',
last_name varchar(45) not null comment '演员的姓氏',
last_update timestamp default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP comment '该行已创建或最近更新的时间'
)
comment '演员表';
create index idx_actor_last_name
on actor (last_name);
- address：地址表，包含客户、员工和商店的地址信息。地址表的主键出现在顾客、 员工、和存储表的外键
create table address
(
address_id smallint unsigned auto_increment comment '代理主键用于唯一标识表中的每个地址'
primary key,
address varchar(50) not null comment '地址的第一行',
address2 varchar(50) null comment '一个可选的第二行地址',
district varchar(20) not null comment '该地区的所属地区，这可以是国家，省，县等',
city_id smallint unsigned not null comment '指向城市表的外键',
postal_code varchar(10) null comment '邮政编码',
phone varchar(20) not null comment '地址的电话号码',
location geometry not null,
last_update timestamp default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP comment '该行已创建或最近更新的时间',
constraint fk_address_city
foreign key (city_id) references city (city_id)
on update cascade
)
comment '地址表';
create index idx_fk_city_id
on address (city_id);
create spatial index idx_location
on address (location);
- category：分类表，列出了可以分配到一个电影类别。分类和电影是多对多的关系，通过表film_category建立关系
create table category
(
category_id tinyint unsigned auto_increment comment '代理主键用于唯一标识表中的每个类别'
primary key,
name varchar(25) not null comment '类别名称',
last_update timestamp default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP comment '该行已创建或最近更新的时间'
)
comment '分类表';
- city：城市表，包含城市名单。城市表使用外键来标示国家；在地址表中被作为外键来使用
create table city
(
city_id smallint unsigned auto_increment comment '代理主键用于唯一标识表中的每个城市'
primary key,
city varchar(50) not null comment '城市的名字',
country_id smallint unsigned not null comment '外键，用于标示城市所属的国家',
last_update timestamp default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP comment '该行已创建或最近更新的时间',
constraint fk_city_country
foreign key (country_id) references country (country_id)
on update cascade
)
comment '城市表';
create index idx_fk_country_id
on city (country_id);
- country：国家表，包含的国家名单。国家表是指在城市表的外键
create table country
(
country_id smallint unsigned auto_increment comment '代理主键用于唯一标识表中的每个国家'
primary key,
country varchar(50) not null comment '国家的名称',
last_update timestamp default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP comment '该行已创建或最近更新的时间'
)
comment '国家表';
- customer：客户表，包含了所有客户的列表 。 客户表在支付表和租金表被作为外键使用；客户表使用外键来表示地址和存储
-- auto-generated definition
create table customer
(
customer_id smallint unsigned auto_increment comment '代理主键用于唯一标识表中的每个客户'
primary key,
store_id tinyint unsigned not null comment '一个外键，确定客户所属的store',
first_name varchar(45) not null comment '客户的名字',
last_name varchar(45) not null comment '客户的姓氏',
email varchar(50) null comment '客户的电子邮件地址',
address_id smallint unsigned not null comment '使用在地址 表的外键来确定客户的地址',
active tinyint(1) default 1 not null comment '表示客户是否是活跃的客户',
create_date datetime not null comment '顾客被添加到系统中的日期。使用 INSERT 触发器自动设置',
last_update timestamp default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP comment '该行已创建或最近更新的时间',
constraint fk_customer_address
foreign key (address_id) references address (address_id)
on update cascade,
constraint fk_customer_store
foreign key (store_id) references store (store_id)
on update cascade
)
comment '客户表';
create index idx_fk_address_id
on customer (address_id);
create index idx_fk_store_id
on customer (store_id);
create index idx_last_name
on customer (last_name);
- film：电影表，是一个可能在商店库存的所有影片名单。每部影片的实际库存信息保存在库存表。电影表指使用外键来标示语言表；在film_category、film_actor和库存表中作为外键使用
-- auto-generated definition
create table film
(
film_id smallint unsigned auto_increment comment '代理主键用于唯一标识表中的每个电影'
primary key,
title varchar(128) not null comment '影片的标题',
description text null comment '一个简短的描述或电影的情节摘要',
release_year year null comment '电影发行的年份',
language_id tinyint unsigned not null comment '使用外键来标示语言',
original_language_id tinyint unsigned null comment '电影的原始语音。使用外键来标示语言',
rental_duration tinyint unsigned default 3 not null comment '租赁期限的长短，以天作为单位',
rental_rate decimal(4, 2) default 4.99 not null comment '指定的期限内电影的租金',
length smallint unsigned null comment '影片的长度，以分钟为单位',
replacement_cost decimal(5, 2) default 19.99 not null comment '如果电影未被归还或损坏状态向客户收取的款项',
rating enum ('G', 'PG', 'PG-13', 'R', 'NC-17') default 'G' null comment '分配给电影评级。可以是 G， PG，PG - 13 ， R 或NC - 17',
special_features set ('Trailers', 'Commentaries', 'Deleted Scenes', 'Behind the Scenes') null comment '包括DVD上常见的特殊功能的列表',
last_update timestamp default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP comment '该行已创建或最近更新的时间',
constraint fk_film_language
foreign key (language_id) references language (language_id)
on update cascade,
constraint fk_film_language_original
foreign key (original_language_id) references language (language_id)
on update cascade
)
comment '电影表';
create index idx_fk_language_id
on film (language_id);
create index idx_fk_original_language_id
on film (original_language_id);
create index idx_title
on film (title);
- film_actor：电影演员表，用来支持许多电影和演员之间的多对多关系 。对于每一个给定的电影演员，将有film_actor表中列出的演员和电影中的一个行
create table film_actor
(
actor_id smallint unsigned not null comment '用于识别演员的外键',
film_id smallint unsigned not null comment '用于识别电影的外键',
last_update timestamp default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP comment '该行已创建或最近更新的时间',
primary key (actor_id, film_id),
constraint fk_film_actor_actor
foreign key (actor_id) references actor (actor_id)
on update cascade,
constraint fk_film_actor_film
foreign key (film_id) references film (film_id)
on update cascade
)
comment '电影演员表';
create index idx_fk_film_id
on film_actor (film_id);
- film_category：电影类别表，用来支持许多电影和类别之间的多对多关系 。应用于电影的每个类别中，将有film_category表中列出的类别和电影中的一个行
create table film_category
(
film_id smallint unsigned not null comment '用于识别电影的外键',
category_id tinyint unsigned not null comment '用于识别类别的外键',
last_update timestamp default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP comment '该行已创建或最近更新的时间',
primary key (film_id, category_id),
constraint fk_film_category_category
foreign key (category_id) references category (category_id)
on update cascade,
constraint fk_film_category_film
foreign key (film_id) references film (film_id)
on update cascade
)
comment '电影类别表';
- film_text：电影信息表，是Sakila样例数据库唯一使用MyISAM存储引擎的表。MyISAM类型不支持事务处理等高级处理，而InnoDB类型支持。MyISAM类型的表强调的是性能，其执行数度比InnoDB类型更快。此表提供允许全文搜索电影表中列出的影片的标题和描述。film_text表包含的film_id，标题和描述的列电影表，保存的内容与电影表上的内容同步（指电影表的插入、更新和删除操作）
create table film_text
(
film_id smallint not null comment '代理主键用于唯一标识表中的每个电影'
primary key,
title varchar(255) not null comment '影片的标题',
description text null comment '一个简短的描述或电影的情节摘要'
)
comment '电影信息表';
create fulltext index idx_title_description
on film_text (title, description);
- inventory：库存表，存放在一个给定的商店里的一个给定的电影的copy副本。使用外键来识别电影和存储；在出租表中使用外键来识别库存
create table inventory
(
inventory_id mediumint unsigned auto_increment comment '代理主键用于唯一标识每个项目在库存'
primary key,
film_id smallint unsigned not null comment '使用外键来识别电影',
store_id tinyint unsigned not null comment '使用外键来识别物品所在的商店',
last_update timestamp default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP comment '该行已创建或最近更新的时间',
constraint fk_inventory_film
foreign key (film_id) references film (film_id)
on update cascade,
constraint fk_inventory_store
foreign key (store_id) references store (store_id)
on update cascade
)
comment '库存表';
create index idx_fk_film_id
on inventory (film_id);
create index idx_store_id_film_id
on inventory (store_id, film_id);
- language：语言表，是一个查找表，列出可能使用的语言，电影可以有自己的语言和原始语言值。语言表在电影表中被作为外键来使用
create table language
(
language_id tinyint unsigned auto_increment comment '代理主键用于唯一标识每一种语言'
primary key,
name char(20) not null comment '语言的英文名称',
last_update timestamp default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP comment '该行已创建或最近更新的时间'
)
comment '代理主键用于唯一标识每一种语言';
- payment：付款表，记录每个客户的付款，如支付的金额和租金的资料。使用外键来表示客户、出租、和工作人员
create table payment
(
payment_id smallint unsigned auto_increment comment '代理主键用于唯一标识每个付款'
primary key,
customer_id smallint unsigned not null comment '使用外键来标识付款的客户',
staff_id tinyint unsigned not null comment '工作人员，负责处理支付 。使用外键来标识',
rental_id int null comment '租借ID, 外键，参照rental表',
amount decimal(5, 2) not null comment '付款金额',
payment_date datetime not null comment '处理付款的日期',
last_update timestamp default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP comment '该行已创建或最近更新的时间',
constraint fk_payment_customer
foreign key (customer_id) references customer (customer_id)
on update cascade,
constraint fk_payment_rental
foreign key (rental_id) references rental (rental_id)
on update cascade on delete set null,
constraint fk_payment_staff
foreign key (staff_id) references staff (staff_id)
on update cascade
)
comment '付款表';
create index idx_fk_customer_id
on payment (customer_id);
create index idx_fk_staff_id
on payment (staff_id);
- rental：租金表，一行表示每个inventory的租借客户、租借时间、归还时间租借表使用外键来标识库存 ，顾客和工作人员；在支付表中使用了外键来标识租金
-- auto-generated definition
create table rental
(
rental_id int auto_increment comment '代理主键唯一标识的租金'
primary key,
rental_date datetime not null comment '该项目租用的日期和时间',
inventory_id mediumint unsigned not null comment '该项目被租用',
customer_id smallint unsigned not null comment '租用该项目的客户',
return_date datetime null comment '归还日期',
staff_id tinyint unsigned not null comment '处理该项业务的工作人员',
last_update timestamp default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP comment '该行已创建或最近更新的时间',
constraint rental_date
unique (rental_date, inventory_id, customer_id),
constraint fk_rental_customer
foreign key (customer_id) references customer (customer_id)
on update cascade,
constraint fk_rental_inventory
foreign key (inventory_id) references inventory (inventory_id)
on update cascade,
constraint fk_rental_staff
foreign key (staff_id) references staff (staff_id)
on update cascade
)
comment '租金表';
create index idx_fk_customer_id
on rental (customer_id);
create index idx_fk_inventory_id
on rental (inventory_id);
create index idx_fk_staff_id
on rental (staff_id);
- staff：工作人员表，列出了所有工作人员，包括电子邮件地址，登录信息和图片信息。使用外键来标识存储和地址表；在出租、支付和商店表中作为外键
-- auto-generated definition
create table staff
(
staff_id tinyint unsigned auto_increment comment '代理主键唯一标识的工作人员'
primary key,
first_name varchar(45) not null comment '工作人员的名字',
last_name varchar(45) not null comment '工作人员的姓氏',
address_id smallint unsigned not null comment '工作人员的地址在地址表的外键',
picture blob null comment '工作人员的照片，使用了 BLOB属性',
email varchar(50) null comment '工作人员的电子邮件地址',
store_id tinyint unsigned not null comment '工作人员所在的商店，用外键标识',
active tinyint(1) default 1 not null comment '是否是活跃的工作人员',
username varchar(16) not null comment '用户名，由工作人员用来访问租赁系统',
password varchar(40) collate utf8mb4_bin null comment '工作人员访问租赁系统所使用的密码。使用了 SHA1 函数',
last_update timestamp default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP comment '该行已创建或最近更新的时间',
constraint fk_staff_address
foreign key (address_id) references address (address_id)
on update cascade,
constraint fk_staff_store
foreign key (store_id) references store (store_id)
on update cascade
)
comment '工作人员表';
create index idx_fk_address_id
on staff (address_id);
create index idx_fk_store_id
on staff (store_id);
- store：商店表，列出了系统中的所有商店。使用外键来标识工作人员和地址；在员工、客户、库存表被作为外键使用
create table store
(
store_id tinyint unsigned auto_increment comment '代理主键唯一标识的商店'
primary key,
manager_staff_id tinyint unsigned not null comment '使用外键来标识这家商店的经理',
address_id smallint unsigned not null comment '使用外键来确定这家店的地址',
last_update timestamp default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP comment '该行已创建或最近更新的时间',
constraint idx_unique_manager
unique (manager_staff_id),
constraint fk_store_address
foreign key (address_id) references address (address_id)
on update cascade,
constraint fk_store_staff
foreign key (manager_staff_id) references staff (staff_id)
on update cascade
)
comment '商店表';
create index idx_fk_address_id
on store (address_id);
3. 视图表
- actor_info：提供了所有演员的列表及所演的电影, 电影按category分组
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
- customer_list：客户列表，firstname和lastname连接成fullname，将
address, city, country集成在一个视图里
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
- film_list：电影列表视图，包含了每一部电影的信息及电影所对应的演员。电影对应的演员以逗号作为分隔符。连接了
film, film_category, category, film_actor, actor表的数据
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
- nicer_but_slower_film_list：电影列表视图，包含了每一部电影的信息及电影所对应的演员。电影对应的演员以逗号作为分隔符。连接了
film, film_category, category, film_actor, actor表的数据。和film_list View不同，演员名字只有单词首字母大写
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
- sales_by_film_category：每个电影种类的销售额。
payment → rental → inventory → film → film_category → category
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
- sales_by_store：每个商店的manager及销售额。
payment → rental → inventory → store → staff
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 p
INNER JOIN rental r ON p.rental_id = r.rental_id
INNER JOIN inventory i ON r.inventory_id = i.inventory_id
INNER JOIN store s ON i.store_id = s.store_id
INNER JOIN address a ON s.address_id = a.address_id
INNER JOIN city c ON a.city_id = c.city_id
INNER JOIN country cy ON c.country_id = cy.country_id
INNER JOIN staff m ON s.manager_staff_id = m.staff_id
GROUP BY s.store_id
ORDER BY cy.country, c.city
- staff_list：工作人员列表
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
4. 表结构关系
