CƠ SỞ DỮ LIỆU MYSQL

      60

There are many excellent và interesting sample databases available, that you can use as a template (or pattern) khổng lồ design your own databases.

Bạn đang xem: Cơ sở dữ liệu mysql

MySQL"s Sample Employee Database

Reference: MySQL"s Sample Employees Database
http://dev.mysql.com/doc/employee/en/index.html.

This is a rather simple database with 6 tables but with millions of records.

Database & Tables

There are 6 tables as follows:

*
Table "employees"

CREATE TABLE employees ( emp_no INT NOT NULL, -- UNSIGNED AUTO_INCREMENT?? birth_date DATE NOT NULL, first_name VARCHAR(14) NOT NULL, last_name VARCHAR(16) NOT NULL, gender ENUM ("M","F") NOT NULL, -- Enumeration of either "M" or "F" hire_date DATE NOT NULL, PRIMARY KEY (emp_no) -- Index built automatically on primary-key column -- INDEX (first_name) -- INDEX (last_name));There are 300,024 records for this table.

Table "departments"

CREATE TABLE departments ( dept_no CHAR(4) NOT NULL, -- in the size of "dxxx" dept_name VARCHAR(40) NOT NULL, PRIMARY KEY (dept_no), -- Index built automatically UNIQUE KEY (dept_name) -- Build INDEX on this unique-value column);The keyword KEY is synonym lớn INDEX. An INDEX can be built on unique-value column (UNIQUE KEY or UNIQUE INDEX) or non-unique-value column (KEY or INDEX). Indexes greatly facilitates fast search. However, they deplete the performance in INSERT, UPDATE and DELETE. Generally, relational databases are optimized for retrievals, và NOT for modifications.

There are 9 records for this table.

Table "dept_emp"

Junction table to lớn support between many-to-many relationship between employees and departments. A department has many employees. An employee can belong to lớn different department at different dates, và possibly concurrently.

CREATE TABLE dept_emp ( emp_no INT NOT NULL, dept_no CHAR(4) NOT NULL, from_date DATE NOT NULL, to_date DATE NOT NULL, KEY (emp_no), -- Build INDEX on this non-unique-value column KEY (dept_no), -- Build INDEX on this non-unique-value column FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE, -- Cascade DELETE from parent table "employee" to this child table -- If an emp_no is deleted from parent "employee", all records -- involving this emp_no in this child table are also deleted -- ON UPDATE CASCADE?? FOREIGN KEY (dept_no) REFERENCES departments (dept_no) ON DELETE CASCADE, -- ON UPDATE CASCADE?? PRIMARY KEY (emp_no, dept_no) -- Might not be unique?? Need to include from_date);The foreign keys have ON DELETE reference action of CASCADE. If a record having a particular key-value from the parent table (employees & departments) is deleted, all the records in this child table having the same key-value are also deleted. Take note that the mặc định ON DELETE reference action of is RESTRICTED, which disallows DELETE on the parent record, if there are matching records in the child table.

There are two reference actions: ON DELETE và ON UPDATE. The ON UPDATE reference action of is defaulted lớn RESTRICT (or disallow). It is more meaningful to set ON UPDATE to lớn CASCADE, so that changes in parent table (e.g., change in emp_no & dept_no) can be cascaded down to the child table(s).

There are 331,603 records for this table.

Table "dept_manager"

join table lớn tư vấn between many-to-many relationship between employees và departments. Same structure as dept_emp.

CREATE TABLE dept_manager ( dept_no CHAR(4) NOT NULL, emp_no INT NOT NULL, from_date DATE NOT NULL, to_date DATE NOT NULL, KEY (emp_no), KEY (dept_no), FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE, -- ON UPDATE CASCADE?? FOREIGN KEY (dept_no) REFERENCES departments (dept_no) ON DELETE CASCADE, PRIMARY KEY (emp_no, dept_no) -- might not be unique?? Need from_date);There are 24 records for this table.

Table "titles"

There is a one-to-many relationship between employees & titles. One employee has many titles (concurrently or at different dates). A titles record refers to lớn one employee (via emp_no).

CREATE TABLE titles ( emp_no INT NOT NULL, title VARCHAR(50) NOT NULL, from_date DATE NOT NULL, to_date DATE, KEY (emp_no), FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE, -- ON UPDATE CASCADE?? PRIMARY KEY (emp_no, title, from_date) -- This ensures unique combination. -- An employee may hold the same title but at different period);There are 443,308 records for this table.

Table "salaries"

Similar structure khổng lồ titles table. One-to-many relationship between employees và salaries.

CREATE TABLE salaries ( emp_no INT NOT NULL, salary INT NOT NULL, from_date DATE NOT NULL, to_date DATE NOT NULL, KEY (emp_no), FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE, PRIMARY KEY (emp_no, from_date));There are 2,844,047 records for this table.

Stored Objects

No stored objects (view, procedure, function, trigger, event) defined.

MySQL"s Sample Salika (DVD Rental) Database

Reference: MySQL"s Sample Sakila Database
http://dev.mysql.com/doc/sakila/en/index.html.

The MySQL"s Sample Salika (DVD Rental) Database can be downloaded fromhttp://dev.mysql.com/doc/sakila/en/index.html. It is a complex database with 16 tables. It also illustrates features such as Views, Stored Procedures và Triggers. This is probably the best sample available for studying MySQL databases.

*
Database và Tables

All the tables have DEFAULT CHARSET of utf8 for internationalization support. All the tables, except film_text, use InnoDB engine, which supports foreign key and transaction. The table film_text uses MyISAM to support FULLTEXT search.

For UTF8 support, we could phối the DEFAULT CHARSET at the database cấp độ as follows:

-- Enable client program to communicate with the server using utf8 character setSET NAMES "utf8";DROPhường DATABASE IF EXISTS `sakila`;-- Set the mặc định charmix khổng lồ utf8 for internationalization, use case-insensitive sầu (ci) collationCREATE DATABASE IF NOT EXISTS `sakila` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;USE `sakila`;We could use "SHOW CREATE DATABASE databaseName G" and "SHOW CREATE TABLE tableName G" lớn display all the defaults used in CREATE DATABASE and CREATE TABLE.

Table "actor"

CREATE TABLE actor ( actor_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, -- 16-bit unsigned int in the range of <0, 65535> first_name VARCHAR(45) NOT NULL, last_name VARCHAR(45) NOT NULL, last_update TIMESTAMPhường NOT NULL DEFAULT CURRENT_TIMESTAMP. ON UPDATE CURRENT_TIMESTAMP., PRIMARY KEY (actor_id), KEY idx_actor_last_name (last_name) -- To build index (non-unique) on last_name) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- Use InnoDB Engine, which supports foreign key and transaction -- Use Unicode "utf8" character set for this tableThere can be one TIMESTAMPhường column with DEFAULT CURRENT_TIMESTAMP.. If you wish to have both create & last_update, you need khổng lồ use a ON INSERT trigger khổng lồ mix the create TIMESTAMPhường. For strict auditing, you might have create_timestamp, create_by, last_update_timestamp and last_update_by.InnoDB engine is used, which support foreign key và transaction.The mặc định character phối for this table is UTF8, which supports all languages for internationalization.Better lớn use INT UNSIGNED for AUTO_INCREMENT column actor_id khổng lồ avoid overrun.There are 200 records for this table.

Table "language"

Languages: such as English, Italian, Japanese, Mandrain, Cantonese, French, German.

CREATE TABLE language ( language_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT, -- 8-bit unsigned int <0, 255> name CHAR(20) NOT NULL, last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMPhường ON UPDATE CURRENT_TIMESTAMP., PRIMARY KEY (language_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8;May be simpler lớn use an ENUM (one choice).

There are 6 records for this table, i.e., "English", "Italian", "Japanese", "Mandarin", "French", "German".

Xem thêm: 5 Bí Quyết Làm Giàu Nhanh Và Đơn Giản Nhất Mà Bạn Cần Biết, 'Bí Quyết Duy Nhất' Để Làm Giàu

Table "film"

CREATE TABLE film ( film_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, title VARCHAR(255) NOT NULL, description TEXT DEFAULT NULL, -- Up khổng lồ 64KB release_year YEAR DEFAULT NULL, -- "yyyy" language_id TINYINT UNSIGNED NOT NULL, -- 8-bit unsigned int <0, 255> 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, -- DECIMAL is precise and igiảm giá for currency <99.99>. UNSIGNED? length SMALLINT UNSIGNED DEFAULT NULL, -- 16-bit unsigned int <0, 65535> replacement_cost DECIMAL(5,2) NOT NULL DEFAULT 19.99, -- <999.99>, UNSIGNED?? rating ENUM("G","PG","PG-13","R","NC-17") DEFAULT "G", special_features SET("Trailers","Commentaries","Deleted Scenes","Behind the Scenes") DEFAULT NULL, -- Can take zero or more values from a SET -- But only one value from ENUM last_update TIMESTAMP.. NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMPhường., PRIMARY KEY (film_id), KEY idx_title (title), KEY idx_fk_language_id (language_id), KEY idx_fk_original_language_id (original_language_id), -- To build index on title, language_id, original_language_id và film_id (primary key) CONSTRAINT fk_film_language FOREIGN KEY (language_id) REFERENCES language (language_id) ON DELETE RESTRICT ON UPDATE CASCADE, -- Cannot delete parent record if there is any matching child record -- Update the matching child records if parent record is updated CONSTRAINT fk_film_language_original FOREIGN KEY (original_language_id) REFERENCES language (language_id) ON DELETE RESTRICT ON UPDATE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8;Instead of hard-coding the "language" & "original language", it uses language_id to look up the language table, in a one-to-one relationship. Could use an ENUM for language directly for simplithành phố.KEYs (INDEXes) are defined on certain columns to facilitate fast tìm kiếm on these columns. We would use "SHOW INDEX FROM tableName G" to display the details on indexes.Should include UNSIGNED for for non-negative numeric columns lượt thích rental_rate.There are 1000 records for this table.

Table "film_actor"

Junction table between actor & film to support the many-to-many relationship.

CREATE TABLE film_actor ( actor_id SMALLINT UNSIGNED NOT NULL, film_id SMALLINT UNSIGNED NOT NULL, last_update TIMESTAMPhường 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=utf8;There are 5462 records for this table.

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=utf8;There are 16 records for this table, i.e., "Action", "Animation", "Children", "Classics", "Comedy", "Documentary", "Drama", "Family", "Foreign", "Games", "Horror", "Music", "New", "Sci-Fi", "Sports", "Travel".May be better to lớn use a SET khổng lồ support multiple categories per film, if the number of categories is small. A SET is limited khổng lồ 64 items in MySquốc lộ.Table "film_category"Junction table to support many-to-many relationship between 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=utf8;There are 1000 records for this table. Each of the 1000 films has ONE category.

Table "film_text" - FULLTEXT Index & Search

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) -- To build index on FULLTEXT khổng lồ facilitate text tìm kiếm -- FULLTEXT is supported in MyISAM engine, NOT in InnoDB engine) ENGINE=MyISAM DEFAULT CHARSET=utf8;This table duplicates information from film table, lớn support FULLTEXT tìm kiếm. That is, user can efficiently search all the words in title & description columns.To ensure consistency between film_text & film, the rows are inserted/updated via a trigger on film table.FULLTEXT tìm kiếm is supported in MyISAM engine only, not the InnoDB engine. A FULLTEXT index is build on columns (title, description). You can persize FULLTEXT tìm kiếm on the index using "WHERE MATCH(columns) AGAINST(words)", for example,mysql> SELECT * FROM film_text WHERE MATCH(title, description) AGAINST ("great"); -- tìm kiếm for the given word on the FULLTEXT index columns mysql> SELECT * FROM film_text WHERE MATCH(title, description) AGAINST ("great good"); -- search for either "great" or "good" mysql> SELECT * FROM film_text WHERE MATCH(title, description) AGAINST (""very good"" IN BOOLEAN MODE); -- Use BOOLEAN MODE to match exact phrase (enclosed in double-quotes) mysql> SELECT * FROM film_text WHERE MATCH(title, description) AGAINST ("+good -bad" IN BOOLEAN MODE); -- Use BOOLEAN MODE lớn search for the word "good", but NOT the word "bad" mysql> SELECT * FROM film_text WHERE MATCH(title, description) AGAINST ("great*" IN BOOLEAN MODE); -- In BOOLEAN MODE, wildcard * matches zero or more characters mysql> SELECT * FROM film_text WHERE MATCH(title, description) AGAINST ("great" WITH QUERY EXPANSION); -- Do a second search on words in the most relevant rows from the first searchThere are 1000 records for this table. Each film record has a film_text counterpart. The records in the film_text table is created via a INSERT trigger on the film table.

Table "inventory"

The company could have many copies of a particular film (in one store or many stores). Each copy is represented by an inventory record. The store is linked thru store_id to lớn the table store.

CREATE TABLE inventory ( inventory_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT, -- Simpler to lớn use INT UNSIGNED film_id SMALLINT UNSIGNED NOT NULL, store_id TINYINT UNSIGNED NOT NULL, last_update TIMESTAMPhường. 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=utf8;There are 4581 records for this table.

Table "store"

Each store has a manager, linked thru manager_staff_id lớn the staff table. The address of the store is also linked thru address_id to the address table.

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_TIMESTAMPhường ON UPDATE CURRENT_TIMESTAMPhường, PRIMARY KEY (store_id), UNIQUE KEY idx_unique_manager (manager_staff_id), -- one manager manages only one store 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=utf8;There are 2 records for this table.

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, -- Kept a picture as BLOB (up lớn 64KB) gmail VARCHAR(50) DEFAULT NULL, store_id TINYINT UNSIGNED NOT NULL, active BOOLEAN NOT NULL DEFAULT TRUE, -- BOOLEAN FALSE (0) TRUE (non-0) username VARCHAR(16) NOT NULL, password VARCHAR(40) BINARY DEFAULT NULL, -- BINARY?? last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMPhường ON UPDATE CURRENT_TIMESTAMPhường, 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=utf8;There are 2 records for this table, with pictures (BLOB) provided.

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 sầu BOOLEAN NOT NULL DEFAULT TRUE, create_date DATETIME NOT NULL, last_update TIMESTAMPhường. 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=utf8;There are 599 records for this table.

Table "rental"

Rental rate is kept in the film table.

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 TIMESTAMPhường 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=utf8;There are 16,044 records for this table.

Table "payment"

An rental can have sầu multiple payments?

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 TIMESTAMPhường 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=utf8;There are 16,049 records for this table, more than rental table.

Table "address"

It is unlikely that two persons chia sẻ the same address. Address is often a required field for a rental transaction. So it is probably better khổng lồ store directly inside the customers table.

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 TIMESTAMPhường. NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMPhường, PRIMARY KEY (address_id), KEY idx_fk_city_id (city_id), CONSTRAINT `fk_address_city` FOREIGN KEY (city_id) REFERENCES đô thị (city_id) ON DELETE RESTRICT ON UPDATE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8;There are 603 records for this table.

Table "city"

CREATE TABLE city ( city_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, thành phố VARCHAR(50) NOT NULL, country_id SMALLINT UNSIGNED NOT NULL, last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMPhường ON UPDATE CURRENT_TIMESTAMPhường, 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=utf8;There are 600 records for this table.

Table "country"

Having a country table may facilitate the creation of pull-down thực đơn. Alternatively, you could consider using an ENUM (number of countries may exceed ENUM"s limit). For city, there are just too many cities in the world that the menu can never be exhaustive sầu. Probably better lớn keep inside the address table.

CREATE TABLE country ( country_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, country VARCHAR(50) NOT NULL, last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMPhường ON UPDATE CURRENT_TIMESTAMPhường., PRIMARY KEY (country_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8;There are 109 records for this table.

Views

A VIEW is a virtual table (without data) that provides an alternate way to look at the data. It could be a consolidated set of columns from multiple table, or include derived column (such as total price).

We could use "SHOW CREATE VIEW viewName G" lớn show all the defaults.

View "staff_list"

CREATE VIEW staff_listASSELECT 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, thành phố.thành phố 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 thành phố ON a.city_id = city.city_id JOIN country ON city.country_id = country.country_id;String literal can be expressed with optional introducer & collation in the form of:-- Syntax<_charsetName>"stringLiteral" collationName> -- ExampleSELECT _utf8" "; -- space in UTF8For Example,

mysql> SELECT * FROM staff_list;+----+--------------+----------------------+----------+-------------+------------+-----------+-----+| ID | name | address | zip code | phone | thành phố | country | SID |+----+--------------+----------------------+----------+-------------+------------+-----------+-----+| 1 | Mike Hillyer | 23 Workhaven Lane | | 14033335568 | Lethbridge | Canadomain authority | 1 || 2 | Jon Stephens | 1411 Lillydale Drive sầu | | 6172235589 | Woodridge | Australia | 2 |+----+--------------+----------------------+----------+-------------+------------+-----------+-----+View "customer_list"CREATE VIEW customer_listASSELECT 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.thành phố AS city, country.country AS country, IF(cu.active, _utf8"active", _utf8"") 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 = thành phố.city_id JOIN country ON city.country_id = country.country_id;View "film_list"CREATE VIEW film_listASSELECT film.film_id AS FID, film.title AS title, film.mô tả tìm kiếm AS mô tả tìm kiếm, 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 actorsFROM 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_idGROUPhường BY film.film_id;The GROUP_CONCAT(col SEPARATOR str) GROUP BY aggregate function can be used lớn produce a concatenate string for each group returned by the GROUP BY clause. Each film_id (in GROUPhường BY) has many actors.For example,mysql> SELECT * FROM film_danh sách LIMIT 1 G*************************** 1. row *************************** FID: 1 title: ACADEMY DINOSAURdescription: A Epic Drama of a Feminist And a Mad Scientist who must Battle a Teacher in The Canadian Rockies category: Documentary price: 0.99 length: 86 rating: PG actors: PENELOPE GUINESS, CHRISTIAN GABLE, LUCILLE TRACY, SANDRA PECK, JOHNNY CAGE, MENA TEMPLE, WARREN NOLTE, OPRAH KILMER, ROCK DUKAKIS, MARY KEITELView "nicer_but_slower_film_list"CREATE VIEW nicer_but_slower_film_listASSELECT film.film_id AS FID, film.title AS title, film.description AS mô tả tìm kiếm, 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)), -- first_name initial-cap LCASE(SUBSTR(actor.first_name, 2, LENGTH(actor.first_name))), _utf8" ", -- space CONCAT(UCASE(SUBSTR(actor.last_name, 1, 1)), -- last_name initial-cap LCASE(SUBSTR(actor.last_name, 2, LENGTH(actor.last_name)))))) -- end of outer CONCAT SEPARATOR ", ") AS actorsFROM 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_idGROUPhường BY film.film_id;The complex CONCAT() is used to lớn produce camel-case (initial-capitalized) for the first_name & last_name, e.g., "Penelope Guiness".LENGTH(str) returns the length of the string.SUBSTR(str, fromIndex, length) returns the substring from index of length (index begins at 1).UCASE(str) và LCASE(str) returns the uppercase và lowercase.This view is exactly the same as film_danh mục view. Why is it called nicer_but_slower_film_list?View "sales_by_store"CREATE VIEW sales_by_storeASSELECT CONCAT(c.city, _utf8",", cy.country) AS store, CONCAT(m.first_name, _utf8" ", m.last_name) AS manager, SUM(p.amount) AS total_salesFROM 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 store AS s ON i.store_id = s.store_id INNER JOIN address AS a ON s.address_id = a.address_id INNER JOIN city AS c ON a.city_id = c.city_id INNER JOIN country AS cy ON c.country_id = cy.country_id INNER JOIN staff AS m ON s.manager_staff_id = m.staff_idGROUP BY s.store_idORDER BY cy.country, c.city;The SUM() GROUP.. BY aggregate function applies lớn each group of store_id, i.e., per store.

For example,

+---------------------+--------------+-------------+| store | manager | total_sales |+---------------------+--------------+-------------+| Woodridge,Australia | Jon Stephens | 33726.77 || Lethbridge,Canadomain authority | Mike Hillyer | 33679.79 |+---------------------+--------------+-------------+View "sales_by_film_category"CREATE VIEW sales_by_film_categoryASSELECT c.name AS category, SUM(p.amount) AS total_salesFROM 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_idGROUP BY c.nameORDER BY total_sales DESC;The GROUP.. BY aggregate function SUM() applies to each group of c.name, i.e., per category"s name.

View "actor_info"

CREATE DEFINER=CURRENT_USER SQL SECURITY INVOKER VIEW actor_infoASSELECT 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) ) -- end CONCAT 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_idGROUPhường BY a.actor_id, a.first_name, a.last_name;Squốc lộ SECURITY INVOKER specifies that the it executes with the privileges of the user who invoke it (instead of the DEFINER).GROUP_CONCAT( col ): You can apply optional DISTINCT và ORDER BY lớn GROUP_CONCAT().For example,mysql> SELECT * FROM actor_info LIMIT 1 G*************************** 1. row *************************** actor_id: 1first_name: PENELOPE last_name: GUINESS film_info: Animation: ANACONDA CONFESSIONS; Children: LANGUAGE COWBOY; Classics: COLOR PHILADELPHIA, WESTWARD SEABISCUIT; ......Stored Routines: Procedures and FunctionsProcedure "rewards_report"-- Change the MySquốc lộ statement delimiter lớn // as it crashes with procedure"s delimiter ";"DELIMITER // CREATE PROCEDURE rewards_report ( IN min_monthly_purchases TINYINT UNSIGNED, -- min number of purchases IN min_dollar_amount_purchased DECIMAL(10,2) UNSIGNED, -- min dollar amount purchased OUT count_rewardees INT -- number of customers khổng lồ be rewarded)LANGUAGE SQLNOT DETERMINISTICREADS SQL DATASQL SECURITY DEFINERCOMMENT "Provides a customizable report on best customers" proc: BEGIN DECLARE last_month_start DATE; DECLARE last_month_over DATE; /* Some sanity checks... */ 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; /* Determine start and kết thúc time periods */ 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_kết thúc = LAST_DAY(last_month_start); /* Create a temporary storage area for Customer IDs */ CREATE TEMPORARY TABLE tmpCustomer (customer_id SMALLINT UNSIGNED NOT NULL PRIMARY KEY); /* Find all customers meeting the monthly purchase requirements */ 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_over GROUPhường. BY customer_id HAVING SUM(p.amount) > min_dollar_amount_purchased AND COUNT(customer_id) > min_monthly_purchases; /* Populate OUT parameter with count of found customers */ SELECT COUNT(*) FROM tmpCustomer INTO count_rewardees; /* Output ALL customer information of matching rewardees. Customize output as needed. */ SELECT c.* FROM tmpCustomer AS t INNER JOIN customer AS c ON t.customer_id = c.customer_id; /* Clean up */ DROPhường TABLE tmpCustomer;END // -- Change the MySQL delimiter baông xã to ";"DELIMITER ;To demo the procedure,