Файл: Е.А. Бессонов Access Запросы на языке SQL.pdf

Таблица 2

Экзамены

Пример

TRANSFORM Count([Оценка])

SELECT Группа, Count([Предмет]) AS Всего FROM Экзамены WHERE [Предмет]=1 OR [Предмет]=2

GROUP BY Группа

ORDER BY Группа PIVOT Оценка IN(5,4,3,2);

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

Столбцы “Группа” и “Всего” сформированы инструкцией SELECT, включающей в себя предложения WHERE, FROM, GROUP BY и ORDER BY. Заголовки остальных столбцов определены предложением PIVOT, а значения в ячейках этих столбцов формирует функция

Count из предложения TRANSFORM.

Пример

PIVOT “кв.“ & DatePart(“q”,[Дата]) IN(‘кв.1’,’кв.2’,’кв.3’,’кв.4’)

В функции DatePart первый аргумент указывает, какую часть нужно извлечь из даты, представленной вторым аргументом. Признак “q” предписывает извлечь из даты номер квартала 1,2,3 или 4. Оператор сцепления строк ”&” соединяет текст “кв.“ с номером квартала. Результат сравнивается с текстами из предложения IN. При успехе сравнения в соответствующий столбец заносится результат вычисления функции из предложения TRANSFORM.

UNION

Операция UNION позволяет получить запрос-объединение, являющийся объединением двух или более таблиц или запросов. В простейшем случае объединение имеет формат:

Запрос1 UNION Запрос2

ЗапросN -имя сохраненного запроса или инструкция SQL. В объединении может участвовать таблица, перед именем которой стоит зарезервированное слово TABLE.

Вследующем примере объединяется таблица “Новые преподаватели” и результат выполнения инструкции SQL.

TABLE [Новые преподаватели]

UNION SELECT * FROM Преподаватели;

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

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

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

Вконец каждого аргумента ЗапросN можно включить предложение GROUP BY или HAVING, чтобы сгруппировать и отфильтровать возвращаемые записи. В конец последнего аргумента ЗапросN можно включить предложение ORDER BY, чтобы упорядочить возвращенные записи.

Пример

SELECT Название, Город FROM Поставщики WHERE Страна=”Украина”

UNION SELECT Название, Город FROM Клиенты WHERE Страна=”Украина”

UNION SELECT Фамилия, Город FROM Сотрудники WHERE Регион=”Европа”;

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

ПОДЧИНЕННЫЕ ЗАПРОСЫ

Инструкция SELECT может быть вложена в другую инструкцию (SELECT, DELETE или UPDATE). Такой запрос называется подчиненным. Подчиненные запросы можно использовать в режиме SQL окна запроса, в ячейках “Условие отбора” и “Поле” конструктора запросов и в инструкциях SQL в программах на языке Access Basic. Ниже рассматривается лишь вариант подчиненного запроса в режиме SQL. В этом случае он представляется в виде инструкции SQL, заключенной в круглые скобки. Если инструкция SQL выдает единственное значение, то ее можно использовать в условных выражениях предложений WHERE и HAVING.

Пример

SELECT Группа, Студент FROM Успеваемость

WHERE [Средний балл]>(SELECT Avg([Средний балл]) FROM Успеваемость);

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

Пример

SELECT У.Группа,У.Студент

FROM Успеваемость AS У

WHERE Группа=1 And У.[Средний балл]>= ALL (SELECT Avg([Средний балл])

FROM Успеваемость

WHERE Группа=2 )

ORDER BY Студент;

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

[Средний балл]>=ALL(Подчиненный запрос)

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

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

Наряду с ALL находит применение предикат ANY (синоним SOME). В этом случае предикат принимает значение TRUE, когда результат сравнения хотя бы с одним из возвращаемых подчиненным запросом значений принимает значение TRUE.

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

чение FALSE.

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

первой группе (табл. 1) нет студентов, средний балл которых был бы не ниже среднего балла любого из студентов второй группы, например Белкина. Если заменить предикат ALL на ANY (или SOME), то все 4 студента первой группы попадут в результирующую таблицу, так как

все они учатся лучше Лисицына.

Пример

SELECT [Название предмета], Дата FROM Экзаменаторы AS Э

WHERE Э.Преподаватель IN (SELECT Преподаватель FROM Преподаватели

WHERE Должность=”Профессор”);

В этом примере применен предикат IN, сравнивающий выражение

слева от него (поле “Преподаватель” из таблицы “Экзаменаторы”) со списком значений, выдаваемых подчиненным запросом. В результате главный запрос выдает название предмета и дату экзаменов, которые проводят профессора ( математика, 8.01.99 ).

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

Пример

SELECT [Название предмета], Дата FROM Экзаменаторы AS Э WHERE NOT EXISTS

(SELECT * FROM Экзаменаторы INNER JOIN Экзамены

ON Экзаменаторы.Предмет=Экзамены.Предмет

WHERE Оценка=2

AND Экзамены.Предмет=Э.Предмет);

Предикат EXISTS проверяет не значения отдельных полей, а наличие или отсутствие в подчиненном запросе записей, удовлетворяющих условиям отбора в предложениях WHERE и HAVING. Поскольку отдельные поля не возвращаются, в подчиненном запросе список полей представлен звездочкой. В данном примере запрос выдает список предметов, по которым нет двоек, и даты экзаменов (математика 8.01.99, кибернетика 12.01.99).

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

UPDATE

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

UPDATE таблицы SET присваивания WHERE условие

Таблицы - отдельная таблица или объединение таблиц, как в предложе-

нии FROM.

Присваивания - последовательность присваиваний, разделенных запя-

тыми. Каждое присваивание имеет вид “поле=выражение”.

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

Пример

UPDATE Экзаменаторы SET [Дата]=[Дата]+1

WHERE [Название предмета]=”Информатика”

Даты всех экзаменов по информатике сдвигаются на один день вперед.

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

INSERT

Инструкция INSERT вставляет одну или несколько новых строк в таблицу или запрос. Вариант

INSERT INTO таблица SELECT …;

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

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

INSERT INTO Преподаватели

SELECT Преподаватель, ФИО, Должность, Степень, Звание FROM [Новые преподаватели]

WHERE Дата<Now()-30;

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

INSERT INTO таблица VALUES(список_значений)

или

INSERT INTO таблица (список_полей) VALUES (спи-

сок_значений)

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

Пример

INSERT INTO Преподаватели (Преподаватель, ФИО,Должность)