7.4. Команды языка манипулирования данными

Выборка данных из таблицы

Выборка данных из таблицы (таблиц) выполняется командой SELECT. При выполнении команды осуществляется поиск указанной таблицы или таблиц, извлечение данных, соответствующих условию отбора, их группировка или сортировка в указанном порядке и возвращение в виде таблицы запроса. Команда SELECT не изменяет данные в базе данных.

Команда имеет синтаксис:

SELECT [предикат] список_столбцов

FROM имена_таблиц

[WHERE условие_отбора]

[GROUP BY критерий_группировки ]

[HAVING критерий_отбора]

[ORDER BY критерий_столбца]

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

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

SELECT [ALL|DISTINCT] [TOP n [PERCENT][WITH TIES]] список_столбцов

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

1. * - что означает выбрать все столбцы из указанной таблицы. Например, вывести сведения обо всех факультетах университета:

SELECT * FROM Факультет²

2. таблица.* | представление.* | псевдоним.* - что означает выбрать все столбцы из таблицы или представления с указанным их имени или псевдонима. Псевдонимом является другое имя таблицы, которое может быть использовано в запросе для ссылки на таблицу. Например, вывести все сведения о группах, с указанием наименования специальностей:

SELECT Факультет.Наименование,Группа.*

FROM Факультет,Группа

WHERE Факультет.[Номер факультета]=

Группа.[Номер факультета]

Если таблицам присвоить псевдонимы, то запрос будет иметь вид:

SELECT f.Наименование, g.*

FROM Факультет f,Группа g

WHERE f.[Номер факультета]=

g.[Номер факультета]

3. {[таблица.]столбец|выражение}[AS псевдоним][,...] - с помощью этого способа можно указать имена столбцов, значение которых требуется получить в результате выполнения команды. Имена столбцов в списке разделяются запятой, порядок их следования в разделе SELECT произвольный. Например, вывести сведения обо всех специальностях университета.

SELECT [Номер специальности],[Наименование специальности], Стоимость

FROM Специальность

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

SELECT [Номер специальности], Стоимость*1.2

FROM Специальность

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

SELECT [Номер специальности], Стоимость *1.2 AS [Стоимость с НДС]

FROM Специальность

В разделе SELECT могут быть указаны предикаты ALL или DISTINCT.

В стандарте предикатами называются условия отбора строк из таблицы.

Предикат – это выражение, которое может быть истинным, ложным или неопределенным.

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

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

SELECT DISTINCT [Номер группы]

FROM Студент

Если в разделе Select список из нескольких столбцов, то Distinct должен предшествовать всем именам столбцов. В результате выборка будет содержать строки с отличными значениями выбранных столбцов.

Аргумент TOP n [PERCENT] указывает на необходимость выбора не всех строк, а только первых n. Например, вывести сведения о пяти студентах из таблицы Студент:

SELECT TOP 5 Фамилия,[Дата рождения]

FROM Студент

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

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

Синтаксис раздела:

FROM таблица [AS псевдоним] | представление [AS псевдоним][,…]

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

SELECT [Номер специальности],

Студент.[Номер группы],Фамилия

FROM Студент,Группа

WHERE Студент.[Номер группы]=

Группа.[Номер группы]

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

Раздел WHERE позволяет задать правило отбора строк из таблиц, перечисленных в разделе FROM для включения их в результат выполнения команды SELECT.

Синтаксис раздела:

WHERE условие_отбора

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

Стандартом определены следующие предикаты (условия отбора):

предикат сравнения – проверяемое значение сравнивается со значением выражения;

предикат «между» - проверяется, попадает ли значение в указанный диапазон;

предикат «в» - проверяется, совпадает ли значение с одним из значений заданного множества;

предикат «как» - проверяется, соответствует ли строковое значение заданному шаблону;

«NULL – предикат» - проверяется, не содержится ли в столбце значение NULL;

NULL – это специальное значение, представляющее собой отсутствие любого значения. Стандарт ANSY/ISO определяет, что результатом сравнения любого значения со значением NULL не могут быть такие значения как TRUE или FALSE. Результатом сравнения будет UNKNOWN (неизвестно).

«предикат существования» - проверяется, получены ли в результате работы подзапроса одна или несколько строк.

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

Пример: Вывести сведения о студентах, обучающихся в группе 1191.

SELECT * FROM Студент

WHERE [Номер группы]=1191

Условие отбора может быть задано сложным логическим выражением, в котором простые логические условия связанны логическими операторами, такими как NOT, AND и OR.

Пример: Вывести сведения о коммерческих студентах, обучающихся в группе 1191.

SELECT * FROM Студент

WHERE Коммерческий = ‘Да’ AND [Номер группы]=1191

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

Пример: Вывести сведения о коммерческих студентах, обучающихся в группах 1191 и 1192.

SELECT * FROM Студент

WHERE Коммерческий = ‘Да’ AND ([Номер группы]=1191 OR [Номер группы]=1192)

Предикат «между»записывается с помощью оператора BETWEEN.ОператорBETWEENпроверяет, попадает ли значение в указанный диапазон

Синтаксис предиката:

выражение [NOT] BETWEEN значение₁ AND значение₂

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

Пример: Вывести сведения о тех студентах, фамилии которых попадают в заданный алфавитный диапазон.

SELECT * FROM Студент

WHERE Фамилия BETWEEN 'К' AND 'Н'

Пример: Вывести сведения о факультетах, имеющих номера с 1 до 5.

SELECT * FROM Факультет

WHERE [Номер факультета] BETWEEN 1 AND 5

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

выражение [NOT] IN {(значение₁, значение₂ [,…])| подзапрос}

Пример: Вывести сведения о студентах, родившихся зимой.

SELECT [Номер группы],Фамилия, [Дата рождения]

FROM Студент

WHERE MONTH([Дата рождения]) IN (12,1,2)

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

С помощью проверки NOT INможно убедиться в том, что данное значение не принадлежит указанному списку значений. Максимальное количество элементов в списке стандартом не определено.

Предикат «как»реализуется в языке оператором LIKE.

Оператор LIKEвыполняет проверку строкового значения (типChar,VarChar) на соответствие шаблону. Синтаксис оператора:

выражение [NOT] LIKE шаблон [ESCAPE символ_пропуска]

Шаблон строится с помощью обычных и подстановочных символов. Рассмотрим назначение подстановочных символов:

• % - вместо этого символа может быть подставлено любое количество символов. Например, чтобы найти всех студентов, фамилии которых начинаются на ‘А’, следует задать логическое условие:

WHERE Фамилия LIKE 'А%'

• _ – заменяет один символ. Например, чтобы найти всех студентов, в фамилиях которых вторая буква ‘е’, следует задать логическое условие:

WHERE Фамилия LIKE '_е%'

• [ список_символов] – заменяет один символ. В данной позиции шаблона может находиться любой символ из списка. Список допустимых символов можно задавать диапазоном. Например, условию Фамилия LIKE '[АБ]%' будут соответствовать фамилии, начинающиеся на ‘А’ и ‘Б’.

Условию Фамилия LIKE '[К-М]%' будут соответствовать фамилии, начинающиеся на ‘К’, ‘Л’ и ‘М’.

• [^список_символов] – заменяет один символ. В данной позиции шаблона может находиться любой символ, кроме символов указанных в списке.

Например, условию Фамилия LIKE '[^АБ]%' будут соответствовать фамилии, не начинающиеся на ‘А’ и ‘Б’.

Может оказаться, что подстановочные символы входят в проверяемую строку. В стандарте определен способ проверки наличия в строке символов, используемых в качестве подстановочных. Для этого применяются символы пропуска. Когда в шаблоне встречается символ пропуска, то символ, следующий за ним, считается не подстановочным, а обычным символом. Символ пропуска определяется после ключевого слова ESCAPE.

Например: НДС LIKE ‘__#%’ ESCAPE ’#’

Иногда бывает необходимо явно проверить, имеет ли значение столбец. Синтаксис «NULL – предиката»:

выражение IS [NOT] NULL

Проверка на NULL не может возвращать в качестве результата значение NULL, она будет возвращать значение TRUE или FALSE. Например, вывести сведения о тех факультетах, для которых сведения о деканах неизвестны.

SELECT * FROM Факультет

WHERE Декан IS NULL

Нельзя сравнивать значение столбца на равенство NULL с помощью операции сравнения «=», поскольку NULL не является значением, это сигнал о том, что значение неизвестно.

Раздел ORDER BY позволяет задать сортировку результатов запроса. Синтаксис раздела:

ORDER BY критерий_столбца [{ASC|DESC}][,…]

Критерий_столбца- это имя столбца, по значениям которого будет упорядочена таблица результатов запроса. С помощью ключевых словASCиDESCможно указать порядок сортировки (ASC– по возрастанию значений,DESC– по убыванию значений). По умолчанию выполняется сортировка по возрастанию значений. Сортировка может выполняться по значениям нескольких столбцов. Стандарт позволяет управлять порядком сортировки отдельно по каждому столбцу.

Пример: Найти сведения о студентах групп 1191 и 1192 и отсортировать их по возрастанию номеров групп, а внутри групп по убыванию дат рождения студентов.

SELECT *

FROM Студент

WHERE [Номер группы]=1191 or [Номер группы]=1192

ORDER BY [Номер группы],[Дата рождения]DESC

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

SELECT [Наименование специальности],Стоимость

FROM Специальность

ORDER BY 2 DESC

Раздел ORDER BY должен быть последним в команде SELECT, так как результатом сортировка является не реляционная таблица (отношение), а некоторая последовательность строк.

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

1. Выбирается таблица, указанная в разделе FROM.

2. Если имеется раздел WHERE, то условие отбора применяется к каждой строке таблицы. Те строки, для которых условие отбора равно TRUE включаются в таблицу результатов запроса, строки для которых условие отбора равно FALSE или NULL – отбрасываются.

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

4. Если в разделе SELECT присутствует предикат DISTINCT, то из таблицы результатов запроса удаляются все повторяющиеся строки.

5. Если имеется раздел ORDER BY, то выполняется сортировка строк таблицы результатов запроса.

Выборка данных из таблицы с группировкой

Раздел GROUP BYуказывает на необходимость группировки строк таблиц по определенным критериям для выполнения вычислений над значениями строк таблицы попавшими в группу. Синтаксис раздела:

GROUP BY критерий_группировки

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

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

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

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

Функция AVG() – вычисляет среднее значение для группы значений. Синтаксис функции:

AVG ([выражение | DISTINCT имя_столбца])

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

Функция COUNT() – подсчитывает количество значений в группе. Синтаксис функции:

COUNT ({[DISTINCT] имя_столбца | * })

Если аргумент функции имя столбца, то функция подсчитывает количество непустых значений в группе, если аргумент ‘*’, то функция подсчитывает общее количество строк независимо от того, содержат ли они значения NULL или нет.

Пример: Определить количество студентов в каждой группе.

SELECT [Номер группы], COUNT(Фамилия)

FROM Студент

GROUP BY [Номер группы]

Функция SUM() – выполняет суммирование всех значений указанного столбца для каждой группы. Синтаксис функции:

SUM ([выражение | DISTINCT имя_столбца])

Функция MAX() – возвращает максимальное значение столбца в каждой группе. Синтаксис функции:

MAX (выражение)

Функция MIN() – возвращает минимальное значение столбца в каждой группе. Синтаксис функции:

MIN (выражение)

Пример: Определить год рождения самого молодого студента в каждой группе.

SELECT [Номер группы], MIN(YEAR([Дата рождения])) AS [Год рождения]

FROM Студент

GROUP BY [номер группы]

Выборка данных с группировкой выполняется по следующим правилам:

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

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

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

Пример: Для каждого факультета определить, сколько групп на каждом курсе.

SELECT [Номер факультета], Курс, COUNT([Номер группы]) AS [Количество групп]

FROM Группа

GROUP BY [Номер факультета], Курс

Последовательность имен столбцов в разделе Group by не обязательно должна соответствовать последовательности имен в списке Select.

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

Совместно с разделом GROUP BYкомандаSELECTможет содержать разделHAVING для задания условия отбора групп строк. Синтаксис раздела:

HAVING критерий_отбора

Критерий_отбора- это логическое условие, определяющее строки каких групп включать в таблицу результатов запроса.

Пример: Определить номера тех групп, в которых меньше 25 человек.

SELECT [Номер группы], COUNT(Фамилия)AS [Количество студентов]

FROM Студент

GROUP BY [Номер группы]

HAVING COUNT(Фамилия)< 25

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

Выборка данных из нескольких таблиц

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

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

SELECT [Наименование специальности], Группа.*

FROM Специальность, Группа

Соединение таблиц – это процесс формирования пар строк путем сравнения содержимого соответствующих столбцов.

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

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

Для соединения таблиц можно использовать две различные формы:

- явный синтаксис соединения с помощью оператора join, указанного в разделеSelect;

- неявный синтаксис соединения, когда условия каждой операции соединения определяется неявно в разделе WHERE.

Синтаксис неявного соединения таблиц:

SELECT список_столбцов

FROM таблица₁ , таблица₂

WHERE таблица₁.столбец₁ оператор таблица₂.стлолбец₁

В качестве условия отбора в разделе WHEREзадается условие, основанное на сравнении значений столбцов из двух таблиц. Если при этом выполняется проверка на равенство значений сравниваемых столбцов, то выполняется внутреннее соединение двух таблиц.

В нашем примере связь должна осуществляться по равенству значений номеров специальностей. Тогда запрос будет иметь вид:

SELECT [Наименование специальности], g.*

FROM Специальность s, Группа g

WHERE s.[Номер специальности]=

g.[Номер специальности]

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

Соединение таблиц может выполнять и по нескольким столбцам. В этом случае условие соединения задается следующим образом:

SELECT список_столбцов

FROM таблица₁ , таблица₂

таблица₁.столбец₁ оператор таблица₂.столбец₁ AND

таблица₁.столбец₂ оператор таблица₂.столбец₂)

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

SELECT список_столбцов

FROM таблица₁ , таблица₂ , таблица₃

WHERE таблица₁.столбец₁ оператор таблица₂.стлолбец₁ AND таблица₂.столбец₂ оператор таблица₃.столбец₂

Пример: Сформировать список студентов с указанием наименований специальностей, на которых они обучаются:

SELECT [Наименование специальности], с.*

FROM Специальность s, Группа g, Студент c

WHERE s.[Номер специальности]=

g.[Номер специальности] AND

g.[Номер группы]=

c.[Номер группы]

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

SELECT [Наименование специальности], с.*

FROM Специальность s, Группа g, Студент c

WHERE s.[Номер специальности]=

g.[Номер специальности] AND

g.[Номер группы]=c.[Номер группы] AND c.[Номер группы]=1191

Синтаксис неявного соединения является синтаксисом «старого стиля». Как видно из примера раздел WHERE содержит достаточно сложное условие, что усложняет анализ текста запроса при его отладке, поэтому рекомендуется использовать явный синтаксис соединения.

Соединение таблиц в разделе FROM

Явный синтаксис соединение таблиц имеет вид:

FROM таблица₁ [{INNER|{LEFT|RIGHT|FULL} [OUTER]} JOIN таблица₂ ON таблица₁.столбец₁ оператор таблица₂.столбец₂

INNER JOINуказывает, что возвращаются все совпадающие пары строк. Отмена несовпадающих строк из обеих таблиц. Если тип соединения не указан, этот тип задается по умолчанию.

Пример. Подсчитать количество групп на каждой специальности.

SELECT [Наименование специальности],

COUNT([Номер группы])

FROM Специальность s INNER JOIN Группа g ON s.[Номер специальности]= g.[Номер специальности]

GROUP BY [Наименование специальности]

Разделы JOINмогут быть вложенными, в таком случае будет выполняться множественное соединение.

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

SELECT [Наименование специальности],

COUNT(Фамилия)

FROM Студент c INNER JOIN Группа g ON

c.[Номер группы]=g.[Номер группы]

INNER JOIN Специальность s ON

g.[Номер специальности]=

s.[Номер специальности]

GROUP BY [Наименование специальности]

LEFT [OUTER] JOIN используется для создания левого внешнего соединения, при котором все строки из левой таблицы, не соответствующие условиям соединения, включаются в результирующий набор, а выходные столбцы из оставшейся таблицы устанавливаются в значение NULL в дополнение ко всем строкам, возвращаемым внутренним соединением.

- RIGHT [OUTER] JOINуказывает выполнять правое внешнее соединение, при котором все строки из правой таблицы, не соответствующие условиям соединения, включаются в результирующий набор, а выходные столбцы, соответствующие оставшейся таблице, устанавливаются в значение NULL в дополнение ко всем строкам, возвращаемым внутренним соединением.

- FULL [OUTER] JOIN– указывает, что в результирующий набор включаются строки как из левой, так и из правой таблицы, несоответствующие условиям соединения, а выходные столбцы, соответствующие оставшейся таблице, устанавливаются в значение NULL. Этим дополняются все строки, обычно возвращаемые при помощи INNER JOIN.

Примечание. Операции LEFT JOIN или RIGHT JOIN могут быть вложены в операцию INNER JOIN, но операция INNER JOIN не может быть вложена в операцию LEFT JOIN или RIGHT JOIN.

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

SELECT [Наименование специальности],

[Номер группы]

FROM Специальность LEFT JOIN Группа ON

Специальность.[Номер специальности]=

Группа.[Номер специальности]

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

Подзапросы

SQL позволяет вкладывать запросы друг в друга. Такой механизм позволяет использовать результаты одного запроса в качестве составной части другого.

Подзапрос (подчиненный запрос) – это запрос, содержащийся в предложении WHERE, HAVING или FROMдругого (основного) запроса.

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

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

Пример: Сформировать список тех групп, номер факультета которых соответствует факультету «Экономика и управление на предприятиях машиностроения».

SELECT [Номер группы]

FROM Факультет f INNER JOIN Группа g

ON f.[Номер факультета]=

g.[ Номер факультета]

WHERE Наименование = ’Экономика и управление на предприятиях машиностроения’

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

SELECT [Номер группы]

FROM Группа

WHERE [Номер факультета] =

(SELECT [Номер факультета]

FROM Факультет

WHERE Наименование = ’Экономика и управление на предприятиях машиностроения’)

Как видно из примера структура запроса полностью соответствует его описанию.

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

Подзапрос всегда заключается в круглые скобки. В качестве подзапроса может использоваться только команда SELECT, содержащая разделFROMи необязательные разделыWHERE,GROUPBYиHAVING.

Команда SELECTв подзапросе:

- может возвращать таблицу результатов, состоящую только из одного значения столбца, или из столбца значений, или из нескольких столбцов;

- не должна содержать раздел ORDER BY. Так как результат подзапроса используется основным запросом, то не имеет смысла его сортировать;

- имена столбцов подзапроса могут являться ссылками на столбцы основного запроса.

Подзапрос не может быть запросом на объединение (UNION) нескольких командSELECT.

Подзапросы в разделе WHERE

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

выражение оператор_сравнения подзапрос

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

Пример: Вывести сведения о тех группах, студенты которых обучаются на специальности «Управление качеством».

SELECT *

FROM Группа

WHERE [Номер специальности] =

(SELECT [Номер специальности]

FROM Специальность

WHERE [Наименование специальности]= ’Управление качеством’)

Подзапрос находит номер специальности «Управление качеством». Для каждой строки таблицы ГруппаНомер специальности сравнивается со значением, возвращенным подзапрос. Если сравниваемые значения равны, то данные из строкиГруппапомещаются в таблицу результатов запроса. Если результатом выполнения данного подзапроса будет несколько строк, то сравнение невозможно и СУБД выдаст ошибку. Если в результате выполнения подзапроса не будет получено ни одной строки или будет полученоNULL, то операция сравнения вернет значениеNULL.

В операции сравнения можно использовать все шесть операторов сравнения: >, <, =, >=, <=, <>.

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

выражение [NOT] IN подзапрос

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

SELECT *

FROM Специальность

WHERE [Номер специальности] IN

(SELECT DISTINCT[Номер специальности]

FROM Группа

WHERE [Номер курса]=1)

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

Критерий отбора может выполнять проверку на существование данных подзапроса. Синтаксис проверки на существование:

NOT EXISTS подзапрос

Оператор EXISTSпроверяет, содержится ли в таблице результатов подзапроса хотя бы одна строка. Если результат подзапроса содержит хотя бы одну строку, то оператор возвращает значениеTRUE, в противном случаеFALSE.

Пример: Создать запрос, выводящий наименование специальности 220501, если по этой специальности есть сведения в таблицеГруппа.

SELECT [Наименование специальности]

FROM Специальность

WHERE [Номер специальности]=220501 AND EXISTS

(SELECT [Номер группы] FROM Группа

WHERE [Номер специальности]=220501)

Подзапрос ищет номера групп в таблице Группа, принадлежащие специальности 220501. Если такие группы найдены, то операторEXISTSвернет значениеTRUE, иначе FALSE.Для строки таблицыСпециальностьосновного запрос вычисляется логическое выражение. Только в том случае, если номер специальности 220501 и результат подзапросаTRUE, строка таблицыСпециальностьбудут помещена в таблицу результатов запроса.

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

Синтаксис многократного сравнения:

выражение операция_сравнения {ANY|ALL} подзапрос

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

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

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

SELECT [Номер группы], Фамилия

FROM Студент

WHERE [Номер группы] = ANY

(SELECT [Номер группы] FROM Группа

WHERE [Номер специальности]=220501)

Уровни вложенности подзапросов

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

SELECT [Номер группы],Фамилия,

[Номер зачетной книжки]

FROM студент WHERE [Номер группы] IN

(SELECT [Номер группы] FROM Группа

WHERE [Номер специальности]=

(SELECT [Номер специальности]

FROM Специальность

WHERE [Наименование специальности]=

‘Управление качеством’))

Это пример «трехуровневого» запроса. Вложенный подзапрос возвращает номер специальности «Управления качеством». Подзапрос основного запроса, используя это значение, находит и возвращает номера групп этой специальности. Основной запрос включает в таблицу результатов сведения о тех студентах, номера групп которых равны одному из возвращенных подзапросом значений.

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

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

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

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

SELECT [Номер группы] FROM Группа g

WHERE NOT EXISTS

(SELECT * FROM Студент s

WHERE s.[Номер группы]=g.[Номер группы])

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

Процедура выполнения приведенного выше запроса следующая. Выбирается первая строка таблица Группа. Выполняется подзапрос, и из таблицы Студент отбираются только те строки, которые в столбце Номер группы имеют такое же значение что и Номер группы из таблицы Группа. Затем вычисляется предикат NOT EXISTS основного запроса для первой строки таблицы Группа. Если подзапрос не нашел в таблице Студент записей с указанным номером группы, то предикат имеет значение TRUE и Номер группы записывается в таблицу результатов. Если записи в таблице Студент найдены, тогда предикат имеет значение FALSE и в таблицу результатов ничего не записывается. Затем указанные выше действия повторяются для всех остальных строк основного запроса.

Подзапросы в разделе HAVING

Подзапросы можно использовать и в разделе HAVING основного запроса. В этом случае подзапрос будет выполняться один раз для каждой группы строк основного запроса и его результат будет участвовать в отборе групп строк.

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

SELECT Фамилия, AVG(CAST(Оценка AS

DECIMAL(4,2))) AS [Средний балл]

FROM [Общая ведомость] v INNEG JOIN Студент s ON v.[Номер зачетной книжки]=s.[Номер зачетной книжки]

GROUP BY Фамилия

HAVING ANG(CAST(Оценка AS DECIMAL(4,2)))>

(SELECT AVG(CAST(Оценка AS DECIMAL(4,2))) FROM [Общая ведомость])

Подзапрос вычисляет средний балл по результатам сдачи всех экзаменов всеми студентами. Для вычисления среднего балла используется агрегатная функция AVG. Результат функции будет преобразован к типу данных аргумента функции (тип данных столбца Оценка tinyint). Чтобы получить нецелочисленное значение среднего балла, в запросе значение аргумента функции AVG преобразуется с помощью функции CAST к нецелочисленному типу.

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

В разделе HAVING можно использовать и связанный запрос.

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

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

Подзапросы в разделе From

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

Добавление, изменение и удаление данных

Добавление данных

Добавление данных в таблицу выполняется с помощью команд INSERTиSELECT INTO.

Команда INSERT INTOпозволяет добавить одну или несколько строк в уже существующую таблицу. Синтаксис команды на добавление строк:

INSERT INTO таблица [(столбец₁[, столбец₂ [, ...]])]

{VALUES (значение₁[,значение₂[, ...])[,…] |запрос }

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

Вставляемые в таблицу значения могут быть переданы следующими способами:

- списком значений в разделе VALUES;

- значениями, полученными в результате выполнения запроса.

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

Значения могут быть заданы одним из способов:

{ значение|DEFAULT|NULL }

Пример: Добавить сведения о специальности «Управление качеством» в таблицу Специальность.

INSERT INTO Специальность ([Номер специальности],[наименование специальности],[Стоимость обучения])

VALUES (220501, 'Управление качеством', 45000)

Значение DEFAUNT– это значение, определенное для столбца по умолчанию. Значение по умолчанию определяется при создании таблицы.

Значение NULLможет быть помещено в столбец, если при создании таблицы это было разрешено.

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

Пример: Добавить сведения о результатах сдачи экзаменов студентом в сессию.

INSERT INTO [Общая ведомость]([Номер зачетной книжки],[Код дисциплины],[Оценка])

VALUES (‘21125/10’, 11, 4), (‘21125/10’, 21, 3), (‘21125/10’, 23, 5)

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

INSERT INTO таблица [(столбец₁[, столбец₂ [, ...]])]

SELECT столбец₁[, столбец₂[, ...]

FROM список_таблиц

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

DROP TABLE #VedOpl

CREATE TABLE #VedOpl (

[Номер группы] [smallint] NOT NULL,

[Фамилия] [char](15) NOT NULL,

[Плата за обучение] [int] NOT NULL)

INSERT INTO #VedOpl

SELECT c.[Номер группы],Фамилия, [Стоимость обучение]

FROM (Студент c INNER JOIN Группа g

ON c.[Номер группы]=g.[Номер группы]) INNER JOIN Специальность S ON g.[Номер специальности]=s.[Номер специальности]

WHERE c.[номер группы]=1191 AND Коммерческий=1

SELECT * FROM #VedOpl

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

SELECT столбец₁[, столбец₂[, ...]] INTO

новая_таблица

FROM список_таблиц

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

Пример: Создать таблицу “Список групп” на основе данных полученных по запросу. Список должен содержать номера групп специальности «Управление качеством» и сведения о количестве студентов в группах.

SELECT c.[Номер группы], COUNT(Фамилия) as [Количество студентов]

INTO [Список групп]

FROM Студент c INNER JOIN Группа g ON c.[Номер группы]=g.[Номер группы] INNER JOIN Специальность s ON g.[Номер специальности]=s.[Номер специальности]

WHERE [Наименование специальности]=’Управление качеством’

GROUP BY c.[Номер группы]

SELECT * FROM [Список групп]

Изменение данных

Изменение данных выполняется с помощью команды UPDATE. Команда позволяет обновлять данные одного или нескольких столбцов таблицы в одной или нескольких строках. Синтаксис таблицы:

UPDATE имя_таблицы

SET столбец=

{новое_значение|DEFAULT|NULL}[,…]

[FROM имя_таблицы [,…]]

[WHERE критерий_отбора]

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

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

В инструкции UPDATE, которая прямо или косвенно ссылается на представление с определенным для него триггером INSTEAD OF, не может быть указано предложение FROM.

Пример: Создать запрос на увеличение стоимости обучения на 10% по всем специальностям.

UPDATE Специальность

SET Стоимость=Стоимость*1.1

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

Пример: Создать запрос на изменение номера специальности «Управление качеством».

UPDATE Специальность

SET [Номер специальности]= 221401

WHERE [номер специальности]=220501

Запрос изменяет значение Номера специальности только в той строке, которая в столбце Номер специальности имела значение 220501.

Удаление данных

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

DELETE таблица

[FROM имя_таблицы [,…]]

[WHERE критерий_отбора]

Пример: Создать запрос на удаление из таблицыСтудентсведений о студенте Иванове из группы 1191.

DELETE Студент

WHERE [номер группы]=1191 AND Фамилия=’Иванов’

Запрос удалит из таблицы одну строку.

Пример: Создать запрос на удаление из таблицыСтудентсведений о студентах группы 1191.

DELETE Студент WHERE [номер группы]=1191

Если раздел WHEREотсутствует, команда удалит все строки таблицы. Удаление всех строк из таблицы не приводит к удалению сомой таблицы.

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