Лабораторная работа №2 – Сложные запросы SQL
Оглавление
Ключи. 2
Соединения таблиц. 4
INNER JOIN.. 5
LEFT JOIN.. 6
RIGHT JOIN.. 7
NATURAL JOIN.. 7
FULL OUTER JOIN.. 9
Декартова выборка. 9
Объединения запросов. 10
UNION.. 10
UNION ALL и UNION DISTINCT. 10
Подзапросы.. 11
EXISTS. 12
SOME/ANY. 12
ALL. 13
Представления. 13
Задания к лабораторной работе №2. 14
Ключи
Рассмотрим базу данных из двух отношений.
· Специализация врача – ключ специализации (первичный ключ) и ее название;
· Доктор – ключевое поле, имя врача, дата рождения и специализация (внешний ключ).
При создании таблицы spec укажем, что поле spec_id является первичным ключом:
CREATE TABLE `spec`(
`spec_id` INT(3) UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT,
`name` VARCHAR(20)
) ENGINE=InnoDB;
Обратите внимание, что таблица должна иметь тип InnoDB. Это связано с тем, что данный тип единственный в MySQL, поддерживающий механизмы транзакций и внешних ключей.
При создании таблицы doctor необходимо указать, что поле spec является внешним ключом.
В общем виде синтаксис создания внешнего ключа:
CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (index_col_name,...)
REFERENCES tbl_name (index_col_name,...)
[ON DELETE reference_option]
[ON UPDATE reference_option]
Где:
· symbol - имя ключа
· index_name - имя поля в таблице, которое мы хотим сделать ключом
· tbl_name - таблица, с которой осуществляем связывание
· index_col_name - имя поля, с которым связываем наш ключ
· reference_option – ограничения внешнего ключа
Ограничения reference_option:
RESTRICT | CASCADE | SET NULL | NO ACTION
· CASCADE: если связанная запись родительской таблицы обновлена или удалена, и мы хотим чтобы соответствующие записи в таблицах-потомках также были обновлены или удалены. Что происходит с записью в родительской таблице, тоже самое произойдет с записью в дочерних таблицах.
· SET NULL:если запись в родительской таблице обновлена или удалена, а мы хотим чтобы в дочерней таблице некоторым занчениям было присвоено NULL (конечно если поле таблицы это позволяет)
|
|
· NO ACTION: смотри RESTRICT
· RESTRICT:если связанные записи родительской таблицы обновляются или удаляются со значениями которые уже/еще содержатся в соответствующих записях дочерней таблицы, то база данных не позволит изменять записи в родительской таблице. Обе команды NO ACTION и RESTRICT эквивалентны отсутствию
подвыражений ON UPDATE or ON DELETE для внешних ключей.
Итак, создадим таблицу doctor также типа InnoDB и укажем, что поле spec- внешний ключ, а также зададим ограничения целостности ON DELETE CASCADE, ON UPDATE CASCADE.
CREATE TABLE `doctor` (
`doc_id` INT(3) UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT,
`name` VARCHAR(20),
`birth` DATE,
`spec` INT(3) UNSIGNED NOT NULL,
CONSTRAINT `spec` FOREIGN KEY(`spec`) REFERENCES `spec`(`spec_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoBB;
В утилите phpMyAdmin ограничения внешнего ключа можно задать в редакторе связей:
Заполним таблицы данными:
Таблица spec:
INSERT INTO `isis`.`spec` (`spec_id`, `name`)
VALUES (NULL, 'Терапевт'), (NULL, 'Кардиолог'), (NULL, 'Хирург');
Таблица doctor:
INSERT INTO `isis`.`doctor` (`doc_id`, `name`, `birth`, `spec`)
VALUES (NULL, 'Иванов И.И.', '1972-02-02', '1'), (NULL, 'Жулин А.К.', '1949-03-19', '1'), (NULL, 'Голубев И.А.', '1968-06-29', '1'), (NULL, 'Терезникова Н.А.', '1955-09-17', '2'), (NULL, 'Самойлова О.Т.', '1978-12-13', '2');
Соединения таблиц
При разработке веб-проектов с участием базы данных нам часто необходимо в запросах объединять таблицы базы, чтобы получить необходимые данные.
Основные типы объединения таблиц в MySQL:
1. CROSS JOIN, он же INNER JOIN, он же JOIN
2. LEFT JOIN
3. RIGHT JOIN
4. NATURAL JOIN
5. Аналоги FULL OUTER JOIN для MySQL
6. Если не указать USING или ON в объединении (Декартова выборка)
|
|
В предложении FROM может быть указана явная операция соединения двух и более таблиц. Среди ряда операций соединения, описанных в стандарте языка SQL, многими серверами баз данных поддерживается только операция соединения по предикату. Синтаксис соединения по предикату имеет вид:
FROM <таблица 1>
[INNER]
{{LEFT | RIGHT | FULL } [OUTER]} JOIN <таблица 2>
[ON <предикат>]
Соединение может быть либо внутренним (INNER), либо одним из внешних (OUTER). Служебные слова INNER и OUTER можно опускать, поскольку внешнее соединение однозначно определяется его типом — LEFT (левое), RIGHT (правое) или FULL (полное), а просто JOIN будет означать внутреннее соединение.
Предикат определяет условие соединения строк из разных таблиц. При этом INNER JOIN означает, что в результирующий набор попадут только те соединения строк двух таблиц, для которых значение предиката равно TRUE. Как правило, предикат определяет эквисоединение по внешнему и первичному ключам соединяемых таблиц, хотя это не обязательно.
INNER JOIN
ü INNER JOIN производит выборку записей, которые только существуют в TableA и TableB одновременно.
ü CROSS JOIN — это эквивалент INNER JOIN.
ü INNER JOIN можно заменить условием объединения в WHERE.
Пример:
SELECT * FROM `spec`, `doctor`
WHERE `doctor`.`spec` = `spec`.`spec_id`
Идентичный запрос:
Условие соединения указывается при помощи ON.
SELECT * FROM `doctor`
INNER JOIN `spec`
ON `spec`.`spec_id` = `doctor`.`spec`
Если поле имеет одинаковое название в обеих таблицах, возможно использование USING.
…
INNER JOIN `таблица`
USING (`поле`);
Результат:
Таким образом, соединяются только те строки таблицы, у которых в указанных столбцах находятся равные значения (эквисоединение). В таблице нет ни одного доктора со специализацией 3, т.е. хирург, поэтому INNER JOIN для него не сработал. Если разные таблицы имеют столбцы с одинаковыми именами, то для однозначности требуется использовать точечную нотацию, которая называется уточнением имени столбца: <имя таблицы>.<имя столбца>. В тех случаях, когда это не вызывает неоднозначности, использование данной нотации не является обязательным.
|
|
LEFT JOIN
LEFT OUTER JOIN (LEFT JOIN) указывает, что левая таблица управляющая (в нашем случае TableA) и производит из нее полную выборку, осуществляя поиск соответствующих записей в таблице TableB. Если таких соответствий не найдено, то база вернет пустой указатель - NULL. Указание OUTER - необязательно.
Запрос:
SELECT * FROM `spec`
LEFT JOIN `doctor`
ON `spec`.`spec_id` = `doctor`.`spec`
Результат:
Для полей таблицы spec (терапевта и кардиолога) были найдены соответствия в таблице doctor, а для хирурга не было найдено ни одного соответствия, поэтому был возвращен NULL.
Чтобы произвести выборку записей из таблицы TableA, которых не существует в таблице TableB, мы выполняем LEFT JOIN, но затем из результата исключаем записи, которые не хотим видеть, путем указания, что TableB.id является нулем (указывая, что записи нет в таблице TableB).
Запрос:
SELECT *
FROM `spec`
LEFT JOIN `doctor` ON `spec`.`spec_id` = `doctor`.`spec`
WHERE `doctor`.`spec` IS NULL
Результат:
RIGHT JOIN
RIGHT JOIN выполняет те же самые функции, что и LEFT JOIN, за исключением того, что правая таблица будет прочитана первой. Таким образом, если в предыдущих запросах LEFT заменить на RIGHT, то таблица результатов, грубо говоря, отразится по вертикали. То есть, в результате вместо значений TableA будут записи TableB и наоборот.
NATURAL JOIN
Суть этой конструкции в том, что база сама выбирает, по каким столбцам сравнивать и объединять таблицы. А выбор этот падает на столбцы с одинаковыми именами. С одной стороны, это весьма удобно, но с другой стороны, база может выбрать совершенно не те столбцы для объединения и запрос будет работать совершенно не так, как вы предполагали. Нет гарантии того, что столбцы с одинаковыми именами в таблицах будут именно ключевыми и предназначены для объединения. NATURAL JOIN ухудшает читаемость кода, так как разработчик не сможет по запросу определить, как объединяются таблицы. Поэтому, обращая внимание на такие факторы, NATURAL JOIN использовать не рекомендуется. Рассмотрим примеры.
Запрос:
SELECT * FROM `doctor`
NATURAL JOIN `spec`
В этом случае СУБД выполняет объединение таблиц по столбцу name, так как он присутствует в обеих таблицах, что идентично следующему запросу:
SELECT * FROM `doctor`
INNER JOIN `spec`
USING (`name`)
Результат:
Поскольку не существует записей с одинаковым полем name одновременно в обеих таблицах, запрос вернет пустой результат.
Если попробовать изменить запрос следующим образом.
Запрос:
SELECT `spec`. *, `doctor`. *
FROM `doctor`
NATURAL RIGHT JOIN `spec`
Такой запрос приводится к следующему:
SELECT `spec`. *, `doctor`. *
FROM `doctor`
RIGHT JOIN `spec`
USING (`name`)
Результат:
Происходит это так: так как правая таблица управляющая (т.е. таблица spec), то она читается первой и полностью выбирается, независимо от левой (doctor) таблицы; когда начинается поиск соответствующих записей в левой таблице, то СУБД не находит ни одной записи, которая была бы идентична по name, поэтому возвращаются пустые указатели.
Для более подробного понимания работы NATURAL JOIN изменим имя доктора на Терапевт, а затем выполним те же самые запросы.
UPDATE `isis`.`doctor` SET `name` = 'Терапевт' WHERE `doctor`.`doc_id` =1;
Запрос:
SELECT * FROM `doctor`
NATURAL JOIN `spec`
Результат:
Выводится одна запись, поскольку теперь в обеих таблицах существует запись с одинаковым значением поля name.
Запрос:
SELECT `spec`. *, `doctor`. *
FROM `doctor`
NATURAL RIGHT JOIN `spec`
Возвращает результат:
FULL OUTER JOIN
*Недоступно в MySQL
FULL OUTER JOIN производит выборку всех записей из TableA и TableB, вне зависимости есть ли соответствующая запись в соседней таблице. Если таковой нет, то недостающая сторона будет содержать пустой указатель и результатом будет выводится NULL.
Декартова выборка
Если при объединении таблиц не указать условие объединения через ON или USING, то база произведет так называемую Декартову выборку, когда значению одной таблицы приравнивается каждое значение другой. Таким образом, СУБД, в нашем случае, возвращает 3x5 = 15 строк.
Запрос:
SELECT *
FROM `doctor`
JOIN `spec`
Результат:
Объединения запросов
UNION
Объединением двух множеств называется множество всех элементов, принадлежащих какому-либо одному или обоим исходным множествам. Результатом объединения будет множество, состоящее из всех строк, входящих в какое-либо одно или в оба первоначальных отношения. Однако, если этот результат сам по себе должен быть другим отношением, а не просто разнородной смесью строк, то два исходных отношения должны быть совместимыми по объединению, т. е. строки в обоих отношениях должны быть одной и той же формы. Что касается SQL, то две таблицы совместимы по объединению (и к ним может быть применен оператор объединения UNION) тогда и только тогда, когда:
· Они имеют одинаковое число полей (например, m);
· Для всех i (i = 1, 2, …, m) i-е поле первой таблицы и i-е поле второй таблицы имеют в точности одинаковый тип данных.
Пример:
(
SELECT * FROM `doctor`
WHERE `name` LIKE '%ов%'
)
UNION
(
SELECT * FROM `doctor`
WHERE `name` LIKE 'Ж%'
)
В данном абстрактном примере UNION используется для объединения результатов работы нескольких команд SELECT в один набор результатов. Круглые скобки используются для визуального разделения запросов и обязательны при использовании операторов ORDER BY и др.
Результат:
UNION ALL и UNION DISTINCT
Если не используется ключевое слово ALL для UNION, все возвращенные строки будут уникальными, так как по умолчанию подразумевается DISTINCT для всего результирующего набора данных. Если указать ключевое слово ALL, то результат будет содержать все найденные строки из всех примененных команд SELECT.
Пример:
(
SELECT *
FROM `doctor`
WHERE `name` LIKE '%ов%'
)
UNION ALL (
SELECT *
FROM `doctor`
WHERE `name` LIKE 'И%'
)
Результат:
Подзапросы
При работе с базой данных может возникнуть потребность в запросе, который зависел бы от результата другого запроса. Подзапрос - это запрос, результат которого используется в условии другого запроса. Основные преимущества подзапросов:
· Они позволяют писать структурированные запросы таким образом, что можно изолировать части оператора.
· Они представляют альтернативный способ выполнения операций, которые требуют применения сложных соединений и слияний (JOIN и UNION).
· По мнению многих, они более читабельны.
Обратите внимание, что такого рода подзапросы не могут использоваться с SELECT *, поскольку они выбирают лишь одиночный столбец.
Пример:
SELECT * FROM `doctor`
WHERE `spec` = (SELECT `spec_id` FROM `spec` WHERE `name` LIKE 'К%')
Результат:
Таким образом, выполняется выборка всех врачей со специализацией, начинающейся на букву К. Подзапрос используется для поиска spec_id, т.е. идентификатора специализации на букву К. Затем найденный идентификатор служит условием поиска врачей с такой специализацией. На подзапросы можно налагать дополнительные ограничения, такие как:
· EXISTS
· SOME/ANY
· ALL
EXISTS
Используется, чтобы указать предикату, производит ли подзапрос вывод. Возвращает булево значение.
Пример:
SELECT * FROM `doctor`
WHERE EXISTS (
SELECT *
FROM `spec`
WHERE `name` LIKE 'Хирург'
)
Результат:
Оператор EXISTS во внешнем предикате отмечает, что извлекать данные о врачах следует только в случае, если существует запись Хирург. В противном случае будет возвращен пустой результат.
SOME/ANY
Операторы SOME/ANY (взаимозаменяемые — различие в терминологии состоит в том, чтобы позволить людям использовать тот термин, который наиболее однозначен).
Оператор ANY берет все значения выведенные подзапросом, причем такого же типа, как и те, которые сравниваются в основном предикате. В этом его отличие от EXISTS, который просто определяет, производит ли подзапрос результаты, и фактически не использует эти результаты.
Пример:
SELECT * FROM `doctor`
WHERE `spec` = ANY(
SELECT `spec_id`
FROM `spec`
WHERE `spec_id` <3
)
Результат:
В данном случае оператор ANY берет все значения, выведенные подзапросом (все записи с идентификатором < 3) и оценивает их как верные, если любой из них равняется идентификатору текущей строки внешнего запроса.
ALL
Предикат является верным, если каждое значение, выбранное подзапросом, удовлетворяет условию в предикате внешнего запроса.
Пример:
SELECT * FROM `doctor`
WHERE `spec` = ALL (
SELECT `spec_id`
FROM `spec`
WHERE `name` LIKE '%ог'
)
Результат:
Данный запрос позволяет найти всех врачей, у которых название специализации заканчивается на «ог».
Представления
Представление — запрос на выборку, сохраненный в базе данных под каким-то названием, виртуальная таблица. Данные представления динамически рассчитываются по запросу из данных реальных таблиц, но структура (поля) результата запроса не меняются при изменении исходных таблиц.
Плюсы использования виртуальных таблиц:
1. Повышение скорости работы. Когда прикладной программе требуется таблица с определённым набором данных, она делает простейший запрос из подготовленного представления. Поскольку SQL-запрос, выбирающий данные представления, зафиксирован на момент его создания, СУБД получает возможность применить к этому запросу оптимизацию или предварительную компиляцию, что положительно сказывается на скорости обращения к представлению по сравнению с прямым выполнением того же запроса из прикладной программы.
2. Независимая модификация прикладной программы и схемы хранения данных.
3. Повышение безопасности. За счет предоставления пользователю только тех данных, на которые он имеет права — от него скрыта реальная структура таблиц базы данных.
Может возникнуть такая ситуация, что в исходных таблицах есть обязательные и не пустые (NOT NULL) поля, а в представлении их нет. Тогда операция добавления строки данных не может быть выполнена за одно действие. Во избежание потерь данных подобных действий следует избегать.
Работа с представлениями:
Создание:
CREATE VIEW `name` [ FIELDS ] AS { запрос };
Удаление:
DROP VIEW `name`;
Изменение:
ALTER VIEW `name` [ FIELDS ] AS { новый запрос }