SQL - создание, изменение и удаление VIEWS - виртуальных таблиц

Примеры использования:

PRIMARY KEY
FOREIGN KEY
CREATE VIEW
CREATE OR REPLACE VIEW
DROP VIEW
и создание пользователя и права на таблицу view.

 Что такое FOREIGN KEY:
 Внешние ключи позволяют установить связи между таблицами. Внешний ключ устанавливается для столбцов из зависимой, подчиненной таблицы, и  указывает на один из столбцов из главной таблицы. Как правило, внешний ключ указывает на первичный ключ из связанной главной таблицы.

Создаем таблицу 1

> CREATE TABLE employees (emp_id int UNIQUE, emp_name varchar(20), PRIMARY KEY (emp_id) );

Создаем таблицу 2 и связываем ее через внешний ключ FOREIGN KEY:

> CREATE TABLE dogs ( dog_id int UNIQUE, dog_name varchar(20), walker int, walk_distance decimal(3,2), meals_perday int, cups_permeal decimal(3,2), PRIMARY KEY (dog_id), FOREIGN KEY (walker), REFERENCES employees(emp_ID) );

Заполняем таблицу 1:

> INSERT INTO employees VALUES (1, 'Peter'), (2, 'Paul'), (3, 'Mary');

Заполняем таблицу 2:

> INSERT INTO dogs VALUES (1, 'Dottie', 1, 5, 3, 1), (2, 'Bronx', 3, 6.5, 3, 1.25), (3, 'Harlem', 3, 1.25, 2, 0.25), (4, 'Link', 2, 2.75, 2, 0.75), (5, 'Otto', 1, 4.5, 3, 2), (6, 'Juno', 1, 4.5, 3, 2), (7, 'Zephyr', 3, 3, 2, 1.5);

Создаем нужный нам SELECT данных из двух таблиц

> SELECT emp_name, dog_name, walk_distance, meals_perday, cups_permeal FROM employees JOIN dogs ON emp_ID = walker;

Создаем для этого SELECT-а виртуальную таблицу VIEW:

> CREATE VIEW walking_schedule AS SELECT emp_name, dog_name, walk_distance, meals_perday, cups_permeal FROM employees JOIN dogs ON emp_ID = walker;

Проверяем SELECT всех данных с нашего VIEW:

> SELECT * FROM walking_schedule;

Пример работы с данными виртуальной таблицы VIEW:

> SELECT emp_name, MAX(walk_distance) AS longest_walks FROM walking_schedule GROUP BY emp_name;

Создать пользователя user1 с правами SELECT на этот VIEW:

> CREATE USER 'user1'@'localhost' IDENTIFIED BY 'password';
> GRANT SELECT ON views_db.walking_schedule to 'user1'@'localhost';

Добавление данных в таблицу 2 и появлениех в нашей view:

> INSERT INTO dogs VALUES (8, 'Charlie', 2, 3.5, 3, 1);
> SELECT * FROM walking_schedule;

Изменение нашей VIEW другим SELECT запросом через CREATE OR REPLACE VIEW:

> CREATE OR REPLACE VIEW walking_schedule AS SELECT emp_name, dog_name, walk_distance, meals_perday, (cups_permeal * meals_perday) AS total_kibble FROM employees JOIN dogs ON emp_ID = walker;
> SELECT * FROM walking_schedule;