Файл: Методическое пособие к выполнению самостоятельной и лабораторных работ и курсового проекта по дисциплине Организация баз данных для студентов направления подготовки бакалавров.docx

FROM Студенты LEFT JOIN Задолженность_за_обучение ON Студенты.Номер_зачетной_книжки = Задолженность_за_обучение.Номер_зачетной_книжки;

Поле «Номер_зачетной_книжки» в этом примере используется для объединения таблиц, однако, оно не включается в результат вы-

полнения запроса, поскольку не включено в инструкцию SELECT. Чтобы включить связующее поле (в данном случае поле «Но- мер_зачетной_книжки») в результат выполнения запроса, его имя необходимо включить в инструкцию SELECT.

Важно отметить, что операции LEFT JOIN или RIGHT JOIN мо- гут быть вложены в операцию INNER JOIN, но операция INNER JOIN не может быть вложена в операцию LEFT JOIN или RIGHT JOIN.

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

В некоторых СУБД (в частности в MS Access) существует такой вид запросов как перекрестный. В перекрестном запросе отображают- ся результаты статистических функций — суммы, средние значения и др., а также количество записей. При этом подсчет выполняется по данным из одного полей таблицы. Результаты группируются по двум наборам данных, один из которых расположен в левом столбце табли- цы, а другой в заголовке таблицы. Например, при необходимости вы- числить средний балл студентов за семестр, обучающихся на разных кафедрах, необходимо реализовать перекрестный запрос, в результате выполнения которого будет создана таблица, где заголовками строк будут служить номер семестра, заголовками столбцов — названия ка- федр, а в полях таблицы будет рассчитан средний балл.

Для создания перекрестного запроса необходимо использовать следующую инструкцию:

TRANSFORM статистическая_функция инструкция_SELECT

PIVOT поле [IN (значение_1[, значение_2[, ...]])],

где статистическая_функция — статистическая функция SQL, обраба- тывающая указанные данные;

инструкция_SELECT — запрос на выборку;

поле — поле или выражение, которое содержит заголовки столб- цов для результирующего набора;

значение_1, значение_2 — фиксированные значения, используе- мые при создании заголовков столбцов.

Составим SQL-запрос, реализующий описанный выше пример. В качестве исходного набора данных используется таблица Успевае- мость (Рисунок 20).


Рисунок 20

---

– Таблица УСПЕВАЕМОСТЬ

В результате выполнения нижеприведенного перекрестного SQL- запроса формируется следующая таблица (Рисунок 20):

TRANSFORM AVG(Успеваемость.Оценка) AS Сред_балл SELECT Успеваемость.Семестр

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

GROUP BY Успеваемость.Семестр PIVOT Успеваемость.Кафедра

Рисунок 21 – Результат выполнения перекрестного запроса Таким образом, когда данные сгруппированы с помощью пере-

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

Подчиненныезапросы

Часто возникает ситуация, когда желаемый результат нельзя по- лучить с помощью одного SQL-запроса. Одним из способов решения такой задачи является использование подчиненных запросов в соста- ве главного SQL-запроса. Подчиненным SQL-запросом называют ин- струкцию SELECT, включаемую в инструкции SELECT, SELECT...INTO, INSERT...INTO, DELETE или UPDATE или в другой

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

1. 
   сравнение [ANY | ALL | SOME] (инструкцияSQL)
   
2. 
   выражение [NOT] IN (инструкцияSQL)
   
3. 
   [NOT] EXISTS (инструкцияSQL),
   

где сравнение — выражение и оператор сравнения, который сравни- вает выражение с результатами подчиненного запроса;

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

инструкцияSQL — инструкция SELECT, заключенная круглые скобки.

Подчиненный запрос можно использовать вместо выражения в списке полей инструкции SELECT или в предложениях WHERE и HAVING. Инструкция SELECT используется в подчиненном запросе для задания набора конкретных значений, вычисляемых в выражениях предложений WHERE или HAVING.

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

---

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

SELECT * FROM Студенты

WHERE Номер_зачетной_книжки = ANY

(SELECT Номер_зачетной_книжки FROM Успеваемость WHERE оценка > 4)

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

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

SELECT * FROM Студенты WHERE Номер_зачетной_книжки in

(SELECT Номер_зачетной_книжки FROM Успеваемость WHERE оценка > 4)

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

Предикат EXISTS (с необязательным зарезервированным словом NOT) используется в логическом выражении для определения того, должен ли подчиненный запрос возвращать какие-либо записи.

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

SELECT Фамилия,

Имя, Номер_группы, Стипендия FROM СТУДЕНТЫ AS С1

WHERE Стипендия >= (SELECT Avg(Стипендия) FROM СТУДЕНТЫ

WHERE С1.Номер_группы = СТУДЕНТЫ.Номер_группы) Order by Номер_группы;

В последнем примере зарезервированное слово AS не является обязательным.

Некоторые подчиненные запросы можно использовать в пере- крестных запросах как предикаты (в предложении WHERE). Подчи- ненные запросы, используемые для вывода результатов (в списке SELECT), нельзя использовать в перекрестных запросах.

---

Созданиеновойтаблицы

Инструкция CREATE TABLE создает новую таблицу и исполь- зуется для описания ее полей и индексов. Если для поля добавлено ограничение NOT NULL, то при добавлении новых записей это поле должно содержать допустимые данные. Синтаксис:

CREATE TABLE таблица (поле_1 тип [(размер)] [NOT NULL] [индекс_1] [, поле_2 тип [(размер)]

[NOT NULL] [индекс_2] [, ...]] [, CONSTRAINT составной Индекс [, ...]]),

где таблица — имя создаваемой таблицы;

поле_1, поле_2 — имена одного или нескольких полей, создавае- мых в новой таблице. Таблица должна содержать хотя бы одно поле;

тип — тип данных поля в новой таблице;

размер — размер поля в символах (только для текстовых и двоич- ных полей);

индекс_1, индекс_2 — предложение CONSTRAINT, предназна- ченное для создания простого индекса;

составной Индекс — предложение CONSTRAINT, предназначен- ное для создания составного индекса.

следующем примере создается новая таблица с двумя полями: CREATE TABLE Студенты (Номер_зачетной_книжки integer

PRIMARY KEY, ФИО_студента TEXT (50), Место_рождения TEXT (50));

В результате выполнения этого запроса будет создана таблица со следующей схемой (Рисунок 22):



Рисунок 22. Схема таблицы СТУДЕНТЫ Предложение CONSTRAINTиспользуется в инструкциях ALTER

TABLE и CREATE TABLE для создания или удаления индексов. Су- ществуют два типа предложений CONSTRAINT: для создания просто- го индекса (по одному полю) и для создания составного индекса (по нескольким полям). Синтаксис:

простой индекс:

CONSTRAINT имя {PRIMARY KEY|UNIQUE | NOT NULL]}

составной индекс:

CONSTRAINT имя

{PRIMARY KEY (ключевое_1[, ключевое_2 [, ...]]) | UNIQUE (уникальное_1[, уникальное_2 [, ...]]) | NOT NULL (непустое_1[, непустое_2 [, ...]]) | FOREIGN KEY (ссылка_1[, ссылка_2 [, ...]])

REFERENCES внешняя

---

Таблица [(внешнее Поле_1 [, внешнее Поле_2 [, ...]])]},

где имя — имя индекса, который следует создать;

ключевое_1, ключевое_2 — имена одного или нескольких полей, которые следует назначить ключевыми;

уникальное_1, уникальное_2 — имена одного или нескольких по- лей, которые следует включить в уникальный индекс;

непустое_1, непустое_2 — имена одного или нескольких полей, в которых запрещаются значения Null;

ссылка_1, ссылка_2 — имена одного или нескольких полей, включенных во внешний ключ, которые содержат ссылки на поля в другой таблице;

внешняя Таблица — имя внешней таблицы, которая содержит по- ля, указанные с помощью аргумента внешнееПоле;

внешнее Поле_1, внешнее Поле_2 — имена одного или несколь- ких полей во внешней Таблице, на которые ссылаются поля, указан- ные с помощью аргумента ссылка_1, ссылка_2. Это предложение можно опустить, если данное поле является ключом внешней Табли- цы.

Предложение CONSTRAINT позволяет создать для поля индекс одного из двух описанных ниже типов:

1. 
   уникальный индекс, использующий для создания зарезервиро- ванное слово UNIQUE. Это означает, что в таблице не может быть двух записей, имеющих одно и то же значение в этом поле. Уникаль- ный индекс создается для любого поля или любой группы полей. Если в таблице определен составной уникальный индекс, то комбинация значений включенных в него полей должна быть уникальной для каж- дой записи таблицы, хотя отдельные поля и могут иметь совпадающие значения;
   
2. 
   ключ таблицы, состоящий из одного или нескольких полей, использующий зарезервированные слова PRIMARY KEY. Все значения ключа таблицы должны быть уникальными и не значениями Null. Кроме того, в таблице может быть только один ключ.
   

Для создания внешнего ключа можно использовать зарезервиро- ванную конструкцию FOREIGN KEY. Если ключ внешней таблицы состоит из нескольких полей, необходимо использовать предложение CONSTRAINT. При этом следует перечислить все поля, содержащие ссылки на поля во внешней таблице, а также указать имя внешней таб- лицы и имена полей внешней таблицы, на которые ссылаются поля, перечисленные выше, причем в том же порядке. Однако, если послед- ние поля являются ключом внешней таблицы, то указывать их необя- зательно, поскольку ядро базы данных считает, что в

---