The following sections describe the stored functions included with the Sakila sample database. name AS category, SUM( p.While these operations are good candidates for stored procedures and views, such implementation is intentionally left as an exercise to the user. city ĬREATE VIEW sales_by_film_category AS SELECT c. rental_id INNER JOIN inventory AS i ON r. country_id ĬREATE VIEW sales_by_store AS SELECT CONCAT( c. store_id AS SIDįROM staff AS s JOIN address AS a ON s. category_id LEFT JOIN film ON film_category. GROUP_CONCAT(CONCAT(CONCAT(UCASE(SUBSTR( actor. name ĬREATE VIEW nicer_but_slower_film_list AS SELECT film. last_name, ', ')) AS actorsįROM category LEFT JOIN film_category ON category. country_id ĬREATE VIEW film_list AS SELECT film. store_id AS SIDįROM customer AS cu JOIN address AS a ON cu. Manager_staff_id TINYINT UNSIGNED NOT NULL,ĬONSTRAINT fk_store_staff FOREIGN KEY (manager_staff_id)ĬONSTRAINT fk_store_address FOREIGN KEY (address_id)ĬREATE VIEW customer_list AS SELECT cu. Store_id INTEGER PRIMARY KEY AUTOINCREMENT,
#SAKILA DATABASE MYSQL DOWNLOAD UPDATE#
REFERENCES address (address_id) ON DELETE RESTRICT ON UPDATE CASCADE Staff_id INTEGER PRIMARY KEY AUTOINCREMENT,ĬONSTRAINT fk_staff_store FOREIGN KEY (store_id)ĬONSTRAINT fk_staff_address FOREIGN KEY (address_id) REFERENCES customer (customer_id) ON DELETE RESTRICT ON UPDATE CASCADE REFERENCES inventory (inventory_id) ON DELETE RESTRICT ON UPDATE CASCADE,ĬONSTRAINT fk_rental_customer FOREIGN KEY (customer_id) REFERENCES staff (staff_id) ON DELETE RESTRICT ON UPDATE CASCADE,ĬONSTRAINT fk_rental_inventory FOREIGN KEY (inventory_id) Inventory_id MEDIUMINT UNSIGNED NOT NULL,ĬONSTRAINT fk_rental_staff FOREIGN KEY (staff_id) Rental_id INTEGER PRIMARY KEY AUTOINCREMENT, REFERENCES staff (staff_id) ON DELETE RESTRICT ON UPDATE CASCADE REFERENCES customer (customer_id) ON DELETE RESTRICT ON UPDATE CASCADE,ĬONSTRAINT fk_payment_staff FOREIGN KEY (staff_id) REFERENCES rental (rental_id) ON DELETE SET NULL ON UPDATE CASCADE,ĬONSTRAINT fk_payment_customer FOREIGN KEY (customer_id) Payment_id INTEGER PRIMARY KEY AUTOINCREMENT,ĬONSTRAINT fk_payment_rental FOREIGN KEY (rental_id) Language_id INTEGER PRIMARY KEY AUTOINCREMENT, REFERENCES store (store_id) ON DELETE RESTRICT ON UPDATE CASCADE,ĬONSTRAINT fk_inventory_film FOREIGN KEY (film_id) Inventory_id INTEGER PRIMARY KEY AUTOINCREMENT,ĬONSTRAINT fk_inventory_store FOREIGN KEY (store_id) film_id ĬREATE TRIGGER ` del_film` AFTER DELETE ON `film ` FOR EACH ROW BEGIN DELETE FROM film_text WHERE film_id = old. description) ĬREATE TRIGGER ` upd_film` AFTER UPDATE ON `film ` FOR EACH ROW REFERENCES category (category_id) ON DELETE RESTRICT ON UPDATE CASCADEĬREATE TRIGGER ` ins_film` AFTER INSERT ON `film ` FOR EACH ROW BEGIN INSERT INTO film_text (film_id, title, description) REFERENCES film (film_id) ON DELETE RESTRICT ON UPDATE CASCADE,ĬONSTRAINT fk_film_category_category FOREIGN KEY (category_id) REFERENCES film (film_id) ON DELETE RESTRICT ON UPDATE CASCADEĬONSTRAINT fk_film_category_film FOREIGN KEY (film_id) REFERENCES actor (actor_id) ON DELETE RESTRICT ON UPDATE CASCADE,ĬONSTRAINT fk_film_actor_film FOREIGN KEY (film_id)
REFERENCES language (language_id) ON DELETE RESTRICT ON UPDATE CASCADEĬONSTRAINT fk_film_actor_actor FOREIGN KEY (actor_id) REFERENCES language (language_id) ON DELETE RESTRICT ON UPDATE CASCADE,ĬONSTRAINT fk_film_language_original FOREIGN KEY (original_language_id) 99,ĬONSTRAINT fk_film_language FOREIGN KEY (language_id) Replacement_cost DECIMAL( 5, 2) NOT NULL DEFAULT 19. Rental_rate DECIMAL( 4, 2) NOT NULL DEFAULT 4.
Rental_duration TINYINT UNSIGNED NOT NULL DEFAULT 3, Original_language_id TINYINT UNSIGNED DEFAULT NULL,
REFERENCES store (store_id) ON DELETE RESTRICT ON UPDATE CASCADEįilm_id INTEGER PRIMARY KEY AUTOINCREMENT, REFERENCES address (address_id) ON DELETE RESTRICT ON UPDATE CASCADE,ĬONSTRAINT fk_customer_store FOREIGN KEY (store_id) REFERENCES country (country_id) ON DELETE RESTRICT ON UPDATE CASCADEĬountry_id INTEGER PRIMARY KEY AUTOINCREMENT,Ĭustomer_id INTEGER PRIMARY KEY AUTOINCREMENT,ĬONSTRAINT fk_customer_address FOREIGN KEY (address_id) REFERENCES city (city_id) ON DELETE RESTRICT ON UPDATE CASCADEĬategory_id INTEGER PRIMARY KEY AUTOINCREMENT,Ĭity_id INTEGER PRIMARY KEY AUTOINCREMENT,ĬONSTRAINT `fk_city_country ` FOREIGN KEY (country_id) Actor_id INTEGER PRIMARY KEY AUTOINCREMENT,Īddress_id INTEGER PRIMARY KEY AUTOINCREMENT,ĬONSTRAINT `fk_address_city ` FOREIGN KEY (city_id)
0 kommentar(er)
