Запрос на создание новой таблицы

 

Этот вид запроса аналогичен запросу на выборку, рассмотренному выше, за одним исключением: результат запроса сохраняется как постоянная таблица и не имеет связи со своим источником. Для этого в запросе должна быть фраза INTO, за которой следует имя новой таблицы – результата. Фраза INTO должна записываться перед фразой FROM. Например, создать таблицу со списком групп специальности 2204 можно следующим запросом:

SELECT НомерГруппы, Староста, КоличСтудентов INTO Группы_2204

FROM ГРУППА WHERE ШифрСпец = '2204';.

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

Назначение перекрестного запроса

 

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

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

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

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

Обобщенный синтаксис перекрестного запроса

 

TRANSFORM <Значение>

SELECT <Заголовки столбцов>

FROM <Источник данных>

GROUP BY <Поле группирования>

PIVOT <Заголовки столбцов>;.

Примеры перекрестных запросов

 

Пример 1

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

TRANSFORM SUM(КоличСтудентов)

SELECT ШифрСпец, SUM(КоличСтудентов) AS ВсегоСтудентов

FROM ГРУППА

GROUP BY ШифрСпец

PIVOT НомерГруппы;.

Результат выполнения запроса представлен в табл. 7.

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

Таблица 7

ШифрСпец	ВсегоСтудентов
Сп1
Сп2
Сп3

 

Пример 2

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

Определим вначале компоненты перекрестного запроса.

Ø Источник данных – соединение таблиц РАСПИСАНИЕ, ЗАНЯТИЕ, ДЕНЬ, ПАРА, ДИСЦИПЛИНА, ВИДЗАН, ПРЕПОДАВАТЕЛЬ.

Ø Заголовки столбцов – поле НомерГруппы.

Ø Заголовки строк – поле НазваниеДня, поле ВремяНач.

Ø Значения – выражение, включающее конкатенацию полей НазваниеДисц & ИмяПрепод & НомерАуд.

Таким образом, получаем следующий текст запроса на языке SQL:

TRANSFORM First(НазваниеД & ', ' & ИмяПрепод & Chr(13) & Chr(10) & "ауд. " & НомерАуд)

SELECT ДЕ.НазваниеДня AS День, ПА.ВремяНач AS Время

FROM ГРУППА ГР INNER JOIN (ПАРА ПА INNER JOIN ((ДЕНЬ ДЕ INNER JOIN ЗАНЯТИЕ ЗА ON ДЕ.НомерДня] = ЗА.НомерДня) INNER JOIN (ДИСЦИПЛИНА ДИ INNER JOIN (ПРЕПОДАВАТЕЛЬ ПР INNER JOIN РАСПИСАНИЕ РА ON ПР.ШифрПрепод = РА.ШифрПрепоп) ON ДИ.ШифрДисц = РА.ШифрДисц) ON ЗА.НомерЗан = РА.НомерЗан) ON ПА.НомерПары = ЗА.НомерПары) ON ГР.НомерГруппы = РА.НомерГруппы

WHERE (((ГР.ШифрФак)="ФВТ") AND ((ГР.НомерГруппы) Like "0*"))

GROUP BY ЗА.ДеньНед, ПА.ВремяНач, ДЕ.НомерДня, ГР.ШифрФак, ГР.НомерГруппы

ORDER BY ДЕ.НомерДня, ПА.ВремяНач

PIVOT РА.НомерГруппы;

Комментарии к запросу:

Ø агрегатная функция First используется для соблюдения синтаксиса группировки и не несет своей основной нагрузки;

Ø цепочка функций Chr(13) & Chr(10) используется для обеспечения вывода значения в 2 строки;

Ø фильтр Like "0*" обеспечивает селекцию групп 3 курса, что характерно только для данного конкретного периода обучения и данного вуза;

Ø список упорядочивания во фразе ORDER BY обеспечивает вывод расписания по дням недели, а внутри одного дня по времени начала занятия.

Результат выполнения запроса представлен в табл. 8.

Таблица 8

День	Время
ПН	8:10			Физика, Иванов В И ауд. 444
ПН	9:55				Химия, Петров И А ауд. 123
ПН	13:35			Физика, Петров И А ауд. 333
ВТ	8:10	Физика, Иванов В И ауд. 234
ВТ	8:10				Математика, Петров И А ауд. 324
СР	8:10		Математика, Петров И А ауд. 333
ЧТ	8:10				Математика, Петров И А ауд. 123
ПТ	8:10			Физика, Бирюков Д Б ауд. 203
СБ	8:10				Химия, Петров И А ауд. 123

Запрос на добавление

 

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

Ø добавить одну запись с заданными значениями полей (INSERT … VALUES);

Ø добавить множество записей, сформированных запросом на выборку (INSERT …SELECT).

Обобщенный синтаксис запроса на добавление:

Добавление одной записи:

INSERT INTO <Имя таблицы - приемника> (<Список полей>)

VALUES (<Список значений>);

Добавление множества записей:

INSERT INTO <Имя таблицы - приемника> (<Список полей>)

<Запрос на выборку>;

Рассмотрим оба варианта добавления, но вначале следует отметить, что В СУБД Access синтаксис добавления одной записи отличается от стандарта SQL тем, что вместо слова VALUES используется слово SELECT, за которым далее идет текст обычного запроса на выборку, но в усеченном варианте.

Примеры

Добавить новую группу в таблицу группа

INSERT INTO ГРУППА (НомерГруппы, ШифрСпец, КоличСтудентов, КоличКоммерчСтуд, КоличМуж, КоличЖен, Староста, ШифрФак)

SELECT 2110 AS Выражение1, 2010 AS Выражение2, 24 AS Выражение3;

5 AS Выражение4, 18 AS Выражение5, 6 AS Выражение6,

"Степанова О" AS Выражение7, "РТФ" AS Выражение8;

Добавить множество записей из таблицы ГРУППА во вспомогательную таблицу ГРУППЫ_ФВТ:

INSERT INTO ГРУППЫ_ФВТ (НомерГруппы, ШифрСпец, КоличСтудентов, КоличКоммерчСтуд, КоличМуж, КоличЖен, Староста, ШифрФак)

SELECT НомерГруппы, ШифрСпец, КоличСтудентов, КоличКоммерчСтуд, КоличМуж, КоличЖен, Староста, ШифрФак

FROM Группа

WHERE ШифрФак="ФВТ";

Вспомогательную таблицу ГРУППЫ_ФВТ можно создать с помощью следующего запроса на создание таблицы:

SELECT * INTO ГРУППЫ_ФВТ FROM ГРУППА WHERE КоличСтудентов =100;

Здесь задана селекция с заведомо ложным значением, что позволяет создать, тем не менее, пустую таблицу, заголовок которой повторяет заголовок таблицы ГРУППА, символ * во фразе SELECT означает взять все поля.

 

Запрос на удаление

 

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

Обобщенный синтаксис запроса на удаление:

DELETE <Список полей> FROM <Имя таблицы - источника>

WHERE <Предикат операции селекции>;

Здесь <Cписок полей> используется для соблюдения синтаксиса и не имеет какого-либо другого смысла, т. к. удаляются все записи целиком. Например, удалим из таблицы РАСПИСАНИЕ все занятия 5 курса специальности 2204.

DELETE Р.*

FROM РАСПИСАНИЕ Р INNER JOIN ГРУППА Г ON Р.НомерГруппы = Г.НомерГруппы

WHERE (Р.НомерГруппы Like "8*") AND (Г.ШифрСпец)="2204");

Здесь в источнике использовано соединение основной таблицы РАСПИСАНИЕ с таблицей ГРУППА для селекции записей, значение специальности в которых равно 2204.

 

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

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

Обобщенный синтаксис запроса на изменение:

UPDATE <Соединение таблиц - источника>

SET <Имя поля = Выражение>, <Имя поля = Выражение>, …

WHERE <Предикат операции селекции>;

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

Пример. Увеличить на 1 значения полей КоличСтудентов и КоличКоммерчСтуд в группах, занимающихся в аудитории 123:

UPDATE ГРУППА Г INNER JOIN РАСПИСАНИЕ Р ON Г.НомерГгруппы = Р.НомерГруппы

SET Г.КоличСтудентов =Г.КоличСтудентов + 1, КоличКоммерчСтуд = КоличКоммерчСтуд + 1

WHERE Р.НомерАуд = "123";

Параметрические запросы

 

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

Параметр – это любой текст, заключенный в квадратные скобки. Например, в последнем примере запроса на изменение можно вместо констант 1 во фразе SET использовать параметры.

SET Г.КоличСтудентов =Г.КоличСтудентов + [Добавить всего студентов?], КоличКоммерчСтуд = КоличКоммерчСтуд + [Из них коммерческих?]

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

SELECT А.НомерАуд

FROM АУДИТОРИЯ А

WHERE NOT EXISTS (SELECT DISTINCT Р.НомерАуд

FROM (ПАРА П INNER JOIN ЗАНЯТИЕ З ON П.НомерПары = З.НомерПары)

INNER JOIN РАСПИСАНИЕ ON З.НомерЗан = Р.НомерЗан

WHERE (П.ВремяНач = [Время?]) And (А.НомерАуд = Р.НомерАуд));

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

При запуске этого запроса на экране появляется окно (рис. 13) для ввода времени начала занятия, если ввести 8:10, то выводится список свободных аудиторий.

Рис. 13. Окно ввода значения параметра запроса

 

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

PARAMETERS В DateTime, Д Text (255);

SELECT П.ВремяНач, П.Времяоконч, З.ДеньНед, Р.НомерАуд

FROM (ПАРА П INNER JOIN ЗАНЯТИЕ З ON П.НомерПары = З.НомерПары) INNER JOIN РАСПИСАНИЕ ON З.НомерЗан = Р.НомерЗан

WHERE (П.ВремяНач = [В]) AND (З.ДеньНед = [Д]);

Здесь параметр В имеет тип Дата/Время, а параметр Д – Текстовый.