Базы данных (стр. 5 )

Insert Into BadStudent Select (Nzach, Nved, CodOcen) values (“ак51/5”, 5, 2)

В SQL – операторах имеется возможность использовать параметры, назначение которых совпадает с назначением переменных в программных операторах. Значение параметру присваивает прикладная программа. Чтобы использовать параметр, его сначала надо описать в разделе Parameters объявлений оператора. Это объявление находится перед командой Select или другими командами. В объявлении присутствует как имя переменной, так и ее тип. Предложение Parameters отделяется от остальной части ;

Parameters p1 variant; Select * From Student Where Nzach = p1

Каждый параметр рассматривается как свойство объекта QueryDef.

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

Delete From <таблица> [Where <условие удаления>]

Если Where отсутствует, удаляются все записи из таблицы.

Удалить из таблицы BadStudent информацию о двоечниках

Delete From BadStudent Where CodOcen = 2

В запросах на обновление используется операция Set:

Update <table> Set <field1> = <value1> [, <field2> = <value2> [,¼]] [

Where <Condition>]

Заменить в таблице студент год поступления, оставив в нем только две последние цифры.

Update Student Set YearEnter = Int(Mid(Str(YearEnter), 3, 2))

Язык DDL в SQL

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

Создать итоговую таблицу Results, содержащую поля Sgroup, текстовый, 12 байт, Predm, текстовый, 20 байт, DateExamen, дата, AvgBall, вещественное число.

Create Table Results (Sgroup Text (12), Predm Text (20), DateExamen Date, AvgBall Double)

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

С помощью оператора Alter Table к существующей таблице можно добавить поле или удалить его из нее. При создании поля задается его имя, тип и размер. Для добавления поля используется выражение Add Column:

Alter Table Results Add Column SumBall Long

Для удаления поля достаточно указать его имя и использовать предложение Drop Column:

Alter Table Results Drop Column AvgBall

Удалить таблицу из БД можно с помощью оператора Drop Table/

Drop Table Results

Подзапросы

В этом случае подзапрос (оператор Select) внедряется в тело другого оператора Select. Внешний оператор Select использует результаты выполнения внутреннего оператора Select для определения окончательного результата всей операции. Внутренние запросы могут быть помещены в предложения Where и Having внешнего оператора Select. В этом случае они получают название подзапросов. Кроме того, внутренние операторы Select могут использоваться в Insert, Delete, Update.

Существует три типа подзапросов:

1.  Связанный подзапрос возвращает значение, выбираемое из пересечения одного столбца с одной строкой – то есть единственное значение.

2.  Строковый подзапрос возвращает значение нескольких столбцов таблицы, но в виде единственной строки.

3.  Табличный подзапрос возвращает значения одного или более столбцов в более чем одной строке.

Составить список экзаменов, принимавшихся преподавателем

Select Nved, DataEkz From Vedom Where CodePrep =

(Select CodePrep From Prep Where FIOP = “”)

Предположим, что код равен 237. Тогда оператор приобретает вид:

Select ¼ From Vedom Where CodePrep = 237

Составить список студентов, сдававших экзамен 22.06.03.

Select Sgroup, Nzach, Fios From Student Where Nzach In

(Select Nzach From Vedom Where DataEkz = # 22.06.03 #)

К подзапросам применяются следующие правила:

1.  В подзапросах не должна использоваться фраза Order By, хотя она может присутствовать во внешнем запросе.

2.  Список в предложении Select подзапроса должен состоять из имен отдельных столбцов или составленных из них выражений.

3.  По умолчанию имена столбцов в подзапросе относятся к таблицам, указанным в предложении From.

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

Ключевые слова Any и All

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

Составить перечень факультетов и групп на этих факультетах, , в которых меньше студентов, чем в группах АП.

Select Sfacult, Sgroup From Group Where Qstud < Any (Select Qstud From Group Where Sgroup Like “АП”)

Ключевые слова Exists и NotExist

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

Комбинирование результирующих таблиц.

Три операции над множествами, предусмотренные стандартом ISO, носят название Union (объединение), Intersect (пересечение) и Except (вычитание). В каждом случае формат операции над множествами должен иметь вид:

<operator> [All] [corresponding [By {<column1> [, ¼]}]]

При указании фразы Corresponding by операция над множествами выполняется для указанных столбцов. Если задано только ключевое слово Corresponding, а фраза By отсутствует, операция над множествами выполняется для столбцов, которые являются общими для обеих таблиц. Если указано ключевое слово All, результирующая таблица может содержать дублирующиеся строки.

В Access операция Union позволяет объединять в одно множество результирующие множества нескольких запросов, созданных предложением Select. Запрос, содержащий операцию Union, обозначается в окне базы данных специальным символом: двумя перекрывающимися кругами. Воспользоваться операцией Union можно только в инструкции SQL. Если добавить к запросу ключевое слово Union, то кнопка «Конструктор» и команда меню «Вид. Конструктор» становятся недоступными. Синтаксис операции имеет вид:

Select <инструкция выбора1> Union Select <инструкция выбора2>

[Group By <критерий1> [Having <критерий2>]

[ Union Select <инструкция выбора2>

[Group By <критерий1> [Having <критерий2>]]

[Union ¼] [Sort By <критерий3>]

На запросы, содержащие операцию Union, накладываются следующие ограничения:

·  Количество полей в <список полей> каждой из инструкций Select и Union Select должны совпадать. Если число полей не совпадает, то выводится сообщение об ошибке.

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

·  Допускается указание только одного предложения Group By, и оно должно указываться после последнего предложение Union Select. При необходимости в каждой операции Select и Union Select можно задать предложения Group By и Having.

Табло запросов (QBE)

Табло запросов похоже на отношение, с той разницей, что вместо значений содержит переменные из некоторого множества. Табло T со схемой R можно рассматривать как шаблон отношения со схемой R.

Одной из разновидностей является QBE. QBE имеет двумерный синтаксис. Запрос формулируется путем заполнения табличной формы, содержащий имена отношения и его атрибутов.

Здесь ЧН - № узла, ДЧ – деталь этого узла, НЧ – название узла.

Эта форма заполняется строками из констант и переменных.

Все переменные в QBE неявно связаны кванторами существования. Те переменные или константы, значения которых должны войти в результат запроса снабжаются префиксом P. (print). Префикс P. используется для получения эквивалента понятия резюме табличного запроса без выписывания отдельной строки.

Запрос 1. В каких пунктах имеется узел № 000.

А – адрес, К – количество.

Если переменная упоминается в запросе только один раз ее можно опустить.

Для выбора по условию отличному от равенства, перед переменной или константой следует указать знак сравнения. Запись вида qa, где q - знак сравнения, представляет подмножество домена, связанного с содержимым этого столбца.

Запрос 2. Какие запчасти, хотя бы в одном из пунктов, имеются в количестве, превышающем 50 штук.

Наличие ЧН А К P. >=50

 

Запрос 3. Какие узлы применяются в самолетах, кроме ТУ-154, в количестве, превышающем 100 штук.

Использование ЧН Тип Число P. <>ТУ-154 >=100

 

Запрос может содержать несколько строк.

Запрос 4. Какие одинаковые запчасти есть в Туле и Пскове.

Наличие ЧН А К P. x Тула x Псков

 

Если оператор P используется в нескольких строках с разными переменными, то его результат представляет объединение результатов, соответствующих отдельным строкам.

Запрос 5. Какие запчасти есть в Туле и Пскове.

Наличие ЧН А К P. x1 Тула P. x2 Псков

 

Запрос 6. Дать сведения об узле с № 000 (ДЧН – узел, входящий в другой узел).

Детали ЧН ДЧН НЧ P.211 P. P. P. P.211 P.

 

Оператор печати можно применять ко всей строке целиком. Для этого он помешается в столбце с именем отношения. Поэтому запрос 6 можно записать:

Детали ЧН ДЧН НЧ P. 211 P. 211

 

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

Запрос 7. В каких пунктах есть детали, которых нет в Туле, и что это за детали.

Наличие ЧН А К P. x P. y ^x Тула

 

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

Запрос 8. Запчасти, каких наименований есть в Туле.

 

Запрос 9. Какие запчасти и в каких пунктах имеются в количестве достаточном для МИГ – 29.

 

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

Запрос 10. Запчасти, каких наименований, и в каком количестве есть в Туле.

 

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

Запрос 11. Какие детали являются деталями данного узла.

 

Упорядоченность кортежей результата указывается с помощью префиксов АО (возрастание) или DO (убывание). При сортировке по значениям нескольких столбцов после этих префиксов в круглых скобках записывается натуральное число, определяющее порядок старшинства столбцов.

Запрос 12. Выдать номера деталей и их количество в порядке убывания количества и порядке возрастания номеров.

Наличие ЧН А К P. AO(2). DO(1)

 

В табличных формах и блоках условий можно употреблять арифметические выражения.

Запрос 13. Какие детали и в каких пунктах имеются в количестве не меньшем, чем половина для машин какого-либо типа.

 

Запрос 14. Какая доля кресел, необходимых для ТУ – 204, имеется в каждом из пунктов.

Можно использовать агрегирующие операции Count, Sum и т. д., которые применяются к компонентам строк запроса. Компонента, к которой применяются агрегирующие операции снабжается префиксом All, указывающим на то, что все значения этой компоненты должны быть собраны во едино и рассматриваться как единое множество.

Запрос 15. Сколько всего деталей имеется с номером 211.

Наличие ЧН А К 211 P. Sum. All.

 

Для исключения дубликатов из множества значений используется префикс UNQ.

Запрос 16. В каких пунктах имеются хоть какие-нибудь запчасти.

Наличие ЧН А К P. UNQ. All CNT. All.>0

 

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

Запрос 16. Количество каких деталей, хотя бы в одном из пунктов, превышает то, которое используется в ТУ – 204.

 

Запрос 17. Каково общее количество деталей одного наименования.

Наличие ЧН А К P. G. P. Sum. All

 

Запрос 18. Для каждого вида деталей определить в скольких пунктах имеются детали этого вида.

Наличие ЧН А К P. G. P. CNT. ALL