MySQL示例数据库(MySQL Sample Databases) 之 sakila数据库

数据库 mysql sql 2023-08-17 07:08:17 490人浏览安东尼

摘要

文章目录 MySQL示例数据库(MySQL Sample Databases) 之 sakila数据库官方示例数据介绍sakila数据库sakila数据库安装sakila/sakila-schema.sql的脚本内容sakila的结

文章目录

- MySQL示例数据库(MySQL Sample Databases) 之 sakila数据库

Mysql示例数据库(mysql Sample Databases) 之 sakila数据库

官方示例数据介绍

Mysql 官方提供了多个示例数据库，在MySQL的学习、开发和实践中具有非常重要的作用，能够帮助初学者更好地理解和应用MySQL的各种功能和特性。

官方示例数据具体列表如下：

1. employee data (large dataset, includes data and test/verification suite)2. world database3. world_x database4. sakila database5. airportdb database (large dataset, intended for MySQL on OCI and HeatWave)6. menagerie database

这些数据库都可以通过如下官方网址进行下载和安装

https://dev.mysql.com/doc/index-other.html

在这里插入图片描述

sakila数据库

可以在MySQL官方网站的示例数据库中(Sample Databases)找到Sakila示例数据库。Sakila示例数据库是由oracle提供的，旨在替代原有的world示例数据库。相比于world示例数据库，Sakila示例数据库更加复杂，用于模拟一个DVD租赁店，并且包含了更多表格以及更复杂的表格关系。Sakila示例数据库被设计用来提供一个更加实用和有趣的示例，供MySQL学习者训练他们的SQL技能。如果您想要练习SQL技能，可以下载Sakila示例数据库进行尝试。

Sakila数据库包括16个表、7个视图、3个 Stored Procedures、3个 Stored Functions、6个触发器。

Sakila示例数据库中的基本表和视图简要介绍如下：1. actor - 这张表存储了所有演员的信息，包括演员的ID、姓名和最后更新时间。2. actor_info - 这个视图结合了 actor、film_actor 和 film 表，提供了更详细的演员信息，包括演员所出演的电影名称和描述。3. address - 这张表存储了客户的地址信息，包括地址ID、地址、区域、城市、邮编、国家和最后更新日期。4. cateGory - 这张表存储了所有电影的分类信息，包括分类ID 和分类名称。5. city - 这张表存储了城市的信息，包括城市ID、城市名称、区域以及最后更新时间。6. country - 这张表存储了国家的信息，包括国家ID、国家名称以及最后更新时间。7. customer - 这张表存储了客户信息，包括客户ID、姓名、电子邮件、地址、区域、城市、邮编以及最后更新时间。8. customer_list - 这个视图结合了 customer、address 和 city 表，提供了更详细的客户信息，包括客户ID、姓名、电子邮件、地址、区域、城市、邮编、国家和最后更新日期。9. film - 这张表存储了所有电影的信息，包括电影ID、电影名称、描述、发布年份、语言ID、原始语言ID、时长、租赁持续时间、租赁费用以及最后更新时间。10. film_actor - 这张表存储了电影演员的关联信息，包括电影ID、演员ID以及最后更新时间。11. film_category - 这张表存储了电影类型的关联信息，包括电影ID、分类ID以及最后更新时间。12. film_list - 这个视图结合了 film 和 category 表，提供了更详细的电影信息，包括电影ID、电影名称、描述、发布年份、语言、分类、时长、租赁持续时间以及租赁费用。13. film_text - 这张表存储了电影的描述信息，包括电影ID、标题、描述以及最后更新时间。14. inventory - 这张表存储了所有电影的库存信息，包括库存ID、电影ID、租赁商店ID以及最后更新时间。15. language - 这张表存储了语言的信息，包括语言ID、语言名称以及最后更新时间。16. nicer_but_slower_film_list - 这个视图结合了 film_list、film_actor、actor 和 category 表，提供更详细的电影信息，包括电影ID、电影名称、描述、发布年份、语言、租赁持续时间、租赁费用、演员列表、分类列表以及平均评分。17. payment - 这张表存储了所有顾客的付款信息，包括付款ID、顾客ID、租赁ID、付款金额、付款时间以及最后更新时间。18. rental - 这张表存储了所有出租信息，包括租赁ID、客户ID、库存ID、租赁时间、返还时间、出租费用以及最后更新时间。19. sales_by_film_category - 这个视图统计了每种电影类型的销售额，包括分类名称、销售额以及最后更新时间。20. sales_by_store - 这个视图统计了每个店铺的销售额，包括店铺ID、城市、国家、销售额以及最后更新时间。21. staff - 这张表存储了员工的信息，包括员工ID、姓名、电子邮件、店铺ID、用户名、密码以及最后更新时间。22. staff_list - 这个视图结合了 staff 和 address 表，提供了更详细的员工信息，包括员工ID、姓名、电子邮件、地址、区域、城市、邮编、国家以及最后更新时间。23. store - 这张表存储了店铺的信息，包括店铺ID、地址ID、经理ID以及最后更新时间。

Sakila数据库提供了丰富的数据集来演示如何使用MySQL进行高效的数据库查询和数据管理。通过使用Sakila数据库，初学者可以学习如何使用SELECT、JOIN、GROUP BY等关键字进行复杂的查询，使用INSERT、UPDATE、DELETE等关键字进行数据管理，以及使用约束、索引、存储过程等高级功能进行数据库设计和优化。

Sakila数据库还提供了一个典型的电影租赁业务场景，可以让学习者更好地理解数据库如何应用在真实的业务场景中。在官方网站上，有关该数据库的文档和示例都可以免费下载和使用。

sakila数据库安装

下载压缩包后就可以解压安装了。
解压：

ubuntu@mysql-vm:~$ tar -xvf sakila-db.tar.gzsakila-db/sakila-db/sakila-data.sqlsakila-db/sakila-schema.sqlsakila-db/sakila.mwb

安装：

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

查看数据

mysql>  USE sakila;Database changedmysql> 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.00 sec)

ubuntu@mysql-vm:~$ mysqlshow -uroot  -prootroot -vv sakilamysqlshow: [Warning] Using a passWord on the command line interface can be insecure.Database: sakila+----------------------------+----------+------------+|           Tables           | Columns  | Total Rows |+----------------------------+----------+------------+| actor                      |        4 |        200 || actor_info                 |        4 |        200 || address                    |        9 |        603 || category                   |        3 |         16 || city                       |        4 |        600 || country                    |        3 |        109 || customer                   |        9 |        599 || customer_list              |        9 |        599 || film                       |       13 |       1000 || film_actor                 |        3 |       5462 || film_category              |        3 |       1000 || film_list                  |        8 |       1000 || film_text                  |        3 |       1000 || inventory                  |        4 |       4581 || language                   |        3 |          6 || nicer_but_slower_film_list |        8 |       1000 || payment                    |        7 |      16044 || rental                     |        7 |      16044 || sales_by_film_category     |        2 |         16 || sales_by_store             |        3 |          2 || staff                      |       11 |          2 || staff_list                 |        8 |          2 || store                      |        4 |          2 |+----------------------------+----------+------------+23 rows in set.

sakila/sakila-schema.sql的脚本内容

-- Sakila Sample Database Schema-- Version 1.4-- Copyright (c) 2006, 2022, Oracle and/or its affiliates.-- Redistribution and use in source and binary forms, with or without-- modification, are permitted provided that the following conditions are-- met:-- * Redistributions of source code must retain the above copyright notice,--   this list of conditions and the following disclaimer.-- * Redistributions in binary form must reproduce the above copyright--   notice, this list of conditions and the following disclaimer in the--   documentation and/or other materials provided with the distribution.-- * Neither the name of Oracle nor the names of its contributors may be used--   to endorse or promote products derived from this software without--   specific prior written permission.-- THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS-- IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO,-- THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR-- PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR-- CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,-- EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,-- PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR-- PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF-- LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING-- NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS-- SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.SET NAMES utf8mb4;SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';DROP SCHEMA IF EXISTS sakila;CREATE SCHEMA sakila;USE sakila;---- Table structure for table `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 NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  PRIMARY KEY  (actor_id),  KEY idx_actor_last_name (last_name)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;---- Table structure for table `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,  -- Add GEOMETRY column for MySQL 5.7.5 and higher  -- Also include SRID attribute for MySQL 8.0.3 and higher      last_update TIMESTAMP NOT NULL 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 RESTRICT ON UPDATE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;---- Table structure for table `category`--CREATE TABLE category (  category_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,  name VARCHAR(25) NOT NULL,  last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  PRIMARY KEY  (category_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;---- Table structure for table `city`--CREATE TABLE city (  city_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,  city VARCHAR(50) NOT NULL,  country_id SMALLINT UNSIGNED NOT NULL,  last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  PRIMARY KEY  (city_id),  KEY idx_fk_country_id (country_id),  CONSTRAINT `fk_city_country` FOREIGN KEY (country_id) REFERENCES country (country_id) ON DELETE RESTRICT ON UPDATE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;---- Table structure for table `country`--CREATE TABLE country (  country_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,  country VARCHAR(50) NOT NULL,  last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  PRIMARY KEY  (country_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;---- Table structure for table `customer`--CREATE TABLE customer (  customer_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,  store_id TINYINT UNSIGNED NOT NULL,  first_name VARCHAR(45) NOT NULL,  last_name VARCHAR(45) NOT NULL,  email VARCHAR(50) DEFAULT NULL,  address_id SMALLINT UNSIGNED NOT NULL,  active BOOLEAN NOT NULL DEFAULT TRUE,  create_date DATETIME NOT NULL,  last_update TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  PRIMARY KEY  (customer_id),  KEY idx_fk_store_id (store_id),  KEY idx_fk_address_id (address_id),  KEY idx_last_name (last_name),  CONSTRAINT fk_customer_address FOREIGN KEY (address_id) REFERENCES address (address_id) ON DELETE RESTRICT ON UPDATE CASCADE,  CONSTRAINT fk_customer_store FOREIGN KEY (store_id) REFERENCES store (store_id) ON DELETE RESTRICT ON UPDATE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;---- Table structure for table `film`--CREATE TABLE film (  film_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,  title VARCHAR(128) NOT NULL,  description TEXT DEFAULT NULL,  release_year YEAR DEFAULT NULL,  language_id TINYINT UNSIGNED NOT NULL,  original_language_id TINYINT UNSIGNED DEFAULT NULL,  rental_duration TINYINT UNSIGNED NOT NULL DEFAULT 3,  rental_rate DECIMAL(4,2) NOT NULL DEFAULT 4.99,  length SMALLINT UNSIGNED DEFAULT NULL,  replacement_cost DECIMAL(5,2) NOT NULL DEFAULT 19.99,  rating ENUM('G','PG','PG-13','R','NC-17') DEFAULT 'G',  special_features SET('Trailers','Commentaries','Deleted Scenes','Behind the Scenes') DEFAULT NULL,  last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  PRIMARY KEY  (film_id),  KEY idx_title (title),  KEY idx_fk_language_id (language_id),  KEY idx_fk_original_language_id (original_language_id),  CONSTRAINT fk_film_language FOREIGN KEY (language_id) REFERENCES language (language_id) ON DELETE RESTRICT ON UPDATE CASCADE,  CONSTRAINT fk_film_language_original FOREIGN KEY (original_language_id) REFERENCES language (language_id) ON DELETE RESTRICT ON UPDATE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;---- Table structure for table `film_actor`--CREATE TABLE film_actor (  actor_id SMALLINT UNSIGNED NOT NULL,  film_id SMALLINT UNSIGNED NOT NULL,  last_update TIMESTAMP NOT NULL 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 DELETE RESTRICT ON UPDATE CASCADE,  CONSTRAINT fk_film_actor_film FOREIGN KEY (film_id) REFERENCES film (film_id) ON DELETE RESTRICT ON UPDATE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;---- Table structure for table `film_category`--CREATE TABLE film_category (  film_id SMALLINT UNSIGNED NOT NULL,  category_id TINYINT UNSIGNED NOT NULL,  last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  PRIMARY KEY (film_id, category_id),  CONSTRAINT fk_film_category_film FOREIGN KEY (film_id) REFERENCES film (film_id) ON DELETE RESTRICT ON UPDATE CASCADE,  CONSTRAINT fk_film_category_category FOREIGN KEY (category_id) REFERENCES category (category_id) ON DELETE RESTRICT ON UPDATE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;---- Table structure for table `film_text`-- -- InnoDB added FULLTEXT support in 5.6.10. If you use an-- earlier version, then consider upgrading (recommended) or -- changing InnoDB to MyISAM as the film_text engine---- Use InnoDB for film_text as of 5.6.10, MyISAM prior to 5.6.10.SET @old_default_storage_engine = @@default_storage_engine;SET @@default_storage_engine = 'MyISAM';;CREATE TABLE film_text (  film_id SMALLINT NOT NULL,  title VARCHAR(255) NOT NULL,  description TEXT,  PRIMARY KEY  (film_id),  FULLTEXT KEY idx_title_description (title,description)) DEFAULT CHARSET=utf8mb4;SET @@default_storage_engine = @old_default_storage_engine;---- Triggers for loading film_text from film--DELIMITER ;;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;;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;;CREATE TRIGGER `del_film` AFTER DELETE ON `film` FOR EACH ROW BEGIN    DELETE FROM film_text WHERE film_id = old.film_id;  END;;DELIMITER ;---- Table structure for table `inventory`--CREATE TABLE inventory (  inventory_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,  film_id SMALLINT UNSIGNED NOT NULL,  store_id TINYINT UNSIGNED NOT NULL,  last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  PRIMARY KEY  (inventory_id),  KEY idx_fk_film_id (film_id),  KEY idx_store_id_film_id (store_id,film_id),  CONSTRAINT fk_inventory_store FOREIGN KEY (store_id) REFERENCES store (store_id) ON DELETE RESTRICT ON UPDATE CASCADE,  CONSTRAINT fk_inventory_film FOREIGN KEY (film_id) REFERENCES film (film_id) ON DELETE RESTRICT ON UPDATE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;---- Table structure for table `language`--CREATE TABLE language (  language_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,  name CHAR(20) NOT NULL,  last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  PRIMARY KEY (language_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;---- Table structure for table `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),  CONSTRAINT fk_payment_rental FOREIGN KEY (rental_id) REFERENCES rental (rental_id) ON DELETE SET NULL ON UPDATE CASCADE,  CONSTRAINT fk_payment_customer FOREIGN KEY (customer_id) REFERENCES customer (customer_id) ON DELETE RESTRICT ON UPDATE CASCADE,  CONSTRAINT fk_payment_staff FOREIGN KEY (staff_id) REFERENCES staff (staff_id) ON DELETE RESTRICT ON UPDATE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;---- Table structure for table `rental`--CREATE TABLE rental (  rental_id INT NOT NULL AUTO_INCREMENT,  rental_date DATETIME NOT NULL,  inventory_id MEDIUMINT UNSIGNED NOT NULL,  customer_id SMALLINT UNSIGNED NOT NULL,  return_date DATETIME DEFAULT NULL,  staff_id TINYINT UNSIGNED NOT NULL,  last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  PRIMARY KEY (rental_id),  UNIQUE KEY  (rental_date,inventory_id,customer_id),  KEY idx_fk_inventory_id (inventory_id),  KEY idx_fk_customer_id (customer_id),  KEY idx_fk_staff_id (staff_id),  CONSTRAINT fk_rental_staff FOREIGN KEY (staff_id) REFERENCES staff (staff_id) ON DELETE RESTRICT ON UPDATE CASCADE,  CONSTRAINT fk_rental_inventory FOREIGN KEY (inventory_id) REFERENCES inventory (inventory_id) ON DELETE RESTRICT ON UPDATE CASCADE,  CONSTRAINT fk_rental_customer FOREIGN KEY (customer_id) REFERENCES customer (customer_id) ON DELETE RESTRICT ON UPDATE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;---- Table structure for table `staff`--CREATE TABLE staff (  staff_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,  first_name VARCHAR(45) NOT NULL,  last_name VARCHAR(45) NOT NULL,  address_id SMALLINT UNSIGNED NOT NULL,  picture BLOB DEFAULT NULL,  email VARCHAR(50) DEFAULT NULL,  store_id TINYINT UNSIGNED NOT NULL,  active BOOLEAN NOT NULL DEFAULT TRUE,  username VARCHAR(16) NOT NULL,  password VARCHAR(40) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,  last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  PRIMARY KEY  (staff_id),  KEY idx_fk_store_id (store_id),  KEY idx_fk_address_id (address_id),  CONSTRAINT fk_staff_store FOREIGN KEY (store_id) REFERENCES store (store_id) ON DELETE RESTRICT ON UPDATE CASCADE,  CONSTRAINT fk_staff_address FOREIGN KEY (address_id) REFERENCES address (address_id) ON DELETE RESTRICT ON UPDATE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;---- Table structure for table `store`--CREATE TABLE store (  store_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,  manager_staff_id TINYINT UNSIGNED NOT NULL,  address_id SMALLINT UNSIGNED NOT NULL,  last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  PRIMARY KEY  (store_id),  UNIQUE KEY idx_unique_manager (manager_staff_id),  KEY idx_fk_address_id (address_id),  CONSTRAINT fk_store_staff FOREIGN KEY (manager_staff_id) REFERENCES staff (staff_id) ON DELETE RESTRICT ON UPDATE CASCADE,  CONSTRAINT fk_store_address FOREIGN KEY (address_id) REFERENCES address (address_id) ON DELETE RESTRICT ON UPDATE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;---- View structure for view `customer_list`--CREATE VIEW customer_listASSELECT cu.customer_id AS ID, CONCAT(cu.first_name, _utf8mb4' ', 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, _utf8mb4'active',_utf8mb4'') AS notes, cu.store_id AS SIDFROM customer AS cu JOIN address AS a ON cu.address_id = a.address_id JOIN city ON a.city_id = city.city_idJOIN country ON city.country_id = country.country_id;---- View structure for view `film_list`--CREATE VIEW film_listASSELECT 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, _utf8mb4' ', actor.last_name) SEPARATOR ', ') AS actorsFROM film LEFT JOIN film_category ON film_category.film_id = film.film_idLEFT JOIN category ON category.category_id = film_category.category_id LEFTJOIN film_actor ON film.film_id = film_actor.film_id LEFT JOIN actor ON  film_actor.actor_id = actor.actor_idGROUP BY film.film_id, category.name;---- View structure for view `nicer_but_slower_film_list`--CREATE VIEW nicer_but_slower_film_listASSELECT 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))),_utf8mb4' ',CONCAT(UCASE(SUBSTR(actor.last_name,1,1)),LCASE(SUBSTR(actor.last_name,2,LENGTH(actor.last_name)))))) SEPARATOR ', ') AS actorsFROM film LEFT JOIN film_category ON film_category.film_id = film.film_idLEFT JOIN category ON category.category_id = film_category.category_id LEFTJOIN film_actor ON film.film_id = film_actor.film_id LEFT JOIN actor ON  film_actor.actor_id = actor.actor_idGROUP BY film.film_id, category.name;---- View structure for view `staff_list`--CREATE VIEW staff_listASSELECT s.staff_id AS ID, CONCAT(s.first_name, _utf8mb4' ', 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 SIDFROM staff AS s JOIN address AS a ON s.address_id = a.address_id JOIN city ON a.city_id = city.city_idJOIN country ON city.country_id = country.country_id;---- View structure for view `sales_by_store`--CREATE VIEW sales_by_storeASSELECTCONCAT(c.city, _utf8mb4',', cy.country) AS store, CONCAT(m.first_name, _utf8mb4' ', m.last_name) AS manager, SUM(p.amount) AS total_salesFROM payment AS pINNER JOIN rental AS r ON p.rental_id = r.rental_idINNER JOIN inventory AS i ON r.inventory_id = i.inventory_idINNER JOIN store AS s ON i.store_id = s.store_idINNER JOIN address AS a ON s.address_id = a.address_idINNER JOIN city AS c ON a.city_id = c.city_idINNER JOIN country AS cy ON c.country_id = cy.country_idINNER JOIN staff AS m ON s.manager_staff_id = m.staff_idGROUP BY s.store_idORDER BY cy.country, c.city;---- View structure for view `sales_by_film_category`---- Note that total sales will add up to >100% because-- some titles belong to more than 1 category--CREATE VIEW sales_by_film_categoryASSELECTc.name AS category, SUM(p.amount) AS total_salesFROM payment AS pINNER JOIN rental AS r ON p.rental_id = r.rental_idINNER JOIN inventory AS i ON r.inventory_id = i.inventory_idINNER JOIN film AS f ON i.film_id = f.film_idINNER JOIN film_category AS fc ON f.film_id = fc.film_idINNER JOIN category AS c ON fc.category_id = c.category_idGROUP BY c.nameORDER BY total_sales DESC;---- View structure for view `actor_info`--CREATE DEFINER=CURRENT_USER SQL SECURITY INVOKER VIEW actor_infoASSELECTa.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_infoFROM sakila.actor aLEFT JOIN sakila.film_actor fa  ON a.actor_id = fa.actor_idLEFT JOIN sakila.film_category fc  ON fa.film_id = fc.film_idLEFT JOIN sakila.category c  ON fc.category_id = c.category_idGROUP BY a.actor_id, a.first_name, a.last_name;---- Procedure structure for procedure `rewards_report`--DELIMITER //CREATE PROCEDURE rewards_report (    IN min_monthly_purchases TINYINT UNSIGNED    , IN min_dollar_amount_purchased DECIMAL(10,2)    , OUT count_rewardees INT)LANGUAGE SQLNOT DETERMINISTICREADS SQL DATASQL SECURITY DEFINERCOMMENT 'Provides a customizable report on best customers'proc: BEGIN    DECLARE last_month_start DATE;    DECLARE last_month_end DATE;        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;        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 TEMPORARY TABLE tmpCustomer (customer_id SMALLINT UNSIGNED NOT NULL PRIMARY KEY);        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;        SELECT COUNT(*) FROM tmpCustomer INTO count_rewardees;        SELECT c.*    FROM tmpCustomer AS t    INNER JOIN customer AS c ON t.customer_id = c.customer_id;        DROP TABLE tmpCustomer;END //DELIMITER ;DELIMITER $$CREATE FUNCTION get_customer_balance(p_customer_id INT, p_effective_date DATETIME) RETURNS DECIMAL(5,2)    DETERMINISTIC    READS SQL DATABEGIN       #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 $$DELIMITER ;DELIMITER $$CREATE PROCEDURE film_in_stock(IN p_film_id INT, IN p_store_id INT, OUT p_film_count INT)READS SQL DATABEGIN     SELECT inventory_id     FROM inventory     WHERE film_id = p_film_id     AND store_id = p_store_id     AND inventory_in_stock(inventory_id);     SELECT COUNT(*)     FROM inventory     WHERE film_id = p_film_id     AND store_id = p_store_id     AND inventory_in_stock(inventory_id)     INTO p_film_count;END $$DELIMITER ;DELIMITER $$CREATE PROCEDURE film_not_in_stock(IN p_film_id INT, IN p_store_id INT, OUT p_film_count INT)READS SQL DATABEGIN     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 COUNT(*)     FROM inventory     WHERE film_id = p_film_id     AND store_id = p_store_id     AND NOT inventory_in_stock(inventory_id)     INTO p_film_count;END $$DELIMITER ;DELIMITER $$CREATE FUNCTION inventory_held_by_customer(p_inventory_id INT) RETURNS INTREADS SQL DATABEGIN  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 $$DELIMITER ;DELIMITER $$CREATE FUNCTION inventory_in_stock(p_inventory_id INT) RETURNS BOOLEANREADS SQL DATABEGIN    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 $$DELIMITER ;SET SQL_MODE=@OLD_SQL_MODE;SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;