Лабораторная работа № 4 Запросы на выборку в форме sql

Теоретические сведения

SQL (Structured Query Language) – это язык программирования, используемый для работы с реляционными БД, создания запросов на выборку данных, обеспечения их целостности, выполнения вычислений. Синтаксис языка SQL может различаться для разных СУБД. Создание таблицы выполняется оператором:

CREATE TABLE таблица (поле1 тип [(размер)] [индекс 1]

[, поле2 тип [(размер)] [индекс2] [, ...]] [, составной индекс[, ...]]) ,

где таблица – имя таблицы; поле1, поле2 – имена полей; тип – тип поля; размер – размер текстового поля; индекс1, индекс2 – директивы создания простых индексов; составной индекс – директива создания составного индекса.

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

CONSTRAINT имя индекса {PRIMARY KEY | UNIQUE |

REFERENCES внешняя таблица [(внешнее поле)]}

Директива создания составного индекса (после определения его элементов):

CONSTRAINT имя {PRIMARY KEY (ключевое1[, ключевое2 [, ...]]) |

UNIQUE (уникальное1[, ...]]) | FOREIGN KEY (ссылка1 [, ссылка2[, ...]])

REFERENCES внешняя таблица [(внешнее поле1 [, внешнее поле2[, ...]])]}

Служебными словами являются: UNIQUE – уникальный индекс (в таблице не может быть записей, имеющих одно значение полей, входящих в индекс); PRIMARY KEY – первичный ключ таблицы (может состоять из нескольких полей); FOREIGN KEY – внешний ключ для связи с другими таблицами (может состоять из нескольких полей); REFERENCES – ссылка на внешнюю таблицу.

CREATE TABLE Студент

([Имя] TEXT, [Фамилия] TEXT, [Дата рождения] DATETIME,

CONSTRAINT Адр UNIQUE ([Имя], [Фамилия], [Дата рождения]))

В результате выполнения запроса будет создана таблица СТУДЕНТ, в составе которой два текстовых поля и одно поле типа дата/время. Будет создан составной индекс Адр по значениям указанных полей, индекс имеет уникальное значение.

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

ALTER TABLE таблица

ADD {[COLUMN] поле тип [(paзмep)][CONSTRAINT индекс]

CONSTRAINT составной индекс}|

DROP {[COLUMN] поле CONSTRAINT имя индекса}}

Опция ADD обеспечивает добавление поля, опция DROP – удаление поля, добавление опции CONSTRAINT означает подобные действия для индексов таблицы.

ALTER TABLE Студент ADD COLUMN [Группа] ТЕХТ(5)

Создать новый индекс для существующей таблицы можно командой:

CREATE [UNIQUE] INDEX индекс

ON таблица (поле [, ...])

[WITH {PRIMARY|DISALLOW NULL|IGNORE NULL}]

Фраза WITH обеспечивает наложение условий на значения полей, включенных в индекс: DISALLOW NULL – запретить пустые значения в индексированных полях новых записей; IGNORE NULL – включать в индекс записи, имеющие пустые значения в индексированных полях.

CREATE INDEX Гр ON Студент([группа]) WITH DISALLOW NULL

Для удаления таблицы (одновременно структуры и данных) в языке SQL используется команда:

DROP TABLE таблица

Для удаления только индекса таблицы (данные не разрушаются) – команда:

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

DROP INDEX Адр ON Студент – удален только индекс Адр;

DROP TABLE Студент – удалена вся таблица.

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

Универсальный оператор SELECT имеет следующую конструкцию:

SELECT [предикат] {*|таблица .*|[таблица .]поле1[,[таблица.] поле2[, ...]]}

[AS псевдоним1[, псевдоним2[, ...]]]

FROM выражение[, ...] [IN внешняя база данных]

[WHERE ...] [GROUP BY...] [HAVING ...] [ORDER BY ...]

[WITH OWNERACCESS OPTION]

Этот оператор обладает большими возможностями (табл. 9). Оператор SELECT реализует сложные алгоритмы запросов. Выводимая информации может включать поля таблиц и вычисляемые выражения.

SELECT [Имя], [Фамилия] FROM Студент

SELECT TOP 5 [Фамилия] FROM Студент

SELECT TOP 5 [Фамилия] FROM Студент ORDER BY [Группа]

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

Если в FROM используются одноименные поля нескольких таблиц, перед именем поля следует указать имя таблицы: [Студент].[Группа]. Для изменения заголовка столбца с результатами выборки используется слово AS.

Таблица 9

Аргумент	Назначение
Предикат	Ограничение числа возвращаемых записей: ALL – все записи; DISTINCT – записи, различающиеся в указанных для вывода полях; DISTINCTROW – полностью различающиеся записи по всем полям; ТОР – возврат заданного числа (процента) записей в диапазоне, соответствующем фразе ORDER BY
Таблица	Имя таблицы, поля которой формируют выходные данные
Поле1, Поле2	Имена полей, используемых при отборе (порядок их следования определяет выходную структуру выборки данных)
Псевдоним1, Псевдоним2	Новые заголовки столбцов результата выборки данных
FROM	Определяет выражение, используемое для задания источника формирования выборки (обязательно присутствует в каждом операторе)
Внешняя БД	Имя внешней базы данных – источника данных для выборки
[WHERE ...]	Определяет условия отбора записей (необязательное)
[GROUP BY…]	Указание полей (максимум – 10) для формирования групп, по которым возможно вычисление групповых итогов; порядок их следования определяет виды итогов (старший, промежуточный и т. п.) – необязательное
[HAVING ...]	Определяет условия отбора записей для сгруппированных данных (задан способ группирования GROUP BY...) – необязательное
[ORDER BY ...]	Определяет поля, по которым выполняется упорядочение выходных записей; порядок их следования соответствует старшинству ключей сортировки. Упорядочение возможно как по возрастанию (ASC), так и по убыванию (DESC)
[WITH OWNERACCESS OPTION]	При работе в сети в составе защищенной рабочей группы для указания пользователям, не обладающим достаточными правами, возможности просматривать результат запроса или выполнять запрос

SELECT DISTINCT [Дата рождения] AS Юбилей FROM Студент

SELECT [Фамилия] &" "&[Имя] AS ФИО, [Дата рождения] AS Год

FROM Студент

В первом случае будут выведены неповторяющиеся даты рождения студентов, которые имеют новое наименование – Юбилей. Во втором случае в результирующей таблице присутствуют все записи, но вместо Дата рождения указан Год и вместо Фамилия и Имя, соединенных вместе через пробел, – ФИО.

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

Таблица 10

Оператор	Назначение	Оператор	Назначение	Оператор	Назначение
AND	Логическое И или конъюнкция (логическое умножение)	Imp	Логическая импликация выражений	Or	Логическое ИЛИ, дизъюнкция (включающее Or)
Eqv	Проверка логической эквивалентности выражений	Not	Отрицание	Хоr	Логическое ИЛИ (исключающее Or)

Для построения условий могут использоваться операторы: LIKE – выполняет сравнение строковых значений; BETWEEN ... AND – выполняет проверку на диапазон значений; IN – выполняет проверку выражения на совпадение с любым из элементов списка; IS – проверка значения на Null.

SELECT Студент .* FROM Студент WHERE [Дата рождения] >= #01/01/79#

SELECT Студент .* FROM Студент

WHERE [Дата рождения] >= #01/01/79# AND [Группа] IN ("212", "213")

SELECT Студент .* FROM Студент

WHERE [Дата рождения] BETWEEN #01/01/79#

AND #01/01/81# AND [Группа] IN ("212", "213")

SELECT Студент .* FROM Студент INNER JOIN [Студент-заочник]

On Студент .[Группа] = [Студент-заочник].[Группа]

WHERE Студент .[Дата рождения] >= #01/01/79#

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

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

• Avg – вычисляет арифметическое среднее набора чисел, содержащихся в указанном поле запроса;

• Count – вычисляет количество выделенных записей в запросе;

• Min, Max – возвращают минимальное и максимальное значения из набора в указанном поле запроса;

• StDev, StDevPs – возвращают среднеквадратическое отклонение генеральной совокупности и выборки для указанного поля в запросе;

• Sum – возвращает сумму значений в заданном поле запроса;

• Var, VarPs – возвращает дисперсию распределения генеральной совокупности и выборки для указанного поля в запросе.

Для определения полей группирования указывается ключевое слово HAVING для заданного условия по группе при вычислении групповых значений.

SELECT [№ зач книжки], Avg(Результат) AS [Средний балл]

FROM Результаты GROUP BY [№ зач книжки]

Создается список студентов с указанием средних баллов каждого студента.

SELECT [№ зач книжки], Avg(Результат) AS [Средний балл]

FROM Результаты GROUP BY [№ зач книжки] HAVING Avg(Результат)<4

Создается список студентов (номера зачетных книжек) с указанием среднего балла по каждому студенту при условии, что он ниже 4.

В инструкцию SELECT может быть вложена другая инструкция SELECT, SELECT ... INTO, INSERT ... INTO, DELETE или UPDATE. Различают основной и подчиненный запросы. Подчиненный запрос можно использовать вместо выражения в списке полей инструкции SELECT или в предложениях WHERE и HAVING.

Существуют три типа подчиненных запросов: 1) сравнение (ANY | ALL | SOME) (инструкция); 2) выражение [NOT] IN (инструкция); 3) [NOT] EXISTS (инструкция). Первый тип – сравнение выражения с результатом подчиненного запроса. Ключевые слова: ANY – каждый, ALL – все, SOME – некоторые.

SELECT * FROM Оценка

WHERE [Результат] > ALL (SELECT [Результат]

FROM Оценка WHERE [№ зач книжки] ="123124")

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

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

SELECT * FROM Студент WHERE [№ зач книжки] IN

(SELECT [№ зач книжки] FROM Оценка WHERE [Результат] >=4)

Отбираются студенты, которые в таблице ОЦЕНКА имеют результат 4 и выше.

Третий тип – инструкция SELECT с предикатом EXISTS для определения, должен ли подчиненный запрос возвращать записи.

SELECT * FROM Студент WHERE EXISTS

(SELECT * FROM Оценка WHERE Студент .[№ зач книжки] =

Оценка .[№ зач книжки])

Отбираются студенты, которые имеют хотя бы одну оценку.

Практическая работа

При выполнении лабораторной работы необходимо:

• для заданной предметной области построить два запроса на выборку с помощью инструкций языка SQL;

• составить отчет по лабораторной работе.