Sqlреализация операций реляционной алгебры предложением select


Содержание

Оператор SELECT: использование объединения, пересечения и разности для реализации реляционной алгебры. Примеры

Читайте также:

  1. II. ИСПОЛЬЗОВАНИЕ НАПОЛЬНЫХ ВЕСОВ
  2. II. СТРАТЕГИЧЕСКИЕ ЦЕЛИ И ОСНОВНЫЕ ПОДХОДЫ К ИХ РЕАЛИЗАЦИИ
  3. III. КООРДИНАЦИЯ ДЕЙСТВИЙ ПО РЕАЛИЗАЦИИ ОСНОВНЫХ ПОЛОЖЕНИЙ КОНЦЕПЦИИ
  4. Microsoft SQL Server 2000. Создание и использование хранимых процедур и пользовательских функций.
  5. XIII. Правила пересечения воздушных трасс
  6. Агрегатные функции, вложенные запросы в операторе выбора.
  7. Административно-правовые гарантии реализации прав граждан РФ.
  8. Административно-правовые методы реализации исполнительной власти.
  9. Административно-правовые формы реализации исполнительной власти
  10. Антонимы и их стилистическое использование.
  11. Аэробное дыхание. Химизм и использование энергии м-о
  12. Б коэффициента «ЦенаПрибыль» по плановым показателям предприятия, вытекающим из бизнес-плана финансового оздоровления при реализации инновационного проекта

Оператор SELECT является фактически самым важным для польз-ля и самым сложным оператором SQL. Он предназначен для выборки дан-х из таблиц, т.е. он, собственно, и реализует одно их осн-х назначение БД — предоставлять информацию польз-лю.

Структура команды select:

— from …– таблица, из которой будут извлекаться данные;

— where …– «горизонтальный» фильтр, условие на строки;

— order by …– критерий упорядочения строк результатирующей таблицы

— group by… – критерий группирования строк таблицы: строки таблицы разбиваются на группы с одинаковым значением критерия, и каждая группа дает единственную строку в выходную таблицу;

— having… – критерий фильтрации групп;

— into… — куда и в каком виде записать результат.

В реализациях конкретных реляционных СУБД сейчас не используется в чистом виде ни реляционная алгебра, ни реляционное исчисление. Фактическим стандартом доступа к реляционным дан-м стал язык SQL (Structured Query Language). Язык SQL предст-т собой смесь операторов реляционной алгебры и выражений реляционного исчисления, использующий синтаксис, близкий к фразам английского языка и расширенный дополнительными возможностями, отсутствующими в реляционной алгебре и реляционном исчислении. Вообще, язык доступа к дан-м называется реляционно полным, если он по выразительной силе не уступает реляционной алгебре (или, что то же самое, реляционному исчислению), т.е. любой оператор реляционной алгебры может быть выражен средствами этого языка. Именно таким и является язык SQL.

Реляционные операторы объединения, пересечения и взятия разности требуют, чтобы отношения имели одинаковые заголовки. Действительно, отношения состоят из заголовка и тела. Операция объединения двух отношений есть просто объединение двух множеств кортежей, взятых из тел соответствующих отношений.

Отношения называются совместимыми по объединению, если 1)имеют одно и то же множество имен атрибутов, то есть для любого атрибута в одном отношении найдется атрибут с таким же наименованием в другом отношении, 2)атрибуты с одинаковыми именами определены на одних и тех же доменах.

Некоторые отношения не являются совместимыми по объединению, но становятся таковыми после некоторого переименования атрибутов.

●объединение двух подзапросов — ключевое слово UNION.

Пр. Получить имена поставщиков, имеющих статус, больший 3 или поставляющих хотя бы одну деталь номер 2

SELECT P.PNAME FROM P WHERE P.STATUS > 3 UNION SELECT P.PNAME FROM P, PD WHERE P.PNUM = PD.PNUM AND PD.DNUM = 2;

Замечание. Результатирующие таблицы объединяемых запросов должны быть совместимы, т.е. иметь одинаковое количество столбцов и одинаковые типы столбцов в порядке их перечисления. Не требуется, чтобы объединяемые таблицы имели бы одинаковые имена колонок. Это отличает операцию объединения запросов в SQL от операции объединения в реляционной алгебре. Наименования колонок в результатирующем запросе будут автоматически взяты из результата первого запроса в объединении.

●пересечение двух подзапросов — ключевое слово INTERSECT

Пр. Получить имена поставщиков, имеющих статус, больший 3 и одновременно поставляющих хотя бы одну деталь номер 2.

SELECT P.PNAME FROM P WHERE P.STATUS > 3 INTERSECT SELECT P.PNAME FROM P, PD WHERE P.PNUM = PD.PNUM AND PD.DNUM = 2;

●разность двух подзапросов — ключевое слово EXCEPT

Пр. Получить имена поставщиков, имеющих статус, больший 3, за исключением тех, кто поставляет хотя бы одну деталь номер 2:

Дата добавления: 2015-05-09 ; Просмотров: 1193 ; Нарушение авторских прав? ;

Нам важно ваше мнение! Был ли полезен опубликованный материал? Да | Нет

Реляционная алгебра и язык SQL

Основы реляционной алгебры

Доступ к реляционным данным осуществляется при помощи реляционной алгебры. Основная идея реляционной алгебры состоит в том, что коль скоро отношения являются множествами, то средства манипулирования отношениями могут базироваться на традиционных теоретико-множественных операциях, дополненных некоторыми специальными операциями, специфичными для баз данных.

Рассмотрим вариант алгебры, который был предложен Коддом. Набор основных алгебраических операций состоит из восьми операций, которые делятся на два класса — теоретико-множественные операции и специальные реляционные операции. В состав теоретико-множественных операций входят операции:

· взятия разности отношений;

· прямого произведения отношений.

Специальные реляционные операции включают:

Операндами каждой такой операции является одно или несколько отношений, результатом выполнения операции всегда является новое отношение.

В рассмотренных ниже примерах используются следующие отношения:

P(D1,D2,D3) Q(D4,D5) R(M,P,Q,T) S(A,B)

1 11 x x 1 x 101 5 a 5 a

2 11 y x 2 y 105 3 a 10 b

3 11 z y 1 z 500 9 a 15 c

4 12 x w 50 1 b 2 d

· ОБЪЕДИНЕНИЕ
Отношения-операнды в этом случае должны быть определены по одной схеме. Результирующее отношение содержит все строки операндов, за исключением повторяющихся.

· ПЕРЕСЕЧЕНИЕ
На входе операции два отношения, определенные по одной схеме. На выходе — отношение, содержащие кортежи, которые присутствуют в обоих исходных отношениях.

· РАЗНОСТЬ
Операция, во многом похожая на ПЕРЕСЕЧЕНИЕ, за исключением того, что в результирующем отношении содержатся кортежи, присутствующие в первом и отсутствующие во втором исходных отношениях.

Входные отношения могут быть определены по разным схемам. Схема результирующего отношения включает все атрибуты исходных. Кроме того:

o степень результирующего отношения равна сумме степеней исходных отношений;

o мощность результирующего отношения равна произведению мощностей исходных отношений.

· ПРОЕКЦИЯ (ВЕРТИКАЛЬНОЕ ПОДМНОЖЕСТВО)

Операция проекции представляет собой выборку из каждого кортежа отношения значений атрибутов, входящих в список A, и удаление из полученного отношения повторяющихся строк.

· ВЫБОРКА (ОГРАНИЧЕНИЕ, ГОРИЗОНТАЛЬНОЕ ПОДМНОЖЕСТВО)
На входе используется одно отношение, результат — новое отношение, построенное по той же схеме, содержащее подмножество кортежей исходного отношения, удовлетворяющих условию выборки.

· СОЕДИНЕНИЕ
Данная операция имеет сходство с ДЕКАРТОВЫМ ПРОИЗВЕДЕНИЕМ. Однако здесь добавлено условие, согласно которому вместо полного произведения всех строк в результирующее отношение включаются только строки, удовлетворяющие определенному соотношению между атрибутами соединения 1,A2) соответствующих отношений.

Пусть отношение R , называемое делимым, содержит атрибуты (A1,A2. An). Отношение S — делитель содержит подмножество атрибутов A: (A1,A2. Ak)(k

Стандарт языка SQL, хотя и основан на реляционной теории, но во многих местах отходит он нее. Например, отношение в реляционной модели данных не допускает наличия одинаковых кортежей, а таблицы в терминологии SQL могут иметь одинаковые строки. Имеются и другие отличия.

Операторы SQL. Основу языка SQL составляют операторы, разбитые на две группы по общности выполняемых функцияй:

Операторы DDL (Data Definition Language) — операторы определения объектов базы данных:

· CREATE TABLE — создать таблицу,

· ALTER TABLE — изменить таблицу,

· DROP TABLE — удалить таблицу,

· CREATE DOMAIN — создать домен,

· ALTER DOMAIN — изменить домен,

· DROP DOMAIN — удалить домен,

· CREATE VIEW — создать представление,

· DROP VIEW — удалить представление.

Операторы DML (Data Manipulation Language) — операторы манипулирования данными:

· SELECT — отобрать строки из таблиц,

· INSERT — добавить строки в таблицу,

· UPDATE — изменить строки в таблице,

· DELETE — удалить строки в таблице,

· COMMIT — зафиксировать внесенные изменения,

· ROLLBACK — откатить внесенные изменения.

Любая операция реляционной алгебры может быть выражена одним оператором SELECT.

Последнее изменение этой страницы: 2020-12-16; Нарушение авторского права страницы

6.4.4. Язык SQL и операции реляционной алгебры

Язык SQL является средством выражения мощного математического аппарата теории множеств и реляционной алгебры. В данном разделе рассматривается связь операторов языка SQL с операциями реляционной алгебры и теории множеств.

Средствами языка SQL операция объединения представляется следующим образом:

Средствами языка SQL операция объединения представляется следующим образом:

SELECT Field i1 , … , Field in

Операция выборка (селекция)

Операция соединение, эквисоединение

SELECT A.Field 1 , … , A.Field n , B.Field 1 , … , B.Field m

WHERE (A.Field i Θ B.Field 1 )

Если Θ – операция «=», то это эквисоединение.

Операция естественное соединение

Пусть есть отношения A (X 1 , … , X n , A 1 , …, A m ) и B (X 1 , … , X n , B 1 , …, B r ).

SELECT A.X 1 , … , A.X n , A.A 1 , … , A.A m, B.B 1 , … , B.B r

Diplom Consult.ru


Для того, чтобы показать, что язык SQL является реляционно полным, нужно показать, что любой реляционный оператор может быть выражен средствами SQL. На самом деле достаточно показать, что средствами SQL можно выразить любой из примитивных реляционных операторов.

Оператор декартового произведения

SELECT A.Поле1, A.Поле2, …, B.Поле1, B.Поле2, …

SELECT A.Поле1, A.Поле2, …, B.Поле1, B.Поле2, …

FROM A CROSS JOIN B;

SELECT DISTINCT X, Y, …, Z

Реляционный оператор переименования RENAME выражается при помощи ключевого слова AS в списке отбираемых полей оператора SELECT. Таким образом, язык SQL является реляционно полным.

Остальные операторы реляционной алгебры (соединение, пересечение, деление) выражаются через примитивные, следовательно, могут быть выражены операторами SQL. Тем не менее, для практических целей приведем их.

SELECT A.Поле1, A.Поле2, …, B.Поле1, B.Поле2, …

SELECT A.Поле1, A.Поле2, …, B.Поле1, B.Поле2, …

FROM A CROSS JOIN B

SELECT DISTINCT A.X

WHERE NOT EXIST

WHERE NOT EXIST

Замечание. Оператор SQL, реализующий деление отношений трудно запомнить, поэтому дадим пример эквивалентного преобразования выражений, представляющих суть запроса.

Пусть отношение A содержит данные о поставках деталей, отношение B содержит список всех деталей, которые могут поставляться. Атрибут X является номером поставщика, атрибут Y является номером детали.

Разделить отношение A на отношение B означает в данном примере «отобрать номера поставщиков, которые поставляют все детали».

Преобразуем текст выражения:

«Отобрать номера поставщиков, которые поставляют все детали» эквивалентно

«Отобрать те номера поставщиков из таблицы A, для которых не существует непоставляемых деталей в таблице B» эквивалентно

«Отобрать те номера поставщиков из таблицы A, для которых не существует тех номеров деталей из таблицы B, которые не поставляются этим поставщиком» эквивалентно

«Отобрать те номера поставщиков из таблицы A, для которых не существует тех номеров деталей из таблицы B, для которых не существует записей о поставках в таблице A для этого поставщика и этой детали».

Последнее выражение дословно переводится на язык SQL. При переводе выражения на язык SQL нужно учесть, что во внутреннем подзапросе таблица A должна быть переименована, для того чтобы отличать ее от экземпляра этой же таблицы, используемой во внешнем запросе.

Фактически стандартным языком доступа к базам данных в настоящее время стал язык SQL (Structured Query Language).

Язык SQL оперирует терминами, несколько отличающимися от терминов реляционной теории, например, вместо «отношений» используются «таблицы», вместо «кортежей» — «строки», вместо «атрибутов» — «колонки» или «столбцы».

Стандарт языка SQL, хотя и основан на реляционной теории, но во многих местах отходит он нее.

Основу языка SQL составляют операторы, условно разбитые не несколько групп по выполняемым функциям:

Операторы DDL (Data Definition Language) — операторы определения объектов базы данных.

Операторы DML (Data Manipulation Language) — операторы манипулирования данными.

Операторы защиты и управления данными, и др.

Одним из основных операторов DML является оператор SELECT, позволяющий извлекать данные из таблиц и получать ответы на различные запросы. Оператор SELECT содержит в себе все возможности реляционной алгебры. Это означает, что любой оператор реляционной алгебры может быть выражен при помощи подходящего оператора SELECT. Этим доказывается реляционная полнота языка SQL.

Различают концептуальную схему выполнения оператора SELECT и фактическую схему его выполнения. Концептуальная схема описывает, в какой логической последовательности должны выполняться операции, чтобы получить результат. При реальном выполнении оператора SELECT на первый план выступает достижение максимальной скорости выполнения запроса. Для этого используется оптимизатор, который, анализируя различные планы выполнения запроса, выбирает наилучший из них.

Р ЕЛЯЦИОННАЯ АЛГЕБРА И SQL. Рассмотрим, как связаны операции реляционной алгебры и язык SQL, т.е. приведем примеры запросов SQL, аналогичных операциям. — презентация

Презентация была опубликована 5 лет назад пользователемНикита Важенин

Похожие презентации

Презентация на тему: » Р ЕЛЯЦИОННАЯ АЛГЕБРА И SQL. Рассмотрим, как связаны операции реляционной алгебры и язык SQL, т.е. приведем примеры запросов SQL, аналогичных операциям.» — Транскрипт:

1 Р ЕЛЯЦИОННАЯ АЛГЕБРА И SQL

2 Рассмотрим, как связаны операции реляционной алгебры и язык SQL, т.е. приведем примеры запросов SQL, аналогичных операциям реляционной алгебры. В качестве примера базы данных будем использовать «Музыкантов».

3 П РИМЕР ER- МОДЕЛИ : «М УЗЫКАНТЫ »

4 П РИМЕР РЕЛЯЦИОННОЙ МОДЕЛИ : «М УЗЫКАНТЫ » Музыканты (НомМуз, ИмяМуз, ДатаРожд, СтрРожд) Сочинения (НомСоч, НазСоч, ДатаСоч, НомМуз ) С толбец НомМуз представляет собой ссылку на таблицу «Музыканты» и содержит номера музыкантов- композиторов. Исполнители (НомИсп, Инструмент, Оценка, НомМуз ) Столбец НомМуз представляет собой ссылку на таблицу «Музыканты». Ансамбли (НомАнс, НазАнс, СтрАнс, НомМуз ) Столбец НомМуз представляет собой ссылку на таблицу «Музыканты» и содержит номера музыкантов-руководителей ансамблей. УчастникиАнсамблей( НомАнс, НомИсп ) Эта таблица содержит ссылки на таблицы «Ансамбли» и «Исполнители». Исполнения ( НомМуз, НомАнс, НомСоч, ДатаИсп, СтрИсп, ГорИсп) Таблица имеет составной первичный ключ, а также ссылки на таблицы «Сочинения», «Музыканты» (имеются в виду дирижеры) и «Ансамбли».

5 О ПЕРАЦИЯ ПРОЕКЦИИ PROJ выражается через SELECT с ключевым словом DISTINCT. Получить все названия ансамблей: proj НазАнс (Ансамбли) SELECT DISTINCT НазАнс FROM Ансамбли

6 О ПЕРАЦИЯ ВЫБОРА SEL выражается через SELECT с ключевым словом WHERE. Получить данные об ансамблях из России: sel СтрАнс=’Россия’ (Ансамбли) SELECT * FROM Ансамбли WHERE СтрАнс=’Россия’ Условия также могут быть и сложными. Получить имена музыкантов, родившихся в 20-м веке SELECT ИмяМуз FROM Музыканты WHERE ДатаРожд>’ ‘ AND ДатаРожд ‘31.12.1900’ AND ДатаРожд»>

7 О ПЕРАЦИЯ СОЕДИНЕНИЯ ТАБЛИЦ JOIN может быть выражена несколькими способами. Получить имена композиторов: proj ИмяМуз (Музыканты join Сочинения) Можно использовать связь таблиц через условие WHERE: SELECT DISTINCT ИмяМуз FROM Музыканты М, Сочинения С WHERE С.НомМуз=М.НомМуз Можно использовать более современный синтаксис JOIN. ON SELECT DISTINCT ИмяМуз FROM Музыканты М JOIN Сочинения С ON С.НомМуз=М.НомМуз

8 О ПЕРАЦИЯ СОЕДИНЕНИЯ ТАБЛИЦ JOIN Если требуется вывести данные из одной таблицы, а условие накладывать на другую таблицу, то удобно использовать подзапросы, связанные и несвязанные. SELECT DISTINCT ИмяМуз FROM Музыканты WHERE НомМуз IN (SELECT НомМуз FROM Сочинения) или SELECT DISTINCT ИмяМуз FROM Музыканты WHERE НомМуз = Any (SELECT НомМуз FROM Сочинения) или SELECT DISTINCT ИмяМуз FROM Музыканты М WHERE EXISTS (SELECT * FROM Сочинения С WHERE С.НомМуз=М.НомМуз)

9 О ПЕРАЦИЯ СОЕДИНЕНИЯ ТАБЛИЦ JOIN Приведем пример сложного запроса, использующего данные из всех 6 таблиц базы данных. Получить названия ансамблей, которые играли Моцарта на саксофоне: SELECT НазАнс FROM Ансамбли WHERE НомАнс IN ( SELECT И1.НомАнс FROM Исполнения И1, Исполнители И2, Музыканты М, Сочинения С, УчастникиАнсамблей У WHERE И1.НомСоч=С.НомСоч AND С.НомМуз=М.НомМуз AND И1.НомАнс=У.НомАнс AND И2.НомИсп=У.НомИсп AND М.ИмяМуз=’Моцарт’ AND И2.Инструмент=’Саксофон’ )

10 О ПЕРАЦИЯ ОБЪЕДИНЕНИЯ UNION соответствует нескольким командам SELECT, связанным ключевым словом UNION. Получить общий список фамилий композиторов и дирижеров: proj ИмяМуз (Музыканты join Сочинения) union proj ИмяМуз (Музыканты join Исполнения) SELECT DISTINCT ИмяМуз FROM Музыканты М, Сочинения С WHERE С.НомМуз=М.НомМуз UNION SELECT DISTINCT ИмяМуз FROM Музыканты М, Исполнения И WHERE И.НомМуз=М.НомМуз

11 О ПЕРАЦИЯ ПЕРЕСЕЧЕНИЯ INTERSECTION может быть выражена несколькими способами. Получить имена музыкантов, которые играют и на саксофоне, и на кларнете: proj ИмяМуз (Музыканты join sel Инструмент=’Саксофон'(Исполнители)) intersection proj ИмяМуз (Музыканты join sel Инструмент=’Кларнет'(Исполнители))

12 О ПЕРАЦИЯ ПЕРЕСЕЧЕНИЯ INTERSECTION SELECT DISTINCT ИмяМуз FROM Музыканты М1, Исполнители И1, Исполнители И2 WHERE М1.НомМуз=И1.НомМуз AND И1.Инструмент=’Саксофон’ AND И2.Инструмент=’Кларнет’ AND И2.НомМуз=И1.НомМуз или SELECT DISTINCT ИмяМуз FROM Музыканты М1, Исполнители И1 WHERE М1.НомМуз=И1.НомМуз AND И1.Инструмент=’Саксофон’ AND М1.НомМуз IN (SELECT НомМуз FROM Исполнители И2 WHERE И2.Инструмент=’Кларнет’)

13 О ПЕРАЦИЯ ПЕРЕСЕЧЕНИЯ INTERSECTION или SELECT DISTINCT ИмяМуз FROM Музыканты М1, Исполнители И1 WHERE М1.НомМуз=И1.НомМуз AND И1.Инструмент=’Саксофон’ AND М1.НомМуз =ANY (SELECT НомМуз FROM Исполнители И2 WHERE И2.Инструмент=’Кларнет’) или SELECT DISTINCT ИмяМуз FROM Музыканты М1, Исполнители И1 WHERE М1.НомМуз=И1.НомМуз AND И1.Инструмент=’Саксофон’ AND EXISTS (SELECT * FROM Исполнители И2 WHERE И2.Инструмент=’Кларнет’ AND И2.НомМуз=И1.НомМуз)

14 О ПЕРАЦИЯ ВЫЧИТАНИЯ DIFFERENCE также может быть выражена несколькими способами. Получить имена музыкантов, которые играют на саксофоне, но не играют на кларнете: proj ИмяМуз (Музыканты join sel Инструмент=’Саксофон'(Исполнители)) difference proj ИмяМуз (Музыканты join sel Инструмент=’Кларнет'(Исполнители))

Илон Маск рекомендует:  Что такое код 2d

15 О ПЕРАЦИЯ ВЫЧИТАНИЯ DIFFERENCE SELECT DISTINCT ИмяМуз FROM Музыканты М1, Исполнители И1 WHERE М1.НомМуз=И1.НомМуз AND И1.Инструмент=’Саксофон’ AND М1.НомМуз NOT IN (SELECT НомМуз FROM Исполнители И2 WHERE И2.Инструмент=’Кларнет’) или SELECT DISTINCT ИмяМуз FROM Музыканты М1, Исполнители И1 WHERE М1.НомМуз=И1.НомМуз AND И1.Инструмент=’Саксофон’ AND М1.НомМуз !=ALL (SELECT НомМуз FROM Исполнители И2 WHERE И2.Инструмент=’Кларнет’)

16 О ПЕРАЦИЯ ВЫЧИТАНИЯ DIFFERENCE или SELECT DISTINCT ИмяМуз FROM Музыканты М1, Исполнители И1 WHERE М1.НомМуз=И1.НомМуз AND И1.Инструмент=’Саксофон’ AND NOT EXISTS (SELECT * FROM Исполнители И2 WHERE И2.Инструмент=’Кларнет’ AND И2.НомМуз=И1.НомМуз)

17 О ПЕРАЦИЯ УМНОЖЕНИЯ PRODUCT получается, если мы выполняем выборку из 2 таблиц, но не указываем условия связи. Получить всевозможные пары имен музыкантов: Музыканты2 aliases Музыканты proj Музыканты.ИмяМуз, Музыканты2.ИмяМуз (Музыканты product Музыканты2) SELECT М1.ИмяМуз, М2.ИмяМуз FROM Музыканты М1, Музыканты М2

18 ОПЕРАЦИЯ ДЕЛЕНИЯ DIVISION Очень интересно выглядит операция деления division. Она представляет собой двойное отрицание существования. Получить названия ансамблей, которые играли все произведения Моцарта (т.е., нет ни одного произведения Моцарта, которого они бы не играли): proj НазАнс (proj НомАнс, НомСоч (Исполнения) division proj НомСоч (sel ИмяМуз=’Моцарт’ (Музыканты) join Сочинения) join Ансамбли)

19 ОПЕРАЦИЯ ДЕЛЕНИЯ DIVISION SELECT НазАнс FROM Ансамбли А WHERE NOT EXISTS ( SELECT * FROM Сочинения С, Музыканты М WHERE С.НомМуз=М.НомМуз AND ИмяМуз=’Моцарт’ AND NOT EXISTS ( SELECT * FROM Исполнения И WHERE И.НомСоч=С.НомСоч AND И.НомАнс=А.НомАнс )

Содержание

Глава 3. Запросы с использованием нескольких таблиц

3.1. О средствах одновременной работы с множеством таблиц

Затрагивая вопросы проектирования баз данных [2], мы выяснили, что базы данных — это множество взаимосвязанных сущностей или отношений (таблиц) в терминологии реляционных СУБД. При проектировании стремятся создавать таблицы, в каждой из которых содержалась бы информация об одном и только об одном типе сущностей. Это облегчает модификацию базы данных и поддержание ее целостности. Но такой подход тяжело усваивается начинающими проектантами, которые пытаются привязать проект к будущим приложениям и так организовать таблицы, чтобы в каждой из них хранилось все необходимое для реализации возможных запросов. Типичен вопрос: как же получить сведения о том, где купить продукты для приготовления того или иного блюда и определить его калорийность и стоимость, если нужные данные «рассыпаны» по семи различным таблицам? Не лучше ли иметь одну большую таблицу, содержащую все сведения базы данных ПАНСИОН ?

Даже при отсутствии средств одновременного доступа ко многим таблицам нежелателен проект, в котором информация о многих типах сущностей перемешана в одной таблице. SQL же обладает великолепным механизмом для одновременной или последовательной обработки данных из нескольких взаимосвязанных таблиц. В нем реализованы возможности «соединять» или «объединять» несколько таблиц и так называемые «вложенные подзапросы». Например, чтобы получить перечень поставщиков продуктов, необходимых для приготовления Сырников, возможен запрос

Продукт Цена Название Статус
Яйца 1.8 ПОРТОС кооператив
Яйца 2. КОРЮШКА кооператив
Сметана 3.6 ПОРТОС кооператив
Сметана 2.2 ОГУРЕЧИК ферма
Творог 1. ОГУРЕЧИК ферма
Мука 0.5 УРОЖАЙ коопторг
Сахар 0.94 ТУЛЬСКИЙ универсам
Сахар 1. УРОЖАЙ коопторг

Он получен следующим образом: СУБД последовательно формирует строки декартова произведения таблиц, перечисленных во фразе FROM, проверяет, удовлетворяют ли данные сформированной строки условиям фразы WHERE, и если удовлетворяют, то включает в ответ на запрос те ее поля, которые перечислены во фразе SELECT.

Следует подчеркнуть, что в SELECT и WHERE (во избежание двусмысленности) ссылки на все (*) или отдельные столбцы могут (а иногда и должны) уточняться именем соответствующей таблицы, например, Поставки.ПС, Поставщики.ПС, Меню.*, Состав.БЛ, Блюда.* и т.п.

Очевидно, что с помощью соединения несложно сформировать запрос на обработку данных из нескольких таблиц. Кроме того, в такой запрос можно включить любые части предложения SELECT, рассмотренные в главе 2 (выражения с использованием функций, группирование с отбором указанных групп и упорядочением полученного результата). Следовательно, соединения позволяют обрабатывать множество взаимосвязанных таблиц как единую таблицу, в которой перемешана информация о многих типах сущностей. Поэтому начинающий проектант базы данных может спокойно создавать маленькие нормализованные таблицы, так как он всегда может получить из них любую «большую» таблицу.

Кроме механизма соединений в SQL есть механизм вложенных подзапросов, позволяющий объединить несколько простых запросов в едином предложении SELECT. Иными словами, вложенный подзапрос — это уже знакомый нам подзапрос (с небольшими огра-ничениями), который вложен в WHERE фразу другого вложенного подзапроса или WHERE фразу основного запроса.

Для иллюстрации вложенного подзапроса вернемся к предыдущему примеру и попробуем получить перечень тех поставщиков продуктов для Сырников, которые поставляют нужные продукты за минимальную цену.

Результат запроса имеет вид

Продукт Цена Название Статус
Яйца 1.8 ПОРТОС кооператив
Сахар 0.94 ТУЛЬСКИЙ универсам
Мука 0.5 УРОЖАЙ коопторг
Сметана 2.2 ОГУРЕЧИК ферма
Творог 1. ОГУРЕЧИК ферма

Здесь с помощью подзапроса, размещенного в трех последних строках запроса, описывается процесс определения минимальной цены каждого продукта для Сырников и поиск поставщика, предлагающего этот продукт за такую цену. Механизм реализации подзапросов будет подробно описан в п.3.3. Там же будет рассмотрено, как и для чего вводится псевдоним X для имени таблицы Поставки.

3.2. Запросы, использующие соединения

3.2.1. Декартово произведение таблиц

В литературе [2] показано, что соединения — это подмножества декартова произведения. Так как декартово произведение n таблиц — это таблица, содержащая все возможные строки r, такие, что r является сцеплением какой-либо строки из первой таблицы, строки из второй таблицы, . и строки из n-й таблицы (а мы уже научились выделять с помощью SELECT любое подмножество реляционной таблицы), то осталось лишь выяснить, можно ли с помощью SELECT получить декартово произведение. Для получения декартова произведения нескольких таблиц надо указать во фразе FROM перечень перемножаемых таблиц, а во фразе SELECT – все их столбцы.

Так, для получения декартова произведения Вид_блюд и Трапезы надо выдать запрос

Получим таблицу, содержащую 5 х 3 = 15 строк:

В Вид Т Трапеза
З Закуска 1 Завтрак
З Закуска 2 Обед
З Закуска 3 Ужин
С Суп 1 Завтрак
С Суп 2 Обед
С Суп 3 Ужин
Г Горячее 1 Завтрак
Г Горячее 2 Обед
Г Горячее 3 Ужин
Д Десерт 1 Завтрак
Д Десерт 2 Обед
Д Десерт 3 Ужин
Н Напиток 1 Завтрак
Н Напиток 2 Обед
Н Напиток 3 Ужин

В другом примере, где перемножаются таблицы Меню, Трапезы, Вид_блюд, Блюда:

образуется таблица (рис 3.1), содержащая 21 х 3 х 5 х 33 = 10395 строк.

Из первых 39 строк этой таблицы лишь две актуальных (отмечены «*»): в них совпадают номера блюд таблиц Меню и Блюда. В остальных – полная чепуха: к закускам отнесены супы и напитки, на завтрак предлагается незапланированный суп и т.д.

3.2.2. Эквисоединение таблиц

Если из декартова произведения убрать ненужные строки и столбцы, то можно получить актуальные таблицы, соответствующие любому из соединений.

Меню Трапезы Вид_блюд Блюда
Т В БЛ Т Трапеза В Вид БЛ Блюдо В Основа Выход Труд
1 З 3 1 Завтрак З Закуска 1 Салат летний З Овощи 200. 3
1 З 3 1 Завтрак З Закуска 2 Салат мясной З Мясо 200. 4
1 З 3 1 Завтрак З Закуска 3 Салат витаминный З Овощи 200. 4 *
. . .
1 З 3 1 Завтрак З Закуска 12 Суп молочный С Молоко 500. 3
1 З 3 1 Завтрак З Закуска 13 Бастурма Г Мясо 300. 5
. . .
1 З 3 1 Завтрак З Закуска 32 Кофе черный Н Кофе 100. 1
1 З 3 1 Завтрак З Закуска 33 Кофе на молоке Н Кофе 200. 2
1 З 6 1 Завтрак З Закуска 1 Салат летний З Овощи 200. 3
1 З 6 1 Завтрак З Закуска 2 Салат мясной З Мясо 200. 4
1 З 6 1 Завтрак З Закуска 3 Салат витаминный З Овощи 200. 4
1 З 6 1 Завтрак З Закуска 4 Салат рыбный З Рыба 200. 4
1 З 6 1 Завтрак З Закуска 5 Паштет из рыбы З Рыба 120. 5
1 З 6 1 Завтрак З Закуска 6 Мясо с гарниром З Мясо 250. 3 *
. . .

Рис. 3.1. Иллюстрация декартова произведения

Очевидно, что отбор актуальных строк обеспечивается вводом в запрос WHERE фразы, в которой устанавливается соответствие между:

  • кодами трапез (Т) в таблицах Меню и Трапезы (Меню.Т = Трапезы.Т),
  • кодами видов блюд (В) в таблицах Меню и Вид_блюд (Меню.В = Вид_блюд.В),
  • номерами блюд (БЛ) в таблицах Меню и Блюда (Меню.БЛ = Блюда.БЛ).


Такой скорректированный запрос

позволит получить эквисоединение таблиц Меню, Трапезы, Вид_блюд и Блюда:

Т В БЛ Т Трапеза В Вид БЛ Блюдо В Основа Выход Труд
1 З 3 1 Завтрак З Закуска 3 Салат витаминный З Овощи 200. 4
1 З 6 1 Завтрак З Закуска 6 Мясо с гарниром З Мясо 250. 3
1 Г 19 1 Завтрак Г Горячее 19 Омлет с луком Г Яйца 200. 5
. . .
3 Г 16 3 Ужин Г Горячее 16 Драчена Г Яйца 180. 4
3 Н 30 3 Ужин Н Напиток 30 Компот Н Фрукты 200. 2
3 Н 31 3 Ужин Н Напиток 31 Молочный напиток Н Молоко 200. 2

3.2.3. Естественное соединение таблиц

Легко заметить, что в эквисоединение таблиц вошли дубликаты столбцов, по которым проводилось соединение (Т, В и БЛ). Для исключения этих дубликатов можно создать естественное соединение тех же таблиц:

Реализация естественного соединения таблиц имеет вид

Т В БЛ Трапеза Вид Блюдо Основа Выход Труд
1 З 3 Завтрак Закуска Салат витаминный Овощи 200. 4
1 З 6 Завтрак Закуска Мясо с гарниром Мясо 250. 3
1 Г 19 Завтрак Горячее Омлет с луком Яйца 200. 5
.
3 Г 16 Ужин Горячее Драчена Яйца 180. 4
3 Н 30 Ужин Напиток Компот Фрукты 200. 2
3 Н 31 Ужин Напиток Молочный напиток Молоко 200. 2

3.2.4. Композиция таблиц

Для исключения всех столбцов, по которым проводится соединение таблиц, надо создать композицию

Трапеза Блюдо Вид Основа Выход Труд
Завтрак Салат витаминный Закуска Овощи 200. 4
Завтрак Мясо с гарниром Закуска Мясо 250. 3
Завтрак Омлет с луком Горячее Яйца 200. 5
. . .
Ужин Драчена Горячее Яйца 180. 4
Ужин Компот Напиток Фрукты 200. 2
Ужин Молочный напиток Напиток Молоко 200. 2

3.2.5. Тета-соединение таблиц

В базе данных ПАНСИОН трудно подобрать несложный пример, иллюстрирующий тета-соединение таблиц. Поэтому сконструируем такой надуманный запрос:

позволяющий выбрать из полученного в п.3.2.1 декартова произведения таблиц Вид_блюд и Трапезы лишь те строки, в которых значение трапезы «меньше» (по алфавиту) значения вида блюда:

В Вид Т Трапеза
З Закуска 1 Завтрак
С Суп 1 Завтрак
С Суп 2 Обед
Н Напиток 1 Завтрак

3.2.6. Соединение таблиц с дополнительным условием

При формировании соединения создается рабочая таблица, к которой применимы все операции, рассмотренные в главе 2: отбор нужных строк соединения (WHERE фраза), упорядочение получаемого результата (ORDER BY фраза) и агрегатирование данных (SQL-функции и GROUP BY фраза).

Например, для получения перечня блюд, предлагаемых в меню на завтрак, можно сформировать запрос на основе композиции (п. 3.2.4):

Вид Блюдо Основа Выход ‘Номер -‘ БЛ
Закуска Салат витаминный Овощи 200. Номер — 3
Закуска Мясо с гарниром Мясо 250. Номер — 6
Горячее Омлет с луком Яйца 200. Номер — 19
Горячее Пудинг рисовый Крупа 160. Номер — 21
Напиток Молочный напиток Молоко 200. Номер — 31
Напиток Кофе черный Кофе 100. Номер — 32

В п.3.6 можно познакомиться с достаточно полным примером соединения таблиц с различными дополнительными фразами.

3.2.7. Соединение таблицы со своей копией

В ряде приложений возникает необходимость одновременной обработки данных какой-либо таблицы и одной или нескольких ее копий, создаваемых на время выполнения запроса.

Например, при создании списков студентов (таблица Студенты) возможен повторный ввод данных о каком-либо студенте с присвоением ему второго номера зачетной книжки. Для выявления таких ошибок можно соединить таблицу Студенты с ее временной копией, установив в WHERE фразе равенство значений всех одноименных столбцов этих таблиц кроме столбцов с номером зачетной книжки (для последних надо установить условие неравенства значений).

Временную копию таблицы можно сформировать, указав имя псевдонима за именем таблицы во фразе FROM. Так, с помощью фразы

будут сформированы три копии таблицы Блюда с именами X, Y и Z.

В качестве примера соединения таблицы с ней самой сформируем запрос на вывод таких пар блюд таблицы Блюда, в которых совпадает основа, а название первого блюда пары меньше (по алфавиту), чем номер второго блюда пары. Для этого можно создать запрос с одной копией таблицы Блюда (Копия):

или двумя ее копиями (Первая и Вторая):

Получим результат вида

Первая.Блюдо Вторая.Блюдо Основа
Морковь с рисом Помидоры с луком Овощи
Морковь с рисом Салат летний Овощи
Морковь с рисом Салат витаминный Овощи
Помидоры с луком Салат витаминный Овощи
Помидоры с луком Салат летний Овощи
Салат витаминный Салат летний Овощи
Бастурма Бефстроганов Мясо
Бастурма Мясо с гарниром Мясо
Бефстроганов Мясо с гарниром Мясо

3.3. Вложенные подзапросы

3.3.1. Виды вложенных подзапросов

Вложенный подзапрос — это подзапрос, заключенный в круглые скобки и вложенный в WHERE (HAVING) фразу предложения SELECT или других предложений, использующих WHERE фразу. Вложенный подзапрос может содержать в своей WHERE (HAVING) фразе другой вложенный подзапрос и т.д. Нетрудно догадаться, что вложенный подзапрос создан для того, чтобы при отборе строк таблицы, сформированной основным запросом, можно было использовать данные из других таблиц (например, при отборе блюд для меню использовать данные о наличии продуктов в кладовой пансионата).

Существуют простые и коррелированные вложенные подзапросы. Они включаются в WHERE (HAVING) фразу с помощью условий IN, EXISTS или одного из условий сравнения ( = | <> | | >= ). Простые вложенные подзапросы обрабатываютя системой «снизу вверх». Первым обрабатывается вложенный подзапрос самого нижнего уровня. Множество значений, полученное в результате его выполнения, используется при реализации подзапроса более высокого уровня и т.д.

Запросы с коррелированными вложенными подзапросами обрабатываются системой в обратном порядке. Сначала выбирается первая строка рабочей таблицы, сформированной основным запросом, и из нее выбираются значения тех столбцов, которые используются во вложенном подзапросе (вложенных подзапросах). Если эти значения удовлетворяют условиям вложенного подзапроса, то выбранная строка включается в результат. Затем выбирается вторая строка и т.д., пока в результат не будут включены все строки, удовлетворяющие вложенному подзапросу (последовательности вложенных подзапросов).

Следует отметить, что SQL обладает большой избыточностью в том смысле, что он часто предоставляет несколько различных способов формулировки одного и того же запроса. Поэтому во многих примерах данной главы будут использованы уже знакомые нам по предыдущей главе концептуальные формулировки запросов. И несмотря на то, что часть из них успешнее реализуется с помощью соединений, здесь все же будут приведены их варианты с использованием вложенных подзапросов. Это связано с необходимостью детального знакомства с созданием и принципом выполнения вложенных подзапросов, так как существует немало задач (особенно на удаление и изменение данных), которые не могут быть реализованы другим способом. Кроме того, разные формулировки одного и того же запроса требуют для своего выполнения различных ресурсов памяти и могут значительно отличаться по времени реализации в разных СУБД.

3.3.2. Простые вложенные подзапросы

Простые вложенные подзапросы используются для представления множества значений, исследование которых должно осуществляться в каком-либо предикате IN, что иллюстрируется в следующем примере: выдать название и статус поставщиков продукта с номером 11, т.е. помидоров.

Результат:
Название Статус
СЫТНЫЙ рынок
УРОЖАЙ коопторг
ЛЕТО агрофирма
КОРЮШКА кооператив

Как уже отмечалось в п.3.3.1, при обработке полного запроса система выполняет прежде всего вложенный подзапрос. Этот подзапрос выдает множество номеров поставщиков, которые поставляют продукт с кодом ПР = 11, а именно множество (1, 5, 6, 8). Поэтому первоначальный запрос эквивалентен такому простому запросу:

Подзапрос с несколькими уровнями вложенности можно проиллюстрировать на том же примере. Пусть требуется узнать не поставщиков продукта 11, как это делалось в предыдущих запросах, а поставщиков помидоров, являющихся продуктом с номером 11. Для этого можно дать запрос

В данном случае результатом самого внутреннего подзапроса является только одно значение (11). Как уже было показано выше, подзапрос следующего уровня в свою очередь дает в результате множество (1, 5, 6, 8). Последний, самый внешний SELECT, вычисляет приведенный выше окончательный результат. Вообще допускается любая глубина вложенности подзапросов.

Тот же результат можно получить с помощью соединения

При выполнении этого компактного запроса система должна одновременно обрабатывать данные из трех таблиц, тогда как в предыдущем примере эти таблицы обрабатываются поочередно. Естественно, что для их реализации тебуются различные ресурсы памяти и времени, однако этого невозможно ощутить при работе с ограниченным объемом данных в иллюстративной базе ПАНСИОН.

3.3.3. Использование одной и той же таблицы во внешнем и вложенном подзапросе

Выдать номера поставщиков, которые поставляют хотя бы один продукт, поставляемый поставщиком 6.

Результат:
ПС
1
3
5
6
8

Отметим, что ссылка на Поставки во вложенном подзапросе означает не то же самое, что ссылка на Поставки во внешнем запросе. В действительности, два имени Поставки обозначают различные значения. Чтобы этот факт стал явным, полезно использовать псевдонимы, например, X и Y:

Здесь X и Y – произвольные псевдонимы таблицы Поставки, определяемые во фразе FROM и используемые как явные уточнители во фразах SELECT и WHERE. Напомним, что псевдонимы определены лишь в пределах одного запроса.

3.3.4. Вложенный подзапрос с оператором сравнения, отличным от IN

Выдать номера поставщиков, находящихся в том же городе, что и поставщик с номером 6.

Результат:
ПС
1
4
6

В подобных запросах можно использовать и другие операторы сравнения (<>, = или >), однако, если вложенный подзапрос возвращает более одного значения и не используется оператор IN, будет возникать ошибка.

3.3.5. Коррелированные вложенные подзапросы

Выдать название и статус поставщиков продукта с номером 11.

Такой подзапрос отличается от рассмотренного в п.3.3.2 тем, что вложенный подзапрос не может быть обработан прежде, чем будет обрабатываться внешний подзапрос. Это связано с тем, что вложенный подзапрос зависит от значения Поставщики.ПС а оно изменяется по мере того, как система проверяет различные строки таблицы Поставщики. Следовательно, с концептуальной точки зрения обработка осуществляется следующим образом:

  1. Система проверяет первую строку таблицы Поставщики. Предположим, что это строка поставщика с номером 1. Тогда значение Поставщики.ПС будет в данный момент имеет значение, равное 1, и система обрабатывает внутренний запрос получая в результате множество (9, 11, 12, 15). Теперь система может завершить обработку для поставщика с номером 1. Выборка значений Название и Статус для ПС=1 (СЫТНЫЙ и рынок) будет проведена тогда и только тогда, когда ПР=11 будет принадлежать этому множеству, что, очевидно, справедливо.
  2. Далее система будет повторять обработку такого рода для следующего поставщика и т.д. до тех пор, пока не будут рассмотрены все строки таблицы Поставщики.

Подобные подзапросы называются коррелированными, так как их результат зависит от значений, определенных во внешнем подзапросе. Обработка коррелированного подзапроса, следовательно, должна повторяться для каждого значения извлекаемого из внешнего подзапроса, а не выполняться раз и навсегда.

Рассмотрим пример использования одной и той же таблицы во внешнем подзапросе и коррелированном вложенном подзапросе.

Выдать номера всех продуктов, поставляемых только одним по-ставщиком.

Результат:
X.ПР
17
Илон Маск рекомендует:  Бесплатные онлайн-курсы для изучения HTML и CSS

Действие этого запроса можно пояснить следующим образом: «Поочередно для каждой строки таблицы Поставки, скажем X, выделить значение номера продукта (ПР), если и только если это значение не входит в некоторую строку, скажем, Y, той же таблицы, а значение столбца номер поставщика (ПС) в строке Y не равно его значению в строке X».

Отметим, что в этой формулировке должен быть использован по крайней мере один псевдоним — либо X, либо Y.

3.3.6. Запросы, использующие EXISTS

Квантор EXISTS (существует) — понятие, заимствованное из формальной логики. В языке SQL предикат с квантором существования представляется выражением EXISTS (SELECT * FROM . ).

Такое выражение считается истинным только тогда, когда результат вычисления «SELECT * FROM . » является непустым множеством, т.е. когда существует какая-либо запись в таблице, указанной во фразе FROM подзапроса, которая удовлетворяет условию WHERE подзапроса. (Практически этот подзапрос всегда будет коррелированным множеством.)

Рассмотрим примеры. Выдать названия поставщиков, поставляющих продукт с номером 11.

Результат:
Название
СЫТНЫЙ
УРОЖАЙ
КОРЮШКА
ЛЕТО

Система последовательно выбирает строки таблицы Поставщики, выделяет из них значения столбцов Название и ПС, а затем проверяет, является ли истинным условие существования, т.е. су-ществует ли в таблице Поставки хотя бы одна строка со значением ПР=11 и значением ПС, равным значению ПС, выбранному из таблицы Поставщики. Если условие выполняется, то полученное значение столбца Название включается в результат.

Предположим, что первые значения полей Название и ПС равны, соответственно, ‘СЫТНЫЙ’ и 1. Так как в таблице Поставки есть строка с ПР=11 и ПС=1, то значение ‘СЫТНЫЙ’ должно быть включено в результат.

Хотя этот первый пример только показывает иной способ формулировки запроса для задачи, решаемой и другими путями (с помощью оператора IN или соединения), EXISTS представляет собой одну из наиболее важных возможностей SQL. Фактически любой запрос, который выражается через IN, может быть альтернативным образом сформулирован также с помощью EXISTS. Однако обратное высказывание несправедливо.

Выдать название и статус поставщиков, не поставляющих продукт с номером 11.

Результат:
Название Статус
ПОРТОС кооператив
ШУШАРЫ совхоз
ТУЛЬСКИЙ универсам
ОГУРЕЧИК ферма

3.3.7. Функции в подзапросе

Теперь, после знакомства с различными формулировками вложенных подзапросов и псевдонимами легче понять текст и алгоритм реализации запроса (п. 3.1) на получение тех поставщиков продуктов для Сырников, которые поставляют эти продукты за минимальную цену:

Естественно, что это коррелированный подзапрос: здесь сначала определяется минимальная цена продукта, входящего в состав Сырников, и только затем выясняется его поставщик.

На этом примере мы закончим знакомство с вложенными подзапросами, предложив попробовать свои силы в составлении ряда запросов, с помощью механизма таких подзапросов:

  1. Выдать названия всех мясных блюд.
  2. Выдать количество всех блюд, в состав которых входят помидоры.
  3. Выдать блюда, продукты для которых поставляются агрофирмой ЛЕТО.

3.4. Объединение (UNION)

В литературе [2] рассматривалась реляционная операция «Объединение», позволяющая получить отношение, состоящее из всех строк, входящих в одно или оба объединяемых отношения. Но при этом исходные отношения или их объединяемые проекции должны быть совместимыми по объединению. Для SQL это означает, что две таблицы можно объединять тогда и только тогда, когда:

  1. они имеют одинаковое число столбцов, например, m;
  2. для всех i (i = 1, 2, . m) i-й столбец первой таблицы и i-й столбец второй таблицы имеют в точности одинаковый тип данных.

Например, выдать названия продуктов, в которых нет жиров, либо входящих в состав блюда с кодом БЛ = 1:

Результат: Продукт
Майонез
Лук
Помидоры
Зелень
Яблоки
Сахар

Из этого простого примера видно, что избыточные дубликаты всегда исключаются из результата UNION. Поэтому, хотя в рассматриваемом примере Помидоры, Зелень и Яблоки выбираются обеими из двух составляющих предложения SELECT, в окончательном результате они появляются только один раз.


Предложением с UNION можно объединить любое число таблиц (проекций таблиц). Так, к предыдущему запросу можно добавить (перед точкой с запятой) конструкцию

позволяющую добавить к списку продуктов Масло, Рис, Мука и Кофе. Однако тот же результат можно получить простым изменением фразы WHERE первой части исходного запроса

3.5. Реализация операций реляционной алгебры предложением SELECT

С помощью предложения SELECT можно реализовать любую операцию реляционной алгебры [2].

Селекция (горизонтальное подмножество) таблицы создается из тех ее строк, которые удовлетворяют заданным условиям. Пример:

Проекция (вертикальное подмножество) таблицы создается из указанных ее столбцов (в заданном порядке) с последующим исключением избыточных дубликатов строк. Пример:

Объединение двух таблиц содержит те строки, которые есть либо в первой, либо во второй, либо в обеих таблицах. Пример:

Пересечение двух таблиц содержит только те строки, которые есть и в первой, и во второй. Пример:

Разность двух таблиц содержит только те строки, которые есть в первой, но отсутствуют во второй. Пример:

Декартово произведение таблиц и различные виды соединений были подробно рассмотрены в п. 3.2.1-3.2.6.

Здесь опущено лишь достаточно нудное описание редко встречаемой операция деления, которая также может быть реализована предложением SELECT с коррелированными вложенными подзапросами.

3.6. Резюме

Краткое знакомство с возможностями предложения SELECT показало, что с его помощью можно реализовать все реляционные операции. Кроме того, в предложении SELECT выполняются разнообразные вычисления, агрегирование данных, их упорядочение и ряд других операций, позволяющих описать в одном предложении ту работу, для выполнения которой потребовалось бы написать несколько страниц программы на алгоритмических языках Си, Паскаль или на внутренних языках ряда распространенных СУБД.

Например, пусть требуется получить калорийность и стоимость тех блюд, для которых:

  • есть все составляющие их продукты;
  • калорийность не превышает 400 ккал;
  • стоимость не превышает 1.5 рубля, а результат надо упорядочить по возрастанию калорийности блюд в рамках их видов.

Для этого можно дать запрос, показанный на рис. 3.2, позволяющий получить искомый результат в виде таблицы

Вид Блюдо
Горячее Помидоры с луком калорий — 244.6 0.44 руб
Горячее Бефстроганов калорий — 321.3 0.53 руб
Горячее Драчена калорий — 333.9 0.33 руб
Горячее Каша рисовая калорий — 339.2 0.27 руб
Горячее Омлет с луком калорий — 354.9 0.36 руб
Десерт Яблоки печеные калорий — 170.2 0.30 руб
Десерт Крем творожный калорий — 394.3 0.27 руб
Закуска Салат летний калорий — 155.5 0.32 руб
Закуска Салат витаминный калорий — 217.4 0.37 руб
Закуска Творог калорий — 330.0 0.22 руб
Закуска Мясо с гарниром калорий — 378.7 0.62 руб
Напиток Кофе черный калорий — 7.1 0.05 руб
Напиток Компот калорий — 74.4 0.14 руб
Напиток Кофе на молоке калорий — 154.8 0.11 руб
Напиток Молочный напиток калорий — 264.9 0.34 руб
Суп Суп молочный калорий — 396.6 0.22 руб

Рис. 3.2. Пример сложного запроса

Такой результат, нестрого говоря, строился следующим образом.

  1. FROM. Эта фраза инициирует создание в рабочей памяти таблицы, являющейся декартовым произведением таблиц Блюда, Вид_блюд, Состав, Продукты и Наличие.
  2. WHERE. Эта фраза нужна для преобразования полученного декартова произведения в естественное соединение и удаления из последнего строк с кодами блюд, не обеспеченных продуктами. Естественное соединение образуется путем вычеркивания строк, где не совпадают: код блюда из таблицы Блюда с кодом блюда из таблицы Состав, код продукта из таблицы Состав с кодом продукта из таблицы Продукты и т.д. Обеспеченность блюда всеми продуктами проверяется с помощью последовательности подзапросов. Внутренний подзапрос выдает перечень кодов продуктов, которых нет в кладовой пансионата. Следующий подзапрос выдает коды тех блюд, в состав которых должны входить «отсутствующие» продукты. И, наконец, из естественного соединения вычеркиваются строки с кодами полученных блюд (точнее оставляются строки «Где код блюда не принадлежит перечню кодов блюд, полученному в подзапросе».
  3. SELECT. Из полученного соединения удаляются столбцы, не используемые в выражениях SELECT или других фразах. Если в списке SELECT есть выражения (константы), то для хранения их значений формируются дополнительные столбцы и инициируются операции по их заполнению. В рассматриваемом примере будут сохранены столбцы Вид, Блюдо, Белки, Углев, Жиры, Вес, Стоимость, К_во и созданы дополнительные столбцы для формирования и хранения значений стоимости и калорийности составляющих каждого блюда, а также для хранения текстовых констант ‘калорий -‘ и ‘руб’. Обратите внимание на прием, использованный при суммировании стоимостей продуктов, входящих в состав блюда, и стоимости его приготовления (Труд): можно ли заменить MIN на MAX или AVG?
  4. GROUP BY. Отредактированное естественное соединение группируется по видам блюд и их названиям. Создаются группы горячих блюд, десертов и т.д., а внутри каждой группы создаются подгруппы строк со сведениями о продуктах, относящихся к конкретному блюду группы.
  5. SELECT. Каждая подгруппа строк, полученная на предыдущем шаге, преобразуется в единственную строку для результата. В нее заносится вид блюда (общий для всех подгрупп группы), название блюда (общее для всех строк подгруппы), две текстовых константы (‘калорий -‘ и ‘руб’) и две суммы. Последние формируются путем суммирования тех значений дополнительных столбцов, которые принадлежат подгруппе.
  6. HAVING. Сформированные строки, не удовлетворяющие условиям фразы HAVING исключаются из результата предыдущего шага.
  7. ORDER BY. Результат шага 6 упорядочивается в соответствии со списком фразы ORDER BY для получения окончательного результата. Сначала строки группируются по видам блюд (в алфавитном порядке), а затем – по значению элемента данных, указанного на четвертом месте фразы SELECT, т.е. по калорийности.

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

РЕАЛИЗАЦИЯ ОПЕРАЦИЙ РЕЛЯЦИОННОЙ АЛГЕБРЫ ОПЕРАТОРОМ SELECT

С помощью предложения SELECT можно реализовать любую операцию реляционной алгебры.

□ Выборка (горизонтальное подмножество) таблицы создается из тех ее строк, которые удовлетворяют заданным условиям.

SELECT * FROM Clients WHERE City = ‘Тверь’

□ Проекция (вертикальное подмножество) таблицы создается из указанных ее столбцов (в заданном порядке) с последующим исключением избыточных дубликатов строк.

SELECT DISTINCT City FROM Clients

□ Объединение двух таблиц содержит те строки, которые есть либо в первой, либо во второй, либо в обеих таблицах.

SELECT * FROM Clients WHERE Age = (SELECT MIN(Age) FROM Clients) UNION

SELECT * FROM Clients WHERE Age = (SELECT MAX(Age) FROM Clients)

□ Пересечение двух таблиц содержит только те строки, которые есть и в первой, и во второй.

SELECT * FROM Clients WHERE Age IN (SELECT MIN(Age) FROM Clients)

□ Разность двух таблиц содержит только те строки, которые есть в первой, но отсутствуют во второй. Пример:

SELECT * FROM Clients WHERE City NOT IN

(SELECT City FROM Clients GROUP BY City HAVING Count(*)

Допускаются перекрестные (CROSS), естественные (NATURAL) соединения, т.е. соединения по нескольким столбцам с одинаковыми именами, и соединения типа «объединений» (UNION). Соединение UNION и оператор UNION являются различными понятиями. Оператор UNION служит для объединения выходных данных нескольких операторов SELECT.

таблица A CROSS JOIN таблица B

естественно е соединение:

таблица A [NATURAL] [тип соединения] JOIN таблица B

таблица A UNION JOIN таблица B

объединение посредством предиката:

таблица A [тип соединения] JOIN таблица B ON предикат

объединение посредством имен столбцов:

таблица A [тип соединения] JOIN таблица B USING (имя столбца. )

ти п соединения:

Все эти структуры служат для того, чтобы заместить имя таблицы в предложении FROM. Хотя в перечне приведен синтаксис соединений, которые используют только две таблицы, можно соединять любое их количество. Результатом соединения является таблица, которая обрабатывается в качестве исходной остальными операторами запроса.

Ключевое слово OUTER (внешний) не является обязательным, оно употребляется только для уточнения и не используется ни в каких операциях с данными.

CROSS – перекрестное соединение. Это простое декартово произведение. Используются все комбинации строк. Пример перекрестного соединения приведен в таблице 2.2.

INNER – внутреннее соединение. Это тип соединения по умолчанию, оно используется, когда другой тип соединения не задан. Соединяются только те строки, где найдены совпадающие значения столбца. Внутреннее соединение иллюстрируется в таблице 3.

NAME PHONE
Иванов 555-8554
Петров 555-5223
Петров 555-9111
Крылов 555-7894
ID_NUM NAME
1334 Иванов
1399 Петров
1208 Сидоров
ID_NUM A.NAME B.NAME PHONE
1334 Иванов Иванов 555-8554
1399 Петров Иванов 555-8554
1208 Сидоров Иванов 555-8554
1334 Иванов Петров 555-5223
1399 Петров Петров 555-5223
1208 Сидоров Петров 555-5223
1334 Иванов Петров 555-9111
1399 Петров Петров 555-9111
1208 Сидоров Петров 555-9111
1334 Иванов Крылов 555-7894
1399 Петров Крылов 555-7894
1208 Сидоров Крылов 555-7894
ID_NUM NAME PHONE
1334 Иванов 555-8554
1399 Петров 555-5223
1399 Петров 555-9111

LEFT (OUTER) – левое (внешнее). Это соединение включает в себя все строки из таблицы А (совпадающие и несовпадающие) плюс совпадающие значения из таблицы В. Для строк из таблицы А, которым не найдено соответствие, значения NULL заносятся в столбцы, извлекаемые из таблицы В.

Левое внешнее соединение A LEFT OUTER JOIN В

ID_NUM NAME PHONE
1334 Иванов 555-8554
1399 Петров 555-5223
1399 Петров 555-9111
1208 Сидоров NULL

RIGHT (OUTER) – правое (внешнее). Как можно предположить, это соединение является обратным предыдущему. Другими словами, все строки из таблицы В (правой таблицы) представлены в соединении и они дополнены совпадающими строками из таблицы А, в столбцы для строк, не имеющих совпадения, заносятся значения NULL.

FULL (OUTER) – полное (внешнее). Это комбинация левого и правого соединения. Присутствуют все строки из обоих таблиц. Если строки совпадают, то они заполнены реальными значениями. В несовпадающих строках значения столбцов заполняются значениями NULL. Этот тип соединения иллюстрируется в таблице 2.6.

Правое внешнее соединение

A RIGHT OUTER JOIN

Дата добавления: 2020-09-13 ; просмотров: 6 ; ЗАКАЗАТЬ РАБОТУ

Реляционная алгебра. Основы SQL

Традиционные, специальные и дополнительные реляционные операции. Синтаксис и конструкции языка структурированных запросов SQL, типы данных. Запросы, выполняющие реляционные операции вычитания, пересечения и деления. Создание и обслуживание таблиц.

Рубрика Программирование, компьютеры и кибернетика
Вид контрольная работа
Язык русский
Дата добавления 21.06.2020
Размер файла 57,1 K

Отправить свою хорошую работу в базу знаний просто. Используйте форму, расположенную ниже

Студенты, аспиранты, молодые ученые, использующие базу знаний в своей учебе и работе, будут вам очень благодарны.

Размещено на http://www.allbest.ru/

Размещено на http://www.allbest.ru/

Реляционная алгебра. Основы SQL

1. Реляционная алгебра

1.1 Традиционные реляционные операции

1.2 Специальные реляционные операции

1.3 Дополнительные реляционные операции

2.2 Создание и обслуживание таблиц

2.3 Запрос на выборку

2.4 Статистические функции

2.5 Создание соединений

2.6 Вложенные запросы

2.7 Запрос на объединение

2.8 Запросы, выполняющие реляционные операции вычитания, пересечения и деления

2.9 Запросы на изменение


2.10 Перекрестные запросы

1. Реляционная алгебра

Часть реляционной модели, связанная с операторами, основана на реляционной алгебре. Операторы в реляционной алгебре используют отношения в качестве операндов и возвращают отношения в качестве результата. Это реляционное свойство называется свойством замкнутости. Благодаря этому свойству результат одной операции может использоваться в качестве исходных данных для другой. Это, в свою очередь, дает возможность формировать вложенные выражения, т.е., выражения, в которых операнды сами представлены выражениями. Это же свойство обусловливает необходимость принятия правил наследования имен атрибутов для того, чтобы можно было предсказывать имена атрибутов на выходе произвольной реляционной операции. Задав такие правила для всех операций, можно гарантировать, что для выражения любой сложности будет вычисляться результат, имеющий вполне определенный набор атрибутов. Более того, поскольку каждое отношение имеет множество потенциальных ключей, то могут быть ситуации, когда должны быть известны потенциальные ключи для каждого результирующего отношения, т.е., должны быть приняты правила наследования потенциальных ключей.

По определению Е. Кодда, основу реляционной алгебры составляют восемь операторов. Причем, некоторые из них могут выполняться при определенных условиях. Например, в математике объединение двух множеств является множеством всех элементов, принадлежащих или обоим, или одному из исходных множеств. Объединение множества кортежей поставщиков в отношении ПОСТАВЩИКИ и множества кортежей деталей в отношении ДЕТАЛИ является множеством, но не являются отношением — отношения не могут содержать смесь кортежей разных типов. В реляционной алгебре для объединения требуется, чтобы два исходных отношения имели одну и ту же форму, то есть — были совместимы по типу. Два отношения совместимы по типу, если каждое из них имеет одно и то же множество имен атрибутов и соответствующие атрибуты определены на одном и том же домене.

1.1 Традиционные реляционные операции

К традиционным операциям над множествами относятся операторы объединения, пересечения, вычитания и произведения.

Объединением двух совместимых по типу отношений А и В (A UNION B) называется отношение с тем же заголовком, как и в отношениях А и В, и с телом, состоящим из множества всех кортежей t, принадлежащих А или В или обоим отношениям. При этом совпадающие кортежи записываются один раз.

Пересечением двух совместимых по типу отношений А и В (A INTERSECT B) называется отношение с тем же заголовком, как и в отношениях А и В, и с телом, состоящим из множества всех кортежей t, которые принадлежат одновременно обоим отношениям А и В.

Вычитанием двух совместимых по типу отношений А и В (A MINUS B) называется отношение с тем же заголовком, как и в отношениях А и В, и с телом, состоящим из множества всех кортежей t, принадлежащих отношению А и не принадлежащих отношению В.

10 простых шагов к полному пониманию SQL

Очень многие программисты смотрят на SQL, как на страшного зверя. SQL — один из немногих декларативных языков, и его поведение в корне отличается от того, как работают императивные, объектно-ориентированные и даже функциональные языки программирования. Хотя многие рассматривают SQL в некоторой степени функциональным.

Поэтому я чувствую себя обязанным донести прелести SQL до умов тех, кто все еще испытывает трудности с его пониманием. Этот урок посвящен тем, кто:

  • Уже работает с SQL, но не до конца его понимает
  • Неплохо знает SQL, но никогда серьезно не задумывался над его структурой
  • Хочет обучать SQL других

В этом руководстве мы сосредоточимся только на операторах SELECT. Другие DML-операторы будут подробно рассмотрены в следующий раз.


Примечание. Этот урок раньше публиковался только на
Tech.Pro (оригинальная версия находится здесь). К сожалению, материал Tech.Pro стал недоступным. С разрешения Tech.Pro мы снова публикуем его контент в блоге jOOQ.

SQL является декларативным

Все понятно. И вас не волнует, откуда эти записи о сотруднике (employee) берутся. Вам лишь нужны те, у которых достойная зарплата (salary).

  • Что мы из этого узнаем?

Если все так просто, в чем же проблема? Проблема в том, что большинство из нас интуитивно мыслит рамками императивного программирования. Вроде: «машина, сделай это, затем то, но сначала проверь то и се.» А значит, нужно хранить временные результаты в переменных, составлять циклы, вызывать функции и т. д. и т. п.

Забудьте обо всем этом. Думайте о том, как объявлять вещи. Не о том, как сказать машине, вычислить что-либо.

2. Код SQL не является упорядоченным

Обычно путаница происходит из-за того, что элементы кода SQL не расположены в том порядке, в каком они выполняются. Лексический порядок выглядит так:

  • SELECT [ DISTINCT ]
  • FROM
  • WHERE
  • GROUP BY
  • HAVING
  • UNION
  • ORDER BY

Для простоты перечислены не все предложения SQL. Этот словесный порядок принципиально отличается от логического порядка (который в свою очередь может отличаться от порядка выполнения в зависимости от выбора оптимизатора):

Три пункта, которые следует отметить:

  1. Первое предложение здесь FROM, а не SELECT. Сначала происходит загрузка данных с диска в память, чтобы с ними можно было работать.
  2. SELECT выполняется после большинства других предложений. Главное, после FROM и GROUP BY. Это важно понимать, если думаете, что можете ссылаться на элементы, которые объявляете в предложении SELECT из предложения WHERE. Следующее невозможно:

Есть два варианта повторно использовать z. Либо повторить выражение:

… либо прибегнуть к производным таблицам, общим табличным выражениям или представлениям данных, чтобы избежать повторения кода. Смотрите примеры ниже.

3. UNION ставится перед ORDER BY в лексическом и логическом порядках. Многие думают, что каждый подзапрос UNION можно упорядочить, но по стандарту SQL и в большинстве диалектов SQL это не так. Хотя в некоторых диалектах позволяется упорядочивание подзапросов или производных таблиц, нет никакой гарантии, что такой порядок будет сохранен после выполнения операции UNION.

Обратите внимание, что не все базы данных реализуют вещи одинаковым образом. Правило номер 2, например, не применяется в точности, как описано выше, для MySQL, PostgreSQL, и SQLite.

Что мы из этого узнаем?

Чтобы избежать распространенных ошибок, всегда следует помнить о лексическом и логическом порядках предложений SQL. Если вы усвоили эти отличия, становится понятным, почему одни вещи работают, другие нет.

Конечно, неплохо, если бы язык был спроектирован таким образом, когда лексическая последовательность фактически отражает логический порядок, как это реализовано в Microsoft LINQ.

3. Вся суть SQL заключается в ссылках на таблицы

Из-за разницы между лексическим упорядочением и логическим упорядочением большинство новичков, вероятно, думают, что значения столбцов являются объектами первого класса в SQL. Но, нет. Наиболее важными являются ссылки на таблицы.

Стандарт SQL определяет предложение FROM следующим образом:

Вывод предложения FROM — это объединенная ссылка на таблицу объединенного значения всех ссылок. Попробуем это переварить.

Указанная выше запись производит комбинированную ссылку на таблицу значений a и b. Если a имеет 3 колонки и b 5 колонок, тогда выходная таблица “output table” будет состоять из 8 (3 + 5) столбцов.

Записи, содержащиеся в этой комбинированной табличной ссылке являются перекрестным / декартовым произведением a x b. Иными словами, каждая запись a образует пару с записью b. Если a имеет 3 записи, а b 5 записей, описанная выше ссылка на таблицу производит 15 записей (3 x 5).

Этот вывод переводится в предложение GROUP BY (после фильтрации в предложении WHERE), где преобразуется в новый вывод. Разберемся с этим позже.

Если мы посмотрим на эти примеры в свете реляционной алгебры / теории множества, то таблица SQL — это отношение или набор кортежей. Каждое предложение SQL преобразует одно или несколько отношений, чтобы создать новые отношения.

Что мы из этого узнаем?

Всегда думайте категориями ссылок на таблицы, чтобы понять, как данные передаются в предложениях SQL.

4. Ссылки на таблицы SQL могут быть довольно функциональными

Ссылка на таблицу — мощная штука. Примером их силы является ключевое слово JOIN, которое в действительности не является выражением SELECT, а частью специальной ссылки на таблицу. Объединенная таблица, как определено в стандарте SQL (упрощенный):

Вернемся к нашему примеру:

a может по сути быть объединенной таблицей:

Развивая предыдущее выражение, получим:

Хотя не рекомендуется объединять синтаксис, где приводится разделенный запятыми список ссылок на таблицы, с синтаксисом объединенных таблиц, но так можно делать. В результате, объединенная ссылка будет содержать величины a1+a2+b.

Производные таблицы еще мощнее, чем объединенные таблицы. Мы до этого еще дойдем.

Что мы из этого узнаем?

Всегда нужно думать категориями ссылок на таблицы. Это поможет не только понять, как данные переходят в предложениях SQL, (смотрите предыдущий раздел), но и как создаются сложные ссылки на таблицы.

И, что важно, понять, JOIN является ключевым словом для построения соединенных таблиц. А не частью оператора SELECT. Некоторые базы данных позволяют использование JOIN в операторах INSERT, UPDATE, DELETE

5. В SQL следует использовать таблицы JOIN, вместо разделенных запятыми

Ранее мы видели это предложение:

Продвинутые разработчики SQL, вероятно, скажут, что не следует вообще использовать список с разделителями-запятыми, и всегда отображать таблицы JOIN. Это улучшит читаемость инструкции SQL, и тем самым предотвратить ошибки.

Одна очень распространенная ошибка заключается в том, чтобы где-то забыть предикат JOIN. Подумайте о следующем:

Синтаксис соединенных таблиц:

  • Безопаснее, так как предикаты join можно размещать вблизи соединенных таблиц, тем самым предотвращая ошибки.
  • Более выразительные, так как можно различать OUTER JOIN, INNER JOIN и т. д.

Что мы из этого узнаем?

Всегда применяйте JOIN. Никогда не используйте ссылки на таблицы, разделенные запятыми, в предложениях FROM.

6. Различные операции JOIN в SQL

Операции JOIN состоят, в основном, из следующих пяти видов:

  • EQUI JOIN
  • SEMI JOIN
  • ANTI JOIN
  • CROSS JOIN
  • DIVISION

Эти термины обычно используются в реляционной алгебре. SQL задействует другие термины для вышеуказанных понятий, если они вообще существуют. Давайте рассмотрим их поближе:

EQUI JOIN

Это наиболее распространенная операция JOIN. Содержит два подвида:

  • INNER JOIN (или просто JOIN)
  • OUTER JOIN (далее подразделяется на LEFT, RIGHT, FULL OUTER JOIN)

Разницу лучше объяснить на примере:

SEMI JOIN

Эта реляционная концепция в SQL может быть выражена двумя способами: С помощью предиката IN или с использованием предиката EXISTS. «Semi» на латыни означает «половина». Этот тип соединения используется для объединения только «половины» ссылки на таблицу. Что это значит? Рассмотрим вновь вышеуказанное объединение автора и книги. Представим, что нам не нужны комбинации автор — книга, а только авторы, у которых есть книги. Тогда можно написать:

Хотя нет общего правила, следует предпочесть IN или EXISTS, но можно сказать примерно так:

  • Предикаты IN легче читать, чем предикаты EXISTS
  • Предикаты EXISTS более выразительные, чем предикаты IN (т. е. их проще выразить, чем сложные предикаты SEMI JOIN)
  • В производительности нет какой-либо заметной разницы. Но большая разница в производительности может быть в некоторых базах данных.

Поскольку INNER JOIN также выдают результаты авторов, у которых есть книги, многие начинающие подумают, что можно удалить дубликаты с помощью ключевого слова DISTINCT. Им кажется, что SEMI JOIN можно выразить следующим образом:


Так делать не рекомендуется по двум причинам:

  • Замедляется производительность, так как база данных только для того, чтобы удалить дубликаты, должна загрузить в память множество данных.
  • Такой способ решения не совсем правильный, хотя и приводит к верным результатам в этом примере. Но если вы соединяете больше ссылок на таблицы, удаление дубликатов превращается в реальную проблему.

Подробную информацию о злоупотреблении DISTINCT можно найти в этом посте.

ANTI JOIN

Эта реляционная концепция является полной противоположностью SEMI JOIN. Ее можно образовать, просто добавив ключевое слово NOT в предикатах IN или EXISTS. Пример, где мы выберем тех авторов, у которых нет книг:

Применяются те же правила в отношении производительности, читаемости и выразительности. Однако есть небольшой нюанс в отношении NULL при использовании NOT IN, но эта тема выходит за рамки нашего урока.

CROSS JOIN

Этот оператор создает перекрестное произведение двух соединенных ссылок на таблицу, комбинируя каждую запись первой ссылки с записью второй ссылки на таблицу. Мы уже видели раньше, что это может быть достигнуто посредством ссылок на таблицы с разделителями-запятыми в предложении FROM. В редких случаях, когда это действительно необходимо, в большинстве диалектов SQL перекрестное соединение CROSS JOIN можно написать явным образом:

DIVISION

Реляционное деление — вот кто настоящий зверь. В кратце, если JOIN это умножение, division (деление) — противоположность операции JOIN. Реляционные деления очень трудно выразить в SQL. Поскольку этот урок для начинающих, деление мы не рассматриваем. Для самых храбрых информация находится здесь, здесь и здесь.

Что мы из этого узнаем?

Много чего. Давайте запихнем это в наши головы. В SQL повсюду ссылки на таблицы. Соединяемые таблицы представляют собой довольно сложные ссылки на таблицы. Но между реляционными выражениями и выражениями SQL есть разница. Не все операции реляционного соединения также являются формальными операциями соединения SQL. Имея немного практики и знаний в реляционной теории, вы всегда сможете выбрать верный тип реляционного соединения JOIN и правильно представить его на языке SQL.

7. Производные таблицы SQL похожи на табличные переменные

Раньше мы узнали, что SQL является декларативным языком, и не имеет переменных (хотя в некоторых диалектах SQL они существуют). Но можно написать нечто похожее на переменные. Эти звери называются производными таблицами.

Производная таблица — это не что иное, как вложенный запрос, заключенный в круглые скобки.

Обратите внимание, что некоторые диалекты SQL требуют, чтобы производные таблицы имели корреляционное имя (также называемое псевдонимом).

Производные таблицы великолепны, если необходимо обойти проблемы, вызванные логическим упорядочением предложений SQL. Например, если вы хотите повторно использовать выражение столбца в предложении SELECT и WHERE, просто напишите (диалект Oracle):

Обратите внимание, что некоторые базы данных и стандарт SQL:1999 подняли производные таблицы на следующий уровень введением обобщенных табличных выражений. Это позволит повторно использовать одну и ту же производную таблицу несколько раз в одной инструкции SQL SELECT. Приведенный выше запрос будет переведен на (почти) эквивалент:

Очевидно, что «a» можно также вывести в отдельное представление для более широкого использования общих подзапросов SQL. Подробнее о представлениях здесь.

Что мы из этого узнаем?

Снова, снова и снова. В SQL все завязано на ссылках, а не столбцах. Как этим воспользоваться. Не бойтесь написания производных таблиц или других сложных ссылок на таблицы.

8. SQL GROUP BY преобразует прежние ссылки на таблицу

Давайте вновь обсудим наше предложение FROM:

А теперь к соединенной ссылке (см. выше) применим предложение GROUP BY

Предложение производит новую ссылку на таблицу только с тремя оставшимися столбцами (!). Попробуем это переварить. С применением GROUP BY, уменьшается число доступных столбцов во всех последующих логических предложениях, включая SELECT. Ссылаться только на столбцы из предложения GROUP BY в предложении SELECT можно по синтаксической причине.

  • Обратите внимание, что другие столбцы могут по-прежнему быть доступными в качестве аргументов агрегатных функций:
  • Следует заметить, что MySQL, к сожалению, не придерживается этого стандарта, отчего получается только путаница. Не попадайтесь на уловки в MySQL. GROUP BY преобразует ссылки на таблицу Таким образом, можно ссылаться только на столбцы, также упоминаемые в предложении GROUP BY.

Что мы из этого узнаем?

GROUP BY, опять же, работает с табличными ссылками, преобразовывая их в новую форму.

9. SQL SELECT в реляционной алгебре называется проекцией

Мне лично нравится термин «проекция», так как он используется в реляционной алгебре. После того как вы создали ссылку на таблицу, отфильтровали и преобразовали ее, можете переходить к проецированию в другую форму. Предложение SELECT подобно проектору. Табличная функция использующет выражение значения строки для преобразования каждой записи из ранее созданной ссылки на таблицу в конечный результат.

В предложении SELECT можно работать со столбцами, создавая сложные выражения столбцов как части записи/строки.

Есть много специальных правил в отношении характера доступных выражений, функций и т.д. Главное, нужно помнить следующее:

  1. Можно использовать только ссылки на столбцы, полученные из ссылки на таблицу в «output».
  2. Если у вас есть предложение GROUP BY, вы можете ссылаться только на столбцы из этого предложения или агрегатные функции.
  3. Если нет предложения GROUP BY вместо агрегатных можно использовать оконные функции.
  4. Если нет предложения GROUP BY, нельзя сочетать агрегатные и неагрегатные функции.
  5. Существуют некоторые правила, касающиеся переноса регулярных функций в агрегатные функции и наоборот.
  6. Есть…

Много сложных правил. Которыми можно заполнить еще один урок. Например, причина почему нельзя комбинировать агрегатные функции с неагрегатными функциями в проекции инструкции SELECT без предложения GROUP BY (правило № 4), такова:

  1. Это не имеет смысла. Интуитивно.
  2. Если не помогает интуиция (например, новичкам в SQL), выручают синтаксические правила. В SQL:1999 реализован оператор GROUPING SETS, а в SQL:2003 — пустой оператор grouping sets: GROUP BY (). Всякий раз, когда присутствует агрегатная функция и нет явного предложения GROUP BY, применяется неявный пустой GROUPING SET (правило №2). Следовательно, исходные правила о логическом упорядочении больше не являются верными, и проекция (SELECT) влияет на результат логически предшествующего, но лексически последовательного предложения (GROUP BY).

Запутались? Да. Я тоже. Давайте вернемся к более простым вещам.

Что мы из этого узнаем?

Предложение SELECT может быть одним из самых сложных предложений в SQL, даже если оно выглядит просто. Все другие предложения только переносят ссылки на таблицы от одного к другому. Предложение SELECT портит всю красоту этих ссылок, полностью их преобразовывая путем применения к ним правил.

Чтобы понять SQL, перед использованием оператора SELECT нужно усвоить все остальное. Даже если SELECT является первым предложением в лексической упорядоченности, он должен быть последним.

10. SQL DISTINCT, UNION, ORDER BY и OFFSET — намного проще

После сложного SELECT мы можем снова вернуться к простым истинам:

  • Операции Set (DISTINCT и UNION)
  • Операции упорядочивания (ORDER BY, OFFSET .. FETCH)

Операции Set

Операции set работают с наборами, которые на самом деле являются таблицами. Почти. Теоретически, это несложно понять.

  • DISTINCT удаляет дубликаты после проекции.
  • UNION объединяет два подзапроса и удаляет дубликаты
  • UNION ALL объединяет два подзапроса, сохраняя дубликаты
  • EXCEPT удаляет записи из первого подзапроса, которые также содержатся во втором подзапросе (и затем удаляет дубликаты)
  • INTERSECT сохраняет только записи, содержащиеся в обоих поздапросах (а затем удаляет дубликаты)

Удаление дубликатов во всех этих случаях не имеет смысла. Для объединения подзапросов чаще всего следует применять UNION ALL.

Операции упорядочивания

Упорядочение не является реляционной функцией. Это функция, предназначенная только для SQL. Она применяется в самом конце лексического упорядочения и логического упорядочения инструкции SQL. Использование ORDER BY и OFFSET.. FETCH — это единственный способ гарантировать, что записи могут быть доступны по индексу надежным способом. Все остальные способы упорядочивания всегда произвольны и случайны, даже если они могут показаться воспроизводимыми.

OFFSET .. FETCH — это только один вариант синтаксиса. Другие варианты включают LIMIT, OFFSET в MySQL и PostgreSQL .. или TOP и START AT в SQL Server и Sybase. Хороший обзор различных способов реализации OFFSET.. FETCH можно увидеть здесь.

Приступаем к работе

Как и в каждом языке, чтобы освоить SQL, требуется практика. 10 вышеупомянутых простых шагов помогут вам понять обычные операции SQL. С другой стороны, неплохо учиться также на распространенных ошибках.

«Реализация операций реляционной алгебры предложением SELECT С помощью предложения SELECT можно реализовать любую операцию реляционной . »

Реализация операций реляционной алгебры предложением SELECT

С помощью предложения SELECT можно реализовать любую операцию реляционной алгебры.

Селекция (горизонтальное подмножество) таблицы создается из тех ее строк, которые удовлетворяют заданным условиям. Пример:

Проекция (вертикальное подмножество) таблицы создается из указанных ее столбцов (в заданном порядке) с последующим исключением избыточных дубликатов строк. Пример:

SELECTDISTINCT Блюдо, Выход, Основа

Объединение двух таблиц содержит те строки, которые есть либо в первой, либо во второй, либо в обеих таблицах. Пример:

SELECTБлюдо, Основа, Выход

SELECTБлюдо, Основа, Выход

Пересечение двух таблиц содержит только те строки, которые есть и в первой, и во второй. Пример:

SELECTБЛ

WHEREБЛ IN

Разность двух таблиц содержит только те строки, которые есть в первой, но отсутствуют во второй. Пример:

SELECTБЛ

WHEREБЛ NOT IN

Соединение таблицы со своей копией

В ряде приложений возникает необходимость одновременной обработки данных какой-либо таблицы и одной или нескольких ее копий, создаваемых на время выполнения запроса.

Например, при создании списков студентов (таблица Студенты) возможен повторный ввод данных о каком-либо студенте с присвоением ему второго номера зачетной книжки. Для выявления таких ошибок можно соединить таблицу Студенты с ее временной копией, установив в WHERE фразе равенство значений всех одноименных столбцов этих таблиц кроме столбцов с номером зачетной книжки (для последних надо установить условие неравенства значений).

Временную копию таблицы можно сформировать, указав имя псевдонима за именем таблицы во фразе FROM. Так, с помощью фразы

FROM Блюда X, Блюда Y, Блюда Z

будут сформированы три копии таблицы Блюда с именами X, Y и Z.

В качестве примера соединения таблицы с ней самой сформируем запрос на вывод таких пар блюд таблицы Блюда, в которых совпадает основа, а название первого блюда пары меньше (по алфавиту), чем номер второго блюда пары. Для этого можно создать запрос с одной копией таблицы Блюда (Копия):

SELECTБлюдо, Копия.Блюдо, Основа

FROMБлюда, Блюда Копия

«Александр Лекомцев Время создания пьесы: начало 21-го века АДАТМЫРА – НЕЖНОЕ ИМЯ (типичная трагикомедия в двух действиях)Действующие лица: Адатмыра – зелёноволосая женщина неопределённого возраста, как бы, невеста Лёши Лёша – единствен. »

«ПРОЕКТПОСТАНОВЛЕНИЕ КАРАР от » » _ 2013 года №_ О Положении об организации ритуальных услуг, погребении, похоронного дела и содержании кладбищ на территории муниципального образования «город Агрыз» В соответствии с требованиями федеральных законов от 06.10.2003 № 131-ФЗ «Об общих принципах организации местного самоуправлени. »

«ПРОВЕДЕНИЕ ОТКРЫТОГО КОНКУРСА В «АЦК-ГОСЗАКАЗ» Процедура закупки путем проведения конкурса – это способ размещения заказа, при котором заказчик проводит конкурсные торги. Победителем признается участник, предложивший лучшие условия исполнения, заключаемого на основе проведения торгов, государственного/муниципального контракта. »

«Все без исключения “энергетики” содержат кофеин.right0 Действие энерготоника длится на протяжении 3–4 часов в отличие от кофе, которого хватает всего на пару часов. Все энерготоники делятся на две категории: в одних больше кофеина, в др. »

«Ведущая: Уже горят огнем рябины кисти, И пожелтели на березах листья, И пенья птиц уже не слышно вовсе, И тихо-тихо к нам приходит осень. 1 ученик: Осень! Славная пора! Любит осень детвора. Сливы, груши, виноград – Все поспело для ребят. 2 ученик: И арбуз увидев важный, Оживится детвора –И радушно скажет каждый: Здра. »

2020 www.docx.lib-i.ru — «Бесплатная электронная библиотека — интернет материалы»

Материалы этого сайта размещены для ознакомления, все права принадлежат их авторам.
Если Вы не согласны с тем, что Ваш материал размещён на этом сайте, пожалуйста, напишите нам, мы в течении 1-2 рабочих дней удалим его.

Илон Маск рекомендует:  Что такое код gettype
Понравилась статья? Поделиться с друзьями:
Кодинг, CSS и SQL