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語句，可以幫助大家更好地理解關係型資料庫了解關係型資料庫的特點和優勢，為之後的資料庫設計和應用開發打下基礎。