sakila数据库详解

一、sakila数据库简介

sakila是为了演示MySQL的样例数据库，在MySQL5.0版本中被首次引入。该数据库主要用于演示基本的关系型数据库知识，包括表的关系，表之间的连接以及一些基本SQL语句的使用。

在sakila数据库中，包含有22个表格，包括有客户信息、租赁订单、电影信息等，这些表格之间有各种不同的关系。sakila数据库是学习MySQL关系型数据库特性以及查询，更新数据的最佳实践示例。

二、sakila数据库表格详解

1、表格actor

表格actor存储着电影演员的基本信息，例如姓名以及演员的ID。actor_id是唯一标识，非空字段。其他列包括first_name和last_name，可为空的last_update字段。

/* 创建表格actor */
CREATE TABLE actor (
actor_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
first_name VARCHAR(45) NOT NULL,
last_name VARCHAR(45) NOT NULL,
last_update TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (actor_id),
KEY idx_actor_last_name (last_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2、表格film_actor

表格film_actor将演员和电影表格联系在了一起，储存每个演员已经出演的电影。该表中，actor_id和film_id分别对应着表格actor和表格film的主键。last_update字段可以更新为当前时间戳。

/* 创建表格film_actor */
CREATE TABLE film_actor (
actor_id SMALLINT UNSIGNED NOT NULL,
film_id SMALLINT UNSIGNED NOT NULL,
last_update TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (actor_id,film_id),
KEY idx_fk_film_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
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

3、表格address

表格address包含了地址的所有相关信息，包括街道地址、邮政编码、城市ID以及更新时间等。address_id是该表格的主键。

/* 创建表格address */
CREATE TABLE address (
address_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
address VARCHAR(50) NOT NULL,
address2 VARCHAR(50) DEFAULT NULL,
district VARCHAR(20) NOT NULL,
city_id SMALLINT UNSIGNED NOT NULL,
postal_code VARCHAR(10) DEFAULT NULL,
phone VARCHAR(20) NOT NULL,
last_update TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (address_id),
KEY idx_fk_city_id (city_id),
CONSTRAINT `fk_address_city` FOREIGN KEY (`city_id`) REFERENCES `city` (`city_id`) ON DELETE NO ACTION ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

4、表格payment

表格payment储存了关于客户租赁订单相关的信息，包括支付方式、租赁时间以及返回时间等。payment_id是该表格的主键。

/* 创建表格payment */
CREATE TABLE payment (
payment_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
customer_id SMALLINT UNSIGNED NOT NULL,
staff_id TINYINT UNSIGNED NOT NULL,
rental_id INT DEFAULT NULL,
amount DECIMAL(5,2) NOT NULL,
payment_date DATETIME NOT NULL,
last_update TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (payment_id),
KEY idx_fk_staff_id (staff_id),
KEY idx_fk_customer_id (customer_id),
KEY idx_fk_rental_id (rental_id),
CONSTRAINT `fk_payment_customer` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`customer_id`) ON DELETE NO ACTION ON UPDATE CASCADE,
CONSTRAINT `fk_payment_rental` FOREIGN KEY (`rental_id`) REFERENCES `rental` (`rental_id`) ON DELETE SET NULL ON UPDATE CASCADE,
CONSTRAINT `fk_payment_staff` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`staff_id`) ON DELETE NO ACTION ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

5、表格film_category

表格film_category储存了每个电影的类别信息，例如：动作、科幻、经典、新片等。film_id和category_id分别对应着表格film和表格category中的主键。last_update字段可以更新为当前时间戳。

/* 创建表格film_category */
CREATE TABLE film_category (
film_id SMALLINT UNSIGNED NOT NULL,
category_id TINYINT UNSIGNED NOT NULL,
last_update TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (film_id,category_id),
KEY fk_film_category_category (category_id),
CONSTRAINT fk_film_category_film FOREIGN KEY (film_id) REFERENCES film (film_id) ON UPDATE CASCADE,
CONSTRAINT fk_film_category_category FOREIGN KEY (category_id) REFERENCES category (category_id) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

三、sakila数据库的使用

1、查询客户信息

查询客户信息是sakila数据库中最常见的功能之一，以下的SQL语句可以查询出客户表格customer中所有信，包括客户ID、名字、邮箱地址、地址以及所有的租借订单数等信息。

SELECT 
  customer.customer_id,
  CONCAT(customer.first_name,' ',customer.last_name) AS name,
  customer.email,
  address.address,
  address.phone, 
  COUNT(rental.rental_id) AS total_rentals
FROM 
  customer
  INNER JOIN address ON customer.address_id=address.address_id
  INNER JOIN rental ON customer.customer_id=rental.customer_id
GROUP BY 
  customer.customer_id
ORDER BY 
  total_rentals DESC;

2、查询电影信息

查询电影信息同样是sakila数据库中常用的功能，以下的SQL语句可以查询film表格中所有电影的信息，包括电影ID、电影标题、描述、长度以及评级等信息。

SELECT 
  film.film_id, 
  film.title, 
  film.description, 
  film.length, 
  rating.rating
FROM 
  film
  INNER JOIN film_rating ON film.film_id = film_rating.film_id
  INNER JOIN rating ON film_rating.rating_id = rating.rating_id
ORDER BY 
  film.length DESC;

3、更新数据

更新数据也是sakila数据库常用的功能，以下的SQL语句可以更新表格store的地址信息，将store_id为1的门店复制到prague地址。

UPDATE store
SET 
  address_id = (SELECT address_id FROM address WHERE address = '1136 Hare Krishna Parkway')
WHERE 
  store_id = 1;

四、总结

sakila数据库是学习MySQL关系型数据库特性以及查询，更新数据的最佳实践示例。通过深入的了解sakila数据库表格以及运用SQL语句，可以帮助大家更好地理解关系型数据库了解关系型数据库的特点和优势，为之后的数据库设计和应用开发打下基础。