ЛАБОРАТОРНАЯ РАБОТА №8
Тема: SQL – запросы.
На вопрос: «Что такое запрос?» - ответ будет такой: «Запрос – это вопрос», т.к. запрос это действительно вопрос, с которым пользователь обращается к базе данных. Для того чтобы пользователь мог найти ответ практически на любой вопрос, существует такое хитрое и очень мощное средство как запросы (Query). В Visual FoxPro для создания запросов используется язык SQL, позволяющий реализовать все функциональные возможности, необходимые для управления БД, в том числе:
– организации данных;
– обработки данных;
– управления доступом.
Рассмотрим для начала синтаксис запросов, операторы, ключевые слова и условия будущих запросов.
Синтаксис:
Обязательная часть:
SELECT <список_выбора>;
Использовать при необходимости:
INТО <Новая_таблица>
Обязательная часть:
FROM db!исходная_таблица;
Условия запроса:
WHERE <условие отбора>;
GROUP ВY <Ключи_группировки>;
HAVING <Условие_отбора>;
ORDER ВY <Ключи_сортировки> ASC|DESC;
Использовать при необходимости:
INTO TABLE "Запрос1.dbf"
BROWSE
USE
Условия выборки записей:
Из приведенного синтаксиса видно, что обязательными являются только разделы SELECT и FROM, а остальные могут быть опущены.
Очень часто требуется тем или иным образом ограничить набор строк, помещаемых в результирующую таблицу запроса. Это достигается с помощью указания в запросе условий: WHERE, GROUP ВY, HAVING, ORDER ВY.
Условие состоит из ключевого слова, за которым следует перечень условий поиска, определяющих те строки, которые должны быть выбраны при выполнении запроса. Существует пять основных типов условий поиска (или предикатов, если пользоваться терминологией ISO):
- Сравнение. Сравниваются результаты вычисления одного выражения с результатами вычисления другого выражения.
|
|
- Диапазон, Проверяется, попадает ли результат вычисления выражения заданный диапазон значений.
- Принадлежность к множеству. Проверяется, принадлежит ли результат вычисления выражения к заданному множеству значений.
- Соответствие шаблону. Проверяется, отвечает ли некоторое строковое значение заданному шаблону.
- Значение NULL. Проверяется, содержит ли данный столбец определитель NULL (неизвестное значение).
Операторы, используемые в условии отбора:
В языке SQL можно использовать следующие операторы сравнения:
| = | Равенство |
| < | Меньше |
| > | Больше |
| <= | Меньше или равно |
| >= | Больше или равно |
| <> | Не равно (стандарт ISO) |
| != | Не равно (используется в некоторых диалектах) |
| == | Создание более ограниченного сравнения (точно равно) |
| Like | Такой как (посимвольное сравнение выражения, стоящего справа от оператора Like, с выражением, стоящим слева) |
| Between | Между (выбираются записи, значения которых попадают в указанный диапазон) |
Сложные предикаты для условия отбора:
Более сложные предикаты могут быть построены с помощью логически операторов AND, OR или NOT, а также с помощью скобок, используемых для определения; порядка вычисления выражения (если это необходимо или желательно). Вычисление выражений в условиях выполняется по следующим правилам:
- выражение вычисляется слева направо;
- первыми вычисляются подвыражения в скобках;
- операторы NOT выполняются до выполнения операторов AND и OR;
- операторы AND выполняются до выполнения операторов OR.
|
|
| % | — | Представляет любую последовательность из нуля или более символов. |
Пример: SELECT
FROM
WHERE должность LIKE '%np%'
где - % может быть заменен в символьном выражении любым количеством произвольных символов.
Например, %кош% позволяет отобрать слова: 'кошка', 'окошко', 'лукошко' и т.д. может быть заменен в символьном выражении любым, но только одним символом.
Например 'программ_' позволяет отобрать слова 'программа', 'программы', но не 'программист'.
Задание:
1. Сформировать базу данных для своего проекта (таблицы должны быть в НФБК или 4 НФ).
2. Сформулировать и реализовать запросы следующих типов:
а) сравнение условий поиска, пример:
SELECT <Список_выбора>
FROM <Исходная_таблица>
WHERE <Условие _отбора>
б) сложные условия поиска, пример:
SELECT <Список_выбора>
FROM <Иcxoднaя_тaблицa>
WHERE <Условие _отбора> OR <Условие_отбора>
в) диапазон ( BETWEEN ), пример:
SELECT <Cписок_выбора>
FROM <Иcxoднaя_тaблицa>
WHERE <Выражение> BETWEEN <нaчaлo диaпaзoнa>
АND <конец_диапазона>
г) условие поиска с проверкой вхождения во множество ([NOT]IN), пример:
SELECT <Список_выбора>
FROM <Иcxoднaя_тaблицa>
WHERE <выражение> [NOT] IN(<выражение1>,...,<выражениеN>);
д) условие поиска с указанием шаблонов (LIKE/ NOT LIKE), пример:
С помощью оператора LIKE можно выполнять сравнение выражения символьного типа с заданным шаблоном.
SELECT <Список_выбора>
FROM <Исходная_таблица>
WHERE «Символьное выражение> [NOT] LIKE <образец>
где <Образец> задает символьный шаблон для сравнения и заключается кавычки.
Сортировка столбца с записями в SQL-запросе:
В общем случае строки в результирующей таблице SQL-запроса неупорядочены каким-либо определенным образом. Однако их можно требуемым образом отсортировать, для чего в запрос помещается оператор ORDER BY.
|
|
Условие ORDER BY позволяет упорядочить выбранные записи в порядке возрастания (ASC) или убывания (DESC) значений любого столбца или комбинации столбцов, независимо от того, присутствуют эти столбцы в таблице результатов или нет. Однако в некоторых диалектах требуется, чтобы условие ORDER BY обязательно присутствовала в списке выборки оператора SELECT. В любом случае условие ORDER BY всегда должна быть последним элементом в операторе SELECT.
Условие ORDER BY включает список разделенных запятыми идентификаторов столбцов, по которым требуется упорядочить результирующую таблицу запроса. Идентификатор столбца может представлять собой либо его имя, либо номер, который идентифицирует элемент списка SELECT его позицией в этом списке. Самый левый элемент списка имеет номер 1, следующий — номер 2 и т.д. Номера столбцов могут использоваться в тех случаях, когда столбцы, по которым следует упорядочить результат, являются вычисляемыми, а слово AS с указанием имени этого столбца в операторе SELECT отсутствует.
Полный синтаксис оператора ORDER BY:
ORDER BY {<условие_сортировки> [ASC| DESC]} [,...,n]
Параметр <условие_сортировки> требует задания выражения, в соответствии с которым будет осуществляться сортировка строк.
Сортировка записей в нескольких столбцах:
Данные можно отсортировать по нескольким столбцам. Для этого необходимо ввести имена столбцов через запятую по порядку сортировки. Сначала данные сортируются по столбцу, имя которого было указано в операторе ORDER BY. Затем, если имеется множество строк с одинаковыми значениями в первом столбце, выполняется дополнительная сортировка этих строк по второму столбцу и т.д.
Группирующие запросы:
Очень часто в отчетах требуется формировать и промежуточные итоги. Для этой цели может указываться условие GROUP BY. Запрос, в котором присутствует условие GROUP BY, называется группирующим запросом, поскольку в нем группируются данные, полученные в результате выполнения операции SELECT, после чего для каждой отдельной группы создается единственная суммарная строка. Столбцы, перечисленные в условии GROUP BY, называются группируемыми столбцами. Стандарт ISO требует, чтобы предложение SELECT и условие GROUP BY были тесно связаны между собой.
При использовании в операторе SELECT условия GROUP BY каждый элемент списка в предложении SELECT должен иметь единственное значение для всей группы. Более того, предложение SELECT может включать только следующие типы элементов:
-имена столбцов;
-обобщающие функции;
-константы;
-выражения, включающие комбинации перечисленных выше элементов.
Все имена столбцов, приведенные в списке предложения SELECT, должны присутствовать и в условии GROUP BY — за исключением случаев, когда имя столбца используется в обобщающей функции. Обратное правило не является справедливым — в условии GROUP BY могут присутствовать имена столбцов отсутствующие в списке предложения SELECT. Если совместно с условием GROUP BY используется предложение WHERE, то оно обрабатывается первым, а группированию подвергаются только те строки, которые удовлетворяют условию поиска.
Стандартом ISO определено, что при проведении группирования все отсутствующие значения рассматриваются как равные. Если две строки таблицы в одном и том же группируемом столбце содержат значения NULL и идентичные значения во всех остальных непустых группируемых столбцах, они помещаются в одну и ту же группу.
Во многих функциях допускается использование ключевых слов ALL и DISTINCT. Ключевое слово ALL выполняет агрегирование всех строк исходного набора данных. При указании ключевого слова DISTINCT будет выполняться агрегирование только уникальных строк. Все повторяющиеся строки будут проигнорированы. По умолчанию выполняется агрегирование всех строк, то есть используется ключевое слово ALL.
Синтаксис:
GROUP BY [АLL] <условие_группировки> [,...,n]
Задание:
Сформулировать и реализовать запросы следующих типов:
а) сортировка по значению одного столбца, пример:
SELECT <Список__выбора>
FROM <Иcxoднaя_тaблицa>
ORDER BY <условие сортировки> DESC
б) сортировка по нескольким столбцам;
в) условия для вычисления полей, пример:
Создайте отчет о ежемесячной зарплате всего персонала с указанием табельного номера, имени, фамилии и суммы зарплаты.
SELECT sno, fname, Iname, salary/12
FROM staff
Где, значение salary – сумма зарплаты за год.
В данном случае желаемый результат может быть достигнут простым делением суммы зарплаты за год на 12.
г) условие, которое позволяет выполнить группировку по каким–либо критериям (для вашей базы)
Функция AVG()
Вычисляет среднее значение для указанного столбца.
Синтаксис: AVG ([ALL| DISTINCT]<выражение>)
Функция COUNT()
Подсчитывает количество строк в группе (при выполнении группировки) или количество строк результата запроса.
Синтаксис: COUNT ({[АLL|DISTINCT]<выражение>}| *)
Параметр <выражение> в простейшем случае представляет собой имя столбца. Указание символа (*) предписывает считать общее количество строк независимо от того, содержат ли они значения NULL или нет.
ПРИМЕР:
Определите количество персонала, работающего в каждом из отделений компании, а также их суммарную заработную плату.
SELECT bno, COUNT(sno) AS count, SUM(salary AS sum
FROM staff
GROUP BY bno
ORDER BY bno
Функция МАХ()
Возвращает максимальное значение в указанном диапазоне. Эта функция может использоваться как в обычных запросах, так и в запросах, так и в запросах с группировкой.
Синтаксис: МАХ ([ALL|DISTINCT]<Выражение>)
ПРИМЕР:
Вычисление максимальной заработной платы.
SELECT MAX(salary) AS max,
FROM staff;
Функция MIN()
Возвращает минимальное значение в указанном диапазоне.
Синтаксис: MIN ([ALL|DISTINCT]<выражение>)
ПРИМЕР:
Вычисление минимальной заработной платы.
SELECT MIN(salary) AS min
FROM staff;
Функция SUM()
Выполняет обычное суммирование значений в указанном
диапазоне.
Синтаксис: SUM ([АLL|DISTINCT]<выражение>)
Задание:
Сформулировать и реализовать запросы следующих типов:
а) условие вычисление среднего значения для вашего столбца (может быть для зарплаты, количества и т.п.);
б) условие для счета количества строк;
в) условие для определения максимального значения;
г) условие для определения минимального значения;
д) условие для выполнения суммирования значений.
Условие Having
Предназначено для использования совместно с условием GROUP BY для задания ограничений, указываемых с целью отбора тех групп, которые будут помещены в результирующую таблицу запроса. Хотя условия Having и WHERE имеют сходный синтаксис, их назначение различно.
Условие WHERE предназначено для фильтрации отдельных строк, используемых для группирования или помещаемых в результирующую таблицу запроса, тогда как условие HAVING используется для фильтрации групп, помещаемых в результирующую таблицу запроса. Стандарт ISO требует, чтобы имена столбцов, используемые в условии HAVING, обязательно присутствовали в списке условия GROUP BY или применялись в обобщающих функциях.
На практике условия поиска всегда включают в предложение HAVING, по меньшей мере, одну обобщающую функцию, в противном случае эти условия поиска должны быть помещены в предложение WHERE и применяться для отбора отдельных строк. Не забывайте, что обобщающие функции не могут использоваться в предложении WHERE.
Условие HAVING не является необходимой частью языка SQL — любой запрос, написанный с использованием условия HAVING, может быть представлен в ином виде.
ПРИМЕР:
Для каждого отделения компании с численностью персонала более одного человека определите количество работающих и сумму их заработной платы.
SELECT bno, COUNT(sno) AS count, SUM(salary) AS sum
FROM staff
GROUP BY bno
HAVING COUNT(sno) > 1
ORDER BY bno;
Здесь используются дополнительные ограничения, указывающие на то, что нас интересуют сведения только о тех отделениях компании, в которых работает больше одного человека. Подобное требование налагается на группы, поэтому в запросе следует использовать условие HAVING.
Задание:
Сформулировать и реализовать запросы следующих типов:
а) условие для вашего ограничения
Вложенные запросы
Это использование законченных операторов SELECT, внедренных в тело другого оператора SELECT. Внешний (второй) оператор SELECT использует результат выполнения внутреннего (первого) оператора для определения содержания окончательного результата всей операции. Внутренние запросы могут быть помещены в предложения WHERE и HAVING внешнего оператора SELECT — в этом случае они получают название подзапросов, или вложенных запросов. Кроме того, внутренние операторы SELECT могут использоваться в операторах INSERT, UPDATE и DELETE.
Существует три типа подзапросов.
– скалярный подзапрос возвращает значение, выбираемое из пересечения
одного столбца с одной строкой, — т.е. единственное значение. В принципе, скалярный подзапрос может использоваться везде, где требуется указать единственное значение.
– строковый подзапрос возвращает значения нескольких столбцов таблицы, но в виде единственной строки. Строковый подзапрос может использоваться везде, где применяется конструктор строковых значений – обычно это предикаты.
– табличный подзапрос возвращает значения одного или больше столбцов таблицы, размещенные в более чем одной строке. Табличный подзапрос может использоваться везде, где допускается указывать таблицу — например, как операнд предиката IN.
Подзапрос представляет собой инструмент создания временной таблицы, содержимое которой извлекается и обрабатывается внешним оператором. Подзапрос может указываться непосредственно после операторов сравнения (т.е. операторов =, <, >, <=, >=, 0) в предложениях WHERE и HAVING. Текст подзапроса должен быть заключен в скобки.
ПРИМЕР:
Составьте список персонала, работающего в отделении компании, расположенном, на улице'Main St в доме 163.
SELECT sno, fname, Iname, position
FROM staff
WHERE bno = (SELECT bno
FROM branch
WHERE street = ‘163 Main St’);
Внутренний оператор SELECT (SELECT bno FROM branch...) предназначен для определения номера отделения компании, расположенного по адресу 463 Main St. (Существует только одно подобное отделение компании, поэтому данный пример является примером скалярного подзапроса). После получения номера требуемого отделения выполняется внешний подзапрос, предназначенный для выборки подробных сведений о работниках этого отделения. Иначе говоря, внутренний оператор SELECT возвращает таблицу, состоящую из единственного значения 'ВЗ'. Оно представляет собой номер того отделения компании, которое расположено в доме 163 на улице Main Street. В результате внешний оператор SELECT приобретает следующий вид:
SELECT sno, fname, Iname, position
FROM staff
WHERE bno = ‘B3’;
ПРИМЕР:
Составьте перечень сдаваемых в аренду объектов, за которые отвечают работники отделения компании, расположенного на улице 'Main St' в доме 163.
SELECT pno, street, area, city, pcode, type, rooms, rent
FROM property for rent
WHERE sno IN
(SELECT sno FROM staff WHERE bno =
(SELECT bno
FROM branch
WHERE street = '163 Main St'));
Первый, самый внутренний, запрос предназначен для определения номера отделения компании, расположенного по адресу '163 Main St'. Второй, промежуточный, запрос осуществляет выборку сведений о персонале, работающем в этом отделении. В данном случае выбирается более одной строки данных, и поэтому во внешнем запросе нельзя использовать оператор сравнения =. Вместо него необходимо использовать ключевое слово IN. Внешний запрос осуществляет выборку сведений о сдаваемых в аренду объектах, за которые отвечают те работники компании, данные о которых были получены в результате выполнения промежуточного запроса.
Задание:
Сформулировать и реализовать запросы следующих типов:
а) подзапрос для проверки неравенств;
б) подзапрос с использованием предиката IN.
Ключевые слова ANY и ALL
Могут использоваться с подзапросами, возвращающими один столбец чисел. Если подзапросу будет предшествовать ключевое слово ALL, условие сравнения считается выполненным только в том случае, если оно выполняется для всех значений в результирующем столбце подзапроса. Если записи подзапроса предшествует ключевое слово ANY, то условие сравнения будет считаться выполненным, если оно выполняется хотя бы для одного из значений в результирующем столбце подзапроса. Если в результате выполнения подзапроса будет получено пустое значение, то для ключевого слова ALL условие сравнения будет считаться выполненным, а для ключевого слова — невыполненным. Согласно стандарту ISO дополнительно можно использовать ключевое слово SOME, являющееся синонимом ключевого слова ANY.
ПРИМЕР:
Найдите всех работников, чья зарплата превышает зарплату хотя бы одного работника отделения компании под номером 'ВЗ'.
SELECT sno, fname, Iname, position, salary FROM staff WHERE salary > SOME
(SELECT salary
FROM staff
WHERE bno =БЗ');
Хотя этот запрос может быть записан с использованием подзапроса, определяющего минимальную зарплату персонала отделения под номером 'ВЗ', после чего внешний подзапрос сможет выбрать сведения обо всем персонале компании, чья зарплата превосходит это значение, возможен и другой подход, заключающийся в использовании ключевых слов SOME/ANY. В этом случае внутренний подзапрос создает набор числовых значений (12 000, 18 000, 24 000), а внешний запрос выбирает сведения о тех работниках, чья зарплата больше любого из значений в этом наборе (фактически, больше минимального значения — 12 000). Подобный альтернативны метод можно считать более естественным, чем определение в подзапросе минимальной зарплаты.
Задание:
Создайте подзапрос с использованием одного из ключевых слов.