12. Повторите задание п.10. Проверьте успешность выполнения действий.
13. Отнимите предоставленные Вами права на пользование Вашей базой данных.
Индивидуальные варианты заданий
Вариант 1.
1. Выдать список всех поставок, в которых количество деталей находится в диапазоне от 300 до 750 включительно.
2. Выдать номера изделий, использующих по крайней мере одну деталь, поставляемую поставщиком S6.
Вариант 2.
1. Выдать цвета деталей, поставляемых поставщиком S6.
2. Выдать номера и фамилии поставщиков, поставляющих деталь Р1 для какого-либо изделия в количестве, большем среднего объема поставок детали Р1 для этого изделия.
Вариант 3.
1. Выдать названия изделий, для которых поставляются детали поставщиком S6.
2. Выдать номера и названия изделий, для которых поставщик S6 поставляет несколько деталей каждого из поставляемых им типов.
Вариант 4.
1. Для каждой поставляемой для некоторого изделия детали выдать ее номер, номер изделия и соответствующее общее количество деталей.
2. Выдать номера изделий, для которых детали полностью поставляет поставщик S6.
Вариант 5.
1. Выдать номера и фамилии поставщиков, поставляющих детали для какого-либо изделия с деталью P1 в количестве, большем, чем средний объем поставок детали P1 для этого изделия.
2. Выдать номера изделий, использующих только детали, поставляемые поставщиком S6.
Контрольные вопросы
1. Кто является владельцем базы данных?
2. Какими правами обладают другие пользователи по отношению к Вашей базе данных?
3. Какими правами обладает администратор базы данных по отношению к Вашей базе данных?
4. Каким образом предоставляются права на пользование базой данных и отдельными ее таблицами?
5. Каким образом изымаются права на пользование базой данных и отдельными ее таблицами?
6. Что такое внешняя база данных?
7. Как идентифицируется таблица внешней базы данных?
8. Как идентифицируется таблица внешней распределенной базы данных?
Цель работы: познакомиться с возможностями MySQL по работе с хранимыми процедурами, функциями, триггерами, представлениями.
Представления (views) можно сравнить с временными таблицами, наполненными динамически формируемым содержимым.. В настоящей реализации есть две возможности создания представлений: с использованием алгоритма временных таблиц MySQL и с созданием самостоятельной таблицы. Нас интересует именно второй способ (первый был реализован, скорее всего, исходя из соображений совместимости и унификации). Такие представления позволяют значительно снизить объём кода, в котором часто повторялись простые объединения таблиц. К ним (после создания) применимы любые запросы, возвращающие результат в виде набора строк. То есть команды SELECT, UPDATE, DELETE, можно применять так же, как и к реальным таблицам. Важно и то, что посредством представлений можно более гибко распоряжаться правами пользователей базы данных, так как в этом случае есть возможность предоставлять доступ на уровне отдельных записей различных таблиц.
Создание представлений
Для создания представлений используется команда CREATE VIEW
CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] [DEFINER = { user | CURRENT_USER }] [SQL SECURITY { DEFINER | INVOKER }] VIEW view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION]Пример создания и работы простейшего представления:
Create View v as Select column 1 from T
Insert into v Values (1)
Select * from v
Результат
+---------+
| column1 |
+---------+
| 1 |
+---------+
1 row in set (0.00 sec)
Представление может быть создано на основе различных параметров предложения SELECT, при этом можно ссылаться на другие таблицы и представления. Конструкция может использовать оператор UNION и другие подзапросы.
Для внесения изменений в представление используется команда ALTER VIEW
ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] [DEFINER = { user | CURRENT_USER }] [SQL SECURITY { DEFINER | INVOKER }] VIEW view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION]Синтаксис команды DROP VIEW
Для удаления представления используется команда DROP VIEW
VIEW [IF EXISTS] view_name [, view_name] ... [RESTRICT | CASCADE]ПРИМЕР
mysql> CREATE TABLE t (qty INT, price INT);mysql> INSERT INTO t VALUES(3, 50);mysql> CREATE VIEW v AS SELECT qty, price, qty*price AS value FROM t;mysql> SELECT * FROM v;+------+-------+-------+| qty | price | value |+------+-------+-------+| 3 | 50 | 150 |В СУБД MySQL появилась возможность создания и хранения функций и процедур. Объявление и работа с процедурами и функциями отличаются в следующем:
· в заголовке функции помимо описания формальных параметров обязательно указывается тип возвращаемого ею результата;
· для возврата функцией значения в точку вызова среди ее операторов должен быть хотя бы один, в котором имени функции или переменной Result присваивается значение результата;
· вызов процедуры выполняется отдельным оператором;
· вызов функции может выполняться там, где допускается ставить выражение, в частности, в правой части оператора присваивания.
Пользовательские функции по функциональности похожи на хранимые процедуры. Разница заключается в том, что возможностей у них меньше (в частности, они должны возвращать только одно значение, например, скалярное или табличное), но их удобнее использовать с точки зрения синтаксиса.
Как процедуры, так и функции могут возвращать значения (в виде набора записей). Различие состоит в том, что функция вызывается из запроса, а процедура из отдельной команды.
На настоящий момент реализация хранимых процедур не поддерживает никаких внешних языков, но (по крайней мере, так заявляется) соответствует стандарту SQL:2003, позволяющему применять условные конструкции, итерации и обработку ошибок.
Пример создания хранимой процедуры в MySQL 5:
CREATE PROCEDURE p ()
LANGUAGE SQL
NOT DETERMINISTIC
SQL SECURITY DEFINER
COMMENT 'A Procedure' <--
SELECT CURRENT_DATE, RAND() FROM t
В данном случае мы создали процедуру с именем p, которая возвращает текущую дату и псевдослучайное число из таблицы t. Пример ее вызова и возвращаемого результата:
mysql> call p2()
+--------------+-----------------+
| CURRENT_DATE | RAND() |
+--------------+-----------------+
| 2005-06-27 | 0.7822275075896 |
+--------------+-----------------+
1 row in set (0.26 sec)
Query OK, 0 rows affected (0.26 sec)
Чуть более сложный пример создания и использования функции:
CREATE FUNCTION factorial (n DECIMAL(3,0))
RETURNS DECIMAL(20,0)
DETERMINISTIC
BEGIN
DECLARE factorial DECIMAL(20,0) DEFAULT 1;
DECLARE counter DECIMAL(3,0);
SET counter = n;
factorial_loop: REPEAT
SET factorial = factorial * counter;
SET counter = counter - 1;
UNTIL counter = 1
END REPEAT;
RETURN factorial;
END
В приложении:
INSERT INTO t VALUES (factorial(pi))
SELECT s1, factorial (s1) FROM t
UPDATE t SET s1 = factorial(s1)
WHERE factorial(s1) < 5
Разумеется эффективность применения хранимых процедур существенно возрастает при вызове их с параметрами (аргументами). Ниже дан пример процедуры с обработкой переданных ей параметров:
CREATE PROCEDURE p1 (IN parameter1 INT)
BEGIN
DECLARE variable1 INT;
SET variable1 = parameter1 + 1;
IF variable1 = 0 THEN
INSERT INTO t VALUES (17);
END IF;
IF parameter1 = 0 THEN
UPDATE t SET s1 = s1 + 1; <--
ELSE
UPDATE t SET s1 = s1 + 2;
END IF;
END;
Вызов процедуры теперь будет таким:
mysql> CALL p2(0) // Query OK, 2 rows affected (0.28 sec)
и в результате запроса мы получим:
mysql> SELECT * FROM t
+----+
| s1 |
+----+
| 6 |
| 6 |
+------+
2 rows in set (0.01 sec)
Кроме условных, возможны и любые циклические конструкции:
CREATE PROCEDURE p3 ()
BEGIN
DECLARE v INT;
SET v = 0;
WHILE v < 5 DO
INSERT INTO t VALUES (v);
SET v = v + 1;
END WHILE;
END;
Вызов процедуры:
mysql> CALL p3()
+------+
| s1 |
+------+
…………
| 0 |
| 1 |
| 2 |
| 3 |
| 4 |
+------+
Query OK, 1 row affected (0.00 sec)
Также применимы итерации, переходы, словом, всё, что предполагает стандарт.
Внутри функций и хранимых процедур осуществлена реализация курсоров, но, к сожалению, она пока ограничена (ASESITIVE, READ ONLY и NONSCROLL):