Использование выражений в командах Select.

Выражения можно структурировать не только в предложении Where команд обновления, но и в предложении Select. А именно, можно создавать выражения и при этом использовать столбцы в качестве аргументов.

Самая распространенная операция, используемая для составления выражений – это операция конкатенации (объединения). Для написания этой операции в языке SQL используются символы ||.

Например: составить список студентов группы 2202-1 можно следующим образом:

Select family|| ' ' ||name|| ' '||otch

From persons

where nomer_gruppy = ‘2202-1’

order by family desc;

Если в списке не удобно использовать полные имена и отчества, то можно получить выражение, определяющее только инициалы:

Select family||' '||substr(name,1,1)||'.'||substr(otch,1,1)||'.' as fio

From students

where nomer_gruppy = ‘2202-1’

order by family desc;

В данном примере используется также одна из распространенных функций языка SQL – substr(), которая аналогично функции Instr(), используемая для поиска подстрок в СУБД Access.

 

 

Работа с данными с помощью функций агрегирования.

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

1. COUNT определяет количество строк или значений поля, выбранных посредством запроса и не являющихся NULL- значений;

2. SUM вычисляет арифметическую сумму всех выбранных значений данного поля;

3. AVG вычисляет среднее значение для всех выбранных значений данного поля;

4. MAX вычисляет наибольшее из всех выбранных значений данного поля;

5. MIN вычисляет наименьшее из всех выбранных значений данного поля;

 

Функции агрегирования используются как имена полей в предложении запроса SELECT с одним исключением: имена полей применяются как аргументы. Для COUNT, MAX, MIN – цифровые и символьные поля. При употреблении с символьными полями MAX и MIN применяются как MIN предлагает минимальное (первое), а MAX – максимальное (последнее) значения в соответствии с алфавитным порядком.

Чтобы найти сумму (SUM) всех заявок из таблицы Заказы, можно ввести следующий запрос:

SELECT SUM(kol) FROM orders;

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

Похожей операцией является поиск среднего значения:

SELECT AVG(kol) FROM orders;

 

Функция COUNT подсчитывает количество значений в данном столбце или количество строк в таблице. Когда подсчитываются значения по столбцу, в команде используется DISTING для подсчета числа различных значений данного поля. Можно использовать его например, для подсчета количества продавцов, имеющих в настоящее время заказы в таблице Заказы:

SELECT COUNT (DISTING name) FROM orders;

Примечание! В данном примере DISTING вместе со следующим за ним именем поля, к которому он применяется, заключен в круглые скобки и не следует непосредственно за SELECT, как это должно быть. Указанным способом DISTING можно применять с любой функцией агрегирования, но чаще всего он используется с COUNT. Применение его с MAX и MIN бесполезно; а используя SUM и AVG, необходимо включение в выходные данные повторяющихся значений, так как они влияют на сумму и среднее для значений всех столбцов.

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

SELECT COUNT (*) FROM prodav;

 

COUNT со звездочкой включает как NULL – значения, так и повторяющиеся значения, значит DISTING в этом случае не применим. По этой причине в результате получается число, превышающее COUNT для отдельного поля, который исключает из этого поля все избыточные строки или NULL – значения.

DISTING исключен для COUNT(*), поскольку он не имеет смысла для хорошо спроектированной и управляемой базы данных.

Агрегатные функции могут также (во многих реализациях) иметь аргумент ALL, который размещается перед именем поля, как и DISTING, но обозначает противоположное: включить дубликаты. Различие между ALL и * при использовании COUNT заключается в следующем:

§ ALL использует имя поля в качестве аргумента;

§ ALL не подсчитывает NULL – значения.

 

Поскольку * является единственным аргументом, который включает NULL – значения и используется только с COUNT, функции, отличные от COUNT, игнорируют NULL – значения в любом случае. Следующая команда осуществляет подсчет количества значений поля rating, отличных от NULL – значений, в таблице pokypat (включая повторения):

SELECT COUNT (ALL rating) FROM pokypat;

 

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

Конечно можно сделать отдельный запрос на каждого продавца, выбрав MAX(kol) для таблицы Заказы, но на это уйдет много времени.

Используя GROUP BY можно объединить все запросы в одном:

 

SELECT person_n, MAX(kol) FROM Orders GROUP BY person_n;

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

GROUP BY также применяется с многозначными полями. Обращаясь к предыдущему примеру, можно предположить, что необходимо увидеть наибольший заказ, сделанный каждому продавцу на каждую дату. Для этого нужно сгруппировать данные таблицы Заказы по дате внутри одного и того же поля и применить функцию MAX к каждой группе. Запрос:

 

SELECT person_n, data, max(kol) FROM Orders GROUP BY person_n, data;

Примечание! Пустые группы, (например даты, когда данный продавец не получал заказов), не выводятся в выходных данных.

 

Использование множества таблиц в одном запросе.

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

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

При соединении таблиц полное имя столбца состоит из имени таблицы, непосредственно за которым стоит точка, а за ней – имя столбца. Например,

 

Prodav.person_n

Pokypat.ynik_n

Orders.kol

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

Например, чтобы определить фамилию, имя, отчество продавцов, получивших заказы больше 300, то запрос следует написать:

 

SELECT Prodav.fam, Prodav.name,Prodav.otch, Orders.kol

FROM Prodav, Orders

WHERE Orders.kol > 300 AND Prodav.person_n=Orders. person_n;

Этот запрос объединяет две таблицы. Но конструировать запросы можно путем соединения более чем двух таблиц. Предположим, нужно найти все заявки покупателей, не находящихся в том же городе, что и их продавец. Для этого требуется связать все три таблицы:

 

SELECT Orders.n, Pokypat.name, Orders.ynik_n, Orders.person_n

FROM Pokypat, Prodav, Orders

WHERE Pokypat.city <> Prodav.city

AND Orders.ynik_n=Pokypat.ynik_n

AND Orders.person_n= Prodav.person_n;

 

Исходя из данного примера, видно, что если сама команда сложна, то логика написания команды достаточно проста.

 

Вложение запросов.

SQL позволяет вкладывать запросы друг в друга. Обычно внутренний запрос генерирует значения, которые тестируются на предмет истинности условия. Синтаксис вложенного запроса:

 

SELECT * FROM <имя таблицы№1>

WHERE <имя столбца>=

(SELECT <имя того же столбца> FROM <имя таблицы№2>

WHERE <условие>);

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

 

!!!!!! мы конечно могли бы сначала написать запрос на вывод уникального номера продавца из таблицы Продавцы, а потом написать запрос на заказы из таблицы Заказы, но вложенный запрос как раз позволяет написать команду, которая объединит оба эти запроса.

SELECT * FROM Orders

WHERE person_n=

(SELECT person_n FROM Prodav WHERE fam=’Петров’);

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