Оператор Select

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

Общий вид оператора:

Select [предикат] список – полей From список – таблиц [отношение между таблицами] [диапазон] [сортировка]

Элемент «список – полей» оператора Select имеет следующий вид:

[имя – таблицы.]поле1 [As Alias1] [, [имя - таблицы.]поле2 [As Alias2] [¼]]

Для выбора всех полей в указанной таблице используется (*).

Выдать список всех студентов:

Select * From Student

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

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

Select FIOS, YearEnter From Student

Для выбора из множества таблиц нужно указать:

- таблицы, из которых выбираются поля;

- поля, из которых выбираются данные;

- отношения между таблицами.

Имена таблиц и полей разделяются точками, например, Student.FIOS.

Имена используемых таблиц указываются в предложении From.

Отношения между таблицами определяются либо в предложении Where, либо Join.

Составить список студентов, обучающихся на ФИТ.

Select Student.FIOS From Student, Group Where Group.Sfacult = “ФИТ” And Group.Sgroup = Student.Sgroup

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

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

Select FIOS, Year(Date()) – YearEnter As [Срок обучения] From Student

Если имя вычисляемого поля не указывается, то ему автоматически назначается имя типа Expression0001. Вычисляемые поля хранятся в наборе записей, как поля доступные для чтения. Корректировать их нельзя. Чтобы вычисляемое поле получило имя, после описания поля следует указать предложение As и необходимое имя поля. Этот же подход можно использовать, чтобы переопределить имя поля.

Для определения, из каких таблиц следует выбирать информацию, используется предложение From, которое имеет следующий общий вид:

From table1 [In DB1] [As Alias1] [, table2 [In DB2] [As Alias2]

Чаще всего выбираются все записи, удовлетворяющие критерию. Это соответствует предикату All, то есть запись:

Select * From Student

идентична записи:

Select All * From Student.

Для выбора уникальных значений используется предикаты Distinct и Distinctrow.

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

Отношения между таблицами

Чтобы указать отношения между таблицами можно использовать предложения:

- Join объединяет две таблицы с учетом содержимого указанных полей и типа предложения Join.

- Where применяется для фильтрации записей, возвращаемых запросом; может использоваться аналогично предложению Inner Join.

Предложение Join имеет следующий основной формат:

Table1 {Inner/Left/Right} Join Table2 On Table1.Key1 q Table2.Key2

q = {<, <=, >, >=, =, <>}

Table1 называется левой таблицей, а Table2 – правой таблицей.

Зависимость возвращаемых записей от типа предложения Join:

Предложение Where можно использовать для связи двух таблиц аналогично предложению Inner Join.

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

Select Predmet.Predm, Vedom.Nved From Predmet Left Join Vedom On Predmet.CodePredm = Vedom.Code.Predm

Выбор критерия фильтрации

Условия критерия фильтрации задается следующим синтаксисом:

Where логическое выражение

Существует четыре типа логических выражений (предикатов) в предложении Where: сравнение, Like, In, Between.

Предикат сравнения имеет следующий вид:

<выражение1> <операция сравнения> <выражение2>

<операция сравнения> = {=, <, >, <>, >=, <=}.

Составить список студентов, имеющих отличные оценки.

Select Student.FIOS From Student, Ekzamen Where Ekzamen.CodOcen = 5 And Ekzamen.Nzach = Student.NZach

С помощью предиката Like выражение (то есть значение поля) можно сравнить с шаблоном. Его формат следующий:

<выражение> Like <шаблон>

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

Список символов должен отвечать трем требованиям:

1. Список заключается в квадратные скобки.

2. Первый и последний символ должны разделяться дефисом.

3. Диапазон символов должен задаваться в возрастающем порядке.

Шаблоны, используемые в предикате Like

Составить список студентов, фамилии которых со второй буквы фамилии включают комбинацию символов «етр».

Select * From Student Where Fios Like “?етр*”

Предикат In позволяет сравнивать выражение с несколькими значениями. Формат:

<Выражение> In <список значений>

Составить список студентов, поступавших в институт в 2000, 2003 и 1999 году.

Select FIOS From Student Where YearEnter In (2000,2003, 1999)

Предикат Between позволяет искать выражение, имеющее значение в заданном диапазоне. Применимо к строкам, числам и датам. Формат:

<выражение> [Not] Between <значение1> And <значение2>

Перед датой используется символ #.

Выдать список студентов, имеющих положительные оценки.

Select Student.FIOS, Student.Nzach From Student, Ekzamen Where Ekzamen.CodeOcen Between 3 And 5 And Student.Nzach = Ekzamen.Nzach

Предложения Where могут работать с комбинированными условиями, которые объединяются с помощью связок And и Or.

Для сортировки значений используется предложение Order By. Для сортировки можно выбирать несколько полей. В этом случае поля разделяются запятыми. По умолчанию принята сортировка по возрастанию. Порядок сортировки можно изменить, указав после имени поля слово Desc.

Выдать список студентов по алфавиту.

Select * From Student Order By FIOS

Выдать список студентов по убыванию годов их поступления, отсортировав их по алфавиту для каждого года.

Select * From Student Order By YearEnter Desc, FIOS

Оператор Select можно использовать для вычислений со значениями, хранящимися в таблице. Для этого применяются статистические (aggregate – агрегатные) функции языка.

Эти функции имеют синтаксис:

<функция>(<аргумент>)

Выражение может относиться к одному полю, а может быть результатом вычислений на основе вычислений одного или нескольких полей. Функция Count позволяет использовать групповое имя (*).

Основные функции

Определить общее количество студентов.

Select Count (*) From Student

В большинстве случаев с этими функциями используется предложение Group By. Предложение Group By объединяет в группы записи с общим значением некоторого поля.

Определить среднее количество студентов в группах по факультетам.

Select SFacult, Avg(QStud) From Group Group By Sfacult

В предложении Group By можно использовать несколько полей. При этом возвращается по одной записи для каждой уникальной комбинации полей. Если указывается несколько полей, то они разделяются запятыми.

Определить средний балл для каждого студента.

Select Student.FIOS, Avg(Ekzamen.CodeOcen) From Student, Ekzamen Where Student.Nzach = Ekzamen.Nzach Group BY Student.FIOS Order By Student.FIOS

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

Определить студентов, имеющих средний балл больший трех с половиной.

Select Student.FIOS, Avg(Ekzamen.CodeOcen) From Student, Ekzamen Where Student.Nzach = Ekzamen.Nzach Group BY Student.FIOS Having Avg (Ekzamen.CodeOcen) > 3.5 Order By Student.FIOS

Создание таблиц.

При выполнении Select результат запроса помещается в динамический набор. Поскольку это временные наборы, их содержимое существует, пока набор открыт. Чтобы сохранить информацию в операторе Select используется предложение Insert Into или Into. Для создания таблицы используется оператор SQL, имеющий следующий синтаксис:

Select <список выбора> Into <новая таблица> From <исходная таблица> [Where <критерий добавления>]

Создать таблицу, в которой хранятся данные о не аттестованных студентах.

Select * Into BadStudent From Ekzamen Where Ekzamen.CodeOcen = 0

Чтобы скопировать исходную таблицу, требуется в качестве аргумента <список выбора> указать * и опустить предложение Where.

Запросы на добавление имеют следующий вид:

Insert Into <таблица приемник> Select <список выбора> From <таблица источник> [ Where <критерий добавления> ]

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

Insert Into BadStudent Select * From Ekzamen Where Ekzamen.CodeOcen = 2

В программном коде также допустимы следующие формы этой операции:

Insert Into BadStudent Select “ак51/5”, 5, 2

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.