Е.А. Бессонов Access Запросы на языке SQL

Таблица 2

Экзамены

Счетчик	Предмет	Группа	Студент	Оценка
1	1	1	Волков	2
2	1	1	Медведев	5
3	2	2	Белкин	3
4	2	2	Лисицын	4
5	2	1	Волков	3
6	3	1	Барсуков	3

Пример

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

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

GROUP BY Группа

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

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

Группа	Всего	5	4	3	2
1	4	1		2	1
2	2		1	1

Столбцы “Группа” и “Всего” сформированы инструкцией 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.

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

	Экзаменаторы		Таблица 3
Предмет	Название предмета	Преподаватель	Дата
1	Информатика	1	5.01.99
2	Математика	2	8.01.99
3	Кибернетика	3	12.01.99

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

	Преподаватели			Таблица 4
Преподаватель	ФИО	Должность	Степень	Звание
1	Соловьев	Ассистент
2	Щеглов	Профессор	Д.т.н	Профессор
3	Воробьев	Доцент	К.т.н	Доцент

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

Пример

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 Преподаватели (Преподаватель, ФИО,Должность)

VALUES (7, ”Журавлев”, ”Ассистент”);

DELETE

DELETE таблица.* FROM таблицы WHERE условие;

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

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

DROP таблица;

Удаление данных из отдельных полей можно осуществить с помощью инструкции UPDATE, в которой следует указать SET поле=Null. Пример

DELETE * FROM Преподаватели WHERE Преподаватель=3; Отменяются все экзамены преподавателя с шифром 3.

CREATE TABLE

CREATE TABLE таблица (поля, индексы) Поля - список полей с характеристиками.

Индексы - необязательный список составных индексов.

Инструкция CREATE TABLE создает таблицу. Каждое поле в списке поля может иметь до трех элементов, разделенных пробелами: “Поле тип индекс”.

Поле - имя поля.

Тип - тип поля. Если поле текстовое, то в круглых скобках указывается размер в байтах.

Индекс - предложение CONSTRAINT для создания простого индекса. Указывается только в случае, если по данному полю нужно создать индекс. Формат:

CONSTRAINT имя вариант

Имя - имя индекса.

Вариант - необязательный параметр. Может принимать одно из следующих значений:

PRIMARY KEY - поле ключевое;

UNIQUE - уникальный индекс ( в этом поле не допускаются повторения);

REFERENCES - внешняя таблица (внешнее поле) – поле во внешней таблице.

CONSTRAINT для создания составного индекса имеет аналогичный

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

Пример

CREATE TABLE [Расписание экзаменов](

[Предмет] INTEGER CONSTRAINT КлПоле PRIMARY KEY, [Название предмета] TEXT,

[Преподаватель] INTEGER,

Группа TEXT(10),

Дата DATATIME);

CREATE INDEX

Инструкция CREATE INDEX создает новый индекс для существующей таблицы.

CREATE [UNIQUE] индекс ON таблица (список_полей) [WITH вариант]

В квадратных скобках указаны необязательные элементы синтакси-

са.

Список_полей - список полей, составляющих индекс. Индекс - имя создаваемого индекса.

Вариант - дополнительная характеристика индекса – один из вариан-

тов: PRIMARY[(индексированные поля)],DISALLOWNULL, IGNORENULL. Последние два варианта запрещают использование пустых значений в индексированных полях.

Инструкция

DROP INDEX индекс ON таблица;

удаляет индекс из таблицы.

Пример

CREATE INDEX Предметы

ON [Расписание экзаменов]([Название предмета],[Группа]); Пример

DROP INDEX Предметы;

ALTER TABLE

Инструкция ALTER TABLE позволяет добавлять и удалять поля и индексы таблицы в соответствии с форматом

ALTER TABLE таблица вариант;

Вариант принимает в каждом случае одно из значений: ADD COLUMN поле тип [CONSTRAINT индекс];

ADD CONSTRAINT составной_индекс; DROP COLUMN поле;

DROP CONSTRAINT индекс.

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

Пример

ALTER TABLE Преподаватели

ADD COLUMN Комментарий TEXT(50);

Вариант ADD CONSTRAINT позволяет создать в таблице составной индекс. Варианты DROP удаляют из таблиц поля или индексы.

ЗАДАНИЕ

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

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

Список рекомендуемой литературы

1. Мартин Грабер. Справочное руководство по SQL. M.:Изд-во

“Лори”, 1997.

2. Вейскас Д. Эффективная работа с Microsoft Access 2: Пер. с

англ. - СПб.: Питер,1996.-864 c.:ил.

Составитель Евгений Александрович Бессонов

ACCESS

Запросы на языке SQL

Методические указания к лабораторной работе для студентов специальности “Экономика и управление на предприятиях ” по курсу “Автоматизация экономических расчетов”

Редактор Е.Л.Наркевич

ЛР № 020313 от 23.12.96.

Подписано в печать 23.11.2000.

Формат 60х84/16. Бумага офсетная. Отпечатано на ризографе. Уч.-изд. л. 1,00. Тираж 100 экз. Заказ

Кузбасский государственный технический университет. 650026, Кемерово, ул. Весенняя, 28.

Типография Кузбасского государственного технического университета. 650099, Кемерово, ул. Д. Бедного, 4а.