Максимов Е.М. Структурированный язык запросов

Подождите немного. Документ загружается.

Структурированный язык запросов – SQL, страница 41

Вывод этого запроса такой:

TFAM PNAME

Викулина Физика

Костыркин Химия

Казанко Математика

Позднякова Экономика

Загарийчук философия

Казалось бы, эти два запроса должны давать одинаковое количество

строк, но результаты первого запроса насчитывают шесть строк, а второго -

только пять. Это связано с тем, что преподаватель Федченко в настоящий

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

NULL в поле TNUM, следовательно, это значение не совпадает ни с одним

идентификатором учебного предмета в таблице PREDMET. Значит, вывода

для этой строки во втором запросе не будет - она просто исчезает из

объединения. Таким образом, стандартное SQL-объединение может привести

к потере информации, если объединяемые таблицы содержат несвязанные

строки.

Использование вложенных запросов

В самом общем случае, запросы могут управлять другими запросами -

это делается путем размещения запроса внутрь предиката другого, который

использует вывод внутреннего запроса для установления верного или

неверного значения предиката. Например, требуется информация об

успеваемости студента с фамилией Поляков, однако в силу каких-либо

причин неизвестен номер этого студента. Тогда необходимо извлечь этот

номер из таблицы с данными о студентах, и после этого применять результат

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

конструкции:

SELECT * FROM USP

WHERE SNUM = (SELECT SNUM

FROM STUDENTS

WHERE SFAM = 'Поляков');

Результат этого запроса будет следующий:

UNUM OCENKA UDATE SNUM PNUM

1001 5 10/06/1999 3412 2001

1004 4 12/06/1999 3412 2003

Структурированный язык запросов – SQL, страница 42

Чтобы выполнить основной запрос, SQL сначала должен оценить

внутренний запрос (его называют подзапросом) внутри предложения

WHERE. Происходит это традиционным образом, т.е. исполняется

вложенный запрос, извлекающий необходимые для определения значения

предиката данные, а только затем - основной. Разумеется, подзапрос должен

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

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

С другой стороны, возможна ситуация, когда подзапрос выдает в качестве

результата несколько различных значений, что может сделать

невыполнимым оценку предиката основного запроса, и команда выдаст

ошибку. При использовании подзапросов в предикатах, основанных на

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

подзапрос, который будет отображать только одну строку вывода. Кроме

того, при использовании подзапроса, который вообще не выводит никаких

значений, основной запрос не выведет никаких значений: его предикат будет

иметь неизвестное значение.

В некоторых случаях стоит использовать DISTINCT для того, чтобы в

подзапросе получить одиночное значение. Предположим, что преподаватели

могут вести занятия по разным дисциплинам. Тогда для получения ответа на

вопрос о том, какие дисциплины ведет преподаватель Викулина, можно

воспользоваться запросом:

SELECT *

FROM PREDMET WHERE TNUM =

(SELECT DISTINCT TNUM FROM TEACHERS WHERE TFAM =

'Викулина');

В результате будет получено:

PNUM PNAME TNUM HOURS COURS

2001 Физика 4001 34 1

Подзапрос установил, что значение поля TNUM совпало с фамилией

Викулина при значении 4001, а затем основной запрос выделил все записи с

этим значением TNUM из таблицы предметов. Т.к., вообще говоря, могло

получиться, что преподаватель ведет несколько предметов, то фраза

DISTINCT в данном случае обязательна - если подзапрос возвратил бы более

одного значения, то это вызвало бы ошибку.

Следует иметь в виду, что предикаты с подзапросами являются

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

конструкцию в следующем порядке:

<ВЫРАЖЕНИЕ> <ОПЕРАТОР> <ПОДЗАПРОС>,

и ни в коем случае не

Структурированный язык запросов – SQL, страница 43

<ПОДЗАПРОС> <ОПЕРАТОР> <ВЫРАЖЕНИЕ>,

или

<П0ДЗАПР0С> <ОПЕРАТОР> <ПОДЗАПРОС>,

Иначе говоря, предыдущий пример, записанный следующим образом:

SELECT *

FROM PREDMET

WHERE (SELECT DISTINCT TNUM FROM TEACHERS WHERE TFAM =

'Викулина') = TNUM;

является неверным.

Разумеется, в подзапросах допускается использование агрегатных

функций - это удобно и по той причине, что они автоматически производят

одиночное значение для любого числа строк, которое может быть

использовано в основном предикате. Например, если возникает

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

которых выше среднего, то для этого воспользуемся запросом:

SELECT * FROM USP WHERE OCENKA >

(SELECT AVG (OCENKA) FROM USP);

Вывод такого запроса следующий:

JNUM OCENKA UDATE SNUM PNUM

1001 5 10/06/1999 3412 2001

1005 5 12/06/1999 3416 2004

Средняя оценка по имеющимся данным составляет 4.2, следовательно,

основной запрос выбирает только те записи, в которых значение поля

OCENKA больше, чем 4.2.

Не стоит забывать, что сгруппированные агрегатные функции,

определенные в терминах предложения GROUP BY, могут выдавать

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

характера. Даже если GROUP BY или HAVING используются так, что только

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

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

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

(операторы BETWEEN, LIKE, и IS NULL не могут использоваться с

подзапросами). IN определяет набор значений предиката, одно из которых

должно совпадать с другим по порядку. При использовании IN с

подзапросом SQL просто формирует этот набор из вывода подзапроса, а

Структурированный язык запросов – SQL, страница 44

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

подзапрос. Например, запрос

SELECT *

FROM PREDMET WHERE PREDMET . TNUM IN (SELECT

TEACHERS.TNUM FROM TEACHERS WHERE TEACHERS.TFAM

BETWEEN 'К' AND 'С');

Вывод этого запроса такой:

PNUM PNAME TNUM HOURS COURS

2002 Химия 4002 68 1

2003 Математика 4003 68 1

2005 Экономика 4004 17 3

Такой запрос гораздо понятнее, чем выполненный с помощью

объединения, хотя дающий такой же результат:

SELECT PREDMET.PNUM, PREDMET.PNAME, TEACHERS.TNUM,

PREDME T.HOURS, PREDMET.COURS FROM PREDMET, TEACHERS

WHERE TEACHERS.TNUM = PREDMET.TNUM

AND TEACHERS.TFAM BETWEEN 'И' AND 'C;

Вообще говоря скорость выполнения того или иного запроса,

например, как в рассмотренном случае, зависит от реализации той СУБД,

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

связанная с работой оптимизатора запросов, который пытается найти

наиболее эффективный способ выполнения. Традиционно хороший

оптимизатор чаще всего преобразует вариант объединения в подзапрос,

который обычно выполняется быстрее.

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

реляционный оператор равенства (=), можно использовать IN. В отличие от

первого, IN не может заставить запрос потерпеть неудачу, если подзапросом

выбрано больше чем одно значение. Это может быть или преимуществом или

недостатком. Например, уже рассмотренный нами выше запрос можно

переписать следующим образом:

SELECT *

FROM PREDMET WHERE. TNUM IN (SELECT TNUM

FROM TEACHERS

WHERE TFAM = 'Никулина');

Таким образом, подзапросы всегда определяют одиночные столбцы -

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

значением. Подтверждением этому является то, что команда SELECT * не

может использоваться в подзапросе. В подзапросе допускается использовать

Структурированный язык запросов – SQL, страница 45

выражение, основанное на поле, а не просто само поле, как в предложении

SELECT. Это может быть выполнено с помощью реляционных операторов

или при использовании IN. Примером может служить следующий запрос:

SELECT *

FROM PREDMET WHERE PNUM =

(SELECT PNUM-1 FROM PREDMET WHERE PNAME =

'Философия');

Вывод для этого запроса такой:

PNUM PNAME TNUM HOURS COURS

2003 Математика 4003 68 1

Этот запрос находит информацию об учебном предмете, код которого

на 1 меньше кода философии. Разумеется, поле PNUM не должно содержать

повторяющихся значений, иначе запрос вызовет ошибку.

Можно также использовать подзапросы внутри предложения HAVING.

Эти подзапросы могут использовать свои собственные агрегатные функции,

если они не производят многочисленных значений, или использовать GROUP

BY или HAVING. Например, рассмотрим запрос:

SELECT OCENKA, COUNT (SNUM) FROM USP GROUP BY OCENKA

HAVING OCENKA >=

(SELECT AVG (OCENKA) FROM USP

WHERE PNUM = 2003);

Вывод для этого запроса следующий:

OCENKA

5 2

4 2

Данный запрос подсчитывает количество студентов с оценками выше

средней, чем по дисциплине с PNUM = 2003.

Теперь несколько слов об объединении запросов с использованием при

этом подзапросов. Операция, которая бывает часто полезна - это

объединение из двух запросов, в котором второй запрос выбирает строки,

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

исключать строки, которые не удовлетворили предикату при объединении

таблиц. Этот процесс принято называть внешним объединением.

Предположим, что некоторые из студентов еще не получили оценку,

однако уже внесены в таблицу USP. Например, в эту таблицу добавлена

запись {1006, NULL. NULL, 34I6, NULL}. Пусть возникает необходимость в

просмотре успеваемости студентов по дисциплине, не учитывая тех, кто еще

не получил оценку. Этого можно достичь, формируя объединение из двух

запросов, один из которых выполняет объединение, а другой выбирает сту-

Структурированный язык запросов – SQL, страница 46

дентов с NULL значениями поля OCENKA. Этот последний запрос должен

вставлять сообщение в поля, соответствующие полю PNAME, и значение 0 в

поле OCENKA в первом запросе. Как было рассмотрено ранее, можно

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

который вывел данную строку. Использование этой методики во внешнем

объединении дает возможность применять предикаты для классификации, а

не для исключения. Следующий запрос выполняет эти действия:

SELECT USP.SNUM, STUDENTS.SFAM,

PREDMET.PNAME, USP.OCENKA FROM USP, STUDENTS, PREDMET

WHERE USP.SNUM = STUDENTS.SNUM

AND USP.PNUM = PREDMET.PNUM UNION SELECT USP.SNUM,

STUDENTS.SFAM,

'НЕТ ', 0 FROM USP, STUDENTS

WHERE USP.SNUM = STUDENTS.SNUM AND NOT USP.OCENKA = ANY

(SELECT OCENKA

FROM USP) ORDER BY 2 ASC;

Вывод этого запроса следующий:

3414 Гриценко Экономика 3

3416 Нагорный Философия 5

3416 Нагорный НЕТ О

3412 Поляков Физика 5

3412 Поляков Математика 4

3413 Старова Математика 4

Обратите внимание на то, что строка 'НЕТ ' была дополнена пробелами,

чтобы получить совпадение поля PNAME по длине. Второй запрос выбирает

даже те строки, которые были исключены первым.

Несколько слов об использованном операторе ANY, более подробно о

котором будем говорить ниже. Оператор ANY берет все значения,

выведенные подзапросом (для нашего случая - это все значения OCENKA в

таблице USP), и оценивает их как верные, если любое из их равняется

значению оценки текущей записи внешнего запроса.

В заключение разговора о вложенных запросах, поговорим о так

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

SQL имеется возможность обратиться к внутреннему запросу таблицы в

предложении внешнего запроса FROM, с помощью соотнесенного

подзапроса. При этом подзапрос выполняется неоднократно, по одному разу

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

соотнесенного подзапроса можно найти данные на всех студентов, которые

получали оценки 10/06/1999:

SELECT *

FROM STUDENTS FIRST WHERE 36321 IN (SELECT UDATE

Структурированный язык запросов – SQL, страница 47

FROM USP SECOND

WHERE FIRST. SNUM = SECOND . SNUM) ;

Вывод этого запроса таков:

SNUM SFAM SIMA SOTCH STIP

3412 Поляков Анатолий Алексеевич 25.50

3413 Старова Любовь Михайловна 17.00

В этом примере FIRST и SECOND - псевдонимы таблиц, при этом

получается, что значение в поле SNUM внешнего запроса меняется, а значит,

внутренний запрос должен выполняться отдельно для каждой строки

внешнего запроса.

Строку внешнего запроса, для которой внутренний запрос каждый раз

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

процедура оценки, выполняемой соотнесенным подзапросом:

выбор строки из таблицы во внешнем запросе - это текущая строка:

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

в предложении FROM внешнего запроса:

 выполнение подзапроса, при этом везде, где найден псевдоним из

внешнего запроса, используется значение из текущей строки (это

принято называть внешней ссылкой);

 оценка предиката внешнего запроса на основе результатов подзапроса:

 описанная выше последовательность повторяется для следующей

строки из таблицы внешнего запроса, и так до тех пор. пока все строки

не будут проверены.

Вообще говоря, последний пример мог быть реализован, используя

объединение следующего вида:

SELECT STUDENTS.SNUM, STUDENTS.SFAM,

STUDENTS.SIMA, STUDENTS.SOTCH, STUDENTS.STIP FROM

STUDENTS, USP

WHERE STUDENTS.SNUM = USP.SNUM AND USP.UDATE =

10/06/1999;

Предположим, что есть необходимость в выводе фамилии и номера

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

следующим запросом:

SELECT SNUM, SFAM

FROM STUDENTS FIRST WHERE 1 < (SELECT COUNT(*) PROM USP

WHERE SNUM = FIRST. SNUM) ;

Вывод этого запроса приведен ниже:

SNUM SFAM 3412 Поляков

Структурированный язык запросов – SQL, страница 48

Стоит обратить внимание на то, что предложение FROM подзапроса в

этом примере не использует псевдоним. При отсутствии имени таблицы или

префикса псевдонима, SQL может для начала принять, что любое поле

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

запроса. Если поле с этим именем отсутствует в этой таблице, SQL будет

проверять внешние запросы. Именно по этой причине префикс имени таб-

лицы обычно необходим в соотнесенных подзапросах - для отмены этого

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

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

запросе без какой-либо неоднозначности.

Часто соотнесенный подзапрос используют на основе той же самой

таблицы, что и основной запрос. Это дает возможность извлечь сложные

формы информации. Например, с помощью следующего запроса можно

найти все оценки по дисциплине со значениями, выше средней по этой же

дисциплине:

SELECT *

FROM USP FIRST WHERE OCENKA > (SELECT AVG (OCENKA) FROM

USP SECOND WHERE SECOND. PNUM = FIRST . PNUM) ;

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

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

выше средней. Если же условие внешнего предиката изменить на >=. то в

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

Предложение HAVING может также работать и с соотнесенными

подзапросами. При использовании соотнесенного подзапроса в предложении

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

из внешнего запроса, а не для каждой строки. Следовательно, подзапрос

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

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

чения оценок за каждый день, причем такой, что это среднее должно быть

больше или равно хотя бы на половину балла, чем минимальное значение

оценки в этот день, можно воспользоваться запросом:

SELECT UDATE, AVG (OCENKA) FROM USP FIRST GROUP BY

UDATE HAVING AVG (OCENKA) >=

(SELECT MIN(OCENKA) +0.5 FROM USP SECOND WHERE

FIRST.UDATE = SECOND.UDATE);

Результат запроса будет такой:

UDATE

10/06/1999 4.5

12/06/1999 4.5

Структурированный язык запросов – SQL, страница 49

Подзапрос вычисляет значение MIN для всех записей с той же самой

датой, что и у текущей агрегатной группы основного запроса.

Соотнесенные подзапросы по своей сути близки к объединениям - обе

конструкции включают проверку каждой записи одной таблицы с каждой

записью другой или с псевдонимом из той же таблицы, при этом

большинство операций у них похожи. По этой причине подробно

останавливаться на этом материале не будем.

Таким образом, применение вложенных запросов в целях ис-

пользования их результата для управления другим запросом расширяет

возможности SQL, позволяя выполнить большее количество функций.

Глава 2.3 Внесение изменений в базу данных

При работе с SQL исключительно важно не только уметь выбирать

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

таблице. Значения могут быть помещены и удалены из полей тремя

командами языка DML (Язык Манипулирования Данными), а именно:

■ INSERT - вставить;

■ UPDATE - модифицировать;

■ DELETE - удалить.

Добавление информации в базу данных

Все записи в SQL вводятся с использованием команды модификации

INSERT. В самой простой форме эта команда имеет следующий синтаксис:

INSERT INTO <table name>

VALUES (<value>, <value> . . .);

Так, например, для добавления записи в таблицу преподавателей

TEACHERS, можно воспользоваться следующим выражением:

INSERT INTO TEACHERS

VALUES (4006, 'Федченко', 'Светлана', 'Геннадиевна',

01/09/1999);

Команда INSERT не производит никакого вывода. Поэтому контроль

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

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

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

каждое значение в списке вставляемых данных должно совпадать с типом

Структурированный язык запросов – SQL, страница 50

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

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

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

столбец и т. д.

Если требуется ввести в таблицу NULL значение, то оно вводится

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

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

INSERT INTO TEACHERS

VALUES (NULL, 'Федченко', 'Светлана',

'Геннадиевна', 01/09/1999);

Так как значение NULL - специальное служебное слово, то заключать его в

одиночные кавычки не требуется.

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

вставку значения, что позволяет делать это в любом порядке. Например,

команда:

INSERT INTO TEACHERS (TDATE, TFAM, TIMA) VALUES

(01/09/1999, 'Федченко', 'Светлана') ;

позволяет вставить значения в поля таблицы в порядке TDATE, TFAM,

TIMA, причем столбцы TNUM и ТОТСН отсутствуют. Это означает, что для

этих полей автоматически устанавливается значение по умолчанию.

Значение по умолчанию может быть введено заранее или, в противном

случае, это будет NULL значение. Если ограничение запрещает

использование значения NULL в данном поле, то обязательно надо

позаботиться об обеспечении столбца содержательным значением для любой

команды INSERT.

Можно использовать команду INSERT для того, чтобы получать или

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

запросом. Для этого предложение VALUES заменяется на соответствующий

запрос:

INSERT INTO EXCELLENT SELECT * FROM USP

WHERE OCENKA = 5;

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

EXCELLENT (напомним, что сама INSERT команда никакого вывода не

производит).

Таблица EXCELLENT

EXCELLENT

UNUM OCENKA UDATE SNUM PNUM

Максимов Е.М. Структурированный язык запросов - SQL

Подождите немного. Документ загружается.